pears.AWRQualified

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."AWRQualified" IS 
{CREATE FUNCTION AWRQualified 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN jobmasterid CHAR(20),IN WeekNo INTEGER,IN VacID CHAR(20),IN PersID CHAR(20),IN PlacID CHAR(20),IN ShiftID CHAR(20))
RETURNS INTEGER
NOT deterministic
BEGIN
  DECLARE pid CHAR(20);
  DECLARE vid CHAR(20);
  DECLARE jobmid CHAR(20);
  DECLARE sdate DATE;
  DECLARE AWRStat SMALLINT;
  DECLARE Override SMALLINT;
  DECLARE Odate DATE;
  DECLARE @Grade CHAR(4);
  DECLARE Spec CHAR(4);
  -- get last week
  IF weekno IS NOT NULL THEN
    SET weekno=tempgetweekago(weekno/100,MOD(weekno,100),1)
  END IF;
  IF jobmasterid IS NOT NULL THEN
    SELECT FIRST
      AWRStatus,AWRjobmasterid,isnull(OverrideSystem,1),OverrideDate INTO AWRstat,jobmid,Override,Odate FROM AWRjobmaster WHERE AWRjobmasterid = jobmasterid ORDER BY whenentered DESC;
    IF odate IS NOT NULL AND weekno >= weekcontaining(ODate) THEN
      IF Override = 3 THEN RETURN AWRQualifiedCustomOverRide(0,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
      ELSEIF Override = 2 THEN RETURN AWRQualifiedCustomOverRide(1,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
      END IF
    END IF;
    IF AWRWeeks(jobmid,WeekNo,vid,pid,placid,shiftid) >= 12 THEN
      RETURN AWRQualifiedCustomOverRide(1,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
    ELSE RETURN AWRQualifiedCustomOverRide(0,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
    END IF
  END IF;
  IF ShiftID IS NOT NULL THEN
    -- get last week
    SELECT personid,vacancyid,dateadd(DAY,-7,shiftdate) INTO pid,vid,sdate FROM tempshift WHERE tempshiftid = shiftid;
    SELECT AWREntityGrade(VID,PID,PlacID,ShiftID),EntitySpeciality(VID,PID,PlacID,ShiftID) INTO @Grade,Spec;
    SELECT FIRST
      AWRStatus,AWRjobmasterid,isnull(OverrideSystem,1),OverrideDate INTO AWRstat,jobmid,Override,Odate FROM AWRjobmaster WHERE personid = pid AND vacancyid = vid
      AND isnull(Grade,'') = isnull(@Grade,'') AND isnull(Speciality,'') = isnull(Spec,'') ORDER BY whenentered DESC;
    IF odate IS NOT NULL AND weekcontaining(sdate) >= weekcontaining(ODate) THEN
      IF Override = 3 THEN RETURN AWRQualifiedCustomOverRide(0,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
      ELSEIF Override = 2 THEN RETURN AWRQualifiedCustomOverRide(1,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
      END IF
    END IF;
    IF AWRWeeks(jobmid,weekcontaining(sdate),vid,pid,placid,shiftid) >= 12 THEN
      RETURN AWRQualifiedCustomOverRide(1,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
    ELSE RETURN AWRQualifiedCustomOverRide(0,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
    END IF
  ELSE
    IF persid IS NOT NULL AND vacid IS NOT NULL AND weekno IS NOT NULL THEN
      SELECT AWREntityGrade(VacID,PersID,PlacID,ShiftID),EntitySpeciality(VacID,PersID,PlacID,ShiftID) INTO @Grade,Spec;
      SELECT FIRST
        AWRStatus,AWRjobmasterid,isnull(OverrideSystem,1),OverrideDate INTO AWRstat,jobmid,Override,Odate FROM AWRjobmaster WHERE personid = persid AND vacancyid = vacid
        AND isnull(Grade,'') = isnull(@Grade,'') AND isnull(Speciality,'') = isnull(Spec,'') ORDER BY whenentered DESC;
      IF weekno >= weekcontaining(ODate) THEN
        IF Override = 3 THEN RETURN AWRQualifiedCustomOverRide(0,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
        ELSEIF Override = 2 THEN RETURN AWRQualifiedCustomOverRide(1,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
        END IF
      END IF;
      IF AWRWeeks(jobmid,weekno,vacid,persid,placid,shiftid) >= 12 THEN
        RETURN AWRQualifiedCustomOverRide(1,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
      ELSE RETURN AWRQualifiedCustomOverRide(0,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
      END IF
    ELSE
      IF placid IS NOT NULL AND weekno IS NOT NULL THEN
        SELECT personid,vacancyid INTO pid,vid FROM placement KEY JOIN employment WHERE placementid = placid;
        SELECT AWREntityGrade(VID,PID,PlacID,ShiftID),EntitySpeciality(VID,PID,PlacID,ShiftID) INTO @Grade,
          Spec;
        SELECT FIRST
          AWRStatus,AWRjobmasterid,isnull(OverrideSystem,1),OverrideDate INTO AWRstat,jobmid,Override,Odate FROM AWRjobmaster WHERE personid = pid AND vacancyid = vid
          AND isnull(Grade,'') = isnull(@Grade,'') AND isnull(Speciality,'') = isnull(Spec,'') ORDER BY whenentered DESC;
        IF weekno >= weekcontaining(ODate) THEN
          IF Override = 3 THEN RETURN AWRQualifiedCustomOverRide(0,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
          ELSEIF Override = 2 THEN RETURN AWRQualifiedCustomOverRide(1,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
          END IF
        END IF;
        IF AWRWeeks(jobmid,weekno,vid,pid,placid,shiftid) >= 12 THEN
          RETURN AWRQualifiedCustomOverRide(1,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
        ELSE RETURN AWRQualifiedCustomOverRide(0,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
        END IF
      END IF
    END IF
  END IF;
  RETURN AWRQualifiedCustomOverRide(NULL,JobMasterID,WeekNo,VacID,PersID,PlacID,ShiftID)
END
}