[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)
337: -- which have details falling outside the DATA Set.
338:
339: DELETE FROM hxc_transactions ht
340: WHERE ROWID IN ( SELECT CHARTOROWID(trans_rowid)
341: FROM hxc_ar_trans_temp
342: WHERE thread_id = 0 )
343: AND NOT EXISTS( SELECT 1
344: FROM hxc_transaction_details htd
345: WHERE htd.transaction_id = ht.transaction_id);
346:
347:
348: -- Clear all the records, which are processed.
349:
350: DELETE FROM hxc_ar_trans_temp
351: WHERE thread_id = 0 ;
352:
353: COMMIT;
354:
708: -- Delete the records from hxc_transaction_details_ar using the ROWID.
709: -- Delete duplicate transaction ids from hxc_archive_temp.
710: -- Select all deposit transaction records from hxc_transactions table and
711: -- insert into hxc_transactions_ar table.
712: -- Select all retrieval transaction ids and insert into hxc_ar_trans_temp
713: -- table for the parent thread to process.
714: -- Delete all transaction records from hxc_transactions table, if the transaction
715: -- id is present in hxc_archive_temp.
716: -- Delete from hxc_archive temp for the the next iteration of this loop.
1294: l_trans_del_count := l_trans_del_count + SQL%ROWCOUNT;
1295:
1296: END IF;
1297:
1298: INSERT INTO hxc_ar_trans_temp
1299: ( transaction_id, data_set_id, thread_id, trans_rowid )
1300: SELECT bkuptxn.transaction_id,
1301: p_data_set_id,
1302: p_thread_id,