Search Results select_from_stage_table

Overview

APPS.EC_OUTBOUND_STAGE is a PL/SQL package body in the Oracle E-Business Suite E-Commerce Gateway (EC) module. It implements the outbound staging engine that extracts transaction data from the ECE_STAGE interface table and writes it to a flat file according to the mapping metadata seeded for a given transaction type. The package header declares it as PACKAGE BODY EC_OUTBOUND_STAGE AS, confirming it is the implementation counterpart to the EC_OUTBOUND_STAGE specification.

The package is the primary program responsible for transforming staged interface rows into the delimited or positional flat files consumed by external trading partners. Comments in the source describe it as "the Main Staging Program" that, for a given transaction and outbound file definition (file name and file path), extracts staging table data into the flat file. Notably, the header documentation states that no data validation is performed at this layer; rows are emitted strictly according to the mapping information configured for the transaction. Mapping metadata is therefore the controlling input, not the package code itself.

The body maintains local PL/SQL table variables declared as STAGE_ID_TYPE and TRANSACTION_LEVEL_TYPE, both indexed by BINARY_INTEGER collections keyed on ECE_STAGE column types. These are used to buffer stage identifiers and transaction levels during extraction, supporting the level-oriented structure of outbound processing.

Key Procedures and Functions

The package exposes six documented program units. Their purposes are as follows:

• GET_DATA — The main staging driver. It accepts a transaction type, target file name and path, map identifier, and run identifier, and orchestrates extraction of staged rows into the flat file. Its internal cursors (c_level_info and c_record_info) resolve interface tables, level matrices, and external levels for the supplied transaction and map.
• FETCH_STAGE_DATA — Retrieves rows from the staging table for the active transaction and map. This is the routine most directly associated with the user search term "fetch_stage_data" and is the row-retrieval workhorse invoked during flat-file generation.
• POPULATE_FLATFILE_MAPPING — Builds the in-memory mapping structure that governs how staged columns are placed into output record positions.
• GET_SELECT_STMT — Constructs the dynamic SQL statement used to query the staging table, reflecting the configured interface columns and record layout.
• SELECT_FROM_STAGE_TABLE — Executes the query against ECE_STAGE and returns the staged data needed by the flat-file writer.
• SELECT_COMMON_KEY — Resolves common key values shared across interface levels, ensuring records at different levels are correlated correctly.

Dynamic SQL is handled through DBMS_SQL, and file output through UTL_FILE. The PLITBLM package is also referenced, consistent with PL/SQL table buffering.

Tables Accessed

Mapping and configuration reads come from ECE_INTERFACE_TABLES, ECE_INTERFACE_COLUMNS, ECE_LEVEL_MATRICES, and ECE_EXTERNAL_LEVELS. These define which interface table supplies each level, which columns map to output positions, and the level hierarchy for the transaction. Transaction data is read from ECE_STAGE, the central staging table holding interface records awaiting outbound extraction. DBMS_SQL and UTL_FILE appear as referenced objects for dynamic query execution and file writing respectively.

Usage Notes

EC_OUTBOUND_STAGE is not intended for direct end-user invocation. It is driven by the E-Commerce Gateway outbound concurrent programs, which supply the transaction type, map, run identifier, and output file path. The package is referenced by one other package in the ETRM metadata, indicating a layered call pattern rather than standalone use. Customizations should avoid modifying this package; instead, extend behavior through mapping and interface table configuration, since the extraction logic is entirely metadata-driven. Both Oracle EBS 12.1.1 and 12.2.2 environments use the same APPS-owned implementation.