Search Results csd_so_orchestration

Overview

The CSD.CSD_SO_ORCHESTRATION table is a Depot Repair (CSD) module object in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description states that it stores statuses of documents outside of Depot Repair — that is, it tracks the lifecycle state of external or foreign documents (such as sales orders, order lines, or other orchestrated business documents) as they progress through flows that Depot Repair must observe or coordinate.

The table resides in the CSD schema and is classified as VALID. The ET ReTRM metadata records 34 columns and identifies the primary key constraint as CSD_SO_ORCHESTRATION_PK1, defined on ORCHESTRATION_ID.

Heuristic Data Vault classification mined from the foreign-key structure returns a standalone classification: the table does not behave as a true hub, link, or satellite because its only outbound foreign key, REPAIR_LINE_ID → CSD_REPAIRS, is a soft or descriptive reference rather than a canonical Data Vault link. As a modeling suggestion, this object is best treated as a satellite-like status/audit record keyed by the surrogate ORCHESTRATION_ID, with the unique business key CSD_SO_ORCHESTRATION_U1 available for natural-key resolution. It is not a true standalone object because it depends on the Depot Repair repair-line context for meaning.

Key Information Stored

The most operationally significant columns are:

• ORCHESTRATION_ID — the surrogate primary key (CSD_SO_ORCHESTRATION_PK1) and the sole unique identifier for each orchestration record.
• REPAIR_LINE_ID — the foreign key linking the orchestration row to CSD_REPAIRS; identifies the repair line whose external document is being tracked.
• DOCUMENT_HEADER_ID and DOCUMENT_LINE_ID — the external document identifiers for the header and line being orchestrated.
• DOCUMENT_HEADER_NAME and DOCUMENT_LINE_NAME — human-readable names or numbers for those external documents.
• DOCUMENT_TYPE — the category of the external document, used to distinguish orchestrated document families.
• STATUS_ID and STATUS_CODE — the current orchestration status of the external document.
• PREV_STATUS_ID and PREV_STATUS_CODE — the immediately prior status, enabling status-transition history and audit.
• PARAMN1 — a numeric parameter column reserved for orchestration-specific numeric data.
• OBJECT_VERSION_NUMBER — the optimistic-locking version marker, plus the standard WHO audit columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN.
• ATTRIBUTE1 through ATTRIBUTE15 and ATTRIBUTE_CATEGORY — the standard Oracle flexfield/descriptive extension set for customer-specific data.

The business-key candidate is the unique index CSD_SO_ORCHESTRATION_U1, defined across REPAIR_LINE_ID, DOCUMENT_HEADER_ID, DOCUMENT_LINE_ID, and DOCUMENT_TYPE. This confirms that a given external document line, of a given document type, for a given repair line is intended to be unique — the surrogate ORCHESTRATION_ID simply provides the physical key.

Common Use Cases and Queries

Typical uses involve reporting current orchestration status for external documents tied to repair lines, tracking status transitions, and auditing document flow through Depot Repair.

To retrieve the latest status for a repair line:

SELECT o.REPAIR_LINE_ID, o.DOCUMENT_TYPE, o.DOCUMENT_HEADER_ID, o.DOCUMENT_LINE_ID, o.STATUS_CODE, o.PREV_STATUS_CODE FROM CSD.CSD_SO_ORCHESTRATION o WHERE o.REPAIR_LINE_ID = :p_repair_line_id;

To find documents in a specific state:

SELECT o.ORCHESTRATION_ID, o.DOCUMENT_HEADER_NAME, o.STATUS_CODE FROM CSD.CSD_SO_ORCHESTRATION o WHERE o.STATUS_CODE = :p_status_code AND o.DOCUMENT_TYPE = :p_doc_type;

To detect status changes since a point in time, join the audit columns:

SELECT o.ORCHESTRATION_ID, o.PREV_STATUS_CODE, o.STATUS_CODE, o.LAST_UPDATE_DATE FROM CSD.CSD_SO_ORCHESTRATION o WHERE o.LAST_UPDATE_DATE >= :p_from_date;

These queries support dashboards, SLA measurement for external document turnaround, and reconciliation between Depot Repair and upstream order systems.

Related Objects

The most significant related objects and the join columns are:

• CSD_REPAIRS — joined via CSD_SO_ORCHESTRATION.REPAIR_LINE_ID → CSD_REPAIRS; the primary parent context for each orchestration record.
• CSD_REPAIR_LINES (or the repair-line entity referenced by CSD_REPAIRS) — supplies line-level repair detail for the REPAIR_LINE_ID value.
• OE_ORDER_HEADERS_ALL / OE_ORDER_LINES_ALL — likely sources or targets of DOCUMENT_HEADER_ID and DOCUMENT_LINE_ID when the orchestrated document is a sales order.
• CSD_REPAIR_ORDERS — the repair order context used to aggregate orchestration status back to the Depot Repair transaction.
• CSD_SO_ORCHESTRATION_PK1 and CSD_SO_ORCHESTRATION_U1 — the constraint and unique-index definitions that enforce row identity and business-key uniqueness.
• FND_FLEX_VALUES_VL and FND_FLEX_VALUE_SETS — used to resolve ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 descriptive flexfield segments.

Because the metadata classifies this table as standalone with only one outbound foreign key, related-object enumeration is intentionally narrow; broader integration is achieved through the external document identifiers rather than hard referential constraints.