Results for “amount_sg”

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

Overview

POA_API_001_MV is an APPS-owned materialized view (implemented as a table object of type TABLE in the EBS data dictionary) that serves as a pre-aggregated analytical summary of procurement spend data. It is documented under the FND — Application Object Library product, but its column structure and foreign key targets place it squarely in the Oracle Procurement Analytics / spend analysis domain, where "POA" conventionally denotes Procurement Online Analytical processing. The "API" and "001" tokens identify it as one member of a numbered family of staging or summary materialized views, and the "_MV" suffix confirms it is refreshed rather than transactionally maintained. In Oracle EBS 12.1.1 and 12.2.2, such objects are typically refreshed by concurrent programs against underlying PO_ and PN_ base tables, providing denormalized, indexed snapshots that reporting tools can query without traversing the normalized transactional schema.

The ETRM metadata classifies this object heuristically as standalone in the Data Vault sense — that is, neither a pure hub, link, nor satellite. The classification is a modeling suggestion only: the object behaves like a combined hub-plus-satellite aggregate, since it carries both dimension-like foreign key columns (supplier, commodity, category, company, cost center, org, item) and numeric measure columns (AMOUNT_B, AMOUNT_G, AMOUNT_SG and their committed counterparts). No foreign keys are declared outbound from this MV to any parent hub in the Data Vault sense beyond the two documented references.

Key Information Stored

The view exposes 22 columns. Its business-key candidate set is defined by the unique index I_SNAP$_POA_API_001_MV, which applies SYS_OP_MAP_NONNULL to each key column — a technique that allows null-valued dimension members to participate in the uniqueness constraint. The documented unique index spans COMPANY_ID, COST_CENTER_ID, COMMODITY_ID, CATEGORY_ID, SUPPLIER_ID, ORG_ID, PO_ITEM_ID, BASE_UOM, ENT_PERIOD_ID, WEEK_ID, DAY_ID, ENT_QTR_ID, GRP_ID, and AGGREGATION_LEVEL.

There is no single surrogate primary key documented for this object; uniqueness is instead enforced over the composite of all dimension and time-grain columns together with AGGREGATION_LEVEL, which discriminates the granularity at which each row was summarized. The most important columns fall into three groups:

• Dimension keys: COMPANY_ID (references PN_COMPANIES_ALL), COMMODITY_ID (references PO_COMMODITIES_B), plus CATEGORY_ID, SUPPLIER_ID, ORG_ID, COST_CENTER_ID, and PO_ITEM_ID, all of which identify the procurement context of the summarized amount.
• Time and grain: TIME_ID, ENT_QTR_ID, ENT_PERIOD_ID, WEEK_ID, and DAY_ID define the enterprise calendar hierarchy, while GRP_ID and AGGREGATION_LEVEL control which rollup the row represents.
• Measures: AMOUNT_B, AMOUNT_G, AMOUNT_SG, C_AMOUNT_B, C_AMOUNT_G, C_AMOUNT_SG, and C_TOTAL hold the aggregated monetary figures — base, grouped, and sub-grouped amounts alongside their committed (C_) equivalents and a consolidated total.

Common Use Cases and Queries

Because the view pre-joins commodities and companies and pre-aggregates amounts by calendar grain, it is the natural source for spend-analysis dashboards, supplier scorecards, and period-over-period commodity comparisons. Typical queries filter AGGREGATION_LEVEL to a single value to avoid double-counting across rollups, and constrain ENT_PERIOD_ID or ENT_QTR_ID to a fiscal window.

• Spend by supplier for a quarter: SELECT SUPPLIER_ID, SUM(C_TOTAL) FROM POA_API_001_MV WHERE ENT_QTR_ID = :qtr AND AGGREGATION_LEVEL = :lvl GROUP BY SUPPLIER_ID;
• Commodity trend across periods: join COMMODITY_ID to PO_COMMODITIES_B and group by ENT_PERIOD_ID.
• Company/cost-center variance using C_AMOUNT_B versus AMOUNT_B.
• Item-level drill-down filtered by PO_ITEM_ID and BASE_UOM.

Related Objects

The documented foreign key relationships anchor this MV to two base tables:

• PO_COMMODITIES_B — joined on COMMODITY_ID; supplies commodity code and description.
• PN_COMPANIES_ALL — joined on COMPANY_ID; supplies legal entity names and attributes.

Beyond these, POA_API_001_MV logically depends on the standard Procurement tables supplying its dimension keys — PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, and PO_DISTRIBUTIONS_ALL — which populate SUPPLIER_ID, PO_ITEM_ID, ORG_ID, and the amount measures. It is also a peer of the other POA_API_###_MV members, which share the same refresh cycle and are typically queried together in Procurement Analytics reports.