pears.OverrideTempRate

CREATE FUNCTION "pears"."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