[Home] [Help]
39: temp1.start_time,
40: temp1.time_building_block_id tc_id,
41: temp2.thread_id||'('||temp2.chunk_number||')' detail
42: FROM hxc_ar_detail_log temp2,
43: hxc_ar_tc_ids_temp temp1
44: WHERE temp1.time_building_block_id = temp2.time_building_block_id
45: AND temp1.object_version_number = temp2.object_version_number
46: AND temp2.process_type NOT LIKE '%INCOMPLETE%'
47: ORDER BY temp2.thread_id,
55: temp1.start_time,
56: temp1.time_building_block_id tc_id,
57: temp2.thread_id||'('||temp2.chunk_number||')' detail
58: FROM hxc_ar_detail_log temp2,
59: hxc_ar_tc_ids_temp temp1
60: WHERE temp1.time_building_block_id = temp2.time_building_block_id
61: AND temp1.object_version_number = temp2.object_version_number
62: AND temp2.process_type LIKE '%INCOMPLETE%'
63: ORDER BY temp2.thread_id,
128:
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;
143: BULK COLLECT INTO l_tc_tab LIMIT 500;
144:
145: EXIT WHEN l_tc_tab.COUNT = 0;
146:
147: -- Insert these 500 records into hxc_ar_tc_ids_temp
148: FORALL i IN l_tc_tab.FIRST..l_tc_tab.LAST
149: INSERT INTO hxc_ar_tc_ids_temp
150: --(TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
151: --SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
145: EXIT WHEN l_tc_tab.COUNT = 0;
146:
147: -- Insert these 500 records into hxc_ar_tc_ids_temp
148: FORALL i IN l_tc_tab.FIRST..l_tc_tab.LAST
149: INSERT INTO hxc_ar_tc_ids_temp
150: --(TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
151: --SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
152: --APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
153: --PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,DATA_SET_ID,TRANSLATION_DISPLAY_KEY)
458:
459: CURSOR get_tcs
460: IS SELECT time_building_block_id,
461: object_version_number
462: FROM hxc_ar_tc_ids_temp
463: WHERE time_building_block_id >= p_from_id
464: AND time_building_block_id < p_to_id ;
465:
466: CURSOR get_transactions
620: -- This procedure is rewritten in line with the changed Archive process
621: -- The process is now multithreaded, and each thread processes independant
622: -- of others. Detailed algorithm/approach as below.
623:
624: -- HXC_AR_TC_IDS_TEMP table populated by the parent process holds all the timecard
625: -- scope records that are to be archived. The process parameters are as below.
626: -- p_from_id -- Starting time_building_block_id for this thread.
627: -- p_to_id -- Ending time_building_block_id for this thread
628: -- p_data_set_id -- Data set id to be populated in the tables.
629: -- p_thread_id -- Thread number for this thread.
630: --
631: -- Following are the steps.
632: --
633: -- * Pick up the timecards(id-ovn) from hxc_ar_tc_ids_temp table, limited by
634: -- chunk size profile option.
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