Results for “qc_grade”

50+ results




Overview
Explore in AI Chat

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

Overview

GMI_LOTS_SOURCE_LIST_V is a database view owned by the APPS schema within the Process Manufacturing Inventory (GMI) module of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2 releases. The view is documented in the E-Business Suite Technical Reference Manual (ETRM) as a supporting object for the Oracle Process Manufacturing (OPM) lot genealogy functionality. Its specific purpose is to populate the Lot Genealogy Tree Navigator, which is rendered within the Lot Genealogy Inquiry form, when the navigation mode is set to "Lot Source" and the user has submitted query parameters through the Find dialogue box. In practical reporting and integration terms, the view exposes a flattened, query-ready result set of lot records together with a computed indicator of whether an ingredient (child) relationship exists for each lot, enabling both form-driven navigation and external reporting against lot traceability data.

Underlying Base Objects

The ETRM metadata documents two referenced base objects for this view:

  • GMI_LOTS_LIST_V (VIEW) — The primary source of the columns returned by the view. The view text selects from this underlying view aliased as L and applies a GROUP BY over the selected columns.
  • GMI_LOT_TRACE_PKG (PACKAGE) — A PL/SQL package invoked within the SELECT list. The function GMI_LOT_TRACE_PKG.HAS_INGRED(ITEM_ID, LOT_ID) is called to derive the HAS_CHILD column, which flags whether a given item/lot combination has ingredient (child) records in the lot genealogy hierarchy.

Because the definition aggregates over the columns of GMI_LOTS_LIST_V and calls a packaged function per grouped row, the view effectively de-duplicates the source listing while enriching each row with traceability context. The dependency on GMI_LOT_TRACE_PKG places the view within the broader OPM lot traceability framework rather than the pure inventory transaction tables.

Key Columns

The view exposes the following columns, as documented in the ETRM:

  • ITEM_NO — The item (product or ingredient) number.
  • ITEM_DESC1 — The primary description of the item.
  • ITEM_ID — The internal surrogate key for the item, used as an argument to the HAS_INGRED function and for joins.
  • LOT_ID — The internal identifier for the lot; also passed to HAS_INGRED.
  • LOT_NO — The user-facing lot number.
  • SUBLOT_NO — The sublot (secondary lot) number where sublot tracking is in effect.
  • CREATION_DATE — The creation date associated with the lot record.
  • QC_GRADE — The quality control grade assigned to the lot.
  • ORGN_CODE — The organization (warehouse/plant) code owning the lot.
  • VENDOR_NO — The vendor number, relevant where the lot originated from a supplier.
  • HAS_CHILD — A derived flag from GMI_LOT_TRACE_PKG.HAS_INGRED indicating whether the lot has ingredient-level descendants, used by the tree navigator to determine expandability.

Common Use Cases and Queries

The principal use case is driving the Lot Genealogy Inquiry form's source-navigation tree. Reporting and integration scenarios include lot traceability reporting, recall/withdrawal investigations, and quality analyses. A representative query returning all lots with at least one ingredient child is:

  • SELECT item_no, lot_no, sublot_no, orgn_code, qc_grade, vendor_no, creation_date FROM apps.gmi_lots_source_list_v WHERE has_child = 1;
  • SELECT lot_no, item_desc1, qc_grade FROM apps.gmi_lots_source_list_v WHERE item_id = :p_item_id AND orgn_code = :p_orgn;

Because HAS_CHILD is computed via a package function, queries that filter on it should be used judiciously in high-volume extracts; filtering first by ITEM_ID, LOT_NO, or ORGN_CODE limits function invocations. The view is read-only and should be queried with the APPS schema context or a synonym exposing it to reporting users.