COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE DROPREPORT 
(
  REPORTFILENAME VARCHAR(64)
)
RETURNS
(
  RESULTSTRING VARCHAR(240)
)
AS
BEGIN EXIT; END ^


ALTER PROCEDURE DROPREPORT 
(
  REPORTFILENAME VARCHAR(64)
)
RETURNS
(
  RESULTSTRING VARCHAR(240)
)
AS
/* This procedure Drops a report from a Report Tree. */
/* and closes any resulting rank gap in the Folder */
declare variable   RptMasKey INTEGER;
declare variable   FolderKey INTEGER;
declare variable   ReportRankInFolder INTEGER;
declare variable   RptSetNodKey INTEGER;
declare variable   MaxRank INTEGER;
begin   /* program block */
/*Get RepMasKey*/
/*If FileName is not registered, Exit */
  select
    max(RptMasKey)
  from
    RptMas
  where
    Upper(FILENAME)=Upper(:ReportFileName)
  into :RptMasKey;
  If (RptMasKey is null) then
  begin
    ResultString = 'Report ' || ReportFileName || ' is not registered.';
  end
  else
  begin
  /*Identify FolderKey and ReportRankInFolder */
    select
      MasNodKey,RptSetNodRnk
    from
      RptSetNod
    where
      RptMasKey=:RptMasKey
    into :FolderKey, ReportRankInFolder;
  /*Drop related records from RptSesPar */
    delete 
    from 
      RptSesPar
    where
      RptSesKey in (Select Distinct RptSesKey from RptSes where RptMasKey=:RptMasKey);
  /*Drop related records from RptSes */
    delete 
    from 
      RptSes
    where
      RptMasKey=:RptMasKey;
  /*Drop record from RptSetNod */
    delete 
    from 
      RptSetNod
    where
      RptMasKey=:RptMasKey;
  /*Drop record from RptMas */
    delete 
    from 
      RptMas
    where
      RptMasKey=:RptMasKey;
  /*Identify topmost remaining rank in Folder*/
  /*If it's higher than ReportRankInFolder, close gap in ranks*/
    select
      max(RptSetNodRnk)
    from
      RptSetNod
    where
      MasNodKey=:FolderKey
    into :MaxRank;
    if (MaxRank > ReportRankInFolder) then 
    for select RptSetNodKey
        from RptSetNod
        where MasNodKey=:FolderKey
          and RptSetNodRnk > :ReportRankInFolder
        into :RptSetNodKey do
    begin
      update RptSetNod set RptSetNodRnk = (RptSetNodRnk - 1) 
      where RptSetNodKey=:RptSetNodKey; 
    end
    ResultString = 'Successful.';
  end
end
 ^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;