Search Results csd_so_orchestration_u1

Overview

CSD.CSD_SO_ORCHESTRATION is a transactional table in the Depot Repair (CSD) schema of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. Its documented purpose is to store statuses of documents that live outside of Depot Repair, allowing a repair line to reference and track the lifecycle of external documents — service orders, sales orders, purchase orders, or similar artifacts identified by DOCUMENT_TYPE. The table therefore acts as a bridging registry between depot repair activity and the broader EBS document ecosystem.

The object is registered in FND Design Data as CSD.CSD_SO_ORCHESTRATION and resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, which is consistent with a moderate-update transactional table. Its unique index, CSD_SO_ORCHESTRATION_U1, resides separately in APPS_TS_TX_IDX.

The heuristic Data Vault classification mined from the foreign-key structure is standalone. Modeled as a Data Vault, this table is best understood as a link candidate: it carries the relationship between a repair line and an external document, with the document identity (DOCUMENT_HEADER_ID, DOCUMENT_LINE_ID, DOCUMENT_TYPE) and the repair line (REPAIR_LINE_ID) forming the natural business key. Because it holds descriptive status and attribute columns, it also exhibits satellite characteristics, and a hybrid link-satellite modeling suggestion is reasonable.

Key Information Stored

The primary key is the surrogate column ORCHESTRATION_ID, backed by the constraint CSD_SO_ORCHESTRATION_PK1. It is generated by the application and carries no business meaning; it should never be used as a join key to external entities.

The business-key candidate, enforced by the unique index CSD_SO_ORCHESTRATION_U1, is the composite of REPAIR_LINE_ID, DOCUMENT_HEADER_ID, DOCUMENT_LINE_ID, and DOCUMENT_TYPE. This guarantees that a given external document line cannot be registered twice against the same repair line.

The most operationally significant columns are:

• REPAIR_LINE_ID — the repair order to which the document is linked; foreign key to CSD_REPAIRS, and the only documented outbound FK from this table.
• DOCUMENT_HEADER_ID — identifier of the external document header, populated when the document contains several lines.
• DOCUMENT_LINE_ID — identifier of the specific line within that header.
• DOCUMENT_HEADER_NAME and DOCUMENT_LINE_NAME — human-readable names denormalized for display and reporting.
• DOCUMENT_TYPE — the discriminator identifying which external entity the document IDs refer to; values derive from the CSD_DOCUMENT_TYPES lookup.
• STATUS_ID and STATUS_CODE — the current status of the external document; either may be null, with the other supplying the value.
• PREV_STATUS_ID and PREV_STATUS_CODE — the prior status, enabling status-transition tracking and reporting.
• Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER for optimistic locking.
• Descriptive flexfield columns ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15, plus PARAMN1 for additional application-defined values.

Common Use Cases and Queries

The principal use case is tracing which external documents are attached to a repair order and what their current status is. A typical query joins back to the repair line:

SELECT o.orchestration_id,
       o.repair_line_id,
       o.document_type,
       o.document_header_name,
       o.document_line_name,
       o.status_code,
       o.prev_status_code
FROM   csd_so_orchestration o
WHERE  o.repair_line_id = :p_repair_line_id;

Reporting on status transitions relies on comparing STATUS_CODE with PREV_STATUS_CODE; a null STATUS_CODE indicates the ID-based column should be used instead, and vice versa. A reconciliation query identifying documents of a single type across all repairs filters on DOCUMENT_TYPE, which is also the leading discriminator for the unique index when combined with the repair line. Auditing queries order by LAST_UPDATE_DATE to detect recently changed external document statuses, and the flexfield attributes may be surfaced in customer-specific reports where standard columns are insufficient.

Related Objects

• CSD_REPAIRS — the only documented FK target; joined on CSD_SO_ORCHESTRATION.REPAIR_LINE_ID = CSD_REPAIRS.REPAIR_LINE_ID. This is the canonical parent repair order record.
• CSD_DOCUMENT_TYPES — the FND lookup supplying valid DOCUMENT_TYPE codes; join on the lookup code.
• CSD_SO_ORCHESTRATION_PK1 — the primary-key constraint on ORCHESTRATION_ID, used by the application for row retrieval.
• CSD_SO_ORCHESTRATION_U1 — the unique index over REPAIR_LINE_ID, DOCUMENT_HEADER_ID, DOCUMENT_LINE_ID, DOCUMENT_TYPE; relevant for duplicate-prevention logic and reverse lookups.
• CSD_ATTRIBUTES / flexfield infrastructure — the descriptive flexfield context defined by ATTRIBUTE_CATEGORY.
• FND_LOOKUPS — backing store for status and document-type lookup values when codes rather than IDs are populated.

Because the table is classified as standalone with a single outbound FK, dependent objects outside Depot Repair reference it indirectly through the repair line rather than through direct foreign keys.