Extending the Oracle Analytics Publisher Data Model

Note:

While extending BI Publisher reports, irrespective of whether the extension is in the data model layer or the layout, Oracle recommends taking a complete backup of the report in another catalog folder and then proceeding with the extension

Query 1: Q_PMDA: This query fetches data from gtt_rpt_expd_xml where the modified XML data is present.

SELECT x.e2b_xml.getClobVal() col from gtt_rpt_expd_xml x where report_id=:PN_REPORT_ID

Query 2: Q_LAB: This query fetches lab data from gtt_lab tables which get populated from the XML.

select CASE is_header WHEN 0 THEN testname||CASE WHEN Testnamellt IS NOT NULL THEN '('||Testnamellt||')' END ELSE testname END testname,
testunit, lowtestrange, hightestrange, testresult, testdate1, testdate2, testdate3
from gtt_rpt_expd_lab_mat
where report_id = :pn_report_id
order by header_seq_num, data_ord_num

Query 3: &Q_FAX_HEAD, Lexical parameter where the query resides in the package.

SELECT VALUE Q_FAX
FROM Cmn_Profile
WHERE KEY = CASE :PN_REPORT_FORM_ID
WHEN 81 THEN 'MKT_FORM_1_2_J'
WHEN 82 THEN 'MKT_FORM_3_4_J'
WHEN 83 THEN 'MKT_FORM_5_6_J'
WHEN 84 THEN 'INV_FORM_1_2_J'
WHEN 85 THEN 'INV_FORM_3_4_J'
WHEN 86 THEN 'INV_FORM_5_6_J'
ELSE ''''
END
AND NVL(:PN_PRT_FAXHEADER, 0) = 1;

Query 4: To print the report title the below function is called in the query.

select pkg_expd_rpt.f_get_pmda_title(:PN_REPORT_FORM_ID) TITLE from dual;

Query 5: &Q_WMARK

SELECT NVL(default_value,'Draft') Q_MARK
FROM cfg_rpt_agg_params
WHERE report_template = (SELECT b.display_value
FROM code_list_detail_discrete a, code_list_detail_discrete b
WHERE a.code_list_id = 'LM_REPORT_FORMS_EXPEDITED'
AND a.decode_context='REPORT_FORM_ID'
AND b.decode_context='BIPREPTYP'
AND a.code_list_id = b.code_list_id
AND a.code = b.code
AND a.display_value = :PN_REPORT_FORM_ID)
AND parameter = 'PN_PRINT_AS'
AND :pn_print_as = 1;

Element	Description
Q_WMARK	Populates water marks for the reports. For example, DRAFT.
Q_FAX_HEAD	Fetches Fax titles from cmn_profile table.

There are no lexical parameters used in PMDA reports.