====== pears.GetTempRateExtra ======
<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 function "pears"."GetTempRateExtra"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "FName" char(20),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;
  if("fname" = 'P') or("fname" = 'V') then
    set "rv" = "GetTempRateCustom"("FName","PlacOrVac","ID");
    if "rv" is not null then return "rv" end if
  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",'Z') as "soltd",
        "isnull"("tempjobrate"."filter1",'Z') 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"("tempjobrate"."extrarate1",(select first "extrarate1" from "tempjobratemaster" where "tempjobratemasterid" = "masterid")) as "extrarate1",
        "isnull"("tempjobrate"."extrarate2",(select first "extrarate2" from "tempjobratemaster" where "tempjobratemasterid" = "masterid")) as "extrarate2",
        "isnull"("tempjobrate"."extrarate3",(select first "extrarate3" from "tempjobratemaster" where "tempjobratemasterid" = "masterid")) as "extrarate3",
        "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" 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",
        "tempjobrate"."filter3",
        "isnull"("tempjobrate"."filter2",'Z') as "soltd",
        "isnull"("tempjobrate"."filter1",'Z') 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"("tempjobrate"."extrarate1",(select first "extrarate1" from "tempjobratemaster" where "tempjobratemasterid" = "masterid")) as "extrarate1",
        "isnull"("tempjobrate"."extrarate2",(select first "extrarate2" from "tempjobratemaster" where "tempjobratemasterid" = "masterid")) as "extrarate2",
        "isnull"("tempjobrate"."extrarate3",(select first "extrarate3" from "tempjobratemaster" where "tempjobratemasterid" = "masterid")) as "extrarate3",
        "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" 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",
        "tempjobratemaster"."filter3",
        "isnull"("tempjobratemaster"."filter2",'Z') as "soltd",
        "isnull"("tempjobratemaster"."filter1",'Z') 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",
        "tempjobratemaster"."extrarate1",
        "tempjobratemaster"."extrarate2",
        "tempjobratemaster"."extrarate3",
        "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" 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,"startdate" asc,"grade" asc,"filter3" asc,"soLtd" asc,"soAWR" asc,"sortorder" asc for read only do
      case "FName"
      when 'P' then set "rv" = "payrate"
      when 'V' then set "rv" = "discountedcharge"
      when 'ExtraRate1' then set "rv" = "ExtraRate1"
      when 'ExtraRate2' then set "rv" = "ExtraRate2"
      when 'ExtraRate3' then set "rv" = "ExtraRate3"
      end case;
      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",'Z') as "soltd",
        "isnull"("tempjobrate"."filter1",'Z') 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"("tempjobrate"."extrarate1",(select first "extrarate1" from "tempjobratemaster" where "tempjobratemasterid" = "masterid")) as "extrarate1",
        "isnull"("tempjobrate"."extrarate2",(select first "extrarate2" from "tempjobratemaster" where "tempjobratemasterid" = "masterid")) as "extrarate2",
        "isnull"("tempjobrate"."extrarate3",(select first "extrarate3" from "tempjobratemaster" where "tempjobratemasterid" = "masterid")) as "extrarate3",
        "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" 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",
        "tempjobratemaster"."filter3",
        "isnull"("tempjobratemaster"."filter2",'Z') as "soltd",
        "isnull"("tempjobratemaster"."filter1",'Z') 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",
        "tempjobratemaster"."extrarate1",
        "tempjobratemaster"."extrarate2",
        "tempjobratemaster"."extrarate3",
        "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" 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,"startdate" asc,"grade" asc,"filter3" asc,"soLtd" asc,"soAWR" asc,"sortorder" asc for read only do
      case "FName"
      when 'P' then set "rv" = "payrate"
      when 'V' then set "rv" = "discountedcharge"
      when 'ExtraRate1' then set "rv" = "ExtraRate1"
      when 'ExtraRate2' then set "rv" = "ExtraRate2"
      when 'ExtraRate3' then set "rv" = "ExtraRate3"
      end case;
      return "rv"
    end for end if;
  return "rv"
end
</code>