pears.AWRQualified

CREATE FUNCTION "pears"."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