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