Generic Package

Oracle Analytics Publisher report has pkg_rep_generic as the generic package that will be used to create/modify all future Oracle Analytics 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	pi_enterprise_id: Enterprise ID pi_user_name: Report User Name (the user who has logged in to Publisher)	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	pi_querytype: Q - Query, A-Advance Condition, F - Filter, and C - Case Series pi_id: CASESERIES_ID/QUERY_ID/AC_ID/Filter_ID to get data for cases	This procedure populates case series in global table rep_case_detail_tmp, used in 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	pi_ora_err_desc: Oracle-defined error code and description pi_table_name: Table/Module name pi_description: User-defined descriptive error message	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: Populate Data for Generic Line Listing Report
4.	p_rep_sql_log	pi_module_name: identifier to various calling modules pi_sql_text: Dynamic SQL created	This procedure logs dynamic SQL queries created in the generic package. The following SQL statements are logged in this package: Insert statements in the table rep_case_detail_tmp. Update study_unblind_ok, code_broken statement in the table rep_case_detail_tmp. Insert statements in the report log tables. 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	pi_module_name: Calling module name pi_src_table: Source table name pi_tgt_table: Target table name	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	pi_src_table: Source table name pi_tgt_table: Target table name pi_append_flag: Append hint	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	pi_ac_id: Query ID to get SQLs for case detail and blinded security pi_querytype: Q - Query, and F - Filter	This procedure inserts cases into the table rep_case_detail_tmp, when the Query/Case parameter is passed a value as Q/F: For Query type - Q, the SQL query is fetched from the table cfg_adv_cond. For Query type - F, the SQL query is fetched from the table filter_valuesets. This procedure is called in the procedure p_pop_case_detail to populate cases for Query or Filters.
8.	f_get_query_details	xdo_user_name: Report User Name (the user who has logged in the Publisher) pi_enterprise_id: Enterprise ID pi_querytype: C - Case Series, Q - QBE, A - Advanced Condition, or F- Filter	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).