Results for “poa_api_002_mv”

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

Overview

POA_API_002_MV is an APPS-owned materialized view registered as a table within the FND – Application Object Library product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the Procurement/Procurement Analytics (POA) schema lineage associated with spend and procurement activity analysis. The object exposes aggregated purchasing transaction data across organizational, supplier, commodity, and cost-center dimensions, and is typically consumed by Oracle Procurement Analytics dashboards, spend classification reports, and custom BI extracts.

From a Data Vault modeling perspective, the mined foreign keys and structure suggest this object behaves primarily as a link table, joining business hubs such as suppliers, commodities, companies, cost centers, and time periods. It carries additive measures (AMOUNT_B, AMOUNT_G, AMOUNT_SG and their currency-adjusted counterparts), which is characteristic of a link-satellite hybrid rather than a pure hub. The classification is heuristic and should be treated as a modeling suggestion rather than a documented architectural fact.

Key Information Stored

The table contains 20 documented columns. The most significant include:

• Dimension keys: COMPANY_ID, PARENT_COMPANY_ID, COST_CENTER_ID, PARENT_CC_ID — organizational hierarchy for spend roll-up.
• Supplier attribution: SUPPLIER_ID — identifies the vendor associated with the aggregated amount.
• Commodity and item: COMMODITY_ID, CATEGORY_ID, PO_ITEM_ID — classify purchased goods and services.
• Unit of measure: BASE_UOM — supports normalized quantity and amount analysis.
• Time dimension: TIME_ID — the period key for trend and period-over-period reporting.
• Grouping: ORG_ID, GRP_ID, AGGREGATION_LEVEL — operational unit and level of aggregation.
• Measures: AMOUNT_B, AMOUNT_G, AMOUNT_SG and C_AMOUNT_B, C_AMOUNT_G, C_AMOUNT_SG (currency-converted variants), plus C_TOTAL for the rolled-up total.

There is no documented single surrogate primary key column. Instead, business-key uniqueness is enforced through the unique index I_SNAP$_POA_API_002_MV, which is defined over the combined set of PARENT_COMPANY_ID, PARENT_CC_ID, COMPANY_ID, COST_CENTER_ID, TIME_ID, COMMODITY_ID, CATEGORY_ID, PO_ITEM_ID, BASE_UOM, ORG_ID, SUPPLIER_ID, GRP_ID, and AGGREGATION_LEVEL using SYS_OP_MAP_NONNULL to handle nulls — a pattern typical of Oracle's automatic materialized view refresh logs.

Common Use Cases and Queries

Typical scenarios include spend analysis by supplier, commodity trend reporting, and cost-center accountability dashboards. A representative query pattern:

• Join POA_API_002_MV to PO_COMMODITIES_B on COMMODITY_ID to resolve commodity descriptions.
• Join to PN_COMPANIES_ALL on COMPANY_ID to resolve company names.
• Filter on TIME_ID ranges and AGGREGATION_LEVEL to control granularity.
• Aggregate C_TOTAL or C_AMOUNT_SG by SUPPLIER_ID for top-vendor spend rankings.
• Compare AMOUNT_B against C_AMOUNT_B to evaluate currency conversion impact.

Because it is a materialized view, refresh timing affects freshness; scheduled full refreshes are common in 12.1.1, while 12.2.2 environments may use incremental refresh depending on the underlying log configuration.

Related Objects

• PO_COMMODITIES_B — referenced via POA_API_002_MV.COMMODITY_ID → PO_COMMODITIES_B.COMMODITY_ID.
• PN_COMPANIES_ALL — referenced via POA_API_002_MV.COMPANY_ID → PN_COMPANIES_ALL.COMPANY_ID.
• PO_ITEMS / PO_LINES_ALL — source lineage for PO_ITEM_ID and aggregation measures.
• PO_VENDORS / PO_SUPPLIERS — lineage for SUPPLIER_ID.
• FND_ID_FLEX_STRUCTURES and related FND key-flex objects — contextualize CATEGORY_ID and ORG_ID.
• GL_PERIODS / FND_CALENDARS — resolve TIME_ID to accounting periods.
• DBMS_MVIEW / DBMS_SNAP_INTERNAL — refresh and logging infrastructure governing the MV.