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:
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 Report Line Listing Package](img/image001.png)
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:
|
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: 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: 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: 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: 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:
- 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:
- 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:
- Global Temporary Tables > Extend Global Temporary Tables
- Populate Data for Generic Line Listing Report
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 Modify Report Line Listing Package](img/image002.png)
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 Add code in package body screen](img/image003.png)
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