Thursday, October 8, 2015

How to delete records from wf_notifications table

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;   
/

No comments:

Post a Comment