Search Results material_requirement_date




Overview
Explore in AI Chat

Overview

GME_INGRED_PICK_VW is a PL/SQL-based view owned by the APPS schema in Oracle E-Business Suite, defined within the GME (Process Manufacturing Process Execution) product family. Its purpose, as documented in ETRM, is to present the ingredient picking requirements for open and released process manufacturing batches. The view consolidates batch header information, material detail lines, operation and step context, and item master descriptions into a single queryable structure, deriving a computed OPEN_QTY value at runtime through a call to the GME_PICKING_PVT package.

Because Oracle does not persist an open-pick quantity as a stored column on GME_MATERIAL_DETAILS, this view exists to expose that derived value in a report-ready form. It filters aggressively to batches that are still actionable — only batches in status 1 or 2, batch type 0, with update-inventory flag set to 'Y', and a populated subinventory — making it suitable for shop-floor picking reports, backorder analysis, and integration workloads that must identify remaining material to be issued.

Underlying Base Objects

The view is defined over the following documented objects:

  • GME_BATCH_HEADER (synonym) — batch master, aliased H; drives status, dates, organization, and batch identity.
  • GME_MATERIAL_DETAILS (synonym) — ingredient line detail, aliased M; source of plan, actual, WIP plan, and backordered quantities.
  • MTL_SYSTEM_ITEMS_VL (view) — item master, aliased MSI; supplies concatenated segments and description, filtered on MTL_TRANSACTIONS_ENABLED_FLAG = 'Y'.
  • GME_BATCH_STEP_ITEMS and GME_BATCH_STEPS (synonyms) — operation/step context, outer-joined to material detail lines.
  • GMD_OPERATIONS_B (synonym) — operation definition, outer-joined via OPRN_ID.
  • GME_PICKING_PVT (package) — source of the OPEN_QTY scalar function GET_OPEN_QTY.

Joins between GME_MATERIAL_DETAILS, GME_BATCH_STEP_ITEMS, GME_BATCH_STEPS, and GMD_OPERATIONS_B are outer joins, so a material line is not suppressed when step or operation data is absent. In contrast, the item master join is mandatory.

Key Columns

Common Use Cases and Queries

The view is typically queried to drive picking reports, open-requirement dashboards, and interface extracts. A representative query filtering on the computed open quantity is:

  • SELECT batch_no, concatenated_segments, subinventory, plan_qty, actual_qty, open_qty FROM apps.gme_ingred_pick_vw WHERE open_qty > 0 ORDER BY batch_no, line_no;
  • Aggregating open demand per item across batches: SELECT concatenated_segments, SUM(open_qty) FROM apps.gme_ingred_pick_vw GROUP BY concatenated_segments;
  • Reporting by operation: SELECT oprn_no, batchstep_no, SUM(open_qty) FROM apps.gme_ingred_pick_vw GROUP BY oprn_no, batchstep_no;
  • Backorder investigation: SELECT * FROM apps.gme_ingred_pick_vw WHERE backordered_qty > 0;

Because OPEN_QTY is derived per row through a PL/SQL function, heavy unpredicated queries can be costly; restricting by BATCH_NO or ORGANIZATION_ID is advisable. The view is read-only and intended for reporting and integration, not for direct DML.