Search Results sample_qty

Overview

PMIBV_QC_SAMPLE_V is a reporting view owned by the APPS schema in Oracle EBS Process Manufacturing Intelligence (PMI). It exposes the full, denormalized picture of a quality control sample record held in QC_SMPL_MST, enriching each sample with the item, lot, batch, formula, routing, operation, customer, and supplier context needed for analysis. The view carries the "BV" (Business View) prefix typical of PMI reporting objects, indicating it is intended for read-only query and integration use rather than as a transactional entity. In 12.1.1 and 12.2.2 the object is documented as VALID, with the same column list and join semantics, so reports, discoverer workbooks, and interface extracts developed against it remain portable across both releases. The view is frequently used when QC data must be reported outside the Process Manufacturing workbench—for example in operational dashboards, LIMS extracts, or buyer/supplier quality scorecards—because it resolves many foreign keys that would otherwise require lengthy application-specific joins.

Underlying Base Objects

The documented base objects referenced by the view are FM_FORM_MST, FM_OPRN_MST, FM_ROUT_DTL, FM_ROUT_HDR, FND_USER, GME_BATCH_HEADER, HZ_CUST_ACCOUNTS_ALL, HZ_PARTIES, IC_ITEM_MST, IC_LOTS_MST, PO_VEND_MST, and QC_SMPL_MST, all exposed to APPS through synonyms. QC_SMPL_MST is the driving table (sample master); the remaining objects are joined with outer joins keyed on the sample's foreign keys. FM_FORM_MST supplies formula identity when QS.FORMULA_ID is populated; FM_ROUT_HDR and FM_ROUT_DTL supply routing header information and the specific routing step when QS.ROUTING_ID and QS.ROUTINGSTEP_ID are set; FM_OPRN_MST supplies the operation. GME_BATCH_HEADER supplies batch context via QS.BATCH_ID, and IC_LOTS_MST supplies lot and sublot detail via QS.LOT_ID (restricted to the same item). IC_ITEM_MST is an inner-joined lookup on QS.ITEM_ID, so a sample without a valid item is excluded. FND_USER is inner-joined on QS.SAMPLED_BY, meaning the sampling user must exist in the user directory. HZ_CUST_ACCOUNTS_ALL and HZ_PARTIES resolve the customer account and party name from QS.CUST_ID, while PO_VEND_MST resolves supplier details from QS.VENDOR_ID.

Key Columns

The view exposes the QC sample identity and descriptive attributes—SAMPLE_ID, ORGANIZATION_CODE, SAMPLE_NO, SAMPLE_DESCRIPTION, WAREHOUSE_CODE, LOCATION, SAMPLE_DATE, SAMPLE_QTY, and SAMPLE_UOM—alongside SAMPLED_BY, which is the FND_USER.USER_NAME of the sampler. DELETE_MARK identifies soft-deleted rows and should normally be filtered to zero. EXTERNAL_ID is the column most relevant to external system linkage: it stores the identifier assigned by a third-party or upstream system (such as a LIMS or supplier portal) and is the natural key for reconciliation between the QC sample and the originating external record. Item context is provided through ITEM_NUMBER, ITEM_DESCRIPTION, ITEM_UOM, and INVENTORY_TYPE. Process context is supplied by FORMULA_ID, FORMULA_NUMBER, FORMULA_VERSION, ROUTING_ID, ROUTING_NUMBER, ROUTING_VERSION, ROUTING_STEP_NUMBER, OPERATION_ID, OPERATION_NUMBER, and OPERATION_VERSION, with LOT_NUMBER, SUB_LOT_NUMBER, and BATCH_NUMBER completing traceability. Trading partner context is available through CUSTOMER_NUMBER, CUSTOMER_NAME, VENDOR_NUMBER, and VENDOR_NAME. The raw keys CUST_ID and SHIP_TO_SITE_ID are also carried for downstream joins.

Common Use Cases and Queries

Typical uses include sample traceability reports, quality-by-lot or quality-by-supplier analysis, and interface extracts that map internal samples to external records using EXTERNAL_ID. A simple query for one sample follows:

• SELECT sample_no, sample_date, item_number, lot_number, batch_number, external_id FROM apps.pmibv_qc_sample_v WHERE sample_id = :p_sample_id;

• SELECT sample_no, item_number, organization_code, customer_name, vendor_name, sampled_by FROM apps.pmibv_qc_sample_v WHERE delete_mark = 0 AND sample_date >= :p_from_date AND sample_date < :p_to_date;

• SELECT external_id, sample_no, item_number FROM apps.pmibv_qc_sample_v WHERE external_id IS NOT NULL ORDER BY sample_date DESC; — used to reconcile samples against an external system and to identify records where the external reference has not yet been populated.

Because the view already resolves the item, lot, batch, formula, routing, customer, and vendor lookups, it reduces the join complexity of equivalent queries written directly against QC_SMPL_MST. Reports should still filter on DELETE_MARK and constrain sample_date to keep execution plans efficient, and integration code should treat EXTERNAL_ID as nullable because not every sample originates from an external source.