1. Extensibility Guide
  2. Report Extensibility
  3. Oracle Business Intelligence Publisher Extensibility
  4. Report Package Features
  5. Line Listing Package

Line Listing Package

The Oracle Business Intelligence Publisher report has pkg_rep_linelisting as a Generic Line Listing Report specific package. In this package the report GTTs are populated.

See Also:

Global Temporary Tables

Generic Parameters

For generic parameters, it is mandatory to declare these parameters in the package that are used in the Oracle Business Intelligence Publisher report. Henceforth, if any new parameter is required to be included in the report then it (new parameter) must be declared in the report specific package.

See Also:

Report Parameters for more information about the parameter variables usage in data model.

The following report parameters are declared in the report package pkg_rep_linelisting:

Table 5-2 Report Parameters

S.No. Parameter Name Mandatory/ Optional Description

1.

pi_enterprise_id: Enterprise ID

Mandatory

A user specific Enterprise ID is passed from Oracle Business Intelligence Publisher to the package, where Enterprise ID is fetched from the table cfg_user_enterprise_apps.

2.

pi_querytype: Case Series or Query

Mandatory

A Case Series (C), Query/QBE (Q), Advanced Condition (A) or Filter (F) is passed from Oracle Business Intelligence Publisher based on the user selection.

3.

pi_id: CASESERIES_ID/QUERY_ID/AC_ID/Filter_ID to get data for cases

Mandatory

A user specific case series ID, query ID or filter ID is passed to the package based on the user selection. But in the report, Case series or Query Name is displayed for the enterprise ID and query type selected.

4.

pi_category_name: Category Name

Optional

This is an optional free text parameter, where a user can enter report category name.

5.

pi_rpt_sub_title: Report Sub-heading

Optional

This is an optional free text parameter, where report sub-title is entered.

6.

pi_rpt_title: Report Name

Optional

This is an optional free text parameter, where report name is entered.

7.

xdo_user_name

Optional

A Oracle Business Intelligence Publisher login user name is passed to this parameter. This is BI Publisher system parameter.

See Also:

BI Publisher Technical Reference document.

Add New Parameter in Package

This section is explained with the help of an example. Let us say, you want to add a new parameter pi_case and restrict the data model based on the Case ID input. To do so, declare the new parameter in the package as shown below:

Report Line Listing Package

See Also:

Report Parameters > Add New Parameter in Data Model

Populate Data for Generic Line Listing Report

The list of routines/functions that are used to populate data for the Generic Line Listing Report is as follows:

Table 5-3 List of Routine/Function used for Generic Line Listing Report Data

S.No. Routine/Function Name Parameter Used Description

1.

f_pop_report_data

pi_enterprise_id,

xdo_user_name,

pi_id,

pi_querytype

See Also:

Report Parameters

Generic Parameters

In this function, the following procedures are called in the same order as listed:

  1. To set user context call the procedure as: pkg_rep_generic.p_set_user_context (pi_enterprise_id, xdo_user_name);
  2. To populate the cases in GTT rep_case_detail_tmp after applying user security, call the routine as: pkg_rep_generic.p_pop_case_detail (pi_id,pi_querytype);
  3. p_pop_case_tmp - This routine is explained later in the table.
  4. p_pop_event_tmp - This routine is explained later in the table.
  5. p_pop_prod_dose_tmp - This routine is explained later in the table.
  6. p_pop_evt_assess_tmp - This routine is explained later in the table.

2.

p_pop_case_tmp

Not applicable

This Procedure populates data in the GTT rep_case_tmp. Before inserting data in the table rep_case_tmp, log table rep_execution_log is populated with the message as:

PKG_REP_GENERIC.P_REP_EXECUTION_LOG (NULL, 'p_pop_case_tmp', 'Data population for table REP_CASE_TMP started.');

See Also:

Generic Parameters

Once the processing is completed for all the rows in the table rep_case_tmp, log the completion details as:

PKG_REP_GENERIC.P_REP_EXECUTION_LOG (NULL, 'p_pop_case_tmp', 'Data population for table REP_CASE_TMP completed successfully. '|| SQL%ROWCOUNT || ' row(s) processed.')

Calling User Exit procedure:

You can write your own logic to update case data in the User Exit procedure PKG_REP_LINELISTING_USER_EXIT.P_MODIFY_CASE_TMP;

Any exception/errors while populating the table rep_case_tmp are handled in WHEN OTHERS exception as:

pkg_rep_generic.p_rep_execution_log (SUBSTR (SQLERRM, 1, 300), 'p_pop_case_tmp', 'Error during data population for table REP_CASE_TMP.')

3.

p_pop_event_tmp

Not applicable

This procedure populates data in the GTT rep_event_tmp.

Before inserting data in the table rep_event_tmp, log table rep_execution_log is populated with the message as:

PKG_REP_GENERIC.P_REP_EXECUTION_LOG (NULL, 'p_pop_event_tmp', 'Data population for table REP_EVENT_TMP started.');

See Also:

Generic Parameters

Once the processing is completed for all the rows in the table rep_event_tmp, log the completion details as:

PKG_REP_GENERIC.P_REP_EXECUTION_LOG (NULL, 'p_pop_event_tmp', 'Data population for table REP_EVENT_TMP completed successfully. ' || SQL%ROWCOUNT || ' row(s) processed.');

Calling User Exit procedure:

You can write your own logic to update the event data in the User Exit procedure:

PKG_REP_LINELISTING_USER_EXIT.P_MODIFY_EVENT_TMP;

Any exception/errors while populating the table rep_event_tmp are handled in WHEN OTHERS exception as

pkg_rep_generic.p_rep_execution_log (SUBSTR (SQLERRM, 1, 300), 'p_pop_event_tmp', 'Error during data population for table REP_EVENT_TMP.')

4.

p_pop_prod_dose_tmp

Not applicable

This procedure populates data in the GTT rep_prod_dose_tmp.

Before inserting data in the table rep_prod_dose_tmp, log table rep_execution_log is populated with the message as: PKG_REP_GENERIC.P_REP_EXECUTION_LOG (NULL, 'p_pop_prod_dose_tmp', 'Data population for table REP_PROD_DOSE_TMP started.');

See Also:

Generic Parameters

Once the processing is completed for all the rows in the table rep_prod_dose_tmp, log the completion details as:

PKG_REP_GENERIC.P_REP_EXECUTION_LOG (NULL, 'p_pop_prod_dose_tmp', 'Data population for table REP_PROD_DOSE_TMP completed successfully. ' || SQL%ROWCOUNT || ' row(s) processed.');

Calling User Exit procedure:

You can write your own logic to update the product related data in the User Exit procedure:

PKG_REP_LINELISTING_USER_EXIT.P_MODIFY_PROD_DOSE_TMP;

Any exception/errors while populating the table rep_prod_dose_tmp are handled in WHEN OTHERS exception as:

pkg_rep_generic.p_rep_execution_log (SUBSTR (SQLERRM, 1, 300), 'p_pop_prod_dose_tmp', 'Error during data population for table REP_PROD_DOSE_TMP.')

5.

p_pop_evt_assess_tmp

Not applicable

This procedure populates data in the GTT rep_evt_assess_tmp.

Before inserting data in the table rep_evt_assess_tmp, log table rep_execution_log is populated with the message as:

PKG_REP_GENERIC.P_REP_EXECUTION_LOG (NULL, 'p_pop_evt_assess_tmp', 'Data population for table REP_EVT_ASSESS_TMP started.');

See Also:

Generic Parameters

Once the processing is completed for all the rows in the table rep_evt_assess_tmp, log the completion details as:

PKG_REP_GENERIC.P_REP_EXECUTION_LOG (NULL, 'p_pop_evt_assess_tmp', 'Data population for table REP_EVT_ASSESS_TMP completed successfully. ' || SQL%ROWCOUNT || ' row(s) processed.');

Calling User Exit procedure:

You can write your own logic to update the event assessment data in the User Exit procedure: PKG_REP_LINELISTING_USER_EXIT.P_MODIFY_EVT_ASSESS_TMP;

Any exception/errors while populating the table rep_evt_assess_tmp are handled in WHEN OTHERS exception as:

pkg_rep_generic.p_rep_execution_log (SUBSTR (SQLERRM, 1, 300), 'p_pop_evt_assess_tmp', 'Error during data population for table REP_EVT_ASSESS_TMP.');

Any error exception in the function f_pop_report_data, is handled with message as:

pkg_rep_generic.p_rep_execution_log (SUBSTR (SQLERRM, 1, 300), 'f_pop_report_data', 'Error during execution of f_pop_report_data for ENTERPRISE ID - ' || pi_enterprise_id || ', USER NAME - ' || xdo_user_name || '.')

Log (Audit) Table

The log tables are divided into three categories as follows:

  • Session Details - There are four report log tables to hold the session data, namely:
    • rep_case_log
    • rep_prod_dose_log
    • rep_event_log
    • rep_evt_assess_log

    These tables are populated only if the BI Publisher profile switch KEEP_REPORT_DATA is 'Y' that is, populate the report log tables. By default it is set as 'N' that is, do not populate the report log tables. This is an enterprise specific switch.

    The profile switch are available in the Argus Insight List Maintenance section, where you can set it to 'Y' or 'N'.

    See Also:

    Admin Guide > <section - TBD> for the profile switch information.

    The procedure p_keep_report_data, in generic package is used to populate data for the Report Log tables.

    See Also:

    Generic Package

  • Process Details - The log table rep_execution_log, records the entire report table process details. At each temporary table population procedures the log table will be populated. In all exceptions, this log table is populated with Oracle SQL errors.

    See Also:

    Generic Package

  • Dynamic SQL Details - The log table rep_sql_log, is populated with the dynamic SQLs generated in the generic package, only if the database profile switch LOG_REPORT_SQL value is '1' that is, yes. This is a global switch to identify, if report SQL is to be logged or not. The default value of this switch is '0' that is, no.

    This database switch is not available in the Argus Insight UI List maintenance section. It is required to be set in the database only.

    See Also:

User Exits

A User Exit is a package, which provides a way to pass control from reports specific package to a User Exit package that performs some function (more appropriately data manipulation function), and then return control to main report specific package.

User Exit is used for data manipulations that need extended procedural capabilities.

In section Populate Data for Generic Line Listing Report, under each report table population, corresponding User Exit tables are mentioned.

See Also:

Lexical Parameters

A Lexical Parameter is a placeholder column containing the actual text to be used in a query. At runtime report query can be modified using lexical parameters.

Modify the Report Package specification to add Lexical Parameters as shown below:

Modify Report Line Listing Package

In the above figure, two Lexical Parameters pi_orderby and gl_orderby are added to the Report Package.

pi_orderby is the parameter in the Data Model based on the value selected in this parameter, the parameter gl_orderby will be selected.

Now, add code in the Report Package body that is, in the function f_pop_report_data, the parameter pi_orderby is included as shown below:

Add code in package body screen

Once the package is compiled without any errors, refer to Add Lexical Parameter in Data Model, to add the lexical parameters in the BI Publisher.

Parent topic: Report Package Features