====== pears.AWRGap ======
<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"."AWRGap" IS 
{create function AWRGap 

/* 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
begin
  declare rv integer;
  declare AWRStat integer;
  declare AWRLink char(20);
  declare LastRec integer;
  declare LastWork integer;
  declare pid char(20);
  set rv=0;
  -- check role qualified 
  -- check temp directly employed 
  if nullif(jobmasterid,'') is not null then
    select AWRStatus,AWRLinkCode,personid into AWRStat,
      AWRLink,pid from AWRJobMaster where AWRJobMasterID = jobmasterID;
    if AWRStat = 3 then
      return rv
    end if;
    if exists(select * from person where personid = pid and isnull(directlyemployed,0) >= 1) then
      return rv
    end if;
    -- find most recent work
    with
    V( Rec) as(select max(period) from AWRWeeklyDetail where AWRJobMasterID = any(select AWRJobMasterID from AWRJobMaster where AWRLinkCode = AWRLink) and qualtype = 2 and isnull(disregard,0) = 0 and period <= WeekNo union all
    select max(period) from AWRWeeklyDetail where personid = pid and isnull(disregard,0) = 0 and period <= WeekNo and QualType = 2)
    select max(Rec) into LastWork from V;
    if LastWork is not null then
      if truncnum(LastWork,-2)/100 = truncnum(WeekNo,-2)/100 
        then set rv = WeekNo - LastWork  //same year
          else set rv=datediff(week,weekmonthenddate(LastWork,'W'),weekmonthenddate(WeekNo,'W')) //different year
      end if;
    end if;
    -- subtract pauses since
    select count(distinct period) into LastRec from AWRWeeklyDetail where((personid = pid) or(companyid = any(select companyid from AWRJobMaster key join vacancy key join employment where AWRLinkCode = 
     AWRLink ))) and QualType = 1 and isnull(disregard,0) = 0 and period <= WeekNo and period > LastWork;
    set rv=rv-LastRec;
  end if;
  return rv
end
}
</code>