pears.MasterRosterCurrent

CREATE PROCEDURE "pears"."MasterRosterCurrent"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "SDate" DATE,IN "EDate" DATE,IN "VacID" CHAR(20),IN "MRID" CHAR(20),IN "iid" CHAR(20) ) 
RESULT( "SID" CHAR(20),"sdate" DATE ) 
BEGIN
  IF "isnull"("MRID",'') = '' THEN
    DELETE FROM "MRShiftIDs" WHERE "instid" = "iid";
    FOR "forlab" AS "curs" no scroll cursor FOR SELECT "masterrostershiftid","nextdate","finaldate","daysincycle","startdate","shiftstartdate" FROM "masterrostershift" KEY JOIN "masterroster" WHERE "vacancyid" = "VacID"
        AND "startdate" <= "edate" AND "shiftstartdate" <= "edate" AND("masterrostershift"."finaldate" IS NULL OR "masterrostershift"."finaldate" >= "SDate") do
      IF "nextdate" BETWEEN "sdate" AND "edate" THEN
        INSERT INTO "MRShiftIDs"( "instid","sids","sdates" ) VALUES( "iid","masterrostershiftid","nextdate" ) 
      ELSE
        IF "nextdate" > "edate" THEN
          while("nextdate" NOT BETWEEN "sdate" AND "edate") AND "nextdate" > "isnull"("finaldate","nextdate"-1) loop
            SET "nextdate" = "nextdate"-"daysincycle"
          END loop;
          IF "nextdate" BETWEEN "sdate" AND "edate" AND "nextdate" >= "startdate" AND "nextdate" >= "shiftstartdate" THEN
            INSERT INTO "MRShiftIDs"( "instid","sids","sdates" ) VALUES( "iid","masterrostershiftid","nextdate" ) 
          END IF END IF;
        IF "nextdate" < "sdate" THEN
          while("nextdate" NOT BETWEEN "sdate" AND "edate") AND "nextdate" > "isnull"("finaldate","nextdate"-1) loop
            SET "nextdate" = "nextdate"+"daysincycle"
          END loop;
          IF "nextdate" BETWEEN "sdate" AND "edate" THEN
            INSERT INTO "MRShiftIDs"( "instid","sids","sdates" ) VALUES( "iid","masterrostershiftid","nextdate" ) 
          END IF
        END IF END IF END FOR;
    SELECT "sids","sdates" FROM "MRShiftIDs" WHERE "instid" = "iid"
  ELSE
    DELETE FROM "MRShiftID2s" WHERE "instid" = "iid";
    FOR "forlab2" AS "curs2" no scroll cursor FOR SELECT "masterrostershiftid","nextdate","finaldate","daysincycle","startdate","shiftstartdate" FROM "masterrostershift" KEY JOIN "masterroster" WHERE "masterroster"."masterrosterid" = "MRID"
        AND "startdate" <= "edate" AND "shiftstartdate" <= "edate" AND("masterrostershift"."finaldate" IS NULL OR "masterrostershift"."finaldate" >= "SDate") do
      IF "nextdate" BETWEEN "sdate" AND "edate" THEN
        INSERT INTO "MRShiftID2s"( "instid","sids","sdates" ) VALUES( "iid","masterrostershiftid","nextdate" ) 
      ELSE
        IF "nextdate" > "edate" THEN
          while("nextdate" NOT BETWEEN "sdate" AND "edate") AND "nextdate" > "isnull"("finaldate","nextdate"-1) loop
            SET "nextdate" = "nextdate"-"daysincycle"
          END loop;
          IF "nextdate" BETWEEN "sdate" AND "edate" AND "nextdate" >= "startdate" AND "nextdate" >= "shiftstartdate" THEN
            INSERT INTO "MRShiftID2s"( "instid","sids","sdates" ) VALUES( "iid","masterrostershiftid","nextdate" ) 
          END IF END IF;
        IF "nextdate" < "sdate" THEN
          while("nextdate" NOT BETWEEN "sdate" AND "edate") AND "nextdate" > "isnull"("finaldate","nextdate"-1) loop
            SET "nextdate" = "nextdate"+"daysincycle"
          END loop;
          IF "nextdate" BETWEEN "sdate" AND "edate" THEN
            INSERT INTO "MRShiftID2s"( "instid","sids","sdates" ) VALUES( "iid","masterrostershiftid","nextdate" ) 
          END IF
        END IF END IF END FOR;
    SELECT "sids","sdates" FROM "MRShiftID2s" WHERE "instid" = "iid"
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."MasterRosterCurrent" IS 
{CREATE PROCEDURE MasterRosterCurrent 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( 
  IN SDate DATE,IN EDate DATE,IN VacID CHAR(20),IN MRID CHAR(20), IN iid CHAR(20) ) 
RESULT( SID CHAR(20),sdate DATE ) 
BEGIN
  IF isnull(MRID,'') = '' THEN
    DELETE FROM MRShiftIDs WHERE instid = iid;
    FOR forlab AS curs no scroll cursor FOR SELECT masterrostershiftid,nextdate,finaldate,daysincycle,startdate,shiftstartdate FROM masterrostershift KEY JOIN masterroster WHERE vacancyid = VacID
        AND startdate <= edate AND shiftstartdate <= edate AND(masterrostershift.finaldate IS NULL OR masterrostershift.finaldate >= SDate) do
      IF nextdate BETWEEN sdate AND edate THEN
        INSERT INTO MRShiftIDs( instid,sids,sdates ) VALUES( iid,masterrostershiftid,nextdate ) 
      ELSE
        IF nextdate > edate THEN
          while(nextdate NOT BETWEEN sdate AND edate) AND nextdate > isnull(finaldate,nextdate-1) loop
            SET nextdate = nextdate-daysincycle
          END loop;
          IF nextdate BETWEEN sdate AND edate AND nextdate >= startdate AND nextdate >= shiftstartdate THEN
            INSERT INTO MRShiftIDs( instid,sids,sdates ) VALUES( iid,masterrostershiftid,nextdate ) 
          END IF END IF;
        IF nextdate < sdate THEN
          while(nextdate NOT BETWEEN sdate AND edate) AND nextdate > isnull(finaldate,nextdate-1) loop
            SET nextdate = nextdate+daysincycle
          END loop;
          IF nextdate BETWEEN sdate AND edate THEN
            INSERT INTO MRShiftIDs( instid,sids,sdates ) VALUES( iid,masterrostershiftid,nextdate ) 
          END IF
        END IF
      END IF END FOR;
    SELECT sids,sdates FROM MRShiftIDs WHERE instid = iid
  ELSE
    DELETE FROM MRShiftID2s WHERE instid = iid;
    FOR forlab2 AS curs2 no scroll cursor FOR SELECT masterrostershiftid,nextdate,finaldate,daysincycle,startdate,shiftstartdate FROM masterrostershift KEY JOIN masterroster WHERE masterroster.masterrosterid = MRID
        AND startdate <= edate AND shiftstartdate <= edate AND(masterrostershift.finaldate IS NULL OR masterrostershift.finaldate >= SDate) do
      IF nextdate BETWEEN sdate AND edate THEN
        INSERT INTO MRShiftID2s( instid,sids,sdates ) VALUES( iid,masterrostershiftid,nextdate ) 
      ELSE
        IF nextdate > edate THEN
          while(nextdate NOT BETWEEN sdate AND edate) AND nextdate > isnull(finaldate,nextdate-1) loop
            SET nextdate = nextdate-daysincycle
          END loop;
          IF nextdate BETWEEN sdate AND edate AND nextdate >= startdate AND nextdate >= shiftstartdate THEN
            INSERT INTO MRShiftID2s( instid,sids,sdates ) VALUES( iid,masterrostershiftid,nextdate ) 
          END IF END IF;
        IF nextdate < sdate THEN
          while(nextdate NOT BETWEEN sdate AND edate) AND nextdate > isnull(finaldate,nextdate-1) loop
            SET nextdate = nextdate+daysincycle
          END loop;
          IF nextdate BETWEEN sdate AND edate THEN
            INSERT INTO MRShiftID2s( instid,sids,sdates ) VALUES( iid,masterrostershiftid,nextdate ) 
          END IF
        END IF
      END IF END FOR;
    SELECT sids,sdates FROM MRShiftID2s WHERE instid = iid
  END IF
END
}