Search Results opm_org_fk

Overview

PMI_EDW_BTCH_QC_F_FCV is a Process Manufacturing Intelligence (PMI) view that exposes batch quality control (QC) results in a flattened, surrogate-key form suitable for the Oracle E-Business Suite Operational Data Store / Enterprise Data Warehouse (EDW). It belongs to the PMI_EDW family of "*_FCV" ("flat column view") objects, which are populated by the PMI batch extraction programs and consumed by Oracle Business Intelligence and Discoverer reporting layers. The view is not implemented as a base table in every environment; the ETRM metadata records it as "Not implemented in this database," confirming that it exists only where the PMI EDW schema has been deployed.

The view presents one row per QC result line, joined to its parent sample, batch header, routing step, operation, and item context. Its purpose is to denormalize normalized OPM QC tables into a star-schema-compatible fact record, replacing natural multi-part keys with single concatenated foreign keys (for example, '<organization>-<assay>-<instance>-OPM' for the assay foreign key).

Underlying Base Objects

Although the ETRM metadata records no documented base objects, the view text shows it is defined over a set of OPM and Oracle General Ledger tables:

• QC_RSLT_MST QR — QC result master; source of results, routing step, operation, assay, and unit of measure.
• QC_SMPL_MST QS — QC sample master; source of sample number, description, and sample status.
• FM_ROUT_HDR RH — Routing header (outer-joined), supplying routing number and version.
• FM_OPRN_MST OP — Operation master (outer-joined), supplying the operation number.
• PM_BTCH_HDR BH — Batch header; principal driver of the row, filtered on BATCH_STATUS IN (3, 4).
• IC_ITEM_MST IM — Item master; supplies the item number used in the item foreign key.
• SY_ORGN_MST OM, GL_PLCY_MST PM, GL_SETS_OF_BOOKS SOB — Organization, policy, and set-of-books tables used to resolve the calendar/time foreign key.
• EDW_LOCAL_INSTANCE INST — Instance code used as a suffix in the concatenated foreign keys.

Joins are driven from QC_RSLT_MST through BATCH_ID, ITEM_ID, SAMPLE_ID, ROUTING_ID (outer), and OPRN_ID (outer). The view is effectively a QC fact source layered over the batch header, not a batch-grain object.

Key Columns

• BATCH_QC_PK — Surrogate primary key for the QC fact row.
• OPM_ORG_FK — Organization foreign key in the form PLANT_CODE-PLANT_CODE-PORG-INSTANCE_CODE-OPM; this is the column most users search for when tracing batch plant/organization lineage.
• ITEM_FK — Concatenated item key 0-ITEM_NO-WIP_WHSE_CODE.
• ASSAY_FK — Concatenated assay key ORGN_CODE-ASSAY_CODE-INSTANCE_CODE-OPM.
• TIME_FK — Calendar day key derived from EDW_TIME_PKG.CAL_DAY_FK on ACTUAL_CMPLT_DATE.
• ROUTING_FK — Routing number, version, instance, and module concatenation.
• SAMPLE_STATUS_FK — Sample status joined to the GMD_SAMPLE_STATUS lookup.
• RESULT — Decoded as NUM_RESULT where present, otherwise TEXT_RESULT.
• BATCH_ADJUSTED, ACCEPT, USER_MEASURE1..USER_MEASUREN — Batch adjustment count, acceptance flag, and user-defined measure columns.

Common Use Cases and Queries

Typical uses include QC result trending by plant, item, assay, and time; batch release and acceptance reporting; and conforming QC facts to the PMI EDW star schema via the surrogate keys.

SELECT BATCH_QC_PK,
       OPM_ORG_FK,
       ITEM_FK,
       ASSAY_FK,
       TIME_FK,
       RESULT,
       ACCEPT
FROM   PMI_EDW_BTCH_QC_F_FCV
WHERE  OPM_ORG_FK LIKE '%PORG%'
ORDER  BY TIME_FK DESC;

Because the foreign keys are pipe-concatenated strings, filtering is normally done with LIKE patterns or by resolving the corresponding dimension view. Users tracing opm_org_fk should note that the plant code appears twice in the key, reflecting the batch header-to-organization join used in the view definition.