Results for “formula_version”

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

Overview

PMIBV_QC_PRODUCTION_SPEC_V is an APPS-owned database view within the Oracle E-Business Suite Process Manufacturing Intelligence (PMI) product family. Its documented description is "QC Production Specification Full View," and it exposes quality control specification data joined to the surrounding production context — item, formula, lot, batch, routing, and operation master data — in a single denormalized result set. The view is marked VALID in the ETRM metadata for both 12.1.1 and 12.2.2, although the PMI product itself is flagged as obsolete. This means the object typically persists in existing installations for backward compatibility and reporting continuity rather than as the target of new development.

Because quality specifications in Process Manufacturing are defined at the intersection of an item, a formula, a lot, and (optionally) a routing step, a single flat view that resolves all of those relationships is valuable for downstream reporting, operational quality dashboards, and integration extracts. PMIBV_QC_PRODUCTION_SPEC_V serves that purpose: it answers "what quality spec applies to this production step, and under what production context?" without requiring the consumer to reconstruct the joins. Note that several columns are returned as literal placeholders — CUSTOMER_NUMBER, CUSTOMER_NAME, VENDOR_NUMBER, and VENDOR_NAME are hard-coded as blanks, and VENDOR_ID as zero — indicating the view was designed for a broader QC specification model but only the production-oriented branches are populated.

Underlying Base Objects

The view is defined over the following documented base objects:

• QC_SPEC_MST — the driving table; source of the quality control specification attributes (spec ID, assay type, min/max/target, unit of measure, effective dates, out-of-action parameters, and the routing step number).
• IC_ITEM_MST — supplies item number (ITEM_NO) and item description (ITEM_DESC1), joined on ITEM_ID.
• FM_FORM_MST — supplies formula identity and version (FORMULA_ID, FORMULA_NO, FORMULA_VERS), joined on FORMULA_ID.
• IC_LOTS_MST — joined with an outer join (LOT_ID(+)) to supply lot and sublot numbers without eliminating specs that have no lot association.
• GME_BATCH_HEADER — supplies the batch number (BATCH_NO).
• FM_ROUT_HDR — supplies routing identity, number, and version (ROUTING_ID, ROUTING_NO, ROUTING_VERS).
• FM_OPRN_MST — supplies operation identity, number, and version (OPRN_ID, OPRN_NO, OPRN_VERS).
• FND_LOOKUPS — joined as ACTIVELKUP to resolve the effective/active lookup meaning.
• FND_GLOBAL — referenced as a package, typically for session context such as organization or user identifiers.

All Process Manufacturing master tables are referenced through APPS synonyms. Do not confuse the IC_ITEM_MST and IC_LOTS_MST names here with the discrete Inventory tables of the same short name; in the Process Manufacturing schema these are the GME/OPM-style synonyms.

Key Columns

The most relevant columns, grouped by purpose:

• Quality spec attributes: QUALITY_CONTROL_SPEC_ID, ASSAY_TYPE_ID, ASSAY_CODE, TEXT_SPEC, MIN_SPEC, MAX_SPEC, TARGET_SPEC, QUALITY_CONTROL_UNIT_CODE, OUT_OF_ACTION_CODE, OUT_OF_ACTION_INTERVAL, PREFERENCE, FROM_DATE, TO_DATE, DELETE_MARK.
• Documentation control: DOCUMENT_TYPE, VENDOR_SPECIFICATION, VENDOR_CERTIFICATION, CUSTOMER_SPECIFICATION, CUSTOMER_CERTIFICATION, PRINT_COA_SHIPPED, PRINT_COA_INVOICED, VENDOR_COA_REQUIRED.
• Organizational context: ORGANIZATION_CODE, WAREHOUSE_CODE, LOCATION.
• Production context: ITEM_NUMBER, ITEM_DESCRIPTION, FORMULA_ID, FORMULA_NUMBER, FORMULA_VERSION, LOT_NUMBER, SUB_LOT_NUMBER, BATCH_NUMBER.
• Routing context: ROUTING_ID, ROUTING_NUMBER, ROUTING_VERSION, ROUTING_STEP_NUMBER, OPERATION_ID, OPERATION_NUMBER, OPERATION_VERSION.
• Placeholders: CUSTOMER_NUMBER, CUSTOMER_NAME, VENDOR_ID, VENDOR_NUMBER, VENDOR_NAME are returned as blanks or zero and carry no data.

For a user searching on "routing_version," the column of interest is ROUTING_VERSION, sourced from FM_ROUT_HDR.ROUTING_VERS and aliased to ROUTING_VERSION. It identifies which version of the routing the quality specification applies to, and it is distinct from FORMULA_VERSION and OPERATION_VERSION, which version the formula and operation respectively.

Common Use Cases and Queries

Typical uses include extracting active quality specifications by routing version for a given item or batch, generating Certificate of Analysis inputs, and feeding quality reporting where the production routing context must be traced alongside the spec.

Sample query — list quality specs by item and routing version:

SELECT item_number, routing_number, routing_version, routing_step_number, assay_code, min_spec, max_spec, target_spec, quality_control_unit_code, from_date, to_date FROM apps.pmibv_qc_production_spec_v WHERE item_number = :item AND routing_version = :routing_vers AND NVL(delete_mark, 0) = 0;

Sample query — retrieve specs for a specific batch and lot:

SELECT batch_number, lot_number, sub_lot_number, formula_number, formula_version, operation_number, operation_version, assay_code, target_spec FROM apps.pmibv_qc_production_spec_v WHERE batch_number = :batch AND lot_number = :lot;

Sample query — group routing versions in use for an item:

SELECT item_number, routing_number, routing_version, COUNT(*) spec_count FROM apps.pmibv_qc_production_spec_v WHERE NVL(delete_mark, 0) = 0 GROUP BY item_number, routing_number, routing_version ORDER BY item_number, routing_number, routing_version;

Because the driving table is joined to item, formula, and lot master data, always filter on DELETE_MARK and constrain via the effective FROM_DATE/TO_DATE range to avoid returning historical or logically deleted specifications.