Results for “eni_dbi_co_union_mv”

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

Overview

ENI_DBI_CO_UNION_MV is a materialized view owned by the APPS schema within the ENI — Product Intelligence product family of Oracle E-Business Suite. It functions as a denormalized analytics data object that consolidates engineering change order (ECO) transactional activity across organizational and calendar dimensions. Because it is a materialized view rather than a base transactional table, it is refreshed periodically from underlying change order, inventory, and calendar sources to support aggregated reporting in the Daily Business Intelligence (DBI) framework without imposing query load on OLTP tables.

The object is registered as VALID and exposes 37 documented columns in the 12.1.1 ETRM physical schema. Its design is characteristic of a dimensional aggregate: a composite of foreign-key surrogate references (organization, item, change order type, calendar periods) combined with additive measures such as counts and sums. The heuristic Data Vault classification supplied in the metadata is standalone, indicating no classic hub/link/satellite decomposition was detected. From a modeling perspective, this suggests the object should be treated as a self-contained summary fact rather than as part of a normalized Data Vault structure. Two referenced foreign keys are documented — CHANGE_ORDER_TYPE_ID to ENG_CHANGE_ORDER_TYPES and YEAR_ID to JAI_FA_AST_YEARS — confirming the ECO type and fiscal-year dimensions as the principal conformed relationships.

Key Information Stored

The most significant columns cluster into dimension keys, measures, and bucket metrics:

• ORGANIZATION_ID, ITEM_ID, ITEM_ORG_ID — inventory organization and item grain, anchoring each row to a specific item within an operating unit.
• CHANGE_ORDER_TYPE_ID — foreign key to ENG_CHANGE_ORDER_TYPES, classifying the ECO type (e.g., engineering, manufacturing).
• REASON_CODE, PRIORITY_CODE, STATUS_TYPE, SOURCE_FLAG — categorical attributes qualifying the change order population.
• TIME_ID, PERIOD_TYPE_ID, YEAR_ID, QTR_ID, MONTH_ID, WEEK_ID, DAY_ID — the calendar hierarchy; YEAR_ID references JAI_FA_AST_YEARS for fiscal alignment.
• CNT — the primary row-count measure of change orders at the defined grain.
• IMPLEMENTED_SUM / IMPLEMENTED_CNT, CANCELLED_SUM / CANCELLED_CNT, NEW_SUM / NEW_CNT — lifecycle-state aggregates partitioned by outcome.
• CYCLE_TIME_SUM / CYCLE_TIME_CNT, APPROVE_TO_IMPL_SUM / APPROVE_TO_IMPL_CNT, CREATE_TO_APPROVE_SUM / CREATE_TO_APPROVE_CNT — duration accumulators supporting average cycle-time computation.
• BUCKET1_SUM through BUCKET4_CNT — configurable aging or threshold buckets for distribution analysis.
• GROUPING_ID (ENT_YEAR_...) — a grouping identifier supporting rollup-aware aggregation.

No explicit single-column surrogate primary key is documented; the effective grain is the composite of the dimension columns. Sum/Count pairs imply each measure is designed so averages can be recomputed as SUM ÷ CNT at query time.

Common Use Cases and Queries

Typical usage centers on ECO throughput, cycle-time, and status-distribution reporting. Analysts aggregate the sum/count pairs to derive averages at any calendar level:

• Average cycle time by organization and month: SELECT ORGANIZATION_ID, MONTH_ID, SUM(CYCLE_TIME_SUM)/NULLIF(SUM(CYCLE_TIME_CNT),0) FROM ENI_DBI_CO_UNION_MV GROUP BY ORGANIZATION_ID, MONTH_ID;
• ECO volume by type: join CHANGE_ORDER_TYPE_ID to ENG_CHANGE_ORDER_TYPES and aggregate SUM(CNT).
• Implementation vs. cancellation rates: compare SUM(IMPLEMENTED_CNT) against SUM(CANCELLED_CNT) and SUM(NEW_CNT).
• Fiscal reporting: join YEAR_ID to JAI_FA_AST_YEARS for fiscal-period alignment.
• Aging buckets: report BUCKET1–BUCKET4 counts to profile change-order age distribution.

Because it is a materialized view, results reflect the last refresh; reports should note staleness.

Related Objects

The most significant related objects are:

• ENG_CHANGE_ORDER_TYPES — joined via CHANGE_ORDER_TYPE_ID to supply ECO type descriptions.
• JAI_FA_AST_YEARS — joined via YEAR_ID for fiscal calendar context.
• Inventory organization and item master tables (e.g., MTL_SYSTEM_ITEMS_B, HR_ALL_ORGANIZATION_UNITS) implied by ORGANIZATION_ID and ITEM_ID.
• Underlying ECO base tables (ENG_ENGINEERING_CHANGE_ORDERS and related status tables) that populate the materialized view.
• DBI calendar/time dimension objects referenced by the TIME_ID, PERIOD_TYPE_ID, and period columns.