pears.AWRQualified [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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>
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
}
</code>