Search Results select_common_key

Overview

EC_OUTBOUND_STAGE is an Oracle E-Business Suite PL/SQL package owned by APPS that supports the E-Commerce Gateway (EC) outbound processing architecture. Its primary business function is to stage outbound transaction data extracted from ECE staging tables into a flat-file structure, providing the intermediate layer between the ECE interface definitions and the physical outbound data files delivered to trading partners. The package is classified as OTHER in the ETRM documentation, indicating it is an internal supporting package rather than one of the public ECE APIs.

The package depends heavily on ECE interface metadata to drive its behavior dynamically. It reads the interface column definitions, interface table registrations, and level matrices that describe how a particular ECE transaction (such as an outbound purchase order, invoice, or ASN) is organized into hierarchical levels. The record structure declared in the package specification, start_rec, encapsulates the information specific to a level: the start record number, total records, Document_Id, Line_Number, Parent_Stage_Id, Select_Cursor, Transaction_type, Run_Id, Document_Number, Status, Stage_Id, key column name, key column position, key column staging, and primary address type. This structure reveals the central role of the Parent_Stage_Id column in tracking hierarchical relationships between staged records.

Key Procedures and Functions

The package exposes six documented procedures and functions:

• GET_DATA — Retrieves outbound data from the ECE staging tables based on the interface metadata definition, serving as the primary data acquisition routine.
• FETCH_STAGE_DATA — Fetches rows from the staging table and populates the internal record structures used by the flat-file generation logic.
• POPULATE_FLATFILE_MAPPING — Builds the mapping between staged data columns and their positions in the generated flat-file output, using record number, line length, and filler constants such as g_rec_num_ln and g_rec_ckey_ln.
• GET_SELECT_STMT — Constructs the dynamic SQL SELECT statement used to read from the staging tables, driven by interface column definitions.
• SELECT_FROM_STAGE_TABLE — Executes selection against the stage table, typically via dynamic SQL (DBMS_SQL), to retrieve records for a given document and level.
• SELECT_COMMON_KEY — Extracts and returns the common key value that uniquely identifies a document across hierarchy levels, using the configurable g_ref_ckey_ln (22) and g_tp_ckey_ln (25) length parameters.

Tables Accessed

The package operates against several ECE configuration and staging tables through APPS synonyms:

• ECE_INTERFACE_TABLES — Provides transaction type, key column name, and table registration for each interface.
• ECE_INTERFACE_COLUMNS — Supplies record numbers and staging column definitions used in mapping.
• ECE_EXTERNAL_LEVELS and ECE_LEVEL_MATRICES — Define the hierarchical level structure that governs how records relate via Parent_Stage_Id.
• ECE_STAGE — The physical staging table from which outbound data is selected.
• UTL_FILE and DBMS_SQL — Oracle-supplied packages used for file I/O and dynamic SQL parsing/execution.
• PLITBLM — Oracle's PL/SQL integer table package used for internal collection handling.

Usage Notes

EC_OUTBOUND_STAGE is invoked during outbound ECE processing, typically from concurrent programs such as the Outbound Transaction Manager or the Extract/Flat File generation programs. It is referenced by one other package in the ECE outbound flow, which drives it by passing the run ID, document ID, and interface context. Developers extending ECE outbound transactions may call the documented procedures directly to customize flat-file layouts, but should be aware that the global variable declarations in the package header (counter, i_document_number, u_file_handle, and the key length constants) are shared session state and can be affected by concurrent execution. The Parent_Stage_Id field referenced in the user's search appears within the start_rec record type and is used to link child-level staged records to their parent document record during hierarchical assembly.