[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_drop_locator_for_task
(x_return_status OUT nocopy VARCHAR2,
x_message OUT nocopy VARCHAR2,
x_drop_lpn_option OUT nocopy NUMBER,
p_transfer_lpn_id IN NUMBER
)
IS
TYPE TransactionTempIDTable IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
SELECT mmtt.transaction_temp_id transaction_temp_id,
mmtt.transfer_subinventory pick_release_subinventory,
mmtt.transfer_to_location pick_release_locator_id,
mmtt.wms_task_type,
mmtt.parent_line_id parent_line_id,
mmtt.move_order_line_id move_order_line_id,
op.operation_plan_id operation_plan_id,
Nvl(op.loc_selection_criteria, g_op_dest_sys_suggested) dest_loc_sel_criteria,
op.loc_selection_api_id dest_loc_sel_api_id,
Nvl(op.drop_lpn_option, g_op_drop_lpn_optional) drop_lpn_option,
Nvl(op.consolidation_method_id, 2) consolidation_method_id -- xdock MDC default to within delivery
FROM mtl_material_transactions_temp mmtt,
(SELECT plan.operation_plan_id,
detail.loc_selection_criteria,
detail.loc_selection_api_id,
detail.drop_lpn_option,
detail.consolidation_method_id
FROM
wms_op_plans_b plan,
wms_op_plan_details detail
WHERE
plan.operation_plan_id = detail.operation_plan_id AND
detail.operation_sequence = v_operation_sequence_id
) op
WHERE mmtt.transfer_lpn_id = p_transfer_lpn_id AND
(mmtt.parent_line_id IS NULL -- xdock MDC non bulk line or child line for bulk task
OR mmtt.parent_line_id <> mmtt.transaction_temp_id) AND
((mmtt.transaction_source_type_id = g_sourcetype_salesorder
AND mmtt.transaction_action_id = g_action_stgxfr)
OR
(mmtt.transaction_source_type_id = g_sourcetype_intorder
AND mmtt.transaction_action_id = g_action_stgxfr)
OR
mmtt.wms_task_type = g_wms_task_type_stg_move
)AND
mmtt.operation_plan_id = op.operation_plan_id (+)
;
SAVEPOINT update_drop_loc_sp;
print_debug('Enter update_drop_locator_for_task', 1);
wms_op_dest_sys_apis.g_sug_dest_sub_loc_table.DELETE; -- 16371756 FP of Bug 16344535
SELECT wda.delivery_id
INTO l_delivery_id
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.move_order_line_id = l_task_drop_loc_rec.move_order_line_id;
p_call_mode => 1, -- locator selection
p_task_type => g_wms_task_type_pick, -- picking
p_task_id => l_task_drop_loc_rec.transaction_temp_id,
p_locator_id => NULL);
print_debug('Calling WMS_OP_DEST_SYS_APIS.Get_CONS_Loc_For_Delivery. Leaving update_drop_locator_for_task ... ', 4);
p_call_mode => 1, -- locator selection
p_task_type => g_wms_task_type_pick, -- picking
p_task_id => l_task_drop_loc_rec.transaction_temp_id,
p_locator_id => NULL);
print_debug('Back to update_drop_locator_for_task.', 4);
-- so update dest sub/loc immediately
--
UPDATE mtl_material_transactions_temp
SET transfer_subinventory
= l_sug_dest_sub_table(l_task_count)
, transfer_to_location
= l_sug_dest_loc_id_table(l_task_count)
WHERE transaction_temp_id
= l_task_drop_loc_rec.transaction_temp_id;*/ -- 12800191
SELECT wda.delivery_id
INTO l_delivery_id
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.move_order_line_id = l_task_drop_loc_rec.move_order_line_id;
p_call_mode => 1, -- locator selection
p_task_type => g_wms_task_type_pick, -- picking
p_task_id => l_task_drop_loc_rec.transaction_temp_id,
p_locator_id => NULL);
print_debug('Invalid loc_selection_api_id : '|| l_task_drop_loc_rec.dest_loc_sel_api_id ||'for operation_plan_id : '||l_operation_sequence_id||' and operation_sequence :'||l_operation_sequence_id, 4);
print_debug('Invalid loc_selection_criteria : '|| l_task_drop_loc_rec.dest_loc_sel_criteria ||'for operation_plan_id : '||l_operation_sequence_id||' and operation_sequence :'||l_operation_sequence_id, 4);
UPDATE mtl_material_transactions_temp
SET transfer_subinventory
= l_sug_dest_sub_table(l_task_count)
, transfer_to_location
= l_sug_dest_loc_id_table(l_task_count)
WHERE transaction_temp_id
= l_task_drop_loc_rec.transaction_temp_id; -- 12800191
wms_op_dest_sys_apis.g_sug_dest_sub_loc_table.DELETE; -- 16371756 FP of Bug 16344535
UPDATE mtl_material_transactions_temp
SET transfer_subinventory = l_sug_dest_sub_table(i),
transfer_to_location = l_sug_dest_loc_id_table(i)
--cartonization_id = l_sug_lpn_id_table(i) -- xdock MDC
-- mrana:bug5257431: the above update should not be committed
-- before the task is confirmed. If the drop is cancelled, we would
-- like to retain the original cartonization id
WHERE transaction_temp_id = l_temp_id_table(i);*/ -- 12800191
UPDATE wms_dispatched_tasks
SET suggested_dest_subinventory = l_pick_rel_sub_table(i),
suggested_dest_locator_id = l_pick_rel_loc_id_table(i)
WHERE transaction_temp_id = l_temp_id_table(i)
AND task_type IN (g_wms_task_type_pick, g_wms_task_type_stg_move);
UPDATE mtl_material_transactions_temp
SET cartonization_id = l_sug_lpn_id_table(i) -- MDC
WHERE transaction_temp_id = l_temp_id_table(i);
ROLLBACK TO update_drop_loc_sp;
ROLLBACK TO update_drop_loc_sp;
print_debug('Other exception in update_drop_locator_for_task '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')|| ' after where l_progress = ' || l_progress, 1);
END update_drop_locator_for_task;
SELECT mmtt.transaction_temp_id transaction_temp_id,
mmtt.transfer_subinventory pick_release_subinventory,
mmtt.transfer_to_location pick_release_locator_id,
op.operation_plan_id operation_plan_id,
Nvl(op.loc_selection_criteria, g_op_dest_sys_suggested) dest_loc_sel_criteria,
op.loc_selection_api_id dest_loc_sel_api_id,
Nvl(op.drop_lpn_option, g_op_drop_lpn_optional) drop_lpn_option,
Nvl(consolidation_method_id, 2) consolidation_method_id,
mmtt.organization_id organization_id --BUG13839132
FROM mtl_material_transactions_temp mmtt,
(SELECT plan.operation_plan_id,
detail.loc_selection_criteria,
detail.loc_selection_api_id,
detail.drop_lpn_option,
detail.consolidation_method_id
FROM
wms_op_plans_b plan,
wms_op_plan_details detail
WHERE
plan.operation_plan_id = detail.operation_plan_id AND
detail.operation_sequence = v_operation_sequence_id
) op
WHERE mmtt.transaction_temp_id = p_task_id AND
((mmtt.transaction_source_type_id = g_sourcetype_salesorder
AND mmtt.transaction_action_id = g_action_stgxfr)
OR
(mmtt.transaction_source_type_id = g_sourcetype_intorder
AND mmtt.transaction_action_id = g_action_stgxfr)
OR
mmtt.wms_task_type = g_wms_task_type_stg_move
)AND
mmtt.operation_plan_id = op.operation_plan_id (+)
;
print_debug('Back to update_drop_locator_for_task.', 4);
print_debug('Back to update_drop_locator_for_task.', 4);
print_debug('Invalid loc_selection_api_id : '|| l_task_drop_loc_rec.dest_loc_sel_api_id ||'for operation_plan_id : '||l_operation_sequence_id||' and operation_sequence :'||l_operation_sequence_id, 4);
print_debug('Invalid loc_selection_criteria : '|| l_task_drop_loc_rec.dest_loc_sel_criteria ||'for operation_plan_id : '||l_operation_sequence_id||' and operation_sequence :'||l_operation_sequence_id, 4);