====== pears.AWRQualified ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
</code>