pears.OverrideTempRate

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."OverrideTempRate" IS 
{CREATE FUNCTION OverrideTempRate 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( 
  IN VacID CHAR(20),IN PlacID CHAR(20),IN BandID CHAR(20),IN GradeID CHAR(4),IN pFilter1 CHAR(4),IN pFilter2 CHAR(4),IN pFilter3 CHAR(4),IN dFrom DATE,IN dTo DATE ) 
RETURNS CHAR(20)
// Purpose IS TO hide rate ROWS FROM scheme OR vacancy which are over-ridden at the relevant vacancy OR placement level. IF non-NULL VALUE returned the rate will be hidden.
// NEW method introduced MHS 25/3/13 IS less aggressive - it ONLY hides rates which are totally occluded, date-wise, BY the over-riding rate.
// Previous method commented OUT.
BEGIN
  DECLARE rv CHAR(20);
  SET rv = NULL;
  SET gradeid = TRIM(isnull(gradeid,''));
  SET pFilter1 = TRIM(isnull(pFilter1,''));
  SET pFilter2 = TRIM(isnull(pFilter2,''));
  SET pFilter3 = TRIM(isnull(pFilter3,''));
  IF TRIM(vacid) = '' THEN
    SET vacid = NULL
  END IF;
  IF TRIM(placid) = '' THEN
    SET placid = NULL
  END IF;
  IF placid IS NOT NULL THEN
    SELECT FIRST r.tempjobrateid INTO rv FROM tempjobrate AS r WHERE r.placementid = placid AND r.temppaybandid = bandid
      AND TRIM(isnull(r.grade,'')) = gradeid
      AND TRIM(isnull(r.filter1,'')) = pFilter1
      AND TRIM(isnull(r.filter2,'')) = pFilter2
      AND TRIM(isnull(r.filter3,'')) = pFilter3
      --isnull(dfrom,'1900-01-01') <= isnull(r.enddate,current date) and isnull(dto,'2200-01-01') >= isnull(r.startdate,current date)
      AND(r.enddate IS NULL OR r.enddate >= dto) AND(r.startdate IS NULL OR r.startdate <= dfrom)
  END IF;
  IF rv IS NULL AND vacid IS NOT NULL THEN
    SELECT FIRST r.tempjobrateid INTO rv FROM tempjobrate AS r WHERE r.vacancyid = vacid AND r.temppaybandid = bandid
      AND TRIM(isnull(r.grade,'')) = gradeid
      AND TRIM(isnull(r.filter1,'')) = pFilter1
      AND TRIM(isnull(r.filter2,'')) = pFilter2
      AND TRIM(isnull(r.filter3,'')) = pFilter3
      --isnull(dfrom,'1900-01-01') <= isnull(r.enddate,current date) and isnull(dto,'2200-01-01') >= isnull(r.startdate,current date)
      AND(r.enddate IS NULL OR r.enddate >= dto) AND(r.startdate IS NULL OR r.startdate <= dfrom)
  END IF;
  RETURN rv
END
}