Results for “end_cust_account_id”

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

Overview

IBE_ORDER_ITEMS_ADV_V is a denormalized reporting view owned by the APPS schema in Oracle E-Business Suite, catalogued under the IBE (iStore) product. Its status is VALID in both 12.1.1 and 12.2.2. The view provides a single-row-per-order-line projection of order information by joining the Order Management transaction tables (OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL) to inventory item master data, unit-of-measure translations, iStore pricing attributes, and Oracle Receivables customer account and site-use tables (HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL). It is intended for iStore order-status and order-history display pages, and for downstream reporting or integration extracts that need line-level order context without re-engineering the full OE_ORDER_LINES_ALL join graph.

Underlying Base Objects

The view is defined over the following documented base objects. Where a base object is quoted by a synonym, the view resolves it through the APPS synonym pointing at the underlying owner schema:

• OE_ORDER_LINES_ALL (SYNONYM) — order line attributes, quantities, pricing, and line relationships.
• OE_ORDER_HEADERS_ALL (SYNONYM) — header-level order number, category, currency, dates, and sold-to identifiers.
• MTL_SYSTEM_ITEMS_VL (VIEW) — item identifier, concatenated item number, and description.
• MTL_UNITS_OF_MEASURE_TL (SYNONYM) — UOM code to translatable UOM name mapping.
• OE_LOOKUPS (VIEW) — decode of FLOW_STATUS_CODE into a meaningful line-status MEANING.
• HZ_CUST_ACCT_SITES_ALL and HZ_CUST_SITE_USES_ALL (SYNONYMS) — customer account and site-use resolution for invoice-to, ship-to, and header-level sites.
• OE_PROFILE (PACKAGE) — schema package invoked in the WHERE clause via OE_PROFILE.VALUE('OE_ORGANIZATION_ID', OL.ORG_ID) to derive the correct inventory organization per line.

The joins enforce multi-org and language context: MSIT.LANGUAGE = USERENV('LANG') restricts the UOM name to the session language, and the OE_PROFILE call constrains MTL_SYSTEM_ITEMS_VL rows to the operating unit's inventory organization.

Key Columns

• LICENSING_UNIT — mapped to MSI.SEGMENT6 from MTL_SYSTEM_ITEMS_VL. Because it reads a fixed segment position of the item's key flexfield, it reflects whatever the implementation has assigned to segment 6 (for example license tier, subcategory, or a product-family attribute). It is not a native Order Management column and depends entirely on key flexfield configuration.
• ITEM_NUMBER (MSI.CONCATENATED_SEGMENTS) and ITEM_DESCRIPTION — human-readable item identity.
• ORDER_NUMBER, LINE_NUMBER, SHIPMENT_NUMBER, OPTION_NUMBER, COMPONENT_NUMBER, SERVICE_NUMBER — full line-hierarchy addressing for ATO, configured, and service lines.
• ORDERED_QUANTITY, SHIPPED_QUANTITY, CANCELLED_QUANTITY, ORDER_QUANTITY_UOM, UOM_NAME — quantity and unit information.
• UNIT_SELLING_PRICE, UNIT_LIST_PRICE, TRANSACTIONAL_CURR_CODE, PRICING_ATTRIBUTE10 — pricing context.
• ORDERED_DATE, BOOKED_DATE, SCHEDULE_SHIP_DATE, REQUEST_DATE, PROMISE_DATE — order and fulfillment timeline.
• FLOW_STATUS_CODE and LINE_STATUS — raw status code plus decoded lookup meaning.
• CUST_ACCOUNT_ID and CUST_ACCT_SITE_ID variants for header invoice, invoice, and ship-to sites; SOLD_TO_ORG_ID and END_CUSTOMER_ID.
• ORG_ID — operating unit, for multi-org filtering and security.

Common Use Cases and Queries

Typical uses include iStore order-history pages, license-entitlement reporting driven by LICENSING_UNIT, and integration extracts that feed fulfillment or entitlement systems. Always constrain ORG_ID to the operating unit, since the view does not enforce row-level security by itself.

Order lines by licensing unit:

• SELECT order_number, line_number, item_number, licensing_unit, ordered_quantity, order_quantity_uom FROM ibe_order_items_adv_v WHERE org_id = :p_org_id AND licensing_unit = :p_licensing_unit AND booked_date >= :p_from_date ORDER BY order_number, line_number;

Line detail with customer and fulfillment status (illustrating the HZ_CUST_ACCT_SITES_ALL/HZ_CUST_SITE_USES_ALL joins):

• SELECT oh_order_number AS order_number, line_number, item_number, item_description, ordered_quantity, shipped_quantity, uom_name, line_status, promise_date FROM ibe_order_items_adv_v WHERE sold_to_org_id = :p_customer_id AND org_id = :p_org_id;

Because the view aggregates several tables and calls a PL/SQL function (OE_PROFILE.VALUE) in its predicate, performance can degrade on large extracts. Where possible, filter on ORG_ID and booked_date to bound the result set, and consider joining the base OE tables directly for high-volume batch reporting.