====== pears.MasterRosterCancel ======
<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 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
}
</code>