INV Physical Inventory Locator Count Sheet

select
mp.organization_code,
milk.subinventory_code subinventory,
milk.concatenated_segments locator,
milk.description locator_description,
mmsv.status_code locator_status,
pjm_project.all_proj_idtonum(mil.project_id) project,
pjm_project.all_task_idtonum(mil.task_id) task,
msiv.concatenated_segments item,
msiv.description item_description,
moqd.revision,
moqd.lot_number,
mln.expiration_date lot_expiration_date,
wlpn.license_plate_number lpn,
xxen_util.yes(case when msiv.serial_number_control_code in (2,5) then 'Y' end) serial_controlled,
msiv.primary_uom_code uom,
&system_quantity
to_number(null) count_quantity,
null counted_by,
null count_comments
from
mtl_parameters mp,
mtl_secondary_inventories msi,
mtl_item_locations_kfv milk,
mtl_material_statuses_vl mmsv,
mtl_item_locations mil,
mtl_onhand_quantities_detail moqd,
mtl_system_items_vl msiv,
mtl_lot_numbers mln,
wms_license_plate_numbers wlpn
where
1=1 and
mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and
mp.organization_id=msi.organization_id and
msi.quantity_tracked=1 and
nvl(msi.disable_date,sysdate)>=sysdate and
msi.organization_id=milk.organization_id and
msi.secondary_inventory_name=milk.subinventory_code and
nvl(milk.physical_location_id,milk.inventory_location_id)=milk.inventory_location_id and
nvl(milk.disable_date,sysdate)>=sysdate and
milk.status_id=mmsv.status_id(+) and
milk.organization_id=mil.organization_id(+) and
milk.inventory_location_id=nvl(mil.physical_location_id(+),mil.inventory_location_id(+)) and
mil.organization_id=moqd.organization_id(+) and
mil.inventory_location_id=moqd.locator_id(+) and
moqd.organization_id=msiv.organization_id(+) and
moqd.inventory_item_id=msiv.inventory_item_id(+) and
moqd.organization_id=mln.organization_id(+) and
moqd.inventory_item_id=mln.inventory_item_id(+) and
moqd.lot_number=mln.lot_number(+) and
moqd.lpn_id=wlpn.lpn_id(+)
group by
mp.organization_code,
milk.subinventory_code,
milk.concatenated_segments,
milk.description,
mmsv.status_code,
mil.project_id,
mil.task_id,
msiv.concatenated_segments,
msiv.description,
moqd.revision,
moqd.lot_number,
mln.expiration_date,
wlpn.license_plate_number,
msiv.serial_number_control_code,
msiv.primary_uom_code
order by
mp.organization_code,
milk.subinventory_code,
milk.concatenated_segments,
msiv.concatenated_segments,
moqd.revision,
moqd.lot_number,
wlpn.license_plate_number

select mp.organization_code, milk.subinventory_code subinventory, milk.concatenated_segments locator, milk.description locator_description, mmsv.status_code locator_status, pjm_project.all_proj_idtonum(mil.project_id) project, pjm_project.all_task_idtonum(mil.task_id) task, msiv.concatenated_segments item, msiv.description item_description, moqd.revision, moqd.lot_number, mln.expiration_date lot_expiration_date, wlpn.license_plate_number lpn, xxen_util.yes(case when msiv.serial_number_control_code in (2,5) then 'Y' end) serial_controlled, msiv.primary_uom_code uom, &system_quantity to_number(null) count_quantity, null counted_by, null count_comments from mtl_parameters mp, mtl_secondary_inventories msi, mtl_item_locations_kfv milk, mtl_material_statuses_vl mmsv, mtl_item_locations mil, mtl_onhand_quantities_detail moqd, mtl_system_items_vl msiv, mtl_lot_numbers mln, wms_license_plate_numbers wlpn where 1=1 and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=msi.organization_id and msi.quantity_tracked=1 and nvl(msi.disable_date,sysdate)>=sysdate and msi.organization_id=milk.organization_id and msi.secondary_inventory_name=milk.subinventory_code and nvl(milk.physical_location_id,milk.inventory_location_id)=milk.inventory_location_id and nvl(milk.disable_date,sysdate)>=sysdate and milk.status_id=mmsv.status_id(+) and milk.organization_id=mil.organization_id(+) and milk.inventory_location_id=nvl(mil.physical_location_id(+),mil.inventory_location_id(+)) and mil.organization_id=moqd.organization_id(+) and mil.inventory_location_id=moqd.locator_id(+) and moqd.organization_id=msiv.organization_id(+) and moqd.inventory_item_id=msiv.inventory_item_id(+) and moqd.organization_id=mln.organization_id(+) and moqd.inventory_item_id=mln.inventory_item_id(+) and moqd.lot_number=mln.lot_number(+) and moqd.lpn_id=wlpn.lpn_id(+) group by mp.organization_code, milk.subinventory_code, milk.concatenated_segments, milk.description, mmsv.status_code, mil.project_id, mil.task_id, msiv.concatenated_segments, msiv.description, moqd.revision, moqd.lot_number, mln.expiration_date, wlpn.license_plate_number, msiv.serial_number_control_code, msiv.primary_uom_code order by mp.organization_code, milk.subinventory_code, milk.concatenated_segments, msiv.concatenated_segments, moqd.revision, moqd.lot_number, wlpn.license_plate_number