Search Results csd_serial_numbers_v

Overview

CSD_SERIAL_NUMBERS_V is a valid, seeded view owned by the APPS schema within the CSD – Depot Repair product of Oracle E-Business Suite. Its stated purpose is to display all possible serial numbers originating from three distinct sources: Oracle Installed Base (IB), service requests (depot repair incidents), and the manufacturing/inventory serial number table (MTL_SERIAL_NUMBERS). The view therefore acts as a consolidated, cross-module serial number lookup, returning a unified result set that spans installed instances, service-related serial identifiers, and inventory-held serial numbers.

The view is defined with a UNION rather than a UNION ALL, so duplicate serial numbers appearing across sources are eliminated. This makes it suitable as a validation and selection list source, particularly when a Depot Repair user, concurrent program, or integration must resolve a serial number without knowing a priori whether that serial exists in Installed Base, in a service request context, or simply as a stocked serial in inventory. The user query term "inventory_revision" maps directly to the INVENTORY_REVISION column exposed by the view, which is populated from CSI_ITEM_INSTANCES in the first branch of the union and from MTL_SERIAL_NUMBERS.REVISION in the second branch.

Underlying Base Objects

The documented referenced base objects are CSI_ITEM_INSTANCES, CSI_I_PARTIES, CS_INCIDENTS_ALL_B, CS_STD, MTL_SERIAL_NUMBERS, and MTL_SYSTEM_ITEMS_VL. The first branch of the view joins CSI_ITEM_INSTANCES (CII) to MTL_SYSTEM_ITEMS_VL (MSI) on INVENTORY_ITEM_ID and to CSI_I_PARTIES (CIP) on INSTANCE_ID. The second branch draws serial numbers from MTL_SERIAL_NUMBERS (MSN) joined to MTL_SYSTEM_ITEMS_VL. CS_INCIDENTS_ALL_B supplies the service request linkage, while the CS_STD package is invoked in the predicate MSI.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID to restrict items to the item validation organization.

Filtering is substantial. The first branch requires the owner party source table to be HZ_PARTIES, a non-null serial number, an active date window bounded by ACTIVE_START_DATE and ACTIVE_END_DATE, an OWNER relationship in CSI_I_PARTIES, and location type codes among HZ_PARTY_SITES, HZ_LOCATIONS, VENDOR_SITE, or INTERNAL_SITE, or an INVENTORY location whose ACTIVE_END_DATE has passed. Items must be enabled and effective, must have no contract item type, must be service-request enabled ('E'), and must not be serial-controlled at the lowest level (SERIAL_NUMBER_CONTROL_CODE <> 1).

Key Columns

• SERIAL_NUMBER — The serial identifier, taken from CSI_ITEM_INSTANCES.SERIAL_NUMBER or MTL_SERIAL_NUMBERS.SERIAL_NUMBER.
• INSTANCE_ID / INSTANCE_NUMBER — Installed Base instance identifiers; null for the inventory-sourced rows in the second union branch.
• INVENTORY_REVISION — The item revision associated with the instance or serial number; this is the column relevant to the "inventory_revision" search term.
• INVENTORY_ITEM_ID — The inventory item identifier linking the record to MTL_SYSTEM_ITEMS_VL.
• CONCATENATED_SEGMENTS — The concatenated item key, providing a human-readable item identifier.
• ORGANIZATION_ID — The inventory organization owning the item definition.
• DESCRIPTION — The item description.
• SERIAL_NUMBER_CONTROL_CODE and REVISION_QTY_CONTROL_CODE — Item-level control flags indicating serial and revision/quantity control behavior.
• CUSTOMER_ID — The owning party identifier from CSI_I_PARTIES; null for inventory-sourced rows.

Common Use Cases and Queries

The view is typically used to populate serial number LOVs in Depot Repair, to validate a serial entered against both Installed Base and inventory, and to reconcile serial-linked revisions across IB and MTL_SERIAL_NUMBERS. A representative query retrieving revision information for a given serial is:

SELECT serial_number, inventory_revision, inventory_item_id, concatenated_segments, organization_id, description, customer_id FROM apps.csd_serial_numbers_v WHERE serial_number = :p_serial_number AND organization_id = :p_org_id;

To list all revisions recorded for an item's serials:

SELECT DISTINCT serial_number, inventory_revision, concatenated_segments FROM apps.csd_serial_numbers_v WHERE inventory_item_id = :p_item_id ORDER BY serial_number, inventory_revision;

Because INSTANCE_ID and CUSTOMER_ID are null for inventory-sourced rows, queries that must distinguish Installed Base instances from pure inventory serials should test INSTANCE_ID IS NOT NULL. Similarly, join predicates on ORGANIZATION_ID should be used where organization-specific serial data is required, and the view's built-in union behavior should be relied upon to suppress duplicates when a serial exists in more than one source system.