Search Results serial_number1

Overview

IBE_RETURNABLE_SERIAL_V is a read-only Oracle EBS view owned by the APPS schema and validated across releases 12.1.1 and 12.2.2. It belongs to the IBE (iStore) product family and is documented as storing information about order lines together with the serial numbers of the ordered items. Its functional purpose is to expose, in a single flattened row set, the order-line attributes needed for return eligibility processing in the iStore/Order Management flow, joined to the serial numbers captured against those items through material transactions.

The view answers the core business question of whether a shipped, serialized item on a given order line can be returned. It does so by combining order header and line context, item master returnability attributes, unit-of-measure descriptions, and the serial number itself, while deriving a computed LINE_RETURNABLE_FLAG through nested DECODE logic. Because it is a view rather than a table, it carries no storage and no DML; it is a query surface for reporting, iStore return flows, and diagnostic SQL.

Underlying Base Objects

The documented base objects are:

• OE_ORDER_LINES_ALL (SYNONYM) — order line attributes: line/header IDs, quantities, pricing, dates, line category, flow status.
• OE_ORDER_HEADERS_ALL (SYNONYM) — header-level context: order number, org, currency, sold-to, order category, header flow status.
• MTL_SYSTEM_ITEMS_VL (VIEW) — item master details, including concatenated segments, description, ATTRIBUTE7 product description, and RETURNABLE_FLAG.
• MTL_SERIAL_NUMBERS (SYNONYM) — the serial number assigned to the item.
• MTL_MATERIAL_TRANSACTIONS (SYNONYM) — the shipping transaction linking the serialized item back to the order line.
• MTL_UNITS_OF_MEASURE_TL (SYNONYM) — translated unit-of-measure name.
• OE_PROFILE (PACKAGE) — resolves the OE_ORGANIZATION_ID profile value used to constrain the item master join.

Joins are driven from OE_ORDER_LINES_ALL to the header, to the item master (organization-resolved via OE_PROFILE.VALUE), and to MTL_MATERIAL_TRANSACTIONS, which in turn supplies the serial number from MTL_SERIAL_NUMBERS. Transaction filters restrict to source type 2 with transaction type 33 (shipment), and a DECODE on SERIAL_NUMBER_CONTROL_CODE constrains participation to serial-controlled items. A DISTINCT clause collapses duplicates produced by the transactional join path.

Key Columns

• LINE_ID / HEADER_ID / ORDER_NUMBER / LINE_NUMBER — the order-line identity used by callers such as iStore return pages.
• ORG_ID, SOLD_TO_ORG_ID, ORDER_CATEGORY_CODE, FLOW_STATUS_CODE — header context; flow status must not be ENTERED or CANCELLED for a line to be returnable.
• INVENTORY_ITEM_ID, ITEM_NUMBER, ITEM_DESCRIPTION, PRODUCT_DESCRIPTION, ITEM_TYPE_CODE — item identity and descriptive attributes.
• ORDERED_QUANTITY, SHIPPED_QUANTITY, CANCELLED_QUANTITY, ORDER_QUANTITY_UOM, UOM_NAME — fulfillment and quantity context.
• UNIT_LIST_PRICE, UNIT_SELLING_PRICE, SCHEDULE_SHIP_DATE, REQUEST_DATE, PROMISE_DATE — pricing and date references.
• SHIPMENT_NUMBER, OPTION_NUMBER, COMPONENT_NUMBER, SERVICE_NUMBER, TOP_MODEL_LINE_ID, LINK_TO_LINE_ID, ATO_LINE_ID — configure-to-order and shipment structure.
• SERIAL_NUMBER — the serialized identifier of the shipped unit; this is the column most often referenced when users search for a specific serial.
• LINE_RETURNABLE_FLAG — derived flag; returns 'Y' only when the line is not a top model, is shippable, the item is returnable, the item type is not SERVICE, the line category is not RETURN, and the header flow status is neither ENTERED nor CANCELLED.
• BOOKED_FLAG, CANCELLED_FLAG — line state indicators used in downstream filtering.

Common Use Cases and Queries

Typical uses include verifying that a specific serial number is associated with a returnable order line, driving iStore return eligibility checks, and auditing serialized shipments by order or item.

Locate a line by serial number (the classic "serial_number1" search):

• SELECT order_number, line_number, serial_number, line_returnable_flag
• FROM apps.ibe_returnable_serial_v
• WHERE serial_number = '&serial_number1';

List returnable serialized lines for a customer:

• SELECT order_number, line_number, item_number, serial_number
• FROM apps.ibe_returnable_serial_v
• WHERE sold_to_org_id = :p_customer_id
• AND line_returnable_flag = 'Y';

Audit shipped serials for an item within an org:

• SELECT order_number, shipped_quantity, serial_number
• FROM apps.ibe_returnable_serial_v
• WHERE org_id = :p_org_id
• AND inventory_item_id = :p_item_id
• ORDER BY schedule_ship_date DESC;

Because the view is DISTINCT and depends on a multi-table transactional join, performance-sensitive queries should filter on SERIAL_NUMBER, ORG_ID, ORDER_NUMBER, or INVENTORY_ITEM_ID to drive the underlying indexes on the base order and transaction tables.