Recover saved interactive reports after an application import

During a recent update of my live APEX system I noticed that the user's saved interactive reports had been lost. I had made numerous updates of the live system before and never experienced this issue before. I found an interesting post from David Peak detailing how to avoid the loss of the saved reports http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html , but before doing this I had to understand exactly what had happened and hopefully work out how to recover the lost reports.

My normal workflow would be to develop in my development environment and then progress this application through testing and into live, preserving the application id through every progression. In this specific instance I had taken a cut of the application from live and imported it into development using a temporary id. On each subsequent promotion I then changed the id to the original, and correct, id. It seems that this changing of the id causes the issue.

Now that I understood how the issue occurred and, thanks to David Peake, understood how to stop this from happening again, I wanted to recover the reports and hopefully calm down irate users.

The saved reports are saved in the table flows_030100.wwv_flow_worksheet_rpts.



Key columns are as follows
flow_id - the id of the application
page_id - the page number where the interactive report is situated
Worksheet_id - the internal unique id
session_id - being blank indicates that this is the "master" version of the individual users saved report.

So now I know where the data is stored, the next step is understanding the data. From David Peake's site mentioned earlier, I know that the worksheet id is the unique identifier for the interactive report, but I knew little else.

After ordering the data based on the date found in the updated_on column I noticed that there was a distinct change in the workspace ID, around the same time that I had done the upgrade. From there it was straightforward enough to realise that I had to update the previous records to include the new worksheet_id that APEX had automatically assigned to the Interactive Region. I also noticed for each unique user and interactive report there were many rows. Although I do not fully understand the use for these multiple rows, except for perhaps auditing purposes, it is clear(after some tests) that the actual saved report is defined by the row with the blank session_id.

Now that I had a bit of understanding of the data I could attempt the recovery. The following is a brief overview of how I actually did it.

1. Determine the new worksheet_id for the specific interactive report where the saved reports are missing. For this, focus on the flow_id(application_id) and page_id. Ensuring that you select data dated after the application upgrade. If there are no rows present for the specific interactive report, then a quick visit to the relevant page in the application will create a row. Simply visiting the report will create a row, but saving a report will allow it to be found even easier. Do not worry about saving a report in a live system, as this report is linked solely to the user you logged in as.
2. Now that you have the correct worksheet_id, you have to determine the rows that contain the data for the saved reports. To do this follow the above procedure, but only include those rows which fall within the date range of your last version of the application.
3. Now simply update the rows containing the saved reports with the new worksheet_id.

A couple of points about the above

1. Before modifying the data you have to unlock the flows_030100 user (or the flows user for whatever version of APEX you use).
2. There is a unique key present that will stop you from having multiple saved reports with the same name, for the same report and for the same user. To get round this you can alter the name to something like, "report A (old)". I'm sure you will think of something better than this, but you get my drift.
3. Remember that you are messing with core APEX tables, and messing with these tables can cause issues with your APEX installation as a whole. Basically, make a backup before you start.

Hopefully the above is of some use to you if you do experience the same issues as I did.