pears.MasterRosterCurrent [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.MasterRosterCurrent ======
<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"."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
}
</code>