Search Results xni_level_3_rev_v

Overview

XNI_LEVEL_3_REV_V is a reporting view historically shipped within the Oracle E-Business Suite Install Base Intelligence (XNI) product family. In EBS 12.1.1 and 12.2.2 the XNI module is classified as obsolete, and the ETRM metadata confirms the view is "Not implemented in this database." Consequently, the object exists only as documentation of a legacy design pattern rather than as a queryable database entity in a current installation. Its purpose, as reflected in the view text, was to flatten install base instance records against their originating order line, order header, and inventory item definition so that revenue and product attribution reporting could be executed without joining the commerce and install base schemas manually.

The view was intended to expose one row per installed instance, denormalized with commercial (order) and descriptive (product) attributes. A FIRST_ROWS hint in the defining query indicates it was optimized for interactive, page-at-a-time reporting rather than bulk extraction.

Underlying Base Objects

ETRM does not document referenced base objects separately, but the embedded view text identifies the four tables joined in the definition:

• CSI_ITEM_INSTANCES (alias A) — the install base instance record, providing instance identity, item reference, creation date, owner account, last order line reference, quantity, and instance status.
• OE_ORDER_LINES_ALL (alias B) — the order line supplying the unit selling price and the header foreign key.
• OE_ORDER_HEADERS_ALL (alias C) — the order header supplying order number, order currency, and operating unit.
• MTL_SYSTEM_ITEMS_B (alias D) — the item master supplying the product number, product name, description, and item organization.

The joins are: instance to item on INVENTORY_ITEM_ID, instance to order line on LAST_OE_ORDER_LINE_ID (outer join), order line to header on HEADER_ID (outer join), and order line to item master on INVENTORY_ITEM_ID. The outer joins on the order tables allow instances without a surviving order line or header to remain in the result set. A filter excludes instances whose status is 1 or 5. Because both CSI and OE objects are multi-organization, the joins should be constrained on ORGANIZATION_ID in any reimplementation.

Key Columns

• INSTANCE_ID — primary identifier of the install base instance; unique grain of the view.
• INVENTORY_ITEM_ID / MTL_ORGANIZATION_ID — item and item-master organization keys used to join to MTL_SYSTEM_ITEMS_B.
• PRODUCT_NUMBER — sourced from MTL_SYSTEM_ITEMS_B.SEGMENT1. This is the column most often sought under the search term "product_number."
• PRODUCT_NAME — sourced from SEGMENT2; DESCRIPTION carries the long item description.
• ORDER_LINE_ID / ORDER_HEADER_ID / ORDER_NUMBER — traceability from the installed instance back to the originating sales order.
• TRANS_CUR_CODE / ORG_ID — transactional currency and operating unit context.
• OWNER_PARTY_ACCOUNT_ID / CREATION_DATE — ownership and instance creation attributes used for customer and aging reports.
• AMOUNT — computed as ROUND((QUANTITY * UNIT_SELLING_PRICE)/11000000, 2), a derived revenue measure scaled by a hard-coded constant that reflects a legacy currency conversion assumption.

Common Use Cases and Queries

Reconstructed usage centers on install base revenue attribution and product-level installed asset reporting. A representative query, adjusted for organization partitioning, would resemble:

• SELECT instance_id, product_number, product_name, order_number, trans_cur_code, org_id, amount FROM xni_level_3_rev_v WHERE product_number = :product_number;
• Install base by customer: SELECT owner_party_account_id, COUNT(*) inst_count, SUM(amount) FROM xni_level_3_rev_v GROUP BY owner_party_account_id;
• Product adoption trending: SELECT product_number, TRUNC(creation_date, 'MM') month, COUNT(*) FROM xni_level_3_rev_v GROUP BY product_number, TRUNC(creation_date,'MM') ORDER BY 2;
• Order-to-install reconciliation: SELECT order_number, instance_id FROM xni_level_3_rev_v WHERE order_number IS NOT NULL ORDER BY order_number;

Because the view is obsolete and not implemented, these patterns should not be run against a production 12.1.1 or 12.2.2 instance. Equivalent reporting must be built directly on CSI_ITEM_INSTANCES joined to OE_ORDER_LINES_ALL, OE_ORDER_HEADERS_ALL, and MTL_SYSTEM_ITEMS_B, applying the same status exclusion and validating the currency normalization factor against current business rules.