The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT meaning
INTO l_temp_status_code
FROM mfg_lookups
WHERE lookup_type = 'WMS_TASK_STATUS'
AND lookup_code = i;
l_CursorStmt := ' SELECT wdt.status, count(*) task_count FROM ' || p_at_from || ' WHERE ' || p_at_where ||
' AND wdt.status IN (2,3,4,5) GROUP BY wdt.status UNION ALL ' ||
' SELECT 1, count(*) task_count FROM ' || p_pt_from || ' WHERE ' || p_pt_where || ' HAVING count(rownum) > 0 ' ||
' UNION ALL SELECT 6, count(*) task_count FROM ' || p_ct_from || ' WHERE ' || p_ct_where || ' HAVING count(rownum) > 0 ';
' SELECT wdt.status, count(*) task_count FROM ' || p_acy_from ||
' WHERE ' || p_acy_where || ' AND wdt.status IN (2,3,4,5) GROUP BY wdt.status UNION ALL ' ||
' SELECT 1, count(min(mcce.cycle_count_entry_id)) task_count FROM ' || p_pcy_from ||
' WHERE ' || p_pcy_where || --' and count(rownum) > 0 ' ||
' UNION ALL SELECT 6, count(*) task_count FROM ' || p_ccy_from ||
' WHERE ' || p_ccy_where ||
' HAVING count(rownum) > 0 ';
l_CursorStmt := ' SELECT wdt.status, count(*) task_count FROM ' || p_acy_from ||
' WHERE ' || p_acy_where || ' AND wdt.status IN (2,3,4,5) GROUP BY wdt.status UNION ALL ' ||
' SELECT 1, count(min(mcce.cycle_count_entry_id)) task_count FROM ' || p_pcy_from ||
' WHERE ' || p_pcy_where || --' and count(rownum) > 0 ' ||
' UNION ALL SELECT 6, count(*) task_count FROM ' || p_ccy_from ||
' WHERE ' || p_ccy_where ||
' HAVING count(rownum) > 0 ';
SELECT meaning
INTO l_temp_task_type
FROM mfg_lookups
WHERE lookup_type = 'WMS_TASK_TYPES'
AND lookup_code = l_loop_index;
l_CursorStmt := 'SELECT NVL(wdt.task_type,mmtt.wms_task_type) task_type, count(*) task_count FROM ' ||
p_at_from || ' WHERE ' || p_at_where ||
' AND status IN (2,3,4,5) GROUP BY NVL(wdt.task_type,mmtt.wms_task_type) UNION ALL ' ||
'SELECT mmtt.wms_task_type task_type, count(*) task_count FROM ' ||
p_pt_from || ' WHERE ' || p_pt_where || ' GROUP BY mmtt.wms_task_type UNION ALL ' ||
'SELECT wdth.task_type task_type, count(*) task_count FROM ' ||
p_ct_From || ' WHERE ' || p_ct_where || ' GROUP BY wdth.task_type ';
' SELECT 3 task_type, count(*) task_count FROM ' || p_acy_from ||
' WHERE ' || p_acy_where || ' AND wdt.status IN (2,3,4,5) HAVING count(rownum) > 0 UNION ALL ' ||
' SELECT 3 task_type, count(min(mcce.cycle_count_entry_id)) task_count FROM ' || p_pcy_from ||
' WHERE ' || p_pcy_where || --' and count(rownum) > 0 UNION ALL ' ||
' UNION ALL SELECT 3 task_type, count(*) task_count FROM ' || p_ccy_From ||
' WHERE ' || p_ccy_where || ' HAVING count(rownum) > 0 ';
l_CursorStmt := ' SELECT 3 task_type, count(*) task_count FROM ' || p_acy_from ||
' WHERE ' || p_acy_where || ' AND wdt.status IN (2,3,4,5) HAVING count(rownum) > 0 UNION ALL ' ||
' SELECT 3 task_type, count(min(mcce.cycle_count_entry_id)) task_count FROM ' || p_pcy_from ||
' WHERE ' || p_pcy_where || --' and count(rownum) > 0 UNION ALL ' ||
' UNION ALL SELECT 3 task_type, count(*) task_count FROM ' || p_ccy_From ||
' WHERE ' || p_ccy_where || ' HAVING count(rownum) > 0 ';
CURSOR C_mcce IS SELECT --kkoothan
cycle_count_entry_id
, 1 -- status
, task_priority
FROM MTL_CYCLE_COUNT_ENTRIES
WHERE cycle_count_entry_id = p_transaction_temp_id
FOR UPDATE OF entry_status_code, task_priority NOWAIT;
CURSOR C_mmtt IS SELECT
transaction_temp_id
, wms_task_status
, task_priority
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE transaction_temp_id = p_transaction_temp_id
FOR UPDATE OF wms_task_status, task_priority NOWAIT;
CURSOR C_wdt IS SELECT
transaction_temp_id
, task_id
, status
, priority
, person_id
, person_resource_id
FROM WMS_DISPATCHED_TASKS
WHERE transaction_temp_id = p_transaction_temp_id
AND task_id = p_task_id
FOR UPDATE OF status, priority, person_id, person_resource_id NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
CURSOR C_mcce IS SELECT --kkoothan
cycle_count_entry_id
, 1 -- status
, task_priority
FROM MTL_CYCLE_COUNT_ENTRIES
WHERE cycle_count_entry_id = p_transaction_temp_id
FOR UPDATE OF entry_status_code, task_priority NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
PROCEDURE update_mcce(
p_cycle_count_entry_id IN NUMBER
,p_priority IN NUMBER
,p_updated_by IN NUMBER
,p_user_task_type IN NUMBER
,p_last_update_date IN DATE /* Bug 2372652 */
) IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
UPDATE MTL_CYCLE_COUNT_ENTRIES
SET
task_priority = p_priority
, last_updated_by = p_updated_by
, last_update_date= p_last_update_date /* Bug 2372652 */
, standard_operation_id = p_user_task_type
WHERE cycle_count_entry_id = p_cycle_count_entry_id;
END update_mcce;
PROCEDURE update_mmtt(
p_transaction_temp_id IN NUMBER
,p_priority IN NUMBER
,p_from_status IN NUMBER
,p_to_status IN NUMBER
,p_updated_by IN NUMBER
,p_user_task_type IN NUMBER
,p_task_type IN NUMBER
) IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
--dbms_output.put_line('in update_mmtt '|| p_transaction_temp_id || ':' || p_priority || ':' ||p_from_status || ':' ||p_to_status);
UPDATE mtl_material_transactions_temp
SET
task_priority = p_priority
, wms_task_status = decode(p_to_status, NULL, p_from_status, p_to_status)
, last_updated_by = p_updated_by
, last_update_date= SYSDATE
, standard_operation_id = p_user_task_type
, wms_task_type = p_task_type
WHERE transaction_temp_id = p_transaction_temp_id;
--dbms_output.put_line('did update_mmtt ');
END update_mmtt;
PROCEDURE update_wdt(
p_transaction_temp_id IN NUMBER
,p_task_id IN NUMBER
,p_priority IN NUMBER
,p_from_status IN NUMBER
,p_to_status IN NUMBER
,p_person_id IN NUMBER
,p_person_resource_id IN NUMBER
,p_updated_by IN NUMBER
,p_user_task_type IN NUMBER
,p_task_type IN NUMBER
) IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
--dbms_output.put_line('in update wdt');
UPDATE wms_dispatched_tasks
SET
person_resource_id = p_person_resource_id
, person_id = p_person_id
, priority = p_priority
, status = p_to_status
, last_updated_by = p_updated_by
, last_update_date = SYSDATE
, user_task_type = p_user_task_type
, task_type = p_task_type
WHERE transaction_temp_id = p_transaction_temp_id
AND task_id = nvl(p_task_id, task_id);
END update_wdt;
PROCEDURE insert_to_wdt(
p_transaction_temp_id IN NUMBER
,p_status IN NUMBER
,p_person_id IN NUMBER
,p_person_resource_id IN NUMBER
,p_updated_by IN NUMBER
,p_transaction_source_type_id IN NUMBER --kkoothan
,x_task_id OUT NOCOPY /* file.sql.39 change */ NUMBER
,x_priority IN NUMBER
) IS
l_org_id NUMBER;
SELECT wms_dispatched_tasks_s.NEXTVAL
INTO l_next_task_id
FROM dual ;
SELECT organization_id, standard_operation_id, 3 --task type
INTO l_org_id, l_user_task_type, l_wms_task_type
FROM mtl_cycle_count_entries
WHERE cycle_count_entry_id = p_transaction_temp_id;
SELECT organization_id, standard_operation_id, wms_task_type, operation_plan_id, move_order_line_id
INTO l_org_id, l_user_task_type, l_wms_task_type, l_operation_plan_id, l_move_order_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
--dbms_output.put_line('Before Insert into WMSDT');
INSERT INTO WMS_DISPATCHED_TASKS
( TASK_ID
, TRANSACTION_TEMP_ID
, ORGANIZATION_ID
, USER_TASK_TYPE
, PERSON_ID
, EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE
, PERSON_RESOURCE_ID
, STATUS
, DISPATCHED_TIME
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, task_type
, priority
, operation_plan_id
, move_order_line_id )
VALUES( l_next_task_id
, p_transaction_temp_id
, l_org_id
, Nvl(l_user_task_type,2)
, p_person_id
, sysdate
, sysdate
, p_person_resource_id
, p_status
, sysdate
, sysdate
, p_updated_by
, sysdate
, p_updated_by
, l_wms_task_type
, x_priority
, l_operation_plan_id
, l_move_order_line_id);
END insert_to_wdt;
PROCEDURE delete_from_wdt(
p_transaction_temp_id IN NUMBER , p_task_id IN NUMBER) IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_transaction_temp_id
AND task_id = nvl(p_task_id, task_id);
END delete_from_wdt;
,p_updated_by IN NUMBER
,p_task_id IN NUMBER
,p_transaction_temp_id IN NUMBER
,p_organization_id IN NUMBER
,p_person_resource_id IN NUMBER
,p_person_id IN NUMBER
,p_priority IN NUMBER
,p_from_status IN NUMBER
,p_to_status IN NUMBER
,p_user_task_type IN NUMBER
,p_task_type IN NUMBER
,p_transaction_source_type_id IN NUMBER -- kkoothan
,p_last_update_date IN DATE /* Bug 2372652 */
) IS
-- Bug# 1728558, added p_user_task_type parameter in task_manipulator,
-- update_mmtt, update_wdt
c_api_name CONSTANT VARCHAR2(20) := 'task_manipulator';
/*select mlk1.meaning, mlk2.meaning
into from_status_code, to_status_code
from mfg_lookups mlk1, mfg_lookups mlk2
where mlk1.lookup_type = 'WMS_TASK_TYPES'
and mlk2.lookup_type = 'WMS_TASK_TYPES'
and mlk1.lookup_code = from_status
and mlk2.lookup_code = to_status;*/
-- Update mmtt/mcce
IF p_transaction_source_type_id = 9 THEN -- kkoothan
-- cycle count
update_mcce(p_transaction_temp_id
,p_priority
,p_updated_by
,p_user_task_type
,p_last_update_date /* Bug 2372652 */
);
update_mmtt(p_transaction_temp_id
,p_priority
,from_status
,from_status
,p_updated_by
,p_user_task_type
,p_task_type);
-- update mmtt/mcce and wdt
--dbms_output.put_line('calling update_mmtt ');
update_mcce(p_transaction_temp_id
,p_priority
,p_updated_by
,p_user_task_type
,p_last_update_date /* Bug 2372652 */
);
update_mmtt(p_transaction_temp_id
,p_priority
,from_status
,from_status
,p_updated_by
,p_user_task_type
,p_task_type);
update_wdt(p_transaction_temp_id
, p_task_id
, p_priority
, from_status
, to_status
, p_person_id
, p_person_resource_id
, p_updated_by
, p_user_task_type
, p_task_type);
update_mcce(p_transaction_temp_id
,p_priority
,p_updated_by
,p_user_task_type
,p_last_update_date /* Bug 2372652 */
);
update_mmtt(p_transaction_temp_id
,p_priority
,from_status
,from_status
,p_updated_by
,p_user_task_type
,p_task_type);
insert_to_wdt(p_transaction_temp_id
, to_status
, p_person_id
, p_person_resource_id
, p_updated_by
, p_transaction_source_type_id -- kkoothan
, x_task_id
, p_priority);
update_mcce(p_transaction_temp_id
,p_priority
,p_updated_by
,p_user_task_type
,p_last_update_date /* Bug 2372652 */
);
update_mmtt(p_transaction_temp_id
,p_priority
,from_status
,from_status
,p_updated_by
,p_user_task_type
,p_task_type);
delete_from_wdt(p_transaction_temp_id
, p_task_id);
update_wdt(p_transaction_temp_id
, p_task_id
, p_priority
, from_status
, to_status
, p_person_id
, p_person_resource_id
, p_updated_by
, p_user_task_type
, p_task_type);
update_mcce(p_transaction_temp_id
,p_priority
,p_updated_by
,p_user_task_type
,p_last_update_date /* Bug 2372652 */
);
update_mmtt(p_transaction_temp_id
,p_priority
,from_status
,from_status
,p_updated_by
,p_user_task_type
,p_task_type);
delete_from_wdt(p_transaction_temp_id
, p_task_id);
update_mcce(p_transaction_temp_id
,p_priority
,p_updated_by
,p_user_task_type
,p_last_update_date /* Bug 2372652 */
);
update_mmtt(p_transaction_temp_id
,p_priority
,from_status
,from_status
,p_updated_by
,p_user_task_type
,p_task_type);
update_wdt(p_transaction_temp_id
, p_task_id
, p_priority
, from_status
, to_status
, p_person_id
, p_person_resource_id
, p_updated_by
, p_user_task_type
, p_task_type);
update_mcce(p_transaction_temp_id
,p_priority
,p_updated_by
,p_user_task_type
,p_last_update_date /* Bug 2372652 */
);
update_mmtt(p_transaction_temp_id
,p_priority
,from_status
,from_status
,p_updated_by
,p_user_task_type
,p_task_type);
delete_from_wdt(p_transaction_temp_id, p_task_id);
update_mcce(p_transaction_temp_id
,p_priority
,p_updated_by
,p_user_task_type
,p_last_update_date /* Bug 2372652 */
);
update_mmtt(p_transaction_temp_id
,p_priority
,from_status
,from_status
,p_updated_by
,p_user_task_type
,p_task_type);
update_wdt(p_transaction_temp_id
, p_task_id
, p_priority
, from_status
, to_status
, p_person_id
, p_person_resource_id
, p_updated_by
, p_user_task_type
, p_task_type);