Search Results ece_spso_headers_u1

Overview

EC.ECE_SPSO_HEADERS is an e-Commerce Gateway interface table that holds supplier schedule data used to support the outbound Planning Schedule (830/DELFOR) and Shipping Schedule (862/DELJIT) transactions in Oracle E-Business Suite Release 12.1.1 and 12.2.2. The table is owned by the EC schema and its FND Design Data reference is EC.ECE_SPSO_HEADERS. It is classified as VALID in the ETRM repository, and the documented physical schema carries 179 columns. This is a transient staging table: each row is purged once its data has been written to the outbound data file, so it should be treated as an interface buffer rather than a permanent record.

Data is sourced primarily from CHV_SCHEDULE_HEADERS in Oracle Supplier Scheduling, together with scheduling options and ship-to organization defaults found in CHV_ORG_OPTIONS. Rows represent confirmed planning or shipping schedules identified by the Oracle Supplier Scheduler Workbench, the Oracle Supplier Scheduling AutoSchedule process, or selected by the e-Commerce Gateway user. Because the table is shared by two transactions, RUN_ID uniquely identifies the session that created the row. From a Data Vault modeling perspective, the metadata's relationship analysis classifies this object as standalone, which suggests a hub-like treatment in a heuristic model, with SCHEDULE_ID acting as the natural business key and RUN_ID as a session qualifier.

Key Information Stored

The documented surrogate primary key is ECE_SPSO_HEADERS_PK on (SCHEDULE_ID, RUN_ID). Two business-key candidates are documented: the unique index ECE_SPSO_HEADERS_U1, also on (SCHEDULE_ID, RUN_ID), and the equivalent PK constraint. SCHEDULE_ID is the schedule's primary identifier, while RUN_ID distinguishes the gateway session that created the row because the table is shared across the two outbound transaction types.

• SCHEDULE_ID – the primary schedule identifier.
• RUN_ID – unique session identifier for the row's creation run.
• SCHEDULE_TYPE and SCHEDULE_SUB_TYPE – classify the schedule for the 830/DELFOR or 862/DELJIT transaction.
• SCHEDULE_NUMBER_REVISION and SCHEDULE_STATUS – revision and current status of the confirmed schedule.
• SCHEDULE_GENERATION_DATE, FORECAST_HORIZON_START_DATE, FORECAST_HORIZON_END_DATE – define the scheduling and forecast planning horizon.
• SUPPLIER_NAME and SUPPLIER_NUMBER – vendor identification, with SUPPLIER_NUMBER_EXT carrying the converted external value.
• SHIP_TO_ORG_NAME, SHIP_TO_ORG_CODE and SHIP_TO_ORG_LOCATION – ship-to organization defaults inherited from CHV_ORG_OPTIONS.
• MULTIPLE_SHIP_TO_ORG_FLAG and SHIP_TO_ORG_ENABLE_CUM_FLAG – control multi-ship-to processing and cumulative quantities.
• TRANSACTION_RECORD_ID – the unique key linking this row to its extension table ECE_SPSO_HEADER_X.
• DOCUMENT_ID, DOCUMENT_TYPE, DOCUMENT_CODE and DOCUMENT_PURPOSE_CODE – document identity for the outbound transaction.
• TP_LOCATION_CODE_EXT, TP_TRANSLATOR_CODE and TP_DESCRIPTION – trading partner location and translator metadata.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID and PROGRAM_UPDATE_DATE – concurrent program context.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY and LAST_UPDATE_LOGIN – standard audit columns.

Columns flagged with the "Int" or "Internal" naming convention hold Oracle Applications internal values, while those flagged "Ext" or "External" hold equivalent values sourced from ECE_XREF_DATA when code conversion is enabled. The table resides in tablespace APPS_TS_INTERFACE with PCT Free 10.

Common Use Cases and Queries

Typical uses include diagnosing why an 830 or 862 schedule failed to reach the trading partner, auditing which session produced a given schedule, and verifying ship-to defaults and cumulative flags before the data file is written. Because rows are purged after output, queries are usually run against active, unprocessed sessions.

• Retrieve a confirmed planning schedule by its natural key: SELECT * FROM ec.ece_spso_headers WHERE schedule_id = :p_schedule_id AND run_id = :p_run_id;
• List all rows for a gateway session, ordered by schedule: SELECT schedule_id, schedule_type, schedule_number_revision, schedule_status FROM ec.ece_spso_headers WHERE run_id = :p_run_id ORDER BY schedule_id;
• Join to the extension table for descriptive attributes: SELECT h.schedule_id, x.* FROM ec.ece_spso_headers h, ec.ece_spso_header_x x WHERE h.transaction_record_id = x.transaction_record_id;
• Audit rows produced by a concurrent request: filter on REQUEST_ID, PROGRAM_ID, or PROGRAM_APPLICATION_ID.
• Verify the forecast horizon for a supplier: SELECT supplier_name, forecast_horizon_start_date, forecast_horizon_end_date FROM ec.ece_spso_headers WHERE supplier_number = :p_supplier;
• Confirm the unique-key candidate is honored across sessions: group by SCHEDULE_ID and RUN_ID and assert count = 1.

Because RUN_ID separates the two transaction types, reports must always include both SCHEDULE_ID and RUN_ID to avoid cross-session ambiguity.

Related Objects

The following objects are the most significant in the surrounding ETRM model, based on the documented dependency and relationship metadata.

• EC.ECE_SPSO_HEADER_X – the extension table linked by TRANSACTION_RECORD_ID (documented as the unique key that links the header to its corresponding extension).
• EC.ECE_XREF_DATA – the cross-reference source for all Ext/External columns when code conversion is enabled.
• CHV_SCHEDULE_HEADERS – the Oracle Supplier Scheduling base table from which the header data is sourced.
• CHV_ORG_OPTIONS – source of the scheduling options and ship-to organization defaults.
• EC.ECE_SPSO_LINES and EC.ECE_SPSO_LINES_X – the corresponding line-level interface tables that carry schedule quantities.
• EC.ECE_SPSO_HEADER_INTERFACE – the gateway inbound/outbound processing interface that reads and writes these rows.
• The e-Commerce Gateway concurrent programs and Oracle Supplier Scheduling Workbench/AutoSchedule processes that populate and consume this table.

No foreign-key relationships are documented for this object; the metadata classifies it as standalone, meaning joins are performed on the TRANSACTION_RECORD_ID and SCHEDULE_ID/RUN_ID keys described above rather than enforced referential constraints.