database:functions:pears_gettempratebydesc



pears.GetTempRateByDesc

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Original SQL

CREATE FUNCTION "pears"."GetTempRateByDesc"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "PayOrCharge" CHAR(1),IN "PlacOrVac" CHAR(1),IN "ID" CHAR(20),IN "descrp" CHAR(50),IN @awr tinyint,IN @grade CHAR(4),IN @ltd CHAR(4) ) 
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;
  DECLARE "pawrval" CHAR(1);
  DECLARE "pawrfilter" CHAR(1);
  DECLARE "pawr" tinyint;
  --  declare @sql long varchar;
  SET "rv" = NULL;
  SET "pawr" = "isnull"(@awr,0);
  IF "pawr" = 0 THEN
    SET "pawrval" = 'Z';
    SET "pawrfilter" = ''
  ELSE
    SET "pawrval" = 'X';
    SET "pawrfilter" = 'Y'
  END IF;
  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",
        "tempjobrate"."filter3",
        "isnull"("tempjobrate"."filter2","pawrval") AS "soltd",
        "isnull"("tempjobrate"."filter1","pawrval") 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",
        IF "pawr" = 0 THEN
          ("RANK"() OVER(ORDER BY "IsExpenses" ASC,"startdate" ASC,"grade" ASC,"filter3" ASC,"soLtd" ASC,"soAWR" ASC,"temppayband"."sortorder" ASC))
        ELSE("RANK"() OVER(ORDER BY "IsExpenses" ASC,"soAWR" DESC,"temppayband"."sortorder" ASC,"temppayband"."description" ASC,"startdate" ASC,"enddate" ASC,"grade" ASC))
        endif AS "ranka"
        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("isnull"("tempjobrate"."grade",'') = @grade OR("tempjobrate"."grade" LIKE "isnull"("entitygrade"("pvacid","ppersid","pplacid",NULL),'%') AND @grade = ''))
        AND(("isnull"("tempjobrate"."filter1","pawrfilter") = "pawrfilter") OR "tempjobrate"."filter1" LIKE "isnull"("entityratefilter"(1,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND("isnull"("tempjobrate"."filter2",'') = @ltd OR("tempjobrate"."filter2" LIKE "isnull"("entityratefilter"(2,NULL,"pvacid","ppersid","pplacid",NULL),'%') AND @ltd = ''))
        AND("tempjobrate"."filter3" IS NULL OR "tempjobrate"."filter3" LIKE "isnull"("entityratefilter"(3,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND "temppayband"."description" LIKE "string"("descrp",'%') 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",
        "tempjobrate"."filter3",
        "isnull"("tempjobrate"."filter2","pawrval") AS "soltd",
        "isnull"("tempjobrate"."filter1","pawrval") 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",
        IF "pawr" = 0 THEN
          ("RANK"() OVER(ORDER BY "IsExpenses" ASC,"startdate" ASC,"grade" ASC,"filter3" ASC,"soLtd" ASC,"soAWR" ASC,"temppayband"."sortorder" ASC))
        ELSE("RANK"() OVER(ORDER BY "isExpenses" ASC,"soAWR" DESC,"temppayband"."sortorder" ASC,"temppayband"."description" ASC,"startdate" ASC,"enddate" ASC,"grade" ASC))
        endif AS "ranka"
        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("isnull"("tempjobrate"."grade",'') = @grade OR("tempjobrate"."grade" LIKE "isnull"("entitygrade"("pvacid","ppersid","pplacid",NULL),'%') AND @grade = ''))
        AND(("isnull"("tempjobrate"."filter1","pawrfilter") = "pawrfilter") OR "tempjobrate"."filter1" LIKE "isnull"("entityratefilter"(1,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND("isnull"("tempjobrate"."filter2",'') = @ltd OR("tempjobrate"."filter2" LIKE "isnull"("entityratefilter"(2,NULL,"pvacid","ppersid","pplacid",NULL),'%') AND @ltd = ''))
        AND("tempjobrate"."filter3" IS NULL OR "tempjobrate"."filter3" LIKE "isnull"("entityratefilter"(3,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND "temppayband"."description" LIKE "string"("descrp",'%') 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",
        "tempjobratemaster"."filter3",
        "isnull"("tempjobratemaster"."filter2","pawrval") AS "soltd",
        "isnull"("tempjobratemaster"."filter1","pawrval") 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",
        IF "pawr" = 0 THEN
          ("RANK"() OVER(ORDER BY "IsExpenses" ASC,"startdate" ASC,"grade" ASC,"filter3" ASC,"soLtd" ASC,"soAWR" ASC,"temppayband"."sortorder" ASC))
        ELSE("RANK"() OVER(ORDER BY "IsExpenses" ASC,"soAWR" DESC,"temppayband"."sortorder" ASC,"temppayband"."description" ASC,"startdate" ASC,"enddate" ASC,"grade" ASC))
        endif AS "ranka"
        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("isnull"("tempjobratemaster"."grade",'') = @grade OR("tempjobratemaster"."grade" LIKE "isnull"("entitygrade"("pvacid","ppersid","pplacid",NULL),'%') AND @grade = ''))
        AND(("isnull"("tempjobratemaster"."filter1","pawrfilter") = "pawrfilter") OR "tempjobratemaster"."filter1" LIKE "isnull"("entityratefilter"(1,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND("isnull"("tempjobratemaster"."filter2",'') = @ltd OR("tempjobratemaster"."filter2" LIKE "isnull"("entityratefilter"(2,NULL,"pvacid","ppersid","pplacid",NULL),'%') AND @ltd = ''))
        AND("tempjobratemaster"."filter3" IS NULL OR "tempjobratemaster"."filter3" LIKE "isnull"("entityratefilter"(3,NULL,"pvacid","ppersid","pplacid",NULL),'%'))
        AND "tempjobtype"."dynamic" = 1 AND "temppayband"."description" LIKE "string"("descrp",'%')
        ORDER BY "ranka" 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",
        "tempjobrate"."filter3",
        "isnull"("tempjobrate"."filter2","pawrval") AS "soltd",
        "isnull"("tempjobrate"."filter1","pawrval") 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",
        IF "pawr" = 0 THEN
          ("RANK"() OVER(ORDER BY "isExpenses" ASC,"startdate" ASC,"grade" ASC,"filter3" ASC,"soLtd" ASC,"soAWR" ASC,"temppayband"."sortorder" ASC))
        ELSE("RANK"() OVER(ORDER BY "isExpenses" ASC,"soAWR" DESC,"temppayband"."sortorder" ASC,"temppayband"."description" ASC,"startdate" ASC,"enddate" ASC,"grade" ASC))
        endif AS "ranka"
        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("isnull"("tempjobrate"."grade",'') = @grade OR("tempjobrate"."grade" LIKE "isnull"("entitygrade"("pvacid",NULL,NULL,NULL),'%') AND @grade = ''))
        AND(("isnull"("tempjobrate"."filter1","pawrfilter") = "pawrfilter") OR "tempjobrate"."filter1" LIKE "isnull"("entityratefilter"(1,NULL,"pvacid",NULL,NULL,NULL),'%'))
        AND("isnull"("tempjobrate"."filter2",'') = @ltd 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),'%'))
        AND "temppayband"."description" LIKE "string"("descrp",'%') UNION ALL
      SELECT "tempjobratemaster"."tempjobratemasterid" AS "masterid",
        "temppayband"."sortorder",
        "temppayband"."description",
        "tempjobratemaster"."Startdate" AS "StartDate",
        "tempjobratemaster"."Enddate" AS "EndDate",
        "tempjobratemaster"."Grade",
        "tempjobratemaster"."filter3",
        "isnull"("tempjobratemaster"."filter2","pawrval") AS "soltd",
        "isnull"("tempjobratemaster"."filter1","pawrval") 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",
        IF "pawr" = 0 THEN
          ("RANK"() OVER(ORDER BY "startdate" ASC,"grade" ASC,"filter3" ASC,"soLtd" ASC,"soAWR" ASC,"temppayband"."sortorder" ASC))
        ELSE("RANK"() OVER(ORDER BY "soAWR" DESC,"temppayband"."sortorder" ASC,"temppayband"."description" ASC,"startdate" ASC,"enddate" ASC,"grade" ASC))
        endif AS "ranka"
        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("isnull"("tempjobratemaster"."grade",'') = @grade OR("tempjobratemaster"."grade" LIKE "isnull"("entitygrade"("pvacid",NULL,NULL,NULL),'%') AND @grade = ''))
        AND(("isnull"("tempjobratemaster"."filter1","pawrfilter") = "pawrfilter") OR "tempjobratemaster"."filter1" LIKE "isnull"("entityratefilter"(1,NULL,"pvacid",NULL,NULL,NULL),'%'))
        AND("isnull"("tempjobratemaster"."filter2",'') = @ltd OR("tempjobratemaster"."filter2" LIKE "isnull"("entityratefilter"(2,NULL,"pvacid",NULL,NULL,NULL),'%') AND @ltd = ''))
        AND("tempjobratemaster"."filter3" IS NULL OR "tempjobratemaster"."filter3" LIKE "isnull"("entityratefilter"(3,NULL,"pvacid",NULL,NULL,NULL),'%'))
        AND "tempjobtype"."dynamic" = 1 AND "temppayband"."description" LIKE "string"("descrp",'%')
        ORDER BY "ranka" 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
  • database/functions/pears_gettempratebydesc.txt
  • Last modified: 2026/08/07 19:24
  • by 127.0.0.1