Search Results invbv_serial_numbers

Overview

INVBV_SERIAL_NUMBERS is a read-only view owned by the APPS schema in Oracle E-Business Suite, classified under the Inventory (INV) product family. It is documented in ETRM as a retrofitted object, meaning it was introduced or adjusted to conform to the EBS multi-organization security model rather than being a legacy seed object. The view presents serial number attribute data drawn from the serial number master table and is intended primarily for reporting, inquiry, and integration scenarios in which downstream consumers require a stable, security-filtered projection of serialized inventory data without querying the base table directly.

Because it exposes organizational context and applies an organization security predicate, the view is suited to environments running 12.1.1 and 12.2.2 where responsibility-level organization access must be enforced. It is marked VALID in the data dictionary and includes the WITH READ ONLY clause, which prohibits DML through the view and reinforces its role as a query-only interface.

Underlying Base Objects

The view is defined over a single documented base object: MTL_SERIAL_NUMBERS, referenced through the synonym MSN. MTL_SERIAL_NUMBERS is the authoritative repository of every serialized unit tracked in Inventory, storing its current organization, subinventory, locator, revision, lot association, and transactional history markers. INVBV_SERIAL_NUMBERS is therefore a thin, denormalized projection of that table: it selects a defined column list, aliases several columns for readability, and adds two security constructs.

No joins to inventory items, organizations, or subinventories are present in the view text. As a result, the view returns surrogate identifiers (item ID, organization ID, locator ID) rather than descriptive names; consumers requiring item descriptions, organization codes, or subinventory names must join to MTL_SYSTEM_ITEMS_B, HR_OPERATING_UNITS or ORG_ORGANIZATION_DEFINITIONS, and MTL_SECONDARY_INVENTORIES respectively.

Key Columns

• SERIAL_NUMBER — The user-facing serial identifier for the unit.
• INVENTORY_ITEM_ID — Surrogate key to the item definition; join to MTL_SYSTEM_ITEMS_B for segment values.
• ORGANIZATION_ID — Sourced from CURRENT_ORGANIZATION_ID; identifies the organization currently holding the unit.
• UNIT_INITIALIZATION_DATE — Sourced from INITIALIZATION_DATE; the date the serial was first created.
• SUBINVENTORY_NAME — Sourced from CURRENT_SUBINVENTORY_CODE; the current subinventory.
• INVENTORY_LOCATOR_ID — Sourced from CURRENT_LOCATOR_ID; the current locator within the subinventory.
• REVISION, LOT_NUMBER — Revision and lot context for the serialized unit.
• _LA:SERIAL_NUMBER_STATUS — A descriptive flexfield-style lookup column resolving CURRENT_STATUS through MFG_LOOKUPS (lookup type SERIAL_NUM_STATUS, meaning column MEANING).
• RECEIPT_DATE, SHIP_DATE — Completion and shipment milestones.
• ORIGINAL_WIP_ENTITY_ID, ORIGINAL_UNIT_VENDOR_ID — Traceability to the originating work order or outside-processing vendor.
• VENDOR_SERIAL_NUMBER, VENDOR_LOT_NUMBER — Supplier-assigned identifiers.
• LAST_TRANSACTION_ID, LAST_RECEIPT_ISSUE_TYPE — Most recent transaction and its receipt/issue classification.
• END_ITEM_UNIT_NUMBER — End item unit designation for tracked assemblies.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — Standard audit columns.

Common Use Cases and Queries

Typical uses include serial genealogy reporting, cycle-count reconciliation, warranty and service lookups, and interfaces that extract serialized inventory snapshots for external systems. The organization security predicate means callers only see rows for organizations accessible to their responsibility.

List all serials in a given organization with descriptive item context:

• SELECT s.serial_number, s.organization_id, i.segment1 item, s.subinventory_name, s.revision, s.lot_number, s."_LA:SERIAL_NUMBER_STATUS" status, s.receipt_date, s.ship_date FROM apps.invbv_serial_numbers s, apps.mtl_system_items_b i WHERE s.inventory_item_id = i.inventory_item_id AND s.organization_id = i.organization_id AND s.organization_id = :p_org_id ORDER BY s.serial_number;

Identify serials never shipped (still on hand) for inventory valuation or aging review:

• SELECT serial_number, inventory_item_id, subinventory_name, inventory_locator_id, unit_initialization_date FROM apps.invbv_serial_numbers WHERE ship_date IS NULL AND organization_id = :p_org_id;

Trace supplier-received units and their last receipt or issue type:

• SELECT serial_number, vendor_serial_number, vendor_lot_number, last_receipt_issue_type, last_transaction_id FROM apps.invbv_serial_numbers WHERE original_unit_vendor_id IS NOT NULL;

Because the view is read-only and resolves the status lookup through a descriptive column, it is well suited to ad hoc inquiry and to feeder queries for custom reports, with joins applied against the base item and organization tables to supply human-readable descriptions.