pears.GetTempRateByDesc

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetTempRateByDesc" IS 
{CREATE FUNCTION GetTempRateByDesc 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( IN PayOrCharge CHAR(1),IN PlacOrVac CHAR(1),IN ID CHAR(20), IN descrp CHAR(50), IN @awr tinyint, IN @grade CHAR(4), IN @ltd CHAR(4)) 
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;
  DECLARE pawrval CHAR(1);
  DECLARE pawrfilter CHAR(1);
  DECLARE pawr tinyint;
--  declare @sql long varchar;
  SET rv = NULL;
  SET pawr = isnull(@awr,0);
  IF pawr=0 THEN 
    SET pawrval = 'Z' ;
    SET pawrfilter = '' ;
  ELSE  
    SET pawrval = 'X' ;
    SET pawrfilter = 'Y' ;
  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,pawrval) AS soltd,
        isnull(tempjobrate.filter1,pawrval) 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(temppayband.IsExpenses,0) AS IsExpenses,
        IF pawr = 0  THEN
(RANK() OVER (ORDER BY  IsExpenses, startdate ASC ,grade ASC ,filter3 ASC,soLtd ASC,soAWR ASC,temppayband.sortorder ASC))  
ELSE (RANK() OVER (ORDER BY  IsExpenses, soAWR DESC,temppayband.sortorder ASC,temppayband.description ASC,startdate ASC,enddate ASC,grade ASC))   
endif 
  AS ranka
        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(isnull(tempjobrate.grade,'') = @grade OR (tempjobrate.grade LIKE isnull(entitygrade(pvacid,ppersid,pplacid,NULL),'%') AND @grade=''))
        AND((isnull(tempjobrate.filter1,pawrfilter)=pawrfilter) OR tempjobrate.filter1 LIKE isnull(entityratefilter(1,NULL,pvacid,ppersid,pplacid,NULL),'%'))
        AND(isnull(tempjobrate.filter2,'') = @ltd OR (tempjobrate.filter2 LIKE isnull(entityratefilter(2,NULL,pvacid,ppersid,pplacid,NULL),'%') AND @ltd=''))
        AND(tempjobrate.filter3 IS NULL OR tempjobrate.filter3 LIKE isnull(entityratefilter(3,NULL,pvacid,ppersid,pplacid,NULL),'%'))
        AND temppayband.description LIKE string(descrp, '%')
        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,pawrval) AS soltd,
        isnull(tempjobrate.filter1,pawrval) 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(temppayband.IsExpenses,0) AS IsExpenses,
           IF pawr = 0 THEN
(RANK() OVER (ORDER BY  IsExpenses, startdate ASC ,grade ASC ,filter3 ASC,soLtd ASC,soAWR ASC,temppayband.sortorder ASC)) 
ELSE (RANK() OVER (ORDER BY  isExpenses, soAWR DESC,temppayband.sortorder ASC,temppayband.description ASC,startdate ASC,enddate ASC,grade ASC))    
endif 
  AS ranka
        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(isnull(tempjobrate.grade ,'') =@grade  OR (tempjobrate.grade LIKE isnull(entitygrade(pvacid,ppersid,pplacid,NULL),'%') AND @grade=''))
        AND( (isnull(tempjobrate.filter1,pawrfilter)=pawrfilter) OR tempjobrate.filter1 LIKE isnull(entityratefilter(1,NULL,pvacid,ppersid,pplacid,NULL),'%'))
        AND(isnull(tempjobrate.filter2,'') = @ltd OR (tempjobrate.filter2 LIKE isnull(entityratefilter(2,NULL,pvacid,ppersid,pplacid,NULL),'%') AND @ltd=''))
        AND(tempjobrate.filter3 IS NULL OR tempjobrate.filter3 LIKE isnull(entityratefilter(3,NULL,pvacid,ppersid,pplacid,NULL),'%'))
        AND temppayband.description LIKE string(descrp, '%')  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,pawrval) AS soltd,
        isnull(tempjobratemaster.filter1,pawrval) 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, 
        isnull(temppayband.IsExpenses,0) AS IsExpenses,
        IF pawr = 0  THEN
(RANK() OVER (ORDER BY  IsExpenses, startdate ASC ,grade ASC ,filter3 ASC,soLtd ASC,soAWR ASC,temppayband.sortorder ASC))  
ELSE (RANK() OVER (ORDER BY  IsExpenses, soAWR DESC,temppayband.sortorder ASC,temppayband.description ASC,startdate ASC,enddate ASC,grade ASC))  
endif 
  AS ranka
        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(isnull(tempjobratemaster.grade,'') = @grade OR (tempjobratemaster.grade LIKE isnull(entitygrade(pvacid,ppersid,pplacid,NULL),'%') AND @grade=''))
        AND((isnull(tempjobratemaster.filter1,pawrfilter)=pawrfilter)  OR tempjobratemaster.filter1 LIKE isnull(entityratefilter(1,NULL,pvacid,ppersid,pplacid,NULL),'%'))
        AND(isnull(tempjobratemaster.filter2,'') = @ltd OR (tempjobratemaster.filter2 LIKE isnull(entityratefilter(2,NULL,pvacid,ppersid,pplacid,NULL),'%') AND @ltd=''))
        AND(tempjobratemaster.filter3 IS NULL OR tempjobratemaster.filter3 LIKE isnull(entityratefilter(3,NULL,pvacid,ppersid,pplacid,NULL),'%'))
        AND tempjobtype."dynamic" = 1  AND temppayband.description LIKE string(descrp, '%')  
    ORDER BY  ranka  
 FOR READ ONLY do
      IF payorcharge = 'P' THEN
        SET rv = payrate
      ELSE
        SET rv = chargerate
      END IF;
      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,pawrval) AS soltd,
        isnull(tempjobrate.filter1,pawrval) 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(temppayband.IsExpenses,0) AS IsExpenses,
     IF pawr = 0  THEN   
(RANK() OVER (ORDER BY  isExpenses, startdate ASC ,grade ASC ,filter3 ASC,soLtd ASC,soAWR ASC,temppayband.sortorder ASC))  
ELSE (RANK() OVER (ORDER BY  isExpenses, soAWR DESC,temppayband.sortorder ASC,temppayband.description ASC,startdate ASC,enddate ASC,grade ASC))    
endif 
  AS ranka
        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(isnull(tempjobrate.grade,'')=@grade OR (tempjobrate.grade LIKE isnull(entitygrade(pvacid,NULL,NULL,NULL),'%') AND @grade='') )
        AND((isnull(tempjobrate.filter1,pawrfilter)=pawrfilter)  OR tempjobrate.filter1 LIKE isnull(entityratefilter(1,NULL,pvacid,NULL,NULL,NULL),'%'))
        AND(isnull(tempjobrate.filter2,'') = @ltd  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),'%'))
        AND temppayband.description LIKE string(descrp, '%')   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,pawrval) AS soltd,
        isnull(tempjobratemaster.filter1,pawrval) 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,
        isnull(temppayband.IsExpenses,0) AS IsExpenses, 
     IF pawr = 0  THEN
(RANK() OVER (ORDER BY  startdate ASC ,grade ASC ,filter3 ASC,soLtd ASC,soAWR ASC,temppayband.sortorder ASC))  
ELSE (RANK() OVER (ORDER BY  soAWR DESC,temppayband.sortorder ASC,temppayband.description ASC,startdate ASC,enddate ASC,grade ASC))  
endif 
  AS ranka 
        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(isnull(tempjobratemaster.grade,'')=@grade OR (tempjobratemaster.grade LIKE isnull(entitygrade(pvacid,NULL,NULL,NULL),'%') AND @grade=''))
        AND((isnull(tempjobratemaster.filter1,pawrfilter)=pawrfilter)  OR tempjobratemaster.filter1 LIKE isnull(entityratefilter(1,NULL,pvacid,NULL,NULL,NULL),'%'))
        AND(isnull(tempjobratemaster.filter2,'') = @ltd  OR (tempjobratemaster.filter2 LIKE isnull(entityratefilter(2,NULL,pvacid,NULL,NULL,NULL),'%') AND @ltd=''))
        AND(tempjobratemaster.filter3 IS NULL OR tempjobratemaster.filter3 LIKE isnull(entityratefilter(3,NULL,pvacid,NULL,NULL,NULL),'%'))
        AND tempjobtype."dynamic" = 1  AND temppayband.description LIKE string(descrp, '%')  
ORDER BY ranka
        FOR READ ONLY do
      IF payorcharge = 'P' THEN
        SET rv = payrate
      ELSE
        SET rv = discountedcharge;
      END IF;
      RETURN rv;
    END FOR END IF;
  RETURN rv;
END
}