pears.MasterRosterCancel

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."MasterRosterCancel"( IN "MRShiftID" CHAR(20),IN "Sdate" DATE,IN "EDate" DATE,IN "SusOrResched" CHAR(1),IN "MRChangeReason" CHAR(20) ) 
BEGIN
  DECLARE "CancelReasonID" CHAR(2);
  -- SusOrResched S - suspend R - reschedule
  IF "isnull"("SDate",'') = '' THEN SET "Sdate" = CURRENT DATE
  END IF;
  IF "isnull"("EDate",'') = '' THEN SET "Edate" = "dateadd"("year",10,CURRENT DATE)
  END IF;
  IF EXISTS(SELECT * FROM "tempshiftplan" WHERE "masterrostershiftid" = "MRShiftID" AND "ShiftDate" BETWEEN "SDate" AND "EDate") THEN
    // need TO do something
    IF "SusOrResched" = 'S' THEN
      IF "MRChangeReason" <> '' THEN
        SET "CancelReasonID" = 'A'
      END IF;
      FOR "forlab" AS "curs" no scroll cursor FOR SELECT "tempshiftplanid" AS "tspid" FROM "tempshiftplan" WHERE "masterrostershiftid" = "MRShiftID" AND "ShiftDate" BETWEEN "SDate" AND "EDate" do
        CALL "TempShiftCancel"("tspid","CancelReasonID",'')
      -- look up shiftid from plan
      END FOR
    ELSE IF "MRChangeReason" <> '' THEN
        SET "CancelReasonID" = 'A'
      END IF;
      FOR "forlab2" AS "curs2" no scroll cursor FOR SELECT "tempshiftplanid" AS "tspid" FROM "tempshiftplan" WHERE "masterrostershiftid" = "MRShiftID" AND "ShiftDate" BETWEEN "SDate" AND "EDate" do
        CALL "TempShiftCancel"("tspid","CancelReasonID",'')
      END FOR
    END IF
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."MasterRosterCancel" IS 
{CREATE PROCEDURE pears."MasterRosterCancel"                              (IN MRShiftID CHAR(20),IN Sdate DATE,IN EDate DATE,IN SusOrResched CHAR(1),IN MRChangeReason CHAR(20) )                               
BEGIN
  DECLARE "CancelReasonID" CHAR(2);
  -- SusOrResched S - suspend R - reschedule
  IF "isnull"("SDate",'') = '' THEN SET "Sdate" = CURRENT DATE
  END IF;
  IF "isnull"("EDate",'') = '' THEN SET "Edate" = "dateadd"("year",10,CURRENT DATE)
  END IF;
  IF EXISTS(SELECT * FROM "tempshiftplan" WHERE "masterrostershiftid" = "MRShiftID" AND "ShiftDate" BETWEEN "SDate" AND "EDate") THEN
    // need TO do something
    IF "SusOrResched" = 'S' THEN
      IF "MRChangeReason" <> '' THEN
        SET "CancelReasonID" = 'A'
      END IF;
      FOR "forlab" AS "curs" no scroll cursor FOR SELECT "tempshiftplanid" AS "tspid" FROM "tempshiftplan" WHERE "masterrostershiftid" = "MRShiftID" AND "ShiftDate" BETWEEN "SDate" AND "EDate" do
        CALL "TempShiftCancel"("tspid","CancelReasonID",'')
      END FOR -- look up shiftid from plan
    ELSE IF "MRChangeReason" <> '' THEN
        SET "CancelReasonID" = 'A'
      END IF;
      FOR "forlab2" AS "curs2" no scroll cursor FOR SELECT "tempshiftplanid" AS "tspid" FROM "tempshiftplan" WHERE "masterrostershiftid" = "MRShiftID" AND "ShiftDate" BETWEEN "SDate" AND "EDate" do
        CALL "TempShiftCancel"("tspid","CancelReasonID",'')
      END FOR
    END IF
  END IF
END
}