Results for “sum_trans_qty2”

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

Overview

GMI_LOTS_DEST_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the GMI (Process Manufacturing Inventory) product family. The object carries a status of VALID and is documented against both EBS 12.1.1 and 12.2.2. Its purpose is narrowly and explicitly defined: the view populates the Where Used tab of the Lot Genealogy Inquiry when that inquiry is invoked in the "Where Used" navigation mode. In other words, it answers the question "which lots were consumed as ingredients to produce this lot?" by tracing production transactions backward from a finished or semi-finished lot to its input lots.

Because it is a view rather than a table, it stores no data of its own. It consolidates production batch information sourced from pending inventory transaction data and joins it to item and lot master information to present human-readable results. Report developers and integration specialists query it directly when building custom lot traceability reports, regulatory compliance extracts, and recall or quality investigations that fall outside the standard Oracle Forms-based Lot Genealogy Inquiry.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, GMI_LOTS_DEST_V is defined over the following synonyms (each resolving to its corresponding GMI base table): FM_FORM_MST, IC_ITEM_MST, IC_LOTS_MST, IC_TRAN_CMP, IC_TRAN_PND, PM_BTCH_HDR. The view text confirms that the principal driver is IC_TRAN_PND, aliased twice: once as PRODUCT (the produced output, restricted to DOC_TYPE = 'PROD', COMPLETED_IND = 1, and LINE_TYPE IN (1,2)) and once in a correlated subquery aliased as INGRED (the consumed ingredient lines, restricted to DOC_TYPE = 'PROD', COMPLETED_IND = 1, LINE_TYPE = -1, with a HAVING SUM(TRANS_QTY) <> 0 filter). IC_ITEM_MST supplies item number, description, and unit-of-measure attributes, while IC_LOTS_MST supplies lot and sublot numbers.

Batch validation is enforced through an inline restriction requiring the PRODUCT.DOC_ID to exist in PM_BTCH_HDR, joined to FM_FORM_MST on FORMULA_ID — ensuring only lots originating from genuine formula-based production batches are surfaced. The structure is a UNION ALL of at least two symmetric query blocks, which broadens coverage across production transaction scenarios.

Key Columns

• PRODUCT_ITEM_ID / PRODUCT_LOT_ID — identifiers of the produced (destination) item and lot.
• PRODUCT_ITEM_NO — the item number of the produced item, sourced from IC_ITEM_MST.ITEM_NO. This is the column most commonly matched when users search for "product_item_no".
• PRODUCT_ITEM_DESC1, PRODUCT_ITEM_UM, PRODUCT_ITEM_UM2, INV_TYPE, INV_CLASS — descriptive and classification attributes of the produced item.
• INGRED_ITEM_ID / INGRED_LOT_ID — identifiers of the ingredient (source) item and lot consumed in the batch.
• PRODUCT_LOT_NO, PRODUCT_SUBLOT_NO — lot and sublot numbers of the produced lot from IC_LOTS_MST.
• SUM_TRANS_QTY1, SUM_TRANS_QTY2 — aggregated transaction quantities for the produced lot, summed across the grouped dimensions.
• TRANS_DATE, LINE_TYPE, WHSE_CODE, LOCATION, QC_GRADE, LOT_STATUS — transaction date, line type indicator, warehouse, location, quality grade, and lot status.

Common Use Cases and Queries

The dominant use case is batch traceability: identifying every ingredient lot that contributed to a given produced lot, or conversely, finding all produced lots into which a given ingredient lot was incorporated. Sample query for a single product item number:

SELECT product_item_no, product_lot_no, product_sublot_no, ingred_item_id, ingred_lot_id, trans_date, sum_trans_qty1, whse_code, lot_status FROM apps.gmi_lots_dest_v WHERE product_item_no = :p_item_no ORDER BY trans_date DESC;

For a downstream recall, join the ingredient lot back to IC_LOTS_MST or IC_ITEM_MST for additional attributes:

SELECT v.product_item_no, v.product_lot_no, v.ingred_lot_id, i.item_no ingred_item_no FROM apps.gmi_lots_dest_v v, apps.ic_item_mst i WHERE v.ingred_item_id = i.item_id AND v.product_lot_no = :p_lot;

Because the view aggregates with SUM and GROUP BY, consumers should apply their own filter predicates carefully and avoid double-counting when layering additional joins. Performance is generally acceptable on indexed lot and document identifiers, but queries returning large date ranges benefit from restricting on TRANS_DATE and WHSE_CODE.