COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE ADDREPORT 
(
  TREENAME VARCHAR(30),
  FOLDERNAME VARCHAR(64),
  PROCEDURENAME VARCHAR(32),
  REPORTNAME VARCHAR(64),
  TARGETRANK INTEGER,
  REPORTVENDOR VARCHAR(64),
  REPORTFILENAME VARCHAR(64),
  STANDARDREPORTTF VARCHAR(1),
  ROLEWITHACCESS VARCHAR(15)
)
RETURNS
(
  RESULTSTRING VARCHAR(240)
)
AS
BEGIN EXIT; END ^

ALTER PROCEDURE ADDREPORT 
(
  TREENAME VARCHAR(30),
  FOLDERNAME VARCHAR(64),
  PROCEDURENAME VARCHAR(32),
  REPORTNAME VARCHAR(64),
  TARGETRANK INTEGER,
  REPORTVENDOR VARCHAR(64),
  REPORTFILENAME VARCHAR(64),
  STANDARDREPORTTF VARCHAR(1),
  ROLEWITHACCESS VARCHAR(15)
)
RETURNS
(
  RESULTSTRING VARCHAR(240)
)
AS
/* This procedure Adds a report to a Report Tree. */
/* TreeName must be the name of an existing Report Tree */
/* FolderName must be the name of an existing Folder */
declare variable   RptSetKey INTEGER;
declare variable   RptMasKey INTEGER;
declare variable   RptSetNodKey INTEGER;
declare variable   MasNodKey INTEGER;
declare variable   FolderRankInTree INTEGER;
declare variable   MaxRankInFolder INTEGER;
declare variable   ExtFolderName VARCHAR(240);
declare variable   DummyInt INTEGER;
declare variable   DummyStr30 varchar(30);
begin   /* program block */
/*Make sure RPTNAME is unique*/
/*If not, Exit */
  select
    count(*)
  from
    RptMas
  where
    Upper(RptName)=Upper(:ReportName)
  into :DummyInt;
  If (DummyInt>0) then
  begin
    ResultString = 'Refused: Report Name is not unique.';
  end
  else
  begin
/*Look up Report Set Key */
/*If not found, Exit */
  select
    max(RptSetKey)
  from
    RptSet
  where
    Upper(RptSetDes)=Upper(:TreeName)
  into :RptSetKey;
  If (RptSetKey is null) then
  begin
    ResultString = 'Refused: Tree Name does not match known Trees.';
  end
  else
  begin
  /*Look up FolderRankInTree of Folder matching FolderName */
    select
      max(RptSetNodRnk)
    from
      RptSetNod
    where
      RptSetKey=:RptSetKey
      and Upper(RptSetNodDes)=Upper(:FolderName)
    into :FolderRankInTree;
    /* If Folder not found, Set up Report Folder. */
    if (FolderRankInTree is null) then
    begin
      select
        max(RptSetNodRnk)
      from
        RptSetNod
      where
        RptSetKey=:RptSetKey
      into :FolderRankInTree;
      FolderRankInTree = FolderRankInTree + 1;
      execute procedure newrptsetfolder (:TreeName, '/', :FolderName, '', :FolderRankInTree)
      returning_values :DummyInt, :DummyInt, :DummyInt, :MasNodKey;
    end
    else
    /* If Folder IS found, identify its MasNodKey */
    begin
      select
        max(RptSetNodKey)
      from
        RptSetNod
      where
        RptSetKey=:RptSetKey
        and Upper(RptSetNodDes)=Upper(:FolderName)
      into :MasNodKey;
    end
    /*Register Report*/
    execute procedure DeclareRptMas (:ProcedureName, :ReportName, :ReportVendor, :ReportFileName, '')
    returning_values :RptMasKey;
    /*Set Ranks*/
    /*Identify topmost rank in Folder*/
    select
      max(RptSetNodRnk)
    from
      RptSetNod
    where
      MasNodKey=:MasNodKey
    into :MaxRankInFolder;
    if (MaxRankInFolder is null) then begin MaxRankInFolder = 0; end
    /*If TargetRank is 0, target the end of the stack*/
    if (TargetRank =0) then begin TargetRank = (MaxRankInFolder +1); end
    /*If appropriate, reduce TargetRank */
    if (TargetRank > (MaxRankInFolder +1) ) then begin TargetRank = (MaxRankInFolder +1); end
    /*If necessary, make a gap in the ranks*/
    for select RptSetNodKey
        from RptSetNod
        where MasNodKey = :MasNodKey
          and RptSetNodRnk >= :TargetRank
        into :RptSetNodKey do
    begin
      update RptSetNod set RptSetNodRnk = (RptSetNodRnk + 1)
      where RptSetNodKey = :RptSetNodKey;
    end
    /*Associate Report with Report Folder*/
    ExtFolderName = '/' || FolderName;
    execute procedure NewRptSetRpt (:TreeName, :ExtFolderName, '', '', :TargetRank, :ReportName)
    returning_values :DummyInt, :DummyInt, :DummyInt, :DummyInt;
    /*Set StandardTF flag*/
    update rptmas set stdreptf=:StandardReportTF where rptmaskey=:RptMasKey;
    /*Assign Role Access*/
    execute procedure setreportroles (:ReportName, :RoleWithAccess)
    returning_values :DummyStr30;
    update rptsetnod 
    set lmxrolkey=(select lmxrolkey from lmxrol where lmxrollab='REPORTING') 
    where lmxrolkey is null;
    ResultString = 'Accepted.';
  end
  end
end
 ^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;