[Home] [Help]
129: -- Delete from the temp tables, if there is any left over data.
130:
131: DELETE FROM hxc_ar_detail_log ;
132: DELETE FROM hxc_ar_tc_ids_temp;
133: DELETE FROM hxc_ar_trans_temp;
134: DELETE FROM hxc_data_set_details;
135:
136: l_bb_ctr := 0;
137:
291: END IF ;
292: END IF;
293: END LOOP;
294:
295: -- Check if hxc_ar_trans_temp table has records.
296: -- ( Rownum condition put so that no FTS is issued by the DB.
297: -- We dont want the total count, just wanna know if it has atleast
298: -- one record )
299: SELECT COUNT(1)
297: -- We dont want the total count, just wanna know if it has atleast
298: -- one record )
299: SELECT COUNT(1)
300: INTO trans_count
301: FROM hxc_ar_trans_temp
302: WHERE rownum < 2;
303:
304: -- If it has records, it means that the thread processes would have
305: -- created RETRIEVAL transaction records in the table. Process them.
305: -- created RETRIEVAL transaction records in the table. Process them.
306: IF trans_count >= 1
307: THEN
308: -- Update all threads to zero, to ensure we seal them from the threads.
309: UPDATE hxc_ar_trans_temp
310: SET thread_id = 0 ;
311:
312: -- Not much processing left, just insert the records from hxc_transacitons
313: -- to hxc_transactions_ar if they are not present already.
322: CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
323: TRANSACTION_CODE
324: FROM hxc_transactions bkuptxn
325: WHERE bkuptxn.transaction_id IN ( SELECT temp.transaction_id
326: FROM hxc_ar_trans_temp temp
327: WHERE thread_id = 0 )
328: AND bkuptxn.transaction_id NOT IN ( SELECT transaction_id
329: FROM hxc_transactions_ar hxc
330: WHERE bkuptxn.transaction_id = hxc.transaction_id)
333: -- Delete those records from hxc_transactions.
334:
335: DELETE FROM hxc_transactions
336: WHERE ROWID IN ( SELECT CHARTOROWID(trans_rowid)
337: FROM hxc_ar_trans_temp
338: WHERE thread_id = 0 );
339:
340:
341: -- Clear all the records, which are processed.
339:
340:
341: -- Clear all the records, which are processed.
342:
343: DELETE FROM hxc_ar_trans_temp
344: WHERE thread_id = 0 ;
345:
346: COMMIT;
347:
665: -- Delete the records from hxc_transaction_details_ar using the ROWID.
666: -- Delete duplicate transaction ids from hxc_archive_temp.
667: -- Select all deposit transaction records from hxc_transactions table and
668: -- insert into hxc_transactions_ar table.
669: -- Select all retrieval transaction ids and insert into hxc_ar_trans_temp
670: -- table for the parent thread to process.
671: -- Delete all transaction records from hxc_transactions table, if the transaction
672: -- id is present in hxc_archive_temp.
673: -- Delete from hxc_archive temp for the the next iteration of this loop.
1236: AND type <> 'RETRIEVAL';
1237:
1238: l_trans_del_count := l_trans_del_count + SQL%ROWCOUNT;
1239:
1240: INSERT INTO hxc_ar_trans_temp
1241: ( transaction_id, data_set_id, thread_id, trans_rowid )
1242: SELECT bkuptxn.transaction_id,
1243: p_data_set_id,
1244: p_thread_id,