pears.GetTempRateExtra

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetTempRateExtra" IS 
{CREATE FUNCTION GetTempRateExtra 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( 
  IN FName CHAR(20),IN PlacOrVac CHAR(1),IN ID CHAR(20) ) 
RETURNS DOUBLE
BEGIN
  DECLARE rv DOUBLE; 
  DECLARE pvacid CHAR(20); 
  DECLARE ppersid CHAR(20); 
  DECLARE pplacid CHAR(20); 
  DECLARE pjobtypeid CHAR(20); 
  DECLARE pdirectengagement SMALLINT; 
  IF (fname = 'P') OR (fname='V') THEN
      SET rv = GetTempRateCustom("FName","PlacOrVac" , "ID" ); 
       IF "rv" IS NOT NULL THEN RETURN "rv" END IF; 
   END IF; 
  IF placorvac = 'P' THEN
    SET pplacid = id; 
    SELECT pl.vacancyid,em.personid,v.tempjobtypeid,isnull(pl.directengagement,0) INTO pvacid,ppersid,pjobtypeid,pdirectengagement FROM placement AS pl KEY JOIN(employment AS em,vacancy AS v) WHERE pl.placementid = pplacid; 
    FOR PRATEFOR AS PRATECUR no scroll cursor FOR
      SELECT vacancytemprate(pvacid,temppayband.temppayBandID,tempjobrate.grade,tempjobrate.filter1,tempjobrate.filter2,tempjobrate.filter3,tempjobrate.Startdate,tempjobrate.Enddate) AS VacRateID,
        mastertemprate(pJobTypeID,temppayband.temppayBandID,tempjobrate.grade,tempjobrate.filter1,tempjobrate.filter2,tempjobrate.filter3,tempjobrate.Startdate,tempjobrate.Enddate) AS MasterID,
        temppayband.sortorder,
        temppayband.description,
        tempjobrate.Startdate AS StartDate,
        tempjobrate.Enddate AS EndDate,
        tempjobrate.Grade,
        tempjobrate.filter3,
        isnull(tempjobrate.filter2,'Z') AS soltd,
        isnull(tempjobrate.filter1,'Z') AS soawr,
        tempjobrate.TempJobRateID,
        temppayband.TempPayBandID,
        tempjobrate.PayRate AS SavedPayRate,
        (SELECT FIRST payrate FROM tempjobratemaster WHERE tempjobratemasterid = masterid) AS MasterPayRate,
        isnull((SELECT FIRST payrate FROM tempjobrate WHERE tempjobrateid = vacrateid),MasterPayRate) AS OrigPayRate,
        ModifiedTempRate('P',pvacid,ppersid,pplacid,temppayband.temppaybandid,OrigPayRate) AS ModifiedPayRate,
        isnull(SavedPayRate,ModifiedPayRate) AS PayRate,
        tempjobrate.ChargeRate AS SavedChargeRate,
        (SELECT FIRST chargerate FROM tempjobratemaster WHERE tempjobratemasterid = masterid) AS MasterChargeRate,
        isnull((SELECT FIRST chargerate FROM tempjobrate WHERE tempjobrateid = vacrateid),MasterChargeRate) AS OrigChargeRate,
        round(origchargerate-(isnull((SELECT FIRST discount FROM vacancy WHERE vacancyid = pvacid),0)*origchargerate/100),2) AS DiscountedCharge,
        ModifiedTempRate('C',pvacid,ppersid,pplacid,temppayband.temppaybandid,DiscountedCharge) AS ModifiedChargeRate,
        isnull(SavedChargeRate,ModifiedChargeRate) AS ChargeRate,
        isnull(tempjobrate.extrarate1,(SELECT FIRST extrarate1 FROM tempjobratemaster WHERE tempjobratemasterid=masterid)) AS extrarate1,
        isnull(tempjobrate.extrarate2,(SELECT FIRST extrarate2 FROM tempjobratemaster WHERE tempjobratemasterid=masterid)) AS extrarate2,
        isnull(tempjobrate.extrarate3,(SELECT FIRST extrarate3 FROM tempjobratemaster WHERE tempjobratemasterid=masterid)) AS extrarate3,
        isnull(temppayband.IsExpenses,0) AS IsExpenses
        FROM tempjobrate KEY JOIN temppayband
        WHERE tempjobrate.placementid = pplacid
        AND(startdate IS NULL OR startdate <= CURRENT DATE) AND(enddate IS NULL OR enddate >= CURRENT DATE)
        AND(tempjobrate.grade IS NULL OR tempjobrate.grade LIKE isnull(entitygrade(pvacid,ppersid,pplacid,NULL),'%'))
        AND(tempjobrate.filter1 IS NULL OR tempjobrate.filter1 LIKE isnull(entityratefilter(1,NULL,pvacid,ppersid,pplacid,NULL),'%'))
        AND(tempjobrate.filter2 IS NULL OR tempjobrate.filter2 LIKE isnull(entityratefilter(2,NULL,pvacid,ppersid,pplacid,NULL),'%'))
        AND(tempjobrate.filter3 IS NULL OR tempjobrate.filter3 LIKE isnull(entityratefilter(3,NULL,pvacid,ppersid,pplacid,NULL),'%')) UNION ALL
      SELECT tempjobrate.TempJobRateID AS VacRateID,
        mastertemprate(pJobTypeID,temppayband.temppayBandID,tempjobrate.grade,tempjobrate.filter1,tempjobrate.filter2,tempjobrate.filter3,tempjobrate.Startdate,tempjobrate.Enddate) AS MasterID,
        temppayband.sortorder,
        temppayband.description,
        tempjobrate.Startdate AS StartDate,
        tempjobrate.Enddate AS EndDate,
        tempjobrate.Grade,
        tempjobrate.filter3,
        isnull(tempjobrate.filter2,'Z') AS soltd,
        isnull(tempjobrate.filter1,'Z') AS soawr,
        CAST(NULL AS CHAR(20)) AS TempJobRateID,
        temppayband.TempPayBandID,
        CAST(NULL AS DOUBLE) AS SavedPayRate,
        (SELECT FIRST payrate FROM tempjobratemaster WHERE tempjobratemasterid = masterid) AS MasterPayRate,
        isnull(tempjobrate.payrate,MasterPayRate) AS OrigPayRate,
        ModifiedTempRate('P',pvacid,ppersid,pplacid,temppayband.temppaybandid,OrigPayRate) AS ModifiedPayRate,
        ModifiedPayRate AS PayRate,
        CAST(NULL AS DOUBLE) AS SavedChargeRate,
        (SELECT FIRST chargerate FROM tempjobratemaster WHERE tempjobratemasterid = masterid) AS MasterChargeRate,
        isnull(tempjobrate.chargerate,MasterChargeRate) AS OrigChargeRate,
        round(origchargerate-(isnull((SELECT FIRST discount FROM vacancy WHERE vacancyid = pvacid),0)*origchargerate/100),2) AS DiscountedCharge,
        ModifiedTempRate('C',pvacid,ppersid,pplacid,temppayband.temppaybandid,DiscountedCharge) AS ModifiedChargeRate,
        ModifiedChargeRate AS ChargeRate,
        isnull(tempjobrate.extrarate1,(SELECT FIRST extrarate1 FROM tempjobratemaster WHERE tempjobratemasterid=masterid)) AS extrarate1,
        isnull(tempjobrate.extrarate2,(SELECT FIRST extrarate2 FROM tempjobratemaster WHERE tempjobratemasterid=masterid)) AS extrarate2,
        isnull(tempjobrate.extrarate3,(SELECT FIRST extrarate3 FROM tempjobratemaster WHERE tempjobratemasterid=masterid)) AS extrarate3,
        isnull(temppayband.IsExpenses,0) AS IsExpenses
        FROM tempjobrate KEY JOIN temppayband
        WHERE tempjobrate.vacancyid = pvacid AND pdirectengagement=0
        AND OverrideTempRate(NULL,pplacid,temppayband.temppaybandid,tempjobrate.Grade,tempjobrate.filter1,tempjobrate.filter2,tempjobrate.filter3,tempjobrate.Startdate,tempjobrate.Enddate) IS NULL
        AND(startdate IS NULL OR startdate <= CURRENT DATE) AND(enddate IS NULL OR enddate >= CURRENT DATE)
        AND(tempjobrate.grade IS NULL OR tempjobrate.grade LIKE isnull(entitygrade(pvacid,ppersid,pplacid,NULL),'%'))
        AND(tempjobrate.filter1 IS NULL OR tempjobrate.filter1 LIKE isnull(entityratefilter(1,NULL,pvacid,ppersid,pplacid,NULL),'%'))
        AND(tempjobrate.filter2 IS NULL OR tempjobrate.filter2 LIKE isnull(entityratefilter(2,NULL,pvacid,ppersid,pplacid,NULL),'%'))
        AND(tempjobrate.filter3 IS NULL OR tempjobrate.filter3 LIKE isnull(entityratefilter(3,NULL,pvacid,ppersid,pplacid,NULL),'%')) UNION ALL
      SELECT CAST(NULL AS CHAR(20)) AS VacRateID,
        tempjobratemaster.tempjobratemasterid AS masterid,
        temppayband.sortorder,
        temppayband.description,
        tempjobratemaster.Startdate AS StartDate,
        tempjobratemaster.Enddate AS EndDate,
        tempjobratemaster.Grade,
        tempjobratemaster.filter3,
        isnull(tempjobratemaster.filter2,'Z') AS soltd,
        isnull(tempjobratemaster.filter1,'Z') AS soawr,
        CAST(NULL AS CHAR(20)) AS TempJobRateID,
        temppayband.TempPayBandID,
        CAST(NULL AS DOUBLE) AS SavedPayRate,
        tempjobratemaster.payrate AS MasterPayRate,
        MasterPayRate AS OrigPayRate,
        ModifiedTempRate('P',pvacid,ppersid,pplacid,temppayband.temppaybandid,OrigPayRate) AS ModifiedPayRate,
        ModifiedPayRate AS PayRate,
        CAST(NULL AS DOUBLE) AS SavedChargeRate,
        tempjobratemaster.chargerate AS MasterChargeRate,
        MasterChargeRate AS OrigChargeRate,
        round(origchargerate-(isnull((SELECT FIRST Discount FROM vacancy WHERE vacancyid = pvacid),0)*origchargerate/100),2) AS DiscountedCharge,
        ModifiedTempRate('C',pvacid,ppersid,pplacid,temppayband.temppaybandid,DiscountedCharge) AS ModifiedChargeRate,
        ModifiedChargeRate AS ChargeRate,
        tempjobratemaster.extrarate1,
        tempjobratemaster.extrarate2,
        tempjobratemaster.extrarate3 ,
        isnull(temppayband.IsExpenses,0) AS IsExpenses
        FROM tempjobratemaster KEY JOIN(tempjobtype,temppayband)
        WHERE tempjobtype.tempjobtypeid = pjobtypeid AND pdirectengagement=0
        AND OverrideTempRate(pvacid,pplacid,temppayband.temppaybandid,tempjobratemaster.Grade,tempjobratemaster.filter1,tempjobratemaster.filter2,
            tempjobratemaster.filter3,tempjobratemaster.Startdate,tempjobratemaster.Enddate) IS NULL
        AND(startdate IS NULL OR startdate <= CURRENT DATE) AND(enddate IS NULL OR enddate >= CURRENT DATE)
        AND(tempjobratemaster.grade IS NULL OR tempjobratemaster.grade LIKE isnull(entitygrade(pvacid,ppersid,pplacid,NULL),'%'))
        AND(tempjobratemaster.filter1 IS NULL OR tempjobratemaster.filter1 LIKE isnull(entityratefilter(1,NULL,pvacid,ppersid,pplacid,NULL),'%'))
        AND(tempjobratemaster.filter2 IS NULL OR tempjobratemaster.filter2 LIKE isnull(entityratefilter(2,NULL,pvacid,ppersid,pplacid,NULL),'%')) 
        AND(tempjobratemaster.filter3 IS NULL OR tempjobratemaster.filter3 LIKE isnull(entityratefilter(3,NULL,pvacid,ppersid,pplacid,NULL),'%'))
        AND tempjobtype."dynamic" = 1
        ORDER BY IsExpenses ASC, startdate ASC,grade ASC,filter3 ASC,soLtd ASC,soAWR ASC,sortorder ASC FOR READ ONLY do
     CASE  FName  
       WHEN  'P' THEN SET rv = payrate
       WHEN 'V' THEN  SET rv = discountedcharge
       WHEN 'ExtraRate1' THEN  SET rv = ExtraRate1
       WHEN 'ExtraRate2' THEN  SET rv = ExtraRate2
       WHEN 'ExtraRate3' THEN  SET rv = ExtraRate3
      END; 
      RETURN rv
    END FOR
  ELSE SET pvacid = id; 
    SET ppersid = NULL; 
    SET pplacid = NULL; 
    SELECT tempjobtypeid INTO pjobtypeid FROM vacancy WHERE vacancyid = pvacid; 
    FOR VRATEFOR AS VRATECUR no scroll cursor FOR
      SELECT mastertemprate(pJobTypeID,temppayband.temppayBandID,tempjobrate.grade,tempjobrate.filter1,tempjobrate.filter2,tempjobrate.filter3,tempjobrate.Startdate,tempjobrate.Enddate) AS MasterID,
        temppayband.sortorder,
        temppayband.description,
        tempjobrate.Startdate AS StartDate,
        tempjobrate.Enddate AS EndDate,
        tempjobrate.Grade,
        tempjobrate.filter3,
        isnull(tempjobrate.filter2,'Z') AS soltd,
        isnull(tempjobrate.filter1,'Z') AS soawr,
        tempjobrate.VacancyID,
        tempjobrate.TempJobRateID,
        temppayband.TempPayBandID,
        tempjobrate.PayRate AS SavedPayRate,
        (SELECT FIRST payrate FROM tempjobratemaster WHERE tempjobratemasterid = masterid) AS OrigPayRate,
        isnull(SavedPayRate,OrigPayRate) AS PayRate,
        tempjobrate.ChargeRate AS SavedChargeRate,
        (SELECT FIRST chargerate FROM tempjobratemaster WHERE tempjobratemasterid = masterid) AS OrigChargeRate,
        isnull(SavedChargeRate,OrigChargeRate) AS ChargeRate,
        round(chargerate-(isnull(vacancy.discount,0)*chargerate/100),2) AS DiscountedCharge,
        isnull(tempjobrate.extrarate1,(SELECT FIRST extrarate1 FROM tempjobratemaster WHERE tempjobratemasterid=masterid)) AS extrarate1,
        isnull(tempjobrate.extrarate2,(SELECT FIRST extrarate2 FROM tempjobratemaster WHERE tempjobratemasterid=masterid)) AS extrarate2,
        isnull(tempjobrate.extrarate3,(SELECT FIRST extrarate3 FROM tempjobratemaster WHERE tempjobratemasterid=masterid)) AS extrarate3,
        isnull(temppayband.IsExpenses,0) AS IsExpenses
        FROM tempjobrate KEY JOIN(vacancy,temppayband)
        WHERE vacancy.vacancyid = pvacid
        AND(startdate IS NULL OR startdate <= CURRENT DATE) AND(enddate IS NULL OR enddate >= CURRENT DATE)
        AND(tempjobrate.grade IS NULL OR tempjobrate.grade LIKE isnull(entitygrade(pvacid,NULL,NULL,NULL),'%'))
        AND(tempjobrate.filter1 IS NULL OR tempjobrate.filter1 LIKE isnull(entityratefilter(1,NULL,pvacid,NULL,NULL,NULL),'%'))
        AND(tempjobrate.filter2 IS NULL OR tempjobrate.filter2 LIKE isnull(entityratefilter(2,NULL,pvacid,NULL,NULL,NULL),'%'))
        AND(tempjobrate.filter3 IS NULL OR tempjobrate.filter3 LIKE isnull(entityratefilter(3,NULL,pvacid,NULL,NULL,NULL),'%')) UNION ALL
      SELECT tempjobratemaster.tempjobratemasterid AS masterid,
        temppayband.sortorder,
        temppayband.description,
        tempjobratemaster.Startdate AS StartDate,
        tempjobratemaster.Enddate AS EndDate,
        tempjobratemaster.Grade,
        tempjobratemaster.filter3,
        isnull(tempjobratemaster.filter2,'Z') AS soltd,
        isnull(tempjobratemaster.filter1,'Z') AS soawr,
        CAST(pvacid AS CHAR(20)) AS VacancyID,
        CAST(NULL AS CHAR(20)) AS TempJobRateID,
        temppayband.TempPayBandID,
        CAST(NULL AS DOUBLE) AS SavedPayRate,
        tempjobratemaster.payrate AS OrigPayRate,
        OrigPayRate AS PayRate,
        CAST(NULL AS DOUBLE) AS SavedChargeRate,
        tempjobratemaster.chargerate AS OrigChargeRate,
        OrigChargeRate AS ChargeRate,
        round(chargerate-(isnull((SELECT Discount FROM vacancy WHERE vacancyid = pvacid),0)*chargerate/100),2) AS DiscountedCharge,
        tempjobratemaster.extrarate1,
        tempjobratemaster.extrarate2,
        tempjobratemaster.extrarate3 ,
        isnull(temppayband.IsExpenses,0) AS IsExpenses
        FROM tempjobratemaster KEY JOIN(tempjobtype,temppayband)
        WHERE tempjobtype.tempjobtypeid = pjobtypeid
        AND OverrideTempRate(pvacid,NULL,temppayband.temppaybandid,tempjobratemaster.Grade,tempjobratemaster.filter1,tempjobratemaster.filter2,
           tempjobratemaster.filter3,tempjobratemaster.Startdate,tempjobratemaster.Enddate) IS NULL
        AND(startdate IS NULL OR startdate <= CURRENT DATE) AND(enddate IS NULL OR enddate >= CURRENT DATE)
        AND(tempjobratemaster.grade IS NULL OR tempjobratemaster.grade LIKE isnull(entitygrade(pvacid,NULL,NULL,NULL),'%'))
        AND(tempjobratemaster.filter1 IS NULL OR tempjobratemaster.filter1 LIKE isnull(entityratefilter(1,NULL,pvacid,NULL,NULL,NULL),'%'))
        AND(tempjobratemaster.filter2 IS NULL OR tempjobratemaster.filter2 LIKE isnull(entityratefilter(2,NULL,pvacid,NULL,NULL,NULL),'%'))
        AND(tempjobratemaster.filter3 IS NULL OR tempjobratemaster.filter3 LIKE isnull(entityratefilter(3,NULL,pvacid,NULL,NULL,NULL),'%'))
        AND tempjobtype."dynamic" = 1
        ORDER BY IsExpenses ASC, startdate ASC,grade ASC,filter3 ASC,soLtd ASC,soAWR ASC,sortorder ASC FOR READ ONLY do
     CASE  FName  
       WHEN 'P' THEN SET rv = payrate
       WHEN 'V' THEN  SET rv = discountedcharge
       WHEN 'ExtraRate1' THEN  SET rv = ExtraRate1
       WHEN 'ExtraRate2' THEN  SET rv = ExtraRate2
       WHEN 'ExtraRate3' THEN  SET rv = ExtraRate3
      END; 
      RETURN rv
    END FOR END IF; 
  RETURN rv
END
}