Reports 2017-11-18T12:27:27+00:00

INV Onhand Quantities

Description
Categories: Enginatics, Manufacturing
Inventory item onhand quantity details

select
x.organization_code,
x.subinventory,
x.locator,
x.item,
x.item_description,
x.revision,
x.unit_of_measure,
x.on_hand,
mr.reserved,
greatest(nvl(x.on_hand,0)-nvl(mr.reserved,0),0) available,
x.license_plate_number,
x.lot_number,
ppa.project_number project,
pt.task_number task,
x.serial_control,
x.lot_control,
x.availability_type
from
(
select distinct
mp.organization_code,
msi.secondary_inventory_name subinventory,
inv_project.get_locator(mil.inventory_location_id,mil.organization_id) locator,
msiv.concatenated_segments item,
msiv.description item_description,
moqd.revision,
nvl(msiv.primary_unit_of_measure,msiv.primary_uom_code) unit_of_measure,
sum(moqd.primary_transaction_quantity) over (partition by moqd.inventory_item_id, moqd.organization_id, moqd.subinventory_code, moqd.locator_id, moqd.lpn_id, moqd.revision) on_hand,
sum(moqd.primary_transaction_quantity) over (partition by moqd.inventory_item_id) on_hand_sum,
wlpn.license_plate_number,
moqd.lot_number,
decode(msiv.serial_number_control_code,1,'No Control',2,'Predefined',5,'At Receipt',6,'At Sales Order Issue',msiv.serial_number_control_code) serial_control,
decode(msiv.lot_control_code,1,'No Control',2,'Full Control',msiv.lot_control_code) lot_control,
xxen_util.meaning(msi.availability_type,'MTL_AVAILABILITY',700) availability_type,
moqd.inventory_item_id,
moqd.organization_id,
moqd.subinventory_code,
moqd.locator_id,
moqd.lpn_id,
moqd.project_id,
moqd.task_id
from
mtl_onhand_quantities_detail moqd,
mtl_parameters mp,
mtl_secondary_inventories msi,
mtl_item_locations mil,
wms_license_plate_numbers wlpn,
mtl_system_items_vl msiv
where
1=1 and
moqd.organization_id=mp.organization_id and
moqd.organization_id=msi.organization_id(+) and
moqd.subinventory_code=msi.secondary_inventory_name(+) and
moqd.organization_id=mil.organization_id(+) and
moqd.locator_id=mil.inventory_location_id(+) and
moqd.lpn_id=wlpn.lpn_id(+) and
moqd.organization_id=msiv.organization_id and
moqd.inventory_item_id=msiv.inventory_item_id
) x,
(
select distinct
sum(mr.primary_reservation_quantity) over (partition by mr.inventory_item_id, mr.organization_id, mr.subinventory_code) reserved,
mr.inventory_item_id,
mr.organization_id,
mr.subinventory_code
from
mtl_reservations mr
) mr,
(
select ppa.project_id, ppa.segment1 project_number from pa_projects_all ppa union
select psm.project_id, psm.project_number from pjm_seiban_numbers psm
) ppa,
pa_tasks pt
where
x.inventory_item_id=mr.inventory_item_id(+) and
x.organization_id=mr.organization_id(+) and
x.subinventory_code=mr.subinventory_code(+) and
x.project_id=ppa.project_id(+) and
x.task_id=pt.task_id(+)
order by
on_hand_sum desc,
item,
on_hand desc

Parameter Name SQL text Validation
Organization Code
mp.organization_code=:organization_code
LOV
Subinventory
msi.secondary_inventory_name=:secondary_inventory_name
LOV
Item
msiv.concatenated_segments=:item
LOV
Nettable only
msi.availability_type=1
LOV Oracle