Results for “csd_cp_reference_v”

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

Overview

CSD_CP_REFERENCE_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, defined within the CSD (Depot Repair) product family. Its documented purpose is to retrieve all valid customer products, presenting a denormalized, report-ready projection of item instance data held in the Oracle Complex Maintenance, Repair and Overhaul (cMRO / CSI) foundation tables. In release 12.1.1 and 12.2.2, the view is used by Depot Repair, Field Service, and integrated service modules to reference an installed-base record without requiring the caller to join the underlying CSI tables directly.

The view applies a set of validity filters at the database level, so that only customer products that are active, non-terminated, and associated with a defined status are returned. Because the filtering logic is encapsulated inside the view, consumers receive a consistent definition of "valid customer product" across form-based lookups, concurrent programs, and external integrations.

Underlying Base Objects

The view is defined over the following documented base objects in the APPS schema:

• CSI_ITEM_INSTANCES (synonym) — aliased as CP; the primary installed-base table supplying instance, ownership, item, serial, lot, revision, quantity, and UOM attributes.
• CSI_INSTANCE_STATUSES (synonym) — aliased as CPS; supplies status name and the incident-allowed indicator, and drives the status validity filter.
• CSI_I_ORG_ASSIGNMENTS (synonym) — aliased as CIOA; the instance-to-operating-unit assignment table, outer-joined for the SOLD_FROM relationship type to derive ORG_ID. This is the object most often referenced when users search for "csi_i_org_assignments" in connection with this view.
• CS_STD (package) — a service/repair standards package whose GET_ITEM_VALDN_ORGZN_ID function returns the item validation organization used to constrain the item master join.
• MTL_SYSTEM_ITEMS_VL (view) — the item master view, supplying concatenated segments and description.
• MTL_UNITS_OF_MEASURE_VL (view) — the unit of measure view, supplying the translated UOM description.

The joins to CSI_INSTANCE_STATUSES are outer joins with termination and status filters (TERMINATED_FLAG != 'Y' and INSTANCE_STATUS_ID != 1), and an outer join to CSI_I_ORG_ASSIGNMENTS restricts the operating-unit assignment to the SOLD_FROM relationship. A date-range predicate on ACTIVE_START_DATE and ACTIVE_END_DATE, defaulted through NVL, further constrains the result set to currently active instances.

Key Columns

• REFERENCE_NUMBER — the instance number identifying the customer product.
• CUSTOMER_PRODUCT_ID — the instance identifier (INSTANCE_ID in CSI_ITEM_INSTANCES).
• CUSTOMER_ID — the owning party account identifier.
• INVENTORY_ITEM_ID / ITEM / ITEM_DESC — the inventory item key, concatenated segments, and item description.
• CURRENT_SERIAL_NUMBER / LOT_NUMBER / REVISION / QUANTITY — instance-level configuration attributes.
• UNIT_OF_MEASURE_CODE / UNIT_OF_MEASURE_TL — the UOM code and its translated description.
• ORG_ID — the operating unit derived from CSI_I_ORG_ASSIGNMENTS.
• INCIDENT_ALLOWED_FLAG / STATUS — status attributes sourced from CSI_INSTANCE_STATUSES.

Common Use Cases and Queries

Typical uses include installed-base reporting, service request and incident validation, and data extraction for integration. Because the view already enforces validity rules, it is commonly queried instead of CSI_ITEM_INSTANCES in read-only contexts.

Example query joining the view to an incident source:

• SELECT reference_number, customer_product_id, item, current_serial_number, status FROM csd_cp_reference_v WHERE customer_id = :p_customer_id;
• SELECT cp.reference_number, cp.item, cp.org_id FROM csd_cp_reference_v cp, csi_i_org_assignments cioa WHERE cp.customer_product_id = cioa.instance_id AND cioa.relationship_type_code = 'SOLD_FROM';
• SELECT cp.reference_number, cp.status FROM csd_cp_reference_v cp WHERE cp.incident_allowed_flag = 'Y' AND cp.inventory_item_id = :p_item_id;

Consumers should be aware that the view performs outer joins and function calls, and therefore can be more expensive than a direct base-table query when large installed-base volumes are involved.