Results for “grade_fk”

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

Overview

The view PMI_EDW_BTCH_GRADE_F_FCV is a Process Manufacturing Intelligence (PMI) object that exposes batch grade transaction data in a flattened, foreign-key-rich format intended for consumption by the Oracle Business Intelligence / Enterprise Data Warehouse (EDW) layer. In Oracle EBS 12.1.1 and 12.2.2, this view belongs to the family of "FCV" (Foreign Column Value / Fact Collection View) objects that bridge native OPM transaction tables to the dimensional structures used by the EDW. Each row represents a completed production transaction at a specific grade, lot, item, warehouse, organization, and set of books, with surrogate foreign-key strings concatenated for the EDW load process.

The view is documented as not implemented in the current database, meaning it is defined in the ETRM repository but is not physically created in the instance referenced. Its role is to feed batch-grade fact records (quantities and costs) into the EDW staging and dimensional model, where surrogate keys conform to the EDW naming conventions (for example, the OPM and PORG suffixes and the NA_EDW placeholder constants).

Underlying Base Objects

The view text is built from a UNION ALL of at least two SELECT statements with identical column projections. The documented base objects referenced in the SQL are:

• IC_TRAN_PND — OPM pending/transaction detail. It is the driving table, filtered to COMPLETED_IND = 1, DOC_TYPE = 'PROD', LINE_TYPE = 1, and TRANS_QTY <> 0.
• IC_LOTS_MST — Lot master, joined on ITEM_ID and LOT_ID.
• IC_ITEM_MST — Item master, joined on ITEM_ID.
• GL_PLCY_MST — Cost policy master, joined on CO_CODE.
• GL_SETS_OF_BOOKS — Accounting books, joined on PM.SET_OF_BOOKS_NAME = SOB.NAME.
• EDW_LOCAL_INSTANCE — Local instance metadata supplying INSTANCE_CODE.

The ETRM metadata documents no referenced base objects explicitly for this view, but the view text confirms the joins above. It also invokes the PL/SQL functions EDW_TIME_PKG.CAL_DAY_FK, PMI_PRODUCTION_SUM.FIND_PROD_GRADE, and PMI_COMMON_PKG.PMICO_GET_COST, which contribute derived values to the projection.

Key Columns

• Batch/transaction key — concatenation of DOC_ID, ITEM_ID, LOT_ID, INSTANCE_CODE, and the literal OPM; used as the EDW surrogate for the batch fact.
• Organization/production org key — ORGN_CODE repeated with the PORG and OPM qualifiers.
• Item/warehouse key — '0' prepended to ITEM_NO and WHSE_CODE.
• Calendar day FK — EDW_TIME_PKG.CAL_DAY_FK(TRANS_DATE, SET_OF_BOOKS_ID), truncated to 120 characters. This is the date foreign key consumed by the EDW time dimension.
• Grade FK — PMI_PRODUCTION_SUM.FIND_PROD_GRADE(TRANS_ID, ITEM_ID, LOT_ID), truncated to four characters, concatenated with instance and OPM. This is the column most directly related to the user's grade_fk search term and represents the production grade foreign key.
• Quantity and cost measures — TRANS_QTY and TRANS_QTY * PMI_COMMON_PKG.PMICO_GET_COST(...), providing the fact measures.
• Descriptive attributes — ITEM_UM, LOT_NO, SUBLOT_NO, BASE_CURRENCY_CODE, and GL_COST_MTHD.
• LAST_UPDATE_DATE — the greatest of the update dates from the joined tables, supporting incremental EDW extraction.

Common Use Cases and Queries

Typical use is EDW extraction of batch-grade production facts, and analytical queries filtering on the grade foreign key.

• Extract all batch-grade facts for a date range using the calendar day FK.
• Aggregate production quantity by grade FK across organizations.
• Join the grade FK to the EDW grade dimension to report yield by grade.

Sample SQL:

SELECT grade_fk, item_no, orgn_code, SUM(trans_qty) total_qty FROM pmi_edw_btch_grade_f_fcv WHERE trans_date BETWEEN :start_date AND :end_date GROUP BY grade_fk, item_no, orgn_code;

Because the view is documented as not implemented, deployments should verify object existence before referencing it in production queries.