Search Results edw_item_onetime_item_lcv

Overview

The EDW_ITEM_ONETIME_ITEM_LCV view is an Oracle E-Business Suite data warehouse object owned by the APPS schema and associated with the Engineering (ENG) product module. It presents a consolidated, analytics-ready list of item-level onetime items — that is, lines that carry descriptive information about a purchased or received item but that are not associated with a defined inventory item master record. Because these lines have no ITEM_ID, they never appear in the standard item dimension, and this view exists specifically to surface them for reporting and integration into an enterprise data warehouse (EDW) model.

The view is dimension-like in shape. It synthesizes a synthetic key, a category-set foreign key, descriptive attributes, and audit-style date aggregates into a single projection suitable for loading into a downstream EDW. In ETRM 12.1.1 and 12.2.2, it functions as a localized capture view (the _LCV suffix indicates a "local" collection view) that feeds the warehouse with records that would otherwise be orphaned from normal item hierarchies.

Underlying Base Objects

The view is defined over three principal base objects, combined through an inner-style join and a UNION of two query blocks:

• EDW_LOCAL_INSTANCE — Supplies the INSTANCE_CODE that identifies the originating environment, which is concatenated into identifiers.
• MTL_CATEGORIES_KFV — The key flexfield view over categories, providing CATEGORY_ID and CONCATENATED_SEGMENTS for the category description appended to the item description.
• PO_LINES_ALL — The first source of onetime data: purchasing lines where ITEM_ID IS NULL.
• RCV_SHIPMENT_LINES — The second source: receipt lines where ITEM_ID IS NULL, excluding any description/category combination already captured from PO_LINES_ALL via a NOT EXISTS subquery, preventing duplication.

Although ETRM's documented base-object list is empty, the embedded view text confirms these four tables/views. Both query blocks join category to the transaction source on CATEGORY_ID and then cross the result with EDW_LOCAL_INSTANCE.

Key Columns

• ITEM_NUMBER_PK — The synthetic primary key, built as ITEM_DESCRIPTION-CATEGORY_ID-ORG_ID-INSTANCE_CODE-ONETIME, uniquely identifying a onetime item occurrence.
• ITEM_NUMBER_DP — Display form of the number, combining the description with the concatenated category segments, truncated to 240 characters.
• NAME — A shortened (80-character) rendering of the description and category.
• CATSET_CATEGORY_FK — The category-set foreign key, formed as CATEGORY_ID-INSTANCE_CODE. This is the column users search for under the term catset_category_fk; it links the onetime item to its category set context for dimensional modeling.
• ONE_TIME_FLAG — Flag ('Y') marking the record as a onetime item.
• DESCRIPTION / ITEM_NAME — The descriptive text, defaulted to 'NA_EDW' when null.
• MIN(CREATION_DATE) / MAX(LAST_UPDATE_DATE) — Audit aggregates spanning the grouped records.
• PRODUCT_GROUP_FK — Defaulted to the literal 'NA_EDW'.

Common Use Cases and Queries

Typical applications include populating a warehouse item dimension for spend analysis, reconciling "no-item" purchasing or receiving activity, and tracing category linkage for unregistered items.

Retrieve onetime items for a specific category set:

SELECT item_number_pk, name, catset_category_fk, description
FROM   apps.edw_item_onetime_item_lcv
WHERE  catset_category_fk = :category_id || '-' || :instance_code;

Count onetime records by instance:

SELECT instance, COUNT(*) cnt
FROM   apps.edw_item_onetime_item_lcv
GROUP  BY instance;

Because the view performs string concatenation and DISTINCT aggregation across large transaction tables, queries should be filtered by category or instance to limit elapsed-time overhead.