CREATE OR REPLACE PROCEDURE SADMIN.DEL_CONTRI_DET_FOR_RECON
IS
L_LIMIT PLS_INTEGER := 20000;
CURSOR C1
IS
SELECT row_id
FROM siebel.s_invoice_item
WHERE person_id IN
(SELECT b.row_id
FROM SADMIN.disalloc A, SIEBEL.S_CONTACT B
WHERE A.PD_NEW_EYEE_ID = B.PERSON_UID
AND B.CON_CD = 'Member'
AND A.PD_DT_MODIFY BETWEEN '01-JAN-2010'
AND '31-MAY-2010');
TYPE temp_mem IS TABLE OF C1%ROWTYPE
INDEX BY PLS_INTEGER;
tem_mp temp_mem;
BEGIN
OPEN C1;
LOOP
FETCH C1
BULK COLLECT INTO tem_mp
LIMIT L_LIMIT;
EXIT WHEN tem_mp.COUNT = 0;
FORALL indx IN 1 .. tem_mp.COUNT
DELETE FROM siebel.s_invoice_item
WHERE row_id = tem_mp (indx).row_id AND row_id LIKE '%-%-%';
DBMS_OUTPUT.put_line (TO_CHAR (SQL%ROWCOUNT) || ' rows deleted');
COMMIT;
END LOOP;
CLOSE C1;
END;
IS
L_LIMIT PLS_INTEGER := 20000;
CURSOR C1
IS
SELECT row_id
FROM siebel.s_invoice_item
WHERE person_id IN
(SELECT b.row_id
FROM SADMIN.disalloc A, SIEBEL.S_CONTACT B
WHERE A.PD_NEW_EYEE_ID = B.PERSON_UID
AND B.CON_CD = 'Member'
AND A.PD_DT_MODIFY BETWEEN '01-JAN-2010'
AND '31-MAY-2010');
TYPE temp_mem IS TABLE OF C1%ROWTYPE
INDEX BY PLS_INTEGER;
tem_mp temp_mem;
BEGIN
OPEN C1;
LOOP
FETCH C1
BULK COLLECT INTO tem_mp
LIMIT L_LIMIT;
EXIT WHEN tem_mp.COUNT = 0;
FORALL indx IN 1 .. tem_mp.COUNT
DELETE FROM siebel.s_invoice_item
WHERE row_id = tem_mp (indx).row_id AND row_id LIKE '%-%-%';
DBMS_OUTPUT.put_line (TO_CHAR (SQL%ROWCOUNT) || ' rows deleted');
COMMIT;
END LOOP;
CLOSE C1;
END;
No comments:
Post a Comment