1 Reply Latest reply: Oct 1, 2012 11:04 AM by 450214 RSS

    User Saved IR Reports got deleted

    450214
      Hi,

      We have apex 4.0.2 version running in dev, test and prod enviornments.
      I always maintain copy from Dev in our version control SVN and move across Test and Prod environments.

      Today when we moved the copy for eg: f200.sql from SVN version control to Prod environment all
      User Saved IR Reports got deleted.

      I see the users reports are still there in WWV_FLOW_WORKSHEETS_RPTS apex meta data table.

      But when i join with WWV_FLOW_WORKSHEETS table on worksheet_id i get no data found.

      I saw new worksheet_id's are generated for all the IR reports in that app 200.

      As per the forums, When you change the app ids across environments users IR reports will be deleted.

      david peakes blog [http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html] is user reports will not be deleted when
      we maintain the app id consistent across all the environments

      Any suggestions what could be the cause ??
      Am I missiing anything ??

      Thnx
      MK
        • 1. Re: User Saved IR Reports got deleted
          450214
          Hi,

          My problem got resolved.
          I thought of sharing the solution

          First of all the main reason for the cause was we had one more development apex environment and we moved the app from that environment to
          prod, it made the user saved ir reports got deleted, even though the app no(200) is same as in prod app no(200).

          The way i recovered the orphaned user saved ir reports ,was I need to update the worksheet id in one of the apex meta data tables.
          I was able to reproduce the issue in test environment and applied the below fix it worked and later applied the fix in production, we did not see any kind of issues.

          Run the below steps as SYSTEM user
          Step 1:

          -- create a temporary table

          create ir_user_old_reports_backup as SELECT * FROM APEX_040000.WWV_FLOW_WORKSHEET_RPTS where FLOW_ID = 200 and
          and IS_DEFAULT = 'N' and SESSION_ID is null and STATUS = 'PRIVATE'
          and WORKSHEET_ID NOT IN (SELECT ID FROM APEX_040000.WWV_FLOW_WORKSHEETS WHERE FLOW_ID = 200 )



          Step 2:
          -- Run the below pl/sql block

          begin

          for c1 in (SELECT id, page_id FROM ir_user_old_reports_backup )
          loop
          -- Get the new worksheet id to link to orphaned user saved ir reports
          for c2 IN (select id from APEX_040000.WWV_FLOW_WORKSHEETS where FLOW_ID = 200 and PAGE_ID = c1.PAGE_ID)
          loop
          -- update the new worksheet id to link orphaned user saved ir reports
          update APEX_040000.WWV_FLOW_WORKSHEET_RPTS set WORKSHEET_ID = c2.id where id = c1.id;
          exit;
          end loop;
          end loop;
          end;


          Thnx
          MK

          Note: Oracle does not provide any kind of support or assistance if you make any kind of changes to APEX meta data tables.