pears.GetTempAWRRate [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.GetTempAWRRate ======
<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"."GetTempAWRRate"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "PayOrCharge" char(1),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;
  set "rv" = null;
  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",
        "isnull"("tempjobrate"."filter1",'X') 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"
        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" = 'Y' or "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",
        "isnull"("tempjobrate"."filter1",'X') 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"
        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" = 'Y' or "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",
        "isnull"("tempjobratemaster"."filter1",'X') 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"
        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" = 'Y' or "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,"soAWR" desc,"sortorder" asc,"description" asc,"startdate" asc,"enddate" asc,"grade" 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",
        "isnull"("tempjobrate"."filter1",'X') 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"
        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" = 'Y' or "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",
        "isnull"("tempjobratemaster"."filter1",'X') 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"
        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" = 'Y' or "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,"soAWR" desc,"sortorder" asc,"description" asc,"startdate" asc,"enddate" asc,"grade" 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
</code>