Search Results ece_spso_items_pk

Overview

ECE_SPSO_ITEMS is an Oracle e-Commerce Gateway (EC) staging table owned by the EC schema. It holds supplier schedule line data used to generate the outbound Planning Schedule (830/DELFOR) and Shipping Schedule (862/DELJIT) transactions. During an outbound schedule run, the extraction program populates this table from the source scheduling and sourcing tables; the e-Commerce Gateway transmission program then reads the staged rows, maps them to the appropriate X12 or EDIFACT document, and creates the outbound interface records. The table is therefore an intermediate aggregation point rather than a transactional master: rows are transient, tied to a specific run and schedule, and generally purged or regenerated on each execution.

The primary key is ECE_SPSO_ITEMS_PK, defined across three columns: SCHEDULE_ITEM_ID, SCHEDULE_ID, and RUN_ID. A unique index, ECE_SPSO_ITEMS_U1, is defined on the same three columns, so the surrogate key and the business-key candidate coincide in this case. Mined foreign-key structure suggests a standalone Data Vault classification; in practice, however, the table behaves as a satellite-like staging object anchored to the schedule header via SCHEDULE_ID and to the run via RUN_ID. In the 12.2.2 documented physical schema the table carries 145 columns.

Key Information Stored

The most significant columns, grouped by purpose:

• Keys: SCHEDULE_ID, SCHEDULE_ITEM_ID, and RUN_ID form the primary key and uniquely identify each staged schedule line within a run.
• Item identity: ITEM_NUMBER, PRODUCT_DESCRIPTION, SUPPLIER_PRODUCT_NUMBER, and PURCHASING_UOM_CODE (with its extension column PURCHASING_UOM_CODE_EXT) describe the scheduled material and unit of measure.
• Cumulative and authorization quantities: STARTING_AUTH_QUANTITY, STARTING_CUM_QUANTITY, and the primary-UOM counterparts STARTING_AUTH_QTY_PRIMARY and STARTING_CUM_QTY_PRIMARY, plus CUM_QTY_START_DATE.
• Prior activity: LAST_RECEIPT_SHIPMENT_CODE, LAST_RECEIPT_DATE, LAST_RECEIPT_QUANTITY, and LAST_RECEIPT_CUM_QTY carry the last receipt or shipment reference points required by the 830/862 format.
• Sourcing context: SUPPLIER_AGREEMENT_NUMBER, SUPPLIER_AGREEMENT_TYPE, and SUPPLIER_AGREEMENT_LINE_NUMBER link the schedule to the underlying agreement.
• Ship-to detail: SHIP_TO_ORG_NAME, SHIP_TO_ORG_CODE, SHIP_TO_ORG_LOCATION_CODE, address lines, geography, and contact fields.
• Contacts: BUYER_LAST_NAME/FIRST_NAME/WORK_TELEPHONE, ITEM_SCHEDULER_*, and ITEM_PLANNER_*.
• Compliance: HAZARD_CLASS, UN_NUMBER, and their descriptive companions.
• Descriptive flexfields: SCH_ITEM_ATTRIBUTE1–15, ITEM_ATTRIBUTE1–15, ASL_ATTRIBUTE1–15, ORG_OPT_ATTRIBUTE1–15, and SHIP_TO_ORG_ATTRIBUTE1–15, each with a category column.
• Audit and run control: TRANSACTION_RECORD_ID, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE, CREATION_DATE, LAST_UPDATE_DATE, and user/login columns.

Common Use Cases and Queries

Typical usage centers on diagnosing a schedule transmission and reproducing its content. A frequently used pattern lists all lines for a given run:

SELECT schedulE_item_id, item_number, starting_auth_quantity,
       starting_cum_quantity, last_receipt_quantity
FROM   ec.ece_spso_items
WHERE  run_id = :run_id
AND    schedule_id = :schedule_id
ORDER BY schedule_item_id;

Reconciliation queries join back to the schedule header through SCHEDULE_ID and to the concurrent request through REQUEST_ID or RUN_ID to confirm which lines were picked up by a specific 830/862 execution. Analysts also aggregate volume by ship-to organization, by supplier agreement, or by purchasing UOM for audit and dispute resolution, and inspect the attribute flexfields to verify that descriptive segments were populated before transmission.

Related Objects

The following objects are the most relevant to working with ECE_SPSO_ITEMS:

• ECE_SPSO_HEADERS — the schedule header table, joined on SCHEDULE_ID; supplies header-level schedule data for the outbound document.
• ECE_SPSO_ITEM_DETAILS — detail-level schedule buckets, joined on SCHEDULE_ID and SCHEDULE_ITEM_ID.
• ECE_OUTBOUND_LOG / ECE_TRANSACTIONS — records the outbound transmission and its status, joined through TRANSACTION_RECORD_ID.
• FND_CONCURRENT_REQUESTS — the concurrent request that populated the run, joined on REQUEST_ID.
• PO_SOURCING_RULES / sourcing source views — the upstream scheduling data from which the staging rows are derived, ultimately tied to the supplier agreement columns in this table.
• EC_GRP / e-Commerce Gateway mapping programs — the concurrent programs and grouping logic that read this table to construct the outbound 830 and 862 interface records.