[Home] [Help]
836: --
837: -- PROCEDURE
838: -- PURGE_PROJ_WORKFLOW
839: -- PURPOSE
840: -- This API purges unused denormalized workflow data from 3 tables pa_wf_processes , pa_wf_process_details
841: -- and pa_wf_ntf_performers
842: --
843: --
844: -- Parameter Name In/Out Data Type Null? Default Value Description
883: l_local_error_flag VARCHAR2(1) :='N'; -- 5201806
884:
885: CURSOR c_purge_wf_details IS
886: SELECT a.item_type, a.item_key, a.wf_type_code
887: FROM pa_wf_processes a
888: WHERE
889: a.item_type in ('PACANDID','PACOPR','PARADVWF','PARAPTEM','PARFIGEN','PAROVCNT','PAWFGPF','PAYPRJNT','PARMATRX')
890: AND a.item_key NOT IN
891: (SELECT wi.item_key
966: , p_error_text => SUBSTRB(SQLERRM,1,240));
967: EXIT; -- 5201806 : exit the loop after discussion with Anders.
968: END;
969:
970: -- Delete pa_wf_processes
971: BEGIN
972: FORALL i IN l_wf_item_type_tbl.FIRST..l_wf_item_type_tbl.LAST
973: DELETE FROM pa_wf_processes
974: WHERE item_key=l_wf_item_key_tbl(i)
969:
970: -- Delete pa_wf_processes
971: BEGIN
972: FORALL i IN l_wf_item_type_tbl.FIRST..l_wf_item_type_tbl.LAST
973: DELETE FROM pa_wf_processes
974: WHERE item_key=l_wf_item_key_tbl(i)
975: AND item_type = l_wf_item_type_tbl(i)
976: AND wf_type_code = l_wf_type_code_tbl(i);
977:
995: END LOOP;
996: CLOSE c_purge_wf_details;
997:
998: IF p_debug_mode = 'Y' THEN
999: pa_debug.g_err_stage:= 'No. of rows deleted from pa_wf_processes ' || l_rows1 ;
1000: pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1001: pa_debug.g_err_stage:= 'No. of rows deleted from pa_wf_process_details ' || l_rows2 ;
1002: pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
1003: pa_debug.g_err_stage:= 'No. of rows deleted from pa_wf_ntf_performers ' || l_rows3 ;
1007:
1008: PA_PURGE_PUB.INSERT_PURGE_LOG
1009: (
1010: p_request_id => p_request_id ,
1011: p_table_name => 'PA_WF_PROCESSES' ,
1012: p_rows_deleted => l_rows1 ,
1013: x_return_status => x_return_status ,
1014: x_msg_count => x_msg_count,
1015: x_msg_data => x_msg_data