pears.GetExtraRate

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