Tuesday, August 12, 2014

APEX Dictionary - Comparing two Interactive Reports

Recently I again had the requirement to compare two Interactive Reports from different applications.
Using the power of the APEX Dictionary this can be done quite easily.

WITH prod AS
(
SELECT column_alias, application_id, report_label, help_text
  FROM apex_application_page_ir_col
 WHERE page_id = :page_id_prod
   AND application_id = :app_id_prod
), dev AS
(
SELECT column_alias, application_id, report_label, help_text
  FROM apex_application_page_ir_col
 WHERE page_id = :page_id_dev
   AND application_id = :app_id_dev
)
SELECT NVL(prod.column_alias, dev.column_alias) column_alias,
       prod.report_label prod_label,
       dev.report_label dev_label,
       CASE WHEN prod.column_alias IS NULL THEN 'Only DEV'
            WHEN dev.column_alias IS NULL THEN 'Only PROD'
            WHEN prod.report_label != dev.report_label 'Differs'
            ELSE NULL
       END status
  FROM prod FULL OUTER JOIN dev ON (prod.column_alias = dev.column_alias)
ORDER BY NVL(prod.column_alias, dev.column_alias)
;
I used "prod" and "dev" as identifiers for the different settings.
This time I was mainly looking for the report labels, but you can easily extend the query to include additional fields from the APEX_APPLICATION_PAGE_IR_COL view.

This has been in my stash for a while but I didn't publish it before.
Oliver Lemm just started a series of APEX Metadata related posts and made me think let's share this snippet.

May the APEX Dictionary be with you!

No comments:

Post a Comment