[Home] [Help]
166: p_stop_date in DATE)
167: IS
168:
169: CURSOR c_tbb_id(p_data_set_id number) is
170: SELECT /*+ INDEX( hxc hxc_time_building_blocks_n1) */
171: distinct time_building_block_id
172: FROM hxc_time_building_blocks hxc
173: WHERE scope ='TIMECARD'
174: AND (data_set_id <> p_data_set_id OR data_set_id IS NULL)
168:
169: CURSOR c_tbb_id(p_data_set_id number) is
170: SELECT /*+ INDEX( hxc hxc_time_building_blocks_n1) */
171: distinct time_building_block_id
172: FROM hxc_time_building_blocks hxc
173: WHERE scope ='TIMECARD'
174: AND (data_set_id <> p_data_set_id OR data_set_id IS NULL)
175: AND stop_time BETWEEN p_start_date AND p_stop_date;
176:
184:
185: -- Bug 7358756
186: -- Archive/Restore process re-architecture.
187: -- HXC_TEMP_TIMECARD_CHUNKS is a global temporary table now
188: -- No need to update any other table except HXC_TIME_BUILDING_BLOCKS ( scope : timecard )
189: -- since the Archive/Restore process drives it from there.
190: -- Removed all unwanted logging, and comments.
191:
192:
239:
240: -- after just DELETE the bulk table
241: l_tbb_id_tab.DELETE;
242:
243: UPDATE hxc_time_building_blocks tbb
244: SET data_set_id = p_data_set_id
245: WHERE scope ='TIMECARD'
246: AND time_building_block_id in
247: (SELECT temp.id
255: -- The following lines of code commented out, we no longer need it.
256:
257: /* INSERT INTO hxc_temp_timecard_chunks (id,data_set_id,scope)
258: SELECT distinct day.time_building_block_id, p_data_set_id,'DAY'
259: FROM hxc_time_building_blocks day
260: WHERE day.scope = 'DAY'
261: AND day.parent_building_block_id in
262: (SELECT temp.id
263: FROM hxc_temp_timecard_chunks temp
265: AND temp.data_set_id = p_data_set_id);
266: --AND day.data_set_id = p_data_set_id;
267:
268:
269: UPDATE hxc_time_building_blocks tbb
270: SET data_set_id = p_data_set_id
271: WHERE scope ='DAY'
272: AND time_building_block_id in
273: (SELECT temp.id
277:
278:
279: INSERT INTO hxc_temp_timecard_chunks (id,data_set_id, scope)
280: SELECT distinct det.time_building_block_id,p_data_set_id,'DETAIL'
281: FROM hxc_time_building_blocks det
282: WHERE det.scope = 'DETAIL'
283: AND det.parent_building_block_id IN
284: (SELECT temp.id
285: FROM hxc_temp_timecard_chunks temp
287: AND temp.data_set_id = p_data_set_id);
288: --AND det.data_set_id = p_data_set_id;
289:
290:
291: UPDATE hxc_time_building_blocks tbb
292: SET data_set_id = p_data_set_id
293: WHERE scope ='DETAIL'
294: AND time_building_block_id in
295: (SELECT ID
466:
467: l_fnd_logging := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
468: l_chunk_size := nvl(fnd_profile.value('HXC_ARCHIVE_RESTORE_CHUNK_SIZE'),25);
469:
470: undo_define_table(p_table_name => 'HXC_TIME_BUILDING_BLOCKS',
471: p_data_set_id => p_data_set_id,
472: p_chunk_size => l_chunk_size);
473:
474: undo_define_table(p_table_name => 'HXC_TIME_ATTRIBUTE_USAGES',
530:
531:
532: CURSOR c_get_unretrieved_tcs(p_data_set_id number,l_bee_retrieval number)
533: IS
534: SELECT /*+ NO_INDEX(day HXC_TIME_BUILDING_BLOCKS_N2) NO_INDEX(det HXC_TIME_BUILDING_BLOCKS_N2) */
535: DISTINCT
536: tsum.resource_id,
537: tsum.start_time,
538: tsum.stop_time,
539: tsum.approval_status,
540: nvl(per.employee_number,'Employee Number Unkown') employee_number,
541: nvl(per.full_name,'Full Name Unknown') full_name
542: FROM
543: hxc_time_building_blocks day,
544: hxc_time_building_blocks det,
545: hxc_latest_details hld,
546: hxc_data_sets hds,
547: hxc_timecard_summary tsum,
540: nvl(per.employee_number,'Employee Number Unkown') employee_number,
541: nvl(per.full_name,'Full Name Unknown') full_name
542: FROM
543: hxc_time_building_blocks day,
544: hxc_time_building_blocks det,
545: hxc_latest_details hld,
546: hxc_data_sets hds,
547: hxc_timecard_summary tsum,
548: hxc_application_sets_v has,
950: IS
951:
952: CURSOR c_lock_resource is
953: SELECT distinct resource_id
954: FROM hxc_time_building_blocks
955: WHERE data_set_id = p_data_set_id
956: AND scope = 'TIMECARD';
957:
958: l_row_lock_id ROWID;