Search Results sr_supplier_id

Overview

ISC_DBI_PM_0000_MV is an Oracle EBS materialized view owned by the APPS schema and registered in FND Design Data as ISC.ISC_DBI_PM_0000_MV. It resides in the APPS_TS_SUMMARY tablespace, the storage area typically reserved for pre-aggregated summary objects, and holds VALID status across both 12.1.1 and 12.2.2 releases. Functionally, the object serves as a reporting and analytics layer within the Oracle Supply Chain Intelligence (ISC) product family, materializing procurement spend, purchasing cost, and demand-planning data at a combination of snapshot, organization, supplier, item, category, group, and time dimensions. Its name prefix (DBI_PM) indicates a Daily Business Intelligence Procurement materialized view, refreshed on a periodic schedule rather than queried directly against transactional tables.

From a Data Vault modeling perspective, the heuristic classification inferred from the foreign-key structure is standalone. No parent foreign keys were mined into hub-type structures, and the only documented FK link is a snapshot reference to AHL_SNAPSHOTS. This suggests the object behaves more like an aggregated report snapshot than a normalized hub, link, or satellite construct. The nearest conceptual analogue is a periodic snapshot satellite keyed by a composite of time, organization, and item dimensions.

Key Information Stored

The materialized view contains 16 columns. The most significant are the dimensional keys and the additive measures that support procurement analytics:

• SNAPSHOT_ID — Plan snapshot identifier and the only column with a documented foreign-key relationship (to AHL_SNAPSHOTS). It anchors every row to a refresh cycle.
• ORGANIZATION_ID — Inventory organization identifier, the primary operational scope dimension.
• SR_SUPPLIER_ID — Supplier identifier for spend-by-vendor analysis.
• ITEM_ID — Stored as VARCHAR2(81), the item organization identifier.
• ITEM_CATEGORY_ID — Inventory category identifier used for category-level roll-ups.
• GRP_ID and ITEM_CAT_FLAG — Grouping ID columns. ITEM_CAT_FLAG is the column matching the user's search term; it functions as a grouping discriminator for item-level versus category-level aggregation and participates in the lead unique index.
• START_DATE and PERIOD_TYPE_ID — Nested time dimensions controlling period alignment and rolling-window calculations.
• PURCHASING_COST, PURCHASING_COST_G, and PURCHASING_COST_G1 — Purchasing cost expressed in functional, global, and secondary global currencies respectively.
• PURCHASING_COST_CNT, PUR_COST_G_CNT, PUR_COST_G1_CNT, and CNT — Corresponding count columns supporting weighted averages and row-frequency analysis.

The surrogate/business key is the function-based unique index I_SNAP$_ISC_DBI_PM_0000_MV, which covers SNAPSHOT_ID, ORGANIZATION_ID, SR_SUPPLIER_ID, START_DATE, PERIOD_TYPE_ID, ITEM_CATEGORY_ID, ITEM_ID, GRP_ID, and ITEM_CAT_FLAG using SYS_OP_MAP_NONNULL to treat nulls as values. This composite is the effective business-key candidate for the summary grain.

Common Use Cases and Queries

Typical uses include spend dashboards, supplier scorecards, and category-level purchasing trend reports. A common pattern filters on the latest snapshot and a period type:

• Supplier spend ranking by organization: aggregate PURCHASING_COST grouped by SR_SUPPLIER_ID for a given SNAPSHOT_ID and ORGANIZATION_ID.
• Category trend analysis: sum PURCHASING_COST by ITEM_CATEGORY_ID across START_DATE for a PERIOD_TYPE_ID.
• Currency-normalized reporting: compare PURCHASING_COST, PURCHASING_COST_G, and PURCHASING_COST_G1.
• Row-count validation: use CNT and the *_CNT columns to verify aggregation completeness and detect missing supplier-item combinations.

Queries should always restrict SNAPSHOT_ID to avoid double-counting across refresh cycles. The nonunique indexes _N1, _N2, and _N3 lead with START_DATE, SNAPSHOT_ID, and PERIOD_TYPE_ID, so filtering on those columns first yields the best plan.

Related Objects

• AHL_SNAPSHOTS — parent table; join on SNAPSHOT_ID to resolve snapshot name, refresh date, and plan metadata.
• ISC_DBI_PM_0000_MV's indexes I_SNAP$_ISC_DBI_PM_0000_MV, ISC_DBI_PM_0000_MV_N1/_N2/_N3 — supporting access paths.
• MTL_SYSTEM_ITEMS_B — join on ITEM_ID to obtain descriptions and item attributes.
• MTL_CATEGORIES_B and MTL_CATEGORY_SETS_B — join on ITEM_CATEGORY_ID for category naming.
• HR_ALL_ORGANIZATION_UNITS or ORG_ORGANIZATION_DEFINITIONS — join on ORGANIZATION_ID for organization descriptions.
• PO_VENDORS/AP_SUPPLIERS — join on SR_SUPPLIER_ID for supplier detail.
• BI Publisher / OBIEE ISC dashboards that consume this materialized view directly.