Results for “csd_ro_soln_sc_route_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

CSD_RO_SOLN_SC_ROUTE_V is a reporting view within the Oracle E-Business Suite Depot Repair (CSD) module. It presents a consolidated, denormalized picture of the relationship between knowledge-base solution definitions and the manufacturing or repair routes associated with them. In the Service Depot Repair flow, a solution frequently drives the sequence of operations a technician must perform; this view exposes that linkage in a single queryable structure, allowing reports, concurrent programs, and integration interfaces to resolve solution metadata and its corresponding route definition without hand-joining the underlying knowledge management and route tables.

The view is documented in ETRM 12.2.2 as "not implemented in this database," meaning that in the reference instance from which the metadata was extracted, the object did not physically exist. Nevertheless, the captured view text defines its intended semantics precisely and can be used as a specification if the view must be recreated or if equivalent logic is required in a custom query. The metadata lists no owner and no explicitly referenced base objects, but the stored SELECT statement reveals the full set of dependencies.

Underlying Base Objects

The view is defined over five documented objects. CS_KB_SETS_VL supplies the solution header (SET_ID, SET_NUMBER, NAME) from the knowledge-base sets tables. CSD_RO_SOLUTIONS provides the repair-line context through REPAIR_LINE_ID and the solution number used for correlation. AHL_MR_ROUTES_V is the multi-record route view that supplies MR_HEADER_ID, ROUTE_ID, ROUTE_NUMBER, ROUTE_DESCRIPTION, and the effective start and end dates. AHL_ROUTES_V contributes the route revision status code keyed on ROUTE_ID. Finally, CS_KB_SET_LINKS acts as the association bridge, carrying OBJECT_CODE, OTHER_ID, and LINK_TYPE to connect knowledge-base sets to both Depot Repair solutions and route headers.

The join topology is significant. The link between solution and route is established through CS_KB_SET_LINKS with OBJECT_CODE = 'DRSC', where the link's OTHER_ID resolves to the route MR_HEADER_ID. A second, correlated subquery against CS_KB_SET_LINKS and CS_KB_SETS_B — restricted to OBJECT_CODE = 'DR' and LINK_TYPE = 'S' — selects the maximum SET_ID for the repair line, ensuring that only the most recent solution revision for a given repair line is returned. This MAX subquery is the mechanism that keeps the result set from multiplying across historical solution versions.

Key Columns

• SOLUTION_ID / SOLUTION_NUMBER / SOLUTION_TITLE — Identity and descriptive name of the knowledge-base solution attached to the repair line.
• REPAIR_LINE_ID — The Depot Repair line to which the solution applies, linking operational repair activity to the solution record.
• MR_HEADER_ID / ROUTE_ID — Primary and foreign key identifiers for the route header and its revision, respectively.
• ROUTE_NUMBER / ROUTE_DESCRIPTION — Human-readable route identifiers used in dispatch and traveler documentation.
• START_DATE_ACTIVE / END_DATE_ACTIVE — Effective date range of the route, essential for filtering to currently valid routings.
• REVISION_STATUS_CODE — Status of the route revision (for example, released versus draft), typically used to exclude unreleased routings from execution.

Common Use Cases and Queries

Typical uses include verifying that each Depot Repair solution has an active route, generating traveler packets that combine solution instructions with route operations, and auditing links where OBJECT_CODE = 'DRSC' exists but the route is expired. A representative query filtering to currently effective routes is:

SELECT solution_number, solution_title, repair_line_id,
       route_number, route_description, revision_status_code
FROM   csd_ro_soln_sc_route_v
WHERE  SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
                   AND NVL(end_date_active, SYSDATE)
ORDER  BY solution_number, route_number;

Because the documentation marks the view as not implemented, developers should confirm its existence with a data dictionary query before relying on it in production, and otherwise recreate the documented SELECT as a custom view. Note that the user search term ahl_mr_routes_v corresponds to one of this view's principal base objects rather than the view itself.