====== pears.OverrideTempRate ======
<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"."OverrideTempRate"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "VacID" char(20),in "PlacID" char(20),in "BandID" char(20),in "GradeID" char(4),in "pFilter1" char(4),in "pFilter2" char(4),in "pFilter3" char(4),in "dFrom" date,in "dTo" date ) 
returns char(20)
// Purpose is to hide rate rows from scheme or vacancy which are over-ridden at the relevant vacancy or placement level. If non-null value returned the rate will be hidden.
// New method introduced MHS 25/3/13 is less aggressive - it only hides rates which are totally occluded, date-wise, by the over-riding rate.
// Previous method commented out.
begin
  declare "rv" char(20);
  set "rv" = null;
  set "gradeid" = "trim"("isnull"("gradeid",''));
  set "pFilter1" = "trim"("isnull"("pFilter1",''));
  set "pFilter2" = "trim"("isnull"("pFilter2",''));
  set "pFilter3" = "trim"("isnull"("pFilter3",''));
  if "trim"("vacid") = '' then
    set "vacid" = null
  end if;
  if "trim"("placid") = '' then
    set "placid" = null
  end if;
  if "placid" is not null then
    select first "r"."tempjobrateid" into "rv" from "tempjobrate" as "r" where "r"."placementid" = "placid" and "r"."temppaybandid" = "bandid"
      and "trim"("isnull"("r"."grade",'')) = "gradeid"
      and "trim"("isnull"("r"."filter1",'')) = "pFilter1"
      and "trim"("isnull"("r"."filter2",'')) = "pFilter2"
      and "trim"("isnull"("r"."filter3",'')) = "pFilter3"
      --isnull(dfrom,'1900-01-01') <= isnull(r.enddate,current date) and isnull(dto,'2200-01-01') >= isnull(r.startdate,current date)
      and("r"."enddate" is null or "r"."enddate" >= "dto") and("r"."startdate" is null or "r"."startdate" <= "dfrom")
  end if;
  if "rv" is null and "vacid" is not null then
    select first "r"."tempjobrateid" into "rv" from "tempjobrate" as "r" where "r"."vacancyid" = "vacid" and "r"."temppaybandid" = "bandid"
      and "trim"("isnull"("r"."grade",'')) = "gradeid"
      and "trim"("isnull"("r"."filter1",'')) = "pFilter1"
      and "trim"("isnull"("r"."filter2",'')) = "pFilter2"
      and "trim"("isnull"("r"."filter3",'')) = "pFilter3"
      --isnull(dfrom,'1900-01-01') <= isnull(r.enddate,current date) and isnull(dto,'2200-01-01') >= isnull(r.startdate,current date)
      and("r"."enddate" is null or "r"."enddate" >= "dto") and("r"."startdate" is null or "r"."startdate" <= "dfrom")
  end if;
  return "rv"
end
</code>