[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMECARD_AUDIT
Source
1 PACKAGE BODY hxc_timecard_audit AS
2 /* $Header: hxctcaudit.pkb 120.18.12020000.4 2012/10/17 07:30:17 bbayragi ship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 Function find_overall_status
7 (p_transaction_info in hxc_timecard.transaction_info
8 ) return varchar2 is
9
10 l_index NUMBER;
11 l_status varchar2(20);
12
13 Begin
14
15 l_index := p_transaction_info.first;
16
17 LOOP
18 EXIT WHEN
19 (
20 (NOT p_transaction_info.exists(l_index))
21 OR
22 (l_status <> hxc_timecard.c_trans_success)
23 );
24
25 l_status := p_transaction_info(l_index).status;
26
27 l_index := p_transaction_info.next(l_index);
28
29 END LOOP;
30
31 return l_status;
32
33 End find_overall_status;
34
35 Procedure insert_audit_header
36 (p_overall_status in varchar2
37 ,p_transaction_info in hxc_timecard.transaction_info
38 ,p_messages in out nocopy hxc_message_table_type
39 ,p_transaction_id out nocopy hxc_transactions.transaction_id%type
40 ) is
41
42 cursor c_transaction_sequence is
43 select hxc_transactions_s.nextval from dual;
44
45 l_transaction_id hxc_transactions.transaction_id%TYPE;
46 l_deposit_process_id hxc_deposit_processes.deposit_process_id%type;
47 l_data_set_id hxc_transactions.data_set_id%TYPE;
48 l_index BINARY_INTEGER;
49
50 Begin
51
52 select deposit_process_id
53 into l_deposit_process_id
54 from hxc_deposit_processes
55 where name = 'OTL Deposit Process';
56
57 open c_transaction_sequence;
58 fetch c_transaction_sequence into l_transaction_id;
59 close c_transaction_sequence;
60
61 l_index := p_transaction_info.first;
62 if l_index is not null then
63 l_data_set_id := p_transaction_info(l_index).data_set_id;
64 end if;
65
66
67 insert into hxc_dep_transactions
68 (transaction_id
69 ,transaction_date
70 ,type
71 ,transaction_process_id
72 ,created_by
73 ,creation_date
74 ,last_updated_by
75 ,last_update_date
76 ,last_update_login
77 ,status
78 ,data_set_id
79 ) values
80 (l_transaction_id
81 ,sysdate
82 ,'DEPOSIT'
83 ,l_deposit_process_id
84 ,null
85 ,sysdate
86 ,null
87 ,sysdate
88 ,null
89 ,p_overall_status
90 ,l_data_set_id
91 );
92
93 p_transaction_id := l_transaction_id;
94
95 End insert_audit_header;
96
97 Procedure insert_audit_details
98 (p_transaction_info in out nocopy hxc_timecard.transaction_info
99 ,p_messages in out nocopy hxc_message_table_type
100 ,p_transaction_id in hxc_transactions.transaction_id%type
101 ) is
102
103 l_index NUMBER;
104
105 cursor c_transaction_detail_sequence is
106 select hxc_transaction_details_s.nextval from dual;
107
108 l_transaction_detail_id hxc_transaction_details.transaction_detail_id%TYPE;
109
110 Begin
111
112 l_index := p_transaction_info.first;
113
114 Loop
115 EXIT WHEN NOT p_transaction_info.exists(l_index);
116
117 open c_transaction_detail_sequence;
118 fetch c_transaction_detail_sequence into l_transaction_detail_id;
119 close c_transaction_detail_sequence;
120
121 insert into hxc_dep_transaction_details
122 (transaction_detail_id
123 ,time_building_block_id
124 ,transaction_id
125 ,created_by
126 ,creation_date
127 ,last_updated_by
128 ,last_update_date
129 ,last_update_login
130 ,time_building_block_ovn
131 ,status
132 ,exception_description
133 ,data_set_id
134 ) values
135 (l_transaction_detail_id
136 ,p_transaction_info(l_index).time_building_block_id
137 ,p_transaction_id
138 ,null
139 ,sysdate
140 ,null
141 ,sysdate
142 ,null
143 ,p_transaction_info(l_index).object_version_number
144 ,p_transaction_info(l_index).status
145 ,p_transaction_info(l_index).exception_desc
146 ,p_transaction_info(l_index).data_set_id
147 );
148
149 p_transaction_info(l_index).transaction_detail_id := l_transaction_detail_id;
150
151 l_index := p_transaction_info.next(l_index);
152
153 End Loop;
154
155 End insert_audit_details;
156
157 Procedure audit_deposit
158 (p_transaction_info in out nocopy hxc_timecard.transaction_info
159 ,p_messages in out nocopy hxc_message_table_type
160 ) is
161
162 PRAGMA AUTONOMOUS_TRANSACTION;
163
164 l_transaction hxc_transactions.transaction_id%type;
165
166 Begin
167
168 insert_audit_header
169 (find_overall_status(p_transaction_info)
170 ,p_transaction_info
171 ,p_messages
172 ,l_transaction
173 );
174
175 insert_audit_details
176 (p_transaction_info
177 ,p_messages
178 ,l_transaction
179 );
180
181 commit;
182
183 End audit_deposit;
184
185 Procedure maintain_latest_details
186 (p_blocks in hxc_block_table_type
187 ) IS
188
189 l_timecard_blocks hxc_timecard.block_list;
190 l_day_blocks hxc_timecard.block_list;
191 l_detail_blocks hxc_timecard.block_list;
192
193 l_detail_ind PLS_INTEGER;
194
195 l_blk_ind PLS_INTEGER;
196
197 l_status hxc_time_building_blocks.approval_status%TYPE;
198
199 l_org_id NUMBER;
200 l_bg_id NUMBER;
201
202 -- Bug 8888801
203 -- Added the following datatypes and variables for
204 -- recording Apps specific details.
205
206 l_timecard_id hxc_time_building_blocks.time_building_block_id%type;
207
208 TYPE NUMTAB IS TABLE OF NUMBER;
209 TYPE VARCHARTAB IS TABLE OF VARCHAR2(2500);
210 TYPE DATETAB IS TABLE OF DATE;
211
212 -- Bug 9684373
213 TYPE NUMBERTAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
214
215 l_app_sets NUMBERTAB;
216 l_day_scope NUMBERTAB;
217
218
219 pa_resource_id_tab NUMTAB;
220 pa_time_building_block_id_tab NUMTAB;
221 pa_object_version_number_tab NUMTAB;
222 pa_application_set_id_tab NUMTAB;
223 pa_org_id_tab NUMTAB;
224 pa_business_group_id_tab NUMTAB;
225 pa_timecard_id_tab NUMTAB;
226 pa_start_time_tab DATETAB;
227 pa_stop_time_tab DATETAB;
228 pa_last_update_date_tab DATETAB;
229 pa_approval_status_tab VARCHARTAB;
230 pa_comment_text_tab VARCHARTAB;
231 pa_resource_type_tab VARCHARTAB;
232
233 pay_resource_id_tab NUMTAB;
234 pay_time_building_block_id_tab NUMTAB;
235 pay_object_version_number_tab NUMTAB;
236 pay_application_set_id_tab NUMTAB;
237 pay_org_id_tab NUMTAB;
238 pay_business_group_id_tab NUMTAB;
239 pay_timecard_id_tab NUMTAB;
240 pay_start_time_tab DATETAB;
241 pay_stop_time_tab DATETAB;
242 pay_last_update_date_tab DATETAB;
243 pay_approval_status_tab VARCHARTAB;
244 pay_comment_text_tab VARCHARTAB;
245 pay_resource_type_tab VARCHARTAB;
246
247 l_pay_index NUMBER := 0;
248 l_pa_index NUMBER := 0;
249
250 -- Bug 9747820
251 -- New datatype for TC id and app set combo.
252 l_app_tc VARCHARTAB;
253 l_app_index BINARY_INTEGER;
254
255 -- Bug 8888801
256 -- Define a new exception type.
257 TABLE_EXCEPTION EXCEPTION;
258 PRAGMA EXCEPTION_INIT(TABLE_EXCEPTION,-24381);
259
260
261 -- Bug 8888801
262 -- Private Procedure added to extend the tables
263 -- used for insert/update.
264 PROCEDURE extend_tables(p_app IN VARCHAR2)
265 IS
266
267 BEGIN
268
269 IF p_app = 'PAY'
270 THEN
271 pay_resource_id_tab.EXTEND(1);
272 pay_time_building_block_id_tab.EXTEND(1);
273 pay_object_version_number_tab.EXTEND(1);
274 pay_application_set_id_tab.EXTEND(1);
275 pay_org_id_tab.EXTEND(1);
276 pay_business_group_id_tab.EXTEND(1);
277 pay_timecard_id_tab.EXTEND(1);
278 pay_start_time_tab.EXTEND(1);
279 pay_stop_time_tab.EXTEND(1);
280 pay_last_update_date_tab.EXTEND(1);
281 pay_approval_status_tab.EXTEND(1);
282 pay_comment_text_tab.EXTEND(1);
283 pay_resource_type_tab.EXTEND(1);
284 ELSIF p_app = 'PA'
285 THEN
286 pa_resource_id_tab.EXTEND(1);
287 pa_time_building_block_id_tab.EXTEND(1);
288 pa_object_version_number_tab.EXTEND(1);
289 pa_application_set_id_tab.EXTEND(1);
290 pa_org_id_tab.EXTEND(1);
291 pa_business_group_id_tab.EXTEND(1);
292 pa_timecard_id_tab.EXTEND(1);
293 pa_start_time_tab.EXTEND(1);
294 pa_stop_time_tab.EXTEND(1);
295 pa_last_update_date_tab.EXTEND(1);
296 pa_approval_status_tab.EXTEND(1);
297 pa_comment_text_tab.EXTEND(1);
298 pa_resource_type_tab.EXTEND(1);
299 END IF;
300
301 END extend_tables;
302
303 -- Bug 9684373
304 -- This procedure picks up the DAY scope blocks and
305 -- stores their parent_id -> timecard_id
306 PROCEDURE pick_up_day_scope(p_blocks IN hxc_block_table_type)
307 IS
308
309 l_index BINARY_INTEGER;
310
311 BEGIN
312 l_index := p_blocks.FIRST;
313 LOOP
314 IF p_blocks(l_index).SCOPE = 'DAY'
315 THEN
316 l_day_scope(p_blocks(l_index).time_building_block_id) :=
317 p_blocks(l_index).parent_building_block_id;
318 END IF;
319 l_index := p_blocks.NEXT(l_index);
320 EXIT WHEN NOT p_blocks.EXISTS(l_index);
321 END LOOP;
322 END pick_up_day_scope;
323
324 -- Bug 9747820
325 -- Added this procedure to work out transferred to column.
326
327 PROCEDURE update_transferred_to(p_timecard_id IN NUMBER,
328 p_application_set IN NUMBER )
329 IS
330
331 CURSOR get_row
332 IS
333 SELECT transferred_to,
334 ROWIDTOCHAR(sum.ROWID)
335 FROM hxc_timecard_summary sum
336 WHERE timecard_id = p_timecard_id;
337
338 CURSOR get_application
339 IS SELECT time_recipient_name
340 FROM hxc_application_set_comps_v
341 WHERE application_set_id = p_application_set;
342
343 l_transferred_to VARCHAR2(250);
344 l_rowid VARCHAR2(50);
345 l_recipient VARCHAR2(50);
346
347 BEGIN
348
349 IF g_debug
350 THEN
351 hr_utility.trace('p_timecard_id = '||p_timecard_id);
352 hr_utility.trace('p_application_set = '||p_application_set);
353 END IF;
354
355 IF p_application_set IS NULL
356 THEN
357 RETURN;
358 END IF;
359
360 OPEN get_row;
361 FETCH get_row INTO l_transferred_to,
362 l_rowid;
363 CLOSE get_row;
364
365 IF g_debug
366 THEN
367 hr_utility.trace('l_transferred_to = '||l_transferred_to);
368 hr_utility.trace('l_rowid = '||l_rowid);
369 END IF;
370
371
372 IF l_rowid IS NULL
373 THEN
374 RETURN;
375 END IF;
376
377 -- Pick up Time recipients.
378 OPEN get_application;
379 LOOP
380 FETCH get_application INTO l_recipient;
381 EXIT WHEN get_application%NOTFOUND;
382
383 l_transferred_to := RTRIM(LTRIM(REPLACE(l_transferred_to,l_recipient),','),',');
384 END LOOP;
385
386 IF g_debug
387 THEN
388 hr_utility.trace('l_transferred_to = '||l_transferred_to);
389 END IF;
390
391 -- Update the table.
392 UPDATE hxc_timecard_summary
393 SET transferred_to = l_transferred_to
394 WHERE ROWID = CHARTOROWID(l_rowid);
395
396 END update_transferred_to;
397
398
399
400
401 BEGIN
402
403 g_debug := hr_utility.debug_enabled;
404
405 -- Bug 8888801
406 -- Initializing the tables for Insert/Update App specific
407 -- details.
408 pa_resource_id_tab :=NUMTAB();
409 pa_time_building_block_id_tab :=NUMTAB();
410 pa_object_version_number_tab :=NUMTAB();
411 pa_application_set_id_tab :=NUMTAB();
412 pa_org_id_tab :=NUMTAB();
413 pa_business_group_id_tab :=NUMTAB();
414 pa_timecard_id_tab :=NUMTAB();
415 pa_start_time_tab :=DATETAB();
416 pa_stop_time_tab :=DATETAB();
417 pa_last_update_date_tab :=DATETAB();
418 pa_approval_status_tab :=VARCHARTAB();
419 pa_comment_text_tab :=VARCHARTAB();
420 pa_resource_type_tab :=VARCHARTAB();
421
422 pay_resource_id_tab :=NUMTAB();
423 pay_time_building_block_id_tab :=NUMTAB();
424 pay_object_version_number_tab :=NUMTAB();
425 pay_application_set_id_tab :=NUMTAB();
426 pay_org_id_tab :=NUMTAB();
427 pay_business_group_id_tab :=NUMTAB();
428 pay_timecard_id_tab :=NUMTAB();
429 pay_start_time_tab :=DATETAB();
430 pay_stop_time_tab :=DATETAB();
431 pay_last_update_date_tab :=DATETAB();
432 pay_approval_status_tab :=VARCHARTAB();
433 pay_comment_text_tab :=VARCHARTAB();
434 pay_resource_type_tab :=VARCHARTAB();
435
436 -- Bug 8888911
437 l_blk_ind := p_blocks.FIRST;
438
439 BEGIN
440 l_org_id := hr_organization_api.get_operating_unit (p_effective_date => SYSDATE
441 ,p_person_id => p_blocks(l_blk_ind).resource_id);
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 l_org_id := FND_PROFILE.VALUE('ORG_ID');
446 END;
447
448 l_bg_id := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
449
450
451 if g_debug then
452 hr_utility.trace('In maintain details 1');
453 end if;
454
455 hxc_timecard_block_utils.sort_blocks
456 (p_blocks => p_blocks
457 ,p_timecard_blocks => l_timecard_blocks
458 ,p_day_blocks => l_day_blocks
459 ,p_detail_blocks => l_detail_blocks
460 );
461
462 l_detail_ind := l_detail_blocks.FIRST;
463 -- Bug 8888801
464 -- Pick up the timecard id from the blocks list.
465 l_timecard_id := p_blocks(l_blk_ind).time_building_block_id;
466
467 -- Bug 9684373
468 -- Added this call to populate the day scope records and against them,
469 -- the respective timecard_ids.
470 pick_up_day_scope(p_blocks);
471
472 -- Bug 9747820
473 l_app_index := 0;
474 l_app_tc := VARCHARTAB();
475
476
477 WHILE l_detail_ind IS NOT NULL
478 LOOP
479
480 l_blk_ind := l_detail_blocks(l_detail_ind);
481
482 --if g_debug then
483 -- hr_utility.trace('resource_id is '||to_char(p_blocks(l_blk_ind).resource_id));
484 -- hr_utility.trace('tbb id is '||to_char(p_blocks(l_blk_ind).time_building_Block_id));
485 -- hr_utility.trace('ovn is '||to_char(p_blocks(l_blk_ind).object_version_number));
486 -- hr_utility.trace('process is '||p_blocks(l_blk_ind).process);
487 -- hr_utility.trace('approval is '||p_blocks(l_blk_ind).approval_status);
488 --end if;
489
490 -- Override status for deleted building blocks. These should always be passed to
491 -- recipient applications for processing once deleted.
492
493 IF ( fnd_date.canonical_to_date(( p_blocks(l_blk_ind).date_to )) = hr_general.end_of_time )
494 THEN
495
496 l_status := p_blocks(l_blk_ind).approval_status;
497
498 ELSE
499
500 l_status := 'SUBMITTED';
501
502 END IF;
503
504 -- Bug 9747820
505 -- For each detail, create an entry in the VARCHAR table, with a combination of
506 -- timecard id and application set id for this detail.
507 -- Separator is '-'
508 l_app_index := l_app_index + 1;
509 l_app_tc.EXTEND(1);
510 l_app_tc(l_app_index) := l_day_scope(p_blocks(l_blk_ind).parent_building_block_id)||'-'||
511 p_blocks(l_blk_ind).application_set_id;
512
513 -- Added for Bug 14525477
514 IF ( p_blocks(l_blk_ind).time_building_block_id > 0 AND p_blocks(l_blk_ind).object_version_number = 1 AND p_blocks(l_blk_ind).process = 'Y' )
515 THEN
516
517 --if g_debug then
518 -- hr_utility.trace('gaz about to insert '||to_char(p_blocks(l_blk_ind).time_building_Block_id));
519 --end if;
520
521 -- insert row
522
523 INSERT INTO hxc_latest_details (
524 resource_id,
525 time_building_block_id,
526 object_version_number,
527 approval_status,
528 start_time,
529 stop_time,
530 application_set_id,
531 last_update_date,
532 comment_text,
533 resource_type,
534 org_id,
535 business_group_id ) -- Bug 8888911
536 VALUES (
537 p_blocks(l_blk_ind).resource_id,
538 p_blocks(l_blk_ind).time_building_block_id,
539 p_blocks(l_blk_ind).object_version_number,
540 l_status,
541 hxc_timecard_block_utils.date_value(
542 p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).start_time),
543 hxc_timecard_block_utils.date_value(
544 p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).stop_time),
545 p_blocks(l_blk_ind).application_set_id,
546 sysdate,
547 p_blocks(l_blk_ind).comment_text,
548 p_blocks(l_blk_ind).resource_type ,
549 l_org_id,
550 l_bg_id ); -- Bug 8888911
551
552
553 -- Bug 8888801
554 -- If Payroll is a valid recipient for this Application set,
555 -- add the details to the tables.
556 IF valid_time_recipient('Payroll',p_blocks(l_blk_ind).application_set_id)
557 THEN
558 extend_tables('PAY');
559 l_pay_index := l_pay_index + 1;
560 pay_resource_id_tab(l_pay_index) := p_blocks(l_blk_ind).resource_id;
561 pay_time_building_block_id_tab(l_pay_index) := p_blocks(l_blk_ind).time_building_block_id;
562 pay_object_version_number_tab(l_pay_index) := p_blocks(l_blk_ind).object_version_number;
563 pay_application_set_id_tab(l_pay_index) := p_blocks(l_blk_ind).application_set_id;
564 pay_org_id_tab(l_pay_index) := l_org_id;
565 pay_business_group_id_tab(l_pay_index) := l_bg_id;
566 -- Bug 9684373
567 --pay_timecard_id_tab(l_pay_index) := l_timecard_id;
568 pay_timecard_id_tab(l_pay_index) := l_day_scope(p_blocks(l_blk_ind).parent_building_block_id);
569 pay_start_time_tab(l_pay_index) := hxc_timecard_block_utils.date_value
570 (p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).start_time);
571 pay_stop_time_tab(l_pay_index) := hxc_timecard_block_utils.date_value
572 (p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).stop_time);
573 pay_last_update_date_tab(l_pay_index) := SYSDATE;
574 pay_approval_status_tab(l_pay_index) := l_status;
575 pay_comment_text_tab(l_pay_index) := p_blocks(l_blk_ind).comment_text;
576 pay_resource_type_tab(l_pay_index) := p_blocks(l_blk_ind).resource_type;
577
578
579 END IF;
580
581 -- Bug 8888801
582 -- If Projects is a valid recipient for this Application set,
583 -- add the details to the tables.
584 IF valid_time_recipient('Projects',p_blocks(l_blk_ind).application_set_id)
585 THEN
586 extend_tables('PA');
587 l_pa_index := l_pa_index + 1;
588 pa_resource_id_tab(l_pa_index) := p_blocks(l_blk_ind).resource_id;
589 pa_time_building_block_id_tab(l_pa_index) := p_blocks(l_blk_ind).time_building_block_id;
590 pa_object_version_number_tab(l_pa_index) := p_blocks(l_blk_ind).object_version_number;
591 pa_application_set_id_tab(l_pa_index) := p_blocks(l_blk_ind).application_set_id;
592 pa_org_id_tab(l_pa_index) := l_org_id;
593 pa_business_group_id_tab(l_pa_index) := l_bg_id;
594 -- Bug 9684373
595 -- pa_timecard_id_tab(l_pa_index) := l_timecard_id;
596 pa_timecard_id_tab(l_pa_index) := l_day_scope(p_blocks(l_blk_ind).parent_building_block_id);
597 pa_start_time_tab(l_pa_index) := hxc_timecard_block_utils.date_value
598 (p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).start_time);
599 pa_stop_time_tab(l_pa_index) := hxc_timecard_block_utils.date_value
600 (p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).stop_time);
601 pa_last_update_date_tab(l_pa_index) := SYSDATE;
602 pa_approval_status_tab(l_pa_index) := l_status;
603 pa_comment_text_tab(l_pa_index) := p_blocks(l_blk_ind).comment_text;
604 pa_resource_type_tab(l_pa_index) := p_blocks(l_blk_ind).resource_type;
605
606 END IF;
607
608
609 ELSIF ( p_blocks(l_blk_ind).object_version_number > 1 and p_blocks(l_blk_ind).process = 'Y' )
610 THEN
611 --if g_debug then
612 -- hr_utility.trace('gaz about to update '||to_char(p_blocks(l_blk_ind).time_building_Block_id));
613 --end if;
614
615 -- update row
616
617 UPDATE hxc_latest_details
618 SET object_version_number = p_blocks(l_blk_ind).object_version_number,
619 approval_status = l_status,
620 application_set_id = p_blocks(l_blk_ind).application_set_id,
621 last_update_date = sysdate,
622 comment_text = p_blocks(l_blk_ind).comment_text,
623 resource_type = p_blocks(l_blk_ind).resource_type,
624 org_id = l_org_id, -- Bug 8888911
625 business_group_id = l_bg_id -- Bug 8888911
626 WHERE time_building_block_id = p_blocks(l_blk_ind).time_building_block_id;
627
628 IF ( SQL%ROWCOUNT = 0 )
629 THEN
630 -- nothing to update, insert
631
632 INSERT INTO hxc_latest_details (
633 resource_id,
634 time_building_block_id,
635 object_version_number,
636 approval_status,
637 start_time,
638 stop_time,
639 application_set_id,
640 last_update_date,
641 comment_text,
642 resource_type ,
643 org_id, -- Bug 8888911
644 business_group_id) -- Bug 8888911
645 VALUES (
646 p_blocks(l_blk_ind).resource_id,
647 p_blocks(l_blk_ind).time_building_block_id,
648 p_blocks(l_blk_ind).object_version_number,
649 l_status,
650 hxc_timecard_block_utils.date_value(
651 p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).start_time),
652 hxc_timecard_block_utils.date_value(
653 p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).stop_time),
654 p_blocks(l_blk_ind).application_set_id,
655 sysdate,
656 p_blocks(l_blk_ind).comment_text,
657 p_blocks(l_blk_ind).resource_type ,
658 l_org_id, -- Bug 8888911
659 l_bg_id ); -- Bug 8888911
660
661
662 -- Bug 8888801
663 -- If Payroll is a valid recipient for this Application set,
664 -- add the details to the tables.
665
666 IF valid_time_recipient('Payroll',p_blocks(l_blk_ind).application_set_id)
667 THEN
668 extend_tables('PAY');
669 l_pay_index := l_pay_index + 1;
670 pay_resource_id_tab(l_pay_index) := p_blocks(l_blk_ind).resource_id;
671 pay_time_building_block_id_tab(l_pay_index) := p_blocks(l_blk_ind).time_building_block_id;
672 pay_object_version_number_tab(l_pay_index) := p_blocks(l_blk_ind).object_version_number;
673 pay_application_set_id_tab(l_pay_index) := p_blocks(l_blk_ind).application_set_id;
674 pay_org_id_tab(l_pay_index) := l_org_id;
675 pay_business_group_id_tab(l_pay_index) := l_bg_id;
676 -- Bug 9684373
677 pay_timecard_id_tab(l_pay_index) := l_day_scope(p_blocks(l_blk_ind).parent_building_block_id);
678 pay_start_time_tab(l_pay_index) := hxc_timecard_block_utils.date_value
679 (p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).start_time);
680 pay_stop_time_tab(l_pay_index) := hxc_timecard_block_utils.date_value
681 (p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).stop_time);
682 pay_last_update_date_tab(l_pay_index) := SYSDATE;
683 pay_approval_status_tab(l_pay_index) := l_status;
684 pay_comment_text_tab(l_pay_index) := p_blocks(l_blk_ind).comment_text;
685 pay_resource_type_tab(l_pay_index) := p_blocks(l_blk_ind).resource_type;
686 END IF;
687
688
689 -- Bug 8888801
690 -- If Projects is a valid recipient for this Application set,
691 -- add the details to the tables.
692
693 IF valid_time_recipient('Projects',p_blocks(l_blk_ind).application_set_id)
694 THEN
695 extend_tables('PA');
696 l_pa_index := l_pa_index + 1;
697 pa_resource_id_tab(l_pa_index) := p_blocks(l_blk_ind).resource_id;
698 pa_time_building_block_id_tab(l_pa_index) := p_blocks(l_blk_ind).time_building_block_id;
699 pa_object_version_number_tab(l_pa_index) := p_blocks(l_blk_ind).object_version_number;
700 pa_application_set_id_tab(l_pa_index) := p_blocks(l_blk_ind).application_set_id;
701 pa_org_id_tab(l_pa_index) := l_org_id;
702 pa_business_group_id_tab(l_pa_index) := l_bg_id;
703 -- Bug 9684373
704 pa_timecard_id_tab(l_pa_index) := l_day_scope(p_blocks(l_blk_ind).parent_building_block_id);
705 pa_start_time_tab(l_pa_index) := hxc_timecard_block_utils.date_value
706 (p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).start_time);
707 pa_stop_time_tab(l_pa_index) := hxc_timecard_block_utils.date_value
708 (p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).stop_time);
709 pa_last_update_date_tab(l_pa_index) := SYSDATE;
710 pa_approval_status_tab(l_pa_index) := l_status;
711 pa_comment_text_tab(l_pa_index) := p_blocks(l_blk_ind).comment_text;
712 pa_resource_type_tab(l_pa_index) := p_blocks(l_blk_ind).resource_type;
713
714 END IF;
715
716 ELSE
717
718 -- Bug 8888801
719 -- If Payroll is a valid recipient for this Application set,
720 -- add the details to the tables.
721 IF valid_time_recipient('Payroll',p_blocks(l_blk_ind).application_set_id)
722 THEN
723 extend_tables('PAY');
724 l_pay_index := l_pay_index + 1;
725 pay_resource_id_tab(l_pay_index) := p_blocks(l_blk_ind).resource_id;
726 pay_time_building_block_id_tab(l_pay_index) := p_blocks(l_blk_ind).time_building_block_id;
727 pay_object_version_number_tab(l_pay_index) := p_blocks(l_blk_ind).object_version_number;
728 pay_application_set_id_tab(l_pay_index) := p_blocks(l_blk_ind).application_set_id;
729 pay_org_id_tab(l_pay_index) := l_org_id;
730 pay_business_group_id_tab(l_pay_index) := l_bg_id;
731 -- Bug 9684373
732 pay_timecard_id_tab(l_pay_index) := l_day_scope(p_blocks(l_blk_ind).parent_building_block_id);
733 pay_start_time_tab(l_pay_index) := hxc_timecard_block_utils.date_value
734 (p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).start_time);
735 pay_stop_time_tab(l_pay_index) := hxc_timecard_block_utils.date_value
736 (p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).stop_time);
737 pay_last_update_date_tab(l_pay_index) := SYSDATE;
738 pay_approval_status_tab(l_pay_index) := l_status;
739 pay_comment_text_tab(l_pay_index) := p_blocks(l_blk_ind).comment_text;
740 pay_resource_type_tab(l_pay_index) := p_blocks(l_blk_ind).resource_type;
741
742
743 END IF;
744
745 -- Bug 8888801
746 -- If Projects is a valid recipient for this Application set,
747 -- add the details to the tables.
748 IF valid_time_recipient('Projects',p_blocks(l_blk_ind).application_set_id)
749 THEN
750 extend_tables('PA');
751 l_pa_index := l_pa_index + 1;
752 pa_resource_id_tab(l_pa_index) := p_blocks(l_blk_ind).resource_id;
753 pa_time_building_block_id_tab(l_pa_index) := p_blocks(l_blk_ind).time_building_block_id;
754 pa_object_version_number_tab(l_pa_index) := p_blocks(l_blk_ind).object_version_number;
755 pa_application_set_id_tab(l_pa_index) := p_blocks(l_blk_ind).application_set_id;
756 pa_org_id_tab(l_pa_index) := l_org_id;
757 pa_business_group_id_tab(l_pa_index) := l_bg_id;
758 -- Bug 9684373
759 pa_timecard_id_tab(l_pa_index) := l_day_scope(p_blocks(l_blk_ind).parent_building_block_id);
760 pa_start_time_tab(l_pa_index) := hxc_timecard_block_utils.date_value
761 (p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).start_time);
762 pa_stop_time_tab(l_pa_index) := hxc_timecard_block_utils.date_value
763 (p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).stop_time);
764 pa_last_update_date_tab(l_pa_index) := SYSDATE;
765 pa_approval_status_tab(l_pa_index) := l_status;
766 pa_comment_text_tab(l_pa_index) := p_blocks(l_blk_ind).comment_text;
767 pa_resource_type_tab(l_pa_index) := p_blocks(l_blk_ind).resource_type;
768
769 END IF;
770
771 END IF;
772
773
774
775 END IF;
776
777 l_detail_ind := l_detail_blocks.NEXT(l_detail_ind);
778
779 END LOOP;
780
781
782 -- Bug 9747820
783 -- We now have the table of timecard_id- application set_id combo.
784 if l_app_tc.count > 0
785 then
786 -- Get unique combination.
787 l_app_tc := SET(l_app_tc);
788 -- Loop thru the table.
789 FOR i IN l_app_tc.FIRST..l_app_tc.LAST
790 LOOP
791 -- Call to update the table.
792 -- The REGEXP pattern will pick up the string before '-' ( timecard_id)
793 -- and the second pattern will pick up the string after '-' (application set_id)
794 update_transferred_to(REPLACE(REGEXP_SUBSTR(l_app_tc(i),'.*-'),'-'),
795 REPLACE(REGEXP_SUBSTR(l_app_tc(i),'-.*'),'-'));
796 END LOOP;
797 end if;
798
799 -- Bug 8888801
800 -- Updating hxc_pa_latest_details
801 -- with details specific for the application.
802 FORALL i IN pa_resource_id_tab.FIRST..pa_resource_id_tab.LAST
803 UPDATE hxc_pa_latest_details
804 SET object_version_number = pa_object_version_number_tab(i),
805 approval_status = pa_approval_status_tab(i),
806 application_set_id = pa_application_set_id_tab(i),
807 last_update_date = pa_last_update_date_tab(i),
808 comment_text = pa_comment_text_tab(i),
809 resource_type = pa_resource_type_tab(i),
810 org_id = pa_org_id_tab(i),
811 business_group_id = pa_business_group_id_tab(i)
812 WHERE time_building_block_id = pa_time_building_block_id_tab(i);
813
814 -- Bug 8888801
815 -- Updating hxc_pay_latest_details
816 -- with details specific for the application.
817 FORALL i IN pay_resource_id_tab.FIRST..pay_resource_id_tab.LAST
818 UPDATE hxc_pay_latest_details
819 SET object_version_number = pay_object_version_number_tab(i),
820 approval_status = pay_approval_status_tab(i),
821 application_set_id = pay_application_set_id_tab(i),
822 last_update_date = pay_last_update_date_tab(i),
823 comment_text = pay_comment_text_tab(i),
824 resource_type = pay_resource_type_tab(i),
825 org_id = pay_org_id_tab(i),
826 business_group_id = pay_business_group_id_tab(i)
827 WHERE time_building_block_id = pay_time_building_block_id_tab(i);
828
829
830 -- Bug 8888801
831 -- Inserting into hxc_pa_latest_details and hxc_pay_latest_details
832 -- the respective details. Save Exceptions will see to it that
833 -- any exception is raised only at the end.
834 -- with details specific for the application.
835 BEGIN
836
837 FORALL i IN pay_resource_id_tab.FIRST..pay_resource_id_tab.LAST SAVE EXCEPTIONS
838 INSERT INTO hxc_pay_latest_details
839 (
840 resource_id,
841 time_building_block_id,
842 object_version_number,
843 approval_status,
844 start_time,
845 stop_time,
846 application_set_id,
847 last_update_date,
848 comment_text,
849 resource_type ,
850 org_id,
851 business_group_id,
852 timecard_id)
853 VALUES (
854 pay_resource_id_tab(i),
855 pay_time_building_block_id_tab(i),
856 pay_object_version_number_tab(i),
857 pay_approval_status_tab(i),
858 pay_start_time_tab(i),
859 pay_stop_time_tab(i),
860 pay_application_set_id_tab(i),
861 pay_last_update_date_tab(i),
862 pay_comment_text_tab(i),
863 pay_resource_type_tab(i) ,
864 pay_org_id_tab(i),
865 pay_business_group_id_tab(i),
866 pay_timecard_id_tab(i));
867
868 EXCEPTION
869
870 WHEN TABLE_EXCEPTION
871 THEN
872 NULL;
873
874 END;
875
876
877 BEGIN
878
879 FORALL i IN pa_resource_id_tab.FIRST..pa_resource_id_tab.LAST SAVE EXCEPTIONS
880 INSERT INTO hxc_pa_latest_details
881 (
882 resource_id,
883 time_building_block_id,
884 object_version_number,
885 approval_status,
886 start_time,
887 stop_time,
888 application_set_id,
889 last_update_date,
890 comment_text,
891 resource_type ,
892 org_id,
893 business_group_id,
894 timecard_id)
895 VALUES (
896 pa_resource_id_tab(i),
897 pa_time_building_block_id_tab(i),
898 pa_object_version_number_tab(i),
899 pa_approval_status_tab(i),
900 pa_start_time_tab(i),
901 pa_stop_time_tab(i),
902 pa_application_set_id_tab(i),
903 pa_last_update_date_tab(i),
904 pa_comment_text_tab(i),
905 pa_resource_type_tab(i) ,
906 pa_org_id_tab(i),
907 pa_business_group_id_tab(i),
908 pa_timecard_id_tab(i));
909
910 EXCEPTION
911
912 WHEN TABLE_EXCEPTION
913 THEN
914 NULL;
915
916 END;
917
918
919 EXCEPTION
920 -- Bug 8888801
921 -- IF an ORA - 24381 occurs, do nothing.
922 WHEN TABLE_EXCEPTION
923 THEN
924 NULL;
925 WHEN OTHERS THEN
926
927 hr_utility.trace(dbms_utility.format_error_backtrace);
928 IF g_debug
929 THEN
930 hr_utility.trace(SQLERRM);
931
932 END IF;
933
934 RAISE;
935
936 END maintain_latest_details;
937
938 FUNCTION valid_time_recipient(p_recipient IN VARCHAR2,
939 p_app_set_id IN NUMBER)
940 RETURN BOOLEAN
941 IS
942
943 l_exists NUMBER := 0;
944
945 BEGIN
946
947 -- Bug 12609169
948 -- Added code to have HR application also in place of Payroll only, where
949 -- time recipient is Payroll
950
951 IF NOT g_valid_rec.EXISTS(p_recipient||'-'||p_app_set_id)
952 THEN
953
954 IF p_recipient = 'Projects'
955 THEN
956
957 BEGIN
958 SELECT 1
959 INTO l_exists
960 FROM hxc_application_set_comps_v
961 WHERE application_set_id = p_app_set_id
962 AND time_recipient_name = p_recipient;
963
964 EXCEPTION
965 WHEN NO_DATA_FOUND
966 THEN
967 l_exists := 0;
968 END;
969
970 ELSIF p_recipient = 'Payroll'
971 THEN
972 BEGIN
973 -- Bug 14283008
974 -- Added rownum condition to avoid -1422 with HR and Payroll application set.
975 SELECT 1
976 INTO l_exists
977 FROM hxc_application_set_comps_v
978 WHERE application_set_id = p_app_set_id
979 AND time_recipient_name IN (p_recipient,'Human Resources')
980 AND ROWNUM < 2;
981
982 EXCEPTION
983 WHEN NO_DATA_FOUND
984 THEN
985 l_exists := 0;
986 END;
987
988 END IF;
989
990 IF l_exists = 1
991 THEN
992 g_valid_rec(p_recipient||'-'||p_app_set_id) := 'TRUE';
993 RETURN TRUE;
994 ELSE
995 g_valid_rec(p_recipient||'-'||p_app_set_id) := 'FALSE';
996 RETURN FALSE;
997 END IF;
998 ELSE
999 IF g_valid_rec(p_recipient||'-'||p_app_set_id) = 'TRUE'
1000 THEN
1001 RETURN TRUE;
1002 ELSE
1003 RETURN FALSE;
1004 END IF;
1005
1006 END IF;
1007
1008
1009 END valid_time_recipient;
1010
1011
1012 /*Following proc added for Bug 8888904 */
1013
1014 PROCEDURE maintain_rdb_snapshot(p_blocks IN hxc_block_table_type,
1015 p_attributes IN hxc_attribute_table_type)
1016 IS
1017
1018
1019 l_index BINARY_INTEGER:=0;
1020
1021
1022 l_pa_att_index BINARY_INTEGER:=0;
1023 l_pay_att_index BINARY_INTEGER:=0;
1024
1025 TYPE VTAB IS TABLE OF VARCHAR2(2500);
1026 TYPE NTAB IS TABLE OF NUMBER;
1027
1028 PA_TIME_ATTRIBUTE_ID NTAB;
1029 PA_BUILDING_BLOCK_ID NTAB;
1030 PA_BUILDING_BLOCK_OVN NTAB;
1031 PA_ATTRIBUTE_CATEGORY VTAB;
1032 PA_ATTRIBUTE1 VTAB;
1033 PA_ATTRIBUTE2 VTAB;
1034 PA_ATTRIBUTE3 VTAB;
1035 PA_ATTRIBUTE4 VTAB;
1036 PA_ATTRIBUTE5 VTAB;
1037 PA_ATTRIBUTE6 VTAB;
1038 PA_ATTRIBUTE7 VTAB;
1039 PA_MEASURE NTAB;
1040 -- Bug 14543573
1041 PA_DELETED VTAB;
1042
1043 PAY_TIME_ATTRIBUTE_ID NTAB;
1044 PAY_BUILDING_BLOCK_ID NTAB;
1045 PAY_BUILDING_BLOCK_OVN NTAB;
1046 PAY_ATTRIBUTE_CATEGORY VTAB;
1047 PAY_ATTRIBUTE1 VTAB;
1048 PAY_ATTRIBUTE2 VTAB;
1049 PAY_ATTRIBUTE3 VTAB;
1050 PAY_ATTRIBUTE4 VTAB;
1051 PAY_ATTRIBUTE5 VTAB;
1052 PAY_ATTRIBUTE6 VTAB;
1053 PAY_ATTRIBUTE7 VTAB;
1054 PAY_MEASURE NTAB;
1055 -- Bug 14543573
1056 PAY_DELETED VTAB;
1057
1058 TYPE ATTR_TBB_MAP_TYPE IS TABLE OF NUMBER
1059 INDEX BY VARCHAR2(250);
1060
1061 PAY_ATTR_TBB_MAP ATTR_TBB_MAP_TYPE;
1062 PA_ATTR_TBB_MAP ATTR_TBB_MAP_TYPE;
1063
1064 l_measure NUMBER;
1065 l_start_time DATE;
1066 l_stop_time DATE;
1067
1068 l_resource_id NUMBER;
1069 l_pref_date DATE;
1070 l_pref_table hxc_preference_evaluation.t_pref_table;
1071 l_resource_rdb_enabled VARCHAR2(1):='N';
1072 l_pref_index NUMBER;
1073
1074 /*
1075 CURSOR get_upgrade_status
1076 IS
1077 SELECT 'Y'
1078 FROM HXC_UPGRADE_DEFINITIONS
1079 WHERE upg_type = 'RDB_ATTRIB_SNAPSHOT'
1080 AND status = 'COMPLETE';
1081
1082 upg_status VARCHAR2(1):='N';
1083 */
1084
1085 FUNCTION valid_bld_blk_info_type(p_bld_blk_info_type IN VARCHAR2)
1086 RETURN NUMBER
1087 IS
1088
1089 l_bld_blk_info_type_id NUMBER:=0;
1090
1091 BEGIN
1092
1093 IF NOT g_valid_bld_blk.EXISTS(p_bld_blk_info_type) THEN
1094
1095 BEGIN
1096 select bld_blk_info_type_id
1097 into l_bld_blk_info_type_id
1098 from hxc_bld_blk_info_types
1099 where bld_blk_info_type = p_bld_blk_info_type;
1100
1101
1102 EXCEPTION
1103 WHEN NO_DATA_FOUND
1104 THEN
1105 l_bld_blk_info_type_id := 0;
1106 END;
1107
1108 g_valid_bld_blk(p_bld_blk_info_type):= l_bld_blk_info_type_id;
1109
1110 END IF; --g_valid_bld_blk
1111
1112 RETURN g_valid_bld_blk(p_bld_blk_info_type);
1113 END valid_bld_blk_info_type;
1114
1115 PROCEDURE EXTEND_ATTR_TAB(p_appln VARCHAR2)
1116 IS
1117 BEGIN
1118
1119 IF p_appln = 'PAY' then
1120
1121
1122 PAY_TIME_ATTRIBUTE_ID.EXTEND(1);
1123 PAY_BUILDING_BLOCK_ID.EXTEND(1);
1124 PAY_BUILDING_BLOCK_OVN.EXTEND(1);
1125 PAY_ATTRIBUTE_CATEGORY.EXTEND(1);
1126 PAY_ATTRIBUTE1.EXTEND(1);
1127 PAY_ATTRIBUTE2.EXTEND(1);
1128 PAY_ATTRIBUTE3.EXTEND(1);
1129 PAY_ATTRIBUTE4.EXTEND(1);
1130 PAY_ATTRIBUTE5.EXTEND(1);
1131 PAY_ATTRIBUTE6.EXTEND(1);
1132 PAY_ATTRIBUTE7.EXTEND(1);
1133 PAY_MEASURE.EXTEND(1);
1134 -- Bug 14543573
1135 PAY_DELETED.EXTEND(1);
1136
1137 ELSIF p_appln = 'PA' then
1138
1139 PA_TIME_ATTRIBUTE_ID.EXTEND(1);
1140 PA_BUILDING_BLOCK_ID.EXTEND(1);
1141 PA_BUILDING_BLOCK_OVN.EXTEND(1);
1142 PA_ATTRIBUTE_CATEGORY.EXTEND(1);
1143 PA_ATTRIBUTE1.EXTEND(1);
1144 PA_ATTRIBUTE2.EXTEND(1);
1145 PA_ATTRIBUTE3.EXTEND(1);
1146 PA_ATTRIBUTE4.EXTEND(1);
1147 PA_ATTRIBUTE5.EXTEND(1);
1148 PA_ATTRIBUTE6.EXTEND(1);
1149 PA_ATTRIBUTE7.EXTEND(1);
1150 PA_MEASURE.EXTEND(1);
1151 -- Bug 14543573
1152 PA_DELETED.EXTEND(1);
1153
1154 END IF; -- p_appln
1155
1156 END EXTEND_ATTR_TAB;
1157
1158
1159
1160
1161
1162 BEGIN -- maintain_rdb_snapshot
1163
1164 l_resource_id:= p_blocks(p_blocks.FIRST).RESOURCE_ID;
1165 l_pref_date:= TO_DATE( p_blocks(p_blocks.FIRST).START_TIME , 'RRRR/MM/DD HH24:MI:SS');
1166
1167 -- Bug 8888905
1168 hxc_preference_evaluation.resource_preferences
1169 (p_resource_id => l_resource_id,
1170 p_start_evaluation_date => l_pref_date,
1171 p_end_evaluation_date => l_pref_date,
1172 p_pref_table => l_pref_table);
1173
1174 l_pref_index := l_pref_table.FIRST;
1175 LOOP
1176 IF l_pref_table(l_pref_index).preference_code = 'TS_RDB_PREFERENCES'
1177 THEN
1178 l_resource_rdb_enabled := l_pref_table(l_pref_index).attribute1;
1179 EXIT;
1180 END IF;
1181 l_pref_index := l_pref_table.NEXT(l_pref_index);
1182 EXIT WHEN NOT l_pref_table.EXISTS(l_pref_index);
1183 END LOOP;
1184
1185 IF nvl(l_resource_rdb_enabled,'N') <> 'Y' THEN
1186
1187 if g_debug then
1188 hr_utility.trace('RDB Snapshot Pref not enabled');
1189 end if;
1190 return;
1191 END IF;
1192
1193
1194 -- initialize the pay/pa plsql tables
1195
1196 PA_TIME_ATTRIBUTE_ID := NTAB();
1197 PA_BUILDING_BLOCK_ID := NTAB();
1198 PA_BUILDING_BLOCK_OVN := NTAB();
1199 PA_ATTRIBUTE_CATEGORY := VTAB();
1200 PA_ATTRIBUTE1 := VTAB();
1201 PA_ATTRIBUTE2 := VTAB();
1202 PA_ATTRIBUTE3 := VTAB();
1203 PA_ATTRIBUTE4 := VTAB();
1204 PA_ATTRIBUTE5 := VTAB();
1205 PA_ATTRIBUTE6 := VTAB();
1206 PA_ATTRIBUTE7 := VTAB();
1207 PA_MEASURE := NTAB();
1208 -- Bug 14543573
1209 PA_DELETED := VTAB();
1210
1211 PAY_TIME_ATTRIBUTE_ID := NTAB();
1212 PAY_BUILDING_BLOCK_ID := NTAB();
1213 PAY_BUILDING_BLOCK_OVN := NTAB();
1214 PAY_ATTRIBUTE_CATEGORY := VTAB();
1215 PAY_ATTRIBUTE1 := VTAB();
1216 PAY_ATTRIBUTE2 := VTAB();
1217 PAY_ATTRIBUTE3 := VTAB();
1218 PAY_ATTRIBUTE4 := VTAB();
1219 PAY_ATTRIBUTE5 := VTAB();
1220 PAY_ATTRIBUTE6 := VTAB();
1221 PAY_ATTRIBUTE7 := VTAB();
1222 PAY_MEASURE := NTAB();
1223 -- Bug 14543573
1224 PAY_DELETED := VTAB();
1225
1226
1227
1228
1229 if g_debug then
1230 hr_utility.trace('maintain_rdb_snapshot');
1231
1232 if (p_blocks.count>0) then
1233
1234
1235 hr_utility.trace(' P_BLOCK TABLE START ');
1236 hr_utility.trace(' *****************');
1237
1238 l_index := p_blocks.FIRST;
1239
1240 LOOP
1241 EXIT WHEN NOT p_blocks.EXISTS (l_index);
1242
1243
1244 hr_utility.trace(' TIME_BUILDING_BLOCK_ID = '|| p_blocks(l_index).TIME_BUILDING_BLOCK_ID );
1245 hr_utility.trace(' TYPE = '|| p_blocks(l_index).TYPE ) ;
1246 hr_utility.trace(' MEASURE = '|| p_blocks(l_index).MEASURE) ;
1247 hr_utility.trace(' UNIT_OF_MEASURE = '|| p_blocks(l_index).UNIT_OF_MEASURE ) ;
1248 hr_utility.trace(' START_TIME = '|| p_blocks(l_index).START_TIME ) ;
1249 hr_utility.trace(' STOP_TIME = '|| p_blocks(l_index).STOP_TIME ) ;
1250 hr_utility.trace(' PARENT_BUILDING_BLOCK_ID = '|| p_blocks(l_index).PARENT_BUILDING_BLOCK_ID ) ;
1251 hr_utility.trace(' PARENT_IS_NEW = '|| p_blocks(l_index).PARENT_IS_NEW ) ;
1252 hr_utility.trace(' SCOPE = '|| p_blocks(l_index).SCOPE ) ;
1253 hr_utility.trace(' OBJECT_VERSION_NUMBER = '|| p_blocks(l_index).OBJECT_VERSION_NUMBER ) ;
1254 hr_utility.trace(' APPROVAL_STATUS = '|| p_blocks(l_index).APPROVAL_STATUS ) ;
1255 hr_utility.trace(' RESOURCE_ID = '|| p_blocks(l_index).RESOURCE_ID ) ;
1256 hr_utility.trace(' RESOURCE_TYPE = '|| p_blocks(l_index).RESOURCE_TYPE ) ;
1257 hr_utility.trace(' APPROVAL_STYLE_ID = '|| p_blocks(l_index).APPROVAL_STYLE_ID ) ;
1258 hr_utility.trace(' DATE_FROM = '|| p_blocks(l_index).DATE_FROM ) ;
1259 hr_utility.trace(' DATE_TO = '|| p_blocks(l_index).DATE_TO ) ;
1260 hr_utility.trace(' COMMENT_TEXT = '|| p_blocks(l_index).COMMENT_TEXT ) ;
1261 hr_utility.trace(' PARENT_BUILDING_BLOCK_OVN = '|| p_blocks(l_index).PARENT_BUILDING_BLOCK_OVN ) ;
1262 hr_utility.trace(' NEW = '|| p_blocks(l_index).NEW ) ;
1263 hr_utility.trace(' CHANGED = '|| p_blocks(l_index).CHANGED ) ;
1264 hr_utility.trace(' PROCESS = '|| p_blocks(l_index).PROCESS ) ;
1265 hr_utility.trace(' APPLICATION_SET_ID = '|| p_blocks(l_index).APPLICATION_SET_ID ) ;
1266 hr_utility.trace(' TRANSLATION_DISPLAY_KEY = '|| p_blocks(l_index).TRANSLATION_DISPLAY_KEY ) ;
1267 hr_utility.trace('------------------------------------------------------');
1268
1269 l_index := p_blocks.NEXT (l_index);
1270
1271 END LOOP;
1272
1273 hr_utility.trace(' p_blocks TABLE END ');
1274 hr_utility.trace(' *****************');
1275
1276 end if;
1277
1278
1279 if (p_attributes.count>0) then
1280
1281
1282 hr_utility.trace(' ATTRIBUTES TABLE START ');
1283 hr_utility.trace(' *****************');
1284
1285 l_index := p_attributes.FIRST;
1286
1287 LOOP
1288 EXIT WHEN NOT p_attributes.EXISTS (l_index);
1289
1290
1291 hr_utility.trace(' TIME_ATTRIBUTE_ID = '|| p_attributes(l_index).TIME_ATTRIBUTE_ID);
1292 hr_utility.trace(' BUILDING_BLOCK_ID = '|| p_attributes(l_index).BUILDING_BLOCK_ID ) ;
1293 hr_utility.trace(' ATTRIBUTE_CATEGORY = '|| p_attributes(l_index).ATTRIBUTE_CATEGORY) ;
1294 hr_utility.trace(' ATTRIBUTE1 = '|| p_attributes(l_index).ATTRIBUTE1 ) ;
1295 hr_utility.trace(' ATTRIBUTE2 (p_alias_definition_id) = '|| p_attributes(l_index).ATTRIBUTE2 ) ;
1296 hr_utility.trace(' ATTRIBUTE3 (l_alias_value_id) = '|| p_attributes(l_index).ATTRIBUTE3 ) ;
1297 hr_utility.trace(' ATTRIBUTE4 (p_alias_type) = '|| p_attributes(l_index).ATTRIBUTE4 ) ;
1298 hr_utility.trace(' ATTRIBUTE5 = '|| p_attributes(l_index).ATTRIBUTE5 ) ;
1299 hr_utility.trace(' ATTRIBUTE6 = '|| p_attributes(l_index).ATTRIBUTE6 ) ;
1300 hr_utility.trace(' ATTRIBUTE7 = '|| p_attributes(l_index).ATTRIBUTE7 ) ;
1301 hr_utility.trace(' ATTRIBUTE8 = '|| p_attributes(l_index).ATTRIBUTE8 ) ;
1302 hr_utility.trace(' ATTRIBUTE9 = '|| p_attributes(l_index).ATTRIBUTE9 ) ;
1303 hr_utility.trace(' ATTRIBUTE10 = '|| p_attributes(l_index).ATTRIBUTE10 ) ;
1304 hr_utility.trace(' ATTRIBUTE11 = '|| p_attributes(l_index).ATTRIBUTE11 ) ;
1305 hr_utility.trace(' ATTRIBUTE12 = '|| p_attributes(l_index).ATTRIBUTE12 ) ;
1306 hr_utility.trace(' ATTRIBUTE13 = '|| p_attributes(l_index).ATTRIBUTE13 ) ;
1307 hr_utility.trace(' ATTRIBUTE14 = '|| p_attributes(l_index).ATTRIBUTE14 ) ;
1308 hr_utility.trace(' ATTRIBUTE15 = '|| p_attributes(l_index).ATTRIBUTE15 ) ;
1309 hr_utility.trace(' ATTRIBUTE16 = '|| p_attributes(l_index).ATTRIBUTE16 ) ;
1310 hr_utility.trace(' ATTRIBUTE17 = '|| p_attributes(l_index).ATTRIBUTE17 ) ;
1311 hr_utility.trace(' ATTRIBUTE18 = '|| p_attributes(l_index).ATTRIBUTE18 ) ;
1312 hr_utility.trace(' ATTRIBUTE19 = '|| p_attributes(l_index).ATTRIBUTE19 ) ;
1313 hr_utility.trace(' ATTRIBUTE20 = '|| p_attributes(l_index).ATTRIBUTE20 ) ;
1314 hr_utility.trace(' ATTRIBUTE21 = '|| p_attributes(l_index).ATTRIBUTE21 ) ;
1315 hr_utility.trace(' ATTRIBUTE22 = '|| p_attributes(l_index).ATTRIBUTE22 ) ;
1316 hr_utility.trace(' ATTRIBUTE23 = '|| p_attributes(l_index).ATTRIBUTE23 ) ;
1317 hr_utility.trace(' ATTRIBUTE24 = '|| p_attributes(l_index).ATTRIBUTE24 ) ;
1318 hr_utility.trace(' ATTRIBUTE25 = '|| p_attributes(l_index).ATTRIBUTE25 ) ;
1319 hr_utility.trace(' ATTRIBUTE26 = '|| p_attributes(l_index).ATTRIBUTE26 ) ;
1320 hr_utility.trace(' ATTRIBUTE27 = '|| p_attributes(l_index).ATTRIBUTE27 ) ;
1321 hr_utility.trace(' ATTRIBUTE28 = '|| p_attributes(l_index).ATTRIBUTE28 ) ;
1322 hr_utility.trace(' ATTRIBUTE29 (p_alias_ref_object) = '|| p_attributes(l_index).ATTRIBUTE29 ) ;
1323 hr_utility.trace(' ATTRIBUTE30 (p_alias_value_name) = '|| p_attributes(l_index).ATTRIBUTE30 ) ;
1324 hr_utility.trace(' BLD_BLK_INFO_TYPE_ID = '|| p_attributes(l_index).BLD_BLK_INFO_TYPE_ID );
1325 hr_utility.trace(' OBJECT_VERSION_NUMBER = '|| p_attributes(l_index).OBJECT_VERSION_NUMBER );
1326 hr_utility.trace(' NEW = '|| p_attributes(l_index).NEW );
1327 hr_utility.trace(' CHANGED = '|| p_attributes(l_index).CHANGED );
1328 hr_utility.trace(' BLD_BLK_INFO_TYPE = '|| p_attributes(l_index).BLD_BLK_INFO_TYPE );
1329 hr_utility.trace(' PROCESS = '|| p_attributes(l_index).PROCESS );
1330 hr_utility.trace(' BUILDING_BLOCK_OVN = '|| p_attributes(l_index).BUILDING_BLOCK_OVN );
1331 hr_utility.trace('------------------------------------------------------');
1332
1333 l_index := p_attributes.NEXT (l_index);
1334
1335 END LOOP;
1336
1337 hr_utility.trace(' ATTRIBUTES TABLE END ');
1338 hr_utility.trace(' *****************');
1339
1340 end if;
1341
1342 end if; -- g_debug
1343
1344 /*
1345 1. Put it into Globals - the bld_blk_ids of DUMMY ELEMENT CONTEXT and PROJECTS
1346 2. Get the Attributes into 2 sep plsql tables - pa and pay based on the bld blk globals.
1347 3. Make one forall update.
1348 */
1349
1350 if p_attributes.COUNT > 0 then
1351
1352 l_index := p_attributes.FIRST;
1353
1354 LOOP
1355 EXIT WHEN NOT p_attributes.EXISTS (l_index);
1356
1357 if p_attributes(l_index).BLD_BLK_INFO_TYPE_ID = valid_bld_blk_info_type('Dummy Element Context') then
1358
1359
1360 EXTEND_ATTR_TAB('PAY');
1361 l_pay_att_index:= l_pay_att_index +1;
1362
1363 PAY_TIME_ATTRIBUTE_ID(l_pay_att_index) := p_attributes(l_index).TIME_ATTRIBUTE_ID;
1364 PAY_BUILDING_BLOCK_ID(l_pay_att_index) := p_attributes(l_index).BUILDING_BLOCK_ID;
1365 PAY_BUILDING_BLOCK_OVN(l_pay_att_index) := p_attributes(l_index).BUILDING_BLOCK_OVN;
1366 PAY_ATTRIBUTE_CATEGORY(l_pay_att_index) := p_attributes(l_index).ATTRIBUTE_CATEGORY;
1367 PAY_ATTRIBUTE1(l_pay_att_index) := REPLACE(p_attributes(l_index).ATTRIBUTE_CATEGORY,'ELEMENT - ');
1368 PAY_ATTRIBUTE2(l_pay_att_index) := null ; --p_attributes(l_index).ATTRIBUTE2;
1369 PAY_ATTRIBUTE3(l_pay_att_index) := null ; -- p_attributes(l_index).ATTRIBUTE3;
1370 PAY_ATTRIBUTE4(l_pay_att_index) := null ; --p_attributes(l_index).ATTRIBUTE4;
1371 PAY_ATTRIBUTE5(l_pay_att_index) := null ; --p_attributes(l_index).ATTRIBUTE5;
1372 PAY_ATTRIBUTE6(l_pay_att_index) := null ; --p_attributes(l_index).ATTRIBUTE6;
1373 PAY_ATTRIBUTE7(l_pay_att_index) := null ; --p_attributes(l_index).ATTRIBUTE7;
1374
1375 PAY_ATTR_TBB_MAP(p_attributes(l_index).BUILDING_BLOCK_ID ||':'||
1376 p_attributes(l_index).BUILDING_BLOCK_OVN) := l_pay_att_index;
1377
1378 elsif p_attributes(l_index).BLD_BLK_INFO_TYPE_ID = valid_bld_blk_info_type('PROJECTS') then
1379
1380 EXTEND_ATTR_TAB('PA');
1381 l_pa_att_index:= l_pa_att_index +1;
1382
1383 PA_TIME_ATTRIBUTE_ID(l_pa_att_index) := p_attributes(l_index).TIME_ATTRIBUTE_ID;
1384 PA_BUILDING_BLOCK_ID(l_pa_att_index) := p_attributes(l_index).BUILDING_BLOCK_ID;
1385 PA_BUILDING_BLOCK_OVN(l_pa_att_index) := p_attributes(l_index).BUILDING_BLOCK_OVN;
1386 PA_ATTRIBUTE_CATEGORY(l_pa_att_index) := p_attributes(l_index).ATTRIBUTE_CATEGORY;
1387 PA_ATTRIBUTE1(l_pa_att_index) := p_attributes(l_index).ATTRIBUTE1;
1388 PA_ATTRIBUTE2(l_pa_att_index) := p_attributes(l_index).ATTRIBUTE2;
1389 PA_ATTRIBUTE3(l_pa_att_index) := p_attributes(l_index).ATTRIBUTE3;
1390 PA_ATTRIBUTE4(l_pa_att_index) := p_attributes(l_index).ATTRIBUTE4;
1391 PA_ATTRIBUTE5(l_pa_att_index) := p_attributes(l_index).ATTRIBUTE5;
1392 PA_ATTRIBUTE6(l_pa_att_index) := p_attributes(l_index).ATTRIBUTE6;
1393 PA_ATTRIBUTE7(l_pa_att_index) := p_attributes(l_index).ATTRIBUTE7;
1394
1395 PA_ATTR_TBB_MAP(p_attributes(l_index).BUILDING_BLOCK_ID ||':'||
1396 p_attributes(l_index).BUILDING_BLOCK_OVN) := l_pa_att_index;
1397
1398
1399 end if; -- BLD_BLK_INFO_TYPE_ID
1400
1401 l_index := p_attributes.NEXT (l_index);
1402
1403 END LOOP; -- p_attributes
1404
1405 /* Populating PA_MEASURE and PAY_MEASURE */
1406
1407 l_pa_att_index:= 0;
1408 l_pay_att_index:=0;
1409
1410 if p_blocks.COUNT > 0 then
1411
1412 l_index := p_blocks.FIRST;
1413
1414 LOOP
1415 EXIT WHEN NOT p_blocks.EXISTS (l_index);
1416
1417 IF PAY_ATTR_TBB_MAP.EXISTS(p_blocks(l_index).TIME_BUILDING_BLOCK_ID || ':' ||
1418 p_blocks(l_index).OBJECT_VERSION_NUMBER) THEN
1419
1420
1421 l_pay_att_index:= PAY_ATTR_TBB_MAP(p_blocks(l_index).TIME_BUILDING_BLOCK_ID || ':' ||
1422 p_blocks(l_index).OBJECT_VERSION_NUMBER);
1423
1424 if (p_blocks(l_index).DATE_TO <> fnd_date.date_to_canonical(hr_general.end_of_time)) then
1425 l_measure:=0; -- deleted entry
1426 -- Bug 14543573
1427 -- Marking deleted entry into this table.
1428 PAY_DELETED(l_pay_att_index) := 'Y';
1429 elsif p_blocks(l_index).MEASURE IS NOT NULL then
1430 l_measure:= p_blocks(l_index).MEASURE;
1431 else
1432 l_stop_time:= TO_DATE(p_blocks(l_index).STOP_TIME , 'RRRR/MM/DD HH24:MI:SS');
1433 l_start_time := TO_DATE(p_blocks(l_index).START_TIME , 'RRRR/MM/DD HH24:MI:SS');
1434 l_measure:= ( TO_NUMBER(l_stop_time - l_start_time)
1435 * 24 ) ;
1436
1437 if l_measure < 0 then
1438 l_measure:= l_measure + 24;
1439 end if;
1440 end if;
1441
1442 PAY_MEASURE(l_pay_att_index):= l_measure;
1443
1444 END IF;
1445
1446
1447 IF PA_ATTR_TBB_MAP.EXISTS(p_blocks(l_index).TIME_BUILDING_BLOCK_ID || ':' ||
1448 p_blocks(l_index).OBJECT_VERSION_NUMBER) THEN
1449
1450
1451 l_pa_att_index:= PA_ATTR_TBB_MAP(p_blocks(l_index).TIME_BUILDING_BLOCK_ID || ':' ||
1452 p_blocks(l_index).OBJECT_VERSION_NUMBER);
1453
1454 if (p_blocks(l_index).DATE_TO <> fnd_date.date_to_canonical(hr_general.end_of_time)) then
1455 l_measure:=0; -- deleted entry
1456 -- Bug 14543573
1457 -- Marking deleted entry into this table.
1458 PA_DELETED(l_pa_att_index) := 'Y';
1459 elsif p_blocks(l_index).MEASURE IS NOT NULL then
1460 l_measure:= p_blocks(l_index).MEASURE;
1461 else
1462 l_stop_time:= TO_DATE(p_blocks(l_index).STOP_TIME , 'RRRR/MM/DD HH24:MI:SS');
1463 l_start_time := TO_DATE(p_blocks(l_index).START_TIME , 'RRRR/MM/DD HH24:MI:SS');
1464 l_measure:= ( TO_NUMBER(l_stop_time - l_start_time)
1465 * 24 ) ;
1466
1467 if l_measure < 0 then
1468 l_measure:= l_measure + 24;
1469 end if;
1470
1471 end if;
1472
1473 PA_MEASURE(l_pa_att_index):= l_measure;
1474
1475 END IF;
1476
1477 l_index := p_blocks.NEXT (l_index);
1478
1479 END LOOP; -- p_blocks
1480
1481 end if; -- p_block.count
1482
1483
1484
1485
1486 /*PRINTING THE PAY TAB*/
1487 /*time to print out the pa and pay specific stuff we have here */
1488 if g_debug then
1489
1490 if (PAY_TIME_ATTRIBUTE_ID.count>0) then
1491
1492
1493 hr_utility.trace(' PAY ATTRIBUTES TABLE START ');
1494 hr_utility.trace(' *****************');
1495
1496 FOR i in PAY_TIME_ATTRIBUTE_ID.first .. PAY_TIME_ATTRIBUTE_ID.last LOOP
1497
1498
1499 hr_utility.trace(' TIME_ATTRIBUTE_ID = '|| PAY_TIME_ATTRIBUTE_ID(i));
1500 hr_utility.trace(' BUILDING_BLOCK_ID = '|| PAY_BUILDING_BLOCK_ID(i)) ;
1501 hr_utility.trace(' ATTRIBUTE_CATEGORY = '|| PAY_ATTRIBUTE_CATEGORY(i)) ;
1502 hr_utility.trace(' ATTRIBUTE1 = '|| PAY_ATTRIBUTE1(i));
1503 hr_utility.trace(' ATTRIBUTE2 = '|| PAY_ATTRIBUTE2(i)) ;
1504 hr_utility.trace(' ATTRIBUTE3 = '|| PAY_ATTRIBUTE3(i));
1505 hr_utility.trace(' ATTRIBUTE4 = '|| PAY_ATTRIBUTE4(i)) ;
1506 hr_utility.trace(' ATTRIBUTE5 = '|| PAY_ATTRIBUTE5(i)) ;
1507 hr_utility.trace(' ATTRIBUTE6 = '|| PAY_ATTRIBUTE6(i)) ;
1508 hr_utility.trace(' ATTRIBUTE7 = '|| PAY_ATTRIBUTE7(i)) ;
1509 hr_utility.trace(' BUILDING_BLOCK_OVN = '|| PAY_BUILDING_BLOCK_OVN(i));
1510 hr_utility.trace(' MEASURE = '|| PAY_MEASURE(i));
1511 hr_utility.trace('------------------------------------------------------');
1512
1513
1514
1515 END LOOP;
1516
1517 hr_utility.trace(' PAY ATTRIBUTES TABLE END ');
1518 hr_utility.trace(' *****************');
1519
1520
1521 else
1522
1523 hr_utility.trace('NO PAY ATTR');
1524
1525 end if;
1526
1527
1528 /*PRINTING THE PA TAB*/
1529
1530 if (PA_TIME_ATTRIBUTE_ID.count>0) then
1531
1532
1533 hr_utility.trace(' PA ATTRIBUTES TABLE START ');
1534 hr_utility.trace(' *****************');
1535
1536 FOR i in PA_TIME_ATTRIBUTE_ID.first .. PA_TIME_ATTRIBUTE_ID.last LOOP
1537
1538
1539 hr_utility.trace(' TIME_ATTRIBUTE_ID = '|| PA_TIME_ATTRIBUTE_ID(i));
1540 hr_utility.trace(' BUILDING_BLOCK_ID = '|| PA_BUILDING_BLOCK_ID(i)) ;
1541 hr_utility.trace(' ATTRIBUTE_CATEGORY = '|| PA_ATTRIBUTE_CATEGORY(i)) ;
1542 hr_utility.trace(' ATTRIBUTE1 = '|| PA_ATTRIBUTE1(i)) ;
1543 hr_utility.trace(' ATTRIBUTE2 = '|| PA_ATTRIBUTE2(i)) ;
1544 hr_utility.trace(' ATTRIBUTE3 = '|| PA_ATTRIBUTE3(i)) ;
1545 hr_utility.trace(' ATTRIBUTE4 = '|| PA_ATTRIBUTE4(i)) ;
1546 hr_utility.trace(' ATTRIBUTE5 = '|| PA_ATTRIBUTE5(i)) ;
1547 hr_utility.trace(' ATTRIBUTE6 = '|| PA_ATTRIBUTE6(i)) ;
1548 hr_utility.trace(' ATTRIBUTE7 = '|| PA_ATTRIBUTE7(i)) ;
1549 hr_utility.trace(' BUILDING_BLOCK_OVN = '|| PA_BUILDING_BLOCK_OVN(i));
1550 hr_utility.trace(' MEASURE = '|| PA_MEASURE(i));
1551 hr_utility.trace('------------------------------------------------------');
1552
1553 END LOOP;
1554
1555 hr_utility.trace(' PA ATTRIBUTES TABLE END ');
1556 hr_utility.trace(' *****************');
1557
1558 else
1559
1560 hr_utility.trace('NO PA ATTR');
1561
1562 end if;
1563
1564
1565 end if; --g_debug
1566
1567
1568 FORALL i IN PAY_TIME_ATTRIBUTE_ID.FIRST..PAY_TIME_ATTRIBUTE_ID.LAST
1569 UPDATE hxc_pay_latest_details
1570 SET ATTRIBUTE_CATEGORY = PAY_ATTRIBUTE_CATEGORY(i),
1571 ATTRIBUTE1 = PAY_ATTRIBUTE1(i),
1572 ATTRIBUTE2 = PAY_ATTRIBUTE2(i),
1573 ATTRIBUTE3 = PAY_ATTRIBUTE3(i),
1574 ATTRIBUTE4 = PAY_ATTRIBUTE4(i),
1575 ATTRIBUTE5 = PAY_ATTRIBUTE5(i),
1576 ATTRIBUTE6 = PAY_ATTRIBUTE6(i),
1577 ATTRIBUTE7 = PAY_ATTRIBUTE7(i),
1578 MEASURE = PAY_MEASURE(i)
1579 WHERE TIME_BUILDING_BLOCK_ID = PAY_BUILDING_BLOCK_ID(i)
1580 AND OBJECT_VERSION_NUMBER = PAY_BUILDING_BLOCK_OVN(i);
1581
1582
1583 -- Bug 14543573
1584 -- Deleting tbb ids which are LOGICALLY DELETED in hxc_time_building_blocks
1585 -- and never retrieved. Such bb ids need not be considered for retrieval.
1586 -- and can be removed from the HXC_PAY table for upgraded retrieval.
1587 FORALL i IN PAY_TIME_ATTRIBUTE_ID.FIRST..PAY_TIME_ATTRIBUTE_ID.LAST
1588 DELETE FROM hxc_pay_latest_details pre
1589 WHERE TIME_BUILDING_BLOCK_ID = PAY_BUILDING_BLOCK_ID(i)
1590 AND OBJECT_VERSION_NUMBER = PAY_BUILDING_BLOCK_OVN(i)
1591 AND pay_measure(i) = 0
1592 AND pay_deleted(i) = 'Y'
1593 AND NOT EXISTS ( SELECT 1
1594 FROM hxc_ret_pay_latest_details ret
1595 WHERE pre.time_building_block_id = ret.time_building_block_id
1596 );
1597
1598
1599
1600 FORALL i IN PA_TIME_ATTRIBUTE_ID.FIRST..PA_TIME_ATTRIBUTE_ID.LAST
1601 UPDATE hxc_pa_latest_details
1602 SET ATTRIBUTE_CATEGORY = PA_ATTRIBUTE_CATEGORY(i),
1603 ATTRIBUTE1 = PA_ATTRIBUTE1(i),
1604 ATTRIBUTE2 = PA_ATTRIBUTE2(i),
1605 ATTRIBUTE3 = PA_ATTRIBUTE3(i),
1606 ATTRIBUTE4 = PA_ATTRIBUTE4(i),
1607 ATTRIBUTE5 = PA_ATTRIBUTE5(i),
1608 ATTRIBUTE6 = PA_ATTRIBUTE6(i),
1609 ATTRIBUTE7 = PA_ATTRIBUTE7(i),
1610 MEASURE = PA_MEASURE(i)
1611 WHERE TIME_BUILDING_BLOCK_ID = PA_BUILDING_BLOCK_ID(i)
1612 AND OBJECT_VERSION_NUMBER = PA_BUILDING_BLOCK_OVN(i);
1613
1614 -- Bug 14543573
1615 -- Deleting tbb ids which are LOGICALLY DELETED in hxc_time_building_blocks
1616 -- and never retrieved. Such bb ids need not be considered for retrieval.
1617 -- and can be removed from the HXC_PA table for upgraded retrieval.
1618 FORALL i IN PA_TIME_ATTRIBUTE_ID.FIRST..PA_TIME_ATTRIBUTE_ID.LAST
1619 DELETE FROM hxc_pa_latest_details pre
1620 WHERE TIME_BUILDING_BLOCK_ID = PA_BUILDING_BLOCK_ID(i)
1621 AND OBJECT_VERSION_NUMBER = PA_BUILDING_BLOCK_OVN(i)
1622 AND pa_measure(i) = 0
1623 AND pa_deleted(i) = 'Y'
1624 AND NOT EXISTS ( SELECT 1
1625 FROM hxc_ret_pa_latest_details ret
1626 WHERE pre.time_building_block_id = ret.time_building_block_id
1627 );
1628
1629
1630 else
1631
1632 if g_debug then
1633 hr_utility.trace('NO P_ATTRIBUTES');
1634 end if;
1635
1636 end if; -- p_attributes.COUNT
1637
1638 if g_debug then
1639 hr_utility.trace('Came out of maintain_rdb_snapshot');
1640 end if;
1641
1642 END maintain_rdb_snapshot;
1643
1644 End hxc_timecard_audit;