DECLARE
CURSOR csr_transaction_id IS
SELECT hat.transaction_id,
hat.item_type,
hat.item_key,
ppx.employee_number,
hat.section_display_name
FROM hr_api_transactions hat, per_people_x ppx
WHERE hat.process_name = 'HR_PERSONAL_INFO_JSP_PRC'
AND hat.selected_person_id = ppx.person_id
AND ppx.employee_number IN
('100024773',
'100024820',
'100024859',
'100024879',
'100024902',
'100024937',
'100025137',
'100026470',
'610014755',
'610017039')
order by ppx.employee_number;
BEGIN
dbms_output.put_line('***Deleted all Transactions and Notifications of below Employee Personals Tranactions ***');
FOR my_cur_v IN csr_transaction_id
LOOP
/*Delete all Transaction_id's in hr_api_transactions,hr_api_transaction_steps,hr_api_transaction_values and hr_api_transaction_steps_bk tables */
hr_transaction_swi.delete_transaction
(
p_transaction_id =>my_cur_v.transaction_id,
p_validate => hr_api.g_false_num
);
wf_engine.abortprocess (
itemtype => my_cur_v.item_type,
itemkey => my_cur_v.item_key
);
/* Deleted all Notification_id's and item_key's in wf_item_activity_statuses,wf_items,wf_item_attribute_values,wf_notifications Table */
wf_purge.items (
itemtype => my_cur_v.item_type,
itemkey => my_cur_v.item_key
);
dbms_output.put_line('Emp No --'||my_cur_v.employee_number||'Transaction_id :'||my_cur_v.transaction_id||'Emp Personal Info :'||my_cur_v.section_display_name||
'Item Type :'||my_cur_v.item_type|| 'Item Key :'||my_cur_v.item_key);
END LOOP;
commit;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('hr_transaction_swi.delete_transaction api goest to exception block' ||sqlcode|| ' '||sqlerrm);
END;
/
Thursday, October 8, 2015
How to delete records from wf_notifications table
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment