Results for “edw_item_onetime_itemorg_lcv”

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

Overview

EDW_ITEM_ONETIME_ITEMORG_LCV is an Engineering (ENG) module view in Oracle E-Business Suite that surfaces item organization level information for "onetime" items. A onetime item is an item referenced on a purchasing document that has not been defined in the master item table (MTL_SYSTEM_ITEMS_B); Oracle Purchasing permits such ad hoc items on purchase order lines, and this view reconstructs an identity for them by synthesizing a key from the item description, category, organization, and local instance.

In the Oracle EBS 12.1.1 and 12.2.2 architecture, the "EDW" prefix indicates an Enterprise Data Warehouse extract view, used by the Oracle Business Intelligence and ETRM/EDW reporting layers to normalize operational data into a star-schema-friendly shape. The "_LCV" suffix conventionally denotes a derived or "logical" collection view. The view therefore functions as an integration and reporting artifact rather than a transactional object; the ETRM documentation explicitly notes it is "Not implemented in this database" for the referenced environment, meaning the definition is shipped with the product but the view may be absent or disabled in a given instance.

Underlying Base Objects

The documented view text is defined over three base objects joined in a UNION query:

• MTL_CATEGORIES_KFV — the key-flexfield category view supplying CATEGORY_ID and CONCATENATED_SEGMENTS for the category assignment.
• PO_LINES_ALL — the purchasing order lines table, which is the source of the onetime item description, ORG_ID, CREATION_DATE, and LAST_UPDATE_DATE. The filter L.ITEM_ID IS NULL isolates lines with no defined master item, precisely the onetime population.
• EDW_LOCAL_INSTANCE — an EDW-local helper table providing INSTANCE_CODE for the local EBS instance, used to disambiguate records across environments.

The two branches of the UNION distinguish lines identified by ORG_ID from those identified by FROM_ORGANIZATION_ID, ensuring both purchasing and internal requisition-style references are captured. Although no base objects are formally registered for this view in the ETRM metadata, these three tables constitute its documented sources.

Key Columns

• ITEM_NUMBER_FK / ITEM_DESCRIPTION key — a synthesized string concatenating description, category, organization, instance code, and the literal "-ONETIME".
• INSTANCE_CODE — identifies the source EBS instance.
• CATSET_CATEGORY_FK — category and instance combination used as the category-set foreign key.
• DESCRIPTION — the onetime item description from the purchase line.
• ORG_ID — the organization in which the onetime item was referenced.
• PRICE_TOLERANCE_PERCENT — exposed as NULL in this view. The search term "price_tolerance_percent" is a column carried for schema compatibility with the master item extract, but no value is populated because onetime items have no purchasing attribute record.
• MAKE_OR_BUY_FLAG, LIST_UNIT_PRICE, SHELF_LIFE_DAYS, SERIAL_CONTROL and the remaining attribute columns — all NULL for the same reason.
• MIN(CREATION_DATE) / MAX(LAST_UPDATE_DATE) — aggregate dates summarizing the onetime item's appearance span.

Common Use Cases and Queries

The view is used to report procurement spend against items lacking master definitions and to reconcile onetime purchases into the EDW item dimension. A representative query follows:

• SELECT ITEM_NUMBER_FK, DESCRIPTION, ORG_ID, INSTANCE_CODE FROM EDW_ITEM_ONETIME_ITEMORG_LCV WHERE ORG_ID = :org;
• SELECT ORG_ID, COUNT(*) FROM EDW_ITEM_ONETIME_ITEMORG_LCV GROUP BY ORG_ID;
• SELECT ITEM_NUMBER_FK, PRICE_TOLERANCE_PERCENT FROM EDW_ITEM_ONETIME_ITEMORG_LCV; — note this returns NULL, confirming that tolerance data is not available for onetime items and must be sourced from the master item extract for standard items.