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