APEX IR Subscriptions #JoelKallmanDay

Interactive Reports allow to send their content per e-mail on a regular basis thanks to the subscription feature. But this can also raise some security concerns. Let’s see how to bring some kind of control into the game…

Requirements

We developed some reporting solution for an Insurance company based on #orclAPEX. Some of the reports need to be sent on a regular basis, as Excel, to people like executive managers and that’s where Interactive Reports Subscription comes into the game.
As you can imagine, such reports may contain confidential data which should not be sent outside of the company (at least not by the reporting application itself).

There are many ways to control the e-mail flows, but in some cases it takes time to implement changes in company global rules. So, let’s start with small changes until the bigger ones can be implemented…

The need is to let the application Administrators see all IR subscriptions, identify the ones with a target e-mail address outside of the company and delete them.

Implementation

Prior to use of subscription, e-mail server must be configured at APEX instance level.

First we need to build the query of the report. To do so, let’s identify the APEX views which can provide the required information about the IR subscription within the APEX_DICTIONARY.
We are looking for information within the application, so view name will start by APEX_APPLICATION_…
The reports are located in application pages, so view name will add as following APEX_APPLICATION_PAGE_…
Within the page we are looking for the Interactive Report APEX_APPLICATION_PAGE_IR_…
And for the Interactive Report, Subscription definition is required: APEX_APPLICATION_PAGE_IR_SUB

The structure of the reports pages in scope is basic and contains only one Interactive Report. The name of the page is needed to identify the report in the list, so the APEX_APPLICATION_PAGES view needs to be joined.
There is only one workspace, so there is no need to identify the workspace, but only the application.
The query looks as following:

SELECT ap.page_id,
ap.page_name,
apis.notify_id,
apis.owner,
apis.email_address,
apis.email_subject,
apis.start_date,
apis.notify_internal,
apis.end_date,
apis.download_format,
apis.created_on,
apis.status
FROM apex_application_pages ap, apex_application_page_ir_sub apis
WHERE ap.application_id = v('APP_ID')
AND ap.application_id = apis.application_id
AND ap.page_id = apis.page_id

The best place to add the report in the application is the standard administration reports provided by APEX and already included in the application:

The report output is an Interactive Report in a Modal Dialog like the other Administration reports. We use field highlighting to identify e-mail addresses outside of company domain …

Let’s check for a blog within the community to see how to add the delete column:
https://vinish.dev/create-delete-row-button-for-interactive-report-in-oracle-apex

The deletion procedure is DELETE_SUBSCRIPTION given by the APEX_IR PL/SQL API package.

And the Confirmation message looks like this:

Conclusion

With some basic addon, based on APEX views and PL/SQL APIs it is easy to provide monitoring and control upon Interactive Report Subscriptions within an application.
I hope this helps you to start with such requirements…
… enjoy APEX development