Search Results copy_questions

Overview

QA_AUDIT_PKG is a PL/SQL package body owned by APPS within the Oracle E-Business Suite Quality Management (QA) module. Its primary business function is to support the audit copy capability introduced with the Audits Copy UI project (referenced in the source as Bug 4345779). The package provides the low-level plumbing required to duplicate audit question bank, master, and collection plan structures, including the assignment of new surrogate identifiers and the dynamic construction of INSERT statements used to persist audit-related result rows.

The package maintains three PL/SQL global tables — g_qb_result_columns, g_master_result_columns, and g_que_result_columns — that cache the mapping between a plan character ID and its result column name. These globals are populated by init_globals for a question bank plan, an audit master plan, and an audit question plan respectively, so that downstream routines can resolve result columns without repeated queries against QA_PLAN_CHARS.

Key Procedures and Functions

• init_globals — Initializes the three global PL/SQL tables by deleting any prior contents and re-populating them from the QA_PLAN_CHARS cursor for the supplied bank, master, and question plan identifiers. This establishes the result-column mapping used by the rest of the package.
• get_collection_id — The function the user searched for. It returns a new QA_COLLECTION_ID by selecting the next value from the QA_COLLECTION_ID_S sequence via an explicit cursor on DUAL. This provides surrogate primary keys for copied audit collection records.
• get_txn_header_id — Returns a new transaction header identifier by fetching MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL from DUAL, allowing audit copy operations to associate generated material transaction headers.
• common_insert_sql — Builds a dynamic SQL string (up to 1000 characters) used to construct INSERT statements for audit result rows, leveraging the DBMS_SQL package and the global column-mapping tables.
• COPY_QUESTIONS — The single documented API entry point. It orchestrates the copy of audit questions, invoking the supporting functions above to assign new collection IDs and persist duplicated question data.

Tables Accessed

• QA_PLAN_CHARS — Read by init_globals to build the CHAR_ID to result-column-name mapping for each of the three audit plan types.
• QA_COLLECTION_ID_S — Sequence source for get_collection_id, generating surrogate collection identifiers.
• MTL_MATERIAL_TRANSACTIONS_S — Sequence source for get_txn_header_id.
• QA_PC_PLAN_RELATIONSHIP — Referenced to resolve plan-to-collection relationships during copy processing.
• QA_RESULTS — Target table for rows inserted through the dynamically generated SQL produced by common_insert_sql.
• DUAL — Used by the sequence-fetch cursors.
• DBMS_SQL / PLITBLM — Oracle-supplied packages supporting dynamic SQL execution and PL/SQL table operations.

Usage Notes

QA_AUDIT_PKG is an internal helper package rather than a public business API. It is invoked through the Audits Copy user interface in the Quality Management module when users duplicate an existing audit collection, master, or question bank. The package is referenced by one other package in the EBS codebase, indicating it is consumed indirectly rather than called directly by forms or concurrent programs.

Because the header dates to 2005 (version 120.1) and is marked noship, the package is a long-standing component compatible with both EBS 12.1.1 and 12.2.2. Customizations should not call get_collection_id or get_txn_header_id outside the audit copy flow, since the sequence values they return are intended for the package's own insert logic. Any extension of the copy functionality should preserve the global-table initialization order established by init_globals.