Search Results sub_inv_code

Overview

GMP_NETTABLE_ONHANDS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the GMP (Process Manufacturing Process Planning) product family. Its purpose is to expose on-hand inventory balances that qualify as "nettable" — that is, quantities that can be legitimately consumed by the planning engine when computing net requirements. The view is a filtered projection of the on-hand quantities detail table, restricted by a series of item planning attributes, subinventory and lot availability controls, and material status restrictions.

Because planning accuracy depends on excluding non-nettable stock (quarantined lots, non-planning subinventories, obsolete items), this view acts as the authoritative in-database definition of "available to plan." Reports, custom planning extracts, and integration interfaces targeting Process Manufacturing planning frequently reference it rather than querying MTL_ONHAND_QUANTITIES_DETAIL directly. The view is documented as VALID in both 12.1.1 and 12.2.2, and is referenced by name in the ETRM repository.

Underlying Base Objects

The view is defined over a join of several inventory, lot, and project entities. The documented base objects are:

• MTL_ONHAND_QUANTITIES_DETAIL (aliased MOQ) — the driving table supplying organization, item, subinventory, locator, lot, and transaction quantity.
• MTL_SYSTEM_ITEMS_B (aliased MASIS) — the item master, joined on inventory_item_id and organization_id, supplying planning and ATP flags.
• MTL_LOT_NUMBERS (aliased MLN, outer join) — provides expiration_date and lot availability_type.
• MTL_ITEM_LOCATIONS (aliased MIL, outer join) — provides project_id and task_id via locator.
• PJM_PROJECT_PARAMETERS (aliased MPP) — supplies planning_group.
• MTL_SECONDARY_INVENTORIES — used in an EXISTS subquery to enforce subinventory availability_type = 1 (nettable).

Additional synonyms referenced in the 12.2.2 metadata include MTL_MATERIAL_STATUSES, MTL_MATERIAL_TRANSACTIONS_TEMP, MTL_SERIAL_NUMBERS, MTL_SERIAL_NUMBERS_TEMP, and the PJM_UNIT_EFF package, which support material status checks and serial/lot validation. The join structure ensures each qualifying row represents one item-lot-locator combination of nettable stock.

Key Columns

• organization_id — inventory organization owning the on-hand quantity.
• inventory_item_id — the item identifier.
• subinventory_code — the subinventory holding the stock; only subinventories with availability_type = 1 are returned. This is the column most commonly searched by users querying "sub_inv_code".
• lot_number — the lot; if populated, the lot must also have availability_type = 1.
• quantity — transaction_quantity exposed from the on-hand detail.
• planning_organization_id / planning_tp_type / owning_organization_id / owning_tp_type — planning and ownership context, supporting multi-org and third-party planning scenarios.
• expiration_date — lot expiration, from MTL_LOT_NUMBERS.
• project_id / task_id — project and task association derived from the item locator.
• planning_group — planning grouping from PJM_PROJECT_PARAMETERS.
• end_item_unit_number — always NULL, reserved for a future/legacy end-item unit scenario.

Common Use Cases and Queries

Typical uses include validating nettable on-hand before releasing a plan, extracting balances for a specific subinventory, and reconciling inventory against MRP netting output. A standard query filtering by subinventory code might read:

• SELECT organization_id, inventory_item_id, subinventory_code, lot_number, quantity, expiration_date FROM apps.gmp_nettable_onhands_v WHERE subinventory_code = 'STORES' ORDER BY inventory_item_id;
• SELECT inventory_item_id, SUM(quantity) FROM apps.gmp_nettable_onhands_v WHERE organization_id = :org GROUP BY inventory_item_id;

Because the view enforces item planning flags, subinventory availability, and lot availability in a single definition, it reduces the risk of over-planning quarantined or non-nettable stock. When adapting it for custom reports, note that quantity is aliased from transaction_quantity and that end_item_unit_number is always NULL.