PROCEDURE reorganize_report (
      reportid                IN       ebip.report_information.report_id%TYPE,
      oldmainfolderid         IN       ebip.category_detail.category_id%TYPE,
      oldparentfolderid       IN       ebip.category_detail.category_id%TYPE,
      newmainfolderid         IN       ebip.category_detail.category_id%TYPE,
      newparentfolderid       IN       ebip.category_detail.category_id%TYPE,
      sbcuid                  IN       ebip.report_detail.update_sbcuid%TYPE,
      oldreportdirectoryurl   OUT      VARCHAR2,
      newreportdirectoryurl   OUT      VARCHAR2,
      reportfilenames         OUT      VARCHAR2,
      TYPE oldreportparameter IS VARRAY (100) OF VARCHAR2 (6);
      TYPE newreportparameter IS VARRAY (100) OF VARCHAR2 (6);
   )
   IS
      todaydate             DATE;
      oldreportparameters   ebip.report_detail.report_parameters%TYPE;
      newreportparameters   ebip.report_detail.report_parameters%TYPE;
      newrelativepath       ebip.category_detail.category_samba_location%TYPE;
      filename              VARCHAR2 (100);

      CURSOR getreports
      IS
         SELECT report_parameters
           FROM report_detail
          WHERE report_id = reportid;
   BEGIN
      SELECT physical_location || category_samba_location
        INTO oldreportdirectoryurl
        FROM category_detail cd, report_location rl, samba_share_location ssl
       WHERE cd.category_id = oldparentfolderid
         AND rl.category_id = oldmainfolderid
         AND rl.share_id = ssl.share_id;

      SELECT physical_location, category_samba_location
        INTO newreportdirectoryurl, newrelativepath
        FROM category_detail cd, report_location rl, samba_share_location ssl
       WHERE cd.category_id = newparentfolderid
         AND rl.category_id = newmainfolderid
         AND rl.share_id = ssl.share_id;

      newreportdirectoryurl := newreportdirectoryurl || newrelativepath;

      FOR tempgr IN getreports
      LOOP
         oldreportparameters := tempgr.report_parameters;
         filename := getfilename (oldreportparameters);
         reportfilenames := reportfilenames || filename || ',';

         UPDATE report_detail
            SET report_parameters =
                               newrelativepath || filename || ':UNEPFlag=true'
          WHERE report_id = reportid
            AND report_parameters = oldreportparameters;
      END LOOP;

      UPDATE category_resource
         SET category_id = newparentfolderid
       WHERE ID = reportid AND category_id = oldparentfolderid;

      SELECT SYSDATE
        INTO todaydate
        FROM DUAL;

      UPDATE report_detail
         SET report_refresh_date = todaydate,
             update_sbcuid = RTRIM (sbcuid)
       WHERE report_id = reportid;
      
-- Calling reorganize_myreports() Stored procedure of myreports_spk package

    myreports_spk.reorganize_myreports(reportid,newparentfolderid);
     
  EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         RAISE;
   
   END reorganize_report;