MTL_ATT_QTY_V

select 1 type
  ,moq.inventory_item_id
  ,moq.organization_id
  ,moq.revision
  ,moq.subinventory_code
  ,moq.locator_id
  ,moq.transaction_quantity
  ,moq.lot_number
from   mtl_onhand_quantities moq
  ,mtl_lot_numbers       mln
where  mln.lot_number(+) = moq.lot_number
and    mln.inventory_item_id(+) = moq.inventory_item_id
and    mln.organization_id(+) = moq.organization_id
union all
select 2
  ,mmtt.inventory_item_id
  ,mmtt.organization_id
  ,mmtt.revision
  ,mmtt.subinventory_code
  ,mmtt.locator_id
  ,nvl(mtlt.primary_quantity, mmtt.primary_quantity)
  ,nvl(mtlt.lot_number, mmtt.lot_number)
from   mtl_material_transactions_temp mmtt
  ,mtl_transaction_lots_temp      mtlt
where  mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
and    nvl(mtlt.lot_expiration_date,
   nvl(mmtt.lot_expiration_date, sysdate + 1)) > sysdate
and    mmtt.posting_flag = 'Y'
union all
select 7
  ,md.inventory_item_id
  ,md.organization_id
  ,md.revision
  ,md.subinventory
  ,md.locator_id
  ,- (nvl(md.primary_uom_quantity, 0) - nvl(completed_quantity, 0))
  ,md.lot_number
from   mtl_demand      md
  ,mtl_lot_numbers mln
where  mln.lot_number(+) = md.lot_number
and    mln.inventory_item_id(+) = md.inventory_item_id
and    mln.organization_id(+) = md.organization_id
and    nvl(mln.expiration_date, sysdate + 1) > sysdate
and    md.reservation_type = 2  --*Hard reservation
and    md.parent_demand_id is not null
and    md.row_status_flag = 1 --*1 - Active; 2 - Complete

;  

 

Reservation Type                Meaning

  1                                Soft Reservation

  2                                Hard reservation

  3                                Reservation against WIP supply

PARENT_DEMAND_ID - DEMAND_ID of parent (summary demand) for reservation and open demand rows. Null for summary and derived demand rows

 

 

ABLE: INV.MTL_DEMAND

Object Details

Object Name:Object Type:Owner:FND Design Data:Subobject Name:Status:

MTL_DEMAND

TABLE

INV

INV.MTL_DEMAND

VALID

This table stores demand and reservation information used in Available
To Promise, Planning and other Manufacturing functions. There are
three major row types stored in the table: Summary Demand rows,
Open Demand Rows, and Reservation Rows.
.
Summary Demand is direct demand for an item within an organization on
a particular date, which originated from a particular source. For
hard reservations there are several columns which further define what
the reservation is for, and where it is being placed. Currently, four
sources of demand are supported, Sales Order, Account, Account Alias,
and User Defined transaction sources. Five different types of demand,
denoted by DEMAND_TYPE column are used. These five types are Model,
Option Class, Option Item, Configuration Item and Derived. Derived
demand rows are inserted by BOM Demand exploder when demanded item has
ATPable components. Each Summary Demand row may be associated with
one or more Reservation rows. Reservation may be placed against a
particular inventory control (ie, specific subinventory, locator,
revision and lot) against any sources (ie, Account Number, Account
Alias, Sales Order or even User-Defined sources). Each Summary Demand
row may be associated with one or more detailed rows. The detailed
rows consist of reservations and open demand. A reservation row
represents a firm promise of a supply source. Currently, two types of
reservation are supported, reservations to on-hand, and reservations
to WIP jobs. Each summary demand row may be associated with one and only one open demand row. Open Demand rows represent the un-reserved
portion of the the Summary Demand.