[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT transaction_temp_id,
operation_plan_id,
inventory_item_id,
subinventory_code,
locator_id,
transfer_subinventory,
transfer_to_location,
organization_id,
wms_task_type,
primary_quantity
FROM mtl_material_transactions_temp
WHERE transaction_temp_id=p_source_task_id;
SELECT plan_type_id, crossdock_to_wip_flag
FROM WMS_OP_PLANS_B
WHERE operation_plan_id=v_operation_plan_id;
CURSOR c_loc_selection_type(v_op_plan_id NUMBER) IS
SELECT loc_selection_criteria
FROM wms_op_plan_details
WHERE operation_sequence=(SELECT MAX(operation_sequence)
FROM wms_op_plan_details
WHERE operation_plan_id=v_op_plan_id)
AND operation_type IN (G_OP_TYPE_DROP,G_OP_TYPE_CROSSDOCK)
AND operation_plan_id=v_op_plan_id;
SELECT operation_plan_detail_id
, operation_sequence
, is_in_inventory
, operation_type
, subsequent_op_plan_id
FROM WMS_OP_PLAN_DETAILS
WHERE operation_plan_id = v_operation_plan_id
AND operation_sequence = (SELECT MIN(operation_sequence)
FROM WMS_OP_PLAN_DETAILS
WHERE operation_plan_id=v_operation_plan_id);
SELECT 1
FROM WMS_OP_PLAN_INSTANCES
WHERE source_task_id=p_source_task_id
AND activity_type_id=p_activity_id;
SELECT 1
FROM WMS_OP_OPERATION_INSTANCES
WHERE source_task_id=p_source_task_id
AND activity_type_id=p_activity_id;
OPEN c_loc_selection_type(l_operation_plan_id);
FETCH c_loc_selection_type INTO l_final_loc_sel_criter;
CLOSE c_loc_selection_type;
print_debug('Locator Selection Type'||l_final_loc_sel_criter,l_module_name,9);
SELECT WMS_OP_INSTANCE_S.NEXTVAL
INTO l_op_plan_instance_rec.op_plan_instance_id
FROM dual;
print_debug('Call table handler to insert Plan instance',l_module_name,4);
/*Call WMS_OP_PLAN_INSTANCES table handler, WMS_OP_RUNTIME_PVT_APIS.INSERT_OPERATION_PLAN_INSTANCE,
to insert record into the WMS_OP_PLAN_INSTANCES table.*/
WMS_OP_RUNTIME_PVT_APIS.INSERT_PLAN_INSTANCE(
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
p_insert_rec => l_op_plan_instance_rec );
print_debug('Call table handler to insert operation instance',l_module_name,4);
/*Call WMS_OP_OPERATION_INSTANCES table handler, WMS_OP_RUNTIME_PVT_APIS.INSERT_OPERATION_INSTANCE,
to insert record into the WMS_OP_OPERATION_INSTANCES table.*/
WMS_OP_RUNTIME_PVT_APIS.insert_operation_instance
( x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_insert_rec => l_operation_instance_rec
);
l_xd_loc_selection_criteria NUMBER; -- xdock
l_xd_loc_selection_api_id NUMBER; -- xdock
SELECT Nvl(mmtt.transfer_subinventory, mmtt.subinventory_code) subinventory_code,
Nvl(mmtt.transfer_to_location, mmtt.locator_id) locator_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id =
(SELECT child_mmtt.parent_line_id
FROM mtl_material_transactions_temp child_mmtt
WHERE child_mmtt.transaction_temp_id = p_source_task_id);
SELECT wopd.zone_selection_criteria zone_selection_criteria,
wopd.pre_specified_zone_id pre_specified_zone_id,
wopd.zone_selection_api_id zone_selection_api_id,
wopd.sub_selection_criteria sub_selection_criteria,
wopd.pre_specified_sub_code pre_specified_sub_code,
wopd.sub_selection_api_id sub_selection_api_id,
wopd.loc_selection_criteria loc_selection_criteria,
wopd.pre_specified_loc_id pre_specified_loc_id,
wopd.loc_selection_api_id loc_selection_api_id,
wopd.lpn_selection_criteria lpn_selection_criteria,
wopd.lpn_selection_api_id lpn_selection_api_id,
wopd.operation_type operation_type,
nvl(wopd.is_in_inventory,'N') is_in_inventory
FROM wms_op_operation_instances wooi,
wms_op_plan_details wopd
WHERE wooi.activity_type_id = p_activity_type_id
AND wooi.source_task_id = p_source_task_id
AND wooi.operation_status IN (g_op_ins_stat_pending,g_op_ins_stat_active)
AND wooi.operation_plan_detail_id = wopd.operation_plan_detail_id;
SELECT mol.project_id, mol.task_id,mmtt.organization_id, mmtt.operation_plan_id, mmtt.lpn_id,
Nvl(wopb.crossdock_to_wip_flag, 'N') crossdock_to_wip_flag
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mol,
wms_op_plans_b wopb
WHERE mmtt.move_order_line_id = mol.line_id
AND mmtt.operation_plan_id = wopb.operation_plan_id
AND mmtt.transaction_temp_id = p_source_task_id;
SELECT nvl(msi.RESTRICT_SUBINVENTORIES_CODE,2) restrict_subinventories_code
,nvl(msi.RESTRICT_LOCATORS_CODE,2) restrict_locators_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = v_org_id;
SELECT 1
FROM mtl_item_sub_inventories
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = v_org_id
AND secondary_inventory = v_subinventory;
SELECT 1
FROM mtl_secondary_locators
WHERE inventory_item_id = p_inventory_item_id
AND secondary_locator = v_locator_id
AND organization_id = v_org_id;
print_debug('l_plan_details.zone_selection_criteria => ' ||l_plan_details.zone_selection_criteria, l_module_name, 1);
print_debug('l_plan_details.zone_selection_api_id => ' ||l_plan_details.zone_selection_api_id, l_module_name, 1);
print_debug('l_plan_details.sub_selection_criteria => ' ||l_plan_details.sub_selection_criteria, l_module_name, 1);
print_debug('l_plan_details.sub_selection_api_id => ' ||l_plan_details.sub_selection_api_id, l_module_name, 1);
print_debug('l_plan_details.loc_selection_criteria => ' ||l_plan_details.loc_selection_criteria, l_module_name, 1);
print_debug('l_plan_details.loc_selection_api_id => ' ||l_plan_details.loc_selection_api_id, l_module_name, 1);
print_debug('l_plan_details.lpn_selection_criteria => ' ||l_plan_details.lpn_selection_criteria, l_module_name, 1);
print_debug('l_plan_details.lpn_selection_api_id => ' ||l_plan_details.lpn_selection_api_id, l_module_name, 1);
IF NVL(l_plan_details.sub_selection_criteria,0) =
wms_globals.G_OP_DEST_PRE_SPECIFIED
THEN
l_attributes.sug_sub_code := l_plan_details.pre_specified_sub_code;
* IF the selection criteria is Pre-Specified then fetch locator id
* from Plan
*
* If the selection criteria is System suggested or from Custom we
* call the wrapper to fetch the Locator Id
*
* If Rules suggested then this has to be the last operation and
* we get the values from Parent MMTT record
*/
IF NVL(l_plan_details.loc_selection_criteria,0) =
wms_globals.G_OP_DEST_PRE_SPECIFIED
THEN
l_attributes.sug_location_id := l_plan_details.pre_specified_loc_id;
ELSIF NVL(l_plan_details.loc_selection_criteria,0) =
wms_globals.G_OP_DEST_API OR
NVL(l_plan_details.loc_selection_criteria,0) =
wms_globals.G_OP_DEST_CUSTOM_API
THEN
/*
* The API should be validating its inputs so not validating
* for selection_api_id here
*/
IF (l_debug = 1) THEN
print_debug('Before calling wms_atf_dest_locator.get_dest_locator with following parameters: ' , l_module_name, 1);
print_debug('p_hook_call_id => ' || l_plan_details.loc_selection_api_id, l_module_name, 1);
, p_hook_call_id => l_plan_details.loc_selection_api_id
, p_locator_id => NULL
, p_item_id => p_inventory_item_id
, p_api_version => NULL
, p_init_msg_list => NULL
, p_commit => NULL
);
SELECT wopv.operation_plan_name
INTO l_operation_plan_name
FROM wms_op_plans_vl wopv,
mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_source_task_id
AND mmtt.operation_plan_id = wopv.operation_plan_id;
SELECT wopv.operation_plan_name
INTO l_operation_plan_name
FROM wms_op_plans_vl wopv,
mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_source_task_id
AND mmtt.operation_plan_id = wopv.operation_plan_id;
SELECT loc_selection_criteria
, loc_selection_api_id
, consolidation_method_id
, Nvl(drop_lpn_option,2)
INTO l_xd_loc_selection_criteria
, l_xd_loc_selection_api_id
, l_consolidation_method_id
, l_xd_drop_lpn_option
FROM wms_op_plan_details
WHERE operation_plan_id = l_mmtt_data_rec.operation_plan_id
AND operation_type = 2;
print_debug('l_xd_loc_selection_criteria =>'||l_xd_loc_selection_criteria,l_module_name,4);
print_debug('l_xd_loc_selection_api_id =>'||l_xd_loc_selection_api_id,l_module_name,4);
IF (l_xd_loc_selection_criteria = wms_globals.g_op_dest_sys_suggested) THEN --4
IF (l_debug = 1) THEN
print_debug('Outbound plan should not have this value.',l_module_name,4);
ELSIF (l_xd_loc_selection_criteria = wms_globals.g_op_dest_api) THEN --2
IF (l_xd_loc_selection_api_id = 1) THEN--Consolidation Lane
--If it is consolidation based in consolidation loc, always treat
--it as within delivery
x_consolidation_method_id := 2;
p_call_mode => 3, -- xdock locator selection
p_task_type => g_wms_task_type_pick, -- picking
p_task_id => p_source_task_id,
p_locator_id => NULL);
ELSE --l_task_drop_loc_rec.loc_selection_api_id = 1 (Staging Lane)
-- {{
-- Subsequent outbound plan indicates drop to staging lane
-- should suggest staging locator based on delivery consolidation
-- }}
l_progress := '50.20';
SELECT COUNT(line_id)
INTO l_mol_count
FROM mtl_txn_request_lines
WHERE lpn_id = l_mmtt_data_rec.lpn_id;
p_call_mode => 3, -- locator selection
p_task_type => g_wms_task_type_pick, -- picking
p_task_id => p_source_task_id,
p_locator_id => NULL);
END IF;--END IF (l_plan_details.loc_selection_api_id = 1) THEN
END IF;--END IF (l_plan_details.loc_selection_criteria = wms_globals.g_op_dest_sys_suggested) THEN
ELSIF NVL(l_plan_details.loc_selection_criteria,0) =
wms_globals.G_OP_DEST_SYS_SUGGESTED
THEN
IF p_activity_type_id = wms_globals.g_op_activity_inbound THEN
l_progress := '60';
print_debug('Invalid Selection critiera value '||
l_plan_details.loc_selection_criteria, l_module_name, 1);
IF NVL(l_plan_details.lpn_selection_criteria,0) =
wms_globals.G_OP_DEST_API OR
NVL(l_plan_details.lpn_selection_criteria,0) =
wms_globals.G_OP_DEST_CUSTOM_API
THEN
/*
* The API should be validating its inputs so not validating
* for selection_api_id here
*/
IF (l_debug = 1) THEN
print_debug('Before calling wms_atf_dest_lpn.get_dest_lpn with following parameters: ' , l_module_name, 4);
print_debug('p_hook_call_id => ' || l_plan_details.lpn_selection_api_id, l_module_name, 4);
, p_hook_call_id => l_plan_details.lpn_selection_api_id
, p_lpn_id => NULL
, p_item_id => p_inventory_item_id
, p_subinventory_code => l_attributes.sug_sub_code
, p_locator_id => l_attributes.sug_location_id
, p_api_version => NULL
, p_init_msg_list => NULL
, p_commit => NULL
);
IF (x_consolidation_method_id = 2 OR l_xd_loc_selection_api_id = 1) THEN
IF (l_debug = 1) THEN
print_debug('Before calling wms_op_dest_sys_apis.Get_LPN_For_Delivery with following parameters: ' , l_module_name, 4);
ELSIF l_plan_details.lpn_selection_criteria IS NOT NULL THEN
/**
* We should never come here, so raise Data Inconsistency Exception
*/
IF (l_debug = 1) THEN
print_debug('Invalid LPN Selection critiera value '||
l_plan_details.lpn_selection_criteria, l_module_name, 1);
END IF; /* lpn_selection...*/
SELECT status
FROM wms_dispatched_tasks
WHERE transaction_temp_id=p_source_task_id
AND task_type=g_wms_task_type_putaway;
SELECT nvl(wopd.operation_plan_id, mmtt.operation_plan_id) operation_plan_id, -- get from wooi since mmtt may have subsequent plan ID
mmtt.transaction_source_type_id,
mmtt.transaction_action_id,
mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.parent_line_id,
mmtt.primary_quantity,
mmtt.move_order_line_id
FROM mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi,
wms_op_plan_details wopd
WHERE mmtt.transaction_temp_id=p_source_task_id
AND mmtt.transaction_temp_id = wooi.source_task_id (+)
AND wooi.operation_plan_detail_id = wopd.operation_plan_detail_id(+);
SELECT operation_type_id,
operation_status,
op_plan_instance_id,
operation_plan_detail_id,
operation_instance_id
FROM wms_op_operation_instances
WHERE source_task_id=p_source_task_id
AND operation_status IN (G_OP_INS_STAT_PENDING,G_OP_INS_STAT_ACTIVE)
ORDER BY operation_sequence DESC;
SELECT count(op_plan_instance_id) count
FROM wms_op_operation_instances
WHERE op_plan_instance_id=v_op_plan_instance_id
GROUP BY op_plan_instance_id;
SELECT effective_start_date,effective_end_date
FROM per_all_people_f
WHERE person_id = v_person_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
/* Create or update WDT
This API can be called from 'Load', 'Drop', or 'Inspect' UI.
Only when called from drop, there will be a WDT record with 'Loaded' status and
'putaway' task type.For the other two cases, a new WDT record needs to be created.
*/
l_progress:=20;
*Delete this WDT record*/
IF (l_wdt_details.status=g_task_status_dispatched) THEN
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id=p_source_task_id;
/*Non ATF case..Return after inserting WDT*/
IF (l_debug=1) THEN
print_debug('Operation Plan Id null on document record',l_module_name,9);
print_debug('Calling the table handler to insert WDT records',l_module_name,9);
print_debug('Calling insert_Dispatched_tasks with the following values for WDT record',l_module_name,4);
print_debug('last_update_date ==>'||l_wdt_rec.last_update_date,l_module_name,4);
print_debug('last_update_by ==>'||l_wdt_rec.last_updated_by,l_module_name,4);
WMS_OP_RUNTIME_PVT_APIS.Insert_Dispatched_tasks
( p_wdt_rec => l_wdt_rec,
p_source_task_id => p_source_task_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END IF; /*WDT Record to be inserted*/
/*Calling the table handler to insert records in WDT*/
IF (l_wdt_rec.transaction_temp_id IS NOT NULL) THEN /*WDT record needs to be inserted*/
l_progress:=90;
print_debug('Calling the table handler to insert WDT records',l_module_name,9);
print_debug('Calling insert_Dispatched_tasks with the following values for WDT record',l_module_name,4);
print_debug('last_update_date ==>'||l_wdt_rec.last_update_date,l_module_name,4);
print_debug('last_update_by ==>'||l_wdt_rec.last_updated_by,l_module_name,4);
WMS_OP_RUNTIME_PVT_APIS.Insert_Dispatched_tasks
( p_wdt_rec => l_wdt_rec,
p_source_task_id => p_source_task_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END IF; /*WDT Record to be inserted*/
print_debug('Current Operation status is Pending,Update it to Active',l_module_name,9);
/*If the status for this WOOI record is 'pending' update it to 'Active'*/
l_wooi_rec.operation_status := G_OP_INS_STAT_ACTIVE;
* In this case, update WMS_OP_PLAN_INSTANCES status to 'Active' for this OP_PLAN_INSTANCE_ID.*/
OPEN c_operation_sequence(l_op_plan_instance_id);
print_debug('First operation in the plan,hence update plan status to In Progress',l_module_name,9);
print_debug('Calling WMS_OP_RUNTIME_PVT_APIS.UPDATE_PLAN_INSTANCE to update the foll values',l_module_name,9);
WMS_OP_RUNTIME_PVT_APIS.UPDATE_PLAN_INSTANCE
( p_update_rec => l_wopi_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
p_update_param_rec => l_dest_param_rec,
p_document_rec => l_mmtt_rec);
WMS_OP_RUNTIME_PVT_APIS.UPDATE_OPERATION_INSTANCE
( x_return_status => l_return_status --Use l_return_status instead
, x_msg_count => l_msg_count --of x_return_status because
, x_msg_data => l_msg_data --it will override the value
, p_update_rec => l_wooi_rec); --returned from determine_attributes
SELECT transaction_temp_id,
operation_plan_id,
inventory_item_id,
subinventory_code,
locator_id,
transfer_subinventory,
transfer_to_location,
organization_id,
Decode(wms_task_type, -1, g_wms_task_type_putaway, wms_task_type) wms_task_type,
parent_line_id,
transaction_action_id,
transaction_source_type_id,
transaction_type_id,
move_order_line_id,
transaction_uom,
transaction_quantity,
lpn_id,
content_lpn_id,
transfer_lpn_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id=p_source_task_id;
SELECT wooi.operation_instance_id,
wooi.operation_type_id,
wooi.operation_plan_detail_id,
wooi.op_plan_instance_id,
wooi.operation_status,
wooi.operation_sequence,
wooi.sug_to_sub_code,
wooi.sug_to_locator_id,
wooi.organization_id,
wopd.operation_plan_id
FROM wms_op_operation_instances wooi,
wms_op_plan_details wopd
WHERE wooi.source_task_id = p_source_task_id
AND wooi.activity_type_id = p_activity_id
AND wooi.operation_plan_detail_id = wopd.operation_plan_detail_id
ORDER BY wooi.operation_sequence DESC,
wooi.operation_type_id DESC; -- this is added for crossdock, the last two operations will have the same operation_sequence. But we want to show drop first in this query
SELECT wopd1.operation_plan_detail_id
, wopd1.operation_sequence
, wopd1.is_in_inventory
, wopd1.operation_type
, Decode(Nvl(wopb.crossdock_to_wip_flag, 'N'),'N', Nvl(wopd1.subsequent_op_plan_id, 1),v_operation_plan_id) subsequent_op_plan_id-- added for planned xdocking
FROM wms_op_plan_details wopd1,
wms_op_plans_b wopb
WHERE wopd1.operation_plan_id = v_operation_plan_id
AND wopb.operation_plan_id = v_operation_plan_id
AND wopd1.operation_sequence = (SELECT MIN(wopd2.operation_sequence)
FROM wms_op_plan_details wopd2
WHERE wopd2.operation_plan_id = v_operation_plan_id
AND (wopd2.operation_sequence > v_current_detail_sequence -- operations after the current
OR (wopd2.operation_type = g_op_type_crossdock -- when current is a load operation for crossdock, sequence can be same as current operation
AND wopd2.operation_sequence = v_current_detail_sequence)
)
);
SELECT task_id, status
INTO l_wdt_task_id, l_wdt_status
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_source_task_id
AND task_type IN (g_wms_task_type_putaway, g_wms_task_type_inspect);
print_debug('Current operation is load. Update WDT to loaded.' ,l_module_name,4);
UPDATE wms_dispatched_tasks
SET status = g_task_status_loaded,
loaded_time = Sysdate,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id
WHERE task_id = l_wdt_task_id;
SELECT MAX(wopd1.operation_sequence)
INTO l_last_op_sequence
FROM wms_op_plan_details wopd1
WHERE wopd1.operation_plan_id =
(SELECT wopd2.operation_plan_id
FROM wms_op_plan_details wopd2
WHERE wopd2.operation_plan_detail_id = l_wooi_data_rec.operation_plan_detail_id
);
SELECT
Nvl(lpn_controlled_flag, 2) -- 2 is non-lpn controlled
INTO l_lpn_controlled_flag
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = Nvl(l_mmtt_rec.transfer_subinventory,
l_mmtt_rec.subinventory_code)
AND organization_id = l_mmtt_rec.organization_id;
SELECT
loc_selection_criteria,
loc_selection_api_id
INTO
l_loc_sel_criteria_id,
l_loc_sel_api_id
FROM wms_op_plan_details
WHERE operation_plan_detail_id = l_wooi_data_rec.operation_plan_detail_id;
print_debug('Before calling wms_op_runtime_pvt_apis.update_operation_instance with following parameters: ',l_module_name,4);
wms_op_runtime_pvt_apis.update_operation_instance
(x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_update_rec => l_wooi_rec);
print_debug('After calling wms_op_runtime_pvt_apis.update_operation_instance: ' ,l_module_name,4);
print_debug('wms_op_runtime_pvt_apis.update_operation_instance finished with error. l_return_status = ' || l_return_status,l_module_name,4);
-- update document table
IF p_activity_id = g_op_activity_inbound THEN
IF (l_debug=1) THEN
print_debug('NOT Last operation in plan and inbound - Before calling wms_op_inbound_pvt.complete with following parameters:',l_module_name,4);
print_debug('Before calling wms_op_runtime_pvt_apis.insert_operation_instance:' ,l_module_name,4);
wms_op_runtime_pvt_apis.insert_operation_instance
(
x_return_status => l_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
p_insert_rec => l_wooi_rec );
print_debug('After calling wms_op_runtime_pvt_apis.insert_operation_instance:' ,l_module_name,4);
print_debug('wms_op_runtime_pvt_apis.insert_operation_instance finished with error. l_return_status = ' || l_return_status,l_module_name,4);
SELECT mol.inspection_status,
SUM(NVL(mmtt.primary_quantity, wms_task_dispatch_gen.get_primary_quantity(mol.inventory_item_id,mol.organization_id,(mol.quantity - Nvl(mol.quantity_delivered,0)),mol.uom_code))) quantity,
mmtt.operation_plan_id,mmtt.transaction_temp_id,mol.lpn_id,mol.inventory_item_id
FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
WHERE mol.line_id = mmtt.move_order_line_id(+)
AND mol.line_id = p_move_order_line_id
AND mol.organization_id = p_organization_id
AND mol.organization_id = mmtt.organization_id(+)
AND mol.line_status <> 5 -- bug 4179991
GROUP BY mol.inspection_status,mmtt.operation_plan_id,mmtt.transaction_temp_id, mol.lpn_id, mol.inventory_item_id;
SELECT mol.inspection_status,mmtt.operation_plan_id,mmtt.transaction_temp_id
FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
WHERE mol.line_id = mmtt.move_order_line_id(+)
AND mol.lpn_id = p_lpn_id
AND mol.organization_id = p_organization_id
AND mol.line_status <> 5 -- bug 4179991
AND mol.organization_id = mmtt.organization_id(+);
SELECT mol.inspection_status,
SUM(NVL(mmtt.primary_quantity,
wms_task_dispatch_gen.get_primary_quantity(mol.inventory_item_id,mol.organization_id,(mol.quantity - Nvl(mol.quantity_delivered, 0)),mol.uom_code))) quantity,
mol.lpn_id,mmtt.operation_plan_id,mmtt.transaction_temp_id,mol.lot_number,mol.revision
FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
WHERE mol.lpn_id = p_lpn_id
AND mol.inventory_item_id = p_inventory_item_id
AND mol.line_id = mmtt.move_order_line_id(+)
AND mol.organization_id = p_organization_id
AND mol.organization_id = mmtt.organization_id(+)
AND mol.line_status <> 5 -- bug 4179991
GROUP BY mol.lpn_id,mol.inspection_status,mmtt.operation_plan_id,mmtt.transaction_temp_id,mol.lot_number,mol.revision;
SELECT wopi.operation_type_id,
wopd.operation_type wopd_op_type_id
FROM wms_op_operation_instances wopi,
wms_op_plan_details wopd
WHERE wopi.source_task_id = v_source_task_id
AND wopi.operation_status IN (G_OP_INS_STAT_PENDING,G_OP_INS_STAT_ACTIVE)
AND wopi.operation_plan_detail_id = wopd.operation_plan_detail_id
ORDER BY wopi.operation_sequence DESC;
SELECT mol.inspection_status,mmtt.operation_plan_id,
NVL(mmtt.primary_quantity, wms_task_dispatch_gen.get_primary_quantity(mol.inventory_item_id,mol.organization_id,(mol.quantity - Nvl(mol.quantity_delivered,0)),mol.uom_code)) quantity
FROM mtl_material_transactions_temp mmtt , mtl_txn_request_lines mol
WHERE mmtt.transaction_temp_id = p_source_task_id
AND mol.line_status <> 5 -- bug 4179991
AND mmtt.move_order_line_id = mol.line_id;
SELECT SUM(primary_transaction_quantity) quantity,lpn_id,inventory_item_id,lot_number,revision
FROM mtl_onhand_quantities_detail
WHERE lpn_id = p_lpn_id
--3631633
--Force to use the N5 index
AND subinventory_code = l_subinventory_code
AND locator_id = l_locator_id
AND inventory_item_id =nvl(p_inventory_item_id,inventory_item_id)
AND organization_id = p_organization_id
GROUP BY lpn_id,inventory_item_id,lot_number,revision;
l_src_taskid_tab.DELETE;
--Select license plate number details
IF p_lpn_id IS NOT NULL THEN
select subinventory_code,locator_id into l_subinventory_code,l_locator_id from WMS_LICENSE_PLATE_NUMBERS where lpn_id=p_lpn_id;
SELECT Decode(revision_qty_control_code,1,'FALSE','TRUE'),
Decode(lot_control_code,1,'FALSE','TRUE'),
Decode(serial_number_control_code,1,'FALSE',6,'FALSE','TRUE')
INTO l_rev_control_code,
l_lot_control_code,
l_serial_control_code
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT primary_quantity
INTO l_mmtt_pri_qty
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_src_taskid_tab(j);
SELECT operation_plan_id
, parent_line_id
, transaction_quantity
, primary_quantity
, transaction_temp_id
, inventory_item_id
, organization_id
, move_order_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id=v_task_id;
SELECT nvl(lot_control_code,1)lot_control_code,nvl(serial_number_control_code,1) serial_number_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = v_inventory_item_id
AND organization_id = v_organization_id;
SELECT *
/* OP_PLAN_INSTANCE_ID,
OPERATION_PLAN_ID,
ACTIVITY_TYPE_ID,
PLAN_TYPE_ID,
SOURCE_TASK_ID,
STATUS,
PLAN_EXECUTION_START_DATE,
PLAN_EXECUTION_END_DATE,
ORGANIZATION_ID,
ORIG_SOURCE_SUB_CODE,
ORIG_SOURCE_LOC_ID,
ORIG_DEST_SUB_CODE,
ORIG_DEST_LOC_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15 */
FROM wms_op_plan_instances
WHERE source_task_id = v_source_task_id;
SELECT *
/*
OPERATION_INSTANCE_ID,
OPERATION_PLAN_DETAIL_ID,
OPERATION_SEQUENCE,
OPERATION_TYPE_ID,
OP_PLAN_INSTANCE_ID,
OPERATION_STATUS,
ACTIVITY_TYPE_ID,
SOURCE_TASK_ID,
ACTIVATE_TIME,
COMPLETE_TIME,
SUG_TO_SUB_CODE,
SUG_TO_LOCATOR_ID,
FROM_SUBINVENTORY_CODE,
FROM_LOCATOR_ID,
TO_SUBINVENTORY_CODE,
TO_LOCATOR_ID,
IS_IN_INVENTORY,
ORGANIZATION_ID,
EMPLOYEE_ID,
EQUIPMENT_ID,
CREATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
*/
FROM wms_op_operation_instances
WHERE op_plan_instance_id = v_op_plan_instance_id
ORDER BY operation_sequence DESC, operation_type_id DESC;
SELECT task_id,
transaction_id
FROM wms_dispatched_tasks_history
WHERE op_plan_instance_id = v_op_plan_instance_id;
SELECT COUNT(1) INTO l_num_wdts_for_mmtt
FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_inbound_doc_child_rec.transaction_temp_id;
SELECT wms_dispatched_tasks_s.NEXTVAL
INTO l_next_task_id
FROM DUAL;
print_debug('Inserting Duplicate WDT records using ID:'||l_next_task_id,l_module_name,3);
INSERT INTO wms_dispatched_tasks
(
task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, loaded_time
, drop_off_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, task_type
, priority
, task_group_id
, device_id
, device_invoked
, device_request_id
, suggested_dest_subinventory
, suggested_dest_locator_id
, operation_plan_id
, move_order_line_id
, transfer_lpn_id
, op_plan_instance_id
, task_method
)
select l_next_task_id
, l_inbound_doc_child_split_rec.transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, loaded_time
, drop_off_time
, SYSDATE
, FND_GLOBAL.USER_ID
, creation_date
, created_by
, fnd_global.USER_ID
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, task_type
, priority
, task_group_id
, device_id
, device_invoked
, device_request_id
, suggested_dest_subinventory
, suggested_dest_locator_id
, operation_plan_id
, l_inbound_doc_child_split_rec.move_order_line_id
, transfer_lpn_id
, l_wopi_split_rec.op_plan_instance_id
, task_method
FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_inbound_doc_child_rec.transaction_temp_id;
/*update parent transaction quantity, PRIMARY_QUANTITY, create MTLT if appropriate*/
l_inbound_doc_parent_rec.transaction_quantity := l_inbound_doc_child_rec.transaction_quantity;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = l_inbound_doc_parent_rec.transaction_quantity,
primary_quantity = l_inbound_doc_child_rec.primary_quantity,
last_update_date = SYSDATE
WHERE transaction_temp_id = l_inbound_doc_parent_rec.transaction_temp_id;
--delete MTLT where transaction_temp_id = l_inbound_doc_parent_rec.transaction_temp_id
DELETE FROM mtl_transaction_lots_temp WHERE transaction_temp_id = l_inbound_doc_parent_rec.transaction_temp_id;
INSERT INTO mtl_transaction_lots_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, transaction_quantity
, primary_quantity
, secondary_quantity
, secondary_unit_of_measure
, lot_number
, lot_expiration_date
, serial_transaction_temp_id
, description
, vendor_name
, supplier_lot_number
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, status_id
, retest_date
, age
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, vendor_id
, territory_code
)
SELECT l_inbound_doc_parent_rec.transaction_temp_id
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, l_inbound_doc_parent_rec.transaction_quantity
, l_inbound_doc_parent_rec.primary_quantity
, secondary_quantity
, secondary_unit_of_measure
, lot_number
, lot_expiration_date
, serial_transaction_temp_id
, description
, vendor_name
, supplier_lot_number
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, status_id
, retest_date
, age
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, vendor_id
, territory_code
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_inbound_doc_child_rec.transaction_temp_id;
SELECT COUNT(1) INTO l_num_wdts_for_mmtt
FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_inbound_doc_child_rec.transaction_temp_id;
* Update parent_line_id for new child task records
*/
FOR i IN 1 .. p_new_task_id_table.COUNT LOOP
/*query document table based on new_p_task_id_table(i)*/
OPEN inbound_document_details(p_new_task_id_table(i));
--call MMTT(MTLT) table handlers to insert the new parent RECORD l_inbound_doc_parent_split_rec
inv_trx_util_pub.copy_insert_line_trx(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, x_new_txn_temp_id => l_inbound_doc_parent_split_rec.transaction_temp_id
, p_transaction_temp_id => l_inbound_doc_parent_rec.transaction_temp_id
, p_txn_qty => l_inbound_doc_parent_split_rec.transaction_quantity
, p_primary_qty => l_inbound_doc_parent_split_rec.primary_quantity
);
--call MMTT table handlers to update the new child task record
UPDATE mtl_material_transactions_temp
SET parent_line_id = l_inbound_doc_parent_split_rec.transaction_temp_id
WHERE transaction_temp_id = l_inbound_doc_child_split_rec.transaction_temp_id;
--replace the transaction_temp_id with l_inbound_doc_parent_split_rec.transaction_temp_id, and insert into mtlt table
OPEN c_item_details(l_inbound_doc_child_split_rec.INVENTORY_ITEM_ID,l_inbound_doc_child_split_rec.organization_id);
INSERT INTO mtl_transaction_lots_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, transaction_quantity
, primary_quantity
, secondary_quantity
, secondary_unit_of_measure
, lot_number
, lot_expiration_date
, serial_transaction_temp_id
, description
, vendor_name
, supplier_lot_number
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, status_id
, retest_date
, age
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, vendor_id
, territory_code
)
SELECT l_inbound_doc_parent_split_rec.transaction_temp_id
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, l_inbound_doc_parent_split_rec.transaction_quantity
, l_inbound_doc_parent_split_rec.primary_quantity
, secondary_quantity
, secondary_unit_of_measure
, lot_number
, lot_expiration_date
, serial_transaction_temp_id
, description
, vendor_name
, supplier_lot_number
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, status_id
, retest_date
, age
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, vendor_id
, territory_code
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_inbound_doc_child_split_rec.transaction_temp_id;
SELECT WMS_OP_INSTANCE_S.NEXTVAL
INTO l_wopi_split_rec.op_plan_instance_id
FROM dual;
--call wms_op_plan_instance table handler to insert l_wopi_split_rec INTO the table
l_progress := 180;
WMS_OP_RUNTIME_PVT_APIS.INSERT_PLAN_INSTANCE(
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
p_insert_rec => l_wopi_split_rec );
* update transaction_quantity of the original WDTHs to be same
* as l_inbound_doc_parent_rec.
*/
l_progress := 185;
UPDATE wms_dispatched_tasks_history
SET transaction_quantity = l_inbound_doc_parent_rec.transaction_quantity,
last_update_date = Sysdate -- bug 3827507
WHERE op_plan_instance_id =l_wopi_orig_rec.op_plan_instance_id;
SELECT wms_dispatched_tasks_s.NEXTVAL
INTO l_next_task_id
FROM DUAL;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_temp_id
FROM dual;
--INSERT INTO wms_dispatched_tasks_history ???;
INSERT INTO wms_dispatched_tasks_history
(
task_id
, transaction_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, loaded_time
, drop_off_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, task_type
, priority
, task_group_id
, suggested_dest_subinventory
, suggested_dest_locator_id
, operation_plan_id
, move_order_line_id
, transfer_lpn_id
, transaction_batch_id
, transaction_batch_seq
, inventory_item_id
, revision
, transaction_quantity
, transaction_uom_code
, source_subinventory_code
, source_locator_id
, dest_subinventory_code
, dest_locator_id
, lpn_id
, content_lpn_id
, is_parent
, parent_transaction_id
, transfer_organization_id
, source_document_id
, op_plan_instance_id
, task_method
, transaction_type_id
, transaction_source_type_id
, transaction_action_id
, transaction_temp_id -- new link between wdth and we
)
SELECT l_next_task_id
, transaction_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, loaded_time
, drop_off_time
, SYSDATE
, fnd_global.user_id
, SYSDATE -- bug 3827507
, fnd_global.user_id -- bug 3827507
, fnd_global.user_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, task_type
, priority
, task_group_id
, suggested_dest_subinventory
, suggested_dest_locator_id
, operation_plan_id
, l_inbound_doc_child_split_rec.move_order_line_id
, transfer_lpn_id
, transaction_batch_id
, transaction_batch_seq
, inventory_item_id
, revision
, l_inbound_doc_parent_split_rec.transaction_quantity
, transaction_uom_code
, source_subinventory_code
, source_locator_id
, dest_subinventory_code
, dest_locator_id
, lpn_id
, content_lpn_id
, is_parent
, l_inbound_doc_parent_split_rec.transaction_temp_id
, transfer_organization_id
, source_document_id
, l_wopi_split_rec.op_plan_instance_id
, task_method
, transaction_type_id
, transaction_source_type_id
, transaction_action_id
, l_new_temp_id -- new value to link wdth and we -- lezhang
FROM wms_dispatched_tasks_history
WHERE task_id = l_task_id;
INSERT INTO wms_exceptions
(
task_id
, sequence_number
, organization_id
, inventory_item_id
, person_id
, effective_start_date
, effective_end_date
, inventory_location_id
, reason_id
, discrepancy_type
, archive_flag
, subinventory_code
, lot_number
, revision
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, transaction_header_id
, wms_task_type
, wf_item_key
, lpn_id
)
SELECT
l_new_temp_id -- new value to link wdth and we -- lezhang
, sequence_number
, organization_id
, inventory_item_id
, person_id
, effective_start_date
, effective_end_date
, inventory_location_id
, reason_id
, discrepancy_type
, archive_flag
, subinventory_code
, lot_number
, revision
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, transaction_header_id
, wms_task_type
, wf_item_key
, lpn_id
FROM wms_exceptions
WHERE task_id = l_old_temp_id;
* Now we update wooi.source_task_id if wooi.status =
* 'Pending'. We need to change this to: update
* wooi.source_task_id for the last wooi, if the last step is
* load; and update wooi.source_task_id for the last TWO wooi
--Call wms_op_operation_instances table handler to insert the record
SELECT wms_op_instance_s.NEXTVAL
INTO l_wooi_new_rec.operation_instance_id
FROM dual;
WMS_OP_RUNTIME_PVT_APIS.insert_operation_instance
( x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_insert_rec => l_wooi_new_rec
);
--INSERT INTO wms_dispatched_tasks ???;
SELECT wms_dispatched_tasks_s.NEXTVAL
INTO l_next_task_id
FROM DUAL;
INSERT INTO wms_dispatched_tasks
(
task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, loaded_time
, drop_off_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, task_type
, priority
, task_group_id
, device_id
, device_invoked
, device_request_id
, suggested_dest_subinventory
, suggested_dest_locator_id
, operation_plan_id
, move_order_line_id
, transfer_lpn_id
, op_plan_instance_id
, task_method
)
select l_next_task_id
, l_inbound_doc_child_split_rec.transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, loaded_time
, drop_off_time
, SYSDATE
, FND_GLOBAL.USER_ID
, creation_date
, created_by
, fnd_global.USER_ID
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, task_type
, priority
, task_group_id
, device_id
, device_invoked
, device_request_id
, suggested_dest_subinventory
, suggested_dest_locator_id
, operation_plan_id
, l_inbound_doc_child_split_rec.move_order_line_id
, transfer_lpn_id
, l_wopi_split_rec.op_plan_instance_id
, task_method
FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_inbound_doc_child_rec.transaction_temp_id;
SELECT transaction_temp_id,
operation_plan_id,
organization_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id=p_source_task_id;
SELECT operation_instance_id,
operation_type_id,
operation_plan_detail_id,
op_plan_instance_id,
operation_status,
operation_sequence,
sug_to_sub_code,
sug_to_locator_id,
organization_id
FROM wms_op_operation_instances
WHERE source_task_id = p_source_task_id
AND activity_type_id = p_activity_type_id
AND operation_status = g_op_ins_stat_active;
l_wopi_update_rec wms_op_plan_instances%ROWTYPE:=NULL;
SELECT task_id, status
INTO l_wdt_task_id, l_wdt_status
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_source_task_id
AND task_type IN (g_wms_task_type_putaway, g_wms_task_type_inspect);
print_debug('WDT status is dispatched, delete WDT record : task_id = ' || l_wdt_task_id,l_module_name,4);
DELETE wms_dispatched_tasks
WHERE task_id = l_wdt_task_id;
print_debug('Before calling wms_op_runtime_pvt_apis.update_operation_instance with following parameters: ',l_module_name,4);
wms_op_runtime_pvt_apis.update_operation_instance
(x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_update_rec => l_wooi_rec);
print_debug('After calling wms_op_runtime_pvt_apis.update_operation_instance: ' ,l_module_name,4);
print_debug('wms_op_runtime_pvt_apis.update_operation_instance finished with error. l_return_status = ' || l_return_status,l_module_name,4);
SELECT MIN(wopd1.operation_sequence)
INTO l_first_op_sequence
FROM wms_op_plan_details wopd1
WHERE wopd1.operation_plan_id =
(SELECT wopd2.operation_plan_id
FROM wms_op_plan_details wopd2
WHERE wopd2.operation_plan_detail_id = l_wooi_data_rec.operation_plan_detail_id
);
l_wopi_update_rec.status := g_op_ins_stat_pending;
l_wopi_update_rec.op_plan_instance_id := l_wooi_data_rec.op_plan_instance_id;
print_debug('Calling WMS_OP_RUNTIME_PVT_APIS.Update_plan_instance with the following values to be updated',l_module_name,4);
print_debug('l_wopi_update_rec.status => '||l_wopi_update_rec.status,l_module_name,9);
print_debug('l_wopi_update_rec.op_plan_instance_id => '||l_wopi_update_rec.op_plan_instance_id,l_module_name,9);
WMS_OP_RUNTIME_PVT_APIS.Update_Plan_Instance
(p_update_rec => l_wopi_update_rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
print_debug('After calling WMS_OP_RUNTIME_PVT_APIS.Update_plan_instance.',l_module_name,4);
print_debug('wms_op_runtime_pvt_apis.Update_plan_instance finished with error. l_return_status = ' || l_return_status,l_module_name,4);
SELECT operation_plan_id,
transaction_temp_id,
parent_line_id
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE transaction_temp_id = p_source_task_id;
SELECT operation_instance_id,
operation_status,
op_plan_instance_id,
operation_type_id
FROM WMS_OP_OPERATION_INSTANCES
WHERE source_task_id = p_source_task_id
AND activity_type_id = p_activity_type_id
AND operation_status <> g_op_ins_stat_completed
ORDER BY operation_sequence DESC;
l_wooi_update_rec WMS_OP_OPERATION_INSTANCES%ROWTYPE;
l_wopi_update_rec WMS_OP_PLAN_INSTANCES%ROWTYPE;
call WMS_OP_RUNTIME_PVT_APIS.Update_operation_plan_instance to update
the Operation Plan instance*/
l_wopi_update_rec.status := G_OP_INS_STAT_CANCELLED;
l_wopi_update_rec.op_plan_instance_id := l_operation_instance_rec.op_plan_instance_id;
print_debug('Calling Update_plan_instance with the following values to be updated',l_module_name,9);
print_debug('Plan status => '||l_wopi_update_rec.status,l_module_name,9);
print_debug('Plan Instance id => '||l_wopi_update_rec.op_plan_instance_id,l_module_name,9);
WMS_OP_RUNTIME_PVT_APIS.Update_Plan_Instance
(p_update_rec => l_wopi_update_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
* call WMS_OP_RUNTIME_APIS.Update_operation_instance to update the Operation instance
*/
l_wooi_update_rec.operation_status := G_OP_INS_STAT_CANCELLED;
l_wooi_update_rec.operation_instance_id := l_operation_instance_rec.operation_instance_id;
print_debug('Operation status ==> '||l_wooi_update_rec.operation_status,l_module_name,9);
print_debug('Operation Instance Id ==> '||l_wooi_update_rec.operation_instance_id,l_module_name,9);
WMS_OP_RUNTIME_PVT_APIS.Update_Operation_Instance
(p_update_rec => l_wooi_update_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT operation_plan_id,
move_order_line_id,
parent_line_id,
transfer_to_location,
locator_id,
organization_id,
inventory_item_id,
primary_quantity
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE transaction_temp_id = p_source_task_id;
SELECT operation_instance_id,
operation_status,
op_plan_instance_id
FROM WMS_OP_OPERATION_INSTANCES
WHERE source_task_id = p_source_task_id
AND operation_status <> G_OP_INS_STAT_COMPLETED
AND activity_type_id = p_activity_type_id
ORDER BY operation_sequence DESC;
SELECT status,
orig_dest_sub_code,
orig_dest_loc_id
FROM WMS_OP_PLAN_INSTANCES
WHERE op_plan_instance_id=v_op_plan_instance_id;
l_wooi_update_rec WMS_OP_OPERATION_INSTANCES%ROWTYPE:=NULL;
l_wopi_update_rec WMS_OP_PLAN_INSTANCES%ROWTYPE:=NULL;
call WMS_OP_RUNTIME_PVT_APIS.Update_operation_plan_instance to update
the Operation Plan instance*/
l_wopi_update_rec.status := G_OP_INS_STAT_ABORTED;
l_wopi_update_rec.op_plan_instance_id := l_operation_instance.op_plan_instance_id;
print_debug('Calling Update_plan_instance with the following values to be updated',l_module_name,9);
print_debug('Plan status => '||l_wopi_update_rec.status,l_module_name,9);
print_debug('Plan Instance id => '||l_wopi_update_rec.op_plan_instance_id,l_module_name,9);
WMS_OP_RUNTIME_PVT_APIS.Update_Plan_Instance
(p_update_rec => l_wopi_update_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
* call WMS_OP_RUNTIME_APIS.Update_operation_instance to update the Operation instance
*/
l_wooi_update_rec.operation_status := G_OP_INS_STAT_ABORTED;
l_wooi_update_rec.operation_instance_id := l_operation_instance.operation_instance_id;
print_debug('Operation status ==> '||l_wooi_update_rec.operation_status,l_module_name,9);
print_debug('Operation Instance Id ==> '||l_wooi_update_rec.operation_instance_id,l_module_name,9);
WMS_OP_RUNTIME_PVT_APIS.Update_Operation_Instance
(p_update_rec => l_wooi_update_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
/* null out the Operation Plan Id ,parent Line Id and delete the Parent transaction temp Id*/
/*Call table hanlder to delete MMTT/MSNT/MTLT for the Parent MMTT*/
/* INV_TRX_UTIL_PUB.Delete_transaction
(x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_transaction_temp_id => l_parent_source_task_id,
p_update_parent => FALSE);
print_debug('Return status from Delete Transaction'||x_return_status,l_module_name,9);
print_debug('Also update destination sub/loc Src Document record to that of the original task: l_plan_orig_dest_sub = '||l_plan_orig_dest_sub || ' l_plan_orig_dest_loc_id = ' || l_plan_orig_dest_loc_id,l_module_name,9);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET operation_plan_id = NULL ,
parent_line_id = NULL,
subinventory_code = l_plan_orig_dest_sub,
locator_id = l_plan_orig_dest_loc_id
WHERE transaction_temp_id = p_source_task_id;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET operation_plan_id = NULL ,
parent_line_id = NULL,
transfer_subinventory = l_plan_orig_dest_sub,
transfer_to_location = l_plan_orig_dest_loc_id
WHERE transaction_temp_id = p_source_task_id;
print_debug('Need to update the WDT /WDTH records nulling out operation Plan id',l_module_name,9);
UPDATE wms_dispatched_tasks_history
SET parent_transaction_id = NULL
WHERE transaction_id = p_source_task_id;
/* UPDATE wms_dispatched_tasks
SET operation_plan_id = NULL,
op_plan_instance_id = NULL
WHERE transaction_temp_id = p_source_task_id;
UPDATE wms_dispatched_tasks_history
SET operation_plan_id = NULL,
op_plan_instance_id = NULL,
parent_transaction_id = NULL
WHERE transaction_id = p_source_task_id;
SELECT status,
task_id
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_source_task_id;
SELECT operation_plan_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_source_task_id;
SELECT operation_instance_id,
op_plan_instance_id,
operation_type_id,
operation_sequence,
operation_plan_detail_id,
operation_status
FROM wms_op_operation_instances
WHERE source_task_id = p_source_task_id
AND activity_type_id = p_activity_type_id
ORDER BY 4;
SELECT MIN(operation_sequence)
FROM wms_op_plan_details
WHERE operation_plan_id = v_op_plan_id;
/* Update or remove WDT. Based on the p_source_task_id query WMS_DISPATCHED_TASKS and populate WDT record variable as follows:
* a. If no record is found in WDT for source_task_id then raise unexpected error and return after populating appropriate error code.
* b. If WDT.status = 'Dispatched' or 'Loaded' THEN
* a. Remove WDT
* End IF; (If Dispatched)
-- Moved delete WDT to after WMS_OP_INBOUND_PVT.Cleanup
/* Query the document tables based on p_source_taks_id and p_activity_id and populate the document record.
* If p_activity_id=INBOUND then
* Query MMTT (MTLT) where transaction_temp_id is equal p_source_task_id and populate the record variable.
* End If
*/
OPEN c_inbound_document_details;
-- Before we return, we should delete WDT here
-- since now we delete WDT after calling WMS_OP_INBOUND_PVT.Cleanup at the end.
DELETE FROM WMS_DISPATCHED_TASKS
WHERE transaction_temp_id = p_source_task_id
AND task_id = l_wdt_details.task_id;
/* Call WMS_OP_RUNTIME_APIS_PVT.UPDATE_OPERATION_INSTANCE
* to update the Operation Instance populated in WOOI record
* tp statsu Pending
*/
l_wooi_rec.operation_instance_id := l_wooi_data_rec.operation_instance_id;
print_debug('CAlling Update_Operation_instance with the Operation Instance Id as '||l_wooi_rec.operation_instance_id,l_module_name,9);
WMS_OP_RUNTIME_PVT_APIS.UPDATE_OPERATION_INSTANCE
(p_update_rec => l_wooi_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
print_debug('Return Status from Update Operation Instance is '||x_return_status,l_module_name,9);
WMS_OP_RUNTIME_PVT_APIS.DELETE_OPERATION_INSTANCE
( p_operation_instance_id => l_wooi_data_rec.operation_instance_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
print_debug('Return Status from Delete Operation Instance '||x_return_status,l_module_name,9);
* Call WMS_OP_RUNTIME_APIS_PVT.UPDATE_PLAN_INSTANCE to set the plan status as Pending.
*/
OPEN c_min_operation_seq(l_op_plan_id);
print_debug('It is the first Operation in the Plan,hence update status to pending',l_module_name,9);
print_debug('Calling WMS_OP_RUNTIME_APIS_PVT.UPDATE_PLAN_INSTANCE with the following parameters',l_module_name,9);
WMS_OP_RUNTIME_PVT_APIS.UPDATE_PLAN_INSTANCE
(p_update_rec => l_wopi_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
print_debug('Return Status from Update Plan Instance'||x_return_status,l_module_name,9);
-- Moved delete WDT to after calling WMS_OP_INBOUND_PVT.Cleanup
-- because WMS_OP_INBOUND_PVT.Cleanup now calls putaway_cleanup, which assumes WDT still exists.
DELETE FROM WMS_DISPATCHED_TASKS
WHERE transaction_temp_id = p_source_task_id
AND task_id = l_wdt_details.task_id;
SELECT mmtt.operation_plan_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_source_task_id;
SELECT wopi.status
FROM wms_op_plan_instances wopi
WHERE wopi.activity_type_id = p_activity_type_id
AND wopi.source_task_id = p_source_task_id;
SELECT wopi.status
FROM wms_op_plan_instances wopi
WHERE wopi.op_plan_instance_id = (SELECT op_plan_instance_id
FROM wms_op_operation_instances wooi
WHERE wooi.source_task_id = p_source_task_id
AND wooi.activity_type_id = p_activity_type_id
AND ROWNUM =1);