Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
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 }