[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
OPEN p_cursor FOR select base.REVISION
,base.LOT_NUMBER
,base.LOT_EXPIRATION_DATE
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,base.COST_GROUP_ID
,base.UOM_CODE
,decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID
,base.SERIAL_NUMBER
,base.primary_quantity
,base.secondary_quantity
,base.grade_code
,NULL consist_string
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM ) order_by_string
from MTL_LOT_NUMBERS mln
,MTL_SYSTEM_ITEMS msi
,WMS_TRX_DETAILS_TMP_V mptdtv
,(
select msn.current_organization_id organization_id
,msn.inventory_item_id
,msn.revision
,msn.lot_number
,lot.expiration_date lot_expiration_date
,msn.current_subinventory_code subinventory_code
,msn.current_locator_id locator_id
,msn.cost_group_id
,msn.status_id --added status_id
,msn.serial_number
,msn.initialization_date date_received
,1 primary_quantity
,null secondary_quantity -- new
,lot.grade_code grade_code -- new
,sub.reservable_type
,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
,WMS_Rule_PVT.GetConversionRate(
nvl(loc.pick_uom_code, sub.pick_uom_code)
,msn.current_organization_id
,msn.inventory_item_id) conversion_rate
,msn.lpn_id lpn_id
,loc.project_id project_id
,loc.task_id task_id
,NULL locator_inventory_item_id
,NULL empty_flag
,NULL location_current_units
from mtl_serial_numbers msn
,mtl_secondary_inventories sub
,mtl_item_locations loc
,mtl_lot_numbers lot
where msn.current_status = 3
and decode(g_unit_number, '-9999', 'a', '-7777', nvl(msn.end_item_unit_number, '-7777'), msn.end_item_unit_number) =
decode(g_unit_number, '-9999', 'a', g_unit_number)
and (msn.group_mark_id IS NULL or msn.group_mark_id = -1)
--and (g_detail_serial IN ( 1,2)
and ( g_detail_any_serial = 2 or (g_detail_any_serial = 1
and g_from_serial_number <= msn.serial_number
and lengthb(g_from_serial_number) = lengthb(msn.serial_number)
and g_to_serial_number >= msn.serial_number
and lengthb(g_to_serial_number) = lengthb(msn.serial_number))
or ( g_from_serial_number is null or g_to_serial_number is null)
)
and sub.organization_id = msn.current_organization_id
and sub.secondary_inventory_name = msn.current_subinventory_code
and loc.organization_id (+)= msn.current_organization_id
and loc.inventory_location_id (+)= msn.current_locator_id
and lot.organization_id (+)= msn.current_organization_id
and lot.inventory_Item_id (+)= msn.inventory_item_id
and lot.lot_number (+)= msn.lot_number
)base
where base.ORGANIZATION_ID = g_organization_id
and base.INVENTORY_ITEM_ID = g_inventory_item_id
and decode(g_subinventory_code, '-9999', 'a', base.SUBINVENTORY_CODE) = decode(g_subinventory_code, '-9999', 'a', g_subinventory_code)
and ((exists (select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not null ) AND exists(select 1 from mtl_material_statuses where status_id = base.STATUS_ID AND RESERVABLE_TYPE = 1)) OR (NOT exists(select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not NULL) and decode(g_subinventory_code, '-9999', base.RESERVABLE_TYPE, 1) = 1))
and decode(g_locator_id, -9999, 1, base.locator_id) = decode(g_locator_id,-9999, 1, g_locator_id)
and decode(g_revision, '-99', 'a', base.REVISION) = decode(g_revision, '-99', 'a', g_revision)
and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
and (decode(g_project_id, -9999, -1, base.project_id) = decode(g_project_id, -9999, -1, g_project_id) OR ( g_project_id = -7777 and base.project_id IS NULL))
and (g_project_id = -9999 OR nvl(base.task_id, -9999) = g_task_id OR (g_task_id = -7777 and base.task_id IS NULL))
and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
and msi.ORGANIZATION_ID = base.ORGANIZATION_ID
and msi.INVENTORY_ITEM_ID = base.INVENTORY_ITEM_ID
and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
and mln.LOT_NUMBER (+) = base.LOT_NUMBER
and (
mln.EXPIRATION_DATE <= sysdate+30
and msi.SHELF_LIFE_CODE <> 1
)
order by base.CONVERSION_RATE desc
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM ) asc
;
OPEN p_cursor FOR select base.REVISION
,base.LOT_NUMBER
,base.LOT_EXPIRATION_DATE
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,base.COST_GROUP_ID
,base.UOM_CODE
,decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID
,NULL SERIAL_NUMBER
,sum(base.primary_quantity)
,sum(base.secondary_quantity)
,base.grade_code
,NULL consist_string
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM ) order_by_string
from MTL_LOT_NUMBERS mln
,MTL_SYSTEM_ITEMS msi
,WMS_TRX_DETAILS_TMP_V mptdtv
,(
select msn.current_organization_id organization_id
,msn.inventory_item_id
,msn.revision
,msn.lot_number
,lot.expiration_date lot_expiration_date
,msn.current_subinventory_code subinventory_code
,msn.current_locator_id locator_id
,msn.cost_group_id
,msn.status_id --added status_id
,msn.serial_number
,msn.initialization_date date_received
,1 primary_quantity
,null secondary_quantity -- new
,lot.grade_code grade_code -- new
,sub.reservable_type
,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
,WMS_Rule_PVT.GetConversionRate(
nvl(loc.pick_uom_code, sub.pick_uom_code)
,msn.current_organization_id
,msn.inventory_item_id) conversion_rate
,msn.lpn_id lpn_id
,loc.project_id project_id
,loc.task_id task_id
,NULL locator_inventory_item_id
,NULL empty_flag
,NULL location_current_units
from mtl_serial_numbers msn
,mtl_secondary_inventories sub
,mtl_item_locations loc
,mtl_lot_numbers lot
where msn.current_status = 3
and decode(g_unit_number, '-9999', 'a', '-7777', nvl(msn.end_item_unit_number, '-7777'), msn.end_item_unit_number) =
decode(g_unit_number, '-9999', 'a', g_unit_number)
and (msn.group_mark_id IS NULL or msn.group_mark_id = -1)
and (g_detail_serial = 3
OR(g_detail_any_serial = 1
OR (g_from_serial_number <= msn.serial_number
AND lengthb(g_from_serial_number) = lengthb(msn.serial_number)
AND g_to_serial_number >= msn.serial_number
AND lengthb(g_to_serial_number) = lengthb(msn.serial_number)
)))
and sub.organization_id = msn.current_organization_id
and sub.secondary_inventory_name = msn.current_subinventory_code
and loc.organization_id (+)= msn.current_organization_id
and loc.inventory_location_id (+)= msn.current_locator_id
and lot.organization_id (+)= msn.current_organization_id
and lot.inventory_Item_id (+)= msn.inventory_item_id
and lot.lot_number (+)= msn.lot_number
and inv_detail_util_pvt.is_serial_trx_allowed(
g_transaction_type_id
,msn.current_organization_id
,msn.inventory_item_id
,msn.status_id) = 'Y' )base
where base.ORGANIZATION_ID = g_organization_id
and base.INVENTORY_ITEM_ID = g_inventory_item_id
and decode(g_subinventory_code, '-9999', 'a', base.SUBINVENTORY_CODE) = decode(g_subinventory_code, '-9999', 'a', g_subinventory_code)
and ((exists (select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not null ) AND exists(select 1 from mtl_material_statuses where status_id = base.STATUS_ID AND RESERVABLE_TYPE = 1)) OR (NOT exists(select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not NULL) and decode(g_subinventory_code, '-9999', base.RESERVABLE_TYPE, 1) = 1))
and decode(g_locator_id, -9999, 1, base.locator_id) = decode(g_locator_id,-9999, 1, g_locator_id)
and decode(g_revision, '-99', 'a', base.REVISION) = decode(g_revision, '-99', 'a', g_revision)
and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
and (decode(g_project_id, -9999, -1, base.project_id) = decode(g_project_id, -9999, -1, g_project_id) OR ( g_project_id = -7777 and base.project_id IS NULL))
and (g_project_id = -9999 OR nvl(base.task_id, -9999) = g_task_id OR (g_task_id = -7777 and base.task_id IS NULL))
and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
and msi.ORGANIZATION_ID = base.ORGANIZATION_ID
and msi.INVENTORY_ITEM_ID = base.INVENTORY_ITEM_ID
and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
and mln.LOT_NUMBER (+) = base.LOT_NUMBER
and (
mln.EXPIRATION_DATE <= sysdate+30
and msi.SHELF_LIFE_CODE <> 1
)
group by base.ORGANIZATION_ID
,base.INVENTORY_ITEM_ID
,base.REVISION
,base.LOT_NUMBER
,base.LOT_EXPIRATION_DATE
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,base.COST_GROUP_ID
,base.PROJECT_ID
,base.TASK_ID
,base.UOM_CODE
,base.GRADE_CODE
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM ),base.CONVERSION_RATE
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM )
order by base.CONVERSION_RATE desc
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM ) asc
;
OPEN p_cursor FOR select base.REVISION
,base.LOT_NUMBER
,base.LOT_EXPIRATION_DATE
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,base.COST_GROUP_ID
,base.UOM_CODE
,decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID
,NULL SERIAL_NUMBER
,sum(base.primary_quantity)
,sum(base.secondary_quantity)
,base.grade_code
,NULL consist_string
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM ) order_by_string
from MTL_LOT_NUMBERS mln
,MTL_SYSTEM_ITEMS msi
,WMS_TRX_DETAILS_TMP_V mptdtv
,(
select msn.current_organization_id organization_id
,msn.inventory_item_id
,msn.revision
,msn.lot_number
,lot.expiration_date lot_expiration_date
,msn.current_subinventory_code subinventory_code
,msn.current_locator_id locator_id
,msn.cost_group_id
,msn.status_id --added status_id
,msn.serial_number
,msn.initialization_date date_received
,1 primary_quantity
,null secondary_quantity -- new
,lot.grade_code grade_code -- new
,sub.reservable_type
,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
,WMS_Rule_PVT.GetConversionRate(
nvl(loc.pick_uom_code, sub.pick_uom_code)
,msn.current_organization_id
,msn.inventory_item_id) conversion_rate
,msn.lpn_id lpn_id
,loc.project_id project_id
,loc.task_id task_id
,NULL locator_inventory_item_id
,NULL empty_flag
,NULL location_current_units
from mtl_serial_numbers msn
,mtl_secondary_inventories sub
,mtl_item_locations loc
,mtl_lot_numbers lot
where msn.current_status = 3
and decode(g_unit_number, '-9999', 'a', '-7777', nvl(msn.end_item_unit_number, '-7777'), msn.end_item_unit_number) =
decode(g_unit_number, '-9999', 'a', g_unit_number)
and (msn.group_mark_id IS NULL or msn.group_mark_id = -1)
and (g_detail_serial = 4
OR(g_detail_any_serial = 1
OR (g_from_serial_number <= msn.serial_number
AND lengthb(g_from_serial_number) = lengthb(msn.serial_number)
AND g_to_serial_number >= msn.serial_number
AND lengthb(g_to_serial_number) = lengthb(msn.serial_number)
)))
and sub.organization_id = msn.current_organization_id
and sub.secondary_inventory_name = msn.current_subinventory_code
and loc.organization_id (+)= msn.current_organization_id
and loc.inventory_location_id (+)= msn.current_locator_id
and lot.organization_id (+)= msn.current_organization_id
and lot.inventory_Item_id (+)= msn.inventory_item_id
and lot.lot_number (+)= msn.lot_number
)base
where base.ORGANIZATION_ID = g_organization_id
and base.INVENTORY_ITEM_ID = g_inventory_item_id
and decode(g_subinventory_code, '-9999', 'a', base.SUBINVENTORY_CODE) = decode(g_subinventory_code, '-9999', 'a', g_subinventory_code)
and ((exists (select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not null ) AND exists(select 1 from mtl_material_statuses where status_id = base.STATUS_ID AND RESERVABLE_TYPE = 1)) OR (NOT exists(select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not NULL) and decode(g_subinventory_code, '-9999', base.RESERVABLE_TYPE, 1) = 1))
and decode(g_locator_id, -9999, 1, base.locator_id) = decode(g_locator_id,-9999, 1, g_locator_id)
and decode(g_revision, '-99', 'a', base.REVISION) = decode(g_revision, '-99', 'a', g_revision)
and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
and (decode(g_project_id, -9999, -1, base.project_id) = decode(g_project_id, -9999, -1, g_project_id) OR ( g_project_id = -7777 and base.project_id IS NULL))
and (g_project_id = -9999 OR nvl(base.task_id, -9999) = g_task_id OR (g_task_id = -7777 and base.task_id IS NULL))
and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
and msi.ORGANIZATION_ID = base.ORGANIZATION_ID
and msi.INVENTORY_ITEM_ID = base.INVENTORY_ITEM_ID
and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
and mln.LOT_NUMBER (+) = base.LOT_NUMBER
and (
mln.EXPIRATION_DATE <= sysdate+30
and msi.SHELF_LIFE_CODE <> 1
)
group by base.ORGANIZATION_ID
,base.INVENTORY_ITEM_ID
,base.REVISION
,base.LOT_NUMBER
,base.LOT_EXPIRATION_DATE
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,base.COST_GROUP_ID
,base.PROJECT_ID
,base.TASK_ID
,base.UOM_CODE
,base.GRADE_CODE
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM ),base.CONVERSION_RATE
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM )
order by base.CONVERSION_RATE desc
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM ) asc
;
OPEN p_cursor FOR select base.REVISION
,base.LOT_NUMBER
,base.LOT_EXPIRATION_DATE
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,base.COST_GROUP_ID
,base.UOM_CODE
,decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID
,NULL SERIAL_NUMBER
,sum(base.primary_quantity)
,sum(base.secondary_quantity)
,base.grade_code
,NULL consist_string
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM ) order_by_string
from MTL_LOT_NUMBERS mln
,MTL_SYSTEM_ITEMS msi
,WMS_TRX_DETAILS_TMP_V mptdtv
,(
SELECT x.organization_id organization_id
,x.inventory_item_id inventory_item_id
,x.revision revision
,x.lot_number lot_number
,x.lot_expiration_date lot_expiration_date
,x.subinventory_code subinventory_code
,x.locator_id locator_id
,x.cost_group_id cost_group_id
,x.status_id status_id
,NULL serial_number
,x.lpn_id lpn_id
,x.project_id project_id
,x.task_id task_id
,x.date_received date_received
,x.primary_quantity primary_quantity
,x.secondary_quantity secondary_quantity
,x.grade_code grade_code
,x.reservable_type reservable_type
,x.locreservable locreservable
,x.lotreservable lotreservable
,NVL(loc.pick_uom_code,sub.pick_uom_code) uom_code
,WMS_Rule_PVT.GetConversionRate(
NVL(loc.pick_uom_code, sub.pick_uom_code)
,x.organization_id
,x.inventory_item_id) conversion_rate
,NULL locator_inventory_item_id
,NULL empty_flag
,NULL location_current_units
FROM (
select x.organization_id
,x.inventory_item_id
,x.revision
,x.lot_number
,lot.expiration_date lot_expiration_date
,x.subinventory_code
,sub.reservable_type
,nvl(x.reservable_type,1) locreservable -- Bug 6719290
,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
,x.locator_id
,x.cost_group_id
,x.status_id --added status_id
,x.date_received date_received
,x.primary_quantity primary_quantity
,x.secondary_quantity secondary_quantity -- new
,lot.grade_code grade_code -- new
,x.lpn_id lpn_id
,x.project_id project_id
,x.task_id task_id
from
(SELECT
moq.organization_id
,moq.inventory_item_id
,moq.revision
,moq.lot_number
,moq.subinventory_code
,moq.locator_id
,moq.cost_group_id
,moq.status_id --added status_id
,mils.reservable_type -- Bug 6719290
,min(NVL(moq.orig_date_received,
moq.date_received)) date_received
,sum(moq.primary_transaction_quantity) primary_quantity
,sum(moq.secondary_transaction_quantity) secondary_quantity -- new
,moq.lpn_id lpn_id
,decode(mils.project_id, mils.project_id, moq.project_id) project_id
,decode(mils.task_id, mils.task_id, moq.task_id) task_id
FROM
mtl_onhand_quantities_detail moq,mtl_item_locations mils
WHERE
moq.organization_id = g_organization_id
AND moq.inventory_item_id = g_inventory_item_id
AND moq.organization_id = mils.organization_id (+)
AND moq.subinventory_code = mils.subinventory_code (+)
AND moq.locator_id = mils.inventory_location_id (+)
GROUP BY
moq.organization_id, moq.inventory_item_id
,moq.revision, moq.lot_number
,moq.subinventory_code, moq.locator_id --added status_id
,moq.cost_group_id,moq.status_id, mils.reservable_type, moq.lpn_id -- Bug 6719290
,decode(mils.project_id, mils.project_id, moq.project_id)
,decode(mils.task_id, mils.task_id, moq.task_id)
) x
,mtl_secondary_inventories sub
,mtl_lot_numbers lot
where x.primary_quantity > 0
and x.organization_id = sub.organization_id
and x.subinventory_code = sub.secondary_inventory_name
and x.organization_id = lot.organization_id (+)
and x.inventory_item_id = lot.inventory_item_id (+)
and x.lot_number = lot.lot_number (+)
) x
,mtl_secondary_inventories sub
,mtl_item_locations loc
WHERE x.organization_id = loc.organization_id (+)
AND x.locator_id = loc.inventory_location_id (+)
AND sub.organization_id = x.organization_id
AND sub.secondary_inventory_name = x.subinventory_code
) base
where base.ORGANIZATION_ID = g_organization_id
and base.INVENTORY_ITEM_ID = g_inventory_item_id
and decode(g_subinventory_code, '-9999', 'a', base.SUBINVENTORY_CODE) = decode(g_subinventory_code, '-9999', 'a', g_subinventory_code)
and ((exists (select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not null ) AND exists(select 1 from mtl_material_statuses where status_id = base.STATUS_ID AND RESERVABLE_TYPE = 1)) OR (NOT exists(select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not NULL) and decode(g_subinventory_code, '-9999', base.RESERVABLE_TYPE, 1) = 1))
and decode(g_locator_id, -9999, 1, base.locator_id) = decode(g_locator_id,-9999, 1, g_locator_id)
and decode(g_revision, '-99', 'a', base.REVISION) = decode(g_revision, '-99', 'a', g_revision)
and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
and (decode(g_project_id, -9999, -1, base.project_id) = decode(g_project_id, -9999, -1, g_project_id) OR ( g_project_id = -7777 and base.project_id IS NULL))
and (g_project_id = -9999 OR nvl(base.task_id, -9999) = g_task_id OR (g_task_id = -7777 and base.task_id IS NULL))
and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
and msi.ORGANIZATION_ID = base.ORGANIZATION_ID
and msi.INVENTORY_ITEM_ID = base.INVENTORY_ITEM_ID
and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
and mln.LOT_NUMBER (+) = base.LOT_NUMBER
and (
mln.EXPIRATION_DATE <= sysdate+30
and msi.SHELF_LIFE_CODE <> 1
)
group by base.ORGANIZATION_ID
,base.INVENTORY_ITEM_ID
,base.REVISION
,base.LOT_NUMBER
,base.LOT_EXPIRATION_DATE
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,base.COST_GROUP_ID
,base.PROJECT_ID
,base.TASK_ID
,base.UOM_CODE
,base.GRADE_CODE
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM ),base.CONVERSION_RATE
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM )
order by base.CONVERSION_RATE desc
,WMS_Parameter_PVT.GetItemOnHand(
base.ORGANIZATION_ID
,mptdtv.INVENTORY_ITEM_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,msi.PRIMARY_UOM_CODE
,mptdtv.TRANSACTION_UOM ) asc
;