Search Results so_lines_interface_u1

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

OE.SO_LINES_INTERFACE_ALL is the Order Import interface table in which external or feeder systems stage order line information prior to import into Oracle Order Entry/Shipping. Records inserted into this table are consumed by the Order Import concurrent program, which validates them and creates the corresponding sales order lines in the base order tables. The table exists in both Release 12.1.1 and 12.2.2 with an identical role; the documented physical schema lists 198 columns in the OE schema and stores data in the APPS_TS_INTERFACE tablespace with a PCT Free of 10.

Each row represents a single order line at the interface stage, and the column set mirrors the attributes required to create an order line at order entry or booking. ID and name columns corresponding to required order entry information are conditionally required depending on whether the order import source requires IDs. Model import is supported through PARENT_LINE_REF and LINK_TO_LINE_REF, which carry the ORIGINAL_SYSTEM_LINE_REFERENCE of a top-level model line and of the item directly above the current item in the bill of material, respectively. Included items should not be populated into this interface table. From a Data Vault modeling perspective, the table is classified heuristically as satellite-leaning, reflecting its descriptive, attribute-rich relationship to the order header interface and source system references.

Key Information Stored

The primary key is SO_LINES_INTERFACE_PK, defined on ORIGINAL_SYSTEM_REFERENCE and ORIGINAL_SYSTEM_LINE_REFERENCE. A distinct unique index, SO_LINES_INTERFACE_U1, spans ORIGINAL_SYSTEM_REFERENCE, ORDER_SOURCE_ID, and ORIGINAL_SYSTEM_LINE_REFERENCE, making these three columns the business-key candidate for the interface staging area. The nonunique indexes SO_LINES_INTERFACE_N1 (REQUEST_ID), SO_LINES_INTERFACE_N2 (LINE_NUMBER, ORDER_SOURCE_ID, ORIGINAL_SYSTEM_REFERENCE), and SO_LINES_INTERFACE_N3 (CUSTOMER_PRODUCT_ID) support the concurrent program and inquiry access paths.

• ORIGINAL_SYSTEM_REFERENCE — identifier of the source order header; foreign key to SO_HEADERS_INTERFACE_ALL.
• ORIGINAL_SYSTEM_LINE_REFERENCE — unique source line identifier; corresponds to SO_LINES_ALL.ORIGINAL_SYSTEM_LINE_REFERENCE.
• ORDER_SOURCE_ID and LINE_NUMBER — source and line sequencing attributes used for matching and ordering.
• INVENTORY_ITEM_ID and the INVENTORY_ITEM_SEGMENT1–20 columns — flexible-format key components identifying the ordered item.
• ORDERED_QUANTITY, UNIT_CODE, LIST_PRICE, SELLING_PRICE — core quantity, unit of measure, and pricing attributes.
• ORDER_CATEGORY, LINE_TYPE, TRANSACTION_TYPE_CODE — classify the line and drive processing behavior.
• PARENT_LINE_REF and LINK_TO_LINE_REF — model line hierarchy references used during model import.
• SHIP_TO_SITE_USE_ID, SHIP_TO_CONTACT_ID, WAREHOUSE_ID — shipping and fulfillment destination attributes.
• ERROR_FLAG and INTERFACE_STATUS — record the outcome of Order Import validation and processing.
• REQUEST_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard concurrency and Who columns.

Foreign key relationships include AGREEMENT_ID to SO_AGREEMENTS_B, ACCOUNTING_RULE_ID and INVOICING_RULE_ID to RA_RULES, PRICE_LIST_ID to SO_PRICE_LISTS_B, TERMS_ID to RA_TERMS_B, SHIP_TO_SITE_USE_ID to RA_SITE_USES_ALL, COMMITMENT_ID to PON_OFFER_COMMITMENTS, and CUSTOMER_ITEM_ID to MTL_CUSTOMER_ITEMS.

Common Use Cases and Queries

The predominant use case is programmatic loading of sales order lines from legacy systems, EDI feeds, or third-party order capture applications, followed by execution of the Order Import program. A typical reporting pattern retrieves all pending or errored interface lines for a given concurrent request:

• SELECT ORIGINAL_SYSTEM_REFERENCE, ORIGINAL_SYSTEM_LINE_REFERENCE, LINE_NUMBER, ORDERED_QUANTITY, ERROR_FLAG, INTERFACE_STATUS FROM OE.SO_LINES_INTERFACE_ALL WHERE REQUEST_ID = :p_request_id ORDER BY LINE_NUMBER;
• Correlation of source documents to imported sales orders by joining SO_LINES_INTERFACE_ALL to SO_HEADERS_INTERFACE_ALL on ORIGINAL_SYSTEM_REFERENCE to validate header/line source consistency.
• Pre-import validation queries to detect missing pricing, shipping, or item context by inspecting ORDER_CATEGORY, CALCULATE_PRICE, and INVENTORY_ITEM_ID.
• Error triage queries filtering on ERROR_FLAG to isolate lines rejected by Order Import so they can be corrected and resubmitted.
• Model structure resolution using PARENT_LINE_REF and LINK_TO_LINE_REF to reconstruct bill-of-material hierarchies for top-level models.

These patterns are equally valid on 12.1.1 and 12.2.2 because the interface contract is unchanged between the releases.

Related Objects

The following objects are most significant to working with this table:

• OE.SO_HEADERS_INTERFACE_ALL — parent header interface; joined on ORIGINAL_SYSTEM_REFERENCE.
• OE.SO_LINE_DETAILS_INTERFACE — child detail interface; references this table on ORIGINAL_SYSTEM_REFERENCE.
• OE.SO_LINES_ALL — the resulting permanent order line table; ORIGINAL_SYSTEM_LINE_REFERENCE maps source lines to created lines.
• OE.SO_AGREEMENTS_B, SO_PRICE_LISTS_B, RA_TERMS_B, RA_SITE_USES_ALL, RA_RULES — reference targets for AGREEMENT_ID, PRICE_LIST_ID, TERMS_ID, SHIP_TO_SITE_USE_ID, ACCOUNTING_RULE_ID, and INVOICING_RULE_ID.
• MTL_CUSTOMER_ITEMS and PON_OFFER_COMMITMENTS — reference targets for CUSTOMER_ITEM_ID and COMMITMENT_ID.
• Order Import concurrent program — the process that consumes rows from this interface table and writes to SO_LINES_ALL.