pears.AWRGap

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
}