====== 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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetExtraRate" IS 
{create function 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>