1 PACKAGE BODY hxc_data_set AS
2 /* $Header: hxcdataset.pkb 120.8.12020000.2 2012/07/04 09:18:20 amnaraya ship $ */
3
4 ----------------------------------------------------------------------------
5 -- Function Name : validate_data_set_range
6 -- Description : This is called during Define Data Set to check if
7 -- 1)any data set exists with the same name
8 -- 2)if the data set range overlaps with some other
9 -- data set range
10 -- Returns true if everything is fine with the name and range;
11 -- else returns false
12 ----------------------------------------------------------------------------
13 FUNCTION validate_data_set_range
14 (p_data_set_name IN VARCHAR2,
15 p_start_date IN DATE,
16 p_stop_date IN DATE)
17 RETURN BOOLEAN
18 IS
19
20 CURSOR c_check_range_exists(p_start_date date, p_end_date date)
21 IS
22 SELECT 1
23 FROM hxc_data_sets
24 WHERE start_date < p_end_date
25 AND end_date > p_start_date;
26
27 CURSOR c_check_name_exists(p_data_set_name varchar2)
28 IS
29 SELECT 1
30 FROM hxc_data_sets
31 WHERE data_set_name = p_data_set_name;
32
33
34 l_dummy pls_integer;
35
36 BEGIN
37
38 -- check the date first
39 IF p_start_date >= p_stop_date THEN
40 fnd_file.put_line(fnd_file.LOG,'--- >The Start Date of the Data Set cannot be greater than the End Date ');
41 RETURN FALSE;
42 END IF;
43
44
45
46 --check for date range
47 OPEN c_check_range_exists(p_start_date,p_stop_date);
48 FETCH c_check_range_exists INTO l_dummy;
49 IF c_check_range_exists%found THEN
50
51 CLOSE c_check_range_exists;
52 fnd_file.put_line(fnd_file.LOG,'--- >There is an existing Data Set whose range overlaps with the '
53 ||'period specified');
54 RETURN FALSE;
55
56 END IF;
57 CLOSE c_check_range_exists;
58
59 fnd_file.put_line(fnd_file.LOG,'--- >After checking the valid Data Set range');
60
61 OPEN c_check_name_exists(p_data_set_name);
62 FETCH c_check_name_exists INTO l_dummy;
63 IF c_check_name_exists%found THEN
64
65 close c_check_name_exists;
66 fnd_file.put_line(fnd_file.LOG,'--- >Data Set name is already used');
67 RETURN FALSE;
68
69 END IF;
70 CLOSE c_check_name_exists;
71
72 fnd_file.put_line(fnd_file.LOG,'--- >After checking the unique name');
73
74 RETURN TRUE;
75 END validate_data_set_range;
76
77
78 --------------------------------------------------------------------------------------------------
79 -- Procedure Name : show_data_set
80 -- Description : This procedure show in the log file the data set already definied.
81 --------------------------------------------------------------------------------------------------
82
83 PROCEDURE show_data_set is
84
85 cursor c_data_sets is
86 select DATA_SET_ID,DATA_SET_NAME,DESCRIPTION,START_DATE,END_DATE,DATA_SET_MODE,
87 decode(STATUS,'BACKUP_IN_PROGRESS','ARCHIVE_IN_PROGRESS',STATUS) status,
88 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,VALIDATION_STATUS
89 from hxc_data_sets
90 where status <> 'MARKING_IN_PROGRESS';
91
92
93 BEGIN
94
95 fnd_file.put_line(fnd_file.LOG,'------------------------------------------------');
96 fnd_file.put_line(fnd_file.LOG,'----------- EXISTING DATA SETS -----------');
97 fnd_file.put_line(fnd_file.LOG,'------------------------------------------------');
98
99 FOR crs_data_sets in c_data_sets LOOP
100
101 fnd_file.put_line(fnd_file.LOG,' --> Data Set Id: '||crs_data_sets.data_set_id);
102 fnd_file.put_line(fnd_file.LOG,' --> Data Set Name: '||crs_data_sets.data_set_name);
103 fnd_file.put_line(fnd_file.LOG,' --> Description: '||crs_data_sets.description);
104 fnd_file.put_line(fnd_file.LOG,' --> Data Set Status: '||crs_data_sets.status);
105 fnd_file.put_line(fnd_file.LOG,' --> Date From: '||crs_data_sets.start_date);
106 fnd_file.put_line(fnd_file.LOG,' --> Date To: '||crs_data_sets.end_date);
107 fnd_file.put_line(fnd_file.LOG,' --> Validation Status: '||crs_data_sets.validation_status);
108 fnd_file.put_line(fnd_file.LOG,' ---------');
109
110
111
112 END LOOP;
113
114 fnd_file.put_line(fnd_file.LOG,'------------------------------------------------');
115
116
117 END show_data_set;
118
119
120
121 --------------------------------------------------------------------------------------------------
122 --Procedure Name : insert_into_data_set
123 --Description : This procedure inserts the record into hxc_data_sets table corresponding to new
124 -- Data set.
125 --------------------------------------------------------------------------------------------------
126
127 PROCEDURE insert_into_data_set(p_data_set_id OUT NOCOPY NUMBER,
128 p_data_set_name IN VARCHAR2,
129 p_description IN VARCHAR2,
130 p_start_date IN DATE,
131 p_stop_date IN DATE,
132 p_status IN VARCHAR2) is
133
134 BEGIN
135
136 --get the sequence from hxc_data_sets_s
137 select hxc_data_sets_s.nextval into p_data_set_id from dual;
138
139 insert into hxc_data_sets
140 (data_set_id,
141 data_set_name,
142 description,
143 start_date,
144 end_date,
145 data_set_mode,
146 status)
147 values
148 (p_data_set_id,
149 p_data_set_name,
150 p_description,
151 p_start_date,
152 p_stop_date,
153 'B',
154 p_status);
155
156 commit;
157
158 END insert_into_data_set;
159
160 --------------------------------------------------------------------------------------------------
161 --Procedure Name : mark_tables_with_data_set
162 --Description :
163 --------------------------------------------------------------------------------------------------
164 PROCEDURE mark_tables_with_data_set(p_data_set_id in number,
165 p_start_date in DATE,
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)
175 AND stop_time BETWEEN p_start_date AND p_stop_date;
176
177 l_tbb_id_tab hxc_archive_restore_utils.t_tbb_id;
178
179 l_fnd_logging varchar2(10);
180 l_chunk_size number;
181
182 BEGIN
183
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
193 hr_general.g_data_migrator_mode := 'Y';
194
195 l_fnd_logging := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
196 l_chunk_size := nvl(fnd_profile.value('HXC_ARCHIVE_RESTORE_CHUNK_SIZE'),50);
197
198 OPEN c_tbb_id(p_data_set_id);
199
200 LOOP
201 -- we take 100 timecard ids within the given range per iteration
202 -- and mark the corresponding records in the base tables
203 FETCH c_tbb_id bulk collect INTO l_tbb_id_tab limit l_chunk_size;
204
205 fnd_file.put_line(fnd_file.LOG,'================================================================');
206 fnd_file.put_line(fnd_file.LOG,'Entering in a new chunk l_tbb_id_tab.count '||l_tbb_id_tab.count);
207 fnd_file.put_line(fnd_file.LOG,'================================================================');
208
209 IF l_tbb_id_tab.count = 0 THEN
210 CLOSE c_tbb_id;
211 EXIT;
212 END IF;
213
214
215 /* Removed the following since this is a GTT henceforth
216 -- before starting let's DELETE all the data of the data_set_id in the
217 -- temporary table
218
219 DELETE FROM hxc_temp_timecard_chunks
220 WHERE data_set_id = p_data_set_id;
221 */
222
223
224
225 FORALL x IN l_tbb_id_tab.first..l_tbb_id_tab.last
226
227 ---------------------------------
228 -- TIMECARD SCOPE BUILDING BLOCK
229 ---------------------------------
230 -- first get the chunk of timecard
231 -- to work on in the temp chunk table
232 INSERT INTO hxc_temp_timecard_chunks
233 (data_set_id,id, scope)
234 VALUES
235 (p_data_set_id,l_tbb_id_tab(x),'TIMECARD');
236
237 fnd_file.put_line(fnd_file.LOG,'----------------------------------------------------');
238 fnd_file.put_line(fnd_file.LOG,'--- >Count of TIMECARD INSERT INTO TEMP table for this chunk: '||sql%rowcount);
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
248 FROM hxc_temp_timecard_chunks temp
249 WHERE temp.data_set_id = p_data_set_id
250 AND temp.scope = 'TIMECARD');
251
252 fnd_file.put_line(fnd_file.LOG,'--- >Count of TIMECARD UPDATE for this chunk: '||sql%rowcount);
253
254 -- Bug 7358756
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
264 WHERE temp.scope = 'TIMECARD'
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
274 FROM hxc_temp_timecard_chunks temp
275 WHERE temp.data_set_id = p_data_set_id
276 AND temp.scope = 'DAY');
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
286 WHERE temp.scope = 'DAY'
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
296 FROM hxc_temp_timecard_chunks temp
297 WHERE temp.data_set_id = p_data_set_id
298 AND temp.scope = 'DETAIL');
299
300 UPDATE hxc_time_attribute_usages
301 SET data_set_id = p_data_set_id
302 WHERE time_building_block_id in
303 (SELECT temp.id
304 FROM hxc_temp_timecard_chunks temp
305 WHERE temp.data_set_id = p_data_set_id
306 AND temp.scope in ('TIMECARD','DAY','DETAIL'));
307
308
309
310 update hxc_time_attributes
311 set data_set_id = l_data_set_id
312 where time_attribute_id in
313 (select time_attribute_id from hxc_time_attribute_usages
314 where data_set_id = l_data_set_id)
315 and data_set_id is null
316 and nvl(consolidated_flag,'N') <> 'Y';
317
318 UPDATE hxc_transaction_details htd
319 SET htd.data_set_id = p_data_set_id
320 where htd.time_building_block_id in
321 (SELECT temp.id
322 FROM hxc_temp_timecard_chunks temp
323 WHERE temp.data_set_id = p_data_set_id
324 AND temp.scope in ('TIMECARD','DAY','DETAIL'));
325
326 UPDATE hxc_transactions
327 SET data_set_id = p_data_set_id
328 WHERE transaction_id in
329 (SELECT distinct transaction_id
330 FROM hxc_transaction_details txnd,
331 hxc_temp_timecard_chunks temp
332 WHERE txnd.time_building_block_id = temp.id
333 AND temp.data_set_id = p_data_set_id
334 AND temp.scope in ('TIMECARD','DAY','DETAIL'))
335 AND type = 'DEPOSIT';
336
337 */
338 -- Set the Summary timecard table
339 UPDATE hxc_timecard_summary hts
340 SET data_set_id = p_data_set_id
341 where timecard_id in
342 (SELECT temp.id
343 FROM hxc_temp_timecard_chunks temp
344 WHERE temp.data_set_id = p_data_set_id
345 AND temp.scope = 'TIMECARD');
346
347 fnd_file.put_line(fnd_file.LOG,'----------------------------------------------------');
348 fnd_file.put_line(fnd_file.LOG,'--- >Count of TIMECARD SUMMARY for this chunk: '||sql%rowcount);
349
350
351 COMMIT;
352
353 END LOOP;
354
355 IF c_tbb_id%ISOPEN THEN
356 CLOSE c_tbb_id;
357 END IF;
358
359 --final commit;
360 COMMIT;
361
362 END mark_tables_with_data_set;
363
364 --------------------------------------------------------------------------------------------------
365 --Procedure Name : get_data_set_info
366 --Description :
367 --------------------------------------------------------------------------------------------------
368 PROCEDURE get_data_set_info(p_data_set_id IN NUMBER,
369 p_data_set_name OUT NOCOPY VARCHAR2,
370 p_description OUT NOCOPY VARCHAR2,
371 p_start_date OUT NOCOPY DATE,
372 p_stop_date OUT NOCOPY DATE,
373 p_data_set_mode OUT NOCOPY VARCHAR2,
374 p_status OUT NOCOPY VARCHAR2,
375 p_validation_status OUT NOCOPY VARCHAR2,
376 p_found_data_set OUT NOCOPY BOOLEAN)
377 IS
378
379 CURSOR c_get_data_set_status is
380 SELECT data_set_name, description, start_date, end_date,
381 data_set_mode, status, validation_status
382 FROM hxc_data_sets
383 WHERE data_set_id = p_data_set_id;
384
385 BEGIN
386
387 p_found_data_set := FALSE;
388
389 OPEN c_get_data_set_status;
390 FETCH c_get_data_set_status INTO p_data_set_name, p_description, p_start_date, p_stop_date,
391 p_data_set_mode, p_status, p_validation_status;
392 IF c_get_data_set_status%FOUND
393 THEN
394
395 p_found_data_set := TRUE;
396
397 END IF;
398 CLOSE c_get_data_set_status;
399
400 END get_data_set_info;
401
402
403 ----------------------------------------------------------------------------
404 -- Procedure Name : undo_define_table
405 -- Description : This procedure is called during Undo Define Data Set process.
406 -- For a given table and data set, it updates the data set
407 -- id to null in chunks of 100 records
408 ----------------------------------------------------------------------------
409 PROCEDURE undo_define_table (p_table_name VARCHAR2,
410 p_data_set_id NUMBER,
411 p_chunk_size NUMBER)
412 IS
413
414 l_sql VARCHAR2(2000);
415
416 BEGIN
417
418 l_sql := 'update '||p_table_name||
419 ' set data_set_id = null where data_set_id = '||p_data_set_id||
420 ' and rownum <= '||p_chunk_size;
421
422 LOOP
423
424 EXECUTE IMMEDIATE l_sql;
425 IF sql%notfound THEN
426 return;
427 END IF;
428
429 COMMIT;
430
431 END LOOP;
432 /*
433 EXCEPTION
434 WHEN OTHERS THEN
435
436 fnd_file.put_line(fnd_file.LOG,'Error during undo data set: '||sqlerrm);
437 ROLLBACK;
438 p_retcode:=2;
439
440 IF (nvl(fnd_profile.value('AFLOG_ENABLED'),'N')='Y') THEN
441
442 hxc_archive_restore_debug.print_table_record(p_table_name,
443 p_data_set_id,
444 p_column1,
445 p_column2);
446 END IF;
447 */
448
449 END undo_define_table;
450
451
452 --------------------------------------------------------------------------------------------------
453 --Procedure Name : undo_define_data_set
454 --Description :
455 --------------------------------------------------------------------------------------------------
456
457 PROCEDURE undo_define_data_set(p_data_set_id IN NUMBER)
458 IS
459
460 l_chunk_size NUMBER;
461 l_fnd_logging VARCHAR2(10);
462
463 BEGIN
464
465 hr_general.g_data_migrator_mode := 'Y';
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',
475 p_data_set_id => p_data_set_id,
476 p_chunk_size => l_chunk_size);
477
478 undo_define_table(p_table_name => 'HXC_TIME_ATTRIBUTES',
479 p_data_set_id => p_data_set_id,
480 p_chunk_size => l_chunk_size);
481
482 undo_define_table(p_table_name => 'HXC_TRANSACTION_DETAILS',
483 p_data_set_id => p_data_set_id,
484 p_chunk_size => l_chunk_size);
485
486 undo_define_table(p_table_name => 'HXC_TRANSACTIONS',
487 p_data_set_id => p_data_set_id,
488 p_chunk_size => l_chunk_size);
489
490 undo_define_table(p_table_name => 'HXC_TIMECARD_SUMMARY',
491 p_data_set_id => p_data_set_id,
492 p_chunk_size => l_chunk_size);
493
494 DELETE FROM hxc_data_sets
495 WHERE data_set_id = p_data_set_id;
496
497 COMMIT;
498
499 END undo_define_data_set;
500
501
502 ----------------------------------------------------------------------------
503 -- Procedure Name : validate_data_set
504 -- Description : This is the starting point of the concurrent program
505 -- 'Validate Data Set'
506 ----------------------------------------------------------------------------
507 PROCEDURE validate_data_set (p_data_set_id in number,
508 p_error_count out NOCOPY number,
509 p_all_errors in boolean default FALSE)
510 IS
511
512 --Cursor to fetch all the non-errord status unreterived Timecards.
513
514
515 -- Bug 6744917
516 -- Added condition to compare det.date_to to hr_general.end_of_time.
517 -- The query just pulled out all timecards which has details without
518 -- transaction details of type RETRIEVAL.
519 -- For a detail which is overwritten or deleted and where the original
520 -- entry was never retrieved, there neednt be retrieval happening
521 -- ever. The condition added is as follows.
522 -- * If the detail record is not end dated, pull it up anyways
523 -- as a warning, because this has to be retrieved and cant be archived.
524 -- * If the detail record is end dated, check if there was a previous
525 -- OVN of this retrieved. In that case, you have to retrieve this
526 -- also to make adjustments, so pull this record to show in warnings.
527 -- If there is no previous OVN retrieved, and the detail is end dated
528 -- it means this detail is never going to be retrieved at all, and there
529 -- is no point in stopping it from getting archived.
530
531 -- Bug 14028697
532 -- Modified the below cursor to have a better access path.
533 -- hxc_data_sets -> hxc_timecard_summary -> hxc_time_building_blocks
534 --
535
536 /*
537
538 CURSOR c_get_unretrieved_tcs(p_data_set_id number,l_bee_retrieval number)
539 IS
540 SELECT /*+ NO_INDEX(day HXC_TIME_BUILDING_BLOCKS_N2) NO_INDEX(det HXC_TIME_BUILDING_BLOCKS_N2) *
541 DISTINCT
542 tsum.resource_id,
543 tsum.start_time,
544 tsum.stop_time,
545 tsum.approval_status,
546 nvl(per.employee_number,'Employee Number Unkown') employee_number,
547 nvl(per.full_name,'Full Name Unknown') full_name
548 FROM
549 hxc_time_building_blocks day,
550 hxc_time_building_blocks det,
551 hxc_latest_details hld,
552 hxc_data_sets hds,
553 hxc_timecard_summary tsum,
554 hxc_application_sets_v has,
555 hxc_application_set_comps_v hasv,
556 per_all_people_f per
557 WHERE
558 NOT EXISTS
559 (SELECT 1
560 FROM hxc_transaction_details txnd,
561 hxc_transactions txn,
562 hxc_retrieval_processes rp
563 WHERE txn.type = 'RETRIEVAL'
564 AND txn.status = 'SUCCESS'
565 AND txnd.status = 'SUCCESS'
566 AND txnd.time_building_block_id = hld.time_building_block_id
567 AND txnd.time_building_block_ovn = hld.object_version_number
568 AND txnd.transaction_id = txn.transaction_id
569 AND decode(txn.transaction_process_id,-1,l_bee_retrieval,txn.transaction_process_id) = rp.retrieval_process_id
570 AND rp.time_recipient_id = hasv.time_recipient_id
571 )
572 AND per.person_id = tsum.resource_id
573 AND sysdate between per.effective_start_date and per.effective_end_date
574 AND hds.data_set_id =p_data_set_id
575 AND has.application_set_id = hasv.application_set_id
576 AND has.application_set_id = hld.application_set_id
577 AND hld.time_building_block_id = det.time_building_block_id
578 AND hld.object_version_number = det.object_version_number
579 AND det.parent_building_block_id = day.time_building_block_id
580 AND det.parent_building_block_ovn = day.object_version_number
581 AND ( ( det.date_to = hr_general.end_of_time
582 )
583 OR ( det.date_to <> hr_general.end_of_time
584 AND EXISTS (SELECT 1
585 FROM hxc_transaction_details txnd1,
586 hxc_transactions txn1,
587 hxc_retrieval_processes rp1
588 WHERE txn1.type = 'RETRIEVAL'
589 AND txn1.status = 'SUCCESS'
590 AND txnd1.status = 'SUCCESS'
591 AND txnd1.time_building_block_id = hld.time_building_block_id
592 AND txnd1.time_building_block_ovn < hld.object_version_number
593 AND txnd1.transaction_id = txn1.transaction_id
594 AND decode(txn1.transaction_process_id,-1,
595 l_bee_retrieval,
596 txn1.transaction_process_id) = rp1.retrieval_process_id
597 AND rp1.time_recipient_id = hasv.time_recipient_id
598 )
599 )
600 )
601 AND day.parent_building_block_id = tsum.timecard_id
602 AND day.parent_building_block_ovn = tsum.timecard_ovn
603 --AND det.data_set_id = hds.data_set_id
604 --AND day.data_set_id = det.data_set_id
605 --AND tsum.data_set_id = day.data_set_id
606 AND det.scope = 'DETAIL'
607 AND day.scope = 'DAY'
608 AND tsum.stop_time BETWEEN hds.start_date AND hds.end_date
609 AND tsum.approval_status<>'ERROR'
610 ORDER BY tsum.approval_status,tsum.start_time,tsum.resource_id;
611
612 */
613
614 CURSOR c_get_unretrieved_tcs(p_data_set_id number,l_bee_retrieval number)
615 IS
616 SELECT/*+ INDEX(hds HXC_DATA_SETS_PK)
617 INDEX(tsum HXC_TIMECARD_SUMMARY_N1)
618 INDEX(day HXC_TIME_BUILDING_BLOCKS_FK3)
619 INDEX(det HXC_TIME_BUILDING_BLOCKS_FK3)
620 INDEX(per PER_PEOPLE_F_PK) */
621 DISTINCT
622 tsum.resource_id,
623 tsum.start_time,
624 tsum.stop_time,
625 tsum.approval_status,
626 nvl(per.employee_number,'Employee Number Unkown') employee_number,
627 nvl(per.full_name,'Full Name Unknown') full_name
628 FROM hxc_data_sets hds,
629 hxc_timecard_summary tsum,
630 hxc_time_building_blocks day,
631 hxc_time_building_blocks det,
632 hxc_latest_details hld,
633 hxc_application_sets_v has,
634 hxc_application_set_comps_v hasv,
635 per_all_people_f per
636 WHERE
637 NOT EXISTS
638 (SELECT /*+ INDEX(rp HXC_RETRIEVAL_PROCESSES_PK) */
639 1
640 FROM hxc_transaction_details txnd,
641 hxc_transactions txn,
642 hxc_retrieval_processes rp
643 WHERE txn.type = 'RETRIEVAL'
644 AND txn.status = 'SUCCESS'
645 AND txnd.status = 'SUCCESS'
646 AND txnd.time_building_block_id = hld.time_building_block_id
647 AND txnd.time_building_block_ovn = hld.object_version_number
648 AND txnd.transaction_id = txn.transaction_id
649 AND DECODE(txn.transaction_process_id,-1,
650 l_bee_retrieval,
651 txn.transaction_process_id) = rp.retrieval_process_id
652 AND rp.time_recipient_id = hasv.time_recipient_id
653 )
654 AND per.person_id = tsum.resource_id
655 AND SYSDATE BETWEEN per.effective_start_date
656 AND per.effective_end_date
657 AND hds.data_set_id =p_data_set_id
658 AND has.application_set_id = hasv.application_set_id
659 AND has.application_set_id = hld.application_set_id
660 AND hld.time_building_block_id = det.time_building_block_id
661 AND hld.object_version_number = det.object_version_number
662 AND det.parent_building_block_id = day.time_building_block_id
663 AND det.parent_building_block_ovn = day.object_version_number
664 AND ( ( det.date_to = hr_general.end_of_time
665 )
666 OR ( det.date_to <> hr_general.end_of_time
667 AND EXISTS (SELECT /*+ INDEX(rp1 HXC_RETRIEVAL_PROCESSES_PK) */
668 1
669 FROM hxc_transaction_details txnd1,
670 hxc_transactions txn1,
671 hxc_retrieval_processes rp1
672 WHERE txn1.type = 'RETRIEVAL'
673 AND txn1.status = 'SUCCESS'
674 AND txnd1.status = 'SUCCESS'
675 AND txnd1.time_building_block_id = hld.time_building_block_id
676 AND txnd1.time_building_block_ovn < hld.object_version_number
677 AND txnd1.transaction_id = txn1.transaction_id
678 AND decode(txn1.transaction_process_id,-1,
679 l_bee_retrieval,
680 txn1.transaction_process_id) = rp1.retrieval_process_id
681 AND rp1.time_recipient_id = hasv.time_recipient_id
682 )
683 )
684 )
685 AND day.parent_building_block_id = tsum.timecard_id
686 AND day.parent_building_block_ovn = tsum.timecard_ovn
687 --AND det.data_set_id = hds.data_set_id
688 --AND day.data_set_id = det.data_set_id
689 AND tsum.data_set_id = hds.data_set_id
690 AND det.scope = 'DETAIL'
691 AND day.scope = 'DAY'
692 AND tsum.stop_time BETWEEN hds.start_date AND hds.end_date
693 AND tsum.approval_status<>'ERROR'
694 ORDER BY tsum.approval_status,tsum.start_time,tsum.resource_id;
695
696
697
698 --Cursor to fetch all the errored status Timecard.
699
700 CURSOR c_chk_err_tcs(p_data_set_id number)
701 IS
702 SELECT tsum.resource_id,
703 tsum.start_time,
704 tsum.stop_time,
705 nvl(per.employee_number,'Employee Number Unkown') employee_number,
706 nvl(per.full_name,'Full Name Unknown') full_name
707 FROM hxc_timecard_summary tsum,
708 hxc_data_sets d,
709 per_all_people_f per
710 WHERE tsum.approval_status ='ERROR'
711 AND per.person_id = tsum.resource_id
712 AND sysdate between per.effective_start_date and per.effective_end_date
713 AND tsum.stop_time BETWEEN d.start_date AND d.end_date
714 AND d.data_set_id = p_data_set_id
715 ORDER BY tsum.start_time,tsum.resource_id;
716
717
718 --Cursor to fetch all the Timecards for which notifications are pending.
719
720 cursor c_timecard_notifications(p_data_set_id number) is
721 SELECT
722 tsum.resource_id,
723 tsum.start_time,
724 tsum.stop_time,
725 nvl(per.employee_number,'Employee Number Unkown') employee_number,
726 nvl(per.full_name,'Full Name Unknown') full_name
727 FROM hxc_timecard_summary tsum,
728 hxc_app_period_summary apsum,
729 hxc_tc_ap_links tap,
730 wf_notifications wfn,
731 wf_item_activity_statuses wias,
732 wf_item_attribute_values wiav,
733 per_all_people_f per
734 WHERE tsum.approval_status = 'SUBMITTED'
735 AND per.person_id = tsum.resource_id
736 AND sysdate between per.effective_start_date and per.effective_end_date
737 AND tsum.data_set_id =p_data_set_id
738 AND apsum.application_period_id = tap.application_period_id
739 AND tsum.timecard_id = tap.timecard_id
740 AND apsum.approval_item_key is null
741 AND wias.item_key = wiav.item_key
742 AND tap.application_period_id=wiav.number_value
743 AND wias.notification_id=wfn.notification_id
744 AND wias.item_key=wiav.item_key
745 AND wfn.status='OPEN'
746 AND wias.item_type='HXCEMP'
747 AND wiav.item_type = 'HXCEMP'
748 AND wiav.name = 'APP_BB_ID'
749 AND apsum.notification_status = 'NOTIFIED'
750 AND apsum.approval_status = 'SUBMITTED'
751 AND wfn.message_name in('TIMECARD_APPROVAL_INLINE','TIMECARD_APPROVAL')
752 AND wfn.message_type='HXCEMP'
753 UNION
754 SELECT
755 tsum.resource_id,
756 tsum.start_time,
757 tsum.stop_time,
758 nvl(per.employee_number,'Employee Number Unkown') employee_number,
759 nvl(per.full_name,'Full Name Unknown') full_name
760 FROM hxc_timecard_summary tsum,
761 hxc_app_period_summary apsum,
762 hxc_tc_ap_links tap,
763 wf_notifications wfn,
764 wf_item_activity_statuses wias,
765 per_all_people_f per
766 WHERE tsum.approval_status = 'SUBMITTED'
767 AND per.person_id = tsum.resource_id
768 AND sysdate between per.effective_start_date and per.effective_end_date
769 AND tsum.data_set_id =p_data_set_id
770 AND apsum.application_period_id = tap.application_period_id
771 AND tsum.timecard_id = tap.timecard_id
772 AND apsum.approval_item_key is not null
773 AND wias.item_key = apsum.approval_item_key
774 AND wias.notification_id=wfn.notification_id
775 AND wfn.status='OPEN'
776 AND wias.item_type='HXCEMP'
777 AND apsum.notification_status = 'NOTIFIED'
778 AND apsum.approval_status = 'SUBMITTED'
779 AND wfn.message_name = 'TIMECARD_APPROVAL_INLINE'
780 AND wfn.message_type='HXCEMP'
781 ORDER BY 2,1;
782
783 CURSOR c_get_bee_retrieval
784 IS
785 SELECT retrieval_process_id
786 FROM hxc_retrieval_processes
787 WHERE name = 'BEE Retrieval Process';
788
789 l_unretrieved_tc c_get_unretrieved_tcs%rowtype;
790
791 -- Bug 6744917
792 -- Created the below Nested Table to Bulk Collect the
793 -- unretrieved timecards.
794 TYPE tab_unretrieved_tc IS TABLE OF c_get_unretrieved_tcs%ROWTYPE;
795 l_unretrieved_tctab tab_unretrieved_tc;
796
797 l_err_tc c_chk_err_tcs%rowtype;
798 l_timecard_notifications c_timecard_notifications%rowtype;
799
800 -- Bug 6744917
801 -- Created the below Nested Table to Bulk Collect the
802 -- open timecard notifications.
803 TYPE tab_timecard_notifications IS TABLE OF c_timecard_notifications%ROWTYPE;
804 l_timecard_notifications_tab tab_timecard_notifications;
805
806 l_validation_status varchar2(1);
807 l_max_errors_reached boolean;
808 l_count number:=0;
809 l_approved_count number:=0;
810 l_rejected_count number:=0;
811 l_submitted_count number:=0;
812 l_working_count number:=0;
813
814 l_bee_retrieval number:=-1;
815 BEGIN
816
817 l_max_errors_reached := FALSE;
818 l_validation_status := 'V';
819 p_error_count := 0;
820
821 --Start all the validations
822 --for each validation error we need to add that to the log file
823
824 fnd_message.set_name('HXC', 'HXC_VALIDATION_WARNINGS');
825 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
826 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
827
828 OPEN c_get_bee_retrieval;
829 FETCH c_get_bee_retrieval INTO l_bee_retrieval;
830 CLOSE c_get_bee_retrieval;
831
832
833
834 --1.Display all Non-Retrieved and all status TC except Errored Timecards.
835 OPEN c_get_unretrieved_tcs(p_data_set_id,l_bee_retrieval);
836 LOOP
837 -- Bug 6744917
838 -- Made change in fetching records to improve performance.
839 -- Instead of picking them one by one, and writing them one by one,
840 -- BULK COLLECT 100 and loop thru and write them on to the log file.
841
842 FETCH c_get_unretrieved_tcs
843 BULK COLLECT
844 INTO l_unretrieved_tctab LIMIT 100;
845 EXIT WHEN l_unretrieved_tctab.COUNT = 0 ;
846 FOR i IN l_unretrieved_tctab.FIRST..l_unretrieved_tctab.LAST
847 LOOP
848 l_unretrieved_tc := l_unretrieved_tctab(i);
849 l_validation_status := 'E';
850
851 if(l_unretrieved_tc.approval_status='APPROVED') then
852 l_approved_count:=l_approved_count+1;
853 end if;
854
855 if(l_unretrieved_tc.approval_status='REJECTED') then
856 l_rejected_count:=l_rejected_count+1;
857 end if;
858
859 if(l_unretrieved_tc.approval_status='SUBMITTED') then
860 l_submitted_count:=l_submitted_count+1;
861 end if;
862
863 if(l_unretrieved_tc.approval_status='WORKING') then
864 l_working_count :=l_working_count+1;
865 end if;
866
867
868 if(l_approved_count=1 and l_unretrieved_tc.approval_status='APPROVED') then
869
870 fnd_file.put_line(fnd_file.LOG,' ');
871 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
872 fnd_message.set_name('HXC', 'HXC_UNRETRIEVED_APPROVED_TC');
873 fnd_file.put_line(fnd_file.LOG,' '||fnd_message.get);
874 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
875 fnd_message.set_name('HXC', 'HXC_LISTING_HEADER_LINE');
876 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
877 end if;
878
879 if(l_rejected_count=1 and l_unretrieved_tc.approval_status='REJECTED') then
880
881 fnd_file.put_line(fnd_file.LOG,' ');
882 fnd_file.put_line(fnd_file.LOG,' ');
883 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
884 fnd_message.set_name('HXC', 'HXC_UNRETRIEVED_REJECTED_TC');
885 fnd_file.put_line(fnd_file.LOG,' '||fnd_message.get);
886 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
887 fnd_message.set_name('HXC', 'HXC_LISTING_HEADER_LINE');
888 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
889 end if;
890
891
892 if(l_submitted_count=1 and l_unretrieved_tc.approval_status='SUBMITTED') then
893
894 fnd_file.put_line(fnd_file.LOG,' ');
895 fnd_file.put_line(fnd_file.LOG,' ');
896 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
897 fnd_message.set_name('HXC', 'HXC_UNRETRIEVED_SUBMITTED_TC');
898 fnd_file.put_line(fnd_file.LOG,' '||fnd_message.get);
899 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
900 fnd_message.set_name('HXC', 'HXC_LISTING_HEADER_LINE');
901 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
902 end if;
903
904 if(l_working_count=1 and l_unretrieved_tc.approval_status='WORKING') then
905
906 fnd_file.put_line(fnd_file.LOG,' ');
907 fnd_file.put_line(fnd_file.LOG,' ');
908 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
909 fnd_message.set_name('HXC', 'HXC_UNRETRIEVED_WORKING_TC');
910 fnd_file.put_line(fnd_file.LOG,' '||fnd_message.get);
911 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
912 fnd_message.set_name('HXC', 'HXC_LISTING_HEADER_LINE');
913 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
914 end if;
915
916
917
918 fnd_file.put_line(fnd_file.LOG,l_unretrieved_tc.start_time||'-'||l_unretrieved_tc.stop_time||' '||l_unretrieved_tc.resource_id||' - '||l_unretrieved_tc.employee_number||' - '||l_unretrieved_tc.full_name);
919
920 p_error_count := p_error_count + 1;
921
922 IF (hxc_archive_restore_utils.check_max_errors(p_error_count) and p_all_errors=false )
923 THEN
924 l_max_errors_reached := TRUE;
925 EXIT;
926 END IF;
927 END LOOP;
928 l_unretrieved_tctab.DELETE;
929 END LOOP;
930 CLOSE c_get_unretrieved_tcs;
931
932
933 -- 2. Validation to check if there are any errored timecards in the range
934 l_count:=0;
935 IF NOT l_max_errors_reached THEN
936
937 OPEN c_chk_err_tcs(p_data_set_id);
938 LOOP
939 FETCH c_chk_err_tcs INTO l_err_tc;
940 EXIT WHEN c_chk_err_tcs%NOTFOUND;
941
942 l_validation_status := 'E';
943 l_count:=l_count+1;
944
945 if(l_count=1) then
946 fnd_file.put_line(fnd_file.LOG,' ');
947 fnd_file.put_line(fnd_file.LOG,' ');
948 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
949 fnd_message.set_name('HXC', 'HXC_LIST_ERROR_TC');
950 fnd_file.put_line(fnd_file.LOG,' '||fnd_message.get);
951 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
952 fnd_message.set_name('HXC', 'HXC_LISTING_HEADER_LINE');
953 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
954 end if;
955
956
957 fnd_file.put_line(fnd_file.LOG,l_err_tc.start_time||'-'||l_err_tc.stop_time||' '||l_err_tc.resource_id||' - '||l_err_tc.employee_number||' - '||l_err_tc.full_name);
958
959 p_error_count := p_error_count + 1;
960
961 IF (hxc_archive_restore_utils.check_max_errors(p_error_count) and p_all_errors=false )
962 THEN
963 l_max_errors_reached := TRUE;
964 exit;
965 END IF;
966
967 END LOOP;
968 CLOSE c_chk_err_tcs;
969 END IF;
970
971
972 -- 3. Validation to check whether there are any un-notified timecards
973 l_count:=0;
974 IF NOT l_max_errors_reached THEN
975
976 OPEN c_timecard_notifications(p_data_set_id);
977 LOOP
978
979 -- Bug 6744917
980 -- Made change in fetching records to improve performance.
981 -- Instead of picking them one by one, and writing them one by one,
982 -- BULK COLLECT 100 and loop thru and write them on to the log file.
983
984 FETCH c_timecard_notifications
985 BULK COLLECT
986 INTO l_timecard_notifications_tab LIMIT 100;
987 EXIT WHEN l_timecard_notifications_tab.COUNT = 0 ;
988
989 FOR i IN l_timecard_notifications_tab.FIRST..l_timecard_notifications_tab.LAST
990 LOOP
991 l_timecard_notifications := l_timecard_notifications_tab(i);
992 l_validation_status := 'E';
993 l_count:=l_count+1;
994
995 if(l_count=1)
996 then
997 fnd_file.put_line(fnd_file.LOG,' ');
998 fnd_file.put_line(fnd_file.LOG,' ');
999 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
1000 fnd_message.set_name('HXC', 'HXC_LIST_NOT_NOTIFIED_TC');
1001 fnd_file.put_line(fnd_file.LOG,' '||fnd_message.get);
1002 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
1003 fnd_message.set_name('HXC', 'HXC_LISTING_HEADER_LINE');
1004 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
1005 end if;
1006
1007 fnd_file.put_line(fnd_file.LOG,
1008 l_timecard_notifications.start_time||'-'||l_timecard_notifications.stop_time||' '||l_timecard_notifications.resource_id||' - '||l_timecard_notifications.employee_number||' - '||l_timecard_notifications.full_name);
1009 p_error_count := p_error_count + 1;
1010
1011 IF (hxc_archive_restore_utils.check_max_errors(p_error_count) and p_all_errors=false)
1012 THEN
1013 l_max_errors_reached := true;
1014 exit;
1015 END IF;
1016 END LOOP;
1017 l_timecard_notifications_tab.DELETE;
1018 END LOOP;
1019 CLOSE c_timecard_notifications;
1020 END If;
1021
1022
1023 --finally set the validation status
1024 UPDATE hxc_data_sets
1025 SET validation_status = l_validation_status
1026 WHERE data_set_id = p_data_set_id;
1027
1028
1029 END validate_data_set;
1030
1031 ----------------------------------------------------------------------------
1032 -- Procedure Name : lock_data_set
1033 -- Description :
1034 ----------------------------------------------------------------------------
1035 PROCEDURE lock_data_set (p_data_set_id in number,
1036 p_start_date in date,
1037 p_stop_date in date,
1038 p_data_set_lock out NOCOPY BOOLEAN)
1039 IS
1040
1041 CURSOR c_lock_resource is
1042 SELECT distinct resource_id
1043 FROM hxc_time_building_blocks
1044 WHERE data_set_id = p_data_set_id
1045 AND scope = 'TIMECARD';
1046
1047 l_row_lock_id ROWID;
1048 l_messages HXC_MESSAGE_TABLE_TYPE;
1049 l_lock_success BOOLEAN;
1050 l_released_success BOOLEAN;
1051
1052 BEGIN
1053
1054 p_data_set_lock := FALSE;
1055
1056 FOR crs_lock_resource IN c_lock_resource LOOP
1057
1058 l_row_lock_id := NULL;
1059 l_lock_success := FALSE;
1060
1061 hxc_lock_api.request_lock (
1062 p_process_locker_type => hxc_lock_util.c_plsql_ar_action,
1063 p_resource_id => crs_lock_resource.resource_id,
1064 p_start_time => p_start_date,
1065 p_stop_time => p_stop_date,
1066 p_time_building_block_id => NULL,
1067 p_time_building_block_ovn => NULL,
1068 p_transaction_lock_id => p_data_set_id,
1069 p_expiration_time => 60,
1070 p_messages => l_messages,
1071 p_row_lock_id => l_row_lock_id,
1072 p_locked_success => l_lock_success
1073 );
1074
1075 --fnd_file.put_line(fnd_file.LOG,l_messages(l_messages.first).message_name);
1076
1077 IF not(l_lock_success) THEN
1078
1079 -- before returning let's unlock all the timecards that were locked
1080 hxc_lock_api.release_lock (
1081 p_row_lock_id => null,
1082 p_process_locker_type => hxc_lock_util.c_plsql_ar_action,
1083 p_transaction_lock_id => p_data_set_id,
1084 p_released_success => l_released_success
1085 );
1086
1087 fnd_file.put_line(fnd_file.LOG,'========== PROCESS STOPPED ================' );
1088 fnd_file.put_line(fnd_file.LOG,'========== TIMECARD LOCKED ================' );
1089 fnd_file.put_line(fnd_file.LOG,'==> The resource id '||crs_lock_resource.resource_id||' has timecards locked for ');
1090 fnd_file.put_line(fnd_file.LOG,'==> the period from '||p_start_date||' to '||p_stop_date);
1091
1092 RETURN;
1093
1094 END IF;
1095
1096 END LOOP;
1097
1098 p_data_set_lock := TRUE;
1099
1100 END lock_data_set;
1101
1102 ----------------------------------------------------------------------------
1103 -- Procedure Name : release_lock_data_set
1104 -- Description :
1105 ----------------------------------------------------------------------------
1106
1107 PROCEDURE release_lock_data_set(p_data_set_id in number)
1108 IS
1109
1110 l_released_success BOOLEAN;
1111
1112 BEGIN
1113
1114 hxc_lock_api.release_lock (
1115 p_row_lock_id => null,
1116 p_process_locker_type => hxc_lock_util.c_plsql_ar_action,
1117 p_transaction_lock_id => p_data_set_id,
1118 p_released_success => l_released_success
1119 );
1120
1121 END release_lock_data_set;
1122
1123 END hxc_data_set;
[Home] [Help]