[Home] [Help]
PACKAGE BODY: APPS.HXT_OTC_RETRIEVAL_INTERFACE
Source
1 PACKAGE BODY hxt_otc_retrieval_interface AS
2 /* $Header: hxtotcri.pkb 120.23.12020000.3 2013/04/04 15:17:25 asrajago ship $ */
3 --
4 --
5 g_debug BOOLEAN := hr_utility.debug_enabled;
6 g_package CONSTANT VARCHAR2 (31) := 'hxc_otc_retrieval_interface.';
7
8 TYPE t_timcards_tab IS TABLE OF NUMBER
9 INDEX BY VARCHAR2(255);
10
11 --
12 g_status VARCHAR2 (30);
13 g_exception_description VARCHAR2 (2000);
14 e_record_error EXCEPTION;
15 e_amount_hours EXCEPTION;
16 g_timecards t_timcards_tab;
17 g_bg_id NUMBER;
18 l_no_more_timecards BOOLEAN := FALSE;
19
20 -- Bug 12850901
21 -- Added these two variables.
22 g_intg_pref_tab t_timcards_tab;
23 g_intg_error VARCHAR2(4000);
24 -- Bug 12919783
25 -- New tables and their index
26 g_rdb_bb_tab NUMTAB := NUMTAB();
27 g_rdb_ovn_tab NUMTAB := NUMTAB();
28 g_rdb_retro_tab NUMTAB := NUMTAB();
29 g_rdb_index NUMBER := 0;
30
31
32 -- Bug 12919783
33 -- Procedure to mark retro batches on new/old details.
34 -- Explanation inline.
35
36 PROCEDURE mark_retro_batches
37 IS
38
39 l_index VARCHAR2(50);
40 i NUMBER := 0;
41 TYPE NUMTAB IS TABLE OF NUMBER;
42 l_bb_tab NUMTAB := NUMTAB();
43 l_ovn_tab NUMTAB := NUMTAB();
44 l_ret_tab NUMTAB := NUMTAB();
45 l_old_ret_tab NUMTAB := NUMTAB();
46
47 BEGIN
48
49
50 --- Here we have two tasks to do, for all timecards undergoing Retro.
51 ---
52 ---
53 --- Retro entries to each timecard has a retro batch. This retro batch is generated only
54 --- after explosion. Since the explosion happens at the end of the process, by that time
55 --- hxc_generic_retrieval_pkg.update_transaction_status would have updated all the existing
56 --- and inserted new records into hxc_ret_pay_latest_details
57 --- For all of these updated/inserted records, we need to mark batch_id column with the retro_batch_id
58 --- generated during explosion so that later, Xfer to BEE(Retro) correctly knows which records to
59 --- target.
60 ---
61 ---
62 --- The above thing works fine for all time entries touched in the last updation
63 --- and are being transferred.
64 --- However there might be a case where there is already a retro batch in place for some other entries
65 --- where the batch is still in status 'Hold' and is now discarded now.
66 --- Eg. Mon- Fri 8 hrs entered.
67 --- Xferred to OTLR
68 --- Validated and Xferred to BEE
69 --- Monday's entry changed to 9 hrs
70 --- Xferred to OTLR
71 --- Retro1 created.
72 --- Not validated and Xferred, and it still is in status 'Hold'
73 --- Details are all in status 'R'
74 --- Tuesday's entry changed to 9 hrs
75 --- Xferred to OTLR
76 --- Retro2 created
77 --- Retro1 is now end dated, and would not be transferred again.
78 ---
79 --- Here we are targetting Retro1 details;
80 --- We pick up all details in Retro1 and update them to Retro2 and this request_id etc.
81 ---
82
83
84 -- Check if we have Tim_ids (timecards to consider now )
85 IF g_timid_tab.COUNT > 0
86 THEN
87 l_index := g_timid_tab.FIRST;
88 LOOP
89 IF g_debug
90 THEN
91 -- The index is tbb id here
92 hr_utility.trace(' Index is '||l_index);
93 hr_utility.trace(' OVN is '||g_ovn_tab(l_index));
94 hr_utility.trace(' Tim is '||g_timid_tab(l_index));
95 END IF;
96 -- Find if there is a retro batch for this Tim_id
97 IF g_retro_tab.EXISTS(TO_CHAR(g_timid_tab(l_index)))
98 THEN
99 IF g_debug
100 THEN
101 hr_utility.trace(' Retro batch is '||g_retro_tab(TO_CHAR(g_timid_tab(l_index))));
102 END IF;
103 -- Extend all the tables
104 l_bb_tab.EXTEND;
105 l_ovn_tab.EXTEND;
106 l_ret_tab.EXTEND;
107 l_old_ret_tab.EXTEND;
108 i := i+1;
109 l_bb_tab(i) := TO_NUMBER(l_index);
110 l_ovn_tab(i) := g_ovn_tab(l_index);
111 l_ret_tab(i) := g_retro_tab(TO_CHAR(g_timid_tab(l_index)));
112 -- Find out if there was an Older retro batch now being discarded
113 -- Such batches will be in status Hold in hxt_batch_states,
114 -- Details would all be end dated with status 'R' meaning the retro batch was never transferred.
115 IF g_old_retro_tab.EXISTS(TO_CHAR(g_timid_tab(l_index)))
116 AND g_old_retro_tab(TO_CHAR(g_timid_tab(l_index))) <> 0
117 THEN
118 hr_utility.trace(' Older retro batch exists for this bb ');
119 l_old_ret_tab(i) := g_old_retro_tab(TO_CHAR(g_timid_tab(l_index)));
120 ELSE
121 hr_utility.trace(' Older retro batch Does not exist for this bb ');
122 l_old_ret_tab(i) := -99;
123 END IF;
124 END IF;
125 l_index := g_timid_tab.NEXT(l_index);
126 EXIT WHEN NOT g_timid_tab.EXISTS(l_index);
127 END LOOP;
128 END IF;
129
130 IF g_debug
131 THEN
132 hr_utility.trace('Printing out collected info ');
133 IF l_bb_tab.COUNT > 0
134 THEN
135 FOR j IN l_bb_tab.FIRST..l_bb_tab.LAST
136 LOOP
137 hr_utility.trace('tbb_id :'||l_bb_tab(j));
138 hr_utility.trace('Ovn:'||l_ovn_tab(j));
139 hr_utility.trace('Retro Batch '||l_ret_tab(i));
140 hr_utility.trace('Old Retro Batch '||l_old_ret_tab(i));
141 END LOOP;
142 END IF;
143 END IF;
144
145 IF l_bb_tab.COUNT > 0
146 THEN
147
148 -- Updating records touched in this retrieval process here
149
150 FORALL i IN l_bb_tab.FIRST..l_bb_tab.LAST
151 UPDATE hxc_ret_pay_latest_details
152 SET batch_id = l_ret_tab(i)
153 WHERE time_building_block_id = l_bb_tab(i)
154 AND object_version_number = l_ovn_tab(i)
155 AND request_id = FND_GLOBAL.CONC_request_id;
156
157 FORALL i IN l_bb_tab.FIRST..l_bb_tab.LAST
158 UPDATE hxc_ret_pay_details
159 SET batch_id = l_ret_tab(i)
160 WHERE time_building_block_id = l_bb_tab(i)
161 AND object_version_number = l_ovn_tab(i)
162 AND request_id = FND_GLOBAL.CONC_request_id;
163
164 -- Updating records touched in an earlier retrieval process
165 -- but having un transferred retro batches.
166 -- We just move them to this retro batch.
167
168 FORALL i IN l_bb_tab.FIRST..l_bb_tab.LAST
169 UPDATE hxc_ret_pay_latest_details
170 SET batch_id = l_ret_tab(i),
171 request_id = FND_GLOBAL.conc_request_id
172 WHERE time_building_block_id = l_bb_tab(i)
173 AND object_version_number = l_ovn_tab(i)
174 AND request_id <> FND_GLOBAL.CONC_request_id
175 AND batch_id = l_old_ret_tab(i)
176 AND pbl_id IS NULL;
177
178 FORALL i IN l_bb_tab.FIRST..l_bb_tab.LAST
179 UPDATE hxc_ret_pay_details
180 SET batch_id = l_ret_tab(i),
181 request_id = FND_GLOBAL.conc_request_id
182 WHERE time_building_block_id = l_bb_tab(i)
183 AND object_version_number = l_ovn_tab(i)
184 AND request_id <> FND_GLOBAL.CONC_request_id
185 AND batch_id = l_old_ret_tab(i)
186 AND pbl_id IS NULL;
187
188
189 END IF;
190
191 g_timid_tab.DELETE;
192 g_ovn_tab.DELETE;
193 g_retro_tab.DELETE;
194
195 END mark_retro_batches;
196
197
198 -- Bug 12919783
199 -- Picks up any outstanding/Held batches for details being updated
200
201 PROCEDURE pick_held_retro_batches(p_tim_id IN NUMBER,
202 p_bb_id IN NUMBER,
203 p_bb_ovn IN NUMBER)
204 IS
205
206 CURSOR get_retro_batches
207 IS SELECT retro_batch_id
208 FROM hxt_det_hours_worked
209 WHERE tim_id = p_tim_id
210 AND pay_status = 'R';
211
212 l_retro_batch NUMBER := 0;
213 BEGIN
214
215
216 -- Just before updating a timecard, we are trying to figure out if there is
217 -- an already existing outstanding Retro batch
218 -- ( One with status Hold in hxt_batch_states, with details all having status 'R' )
219 --
220
221 IF g_debug
222 THEN
223 hr_utility.trace('Trying to Pick up retro for '||p_tim_id);
224 END IF;
225
226 IF NOT g_old_retro_tab.EXISTS(TO_CHAR(p_tim_id))
227 THEN
228
229 IF g_debug
230 THEN
231 hr_utility.trace('Not already cached; Need to query');
232 END IF;
233
234 OPEN get_retro_batches;
235 FETCH get_retro_batches INTO l_retro_batch;
236 CLOSE get_retro_batches;
237
238 g_old_retro_tab(TO_CHAR(p_tim_id)) := l_retro_batch;
239
240 END IF;
241
242 IF g_debug
243 THEN
244 hr_utility.trace('Selected :'||g_old_retro_tab(TO_CHAR(p_tim_id)));
245 END IF;
246
247 -- Record this retro batch against the building block id and OVN
248 g_rdb_index := g_rdb_bb_tab.COUNT;
249 g_rdb_index := g_rdb_index + 1;
250 g_rdb_bb_tab.EXTEND;
251 g_rdb_ovn_tab.EXTEND;
252 g_rdb_retro_tab.EXTEND;
253 g_rdb_bb_tab(g_rdb_index) := p_bb_id;
254 g_rdb_ovn_tab(g_rdb_index) := p_bb_ovn;
255 g_rdb_retro_tab(g_rdb_index) := g_old_retro_tab(TO_CHAR(p_tim_id));
256
257 END pick_held_retro_batches;
258
259
260 -- Bug 12919783
261 -- Updates all the held batches' details' RDB records to
262 -- an older state.
263 -- Explanation inline.
264
265 PROCEDURE update_held_retro_batches
266
267 IS
268
269
270 l_timecards NUMTAB;
271 l_bb_tab NUMTAB;
272 l_ovn_tab NUMTAB;
273 l_retro_batch NUMBER;
274 l_index VARCHAR2(50);
275
276 BEGIN
277
278 -- We already have the retro batch id to look for here against the tbb id, ovn
279 -- Need to do two things.
280 -- If the held retro batch's detail in RDB table was a fresh entry
281 -- ( corresponding update for held retro batch created this summary newly )
282 -- then we need to delete it. The current request will create this record again
283 -- with the updated details.
284
285
286 -- Eg. Time entered like below.
287 --
288 -- Reg 8 8 8 8
289 --
290 -- Xferred to OTLR
291 -- Xferred to BEE
292 --
293 -- Edited in SS to
294 --
295 -- Reg 8 8 8 8 8
296 -- ( Note the new entry)
297 --
298 -- Xferred to OTLR as Retro1
299 --
300 -- Edited in SS again
301 --
302 -- Reg 8 8 8 8 9
303 -- ( Note the new entry)
304 --
305 -- Xferred to OTLR as Retro2
306 --
307 -- Now Retro1 is discarded, and only Retro2 will be used.
308 -- Retro1 had a fresh detail in RDB table, which we need to delete( this has 8 hrs, not exploded).
309 -- Retro2 will insert a fresh detail with 9 hrs, and later Xfer to BEE Retro for Retro2 will take care
310 -- of explosion.
311 --
312
313
314 -- If the held retro batch's detail in RDB table was an edit
315 -- ( Corresponding update for held retro batch updated an already Xferred detail)
316 -- then we need to pull back the updated details to the earlier state.
317 -- The latest entry would be updated by this request.
318
319
320 -- Eg. Time entered like below.
321 --
322 -- Reg 8 8 8 8
323 --
324 -- Xferred to OTLR
325 -- Xferred to BEE
326 --
327 -- Edited in SS to
328 --
329 -- Reg 8 8 8 9
330 -- ( Note the changed entry)
331 --
332 -- Xferred to OTLR as Retro1
333 --
334 -- Edited in SS again
335 --
336 -- Reg 8 8 8 10
337 -- ( Note the new entry)
338 --
339 -- Xferred to OTLR as Retro2
340 --
341 -- Now Retro1 is discarded, and only Retro2 will be used.
342 -- Retro1 had a changed detail in RDB table, which we need to update back to original
343 -- ( this has 9 hrs, not exploded) We will change this to 8 hrs, exploded.
344 -- Retro2 will update the detail with 10 hrs, and later Xfer to BEE Retro for Retro2 will take care
345 -- of explosion.
346 --
347
348
349
350
351 IF g_rdb_bb_tab.COUNT > 0
352 THEN
353 FORALL i IN g_rdb_bb_tab.FIRST..g_rdb_bb_tab.LAST
354 UPDATE hxc_ret_pay_latest_details
355 SET measure = old_measure,
356 attribute1 = old_attribute1,
357 attribute2 = old_attribute2,
358 attribute3 = old_attribute3,
359 request_id = old_request_id,
360 batch_id = old_batch_id,
361 pbl_id = old_pbl_id,
362 old_measure = NULL,
363 old_attribute1 = NULL,
364 old_attribute2 = NULL,
365 old_attribute3 = NULL,
366 old_request_id = NULL,
367 old_batch_id = NULL,
368 old_pbl_id = NULL
369 WHERE time_building_block_id = g_rdb_bb_tab(i)
370 AND batch_id = g_rdb_retro_tab(i)
371 AND pbl_id IS NULL
372 AND old_measure IS NOT NULL
373 AND old_attribute1 IS NOT NULL
374 AND old_attribute2 IS NOT NULL
375 AND old_attribute3 IS NOT NULL;
376
377
378 FORALL i IN g_rdb_bb_tab.FIRST..g_rdb_bb_tab.LAST
379 DELETE FROM hxc_ret_pay_latest_details
380 WHERE time_building_block_id = g_rdb_bb_tab(i)
381 AND batch_id = g_rdb_retro_tab(i)
382 AND pbl_id IS NULL
383 AND old_measure IS NULL
384 AND old_ovn IS NULL;
385
386
387 END IF;
388
389
390 END update_held_retro_batches;
391
392
393
394 /*
395 || Function to identify whether a Timecard, although approved, should get
396 || retrieved today or not (we cannot accept timecards if they were already send
397 || to payroll today as well because of current DT restrictions in OTLR)
398 */
399 -- Bug 12850901
400 -- Added variables to take in tc bb_id and ovn
401 FUNCTION is_retrievable (
402 p_sum_id IN hxt_sum_hours_worked_f.ID%TYPE,
403 p_date_worked IN hxt_sum_hours_worked_f.date_worked%TYPE,
404 p_person_id IN hxt_timecards_f.for_person_id%TYPE,
405 p_tc_bb_id IN NUMBER DEFAULT 0,
406 p_tc_ovn IN NUMBER DEFAULT 0
407 )
408 RETURN BOOLEAN
409 AS
410 l_proc VARCHAR2 (72);
411 l_is_retrievable BOOLEAN := TRUE;
412 l_dt_update_mode VARCHAR2 (256);
413 l_error_message VARCHAR2 (2000);
414 l_return_code NUMBER;
415 l_time_summary_id hxt_det_hours_worked_f.parent_id%TYPE;
416
417 -- Bug 12850901
418 --Added the following variables and cursor.
419 l_timecard_id NUMBER;
420 l_pref_tab hxc_preference_evaluation.t_pref_table;
421 l_start DATE;
422 l_stop DATE;
423 l_do_intg_check VARCHAR2(5) := 'Y';
424
425 CURSOR get_times(p_id IN NUMBER,
426 p_ovn IN NUMBER)
427 IS SELECT start_time,
428 stop_time
429 FROM hxc_time_building_blocks
430 WHERE time_building_block_id = p_id
431 AND object_version_number = p_ovn;
432
433 FUNCTION timecard_id (
434 p_sum_id IN hxt_sum_hours_worked_f.ID%TYPE,
435 p_date_worked IN hxt_sum_hours_worked_f.date_worked%TYPE,
436 p_person_id IN hxt_timecards_f.for_person_id%TYPE
437 )
438 RETURN hxt_timecards_f.ID%TYPE
439 AS
440 l_proc VARCHAR2 (72);
441
442 CURSOR csr_timecard_id_from_sum (
443 p_sum_id hxt_sum_hours_worked_f.ID%TYPE
444 )
445 IS
446 SELECT tim_id
447 FROM hxt_sum_hours_worked_f
448 WHERE ID = p_sum_id;
449
450 CURSOR csr_timecard_id (
451 p_date_worked hxt_sum_hours_worked_f.date_worked%TYPE,
452 p_person_id hxt_timecards_f.for_person_id%TYPE
453 )
454 IS
455 SELECT HTF.ID
456 FROM hxt_timecards_f HTF, per_time_periods ptp
457 WHERE HTF.for_person_id = p_person_id
458 AND HTF.time_period_id = ptp.time_period_id
459 AND TRUNC (p_date_worked) BETWEEN TRUNC (ptp.start_date)
460 AND TRUNC (ptp.end_date);
461
462 l_timecard_id hxt_timecards_f.ID%TYPE;
463 BEGIN
464
465
466 IF g_debug
467 THEN
468 l_proc := g_package || 'timecard_id';
469 hr_utility.set_location ('Entering: ' || l_proc, 10);
470 END IF;
471
472 IF (p_sum_id IS NOT NULL)
473 THEN
474 OPEN csr_timecard_id_from_sum (p_sum_id);
475
476 FETCH csr_timecard_id_from_sum
477 INTO l_timecard_id;
478
479 CLOSE csr_timecard_id_from_sum;
480 ELSE
481 OPEN csr_timecard_id (p_date_worked, p_person_id);
482
483 FETCH csr_timecard_id
484 INTO l_timecard_id;
485
486 CLOSE csr_timecard_id;
487 END IF;
488
489 IF g_debug
490 THEN
491 hr_utility.set_location ( 'Leaving: '
492 || l_proc
493 || ' returning timecard_id = '
494 || l_timecard_id,
495 100
496 );
497 END IF;
498
499 RETURN l_timecard_id;
500 END timecard_id;
501 /*
502 || MAIN
503 */
504 BEGIN
505 g_debug := hr_utility.debug_enabled;
506
507 IF g_debug
508 THEN
509 l_proc := g_package || 'is_retrievable';
510 hr_utility.set_location ( 'Entering: '
511 || l_proc
512 || ' (p_sum_id IN = '
513 || p_sum_id
514 || ')',
515 10
516 );
517
518 hr_utility.trace('p_person_id '||p_person_id);
519 hr_utility.trace('p_date_worked '||p_date_worked);
520 hr_utility.trace('p_sum_id '||p_sum_id);
521 hr_utility.trace('p_tc_bb_id '||p_tc_bb_id);
522 hr_utility.trace('p_tc_ovn '||p_tc_ovn);
523
524 END IF;
525
526
527 -- If Integration preference is picked up already for this timecard,
528 -- verify that.
529 IF g_intg_pref_tab.EXISTS(p_tc_bb_id)
530 AND g_intg_pref_tab(p_tc_bb_id) = 1
531 THEN
532 fnd_message.set_name ('HXC','HXC_PROJ_PAY_NO_OTLR');
533 g_intg_error := fnd_message.get;
534 l_is_retrievable := FALSE;
535 RETURN l_is_retrievable;
536 ELSIF g_intg_pref_tab.EXISTS(p_tc_bb_id)
537 AND g_intg_pref_tab(p_tc_bb_id) = 0
538 THEN
539 IF g_debug
540 THEN
541 hr_utility.trace('For this timecard, integration is disabled, so neednt check again ');
542 END IF;
543 l_do_intg_check := 'N';
544 END IF;
545
546
547 l_timecard_id := timecard_id (p_sum_id,
548 p_date_worked,
549 p_person_id);
550
551 -- If there is no timecard,
552 IF l_timecard_id IS NULL
553 THEN
554
555 IF NOT g_intg_pref_tab.EXISTS(p_tc_bb_id)
556 THEN
557 OPEN get_times(p_tc_bb_id,
558 p_tc_ovn);
559 FETCH get_times INTO l_start,
560 l_stop;
561 CLOSE get_times;
562
563
564 IF g_debug
565 THEN
566 hr_utility.trace('Start -'||l_start);
567 hr_utility.trace('Stop - '||l_stop);
568 END IF;
569
570 hxc_preference_evaluation.resource_preferences(p_person_id,
571 l_start,
572 l_stop,
573 l_pref_tab);
574
575 g_intg_pref_tab(p_tc_bb_id) := 0;
576 IF l_pref_tab.COUNT > 0
577 THEN
578 FOR i IN l_pref_tab.FIRST..l_pref_tab.LAST
579 LOOP
580
581 IF g_debug
582 THEN
583 hr_utility.trace(' Preference - '||l_pref_tab(i).preference_code);
584 hr_utility.trace(' Attribute1 - '||l_pref_tab(i).attribute1);
585 hr_utility.trace(' Attribute2 - '||l_pref_tab(i).attribute2);
586 hr_utility.trace(' Attribute3 - '||l_pref_tab(i).attribute3);
587 hr_utility.trace(' Attribute4 - '||l_pref_tab(i).attribute4);
588 hr_utility.trace(' Attribute5 - '||l_pref_tab(i).attribute5);
589 END IF;
590
591 -- If this is our preference, check if it is set.
592 -- If yes, return TRUE, and set the global variable.
593
594 IF l_pref_tab(i).preference_code = 'TS_PA_PAY_INTG'
595 AND l_pref_tab(i).attribute1 = 'Y'
596 THEN
597 IF g_debug
598 THEN
599 hr_utility.trace(' Integration is enabled ');
600 END IF;
601 -- Integration enabled. Need to raise error.
602 g_intg_pref_tab(p_tc_bb_id) := 1;
603 END IF;
604 END LOOP;
605 END IF;
606
607 END IF;
608
609 -- Set the integration error, and return.
610 IF g_intg_pref_tab(p_tc_bb_id) = 1
611 THEN
612 fnd_message.set_name ('HXC','HXC_PROJ_PAY_NO_OTLR');
613 g_intg_error := fnd_message.get;
614 l_is_retrievable := FALSE;
615 RETURN l_is_retrievable;
616 END IF;
617 END IF;
618
619
620
621 hxt_td_util.retro_restrict_edit
622 (p_tim_id => l_timecard_id,
623 p_session_date => SYSDATE,
624 o_dt_update_mod => l_dt_update_mode,
625 o_error_message => l_error_message,
626 o_return_code => l_return_code,
627 p_do_intg_check => l_do_intg_check
628 );
629 hr_utility.set_location ('l_dt_update_mode = ' || l_dt_update_mode, 11);
630 hr_utility.set_location ('l_error_message = ' || l_error_message, 12);
631 hr_utility.set_location ('l_return_code = ' || l_return_code, 13);
632
633 -- If we got this error message, need to set g_intg_error.
634 IF l_error_message IS NOT NULL
635 THEN
636 g_intg_error := l_error_message;
637 END IF;
638
639 IF (l_dt_update_mode IS NULL)
640 THEN
641 IF g_debug
642 THEN
643 hr_utility.set_location
644 ( ' This line is not retrievable (p_sum_id = '
645 || p_sum_id
646 || ')',
647 20
648 );
649 END IF;
650
651 l_is_retrievable := FALSE;
652 ELSE
653 l_is_retrievable := TRUE;
654 END IF;
655
656 IF g_debug
657 THEN
658 hr_utility.set_location ('Leaving: ' || l_proc, 100);
659 END IF;
660
661 RETURN l_is_retrievable;
662 END is_retrievable;
663
664 --
665 --
666 -------------------------- get_employee_number -----------------------------
667 --
668 FUNCTION get_employee_number (
669 p_person_id IN NUMBER,
670 p_effective_date IN DATE
671 )
672 RETURN VARCHAR2
673 IS
674 -- local vars
675 l_employee_number VARCHAR2 (30);
676 l_full_name VARCHAR2 (240);
677 BEGIN
678 g_debug := hr_utility.debug_enabled;
679
680 --
681 IF g_debug
682 THEN
683 hr_utility.set_location
684 ('HXT_OTC_RETRIEVAL_INTERFACE.get_employee_number',
685 1
686 );
687 END IF;
688
689 --
690 BEGIN
691 SELECT employee_number, full_name
692 INTO l_employee_number, l_full_name
693 FROM per_people_f
694 WHERE person_id = p_person_id
695 AND p_effective_date BETWEEN effective_start_date
696 AND effective_end_date;
697 EXCEPTION
698 WHEN NO_DATA_FOUND
699 THEN
700 --
701 g_status := 'ERRORS';
702 fnd_message.set_name ('HXC', 'HXC_HXT_RET_NO_EMP_NUMBER');
703 fnd_message.set_token ('PERSON_NAME', g_full_name);
704 g_exception_description := SUBSTR (fnd_message.get, 1, 2000);
705 RAISE e_record_error;
706 --
707 RETURN (NULL);
708 END;
709
710 --
711 IF g_debug
712 THEN
713 hr_utility.set_location
714 ('HXT_OTC_RETRIEVAL_INTERFACE.employee_number',
715 2
716 );
717 hr_utility.TRACE ('Employee Number is ' || l_employee_number);
718 hr_utility.TRACE ('Full Name is ' || l_full_name);
719 END IF;
720
721 --
722 RETURN (l_employee_number);
723 --
724 END get_employee_number;
725
726 --
727 --------------------------- get_assignment_id ---------------------------
728 --
729 PROCEDURE get_assignment_id (
730 p_person_id IN NUMBER,
731 p_payroll_id OUT NOCOPY NUMBER,
732 p_bg_id OUT NOCOPY NUMBER,
733 p_assignment_id OUT NOCOPY NUMBER,
734 p_effective_date IN DATE
735 )
736 IS
737 BEGIN
738 --
739 BEGIN
740 --
741 SELECT paf.payroll_id, paf.business_group_id, paf.assignment_id
742 INTO p_payroll_id, p_bg_id, p_assignment_id
743 FROM per_all_assignments_f paf
744 WHERE paf.person_id = p_person_id
745 AND p_effective_date BETWEEN paf.effective_start_date
746 AND paf.effective_end_date
747 AND paf.assignment_type = 'E'
748 AND paf.primary_flag = 'Y';
749 EXCEPTION
750 WHEN NO_DATA_FOUND
751 THEN
752 g_status := 'ERRORS';
753 fnd_message.set_name ('HXC', 'HXC_HRPAY_RET_NO_ASSIGN');
754 fnd_message.set_token ('PERSON_NAME', g_full_name);
755 g_exception_description := SUBSTR (fnd_message.get, 1, 2000);
756 RAISE e_record_error;
757 RETURN;
758 END;
759 --
760 END get_assignment_id;
761
762 --
763 ------------------------- find_existing_timecard ---------------------------
764 --
765 PROCEDURE find_existing_timecard (
766 p_payroll_id IN NUMBER,
767 p_date_worked IN DATE,
768 p_person_id IN NUMBER,
769 p_old_ovn IN NUMBER DEFAULT NULL,
770 p_bb_id IN NUMBER DEFAULT NULL,
771 p_time_summary_id OUT NOCOPY NUMBER,
772 p_time_sum_start_date OUT NOCOPY DATE,
773 p_time_sum_end_date OUT NOCOPY DATE,
774 p_tim_id OUT NOCOPY NUMBER
775 )
776 IS
777 --
778 l_time_period_id NUMBER (15);
779 l_start_date DATE;
780 l_end_date DATE;
781 --
782 BEGIN
783 --
784 BEGIN
785 --
786 SELECT time_period_id, start_date, end_date
787 INTO l_time_period_id, l_start_date, l_end_date
788 FROM per_time_periods
789 WHERE payroll_id = p_payroll_id
790 AND TRUNC (p_date_worked) BETWEEN TRUNC (start_date)
791 AND TRUNC (end_date);
792
793 SELECT hshw.ID, hshw.effective_start_date, hshw.effective_end_date,
794 hshw.tim_id
795 INTO p_time_summary_id, p_time_sum_start_date, p_time_sum_end_date,
796 p_tim_id
797 FROM hxt_timecards_f HTF, hxt_sum_hours_worked hshw
798 WHERE HTF.for_person_id = p_person_id
799 AND HTF.payroll_id = p_payroll_id
800 AND HTF.time_period_id = l_time_period_id
801 AND HTF.effective_end_date = hr_general.end_of_time
802 AND HTF.ID = hshw.tim_id
803 AND hshw.time_building_block_id = p_bb_id
804 -- AND hshw.time_building_block_ovn = p_old_ovn
805 AND TRUNC (hshw.date_worked) = TRUNC (p_date_worked);
806 EXCEPTION
807 WHEN NO_DATA_FOUND
808 THEN
809 p_time_summary_id := NULL;
810 p_time_sum_start_date := NULL;
811 p_time_sum_end_date := NULL;
812 --
813 -- g_status := 'ERRORS';
814 -- fnd_message.set_name('HXC', 'HXC_HXT_RET_NO_TIMECARD');
815 -- fnd_message.set_token('PERSON_NAME', g_full_name);
816 -- g_exception_description := SUBSTR(fnd_message.get,1,2000);
817 -- raise e_record_error;
818 --
819 WHEN TOO_MANY_ROWS
820 THEN
821 g_status := 'ERRORS';
822 fnd_message.set_name ('HXC', 'HXC_HXT_CANNOT_UPDATE');
823 g_exception_description := SUBSTR (fnd_message.get, 1, 2000);
824 RAISE e_record_error;
825 RETURN;
826 END;
827 --
828 END find_existing_timecard;
829
830 -- In the case where an excpetion is thrown for a bb_id without processing
831 -- the attibutes, this funciton is used to maintain the p_last_att_index
832 -- index
833 FUNCTION sync_attributes (
834 p_att_table IN hxc_generic_retrieval_pkg.t_time_attribute,
835 p_bb_id IN NUMBER,
836 p_last_att_index IN BINARY_INTEGER
837 )
838 RETURN VARCHAR2
839 IS
840 l_att_index BINARY_INTEGER;
841 BEGIN
842 IF (p_att_table.COUNT > 0)
843 THEN
844 l_att_index := NVL (p_last_att_index, p_att_table.FIRST);
845
846 LOOP
847 EXIT WHEN ( (NOT p_att_table.EXISTS (l_att_index))
848 OR (p_att_table (l_att_index).bb_id <> p_bb_id)
849 );
850 l_att_index := p_att_table.NEXT (l_att_index);
851 END LOOP;
852 ELSE
853 l_att_index := p_last_att_index;
854 END IF;
855
856 RETURN l_att_index;
857 EXCEPTION
858 WHEN OTHERS
859 THEN
860 hr_utility.trace(dbms_utility.format_error_backtrace);
861 hr_utility.TRACE ('exception is sync atts ' || SQLERRM);
862 RAISE;
863 END sync_attributes;
864
865 --
866 --------------------------- get_attributes -------------------------------
867 --
868 PROCEDURE get_attributes (
869 p_att_table IN hxc_generic_retrieval_pkg.t_time_attribute,
870 p_bb_id IN NUMBER,
871 p_field_name OUT NOCOPY t_field_name,
872 p_value OUT NOCOPY t_value,
873 p_context OUT NOCOPY t_field_name,
874 p_category OUT NOCOPY t_field_name,
875 p_last_att_index IN OUT NOCOPY BINARY_INTEGER,
876 p_element_type_id OUT NOCOPY NUMBER
877 )
878 IS
879 l_att_index BINARY_INTEGER;
880 l_bld_blk_id hxc_time_building_blocks.time_building_block_id%TYPE;
881 l_bb_id_changed BOOLEAN := FALSE;
882 BEGIN
883 --
884 -- Get the attributes of the detail record - element name, input values
885 --
886 IF g_debug
887 THEN
888 hr_utility.TRACE ('------ Start get_Attributes ------');
889 END IF;
890
891 --
892 IF p_att_table.COUNT <> 0
893 THEN
894 --
895 IF g_debug
896 THEN
897 hr_utility.TRACE (' att table not empty');
898 END IF;
899
900 l_att_index := NVL (p_last_att_index, p_att_table.FIRST);
901
902 IF g_debug
903 THEN
904 hr_utility.TRACE (' RM 2');
905 END IF;
906
907 l_bld_blk_id := p_att_table (l_att_index).bb_id;
908
909 IF g_debug
910 THEN
911 hr_utility.TRACE (' RM 3');
912 END IF;
913 --
914 ELSE
915 --
916 RETURN;
917 --
918 END IF;
919
920 --
921 IF g_debug
922 THEN
923 hr_utility.TRACE ('------ Middle get_Attributes ------');
924 END IF;
925
926 --
927 -- sanity check to make sure we are in sync
928 --
929 IF (l_bld_blk_id <> p_bb_id)
930 THEN
931 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
932 fnd_message.set_token ('PROCEDURE', 'get_attribute');
933 fnd_message.set_token ('STEP', 'bld blk mismatch');
934 fnd_message.raise_error;
935 END IF;
936
937 --
938 IF p_att_table.COUNT <> 0
939 THEN
940 WHILE ((l_att_index IS NOT NULL) AND (NOT l_bb_id_changed))
941 LOOP
942 IF g_debug
943 THEN
944 hr_utility.TRACE ('------ In Attribute Loop ------');
945 END IF;
946
947 p_field_name (l_att_index) := p_att_table (l_att_index).field_name;
948
949 IF g_debug
950 THEN
951 hr_utility.TRACE ( 'p_field_name(l_att_index) is '
952 || p_field_name (l_att_index)
953 );
954 END IF;
955
956
957 -- Copying the element id into a variable for later
958 -- use.
959
960 IF p_field_name(l_att_index) = 'DUMMY ELEMENT CONTEXT'
961 THEN
962 p_element_type_id := TO_NUMBER(REPLACE(p_att_table (l_att_index).VALUE,'ELEMENT - '));
963 END IF;
964
965 p_value (l_att_index) := p_att_table (l_att_index).VALUE;
966
967 IF g_debug
968 THEN
969 hr_utility.TRACE ( 'p_value(l_att_index) is '
970 || p_value (l_att_index)
971 );
972 END IF;
973
974 p_context (l_att_index) := p_att_table (l_att_index).CONTEXT;
975
976 IF g_debug
977 THEN
978 hr_utility.TRACE ( 'p_context(l_att_index) is '
979 || p_context (l_att_index)
980 );
981 END IF;
982
983 p_category (l_att_index) := p_att_table (l_att_index).CATEGORY;
984
985 IF g_debug
986 THEN
987 hr_utility.TRACE ( 'p_category(l_att_index) is '
988 || p_category (l_att_index)
989 );
990 END IF;
991
992 l_att_index := p_att_table.NEXT (l_att_index);
993
994 IF (l_att_index IS NOT NULL)
995 THEN
996 IF (l_bld_blk_id <> p_att_table (l_att_index).bb_id)
997 THEN
998 l_bb_id_changed := TRUE;
999 p_last_att_index := l_att_index;
1000 END IF;
1001 END IF;
1002 END LOOP;
1003 END IF;
1004 END get_attributes;
1005
1006 --
1007 --
1008 -------------------------- get_element_name ------------------------------
1009 --
1010 FUNCTION get_element_name (p_ele_type_id IN NUMBER, p_effective_date IN DATE)
1011 RETURN VARCHAR2
1012 IS
1013 -- local vars
1014 l_element_name VARCHAR2 (80);
1015 BEGIN
1016 --
1017 IF g_debug
1018 THEN
1019 hr_utility.set_location ('get_element_name', 1);
1020 END IF;
1021
1022 --
1023 BEGIN
1024 SELECT petl.element_name
1025 INTO l_element_name
1026 FROM pay_element_types_f pet, pay_element_types_f_tl petl
1027 WHERE pet.element_type_id = p_ele_type_id
1028 AND petl.element_type_id = pet.element_type_id
1029 AND USERENV ('LANG') = petl.LANGUAGE
1030 AND p_effective_date BETWEEN pet.effective_start_date
1031 AND pet.effective_end_date;
1032 EXCEPTION
1033 WHEN NO_DATA_FOUND
1034 THEN
1035 g_status := 'ERRORS';
1036 fnd_message.set_name ('HXC', 'HXC_HRPAY_RET_NO_ELE_NAME');
1037 fnd_message.set_token ('ELE_TYPE_ID', p_ele_type_id);
1038 g_exception_description := SUBSTR (fnd_message.get, 1, 2000);
1039 RAISE e_record_error;
1040 RETURN (NULL);
1041 END;
1042
1043 --
1044 IF g_debug
1045 THEN
1046 hr_utility.set_location ('get_element_name', 2);
1047 END IF;
1048
1049 --
1050 RETURN (l_element_name);
1051 --
1052 END get_element_name;
1053
1054 --------------------------- parse_attributes -------------------------------
1055 --
1056 -- Bug 8888777
1057 -- Added new parameter, building block id
1058 PROCEDURE parse_attributes (
1059 p_category IN OUT NOCOPY t_field_name,
1060 p_field_name IN OUT NOCOPY t_field_name,
1061 p_value IN OUT NOCOPY t_value,
1062 p_context IN OUT NOCOPY t_field_name,
1063 p_date_worked OUT NOCOPY DATE,
1064 p_type IN VARCHAR2,
1065 p_measure IN NUMBER,
1066 p_start_time IN DATE,
1067 p_stop_time IN DATE,
1068 p_assignment_id IN NUMBER,
1069 p_earn_policy OUT NOCOPY VARCHAR2, -- Bug 16598207
1070 p_hours OUT NOCOPY NUMBER,
1071 p_hours_type OUT NOCOPY VARCHAR2,
1072 p_segment OUT NOCOPY t_segment,
1073 p_project OUT NOCOPY VARCHAR2,
1074 p_task OUT NOCOPY VARCHAR2,
1075 p_state_name OUT NOCOPY VARCHAR2,
1076 p_county_name OUT NOCOPY VARCHAR2,
1077 p_city_name OUT NOCOPY VARCHAR2,
1078 p_zip_code OUT NOCOPY VARCHAR2,
1079 p_bb_id IN NUMBER DEFAULT 0 -- Bug 8888777
1080 )
1081 IS
1082 l_seg NUMBER (5);
1083 l_element_type_id NUMBER;
1084 l_base_elt_id NUMBER;
1085 l_earn_policy_id NUMBER;
1086 l_retcode NUMBER (9);
1087 c_proc VARCHAR2 (100)
1088 := 'HXT_OTC_RETRIEVAL_INTERFACE.parse_attributes';
1089
1090 CURSOR c_get_base_hours_type (p_earning_policy_id NUMBER)
1091 IS
1092 SELECT egr.element_type_id
1093 FROM hxt_earning_rules egr, hxt_add_elem_info_f aei
1094 WHERE egr.egp_id = p_earning_policy_id
1095 AND aei.element_type_id = egr.element_type_id
1096 AND aei.earning_category = 'REG'
1097 AND egr.egr_type <> 'HOL';
1098
1099 CURSOR c_get_project (p_project_id NUMBER)
1100 IS
1101 SELECT proj.project_number
1102 FROM hxt_all_projects_v proj
1103 WHERE proj.project_id = p_project_id;
1104
1105 CURSOR c_get_task (p_task_id NUMBER)
1106 IS
1107 SELECT task.task_number
1108 FROM hxt_all_tasks_v task
1109 WHERE task.task_id = p_task_id;
1110
1111 l_id_flex_num NUMBER;
1112 --
1113 BEGIN
1114 g_debug := hr_utility.debug_enabled;
1115 pay_paywsqee_pkg.populate_context_items (g_bg_id, l_id_flex_num);
1116
1117 --
1118 -- Initialize 30 costing segments to NULL
1119 --
1120 FOR seg IN 1 .. 30
1121 LOOP
1122 p_segment (seg) := NULL;
1123 END LOOP;
1124
1125 --
1126 IF g_debug
1127 THEN
1128 hr_utility.set_location (c_proc, 10);
1129 END IF;
1130
1131 --
1132 -- If the detail block is of type duration, then the number
1133 -- of hours is in l_measure.
1134 --
1135 IF p_type = 'MEASURE'
1136 THEN
1137 p_hours := p_measure;
1138
1139 --
1140 IF g_debug
1141 THEN
1142 hr_utility.set_location (c_proc, 20);
1143 END IF;
1144 --
1145 END IF;
1146
1147 --
1148 IF g_debug
1149 THEN
1150 hr_utility.set_location (c_proc, 30);
1151 END IF;
1152
1153 --
1154 -- If the detail block is of type range, then the number
1155 -- of hours is derived from the difference between
1156 -- p_start_time and p_stop_time.
1157 --
1158 IF p_type = 'RANGE'
1159 THEN
1160 p_hours := (p_stop_time - p_start_time) * 24;
1161
1162 --
1163 IF g_debug
1164 THEN
1165 hr_utility.set_location (c_proc, 40);
1166 END IF;
1167 --
1168 END IF;
1169
1170 --
1171 IF g_debug
1172 THEN
1173 hr_utility.TRACE ('The Number of Hours is ' || TO_CHAR (p_hours));
1174 hr_utility.set_location (c_proc, 50);
1175 END IF;
1176
1177 --
1178 -- Set up the date earned for the batch line. The
1179 -- date_earned for the time is the date of the start_time.
1180 --
1181 p_date_worked := TRUNC (p_start_time);
1182
1183 --
1184 IF g_debug
1185 THEN
1186 hr_utility.TRACE ( 'p_date_worked is '
1187 || TO_CHAR (p_date_worked, 'DD-MON-YYYY')
1188 );
1189 hr_utility.TRACE ( 'p_start_time is '
1190 || TO_CHAR (p_start_time, 'DD-MON-YYYY HH:MI:SS')
1191 );
1192 END IF;
1193
1194 --
1195 -- Map all other attributes if they exist
1196 --
1197 IF p_category.COUNT <> 0
1198 THEN
1199 --
1200 FOR l_att IN p_category.FIRST .. p_category.LAST
1201 LOOP
1202 --
1203 IF g_debug
1204 THEN
1205 hr_utility.TRACE ('------ In Parse attribute Loop ------');
1206 hr_utility.TRACE ('category is ' || p_category (l_att));
1207 hr_utility.TRACE ('context is ' || p_context (l_att));
1208 hr_utility.TRACE ('field_name is ' || p_field_name (l_att));
1209 hr_utility.TRACE ('value is ' || p_value (l_att));
1210 hr_utility.set_location (c_proc, 200);
1211 END IF;
1212
1213 --
1214 IF UPPER (p_field_name (l_att)) = 'DUMMY ELEMENT CONTEXT'
1215 THEN
1216 l_element_type_id :=
1217 TO_NUMBER (REPLACE (UPPER (p_value (l_att)), 'ELEMENT - '));
1218
1219 IF l_element_type_id IS NOT NULL
1220 THEN
1221 --
1222 l_retcode :=
1223 hxt_tim_col_util.get_earn_pol_id (p_assignment_id,
1224 p_date_worked,
1225 NULL,
1226 l_earn_policy_id
1227 );
1228
1229 --
1230 OPEN c_get_base_hours_type (l_earn_policy_id);
1231
1232 FETCH c_get_base_hours_type
1233 INTO l_base_elt_id;
1234
1235 CLOSE c_get_base_hours_type;
1236
1237 --
1238 IF g_debug
1239 THEN
1240 hr_utility.TRACE
1241 ('---- Before setting the hours type ----');
1242 hr_utility.TRACE ( 'MH assignment id is '
1243 || p_assignment_id
1244 );
1245 hr_utility.TRACE ( 'MH earning policy id'
1246 || l_earn_policy_id
1247 );
1248 hr_utility.TRACE ( 'MH base element type id '
1249 || l_base_elt_id
1250 );
1251 hr_utility.TRACE ( 'MH l_element_type_id '
1252 || l_element_type_id
1253 );
1254 hr_utility.TRACE ('MH p_hours_type ' || p_hours_type);
1255 END IF;
1256
1257 --
1258 IF l_element_type_id = l_base_elt_id
1259 THEN
1260 p_hours_type := NULL;
1261 ELSE
1262 p_hours_type :=
1263 get_element_name (l_element_type_id, p_date_worked);
1264 END IF;
1265
1266 --
1267 IF g_debug
1268 THEN
1269 hr_utility.TRACE
1270 ('---- After setting the hours type ----');
1271 hr_utility.TRACE ('MH p_hours_type ' || p_hours_type);
1272 END IF;
1273 --
1274 END IF;
1275 --
1276 ELSIF UPPER (p_field_name (l_att)) LIKE 'COSTSEGMENT%'
1277 THEN
1278 l_seg :=
1279 TO_NUMBER (REPLACE (UPPER (p_field_name (l_att)),
1280 'COSTSEGMENT'
1281 )
1282 );
1283
1284 IF l_seg <= 30
1285 THEN
1286 --bug 2649003
1287 --change the value of costing from flex_value_id to flex_value for independent value set
1288 IF p_value (l_att) IS NOT NULL
1289 THEN
1290 p_value (l_att) :=
1291 hxt_interface_utilities.costflex_value
1292 (p_id_flex_num => l_id_flex_num,
1293 p_segment_name => 'SEGMENT'
1294 || l_seg,
1295 p_flex_value_id => p_value
1296 (l_att)
1297 );
1298 END IF;
1299
1300 -- bug 2649003 end
1301 p_segment (l_seg) := p_value (l_att);
1302 p_field_name (l_att) := NULL;
1303 p_value (l_att) := NULL;
1304 p_context (l_att) := NULL;
1305 p_category (l_att) := NULL;
1306 END IF;
1307 ELSIF UPPER (p_field_name (l_att)) = 'PROJECT_ID'
1308 THEN
1309 --we need to get the Project number. p_value holds Project ID
1310 OPEN c_get_project (p_value (l_att));
1311
1312 FETCH c_get_project
1313 INTO p_project;
1314
1315 CLOSE c_get_project;
1316 ELSIF UPPER (p_field_name (l_att)) = 'TASK_ID'
1317 THEN
1318 OPEN c_get_task (p_value (l_att));
1319
1320 FETCH c_get_task
1321 INTO p_task;
1322
1323 CLOSE c_get_task;
1324 ELSIF UPPER (p_field_name (l_att)) LIKE 'NA_STATE_NAME'
1325 THEN
1326 p_state_name := p_value (l_att);
1327 ELSIF UPPER (p_field_name (l_att)) LIKE 'NA_COUNTY_NAME'
1328 THEN
1329 p_county_name := p_value (l_att);
1330 ELSIF UPPER (p_field_name (l_att)) LIKE 'NA_CITY_NAME'
1331 THEN
1332 p_city_name := p_value (l_att);
1333 ELSIF UPPER (p_field_name (l_att)) LIKE 'NA_ZIP_CODE'
1334 THEN
1335 p_zip_code := p_value (l_att);
1336 -- Bug 16598207
1337 -- If the attribute encountered is Earning policy, assign to the right OUT variable.
1338 ELSIF UPPER (p_field_name (l_att)) = 'EARNING_POLICY'
1339 THEN
1340 p_earn_policy := p_value (l_att);
1341 END IF;
1342 --
1343 END LOOP;
1344 END IF;
1345
1346 --
1347 IF g_debug
1348 THEN
1349 hr_utility.set_location (c_proc, 90);
1350 END IF;
1351 --
1352 END parse_attributes;
1353
1354 --
1355 --
1356 --------------------------- parse_attributes -------------------------------
1357 --
1358 -- Bug 8888777
1359 -- Added new parameter, building block id
1360
1361 PROCEDURE parse_attributes (
1362 p_category IN OUT NOCOPY t_field_name,
1363 p_field_name IN OUT NOCOPY t_field_name,
1364 p_value IN OUT NOCOPY t_value,
1365 p_context IN OUT NOCOPY t_field_name,
1366 p_date_worked OUT NOCOPY DATE,
1367 p_type IN VARCHAR2,
1368 p_measure IN NUMBER,
1369 p_start_time IN DATE,
1370 p_stop_time IN DATE,
1371 p_assignment_id IN NUMBER,
1372 p_earn_policy OUT NOCOPY VARCHAR2, -- Nug 16598207
1373 p_hours OUT NOCOPY NUMBER,
1374 p_hours_type OUT NOCOPY VARCHAR2,
1375 p_segment OUT NOCOPY t_segment,
1376 --2223669
1377 p_amount OUT NOCOPY NUMBER,
1378 p_hourly_rate OUT NOCOPY NUMBER,
1379 p_rate_multiple OUT NOCOPY NUMBER,
1380 p_project OUT NOCOPY VARCHAR2,
1381 p_task OUT NOCOPY VARCHAR2,
1382 p_state_name OUT NOCOPY VARCHAR2,
1383 p_county_name OUT NOCOPY VARCHAR2,
1384 p_city_name OUT NOCOPY VARCHAR2,
1385 p_zip_code OUT NOCOPY VARCHAR2,
1386 p_bb_id IN NUMBER DEFAULT 0 -- Bug 8888777
1387
1388 )
1389 IS
1390 l_seg NUMBER (5);
1391 l_element_type_id NUMBER;
1392 l_base_elt_id NUMBER;
1393 l_earn_policy_id NUMBER;
1394 l_retcode NUMBER (9);
1395 l_ipv_name VARCHAR2 (80);
1396 l_trans_ipv_name VARCHAR2 (30);
1397 c_proc VARCHAR2 (100)
1398 := 'HXT_OTC_RETRIEVAL_INTERFACE.parse_attributes';
1399
1400 --2223669
1401 CURSOR c_input_value_name (
1402 p_ele_type_id IN NUMBER,
1403 p_ipv_segment IN VARCHAR2
1404 )
1405 IS
1406 SELECT end_user_column_name
1407 FROM fnd_descr_flex_column_usages c, hxc_mapping_components mpc
1408 WHERE c.application_id = 809
1409 AND c.descriptive_flexfield_name = 'OTC Information Types'
1410 AND c.descriptive_flex_context_code =
1411 'ELEMENT - ' || TO_CHAR (p_ele_type_id)
1412 AND c.application_column_name = mpc.SEGMENT
1413 AND UPPER (mpc.field_name) = p_ipv_segment;
1414
1415 CURSOR c_get_base_hours_type (p_earning_policy_id NUMBER)
1416 IS
1417 SELECT egr.element_type_id
1418 FROM hxt_earning_rules egr, hxt_add_elem_info_f aei
1419 WHERE egr.egp_id = p_earning_policy_id
1420 AND aei.element_type_id = egr.element_type_id
1421 AND aei.earning_category = 'REG'
1422 AND egr.egr_type <> 'HOL';
1423
1424 CURSOR c_get_project (p_project_id NUMBER)
1425 IS
1426 SELECT proj.project_number
1427 FROM hxt_all_projects_v proj
1428 WHERE proj.project_id = p_project_id;
1429
1430 CURSOR c_get_task (p_task_id NUMBER)
1431 IS
1432 SELECT task.task_number
1433 FROM hxt_all_tasks_v task
1434 WHERE task.task_id = p_task_id;
1435
1436 l_id_flex_num NUMBER;
1437 --
1438 BEGIN
1439 g_debug := hr_utility.debug_enabled;
1440 pay_paywsqee_pkg.populate_context_items (g_bg_id, l_id_flex_num);
1441
1442 --
1443 -- Initialize 30 costing segments to NULL
1444 --
1445 FOR seg IN 1 .. 30
1446 LOOP
1447 p_segment (seg) := NULL;
1448 END LOOP;
1449
1450 --
1451 IF g_debug
1452 THEN
1453 hr_utility.set_location (c_proc, 10);
1454 END IF;
1455
1456 --
1457 -- If the detail block is of type duration, then the number
1458 -- of hours is in l_measure.
1459 --
1460 IF p_type = 'MEASURE'
1461 THEN
1462 p_hours := p_measure;
1463
1464 --
1465 IF g_debug
1466 THEN
1467 hr_utility.set_location (c_proc, 20);
1468 END IF;
1469 --
1470 END IF;
1471
1472 --
1473 IF g_debug
1474 THEN
1475 hr_utility.set_location (c_proc, 30);
1476 END IF;
1477
1478 --
1479 -- If the detail block is of type range, then the number
1480 -- of hours is derived from the difference between
1481 -- p_start_time and p_stop_time.
1482 --
1483 IF p_type = 'RANGE'
1484 THEN
1485 p_hours := (p_stop_time - p_start_time) * 24;
1486
1487 --
1488 IF g_debug
1489 THEN
1490 hr_utility.set_location (c_proc, 40);
1491 END IF;
1492 --
1493 END IF;
1494
1495 --
1496 IF g_debug
1497 THEN
1498 hr_utility.TRACE ('The Number of Hours is ' || TO_CHAR (p_hours));
1499 hr_utility.set_location (c_proc, 50);
1500 END IF;
1501
1502 --
1503 -- Set up the date earned for the batch line. The
1504 -- date_earned for the time is the date of the start_time.
1505 --
1506 p_date_worked := TRUNC (p_start_time);
1507
1508 --
1509 IF g_debug
1510 THEN
1511 hr_utility.TRACE ( 'p_date_worked is '
1512 || TO_CHAR (p_date_worked, 'DD-MON-YYYY')
1513 );
1514 hr_utility.TRACE ( 'p_start_time is '
1515 || TO_CHAR (p_start_time, 'DD-MON-YYYY HH:MI:SS')
1516 );
1517 END IF;
1518
1519 --
1520 -- Map all other attributes if they exist
1521 --
1522 IF p_category.COUNT <> 0
1523 THEN
1524 --
1525 FOR l_att IN p_category.FIRST .. p_category.LAST
1526 LOOP
1527 --
1528 IF g_debug
1529 THEN
1530 hr_utility.TRACE ('------ In Parse attribute Loop ------');
1531 hr_utility.TRACE ('category is ' || p_category (l_att));
1532 hr_utility.TRACE ('context is ' || p_context (l_att));
1533 hr_utility.TRACE ('field_name is ' || p_field_name (l_att));
1534 hr_utility.TRACE ('value is ' || p_value (l_att));
1535 hr_utility.set_location (c_proc, 200);
1536 END IF;
1537
1538 --
1539 IF UPPER (p_field_name (l_att)) = 'DUMMY ELEMENT CONTEXT'
1540 THEN
1541 l_element_type_id :=
1542 TO_NUMBER (REPLACE (UPPER (p_value (l_att)), 'ELEMENT - '));
1543
1544 IF l_element_type_id IS NOT NULL
1545 THEN
1546 --
1547 l_retcode :=
1548 hxt_tim_col_util.get_earn_pol_id (p_assignment_id,
1549 p_date_worked,
1550 NULL,
1551 l_earn_policy_id
1552 );
1553
1554 --
1555 OPEN c_get_base_hours_type (l_earn_policy_id);
1556
1557 FETCH c_get_base_hours_type
1558 INTO l_base_elt_id;
1559
1560 CLOSE c_get_base_hours_type;
1561
1562 --
1563 IF g_debug
1564 THEN
1565 hr_utility.TRACE
1566 ('---- Before setting the hours type ----');
1567 hr_utility.TRACE ( 'MH assignment id is '
1568 || p_assignment_id
1569 );
1570 hr_utility.TRACE ( 'MH earning policy id'
1571 || l_earn_policy_id
1572 );
1573 hr_utility.TRACE ( 'MH base element type id '
1574 || l_base_elt_id
1575 );
1576 hr_utility.TRACE ( 'MH l_element_type_id '
1577 || l_element_type_id
1578 );
1579 hr_utility.TRACE ('MH p_hours_type ' || p_hours_type);
1580 END IF;
1581
1582 --
1583 IF l_element_type_id = l_base_elt_id
1584 THEN
1585 p_hours_type := NULL;
1586 ELSE
1587 p_hours_type :=
1588 get_element_name (l_element_type_id, p_date_worked);
1589 END IF;
1590
1591 --
1592 IF g_debug
1593 THEN
1594 hr_utility.TRACE
1595 ('---- After setting the hours type ----');
1596 hr_utility.TRACE ('MH p_hours_type ' || p_hours_type);
1597 END IF;
1598 --
1599 END IF;
1600 --
1601 ELSIF UPPER (p_field_name (l_att)) LIKE 'COSTSEGMENT%'
1602 THEN
1603 l_seg :=
1604 TO_NUMBER (REPLACE (UPPER (p_field_name (l_att)),
1605 'COSTSEGMENT'
1606 )
1607 );
1608
1609 IF l_seg <= 30
1610 THEN
1611 --bug 2649003
1612 --change the value of costing from flex_value_id to flex_value for independent value set
1613 IF p_value (l_att) IS NOT NULL
1614 THEN
1615 p_value (l_att) :=
1616 hxt_interface_utilities.costflex_value
1617 (p_id_flex_num => l_id_flex_num,
1618 p_segment_name => 'SEGMENT'
1619 || l_seg,
1620 p_flex_value_id => p_value
1621 (l_att)
1622 );
1623 END IF;
1624
1625 -- bug 2649003 end
1626 p_segment (l_seg) := p_value (l_att);
1627 p_field_name (l_att) := NULL;
1628 p_value (l_att) := NULL;
1629 p_context (l_att) := NULL;
1630 p_category (l_att) := NULL;
1631 END IF;
1632 -- 2223669
1633 ELSIF UPPER (p_field_name (l_att)) LIKE 'INPUTVALUE%'
1634 THEN
1635 OPEN c_input_value_name (l_element_type_id,
1636 p_field_name (l_att)
1637 );
1638
1639 FETCH c_input_value_name
1640 INTO l_ipv_name;
1641
1642 IF (c_input_value_name%FOUND)
1643 THEN
1644 l_trans_ipv_name :=
1645 hxt_batch_process.get_lookup_code (l_ipv_name, SYSDATE);
1646
1647 IF (l_trans_ipv_name = 'AMOUNT')
1648 THEN
1649 IF (p_value (l_att) IS NOT NULL)
1650 AND (NVL (p_hours, 0) <> 0)
1651 THEN
1652 RAISE e_amount_hours;
1653 END IF;
1654
1655 -- Bug 7685797
1656 -- Added FND Number conversions in case the process
1657 -- is run from a resp with Number format 10.000,00
1658 p_amount := FND_NUMBER.CANONICAL_TO_NUMBER(p_value (l_att));
1659 ELSIF (l_trans_ipv_name = 'RATE_MULTIPLE')
1660 THEN
1661 p_rate_multiple := FND_NUMBER.CANONICAL_TO_NUMBER(p_value (l_att));
1662 ELSIF (l_trans_ipv_name = 'HOURLY_RATE')
1663 THEN
1664 p_hourly_rate := FND_NUMBER.CANONICAL_TO_NUMBER(p_value (l_att));
1665 ELSIF (l_trans_ipv_name = 'RATE')
1666 THEN
1667 p_hourly_rate := FND_NUMBER.CANONICAL_TO_NUMBER(p_value (l_att));
1668 ELSE
1669 -- Bug 8888777
1670 -- Added the below code to copy any InputValue to
1671 -- the global table for later retrieval.
1672 -- Would do this only if the Input value is none of the above types.
1673
1674 IF g_debug
1675 THEN
1676 hr_utility.trace('Picking up some configured input value here ');
1677 hr_utility.trace('Field name : '||p_field_name(l_att));
1678 hr_utility.trace('Value : '||p_value(l_att));
1679 END IF;
1680
1681 -- Bug 9774867
1682 -- Added a TO_CHAR conversion to the indexes below.
1683
1684 IF UPPER (p_field_name (l_att)) = 'INPUTVALUE1'
1685 THEN
1686 g_iv_table(TO_CHAR(p_bb_id)).attribute1 := p_value(l_att);
1687 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE2'
1688 THEN
1689 g_iv_table(TO_CHAR(p_bb_id)).attribute2 := p_value(l_att);
1690 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE3'
1691 THEN
1692 g_iv_table(TO_CHAR(p_bb_id)).attribute3 := p_value(l_att);
1693 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE4'
1694 THEN
1695 g_iv_table(TO_CHAR(p_bb_id)).attribute4 := p_value(l_att);
1696 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE5'
1697 THEN
1698 g_iv_table(TO_CHAR(p_bb_id)).attribute5 := p_value(l_att);
1699 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE6'
1700 THEN
1701 g_iv_table(TO_CHAR(p_bb_id)).attribute6 := p_value(l_att);
1702 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE7'
1703 THEN
1704 g_iv_table(TO_CHAR(p_bb_id)).attribute7 := p_value(l_att);
1705 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE8'
1706 THEN
1707 g_iv_table(TO_CHAR(p_bb_id)).attribute8 := p_value(l_att);
1708 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE9'
1709 THEN
1710 g_iv_table(TO_CHAR(p_bb_id)).attribute9 := p_value(l_att);
1711 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE10'
1712 THEN
1713 g_iv_table(TO_CHAR(p_bb_id)).attribute10 := p_value(l_att);
1714 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE11'
1715 THEN
1716 g_iv_table(TO_CHAR(p_bb_id)).attribute11 := p_value(l_att);
1717 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE12'
1718 THEN
1719 g_iv_table(TO_CHAR(p_bb_id)).attribute12 := p_value(l_att);
1720 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE13'
1721 THEN
1722 g_iv_table(TO_CHAR(p_bb_id)).attribute13 := p_value(l_att);
1723 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE14'
1724 THEN
1725 g_iv_table(TO_CHAR(p_bb_id)).attribute14 := p_value(l_att);
1726 ELSIF UPPER (p_field_name (l_att)) = 'INPUTVALUE15'
1727 THEN
1728 g_iv_table(TO_CHAR(p_bb_id)).attribute15 := p_value(l_att);
1729 END IF;
1730
1731 END IF;
1732 END IF;
1733
1734 CLOSE c_input_value_name;
1735 ELSIF UPPER (p_field_name (l_att)) LIKE 'PROJECT_ID'
1736 THEN
1737 --we need to get the Project number. p_value holds Project ID
1738 OPEN c_get_project (p_value (l_att));
1739
1740 FETCH c_get_project
1741 INTO p_project;
1742
1743 CLOSE c_get_project;
1744 ELSIF UPPER (p_field_name (l_att)) LIKE 'TASK_ID'
1745 THEN
1746 OPEN c_get_task (p_value (l_att));
1747
1748 FETCH c_get_task
1749 INTO p_task;
1750
1751 CLOSE c_get_task;
1752 ELSIF UPPER (p_field_name (l_att)) LIKE 'NA_STATE_NAME'
1753 THEN
1754 p_state_name := p_value (l_att);
1755 ELSIF UPPER (p_field_name (l_att)) LIKE 'NA_COUNTY_NAME'
1756 THEN
1757 p_county_name := p_value (l_att);
1758 ELSIF UPPER (p_field_name (l_att)) LIKE 'NA_CITY_NAME'
1759 THEN
1760 p_city_name := p_value (l_att);
1761 ELSIF UPPER (p_field_name (l_att)) LIKE 'NA_ZIP_CODE'
1762 THEN
1763 p_zip_code := p_value (l_att);
1764 -- Bug 16598207
1765 -- If the attribute encountered is Earning policy, assign to the right OUT variable.
1766 ELSIF UPPER (p_field_name (l_att)) = 'EARNING_POLICY'
1767 THEN
1768 p_earn_policy := p_value (l_att);
1769 END IF;
1770 --
1771 END LOOP;
1772 END IF;
1773
1774 --
1775 IF g_debug
1776 THEN
1777 hr_utility.set_location (c_proc, 90);
1778 END IF;
1779 --
1780 END parse_attributes;
1781
1782 --
1783 ------------------------- transfer_to_otm ----------------------------
1784 --
1785 PROCEDURE transfer_to_otm (
1786 p_bg_id IN NUMBER,
1787 p_incremental IN VARCHAR2 DEFAULT 'Y',
1788 p_start_date IN VARCHAR2,
1789 p_end_date IN VARCHAR2,
1790 p_where_clause IN VARCHAR2,
1791 p_transfer_to_bee IN VARCHAR2 DEFAULT 'N',
1792 p_retrieval_transaction_code IN VARCHAR2,
1793 p_batch_ref IN VARCHAR2,
1794 p_no_otm IN OUT NOCOPY VARCHAR2,
1795 p_unique_params IN VARCHAR2,
1796 p_since_date IN VARCHAR2
1797 )
1798 IS
1799 --
1800 -- TYPE t_field_name IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
1801 -- TYPE t_value IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
1802 -- TYPE t_segment IS TABLE OF varchar2(60) INDEX BY BINARY_INTEGER;
1803 --
1804 --
1805 CURSOR get_batch_status (p_tim_id hxt_timecards.ID%TYPE)
1806 IS
1807 SELECT batch_status_cd
1808 FROM hxt_timecards_fmv
1809 WHERE ID = p_tim_id;
1810
1811 l_batch_status_cd hxt_timecards_fmv.batch_status_cd%TYPE;
1812
1813 CURSOR get_debug
1814 IS
1815 SELECT 'X'
1816 FROM hxc_debug
1817 WHERE UPPER (process) = 'HXT_OTC_RETRIEVAL_INTERFACE'
1818 AND TRUNC (debug_date) <= SYSDATE;
1819
1820 CURSOR csr_created_timecards (p_batch_ref VARCHAR2)
1821 IS
1822 SELECT HTF.ID
1823 FROM hxt_timecards_f HTF, pay_batch_headers pbh
1824 WHERE pbh.batch_reference LIKE p_batch_ref || '%'
1825 AND HTF.batch_id = pbh.batch_id
1826 ORDER BY for_person_id, time_period_id;
1827
1828 --
1829 -- global table counts
1830 g_cnt_t_bld_blks NUMBER;
1831 g_cnt_t_attributes NUMBER;
1832 g_cnt_t_detail_bld_blks NUMBER;
1833 g_cnt_t_detail_attributes NUMBER;
1834 g_cnt_t_day_bld_blks NUMBER;
1835 --
1836 g_cnt_t_old_detail_bld_blks NUMBER;
1837 g_cnt_t_old_detail_attributes NUMBER;
1838 g_cnt_t_old_day_bld_blks NUMBER;
1839 --
1840 g_cnt_t_tx_det_bb_id NUMBER;
1841 g_cnt_t_tx_det_status NUMBER;
1842 g_cnt_t_tx_det_exception NUMBER;
1843 -- t_tx_detail_bb_id t_time_building_block_id;
1844 -- t_tx_detail_parent_id t_time_building_block_id;
1845 -- t_tx_detail_bb_ovn t_time_building_block_ovn;
1846 -- t_tx_detail_transaction_id t_transaction_id;
1847 -- t_tx_detail_status t_status;
1848 -- t_tx_detail_exception t_exception_description;
1849 --
1850 -- local tables
1851 l_field_name t_field_name;
1852 l_value t_value;
1853 l_context t_field_name;
1854 l_category t_field_name;
1855 l_segment t_segment;
1856 --
1857 l_old_field_name t_field_name;
1858 l_old_value t_value;
1859 l_old_context t_field_name;
1860 l_old_category t_field_name;
1861 l_old_segment t_segment;
1862 -- local variables
1863 l_last_att_index BINARY_INTEGER;
1864 l_old_last_att_index BINARY_INTEGER;
1865 l_object_version_number NUMBER (9);
1866 l_batch_id NUMBER (15);
1867 l_count_timecards NUMBER (9);
1868 l_max_batches NUMBER
1869 := fnd_profile.VALUE ('HXT_BATCH_SIZE');
1870 -- l_max_batches NUMBER := fnd_profile.Value('OTC_BATCH_SIZE');
1871 l_batch_ref VARCHAR2 (30);
1872 l_batch_name VARCHAR2 (30);
1873 l_non_retro_batch_id NUMBER (15);
1874 l_count_batch_lines NUMBER (9);
1875 l_count_batch_head NUMBER (9);
1876 -- l_retro_batch_ref VARCHAR2(30);
1877 l_retro_batch_name VARCHAR2 (30);
1878 l_retro_batch_id NUMBER (15);
1879 l_retro_count_batch_lines NUMBER (9);
1880 l_retro_count_batch_head NUMBER (9);
1881 --
1882 l_batch_created VARCHAR2 (1) := 'N';
1883 l_retro_batch_created VARCHAR2 (1) := 'N';
1884 --
1885 l_batch_line_id NUMBER (15);
1886 l_retro_batch_request_id NUMBER;
1887 l_batch_process_request_id NUMBER;
1888 --
1889 l_old_bb_index BINARY_INTEGER;
1890 l_bb_id NUMBER (15);
1891 l_ovn NUMBER (9);
1892 l_type VARCHAR2 (30);
1893 l_measure NUMBER;
1894 l_start_time DATE;
1895 l_stop_time DATE;
1896 l_parent_bb_id NUMBER (15);
1897 l_scope VARCHAR2 (30);
1898 l_resource_id NUMBER (15);
1899 l_resource_type VARCHAR2 (30);
1900 l_comment_text VARCHAR2 (2000);
1901 --
1902 l_old_bb_id NUMBER (15);
1903 l_old_ovn NUMBER (9);
1904 l_old_type VARCHAR2 (30);
1905 l_old_measure NUMBER (15);
1906 l_old_start_time DATE;
1907 l_old_stop_time DATE;
1908 l_old_parent_bb_id NUMBER (15);
1909 l_old_scope VARCHAR2 (30);
1910 l_old_resource_id NUMBER (15);
1911 l_old_resource_type VARCHAR2 (30);
1912 l_old_comment_text VARCHAR2 (2000);
1913 --
1914 l_where_clause VARCHAR2 (32000) DEFAULT NULL;
1915 --
1916 l_person_id NUMBER (9);
1917 l_payroll_id NUMBER (9);
1918 l_gre_id NUMBER (9);
1919 l_bg_id NUMBER (9);
1920 l_org_id NUMBER (9);
1921 l_assignment_id NUMBER (9);
1922 l_assignment_number VARCHAR2 (30);
1923 l_effective_date DATE;
1924 --
1925 l_employee_number VARCHAR2 (30);
1926 l_approver_number VARCHAR2 (30);
1927 --
1928 l_errbuf VARCHAR2 (512) DEFAULT NULL;
1929 l_retcode NUMBER (9);
1930 --
1931 l_created_tim_sum_id hxt_sum_hours_worked.ID%TYPE
1932 DEFAULT NULL;
1933 l_otm_error VARCHAR2 (2000) DEFAULT NULL;
1934 l_oracle_error VARCHAR2 (2000) DEFAULT NULL;
1935 --
1936 l_time_summary_id NUMBER;
1937 l_time_sum_start_date DATE;
1938 l_time_sum_end_date DATE;
1939 --
1940 l_earn_policy VARCHAR2 (30);
1941 l_old_earn_policy VARCHAR2 (30);
1942 l_task VARCHAR2 (30);
1943 l_old_task VARCHAR2 (30);
1944 l_task_id NUMBER (15);
1945 l_old_task_id NUMBER (15);
1946 l_hours_type VARCHAR2 (80);
1947 -- Bug 7835456
1948 -- Changed size to 80
1949 l_old_hours_type VARCHAR2 (80);
1950 l_earn_reason_code VARCHAR2 (30);
1951 l_old_earn_reason_code VARCHAR2 (30);
1952 l_project VARCHAR2 (30);
1953 l_old_project VARCHAR2 (30);
1954 l_project_id NUMBER (15);
1955 l_old_project_id NUMBER (15);
1956 l_location VARCHAR2 (30);
1957 l_old_location VARCHAR2 (30);
1958 l_location_id NUMBER (15);
1959 l_old_location_id NUMBER (15);
1960 l_comment VARCHAR2 (30);
1961 l_old_comment VARCHAR2 (30);
1962 l_rate_multiple NUMBER;
1963 l_old_rate_multiple NUMBER;
1964 l_hourly_rate NUMBER;
1965 l_old_hourly_rate NUMBER;
1966 l_amount NUMBER;
1967 l_old_amount NUMBER;
1968 l_sep_check_flag VARCHAR2 (30);
1969 l_old_sep_check_flag VARCHAR2 (30);
1970 l_hours NUMBER;
1971 l_old_hours NUMBER;
1972 l_state_name hxt_sum_hours_worked_f.state_name%TYPE;
1973 l_old_state_name hxt_sum_hours_worked_f.state_name%TYPE;
1974 l_county_name hxt_sum_hours_worked_f.county_name%TYPE;
1975 l_old_county_name hxt_sum_hours_worked_f.county_name%TYPE;
1976 l_city_name hxt_sum_hours_worked_f.city_name%TYPE;
1977 l_old_city_name hxt_sum_hours_worked_f.city_name%TYPE;
1978 l_zip_code hxt_sum_hours_worked_f.zip_code%TYPE;
1979 l_old_zip_code hxt_sum_hours_worked_f.zip_code%TYPE;
1980 --
1981 l_tc_rowid ROWID;
1982 --
1983 l_process_name VARCHAR2 (80);
1984 --
1985 l_element_name VARCHAR2 (80);
1986 l_element_type_id NUMBER (9);
1987 --
1988 l_date_worked DATE;
1989 l_old_date_worked DATE;
1990 l_start_date DATE;
1991 l_end_date DATE;
1992 --
1993 l_changed VARCHAR2 (1) := 'N';
1994 l_deleted VARCHAR2 (1) := 'N';
1995 l_no_times VARCHAR2 (1) := 'N';
1996 l_no_old_times VARCHAR2 (1) := 'N';
1997 --
1998 l_old_att NUMBER;
1999 i VARCHAR2(255);
2000 loop_ok BOOLEAN := TRUE;
2001 l_debug VARCHAR2 (1);
2002 l_dt_update_mode VARCHAR2 (255);
2003 l_return_code NUMBER;
2004 --
2005 -- l_seq NUMBER;
2006 --
2007 e_retrieval_error EXCEPTION;
2008 e_not_retrievable EXCEPTION;
2009 --
2010 c_proc VARCHAR2 (100)
2011 := 'HXT_OTC_RETRIEVAL_INTERFACE.transfer_to_otm';
2012
2013 l_element_id NUMBER;
2014 l_tim_id NUMBER;
2015
2016 -- Bug 12850901
2017 -- New variables to store tc bb id and ovn
2018 l_tc_bb_id NUMBER;
2019 l_tc_ovn NUMBER;
2020
2021 --
2022 --
2023 -------------------------- get_ele_type_id -------------------------------
2024 --
2025 FUNCTION get_ele_type_id (
2026 p_element_name IN VARCHAR2,
2027 p_bg_id IN NUMBER,
2028 p_effective_date IN DATE
2029 )
2030 RETURN NUMBER
2031 IS
2032 -- local vars
2033 l_ele_type_id NUMBER (9);
2034 BEGIN
2035 --
2036 IF g_debug
2037 THEN
2038 hr_utility.set_location
2039 ('HXT_OTC_RETRIEVAL_INTERFACE.get_ele_type_id',
2040 1
2041 );
2042 END IF;
2043
2044 --
2045 BEGIN
2046 SELECT pet.element_type_id
2047 INTO l_ele_type_id
2048 FROM pay_element_types_f pet
2049 WHERE pet.element_name = p_element_name
2050 AND ( pet.business_group_id + 0 = p_bg_id
2051 OR pet.business_group_id IS NULL
2052 )
2053 --and pet.legislation_code = 'US')
2054 --or (pet.business_group_id is null
2055 --and pet.legislation_code is null))
2056 AND p_effective_date BETWEEN pet.effective_start_date
2057 AND pet.effective_end_date;
2058 EXCEPTION
2059 WHEN NO_DATA_FOUND
2060 THEN
2061 --
2062 g_status := 'ERRORS';
2063 fnd_message.set_name ('HXC', 'HXC_HRPAY_RET_NO_ELE_TYPE_ID');
2064 fnd_message.set_token ('ELE_NAME', p_element_name);
2065 g_exception_description := SUBSTR (fnd_message.get, 1, 2000);
2066 RAISE e_record_error;
2067 --
2068 RETURN (NULL);
2069 END;
2070
2071 --
2072 IF g_debug
2073 THEN
2074 hr_utility.set_location
2075 ('HXT_OTC_RETRIEVAL_INTERFACE.get_ele_type_id',
2076 2
2077 );
2078 END IF;
2079
2080 --
2081 RETURN (l_ele_type_id);
2082 --
2083 END get_ele_type_id;
2084
2085 --
2086 ----------------------------- record_time -----------------------------------
2087 --
2088 PROCEDURE record_time (
2089 p_employee_number IN VARCHAR2,
2090 p_approver_number IN VARCHAR2,
2091 p_batch_ref IN VARCHAR2,
2092 p_batch_name IN VARCHAR2,
2093 p_bg_id IN NUMBER,
2094 p_start_time IN DATE,
2095 p_end_time IN DATE,
2096 p_date_worked IN DATE,
2097 p_hours IN NUMBER,
2098 p_earning_policy IN VARCHAR2,
2099 p_hours_type IN VARCHAR2,
2100 p_earn_reason_code IN VARCHAR2,
2101 p_project IN VARCHAR2,
2102 p_task IN VARCHAR2,
2103 p_location IN VARCHAR2,
2104 p_comment IN VARCHAR2,
2105 p_rate_multiple IN NUMBER,
2106 p_hourly_rate IN NUMBER,
2107 p_amount IN NUMBER,
2108 p_sep_check_flag IN VARCHAR2,
2109 p_segment IN t_segment,
2110 p_time_summary_id IN NUMBER DEFAULT NULL,
2111 p_time_sum_start_date IN DATE DEFAULT NULL,
2112 p_time_sum_end_date IN DATE DEFAULT NULL,
2113 p_time_building_block_id IN NUMBER,
2114 p_time_building_block_ovn IN NUMBER,
2115 p_delete IN VARCHAR2,
2116 p_state_name IN VARCHAR2 DEFAULT NULL,
2117 p_county_name IN VARCHAR2 DEFAULT NULL,
2118 p_city_name IN VARCHAR2 DEFAULT NULL,
2119 p_zip_code IN VARCHAR2 DEFAULT NULL
2120 )
2121 IS
2122 --
2123 CURSOR get_timecard_id (p_tim_sum_id NUMBER)
2124 IS
2125 SELECT hshw.tim_id, ht.time_period_id
2126 FROM hxt_sum_hours_worked hshw, hxt_timecards ht
2127 WHERE hshw.ID = p_tim_sum_id AND hshw.tim_id = ht.ID;
2128
2129 --
2130 l_created_tim_sum_id hxt_sum_hours_worked.ID%TYPE DEFAULT NULL;
2131 l_otm_error VARCHAR2 (2000) DEFAULT NULL;
2132 l_oracle_error VARCHAR2 (2000) DEFAULT NULL;
2133 l_time_period_id NUMBER;
2134 l_timecard_id NUMBER;
2135 --
2136 BEGIN
2137 IF g_debug
2138 THEN
2139 hr_utility.TRACE ('---- Before Call to Record Time API ----');
2140 hr_utility.TRACE ('employee_number is ' || p_employee_number);
2141 hr_utility.TRACE ('approver_number is ' || p_approver_number);
2142 hr_utility.TRACE ( 'date_worked is '
2143 || TO_CHAR (p_date_worked, 'DD-MON-YYYY')
2144 );
2145 hr_utility.TRACE ( 'start_time is '
2146 || TO_CHAR (p_start_time,
2147 'DD-MON-YYYY HH:MI:SS')
2148 );
2149 hr_utility.TRACE ( 'end_time is '
2150 || TO_CHAR (p_end_time, 'DD-MON-YYYY HH:MI:SS')
2151 );
2152 hr_utility.TRACE ('hours is ' || TO_CHAR (p_hours));
2153 hr_utility.TRACE ('hours_type is ' || p_hours_type);
2154 hr_utility.TRACE ('earning_policy is ' || p_earning_policy);
2155 hr_utility.TRACE ('project is ' || p_project);
2156 hr_utility.TRACE ('task is ' || p_task);
2157 hr_utility.TRACE ('location is ' || p_location);
2158 hr_utility.TRACE ('rate_multiple is '
2159 || TO_CHAR (p_rate_multiple)
2160 );
2161 hr_utility.TRACE ('hourly_rate is ' || TO_CHAR (p_rate_multiple));
2162 hr_utility.TRACE ('amount is ' || TO_CHAR (p_amount));
2163 hr_utility.TRACE ( 'time_summary_id is '
2164 || TO_CHAR (p_time_summary_id)
2165 );
2166 hr_utility.TRACE ( 'time_sum_start_date is '
2167 || TO_CHAR (p_time_sum_start_date,
2168 'DD-MON-YYYY')
2169 );
2170 hr_utility.TRACE ( 'time_sum_end_date is '
2171 || TO_CHAR (p_time_sum_end_date, 'DD-MON-YYYY')
2172 );
2173 END IF;
2174
2175 --
2176 hxt_time_collection.record_time
2177 (timecard_source => 'Time Store',
2178 batch_ref => p_batch_ref,
2179 batch_name => p_batch_name,
2180 approver_number => p_approver_number,
2181 employee_number => p_employee_number,
2182 date_worked => p_date_worked,
2183 start_time => p_start_time,
2184 end_time => p_end_time,
2185 hours => p_hours,
2186 wage_code => NULL,
2187 earning_policy => p_earning_policy,
2188 hours_type => p_hours_type,
2189 earn_reason_code => p_earn_reason_code,
2190 project => p_project,
2191 task_number => p_task,
2192 location_code => p_location,
2193 COMMENT => p_comment,
2194 rate_multiple => p_rate_multiple,
2195 hourly_rate => p_hourly_rate,
2196 amount => p_amount,
2197 separate_check_flag => p_sep_check_flag,
2198 business_group_id => p_bg_id
2199 -- ,concat_cost_segments =>
2200 ,
2201 cost_segment1 => p_segment (1),
2202 cost_segment2 => p_segment (2),
2203 cost_segment3 => p_segment (3),
2204 cost_segment4 => p_segment (4),
2205 cost_segment5 => p_segment (5),
2206 cost_segment6 => p_segment (6),
2207 cost_segment7 => p_segment (7),
2208 cost_segment8 => p_segment (8),
2209 cost_segment9 => p_segment (9),
2210 cost_segment10 => p_segment (10),
2211 cost_segment11 => p_segment (11),
2212 cost_segment12 => p_segment (12),
2213 cost_segment13 => p_segment (13),
2214 cost_segment14 => p_segment (14),
2215 cost_segment15 => p_segment (15),
2216 cost_segment16 => p_segment (16),
2217 cost_segment17 => p_segment (17),
2218 cost_segment18 => p_segment (18),
2219 cost_segment19 => p_segment (19),
2220 cost_segment20 => p_segment (20),
2221 cost_segment21 => p_segment (21),
2222 cost_segment22 => p_segment (22),
2223 cost_segment23 => p_segment (23),
2224 cost_segment24 => p_segment (24),
2225 cost_segment25 => p_segment (25),
2226 cost_segment26 => p_segment (26),
2227 cost_segment27 => p_segment (27),
2228 cost_segment28 => p_segment (28),
2229 cost_segment29 => p_segment (29),
2230 cost_segment30 => p_segment (30),
2231 time_summary_id => p_time_summary_id,
2232 tim_sum_eff_start_date => p_time_sum_start_date,
2233 tim_sum_eff_end_date => p_time_sum_end_date,
2234 created_by => '-1',
2235 last_updated_by => '-1',
2236 last_update_login => '-1',
2237 -- ,writesum_yn =>
2238 explode_yn => 'N',
2239 delete_yn => p_delete,
2240 dt_update_mode => 'CORRECTION',
2241 created_tim_sum_id => l_created_tim_sum_id,
2242 otm_error => l_otm_error,
2243 oracle_error => l_oracle_error,
2244 p_time_building_block_id => p_time_building_block_id,
2245 p_time_building_block_ovn => p_time_building_block_ovn,
2246 p_validate => FALSE,
2247 p_state_name => p_state_name,
2248 p_county_name => p_county_name,
2249 p_city_name => p_city_name,
2250 p_zip_code => p_zip_code
2251 );
2252
2253 --
2254 IF l_otm_error IS NOT NULL
2255 THEN
2256 --
2257 g_status := 'ERRORS';
2258 fnd_message.set_name ('HXC', 'HXC_HXT_DEP_VAL_OTMERR');
2259 fnd_message.set_token ('ERROR', l_otm_error);
2260 g_exception_description := SUBSTR (fnd_message.get, 1, 2000);
2261 RAISE e_record_error;
2262 --
2263 END IF;
2264
2265 --
2266 IF l_oracle_error IS NOT NULL
2267 THEN
2268 --
2269 g_status := 'ERRORS';
2270 fnd_message.set_name ('HXC', 'HXC_HXT_DEP_VAL_ORAERR');
2271 fnd_message.set_token ('ERROR', l_oracle_error);
2272 g_exception_description := SUBSTR (fnd_message.get, 1, 2000);
2273 RAISE e_record_error;
2274 --
2275 END IF;
2276
2277 --
2278 OPEN get_timecard_id (p_tim_sum_id => l_created_tim_sum_id);
2279
2280 FETCH get_timecard_id
2281 INTO l_timecard_id, l_time_period_id;
2282
2283 CLOSE get_timecard_id;
2284
2285 -- Bug 12919783
2286 -- Record the Tim_id and OVN against the tbb_id for this detail
2287 g_timid_tab(TO_CHAR(p_time_building_block_id)) := l_timecard_id;
2288 g_ovn_tab(TO_CHAR(p_time_building_block_id)) := p_time_building_block_ovn;
2289
2290 -- Bug 8888777
2291 -- Added the following code to update
2292 -- picked up element input values into the summary table.
2293
2294 -- Bug 9774867
2295 -- Added a TO_CHAR conversion to the indexes below.
2296
2297 IF g_iv_table.EXISTS(TO_CHAR(p_time_building_block_id))
2298 THEN
2299 IF g_debug
2300 THEN
2301 hr_utility.trace('There exists some input values, need to update them');
2302 hr_utility.trace(' l_created_sum_id :'||l_created_tim_sum_id);
2303 hr_utility.trace(' bb_id :'||p_time_building_block_id);
2304 hr_utility.trace(' attribute1 : '||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute1 );
2305 hr_utility.trace(' attribute2 : '||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute2 );
2306 hr_utility.trace(' attribute3 : '||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute3 );
2307 hr_utility.trace(' attribute4 : '||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute4 );
2308 hr_utility.trace(' attribute5 : '||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute5 );
2309 hr_utility.trace(' attribute6 : '||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute6 );
2310 hr_utility.trace(' attribute7 : '||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute7 );
2311 hr_utility.trace(' attribute8 : '||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute8 );
2312 hr_utility.trace(' attribute9 : '||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute9 );
2313 hr_utility.trace(' attribute10 :'||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute10);
2314 hr_utility.trace(' attribute11 :'||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute11);
2315 hr_utility.trace(' attribute12 :'||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute12);
2316 hr_utility.trace(' attribute13 :'||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute13);
2317 hr_utility.trace(' attribute14 :'||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute14);
2318 hr_utility.trace(' attribute15 :'||g_iv_table(TO_CHAR(p_time_building_block_id)).attribute15);
2319 END IF;
2320
2321 UPDATE hxt_sum_hours_worked_f
2322 SET attribute1 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute1,
2323 attribute2 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute2,
2324 attribute3 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute3,
2325 attribute4 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute4,
2326 attribute5 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute5,
2327 attribute6 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute6,
2328 attribute7 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute7,
2329 attribute8 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute8,
2330 attribute9 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute9,
2331 attribute10 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute10,
2332 attribute11 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute11,
2333 attribute12 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute12,
2334 attribute13 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute13,
2335 attribute14 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute14,
2336 attribute15 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute15
2337 WHERE id = l_created_tim_sum_id
2338 AND time_building_block_id = p_time_building_block_id
2339 AND time_building_block_ovn = p_time_building_block_ovn ; -- Bug 9159142
2340 END IF;
2341
2342
2343 --
2344 IF g_debug
2345 THEN
2346 hr_utility.TRACE ( 'TIM_SUM_ID IS : '
2347 || TO_CHAR (l_created_tim_sum_id)
2348 );
2349 hr_utility.TRACE ('l_timecard_id is : ' || TO_CHAR (l_timecard_id));
2350 END IF;
2351
2352 --
2353 IF (NOT g_timecards.EXISTS (l_timecard_id))
2354 THEN
2355 g_timecards (l_timecard_id) := l_timecard_id;
2356 END IF;
2357 --
2358 --
2359 END record_time;
2360
2361 --
2362 -------------------------- transfer_to_bee -----------------------------
2363 --
2364 PROCEDURE transfer_to_bee (
2365 p_bg_id IN NUMBER,
2366 p_batch_id IN NUMBER,
2367 p_date_earned IN DATE,
2368 p_batch_ref IN VARCHAR2
2369 )
2370 IS
2371 l_errbuf VARCHAR2 (512) DEFAULT NULL;
2372 l_retcode NUMBER (9);
2373 --
2374 BEGIN
2375 hxt_batch_process.main_process (errbuf => l_errbuf,
2376 retcode => l_retcode,
2377 p_payroll_id => NULL
2378 -- NOT USED
2379 ,
2380 p_date_earned => p_date_earned
2381 -- ,p_time_period_id => l_time_period_id -- DEFAULT NULL
2382 ,
2383 p_from_batch_num => p_batch_id,
2384 p_to_batch_num => p_batch_id,
2385 p_ref_num => p_batch_ref,
2386 p_process_mode => 'V'
2387 -- Validate
2388 ,
2389 p_bus_group_id => p_bg_id
2390 );
2391
2392 --
2393 IF l_retcode <> 0
2394 THEN
2395 NULL;
2396 END IF;
2397
2398 --
2399 IF g_debug
2400 THEN
2401 hr_utility.set_location (c_proc, 7);
2402 END IF;
2403
2404 --
2405 hxt_batch_process.main_process (errbuf => l_errbuf,
2406 retcode => l_retcode,
2407 p_payroll_id => NULL
2408 -- NOT USED
2409 ,
2410 p_date_earned => p_date_earned
2411 -- ,p_time_period_id => l_time_period_id -- DEFAULT NULL
2412 ,
2413 p_from_batch_num => p_batch_id
2414 -- DEFAULT NULL
2415 ,
2416 p_to_batch_num => p_batch_id
2417 -- DEFAULT NULL
2418 ,
2419 p_ref_num => p_batch_ref,
2420 p_process_mode => 'T'
2421 -- Transfer
2422 ,
2423 p_bus_group_id => p_bg_id
2424 );
2425
2426 --
2427 IF l_retcode <> 0
2428 THEN
2429 NULL;
2430 END IF;
2431 --
2432 END transfer_to_bee;
2433
2434 --
2435 ------------------------ transfer_to_bee_retro ---------------------------
2436 --
2437 PROCEDURE transfer_to_bee_retro (
2438 p_bg_id IN NUMBER,
2439 p_retro_batch_id IN NUMBER,
2440 p_date_earned IN DATE,
2441 p_batch_ref IN VARCHAR2
2442 )
2443 IS
2444 l_errbuf VARCHAR2 (512) DEFAULT NULL;
2445 l_retcode NUMBER (9);
2446 --
2447 BEGIN
2448 hxt_retro_process.main_retro (errbuf => l_errbuf,
2449 retcode => l_retcode,
2450 p_payroll_id => NULL
2451 -- NOT USED
2452 ,
2453 p_date_earned => p_date_earned,
2454 p_retro_batch_id => p_retro_batch_id,
2455 p_ref_num => p_batch_ref,
2456 p_process_mode => 'V',
2457 p_bus_group_id => p_bg_id
2458 );
2459 --
2460 hxt_retro_process.main_retro (errbuf => l_errbuf,
2461 retcode => l_retcode,
2462 p_payroll_id => NULL
2463 -- NOT USED
2464 ,
2465 p_date_earned => p_date_earned,
2466 p_retro_batch_id => p_retro_batch_id,
2467 p_ref_num => p_batch_ref,
2468 p_process_mode => 'T',
2469 p_bus_group_id => p_bg_id
2470 );
2471 END transfer_to_bee_retro;
2472
2473 --
2474 --------------------------- create_batch ---------------------------------
2475 --
2476 PROCEDURE create_batch (
2477 p_batch_name IN VARCHAR2,
2478 p_batch_ref IN VARCHAR2,
2479 p_batch_source IN VARCHAR2,
2480 p_batch_id OUT NOCOPY NUMBER,
2481 p_bg_id IN NUMBER,
2482 p_session_date IN DATE
2483 )
2484 IS
2485 l_object_version_number NUMBER (9);
2486 BEGIN
2487 pay_batch_element_entry_api.create_batch_header
2488 (p_validate => FALSE,
2489 p_session_date => p_session_date,
2490 p_batch_name => p_batch_name,
2491 p_batch_status => 'U'
2492 -- DEFAULT 'U'
2493 ,
2494 p_business_group_id => p_bg_id,
2495 p_action_if_exists => 'R'
2496 -- DEFAULT 'R'
2497 ,
2498 p_batch_reference => p_batch_ref,
2499 p_batch_source => p_batch_source,
2500 p_comments => NULL
2501 -- DEFAULT NULL
2502 ,
2503 p_date_effective_changes => 'C'
2504 -- DEFAULT 'C'
2505 ,
2506 p_purge_after_transfer => 'N'
2507 -- DEFAULT 'N'
2508 ,
2509 p_reject_if_future_changes => 'Y'
2510 -- DEFAULT 'Y'
2511 ,
2512 p_batch_id => p_batch_id,
2513 p_object_version_number => l_object_version_number
2514 );
2515 EXCEPTION
2516 WHEN OTHERS
2517 THEN
2518 hr_utility.trace(dbms_utility.format_error_backtrace);
2519 g_status := 'ERRORS';
2520 fnd_message.set_name ('HXC', 'HXC_HRPAY_RET_BATCH_HDR_API');
2521 g_exception_description := SUBSTR (fnd_message.get, 1, 2000);
2522 RAISE e_retrieval_error;
2523 END create_batch;
2524
2525 --
2526 --------------------------- get_assignment_info ---------------------------
2527 --
2528 PROCEDURE get_assignment_info (
2529 p_person_id IN NUMBER,
2530 p_payroll_id OUT NOCOPY NUMBER,
2531 p_bg_id OUT NOCOPY NUMBER,
2532 p_assignment_id OUT NOCOPY NUMBER,
2533 p_effective_date IN DATE
2534 )
2535 IS
2536 BEGIN
2537 --
2538 BEGIN
2539 --
2540 SELECT paf.payroll_id, paf.business_group_id, paf.assignment_id
2541 INTO p_payroll_id, p_bg_id, p_assignment_id
2542 FROM per_all_assignments_f paf
2543 WHERE paf.person_id = p_person_id
2544 AND p_effective_date BETWEEN paf.effective_start_date
2545 AND paf.effective_end_date
2546 AND paf.assignment_type = 'E'
2547 AND paf.primary_flag = 'Y';
2548 EXCEPTION
2549 WHEN NO_DATA_FOUND
2550 THEN
2551 g_status := 'ERRORS';
2552 fnd_message.set_name ('HXC', 'HXC_HRPAY_RET_NO_ASSIGN');
2553 fnd_message.set_token ('PERSON_NAME', g_full_name);
2554 g_exception_description := SUBSTR (fnd_message.get, 1, 2000);
2555 RAISE e_record_error;
2556 RETURN;
2557 END;
2558 --
2559 END get_assignment_info;
2560
2561 --
2562 --------------------------- get_batch_info -------------------------------
2563 --
2564 FUNCTION get_batch_info (
2565 p_batch_ref IN VARCHAR2,
2566 p_count_header IN OUT NOCOPY NUMBER,
2567 p_count_lines IN OUT NOCOPY NUMBER,
2568 p_retro IN VARCHAR2,
2569 p_created OUT NOCOPY VARCHAR2
2570 )
2571 RETURN VARCHAR2
2572 IS
2573 -- local vars
2574 -- l_batch_ref VARCHAR2(30);
2575 l_batch_name VARCHAR2 (30);
2576 l_retro_batch_id NUMBER (15);
2577 l_batch_lines NUMBER;
2578 BEGIN
2579 --
2580 IF g_debug
2581 THEN
2582 hr_utility.set_location
2583 ('HXT_OTC_RETRIEVAL_INTERFACE.get_batch_info',
2584 1
2585 );
2586 END IF;
2587
2588 --
2589 IF p_retro = 'N'
2590 THEN
2591 BEGIN
2592 SELECT MAX (pbh.batch_name)
2593 INTO l_batch_name
2594 FROM pay_batch_headers pbh
2595 WHERE pbh.batch_reference LIKE p_batch_ref || '%'
2596 AND pbh.batch_reference NOT LIKE '%RETRO%'
2597 AND pbh.batch_status NOT IN ('T', 'TW');
2598 EXCEPTION
2599 WHEN NO_DATA_FOUND
2600 THEN
2601 l_batch_name := NULL;
2602 END;
2603 ELSE
2604 BEGIN
2605 SELECT MAX (pbh.batch_name)
2606 INTO l_batch_name
2607 FROM pay_batch_headers pbh
2608 WHERE pbh.batch_reference LIKE p_batch_ref || '%'
2609 AND pbh.batch_status NOT IN ('T', 'TW');
2610 EXCEPTION
2611 WHEN NO_DATA_FOUND
2612 THEN
2613 l_batch_name := NULL;
2614 END;
2615 END IF;
2616
2617 --
2618 IF g_debug
2619 THEN
2620 hr_utility.set_location
2621 ('HXT_OTC_RETRIEVAL_INTERFACE.get_batch_info',
2622 2
2623 );
2624 END IF;
2625
2626 --
2627 IF l_batch_name IS NOT NULL
2628 THEN
2629 SELECT COUNT (pbl.batch_line_id)
2630 INTO l_batch_lines
2631 FROM pay_batch_lines pbl, pay_batch_headers pbh
2632 WHERE pbh.batch_name = l_batch_name
2633 AND pbl.batch_id = pbh.batch_id;
2634
2635 --
2636 IF g_debug
2637 THEN
2638 hr_utility.set_location
2639 ('HXT_OTC_RETRIEVAL_INTERFACE.get_batch_info',
2640 3
2641 );
2642 END IF;
2643
2644 --
2645 IF l_max_batches > l_batch_lines
2646 THEN
2647 p_count_lines := l_batch_lines;
2648 p_count_header :=
2649 TO_NUMBER (REPLACE (l_batch_name,
2650 REPLACE (p_batch_ref, ' ', '_') || '_'
2651 )
2652 );
2653 p_created := 'Y';
2654 ELSE
2655 p_count_lines := 0;
2656 p_count_header :=
2657 TO_NUMBER (REPLACE (l_batch_name,
2658 REPLACE (p_batch_ref, ' ', '_') || '_'
2659 )
2660 )
2661 + 1;
2662 l_batch_name :=
2663 REPLACE (p_batch_ref, ' ', '_')
2664 || '_'
2665 || TO_CHAR (p_count_header);
2666 p_created := 'N';
2667 END IF;
2668
2669 --
2670 IF g_debug
2671 THEN
2672 hr_utility.set_location
2673 ('HXT_OTC_RETRIEVAL_INTERFACE.get_batch_info',
2674 4
2675 );
2676 END IF;
2677 --
2678 ELSE
2679 l_batch_name :=
2680 REPLACE (p_batch_ref, ' ', '_')
2681 || '_'
2682 || TO_CHAR (p_count_header + 1);
2683 p_created := 'N';
2684 END IF;
2685
2686 --
2687 IF g_debug
2688 THEN
2689 hr_utility.set_location
2690 ('HXT_OTC_RETRIEVAL_INTERFACE.get_batch_info',
2691 5
2692 );
2693 END IF;
2694
2695 --
2696 RETURN (l_batch_name);
2697 --
2698 END get_batch_info;
2699
2700 --
2701 ------------------------------- set_transaction --------------------------
2702 --
2703 PROCEDURE set_transaction (
2704 p_bb_id IN NUMBER,
2705 p_bb_index IN BINARY_INTEGER,
2706 p_status IN VARCHAR2,
2707 p_excep IN VARCHAR2
2708 )
2709 IS
2710 BEGIN
2711 --
2712 IF g_debug
2713 THEN
2714 hr_utility.TRACE ('----- In procedure set_transaction -----');
2715 hr_utility.TRACE ('Setting status for bb_id ' || TO_CHAR (p_bb_id)
2716 );
2717 hr_utility.TRACE ('Status is ' || p_status);
2718 hr_utility.TRACE ('Exception is ' || p_excep);
2719 END IF;
2720
2721 IF (hxc_generic_retrieval_pkg.t_tx_detail_bb_id (p_bb_index) <>
2722 p_bb_id
2723 )
2724 THEN
2725 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2726 fnd_message.set_token ('PROCEDURE', 'set_transaction');
2727 fnd_message.set_token ('STEP', 'status bb id mismatch');
2728 fnd_message.raise_error;
2729 END IF;
2730
2731 hxc_generic_retrieval_pkg.t_tx_detail_status (p_bb_index) := p_status;
2732 hxc_generic_retrieval_pkg.t_tx_detail_exception (p_bb_index) :=
2733 p_excep;
2734
2735 IF g_debug
2736 THEN
2737 hr_utility.TRACE ('----- Done setting status -----');
2738 END IF;
2739 END set_transaction;
2740 --
2741 -------------------------- transfer_to_otm Main --------------------
2742 --
2743 -- Main Procedure
2744 BEGIN
2745 g_debug := hr_utility.debug_enabled;
2746
2747 --
2748 --
2749 --
2750 IF g_debug
2751 THEN
2752 hr_utility.set_location (c_proc, 1);
2753 END IF;
2754
2755 --
2756 g_timecards.DELETE;
2757 p_no_otm := 'N';
2758 g_bg_id := p_bg_id;
2759
2760 --
2761 IF g_debug
2762 THEN
2763 hr_utility.TRACE ('****** Parameters are: ******');
2764 hr_utility.TRACE ('p_bg_id is: ' || TO_CHAR (p_bg_id));
2765 hr_utility.TRACE ('p_incremental is: ' || p_incremental);
2766 hr_utility.TRACE ('p_start_date is: ' || p_start_date);
2767 hr_utility.TRACE ('p_end_date is: ' || p_end_date);
2768 hr_utility.TRACE ( 'p_retrieval_transaction_code is: '
2769 || p_retrieval_transaction_code
2770 );
2771 hr_utility.TRACE ('p_batch_ref is: ' || p_batch_ref);
2772 hr_utility.TRACE ('p_transfer_to_bee is: ' || p_transfer_to_bee);
2773 END IF;
2774
2775 --
2776 IF g_debug
2777 THEN
2778 hr_utility.set_location (c_proc, 10);
2779 END IF;
2780
2781 --
2782 -- Set session date
2783 pay_db_pay_setup.set_session_date (SYSDATE);
2784 l_start_date := fnd_date.canonical_to_date (p_start_date);
2785 l_end_date := fnd_date.canonical_to_date (p_end_date);
2786 --
2787 -- Change it now that OTM is not a time recipient.
2788 --
2789 l_process_name := 'Apply Schedule Rules';
2790
2791 --
2792 IF g_debug
2793 THEN
2794 hr_utility.set_location (c_proc, 20);
2795 END IF;
2796
2797 --
2798 -- Initialize batch counters
2799 --
2800 l_count_batch_lines := 0;
2801 l_count_batch_head := 0;
2802 l_count_timecards := 0;
2803 /*
2804 l_batch_name := get_batch_info(p_batch_ref,
2805 l_count_batch_head,
2806 l_count_batch_lines,
2807 'N',
2808 l_batch_created);
2809 */
2810 l_batch_ref := p_batch_ref || ' X';
2811 l_batch_name := REPLACE (l_batch_ref, ' ', '_') || '_';
2812
2813 --
2814 IF g_debug
2815 THEN
2816 hr_utility.set_location (c_proc, 30);
2817 hr_utility.set_location (c_proc, 40);
2818 END IF;
2819
2820 --
2821 /*
2822 l_retro_count_batch_lines := 0;
2823 l_retro_count_batch_head := 0;
2824 l_retro_batch_name := get_batch_info(p_batch_ref || ' RETRO',
2825 l_retro_count_batch_head,
2826 l_retro_count_batch_lines,
2827 'Y',
2828 l_retro_batch_created);
2829 */
2830 --
2831 IF g_debug
2832 THEN
2833 hr_utility.set_location (c_proc, 50);
2834 hr_utility.set_location (c_proc, 60);
2835 END IF;
2836
2837 --
2838 --
2839 l_where_clause := p_where_clause;
2840
2841 --
2842 IF g_debug
2843 THEN
2844 hr_utility.TRACE ('l_where_clause is: ' || l_where_clause);
2845 hr_utility.set_location (c_proc, 70);
2846 END IF;
2847
2848 --
2849 ---------------------------- Call Generic Retrieval -----------------------
2850 --
2851 IF g_debug
2852 THEN
2853 hr_utility.TRACE ('--- Calling Generic Retrieval ---');
2854 END IF;
2855
2856 --
2857 WHILE (NOT l_no_more_timecards)
2858 LOOP
2859 g_timecards.DELETE;
2860 -- Bug 12919783
2861 -- Need to initialize the rdb plsql tables for next chunk
2862 g_rdb_bb_tab := NUMTAB();
2863 g_rdb_ovn_tab := NUMTAB();
2864 g_rdb_retro_tab := NUMTAB();
2865 l_last_att_index := NULL;
2866 l_old_last_att_index := NULL;
2867 l_old_bb_index := NULL; -- GPM v115.45 WWB 3245991
2868 hxc_generic_retrieval_pkg.execute_retrieval_process
2869 (p_process => l_process_name,
2870 p_transaction_code => p_retrieval_transaction_code,
2871 p_start_date => l_start_date,
2872 p_end_date => l_end_date,
2873 p_incremental => p_incremental,
2874 p_rerun_flag => 'N',
2875 p_where_clause => l_where_clause,
2876 p_scope => 'DAY',
2877 p_clusive => 'IN',
2878 p_unique_params => p_unique_params,
2879 p_since_date => p_since_date
2880 );
2881
2882 --
2883 OPEN get_debug;
2884
2885 FETCH get_debug
2886 INTO l_debug;
2887
2888 IF get_debug%FOUND
2889 THEN
2890 hr_utility.trace_on (NULL, 'RET_OTM');
2891 END IF;
2892
2893 CLOSE get_debug;
2894
2895 --
2896 IF g_debug
2897 THEN
2898 hr_utility.TRACE ('--- Returned from Generic Retrieval ---');
2899 hr_utility.set_location (c_proc, 80);
2900 END IF;
2901
2902 --
2903 ----------------------------- Transfer to BEE -----------------------------
2904 --
2905 -- g_cnt_t_bld_blks := hxc_generic_retrieval_pkg.t_bld_blks.COUNT;
2906 g_cnt_t_attributes := hxc_generic_retrieval_pkg.t_attributes.COUNT;
2907 g_cnt_t_detail_bld_blks :=
2908 hxc_generic_retrieval_pkg.t_detail_bld_blks.COUNT;
2909 g_cnt_t_detail_attributes :=
2910 hxc_generic_retrieval_pkg.t_detail_attributes.COUNT;
2911 g_cnt_t_day_bld_blks :=
2912 hxc_generic_retrieval_pkg.t_day_bld_blks.COUNT;
2913 --
2914 g_cnt_t_old_day_bld_blks :=
2915 hxc_generic_retrieval_pkg.t_old_day_bld_blks.COUNT;
2916 g_cnt_t_old_detail_bld_blks :=
2917 hxc_generic_retrieval_pkg.t_old_detail_bld_blks.COUNT;
2918 g_cnt_t_old_detail_attributes :=
2919 hxc_generic_retrieval_pkg.t_old_detail_attributes.COUNT;
2920 --
2921 g_cnt_t_tx_det_bb_id :=
2922 hxc_generic_retrieval_pkg.t_tx_detail_bb_id.COUNT;
2923 g_cnt_t_tx_det_status :=
2924 hxc_generic_retrieval_pkg.t_tx_detail_status.COUNT;
2925 g_cnt_t_tx_det_exception :=
2926 hxc_generic_retrieval_pkg.t_tx_detail_exception.COUNT;
2927
2928 --
2929 -- FOR l_cnt in 1 .. g_cnt_t_detail_bld_blks LOOP
2930 IF hxc_generic_retrieval_pkg.t_detail_bld_blks.COUNT <> 0
2931 THEN
2932 --
2933 FOR l_cnt IN
2934 hxc_generic_retrieval_pkg.t_detail_bld_blks.FIRST .. hxc_generic_retrieval_pkg.t_detail_bld_blks.LAST
2935 LOOP
2936 BEGIN
2937 l_bb_id :=
2938 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).bb_id;
2939 l_ovn :=
2940 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).ovn;
2941 l_type :=
2942 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).TYPE;
2943 l_measure :=
2944 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).measure;
2945 l_start_time :=
2946 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).start_time;
2947 l_stop_time :=
2948 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).stop_time;
2949 l_parent_bb_id :=
2950 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).parent_bb_id;
2951 l_scope :=
2952 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).SCOPE;
2953 l_resource_id :=
2954 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).resource_id;
2955 l_resource_type :=
2956 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).resource_type;
2957 l_comment_text :=
2958 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).comment_text;
2959 l_changed :=
2960 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).changed;
2961 l_deleted :=
2962 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).deleted;
2963 l_no_times := 'N';
2964 -- Bug 12850901
2965 -- Picking up the tc bb id and OVN too.
2966 l_tc_bb_id :=
2967 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).timecard_bb_id;
2968 l_tc_ovn :=
2969 hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).timecard_ovn;
2970
2971
2972 -- Bug 8888777
2973 -- Clear any left over data.
2974
2975 -- Bug 9774867
2976 -- Added TO_CHAR for the index below.
2977 g_iv_table.DELETE(TO_CHAR(l_bb_id));
2978
2979 --
2980 IF g_debug
2981 THEN
2982 hr_utility.set_location (c_proc, 90);
2983 --
2984 hr_utility.TRACE ('--------------------------------');
2985 hr_utility.TRACE ('In Building Block Loop');
2986 hr_utility.TRACE ('l_type is ' || l_type);
2987 hr_utility.TRACE ('l_measure is ' || l_measure);
2988 hr_utility.TRACE ( 'l_start_time is '
2989 || TO_CHAR (l_start_time,
2990 'DD-MON-YYYY HH:MI:SS'
2991 )
2992 );
2993 hr_utility.TRACE ( 'l_stop_time is '
2994 || TO_CHAR (l_stop_time,
2995 'DD-MON-YYYY HH:MI:SS'
2996 )
2997 );
2998 hr_utility.TRACE ('l_scope is ' || l_scope);
2999 hr_utility.TRACE ( 'l_resource_id is '
3000 || TO_CHAR (l_resource_id)
3001 );
3002 hr_utility.TRACE ('l_resource_type is '
3003 || l_resource_type
3004 );
3005 hr_utility.TRACE ('l_changed is ' || l_changed);
3006 hr_utility.TRACE ('l_deleted is ' || l_deleted);
3007 hr_utility.trace ('OTL: l_old_bb_index is '||l_old_bb_index);
3008 hr_utility.TRACE ('--------------------------------');
3009 END IF;
3010
3011 IF l_scope = 'DETAIL'
3012 THEN
3013 -- Get the start time from the parent block if it is a measure
3014 -- building block, which is a day block
3015 IF l_type = 'MEASURE'
3016 THEN
3017 l_no_times := 'Y';
3018 END IF;
3019
3020 IF l_type = 'MEASURE' AND l_start_time IS NULL
3021 THEN
3022 FOR l_bb_cnt IN
3023 hxc_generic_retrieval_pkg.t_day_bld_blks.FIRST .. hxc_generic_retrieval_pkg.t_day_bld_blks.LAST
3024 LOOP
3025 --
3026 IF (l_parent_bb_id =
3027 hxc_generic_retrieval_pkg.t_day_bld_blks
3028 (l_bb_cnt).bb_id
3029 )
3030 AND (hxc_generic_retrieval_pkg.t_day_bld_blks
3031 (l_bb_cnt).SCOPE =
3032 'DAY'
3033 )
3034 THEN
3035 --
3036 l_start_time :=
3037 hxc_generic_retrieval_pkg.t_day_bld_blks
3038 (l_bb_cnt).start_time;
3039 l_stop_time :=
3040 hxc_generic_retrieval_pkg.t_day_bld_blks
3041 (l_bb_cnt).stop_time;
3042 l_no_times := 'Y';
3043
3044 --
3045 IF g_debug
3046 THEN
3047 hr_utility.TRACE
3048 ( 'l_start_time is '
3049 || TO_CHAR
3050 (l_start_time,
3051 'DD-MON-YYYY HH:MI:SS'
3052 )
3053 );
3054 hr_utility.TRACE
3055 ( 'l_stop_time is '
3056 || TO_CHAR
3057 (l_stop_time,
3058 'DD-MON-YYYY HH:MI:SS'
3059 )
3060 );
3061 END IF;
3062
3063 --
3064 EXIT;
3065 END IF;
3066 END LOOP;
3067 END IF;
3068
3069 --
3070 IF g_debug
3071 THEN
3072 hr_utility.set_location (c_proc, 100);
3073 END IF;
3074
3075 --
3076 IF l_resource_type = 'PERSON'
3077 THEN
3078 l_person_id := l_resource_id;
3079
3080 --
3081 IF g_debug
3082 THEN
3083 hr_utility.TRACE ( 'l_person_id is '
3084 || TO_CHAR (l_person_id)
3085 );
3086 END IF;
3087 --
3088 ELSIF l_resource_type = 'ASSIGNMENT'
3089 THEN
3090 --
3091 l_assignment_id := l_resource_id;
3092
3093 --
3094 IF g_debug
3095 THEN
3096 hr_utility.TRACE ( 'l_assignment_id is '
3097 || TO_CHAR (l_assignment_id)
3098 );
3099 END IF;
3100 --
3101 END IF;
3102
3103 --
3104 IF g_debug
3105 THEN
3106 hr_utility.set_location (c_proc, 110);
3107 END IF;
3108
3109 --
3110 l_effective_date := TRUNC (l_start_time);
3111
3112 IF g_debug
3113 THEN
3114 hr_utility.TRACE ( 'l_effective_date is :'
3115 || TO_CHAR (l_effective_date,
3116 'DD-MON-YYYY'
3117 )
3118 );
3119 END IF;
3120
3121 --
3122 BEGIN
3123 SELECT full_name
3124 INTO g_full_name
3125 FROM per_all_people_f
3126 WHERE person_id = l_person_id
3127 AND l_effective_date BETWEEN effective_start_date
3128 AND effective_end_date;
3129 EXCEPTION
3130 WHEN NO_DATA_FOUND
3131 THEN
3132 hr_utility.set_message
3133 (800,
3134 'HR_52365_PTU_NO_PERSON_EXISTS'
3135 );
3136 l_last_att_index :=
3137 sync_attributes
3138 (p_att_table => hxc_generic_retrieval_pkg.t_detail_attributes,
3139 p_bb_id => l_bb_id,
3140 p_last_att_index => l_last_att_index
3141 );
3142 l_old_last_att_index :=
3143 sync_attributes
3144 (p_att_table => hxc_generic_retrieval_pkg.t_old_detail_attributes,
3145 p_bb_id => l_bb_id,
3146 p_last_att_index => l_old_last_att_index
3147 );
3148 hr_utility.raise_error;
3149 END;
3150
3151 --
3152 -- Get the attributes of the detail record.
3153 --
3154 -- Reset all tables
3155 --
3156 l_field_name.DELETE;
3157 l_value.DELETE;
3158 l_context.DELETE;
3159 l_category.DELETE;
3160 --
3161 get_attributes
3162 (hxc_generic_retrieval_pkg.t_detail_attributes,
3163 l_bb_id,
3164 l_field_name,
3165 l_value,
3166 l_context,
3167 l_category,
3168 l_last_att_index,
3169 l_element_id
3170 );
3171
3172 --
3173 IF g_debug
3174 THEN
3175 hr_utility.set_location (c_proc, 120);
3176 END IF;
3177
3178 --
3179 -- If there is a mapping set up for the assignment_id, get the assignment
3180 -- id from the attribute, else get it from the person id.
3181 -- Also, get the element_name.
3182 --
3183 IF l_field_name.COUNT <> 0
3184 THEN
3185 FOR attr_cnt IN
3186 l_field_name.FIRST .. l_field_name.LAST
3187 LOOP
3188 --
3189 IF UPPER (l_field_name (attr_cnt)) =
3190 'P_ASSIGNMENT_ID'
3191 THEN
3192 l_assignment_id :=
3193 TO_NUMBER (l_value (attr_cnt));
3194 ELSIF UPPER (l_field_name (attr_cnt)) =
3195 'P_ASSIGNMENT_NUMBER'
3196 THEN
3197 l_assignment_number := l_value (attr_cnt);
3198 END IF;
3199 --
3200 END LOOP;
3201 END IF;
3202
3203 --
3204 IF g_debug
3205 THEN
3206 hr_utility.set_location (c_proc, 130);
3207 END IF;
3208
3209 --
3210 -- Get payroll id
3211 --
3212 get_assignment_info (l_person_id,
3213 l_payroll_id,
3214 l_bg_id,
3215 l_assignment_id,
3216 l_effective_date
3217 );
3218
3219 --
3220 IF g_debug
3221 THEN
3222 hr_utility.set_location (c_proc, 140);
3223 hr_utility.TRACE
3224 ('--- After Call to get_assignment_info ---');
3225 hr_utility.TRACE ( 'Person IDs are '
3226 || TO_CHAR (l_person_id)
3227 );
3228 hr_utility.TRACE ( 'Payroll IDs are '
3229 || TO_CHAR (l_payroll_id)
3230 );
3231 hr_utility.TRACE ( 'bg IDs are '
3232 || TO_CHAR (l_bg_id)
3233 || ' AND '
3234 || TO_CHAR (p_bg_id)
3235 );
3236 END IF;
3237
3238 --
3239 IF l_person_id IS NOT NULL AND l_bg_id = p_bg_id
3240 THEN
3241 --
3242 IF g_debug
3243 THEN
3244 hr_utility.set_location (c_proc, 150);
3245 hr_utility.TRACE ('Inside IF');
3246 END IF;
3247
3248 --
3249 -- Get the attributes of the detail record
3250 --
3251 IF g_debug
3252 THEN
3253 hr_utility.TRACE
3254 ( 'g_cnt_t_detail_bld_blks count is '
3255 || TO_CHAR (g_cnt_t_detail_bld_blks)
3256 );
3257 END IF;
3258
3259 --
3260 IF l_changed = 'Y' AND p_incremental = 'Y'
3261 THEN
3262 --
3263 -- Get the old data
3264 --
3265 IF g_debug
3266 THEN
3267 hr_utility.set_location (c_proc, 160);
3268 hr_utility.TRACE
3269 ( 'Get old building block info for bb id: '
3270 || TO_CHAR (l_bb_id)
3271 );
3272 END IF;
3273
3274 l_old_bb_index :=
3275 NVL
3276 (l_old_bb_index,
3277 hxc_generic_retrieval_pkg.t_old_detail_bld_blks.FIRST
3278 );
3279
3280 --
3281 IF g_debug
3282 THEN
3283 hr_utility.TRACE ('Before IF');
3284 hr_utility.TRACE
3285 ( 'g_cnt_t_old_detail_bld_blks count is '
3286 || TO_CHAR
3287 (hxc_generic_retrieval_pkg.t_old_detail_bld_blks.COUNT
3288 )
3289 );
3290 hr_utility.TRACE ( 'l_old_bb_index IS : '
3291 || TO_CHAR (l_old_bb_index)
3292 );
3293 hr_utility.TRACE ( 'l_cnt IS : '
3294 || TO_CHAR (l_cnt)
3295 );
3296 END IF;
3297
3298 --
3299
3300 -- Bug 6621627
3301 -- No functional change here, just moved the IF condition
3302 -- outside the loop. Was looping and the condition being checked
3303 -- each time. Need to loop only if you have trace enabled.
3304
3305 IF g_debug
3306 THEN
3307 FOR i IN
3308 hxc_generic_retrieval_pkg.t_old_detail_bld_blks.FIRST .. hxc_generic_retrieval_pkg.t_old_detail_bld_blks.LAST
3309 LOOP
3310 hr_utility.TRACE
3311 ( 'BB ID IS : '
3312 || TO_CHAR
3313 (hxc_generic_retrieval_pkg.t_old_detail_bld_blks
3314 (i).bb_id
3315 )
3316 );
3317 hr_utility.TRACE ('i is : ' || TO_CHAR (i));
3318 END LOOP;
3319 END IF;
3320
3321
3322
3323 --
3324 -- IF hxc_generic_retrieval_pkg.t_old_detail_bld_blks(l_cnt).bb_id <> l_bb_id
3325 IF hxc_generic_retrieval_pkg.t_old_detail_bld_blks
3326 (l_old_bb_index).bb_id <>
3327 l_bb_id
3328 THEN
3329 IF g_debug
3330 THEN
3331 hr_utility.TRACE
3332 ('in old bb id exception!!!');
3333 END IF;
3334
3335 fnd_message.set_name
3336 ('PAY',
3337 'HR_6153_ALL_PROCEDURE_FAIL'
3338 );
3339 fnd_message.set_token ('PROCEDURE',
3340 'transfer to otm'
3341 );
3342 fnd_message.set_token ('STEP',
3343 'bld blk mismatch'
3344 );
3345 fnd_message.raise_error;
3346 END IF;
3347
3348 --
3349 IF g_debug
3350 THEN
3351 hr_utility.TRACE ('After IF');
3352 END IF;
3353
3354 --
3355 l_old_ovn :=
3356 hxc_generic_retrieval_pkg.t_old_detail_bld_blks
3357 (l_old_bb_index).ovn;
3358 l_old_type :=
3359 hxc_generic_retrieval_pkg.t_old_detail_bld_blks
3360 (l_old_bb_index).TYPE;
3361 l_old_measure :=
3362 hxc_generic_retrieval_pkg.t_old_detail_bld_blks
3363 (l_old_bb_index).measure;
3364 l_old_start_time :=
3365 hxc_generic_retrieval_pkg.t_old_detail_bld_blks
3366 (l_old_bb_index).start_time;
3367 l_old_stop_time :=
3368 hxc_generic_retrieval_pkg.t_old_detail_bld_blks
3369 (l_old_bb_index).stop_time;
3370 l_old_parent_bb_id :=
3371 hxc_generic_retrieval_pkg.t_old_detail_bld_blks
3372 (l_old_bb_index).parent_bb_id;
3373 l_old_scope :=
3374 hxc_generic_retrieval_pkg.t_old_detail_bld_blks
3375 (l_old_bb_index).SCOPE;
3376 l_old_resource_id :=
3377 hxc_generic_retrieval_pkg.t_old_detail_bld_blks
3378 (l_old_bb_index).resource_id;
3379 l_old_resource_type :=
3380 hxc_generic_retrieval_pkg.t_old_detail_bld_blks
3381 (l_old_bb_index).resource_type;
3382 l_old_comment_text :=
3383 hxc_generic_retrieval_pkg.t_old_detail_bld_blks
3384 (l_old_bb_index).comment_text;
3385 l_no_old_times := 'N';
3386
3387 -- Bug 9308216
3388 -- Commenting this out, and doing it at the end of the loop.
3389 --l_old_bb_index := l_old_bb_index + 1;
3390 IF g_debug
3391 THEN
3392 hr_utility.trace('OTL: Old bb index was incremented here -- moved below');
3393 hr_utility.trace('OTL: Old bb index still is '||l_old_bb_index);
3394 END IF;
3395
3396 IF l_old_type = 'MEASURE'
3397 THEN
3398 l_no_old_times := 'Y';
3399 END IF;
3400
3401 --
3402 IF l_old_type = 'MEASURE'
3403 AND l_old_start_time IS NULL
3404 THEN
3405 FOR l_old_bb_cnt IN
3406 hxc_generic_retrieval_pkg.t_old_day_bld_blks.FIRST .. hxc_generic_retrieval_pkg.t_old_day_bld_blks.LAST
3407 LOOP
3408 --
3409 IF g_debug
3410 THEN
3411 hr_utility.set_location (c_proc, 170);
3412 hr_utility.TRACE
3413 ('Get old start and stop times');
3414 END IF;
3415
3416 --
3417 IF (l_old_parent_bb_id =
3418 hxc_generic_retrieval_pkg.t_old_day_bld_blks
3419 (l_old_bb_cnt).bb_id
3420 )
3421 AND hxc_generic_retrieval_pkg.t_old_day_bld_blks
3422 (l_old_bb_cnt).SCOPE =
3423 'DAY'
3424 THEN
3425 --
3426 l_old_start_time :=
3427 hxc_generic_retrieval_pkg.t_old_day_bld_blks
3428 (l_old_bb_cnt).start_time;
3429 l_old_stop_time :=
3430 hxc_generic_retrieval_pkg.t_old_day_bld_blks
3431 (l_old_bb_cnt).stop_time;
3432 l_no_old_times := 'Y';
3433
3434 --
3435 IF g_debug
3436 THEN
3437 hr_utility.TRACE
3438 ( 'l_old_start_time is '
3439 || TO_CHAR
3440 (l_old_start_time,
3441 'DD-MON-YYYY HH:MI:SS'
3442 )
3443 );
3444 hr_utility.TRACE
3445 ( 'l_old_stop_time is '
3446 || TO_CHAR
3447 (l_old_stop_time,
3448 'DD-MON-YYYY HH:MI:SS'
3449 )
3450 );
3451 END IF;
3452
3453 --
3454 EXIT;
3455 END IF;
3456 END LOOP;
3457 END IF;
3458
3459 --
3460 -- Reset all old tables
3461 --
3462 l_old_field_name.DELETE;
3463 l_old_value.DELETE;
3464 l_old_context.DELETE;
3465 l_old_category.DELETE;
3466
3467 --
3468 IF g_debug
3469 THEN
3470 hr_utility.set_location (c_proc, 180);
3471 END IF;
3472
3473 --
3474 -- Get the attributes of the old detail record.
3475 --
3476 IF g_debug
3477 THEN
3478 hr_utility.TRACE
3479 ( 'Get old attributes for bb id: '
3480 || TO_CHAR (l_bb_id)
3481 );
3482 END IF;
3483
3484 --
3485 get_attributes
3486 (hxc_generic_retrieval_pkg.t_old_detail_attributes,
3487 l_bb_id,
3488 l_old_field_name,
3489 l_old_value,
3490 l_old_context,
3491 l_old_category,
3492 l_old_last_att_index,
3493 l_element_id
3494 );
3495
3496 --
3497 IF g_debug
3498 THEN
3499 hr_utility.set_location (c_proc, 190);
3500 END IF;
3501 --
3502 END IF; -- l_changed is Y and p_incremental is Y
3503
3504 --
3505 -- Parse attribute Information.
3506 --
3507 -- Bug 8888777
3508 -- Added a new paramter, building_block_id.
3509 parse_attributes (p_category => l_category,
3510 p_field_name => l_field_name,
3511 p_value => l_value,
3512 p_context => l_context,
3513 p_date_worked => l_date_worked,
3514 p_type => l_type,
3515 p_measure => l_measure,
3516 p_start_time => l_start_time,
3517 p_stop_time => l_stop_time,
3518 p_assignment_id => l_assignment_id,
3519 p_earn_policy => l_earn_policy, -- Bug 16598207
3520 p_hours => l_hours,
3521 p_hours_type => l_hours_type,
3522 p_segment => l_segment,
3523 p_amount => l_amount,
3524 p_hourly_rate => l_hourly_rate,
3525 p_rate_multiple => l_rate_multiple,
3526 p_project => l_project,
3527 p_task => l_task,
3528 p_state_name => l_state_name,
3529 p_county_name => l_county_name,
3530 p_city_name => l_city_name,
3531 p_zip_code => l_zip_code,
3532 p_bb_id => l_bb_id -- Bug 8888777
3533 );
3534
3535 --
3536 IF g_debug
3537 THEN
3538 hr_utility.set_location (c_proc, 210);
3539 hr_utility.set_location (c_proc, 220);
3540 hr_utility.set_location (c_proc, 230);
3541 END IF;
3542
3543 --
3544 IF l_changed = 'Y' AND p_incremental = 'Y'
3545 THEN
3546 --
3547 -- Parse the old attributes
3548 --
3549 IF g_debug
3550 THEN
3551 hr_utility.set_location (c_proc, 240);
3552 hr_utility.TRACE ('Parse old attributes.');
3553 END IF;
3554
3555 --
3556 parse_attributes
3557 (p_category => l_old_category,
3558 p_field_name => l_old_field_name,
3559 p_value => l_old_value,
3560 p_context => l_old_context,
3561 p_date_worked => l_old_date_worked,
3562 p_type => l_old_type,
3563 p_measure => l_old_measure,
3564 p_start_time => l_old_start_time,
3565 p_stop_time => l_old_stop_time,
3566 p_assignment_id => l_assignment_id,
3567 p_earn_policy => l_old_earn_policy, -- Bug 16598207
3568 p_hours => l_old_hours,
3569 p_hours_type => l_old_hours_type,
3570 p_segment => l_old_segment,
3571 p_amount => l_old_amount,
3572 p_hourly_rate => l_old_hourly_rate,
3573 p_rate_multiple => l_old_rate_multiple,
3574 p_project => l_old_project,
3575 p_task => l_old_task,
3576 p_state_name => l_old_state_name,
3577 p_county_name => l_old_county_name,
3578 p_city_name => l_old_city_name,
3579 p_zip_code => l_old_zip_code
3580 );
3581
3582 --
3583 IF g_debug
3584 THEN
3585 hr_utility.set_location (c_proc, 270);
3586 END IF;
3587 --
3588 END IF;
3589
3590 --
3591 -------------------- Create Batch Header --------------------
3592 --
3593 IF g_debug
3594 THEN
3595 hr_utility.set_location (c_proc, 280);
3596 END IF;
3597
3598 --
3599 /*
3600 --
3601 IF l_count_timecards > l_max_batches THEN
3602 --
3603 if g_debug then
3604 hr_utility.set_location(c_proc, 290);
3605 end if;
3606 --
3607 -- Reset counter
3608 l_count_timecards := 0;
3609
3610 -- Increment batch header reference
3611 l_count_batch_head := l_count_batch_head + 1;
3612 --
3613 if g_debug then
3614 hr_utility.set_location(c_proc, 300);
3615 end if;
3616 --
3617 --
3618 ------------------- Transfer To BEE -----------------------
3619 --
3620 if g_debug then
3621 hr_utility.set_location(c_proc, 5);
3622 end if;
3623 --
3624 -- Only transfer if the user has asked for it.
3625 --
3626 IF p_transfer_to_bee = 'Y' THEN
3627 --
3628 if g_debug then
3629 hr_utility.set_location(c_proc, 6);
3630 end if;
3631 --
3632 transfer_to_bee(p_bg_id => p_bg_id,
3633 p_batch_id => l_non_retro_batch_id,
3634 p_date_earned => l_date_worked,
3635 p_batch_ref => l_batch_ref);
3636
3637 END IF;
3638 -- Set up new batch name
3639 l_batch_name := replace(l_batch_ref, ' ', '_') ||
3640 to_char(l_count_batch_head);
3641 --
3642 if g_debug then
3643 hr_utility.set_location(c_proc, 310);
3644 end if;
3645 --
3646 END IF;
3647 --
3648 IF l_retro_count_batch_lines > l_max_batches THEN
3649 --
3650 if g_debug then
3651 hr_utility.set_location(c_proc, 320);
3652 end if;
3653 --
3654 -- Reset retro counter
3655 l_retro_count_batch_lines := 0;
3656 --
3657 -- Increment retro batch header reference
3658 l_retro_count_batch_head := l_retro_count_batch_head + 1;
3659 --
3660 if g_debug then
3661 hr_utility.set_location(c_proc, 330);
3662 end if;
3663 --
3664 ------------------- Transfer To BEE -----------------------
3665 --
3666 if g_debug then
3667 hr_utility.set_location(c_proc, 5);
3668 end if;
3669 --
3670 -- Only transfer if the user has asked for it.
3671 --
3672 IF p_transfer_to_bee = 'Y' THEN
3673 --
3674 if g_debug then
3675 hr_utility.set_location(c_proc, 6);
3676 end if;
3677 --
3678 transfer_to_bee_retro(p_bg_id => p_bg_id,
3679 p_retro_batch_id => l_retro_batch_id,
3680 p_date_earned => l_date_worked,
3681 p_batch_ref => l_batch_ref);
3682 END IF;
3683 --
3684 -- Set up new batch name
3685 l_retro_batch_name := replace(l_batch_ref, ' ', '_') ||
3686 '_RETRO_' ||
3687 to_char(l_retro_count_batch_head);
3688 --
3689 if g_debug then
3690 hr_utility.set_location(c_proc, 340);
3691 end if;
3692 --
3693 END IF;
3694 */
3695 IF g_debug
3696 THEN
3697 hr_utility.set_location (c_proc, 350);
3698 END IF;
3699
3700 --
3701 --------------------- Create Timecard ---------------------
3702 --
3703 IF g_debug
3704 THEN
3705 hr_utility.TRACE
3706 ('---- Before Create Timecard ----');
3707 END IF;
3708
3709 -- Bug 12850901
3710 -- Added two new params.
3711 l_time_summary_id := NULL;
3712 IF (NOT is_retrievable
3713 (p_sum_id => l_time_summary_id,
3714 p_date_worked => l_date_worked,
3715 p_person_id => l_person_id,
3716 p_tc_bb_id => l_tc_bb_id,
3717 p_tc_ovn => l_tc_ovn
3718 )
3719 )
3720 THEN
3721 RAISE e_not_retrievable;
3722 END IF;
3723
3724 IF g_debug
3725 THEN
3726 hr_utility.TRACE ( 'l_employee_number is '
3727 || l_employee_number
3728 );
3729 END IF;
3730
3731 --
3732 -- Null out start and stop times is flag is set.
3733 --
3734 IF l_no_times = 'Y'
3735 THEN
3736 l_start_time := NULL;
3737 l_stop_time := NULL;
3738 END IF;
3739
3740 --
3741 IF l_no_old_times = 'Y'
3742 THEN
3743 l_old_start_time := NULL;
3744 l_old_stop_time := NULL;
3745 END IF;
3746
3747 --
3748 -- Make a retro entry in OTM
3749 --
3750 l_time_summary_id := NULL;
3751 l_time_sum_start_date := NULL;
3752 l_time_sum_end_date := NULL;
3753
3754 --
3755 IF l_changed = 'Y'
3756 THEN
3757 --
3758 IF g_debug
3759 THEN
3760 hr_utility.TRACE
3761 ('---- Making retro timecard entry ----');
3762 hr_utility.TRACE ( 'l_retro_batch_id is '
3763 || TO_CHAR (l_retro_batch_id)
3764 );
3765 END IF;
3766
3767 --
3768 find_existing_timecard
3769 (p_payroll_id => l_payroll_id,
3770 p_date_worked => l_old_date_worked,
3771 p_person_id => l_person_id,
3772 p_old_ovn => l_old_ovn,
3773 p_bb_id => l_bb_id,
3774 p_time_summary_id => l_time_summary_id,
3775 p_time_sum_start_date => l_time_sum_start_date,
3776 p_time_sum_end_date => l_time_sum_end_date,
3777 p_tim_id => l_tim_id
3778 );
3779
3780 --
3781 IF g_debug
3782 THEN
3783 hr_utility.set_location (c_proc, 352);
3784 END IF;
3785 END IF; -- l_changed is Y and p_incremental is Y
3786
3787 --
3788 -- Only create a new timecard if there is one to
3789 -- create. That is, if the change is that of a delete,
3790 -- then just need to back out the old entry that was
3791 -- created and NOT create a new entry.
3792 -- If it is not a delete, then create a new entry for the
3793 -- current data.
3794 --
3795 IF l_deleted = 'Y' AND l_changed = 'Y'
3796 THEN
3797
3798 -- g_timecards decides if the timecard needs to
3799 -- be re-exploded. In case the timecard is not
3800 -- added already to g_timecards, check if it needs
3801 -- an explosion. If yes, add it to g_timecards.
3802 IF NOT( g_timecards.EXISTS(l_tim_id))
3803 -- Bug 9308216
3804 -- Added to avoid ORA 6502
3805 AND l_tim_id IS NOT NULL
3806 THEN
3807 IF(chk_need_re_explosion( l_assignment_id,
3808 l_date_worked,
3809 l_element_id ))
3810 THEN
3811 IF g_debug
3812 THEN
3813 hr_utility.trace('This timecard needs re-explosion');
3814 END IF;
3815 l_retcode := hxt_tim_col_util.get_session_date(
3816 hxt_time_collection.g_sess_date);
3817 g_timecards(l_tim_id) := l_tim_id;
3818 END IF;
3819 END IF;
3820
3821 --
3822 -- Delete old summary and detail rows.
3823 --
3824 DELETE FROM hxt_det_hours_worked_f
3825 WHERE parent_id = l_time_summary_id;
3826
3827 --
3828 -- Delete the summary row itself.
3829 --
3830 DELETE FROM hxt_sum_hours_worked_f
3831 WHERE ID = l_time_summary_id;
3832 --
3833 END IF;
3834
3835 --
3836 IF l_deleted = 'N'
3837 THEN
3838 --
3839 IF g_debug
3840 THEN
3841 hr_utility.TRACE
3842 ('---- Creating new timecard ----');
3843 hr_utility.TRACE ( 'l_date_worked is '
3844 || TO_CHAR (l_date_worked,
3845 'DD-MON-YYYY'
3846 )
3847 );
3848 hr_utility.set_location (c_proc, 353);
3849 END IF;
3850
3851 --
3852 -- Pass in Person ID for employee number - issue
3853 -- with going from employee number to person ID
3854 -- in OTM API. Hence bypass it and just pass in person ID.
3855 --
3856 -- IF l_changed = 'N' THEN
3857 --
3858 -- l_count_timecards := l_count_timecards + 1;
3859 --
3860 -- END IF;
3861 --
3862
3863 -- Bug 12919783
3864 -- If there is an already existing timecard, there might be
3865 -- an outstanding Retro entry, which needs to be picked up.
3866
3867 IF l_tim_id IS NOT NULL
3868 THEN
3869 pick_held_retro_batches(l_tim_id,
3870 l_bb_id,
3871 l_ovn);
3872 END IF;
3873
3874 record_time
3875 (p_employee_number => TO_CHAR
3876 (l_person_id
3877 ),
3878 p_approver_number => l_approver_number,
3879 p_batch_ref => l_batch_ref,
3880 p_batch_name => l_batch_name,
3881 p_bg_id => p_bg_id,
3882 p_start_time => l_start_time,
3883 p_end_time => l_stop_time,
3884 p_date_worked => l_date_worked,
3885 p_hours => l_hours,
3886 p_earning_policy => l_earn_policy,
3887 p_hours_type => l_hours_type,
3888 p_earn_reason_code => l_earn_reason_code,
3889 p_project => l_project,
3890 p_task => l_task,
3891 p_location => l_location,
3892 p_comment => l_comment_text,
3893 p_rate_multiple => l_rate_multiple,
3894 p_hourly_rate => l_hourly_rate,
3895 p_amount => l_amount,
3896 p_sep_check_flag => l_sep_check_flag,
3897 p_segment => l_segment,
3898 p_time_summary_id => l_time_summary_id,
3899 p_time_sum_start_date => l_time_sum_start_date,
3900 p_time_sum_end_date => l_time_sum_end_date,
3901 p_time_building_block_id => l_bb_id,
3902 p_time_building_block_ovn => l_ovn,
3903 p_delete => 'N',
3904 p_state_name => l_state_name,
3905 p_county_name => l_county_name,
3906 p_city_name => l_city_name,
3907 p_zip_code => l_zip_code
3908 );
3909
3910 --
3911 IF g_debug
3912 THEN
3913 hr_utility.set_location (c_proc, 360);
3914 END IF;
3915 --
3916 END IF;
3917
3918 --
3919 -------- Update Transaction in OTC for building block --------
3920 --
3921 -- Update with success or failure for each timecard.
3922 -- Currently, only update the detail block since that
3923 -- is the only block that is being used. Should the parent
3924 -- blocks inherit the status of the detail block?
3925 --
3926 -- TRANSACTION_STATUS: S (Success), E (Errors), W (Warnings)
3927 --
3928 g_status := 'SUCCESS';
3929 fnd_message.set_name ('HXC',
3930 'HXC_HXT_RET_REC_SUCCESS');
3931 g_exception_description :=
3932 SUBSTR (fnd_message.get, 1, 2000);
3933 --
3934 set_transaction (p_bb_id => l_bb_id,
3935 p_bb_index => l_cnt,
3936 p_status => g_status,
3937 p_excep => g_exception_description
3938 );
3939
3940 --
3941 IF g_debug
3942 THEN
3943 hr_utility.set_location (c_proc, 370);
3944 END IF;
3945
3946 --
3947 --------------------- Write Error Report --------------------
3948 --
3949 -- Error checking
3950 -- If status in BEE <> Unprocessed, then check to see if
3951 -- validate or transfer resulted in error status
3952 --
3953 IF g_debug
3954 THEN
3955 hr_utility.set_location (c_proc, 380);
3956 hr_utility.set_location (c_proc, 390);
3957 hr_utility.set_location (c_proc, 400);
3958 END IF;
3959 --
3960 END IF; -- parameter validation
3961
3962 --
3963 IF g_debug
3964 THEN
3965 hr_utility.set_location (c_proc, 410);
3966 END IF;
3967 --
3968 END IF; -- scope = 'DETAIL'
3969
3970 -- Bug 9308216
3971 -- Added incrementing Old bb id here so that all processing is complete
3972 -- before this.
3973 IF g_debug
3974 THEN
3975 hr_utility.trace('OTL: Adding bb index here now '||l_old_bb_index);
3976 END IF;
3977 IF l_changed = 'Y' AND p_incremental = 'Y'
3978 THEN
3979 l_old_bb_index := NVL(l_old_bb_index,
3980 hxc_generic_retrieval_pkg.t_old_detail_bld_blks.FIRST
3981 );
3982
3983 IF (l_old_bb_index <= g_cnt_t_old_detail_bld_blks)
3984 THEN
3985 l_old_bb_index := l_old_bb_index + 1;
3986 hr_utility.trace('End of loop: l_old_bb_index is changed '||l_old_bb_index);
3987 END IF;
3988 END IF;
3989
3990
3991 --
3992 EXCEPTION
3993 WHEN e_record_error
3994 THEN
3995 --
3996 IF g_debug
3997 THEN
3998 hr_utility.set_location (c_proc, 411);
3999 END IF;
4000
4001 --
4002 set_transaction (p_bb_id => l_bb_id,
4003 p_bb_index => l_cnt,
4004 p_status => g_status,
4005 p_excep => g_exception_description
4006 );
4007 --
4008 l_last_att_index :=
4009 sync_attributes
4010 (p_att_table => hxc_generic_retrieval_pkg.t_detail_attributes,
4011 p_bb_id => l_bb_id,
4012 p_last_att_index => l_last_att_index
4013 );
4014 l_old_last_att_index :=
4015 sync_attributes
4016 (p_att_table => hxc_generic_retrieval_pkg.t_old_detail_attributes,
4017 p_bb_id => l_bb_id,
4018 p_last_att_index => l_old_last_att_index
4019 );
4020
4021 -- Bug 6621627
4022 -- Added the below adjustment for old building blocks also to
4023 -- avoid propagation of the 6153 error.
4024 -- 6153 error happens from get_attributes when the attribute of
4025 -- a particular building block is missing. The exception that
4026 -- was getting raised, adjusts the attribute and old attribute
4027 -- index, but not the old bb index. Added this code here to
4028 -- adjust that also. Adjust the index only if we are processing a
4029 -- changed record, which has a corresponding old bb id too.
4030 -- The NVL condition put to take care if the first building block
4031 -- itself is missing attributes. In this case, l_old_bb_index
4032 -- would be NULL. The same adjustment done to all the exceptions
4033 -- being raised here.
4034
4035 -- Bug 9308216
4036 IF g_debug
4037 THEN
4038 hr_utility.trace('OTL: l_old_bb_index is '||l_old_bb_index);
4039 END IF;
4040
4041 IF l_changed = 'Y' AND p_incremental = 'Y'
4042 THEN
4043 l_old_bb_index := NVL(l_old_bb_index,
4044 hxc_generic_retrieval_pkg.t_old_detail_bld_blks.FIRST
4045 );
4046
4047 IF (l_old_bb_index <= g_cnt_t_old_detail_bld_blks)
4048 THEN
4049 l_old_bb_index := l_old_bb_index + 1;
4050 hr_utility.trace('e_record_error:l_old_bb_index is changed '||l_old_bb_index);
4051 END IF;
4052 END IF;
4053
4054 IF g_debug
4055 THEN
4056 hr_utility.set_location (c_proc, 412);
4057 END IF;
4058 --
4059 WHEN e_amount_hours
4060 THEN
4061 IF g_debug
4062 THEN
4063 hr_utility.set_location (c_proc, 666);
4064 END IF;
4065
4066 fnd_message.set_name ('HXT', 'HXT_39443_HRS_AMT_EDIT');
4067 g_status := 'ERRORS';
4068 g_exception_description :=
4069 SUBSTR (fnd_message.get, 1, 2000);
4070
4071 IF g_debug
4072 THEN
4073 hr_utility.TRACE ( 'g_exception_description is : '
4074 || g_exception_description
4075 );
4076 END IF;
4077
4078 set_transaction (p_bb_id => l_bb_id,
4079 p_bb_index => l_cnt,
4080 p_status => g_status,
4081 p_excep => g_exception_description
4082 );
4083 l_last_att_index :=
4084 sync_attributes
4085 (p_att_table => hxc_generic_retrieval_pkg.t_detail_attributes,
4086 p_bb_id => l_bb_id,
4087 p_last_att_index => l_last_att_index
4088 );
4089 l_old_last_att_index :=
4090 sync_attributes
4091 (p_att_table => hxc_generic_retrieval_pkg.t_old_detail_attributes,
4092 p_bb_id => l_bb_id,
4093 p_last_att_index => l_old_last_att_index
4094 );
4095
4096
4097 -- Bug 6621627
4098
4099 -- Bug 9308216
4100 IF g_debug
4101 THEN
4102 hr_utility.trace('OTL: l_old_bb_index is '||l_old_bb_index);
4103 END IF;
4104
4105 IF l_changed = 'Y' AND p_incremental = 'Y'
4106 THEN
4107 l_old_bb_index := NVL(l_old_bb_index,
4108 hxc_generic_retrieval_pkg.t_old_detail_bld_blks.FIRST
4109 );
4110
4111 IF (l_old_bb_index <= g_cnt_t_old_detail_bld_blks)
4112 THEN
4113 l_old_bb_index := l_old_bb_index + 1;
4114 hr_utility.trace('e_amount_hours:l_old_bb_index is changed '||l_old_bb_index);
4115 END IF;
4116 END IF;
4117
4118
4119 IF g_debug
4120 THEN
4121 hr_utility.set_location (c_proc, 666.5);
4122 END IF;
4123
4124 fnd_message.raise_error;
4125 RETURN;
4126 WHEN e_not_retrievable
4127 THEN
4128 IF g_debug
4129 THEN
4130 hr_utility.set_location (c_proc, 700);
4131 END IF;
4132
4133 -- Bug 12850901
4134 IF g_intg_error IS NULL
4135 -- This means we got an exception to raise an Edit error.
4136 THEN
4137
4138 fnd_message.set_name ('HXT',
4139 'HXT_TC_CANNOT_BE_CHANGED_TODAY'
4140 );
4141 g_status := 'ERRORS';
4142 g_exception_description :=
4143 SUBSTR (fnd_message.get, 1, 2000);
4144
4145 ELSE
4146 -- This means we got an exception to raise an error for
4147 -- Pay- PA integration.
4148 g_status := 'ERRORS';
4149 g_exception_description :=
4150 SUBSTR (g_intg_error, 1, 2000);
4151 g_intg_error := NULL;
4152 END IF;
4153
4154
4155 IF g_debug
4156 THEN
4157 hr_utility.TRACE ( 'g_exception_description is : '
4158 || g_exception_description
4159 );
4160 END IF;
4161
4162 set_transaction (p_bb_id => l_bb_id,
4163 p_bb_index => l_cnt,
4164 p_status => g_status,
4165 p_excep => g_exception_description
4166 );
4167 l_last_att_index :=
4168 sync_attributes
4169 (p_att_table => hxc_generic_retrieval_pkg.t_detail_attributes,
4170 p_bb_id => l_bb_id,
4171 p_last_att_index => l_last_att_index
4172 );
4173 l_old_last_att_index :=
4174 sync_attributes
4175 (p_att_table => hxc_generic_retrieval_pkg.t_old_detail_attributes,
4176 p_bb_id => l_bb_id,
4177 p_last_att_index => l_old_last_att_index
4178 );
4179
4180 -- Bug 6621627
4181
4182 -- Bug 9308216
4183 IF g_debug
4184 THEN
4185 hr_utility.trace('OTL: l_old_bb_index is '||l_old_bb_index);
4186 END IF;
4187
4188 IF l_changed = 'Y' AND p_incremental = 'Y'
4189 THEN
4190 l_old_bb_index := NVL(l_old_bb_index,
4191 hxc_generic_retrieval_pkg.t_old_detail_bld_blks.FIRST
4192 );
4193
4194 IF (l_old_bb_index <= g_cnt_t_old_detail_bld_blks)
4195 THEN
4196 l_old_bb_index := l_old_bb_index + 1;
4197 hr_utility.trace('e_not_retrievable:l_old_bb_index is changed '||l_old_bb_index);
4198 END IF;
4199 END IF;
4200
4201
4202 IF g_debug
4203 THEN
4204 hr_utility.set_location (c_proc, 710);
4205 END IF;
4206 WHEN OTHERS
4207 THEN
4208 --
4209 IF g_debug
4210 THEN
4211 hr_utility.set_location (c_proc, 413);
4212 END IF;
4213
4214 hr_utility.trace(dbms_utility.format_error_backtrace);
4215
4216 --
4217 g_status := 'ERRORS';
4218 g_exception_description :=
4219 SUBSTR ( 'The error is : '
4220 || TO_CHAR (SQLCODE)
4221 || ' '
4222 || SQLERRM,
4223 1,
4224 2000
4225 );
4226 hr_utility.trace('G_exception description is '||g_exception_description);
4227 --
4228 set_transaction (p_bb_id => l_bb_id,
4229 p_bb_index => l_cnt,
4230 p_status => g_status,
4231 p_excep => g_exception_description
4232 );
4233 --
4234 l_last_att_index :=
4235 sync_attributes
4236 (p_att_table => hxc_generic_retrieval_pkg.t_detail_attributes,
4237 p_bb_id => l_bb_id,
4238 p_last_att_index => l_last_att_index
4239 );
4240 l_old_last_att_index :=
4241 sync_attributes
4242 (p_att_table => hxc_generic_retrieval_pkg.t_old_detail_attributes,
4243 p_bb_id => l_bb_id,
4244 p_last_att_index => l_old_last_att_index
4245 );
4246
4247 -- Bug 6621627
4248
4249 -- Bug 9308216
4250 IF g_debug
4251 THEN
4252 hr_utility.trace('OTL: l_old_bb_index is '||l_old_bb_index);
4253 END IF;
4254
4255 IF l_changed = 'Y' AND p_incremental = 'Y'
4256 THEN
4257 l_old_bb_index := NVL(l_old_bb_index,
4258 hxc_generic_retrieval_pkg.t_old_detail_bld_blks.FIRST
4259 );
4260
4261 IF (l_old_bb_index <= g_cnt_t_old_detail_bld_blks)
4262 THEN
4263 l_old_bb_index := l_old_bb_index + 1;
4264 hr_utility.trace('OTHERS:l_old_bb_index is changed '||l_old_bb_index);
4265 END IF;
4266 END IF;
4267
4268
4269 IF g_debug
4270 THEN
4271 hr_utility.set_location (c_proc, 414);
4272 END IF;
4273 --
4274 END;
4275
4276 --
4277 IF g_debug
4278 THEN
4279 hr_utility.set_location (c_proc, 420);
4280 END IF;
4281 --
4282 END LOOP;
4283
4284 --
4285 IF g_debug
4286 THEN
4287 hr_utility.set_location (c_proc, 430);
4288 END IF;
4289
4290 --
4291 --------------- Update Transaction in OTC for whole process ---------------
4292 --
4293 hxc_generic_retrieval_utils.set_parent_statuses;
4294 --
4295 g_status := 'SUCCESS';
4296 fnd_message.set_name ('HXC', 'HXC_HXT_RET_PROC_SUCCESS');
4297 g_exception_description := SUBSTR (fnd_message.get, 1, 2000);
4298
4299
4300 -- Bug 12919783
4301 -- There might be held batches for this chunk.
4302 -- We need to update the RDB tables for these to older state before
4303 -- update_transaction_status below stamps with the new request.
4304 update_held_retro_batches;
4305 --
4306 hxc_generic_retrieval_pkg.update_transaction_status
4307 (p_process => l_process_name,
4308 p_status => g_status,
4309 p_exception_description => g_exception_description,
4310 p_rollback => FALSE
4311 );
4312
4313 IF g_debug
4314 THEN
4315 hr_utility.set_location (c_proc, 440);
4316 END IF;
4317 ELSE
4318 -- end of loop
4319 l_no_more_timecards := TRUE;
4320 END IF;
4321
4322 IF g_debug
4323 THEN
4324 hr_utility.TRACE ('g_timecards.count = ' || g_timecards.COUNT);
4325 hr_utility.TRACE ('p_batch_ref = ' || p_batch_ref);
4326 END IF;
4327
4328 -- reset timecard list
4329 i := g_timecards.FIRST;
4330
4331 <<re_explode_timecard>>
4332 LOOP
4333 EXIT re_explode_timecard WHEN NOT g_timecards.EXISTS (i);
4334 hxt_td_util.retro_restrict_edit
4335 (p_tim_id => g_timecards
4336 (i),
4337 p_session_date => SYSDATE,
4338 o_dt_update_mod => l_dt_update_mode,
4339 o_error_message => l_otm_error,
4340 o_return_code => l_return_code
4341 );
4342
4343 IF g_debug
4344 THEN
4345 hr_utility.TRACE ('l_dt_update_mode = ' || l_dt_update_mode);
4346 END IF;
4347
4348 hxt_time_collection.re_explode_timecard
4349 (timecard_id => g_timecards
4350 (i),
4351 tim_eff_start_date => NULL,
4352 -- Not Being Used
4353 tim_eff_end_date => NULL,
4354 -- Not Being Used
4355 dt_update_mode => l_dt_update_mode,
4356 -- 'CORRECTION',
4357 otm_error => l_otm_error,
4358 oracle_error => l_oracle_error
4359 );
4360
4361 IF l_otm_error IS NOT NULL
4362 THEN
4363 IF g_debug
4364 THEN
4365 hr_utility.set_location (c_proc, 2000);
4366 hr_utility.TRACE ('l_otm_error :' || l_otm_error);
4367 END IF;
4368 -- raise e_error;
4369 END IF;
4370
4371 IF l_oracle_error IS NOT NULL
4372 THEN
4373 IF g_debug
4374 THEN
4375 hr_utility.set_location (c_proc, 2050);
4376 hr_utility.TRACE ('l_oracle_error :' || l_oracle_error);
4377 END IF;
4378 -- raise e_error;
4379 END IF;
4380
4381 i := g_timecards.NEXT (i);
4382 END LOOP re_explode_timecard;
4383
4384 -- Bug 12919783
4385 -- Just before we commit, we need to mark the newly created retro_batch_ids
4386 -- (created during re_explode_timecard above) on all the relevant records for
4387 -- RDB.
4388 mark_retro_batches;
4389
4390 -- commit records after re-explosion and processing of this chunk
4391 COMMIT;
4392 END LOOP; -- WHILE ( NOT l_no_more_timecards )
4393 ------------------ Conclude transfer_to_otm Main -----------------
4394 --
4395 EXCEPTION
4396 WHEN e_retrieval_error
4397 THEN
4398 --
4399 hxc_generic_retrieval_utils.set_parent_statuses;
4400 --
4401 hxc_generic_retrieval_pkg.update_transaction_status
4402 (p_process => l_process_name,
4403 p_status => g_status,
4404 p_exception_description => g_exception_description,
4405 p_rollback => FALSE
4406 );
4407 --
4408 RETURN;
4409 --
4410 --
4411 WHEN OTHERS
4412 THEN
4413 hr_utility.trace(dbms_utility.format_error_backtrace);
4414 g_status := 'ERRORS';
4415 g_exception_description :=
4416 SUBSTR ('The error is : ' || TO_CHAR (SQLCODE) || ' ' || SQLERRM,
4417 1,
4418 2000
4419 );
4420
4421 --
4422 IF g_debug
4423 THEN
4424 hr_utility.TRACE ('g_exception_description is : ' || SQLERRM);
4425 END IF;
4426
4427 --
4428 IF SQLERRM NOT LIKE '%HXC%'
4429 THEN
4430 --
4431 hxc_generic_retrieval_utils.set_parent_statuses;
4432 --
4433 hxc_generic_retrieval_pkg.update_transaction_status
4434 (p_process => l_process_name,
4435 p_status => g_status,
4436 p_exception_description => g_exception_description,
4437 p_rollback => FALSE
4438 );
4439 --
4440 fnd_message.raise_error;
4441 --
4442 END IF;
4443
4444 --
4445 IF ( (SQLERRM LIKE '%HXC%')
4446 AND (fnd_profile.VALUE ('HXC_RETRIEVAL_OPTIONS') = 'BOTH')
4447 )
4448 THEN
4449 hxc_generic_retrieval_utils.set_parent_statuses;
4450 hxc_generic_retrieval_pkg.update_transaction_status
4451 (p_process => l_process_name,
4452 p_status => 'ERRORS',
4453 p_exception_description => g_exception_description,
4454 p_rollback => FALSE
4455 );
4456
4457 IF (SQLERRM LIKE '%HXC_0017_GNRET_PROCESS_RUNNING%')
4458 THEN
4459 fnd_message.raise_error;
4460 ELSE
4461 p_no_otm := 'Y';
4462 END IF;
4463 ELSIF (fnd_profile.VALUE ('HXC_RETRIEVAL_OPTIONS') = 'OTLR')
4464 THEN
4465 hxc_generic_retrieval_utils.set_parent_statuses;
4466 --
4467 hxc_generic_retrieval_pkg.update_transaction_status
4468 (p_process => l_process_name,
4469 p_status => 'ERRORS',
4470 p_exception_description => g_exception_description,
4471 p_rollback => FALSE
4472 );
4473 --
4474 fnd_message.raise_error;
4475 END IF;
4476
4477 --
4478 RETURN;
4479
4480 --
4481 --
4482 IF g_debug
4483 THEN
4484 hr_utility.set_location (c_proc, 450);
4485 END IF;
4486 --
4487
4488 --
4489 END transfer_to_otm;
4490
4491
4492 FUNCTION chk_need_re_explosion (
4493 p_assignment_id IN NUMBER,
4494 p_date_worked IN DATE,
4495 p_element_type_id IN NUMBER )
4496 RETURN BOOLEAN
4497 IS
4498
4499 CURSOR get_earn_pol( p_asg_id IN NUMBER)
4500 IS SELECT /*+ INDEX(asg HXT_ADD_ASSIGN_INFO_ON1)*/
4501 earning_policy,
4502 effective_start_date,
4503 effective_end_date
4504 FROM hxt_add_assign_info_f asg
4505 WHERE assignment_id = p_asg_id
4506 ORDER BY effective_start_date ;
4507
4508 CURSOR get_earn_group_elements ( p_ep_id IN NUMBER)
4509 IS SELECT /*+ LEADING(ep)
4510 INDEX(ep HXT_EARNING_POLICIES_PK)
4511 INDEX(eg HXT_EARN_GROUPS_EGT_FK) */
4512 element_type_id
4513 FROM hxt_earning_policies ep,
4514 hxt_earn_groups eg
4515 WHERE ep.id = p_ep_id
4516 AND eg.egt_id = ep.egt_id
4517 ORDER BY element_type_id ;
4518
4519 l_ep_id NUMBER;
4520 l_ep_list earn_pol_tab;
4521 l_element_list element_tab;
4522
4523
4524 BEGIN
4525 IF g_debug
4526 THEN
4527 hr_utility.trace('Deleted entry, check if re-explosion needed ');
4528 END IF;
4529
4530 -- Check if the earning policy list is created already for this
4531 -- assignment. If not, create it.
4532 IF NOT(g_earn_pol_list.exists((to_char(p_assignment_id))))
4533 THEN
4534 IF g_debug
4535 THEN
4536 hr_utility.trace('Checking policy for '||p_assignment_id);
4537 END IF;
4538 OPEN get_earn_pol(p_assignment_id);
4539 FETCH get_earn_pol BULK COLLECT INTO l_ep_list ;
4540 CLOSE get_earn_pol;
4541
4542 g_earn_pol_list(to_char(p_assignment_id)).ep_list := l_ep_list;
4543 END IF;
4544
4545 -- Loop thru the earning policy list and find out the one which
4546 -- suits this date_worked.
4547 FOR i IN g_earn_pol_list(to_char(p_assignment_id)).ep_list.FIRST..
4548 g_earn_pol_list(to_char(p_assignment_id)).ep_list.LAST
4549 LOOP
4550 IF p_date_worked BETWEEN g_earn_pol_list(to_char(p_assignment_id)).ep_list(i).start_date
4551 AND g_earn_pol_list(to_char(p_assignment_id)).ep_list(i).end_date
4552 THEN
4553 l_ep_id := g_earn_pol_list(to_char(p_assignment_id)).ep_list(i).earn_pol_id;
4554 EXIT;
4555 END IF;
4556 END LOOP;
4557
4558
4559 IF g_debug
4560 THEN
4561 hr_utility.trace('Earning policy is '||l_ep_id);
4562 END IF;
4563
4564 -- Check if this earning policy already has
4565 -- elements in earning group populated.
4566 -- If not, fetch and populate it.
4567 IF NOT (g_earn_group_list.exists(to_char(l_ep_id)))
4568 THEN
4569 hr_utility.trace(' Checking earning group elements for '||l_ep_id);
4570 OPEN get_earn_group_elements(l_ep_id);
4571 FETCH get_earn_group_elements BULK COLLECT INTO l_element_list;
4572 CLOSE get_earn_group_elements;
4573
4574 g_earn_group_list(l_ep_id).element_list := l_element_list;
4575
4576 END IF;
4577
4578 -- Find out if this element is in the EG. If yes,
4579 -- we need to re-explode, send TRUE. Else do nothing,
4580 -- and return FALSE.
4581
4582 FOR i IN g_earn_group_list(l_ep_id).element_list.first..
4583 g_earn_group_list(l_ep_id).element_list.last
4584 LOOP
4585 IF p_element_type_id < g_earn_group_list(l_ep_id).element_list(i)
4586 THEN
4587 EXIT;
4588 ELSIF p_element_type_id = g_earn_group_list(l_ep_id).element_list(i)
4589 THEN
4590 IF g_debug
4591 THEN
4592 hr_utility.trace(' Element '||p_element_type_id||' in Earning group ');
4593 END IF;
4594 RETURN TRUE;
4595 END IF;
4596 END LOOP;
4597
4598 RETURN FALSE;
4599
4600 END chk_need_re_explosion ;
4601
4602 --
4603 ------------------------------------------------------------------------
4604 END hxt_otc_retrieval_interface;