pears.GetTempRateByDesc [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.GetTempRateByDesc ======
<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"."GetTempRateByDesc"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "PayOrCharge" char(1),in "PlacOrVac" char(1),in "ID" char(20),in "descrp" char(50),in @awr tinyint,in @grade char(4),in @ltd char(4) ) 
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;
  declare "pawrval" char(1);
  declare "pawrfilter" char(1);
  declare "pawr" tinyint;
  --  declare @sql long varchar;
  set "rv" = null;
  set "pawr" = "isnull"(@awr,0);
  if "pawr" = 0 then
    set "pawrval" = 'Z';
    set "pawrfilter" = ''
  else
    set "pawrval" = 'X';
    set "pawrfilter" = 'Y'
  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","pawrval") as "soltd",
        "isnull"("tempjobrate"."filter1","pawrval") 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",
        if "pawr" = 0 then
          ("RANK"() over(order by "IsExpenses" asc,"startdate" asc,"grade" asc,"filter3" asc,"soLtd" asc,"soAWR" asc,"temppayband"."sortorder" asc))
        else("RANK"() over(order by "IsExpenses" asc,"soAWR" desc,"temppayband"."sortorder" asc,"temppayband"."description" asc,"startdate" asc,"enddate" asc,"grade" asc))
        endif as "ranka"
        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("isnull"("tempjobrate"."grade",'') = @grade or("tempjobrate"."grade" like "isnull"("entitygrade"("pvacid","ppersid","pplacid",null),'%') and @grade = ''))
        and(("isnull"("tempjobrate"."filter1","pawrfilter") = "pawrfilter") or "tempjobrate"."filter1" like "isnull"("entityratefilter"(1,null,"pvacid","ppersid","pplacid",null),'%'))
        and("isnull"("tempjobrate"."filter2",'') = @ltd or("tempjobrate"."filter2" like "isnull"("entityratefilter"(2,null,"pvacid","ppersid","pplacid",null),'%') and @ltd = ''))
        and("tempjobrate"."filter3" is null or "tempjobrate"."filter3" like "isnull"("entityratefilter"(3,null,"pvacid","ppersid","pplacid",null),'%'))
        and "temppayband"."description" like "string"("descrp",'%') 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","pawrval") as "soltd",
        "isnull"("tempjobrate"."filter1","pawrval") 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",
        if "pawr" = 0 then
          ("RANK"() over(order by "IsExpenses" asc,"startdate" asc,"grade" asc,"filter3" asc,"soLtd" asc,"soAWR" asc,"temppayband"."sortorder" asc))
        else("RANK"() over(order by "isExpenses" asc,"soAWR" desc,"temppayband"."sortorder" asc,"temppayband"."description" asc,"startdate" asc,"enddate" asc,"grade" asc))
        endif as "ranka"
        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("isnull"("tempjobrate"."grade",'') = @grade or("tempjobrate"."grade" like "isnull"("entitygrade"("pvacid","ppersid","pplacid",null),'%') and @grade = ''))
        and(("isnull"("tempjobrate"."filter1","pawrfilter") = "pawrfilter") or "tempjobrate"."filter1" like "isnull"("entityratefilter"(1,null,"pvacid","ppersid","pplacid",null),'%'))
        and("isnull"("tempjobrate"."filter2",'') = @ltd or("tempjobrate"."filter2" like "isnull"("entityratefilter"(2,null,"pvacid","ppersid","pplacid",null),'%') and @ltd = ''))
        and("tempjobrate"."filter3" is null or "tempjobrate"."filter3" like "isnull"("entityratefilter"(3,null,"pvacid","ppersid","pplacid",null),'%'))
        and "temppayband"."description" like "string"("descrp",'%') 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","pawrval") as "soltd",
        "isnull"("tempjobratemaster"."filter1","pawrval") 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",
        if "pawr" = 0 then
          ("RANK"() over(order by "IsExpenses" asc,"startdate" asc,"grade" asc,"filter3" asc,"soLtd" asc,"soAWR" asc,"temppayband"."sortorder" asc))
        else("RANK"() over(order by "IsExpenses" asc,"soAWR" desc,"temppayband"."sortorder" asc,"temppayband"."description" asc,"startdate" asc,"enddate" asc,"grade" asc))
        endif as "ranka"
        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("isnull"("tempjobratemaster"."grade",'') = @grade or("tempjobratemaster"."grade" like "isnull"("entitygrade"("pvacid","ppersid","pplacid",null),'%') and @grade = ''))
        and(("isnull"("tempjobratemaster"."filter1","pawrfilter") = "pawrfilter") or "tempjobratemaster"."filter1" like "isnull"("entityratefilter"(1,null,"pvacid","ppersid","pplacid",null),'%'))
        and("isnull"("tempjobratemaster"."filter2",'') = @ltd or("tempjobratemaster"."filter2" like "isnull"("entityratefilter"(2,null,"pvacid","ppersid","pplacid",null),'%') and @ltd = ''))
        and("tempjobratemaster"."filter3" is null or "tempjobratemaster"."filter3" like "isnull"("entityratefilter"(3,null,"pvacid","ppersid","pplacid",null),'%'))
        and "tempjobtype"."dynamic" = 1 and "temppayband"."description" like "string"("descrp",'%')
        order by "ranka" 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",
        "tempjobrate"."filter3",
        "isnull"("tempjobrate"."filter2","pawrval") as "soltd",
        "isnull"("tempjobrate"."filter1","pawrval") 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",
        if "pawr" = 0 then
          ("RANK"() over(order by "isExpenses" asc,"startdate" asc,"grade" asc,"filter3" asc,"soLtd" asc,"soAWR" asc,"temppayband"."sortorder" asc))
        else("RANK"() over(order by "isExpenses" asc,"soAWR" desc,"temppayband"."sortorder" asc,"temppayband"."description" asc,"startdate" asc,"enddate" asc,"grade" asc))
        endif as "ranka"
        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("isnull"("tempjobrate"."grade",'') = @grade or("tempjobrate"."grade" like "isnull"("entitygrade"("pvacid",null,null,null),'%') and @grade = ''))
        and(("isnull"("tempjobrate"."filter1","pawrfilter") = "pawrfilter") or "tempjobrate"."filter1" like "isnull"("entityratefilter"(1,null,"pvacid",null,null,null),'%'))
        and("isnull"("tempjobrate"."filter2",'') = @ltd 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),'%'))
        and "temppayband"."description" like "string"("descrp",'%') 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","pawrval") as "soltd",
        "isnull"("tempjobratemaster"."filter1","pawrval") 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",
        if "pawr" = 0 then
          ("RANK"() over(order by "startdate" asc,"grade" asc,"filter3" asc,"soLtd" asc,"soAWR" asc,"temppayband"."sortorder" asc))
        else("RANK"() over(order by "soAWR" desc,"temppayband"."sortorder" asc,"temppayband"."description" asc,"startdate" asc,"enddate" asc,"grade" asc))
        endif as "ranka"
        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("isnull"("tempjobratemaster"."grade",'') = @grade or("tempjobratemaster"."grade" like "isnull"("entitygrade"("pvacid",null,null,null),'%') and @grade = ''))
        and(("isnull"("tempjobratemaster"."filter1","pawrfilter") = "pawrfilter") or "tempjobratemaster"."filter1" like "isnull"("entityratefilter"(1,null,"pvacid",null,null,null),'%'))
        and("isnull"("tempjobratemaster"."filter2",'') = @ltd or("tempjobratemaster"."filter2" like "isnull"("entityratefilter"(2,null,"pvacid",null,null,null),'%') and @ltd = ''))
        and("tempjobratemaster"."filter3" is null or "tempjobratemaster"."filter3" like "isnull"("entityratefilter"(3,null,"pvacid",null,null,null),'%'))
        and "tempjobtype"."dynamic" = 1 and "temppayband"."description" like "string"("descrp",'%')
        order by "ranka" 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>