====== pears.CreateETimesheetRates ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."CreateETimesheetRates"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "ptsid" char(20),in "ppersid" char(20),in "pvacid" char(20),in "pplacid" char(20),in "pweekstart" date,in "pweekend" date,
  in "pjobtypeid" char(20) default null,in "punderlyingscheme" smallint default 0 ) 
begin
  declare "pbandid" char(20);
  declare "ppayrate" double;
  declare "pchgrate" double;
  declare "pgrade" char(4);
  declare "pfilter1" char(4);
  declare "pfilter2" char(4);
  declare "pfilter3" char(4);
  declare "plineno" smallint;
  declare "pweekno" integer;
  set "pweekno" = "weekcontaining"("pweekend");
  set "plineno" = 0;
  if "trim"("isnull"("pjobtypeid",'')) = '' then
    select "tempjobtypeid" into "pjobtypeid" from "vacancy" where "vacancyid" = "pvacid"
  end if;
  if "punderlyingscheme" is null then
    set "punderlyingscheme" = 0
  end if;
  -- SQL taken from TimeSheetEntry.qRates
  for "PRATEFOR" as "PRATECUR" no scroll cursor for
    select "temppayband"."sortorder","temppayband"."TempPayBandID","temppayband"."description","isnull"("tempjobrate"."Grade",'') as "Grade",
      "tempjobrate"."filter1","tempjobrate"."filter2","tempjobrate"."filter3",
      "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",
      "tempjobrate"."Startdate" as "StartDate",
      "tempjobrate"."Enddate" as "EndDate",
      "tempjobrate"."TempJobRateID",
      (select first "discount" from "tempjobtype" where "tempjobtypeid" = "pjobtypeid") 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","temppayband"."payrollflag"
      from "tempjobrate" key join "temppayband"
      where "tempjobrate"."placementid" = "pplacid" and "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"."filter1","tempjobrate"."filter2","tempjobrate"."filter3",
      "tempjobrate"."TempJobRateID" as "VacRateID",
      "mastertemprate"("pjobtypeid","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",
      (select first "discount" from "tempjobtype" where "tempjobtypeid" = "pjobtypeid") 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","temppayband"."payrollflag"
      from "tempjobrate" key join "temppayband"
      where "tempjobrate"."vacancyid" = "pvacid" and "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",
      "tempjobratemaster"."filter1","tempjobratemaster"."filter2","tempjobratemaster"."filter3",
      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",
      "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","temppayband"."payrollflag"
      from "tempjobratemaster" key join("tempjobtype","temppayband")
      where "tempjobtype"."tempjobtypeid" = "pjobtypeid" and("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 for read only do
    set "plineno" = "plineno"+1;
    set "pbandid" = "temppaybandid";
    set "ppayrate" = "payrate";
    set "pchgrate" = "chargerate";
    set "pgrade" = "grade";
    set "pfilter1" = "filter1";
    set "pfilter2" = "filter2";
    set "pfilter3" = "filter3";
    insert into "tempprovtimesheetline"( "tempprovtimesheetlineid","tempprovtimesheetid","temppaybandid","linenumber","payrate","chargerate","grade","filter1","filter2","filter3" ) values
      ( "uniquekey"("plineno"),"ptsid","pbandid","plineno","ppayrate","pchgrate","pgrade","pfilter1","pfilter2","pfilter3" ) 
  end for
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."CreateETimesheetRates" IS 
{create procedure CreateETimesheetRates 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

( 
  in ptsid char(20),in ppersid char(20),in pvacid char(20),in pplacid char(20),in pweekstart date,in pweekend date,
  in pjobtypeid char(20) default null,in punderlyingscheme smallint default 0 ) 
begin
  declare pbandid char(20);
  declare ppayrate double;
  declare pchgrate double;
  declare pgrade char(4);
  declare pfilter1 char(4);
  declare pfilter2 char(4);
  declare pfilter3 char(4);
  declare plineno smallint;
  declare pweekno integer;
  set pweekno = weekcontaining(pweekend);
  set plineno = 0;
  if trim(isnull(pjobtypeid,'')) = '' then
    select tempjobtypeid into pjobtypeid from vacancy where vacancyid = pvacid
  end if;
  if punderlyingscheme is null then
    set punderlyingscheme = 0
  end if;
  -- SQL taken from TimeSheetEntry.qRates
  for PRATEFOR as PRATECUR no scroll cursor for
    select temppayband.sortorder,temppayband.TempPayBandID,temppayband.description,isnull(tempjobrate.Grade,'') as Grade,
      tempjobrate.filter1,tempjobrate.filter2,tempjobrate.filter3,
      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,
      tempjobrate.Startdate as StartDate,
      tempjobrate.Enddate as EndDate,
      tempjobrate.TempJobRateID,
      (select first discount from tempjobtype where tempjobtypeid = pjobtypeid) 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,temppayband.payrollflag
      from tempjobrate key join temppayband
      where tempjobrate.placementid = pplacid and 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.filter1,tempjobrate.filter2,tempjobrate.filter3,
      tempjobrate.TempJobRateID as VacRateID,
      mastertemprate(pjobtypeid,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,
      (select first discount from tempjobtype where tempjobtypeid = pjobtypeid) 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,temppayband.payrollflag
      from tempjobrate key join temppayband
      where tempjobrate.vacancyid = pvacid and 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,
      tempjobratemaster.filter1,tempjobratemaster.filter2,tempjobratemaster.filter3,
      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,
      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,temppayband.payrollflag
      from tempjobratemaster key join(tempjobtype,temppayband)
      where tempjobtype.tempjobtypeid = pjobtypeid and(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 for read only do
    set plineno = plineno+1;
    set pbandid = temppaybandid;
    set ppayrate = payrate;
    set pchgrate = chargerate;
    set pgrade = grade;
    set pfilter1 = filter1;
    set pfilter2 = filter2;
    set pfilter3 = filter3;
    insert into tempprovtimesheetline( tempprovtimesheetlineid,tempprovtimesheetid,temppaybandid,linenumber,payrate,chargerate,grade,filter1,filter2,filter3 ) 
      values( uniquekey(plineno),ptsid,pbandid,plineno,ppayrate,pchgrate,pgrade,pfilter1,pfilter2,pfilter3 ) 
  end for
end
}
</code>