COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */

ALTER PROCEDURE ROLLBACKPAYPERIOD 
(
  PAYSESDEFLAB VARCHAR(15)
)
RETURNS
(
  VRESULT VARCHAR(128)
)
AS
DECLARE VARIABLE PaySesDefKey integer;
DECLARE VARIABLE PaySesKey integer;
DECLARE VARIABLE CurPerDat timestamp;
DECLARE VARIABLE PerEndDat timestamp;
DECLARE VARIABLE HisMasKey integer;
DECLARE VARIABLE HisEveKey integer;
Begin
  /* Using submitted PaySesDefLab, */
  /* acquire PaySesDefKey and CurPerDat from PaySesDef */
  SELECT min(PaySesDefKey), min(CurPerDat) 
         from 	PaySesDef$ 
         where 	PaySesDefLab=:PaySesDefLab
         into 	:PaySesDefKey,
			:CurPerDat;
  if (PaySesDefKey is null) then
  begin
    vResult='Pay Group '|| PaySesDefLab || ' Not Found';
    exit;
  end

  /* Using acquired PaySesDefKey, */
  /* acquire latest PerEndDat from PaySes */
  SELECT max(PerEndDat) 
  from PaySes
  where PaySesDefKey=:PaySesDefKey
  into :PerEndDat;
  if (PerEndDat is null) then
  begin
    vResult='Pay Group '|| PaySesDefLab || ' Has No Closed Periods';
    exit;
  end

  /* Using acquired PaySesDefKey and PerEndDat, */
  /* acquire latest PaySesKey from PaySes */
  SELECT max(PaySesKey) 
  from PaySes
  where PerEndDat=:PerEndDat
  and PaySesDefKey=:PaySesDefKey 
  into :PaySesKey;
  if (PaySesKey is null) then
  begin
    vResult='Pay Group '|| PaySesDefLab || ' Has No Closed Periods-2';
    exit;
  end

  /* we have all the variables we need to go to work */
  vResult='variables collected';

  /* Purge related HisEve and HisMas records */
  /* ======================================= */
  /* Step through each HisMas record having this PaySesKey. */
  /* Acquire HisMasKey from HisMas. */
  FOR SELECT HisMasKey 
  from HisMas
  where PaySesKey=:PaySesKey 
  into :HisMasKey
  Do
  begin

    /* Purge related HisEve records */
   DELETE from HisEve where HisMasKey=:HisMasKey;    

    /* Purge related HisMas records */
    DELETE from HisMas where HisMasKey=:HisMasKey; 
  end

  /* Purge obsoleted PaySes record. */
  DELETE from PaySes where PaySesKey=:PaySesKey;

  /* Purge Update PaySesDef to roll back CurPerDat and CurPerNum. */
  UPDATE PaySesDef$ set CurPerDat=CurPerDat-DayPerPer,
                        CurPerNum=CurPerNum-1
                    where PaySesDefKey=:PaySesDefKey;
  vResult='Pay Group '|| PaySesDefLab || ' rolled back.';
end
 ^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;