pears.GetTempAWRRate

CREATE FUNCTION "pears"."GetTempAWRRate"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "PayOrCharge" CHAR(1),IN "PlacOrVac" CHAR(1),IN "ID" CHAR(20) ) 
RETURNS DOUBLE
BEGIN
  DECLARE "rv" DOUBLE;
  DECLARE "pvacid" CHAR(20);
  DECLARE "ppersid" CHAR(20);
  DECLARE "pplacid" CHAR(20);
  DECLARE "pjobtypeid" CHAR(20);
  DECLARE "pdirectengagement" SMALLINT;
  SET "rv" = NULL;
  IF "placorvac" = 'P' THEN
    SET "pplacid" = "id";
    SELECT "pl"."vacancyid","em"."personid","v"."tempjobtypeid","isnull"("pl"."directengagement",0) INTO "pvacid","ppersid","pjobtypeid","pdirectengagement" FROM "placement" AS "pl" KEY JOIN("employment" AS "em","vacancy" AS "v") WHERE "pl"."placementid" = "pplacid";
    FOR "PRATEFOR" AS "PRATECUR" no scroll cursor FOR
      SELECT "vacancytemprate"("pvacid","temppayband"."temppayBandID","tempjobrate"."grade","tempjobrate"."filter1","tempjobrate"."filter2","tempjobrate"."filter3","tempjobrate"."Startdate","tempjobrate"."Enddate") AS "VacRateID",
        "mastertemprate"("pJobTypeID","temppayband"."temppayBandID","tempjobrate"."grade","tempjobrate"."filter1","tempjobrate"."filter2","tempjobrate"."filter3","tempjobrate"."Startdate","tempjobrate"."Enddate") AS "MasterID",
        "temppayband"."sortorder",
        "temppayband"."description",
        "tempjobrate"."Startdate" AS "StartDate",
        "tempjobrate"."Enddate" AS "EndDate",
        "tempjobrate"."Grade",
        "isnull"("tempjobrate"."filter1",'X') AS "soAWR",
        "tempjobrate"."TempJobRateID",
        "temppayband"."TempPayBandID",
        "tempjobrate"."PayRate" AS "SavedPayRate",
        (SELECT FIRST "payrate" FROM "tempjobratemaster" WHERE "tempjobratemasterid" = "masterid") AS "MasterPayRate",
        "isnull"((SELECT FIRST "payrate" FROM "tempjobrate" WHERE "tempjobrateid" = "vacrateid"),"MasterPayRate") AS "OrigPayRate",
        "ModifiedTempRate"('P',"pvacid","ppersid","pplacid","temppayband"."temppaybandid","OrigPayRate") AS "ModifiedPayRate",
        "isnull"("SavedPayRate","ModifiedPayRate") AS "PayRate",
        "tempjobrate"."ChargeRate" AS "SavedChargeRate",
        (SELECT FIRST "chargerate" FROM "tempjobratemaster" WHERE "tempjobratemasterid" = "masterid") AS "MasterChargeRate",
        "isnull"((SELECT FIRST "chargerate" FROM "tempjobrate" WHERE "tempjobrateid" = "vacrateid"),"MasterChargeRate") AS "OrigChargeRate",
        "round"("origchargerate"-("isnull"((SELECT FIRST "discount" FROM "vacancy" WHERE "vacancyid" = "pvacid"),0)*"origchargerate"/100),2) AS "DiscountedCharge",
        "ModifiedTempRate"('C',"pvacid","ppersid","pplacid","temppayband"."temppaybandid","DiscountedCharge") AS "ModifiedChargeRate",
        "isnull"("SavedChargeRate","ModifiedChargeRate") AS "ChargeRate",
        "isnull"("temppayband"."IsExpenses",0) AS "IsExpenses"
        FROM "tempjobrate" KEY JOIN "temppayband"
        WHERE "tempjobrate"."placementid" = "pplacid"
        AND("startdate" IS NULL OR "startdate" <= CURRENT DATE) AND("enddate" IS NULL OR "enddate" >= CURRENT DATE)
        AND("tempjobrate"."grade" IS NULL OR "tempjobrate"."grade" LIKE "isnull"("entitygrade"("pvacid","ppersid","pplacid",NULL),'%'))
        AND("tempjobrate"."filter1" = 'Y' OR "tempjobrate"."filter1" IS NULL OR "tempjobrate"."filter1" LIKE "isnull"("entityratefilter"(1,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND("tempjobrate"."filter2" IS NULL OR "tempjobrate"."filter2" LIKE "isnull"("entityratefilter"(2,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND("tempjobrate"."filter3" IS NULL OR "tempjobrate"."filter3" LIKE "isnull"("entityratefilter"(3,NULL,"pvacid","ppersid","pplacid",NULL),'%')) UNION ALL
      SELECT "tempjobrate"."TempJobRateID" AS "VacRateID",
        "mastertemprate"("pJobTypeID","temppayband"."temppayBandID","tempjobrate"."grade","tempjobrate"."filter1","tempjobrate"."filter2","tempjobrate"."filter3","tempjobrate"."Startdate","tempjobrate"."Enddate") AS "MasterID",
        "temppayband"."sortorder",
        "temppayband"."description",
        "tempjobrate"."Startdate" AS "StartDate",
        "tempjobrate"."Enddate" AS "EndDate",
        "tempjobrate"."Grade",
        "isnull"("tempjobrate"."filter1",'X') AS "soAWR",
        CAST(NULL AS CHAR(20)) AS "TempJobRateID",
        "temppayband"."TempPayBandID",
        CAST(NULL AS DOUBLE) AS "SavedPayRate",
        (SELECT FIRST "payrate" FROM "tempjobratemaster" WHERE "tempjobratemasterid" = "masterid") AS "MasterPayRate",
        "isnull"("tempjobrate"."payrate","MasterPayRate") AS "OrigPayRate",
        "ModifiedTempRate"('P',"pvacid","ppersid","pplacid","temppayband"."temppaybandid","OrigPayRate") AS "ModifiedPayRate",
        "ModifiedPayRate" AS "PayRate",
        CAST(NULL AS DOUBLE) AS "SavedChargeRate",
        (SELECT FIRST "chargerate" FROM "tempjobratemaster" WHERE "tempjobratemasterid" = "masterid") AS "MasterChargeRate",
        "isnull"("tempjobrate"."chargerate","MasterChargeRate") AS "OrigChargeRate",
        "round"("origchargerate"-("isnull"((SELECT FIRST "discount" FROM "vacancy" WHERE "vacancyid" = "pvacid"),0)*"origchargerate"/100),2) AS "DiscountedCharge",
        "ModifiedTempRate"('C',"pvacid","ppersid","pplacid","temppayband"."temppaybandid","DiscountedCharge") AS "ModifiedChargeRate",
        "ModifiedChargeRate" AS "ChargeRate",
        "isnull"("temppayband"."IsExpenses",0) AS "IsExpenses"
        FROM "tempjobrate" KEY JOIN "temppayband"
        WHERE "tempjobrate"."vacancyid" = "pvacid" AND "pdirectengagement" = 0
        AND "OverrideTempRate"(NULL,"pplacid","temppayband"."temppaybandid","tempjobrate"."Grade","tempjobrate"."filter1","tempjobrate"."filter2","tempjobrate"."filter3","tempjobrate"."Startdate","tempjobrate"."Enddate") IS NULL
        AND("startdate" IS NULL OR "startdate" <= CURRENT DATE) AND("enddate" IS NULL OR "enddate" >= CURRENT DATE)
        AND("tempjobrate"."grade" IS NULL OR "tempjobrate"."grade" LIKE "isnull"("entitygrade"("pvacid","ppersid","pplacid",NULL),'%'))
        AND("tempjobrate"."filter1" = 'Y' OR "tempjobrate"."filter1" IS NULL OR "tempjobrate"."filter1" LIKE "isnull"("entityratefilter"(1,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND("tempjobrate"."filter2" IS NULL OR "tempjobrate"."filter2" LIKE "isnull"("entityratefilter"(2,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND("tempjobrate"."filter3" IS NULL OR "tempjobrate"."filter3" LIKE "isnull"("entityratefilter"(3,NULL,"pvacid","ppersid","pplacid",NULL),'%')) UNION ALL
      SELECT CAST(NULL AS CHAR(20)) AS "VacRateID",
        "tempjobratemaster"."tempjobratemasterid" AS "masterid",
        "temppayband"."sortorder",
        "temppayband"."description",
        "tempjobratemaster"."Startdate" AS "StartDate",
        "tempjobratemaster"."Enddate" AS "EndDate",
        "tempjobratemaster"."Grade",
        "isnull"("tempjobratemaster"."filter1",'X') AS "soAWR",
        CAST(NULL AS CHAR(20)) AS "TempJobRateID",
        "temppayband"."TempPayBandID",
        CAST(NULL AS DOUBLE) AS "SavedPayRate",
        "tempjobratemaster"."payrate" AS "MasterPayRate",
        "MasterPayRate" AS "OrigPayRate",
        "ModifiedTempRate"('P',"pvacid","ppersid","pplacid","temppayband"."temppaybandid","OrigPayRate") AS "ModifiedPayRate",
        "ModifiedPayRate" AS "PayRate",
        CAST(NULL AS DOUBLE) AS "SavedChargeRate",
        "tempjobratemaster"."chargerate" AS "MasterChargeRate",
        "MasterChargeRate" AS "OrigChargeRate",
        "round"("origchargerate"-("isnull"((SELECT FIRST "Discount" FROM "vacancy" WHERE "vacancyid" = "pvacid"),0)*"origchargerate"/100),2) AS "DiscountedCharge",
        "ModifiedTempRate"('C',"pvacid","ppersid","pplacid","temppayband"."temppaybandid","DiscountedCharge") AS "ModifiedChargeRate",
        "ModifiedChargeRate" AS "ChargeRate",
        "isnull"("temppayband"."IsExpenses",0) AS "IsExpenses"
        FROM "tempjobratemaster" KEY JOIN("tempjobtype","temppayband")
        WHERE "tempjobtype"."tempjobtypeid" = "pjobtypeid" AND "pdirectengagement" = 0
        AND "OverrideTempRate"("pvacid","pplacid","temppayband"."temppaybandid","tempjobratemaster"."Grade","tempjobratemaster"."filter1","tempjobratemaster"."filter2","tempjobratemaster"."filter3","tempjobratemaster"."Startdate","tempjobratemaster"."Enddate") IS NULL
        AND("startdate" IS NULL OR "startdate" <= CURRENT DATE) AND("enddate" IS NULL OR "enddate" >= CURRENT DATE)
        AND("tempjobratemaster"."grade" IS NULL OR "tempjobratemaster"."grade" LIKE "isnull"("entitygrade"("pvacid","ppersid","pplacid",NULL),'%'))
        AND("tempjobratemaster"."filter1" = 'Y' OR "tempjobratemaster"."filter1" IS NULL OR "tempjobratemaster"."filter1" LIKE "isnull"("entityratefilter"(1,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND("tempjobratemaster"."filter2" IS NULL OR "tempjobratemaster"."filter2" LIKE "isnull"("entityratefilter"(2,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND("tempjobratemaster"."filter3" IS NULL OR "tempjobratemaster"."filter3" LIKE "isnull"("entityratefilter"(3,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND "tempjobtype"."dynamic" = 1
        ORDER BY "isExpenses" ASC,"soAWR" DESC,"sortorder" ASC,"description" ASC,"startdate" ASC,"enddate" ASC,"grade" ASC FOR READ ONLY do
      IF "payorcharge" = 'P' THEN
        SET "rv" = "payrate"
      ELSE
        SET "rv" = "chargerate"
      END IF;
      RETURN "rv"
    END FOR
  ELSE SET "pvacid" = "id";
    SET "ppersid" = NULL;
    SET "pplacid" = NULL;
    SELECT "tempjobtypeid" INTO "pjobtypeid" FROM "vacancy" WHERE "vacancyid" = "pvacid";
    FOR "VRATEFOR" AS "VRATECUR" no scroll cursor FOR
      SELECT "mastertemprate"("pJobTypeID","temppayband"."temppayBandID","tempjobrate"."grade","tempjobrate"."filter1","tempjobrate"."filter2","tempjobrate"."filter3","tempjobrate"."Startdate","tempjobrate"."Enddate") AS "MasterID",
        "temppayband"."sortorder",
        "temppayband"."description",
        "tempjobrate"."Startdate" AS "StartDate",
        "tempjobrate"."Enddate" AS "EndDate",
        "tempjobrate"."Grade",
        "isnull"("tempjobrate"."filter1",'X') AS "soAWR",
        "tempjobrate"."VacancyID",
        "tempjobrate"."TempJobRateID",
        "temppayband"."TempPayBandID",
        "tempjobrate"."PayRate" AS "SavedPayRate",
        (SELECT FIRST "payrate" FROM "tempjobratemaster" WHERE "tempjobratemasterid" = "masterid") AS "OrigPayRate",
        "isnull"("SavedPayRate","OrigPayRate") AS "PayRate",
        "tempjobrate"."ChargeRate" AS "SavedChargeRate",
        (SELECT FIRST "chargerate" FROM "tempjobratemaster" WHERE "tempjobratemasterid" = "masterid") AS "OrigChargeRate",
        "isnull"("SavedChargeRate","OrigChargeRate") AS "ChargeRate",
        "round"("chargerate"-("isnull"("vacancy"."discount",0)*"chargerate"/100),2) AS "DiscountedCharge",
        "isnull"("temppayband"."IsExpenses",0) AS "IsExpenses"
        FROM "tempjobrate" KEY JOIN("vacancy","temppayband")
        WHERE "vacancy"."vacancyid" = "pvacid"
        AND("startdate" IS NULL OR "startdate" <= CURRENT DATE) AND("enddate" IS NULL OR "enddate" >= CURRENT DATE)
        AND("tempjobrate"."grade" IS NULL OR "tempjobrate"."grade" LIKE "isnull"("entitygrade"("pvacid",NULL,NULL,NULL),'%'))
        AND("tempjobrate"."filter1" = 'Y' OR "tempjobrate"."filter1" IS NULL OR "tempjobrate"."filter1" LIKE "isnull"("entityratefilter"(1,NULL,"pvacid",NULL,NULL,NULL),'%'))
        AND("tempjobrate"."filter2" IS NULL OR "tempjobrate"."filter2" LIKE "isnull"("entityratefilter"(2,NULL,"pvacid",NULL,NULL,NULL),'%'))
        AND("tempjobrate"."filter3" IS NULL OR "tempjobrate"."filter3" LIKE "isnull"("entityratefilter"(3,NULL,"pvacid",NULL,NULL,NULL),'%')) UNION ALL
      SELECT "tempjobratemaster"."tempjobratemasterid" AS "masterid",
        "temppayband"."sortorder",
        "temppayband"."description",
        "tempjobratemaster"."Startdate" AS "StartDate",
        "tempjobratemaster"."Enddate" AS "EndDate",
        "tempjobratemaster"."Grade",
        "isnull"("tempjobratemaster"."filter1",'X') AS "soAWR",
        CAST("pvacid" AS CHAR(20)) AS "VacancyID",
        CAST(NULL AS CHAR(20)) AS "TempJobRateID",
        "temppayband"."TempPayBandID",
        CAST(NULL AS DOUBLE) AS "SavedPayRate",
        "tempjobratemaster"."payrate" AS "OrigPayRate",
        "OrigPayRate" AS "PayRate",
        CAST(NULL AS DOUBLE) AS "SavedChargeRate",
        "tempjobratemaster"."chargerate" AS "OrigChargeRate",
        "OrigChargeRate" AS "ChargeRate",
        "round"("chargerate"-("isnull"((SELECT "Discount" FROM "vacancy" WHERE "vacancyid" = "pvacid"),0)*"chargerate"/100),2) AS "DiscountedCharge",
        "isnull"("temppayband"."IsExpenses",0) AS "IsExpenses"
        FROM "tempjobratemaster" KEY JOIN("tempjobtype","temppayband")
        WHERE "tempjobtype"."tempjobtypeid" = "pjobtypeid"
        AND "OverrideTempRate"("pvacid",NULL,"temppayband"."temppaybandid","tempjobratemaster"."Grade","tempjobratemaster"."filter1","tempjobratemaster"."filter2","tempjobratemaster"."filter3","tempjobratemaster"."Startdate","tempjobratemaster"."Enddate") IS NULL
        AND("startdate" IS NULL OR "startdate" <= CURRENT DATE) AND("enddate" IS NULL OR "enddate" >= CURRENT DATE)
        AND("tempjobratemaster"."grade" IS NULL OR "tempjobratemaster"."grade" LIKE "isnull"("entitygrade"("pvacid",NULL,NULL,NULL),'%'))
        AND("tempjobratemaster"."filter1" = 'Y' OR "tempjobratemaster"."filter1" IS NULL OR "tempjobratemaster"."filter1" LIKE "isnull"("entityratefilter"(1,NULL,"pvacid",NULL,NULL,NULL),'%'))
        AND("tempjobratemaster"."filter2" IS NULL OR "tempjobratemaster"."filter2" LIKE "isnull"("entityratefilter"(2,NULL,"pvacid",NULL,NULL,NULL),'%'))
        AND("tempjobratemaster"."filter3" IS NULL OR "tempjobratemaster"."filter3" LIKE "isnull"("entityratefilter"(3,NULL,"pvacid",NULL,NULL,NULL),'%'))
        AND "tempjobtype"."dynamic" = 1
        ORDER BY "IsExpenses" ASC,"soAWR" DESC,"sortorder" ASC,"description" ASC,"startdate" ASC,"enddate" ASC,"grade" ASC FOR READ ONLY do
      IF "payorcharge" = 'P' THEN
        SET "rv" = "payrate"
      ELSE
        SET "rv" = "discountedcharge"
      END IF;
      RETURN "rv"
    END FOR END IF;
  RETURN "rv"
END