[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_char(sysdate, 'RRRR/MM/DD HH24:MI:SS')
from dual;
select mmtt.operation_plan_id,
mmtt.locator_id,
mmtt.subinventory_code,
wopd.operation_plan_detail_id,
wopi.activity_type_id,
wopi.plan_type_id,
wopi.orig_dest_sub_code,
wopi.orig_dest_loc_id,
wopd.pre_specified_zone_id,
wopd.pre_specified_sub_code,
nvl(wopd.loc_mtrl_grp_rule_id, -99),
wopd.operation_type,
nvl(wopd.is_in_inventory, 'N'),
mmtt.organization_id,
mmtt.lpn_id
from wms_op_plan_instances wopi, -- after review on 07/30/03, replaced wms_op_plans_b table with wms_op_plan_instances.
wms_op_plan_details wopd,
-- wms_zones_b wzb, -- Removed after Code Review on Sept 11th 2003.
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi -- Added after review on 07/30/03
where mmtt.organization_id = nvl(wopi.organization_id, mmtt.organization_id)
and wopd.operation_plan_detail_id = wooi.operation_plan_detail_id -- Added after review on 07/30/03
and wopi.op_plan_instance_id = wooi.op_plan_instance_id
and wopi.operation_plan_id = mmtt.operation_plan_id
and wooi.source_task_id = mmtt.transaction_temp_id -- Added after review on 07/30/03
and mmtt.transaction_temp_id = p_task_id -- 6583491 (dmfdv11i)
--
-- @@@ Commented after for bug fix on Sept 16th 2003. One task is tied to a combination of a Load and a Drop. This means
-- @@@ when you query the wooi with the restriction " wooi.source_task_id = mmtt.transaction_temp_id ", it'll bring back
-- @@@ 2 records, 1 each for a load and drop. We are only interested in the Drop. This is all the more important becase
-- @@@ athis API will abort if the Material Grouping Rule ID stamped on the detail line is null. There will exist no
-- @@@ Material Grouping Rule ID for the Load Operation Plan Detail line. We also know that the operation sequence for the
-- @@@ Drop Line is always greater than the Load line and hence the " order by wooi.operation_sequence desc" will bring
-- @@@ back the Drop line first and then the Load line. In any case we only consider the first recoerd and in this case now
-- @@@ it turns out to be the Drop line.
-- order by wopd.operation_type;
select milk.subinventory_code, nvl(milk.physical_location_id, milk.inventory_location_id)
from mtl_item_locations milk,
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi,
wms_op_plan_instances wopi,
wms_license_plate_numbers wlpn1,
wms_license_plate_numbers wlpn2
where wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
and wooi.op_plan_instance_id = wopi.op_plan_instance_id
and wooi.operation_type_id = 2 -- Drop operation
and wooi.operation_status = 2 -- Active
and wooi.source_task_id = mmtt.transaction_temp_id
and mmtt.organization_id = milk.organization_id
and mmtt.lpn_id = wlpn1.lpn_id
and wlpn2.lpn_id = l_lpn_id
and wlpn1.outermost_lpn_id = wlpn2.outermost_lpn_id
and milk.subinventory_code = l_pre_specified_sub_code
and nvl(mmtt.transfer_to_location, mmtt.locator_id) = milk.inventory_location_id
order by wooi.last_update_date desc;
select milk.subinventory_code, nvl(milk.physical_location_id, milk.inventory_location_id)
from mtl_item_locations milk,
wms_zone_locators wzl,
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi,
wms_op_plan_instances wopi,
wms_license_plate_numbers wlpn1,
wms_license_plate_numbers wlpn2
where wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
and wooi.op_plan_instance_id = wopi.op_plan_instance_id
and wooi.operation_type_id = 2 -- Drop operation
and wooi.operation_status = 2 -- Active
and wooi.source_task_id = mmtt.transaction_temp_id
and mmtt.organization_id = milk.organization_id
and mmtt.lpn_id = wlpn1.lpn_id
and wlpn2.lpn_id = l_lpn_id
and wlpn1.outermost_lpn_id = wlpn2.outermost_lpn_id
and nvl(mmtt.transfer_to_location, mmtt.locator_id) = milk.inventory_location_id
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = milk.subinventory_code
and (wzl.entire_sub_flag = 'Y'
or wzl.inventory_location_id = nvl(milk.physical_location_id, milk.inventory_location_id))
order by wooi.last_update_date desc;
select milk.subinventory_code, nvl(milk.physical_location_id, milk.inventory_location_id)
from mtl_item_locations milk,
wms_zone_locators wzl,
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi,
wms_op_plan_instances wopi,
wms_license_plate_numbers wlpn1,
wms_license_plate_numbers wlpn2
where wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
and wooi.op_plan_instance_id = wopi.op_plan_instance_id
and wooi.operation_type_id = 2 -- Drop operation
and wooi.operation_status = 2 -- Active
and wooi.source_task_id = mmtt.transaction_temp_id
and mmtt.organization_id = milk.organization_id
and mmtt.lpn_id = wlpn1.lpn_id
and wlpn2.lpn_id = l_lpn_id
and wlpn1.outermost_lpn_id = wlpn2.outermost_lpn_id
and nvl(mmtt.transfer_to_location, mmtt.locator_id) = milk.inventory_location_id
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = milk.subinventory_code
and milk.subinventory_code = l_pre_specified_sub_code
and (wzl.entire_sub_flag = 'Y'
or wzl.inventory_location_id = Nvl(milk.physical_location_id, milk.inventory_location_id))
order by wooi.last_update_date desc;
select milk.subinventory_code, nvl(milk.physical_location_id, milk.inventory_location_id)
from mtl_item_locations milk,
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi,
wms_op_plan_instances wopi,
wms_license_plate_numbers wlpn1,
wms_license_plate_numbers wlpn2
where wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
and wooi.op_plan_instance_id = wopi.op_plan_instance_id
and wooi.operation_type_id = 2 -- Drop operation
and wooi.operation_status = 2 -- Active
and wooi.source_task_id = mmtt.transaction_temp_id
and mmtt.organization_id = milk.organization_id
and mmtt.lpn_id = wlpn1.lpn_id
and mmtt.inventory_item_id = p_item_id
and wlpn2.lpn_id = l_lpn_id
and wlpn1.outermost_lpn_id = wlpn2.outermost_lpn_id
and milk.subinventory_code = l_pre_specified_sub_code
and nvl(mmtt.transfer_to_location, mmtt.locator_id) = milk.inventory_location_id
order by wooi.last_update_date desc;
select milk.subinventory_code, nvl(milk.physical_location_id, milk.inventory_location_id)
from mtl_item_locations milk,
wms_zone_locators wzl,
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi,
wms_op_plan_instances wopi,
wms_license_plate_numbers wlpn1,
wms_license_plate_numbers wlpn2
where wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
and wooi.op_plan_instance_id = wopi.op_plan_instance_id
and wooi.operation_type_id = 2 -- Drop operation
and wooi.operation_status = 2 -- Active
and wooi.source_task_id = mmtt.transaction_temp_id
and mmtt.organization_id = milk.organization_id
and mmtt.lpn_id = wlpn1.lpn_id
and mmtt.inventory_item_id = p_item_id
and wlpn2.lpn_id = l_lpn_id
and wlpn1.outermost_lpn_id = wlpn2.outermost_lpn_id
and nvl(mmtt.transfer_to_location, mmtt.locator_id) = milk.inventory_location_id
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = milk.subinventory_code
and (wzl.entire_sub_flag = 'Y'
or wzl.inventory_location_id = nvl(milk.physical_location_id, milk.inventory_location_id))
order by wooi.last_update_date desc;
select milk.subinventory_code, nvl(milk.physical_location_id, milk.inventory_location_id)
from mtl_item_locations milk,
wms_zone_locators wzl,
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi,
wms_op_plan_instances wopi,
wms_license_plate_numbers wlpn1,
wms_license_plate_numbers wlpn2
where wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
and wooi.op_plan_instance_id = wopi.op_plan_instance_id
and wooi.operation_type_id = 2 -- Drop operation
and wooi.operation_status = 2 -- Active
and wooi.source_task_id = mmtt.transaction_temp_id
and mmtt.organization_id = milk.organization_id
and mmtt.lpn_id = wlpn1.lpn_id
and mmtt.inventory_item_id = p_item_id
and wlpn2.lpn_id = l_lpn_id
and wlpn1.outermost_lpn_id = wlpn2.outermost_lpn_id
and nvl(mmtt.transfer_to_location, mmtt.locator_id) = milk.inventory_location_id
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = milk.subinventory_code
and milk.subinventory_code = l_pre_specified_sub_code
and (wzl.entire_sub_flag = 'Y'
or wzl.inventory_location_id = Nvl(milk.physical_location_id, milk.inventory_location_id))
order by wooi.last_update_date desc;
select wooi.from_subinventory_code, milk2.inventory_location_id, wzl.zone_id
from mtl_item_locations milk1,
mtl_item_locations milk2,
mtl_secondary_inventories msi,
wms_zone_locators wzl,
-- ***** tables from the inner cursor *****
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi,
wms_op_operation_instances wooi2,
wms_op_plan_instances wopi
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wooi.from_subinventory_code
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooi.from_locator_id)
and wzl.organization_id = l_organization_id
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and wooi.from_subinventory_code = msi.secondary_inventory_name
and msi.secondary_inventory_name = l_pre_specified_sub_code
and msi.organization_id = l_organization_id
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk1.organization_id = milk2.organization_id
and milk1.inventory_location_id = wooi.from_locator_id
and milk1.organization_id = wooi.organization_id
and wooi.from_locator_id = decode(p_mode, 1, wooi.from_locator_id, 2, p_locator_id)
and wooi.organization_id = mmtt.organization_id
-- *****from the inner cursor****
and wopi.organization_id = mmtt.organization_id
and wopi.status = 6 -- (Operation Plan Status : Active)
and wopi.op_plan_instance_id = wooi.op_plan_instance_id
and wooi2.operation_status = 3 -- Completed
and wooi2.operation_type_id = 2 -- Drop
and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
and wooi.source_task_id = mmtt.transaction_temp_id
and mmtt.organization_id = l_organization_id
and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
and mmtt.transaction_temp_id <> p_task_id
and mmtt.inventory_item_id = p_item_id
and wopi.activity_type_id = l_activity_type_id
and wooi.activity_type_id = l_activity_type_id
and wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
order by mmtt.creation_date desc;
select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
mtl_secondary_inventories msi,
wms_zone_locators wzl,
wms_license_plate_numbers wlpn,
wms_dispatched_tasks_history wdth,
mtl_item_locations milk1,
mtl_item_locations milk2
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk2.organization_id = l_organization_id -- newly modified
and milk1.organization_id = milk2.organization_id
and milk1.inventory_location_id = wooih.to_locator_id -- newly modified
and milk1.organization_id = l_organization_id -- newly modified
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wooih.to_subinventory_code -- newly modified
and wzl.subinventory_code = l_pre_specified_sub_code
and wzl.subinventory_code = msi.secondary_inventory_name -- new
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
and wzl.organization_id = l_organization_id
and wlpn.subinventory_code = msi.secondary_inventory_name
and wooih.to_subinventory_code = l_pre_specified_sub_code
and wooih.to_subinventory_code = msi.secondary_inventory_name
and msi.secondary_inventory_name = l_pre_specified_sub_code -- new
and msi.organization_id = l_organization_id --,newly modified
and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.subinventory_code = wooih.to_subinventory_code
and wlpn.subinventory_code = l_pre_specified_sub_code
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wdth.inventory_item_id = p_item_id
and wdth.organization_id = l_organization_id -- new
and wooih.operation_sequence in (select max(operation_sequence)
from wms_op_opertn_instances_hist wooih2
where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
and wooih2.operation_type_id in (2,9)
and wooih2.operation_status = 3)
and wooih.op_plan_instance_id = wopih.op_plan_instance_id
and wopih.status = 3 -- Plan Completed
and wopih.activity_type_id = l_activity_type_id
and wopih.organization_id = l_organization_id -- new
and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
--and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
order by wooih.last_update_date desc;
select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
mtl_secondary_inventories msi,
wms_zone_locators wzl,
wms_license_plate_numbers wlpn,
wms_dispatched_tasks_history wdth,
mtl_item_locations milk1,
mtl_item_locations milk2
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk2.organization_id = l_organization_id -- newly modified
and milk1.organization_id = milk2.organization_id
and milk1.inventory_location_id = wooih.to_locator_id -- newly modified
and milk1.organization_id = l_organization_id -- newly modified
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wooih.to_subinventory_code -- newly modified
and wzl.subinventory_code = l_pre_specified_sub_code
and wzl.subinventory_code = msi.secondary_inventory_name -- new
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
and wzl.organization_id = l_organization_id
and wlpn.subinventory_code = msi.secondary_inventory_name
and wooih.to_subinventory_code = l_pre_specified_sub_code
and wooih.to_subinventory_code = msi.secondary_inventory_name
and msi.secondary_inventory_name = l_pre_specified_sub_code -- new
and msi.organization_id = l_organization_id --,newly modified
and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.subinventory_code = wooih.to_subinventory_code
and wlpn.subinventory_code = l_pre_specified_sub_code
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wdth.inventory_item_id = p_item_id
and wdth.organization_id = l_organization_id -- new
and wooih.operation_sequence in (select max(operation_sequence)
from wms_op_opertn_instances_hist wooih2
where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
and wooih2.operation_type_id in (2,9)
and wooih2.operation_status = 3)
and wooih.op_plan_instance_id = wopih.op_plan_instance_id
and wopih.status = 3 -- Plan Completed
and wopih.activity_type_id = l_activity_type_id
and wopih.organization_id = l_organization_id -- new
--and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
--and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
--and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
and wopih.orig_dest_sub_code = l_orig_dest_sub_code
order by wooih.last_update_date desc;
select wooi.from_subinventory_code, milk2.inventory_location_id, wzl.zone_id
from
mtl_item_locations milk1,
mtl_item_locations milk2,
mtl_secondary_inventories msi,
wms_zone_locators wzl,
-- ***** tables from the inner cursor *****
wms_op_operation_instances wooi,
wms_op_operation_instances wooi2,
wms_op_plan_instances wopi
where
milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wooi.from_subinventory_code
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooi.from_locator_id)
and wzl.organization_id = l_organization_id
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and wooi.from_subinventory_code = msi.secondary_inventory_name
and msi.secondary_inventory_name = l_pre_specified_sub_code
and msi.organization_id = l_organization_id
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk1.organization_id = milk2.organization_id
and milk1.inventory_location_id = wooi.from_locator_id
and milk1.organization_id = wooi.organization_id
and wooi.from_locator_id = decode(p_mode, 1, wooi.from_locator_id, 2, p_locator_id)
and wooi.organization_id = l_organization_id
-- *****from the inner cursor****
and wopi.status = 6 -- (Operation Plan Status : Active, Completed)
and wopi.op_plan_instance_id = wooi.op_plan_instance_id
and wooi2.operation_status = 3 -- Completed
and wooi2.operation_type_id = 2 -- Drop
and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
and wopi.activity_type_id = l_activity_type_id
and wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
order by wooi.creation_date desc;
select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
mtl_secondary_inventories msi,
wms_zone_locators wzl,
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn,
mtl_item_locations milk1,
mtl_item_locations milk2
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk2.organization_id = l_organization_id
and milk1.inventory_location_id = wlpn.locator_id
and milk1.inventory_location_id = wooih.to_locator_id -- new
and milk1.organization_id = l_organization_id
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wlpn.subinventory_code
and wzl.subinventory_code = wooih.to_subinventory_code -- new
and wzl.subinventory_code = l_pre_specified_sub_code -- new
and wzl.subinventory_code = msi.secondary_inventory_name
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
and wzl.organization_id = l_organization_id
and wlpn.subinventory_code = msi.secondary_inventory_name
and msi.secondary_inventory_name = l_pre_specified_sub_code -- new
and msi.organization_id = l_organization_id -- newly modified
and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.subinventory_code = wooih.to_subinventory_code
and wlpn.subinventory_code = l_pre_specified_sub_code -- new
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wooih.operation_sequence in (select max(operation_sequence)
from wms_op_opertn_instances_hist wooih2
-- where wooih2.op_plan_instance_id = wooih.op_plan_instance_id
where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
and wooih2.operation_type_id in (2,9)
and wooih2.operation_status = 3)
and wooih.op_plan_instance_id = wopih.op_plan_instance_id
and wopih.status = 3 -- Plan Completed
and wopih.activity_type_id = l_activity_type_id
and wopih.organization_id = l_organization_id
and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
--and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
order by wooih.last_update_date desc;
select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
mtl_secondary_inventories msi,
wms_zone_locators wzl,
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn,
mtl_item_locations milk1,
mtl_item_locations milk2
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk2.organization_id = l_organization_id
and milk1.inventory_location_id = wlpn.locator_id
and milk1.inventory_location_id = wooih.to_locator_id -- new
and milk1.organization_id = l_organization_id
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wlpn.subinventory_code
and wzl.subinventory_code = wooih.to_subinventory_code -- new
and wzl.subinventory_code = l_pre_specified_sub_code -- new
and wzl.subinventory_code = msi.secondary_inventory_name
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
and wzl.organization_id = l_organization_id
and wlpn.subinventory_code = msi.secondary_inventory_name
and msi.secondary_inventory_name = l_pre_specified_sub_code -- new
and msi.organization_id = l_organization_id -- newly modified
and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.subinventory_code = wooih.to_subinventory_code
and wlpn.subinventory_code = l_pre_specified_sub_code -- new
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wooih.operation_sequence in (select max(operation_sequence)
from wms_op_opertn_instances_hist wooih2
-- where wooih2.op_plan_instance_id = wooih.op_plan_instance_id
where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
and wooih2.operation_type_id in (2,9)
and wooih2.operation_status = 3)
and wooih.op_plan_instance_id = wopih.op_plan_instance_id
and wopih.status = 3 -- Plan Completed
and wopih.activity_type_id = l_activity_type_id
and wopih.organization_id = l_organization_id
--and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
--and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
--and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
and wopih.orig_dest_sub_code = l_orig_dest_sub_code
order by wooih.last_update_date desc;
select wooi.from_subinventory_code, milk2.inventory_location_id, wzl.zone_id
from mtl_item_locations milk1,
mtl_item_locations milk2,
mtl_secondary_inventories msi,
wms_zone_locators wzl,
-- ***** tables from the inner cursor *****
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi,
wms_op_operation_instances wooi2,
wms_op_plan_instances wopi
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wooi.from_subinventory_code
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooi.from_locator_id)
and wzl.organization_id = l_organization_id
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and wooi.from_subinventory_code = msi.secondary_inventory_name
and msi.organization_id = l_organization_id
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk1.organization_id = milk2.organization_id
and milk1.inventory_location_id = wooi.from_locator_id
and milk1.organization_id = wooi.organization_id
and wooi.from_locator_id = decode(p_mode, 1, wooi.from_locator_id, 2, p_locator_id)
and wooi.organization_id = l_organization_id
-- *****from the inner cursor****
and wopi.organization_id = l_organization_id
and wopi.status = 6 -- (Operation Plan Status : Active, Completed)
and wopi.op_plan_instance_id = wooi.op_plan_instance_id
and wooi2.operation_status = 3 -- Completed
and wooi2.operation_type_id = 2 -- Drop
and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
and wooi.source_task_id = mmtt.transaction_temp_id
and mmtt.organization_id = l_organization_id
and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
and mmtt.inventory_item_id = p_item_id
and wopi.activity_type_id = l_activity_type_id
--and wooi.activity_type_id = l_activity_type_id
and wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
order by mmtt.creation_date desc;
select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
from mtl_item_locations milk1,
mtl_item_locations milk2,
mtl_secondary_inventories msi,
wms_zone_locators wzl,
-- ***** tables from the inner cursor *****
wms_license_plate_numbers wlpn,
wms_dispatched_tasks_history wdth,
wms_op_opertn_instances_hist wooih,
wms_op_plan_instances_hist wopih
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk2.organization_id = l_organization_id -- newly modified
and milk1.inventory_location_id = wooih.to_locator_id
and milk1.inventory_location_id = decode(p_mode, 1, milk1.inventory_location_id, 2, p_locator_id)
and milk1.organization_id = wlpn.organization_id
and milk1.organization_id = l_organization_id -- newly modified
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wlpn.subinventory_code
and wzl.subinventory_code = msi.secondary_inventory_name
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
and wzl.organization_id = l_organization_id
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and msi.organization_id = l_organization_id -- newly modified
and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.locator_id = wooih.to_locator_id
and wlpn.subinventory_code = msi.secondary_inventory_name
and wlpn.subinventory_code = wooih.to_subinventory_code
and wlpn.locator_id = decode(p_mode, 1, wlpn.locator_id, 2, p_locator_id)
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wdth.inventory_item_id = p_item_id
and wdth.organization_id = l_organization_id -- new
and wooih.organization_id = l_organization_id -- new
and wooih.to_subinventory_code = msi.secondary_inventory_name -- new
and wooih.operation_sequence in (select max(operation_sequence)
from wms_op_opertn_instances_hist wooih2
where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
and wooih2.operation_type_id in (2,9)
and wooih2.operation_status = 3)
and wopih.op_plan_instance_id = wooih.op_plan_instance_id
and wopih.status = 3 -- Plan Completed
and wopih.activity_type_id = l_activity_type_id
and wopih.organization_id = l_organization_id
and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
--and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
order by wooih.last_update_date desc;
select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
from mtl_item_locations milk1,
mtl_item_locations milk2,
mtl_secondary_inventories msi,
wms_zone_locators wzl,
-- ***** tables from the inner cursor *****
wms_license_plate_numbers wlpn,
wms_dispatched_tasks_history wdth,
wms_op_opertn_instances_hist wooih,
wms_op_plan_instances_hist wopih
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk2.organization_id = l_organization_id -- newly modified
and milk1.inventory_location_id = wooih.to_locator_id
and milk1.inventory_location_id = decode(p_mode, 1, milk1.inventory_location_id, 2, p_locator_id)
and milk1.organization_id = wlpn.organization_id
and milk1.organization_id = l_organization_id -- newly modified
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wlpn.subinventory_code
and wzl.subinventory_code = msi.secondary_inventory_name
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
and wzl.organization_id = l_organization_id
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and msi.organization_id = l_organization_id -- newly modified
and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.locator_id = wooih.to_locator_id
and wlpn.subinventory_code = msi.secondary_inventory_name
and wlpn.subinventory_code = wooih.to_subinventory_code
and wlpn.locator_id = decode(p_mode, 1, wlpn.locator_id, 2, p_locator_id)
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wdth.inventory_item_id = p_item_id
and wdth.organization_id = l_organization_id -- new
and wooih.organization_id = l_organization_id -- new
and wooih.to_subinventory_code = msi.secondary_inventory_name -- new
and wooih.operation_sequence in (select max(operation_sequence)
from wms_op_opertn_instances_hist wooih2
where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
and wooih2.operation_type_id in (2,9)
and wooih2.operation_status = 3)
and wopih.op_plan_instance_id = wooih.op_plan_instance_id
and wopih.status = 3 -- Plan Completed
and wopih.activity_type_id = l_activity_type_id
and wopih.organization_id = l_organization_id
--and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
--and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
--and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
and wopih.orig_dest_sub_code = l_orig_dest_sub_code
order by wooih.last_update_date desc;
select wooi.from_subinventory_code, milk2.inventory_location_id, wzl.zone_id
from mtl_item_locations milk1,
mtl_item_locations milk2,
mtl_secondary_inventories msi,
wms_zone_locators wzl,
-- ***** tables from the inner cursor *****
wms_op_operation_instances wooi,
wms_op_operation_instances wooi2,
wms_op_plan_instances wopi
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wooi.from_subinventory_code
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooi.from_locator_id)
and wzl.organization_id = l_organization_id
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and msi.secondary_inventory_name = wooi.from_subinventory_code
and msi.organization_id = l_organization_id
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk1.organization_id = milk2.organization_id
and milk1.inventory_location_id = decode(p_mode, 1, milk1.inventory_location_id, 2, p_locator_id) -- new
and milk1.inventory_location_id = wooi.from_locator_id
and milk1.organization_id = wooi.organization_id
and wooi.from_locator_id = decode(p_mode, 1, wooi.from_locator_id, 2, p_locator_id)
and wooi.organization_id = l_organization_id
-- *****from the inner cursor****
and wopi.organization_id = l_organization_id
and wopi.status = 6 -- (Operation Plan Status : Active, Completed)
and wopi.op_plan_instance_id = wooi.op_plan_instance_id
and wooi2.operation_status = 3 -- Completed
and wooi2.operation_type_id = 2 -- Drop
and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
and wopi.organization_id = l_organization_id
and wooi.activity_type_id = l_activity_type_id
and wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
order by wooi.creation_date desc;
select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
mtl_secondary_inventories msi,
wms_zone_locators wzl,
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn,
mtl_item_locations milk1,
mtl_item_locations milk2
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk2.organization_id = l_organization_id -- newly modified
and milk1.organization_id = milk2.organization_id
and milk1.inventory_location_id = wlpn.locator_id
and milk1.inventory_location_id = wooih.to_locator_id -- new
and milk1.organization_id = l_organization_id -- newly modified
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wlpn.subinventory_code
and wzl.subinventory_code = msi.secondary_inventory_name -- new
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
and wzl.organization_id = l_organization_id
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and msi.secondary_inventory_name = wooih.to_subinventory_code -- new
and msi.organization_id = l_organization_id -- newly modified
and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.subinventory_code = msi.secondary_inventory_name
and wlpn.subinventory_code = wooih.to_subinventory_code
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wooih.operation_sequence in (select max(operation_sequence)
from wms_op_opertn_instances_hist wooih2
where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
and wooih2.operation_type_id in (2,9)
and wooih2.operation_status = 3)
and wooih.op_plan_instance_id = wopih.op_plan_instance_id
and wopih.status = 3 -- Plan Completed
and wopih.activity_type_id = l_activity_type_id
and wopih.organization_id = l_organization_id
and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
--and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
order by wooih.last_update_date desc;
select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
mtl_secondary_inventories msi,
wms_zone_locators wzl,
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn,
mtl_item_locations milk1,
mtl_item_locations milk2
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk2.organization_id = l_organization_id -- newly modified
and milk1.organization_id = milk2.organization_id
and milk1.inventory_location_id = wlpn.locator_id
and milk1.inventory_location_id = wooih.to_locator_id -- new
and milk1.organization_id = l_organization_id -- newly modified
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wlpn.subinventory_code
and wzl.subinventory_code = msi.secondary_inventory_name -- new
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
and wzl.organization_id = l_organization_id
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and msi.secondary_inventory_name = wooih.to_subinventory_code -- new
and msi.organization_id = l_organization_id -- newly modified
and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.subinventory_code = msi.secondary_inventory_name
and wlpn.subinventory_code = wooih.to_subinventory_code
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wooih.operation_sequence in (select max(operation_sequence)
from wms_op_opertn_instances_hist wooih2
where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
and wooih2.operation_type_id in (2,9)
and wooih2.operation_status = 3)
and wooih.op_plan_instance_id = wopih.op_plan_instance_id
and wopih.status = 3 -- Plan Completed
and wopih.activity_type_id = l_activity_type_id
and wopih.organization_id = l_organization_id
--and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
--and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
--and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
and wopih.orig_dest_sub_code = l_orig_dest_sub_code
order by wooih.last_update_date desc;
select wooi.from_subinventory_code, milk2.inventory_location_id
from
mtl_item_locations milk1,
mtl_item_locations milk2,
mtl_secondary_inventories msi,
-- ***** tables from the inner cursor *****
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi,
wms_op_operation_instances wooi2,
wms_op_plan_instances wopi
where
milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and wooi.from_subinventory_code = msi.secondary_inventory_name
and wooi.from_subinventory_code = l_pre_specified_sub_code
and msi.secondary_inventory_name = l_pre_specified_sub_code
and msi.organization_id = l_organization_id
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk1.organization_id = milk2.organization_id
and milk1.inventory_location_id = wooi.from_locator_id
and milk1.organization_id = wooi.organization_id
and wooi.from_locator_id = decode(p_mode, 1, wooi.from_locator_id, 2, p_locator_id)
and wooi.organization_id = l_organization_id
-- *****from the inner cursor****
and wopi.status = 6 -- (Operation Plan Status : In progress, Completed)
and wopi.op_plan_instance_id = wooi.op_plan_instance_id
and wooi2.operation_status = 3 -- Completed
and wooi2.operation_type_id = 2 -- Drop
and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
and wooi.source_task_id = mmtt.transaction_temp_id -- Added as per Amin in review.
and mmtt.organization_id = l_organization_id
and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
and mmtt.transaction_temp_id <> p_task_id
and mmtt.inventory_item_id = p_item_id
and wopi.activity_type_id = l_activity_type_id
and wooi.activity_type_id = l_activity_type_id
and wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
order by mmtt.creation_date desc;
select wlpn.subinventory_code, milk2.inventory_location_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
mtl_secondary_inventories msi,
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn,
mtl_item_locations milk1,
mtl_item_locations milk2
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and wooih.to_subinventory_code = msi.secondary_inventory_name
and wooih.to_subinventory_code = l_pre_specified_sub_code
and wooih.organization_id = l_organization_id -- new
and wlpn.subinventory_code = msi.secondary_inventory_name
and wlpn.subinventory_code = l_pre_specified_sub_code
and msi.secondary_inventory_name = l_pre_specified_sub_code
and msi.organization_id = l_organization_id -- newly modified
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk2.organization_id = l_organization_id -- new
and milk1.organization_id = l_organization_id -- newly modified
and milk1.inventory_location_id = wooih.to_locator_id -- new
and milk1.inventory_location_id = wlpn.locator_id
and milk1.organization_id = l_organization_id
and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.locator_id = wooih.to_locator_id
and wlpn.subinventory_code = wooih.to_subinventory_code
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wdth.organization_id = l_organization_id
and wdth.inventory_item_id = p_item_id
and wooih.operation_sequence in (select max(operation_sequence)
from wms_op_opertn_instances_hist wooih2
where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
and wooih2.operation_type_id in (2,9)
and wooih2.operation_status = 3)
and wooih.op_plan_instance_id = wopih.op_plan_instance_id
and wooih.organization_id = l_organization_id -- new
and wopih.organization_id = l_organization_id -- new
and wopih.status = 3 -- Plan Completed
and wopih.activity_type_id = l_activity_type_id
and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
--and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
order by wooih.last_update_date desc;
select wlpn.subinventory_code, milk2.inventory_location_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
mtl_secondary_inventories msi,
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn,
mtl_item_locations milk1,
mtl_item_locations milk2
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and wooih.to_subinventory_code = msi.secondary_inventory_name
and wooih.to_subinventory_code = l_pre_specified_sub_code
and wooih.organization_id = l_organization_id -- new
and wlpn.subinventory_code = msi.secondary_inventory_name
and wlpn.subinventory_code = l_pre_specified_sub_code
and msi.secondary_inventory_name = l_pre_specified_sub_code
and msi.organization_id = l_organization_id -- newly modified
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk2.organization_id = l_organization_id -- new
and milk1.organization_id = l_organization_id -- newly modified
and milk1.inventory_location_id = wooih.to_locator_id -- new
and milk1.inventory_location_id = wlpn.locator_id
and milk1.organization_id = l_organization_id
and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.locator_id = wooih.to_locator_id
and wlpn.subinventory_code = wooih.to_subinventory_code
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wdth.organization_id = l_organization_id
and wdth.inventory_item_id = p_item_id
and wooih.operation_sequence in (select max(operation_sequence)
from wms_op_opertn_instances_hist wooih2
where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
and wooih2.operation_type_id in (2,9)
and wooih2.operation_status = 3)
and wooih.op_plan_instance_id = wopih.op_plan_instance_id
and wooih.organization_id = l_organization_id -- new
and wopih.organization_id = l_organization_id -- new
and wopih.status = 3 -- Plan Completed
and wopih.activity_type_id = l_activity_type_id
--and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
--and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
--and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
and wopih.orig_dest_sub_code = l_orig_dest_sub_code
order by wooih.last_update_date desc;
select wooi.from_subinventory_code, milk2.inventory_location_id
from
mtl_item_locations milk1,
mtl_item_locations milk2,
mtl_secondary_inventories msi,
-- ***** tables from the inner cursor *****
wms_op_operation_instances wooi,
wms_op_operation_instances wooi2,
wms_op_plan_instances wopi
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and wooi.from_subinventory_code = msi.secondary_inventory_name
and msi.secondary_inventory_name = l_pre_specified_sub_code
and msi.organization_id = wooi.organization_id
and msi.organization_id = l_organization_id
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk1.organization_id = milk2.organization_id
and milk1.inventory_location_id = wooi.from_locator_id
and milk1.organization_id = wooi.organization_id
and wooi.from_locator_id = decode(p_mode, 1, wooi.from_locator_id, 2, p_locator_id)
-- *****from the inner cursor****
and wopi.status = 6 -- (Operation Plan Status : In progress, Completed)
and wopi.op_plan_instance_id = wooi.op_plan_instance_id
and wooi2.operation_status = 3 -- Completed
and wooi2.operation_type_id = 2 -- Drop
and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
and wopi.activity_type_id = l_activity_type_id
and wooi.organization_id = l_organization_id
and wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
order by wooi.creation_date desc;
select wlpn.subinventory_code, milk2.inventory_location_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
mtl_secondary_inventories msi,
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn,
mtl_item_locations milk1,
mtl_item_locations milk2
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and wooih.to_subinventory_code = msi.secondary_inventory_name
and wlpn.subinventory_code = msi.secondary_inventory_name
and msi.secondary_inventory_name = l_pre_specified_sub_code
and msi.organization_id = l_organization_id
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk1.organization_id = milk2.organization_id
and milk1.inventory_location_id = wooih.to_locator_id
and milk1.inventory_location_id = wlpn.locator_id
and milk1.organization_id = l_organization_id
and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.locator_id = wooih.to_locator_id
and wlpn.subinventory_code = wooih.to_subinventory_code
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wooih.operation_sequence in (select max(operation_sequence)
from wms_op_opertn_instances_hist wooih2
where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
and wooih2.operation_type_id in (2,9)
and wooih2.operation_status = 3)
and wooih.op_plan_instance_id = wopih.op_plan_instance_id
and wopih.status = 3 -- Plan Completed
and wopih.activity_type_id = l_activity_type_id
and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
--and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
order by wooih.last_update_date desc;
select wlpn.subinventory_code, milk2.inventory_location_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
mtl_secondary_inventories msi,
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn,
mtl_item_locations milk1,
mtl_item_locations milk2
where milk2.subinventory_code = msi.secondary_inventory_name
and milk2.organization_id = msi.organization_id
and ((milk2.disable_date is null and msi.disable_date is null)
or (not ( milk2.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and wooih.to_subinventory_code = msi.secondary_inventory_name
and wlpn.subinventory_code = msi.secondary_inventory_name
and msi.secondary_inventory_name = l_pre_specified_sub_code
and msi.organization_id = l_organization_id
and milk2.segment19 is null
and milk2.segment20 is null
and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
and milk1.organization_id = milk2.organization_id
and milk1.inventory_location_id = wooih.to_locator_id
and milk1.inventory_location_id = wlpn.locator_id
and milk1.organization_id = l_organization_id
and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
and wlpn.locator_id = wooih.to_locator_id
and wlpn.subinventory_code = wooih.to_subinventory_code
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wooih.operation_sequence in (select max(operation_sequence)
from wms_op_opertn_instances_hist wooih2
where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
and wooih2.operation_type_id in (2,9)
and wooih2.operation_status = 3)
and wooih.op_plan_instance_id = wopih.op_plan_instance_id
and wopih.status = 3 -- Plan Completed
and wopih.activity_type_id = l_activity_type_id
--and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
--and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
--and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
and wopih.orig_dest_sub_code = l_orig_dest_sub_code
order by wooih.last_update_date desc;
select milk.inventory_location_id, milk.subinventory_code, wzl.zone_id
from mtl_secondary_inventories msi,
mtl_item_locations milk,
wms_zone_locators wzl
where milk.subinventory_code = msi.secondary_inventory_name
and milk.organization_id = msi.organization_id
and ((milk.disable_date is null and msi.disable_date is null)
or (not ( milk.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk.disable_date is null or milk.disable_date >= l_sys_date)))
and milk.segment20 is null
and milk.segment19 is null
and milk.subinventory_code = msi.secondary_inventory_name
and milk.subinventory_code = l_pre_specified_sub_code
and milk.organization_id = l_organization_id -- new
and msi.secondary_inventory_name = wzl.subinventory_code
and msi.secondary_inventory_name = l_pre_specified_sub_code -- new
and msi.organization_id = l_organization_id -- new
and milk.inventory_location_id = decode(p_mode, 1, milk.inventory_location_id, 2, p_locator_id)
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = milk.inventory_location_id)
and wzl.organization_id = l_organization_id
and wzl.subinventory_code = l_pre_specified_sub_code
and wzl.zone_id = l_pre_specified_zone_id
order by nvl(milk.empty_flag, 'N') desc, nvl(milk.location_suggested_units, 0), milk.dropping_order asc, milk.picking_order asc;
select milk.inventory_location_id, milk.subinventory_code, wzl.zone_id
from mtl_secondary_inventories msi,
mtl_item_locations milk,
wms_zone_locators wzl
where milk.subinventory_code = msi.secondary_inventory_name
and milk.organization_id = msi.organization_id
and ((milk.disable_date is null and msi.disable_date is null)
or (not ( milk.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk.disable_date is null or milk.disable_date >= l_sys_date)))
and milk.segment20 is null
and milk.segment19 is null
and milk.subinventory_code = msi.secondary_inventory_name
and milk.organization_id = l_organization_id -- new
and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
and msi.secondary_inventory_name = wzl.subinventory_code
and msi.organization_id = l_organization_id -- new
and milk.inventory_location_id = decode(p_mode, 1, milk.inventory_location_id, 2, p_locator_id)
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = milk.inventory_location_id)
and wzl.organization_id = l_organization_id -- newly modified
and wzl.zone_id = l_pre_specified_zone_id
order by nvl(milk.empty_flag, 'N') desc, nvl(milk.location_suggested_units, 0), milk.dropping_order asc, milk.picking_order asc;
select milk.inventory_location_id, milk.subinventory_code
from mtl_secondary_inventories msi,
mtl_item_locations milk
where ((milk.disable_date is null and msi.disable_date is null)
or (not ( milk.disable_date < l_sys_date
or msi.disable_date < l_sys_date))
or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
and msi.subinventory_type = 2
and (milk.disable_date is null or milk.disable_date >= l_sys_date)))
and milk.segment20 is null
and milk.segment19 is null
and milk.inventory_location_id = decode(p_mode, 1, milk.inventory_location_id, 2, p_locator_id)
and milk.subinventory_code = msi.secondary_inventory_name
and milk.organization_id = l_organization_id -- new
and milk.subinventory_code = l_pre_specified_sub_code -- new
and msi.secondary_inventory_name = l_pre_specified_sub_code
and msi.organization_id = l_organization_id
order by nvl(milk.empty_flag, 'N') desc, nvl(milk.location_suggested_units, 0), milk.dropping_order asc, milk.picking_order asc;