pears.GetExtraRate

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
}