Results for “subinventory_code”

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

Overview

The WIP_SUB_LOC_NET_AVAIL_VIEW is an Oracle EBS Work in Process (WIP) reporting view owned by the APPS schema. It consolidates job and schedule material requirement supply quantities at the subinventory and locator level, allowing planners and manufacturing users to compare on-hand availability against open WIP demand for a given item within a specific organization. The view is defined as a multi-table join that aggregates requirement operation lines, on-hand quantity detail, and secondary inventory attributes, producing a net-available-quantity figure per item, subinventory, and locator.

Because the view straddles both the supply side (on-hand balances) and the demand side (open WIP requirements), it is principally used in material shortage and net availability reporting, and it frequently feeds ad hoc queries, custom concurrent programs, and Discoverer worksheets in discrete and repetitive manufacturing environments.

Underlying Base Objects

The view is defined over the following documented base objects: MFG_LOOKUPS (a view), MTL_EMPLOYEES_VIEW, MTL_ITEM_FLEXFIELDS (a view), and the synonyms MTL_ITEM_LOCATIONS, MTL_ONHAND_QUANTITIES_DETAIL, MTL_SECONDARY_INVENTORIES, MTL_SYSTEM_ITEMS_B, WIP_DISCRETE_JOBS, WIP_REPETITIVE_SCHEDULES, and WIP_REQUIREMENT_OPERATIONS. Package dependencies include HR_GENERAL, HR_PERSON_NAME, and HR_SECURITY, invoked indirectly through the employee view and security predicates.

In practice, MTL_SYSTEM_ITEMS_B supplies the item number, description, and planner code; WIP_REQUIREMENT_OPERATIONS supplies the supply subinventory and open requirement quantities; MTL_ONHAND_QUANTITIES_DETAIL supplies transaction quantities and availability context; and MTL_SECONDARY_INVENTORIES supplies the availability type (nettable versus non-nettable) used to split on-hand balances.

Key Columns

• ORGANIZATION_ID, INVENTORY_ITEM_ID, ITEM_NUMBER – Organization and item identifiers that anchor every aggregation.
• SUBINVENTORY_CODE, LOCATOR_ID – The supply subinventory and locator context derived from the requirement operation and item location.
• BUYER_EMPLOYEE_NUM, BUYER_NAME – Buyer of the item, sourced through the employee view.
• PLANNER_CODE – Planner responsible for the item.
• PLANNING_MAKE_BUY_CODE – The decoded lookup meaning for the item's make/buy attribute, resolved through MFG_LOOKUPS.
• NO_OF_OPEN_REQUIREMENTS – Count of distinct open requirement rows.
• REQUIRED_QUANTITY, QUANTITY_ISSUED, QUANTITY_OPEN – Demand and issued quantities, normalized by the count of on-hand detail rows to avoid multiplication of aggregates.
• NETTABLE_QUANTITY_ON_HAND, NONNETTABLE_QUANTITY_ON_HAND, TOTAL_QUANTITY_ON_HAND – On-hand split by MTL_SECONDARY_INVENTORIES.AVAILABILITY_TYPE (1=nettable, 2=non-nettable).
• NET_AVAILABLE_QUANTITY – Nettable on-hand plus (issued minus required), the headline figure the view exists to expose.

Common Use Cases and Queries

A typical use is identifying subinventory/locator positions where net available quantity is insufficient to cover open WIP demand for a given item. Because the view already resolves the make/buy lookup, it is also used for filtering by PLANNING_MAKE_BUY_CODE — relevant to users who search on mtl_planning_make_buy — although that attribute originates in MTL_SYSTEM_ITEMS_B and is only joined here for reporting convenience.

SELECT item_number,
       subinventory_code,
       buyer_name,
       planner_code,
       nettable_quantity_on_hand,
       quantity_open,
       net_available_quantity
FROM   apps.wip_sub_loc_net_avail_view
WHERE  organization_id = :org_id
AND    planning_make_buy_code = 'MAKE'
AND    net_available_quantity < 0
ORDER BY item_number, subinventory_code;

A second common pattern aggregates the view to build a shortage report per planner, summing QUANTITY_OPEN and NET_AVAILABLE_QUANTITY grouped by PLANNER_CODE and SUBINVENTORY_CODE. A third pattern joins the view to WIP_DISCRETE_JOBS or WIP_REPETITIVE_SCHEDULES to attribute availability back to specific jobs or schedules. Because aggregation in the view divides by distinct row counts, consumers should not re-aggregate without accounting for this normalization, and should always constrain by organization to preserve performance.