Wednesday, 16 October 2013

Removing old public Interactive Reports

By saving public Apex Interactive Reports you can offer various custom reports to users without programming. The user opens the page and can choose the reports from the Reports drop down list.
New reports can be added by defining the new report and then exporting the application and importing it in the production environment. Changing existing reports can in some cases cause double entries in the Reports drop down list. This is because the changed report has received a new ID and therefor the original report is not overwritten.
The following procedure can be run for each relevant page before importing the application. It deletes the existing public reports for a specific application and page.

create or replace 
  procedure delete_public_ir_report
                ( p_app_id      in  number
                , p_page_id     in  number
                ) is
  dbms_output.put_line('Deleting public IR reports for application '||p_app_id||' page '||p_page_id);
  for r in ( select * 
             from   apex_application_page_ir_rpt
             where  application_id = p_app_id
               and  page_id        = p_page_id
               and  report_type    = 'PUBLIC'
    dbms_output.put_line('Deleting report '||nvl(r.report_name,'with id '||r.report_id));
  end loop;

This procedure works in my situation but each situation is different. Be careful to apply this procedure to your environment and test the results thoroughly before applying it to a production environment.

Happy apexing