[Home] [Help]
6: -- Procedure Name : archive_process
7: -- Description : This procedure is called during Archive Data Set process.
8: -- For a given data set, it copies the records FROM
9: -- base tables to archive tables AND DELETEs the records in base
10: -- table. It removes the links FROM hxc_tc_ap_links AND
11: -- hxc_ap_detail_links. It cancels open notifications. This
12: -- process is done in chunks.
13: ----------------------------------------------------------------------------
14: PROCEDURE archive_process(p_data_set_id NUMBER,
635: -- * Insert the above id-ovn combination into hxc_temp_timecard_chunks table.
636: -- * Pick up all records from hxc_time_building_blocks for these combinations
637: -- and insert into hxc_time_building_blocks_ar
638: -- * For the timecard records in hxc_temp_timecard_chunks, pick up all records
639: -- from hxc_tc_ap_links, and insert into itself with application_period scope.
640: -- * Join hxc_temp_timecard_chunks with hxc_tc_ap_links and insert into hxc_tc_ap_links_ar
641: -- table.
642: -- * Delete these records from hxc_tc_ap_links table.
643: -- * For all the application_period records in hxc_temp_timecard_chunks table, select from
636: -- * Pick up all records from hxc_time_building_blocks for these combinations
637: -- and insert into hxc_time_building_blocks_ar
638: -- * For the timecard records in hxc_temp_timecard_chunks, pick up all records
639: -- from hxc_tc_ap_links, and insert into itself with application_period scope.
640: -- * Join hxc_temp_timecard_chunks with hxc_tc_ap_links and insert into hxc_tc_ap_links_ar
641: -- table.
642: -- * Delete these records from hxc_tc_ap_links table.
643: -- * For all the application_period records in hxc_temp_timecard_chunks table, select from
644: -- hxc_time_building_blocks and insert into hxc_time_building_blocks_ar.
638: -- * For the timecard records in hxc_temp_timecard_chunks, pick up all records
639: -- from hxc_tc_ap_links, and insert into itself with application_period scope.
640: -- * Join hxc_temp_timecard_chunks with hxc_tc_ap_links and insert into hxc_tc_ap_links_ar
641: -- table.
642: -- * Delete these records from hxc_tc_ap_links table.
643: -- * For all the application_period records in hxc_temp_timecard_chunks table, select from
644: -- hxc_time_building_blocks and insert into hxc_time_building_blocks_ar.
645: -- * Delete from hxc_time_building_blocks these records.
646: -- * Pick up all records for the application_period from hxc_app_period_summary
858: 'APPLICATION_PERIOD',
859: ROWIDTOCHAR(talbkup.ROWID),
860: thread_id
861: FROM hxc_temp_timecard_chunks temp,
862: hxc_tc_ap_links talbkup
863: WHERE temp.id = talbkup.timecard_id
864: AND temp.scope IN ('TIMECARD')
865: AND thread_id = p_thread_id ;
866:
864: AND temp.scope IN ('TIMECARD')
865: AND thread_id = p_thread_id ;
866:
867:
868: INSERT INTO hxc_tc_ap_links_ar
869: ( timecard_id,
870: application_period_id)
871: SELECT timecard_id,
872: application_period_id
870: application_period_id)
871: SELECT timecard_id,
872: application_period_id
873: FROM hxc_temp_timecard_chunks temp,
874: hxc_tc_ap_links talbkup
875: WHERE temp.ref_rowid = talbkup.ROWID
876: AND temp.scope = ('APPLICATION_PERIOD')
877: AND thread_id = p_thread_id ;
878:
879:
880: l_tal_count := SQL%ROWCOUNT;
881:
882:
883: DELETE FROM hxc_tc_ap_links
884: WHERE ROWID IN ( SELECT CHARTOROWID(ref_rowid)
885: FROM hxc_temp_timecard_chunks
886: WHERE scope = 'APPLICATION_PERIOD'
887: AND thread_id = p_thread_id );