[Home] [Help]
122: l_no_records NUMBER := 0;
123: l_counter NUMBER := 0;
124: l_temp NUMBER := 0;
125: l_order_exist BOOLEAN := FALSE;
126: l_wi_type WF_ITEMS.ITEM_TYPE%TYPE;
127: l_wi_key WF_ITEMS.ITEM_KEY%TYPE;
128:
129: BEGIN
130: IF p_order_id IS NULL THEN
123: l_counter NUMBER := 0;
124: l_temp NUMBER := 0;
125: l_order_exist BOOLEAN := FALSE;
126: l_wi_type WF_ITEMS.ITEM_TYPE%TYPE;
127: l_wi_key WF_ITEMS.ITEM_KEY%TYPE;
128:
129: BEGIN
130: IF p_order_id IS NULL THEN
131: -- CHECK IF THERE ARE ANY RECORDS IN ANY TABLE THAT HAVE
188: FROM XDP_ORDER_HEADERS
189: WHERE ORDER_ID = p_order_id;
190:
191: SELECT count(*) INTO l_no_records
192: FROM WF_ITEMS
193: START WITH item_type = l_wi_type AND item_key = l_wi_key
194: CONNECT BY PRIOR item_key = parent_item_key
195: AND PRIOR item_type = parent_item_type;
196: RECORDS_PURGED_MSGS(l_no_records,'WF_ITEMS WHERE ORDER_ID = '||p_order_id,
192: FROM WF_ITEMS
193: START WITH item_type = l_wi_type AND item_key = l_wi_key
194: CONNECT BY PRIOR item_key = parent_item_key
195: AND PRIOR item_type = parent_item_type;
196: RECORDS_PURGED_MSGS(l_no_records,'WF_ITEMS WHERE ORDER_ID = '||p_order_id,
197: 'XDP_FOUND_REC',0);
198: END IF;
199:
200: SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_LINE_ITEMS
599: END DELETE_LINE_ITEMS;
600:
601: --
602: -- Internal procedure for deleting workflow items for an order
603: -- Name DELETE_WF_ITEMS
604: --
605: -- This procedure will purge all workflow items that are connected to
606: -- the work item defined by the
607: -- WF_ITEM_TYPE AND WF_ITEM_KEY
606: -- the work item defined by the
607: -- WF_ITEM_TYPE AND WF_ITEM_KEY
608: -- in XDP_ORDER_HEADER table where order_id = p_order_id
609: --
610: -- A cursor is defined against WF_ITEMS table using connect by statement
611: --
612: -- Code reviewers:
613: -- would this risk of purging work items that are not belong to XDP????
614: --
611: --
612: -- Code reviewers:
613: -- would this risk of purging work items that are not belong to XDP????
614: --
615: PROCEDURE DELETE_WF_ITEMS
616: (
617: p_order_id IN VARCHAR2,
618: p_run_mode IN VARCHAR2
619: ) IS
618: p_run_mode IN VARCHAR2
619: ) IS
620: CURSOR c_wi(p_wi_type IN VARCHAR2,p_wi_key IN VARCHAR2) IS
621: SELECT level,item_type,item_key
622: FROM WF_ITEMS
623: START WITH item_type = p_wi_type
624: AND item_key = p_wi_key
625: CONNECT BY PRIOR item_key = parent_item_key
626: AND PRIOR item_type = parent_item_type;
684: NULL;
685: END IF;
686: l_counter := l_counter + 1;
687: END LOOP;
688: RECORDS_PURGED_MSGS(l_counter, 'WF_ITEMS WHERE ORDER_ID = '
689: ||p_order_id,l_rec_name,0);
690: G_Total_WK_TIME := G_Total_WK_TIME + DBMS_UTILITY.get_time - l_mini_secs;
691: EXCEPTION
692: WHEN OTHERS THEN
690: G_Total_WK_TIME := G_Total_WK_TIME + DBMS_UTILITY.get_time - l_mini_secs;
691: EXCEPTION
692: WHEN OTHERS THEN
693: PURGE_ERRORS(P_MSG=>SUBSTR(SQLERRM, 1, 512),P_ABORT => TRUE);
694: END DELETE_WF_ITEMS;
695:
696: -- Procedure XDP_PURGE_ORDER
697: -- Purge order for a given id
698: --
764: SELECT 1 INTO l_dummy FROM XDP_ORDER_HEADERS
765: WHERE order_id = p_order_id for update nowait;
766:
767: DELETE_LINE_ITEMS(p_order_id,p_run_mode);
768: DELETE_WF_ITEMS(p_order_id,p_run_mode);
769:
770: IF ((bAudit = 'TRUE') AND (p_run_mode = 'PURGE')) or (p_run_mode = 'VERIFY') THEN
771: SELECT COUNT(*) INTO l_temp FROM XDP_ORDER_BUNDLES
772: WHERE order_id = p_order_id;
1487: COMMIT;
1488:
1489: l_user_rollback := USER_ROLLBACK_CTRL(p_rollback_segment);
1490:
1491: SELECT COUNT(*) INTO l_no_reords FROM WF_ITEMS
1492: WHERE ITEM_TYPE='XDPRECOV' AND END_DATE < p_time_to;
1493:
1494: RECORDS_PURGED_MSGS(l_no_reords,'WF_ITEMS XDPRECOV',l_rec_name,-1);
1495:
1490:
1491: SELECT COUNT(*) INTO l_no_reords FROM WF_ITEMS
1492: WHERE ITEM_TYPE='XDPRECOV' AND END_DATE < p_time_to;
1493:
1494: RECORDS_PURGED_MSGS(l_no_reords,'WF_ITEMS XDPRECOV',l_rec_name,-1);
1495:
1496:
1497: IF p_run_mode = 'PURGE' THEN
1498: IF G_PURGE_WORK_FLOW = 'TRUE' THEN