[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,
678: -- * Insert the above id-ovn combination into hxc_temp_timecard_chunks table.
679: -- * Pick up all records from hxc_time_building_blocks for these combinations
680: -- and insert into hxc_time_building_blocks_ar
681: -- * For the timecard records in hxc_temp_timecard_chunks, pick up all records
682: -- from hxc_tc_ap_links, and insert into itself with application_period scope.
683: -- * Join hxc_temp_timecard_chunks with hxc_tc_ap_links and insert into hxc_tc_ap_links_ar
684: -- table.
685: -- * Delete these records from hxc_tc_ap_links table.
686: -- * For all the application_period records in hxc_temp_timecard_chunks table, select from
679: -- * Pick up all records from hxc_time_building_blocks for these combinations
680: -- and insert into hxc_time_building_blocks_ar
681: -- * For the timecard records in hxc_temp_timecard_chunks, pick up all records
682: -- from hxc_tc_ap_links, and insert into itself with application_period scope.
683: -- * Join hxc_temp_timecard_chunks with hxc_tc_ap_links and insert into hxc_tc_ap_links_ar
684: -- table.
685: -- * Delete these records from hxc_tc_ap_links table.
686: -- * For all the application_period records in hxc_temp_timecard_chunks table, select from
687: -- hxc_time_building_blocks and insert into hxc_time_building_blocks_ar.
681: -- * For the timecard records in hxc_temp_timecard_chunks, pick up all records
682: -- from hxc_tc_ap_links, and insert into itself with application_period scope.
683: -- * Join hxc_temp_timecard_chunks with hxc_tc_ap_links and insert into hxc_tc_ap_links_ar
684: -- table.
685: -- * Delete these records from hxc_tc_ap_links table.
686: -- * For all the application_period records in hxc_temp_timecard_chunks table, select from
687: -- hxc_time_building_blocks and insert into hxc_time_building_blocks_ar.
688: -- * Delete from hxc_time_building_blocks these records.
689: -- * Pick up all records for the application_period from hxc_app_period_summary
901: 'APPLICATION_PERIOD',
902: ROWIDTOCHAR(talbkup.ROWID),
903: thread_id
904: FROM hxc_temp_timecard_chunks temp,
905: hxc_tc_ap_links talbkup
906: WHERE temp.id = talbkup.timecard_id
907: AND temp.scope IN ('TIMECARD')
908: AND thread_id = p_thread_id ;
909:
907: AND temp.scope IN ('TIMECARD')
908: AND thread_id = p_thread_id ;
909:
910:
911: INSERT INTO hxc_tc_ap_links_ar
912: ( timecard_id,
913: application_period_id)
914: SELECT timecard_id,
915: application_period_id
913: application_period_id)
914: SELECT timecard_id,
915: application_period_id
916: FROM hxc_temp_timecard_chunks temp,
917: hxc_tc_ap_links talbkup
918: WHERE temp.ref_rowid = talbkup.ROWID
919: AND temp.scope = ('APPLICATION_PERIOD')
920: AND thread_id = p_thread_id ;
921:
922:
923: l_tal_count := SQL%ROWCOUNT;
924:
925:
926: DELETE FROM hxc_tc_ap_links
927: WHERE ROWID IN ( SELECT CHARTOROWID(ref_rowid)
928: FROM hxc_temp_timecard_chunks
929: WHERE scope = 'APPLICATION_PERIOD'
930: AND thread_id = p_thread_id );