[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select mmtt.operation_plan_id,
nvl(mmtt.transfer_to_location, mmtt.locator_id),
nvl(mmtt.transfer_subinventory, 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,
wopd.lpn_mtrl_grp_rule_id,
wopd.operation_type,
nvl(wopd.is_in_inventory, 'N'),
mmtt.organization_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 = wopi.organization_id
-- @@@ Commented after Code Review on Sept 11th 2003.
-- and wzb.zone_id(+) = wopd.pre_specified_zone_id
and wopd.operation_plan_detail_id = wooi.operation_plan_detail_id -- Added after review on 07/30/03
-- @@@ Commented after Code Review on Sept 16th 2003.
-- and wopd.operation_plan_id = wopi.operation_plan_id
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 wlpn.outermost_lpn_id
from wms_license_plate_numbers wlpn,
mtl_material_transactions_temp mmtt,
wms_zone_locators wzl,
wms_op_plan_instances wopi,
wms_op_operation_instances wooi,
wms_op_operation_instances wooi2
where wlpn.lpn_context = decode(l_is_in_inventory, 'Y', 1, 3)
and wlpn.organization_id = l_organization_id
and wlpn.subinventory_code = p_subinventory_code -- new
and wlpn.locator_id = p_locator_id -- new
and wlpn.lpn_id = mmtt.lpn_id
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wooi.from_subinventory_code
and wzl.subinventory_code = p_subinventory_code -- new
and wzl.organization_id = l_organization_id -- new
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooi.from_locator_id)
-- *****from the inner cursor****
and wopi.status = 6
and wopi.op_plan_instance_id = wooi.op_plan_instance_id
and wooi2.operation_status = 3 -- Completed -- Typo corrected, earlier it was wooi.operation_status = 3
and wooi2.operation_type_id = 2 -- Drop
and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
and wooi.from_subinventory_code = p_subinventory_code -- new
and wooi.from_locator_id = p_locator_id -- new
and wooi.organization_id = l_organization_id -- new
and wooi.source_task_id = mmtt.transaction_temp_id -- new
and mmtt.inventory_item_id = p_item_id -- new
and mmtt.organization_id = l_organization_id -- new
and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
and wopi.activity_type_id = l_activity_type_id -- 1 (Inbound)
and wopi.organization_id = l_organization_id -- new
and wopi.plan_type_id = decode(l_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
order by wooi.last_update_date desc;
select wlpn.outermost_lpn_id
from mtl_material_transactions_temp mmtt,
wms_license_plate_numbers wlpn,
wms_zone_locators wzl,
wms_op_plan_instances wopi,
wms_op_operation_instances wooi,
wms_op_operation_instances wooi2
where wlpn.lpn_context = decode(l_is_in_inventory, 'Y', 1, 3)
and wlpn.organization_id = l_organization_id
and wlpn.subinventory_code = p_subinventory_code -- new
and wlpn.locator_id = p_locator_id -- new
and wlpn.lpn_id = mmtt.lpn_id
and wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wooi.from_subinventory_code
and wzl.subinventory_code = p_subinventory_code -- new
and wzl.organization_id = l_organization_id -- new
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooi.from_locator_id)
-- *****from the inner cursor****
and wopi.status = 6
and wopi.op_plan_instance_id = wooi.op_plan_instance_id
and wooi2.operation_status = 3 -- Completed -- Typo corrected, earlier it was wooi.operation_status = 3
and wooi2.operation_type_id = 2 -- Drop
and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
and wooi.from_subinventory_code = p_subinventory_code -- new
and wooi.from_locator_id = p_locator_id -- new
and wooi.organization_id = l_organization_id -- new
and wooi.source_task_id = mmtt.transaction_temp_id -- new
and mmtt.organization_id = l_organization_id -- new
and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
and wopi.activity_type_id = l_activity_type_id -- 1
and wopi.organization_id = l_organization_id -- new
and wopi.plan_type_id = decode(l_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
order by mmtt.last_update_date desc;
select wlpn.outermost_lpn_id
from mtl_material_transactions_temp mmtt,
wms_license_plate_numbers wlpn,
wms_op_plan_instances wopi,
wms_op_operation_instances wooi,
wms_op_operation_instances wooi2
where wlpn.lpn_context = decode(l_is_in_inventory, 'Y', 1, 3)
and wlpn.organization_id = l_organization_id
and wlpn.subinventory_code = p_subinventory_code -- new
and wlpn.locator_id = p_locator_id -- new
and wlpn.lpn_id = mmtt.lpn_id
and wopi.status = 6
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 nvl(wooi.is_in_inventory, 'N') = l_is_in_inventory -- new
and wooi.from_subinventory_code = p_subinventory_code -- new
and wooi.from_locator_id = p_locator_id -- new
and wooi.organization_id = l_organization_id -- new
and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
and wooi.source_task_id = mmtt.transaction_temp_id
and mmtt.inventory_item_id = p_item_id
and mmtt.organization_id = l_organization_id -- new
and wopi.activity_type_id = l_activity_type_id
and wopi.organization_id = l_organization_id -- new
and wopi.plan_type_id = decode(l_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
order by wooi.last_update_date desc;
select wlpn.outermost_lpn_id
from mtl_material_transactions_temp mmtt,
wms_license_plate_numbers wlpn,
wms_op_plan_instances wopi,
wms_op_operation_instances wooi,
wms_op_operation_instances wooi2
where wlpn.lpn_context = decode(l_is_in_inventory, 'Y', 1, 3)
and wlpn.organization_id = l_organization_id
and wlpn.subinventory_code = p_subinventory_code -- new
and wlpn.locator_id = p_locator_id -- new
and wlpn.lpn_id = mmtt.lpn_id
and wopi.status = 6
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.organization_id = l_organization_id -- new
and wooi.source_task_id = mmtt.transaction_temp_id
and mmtt.organization_id = l_organization_id -- new
and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
and nvl(wooi.is_in_inventory, 'N') = l_is_in_inventory -- new
and wooi.from_subinventory_code = p_subinventory_code -- new
and wooi.from_locator_id = p_locator_id -- new
and wopi.activity_type_id = l_activity_type_id
and wopi.organization_id = l_organization_id -- new
and wopi.plan_type_id = decode(l_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
and wopi.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
and wopi.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
order by mmtt.last_update_date desc;
select wlpn.outermost_lpn_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
wms_zone_locators wzl,
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn
where wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wlpn.subinventory_code
and wzl.subinventory_code = p_subinventory_code -- new
and wzl.organization_id = l_organization_id -- new
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wlpn.locator_id)
and wlpn.subinventory_code = wdth.dest_subinventory_code
and wlpn.locator_id = wdth.dest_locator_id
and wlpn.subinventory_code = p_subinventory_code -- new
and wlpn.locator_id = p_locator_id -- new
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.dest_locator_id = p_locator_id
and wdth.dest_subinventory_code = p_subinventory_code
and wdth.organization_id = l_organization_id -- new
and wdth.inventory_item_id = p_item_id
and wdth.status = 6 -- Completed task. 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 wooih.organization_id = l_organization_id -- new
and wooih.to_subinventory_code = p_subinventory_code -- new
and wooih.to_locator_id = p_locator_id -- new
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_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
and wopih.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
and wopih.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
order by wlpn.last_update_date desc;
select wlpn.outermost_lpn_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
wms_zone_locators wzl,
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn
where wzl.zone_id = l_pre_specified_zone_id
and wzl.subinventory_code = wlpn.subinventory_code
and wzl.subinventory_code = p_subinventory_code -- new
and wzl.organization_id = l_organization_id -- new
and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wlpn.locator_id)
and wlpn.subinventory_code = wdth.dest_subinventory_code
and wlpn.locator_id = wdth.dest_locator_id
and wlpn.subinventory_code = p_subinventory_code -- new
and wlpn.locator_id = p_locator_id -- new
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.dest_locator_id = p_locator_id
and wdth.dest_subinventory_code = p_subinventory_code
and wdth.organization_id = l_organization_id -- new
and wdth.status = 6 -- Completed task. 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 wooih.organization_id = l_organization_id -- new
and wooih.to_subinventory_code = p_subinventory_code -- new
and wooih.to_locator_id = p_locator_id -- new
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_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
and wopih.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
and wopih.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
order by wlpn.last_update_date desc;
select wlpn.outermost_lpn_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn
where wlpn.subinventory_code = wdth.dest_subinventory_code
and wlpn.locator_id = wdth.dest_locator_id
and wlpn.subinventory_code = p_subinventory_code -- new
and wlpn.locator_id = p_locator_id -- new
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.dest_locator_id = p_locator_id
and wdth.dest_subinventory_code = p_subinventory_code
and wdth.inventory_item_id = p_item_id -- new
and wdth.organization_id = l_organization_id -- new
and wdth.status = 6 -- Completed task. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wooih.organization_id = l_organization_id -- new
and wooih.to_subinventory_code = p_subinventory_code -- new
and wooih.to_locator_id = p_locator_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 wooih.organization_id = l_organization_id -- new
and wooih.to_subinventory_code = p_subinventory_code -- new
and wooih.to_locator_id = p_locator_id -- new
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_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
and wopih.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
and wopih.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
order by wlpn.last_update_date desc;
select wlpn.outermost_lpn_id
from wms_op_plan_instances_hist wopih,
wms_op_opertn_instances_hist wooih,
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn
where wlpn.subinventory_code = wdth.dest_subinventory_code
and wlpn.locator_id = wdth.dest_locator_id
and wlpn.subinventory_code = p_subinventory_code -- new
and wlpn.locator_id = p_locator_id -- new
and wlpn.organization_id = l_organization_id
and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
and wdth.dest_locator_id = p_locator_id
and wdth.dest_subinventory_code = p_subinventory_code
--and wdth.inventory_item_id = p_item_id -- new
and wdth.organization_id = l_organization_id -- new
and wdth.status = 6 -- Completed task. lookup_type is WMS_TASK_STATUS
and wdth.transaction_id = wooih.source_task_id
and wooih.organization_id = l_organization_id -- new
and wooih.to_subinventory_code = p_subinventory_code -- new
and wooih.to_locator_id = p_locator_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 wooih.organization_id = l_organization_id -- new
and wooih.to_subinventory_code = p_subinventory_code -- new
and wooih.to_locator_id = p_locator_id -- new
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_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
and wopih.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
and wopih.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
order by wlpn.last_update_date desc;