pears.AWRGap

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