Search Results batch_status_fk

Overview

PMI_EDW_BTCH_RSRC_F_FCV is a Process Manufacturing Intelligence (PMI) view that exposes batch resource fact data in a flattened, warehouse-ready form. It is a foreign/interface construct used to feed the Oracle E-Business Suite Operational Data Store and Enterprise Data Warehouse. The view is not implemented in the EBS transactional database itself; it exists within the ETRM reporting layer, where it supplies batch-level resource consumption facts to downstream analytics and integrations in EBS 12.1.1 and 12.2.2.

Its role is to conform process manufacturing batch resource transactions into a dimensional fact shape. The view combines header, detail, item, organization, policy, and set-of-books information and projects surrogate foreign-key strings, time foreign keys, quantities, usages, counts, and descriptive identifiers. These are the same construct conventions used across OPM EDW fact views, making PMI_EDW_BTCH_RSRC_F_FCV the resource-specific member of the batch fact family.

Underlying Base Objects

The documented view text defines PMI_EDW_BTCH_RSRC_F_FCV over the following base tables and objects:

• PM_BTCH_HDR (BH) — batch header, providing batch identifiers, plant, warehouse, dates, and status.
• PM_MATL_DTL (BD) — batch material detail, restricted to line_type = 1, supplying item context.
• IC_ITEM_MST (IM) — item master, supplying item number.
• PM_OPRN_DTL (BR) — batch operation detail, the primary source of resource rows, quantities, usages, and step identifiers.
• SY_ORGN_MST (OM) — organization master, linking plant to company code.
• GL_PLCY_MST (PM) — policy master, linking company code to set of books.
• GL_SETS_OF_BOOKS (SOB) — set of books, used for the calendar day foreign key.
• EDW_LOCAL_INSTANCE (INST) — local instance metadata, supplying the instance code.

Joins are enforced on batch_id across header, material detail, and operation detail; plant to organization; organization to policy; policy to set of books; and material detail item_id to item master. Only batches with status 3 or 4 (completed) are exposed. No referenced base objects were separately documented in the metadata, so the view text is the authoritative source.

Key Columns

• BATCH_RSRC_PK — concatenated surrogate key of batch_id, batchstep_no, resource, activity, batchstepline_id, instance code, and 'OPM'.
• OPM_ORG_FK — organization foreign key built from plant code and instance code.
• ITEM_FK — item foreign key derived from item number and warehouse code.
• RESOURCE_FK — resource foreign key from resource and instance code.
• TIME_FK — calendar day foreign key derived from actual completion date via EDW_TIME_PKG.CAL_DAY_FK.
• BATCH_STATUS_FK — status foreign key built from batch status.
• PLAN_RSRC_QTY, ACTUAL_RSRC_QTY, PLAN_RSRC_USAGE, ACTUAL_RSRC_USAGE — plan and actual resource quantities and usages.
• PLAN_RSRC_COUNT, ACTUAL_RSRC_COUNT — plan and actual resource counts.
• BATCH_NO, BATCHSTEP_NO, ACTIVITY, BATCHSTEPLINE_ID — descriptive batch and step identifiers.
• PRIM_RSRC_IND, SCALE_TYPE, USAGE_UM — primary resource indicator, scale type, and unit of measure.
• PLAN_START_DATE, ACTUAL_START_DATE, PLAN_CMPLT_DATE, ACTUAL_CMPLT_DATE — planned and actual step dates.
• LAST_UPDATE_DATE — greatest last-update across the joined tables.

Common Use Cases and Queries

Typical uses include batch resource consumption analysis, plan-versus-actual variance reporting, and population of resource fact tables in the EDW. The user search term "pm_oprn_dtl" reflects the primary underlying table that supplies the resource rows.

Example query for resource variances by batch:

• SELECT BATCH_NO, BATCHSTEP_NO, ACTUAL_RSRC_QTY, PLAN_RSRC_QTY FROM PMI_EDW_BTCH_RSRC_F_FCV WHERE BATCH_NO = :batch_no;

Example aggregation by resource:

• SELECT RESOURCE_FK, SUM(ACTUAL_RSRC_USAGE) FROM PMI_EDW_BTCH_RSRC_F_FCV GROUP BY RESOURCE_FK;

Example time-based extraction:

• SELECT TIME_FK, SUM(ACTUAL_RSRC_QTY) FROM PMI_EDW_BTCH_RSRC_F_FCV GROUP BY TIME_FK;

Because only completed batches (status 3 and 4) and material line_type 1 are included, queries should not expect in-process or non-material-linked resource records.