45: IS
46:
47: CURSOR c_list_entries_deleted IS
48: SELECT LIST_ENTRY_ROW_ID, LIST_HEADER_ID, ROWID PURGE_ROWID
49: FROM AMS_LIST_ENTRIES_PURGE;
50:
51: TYPE TBL_ROWID_TYPE IS TABLE OF VARCHAR2(2000);
52: TYPE LIST_NUM_TYPE IS TABLE OF NUMBER;
53: TYPE LIST_REC_TYPE IS RECORD ( tbl_rowid TBL_ROWID_TYPE,
73:
74: FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start '|| l_full_name || '---');
75: FND_FILE.PUT_LINE(FND_FILE.LOG, 'list_header_id: ' || p_list_name);
76:
77: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || l_schema || '.AMS_LIST_ENTRIES_PURGE');
78:
79: INSERT INTO AMS_LIST_ENTRIES_PURGE (LIST_HEADER_ID,LIST_ENTRY_ROW_ID)
80: SELECT ale.list_header_id list_header_id, ale.rowid list_entry_row_id
81: FROM AMS_LIST_ENTRIES ale
75: FND_FILE.PUT_LINE(FND_FILE.LOG, 'list_header_id: ' || p_list_name);
76:
77: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || l_schema || '.AMS_LIST_ENTRIES_PURGE');
78:
79: INSERT INTO AMS_LIST_ENTRIES_PURGE (LIST_HEADER_ID,LIST_ENTRY_ROW_ID)
80: SELECT ale.list_header_id list_header_id, ale.rowid list_entry_row_id
81: FROM AMS_LIST_ENTRIES ale
82: WHERE ale.list_header_id = p_list_name
83: AND ale.enabled_flag = 'N' ;
77: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || l_schema || '.AMS_LIST_ENTRIES_PURGE');
78:
79: INSERT INTO AMS_LIST_ENTRIES_PURGE (LIST_HEADER_ID,LIST_ENTRY_ROW_ID)
80: SELECT ale.list_header_id list_header_id, ale.rowid list_entry_row_id
81: FROM AMS_LIST_ENTRIES ale
82: WHERE ale.list_header_id = p_list_name
83: AND ale.enabled_flag = 'N' ;
84:
85: COMMIT;
83: AND ale.enabled_flag = 'N' ;
84:
85: COMMIT;
86:
87: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Inserted into AMS_LIST_ENTRIES_PURGE ' );
88:
89: -- Delete with incremental commit
90: LOOP
91:
118: l_limit_flag := TRUE;
119: END IF;
120:
121: -- Delete from tables
122: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Delete list_entries from AMS_LIST_ENTRIES ' );
123: IF AMS_DEBUG_LOW_ON THEN
124: FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 1, l_cursor_limit ' || l_cursor_limit );
125: FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 1, l_list_rec.list_header_id.first ' || l_list_rec.list_header_id.first );
126: FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 1, l_list_rec.list_header_id.last ' || l_list_rec.list_header_id.last );
148: l_last := l_list_rec.list_header_id.last;
149: END IF;
150:
151: FORALL i in l_first..l_last
152: DELETE FROM AMS_LIST_ENTRIES
153: WHERE rowid = l_list_rec.tbl_rowid(i);
154: FORALL i in l_first..l_last
155: DELETE FROM AMS_LIST_ENTRIES_PURGE
156: WHERE rowid = l_list_rec.purge_rowid(i);
151: FORALL i in l_first..l_last
152: DELETE FROM AMS_LIST_ENTRIES
153: WHERE rowid = l_list_rec.tbl_rowid(i);
154: FORALL i in l_first..l_last
155: DELETE FROM AMS_LIST_ENTRIES_PURGE
156: WHERE rowid = l_list_rec.purge_rowid(i);
157: COMMIT;
158:
159: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Records deleted: ' || l_first ||'-'|| l_last );