Generic Package
Oracle Business Intelligence Publisher report has pkg_rep_generic as the generic package that will be used to create/modify all future Oracle Business Intelligence Publisher reports.
This package performs the following functions:
- User Context is set, so that the user can view data only as per user data access rights.
- Global table rep_case_detail_tmp is populated with cases after applying data security.
- Log tables population logic is created within the generic package.
This package contains following procedures/functions:
Table 5-1 Generic Package - Procedures and Functions
S.No. | Procedure/Function Name | Parameter/ Argument Used | Description |
---|---|---|---|
1. |
p_set_user_context |
|
This procedure is used to set user context (for multi-tenancy) and data security variables. Using the package pkg_rls.set_context, user context will be set, by passing enterprise ID, user name and application name to the package. |
2. |
p_pop_case_detail |
|
This procedure populates case series in global table rep_case_detail_tmp, used in BI Publisher reports.
For p_querytype = C, cases are inserted in global table rep_case_detail_tmp. from the table case_detail.
For p_querytype IN ('Q', 'F', 'A'), the global table rep_case_detail_tmp gets populated in the procedure p_caseseries_from_query. |
3. |
p_rep_execution_log |
|
This procedure is used to log status of table population and SQL exceptions in table rep_execution_log.
Routine Call: PKG_REP_GENERIC.P_REP_EXECUTION_LOG (NULL, 'p_pop_case_tmp', 'Data population for table REP_CASE_TMP started.');
Before populating the table rep_case_tmp, this procedure logs a message that 'data population for table <rep_case_tmp> started'. After successful completion of the process, it logs a message that 'data population for table <rep_case_tmp> completed'.
Besides, in each population routine section in the SQL exceptions; this procedure is called to log SQL error messages.
See Also: |
4. |
p_rep_sql_log |
|
This procedure logs dynamic SQL queries created in the generic package. The following SQL statements are logged in this package:
For example: pkg_rep_generic.p_rep_sql_log (pi_module_name, lvc_sql); --lvc_sql Once report is executed, you can copy the query from column sql_text of the table rep_sql_log where all queries exist. Execute the desired query in the database.
Example Routine Call: pkg_rep_generic.p_rep_sql_log ('p_caseseries_from_query', lclb_sql); where lclb_sql := 'INSERT INTO rep_case_detail_tmp (case_id) ' || lclb_rpt_sql; Besides, lclb_rpt_sql > sql_for_report column value from the table cfg_adv_cond. |
5. |
p_keep_report_data |
|
This procedure maintains session data in the report log tables. It is called in the report specific package pkg_rep_linelisting. For example: PKG_REP_GENERIC.P_KEEP_REPORT_DATA ('p_pop_case_tmp', 'REP_CASE_TMP', 'REP_CASE_LOG'); In the above example, if the profile switch KEEP_REPORT_DATA value is yes, then the table rep_case_log will be populated with the session data rep_case_tmp. See Also: Log Audit Tables, explained later in this chapter |
6. |
f_get_insert_sql |
|
This internal function generates dynamic SQL to insert data from the report GTT into the report log tables. It also returns the generated SQL. Example Routine Call: pkg_rep_generic.f_get_insert_sql (pi_src_table, pi_tgt_table The data from source table is inserted into the target table. |
7. |
p_caseseries_from_query |
|
This procedure inserts cases into the table rep_case_detail_tmp, when the Query/Case parameter is passed a value as Q/F:
This procedure is called in the procedure p_pop_case_detail to populate cases for Query or Filters. |
8. |
f_get_query_details |
|
This function populates the Case Series/Query/Advanced Condition/Filter Name as per the user access rights. The parameter pi_id for Case/Query Name prompt, populates with the Case/Query/AC/Filter names based on the selected Enterprise ID. And parameter pi_querytype for Case Series/Query prompt, populates as per the logged-in user. |
Context Setting
The context settings for multi tenancy are described in this section.
The procedure p_set_user_context, sets enterprise, user name (username), and application name (app_name) context for Oracle Virtual Private Database policy (VPD).
See Also:
Oracle Technical Reference documents for more information on Oracle VPD.
Case Series Data Population
The cases in the table rep_case_detail_tmp are populated as follows:
- For Case Series/Query Type - C: Cases from the table case_detail are populated.
- For Case Series/Query Type - Q or A: Execute the SQL command on the column sql_for_report from the table cfg_adv_cond.
- For Case Series/Query Type - F: Execute the SQL command on the column sql_for_report from the table cfg_adv_cond and also join another table filter_valuesets.
Parent topic: Report Package Features