Search Results sequence_audit_log

Overview

QA_SEQUENCE_API is a public PL/SQL package owned by the APPS schema that provides Oracle Quality with controlled, gapless generation of sequence numbers for quality transactions, collection plans, and related result records. It was introduced under the internal "Gapless Sequence Project" to ensure that sequence values assigned to Quality transactions are issued without gaps, a requirement for regulated manufacturing and laboratory environments where traceability of every generated transaction identifier must be preserved. The package is classified as an API, meaning it is intended for programmatic invocation by other Oracle E-Business Suite modules rather than being a private implementation detail. Its source header (qltseqs.pls 120.3) indicates it has been in production since at least 2002–2003 and continues to serve the 12.1.1 and 12.2.2 releases unchanged.

Key Procedures and Functions

The package exposes sixteen documented program units. The core sequence primitives are GET_NEXTVAL and GET_NEXTVAL_NOCOMMIT, which return the next numeric sequence value for a given character identifier; the latter variant does not commit the underlying sequence update, allowing callers to control transaction boundaries. GET_NEXT_SEQ returns the next sequence value as a VARCHAR2 and accepts an optional commit flag. MAX_SEQUENCE returns the highest sequence value currently allocated. GET_SEQUENCE_DEFAULT_VALUE returns the translated display value for the "Automatic" sequence setting, used when inserting background results.

Batch and administrative utilities include FILL_SEQ_TABLE, which pre-populates a caller-supplied table of sequence values for a given identifier and count, and the overloaded AUDIT_SEQUENCE_VALUES procedures, which record sequence audit information for plans, collections, and occurrences. DELETE_AUDITINFO_FOR_TXN and DELETE_AUDITINFO_FOR_DDE remove audit history when a transaction or DDE record is purged. SEQUENCE_AUDIT_LOG writes audit entries to the sequence audit history.

Transaction-level entry points include GENERATE_SEQ_FOR_TXN, GENERATE_SEQ_FOR_TXNINTEG, and the overloaded GENERATE_SEQ_FOR_DDE procedures, which assign sequence numbers to quality transactions, transaction integrations, and DDE (data-driven events) records respectively, returning a status indicator (and, in one overload, a message).

Tables Accessed

The package operates against Quality application tables accessed through APPS synonyms. QA_CHARS and QA_PLAN_CHARS define the sequence character identifiers for which numbers are generated. QA_PLANS, QA_RESULTS, and QA_PC_RESULTS_RELATIONSHIP store the plan, result, and relationship records that consume the generated sequences. QA_SEQ_AUDIT_HISTORY records audit trails written by the audit procedures. DBMS_SQL and PLITBLM are Oracle-supplied and PL/SQL internal objects used for dynamic SQL and index-by table handling. Referenced tables are read to resolve sequence definitions and written to persist generated numbers and audit history.

Usage Notes

QA_SEQUENCE_API is invoked by other Oracle Quality packages (it is referenced by seven other packages) rather than directly by end users. Typical invocation paths include Oracle Quality forms when a user saves a collection, result, or transaction, and concurrent programs that process or integrate quality transactions in batch, such as the transaction integration and DDE generation routines. Custom code extending Oracle Quality should call the public functions and procedures documented here rather than manipulating the underlying sequence tables directly, to preserve gapless numbering and audit integrity. Because several functions accept an explicit commit flag or return status out-parameters, callers must honor the returned status and manage their own transaction boundaries where the non-committing variants are used.