Search Results ece_spso_items_u1

Overview

EC.ECE_SPSO_ITEMS is an e-Commerce Gateway interface table that stages supplier schedule item data used to generate the outbound Planning Schedule (830/DELFOR) and Shipping Schedule (862/DELJIT) transactions. It is populated primarily from data held in CHV_SCHEDULE_ITEMS within Oracle Supplier Scheduling, together with scheduling options and ship-to organization defaults sourced from CHV_ORG_OPTIONS. Rows originate from confirmed planning or shipping schedules produced through the Oracle Supplier Scheduler Workbench, the Oracle Supplier Scheduling AutoSchedule process, or schedules explicitly selected by the e-Commerce Gateway user.

The table is shared by two transaction types, so a RUN_ID column uniquely identifies the extraction session that created each row. A one-to-many relationship exists between ECE_SPSO_HEADERS and this table, and each item row carries detail associated with the header. Rows are purged once the data has been written to the outbound data file, making this a transient staging object rather than a permanent ledger. Under the heuristic Data Vault classification derived from its foreign-key structure, the object is characterized as standalone; in a Data Vault model it is best treated as a satellite-like staging entity attached to the schedule header hub, with each extraction run acting as a load unit.

Key Information Stored

The documented physical schema comprises 145 columns, stored in the APPS_TS_INTERFACE tablespace. The most operationally significant columns are:

• SCHEDULE_ITEM_ID and SCHEDULE_ID — item-level and header-level identifiers that tie each row back to its parent schedule.
• RUN_ID — identifies the extraction session; because the table is shared by the 830 and 862 transactions, this column is essential for isolating a single run.
• TRANSACTION_RECORD_ID — the unique key linking the row to its corresponding extension table, ECE_SPSO_ITEMS_X.
• ITEM_NUMBER and PRODUCT_DESCRIPTION — the item being scheduled and its description.
• PURCHASING_UOM_CODE and its external counterpart PURCHASING_UOM_CODE_EXT — purchasing unit of measure.
• STARTING_AUTH_QUANTITY, STARTING_CUM_QUANTITY, and the primary-quantity variants STARTING_AUTH_QTY_PRIMARY and STARTING_CUM_QTY_PRIMARY — opening authorization and cumulative quantities.
• LAST_RECEIPT_DATE, LAST_RECEIPT_QUANTITY, LAST_RECEIPT_CUM_QTY, and LAST_RECEIPT_SHIPMENT_CODE — prior receipt/shipment history.
• SUPPLIER_PRODUCT_NUMBER, SUPPLIER_AGREEMENT_NUMBER, SUPPLIER_AGREEMENT_TYPE, and SUPPLIER_AGREEMENT_LINE_NUMBER — sourcing agreement references.
• SHIP_TO_ORG_CODE, SHIP_TO_ORG_NAME, and the full ship-to address block (address lines, city, region, country, postal code) — ship-to organization defaults.
• Buyer, planner, and scheduler contact columns such as BUYER_LAST_NAME, ITEM_PLANNER_LAST_NAME, and ITEM_SCHEDULER_LAST_NAME.
• Descriptive attribute groups including SCH_ITEM_ATTRIBUTE1–15, ITEM_ATTRIBUTE1–15, ASL_ATTRIBUTE1–15, ORG_OPT_ATTRIBUTE1–15, and SHIP_TO_ORG_ATTRIBUTE1–15.
• Audit columns including CREATION_DATE, LAST_UPDATE_DATE, REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID.

The surrogate primary key is ECE_SPSO_ITEMS_PK on (SCHEDULE_ITEM_ID, SCHEDULE_ID, RUN_ID). A separate unique index, ECE_SPSO_ITEMS_U1, is defined on the same three columns and serves as the business-key candidate for the object. Index ECE_SPSO_ITEMS_N1 provides non-unique access.

Common Use Cases and Queries

Typical usage centers on validating and troubleshooting outbound schedule transmissions.

• Reviewing a single extraction run prior to purge: SELECT SCHEDULE_ID, SCHEDULE_ITEM_ID, ITEM_NUMBER, STARTING_CUM_QUANTITY FROM EC.ECE_SPSO_ITEMS WHERE RUN_ID = :run_id;
• Confirming that code conversion produced valid external values by comparing internal and external columns, such as PURCHASING_UOM_CODE versus PURCHASING_UOM_CODE_EXT.
• Reconciling header and item counts across an extraction to verify completeness before file generation.
• Joining to ECE_SPSO_ITEMS_X on TRANSACTION_RECORD_ID to retrieve extension attribute values.
• Reporting on supplier agreement coverage and ship-to defaults for a batch of scheduled items.
• Diagnosing missing items in an 830 or 862 file by comparing ECE_SPSO_ITEMS against the source CHV_SCHEDULE_ITEMS records.

Related Objects

• EC.ECE_SPSO_HEADERS — parent header table; joined on SCHEDULE_ID, with a one-to-many relationship to ECE_SPSO_ITEMS.
• EC.ECE_SPSO_DETAILS — detail-level table linked through the header, providing the schedule line breakdown.
• EC.ECE_SPSO_ITEMS_X — extension table joined on TRANSACTION_RECORD_ID.
• EC.ECE_XREF_DATA — source of external code-converted values for columns flagged as Ext.
• CHV_SCHEDULE_ITEMS — primary source table in Oracle Supplier Scheduling.
• CHV_ORG_OPTIONS — supplies scheduling options and ship-to organization defaults.
• EC.ECE_SPSO_ITEMS_U1, ECE_SPSO_ITEMS_N1, and ECE_SPSO_ITEMS_PK — the unique, non-unique, and primary key indexes that govern access paths.