The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure update_mmtt(
p_task_id IN NUMBER
, p_operation_plan_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) is
l_debug NUMBER;
trace(' Entering procedure update_mmtt '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
update mtl_material_transactions_temp mmtt
set mmtt.operation_plan_id = p_operation_plan_id
where mmtt.transaction_temp_id = p_task_id;
trace(' Successfully updated MMTT record with operation_plan_id = ' || p_operation_plan_id, 1);
trace(' Exiting procedure update_mmtt '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
trace(' Unable to update MMTT due to Error : ' || sqlerrm(sqlcode), 1);
trace(' Exiting procedure update_mmtt '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
end update_mmtt;
select rules.rule_id, mmtt.organization_id, mmtt.wms_task_type, rules.type_hdr_id
from wms_rules_b rules, wms_op_plans_b wop, mtl_material_transactions_temp mmtt
where rules.type_code = 7
and rules.enabled_flag = 'Y'
and rules.type_hdr_id = wop.operation_plan_id
and wop.system_task_type = NVL(mmtt.wms_task_type, wop.system_task_type)
and mmtt.transaction_source_type_id IN (2, 8) --restrict to sales order and internal order mmtts only
and mmtt.transaction_temp_id = p_task_id
and rules.organization_id IN (mmtt.organization_id, -1)
and wop.enabled_flag = 'Y'
and wop.activity_type_id = 2 -- (Outbound)
order by rules.rule_weight desc, rules.creation_date;
select rules.rule_id, mmtt.organization_id, mmtt.wms_task_type, rules.type_hdr_id,
wop.plan_type_id -- new column added
from wms_rules_b rules, wms_op_plans_b wop, mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl -- new tables added
where rules.type_code = 7
and rules.enabled_flag = 'Y'
and rules.type_hdr_id = wop.operation_plan_id
and rules.organization_id in (mmtt.organization_id, -1)
and (wop.organization_id = mmtt.organization_id or wop.common_to_all_org = 'Y') -- changed after review
and wop.enabled_flag = 'Y'
and mmtt.transaction_temp_id = p_task_id
and mtrl.line_id = mmtt.move_order_line_id
and mtrl.organization_id = mmtt.organization_id
and wop.activity_type_id = 1
--
-- @@@ if (mtrl.backorder_delivery_detail_id is not null)
-- @@@ then
-- @@@ (backordered line)
-- @@@ else
-- @@@
-- @@@ if (mtrl.inspection_flag is 1)
-- @@@ then
-- @@@ (Inspection routing)
-- @@@ elseif (mtrl.inspection_flag in (2, 3, null) or any other values)
-- @@@ then
-- @@@ (Standard Routing assumed)
-- @@@ end if;
select wop.plan_type_id, wop.operation_plan_id
from mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl,
wms_op_plans_b wop
where mmtt.transaction_temp_id = p_task_id
and mtrl.line_id = mmtt.move_order_line_id
and wop.activity_type_id= 1
--
-- @@@ Changed on October 7th 2003. This decode is supposed to be read as follows:
-- @@@ if (mtrl.backorder_delivery_detail_id is not null)
-- @@@ then
-- @@@ (backordered line)
-- @@@ else
-- @@@
-- @@@ if (mtrl.inspection_flag is 1)
-- @@@ then
-- @@@ (Inspection routing)
-- @@@ elseif (mtrl.inspection_flag in (2, 3, null) or any other values)
-- @@@ then
-- @@@ (Standard Routing assumed)
-- @@@ end if;
select Nvl(default_pick_op_plan_id,1)
from mtl_parameters mp, mtl_material_transactions_temp mmtt
where mp.organization_id = mmtt.organization_id
and mmtt.transaction_temp_id = p_task_id
AND mmtt.transaction_source_type_id IN (2, 8); ----restrict to sales order and internal order mmtts only
SELECT COUNT(object_name)
FROM user_objects
WHERE object_name = l_package_name;
-- ### Update MMTT.
if (l_debug = 1) then
trace('Before calling procedure update_mmtt'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')
|| 'Progress Indicator ' || l_progress, 1);
-- ### Update MMTT.
update_mmtt(
p_task_id => p_task_id
, p_operation_plan_id => l_operation_plan_id
, x_return_status => l_ret_status);
trace(' After calling procedure update_mmtt'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')
|| ' Progress Indicator ' || l_progress, 1);
update_mmtt(
p_task_id => p_task_id
, p_operation_plan_id => l_operation_plan_id
, x_return_status => l_ret_status
);
trace('*** Default Operation Plan ID derived is null and hence cannot update MMTT....***');