Search Results dim_pk




Overview
Explore in AI Chat

Overview

EDW_POA_LN_TYPE_MPV is a dictionary-defined view owned by the APPS schema in Oracle E-Business Suite, classified under the Purchasing (PO) product family. Its documented description, "EDW Line Type Mapping View," identifies it as a data-extraction artifact belonging to the Oracle Enterprise Data Warehouse (EDW) layer that Oracle ships with EBS rather than a functional Purchasing form or concurrent program object. The suffix convention distinguishes the role of the object: "MPV" denotes a materialized or mapped public view used for dimensional extraction, while the paired object in the definition carries the "LCV" suffix, indicating a line-level collection view.

In EBS 12.1.1 and 12.2.2 the object remains a lightweight pass-through view. It exposes a narrow projection of purchasing line-type reference data intended for consumption by external reporting, warehouse loading, and integration routines that require a stable, denormalized identifier for line types. Because the view is stored in the APPS schema and marked VALID, it is accessible to any responsibility or database user granted SELECT on APPS objects, which makes it a convenient, low-risk source for ETL jobs that must not query transactional purchasing tables directly.

Underlying Base Objects

The view text documented in ETRM is:

This establishes a single-level dependency: EDW_POA_LN_TYPE_MPV reads only from EDW_POA_LN_TYPE_LCV, another APPS-owned view in the same EDW family. The documented metadata lists no further referenced base objects, meaning the chain resolves through the LCV object to the underlying Purchasing lookup tables (typically PO_LINE_TYPES_B and its translations), though those deeper dependencies are not enumerated in the ETRM record for this view. Because the MPV layer selects from the LCV layer and not from base tables directly, the object functions as a stable public interface: Oracle can revise the LCV internals without altering the column contract presented by the MPV.

Key Columns

The view projects two columns as documented in the view text, and the ETRM metadata additionally records dimensional columns expected by the warehouse model:

  • LINE_TYPE_PK — The surrogate primary key for a purchasing line type. This is the column most frequently located by the search term "line_type_pk" and is the join key used to associate line-type reference data with purchasing documents in downstream extracts.
  • LAST_UPDATE_DATE — The standard EBS audit column indicating when the source row was last modified. It supports incremental (delta) extraction, allowing ETL processes to pull only records changed since the previous load.
  • DIM_PK — The dimensional primary key defined at the warehouse layer, used to link the line-type record into the EDW dimensional model.
  • DIM_PK_DATE — The date component associated with the dimensional key, supporting type-2 or effective-dated dimensional joins.

Common Use Cases and Queries

The view is chiefly consumed by data-warehouse and BI processes that need line-type identifiers and their change timestamps without touching transactional tables. Typical uses include resolving a line-type surrogate key during fact-table construction, populating a line-type dimension, and performing incremental refreshes filtered on the audit date.

  • Retrieve all line-type keys for a full dimension load:
    SELECT line_type_pk, last_update_date FROM apps.edw_poa_ln_type_mpv;
  • Extract only records changed since the last run:
    SELECT line_type_pk, last_update_date FROM apps.edw_poa_ln_type_mpv WHERE last_update_date >= :last_run_date;
  • Join the mapped key to a purchasing line extract:
    SELECT h.segment1, l.line_num, t.line_type_pk FROM po_headers_all h, po_lines_all l, apps.edw_poa_ln_type_mpv t WHERE h.po_header_id = l.po_header_id AND l.line_type_id = t.line_type_pk;

Because the object is a view over another view, queries carry no materialization benefit; performance depends entirely on the underlying LCV definition and on indexing in the source Purchasing tables.