Search Results bom_item_name

Overview

The CSD_SC_BOM_AND_ROUTE_V view is a reporting and integration object within the Oracle E-Business Suite Depot Repair (CSD) module. It exposes the mapping maintained between Service Codes and the Bills of Material (BOM) and Routings that are used to repair or rebuild an item. In Depot Repair, a Service Code represents a repairable service offering, and the work performed against it is driven by a bill of material and a routing definition. This view flattens the underlying intersection table CSD_SC_WORK_ENTITIES so that the surrogate keys stored against each Service Code are resolved into human-readable inventory item names, descriptions, organization codes, and alternate designator descriptions.

Because the view joins the Depot Repair work-entity definition to manufacturing master data, it is commonly used in custom reports, concurrent programs, and interface extracts that need to answer the question: for a given Service Code, which BOM and routing are attached, in which organization, and against which assembly item? The presence of denormalized columns such as BOM_ITEM_NAME, ROUTING_ITEM_NAME, and the alternate designator descriptions makes it directly suitable for display in BI Publisher reports and OAF-based inquiry pages without additional joins. The routing_item_name column in particular is the attribute most frequently searched by developers looking to report the assembly item carrying the routing for a Service Code.

Underlying Base Objects

The view is owned by the APPS schema and is defined over the following documented base objects:

• CSD_SC_WORK_ENTITIES — the driving table linking Service Codes (SERVICE_CODE_ID) to work entities, filtered to rows where WORK_ENTITY_TYPE_CODE = 'BOM'.
• BOM_BILL_OF_MATERIALS — joined on WORK_ENTITY_ID1 to the bill sequence, supplying the BOM assembly item and alternate BOM designator.
• BOM_OPERATIONAL_ROUTINGS — joined on WORK_ENTITY_ID2 to the routing sequence, supplying the routing assembly item and alternate routing designator.
• BOM_ALTERNATE_DESIGNATORS — outer-joined twice (aliased ALTB and ALTR) to resolve alternate BOM and alternate routing designator descriptions.
• MTL_SYSTEM_ITEMS_KFV — outer-joined twice (aliased MSIB and MSIR) to resolve the BOM and routing assembly items to concatenated segment names and descriptions.
• MTL_PARAMETERS and HR_ALL_ORGANIZATION_UNITS — joined on the organization identifier to return the organization code and organization name.
• FND_GLOBAL — referenced by the view metadata for session and user context.

All manufacturing-side joins are outer joins, so a Service Code row is still returned when its BOM or routing has been deleted or is not yet defined.

Key Columns

• SC_WORK_ENTITY_ID — primary surrogate key of the Service Code work-entity mapping row.
• SERVICE_CODE_ID — foreign key to the Service Code.
• ORGANIZATION_ID / ORGANIZATION_CODE — inventory organization where the BOM and routing reside; ORGANIZATION_CODE is resolved from MTL_PARAMETERS.
• NAME — organization name from HR_ALL_ORGANIZATION_UNITS.
• BOM_SEQUENCE_ID — bill sequence identifier of the associated BOM.
• BOM_ITEM_ID / BOM_ITEM_NAME / BOM_ITEM_DESC — assembly item of the BOM, its concatenated segments, and description.
• ALTERNATE_BOM_DESIGNATOR / ALTERNATE_BOM_DESC — alternate BOM identifier and its description.
• ROUTING_SEQUENCE_ID — routing sequence identifier of the associated routing.
• ROUTING_ITEM_ID / ROUTING_ITEM_NAME / ROUTING_ITEM_DESC — assembly item carrying the routing, its concatenated segments, and description. ROUTING_ITEM_NAME is the column most often targeted when querying this view.
• ALTERNATE_ROUTING_DESIGNATOR / ALTERNATE_ROUTING_DESC — alternate routing identifier and its description.
• Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

The view is typically queried to validate that every Service Code has a BOM and routing attached, to report the routing item name for a given Service Code, or to extract BOM and routing mappings for a specified organization. The following query returns the routing and BOM details for a Service Code by routing item name:

SELECT service_code_id,
       organization_code,
       bom_item_name,
       alternate_bom_designator,
       routing_item_name,
       alternate_routing_designator
FROM   apps.csd_sc_bom_and_route_v
WHERE  routing_item_name = :p_routing_item_name
AND    organization_id   = :p_org_id;

A second common pattern identifies Service Codes missing a routing definition, which the outer joins expose as null ROUTING_SEQUENCE_ID values, and is useful for data-cleansing scripts prior to go-live. Because the view references FND_GLOBAL for session context, queries should be executed with the standard EBS security and MOAC initialization, and callers should filter by ORGANIZATION_ID where operating unit or inventory organization isolation is required.