Search Results insert_into_interface_tbl

Overview

APPS.ECE_EXTRACT_UTILS_PUB is a public PL/SQL package that forms part of the Oracle E-Business Suite e-Commerce Gateway (ECE) extraction infrastructure. Its principal business function is to generate the dynamic SQL and populate the interface tables required when ECE extracts outbound transaction data from EBS base tables and transforms it into flat-file or interface-table format for external trading partners. The package operates alongside the private package ECE_FLATFILE_PVT, which owns the interface_tbl_type and source_tbl_type collection definitions that appear throughout the signatures of ECE_EXTRACT_UTILS_PUB. In release 12.1.1 and 12.2.2 the package is shipped with the header revision ECPEXTUS.pls 120.2, and it remains an intrinsic building block of the extract path rather than a user-facing API. It is a PUBLIC (PUB) classified API, meaning it is callable from custom code, though its intended consumers are the standard ECE concurrent programs and the extraction engine itself.

Key Procedures and Functions

The documented API exposes eight procedures and functions:

• SELECT_CLAUSE — Constructs the SELECT, FROM, and WHERE fragments used to query the source transaction data. It accepts a transaction type, communication method, interface table name, and a source table collection, and returns the three SQL fragments as OUT NOCOPY parameters.
• INSERT_INTO_INTERFACE_TBL — Populates the ECE interface (staging) table with rows built from a source collection. It takes a run identifier, transaction type, communication method, interface table name, source collection, and a foreign key value used to relate staged rows to the extraction run.
• INSERT_INTO_PROD_INTERFACE — The procedure specifically associated with the search term insert_into_prod_interface. It inserts staged/interface data into the production interface tables, accepting the interface table name, an IN OUT NOCOPY cursor or statement identifier, and the APPS table collection. An accompanying private helper, INSERT_INTO_PROD_INTERFACE_PVT, performs the underlying work; bug fix 1854866 added default values for its p_init_msg_list, p_simulate, p_commit, and p_validation_level parameters.
• FIND_POS — Locates the positional index of a supplied element within a collection, supporting column-name resolution during dynamic SQL assembly.
• POS_OF — Returns the ordinal position of a token or value; used together with FIND_POS for mapping source columns to target columns.
• EXT_GET_VALUE — Retrieves an extracted value from the working source record structure, honouring the record layout defined by source_rec_type (data location, table/column names, cross-reference keys, data type, and up to five external values).
• EXT_INSERT_VALUE — Assigns or inserts a value into the extraction record structure, the counterpart to EXT_GET_VALUE.

The package declares a constant g_maxcolwidth of 400, which enforces the maximum width of an extracted column, and references the TRANSACTION_DATE column by name during date handling.

Tables Accessed

The package references the following objects through APPS synonyms:

• ECE_ERROR and ECE_ERROR_S — the error logging and sequence objects used to record validation or extraction failures raised while building and executing the generated SQL.
• DBMS_SQL — the Oracle-supplied dynamic SQL package, invoked because the extract builds and executes SQL statements at runtime from metadata rather than from static code.
• PLITBLM — the PL/SQL table (index-by table) built-in package used for collection handling, consistent with the INDEX BY BINARY_INTEGER source table type declared in the specification.

Usage Notes

ECE_EXTRACT_UTILS_PUB is invoked indirectly through the e-Commerce Gateway outbound extract concurrent programs; it is not intended to be launched directly from a form. When an ECE extract program runs, it calls SELECT_CLAUSE to build the extraction SQL, INSERT_INTO_INTERFACE_TBL to stage rows, and INSERT_INTO_PROD_INTERFACE (with its private helper) to move data into the production interface tables before the flat-file writer creates the output document. Custom integrations that need to reuse ECE extraction logic may call the PUB procedures, but developers should note that the package depends on collection types defined in ECE_FLATFILE_PVT. The package is referenced by nine other packages, confirming its role as a shared utility. Because it relies on dynamic SQL and the ECE_ERROR tables, any custom invocation should preserve standard error handling and supply the expected run identifier and foreign key so that staging rows remain tied to a valid extraction run.