pears.GetTempRateList

CREATE PROCEDURE "pears"."GetTempRateList"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "ppersid" CHAR(20),IN "pvacid" CHAR(20),IN "pplacid" CHAR(20),IN "pJtid" CHAR(20),IN "pweekno" INTEGER,IN "pweekstart" DATE,IN "pweekend" DATE,IN "pUnderlyingScheme" INTEGER ) 
RESULT( 
  "sortorder" INTEGER,"TempPayBandID" CHAR(20),"description" CHAR(50),"payrollflag" CHAR(10),"Grade" CHAR(4),"StartDate" DATE,"EndDate" DATE,
  "Filter1" CHAR(4),"Filter2" CHAR(4),"Filter3" CHAR(4),"MasterPayRate" REAL,"PayRate" REAL,"ChargeRate" REAL,"MasterDiscountedCharge" REAL,
  "UnitsPaid" REAL,"UnitsCharged" REAL,"AmountPaid" REAL,"AmountCharged" REAL,"oldUnitsPaid" REAL,"oldHoursPaid" REAL,"oldHoursCharged" REAL,
  "LineComment" CHAR(100),"tempshiftid" CHAR(20),"shifttime" TIMESTAMP,"jobmasterid" CHAR(20) ) 
BEGIN
  IF EXISTS(SELECT * FROM "placement" WHERE "placementid" = "pplacid" AND "directengagement" = 1) THEN
    SELECT "temppayband"."sortorder","temppayband"."TempPayBandID","temppayband"."description","temppayband"."payrollflag",
      '' AS "Grade",
      CAST(NULL AS DATE) AS "StartDate",
      CAST(NULL AS DATE) AS "EndDate",
      '' AS "Filter1",
      '' AS "Filter2",
      '' AS "Filter3",
      0.0 AS "MasterPayRate",
      "tempjobrate"."PayRate",
      "tempjobrate"."ChargeRate",
      0.0 AS "MasterDiscountedCharge",
      CAST(0 AS REAL) AS "UnitsPaid",
      CAST(0 AS REAL) AS "UnitsCharged",
      CAST(0 AS REAL) AS "AmountPaid",
      CAST(0 AS REAL) AS "AmountCharged",
      CAST(0 AS REAL) AS "oldUnitsPaid",
      CAST(0 AS REAL) AS "oldHoursPaid",
      CAST(0 AS REAL) AS "oldHoursCharged",
      CAST('' AS CHAR(100)) AS "LineComment",
      CAST(NULL AS CHAR(20)) AS "tempshiftid",
      CAST(NULL AS TIMESTAMP) AS "shifttime",
      CAST(NULL AS CHAR(20)) AS "jobmasterid"
      FROM "tempjobrate" KEY JOIN "temppayband"
      WHERE "tempjobrate"."placementid" = "pplacid"
      ORDER BY 1 ASC,3 ASC
  ELSE
    SELECT "sortorder","TempPayBandID","description",
      (SELECT "payrollflag" FROM "temppayband" AS "t" WHERE "t"."temppaybandid" = "t1"."temppaybandid") AS "payrollflag",
      "Grade","StartDate","EndDate","Filter1","Filter2","Filter3",
      "MasterPayRate","PayRate","ChargeRate","MasterDiscountedCharge","UnitsPaid",
      "UnitsCharged","AmountPaid","AmountCharged","oldUnitsPaid","oldHoursPaid","oldHoursCharged","LineComment",
      "tempshiftid","shifttime","jobmasterid"
      FROM(SELECT "temppayband"."sortorder","temppayband"."TempPayBandID","temppayband"."description","isnull"("tempjobrate"."Grade",'') AS "Grade",
          "vacancytemprate"("pvacid","temppayband"."temppayBandID","tempjobrate"."grade","tempjobrate"."filter1","tempjobrate"."filter2",
          "tempjobrate"."filter3","tempjobrate"."Startdate","tempjobrate"."Enddate") AS "VacRateID",
          "mastertemprate"("pJTID","temppayband"."temppayBandID","tempjobrate"."grade","tempjobrate"."filter1","tempjobrate"."filter2",
          "tempjobrate"."filter3","tempjobrate"."Startdate","tempjobrate"."Enddate") AS "MasterID",
          "tempjobrate"."Startdate" AS "StartDate",
          "tempjobrate"."Enddate" AS "EndDate",
          "tempjobrate"."TempJobRateID",
          "tempjobrate"."Filter1",
          "tempjobrate"."Filter2",
          "tempjobrate"."Filter3",
          (SELECT FIRST "discount" FROM "tempjobtype" WHERE "tempjobtypeid" = "pJTID") AS "masterdiscount",
          "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",
          "round"("masterchargerate"-("isnull"("masterdiscount",0)*"masterchargerate"/100),2) AS "MasterDiscountedCharge",
          "tempjobrate"."placementid",
          0 AS "WasDefault",
          CAST(0 AS REAL) AS "UnitsPaid",
          CAST(0 AS REAL) AS "UnitsCharged",
          CAST(0 AS REAL) AS "AmountPaid",
          CAST(0 AS REAL) AS "AmountCharged",
          CAST(0 AS REAL) AS "oldUnitsPaid",
          CAST(0 AS REAL) AS "oldHoursPaid",
          CAST(0 AS REAL) AS "oldHoursCharged",
          CAST('' AS CHAR(100)) AS "LineComment",
          CAST(NULL AS CHAR(20)) AS "tempshiftid",
          CAST(NULL AS TIMESTAMP) AS "shifttime",
          CAST(NULL AS CHAR(20)) AS "jobmasterid"
          FROM "tempjobrate" KEY JOIN "temppayband"
          WHERE "tempjobrate"."placementid" = "pplacid" AND "string"("pUnderlyingScheme") = '0'
          AND("startdate" IS NULL OR "startdate" <= "pweekend") AND("enddate" IS NULL OR "enddate" >= "pweekstart")
          AND("tempjobrate"."grade" IS NULL OR "tempjobrate"."grade" LIKE "isnull"("entitygrade"("pvacid","ppersid","pplacid",NULL),'%'))
          AND("tempjobrate"."filter1" IS NULL OR "tempjobrate"."filter1" LIKE "isnull"("entityratefilter"(1,"pweekno","pvacid","ppersid","pplacid",NULL),'%'))
          AND("tempjobrate"."filter2" IS NULL OR "tempjobrate"."filter2" LIKE "isnull"("entityratefilter"(2,"pweekno","pvacid","ppersid","pplacid",NULL),'%'))
          AND("tempjobrate"."filter3" IS NULL OR "tempjobrate"."filter3" LIKE "isnull"("entityratefilter"(3,"pweekno","pvacid","ppersid","pplacid",NULL),'%')) UNION ALL
        SELECT "temppayband"."sortorder","temppayband"."TempPayBandID","temppayband"."description","isnull"("tempjobrate"."Grade",'') AS "Grade",
          "tempjobrate"."TempJobRateID" AS "VacRateID",
          "mastertemprate"("pJTID","temppayband"."temppayBandID","tempjobrate"."grade","tempjobrate"."filter1","tempjobrate"."filter2",
          "tempjobrate"."filter3","tempjobrate"."Startdate","tempjobrate"."Enddate") AS "MasterID",
          "tempjobrate"."Startdate" AS "StartDate",
          "tempjobrate"."Enddate" AS "EndDate",
          CAST(NULL AS CHAR(20)) AS "TempJobRateID",
          "tempjobrate"."Filter1",
          "tempjobrate"."Filter2",
          "tempjobrate"."Filter3",
          (SELECT FIRST "discount" FROM "tempjobtype" WHERE "tempjobtypeid" = "pJTID") AS "masterdiscount",
          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",
          "round"("masterchargerate"-("isnull"("masterdiscount",0)*"masterchargerate"/100),2) AS "MasterDiscountedCharge",
          CAST("pplacid" AS CHAR(20)) AS "placementid",
          1 AS "WasDefault",
          CAST(0 AS REAL) AS "UnitsPaid",
          CAST(0 AS REAL) AS "UnitsCharged",
          CAST(0 AS REAL) AS "AmountPaid",
          CAST(0 AS REAL) AS "AmountCharged",
          CAST(0 AS REAL) AS "oldUnitsPaid",
          CAST(0 AS REAL) AS "oldHoursPaid",
          CAST(0 AS REAL) AS "oldHoursCharged",
          CAST('' AS CHAR(100)) AS "LineComment",
          CAST(NULL AS CHAR(20)) AS "tempshiftid",
          CAST(NULL AS TIMESTAMP) AS "shifttime",
          CAST(NULL AS CHAR(20)) AS "jobmasterid"
          FROM "tempjobrate" KEY JOIN "temppayband"
          WHERE "tempjobrate"."vacancyid" = "pvacid" AND "string"("pUnderlyingScheme") = '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" <= "pweekend") AND("enddate" IS NULL OR "enddate" >= "pweekstart")
          AND("tempjobrate"."grade" IS NULL OR "tempjobrate"."grade" LIKE "isnull"("entitygrade"("pvacid","ppersid","pplacid",NULL),'%'))
          AND("tempjobrate"."filter1" IS NULL OR "tempjobrate"."filter1" LIKE "isnull"("entityratefilter"(1,"pweekno","pvacid","ppersid","pplacid",NULL),'%'))
          AND("tempjobrate"."filter2" IS NULL OR "tempjobrate"."filter2" LIKE "isnull"("entityratefilter"(2,"pweekno","pvacid","ppersid","pplacid",NULL),'%'))
          AND("tempjobrate"."filter3" IS NULL OR "tempjobrate"."filter3" LIKE "isnull"("entityratefilter"(3,"pweekno","pvacid","ppersid","pplacid",NULL),'%')) UNION ALL
        SELECT "temppayband"."sortorder","temppayband"."TempPayBandID","temppayband"."description","isnull"("tempjobratemaster"."Grade",'') AS "Grade",
          CAST(NULL AS CHAR(20)) AS "VacRateID",
          "tempjobratemaster"."tempjobratemasterid" AS "masterid",
          "tempjobratemaster"."Startdate" AS "StartDate",
          "tempjobratemaster"."Enddate" AS "EndDate",
          CAST(NULL AS CHAR(20)) AS "TempJobRateID",
          "tempjobratemaster"."Filter1",
          "tempjobratemaster"."Filter2",
          "tempjobratemaster"."Filter3",
          "tempjobtype"."discount" AS "masterdiscount",
          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",
          "round"("masterchargerate"-("isnull"("masterdiscount",0)*"masterchargerate"/100),2) AS "MasterDiscountedCharge",
          CAST("pplacid" AS CHAR(20)) AS "placementid",
          1 AS "WasDefault",
          CAST(0 AS REAL) AS "UnitsPaid",
          CAST(0 AS REAL) AS "UnitsCharged",
          CAST(0 AS REAL) AS "AmountPaid",
          CAST(0 AS REAL) AS "AmountCharged",
          CAST(0 AS REAL) AS "oldUnitsPaid",
          CAST(0 AS REAL) AS "oldHoursPaid",
          CAST(0 AS REAL) AS "oldHoursCharged",
          CAST('' AS CHAR(100)) AS "LineComment",
          CAST(NULL AS CHAR(20)) AS "tempshiftid",
          CAST(NULL AS TIMESTAMP) AS "shifttime",
          CAST(NULL AS CHAR(20)) AS "jobmasterid"
          FROM "tempjobratemaster" KEY JOIN("tempjobtype","temppayband")
          WHERE "tempjobtype"."tempjobtypeid" = "pJTid"
          AND("string"("pUnderlyingScheme") = '1'
          OR("OverrideTempRate"(
          "pvacid","pplacid","temppayband"."temppaybandid","tempjobratemaster"."Grade","tempjobratemaster"."filter1","tempjobratemaster"."filter2",
          "tempjobratemaster"."filter3","tempjobratemaster"."Startdate","tempjobratemaster"."Enddate") IS NULL
          AND "tempjobtype"."dynamic" = 1))
          AND("startdate" IS NULL OR "startdate" <= "pweekend") AND("enddate" IS NULL OR "enddate" >= "pweekstart")
          AND("tempjobratemaster"."grade" IS NULL OR "tempjobratemaster"."grade" LIKE "isnull"("entitygrade"("pvacid","ppersid","pplacid",NULL),'%'))
          AND("tempjobratemaster"."filter1" IS NULL OR "tempjobratemaster"."filter1" LIKE "isnull"("entityratefilter"(1,"pweekno","pvacid","ppersid","pplacid",NULL),'%'))
          AND("tempjobratemaster"."filter2" IS NULL OR "tempjobratemaster"."filter2" LIKE "isnull"("entityratefilter"(2,"pweekno","pvacid","ppersid","pplacid",NULL),'%'))
          AND("tempjobratemaster"."filter3" IS NULL OR "tempjobratemaster"."filter3" LIKE "isnull"("entityratefilter"(3,"pweekno","pvacid","ppersid","pplacid",NULL),'%'))
          ORDER BY 1 ASC,3 ASC,4 ASC) AS "t1"
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetTempRateList" IS 
{CREATE PROCEDURE GetTempRateList 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(  IN ppersid CHAR(20), IN pvacid CHAR(20), IN pplacid CHAR(20), IN pJtid CHAR(20), IN pweekno INTEGER, IN pweekstart DATE ,IN pweekend DATE,  IN pUnderlyingScheme INTEGER )
RESULT( 
sortorder INTEGER,TempPayBandID CHAR(20),description CHAR(50),payrollflag CHAR(10),Grade  CHAR(4),StartDate DATE,EndDate DATE,
Filter1 CHAR(4),Filter2  CHAR(4),Filter3  CHAR(4),MasterPayRate  REAL ,PayRate REAL,ChargeRate  REAL,MasterDiscountedCharge  REAL,
UnitsPaid  REAL,UnitsCharged REAL,AmountPaid  REAL,AmountCharged  REAL,oldUnitsPaid  REAL,oldHoursPaid  REAL,oldHoursCharged  REAL,
LineComment CHAR(100),tempshiftid CHAR(20),shifttime TIMESTAMP,jobmasterid CHAR(20)) 
BEGIN
  IF EXISTS(SELECT * FROM placement WHERE placementid = pplacid AND directengagement = 1) THEN
SELECT
temppayband.sortorder,temppayband.TempPayBandID,temppayband.description,temppayband.payrollflag,
 '' AS Grade,
CAST(NULL AS DATE) AS StartDate,
CAST(NULL AS DATE) AS EndDate,
 '' AS Filter1,
 '' AS Filter2,
 '' AS Filter3,
0.0 AS MasterPayRate,
tempjobrate.PayRate,
tempjobrate.ChargeRate,
0.0 AS MasterDiscountedCharge,
CAST(0 AS REAL) AS UnitsPaid,
CAST(0 AS REAL) AS UnitsCharged,
CAST(0 AS REAL) AS AmountPaid,
CAST(0 AS REAL) AS AmountCharged,
CAST(0 AS REAL) AS oldUnitsPaid,
CAST(0 AS REAL) AS oldHoursPaid,
CAST(0 AS REAL) AS oldHoursCharged,
CAST('' AS CHAR(100)) AS LineComment,
CAST(NULL AS CHAR(20)) AS tempshiftid,
CAST(NULL AS TIMESTAMP) AS shifttime,
CAST(NULL AS CHAR(20)) AS jobmasterid
FROM tempjobrate KEY JOIN temppayband
WHERE tempjobrate.placementid = pplacid
ORDER BY 1,3
ELSE
SELECT 
sortorder ,TempPayBandID ,description ,
(SELECT payrollflag FROM temppayband t WHERE t.temppaybandid = t1.temppaybandid) AS payrollflag ,
Grade  ,StartDate,EndDate ,Filter1 ,Filter2  ,Filter3  ,
MasterPayRate  ,PayRate ,ChargeRate ,MasterDiscountedCharge  ,UnitsPaid  ,
UnitsCharged,AmountPaid  ,AmountCharged  ,oldUnitsPaid ,oldHoursPaid,oldHoursCharged,LineComment ,
tempshiftid ,shifttime ,jobmasterid
FROM (
SELECT 
temppayband.sortorder,temppayband.TempPayBandID,temppayband.description,isnull(tempjobrate.Grade,'') AS Grade,
vacancytemprate(pvacid,temppayband.temppayBandID,tempjobrate.grade,tempjobrate.filter1,tempjobrate.filter2,
tempjobrate.filter3,tempjobrate.Startdate,tempjobrate.Enddate) AS VacRateID,
mastertemprate(pJTID,temppayband.temppayBandID,tempjobrate.grade,tempjobrate.filter1,tempjobrate.filter2,
tempjobrate.filter3,tempjobrate.Startdate,tempjobrate.Enddate) AS MasterID,
tempjobrate.Startdate AS StartDate,
tempjobrate.Enddate AS EndDate,
tempjobrate.TempJobRateID,
tempjobrate.Filter1,
tempjobrate.Filter2,
tempjobrate.Filter3,
(SELECT FIRST discount FROM tempjobtype WHERE tempjobtypeid=pJTID ) AS masterdiscount,
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,
round(masterchargerate-(isnull(masterdiscount,0)*masterchargerate/100),2) AS MasterDiscountedCharge,
tempjobrate.placementid,
0 AS WasDefault,
CAST(0 AS REAL) AS UnitsPaid,
CAST(0 AS REAL) AS UnitsCharged,
CAST(0 AS REAL) AS AmountPaid,
CAST(0 AS REAL) AS AmountCharged,
CAST(0 AS REAL) AS oldUnitsPaid,
CAST(0 AS REAL) AS oldHoursPaid,
CAST(0 AS REAL) AS oldHoursCharged,
CAST('' AS CHAR(100)) AS LineComment,
CAST(NULL AS CHAR(20)) AS tempshiftid,
CAST(NULL AS TIMESTAMP) AS shifttime,
CAST(NULL AS CHAR(20)) AS jobmasterid
FROM tempjobrate KEY JOIN temppayband
WHERE tempjobrate.placementid = pplacid AND string(pUnderlyingScheme)='0' AND
(startdate IS NULL OR startdate<=pweekend) AND (enddate IS NULL OR enddate>=pweekstart) AND
(tempjobrate.grade IS NULL OR tempjobrate.grade LIKE isnull(entitygrade(pvacid,ppersid,pplacid,NULL),'%')) AND
(tempjobrate.filter1 IS NULL OR tempjobrate.filter1 LIKE isnull(entityratefilter(1,pweekno,pvacid,ppersid,pplacid,NULL),'%')) AND
(tempjobrate.filter2 IS NULL OR tempjobrate.filter2 LIKE isnull(entityratefilter(2,pweekno,pvacid,ppersid,pplacid,NULL),'%')) AND
(tempjobrate.filter3 IS NULL OR tempjobrate.filter3 LIKE isnull(entityratefilter(3,pweekno,pvacid,ppersid,pplacid,NULL),'%'))
UNION ALL
SELECT
temppayband.sortorder,temppayband.TempPayBandID,temppayband.description,isnull(tempjobrate.Grade,'') AS Grade,
tempjobrate.TempJobRateID AS VacRateID,
mastertemprate(pJTID,temppayband.temppayBandID,tempjobrate.grade,tempjobrate.filter1,tempjobrate.filter2,
tempjobrate.filter3,tempjobrate.Startdate,tempjobrate.Enddate) AS MasterID,
tempjobrate.Startdate AS StartDate,
tempjobrate.Enddate AS EndDate,
CAST(NULL AS CHAR(20)) AS TempJobRateID,
tempjobrate.Filter1,
tempjobrate.Filter2,
tempjobrate.Filter3,
(SELECT FIRST discount FROM tempjobtype WHERE tempjobtypeid=pJTID ) AS masterdiscount,
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,
round(masterchargerate-(isnull(masterdiscount,0)*masterchargerate/100),2) AS MasterDiscountedCharge,
CAST(pplacid AS CHAR(20)) AS placementid,
1 AS WasDefault,
CAST(0 AS REAL) AS UnitsPaid,
CAST(0 AS REAL) AS UnitsCharged,
CAST(0 AS REAL) AS AmountPaid,
CAST(0 AS REAL) AS AmountCharged,
CAST(0 AS REAL) AS oldUnitsPaid,
CAST(0 AS REAL) AS oldHoursPaid,
CAST(0 AS REAL) AS oldHoursCharged,
CAST('' AS CHAR(100)) AS LineComment,
CAST(NULL AS CHAR(20)) AS tempshiftid,
CAST(NULL AS TIMESTAMP) AS shifttime,
CAST(NULL AS CHAR(20)) AS jobmasterid
FROM tempjobrate KEY JOIN temppayband
WHERE tempjobrate.vacancyid=pvacid AND string(pUnderlyingScheme)='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<=pweekend) AND (enddate IS NULL OR enddate>=pweekstart) AND
(tempjobrate.grade IS NULL OR tempjobrate.grade LIKE isnull(entitygrade(pvacid,ppersid,pplacid,NULL),'%')) AND
(tempjobrate.filter1 IS NULL OR tempjobrate.filter1 LIKE isnull(entityratefilter(1,pweekno,pvacid,ppersid,pplacid,NULL),'%')) AND
(tempjobrate.filter2 IS NULL OR tempjobrate.filter2 LIKE isnull(entityratefilter(2,pweekno,pvacid,ppersid,pplacid,NULL),'%')) AND
(tempjobrate.filter3 IS NULL OR tempjobrate.filter3 LIKE isnull(entityratefilter(3,pweekno,pvacid,ppersid,pplacid,NULL),'%'))
UNION ALL
SELECT
temppayband.sortorder,temppayband.TempPayBandID,temppayband.description,isnull(tempjobratemaster.Grade,'') AS Grade,
CAST(NULL AS CHAR(20)) AS VacRateID,
tempjobratemaster.tempjobratemasterid AS masterid,
tempjobratemaster.Startdate AS StartDate,
tempjobratemaster.Enddate AS EndDate,
CAST(NULL AS CHAR(20)) AS TempJobRateID,
tempjobratemaster.Filter1,
tempjobratemaster.Filter2,
tempjobratemaster.Filter3,
tempjobtype.discount AS masterdiscount,
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,
round(masterchargerate-(isnull(masterdiscount,0)*masterchargerate/100),2) AS MasterDiscountedCharge,
CAST(pplacid AS CHAR(20)) AS placementid,
1 AS WasDefault,
CAST(0 AS REAL) AS UnitsPaid,
CAST(0 AS REAL) AS UnitsCharged,
CAST(0 AS REAL) AS AmountPaid,
CAST(0 AS REAL) AS AmountCharged,
CAST(0 AS REAL) AS oldUnitsPaid,
CAST(0 AS REAL) AS oldHoursPaid,
CAST(0 AS REAL) AS oldHoursCharged,
CAST('' AS CHAR(100)) AS LineComment,
CAST(NULL AS CHAR(20)) AS tempshiftid,
CAST(NULL AS TIMESTAMP) AS shifttime,
CAST(NULL AS CHAR(20)) AS jobmasterid
FROM tempjobratemaster KEY JOIN (tempjobtype,temppayband)
WHERE tempjobtype.tempjobtypeid = pJTid AND
( string(pUnderlyingScheme)='1' OR
  (OverrideTempRate 
 ( pvacid,pplacid,temppayband.temppaybandid,tempjobratemaster.Grade,tempjobratemaster.filter1,tempjobratemaster.filter2,
 tempjobratemaster.filter3,tempjobratemaster.Startdate,tempjobratemaster.Enddate) IS NULL AND
  tempjobtype.dynamic=1)) AND
(startdate IS NULL OR startdate<=pweekend) AND (enddate IS NULL OR enddate>=pweekstart) AND
(tempjobratemaster.grade IS NULL OR tempjobratemaster.grade LIKE isnull(entitygrade(pvacid,ppersid,pplacid,NULL),'%')) AND
(tempjobratemaster.filter1 IS NULL OR tempjobratemaster.filter1 LIKE isnull(entityratefilter(1,pweekno,pvacid,ppersid,pplacid,NULL),'%')) AND
(tempjobratemaster.filter2 IS NULL OR tempjobratemaster.filter2 LIKE isnull(entityratefilter(2,pweekno,pvacid,ppersid,pplacid,NULL),'%')) AND
(tempjobratemaster.filter3 IS NULL OR tempjobratemaster.filter3 LIKE isnull(entityratefilter(3,pweekno,pvacid,ppersid,pplacid,NULL),'%'))
ORDER BY 1,3,4) AS t1
  END IF;
END
}