Search Results ece_spso_ship_det




Overview
Explore in AI Chat

Overview

EC.ECE_SPSO_SHIP_DET is a table within the Oracle e-Commerce Gateway (EC) module, a product that provides inbound and inbound data translation between Oracle E-Business Suite applications and external trading partners. The table stores shipping detail records associated with the Scheduled Shipment Information (SPSO) gateway transaction, which is used to communicate shipment schedules and related shipping line details to external systems. In Oracle EBS 12.1.1 and 12.2.2, this object resides in the EC schema and is documented as VALID within the ETRM repository.

From a heuristic Data Vault modeling perspective, the mined foreign-key structure classifies ECE_SPSO_SHIP_DET as a standalone table. This suggests it functions as a satellite or detail-level entity that captures descriptive shipping attributes rather than acting as a hub or link. The absence of inbound foreign-key dependencies indicates that the table stores transactional context attached to the gateway run rather than participating in enforced relational chains within the documented schema.

Key Information Stored

The table contains 43 documented columns. The primary key constraint, ECE_SPSO_SHIP_DET_PK, is a composite surrogate key composed of SCHEDULE_SHIP_ID, SCHEDULE_ID, SCHEDULE_ITEM_ID, SCHEDULE_ITEM_DETAIL_SEQUENCE, and RUN_ID. These columns together uniquely identify a shipping detail record within a specific gateway execution.

Business-key candidates correspond largely to the composite primary key components. RUN_ID ties the record to a specific e-Commerce Gateway run, while SCHEDULE_ID, SCHEDULE_ITEM_ID, and SCHEDULE_ITEM_DETAIL_SEQUENCE represent the scheduling hierarchy. Additional important columns include:

Audit columns such as CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE support concurrency control and traceability back to the concurrent program that populated the record.

Common Use Cases and Queries

This table is primarily queried during troubleshooting and reconciliation of e-Commerce Gateway scheduled shipment transmissions. Analysts inspect records by RUN_ID to verify what shipping details were extracted for a given run:

  • SELECT * FROM ec.ece_spso_ship_det WHERE run_id = :run_id;
  • SELECT schedule_id, schedule_item_id, item_det_ship_quantity, item_det_uom_code FROM ec.ece_spso_ship_det WHERE schedule_ship_id = :ship_id;
  • Join to address fields to validate ship-to data transmitted to the trading partner.
  • Aggregate ITEM_DET_SHIP_QUANTITY by SCHEDULE_ID to reconcile transmitted versus expected quantities.
  • Audit queries filtering on CREATION_DATE to identify when a shipment detail row was generated by the gateway.

Related Objects

The metadata documents the table as standalone with no mined outbound foreign keys; relationships are therefore inferred from shared gateway identifiers and sibling SPSO objects rather than enforced constraints. Significant related objects include:

  • EC.ECE_SPSO_HEADERS — the header-level SPSO record, joinable on RUN_ID.
  • EC.ECE_SPSO_SHIP — the scheduled shipment parent, joinable on SCHEDULE_SHIP_ID.
  • EC.ECE_SPSO_ITEMS — the scheduled item record, joinable on SCHEDULE_ITEM_ID.
  • EC.ECE_GATEWAY_RUNS (or the gateway run log) — joinable on RUN_ID for run diagnostics.
  • EC.ECE_TP_HEADERS / trading partner definitions — used to interpret the external partner context.
  • Oracle Shipping (WSH) delivery detail tables — referenced conceptually via LINE_LOCATION_ID and DOCUMENT_SHIPMENT_NUMBER.