1 PACKAGE BODY hxc_data_set AS
2 /* $Header: hxcdataset.pkb 120.4.12010000.4 2008/09/26 10:54:27 asrajago 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
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,
548 hxc_application_sets_v has,
549 hxc_application_set_comps_v hasv,
550 per_all_people_f per
551 WHERE
552 NOT EXISTS
553 (SELECT 1
554 FROM hxc_transaction_details txnd,
555 hxc_transactions txn,
556 hxc_retrieval_processes rp
557 WHERE txn.type = 'RETRIEVAL'
558 AND txn.status = 'SUCCESS'
559 AND txnd.status = 'SUCCESS'
560 AND txnd.time_building_block_id = hld.time_building_block_id
561 AND txnd.time_building_block_ovn = hld.object_version_number
562 AND txnd.transaction_id = txn.transaction_id
563 AND decode(txn.transaction_process_id,-1,l_bee_retrieval,txn.transaction_process_id) = rp.retrieval_process_id
564 AND rp.time_recipient_id = hasv.time_recipient_id
565 )
566 AND per.person_id = tsum.resource_id
567 AND sysdate between per.effective_start_date and per.effective_end_date
568 AND hds.data_set_id =p_data_set_id
569 AND has.application_set_id = hasv.application_set_id
570 AND has.application_set_id = hld.application_set_id
571 AND hld.time_building_block_id = det.time_building_block_id
572 AND hld.object_version_number = det.object_version_number
573 AND det.parent_building_block_id = day.time_building_block_id
574 AND det.parent_building_block_ovn = day.object_version_number
575 AND ( ( det.date_to = hr_general.end_of_time
576 )
577 OR ( det.date_to <> hr_general.end_of_time
578 AND EXISTS (SELECT 1
579 FROM hxc_transaction_details txnd1,
580 hxc_transactions txn1,
581 hxc_retrieval_processes rp1
582 WHERE txn1.type = 'RETRIEVAL'
583 AND txn1.status = 'SUCCESS'
584 AND txnd1.status = 'SUCCESS'
585 AND txnd1.time_building_block_id = hld.time_building_block_id
586 AND txnd1.time_building_block_ovn < hld.object_version_number
587 AND txnd1.transaction_id = txn1.transaction_id
588 AND decode(txn1.transaction_process_id,-1,
589 l_bee_retrieval,
590 txn1.transaction_process_id) = rp1.retrieval_process_id
591 AND rp1.time_recipient_id = hasv.time_recipient_id
592 )
593 )
594 )
595 AND day.parent_building_block_id = tsum.timecard_id
596 AND day.parent_building_block_ovn = tsum.timecard_ovn
597 --AND det.data_set_id = hds.data_set_id
598 --AND day.data_set_id = det.data_set_id
599 --AND tsum.data_set_id = day.data_set_id
600 AND det.scope = 'DETAIL'
601 AND day.scope = 'DAY'
602 AND tsum.stop_time BETWEEN hds.start_date AND hds.end_date
603 AND tsum.approval_status<>'ERROR'
604 ORDER BY tsum.approval_status,tsum.start_time,tsum.resource_id;
605
606
607
608
609 --Cursor to fetch all the errored status Timecard.
610
611 CURSOR c_chk_err_tcs(p_data_set_id number)
612 IS
613 SELECT tsum.resource_id,
614 tsum.start_time,
615 tsum.stop_time,
616 nvl(per.employee_number,'Employee Number Unkown') employee_number,
617 nvl(per.full_name,'Full Name Unknown') full_name
618 FROM hxc_timecard_summary tsum,
619 hxc_data_sets d,
620 per_all_people_f per
621 WHERE tsum.approval_status ='ERROR'
622 AND per.person_id = tsum.resource_id
623 AND sysdate between per.effective_start_date and per.effective_end_date
624 AND tsum.stop_time BETWEEN d.start_date AND d.end_date
625 AND d.data_set_id = p_data_set_id
626 ORDER BY tsum.start_time,tsum.resource_id;
627
628
629 --Cursor to fetch all the Timecards for which notifications are pending.
630
631 cursor c_timecard_notifications(p_data_set_id number) is
632 SELECT
633 tsum.resource_id,
634 tsum.start_time,
635 tsum.stop_time,
636 nvl(per.employee_number,'Employee Number Unkown') employee_number,
637 nvl(per.full_name,'Full Name Unknown') full_name
638 FROM hxc_timecard_summary tsum,
639 hxc_app_period_summary apsum,
640 hxc_tc_ap_links tap,
641 wf_notifications wfn,
642 wf_item_activity_statuses wias,
643 wf_item_attribute_values wiav,
644 per_all_people_f per
645 WHERE tsum.approval_status = 'SUBMITTED'
646 AND per.person_id = tsum.resource_id
647 AND sysdate between per.effective_start_date and per.effective_end_date
648 AND tsum.data_set_id =p_data_set_id
649 AND apsum.application_period_id = tap.application_period_id
650 AND tsum.timecard_id = tap.timecard_id
651 AND apsum.approval_item_key is null
652 AND wias.item_key = wiav.item_key
653 AND tap.application_period_id=wiav.number_value
654 AND wias.notification_id=wfn.notification_id
655 AND wias.item_key=wiav.item_key
656 AND wfn.status='OPEN'
657 AND wias.item_type='HXCEMP'
658 AND wiav.item_type = 'HXCEMP'
659 AND wiav.name = 'APP_BB_ID'
660 AND apsum.notification_status = 'NOTIFIED'
661 AND apsum.approval_status = 'SUBMITTED'
662 AND wfn.message_name in('TIMECARD_APPROVAL_INLINE','TIMECARD_APPROVAL')
663 AND wfn.message_type='HXCEMP'
664 UNION
665 SELECT
666 tsum.resource_id,
667 tsum.start_time,
668 tsum.stop_time,
669 nvl(per.employee_number,'Employee Number Unkown') employee_number,
670 nvl(per.full_name,'Full Name Unknown') full_name
671 FROM hxc_timecard_summary tsum,
672 hxc_app_period_summary apsum,
673 hxc_tc_ap_links tap,
674 wf_notifications wfn,
675 wf_item_activity_statuses wias,
676 per_all_people_f per
677 WHERE tsum.approval_status = 'SUBMITTED'
678 AND per.person_id = tsum.resource_id
679 AND sysdate between per.effective_start_date and per.effective_end_date
680 AND tsum.data_set_id =p_data_set_id
681 AND apsum.application_period_id = tap.application_period_id
682 AND tsum.timecard_id = tap.timecard_id
683 AND apsum.approval_item_key is not null
684 AND wias.item_key = apsum.approval_item_key
685 AND wias.notification_id=wfn.notification_id
686 AND wfn.status='OPEN'
687 AND wias.item_type='HXCEMP'
688 AND apsum.notification_status = 'NOTIFIED'
689 AND apsum.approval_status = 'SUBMITTED'
690 AND wfn.message_name = 'TIMECARD_APPROVAL_INLINE'
691 AND wfn.message_type='HXCEMP'
692 ORDER BY 2,1;
693
694 CURSOR c_get_bee_retrieval
695 IS
696 SELECT retrieval_process_id
697 FROM hxc_retrieval_processes
698 WHERE name = 'BEE Retrieval Process';
699
700 l_unretrieved_tc c_get_unretrieved_tcs%rowtype;
701
702 -- Bug 6744917
703 -- Created the below Nested Table to Bulk Collect the
704 -- unretrieved timecards.
705 TYPE tab_unretrieved_tc IS TABLE OF c_get_unretrieved_tcs%ROWTYPE;
706 l_unretrieved_tctab tab_unretrieved_tc;
707
708 l_err_tc c_chk_err_tcs%rowtype;
709 l_timecard_notifications c_timecard_notifications%rowtype;
710
711 -- Bug 6744917
712 -- Created the below Nested Table to Bulk Collect the
713 -- open timecard notifications.
714 TYPE tab_timecard_notifications IS TABLE OF c_timecard_notifications%ROWTYPE;
715 l_timecard_notifications_tab tab_timecard_notifications;
716
717 l_validation_status varchar2(1);
718 l_max_errors_reached boolean;
719 l_count number:=0;
720 l_approved_count number:=0;
721 l_rejected_count number:=0;
722 l_submitted_count number:=0;
723 l_working_count number:=0;
724
725 l_bee_retrieval number:=-1;
726 BEGIN
727
728 l_max_errors_reached := FALSE;
729 l_validation_status := 'V';
730 p_error_count := 0;
731
732 --Start all the validations
733 --for each validation error we need to add that to the log file
734
735 fnd_message.set_name('HXC', 'HXC_VALIDATION_WARNINGS');
736 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
737 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
738
739 OPEN c_get_bee_retrieval;
740 FETCH c_get_bee_retrieval INTO l_bee_retrieval;
741 CLOSE c_get_bee_retrieval;
742
743
744
745 --1.Display all Non-Retrieved and all status TC except Errored Timecards.
746 OPEN c_get_unretrieved_tcs(p_data_set_id,l_bee_retrieval);
747 LOOP
748 -- Bug 6744917
749 -- Made change in fetching records to improve performance.
750 -- Instead of picking them one by one, and writing them one by one,
751 -- BULK COLLECT 100 and loop thru and write them on to the log file.
752
753 FETCH c_get_unretrieved_tcs
754 BULK COLLECT
755 INTO l_unretrieved_tctab LIMIT 100;
756 EXIT WHEN l_unretrieved_tctab.COUNT = 0 ;
757 FOR i IN l_unretrieved_tctab.FIRST..l_unretrieved_tctab.LAST
758 LOOP
759 l_unretrieved_tc := l_unretrieved_tctab(i);
760 l_validation_status := 'E';
761
762 if(l_unretrieved_tc.approval_status='APPROVED') then
763 l_approved_count:=l_approved_count+1;
764 end if;
765
766 if(l_unretrieved_tc.approval_status='REJECTED') then
767 l_rejected_count:=l_rejected_count+1;
768 end if;
769
770 if(l_unretrieved_tc.approval_status='SUBMITTED') then
771 l_submitted_count:=l_submitted_count+1;
772 end if;
773
774 if(l_unretrieved_tc.approval_status='WORKING') then
775 l_working_count :=l_working_count+1;
776 end if;
777
778
779 if(l_approved_count=1 and l_unretrieved_tc.approval_status='APPROVED') then
780
781 fnd_file.put_line(fnd_file.LOG,' ');
782 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
783 fnd_message.set_name('HXC', 'HXC_UNRETRIEVED_APPROVED_TC');
784 fnd_file.put_line(fnd_file.LOG,' '||fnd_message.get);
785 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
786 fnd_message.set_name('HXC', 'HXC_LISTING_HEADER_LINE');
787 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
788 end if;
789
790 if(l_rejected_count=1 and l_unretrieved_tc.approval_status='REJECTED') then
791
792 fnd_file.put_line(fnd_file.LOG,' ');
793 fnd_file.put_line(fnd_file.LOG,' ');
794 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
795 fnd_message.set_name('HXC', 'HXC_UNRETRIEVED_REJECTED_TC');
796 fnd_file.put_line(fnd_file.LOG,' '||fnd_message.get);
797 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
798 fnd_message.set_name('HXC', 'HXC_LISTING_HEADER_LINE');
799 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
800 end if;
801
802
803 if(l_submitted_count=1 and l_unretrieved_tc.approval_status='SUBMITTED') then
804
805 fnd_file.put_line(fnd_file.LOG,' ');
806 fnd_file.put_line(fnd_file.LOG,' ');
807 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
808 fnd_message.set_name('HXC', 'HXC_UNRETRIEVED_SUBMITTED_TC');
809 fnd_file.put_line(fnd_file.LOG,' '||fnd_message.get);
810 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
811 fnd_message.set_name('HXC', 'HXC_LISTING_HEADER_LINE');
812 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
813 end if;
814
815 if(l_working_count=1 and l_unretrieved_tc.approval_status='WORKING') then
816
817 fnd_file.put_line(fnd_file.LOG,' ');
818 fnd_file.put_line(fnd_file.LOG,' ');
819 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
820 fnd_message.set_name('HXC', 'HXC_UNRETRIEVED_WORKING_TC');
821 fnd_file.put_line(fnd_file.LOG,' '||fnd_message.get);
822 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
823 fnd_message.set_name('HXC', 'HXC_LISTING_HEADER_LINE');
824 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
825 end if;
826
827
828
829 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);
830
831 p_error_count := p_error_count + 1;
832
833 IF (hxc_archive_restore_utils.check_max_errors(p_error_count) and p_all_errors=false )
834 THEN
835 l_max_errors_reached := TRUE;
836 EXIT;
837 END IF;
838 END LOOP;
839 l_unretrieved_tctab.DELETE;
840 END LOOP;
841 CLOSE c_get_unretrieved_tcs;
842
843
844 -- 2. Validation to check if there are any errored timecards in the range
845 l_count:=0;
846 IF NOT l_max_errors_reached THEN
847
848 OPEN c_chk_err_tcs(p_data_set_id);
849 LOOP
850 FETCH c_chk_err_tcs INTO l_err_tc;
851 EXIT WHEN c_chk_err_tcs%NOTFOUND;
852
853 l_validation_status := 'E';
854 l_count:=l_count+1;
855
856 if(l_count=1) then
857 fnd_file.put_line(fnd_file.LOG,' ');
858 fnd_file.put_line(fnd_file.LOG,' ');
859 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
860 fnd_message.set_name('HXC', 'HXC_LIST_ERROR_TC');
861 fnd_file.put_line(fnd_file.LOG,' '||fnd_message.get);
862 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
863 fnd_message.set_name('HXC', 'HXC_LISTING_HEADER_LINE');
864 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
865 end if;
866
867
868 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);
869
870 p_error_count := p_error_count + 1;
871
872 IF (hxc_archive_restore_utils.check_max_errors(p_error_count) and p_all_errors=false )
873 THEN
874 l_max_errors_reached := TRUE;
875 exit;
876 END IF;
877
878 END LOOP;
879 CLOSE c_chk_err_tcs;
880 END IF;
881
882
883 -- 3. Validation to check whether there are any un-notified timecards
884 l_count:=0;
885 IF NOT l_max_errors_reached THEN
886
887 OPEN c_timecard_notifications(p_data_set_id);
888 LOOP
889
890 -- Bug 6744917
891 -- Made change in fetching records to improve performance.
892 -- Instead of picking them one by one, and writing them one by one,
893 -- BULK COLLECT 100 and loop thru and write them on to the log file.
894
895 FETCH c_timecard_notifications
896 BULK COLLECT
897 INTO l_timecard_notifications_tab LIMIT 100;
898 EXIT WHEN l_timecard_notifications_tab.COUNT = 0 ;
899
900 FOR i IN l_timecard_notifications_tab.FIRST..l_timecard_notifications_tab.LAST
901 LOOP
902 l_timecard_notifications := l_timecard_notifications_tab(i);
903 l_validation_status := 'E';
904 l_count:=l_count+1;
905
906 if(l_count=1)
907 then
908 fnd_file.put_line(fnd_file.LOG,' ');
909 fnd_file.put_line(fnd_file.LOG,' ');
910 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
911 fnd_message.set_name('HXC', 'HXC_LIST_NOT_NOTIFIED_TC');
912 fnd_file.put_line(fnd_file.LOG,' '||fnd_message.get);
913 fnd_file.put_line(fnd_file.LOG,'-------------------------------------------------------------- ');
914 fnd_message.set_name('HXC', 'HXC_LISTING_HEADER_LINE');
915 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
916 end if;
917
918 fnd_file.put_line(fnd_file.LOG,
919 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);
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_timecard_notifications_tab.DELETE;
929 END LOOP;
930 CLOSE c_timecard_notifications;
931 END If;
932
933
934 --finally set the validation status
935 UPDATE hxc_data_sets
936 SET validation_status = l_validation_status
937 WHERE data_set_id = p_data_set_id;
938
939
940 END validate_data_set;
941
942 ----------------------------------------------------------------------------
943 -- Procedure Name : lock_data_set
944 -- Description :
945 ----------------------------------------------------------------------------
946 PROCEDURE lock_data_set (p_data_set_id in number,
947 p_start_date in date,
948 p_stop_date in date,
949 p_data_set_lock out NOCOPY BOOLEAN)
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;
959 l_messages HXC_MESSAGE_TABLE_TYPE;
960 l_lock_success BOOLEAN;
961 l_released_success BOOLEAN;
962
963 BEGIN
964
965 p_data_set_lock := FALSE;
966
967 FOR crs_lock_resource IN c_lock_resource LOOP
968
969 l_row_lock_id := NULL;
970 l_lock_success := FALSE;
971
972 hxc_lock_api.request_lock (
973 p_process_locker_type => hxc_lock_util.c_plsql_ar_action,
974 p_resource_id => crs_lock_resource.resource_id,
975 p_start_time => p_start_date,
976 p_stop_time => p_stop_date,
977 p_time_building_block_id => NULL,
978 p_time_building_block_ovn => NULL,
979 p_transaction_lock_id => p_data_set_id,
980 p_expiration_time => 60,
981 p_messages => l_messages,
982 p_row_lock_id => l_row_lock_id,
983 p_locked_success => l_lock_success
984 );
985
986 --fnd_file.put_line(fnd_file.LOG,l_messages(l_messages.first).message_name);
987
988 IF not(l_lock_success) THEN
989
990 -- before returning let's unlock all the timecards that were locked
991 hxc_lock_api.release_lock (
992 p_row_lock_id => null,
993 p_process_locker_type => hxc_lock_util.c_plsql_ar_action,
994 p_transaction_lock_id => p_data_set_id,
995 p_released_success => l_released_success
996 );
997
998 fnd_file.put_line(fnd_file.LOG,'========== PROCESS STOPPED ================' );
999 fnd_file.put_line(fnd_file.LOG,'========== TIMECARD LOCKED ================' );
1000 fnd_file.put_line(fnd_file.LOG,'==> The resource id '||crs_lock_resource.resource_id||' has timecards locked for ');
1001 fnd_file.put_line(fnd_file.LOG,'==> the period from '||p_start_date||' to '||p_stop_date);
1002
1003 RETURN;
1004
1005 END IF;
1006
1007 END LOOP;
1008
1009 p_data_set_lock := TRUE;
1010
1011 END lock_data_set;
1012
1013 ----------------------------------------------------------------------------
1014 -- Procedure Name : release_lock_data_set
1015 -- Description :
1016 ----------------------------------------------------------------------------
1017
1018 PROCEDURE release_lock_data_set(p_data_set_id in number)
1019 IS
1020
1021 l_released_success BOOLEAN;
1022
1023 BEGIN
1024
1025 hxc_lock_api.release_lock (
1026 p_row_lock_id => null,
1027 p_process_locker_type => hxc_lock_util.c_plsql_ar_action,
1028 p_transaction_lock_id => p_data_set_id,
1029 p_released_success => l_released_success
1030 );
1031
1032 END release_lock_data_set;
1033
1034 END hxc_data_set;
[Home] [Help]