pears.GetExtraRate [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.GetExtraRate ======
<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"."GetExtraRate"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "pExtraRateNumber" smallint,in "pPlacOrVac" char(1),in "pID" char(20),in "pBandID" char(20),in "pEffectiveDate" date,in "pGrade" char(4),in "pFilter1" char(4),in "pFilter2" char(4),in "pFilter3" char(4) ) 
returns double
begin
  -- This function is for providing valid extra rates for reporting purposes handling over-rides, filters, date ranges etc.
  declare "rv" double;
  declare "sqla" long varchar;
  declare "sqlb" long varchar;
  declare "pDynamic" smallint;
  set "rv" = null;
  set "pGrade" = "isnull"("pGrade",'');
  set "pFilter1" = "isnull"("pFilter1",'');
  set "pFilter2" = "isnull"("pFilter2",'');
  set "pFilter3" = "isnull"("pFilter3",'');
  set "sqla" = "string"('select first extrarate',"pExtraRateNumber",' into rv from tempjobrate');
  set "sqlb" = ' where (startdate is null or startdate <= pEffectiveDate) and ';
  set "sqlb" = "sqlb"+'(enddate is null or enddate >= pEffectiveDate) and isnull(grade,'''')=pGrade and ';
  set "sqlb" = "sqlb"+'isnull(filter1,'''')=pFilter1 and isnull(filter2,'''')=pFilter2 and ';
  set "sqlb" = "sqlb"+'isnull(filter3,'''')=pFilter3 and temppaybandid=pBandID and ';
  if "pPlacOrVac" = 'P' then
    execute immediate "sqla"+"sqlb"+'placementid=pID';
    if "rv" is not null then
      return "rv"
    end if;
    select "vacancyid" into "pID" from "placement" where "placementid" = "pID"
  end if;
  execute immediate "sqla"+"sqlb"+'vacancyid=pID';
  if "rv" is not null then
    return "rv"
  end if;
  select "t"."dynamic","t"."tempjobtypeid" into "pDynamic","pID" from "vacancy" as "v" key join "tempjobtype" as "t" where "v"."vacancyid" = "pID";
  if "pDynamic" = 1 then
    execute immediate "sqla"+'master'+"sqlb"+'tempjobtypeid=pID'
  end if;
  return "rv"
end
</code>