Results for “qpfv_item_upgrades”

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

Overview

QPFV_ITEM_UPGRADES is an APPS-owned, read-only view in the Oracle Advanced Pricing (QP) module. It presents a filtered, reporting-oriented projection of pricing modifier data specifically for the Item Upgrades modifier type. In Oracle Advanced Pricing, "Item Upgrade" modifiers define rules that replace or substitute one item with another — for example, prompting a customer to move from an older product version to a newer one, or substituting a related item at a favorable price. The view exposes these rules with their eligibility dates, pricing phase, precedence, organization context, and — most importantly for the user's search — the RELATED_ITEM_ID, which identifies the item that the source inventory item is upgraded to.

Because the view is declared WITH READ ONLY and shipped in the APPS schema (status VALID), it is intended strictly for query, reporting, and integration consumption rather than for DML. It provides a stable, semantically narrowed interface that lets report writers and interface developers retrieve item-upgrade modifiers without having to re-implement the underlying LIST_LINE_TYPE_CODE = 'IUE' filter or decode the multilingual (_LA) and descriptive flexfield (_DF) columns manually.

Underlying Base Objects

The view is defined over a single documented base object, QP_LIST_LINES, referenced through a synonym. QP_LIST_LINES is the core Advanced Pricing table that stores all modifier list lines — discounts, surcharges, price breaks, and item upgrades alike — keyed by LIST_HEADER_ID and LIST_LINE_ID. The view restricts that population with the predicate LIST_LINE_TYPE_CODE = 'IUE', isolating the Item Upgrade (IUE) line type.

Although the ETRM metadata documents only QP_LIST_LINES as a base object, the view's select list references lookup and flexfield metadata tokens (for example QP_LOOKUPS:INCOMPATIBILITY_GROUPS, QP_LOOKUPS:LIST_LINE_TYPE_CODE, QP_LOOKUPS:MODIFIER_LEVEL_CODE, and QP_LIST_LINES). These tokens translate the stored codes into their display meanings at query time and expose the descriptive flexfield context, so downstream consumers receive human-readable results without additional joins.

Key Columns

• RELATED_ITEM_ID — The item to which the current inventory item is upgraded or related. This is the column the user searched for and the semantic heart of the view.
• INVENTORY_ITEM_ID — The source item being upgraded, in the context of ORGANIZATION_ID.
• ORGANIZATION_ID — The inventory organization in which the upgrade rule applies.
• RELATIONSHIP_TYPE_ID — Classifies the nature of the relationship between the source and related items.
• LIST_HEADER_ID / LIST_LINE_ID / LIST_LINE_NO — Identifiers linking the line to its parent price list and its position within it.
• LIST_LINE_TYPE_CODE — The modifier type; always 'IUE' in this view, with a lookup-based meaning column.
• START_DATE_ACTIVE / END_DATE_ACTIVE — Effective date range of the upgrade rule.
• PRICING_PHASE_ID / PRODUCT_PRECEDENCE — Control when the rule fires and how competing modifiers are prioritized.
• MODIFIER_LEVEL_CODE — Indicates the level at which the modifier is defined (line, group, etc.).
• ESTIM_GL_VALUE — Estimated general ledger value associated with the modifier.
• INCOMPATIBILITY_GRP_CODE — Group code preventing incompatible modifiers from co-applying.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY for traceability.

Common Use Cases and Queries

Typical scenarios include reporting all upgrade paths defined in a price list, validating that an item has a designated replacement, and feeding an integration that resolves a customer's item to its upgraded successor during order entry. The following query lists upgrade relationships for a given organization, resolving the related item:

SELECT v.list_header_id,
       v.list_line_no,
       v.inventory_item_id,
       v.related_item_id,
       v.organization_id,
       v.start_date_active,
       v.end_date_active
FROM   apps.qpfv_item_upgrades v
WHERE  v.organization_id = :org_id
AND    SYSDATE BETWEEN NVL(v.start_date_active, SYSDATE)
                   AND NVL(v.end_date_active, SYSDATE + 1);

To find the upgrade target for a specific item — the primary reason users search on related_item_id — a join to the item master resolves both ends of the relationship:

SELECT msi.segment1  source_item,
       tgt.segment1  upgraded_to_item,
       v.list_header_id
FROM   apps.qpfv_item_upgrades v,
       apps.mtl_system_items_b msi,
       apps.mtl_system_items_b tgt
WHERE  v.inventory_item_id = msi.inventory_item_id
AND    v.organization_id   = msi.organization_id
AND    v.related_item_id   = tgt.inventory_item_id
AND    v.organization_id   = tgt.organization_id;

Because the view is read-only and pre-filtered to 'IUE' lines, these queries avoid accidental inclusion of non-upgrade modifiers and require no manual lookup decoding.