[Home] [Help]
PACKAGE BODY: APPS.HXT_RETRO_MIX
Source
1 PACKAGE BODY hxt_retro_mix AS
2 /* $Header: hxtrmix.pkb 120.19.12020000.2 2012/07/06 12:02:12 asrajago ship $ */
3
4 /* Outline for retro processing:
5
6 There are two kinds of detail rows
7 - hours rows
8 - amount rows.
9
10 Step 1: Loop thru each hour detail row that is effective today.
11 For every row found, look for a previous version of the row with
12 pay_status='C'.
13 Compare the hours.
14 if old.hours = new.hours then do nothing.
15 if old.hours <> new.hours then
16 send to paymix an adjustment row of
17 new.hours - old.hours
18 Step 2: Loop thru each amount detail row that is effective today.
19 For every row found, look for a previous version of the row with
20 pay_status='C'.
21 Compare the amounts.
22 if old.amount = new.amount then do nothing.
23 if old.amount <> new.amount then
24 send to paymix an adjustment row of
25 new.amount - old.amount
26 Step 3/4. We want to have only one set of rows with pay_status='C'. After
27 retro processing is complete (2 more steps), the retro_rows will
28 be marked to 'C', so we need to mark the previous rows to show
29 how they became adjusted, backed out nocopy or replaced.
30
31 First, update previous detail pay_status='A' (adjusted if there
32 exists a current version of the row where only the
33 hours/amount are different.
34 Second, update previous detail pay_status='D'(dead) if there exists
35 a current version of the row where the hours/amount are equal.
36 Third, for any detail rows where pay_status='C' and the row
37 is expired, send backout transaction to PayMIX.
38
39 Step 5. Set rows on timecard to pay_status='C' if pay_status='R'.
40 */
41 g_debug boolean := hr_utility.debug_enabled;
42
43 -- Bug 12919783
44 -- New type and new variable
45 TYPE NUMTABLE IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
46 g_line_hours NUMTABLE;
47
48
49 PROCEDURE mark_prev_hours_rows (p_tim_id IN NUMBER);
50
51 PROCEDURE mark_prev_amount_rows (p_tim_id IN NUMBER);
52
53 PROCEDURE back_out_leftover_hours (p_batch_id NUMBER, p_tim_id NUMBER);
54
55 PROCEDURE back_out_leftover_amount (p_batch_id NUMBER, p_tim_id NUMBER);
56
57 PROCEDURE mark_retro_rows_complete (p_tim_id NUMBER);
58
59 g_lookup_not_found EXCEPTION; --GLOBAL
60 g_error_ins_batch_lines EXCEPTION; --SIR517 PWM 18FEB00
61
62
63 -- Bug 12919783
64 -- This function returns the hours for the batch_line_id passed.
65
66 FUNCTION batch_line_hours(p_batch_line_id IN NUMBER)
67 RETURN NUMBER
68 IS
69
70 BEGIN
71 IF p_batch_line_id IS NOT NULL
72 THEN
73 IF g_line_hours.EXISTS(TO_CHAR(p_batch_line_id))
74 THEN
75 RETURN g_line_hours(TO_CHAR(p_batch_line_id));
76 ELSE
77 RETURN -99;
78 END IF;
79 ELSE
80 RETURN -99;
81 END IF;
82 END batch_line_hours;
83
84
85 --
86 -- This function created to get the lookup_code for translated input-value names
87 --
88 FUNCTION get_lookup_code (p_meaning IN VARCHAR2, p_date_active IN DATE)
89 RETURN VARCHAR2
90 IS
91 l_lookup_code hr_lookups.lookup_code%TYPE;
92
93 CURSOR get_lookup_code_cur
94 IS
95 SELECT lookup_code
96 FROM hr_lookups
97 WHERE meaning = p_meaning
98 AND lookup_type = 'NAME_TRANSLATIONS'
99 AND enabled_flag = 'Y'
100 AND p_date_active BETWEEN NVL (start_date_active, p_date_active)
101 AND NVL (end_date_active, p_date_active);
102 BEGIN
103 hxt_util.DEBUG (
104 'get_lookup_ code for meaning = '
105 || p_meaning
106 || ' type = '
107 || 'NAME_TRANSLATIONS'
108 || ' date = '
109 || fnd_date.date_to_chardate (p_date_active)
110 ); --FORMS60 --HXT115
111
112 IF p_meaning IS NOT NULL
113 THEN
114 OPEN get_lookup_code_cur;
115 FETCH get_lookup_code_cur INTO l_lookup_code;
116
117 IF get_lookup_code_cur%NOTFOUND
118 THEN
119
120 -- FND_MESSAGE.SET_NAME('HXT','HXT_39483_LOOKUP_NOT_FOUND');
121 -- FND_MESSAGE.SET_TOKEN('CODE', p_meaning); --SIR517 PWM 18FEB00
122 -- FND_MESSAGE.SET_TOKEN('TYPE', 'NAME_TRANSLATIONS'); --SIR517 PWM 18FEB00
123 -- RAISE g_lookup_not_found;
124 NULL; -- This is to fix bug 1761779. Fassadi 16-may-2001
125 END IF;
126 ELSE
127 l_lookup_code := p_meaning;
128 END IF;
129
130 RETURN l_lookup_code;
131 END get_lookup_code;
132
133
134 --BEGIN GLOBAL
135 FUNCTION convert_lookup (
136 p_lookup_code IN VARCHAR2,
137 p_lookup_type IN VARCHAR2,
138 p_date_active IN DATE
139 )
140 RETURN VARCHAR2
141 IS
142 l_meaning hr_lookups.meaning%TYPE;
143
144 CURSOR get_meaning_cur (p_code VARCHAR2, p_type VARCHAR2, p_date DATE)
145 IS
146 SELECT fcl.meaning
147 FROM hr_lookups fcl --FORMS60
148 WHERE fcl.lookup_code = p_code
149 AND fcl.lookup_type = p_type
150 AND fcl.enabled_flag = 'Y'
151 AND p_date BETWEEN NVL (fcl.start_date_active, p_date)
152 AND NVL (fcl.end_date_active, p_date);
153 BEGIN
154 hxt_util.DEBUG (
155 'convert_lookup - code = '
156 || p_lookup_code
157 || ' type = '
158 || p_lookup_type
159 || ' date = '
160 || fnd_date.date_to_chardate (p_date_active)
161 ); --FORMS60 --HXT115
162
163 -- Bug 8888777
164 -- Added the below condition to restrict IV conversion to
165 -- display value.
166 IF p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL
167 AND hxt_batch_process.g_IV_format = 'N'
168 THEN
169 OPEN get_meaning_cur (p_lookup_code, p_lookup_type, p_date_active);
170 FETCH get_meaning_cur INTO l_meaning;
171
172 IF get_meaning_cur%NOTFOUND
173 THEN
174 fnd_message.set_name ('HXT', 'HXT_39483_LOOKUP_NOT_FOUND');
175 fnd_message.set_token ('CODE', p_lookup_code);
176 fnd_message.set_token ('TYPE', p_lookup_type);
177 RAISE g_lookup_not_found;
178 END IF;
179 ELSE
180 l_meaning := p_lookup_code;
181 END IF;
182
183 RETURN l_meaning;
184 END convert_lookup;
185
186 PROCEDURE insert_pay_batch_lines (
187 p_batch_id NUMBER,
188 p_batch_line_id OUT NOCOPY NUMBER,
189 p_assignment_id NUMBER,
190 p_assignment_number VARCHAR2,
191 p_amount NUMBER,
192 p_cost_allocation_keyflex_id NUMBER,
193 p_concatenated_segments VARCHAR2,
194 p_segment1 VARCHAR2,
195 p_segment2 VARCHAR2,
196 p_segment3 VARCHAR2,
197 p_segment4 VARCHAR2,
198 p_segment5 VARCHAR2,
199 p_segment6 VARCHAR2,
200 p_segment7 VARCHAR2,
201 p_segment8 VARCHAR2,
202 p_segment9 VARCHAR2,
203 p_segment10 VARCHAR2,
204 p_segment11 VARCHAR2,
205 p_segment12 VARCHAR2,
206 p_segment13 VARCHAR2,
207 p_segment14 VARCHAR2,
208 p_segment15 VARCHAR2,
209 p_segment16 VARCHAR2,
210 p_segment17 VARCHAR2,
211 p_segment18 VARCHAR2,
212 p_segment19 VARCHAR2,
213 p_segment20 VARCHAR2,
214 p_segment21 VARCHAR2,
215 p_segment22 VARCHAR2,
216 p_segment23 VARCHAR2,
217 p_segment24 VARCHAR2,
218 p_segment25 VARCHAR2,
219 p_segment26 VARCHAR2,
220 p_segment27 VARCHAR2,
221 p_segment28 VARCHAR2,
222 p_segment29 VARCHAR2,
223 p_segment30 VARCHAR2,
224 p_element_type_id NUMBER,
225 p_element_name VARCHAR2,
226 p_hourly_rate NUMBER,
227 p_locality_worked VARCHAR2,
228 p_rate_code VARCHAR2,
229 p_rate_multiple NUMBER,
230 p_separate_check_flag VARCHAR2,
231 p_tax_separately_flag VARCHAR2,
232 p_hours NUMBER,
233 p_date_worked DATE,
234 p_reason VARCHAR2,
235 p_batch_sequence NUMBER,
236 p_state_name VARCHAR2 default null, --dd
237 p_county_name VARCHAR2 default null,
238 p_city_name VARCHAR2 default null,
239 p_zip_code varchar2 default null,
240 p_parent_id NUMBER default 0
241 )
242 IS
243 -- l_batch_sequence NUMBER;
244 l_return NUMBER; --SIR517 PWM 18FEB00
245 l_batch_line_id number;
246 l_batch_line_ovn number;
247
248 -- Bug 12993528
249 TYPE input_value_record IS RECORD (
250 SEQUENCE pay_input_values_f.input_value_id%TYPE,
251 NAME pay_input_values_f_tl.NAME%TYPE, --FORMS60
252 lookup pay_input_values_f.lookup_type%TYPE,
253 otl_sequence NUMBER);
254
255 TYPE input_values_table IS TABLE OF input_value_record
256 INDEX BY BINARY_INTEGER;
257
258 hxt_value input_values_table;
259
260 TYPE pbl_values_table IS TABLE OF pay_batch_lines.value_1%TYPE
261 INDEX BY BINARY_INTEGER;
262
263 pbl_value pbl_values_table;
264 l_value_meaning hr_lookups.meaning%TYPE;
265
266 CURSOR c_date_input_value (
267 cp_element_type_id NUMBER,
268 cp_assignment_id NUMBER,
269 cp_effective_date DATE
270 )
271 IS
272 SELECT DISTINCT piv.NAME -- PIV.display_sequence
273 FROM --pay_element_types_f PET
274 pay_input_values_f piv,
275 pay_accrual_plans pap,
276 pay_net_calculation_rules pncr
277 WHERE --PET.element_type_id = cp_element_type_id
278
279 -- AND PET.element_type_id = PIV.element_type_id
280 piv.element_type_id = cp_element_type_id
281 AND cp_effective_date BETWEEN piv.effective_start_date
282 AND piv.effective_end_date
283 AND pncr.date_input_value_id = piv.input_value_id
284 AND pncr.input_value_id <> pap.pto_input_value_id
285 AND pncr.input_value_id <> pap.co_input_value_id
286 AND pncr.accrual_plan_id = pap.accrual_plan_id
287 AND pap.accrual_plan_id IN
288 (SELECT papl.accrual_plan_id
289 FROM pay_accrual_plans papl,
290 pay_element_links_f pel,
291 pay_element_entries_f pee
292 WHERE pel.element_type_id =
293 papl.accrual_plan_element_type_id
294 AND cp_effective_date
295 BETWEEN pel.effective_start_date
296 AND pel.effective_end_date
297 AND pee.element_link_id =
298 pel.element_link_id
299 AND pee.assignment_id = cp_assignment_id
300 AND cp_effective_date
301 BETWEEN pee.effective_start_date
302 AND pee.effective_end_date);
303
304 l_piv_name VARCHAR2 (30);
305 lv_pbl_flag VARCHAR2 (1) := 'N';
306 BEGIN
307
308 if g_debug then
309 hr_utility.set_location ('insert_pay_batch_lines', 10);
310 end if;
311
312 -- Initialize tables
313 FOR i IN 1 .. 15
314 LOOP
315 hxt_value (i).SEQUENCE := NULL;
316 hxt_value (i).NAME := NULL;
317 hxt_value (i).lookup := NULL;
318 pbl_value (i) := NULL;
319 END LOOP;
320
321 -- Get input values details for this element
322 -- Bug 12993528
323 -- Replaced the Pay package with the re written HXT procedure.
324 hxt_batch_process.get_input_value_details (
325 p_element_type_id,
326 p_date_worked,
327 hxt_value (1).SEQUENCE,
328 hxt_value (2).SEQUENCE,
329 hxt_value (3).SEQUENCE,
330 hxt_value (4).SEQUENCE,
331 hxt_value (5).SEQUENCE,
332 hxt_value (6).SEQUENCE,
333 hxt_value (7).SEQUENCE,
334 hxt_value (8).SEQUENCE,
335 hxt_value (9).SEQUENCE,
336 hxt_value (10).SEQUENCE,
337 hxt_value (11).SEQUENCE,
338 hxt_value (12).SEQUENCE,
339 hxt_value (13).SEQUENCE,
340 hxt_value (14).SEQUENCE,
341 hxt_value (15).SEQUENCE,
342 hxt_value (1).NAME,
343 hxt_value (2).NAME,
344 hxt_value (3).NAME,
345 hxt_value (4).NAME,
346 hxt_value (5).NAME,
347 hxt_value (6).NAME,
348 hxt_value (7).NAME,
349 hxt_value (8).NAME,
350 hxt_value (9).NAME,
351 hxt_value (10).NAME,
352 hxt_value (11).NAME,
353 hxt_value (12).NAME,
354 hxt_value (13).NAME,
355 hxt_value (14).NAME,
356 hxt_value (15).NAME,
357 hxt_value (1).lookup,
358 hxt_value (2).lookup,
359 hxt_value (3).lookup,
360 hxt_value (4).lookup,
361 hxt_value (5).lookup,
362 hxt_value (6).lookup,
363 hxt_value (7).lookup,
364 hxt_value (8).lookup,
365 hxt_value (9).lookup,
366 hxt_value (10).lookup,
367 hxt_value (11).lookup,
368 hxt_value (12).lookup,
369 hxt_value (13).lookup,
370 hxt_value (14).lookup,
371 hxt_value (15).lookup,
372 hxt_value(1).otl_sequence,
373 hxt_value(2).otl_sequence,
374 hxt_value(3).otl_sequence,
375 hxt_value(4).otl_sequence,
376 hxt_value(5).otl_sequence,
377 hxt_value(6).otl_sequence,
378 hxt_value(7).otl_sequence,
379 hxt_value(8).otl_sequence,
380 hxt_value(9).otl_sequence,
381 hxt_value(10).otl_sequence,
382 hxt_value(11).otl_sequence,
383 hxt_value(12).otl_sequence,
384 hxt_value(13).otl_sequence,
385 hxt_value(14).otl_sequence,
386 hxt_value(15).otl_sequence
387 );
388 if g_debug then
389 hr_utility.set_location ('insert_pay_batch_lines', 20);
390 end if;
391 -- Place OTM data into BEE values per input values
392 hxt_util.DEBUG ('Putting OTM data into BEE values per input values'); --HXT115
393
394
395 --
396 -- In order to get the input-value logic work in diiferent legislations we need
397 -- to create (SEED) new lookups for 'Hours', 'Hourly Rate', 'Rate Multiple',
398 -- and 'Rate Code' with lookup_type of 'NAME_TRANSLATION' and lookup_code of
399 -- 'HOURS', 'HOURLY_RATE', 'RATE_MULTIPLE' and 'RATE_CODE' respectively.
400 -- Then the customers in different countries need to create the above input
401 -- values with the name which is directly translated from the above names for
402 -- OTM elements.
403 --
404 -- For example: In French the user must create an input value for 'Hours' to
405 -- be 'Heures' and then to determine which input value 'Heures' is associated
406 -- with we look at the hr_lookups and if we find an entry with lookup_type =
407 -- 'NAME_TRANSLATIONS' and lookup_code = 'HOURS' and Meaning to be 'Heures'
408 -- then we know that this input vale woul map to 'Hours'.
409 --
410 -- What need to be noted that it is the customer's responsibilty to create
411 -- input values which are the direct translation of 'Hours','Hourly Rate',
412 -- 'Pay Value' , 'Rate Multiple' and 'Rate Code'
413 --
414 FOR i IN 1 .. 15
415 LOOP
416
417 --
418 -- We need to get the lookup_code for the input_value names before processing
419 -- the further logic on the screen value for the input values.
420 --
421 lv_pbl_flag := 'N';
422 if g_debug then
423 hr_utility.set_location ('insert_pay_batch_lines', 30);
424
425 hr_utility.TRACE (
426 'hxt_value_name_'
427 || TO_CHAR (i)
428 || ' :'
429 || hxt_value (i).NAME
430 );
431 hr_utility.TRACE ( 'p_date_worked:'
432 || p_date_worked);
433 end if;
434 l_value_meaning :=
435 get_lookup_code (hxt_value (i).NAME, p_date_worked);
436 if g_debug then
437 hr_utility.TRACE ( 'l_value_meaning :'
438 || l_value_meaning);
439 end if;
440 --if hxt_value(i).name = 'Hours' then
441 IF l_value_meaning = 'HOURS'
442 THEN
443 if g_debug then
444 hr_utility.set_location ('insert_pay_batch_lines', 40);
445 end if;
446 pbl_value (i) :=
447 convert_lookup (p_hours, hxt_value (i).lookup, p_date_worked);
448 if g_debug then
449 hr_utility.TRACE (
450 'pbl_value_'
451 || TO_CHAR (i)
452 || ' :'
453 || pbl_value (i)
454 );
455 end if;
456 --elsif hxt_value(i).name = 'Pay Value' then
457 ELSIF l_value_meaning = 'AMOUNT'
458 THEN
459 if g_debug then
460 hr_utility.set_location ('insert_pay_batch_lines', 50);
461 end if;
462 pbl_value (i) :=
463 convert_lookup (p_amount, hxt_value (i).lookup, p_date_worked);
464 if g_debug then
465 hr_utility.TRACE (
466 'pbl_value_'
467 || TO_CHAR (i)
468 || ' :'
469 || pbl_value (i)
470 );
471 end if;
472 --elsif hxt_value(i).name = 'Multiple' then
473 ELSIF l_value_meaning = 'RATE_MULTIPLE'
474 THEN
475 if g_debug then
476 hr_utility.set_location ('insert_pay_batch_lines', 60);
477 end if;
478 pbl_value (i) := convert_lookup (
479 p_rate_multiple,
480 hxt_value (i).lookup,
481 p_date_worked
482 );
483
484 if g_debug then
485 hr_utility.TRACE (
486 'pbl_value_'
487 || TO_CHAR (i)
488 || ' :'
489 || pbl_value (i)
490 );
491 end if;
492
493 ELSIF l_value_meaning = 'HOURLY_RATE'
494 THEN
495 if g_debug then
496 hr_utility.set_location ('insert_pay_batch_lines', 70);
497 end if;
498 pbl_value (i) := convert_lookup (
499 p_hourly_rate,
500 hxt_value (i).lookup,
501 p_date_worked
502 );
503 if g_debug then
504
505 hr_utility.TRACE (
506 'pbl_value_'
507 || TO_CHAR (i)
508 || ' :'
509 || pbl_value (i)
510 );
511 end if;
512
513
514 --elsif hxt_value(i).name = 'Rate' then
515 ELSIF l_value_meaning = 'RATE'
516 THEN
517 if g_debug then
518 hr_utility.set_location ('insert_pay_batch_lines', 70);
519 end if;
520 pbl_value (i) := convert_lookup (
521 p_hourly_rate,
522 hxt_value (i).lookup,
523 p_date_worked
524 );
525 if g_debug then
526
527 hr_utility.TRACE (
528 'pbl_value_'
529 || TO_CHAR (i)
530 || ' :'
531 || pbl_value (i)
532 );
533 end if;
534 --elsif hxt_value(i).name = 'Rate Code' then
535 ELSIF l_value_meaning = 'RATE_CODE'
536 THEN
537 if g_debug then
538 hr_utility.set_location ('insert_pay_batch_lines', 80);
539 end if;
540 pbl_value (i) := convert_lookup (
541 p_rate_code,
542 hxt_value (i).lookup,
543 p_date_worked
544 );
545 if g_debug then
546 hr_utility.TRACE (
547 'pbl_value_'
548 || TO_CHAR (i)
549 || ' :'
550 || pbl_value (i)
551 );
552 end if;
553
554 -- BEGIN US localization
555 ELSIF hxt_value (i).NAME = 'Jurisdiction'
556 THEN
557 if g_debug then
558 hr_utility.set_location ('insert_pay_batch_lines', 90);
559 end if;
560
561 if(p_state_name is not null or
562 p_county_name is not null or
563 p_city_name is not null or
564 p_zip_code is not null) then
565 pbl_value(i):=convert_lookup(pay_ac_utility.get_geocode(p_state_name,
566 p_county_name,
567 p_city_name,
568 p_zip_code),
569 hxt_value(i).lookup,
570 p_date_worked);
571 else
572 pbl_value (i) := convert_lookup (
573 p_locality_worked,
574 hxt_value (i).lookup,
575 p_date_worked
576 );
577 end if;
578 if g_debug then
579 hr_utility.TRACE (
580 'pbl_value_'
581 || TO_CHAR (i)
582 || ' :'
583 || pbl_value (i)
584 );
585 end if;
586 ELSIF hxt_value (i).NAME = 'Deduction Processing'
587 THEN
588 if g_debug then
589 hr_utility.set_location ('insert_pay_batch_lines', 100);
590 end if;
591 pbl_value (i) := convert_lookup (
592 p_tax_separately_flag,
593 hxt_value (i).lookup,
594 p_date_worked
595 );
596 if g_debug then
597 hr_utility.TRACE (
598 'pbl_value_'
599 || TO_CHAR (i)
600 || ' :'
601 || pbl_value (i)
602 );
603 end if;
604 ELSIF hxt_value (i).NAME = 'Separate Check'
605 THEN
606 if g_debug then
607 hr_utility.set_location ('insert_pay_batch_lines', 110);
608 end if;
609 pbl_value (i) := convert_lookup (
610 p_separate_check_flag,
611 hxt_value (i).lookup,
612 p_date_worked
613 );
614 if g_debug then
615 hr_utility.TRACE (
616 'pbl_value_'
617 || TO_CHAR (i)
618 || ' :'
619 || pbl_value (i)
620 );
621 end if;
622
623 -- END US localization
624
625 ELSIF hxt_value (i).NAME IS NOT NULL
626 THEN -- pbl_value(i) := NULL;
627 if g_debug then
628 hr_utility.set_location ('insert_pay_batch_lines', 120);
629 hr_utility.TRACE ( 'p_element_type_id :'
630 || p_element_type_id);
631 hr_utility.TRACE ( 'p_assignment_id :'
632 || p_assignment_id);
633 hr_utility.TRACE ( 'p_date_worked :'
634 || p_date_worked);
635 end if;
636 OPEN c_date_input_value (
637 p_element_type_id,
638 p_assignment_id,
639 p_date_worked
640 );
641
642 LOOP
643 if g_debug then
644 hr_utility.set_location ('insert_pay_batch_lines', 130);
645 end if;
646 FETCH c_date_input_value INTO l_piv_name;
647 EXIT WHEN c_date_input_value%NOTFOUND;
648 if g_debug then
649 hr_utility.TRACE ( 'l_piv_name :'
650 || l_piv_name);
651 hr_utility.TRACE ( 'lv_pbl_flag :'
652 || lv_pbl_flag);
653 end if;
654
655 IF l_piv_name = hxt_value (i).NAME
656 THEN
657 if g_debug then
658 hr_utility.set_location ('insert_pay_batch_lines', 140);
659 end if;
660 --pbl_value(i) := to_char(p_date_worked,'DD-MON-YYYY');
661 pbl_value (i) := fnd_date.date_to_canonical (p_date_worked);
662 lv_pbl_flag := 'Y';
663 if g_debug then
664 hr_utility.TRACE (
665 'pbl_value_'
666 || TO_CHAR (i)
667 || ' :'
668 || pbl_value (i)
669 );
670 end if;
671 EXIT;
672 END IF;
673 END LOOP;
674
675 CLOSE c_date_input_value;
676
677 -- Bug 8888777
678 -- Control is here means that no fixed input value is encountered, but
679 -- still some IV with a Non NULL name. Convert this and copy it.
680
681 -- Bug 9774867
682 -- Added a TO_CHAR conversion to the indexes below.
683
684 -- Bug 9920871
685 -- Added the IF condition to avoid losing input values
686 -- which are already populated.
687 IF lv_pbl_flag = 'N'
688 -- Bug 11076402
689 -- Added code here
690 AND NVL(FND_PROFILE.VALUE('HXT_ALLOW_SUM_CUSTOM_ATTRIBUTE'),'N') = 'N'
691 THEN
692
693 hr_utility.trace('Coming into new code as if profile is turned OFF');
694
695 IF g_debug
696 AND g_xiv_table.EXISTS(TO_CHAR(p_parent_id))
697 THEN
698 hr_utility.trace('IV : It came out here ');
699 hr_utility.trace('IV : i = '||i);
700 hr_utility.trace('IV : '||g_xiv_table(TO_CHAR(p_parent_id)).attribute1);
701 END IF;
702
703 IF g_xiv_table.EXISTS(TO_CHAR(p_parent_id))
704 THEN
705
706 -- Bug 12993528
707 -- Modified the IF conditions to check OTL_sequence rather than
708 -- the IV sequence from PAY.
709 IF hxt_value(i).otl_sequence = 1
710 THEN pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute1,
711 hxt_value (i).lookup,
712 p_date_worked);
713 lv_pbl_flag := 'Y';
714 ELSIF hxt_value(i).otl_sequence = 2
715 THEN
716 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute2,
717 hxt_value (i).lookup,
718 p_date_worked);
719 lv_pbl_flag := 'Y';
720 ELSIF hxt_value(i).otl_sequence = 3
721 THEN
722 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute3,
723 hxt_value (i).lookup,
724 p_date_worked);
725 lv_pbl_flag := 'Y';
726 ELSIF hxt_value(i).otl_sequence = 4
727 THEN
728 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute4,
729 hxt_value (i).lookup,
730 p_date_worked);
731 lv_pbl_flag := 'Y';
732 ELSIF hxt_value(i).otl_sequence = 5
733 THEN
734 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute5,
735 hxt_value (i).lookup,
736 p_date_worked);
737 lv_pbl_flag := 'Y';
738 ELSIF hxt_value(i).otl_sequence = 6
739 THEN
740 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute6,
741 hxt_value (i).lookup,
742 p_date_worked);
743 lv_pbl_flag := 'Y';
744 ELSIF hxt_value(i).otl_sequence = 7
745 THEN
746 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute7,
747 hxt_value (i).lookup,
748 p_date_worked);
749 lv_pbl_flag := 'Y';
750 ELSIF hxt_value(i).otl_sequence = 8
751 THEN
752 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute8,
753 hxt_value (i).lookup,
754 p_date_worked);
755 lv_pbl_flag := 'Y';
756 ELSIF hxt_value(i).otl_sequence = 9
757 THEN
758 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute9,
759 hxt_value (i).lookup,
760 p_date_worked);
761 lv_pbl_flag := 'Y';
762 ELSIF hxt_value(i).otl_sequence = 10
763 THEN
764 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute10,
765 hxt_value (i).lookup,
766 p_date_worked);
767 lv_pbl_flag := 'Y';
768 ELSIF hxt_value(i).otl_sequence = 11
769 THEN
770 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute11,
771 hxt_value (i).lookup,
772 p_date_worked);
773 lv_pbl_flag := 'Y';
774 ELSIF hxt_value(i).otl_sequence = 12
775 THEN
776 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute12,
777 hxt_value (i).lookup,
778 p_date_worked);
779 lv_pbl_flag := 'Y';
780 ELSIF hxt_value(i).otl_sequence = 13
781 THEN
782 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute13,
783 hxt_value (i).lookup,
784 p_date_worked);
785 lv_pbl_flag := 'Y';
786 ELSIF hxt_value(i).otl_sequence = 14
787 THEN
788 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute14,
789 hxt_value (i).lookup,
790 p_date_worked);
791 lv_pbl_flag := 'Y';
792 ELSIF hxt_value(i).otl_sequence = 15
793 THEN
794 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_parent_id)).attribute15,
795 hxt_value (i).lookup,
796 p_date_worked);
797 lv_pbl_flag := 'Y';
798 END IF;
799 END IF;
800
801 END IF;
802
803 IF lv_pbl_flag = 'N'
804 THEN
805 if g_debug then
806 hr_utility.set_location ('insert_pay_batch_lines', 150);
807 end if;
808 pbl_value (i) := NULL;
809 if g_debug then
810 hr_utility.TRACE (
811 'pbl_value_'
812 || TO_CHAR (i)
813 || ' :'
814 || pbl_value (i)
815 );
816 end if;
817 END IF;
818
819 if g_debug then
820 hr_utility.TRACE ( 'lv_pbl_flag :'
821 || lv_pbl_flag);
822 end if;
823 ELSE
824 if g_debug then
825 hr_utility.set_location ('insert_pay_batch_lines', 160);
826 end if;
827 pbl_value (i) := NULL;
828 if g_debug then
829 hr_utility.TRACE (
830 'pbl_value_'
831 || TO_CHAR (i)
832 || ' :'
833 || pbl_value (i)
834 );
835 end if;
836 END IF;
837
838 if g_debug then
839 hr_utility.set_location ('insert_pay_batch_lines', 170);
840 end if;
841 hxt_util.DEBUG ( 'value_'
842 || TO_CHAR (i)
843 || ' = '
844 || pbl_value (i)); --HXT115
845 END LOOP;
846
847 -- Get next sequence number
848 -- l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence (p_batch_id);
849 hxt_util.DEBUG ( 'batch_sequence = '
850 || TO_CHAR (p_batch_sequence)); --HXT115
851
852 -- Add new hours data
853 PAY_BATCH_ELEMENT_ENTRY_API.create_batch_line
854 (p_session_date => sysdate
855 ,p_batch_id => p_batch_id
856 ,p_batch_line_status => 'U'
857 ,p_assignment_id => p_assignment_id
858 ,p_assignment_number => p_assignment_number
859 ,p_date_earned => p_date_worked
860 ,p_batch_sequence => p_batch_sequence
861 ,p_concatenated_segments => p_concatenated_segments
862 ,p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id
863 ,p_effective_date => p_date_worked
864 ,p_element_name => p_element_name
865 ,p_element_type_id => p_element_type_id
866 ,p_entry_type => 'E'
867 ,p_reason => p_reason
868 ,p_segment1 => p_segment1
869 ,p_segment2 => p_segment2
870 ,p_segment3 => p_segment3
871 ,p_segment4 => p_segment4
872 ,p_segment5 => p_segment5
873 ,p_segment6 => p_segment6
874 ,p_segment7 => p_segment7
875 ,p_segment8 => p_segment8
876 ,p_segment9 => p_segment9
877 ,p_segment10 => p_segment10
878 ,p_segment11 => p_segment11
879 ,p_segment12 => p_segment12
880 ,p_segment13 => p_segment13
881 ,p_segment14 => p_segment14
882 ,p_segment15 => p_segment15
883 ,p_segment16 => p_segment16
884 ,p_segment17 => p_segment17
885 ,p_segment18 => p_segment18
886 ,p_segment19 => p_segment19
887 ,p_segment20 => p_segment20
888 ,p_segment21 => p_segment21
889 ,p_segment22 => p_segment22
890 ,p_segment23 => p_segment23
891 ,p_segment24 => p_segment24
892 ,p_segment25 => p_segment25
893 ,p_segment26 => p_segment26
894 ,p_segment27 => p_segment27
895 ,p_segment28 => p_segment28
896 ,p_segment29 => p_segment29
897 ,p_segment30 => p_segment30
898 ,p_value_1 => pbl_value(1)
899 ,p_value_2 => pbl_value(2)
900 ,p_value_3 => pbl_value(3)
901 ,p_value_4 => pbl_value(4)
902 ,p_value_5 => pbl_value(5)
903 ,p_value_6 => pbl_value(6)
904 ,p_value_7 => pbl_value(7)
905 ,p_value_8 => pbl_value(8)
906 ,p_value_9 => pbl_value(9)
907 ,p_value_10 => pbl_value(10)
908 ,p_value_11 => pbl_value(11)
909 ,p_value_12 => pbl_value(12)
910 ,p_value_13 => pbl_value(13)
911 ,p_value_14 => pbl_value(14)
912 ,p_value_15 => pbl_value(15)
913 ,p_batch_line_id => l_batch_line_id
914 ,p_object_version_number => l_batch_line_ovn
915 ,p_iv_all_internal_format => hxt_batch_process.g_IV_format -- Bug 9156092
916 );
917
918 p_batch_line_id := l_batch_line_id;
919
920 -- Bug 12919783
921 -- Recording the number of hours sent in for each batch line
922 -- against the batch line id. These are used later to snap the details
923 -- to RDB tables.
924
925 g_line_hours(TO_CHAR(l_batch_line_id)) := p_hours;
926
927 EXCEPTION
928 WHEN g_lookup_not_found
929 THEN
930 hxt_util.DEBUG (
931 'Oops...g_lookup_not_found in insert_pay_batch_lines'
932 ); --HXT115
933 RAISE g_lookup_not_found; --SIR517 PWM 18FEB00 Re-raise the exception for the calling procedure
934 WHEN OTHERS
935 THEN
936 hxt_util.DEBUG (SQLERRM); --HXT115
937 hxt_util.DEBUG ('Oops...others in insert_pay_batch_lines'); --HXT115
938 fnd_message.set_name ('HXT', 'HXT_39354_ERR_INS_PAYMX_INFO');
939 fnd_message.set_token ('SQLERR', SQLERRM);
940 RAISE g_error_ins_batch_lines; --SIR517 PWM 18FEB00 Re-raise the exception for the calling procedure
941 END insert_pay_batch_lines;
942
943
944 --END GLOBAL
945
946 PROCEDURE retro_sum_to_mix (
947 p_batch_id IN NUMBER,
948 p_tim_id IN NUMBER,
949 p_sum_retcode OUT NOCOPY NUMBER,
950 p_err_buf OUT NOCOPY VARCHAR2
951 )
952 IS
953
954 -- Bug 8888777
955 -- Added this cursor to pick up input values from
956 -- the summary table.
957 CURSOR get_input_values(p_id IN NUMBER)
958 IS SELECT
959 attribute1,
960 attribute2,
961 attribute3,
962 attribute4,
963 attribute5,
964 attribute6,
965 attribute7,
966 attribute8,
967 attribute9,
968 attribute10,
969 attribute11,
970 attribute12,
971 attribute13,
972 attribute14,
973 attribute15
974 FROM hxt_sum_hours_worked_x
975 WHERE id = p_id;
976
977
978 -- select current hours by detail where hours<>0 and amount=0
979
980 -- Bug 10133503
981 -- Added hints to avoid FTS and improve performance
982
983 CURSOR current_hours (p_tim_id NUMBER)
984 IS
985 SELECT /*+ LEADING(tim.HXT_TIMECARDS_F)
986 INDEX(tim.HXT_TIMECARDS_F HXT_TIMECARDS_PK)
987 USE_NL(tim.HXT_TIMECARDS_F hrw.HXT_DET_HOURS_WORKED_F)
988 INDEX(hrw.HXT_DET_HOURS_WORKED_F HXT_DET_HOURS_WORKED_F_TIM_FK)
989 INDEX(ffvr FND_FLEX_VALUES_U1)
990 INDEX(loct HR_LOCATIONS_ALL_TL_PK)
991 INDEX(loc.HR_LOCATIONS_ALL HR_LOCATIONS_PK)
992 INDEX(elt PAY_ELEMENT_TYPES_F_PK)
993 INDEX(eltv.HXT_ADD_ELEM_INFO_F HXT_ADD_ELEM_INFO_ON1)
994 INDEX(pcak PAY_COST_ALLOCATION_KEYFLE_PK)
995 INDEX(ptp PER_TIME_PERIODS_PK)
996 INDEX(asm.PAA PER_ASSIGNMENTS_F_PK) */
997 asm.assignment_number, elt.element_name, --FORMS60
998 eltv.hxt_premium_type, --SIR65
999 eltv.hxt_premium_amount, --SIR65
1000 eltv.hxt_earning_category, --SIR65
1001 DECODE (
1002 SIGN (
1003 DECODE (
1004 SIGN (
1005 ptp.start_date
1006 - asm.effective_start_date
1007 ),
1008 1, ptp.start_date,
1009 asm.effective_start_date
1010 )
1011 - elt.effective_start_date
1012 ),
1013 1, DECODE (
1014 SIGN ( ptp.start_date
1015 - asm.effective_start_date),
1016 1, ptp.start_date,
1017 asm.effective_start_date
1018 ),
1019 elt.effective_start_date
1020 )
1021 from_date,
1022 DECODE (
1023 SIGN (
1024 DECODE (
1025 SIGN ( ptp.end_date
1026 - asm.effective_end_date),
1027 -1, ptp.end_date,
1028 asm.effective_end_date
1029 )
1030 - elt.effective_end_date
1031 ),
1032 -1, DECODE (
1033 SIGN ( ptp.end_date
1034 - asm.effective_end_date),
1035 -1, ptp.end_date,
1036 asm.effective_end_date
1037 ),
1038 elt.effective_end_date
1039 ) TO_DATE,
1040 hrw.rate_multiple, hrw.hourly_rate,
1041
1042
1043 loct.location_code locality_worked, --FORMS60
1044 ffvr.flex_value rate_code, hrw.separate_check_flag,
1045 hrw.fcl_tax_rule_code tax_separately_flag, hrw.amount,
1046 hrw.hours hours_worked, hrw.assignment_id,
1047 /* fk - assignment_number */
1048 hrw.ffv_cost_center_id, /* fk - cost_center_code */ pcak.concatenated_segments,
1049 pcak.segment1, pcak.segment2, pcak.segment3, pcak.segment4,
1050 pcak.segment5, pcak.segment6, pcak.segment7, pcak.segment8,
1051 pcak.segment9, pcak.segment10, pcak.segment11,
1052 pcak.segment12, pcak.segment13, pcak.segment14,
1053 pcak.segment15, pcak.segment16, pcak.segment17,
1054 pcak.segment18, pcak.segment19, pcak.segment20,
1055 pcak.segment21, pcak.segment22, pcak.segment23,
1056 pcak.segment24, pcak.segment25, pcak.segment26,
1057 pcak.segment27, pcak.segment28, pcak.segment29,
1058 pcak.segment30, hrw.element_type_id,
1059 hrw.location_id,
1060 hrw.ffv_rate_code_id,
1061 asm.effective_end_date asm_effective_end_date,
1062 elt.effective_end_date elt_effective_end_date,
1063 hrw.parent_id, hrw.ROWID hrw_rowid, -- OHM180
1064 hcl.meaning reason, --GLOBAL
1065 hrw.date_worked, ptp.time_period_id,
1066 hrw.state_name,
1067 hrw.county_name,
1068 hrw.city_name,
1069 hrw.zip_code,
1070 -- Bug 9159142
1071 -- Added the following columns to pick up input values.
1072 hsw.attribute1,
1073 hsw.attribute2,
1074 hsw.attribute3,
1075 hsw.attribute4,
1076 hsw.attribute5,
1077 hsw.attribute6,
1078 hsw.attribute7,
1079 hsw.attribute8,
1080 hsw.attribute9,
1081 hsw.attribute10,
1082 hsw.attribute11,
1083 hsw.attribute12,
1084 hsw.attribute13,
1085 hsw.attribute14,
1086 hsw.attribute15
1087 FROM hxt_timecards_x tim,
1088 per_time_periods ptp,
1089 hxt_det_hours_worked_x hrw,
1090 hxt_sum_hours_worked_x hsw,
1091 hr_lookups hcl, --GLOBAL
1092 per_assignments_f asm,
1093 pay_element_types_f elt,
1094 hxt_pay_element_types_f_ddf_v eltv, --SIR65
1095 pay_cost_allocation_keyflex pcak,
1096 hr_locations_all_tl loct, --FORMS60
1097 hr_locations_no_join loc, --FORMS60
1098 fnd_flex_values ffvr
1099 WHERE hrw.ffv_rate_code_id = ffvr.flex_value_id(+)
1100 AND hrw.location_id = loc.location_id(+)
1101 AND hsw.id = hrw.parent_id
1102 AND loc.location_id = loct.location_id(+)
1103 AND DECODE (loct.location_id, NULL, '1', loct.LANGUAGE) =
1104 DECODE (loct.location_id, NULL, '1', USERENV ('LANG'))
1105
1106 --END FORMS60
1107
1108 AND hrw.ffv_cost_center_id =
1109 pcak.cost_allocation_keyflex_id(+)
1110 AND hrw.date_worked BETWEEN elt.effective_start_date
1111 AND elt.effective_end_date
1112 AND hrw.element_type_id = elt.element_type_id
1113 AND elt.element_type_id = eltv.element_type_id
1114 AND hrw.date_worked BETWEEN eltv.effective_start_date
1115
1116 AND eltv.effective_end_date
1117 AND hrw.date_worked BETWEEN asm.effective_start_date
1118 AND asm.effective_end_date
1119 AND hrw.assignment_id = asm.assignment_id
1120 AND hrw.amount IS NULL
1121
1122
1123 AND hrw.tim_id = tim.id
1124 AND tim.id = p_tim_id
1125 AND tim.time_period_id = ptp.time_period_id
1126
1127 --BEGIN GLOBAL
1128 AND hrw.date_worked BETWEEN NVL (
1129 hcl.start_date_active(+),
1130 hrw.date_worked
1131 )
1132 AND NVL (
1133 hcl.end_date_active(+),
1134 hrw.date_worked
1135 )
1136 AND hcl.lookup_type(+) = 'ELE_ENTRY_REASON'
1137 AND hcl.lookup_code(+) = hrw.fcl_earn_reason_code
1138
1139 --END GLOBAL
1140 ORDER BY hrw.id; --SIR95
1141
1142
1143 -- select current amounts by detail where hours=0 and amount is not null
1144 CURSOR current_amount (p_tim_id NUMBER)
1145 IS
1146 SELECT asm.assignment_number, elt.element_name, --FORMS60
1147 DECODE (
1148 SIGN (
1149 DECODE (
1150 SIGN (
1151 ptp.start_date
1152 - asm.effective_start_date
1153 ),
1154 1, ptp.start_date,
1155 asm.effective_start_date
1156 )
1157 - elt.effective_start_date
1158 ),
1159 1, DECODE (
1160 SIGN ( ptp.start_date
1161 - asm.effective_start_date),
1162 1, ptp.start_date,
1163 asm.effective_start_date
1164 ),
1165 elt.effective_start_date
1166 )
1167 from_date,
1168 DECODE (
1169 SIGN (
1170 DECODE (
1171 SIGN ( ptp.end_date
1172 - asm.effective_end_date),
1173 -1, ptp.end_date,
1174 asm.effective_end_date
1175 )
1176 - elt.effective_end_date
1177 ),
1178 -1, DECODE (
1179 SIGN ( ptp.end_date
1180 - asm.effective_end_date),
1181 -1, ptp.end_date,
1182 asm.effective_end_date
1183 ),
1184 elt.effective_end_date
1185 ) TO_DATE,
1186 rate_multiple, hrw.hourly_rate,
1187
1188
1189 loct.location_code locality_worked, --FORMS60
1190 ffvr.flex_value rate_code, hrw.separate_check_flag,
1191 hrw.fcl_tax_rule_code tax_separately_flag,
1192 hrw.hours hours_worked, hrw.amount amount,
1193 hrw.assignment_id, hrw.ffv_cost_center_id,
1194
1195 pcak.concatenated_segments, pcak.segment1, pcak.segment2,
1196 pcak.segment3, pcak.segment4, pcak.segment5, pcak.segment6,
1197 pcak.segment7, pcak.segment8, pcak.segment9, pcak.segment10,
1198 pcak.segment11, pcak.segment12, pcak.segment13,
1199 pcak.segment14, pcak.segment15, pcak.segment16,
1200 pcak.segment17, pcak.segment18, pcak.segment19,
1201 pcak.segment20, pcak.segment21, pcak.segment22,
1202 pcak.segment23, pcak.segment24, pcak.segment25,
1203 pcak.segment26, pcak.segment27, pcak.segment28,
1204 pcak.segment29, pcak.segment30, hrw.element_type_id,
1205
1206
1207
1208 hrw.location_id, hrw.ffv_rate_code_id,
1209
1210 asm.effective_end_date asm_effective_end_date,
1211 elt.effective_end_date elt_effective_end_date,
1212 hrw.parent_id, hrw.ROWID hrw_rowid, -- OHM180
1213 hcl.meaning reason, --GLOBAL
1214 hrw.date_worked, ptp.time_period_id,
1215 hrw.state_name,
1216 hrw.county_name,
1217 hrw.city_name,
1218 hrw.zip_code
1219 FROM hxt_timecards_x tim,
1220 per_time_periods ptp,
1221 hxt_det_hours_worked_x hrw,
1222 hr_lookups hcl, --GLOBAL
1223 per_assignments_f asm,
1224 pay_element_types_f elt,
1225 pay_cost_allocation_keyflex pcak,
1226
1227
1228 hr_locations_all_tl loct, --FORMS60
1229 hr_locations_no_join loc, --FORMS60
1230 fnd_flex_values ffvr
1231 WHERE hrw.ffv_rate_code_id = ffvr.flex_value_id(+)
1232 AND hrw.location_id = loc.location_id(+)
1233
1234 --BEGIN FORMS60
1235 AND loc.location_id = loct.location_id(+)
1236 AND DECODE (loct.location_id, NULL, '1', loct.LANGUAGE) =
1237 DECODE (loct.location_id, NULL, '1', USERENV ('LANG'))
1238
1239 --END FORMS60
1240
1241 AND hrw.ffv_cost_center_id =
1242 pcak.cost_allocation_keyflex_id(+)
1243 AND hrw.date_worked BETWEEN elt.effective_start_date
1244 AND elt.effective_end_date
1245 AND hrw.element_type_id = elt.element_type_id
1246 AND hrw.date_worked BETWEEN asm.effective_start_date
1247 AND asm.effective_end_date
1248 AND hrw.assignment_id = asm.assignment_id
1249 AND hrw.amount IS NOT NULL
1250
1251
1252 AND hrw.tim_id = tim.id
1253 AND tim.id = p_tim_id
1254 AND tim.time_period_id = ptp.time_period_id
1255
1256 --BEGIN GLOBAL
1257 AND hrw.date_worked BETWEEN NVL (
1258 hcl.start_date_active(+),
1259 hrw.date_worked
1260 )
1261 AND NVL (
1262 hcl.end_date_active(+),
1263 hrw.date_worked
1264 )
1265 AND hcl.lookup_type(+) = 'ELE_ENTRY_REASON'
1266 AND hcl.lookup_code(+) = hrw.fcl_earn_reason_code
1267
1268 --END GLOBAL
1269 ORDER BY hrw.id; --SIR95
1270
1271
1272 -- select previous (before retro) hours by detail where hours<>0 and amount is null
1273 -- Bug 9159142
1274 -- Changed the below cursor to take in IV attributes also,
1275 -- and compare them while picking up records.
1276
1277
1278 -- Bug 12655429
1279 -- Corrected the condition linking hsw and hrw.
1280 -- With the earlier check, correct records used to be returned
1281 -- only when Summary and details are changed (Meaning edits to the
1282 -- entries from Self Service). With the new condition,
1283 -- even when the SS entry is not edited, and Summary does not change
1284 -- details are returned.
1285
1286 CURSOR prev_hours (
1287 p_tim_id NUMBER,
1288 p_assignment_id NUMBER, -- fk - assignment_number
1289 p_asm_effective_end_date DATE,
1290 p_ffv_cost_center_id NUMBER, -- fk - cost_center_code
1291 p_element_type_id NUMBER, -- fk - element_name
1292 p_elt_effective_end_date DATE,
1293 p_from_date DATE,
1294 p_hourly_rate NUMBER,
1295
1296
1297 p_location_id NUMBER, -- fk - locality_worked
1298 p_ffv_rate_code_id NUMBER, -- fk - rate_code
1299 p_rate_multiple NUMBER,
1300 p_separate_check_flag VARCHAR2,
1301 p_fcl_tax_rule_code VARCHAR2,
1302 p_to_date DATE,
1303 p_parent_id NUMBER,
1304 p_state_name VARCHAR2,
1305 p_COUNTY_name VARCHAR2,
1306 p_CITY_name VARCHAR2,
1307 p_ZIP_CODE VARCHAR2 ,
1308 p_attribute_list VARCHAR2 )
1309 IS
1310 SELECT hrw.hours hours_worked, hrw.ROWID hrw_rowid -- OHM199
1311 FROM hxt_timecards_x tim,
1312 per_time_periods ptp,
1313 hxt_det_hours_worked_f hrw,
1314 hxt_sum_hours_worked_f hsw,
1315 per_assignments_f asm,
1316 pay_element_types_f elt
1317 WHERE hrw.assignment_id = p_assignment_id
1318 AND asm.assignment_id = hrw.assignment_id
1319 AND hrw.parent_id = hsw.id
1320 -- Bug 12655429
1321 --AND hsw.effective_end_date = hrw.effective_end_date
1322 AND hrw.effective_start_date <= hsw.effective_end_date
1323 AND hrw.effective_end_date >= hsw.effective_start_date
1324 AND NVL(p_attribute_list,'XXX') =
1325 NVL(hsw.attribute1||
1326 hsw.attribute2||
1327 hsw.attribute3||
1328 hsw.attribute4||
1329 hsw.attribute5||
1330 hsw.attribute6||
1331 hsw.attribute7||
1332 hsw.attribute8||
1333 hsw.attribute9||
1334 hsw.attribute10||
1335 hsw.attribute11||
1336 hsw.attribute12||
1337 hsw.attribute13||
1338 hsw.attribute14||
1339 hsw.attribute15,'XXX')
1340 AND asm.effective_end_date = p_asm_effective_end_date
1341 AND NVL (hrw.ffv_cost_center_id, 999999999999999) =
1342 NVL (p_ffv_cost_center_id, 999999999999999)
1343 AND hrw.element_type_id = p_element_type_id
1344 AND elt.element_type_id = hrw.element_type_id
1345 AND elt.effective_end_date = p_elt_effective_end_date
1346 AND p_from_date =
1347 DECODE (
1348 SIGN (
1349 DECODE (
1350 SIGN (
1351 ptp.start_date
1352 - asm.effective_start_date
1353 ),
1354 1, ptp.start_date,
1355 asm.effective_start_date
1356 )
1357 - elt.effective_start_date
1358 ),
1359 1, DECODE (
1360 SIGN (
1361 ptp.start_date
1362 - asm.effective_start_date
1363 ),
1364 1, ptp.start_date,
1365 asm.effective_start_date
1366 ),
1367 elt.effective_start_date
1368 )
1369 AND NVL (hrw.hourly_rate, 999999999999999) =
1370 NVL (p_hourly_rate, 999999999999999)
1371
1372 AND NVL (hrw.location_id, 999999999999999) =
1373 NVL (p_location_id, 999999999999999)
1374 AND NVL (hrw.ffv_rate_code_id, 999999999999999) =
1375 NVL (p_ffv_rate_code_id, 999999999999999)
1376 AND NVL (hrw.rate_multiple, 999999999999999) =
1377 NVL (p_rate_multiple, 999999999999999)
1378 AND NVL (hrw.separate_check_flag, 'ZZZZZZZZZZ') =
1379 NVL (p_separate_check_flag, 'ZZZZZZZZZZ')
1380 AND NVL (hrw.fcl_tax_rule_code, 'ZZZZZZZZZZ') =
1381 NVL (p_fcl_tax_rule_code, 'ZZZZZZZZZZ')
1382 AND p_to_date =
1383 DECODE (
1384 SIGN (
1385 DECODE (
1386 SIGN (
1387 ptp.end_date
1388 - asm.effective_end_date
1389 ),
1390 -1, ptp.end_date,
1391 asm.effective_end_date
1392 )
1393 - elt.effective_end_date
1394 ),
1395 -1, DECODE (
1396 SIGN (
1397 ptp.end_date
1398 - asm.effective_end_date
1399 ),
1400 -1, ptp.end_date,
1401 asm.effective_end_date
1402 ),
1403 elt.effective_end_date
1404 )
1405 AND hrw.date_worked BETWEEN elt.effective_start_date
1406 AND elt.effective_end_date
1407 AND hrw.date_worked BETWEEN asm.effective_start_date
1408 AND asm.effective_end_date
1409 AND hrw.amount IS NULL
1410 AND hrw.parent_id = p_parent_id
1411 AND hrw.tim_id = tim.id
1412 AND tim.id = p_tim_id
1413 AND tim.time_period_id = ptp.time_period_id
1414 AND hrw.pay_status = 'C'
1415 AND NVL (hrw.state_name, 'ZZZZZZZZZZ') =
1416 NVL (p_state_name, 'ZZZZZZZZZZ')
1417 AND NVL (hrw.county_name, 'ZZZZZZZZZZ') =
1418 NVL (p_county_name, 'ZZZZZZZZZZ')
1419 AND NVL (hrw.city_name, 'ZZZZZZZZZZ') =
1420 NVL (p_city_name, 'ZZZZZZZZZZ')
1421 AND NVL (hrw.zip_code, 'ZZZZZZZZZZ') =
1422 NVL (p_zip_code, 'ZZZZZZZZZZ')
1423
1424 ORDER BY hrw.id; --SIR95
1425
1426
1427 -- select previous (before retro) amounts by detail where hours=0 and amount<>0
1428 CURSOR prev_amount (
1429 p_tim_id NUMBER,
1430 p_assignment_id NUMBER, -- fk - assignment_number
1431 p_asm_effective_end_date DATE,
1432 p_ffv_cost_center_id NUMBER, -- fk - cost_center_code
1433 p_element_type_id NUMBER, -- fk - element_name
1434 p_elt_effective_end_date DATE,
1435 p_from_date DATE,
1436 p_hourly_rate NUMBER,
1437
1438
1439 p_location_id NUMBER, -- fk - locality_worked
1440 p_ffv_rate_code_id NUMBER, -- fk - rate_code
1441 p_rate_multiple NUMBER,
1442 p_separate_check_flag VARCHAR2,
1443 p_fcl_tax_rule_code VARCHAR2,
1444 p_to_date DATE,
1445 p_parent_id NUMBER,
1446 p_state_name VARCHAR2,
1447 p_COUNTY_name VARCHAR2,
1448 p_CITY_name VARCHAR2,
1449 p_ZIP_CODE VARCHAR2 )
1450
1451 IS
1452 SELECT hrw.amount amount, hrw.ROWID hrw_rowid -- OHM199
1453 FROM hxt_timecards_x tim,
1454 per_time_periods ptp,
1455 hxt_det_hours_worked_f hrw,
1456 per_assignments_f asm,
1457 pay_element_types_f elt
1458 WHERE hrw.assignment_id = p_assignment_id
1459 AND asm.assignment_id = hrw.assignment_id
1460 AND asm.effective_end_date = p_asm_effective_end_date
1461 AND NVL (hrw.ffv_cost_center_id, 999999999999999) =
1462 NVL (p_ffv_cost_center_id, 999999999999999)
1463 AND hrw.element_type_id = p_element_type_id
1464 AND elt.element_type_id = hrw.element_type_id
1465 AND elt.effective_end_date = p_elt_effective_end_date
1466 AND p_from_date =
1467 DECODE (
1468 SIGN (
1469 DECODE (
1470 SIGN (
1471 ptp.start_date
1472 - asm.effective_start_date
1473 ),
1474 1, ptp.start_date,
1475 asm.effective_start_date
1476 )
1477 - elt.effective_start_date
1478 ),
1479 1, DECODE (
1480 SIGN (
1481 ptp.start_date
1482 - asm.effective_start_date
1483 ),
1484 1, ptp.start_date,
1485 asm.effective_start_date
1486 ),
1487 elt.effective_start_date
1488 )
1489 AND NVL (hrw.hourly_rate, 999999999999999) =
1490 NVL (p_hourly_rate, 999999999999999)
1491
1492
1493 AND NVL (hrw.location_id, 999999999999999) =
1494 NVL (p_location_id, 999999999999999)
1495 AND NVL (hrw.ffv_rate_code_id, 999999999999999) =
1496 NVL (p_ffv_rate_code_id, 999999999999999)
1497 AND NVL (hrw.rate_multiple, 999999999999999) =
1498 NVL (p_rate_multiple, 999999999999999)
1499 AND NVL (hrw.separate_check_flag, 'ZZZZZZZZZZ') =
1500 NVL (p_separate_check_flag, 'ZZZZZZZZZZ')
1501 AND NVL (hrw.fcl_tax_rule_code, 'ZZZZZZZZZZ') =
1502 NVL (p_fcl_tax_rule_code, 'ZZZZZZZZZZ')
1503 AND p_to_date =
1504 DECODE (
1505 SIGN (
1506 DECODE (
1507 SIGN (
1508 ptp.end_date
1509 - asm.effective_end_date
1510 ),
1511 -1, ptp.end_date,
1512 asm.effective_end_date
1513 )
1514 - elt.effective_end_date
1515 ),
1516 -1, DECODE (
1517 SIGN (
1518 ptp.end_date
1519 - asm.effective_end_date
1520 ),
1521 -1, ptp.end_date,
1522 asm.effective_end_date
1523 ),
1524 elt.effective_end_date
1525 )
1526 AND hrw.date_worked BETWEEN elt.effective_start_date
1527 AND elt.effective_end_date
1528 AND hrw.date_worked BETWEEN asm.effective_start_date
1529 AND asm.effective_end_date
1530 AND hrw.amount IS NOT NULL
1531 AND hrw.parent_id > 0
1532 AND hrw.parent_id = p_parent_id
1533 AND hrw.tim_id = tim.id
1534 AND tim.id = p_tim_id
1535 AND tim.time_period_id = ptp.time_period_id
1536 AND hrw.pay_status = 'C'
1537 AND NVL (hrw.state_name, 'ZZZZZZZZZZ') =
1538 NVL (p_state_name, 'ZZZZZZZZZZ')
1539 AND NVL (hrw.county_name, 'ZZZZZZZZZZ') =
1540 NVL (p_county_name, 'ZZZZZZZZZZ')
1541 AND NVL (hrw.city_name, 'ZZZZZZZZZZ') =
1542 NVL (p_city_name, 'ZZZZZZZZZZ')
1543 AND NVL (hrw.zip_code, 'ZZZZZZZZZZ') =
1544 NVL (p_zip_code, 'ZZZZZZZZZZ')
1545
1546 ORDER BY hrw.id; --SIR95
1547
1548 l_return NUMBER;
1549
1550 --BSE128 l_hours_rec current_hours%ROWTYPE;
1551 l_prev_hours_rec prev_hours%ROWTYPE;
1552
1553 --BSE128 l_amount_rec current_amount%ROWTYPE;
1554 l_prev_amount_rec prev_amount%ROWTYPE;
1555 l_expired_pay_status CHAR (1);
1556 -- l_nextval NUMBER (15);
1557 l_batch_line_id NUMBER (15);
1558 l_hours_to_send NUMBER (7, 3);
1559 l_amount_to_send NUMBER (15, 5);
1560 l_retcode NUMBER; /* BSE107 */
1561 l_location VARCHAR2 (20);
1562
1563 l_batch_sequence PAY_BATCH_LINES.BATCH_SEQUENCE%TYPE;
1564
1565 BEGIN
1566
1567 -- Bug 8888777
1568
1569 g_iv_upgrade := hxt_batch_process.get_upgrade_status(p_batch_id);
1570
1571 hxt_util.DEBUG ('retro_mix started.'); -- debug only --HXT115
1572
1573 /************************************************************/
1574 -- Step 1 - retro processing where hours<>0, amount=0
1575 /************************************************************/
1576 l_location := 'Step 1A';
1577
1578 -- bug 3217343 fix BEGIN
1579 -- get the next batch_seq for this batch_id only once and increment the
1580 -- sequence by 1 whenever insert_pay_batch_lines is called.
1581 -- This way the multiple expensive calls to
1582 -- pay_paywsqee_pkg.next_batch_sequence in insert_pay_batch_lines procedure
1583 -- can be avoided.
1584
1585 l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(p_batch_id);
1586
1587 -- bug 3217343 fix END
1588 FOR l_hours_rec IN current_hours (p_tim_id)
1589 LOOP
1590 hxt_util.DEBUG (
1591 'retro row is '
1592 || l_hours_rec.element_name
1593 || ' '
1594 || TO_CHAR (l_hours_rec.hours_worked)
1595 || ' '
1596 ); -- debug only --HXT115
1597 l_hours_to_send := l_hours_rec.hours_worked;
1598
1599 -- Bug 8888777
1600 -- Added the below code to pick up the input values.
1601
1602 -- Bug 9774867
1603 -- Added a TO_CHAR conversion to the indexes below.
1604
1605 OPEN get_input_values(l_hours_rec.parent_id);
1606 FETCH get_input_values INTO g_xiv_table(TO_CHAR(l_hours_rec.parent_id));
1607 CLOSE get_input_values;
1608 IF g_xiv_table.EXISTS(TO_CHAR(l_hours_rec.parent_id))
1609 AND g_debug
1610 THEN
1611 hr_utility.trace('parent = '||l_hours_rec.parent_id);
1612 hr_utility.trace('attribute 1 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute1 );
1613 hr_utility.trace('attribute 2 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute2 );
1614 hr_utility.trace('attribute 3 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute3 );
1615 hr_utility.trace('attribute 4 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute4 );
1616 hr_utility.trace('attribute 5 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute5 );
1617 hr_utility.trace('attribute 6 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute6 );
1618 hr_utility.trace('attribute 7 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute7 );
1619 hr_utility.trace('attribute 8 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute8 );
1620 hr_utility.trace('attribute 9 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute9 );
1621 hr_utility.trace('attribute 10 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute10 );
1622 hr_utility.trace('attribute 11 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute11 );
1623 hr_utility.trace('attribute 12 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute12 );
1624 hr_utility.trace('attribute 13 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute13 );
1625 hr_utility.trace('attribute 14 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute14 );
1626 hr_utility.trace('attribute 15 ='||g_xiv_table(TO_CHAR(l_hours_rec.parent_id)).attribute15 );
1627 END IF;
1628
1629
1630
1631 OPEN prev_hours (
1632 p_tim_id,
1633 l_hours_rec.assignment_id, -- fk - assignment_number
1634 l_hours_rec.asm_effective_end_date,
1635 l_hours_rec.ffv_cost_center_id, -- fk - cost_center_code
1636 l_hours_rec.element_type_id, -- fk - element_name
1637 l_hours_rec.elt_effective_end_date,
1638 l_hours_rec.from_date,
1639 l_hours_rec.hourly_rate,
1640
1641 /*TA36 l_hours_rec.ffv_labor_account_id,*/ -- fk - labor_dist_code
1642 l_hours_rec.location_id, -- fk - locality_worked
1643 l_hours_rec.ffv_rate_code_id, -- fk - rate_code
1644 l_hours_rec.rate_multiple,
1645 l_hours_rec.separate_check_flag,
1646 l_hours_rec.tax_separately_flag, -- fcl_tax_rule_code
1647 l_hours_rec.TO_DATE,
1648 l_hours_rec.parent_id,
1649 l_hours_rec.state_name, --dd
1650 l_hours_rec.county_name,
1651 l_hours_rec.city_name,
1652 l_hours_rec.zip_code,
1653 l_hours_rec.attribute1||
1654 l_hours_rec.attribute2||
1655 l_hours_rec.attribute3||
1656 l_hours_rec.attribute4||
1657 l_hours_rec.attribute5||
1658 l_hours_rec.attribute6||
1659 l_hours_rec.attribute7||
1660 l_hours_rec.attribute8||
1661 l_hours_rec.attribute9||
1662 l_hours_rec.attribute10||
1663 l_hours_rec.attribute11||
1664 l_hours_rec.attribute12||
1665 l_hours_rec.attribute13||
1666 l_hours_rec.attribute14||
1667 l_hours_rec.attribute15
1668 );
1669 FETCH prev_hours INTO l_prev_hours_rec;
1670
1671 IF prev_hours%FOUND
1672 THEN
1673 hxt_util.DEBUG (
1674 'orig row is '
1675 || TO_CHAR (l_prev_hours_rec.hours_worked)
1676 || ' '
1677 ); -- debug only --HXT115
1678 l_hours_to_send :=
1679 l_hours_rec.hours_worked
1680 - l_prev_hours_rec.hours_worked;
1681 l_location := 'Step 1B';
1682
1683
1684 -- begin OHM199
1685 -- Bug 12919783
1686 -- The new column here, retro_batch_id_2 records the retro
1687 -- batch that marks these records to A and D.
1688 -- We need this for Rollback Process.
1689 -- In rollback process, we would search for these retro_batch_id_2 s
1690 -- and move A and D back to 'C', to reverse the data.
1691
1692 IF l_hours_to_send = 0
1693 THEN
1694 UPDATE hxt_det_hours_worked_f
1695 SET pay_status = 'D',
1696 last_update_date = SYSDATE,
1697 retro_batch_id_2 = p_batch_id
1698 WHERE ROWID = l_prev_hours_rec.hrw_rowid
1699 -- ADDED BY MV: IF THERE IS ONLY ONE ROW, PREV and CURR records
1700 -- are the same; we should not update such records.
1701 AND l_prev_hours_rec.hrw_rowid <> l_hours_rec.hrw_rowid;
1702 ELSE
1703 UPDATE hxt_det_hours_worked_f
1704 SET pay_status = 'A',
1705 last_update_date = SYSDATE,
1706 retro_batch_id_2 = p_batch_id
1707 WHERE ROWID = l_prev_hours_rec.hrw_rowid;
1708 END IF;
1709
1710 -- end OHM199
1711 END IF;
1712
1713 hxt_util.DEBUG ( ' hours to send - '
1714 || TO_CHAR (l_hours_to_send)); -- debug only --HXT115
1715 l_location := 'Step 1C';
1716
1717 IF (l_hours_to_send <> 0)
1718 THEN
1719 --begin SIR65
1720 IF l_hours_rec.hxt_earning_category NOT IN ('REG', 'OVT', 'ABS')
1721 THEN
1722 IF l_hours_rec.hxt_premium_type = 'FACTOR'
1723 THEN
1724 IF l_hours_rec.rate_multiple IS NULL
1725 THEN
1726 l_hours_rec.rate_multiple :=
1727 l_hours_rec.hxt_premium_amount;
1728 END IF;
1729
1730 IF l_hours_rec.hourly_rate IS NULL
1731 THEN
1732 l_retcode :=
1733 hxt_td_util.get_hourly_rate (
1734 l_hours_rec.date_worked,
1735 l_hours_rec.time_period_id,
1736 l_hours_rec.assignment_id,
1737 l_hours_rec.hourly_rate
1738 );
1739 END IF;
1740 ELSIF l_hours_rec.hxt_premium_type = 'RATE'
1741 THEN
1742 IF l_hours_rec.hourly_rate IS NULL
1743 THEN
1744 l_hours_rec.hourly_rate :=
1745 l_hours_rec.hxt_premium_amount;
1746 END IF;
1747 END IF;
1748 ELSE
1749 --end SIR65
1750 --BEGIN BSE107 - OHM SPR200
1751 IF l_hours_rec.hourly_rate IS NULL
1752 THEN -- OHM205
1753 l_retcode :=
1754 hxt_td_util.get_hourly_rate (
1755 l_hours_rec.date_worked,
1756 l_hours_rec.time_period_id,
1757 l_hours_rec.assignment_id,
1758 l_hours_rec.hourly_rate
1759 );
1760 END IF; -- OHM205
1761 --END BSE107 - OHM SPR200
1762 END IF; --SIR65
1763
1764
1765 --BEGIN GLOBAL
1766 -- select pay_pdt_batch_lines_s.nextval
1767 -- SELECT pay_batch_lines_s.NEXTVAL
1768
1769 --END GLOBAL
1770 -- INTO l_nextval
1771 -- FROM DUAL;
1772
1773 l_location := 'Step 1D';
1774 hxt_util.DEBUG (' insert hours to paymix.'); -- debug only --HXT115
1775
1776 --BEGIN GLOBAL
1777 -- INSERT into pay_pdt_batch_lines
1778 -- (batch_id, line_id,
1779 -- assignment_number, adjustment_type_code,
1780 -- amount,
1781 -- apply_this_period,
1782 -- cost_allocation_keyflex_id,concatenated_segments,
1783 -- segment1,segment2,segment3,segment4,
1784 -- segment5,segment6,segment7,segment8,
1785 -- segment9,segment10,segment11,segment12,
1786 -- segment13,segment14,segment15,segment16,
1787 -- segment17,segment18,segment19,segment20,
1788 -- segment21,segment22,segment23,segment24,
1789 -- segment25,segment26,segment27,segment28,
1790 -- segment29,segment30,
1791 -- element_name, from_date,
1792 -- to_date, hourly_rate, inc_asc_balance,
1793 -- labor_dist_code,
1794 -- line_status, locality_worked, new_salary, pay_effective_date,
1795 -- pcnt_increase, rate_code, rate_multiple, rating_code,
1796 -- separate_check_flag, shift_type, state_worked,
1797 -- tax_separately_flag, vol_ded_proc_ovd,
1798 -- hours_worked)
1799 -- VALUES(
1800 -- p_batch_id, l_nextval,
1801 -- l_hours_rec.assignment_number, '',
1802 -- l_hours_rec.amount,
1803 -- '',
1804 -- l_hours_rec.ffv_cost_center_id,l_hours_rec.concatenated_segments,
1805 -- l_hours_rec.segment1,l_hours_rec.segment2,l_hours_rec.segment3,l_hours_rec.segment4,
1806 -- l_hours_rec.segment5,l_hours_rec.segment6,l_hours_rec.segment7,l_hours_rec.segment8,
1807 -- l_hours_rec.segment9,l_hours_rec.segment10,l_hours_rec.segment11,l_hours_rec.segment12,
1808 -- l_hours_rec.segment13,l_hours_rec.segment14,l_hours_rec.segment15,l_hours_rec.segment16,
1809 -- l_hours_rec.segment17,l_hours_rec.segment18,l_hours_rec.segment19,l_hours_rec.segment20,
1810 -- l_hours_rec.segment21,l_hours_rec.segment22,l_hours_rec.segment23,l_hours_rec.segment24,
1811 -- l_hours_rec.segment25,l_hours_rec.segment26,l_hours_rec.segment27,l_hours_rec.segment28,
1812 -- l_hours_rec.segment29,l_hours_rec.segment30,
1813 -- l_hours_rec.element_name, '',
1814 -- '', l_hours_rec.hourly_rate, '',
1815 -- /*TA36l_hours_rec.labor_dist_code*/ NULL,
1816 -- '', l_hours_rec.locality_worked, '', '',
1817 -- '', l_hours_rec.rate_code, l_hours_rec.rate_multiple, '',
1818 -- l_hours_rec.separate_check_flag, '', '',
1819 -- l_hours_rec.tax_separately_flag, '',
1820 -- l_hours_to_send
1821 -- );
1822 -- Bug 8888777
1823 -- Added parent_id in the below call.
1824 insert_pay_batch_lines (
1825 p_batch_id,
1826 l_batch_line_id,
1827 l_hours_rec.assignment_id,
1828 l_hours_rec.assignment_number,
1829 l_hours_rec.amount,
1830 l_hours_rec.ffv_cost_center_id,
1831 l_hours_rec.concatenated_segments,
1832 l_hours_rec.segment1,
1833 l_hours_rec.segment2,
1834 l_hours_rec.segment3,
1835 l_hours_rec.segment4,
1836 l_hours_rec.segment5,
1837 l_hours_rec.segment6,
1838 l_hours_rec.segment7,
1839 l_hours_rec.segment8,
1840 l_hours_rec.segment9,
1841 l_hours_rec.segment10,
1842 l_hours_rec.segment11,
1843 l_hours_rec.segment12,
1844 l_hours_rec.segment13,
1845 l_hours_rec.segment14,
1846 l_hours_rec.segment15,
1847 l_hours_rec.segment16,
1848 l_hours_rec.segment17,
1849 l_hours_rec.segment18,
1850 l_hours_rec.segment19,
1851 l_hours_rec.segment20,
1852 l_hours_rec.segment21,
1853 l_hours_rec.segment22,
1854 l_hours_rec.segment23,
1855 l_hours_rec.segment24,
1856 l_hours_rec.segment25,
1857 l_hours_rec.segment26,
1858 l_hours_rec.segment27,
1859 l_hours_rec.segment28,
1860 l_hours_rec.segment29,
1861 l_hours_rec.segment30,
1862 l_hours_rec.element_type_id,
1863 l_hours_rec.element_name,
1864 l_hours_rec.hourly_rate,
1865 l_hours_rec.locality_worked,
1866 l_hours_rec.rate_code,
1867 l_hours_rec.rate_multiple,
1868 l_hours_rec.separate_check_flag,
1869 l_hours_rec.tax_separately_flag,
1870 l_hours_to_send,
1871 l_hours_rec.date_worked,
1872 l_hours_rec.reason,
1873 l_batch_sequence,
1874 l_hours_rec.state_name,
1875 l_hours_rec.county_name,
1876 l_hours_rec.city_name,
1877 l_hours_rec.zip_code,
1878 l_hours_rec.parent_id
1879 );
1880
1881 --END GLOBAL
1882 l_location := 'Step 1E';
1883
1884
1885 -- begin OHM180
1886 UPDATE hxt_det_hours_worked_f
1887 SET retro_pbl_line_id = l_batch_line_id
1888 WHERE ROWID = l_hours_rec.hrw_rowid;
1889
1890 -- end OHM180
1891
1892 l_batch_sequence := l_batch_sequence + 1;
1893
1894 END IF;
1895
1896 CLOSE prev_hours;
1897 END LOOP;
1898
1899
1900 /************************************************************/
1901 -- Step 2 - retro processing where hours=0, amount<>0
1902 /************************************************************/
1903 l_location := 'Step 2A';
1904
1905 l_batch_sequence := l_batch_sequence + 1;
1906
1907 FOR l_amount_rec IN current_amount (p_tim_id)
1908 LOOP
1909 hxt_util.DEBUG (
1910 'retro row is '
1911 || l_amount_rec.element_name
1912 || ' '
1913 || TO_CHAR (l_amount_rec.amount)
1914 || ' '
1915 ); -- debug only --HXT115
1916 l_amount_to_send := l_amount_rec.amount;
1917 OPEN prev_amount (
1918 p_tim_id,
1919 l_amount_rec.assignment_id, -- fk - assignment_number
1920 l_amount_rec.asm_effective_end_date,
1921 l_amount_rec.ffv_cost_center_id, -- fk - cost_center_code
1922 l_amount_rec.element_type_id, -- fk - element_name
1923 l_amount_rec.elt_effective_end_date,
1924 l_amount_rec.from_date,
1925 l_amount_rec.hourly_rate,
1926
1927 /*TA36 l_amount_rec.ffv_labor_account_id,*/ -- fk - labor_dist_code
1928 l_amount_rec.location_id, -- fk - locality_worked
1929 l_amount_rec.ffv_rate_code_id, -- fk - rate_code
1930 l_amount_rec.rate_multiple,
1931 l_amount_rec.separate_check_flag,
1932 l_amount_rec.tax_separately_flag, -- fcl_tax_rule_code
1933 l_amount_rec.TO_DATE,
1934 l_amount_rec.parent_id,
1935 l_amount_rec.state_name, --dd
1936 l_amount_rec.county_name,
1937 l_amount_rec.city_name,
1938 l_amount_rec.zip_code
1939
1940 );
1941 FETCH prev_amount INTO l_prev_amount_rec;
1942
1943 IF prev_amount%FOUND
1944 THEN
1945 hxt_util.DEBUG (
1946 'orig row is '
1947 || TO_CHAR (l_prev_amount_rec.amount)
1948 || ' '
1949 ); -- debug only --HXT115
1950 l_amount_to_send :=
1951 l_amount_rec.amount
1952 - l_prev_amount_rec.amount;
1953 l_location := 'Step 2B';
1954
1955
1956 -- begin OHM199
1957 IF l_amount_to_send = 0
1958 THEN
1959 UPDATE hxt_det_hours_worked_f
1960 SET pay_status = 'D',
1961 last_update_date = SYSDATE
1962 WHERE ROWID = l_prev_amount_rec.hrw_rowid
1963 -- ADDED BY MV: IF THERE IS ONLY ONE ROW, PREV and CURR records
1964 -- are the same; we should not update such records.
1965 AND l_prev_amount_rec.hrw_rowid <> l_amount_rec.hrw_rowid;
1966 ELSE
1967 UPDATE hxt_det_hours_worked_f
1968 SET pay_status = 'A',
1969 last_update_date = SYSDATE
1970 WHERE ROWID = l_prev_amount_rec.hrw_rowid;
1971 END IF;
1972
1973 -- end OHM199
1974 END IF;
1975
1976 hxt_util.DEBUG ( ' amount to send - '
1977 || TO_CHAR (l_amount_to_send)); -- debug only --HXT115
1978 l_location := 'Step 2C';
1979
1980 IF (l_amount_to_send <> 0)
1981 THEN
1982
1983 /* CODE ADDED PER BSE107 */
1984 --BSE130 l_retcode := HXT_TD_UTIL.get_hourly_rate(l_amount_rec.date_worked,
1985 --BSE130 l_amount_rec.time_period_id,
1986 --BSE130 l_amount_rec.assignment_id,
1987 --BSE130 l_amount_rec.hourly_rate);
1988
1989 /* END BSE107 */
1990 --BEGIN GLOBAL
1991 -- select pay_pdt_batch_lines_s.nextval
1992 -- SELECT pay_batch_lines_s.NEXTVAL
1993
1994 --END GLOBAL
1995 -- INTO l_nextval
1996 -- FROM DUAL;
1997
1998 l_location := 'Step 2D';
1999
2000 --BEGIN GLOBAL
2001 -- INSERT into pay_pdt_batch_lines
2002 -- (batch_id, line_id,
2003 -- assignment_number, adjustment_type_code,
2004 -- amount,
2005 -- apply_this_period,
2006 -- cost_allocation_keyflex_id,concatenated_segments,
2007 -- segment1,segment2,segment3,segment4,
2008 -- segment5,segment6,segment7,segment8,
2009 -- segment9,segment10,segment11,segment12,
2010 -- segment13,segment14,segment15,segment16,
2011 -- segment17,segment18,segment19,segment20,
2012 -- segment21,segment22,segment23,segment24,
2013 -- segment25,segment26,segment27,segment28,
2014 -- segment29,segment30,
2015 -- element_name, from_date,
2016 -- to_date, hourly_rate, inc_asc_balance,
2017 -- labor_dist_code,
2018 -- line_status, locality_worked, new_salary, pay_effective_date,
2019 -- pcnt_increase, rate_code, rate_multiple, rating_code,
2020 -- separate_check_flag, shift_type, state_worked,
2021 -- tax_separately_flag, vol_ded_proc_ovd,
2022 -- hours_worked)
2023 -- VALUES(
2024 -- p_batch_id, l_nextval,
2025 -- l_amount_rec.assignment_number, '',
2026 -- l_amount_to_send,
2027 -- '',
2028 -- l_amount_rec.ffv_cost_center_id,l_amount_rec.concatenated_segments,
2029 -- l_amount_rec.segment1,l_amount_rec.segment2,l_amount_rec.segment3,l_amount_rec.segment4,
2030 -- l_amount_rec.segment5,l_amount_rec.segment6,l_amount_rec.segment7,l_amount_rec.segment8,
2031 -- l_amount_rec.segment9,l_amount_rec.segment10,l_amount_rec.segment11,l_amount_rec.segment12,
2032 -- l_amount_rec.segment13,l_amount_rec.segment14,l_amount_rec.segment15,l_amount_rec.segment16,
2033 -- l_amount_rec.segment17,l_amount_rec.segment18,l_amount_rec.segment19,l_amount_rec.segment20,
2034 -- l_amount_rec.segment21,l_amount_rec.segment22,l_amount_rec.segment23,l_amount_rec.segment24,
2035 -- l_amount_rec.segment25,l_amount_rec.segment26,l_amount_rec.segment27,l_amount_rec.segment28,
2036 -- l_amount_rec.segment29,l_amount_rec.segment30,
2037 -- l_amount_rec.element_name, '',
2038 -- '', l_amount_rec.hourly_rate, '',
2039 --/*TA36 l_amount_rec.labor_dist_code*/NULL,
2040 -- '', l_amount_rec.locality_worked, '', '',
2041 -- '', l_amount_rec.rate_code, l_amount_rec.rate_multiple, '',
2042 -- l_amount_rec.separate_check_flag, '', '',
2043 -- l_amount_rec.tax_separately_flag, '',
2044 -- l_amount_rec.hours_worked
2045 -- );
2046 insert_pay_batch_lines (
2047 p_batch_id,
2048 l_batch_line_id,
2049 l_amount_rec.assignment_id,
2050 l_amount_rec.assignment_number,
2051 l_amount_to_send,
2052 l_amount_rec.ffv_cost_center_id,
2053 l_amount_rec.concatenated_segments,
2054 l_amount_rec.segment1,
2055 l_amount_rec.segment2,
2056 l_amount_rec.segment3,
2057 l_amount_rec.segment4,
2058 l_amount_rec.segment5,
2059 l_amount_rec.segment6,
2060 l_amount_rec.segment7,
2061 l_amount_rec.segment8,
2062 l_amount_rec.segment9,
2063 l_amount_rec.segment10,
2064 l_amount_rec.segment11,
2065 l_amount_rec.segment12,
2066 l_amount_rec.segment13,
2067 l_amount_rec.segment14,
2068 l_amount_rec.segment15,
2069 l_amount_rec.segment16,
2070 l_amount_rec.segment17,
2071 l_amount_rec.segment18,
2072 l_amount_rec.segment19,
2073 l_amount_rec.segment20,
2074 l_amount_rec.segment21,
2075 l_amount_rec.segment22,
2076 l_amount_rec.segment23,
2077 l_amount_rec.segment24,
2078 l_amount_rec.segment25,
2079 l_amount_rec.segment26,
2080 l_amount_rec.segment27,
2081 l_amount_rec.segment28,
2082 l_amount_rec.segment29,
2083 l_amount_rec.segment30,
2084 l_amount_rec.element_type_id,
2085 l_amount_rec.element_name,
2086 l_amount_rec.hourly_rate,
2087 l_amount_rec.locality_worked,
2088 l_amount_rec.rate_code,
2089 l_amount_rec.rate_multiple,
2090 l_amount_rec.separate_check_flag,
2091 l_amount_rec.tax_separately_flag,
2092 l_amount_rec.hours_worked,
2093 l_amount_rec.date_worked,
2094 l_amount_rec.reason,
2095 l_batch_sequence,
2096 l_amount_rec.state_name,
2097 l_amount_rec.county_name,
2098 l_amount_rec.city_name,
2099 l_amount_rec.zip_code
2100 );
2101
2102 --END GLOBAL
2103 l_location := 'Step 2E';
2104 hxt_util.DEBUG (' insert amount to paymix.'); -- debug only --HXT115
2105
2106
2107 -- begin OHM180
2108 UPDATE hxt_det_hours_worked_f
2109 SET retro_pbl_line_id = l_batch_line_id
2110 WHERE ROWID = l_amount_rec.hrw_rowid;
2111
2112 -- end OHM180
2113
2114 l_batch_sequence := l_batch_sequence + 1;
2115
2116 END IF;
2117
2118 CLOSE prev_amount;
2119 END LOOP;
2120
2121
2122 /************************************************************/
2123 -- Step 3 - loop thru retro rows, mark matching rows 'A' or 'D'
2124 /************************************************************/
2125 --OHM199 commented out because now we mark in loops above.
2126 --OHM199mark_prev_hours_rows (p_tim_id);
2127 --OHM199mark_prev_amount_rows (p_tim_id);
2128
2129
2130 /************************************************************/
2131 -- Step 4 - send whatever is left over as backout transactions
2132 /************************************************************/
2133
2134 g_xiv_table.DELETE;
2135
2136 l_location := 'Step 4A';
2137 back_out_leftover_hours (p_batch_id, p_tim_id);
2138 l_location := 'Step 4B';
2139 back_out_leftover_amount (p_batch_id, p_tim_id);
2140
2141 /************************************************************/
2142 -- Step 5 - mark retro rows on timecard complete
2143 /************************************************************/
2144 l_location := 'Step 5A';
2145 mark_retro_rows_complete (p_tim_id);
2146 l_location := 'Step 5B';
2147 p_sum_retcode := 0;
2148 p_err_buf := '';
2149 -- Bug 9494444
2150 snap_retrieval_details(p_batch_id,p_tim_id);
2151
2152 RETURN;
2153 EXCEPTION
2154 WHEN g_lookup_not_found
2155 THEN --SIR517 PWM 18FEB00
2156 hxt_util.DEBUG (
2157 'Oops...g_lookup_not_found in procedure retro_sum_to_mix'
2158 ); --HXT115
2159 p_err_buf := SUBSTR (fnd_message.get, 1, 65); --HXT111
2160 hxt_batch_process.insert_pay_batch_errors (
2161 p_batch_id,
2162 'VE', -- RETROPAY
2163 '',
2164 l_return
2165 );
2166 RETURN;
2167 WHEN g_error_ins_batch_lines
2168 THEN --SIR517 PWM 18FEB00
2169 hxt_util.DEBUG ('Error attempting to insert paymix information'); -- debug only --HXT115
2170 fnd_message.set_name ('HXT', 'HXT_39354_ERR_INS_PAYMX_INFO'); --HXT111
2171 fnd_message.set_token ('SQLERR', SQLERRM); --HXT111
2172 p_err_buf := SUBSTR (fnd_message.get, 1, 65); --HXT111
2173 hxt_batch_process.insert_pay_batch_errors (
2174 p_batch_id,
2175 'VE', -- RETROPAY
2176 '',
2177 l_return
2178 );
2179 hxt_util.DEBUG (' back from calling insert_pay_batch_errors'); -- debug only --HXT115
2180 RETURN;
2181 WHEN OTHERS
2182 THEN
2183 hxt_util.DEBUG (
2184 ' exception received at '
2185 || l_location
2186 || '. '
2187 || SQLERRM
2188 ); -- debug only --HXT115
2189 p_sum_retcode := 3;
2190
2191 --HXT111 p_err_buf := substr(' exception received at '||l_location||'. '||sqlerrm,1,65);
2192 fnd_message.set_name ('HXT', 'HXT_39453_EXCPT_RCVD_AT'); --HXT111
2193 fnd_message.set_token ('LOCATION', l_location); --HXT111
2194 fnd_message.set_token ('SQLERR', SQLERRM); --HXT111
2195 p_err_buf := SUBSTR (fnd_message.get, 1, 65); --HXT111
2196 hxt_batch_process.insert_pay_batch_errors (
2197 p_batch_id,
2198 'VE', -- RETROPAY
2199
2200 --HXT111 'Error attempting to insert paymix information: (' || sqlerrm || ')',
2201 '', --HXT111
2202 l_return
2203 );
2204 hxt_util.DEBUG (' back from calling insert_pay_batch_errors'); -- debug only --HXT115
2205 RETURN;
2206 END retro_sum_to_mix;
2207
2208 PROCEDURE mark_prev_hours_rows (p_tim_id IN NUMBER)
2209 IS
2210 BEGIN
2211 UPDATE hxt_det_hours_worked_f
2212 SET pay_status = 'D',
2213 last_update_date = SYSDATE
2214 WHERE ROWID IN
2215 (SELECT hrw.ROWID
2216 FROM hxt_det_hours_worked_f hrw
2217 WHERE hrw.tim_id = p_tim_id
2218 AND hrw.pay_status = 'C'
2219 AND hrw.amount IS NULL
2220 AND hrw.parent_id > 0
2221 AND EXISTS ( SELECT 'X'
2222 FROM hxt_det_hours_worked_x retro
2223 WHERE hrw.parent_id = retro.parent_id
2224 AND retro.pay_status = 'R'
2225 AND hrw.hours = retro.hours
2226 AND hrw.amount IS NULL
2227 and NVL(hrw.state_name,'ZZZZZZZZZZ')=
2228 NVL(retro.state_name,'ZZZZZZZZZZ')
2229 and NVL(hrw.county_name,'ZZZZZZZZZZ')=
2230 NVL(retro.county_name,'ZZZZZZZZZZ')
2231 and NVL(hrw.city_name,'ZZZZZZZZZZ')=
2232 NVL(retro.city_name,'ZZZZZZZZZZ')
2233 and NVL(hrw.zip_code,'ZZZZZZZZZZ')=
2234 NVL(retro.zip_code,'ZZZZZZZZZZ')
2235 AND hrw.assignment_id =
2236 retro.assignment_id
2237 AND NVL (
2238 hrw.ffv_cost_center_id,
2239 999999999999999
2240 ) = NVL (
2241 retro.ffv_cost_center_id,
2242 999999999999999
2243 )
2244 AND hrw.element_type_id =
2245 retro.element_type_id
2246 AND NVL (
2247 hrw.hourly_rate,
2248 999999999999999
2249 ) = NVL (
2250 retro.hourly_rate,
2251 999999999999999
2252 )
2253
2254 AND NVL (
2255 hrw.location_id,
2256 999999999999999
2257 ) = NVL (
2258 retro.location_id,
2259 999999999999999
2260 )
2261 AND NVL (
2262 hrw.ffv_rate_code_id,
2263 999999999999999
2264 ) = NVL (
2265 retro.ffv_rate_code_id,
2266 999999999999999
2267 )
2268 AND NVL (
2269 hrw.rate_multiple,
2270 999999999999999
2271 ) = NVL (
2272 retro.rate_multiple,
2273 999999999999999
2274 )
2275 AND NVL (
2276 hrw.separate_check_flag,
2277 'ZZZZZZZZZZ'
2278 ) = NVL (
2279 retro.separate_check_flag,
2280 'ZZZZZZZZZZ'
2281 )
2282 AND NVL (
2283 hrw.fcl_tax_rule_code,
2284 'ZZZZZZZZZZ'
2285 ) = NVL (
2286 retro.fcl_tax_rule_code,
2287 'ZZZZZZZZZZ'
2288 )));
2289
2290 UPDATE hxt_det_hours_worked_f
2291 SET pay_status = 'A',
2292 last_update_date = SYSDATE
2293 WHERE ROWID IN
2294 (SELECT hrw.ROWID
2295 FROM hxt_det_hours_worked_f hrw
2296 WHERE hrw.tim_id = p_tim_id
2297 AND hrw.pay_status = 'C'
2298 AND hrw.amount IS NULL
2299 AND hrw.parent_id > 0
2300 AND EXISTS ( SELECT 'X'
2301 FROM hxt_det_hours_worked_x retro
2302 WHERE hrw.parent_id = retro.parent_id
2303 AND retro.pay_status = 'R'
2304 AND hrw.hours <> retro.hours
2305 AND hrw.amount IS NULL
2306 AND hrw.assignment_id =
2307 retro.assignment_id
2308 and NVL(hrw.state_name,'ZZZZZZZZZZ')=
2309 NVL(retro.state_name,'ZZZZZZZZZZ')
2310 and NVL(hrw.county_name,'ZZZZZZZZZZ')=
2311 NVL(retro.county_name,'ZZZZZZZZZZ')
2312 and NVL(hrw.city_name,'ZZZZZZZZZZ')=
2313 NVL(retro.city_name,'ZZZZZZZZZZ')
2314 and NVL(hrw.zip_code,'ZZZZZZZZZZ')=
2315 NVL(retro.zip_code,'ZZZZZZZZZZ')
2316 AND NVL (
2317 hrw.ffv_cost_center_id,
2318 999999999999999
2319 ) = NVL (
2320 retro.ffv_cost_center_id,
2321 999999999999999
2322 )
2323 AND hrw.element_type_id =
2324 retro.element_type_id
2325 AND NVL (
2326 hrw.hourly_rate,
2327 999999999999999
2328 ) = NVL (
2329 retro.hourly_rate,
2330 999999999999999
2331 )
2332
2333 AND NVL (
2334 hrw.location_id,
2335 999999999999999
2336 ) = NVL (
2337 retro.location_id,
2338 999999999999999
2339 )
2340 AND NVL (
2341 hrw.ffv_rate_code_id,
2342 999999999999999
2343 ) = NVL (
2344 retro.ffv_rate_code_id,
2345 999999999999999
2346 )
2347 AND NVL (
2348 hrw.rate_multiple,
2349 999999999999999
2350 ) = NVL (
2351 retro.rate_multiple,
2352 999999999999999
2353 )
2354 AND NVL (
2355 hrw.separate_check_flag,
2356 'ZZZZZZZZZZ'
2357 ) = NVL (
2358 retro.separate_check_flag,
2359 'ZZZZZZZZZZ'
2360 )
2361 AND NVL (
2362 hrw.fcl_tax_rule_code,
2363 'ZZZZZZZZZZ'
2364 ) = NVL (
2365 retro.fcl_tax_rule_code,
2366 'ZZZZZZZZZZ'
2367 )));
2368 END mark_prev_hours_rows;
2369
2370 PROCEDURE mark_prev_amount_rows (p_tim_id IN NUMBER)
2371 IS
2372 BEGIN
2373 UPDATE hxt_det_hours_worked_f
2374 SET pay_status = 'D',
2375 last_update_date = SYSDATE
2376 WHERE ROWID IN
2377 (SELECT hrw.ROWID
2378 FROM hxt_det_hours_worked_f hrw
2379 WHERE hrw.tim_id = p_tim_id
2380 AND hrw.pay_status = 'C'
2381 AND hrw.amount IS NOT NULL
2382 AND hrw.parent_id > 0
2383 AND EXISTS ( SELECT 'X'
2384 FROM hxt_det_hours_worked_x retro
2385 WHERE hrw.parent_id = retro.parent_id
2386 AND retro.pay_status = 'R'
2387 AND hrw.amount = retro.amount
2388 AND hrw.amount IS NOT NULL
2389 AND hrw.assignment_id =
2390 retro.assignment_id
2391 and NVL(hrw.state_name,'ZZZZZZZZZZ')=
2392 NVL(retro.state_name,'ZZZZZZZZZZ')
2393 and NVL(hrw.county_name,'ZZZZZZZZZZ')=
2394 NVL(retro.county_name,'ZZZZZZZZZZ')
2395 and NVL(hrw.city_name,'ZZZZZZZZZZ')=
2396 NVL(retro.city_name,'ZZZZZZZZZZ')
2397 and NVL(hrw.zip_code,'ZZZZZZZZZZ')=
2398 NVL(retro.zip_code,'ZZZZZZZZZZ')
2399
2400 AND NVL (
2401 hrw.ffv_cost_center_id,
2402 999999999999999
2403 ) = NVL (
2404 retro.ffv_cost_center_id,
2405 999999999999999
2406 )
2407 AND hrw.element_type_id =
2408 retro.element_type_id
2409 AND NVL (
2410 hrw.hourly_rate,
2411 999999999999999
2412 ) = NVL (
2413 retro.hourly_rate,
2414 999999999999999
2415 )
2416
2417 AND NVL (
2418 hrw.location_id,
2419 999999999999999
2420 ) = NVL (
2421 retro.location_id,
2422 999999999999999
2423 )
2424 AND NVL (
2425 hrw.ffv_rate_code_id,
2426 999999999999999
2427 ) = NVL (
2428 retro.ffv_rate_code_id,
2429 999999999999999
2430 )
2431 AND NVL (
2432 hrw.rate_multiple,
2433 999999999999999
2434 ) = NVL (
2435 retro.rate_multiple,
2436 999999999999999
2437 )
2438 AND NVL (
2439 hrw.separate_check_flag,
2440 'ZZZZZZZZZZ'
2441 ) = NVL (
2442 retro.separate_check_flag,
2443 'ZZZZZZZZZZ'
2444 )
2445 AND NVL (
2446 hrw.fcl_tax_rule_code,
2447 'ZZZZZZZZZZ'
2448 ) = NVL (
2449 retro.fcl_tax_rule_code,
2450 'ZZZZZZZZZZ'
2451 )));
2452
2453 UPDATE hxt_det_hours_worked_f
2454 SET pay_status = 'A',
2455 last_update_date = SYSDATE
2456 WHERE ROWID IN
2457 (SELECT hrw.ROWID
2458 FROM hxt_det_hours_worked_f hrw
2459 WHERE hrw.tim_id = p_tim_id
2460 AND hrw.pay_status = 'C'
2461 AND hrw.amount IS NOT NULL
2462 AND hrw.parent_id > 0
2463 AND EXISTS ( SELECT 'X'
2464 FROM hxt_det_hours_worked_x retro
2465 WHERE hrw.parent_id = retro.parent_id
2466 AND retro.pay_status = 'R'
2467 AND hrw.amount <> retro.amount
2468 AND hrw.amount IS NOT NULL
2469 AND hrw.assignment_id =
2470 retro.assignment_id
2471 and NVL(hrw.state_name,'ZZZZZZZZZZ')=
2472 NVL(retro.state_name,'ZZZZZZZZZZ')
2473 and NVL(hrw.county_name,'ZZZZZZZZZZ')=
2474 NVL(retro.county_name,'ZZZZZZZZZZ')
2475 and NVL(hrw.city_name,'ZZZZZZZZZZ')=
2476 NVL(retro.city_name,'ZZZZZZZZZZ')
2477 and NVL(hrw.zip_code,'ZZZZZZZZZZ')=
2478 NVL(retro.zip_code,'ZZZZZZZZZZ')
2479
2480 AND NVL (
2481 hrw.ffv_cost_center_id,
2482 999999999999999
2483 ) = NVL (
2484 retro.ffv_cost_center_id,
2485 999999999999999
2486 )
2487 AND hrw.element_type_id =
2488 retro.element_type_id
2489 AND NVL (
2490 hrw.hourly_rate,
2491 999999999999999
2492 ) = NVL (
2493 retro.hourly_rate,
2494 999999999999999
2495 )
2496
2497 AND NVL (
2498 hrw.location_id,
2499 999999999999999
2500 ) = NVL (
2501 retro.location_id,
2502 999999999999999
2503 )
2504 AND NVL (
2505 hrw.ffv_rate_code_id,
2506 999999999999999
2507 ) = NVL (
2508 retro.ffv_rate_code_id,
2509 999999999999999
2510 )
2511 AND NVL (
2512 hrw.rate_multiple,
2513 999999999999999
2514 ) = NVL (
2515 retro.rate_multiple,
2516 999999999999999
2517 )
2518 AND NVL (
2519 hrw.separate_check_flag,
2520 'ZZZZZZZZZZ'
2521 ) = NVL (
2522 retro.separate_check_flag,
2523 'ZZZZZZZZZZ'
2524 )
2525 AND NVL (
2526 hrw.fcl_tax_rule_code,
2527 'ZZZZZZZZZZ'
2528 ) = NVL (
2529 retro.fcl_tax_rule_code,
2530 'ZZZZZZZZZZ'
2531 )));
2532 END mark_prev_amount_rows;
2533
2534 PROCEDURE mark_retro_rows_complete (p_tim_id NUMBER)
2535 IS
2536 BEGIN
2537 UPDATE hxt_det_hours_worked_f
2538 SET pay_status = 'C',
2539 last_update_date = SYSDATE
2540 WHERE ROWID IN (SELECT hrw.ROWID
2541 FROM hxt_det_hours_worked_x hrw
2542 WHERE hrw.parent_id > 0
2543 AND hrw.pay_status = 'R'
2544 AND hrw.tim_id = p_tim_id);
2545 END mark_retro_rows_complete;
2546
2547 PROCEDURE back_out_leftover_hours (p_batch_id NUMBER, p_tim_id NUMBER)
2548 IS
2549
2550 -- Bug 9159142
2551 -- Added the below cursor to pick up input values for
2552 -- leftover hours to create backout rows.
2553 CURSOR get_prev_input_values(p_id IN NUMBER)
2554 IS SELECT
2555 attribute1,
2556 attribute2,
2557 attribute3,
2558 attribute4,
2559 attribute5,
2560 attribute6,
2561 attribute7,
2562 attribute8,
2563 attribute9,
2564 attribute10,
2565 attribute11,
2566 attribute12,
2567 attribute13,
2568 attribute14,
2569 attribute15
2570 FROM hxt_sum_hours_worked_f
2571 WHERE id = p_id
2572 AND effective_end_date <> hr_general.end_of_time
2573 ORDER BY effective_end_date DESC;
2574
2575 CURSOR leftover_hours (p_tim_id NUMBER)
2576 IS
2577 SELECT hrw.ROWID hrw_rowid,
2578 NVL (hrw.retro_pbl_line_id, hrw.pbl_line_id) line_id,
2579 /* TA36 01/09/98 */
2580 asm.assignment_number, elt.element_name, --FORMS60
2581 eltv.hxt_premium_type, --SIR65
2582 eltv.hxt_premium_amount, --SIR65
2583 eltv.hxt_earning_category, --SIR65
2584 DECODE (
2585 SIGN (
2586 DECODE (
2587 SIGN ( ptp.start_date
2588 - asm.effective_start_date),
2589 1, ptp.start_date,
2590 asm.effective_start_date
2591 )
2592 - elt.effective_start_date
2593 ),
2594 1, DECODE (
2595 SIGN ( ptp.start_date
2596 - asm.effective_start_date),
2597 1, ptp.start_date,
2598 asm.effective_start_date
2599 ),
2600 elt.effective_start_date
2601 )
2602 from_date,
2603 DECODE (
2604 SIGN (
2605 DECODE (
2606 SIGN ( ptp.end_date
2607 - asm.effective_end_date),
2608 -1, ptp.end_date,
2609 asm.effective_end_date
2610 )
2611 - elt.effective_end_date
2612 ),
2613 -1, DECODE (
2614 SIGN ( ptp.end_date
2615 - asm.effective_end_date),
2616 -1, ptp.end_date,
2617 asm.effective_end_date
2618 ),
2619 elt.effective_end_date
2620 ) TO_DATE,
2621 rate_multiple, hrw.hourly_rate, hrw.ffv_cost_center_id,
2622 /* fk - cost_center_code */
2623 pcak.concatenated_segments, pcak.segment1, pcak.segment2,
2624 pcak.segment3, pcak.segment4, pcak.segment5, pcak.segment6,
2625 pcak.segment7, pcak.segment8, pcak.segment9, pcak.segment10,
2626 pcak.segment11, pcak.segment12, pcak.segment13,
2627 pcak.segment14, pcak.segment15, pcak.segment16,
2628 pcak.segment17, pcak.segment18, pcak.segment19,
2629 pcak.segment20, pcak.segment21, pcak.segment22,
2630 pcak.segment23, pcak.segment24, pcak.segment25,
2631 pcak.segment26, pcak.segment27, pcak.segment28,
2632 pcak.segment29, pcak.segment30,
2633
2634 /*TA36 ffvl.flex_value labor_dist_code,*/
2635 loct.location_code locality_worked, --FORMS60
2636 ffvr.flex_value rate_code,
2637 hrw.separate_check_flag,
2638 hrw.fcl_tax_rule_code tax_separately_flag, hrw.amount,
2639 hrw.hours hours_worked, hrw.element_type_id, --GLOBAL
2640 hcl.meaning reason, --GLOBAL
2641 hrw.date_worked, /* BSE107 */ ptp.time_period_id,
2642 /* BSE107 */
2643 hrw.assignment_id, /* BSE107 */
2644 hrw.state_name,
2645 hrw.county_name,
2646 hrw.city_name,
2647 hrw.zip_code,
2648 hrw.parent_id
2649 FROM hxt_timecards_x tim, /* SIR416 PWM 21MAR00 */
2650 per_time_periods ptp,
2651 hxt_det_hours_worked_f hrw,
2652 hr_lookups hcl, --GLOBAL
2653 per_assignments_f asm,
2654 pay_element_types_f elt,
2655 hxt_pay_element_types_f_ddf_v eltv, --SIR65
2656 pay_cost_allocation_keyflex pcak,
2657
2658 /*TA36fnd_flex_values ffvl, */
2659 hr_locations_all_tl loct, --FORMS60
2660 hr_locations_no_join loc, --FORMS60
2661 fnd_flex_values ffvr
2662 WHERE hrw.ffv_rate_code_id = ffvr.flex_value_id(+)
2663 AND hrw.location_id = loc.location_id(+)
2664
2665 --BEGIN FORMS60
2666 AND loc.location_id = loct.location_id(+)
2667 AND DECODE (loct.location_id, NULL, '1', loct.LANGUAGE) =
2668 DECODE (loct.location_id, NULL, '1', USERENV ('LANG'))
2669
2670 --END FORMS60
2671 /*TA36AND hrw.ffv_labor_account_id = ffvl.flex_value_id(+)*/
2672 AND hrw.ffv_cost_center_id = pcak.cost_allocation_keyflex_id(+)
2673 AND hrw.date_worked BETWEEN elt.effective_start_date
2674 AND elt.effective_end_date
2675 AND hrw.element_type_id = elt.element_type_id
2676
2677 /*GLOBAL AND elt.rowid=eltv.row_id --SIR65 */
2678 AND elt.element_type_id = eltv.element_type_id /* GLOBAL */
2679 AND hrw.date_worked BETWEEN eltv.effective_start_date /* GLOBAL */
2680 AND eltv.effective_end_date /* GLOBAL */
2681 AND hrw.date_worked BETWEEN asm.effective_start_date
2682 AND asm.effective_end_date
2683 AND hrw.assignment_id = asm.assignment_id
2684 AND hrw.amount IS NULL
2685
2686 /* GLOBAL AND hrw.parent_id > 0 */
2687 AND hrw.tim_id = tim.id
2688 AND tim.id = p_tim_id
2689 AND tim.time_period_id = ptp.time_period_id
2690
2691 --BEGIN GLOBAL
2692 AND hrw.date_worked BETWEEN NVL (
2693 hcl.start_date_active(+),
2694 hrw.date_worked
2695 )
2696 AND NVL (
2697 hcl.end_date_active(+),
2698 hrw.date_worked
2699 )
2700 AND hcl.lookup_type(+) = 'ELE_ENTRY_REASON'
2701 AND hcl.lookup_code(+) = hrw.fcl_earn_reason_code
2702
2703 --END GLOBAL
2704 AND hrw.pay_status = 'C'
2705 AND hrw.effective_end_date < hr_general.end_of_time; --SIR149 --FORMS60
2706
2707
2708 --BSE128 l_leftover leftover_hours %ROWTYPE;
2709 -- l_nextval NUMBER (15);
2710 l_batch_line_id NUMBER (15);
2711 l_retcode NUMBER; /* BSE107 */
2712
2713 --BEGIN GLOBAL
2714 -- l_hourly_rate pay_pdt_batch_lines.hourly_rate%TYPE := NULL; --SIR65
2715 l_hourly_rate hxt_det_hours_worked_f.hourly_rate%TYPE := NULL;
2716
2717
2718 --CURSOR rate_paid_cur(c_line_id NUMBER) IS --SIR65
2719 --SELECT pbl.hourly_rate --SIR65
2720 -- FROM pay_pdt_batch_lines pbl --SIR65
2721 -- WHERE pbl.line_id=c_line_id; --SIR65
2722 CURSOR rate_paid_cur (c_line_id NUMBER)
2723 IS
2724 SELECT pbl.value_1, pbl.value_2, pbl.value_3, pbl.value_4,
2725 pbl.value_5, pbl.value_6, pbl.value_7, pbl.value_8,
2726 pbl.value_9, pbl.value_10, pbl.value_11, pbl.value_12,
2727 pbl.value_13, pbl.value_14, pbl.value_15
2728 FROM pay_batch_lines pbl
2729 WHERE pbl.batch_line_id = c_line_id;
2730
2731 TYPE input_value_record IS RECORD (
2732 SEQUENCE pay_input_values_f.input_value_id%TYPE,
2733 NAME pay_input_values_f_tl.NAME%TYPE, --FORMS60
2734 lookup pay_input_values_f.lookup_type%TYPE);
2735
2736 TYPE input_values_table IS TABLE OF input_value_record
2737 INDEX BY BINARY_INTEGER;
2738
2739 hxt_value input_values_table;
2740
2741 TYPE pbl_values_table IS TABLE OF pay_batch_lines.value_1%TYPE
2742 INDEX BY BINARY_INTEGER;
2743
2744 pbl_value pbl_values_table;
2745
2746 --END GLOBAL
2747 l_value_meaning hr_lookups.meaning%TYPE;
2748
2749 l_batch_sequence PAY_BATCH_LINES.BATCH_SEQUENCE%TYPE;
2750
2751 BEGIN
2752
2753 l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(p_batch_id);
2754
2755 FOR l_leftover IN leftover_hours (p_tim_id)
2756 LOOP
2757 --begin SIR65
2758 -- Check PayMIX first for the Original rate paid,
2759 -- if one exists use it.
2760 -- Added to always back out the original rate sent.
2761
2762 -- Bug 9774867
2763 OPEN get_prev_input_values(l_leftover.parent_id);
2764 FETCH get_prev_input_values INTO g_xiv_table(TO_CHAR(l_leftover.parent_id));
2765 CLOSE get_prev_input_values;
2766
2767 --BEGIN GLOBAL
2768 -- Initialize tables
2769 FOR i IN 1 .. 15
2770 LOOP
2771 hxt_value (i).SEQUENCE := NULL;
2772 hxt_value (i).NAME := NULL;
2773 hxt_value (i).lookup := NULL;
2774 pbl_value (i) := NULL;
2775 END LOOP;
2776
2777
2778 -- OPEN rate_paid_cur(l_leftover.line_id);
2779 -- FETCH rate_paid_cur INTO l_hourly_rate;
2780 -- CLOSE rate_paid_cur;
2781 OPEN rate_paid_cur (l_leftover.line_id);
2782 FETCH rate_paid_cur INTO pbl_value (1),
2783 pbl_value (2),
2784 pbl_value (3),
2785 pbl_value (4),
2786 pbl_value (5),
2787 pbl_value (6),
2788 pbl_value (7),
2789 pbl_value (8),
2790 pbl_value (9),
2791 pbl_value (10),
2792 pbl_value (11),
2793 pbl_value (12),
2794 pbl_value (13),
2795 pbl_value (14),
2796 pbl_value (15);
2797 CLOSE rate_paid_cur;
2798 -- Get input values details for this element
2799 pay_paywsqee_pkg.get_input_value_details (
2800 l_leftover.element_type_id,
2801 l_leftover.date_worked,
2802 hxt_value (1).SEQUENCE,
2803 hxt_value (2).SEQUENCE,
2804 hxt_value (3).SEQUENCE,
2805 hxt_value (4).SEQUENCE,
2806 hxt_value (5).SEQUENCE,
2807 hxt_value (6).SEQUENCE,
2808 hxt_value (7).SEQUENCE,
2809 hxt_value (8).SEQUENCE,
2810 hxt_value (9).SEQUENCE,
2811 hxt_value (10).SEQUENCE,
2812 hxt_value (11).SEQUENCE,
2813 hxt_value (12).SEQUENCE,
2814 hxt_value (13).SEQUENCE,
2815 hxt_value (14).SEQUENCE,
2816 hxt_value (15).SEQUENCE,
2817 hxt_value (1).NAME,
2818 hxt_value (2).NAME,
2819 hxt_value (3).NAME,
2820 hxt_value (4).NAME,
2821 hxt_value (5).NAME,
2822 hxt_value (6).NAME,
2823 hxt_value (7).NAME,
2824 hxt_value (8).NAME,
2825 hxt_value (9).NAME,
2826 hxt_value (10).NAME,
2827 hxt_value (11).NAME,
2828 hxt_value (12).NAME,
2829 hxt_value (13).NAME,
2830 hxt_value (14).NAME,
2831 hxt_value (15).NAME,
2832 hxt_value (1).lookup,
2833 hxt_value (2).lookup,
2834 hxt_value (3).lookup,
2835 hxt_value (4).lookup,
2836 hxt_value (5).lookup,
2837 hxt_value (6).lookup,
2838 hxt_value (7).lookup,
2839 hxt_value (8).lookup,
2840 hxt_value (9).lookup,
2841 hxt_value (10).lookup,
2842 hxt_value (11).lookup,
2843 hxt_value (12).lookup,
2844 hxt_value (13).lookup,
2845 hxt_value (14).lookup,
2846 hxt_value (15).lookup
2847 );
2848
2849
2850 --
2851 -- In order to get the input-value logic work in diiferent legislations we need
2852 -- to create (SEED) new lookups for 'Hours', 'Hourly Rate', 'Rate Multiple',
2853 -- and 'Rate Code' with lookup_type of 'NAME_TRANSLATION' and lookup_code of
2854 -- 'HOURS', 'HOURLY_RATE', 'RATE_MULTIPLE' and 'RATE_CODE' respectively.
2855 -- Then the customers in different countries need to create the above input
2856 -- values with the name which is directly translated from the above names for
2857 -- OTM elements.
2858 --
2859 -- For example: In French the user must create an input value for 'Hours' to
2860 -- be 'Heures' and then to determine which input value 'Heures' is associated
2861 -- with we look at the hr_lookups and if we find an entry with lookup_type =
2862 -- 'NAME_TRANSLATIONS' and lookup_code = 'HOURS' and Meaning to be 'Heures'
2863 -- then we know that this input vale woul map to 'Hours'.
2864 --
2865 -- What need to be noted that it is the customer's responsibilty to create
2866 -- input values which are the direct translation of 'Hours','Hourly Rate',
2867 -- 'Pay Value' , 'Rate Multiple' and 'Rate Code'
2868 --
2869 FOR i IN 1 .. 15
2870 LOOP
2871
2872 -- We need to get the lookup_code for the input_value names before processing
2873 -- the further logic on the screen value for the input values.
2874 --
2875 l_value_meaning :=
2876 get_lookup_code (hxt_value (i).NAME, l_leftover.date_worked);
2877
2878 --IF hxt_value(i).name = 'Rate' THEN
2879 IF l_value_meaning = 'HOURLY_RATE'
2880 THEN
2881 l_hourly_rate := pbl_value (i);
2882 END IF;
2883 END LOOP;
2884
2885
2886 --END GLOBAL
2887
2888 IF ( l_hourly_rate IS NULL
2889 OR l_leftover.hxt_premium_type = 'FACTOR'
2890 )
2891 THEN
2892 IF l_leftover.hxt_earning_category NOT IN ('REG', 'OVT', 'ABS')
2893 THEN
2894 IF l_leftover.hxt_premium_type = 'FACTOR'
2895 THEN
2896 IF l_leftover.rate_multiple IS NULL
2897 THEN
2898 l_leftover.rate_multiple :=
2899 l_leftover.hxt_premium_amount;
2900 END IF;
2901
2902 IF l_leftover.hourly_rate IS NULL
2903 THEN
2904 l_retcode :=
2905 hxt_td_util.get_hourly_rate (
2906 l_leftover.date_worked,
2907 l_leftover.time_period_id,
2908 l_leftover.assignment_id,
2909 l_leftover.hourly_rate
2910 );
2911 END IF;
2912 ELSIF l_leftover.hxt_premium_type = 'RATE'
2913 THEN
2914 IF l_leftover.hourly_rate IS NULL
2915 THEN
2916 l_leftover.hourly_rate := l_leftover.hxt_premium_amount;
2917 END IF;
2918 END IF;
2919 ELSE
2920 --end SIR65
2921 --BEGIN BSE107 - OHM SPR200
2922 IF l_leftover.hourly_rate IS NULL
2923 THEN -- OHM205
2924 l_retcode :=
2925 hxt_td_util.get_hourly_rate (
2926 l_leftover.date_worked,
2927 l_leftover.time_period_id,
2928 l_leftover.assignment_id,
2929 l_leftover.hourly_rate
2930 );
2931 END IF; -- OHM SPR200
2932 --END BSE107 - OHM SPR200
2933 --begin SIR65
2934 END IF;
2935 ELSE
2936 l_leftover.hourly_rate := l_hourly_rate;
2937 END IF;
2938
2939 --end SIR65
2940
2941 --BEGIN GLOBAL
2942 -- select pay_pdt_batch_lines_s.nextval
2943 -- SELECT pay_batch_lines_s.NEXTVAL
2944
2945 --END GLOBAL
2946 -- INTO l_nextval
2947 -- FROM DUAL;
2948
2949
2950 --BEGIN GLOBAL
2951 -- INSERT into pay_pdt_batch_lines
2952 -- (batch_id, line_id,
2953 -- assignment_number, adjustment_type_code,
2954 -- amount,
2955 -- apply_this_period,
2956 -- cost_allocation_keyflex_id,concatenated_segments,
2957 -- segment1,segment2,segment3,segment4,
2958 -- segment5,segment6,segment7,segment8,
2959 -- segment9,segment10,segment11,segment12,
2960 -- segment13,segment14,segment15,segment16,
2961 -- segment17,segment18,segment19,segment20,
2962 -- segment21,segment22,segment23,segment24,
2963 -- segment25,segment26,segment27,segment28,
2964 -- segment29,segment30,
2965 -- element_name, from_date,
2966 -- to_date, hourly_rate, inc_asc_balance,
2967 -- labor_dist_code,
2968 -- line_status, locality_worked, new_salary, pay_effective_date,
2969 -- pcnt_increase, rate_code, rate_multiple, rating_code,
2970 -- separate_check_flag, shift_type, state_worked,
2971 -- tax_separately_flag, vol_ded_proc_ovd,
2972 -- hours_worked)
2973 -- VALUES(
2974 -- p_batch_id, l_nextval,
2975 -- l_leftover.assignment_number, '',
2976 -- l_leftover.amount,
2977 -- '',
2978 -- l_leftover.ffv_cost_center_id,l_leftover.concatenated_segments,
2979 -- l_leftover.segment1,l_leftover.segment2,l_leftover.segment3,l_leftover.segment4,
2980 -- l_leftover.segment5,l_leftover.segment6,l_leftover.segment7,l_leftover.segment8,
2981 -- l_leftover.segment9,l_leftover.segment10,l_leftover.segment11,l_leftover.segment12,
2982 -- l_leftover.segment13,l_leftover.segment14,l_leftover.segment15,l_leftover.segment16,
2983 -- l_leftover.segment17,l_leftover.segment18,l_leftover.segment19,l_leftover.segment20,
2984 -- l_leftover.segment21,l_leftover.segment22,l_leftover.segment23,l_leftover.segment24,
2985 -- l_leftover.segment25,l_leftover.segment26,l_leftover.segment27,l_leftover.segment28,
2986 -- l_leftover.segment29,l_leftover.segment30,
2987 -- l_leftover.element_name, '',
2988 -- '', l_leftover.hourly_rate, '',
2989 --/*TA36 l_leftover.labor_dist_code*/NULL,
2990 -- '', l_leftover.locality_worked, '', '',
2991 -- '', l_leftover.rate_code, l_leftover.rate_multiple, '',
2992 -- l_leftover.separate_check_flag, '', '',
2993 -- l_leftover.tax_separately_flag, '',
2994 -- 0 - l_leftover.hours_worked
2995 -- );
2996 insert_pay_batch_lines (
2997 p_batch_id,
2998 l_batch_line_id,
2999 l_leftover.assignment_id,
3000 l_leftover.assignment_number,
3001 l_leftover.amount,
3002 l_leftover.ffv_cost_center_id,
3003 l_leftover.concatenated_segments,
3004 l_leftover.segment1,
3005 l_leftover.segment2,
3006 l_leftover.segment3,
3007 l_leftover.segment4,
3008 l_leftover.segment5,
3009 l_leftover.segment6,
3010 l_leftover.segment7,
3011 l_leftover.segment8,
3012 l_leftover.segment9,
3013 l_leftover.segment10,
3014 l_leftover.segment11,
3015 l_leftover.segment12,
3016 l_leftover.segment13,
3017 l_leftover.segment14,
3018 l_leftover.segment15,
3019 l_leftover.segment16,
3020 l_leftover.segment17,
3021 l_leftover.segment18,
3022 l_leftover.segment19,
3023 l_leftover.segment20,
3024 l_leftover.segment21,
3025 l_leftover.segment22,
3026 l_leftover.segment23,
3027 l_leftover.segment24,
3028 l_leftover.segment25,
3029 l_leftover.segment26,
3030 l_leftover.segment27,
3031 l_leftover.segment28,
3032 l_leftover.segment29,
3033 l_leftover.segment30,
3034 l_leftover.element_type_id,
3035 l_leftover.element_name,
3036 l_leftover.hourly_rate,
3037 l_leftover.locality_worked,
3038 l_leftover.rate_code,
3039 l_leftover.rate_multiple,
3040 l_leftover.separate_check_flag,
3041 l_leftover.tax_separately_flag,
3042 0-l_leftover.hours_worked,
3043 l_leftover.date_worked,
3044 l_leftover.reason,
3045 l_batch_sequence,
3046 l_leftover.state_name,
3047 l_leftover.county_name,
3048 l_leftover.city_name,
3049 l_leftover.zip_code,
3050 l_leftover.parent_id
3051 );
3052
3053
3054 --END GLOBAL
3055 -- Bug 12919783
3056 -- New column retro_pbl_line_id_2 records the original retro_pbl_line_id
3057 -- before backing out.
3058 -- This comes handy during rollback.
3059 -- Eg.
3060 -- Reg 8 hrs entered in retro change.
3061 -- HXT_DET_HOURS_WORKED_F has status 'C', retro_pbl_line_id populated, retro_batch_id populated.
3062 -- This entry is zeroed out in the next change.
3063 -- Backout happens and this below update will mark the backout retro_pbl_line_id
3064 -- We now lose the original retro_pbl_line_id and cant rollback this detail.
3065 -- Hence the new column to keep history.
3066 UPDATE hxt_det_hours_worked_f
3067 SET pay_status = 'B',
3068 last_update_date = SYSDATE,
3069 retro_pbl_line_id_2 = retro_pbl_line_id,
3070 retro_pbl_line_id = l_batch_line_id -- OHM180
3071 WHERE ROWID = l_leftover.hrw_rowid;
3072
3073 l_batch_sequence := l_batch_sequence + 1;
3074
3075 END LOOP;
3076 END back_out_leftover_hours;
3077
3078 PROCEDURE back_out_leftover_amount (p_batch_id NUMBER, p_tim_id NUMBER)
3079 IS
3080 CURSOR leftover_amount (p_tim_id NUMBER)
3081 IS
3082 SELECT hrw.ROWID hrw_rowid, asm.assignment_number, elt.element_name, --FORMS60
3083 DECODE (
3084 SIGN (
3085 DECODE (
3086 SIGN ( ptp.start_date
3087 - asm.effective_start_date),
3088 1, ptp.start_date,
3089 asm.effective_start_date
3090 )
3091 - elt.effective_start_date
3092 ),
3093 1, DECODE (
3094 SIGN ( ptp.start_date
3095 - asm.effective_start_date),
3096 1, ptp.start_date,
3097 asm.effective_start_date
3098 ),
3099 elt.effective_start_date
3100 )
3101 from_date,
3102 DECODE (
3103 SIGN (
3104 DECODE (
3105 SIGN ( ptp.end_date
3106 - asm.effective_end_date),
3107 -1, ptp.end_date,
3108 asm.effective_end_date
3109 )
3110 - elt.effective_end_date
3111 ),
3112 -1, DECODE (
3113 SIGN ( ptp.end_date
3114 - asm.effective_end_date),
3115 -1, ptp.end_date,
3116 asm.effective_end_date
3117 ),
3118 elt.effective_end_date
3119 ) TO_DATE,
3120 rate_multiple, hrw.hourly_rate, hrw.ffv_cost_center_id,
3121 /* fk - cost_center_code */
3122 pcak.concatenated_segments, pcak.segment1, pcak.segment2,
3123 pcak.segment3, pcak.segment4, pcak.segment5, pcak.segment6,
3124 pcak.segment7, pcak.segment8, pcak.segment9, pcak.segment10,
3125 pcak.segment11, pcak.segment12, pcak.segment13,
3126 pcak.segment14, pcak.segment15, pcak.segment16,
3127 pcak.segment17, pcak.segment18, pcak.segment19,
3128 pcak.segment20, pcak.segment21, pcak.segment22,
3129 pcak.segment23, pcak.segment24, pcak.segment25,
3130 pcak.segment26, pcak.segment27, pcak.segment28,
3131 pcak.segment29, pcak.segment30,
3132
3133 /*TA36 ffvl.flex_value labor_dist_code,*/
3134 loct.location_code locality_worked, --FORMS60
3135 ffvr.flex_value rate_code,
3136 hrw.separate_check_flag,
3137 hrw.fcl_tax_rule_code tax_separately_flag, hrw.amount,
3138 hrw.hours hours_worked, hrw.element_type_id, --GLOBAL
3139 hcl.meaning reason, --GLOBAL
3140 hrw.date_worked, /* BSE107 */ ptp.time_period_id,
3141 /* BSE107 */
3142 hrw.assignment_id ,
3143 hrw.state_name,
3144 hrw.county_name,
3145 hrw.city_name,
3146 hrw.zip_code
3147 /* BSE107 */
3148 FROM hxt_timecards_x tim, /* SIR416 PWM 21MAR00 */
3149 per_time_periods ptp,
3150 hxt_det_hours_worked_f hrw,
3151 hr_lookups hcl, --GLOBAL
3152 per_assignments_f asm,
3153 pay_element_types_f elt,
3154 pay_cost_allocation_keyflex pcak,
3155
3156 /*TA36fnd_flex_values ffvl, */
3157 hr_locations_all_tl loct, --FORMS60
3158 hr_locations_no_join loc, --FORMS60
3159 fnd_flex_values ffvr
3160 WHERE hrw.ffv_rate_code_id = ffvr.flex_value_id(+)
3161 AND hrw.location_id = loc.location_id(+)
3162
3163 --BEGIN FORMS60
3164 AND loc.location_id = loct.location_id(+)
3165 AND DECODE (loct.location_id, NULL, '1', loct.LANGUAGE) =
3166 DECODE (loct.location_id, NULL, '1', USERENV ('LANG'))
3167
3168 --END FORMS60
3169 /*TA36AND hrw.ffv_labor_account_id = ffvl.flex_value_id(+)*/
3170 AND hrw.ffv_cost_center_id = pcak.cost_allocation_keyflex_id(+)
3171 AND hrw.date_worked BETWEEN elt.effective_start_date
3172 AND elt.effective_end_date
3173 AND hrw.element_type_id = elt.element_type_id
3174 AND hrw.date_worked BETWEEN asm.effective_start_date
3175 AND asm.effective_end_date
3176 AND hrw.assignment_id = asm.assignment_id
3177 AND hrw.amount IS NOT NULL
3178
3179 /* GLOBAL AND hrw.parent_id > 0 */
3180 AND hrw.tim_id = tim.id
3181 AND tim.id = p_tim_id
3182 AND tim.time_period_id = ptp.time_period_id
3183
3184 --BEGIN GLOBAL
3185 AND hrw.date_worked BETWEEN NVL (
3186 hcl.start_date_active(+),
3187 hrw.date_worked
3188 )
3189 AND NVL (
3190 hcl.end_date_active(+),
3191 hrw.date_worked
3192 )
3193 AND hcl.lookup_type(+) = 'ELE_ENTRY_REASON'
3194 AND hcl.lookup_code(+) = hrw.fcl_earn_reason_code
3195
3196 --END GLOBAL
3197 AND hrw.pay_status = 'C'
3198 AND hrw.effective_end_date < hr_general.end_of_time; --SIR149 --FORMS60
3199
3200 l_leftover leftover_amount%ROWTYPE;
3201 -- l_nextval NUMBER (15);
3202 l_batch_line_id NUMBER (15);
3203 l_retcode NUMBER; /* BSE107 */
3204
3205 l_batch_sequence PAY_BATCH_LINES.BATCH_SEQUENCE%TYPE;
3206
3207 BEGIN
3208
3209 l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(p_batch_id);
3210
3211 FOR l_leftover IN leftover_amount (p_tim_id)
3212 LOOP
3213
3214 /* BEGIN BSE107 - OHM SPR200*/
3215 --not needed l_retcode := HXT_TD_UTIL.get_hourly_rate(l_leftover.date_worked,
3216 --not needed l_leftover.time_period_id,
3217 --not needed l_leftover.assignment_id,
3218 --not needed l_leftover.hourly_rate);
3219
3220 /* END BSE107 - OHM SPR200*/
3221
3222 --BEGIN GLOBAL
3223 -- select pay_pdt_batch_lines_s.nextval
3224 -- SELECT pay_batch_lines_s.NEXTVAL
3225
3226 --END GLOBAL
3227 -- INTO l_nextval
3228 -- FROM DUAL;
3229
3230
3231 --BEGIN GLOBAL
3232 -- INSERT into pay_pdt_batch_lines
3233 -- (batch_id, line_id,
3234 -- assignment_number, adjustment_type_code,
3235 -- amount,
3236 -- apply_this_period,
3237 -- cost_allocation_keyflex_id,concatenated_segments,
3238 -- segment1,segment2,segment3,segment4,
3239 -- segment5,segment6,segment7,segment8,
3240 -- segment9,segment10,segment11,segment12,
3241 -- segment13,segment14,segment15,segment16,
3242 -- segment17,segment18,segment19,segment20,
3243 -- segment21,segment22,segment23,segment24,
3244 -- segment25,segment26,segment27,segment28,
3245 -- segment29,segment30,
3246 -- element_name, from_date,
3247 -- to_date, hourly_rate, inc_asc_balance,
3248 -- labor_dist_code,
3249 -- line_status, locality_worked, new_salary, pay_effective_date,
3250 -- pcnt_increase, rate_code, rate_multiple, rating_code,
3251 -- separate_check_flag, shift_type, state_worked,
3252 -- tax_separately_flag, vol_ded_proc_ovd,
3253 -- hours_worked)
3254 -- VALUES(
3255 -- p_batch_id, l_nextval,
3256 -- l_leftover.assignment_number, '',
3257 -- 0 - l_leftover.amount,
3258 -- '',
3259 -- l_leftover.ffv_cost_center_id,l_leftover.concatenated_segments,
3260 -- l_leftover.segment1,l_leftover.segment2,l_leftover.segment3,l_leftover.segment4,
3261 -- l_leftover.segment5,l_leftover.segment6,l_leftover.segment7,l_leftover.segment8,
3262 -- l_leftover.segment9,l_leftover.segment10,l_leftover.segment11,l_leftover.segment12,
3263 -- l_leftover.segment13,l_leftover.segment14,l_leftover.segment15,l_leftover.segment16,
3264 -- l_leftover.segment17,l_leftover.segment18,l_leftover.segment19,l_leftover.segment20,
3265 -- l_leftover.segment21,l_leftover.segment22,l_leftover.segment23,l_leftover.segment24,
3266 -- l_leftover.segment25,l_leftover.segment26,l_leftover.segment27,l_leftover.segment28,
3267 -- l_leftover.segment29,l_leftover.segment30,
3268 -- l_leftover.element_name, '',
3269 -- '', l_leftover.hourly_rate, '',
3270 --/*TA36 l_leftover.labor_dist_code*/NULL,
3271 -- '', l_leftover.locality_worked, '', '',
3272 -- '', l_leftover.rate_code, l_leftover.rate_multiple, '',
3273 -- l_leftover.separate_check_flag, '', '',
3274 -- l_leftover.tax_separately_flag, '',
3275 -- l_leftover.hours_worked
3276 -- );
3277 insert_pay_batch_lines (
3278 p_batch_id,
3279 l_batch_line_id,
3280 l_leftover.assignment_id,
3281 l_leftover.assignment_number,
3282 0
3283 - l_leftover.amount,
3284 l_leftover.ffv_cost_center_id,
3285 l_leftover.concatenated_segments,
3286 l_leftover.segment1,
3287 l_leftover.segment2,
3288 l_leftover.segment3,
3289 l_leftover.segment4,
3290 l_leftover.segment5,
3291 l_leftover.segment6,
3292 l_leftover.segment7,
3293 l_leftover.segment8,
3294 l_leftover.segment9,
3295 l_leftover.segment10,
3296 l_leftover.segment11,
3297 l_leftover.segment12,
3298 l_leftover.segment13,
3299 l_leftover.segment14,
3300 l_leftover.segment15,
3301 l_leftover.segment16,
3302 l_leftover.segment17,
3303 l_leftover.segment18,
3304 l_leftover.segment19,
3305 l_leftover.segment20,
3306 l_leftover.segment21,
3307 l_leftover.segment22,
3308 l_leftover.segment23,
3309 l_leftover.segment24,
3310 l_leftover.segment25,
3311 l_leftover.segment26,
3312 l_leftover.segment27,
3313 l_leftover.segment28,
3314 l_leftover.segment29,
3315 l_leftover.segment30,
3316 l_leftover.element_type_id,
3317 l_leftover.element_name,
3318 l_leftover.hourly_rate,
3319 l_leftover.locality_worked,
3320 l_leftover.rate_code,
3321 l_leftover.rate_multiple,
3322 l_leftover.separate_check_flag,
3323 l_leftover.tax_separately_flag,
3324 l_leftover.hours_worked,
3325 l_leftover.date_worked,
3326 l_leftover.reason,
3327 l_batch_sequence,
3328 l_leftover.state_name,
3329 l_leftover.county_name,
3330 l_leftover.city_name,
3331 l_leftover.zip_code
3332 );
3333
3334
3335 --END GLOBAL
3336 UPDATE hxt_det_hours_worked_f
3337 SET pay_status = 'B',
3338 last_update_date = SYSDATE,
3339 retro_pbl_line_id = l_batch_line_id -- OHM180
3340 WHERE ROWID = l_leftover.hrw_rowid;
3341
3342 l_batch_sequence := l_batch_sequence +1;
3343
3344 END LOOP;
3345 END back_out_leftover_amount;
3346 --begin
3347
3348
3349 -- Bug 9494444
3350 -- Added this procedure to pick up details to be updated
3351 -- for Retrieval Dashboard.
3352
3353 /*
3354
3355 PROCEDURE snap_retrieval_details(p_batch_id IN NUMBER,
3356 p_tim_id IN NUMBER)
3357 IS
3358
3359 TYPE VARCHARTAB IS TABLE OF VARCHAR2(100);
3360 TYPE NUMBERTAB IS TABLE OF NUMBER;
3361 TYPE DATETAB IS TABLE OF DATE;
3362
3363
3364 -- This cursor picks up the details for which
3365 -- time store has been modified.
3366 -- Bug 11727217
3367 -- Added hints to improve performance.
3368 CURSOR get_retro_ids( p_batch_id IN NUMBER,
3369 p_tim_id IN NUMBER)
3370 IS SELECT /*+ LEADING(det)
3371 INDEX(det HXT_DET_HOURS_WORKED_F_TIM_FK)
3372 INDEX(pbl PAY_BATCH_LINES_PK)
3373 INDEX(ret HXC_RET_PAY_LATEST_DETAILS_N7) *
3374 det.retro_pbl_line_id,
3375 p_batch_id,
3376 ROWIDTOCHAR(ret.ROWID)
3377 FROM hxt_det_hours_worked_f det,
3378 hxc_ret_pay_latest_details ret,
3379 pay_batch_lines pbl
3380 WHERE det.pay_status = 'B'
3381 AND det.effective_end_date <> hr_general.end_of_time
3382 AND det.pbl_line_id = ret.old_pbl_id
3383 AND ret.retro_batch_id IS NULL
3384 AND det.tim_id = p_tim_id
3385 AND pbl.batch_id = p_batch_id
3386 AND det.retro_pbl_line_id = pbl.batch_line_id;
3387
3388
3389 -- This cursor picks up the details for which time store
3390 -- has not been modified, but still a backout occured.
3391 -- Bug 11727217
3392 -- Added hints to improve performance.
3393 CURSOR get_retro_ids2( p_batch_id IN NUMBER,
3394 p_tim_id IN NUMBER)
3395 IS SELECT /*+ LEADING(det)
3396 INDEX(det HXT_DET_HOURS_WORKED_F_TIM_FK)
3397 INDEX(pbl PAY_BATCH_LINES_PK)
3398 INDEX(ret HXC_RET_PAY_LATEST_DETAILS_N6) *
3399 det.retro_pbl_line_id,
3400 p_batch_id,
3401 ROWIDTOCHAR(ret.ROWID)
3402 FROM hxt_det_hours_worked_f det,
3403 hxc_ret_pay_latest_details ret,
3404 pay_batch_lines pbl
3405 WHERE det.pay_status = 'B'
3406 AND det.effective_end_date <> hr_general.end_of_time
3407 AND det.pbl_line_id = ret.pbl_id
3408 AND ret.retro_batch_id IS NULL
3409 AND det.tim_id = p_tim_id
3410 AND pbl.batch_id = p_batch_id
3411 AND det.retro_pbl_line_id = pbl.batch_line_id;
3412
3413 -- This picks Details for tbb id ovn combos which are not edited in
3414 -- Self service, but got modified.
3415 CURSOR get_modified_values(p_batch_id IN NUMBER,
3416 p_tim_id IN NUMBER)
3417 IS SELECT det.retro_pbl_line_id,
3418 det.retro_batch_id,
3419 det.hours,
3420 ROWIDTOCHAR(ret.ROWID)
3421 FROM hxt_det_hours_worked_f det,
3422 hxt_sum_hours_worked_f sum,
3423 hxc_ret_pay_latest_details ret
3424 WHERE det.tim_id = p_tim_id
3425 AND det.effective_end_date = hr_general.end_of_time
3426 AND sum.id = det.parent_id
3427 AND sum.effective_end_date = hr_general.end_of_time
3428 AND det.retro_pbl_line_id IS NOT NULL
3429 AND det.retro_batch_id = p_batch_id
3430 AND ret.time_building_block_id = sum.time_building_block_id
3431 AND ret.object_version_number = sum.time_building_block_ovn
3432 AND ret.old_attribute1 = det.element_type_id ;
3433
3434
3435
3436 ret_pbl_tab NUMBERTAB;
3437 ret_batch_tab NUMBERTAB;
3438 hrs_tab NUMBERTAB;
3439 rowtab VARCHARTAB;
3440
3441
3442 resource_id_tab NUMBERTAB;
3443 time_building_block_id_tab NUMBERTAB;
3444 approval_status_tab VARCHARTAB;
3445 start_time_tab DATETAB;
3446 stop_time_tab DATETAB;
3447 org_id_tab NUMBERTAB;
3448 business_group_id_tab NUMBERTAB;
3449 timecard_id_tab NUMBERTAB;
3450 attribute1_tab VARCHARTAB;
3451 attribute2_tab VARCHARTAB;
3452 attribute3_tab VARCHARTAB;
3453 measure_tab NUMBERTAB;
3454 object_version_number_tab NUMBERTAB;
3455 old_ovn_tab NUMBERTAB;
3456 old_measure_tab NUMBERTAB;
3457 old_attribute1_tab VARCHARTAB;
3458 old_attribute2_tab VARCHARTAB;
3459 old_attribute3_tab VARCHARTAB;
3460 pbl_id_tab NUMBERTAB;
3461 retro_pbl_id_tab NUMBERTAB;
3462 old_pbl_id_tab NUMBERTAB;
3463 request_id_tab NUMBERTAB;
3464 old_request_id_tab NUMBERTAB;
3465 batch_id_tab NUMBERTAB;
3466 retro_batch_id_tab NUMBERTAB;
3467 old_batch_id_tab NUMBERTAB;
3468 rowid_tab VARCHARTAB;
3469
3470
3471
3472 -- This picks up details for those tbb id ovn combos for which new
3473 -- elements came up in explosion.
3474
3475 -- Bug 10215710
3476 -- Corrected the hint below to force the plan
3477 -- DET->SUM->RET
3478
3479 CURSOR pick_new_details(p_tim_id IN NUMBER,
3480 p_batch_id IN NUMBER)
3481 IS SELECT resource_id,
3482 time_building_block_id,
3483 approval_status,
3484 start_time,
3485 stop_time,
3486 org_id,
3487 business_group_id,
3488 timecard_id,
3489 element_type_id,
3490 attribute2,
3491 attribute3,
3492 hours,
3493 object_version_number,
3494 old_ovn,
3495 old_measure,
3496 old_attribute1,
3497 old_attribute2,
3498 old_attribute3,
3499 pbl_id,
3500 retro_pbl_id,
3501 old_pbl_id,
3502 request_id,
3503 old_request_id,
3504 batch_id,
3505 retro_batch_id,
3506 old_batch_id
3507 FROM ( SELECT /*+ INDEX(det HXT_DET_HOURS_WORKED_F_TIM_FK)
3508 INDEX(sum HXT_SUM_HOURS_WORKED_PK)
3509 INDEX(ret HXC_RET_PAY_LATEST_DETAILS_FK1) *
3510 ret.resource_id,
3511 ret.time_building_block_id,
3512 ret.approval_status,
3513 ret.start_time,
3514 ret.stop_time,
3515 ret.org_id,
3516 ret.business_group_id,
3517 ret.timecard_id,
3518 det.element_type_id,
3519 ret.attribute2,
3520 ret.attribute3,
3521 det.hours,
3522 ret.object_version_number,
3523 NULL old_ovn,
3524 NULL old_measure,
3525 NULL old_attribute1,
3526 NULL old_attribute2,
3527 NULL old_attribute3,
3528 det.retro_pbl_line_id pbl_id,
3529 NULL retro_pbl_id,
3530 NULL old_pbl_id,
3531 FND_GLOBAL.conc_request_id request_id,
3532 NULL old_request_id,
3533 det.retro_batch_id batch_id,
3534 NULL retro_batch_id,
3535 NULL old_batch_id,
3536 RANK() OVER (PARTITION BY ret.time_building_block_id,
3537 ret.object_version_number
3538 ORDER BY ret.ROWID) rank
3539 FROM hxt_det_hours_worked_f det,
3540 hxt_sum_hours_worked_f sum,
3541 hxc_ret_pay_latest_details ret
3542 WHERE det.parent_id = sum.id
3543 AND det.tim_id = p_tim_id
3544 AND det.hours <> 0
3545 AND det.retro_batch_id = p_batch_id
3546 AND det.effective_end_date = hr_general.end_of_time
3547 AND sum.effective_end_date = hr_general.end_of_time
3548 AND sum.time_building_block_id = ret.time_building_block_id
3549 AND sum.time_building_block_ovn = ret.object_version_number
3550 AND (ret.batch_id IS NOT NULL
3551 OR ret.retro_batch_id IS NOT NULL
3552 OR ret.old_batch_id IS NOT NULL)
3553 AND NOT EXISTS ( SELECT /*+ INDEX(ret2 HXC_RET_PAY_LATEST_DETAILS_FK1) *
3554 1
3555 FROM hxc_ret_pay_latest_details ret2
3556 WHERE ret.time_building_block_id = ret2.time_building_block_id
3557 AND ret.object_version_number = ret2.object_version_number
3558 AND ret2.old_attribute1 = det.element_type_id )
3559 )
3560 WHERE rank = 1;
3561
3562 -- This cursor picks up entirely new details.
3563 CURSOR pick_new_sum(p_tim_id IN NUMBER,
3564 p_batch_id IN NUMBER)
3565 IS SELECT
3566 ret.resource_id,
3567 ret.time_building_block_id,
3568 ret.approval_status,
3569 ret.start_time,
3570 ret.stop_time,
3571 ret.org_id,
3572 ret.business_group_id,
3573 ret.timecard_id,
3574 det.element_type_id,
3575 ret.attribute2,
3576 ret.attribute3,
3577 det.hours,
3578 ret.object_version_number,
3579 ret.old_ovn,
3580 ret.old_measure,
3581 ret.old_attribute1,
3582 ret.old_attribute2,
3583 ret.old_attribute3,
3584 det.retro_pbl_line_id,
3585 ret.retro_pbl_id,
3586 ret.old_pbl_id,
3587 FND_GLOBAL.conc_request_id,
3588 ret.old_request_id,
3589 det.retro_batch_id,
3590 ret.retro_batch_id,
3591 ret.old_batch_id,
3592 ROWIDTOCHAR(ret.ROWID)
3593 FROM hxt_det_hours_worked_f det,
3594 hxt_sum_hours_worked_f sum,
3595 hxc_ret_pay_latest_details ret
3596 WHERE det.tim_id = p_tim_id
3597 AND det.effective_end_date = hr_general.end_of_time
3598 AND det.retro_batch_id = p_batch_id
3599 AND det.parent_id = sum.id
3600 AND sum.effective_end_date = hr_general.end_of_time
3601 AND sum.time_building_block_id = ret.time_building_block_id
3602 AND sum.time_building_block_ovn = ret.object_version_number
3603 AND det.retro_pbl_line_id IS NOT NULL
3604 AND ret.pbl_id IS NULL
3605 AND ret.old_pbl_id IS NULL;
3606
3607
3608
3609
3610
3611 BEGIN
3612
3613 -- Case 1
3614 -- First pick up those entries which are backed out because
3615 -- of a Time store change.
3616 -- Eg. tbb id 1234, ovn 1 Reg 8 hrs changed to tbbid 1234 ovn 2 Reg 7 hrs.
3617 OPEN get_retro_ids(p_batch_id,p_tim_id);
3618 FETCH get_retro_ids BULK COLLECT INTO ret_pbl_tab,
3619 ret_batch_tab,
3620 rowtab;
3621
3622 CLOSE get_retro_ids;
3623
3624 -- Update the relevant retro batch details.
3625 FORALL i IN ret_pbl_tab.FIRST..ret_pbl_tab.LAST
3626 UPDATE hxc_ret_pay_latest_details
3627 SET retro_batch_id = ret_batch_tab(i),
3628 retro_pbl_id = ret_pbl_tab(i),
3629 request_id = FND_GLOBAL.conc_request_id
3630 WHERE ROWID = CHARTOROWID(rowtab(i));
3631
3632 -- Case 2
3633 -- Next, pick up those entries which are backed out not because
3634 -- of a Time store change, but an explosion change
3635 -- Eg. tbb id 1234, ovn 1 Reg 8 hrs still remains the same, but
3636 -- because another detail is changed now it has become Overtime 8 hrs.
3637
3638 OPEN get_retro_ids2(p_batch_id,p_tim_id);
3639 FETCH get_retro_ids2 BULK COLLECT INTO ret_pbl_tab,
3640 ret_batch_tab,
3641 rowtab;
3642
3643 CLOSE get_retro_ids2;
3644
3645 -- Update the relevant retro batch details.
3646 FORALL i IN ret_pbl_tab.FIRST..ret_pbl_tab.LAST
3647 UPDATE hxc_ret_pay_latest_details
3648 SET retro_batch_id = ret_batch_tab(i),
3649 retro_pbl_id = ret_pbl_tab(i),
3650 old_pbl_id = pbl_id,
3651 old_batch_id = batch_id,
3652 old_request_id = request_id,
3653 old_measure = measure,
3654 old_attribute1 = attribute1,
3655 old_attribute2 = attribute2,
3656 attribute1 = NULL,
3657 attribute2 = NULL,
3658 measure = NULL,
3659 pbl_id = NULL,
3660 request_id = FND_GLOBAL.conc_request_id,
3661 batch_id = NULL
3662 WHERE ROWID = CHARTOROWID(rowtab(i));
3663
3664
3665 -- Case 3
3666 -- Now pick up the entries which are not Timestore changes, but actual
3667 -- explosion changes following the backouts in Case 2.
3668 -- Eg. tbb id 1234, Reg 12 hours it was.
3669 -- Earlier, it was
3670 -- 1234 1 Reg 8 hrs
3671 -- 1234 1 Ovt 4 hrs.
3672 --
3673 -- Now the detail is not changed, total hours not changed, but because
3674 -- of some other detail's change, it became
3675 --
3676 -- 1234 1 Reg 10 hrs
3677 -- 1234 1 Ovt 2 hrs.
3678 -- The retro or backout entries for the earlier 8 and 4 would have gone out
3679 -- via Case 2. This cursor would update the new values.
3680 --
3681 OPEN get_modified_values(p_batch_id,p_tim_id);
3682 FETCH get_modified_values BULK COLLECT INTO ret_pbl_tab,
3683 ret_batch_tab,
3684 hrs_tab,
3685 rowtab;
3686
3687 CLOSE get_modified_values;
3688
3689 FORALL i IN ret_pbl_tab.FIRST..ret_pbl_tab.LAST
3690 UPDATE hxc_ret_pay_latest_details
3691 SET batch_id = ret_batch_tab(i),
3692 pbl_id = ret_pbl_tab(i),
3693 attribute1 = old_attribute1,
3694 measure = hrs_tab(i),
3695 request_id = fnd_global.conc_request_id
3696 WHERE ROWID = CHARTOROWID(rowtab(i));
3697
3698
3699 -- Case 4
3700 -- Now pick up the entries which are not Timestore changes, but actual
3701 -- explosion changes following the backouts in Case 2.
3702 -- Eg. tbb id 1234, Reg 12 hours it was.
3703 -- Earlier, it was
3704 -- 1234 1 Reg 8 hrs
3705 -- 1234 1 Ovt 4 hrs.
3706 --
3707 -- Now the detail is not changed, total hours not changed, but because
3708 -- of some other detail's change, it became
3709 --
3710 -- 1234 1 Ovt 4 hrs
3711 -- 1234 1 Dbt 8 hrs.
3712 -- The retro or backout entries for the earlier 8 and 4 would have gone out
3713 -- via Case 2. This cursor would update the new values.
3714 -- Here, Dbt 8 hours is a new entry altogether, while Ovt 4 hours is just
3715 -- a change. Change would have been picked up by the earlier cursor. This
3716 -- cursor would pick up the new entry.
3717 -- The cursor will pick up one record for each time building block id from
3718 -- hxc_ret_pay_latest_details just to provide the relevant details which are
3719 -- not in hxt_det_hours_worked_f
3720
3721 OPEN pick_new_details(p_tim_id,p_batch_id);
3722 FETCH pick_new_details BULK COLLECT INTO
3723 resource_id_tab,
3724 time_building_block_id_tab,
3725 approval_status_tab,
3726 start_time_tab,
3727 stop_time_tab,
3728 org_id_tab,
3729 business_group_id_tab,
3730 timecard_id_tab,
3731 attribute1_tab,
3732 attribute2_tab,
3733 attribute3_tab,
3734 measure_tab,
3735 object_version_number_tab,
3736 old_ovn_tab,
3737 old_measure_tab,
3738 old_attribute1_tab,
3739 old_attribute2_tab,
3740 old_attribute3_tab,
3741 pbl_id_tab,
3742 retro_pbl_id_tab,
3743 old_pbl_id_tab,
3744 request_id_tab,
3745 old_request_id_tab,
3746 batch_id_tab,
3747 retro_batch_id_tab,
3748 old_batch_id_tab;
3749
3750
3751 FORALL i IN old_batch_id_tab.FIRST..old_batch_id_tab.LAST
3752 INSERT INTO hxc_ret_pay_latest_details
3753 ( resource_id,
3754 time_building_block_id,
3755 approval_status,
3756 start_time,
3757 stop_time,
3758 org_id,
3759 business_group_id,
3760 timecard_id,
3761 attribute1,
3762 attribute2,
3763 attribute3,
3764 measure,
3765 object_version_number,
3766 old_ovn,
3767 old_measure,
3768 old_attribute1,
3769 old_attribute2,
3770 old_attribute3,
3771 pbl_id,
3772 retro_pbl_id,
3773 old_pbl_id,
3774 request_id,
3775 old_request_id,
3776 batch_id,
3777 retro_batch_id,
3778 old_batch_id)
3779 VALUES ( resource_id_tab(i),
3780 time_building_block_id_tab(i),
3781 approval_status_tab(i),
3782 start_time_tab(i),
3783 stop_time_tab(i),
3784 org_id_tab(i),
3785 business_group_id_tab(i),
3786 timecard_id_tab(i),
3787 attribute1_tab(i),
3788 attribute2_tab(i),
3789 attribute3_tab(i),
3790 measure_tab(i),
3791 object_version_number_tab(i),
3792 old_ovn_tab(i),
3793 old_measure_tab(i),
3794 old_attribute1_tab(i),
3795 old_attribute2_tab(i),
3796 old_attribute3_tab(i),
3797 pbl_id_tab(i),
3798 retro_pbl_id_tab(i),
3799 old_pbl_id_tab(i),
3800 request_id_tab(i),
3801 old_request_id_tab(i),
3802 batch_id_tab(i),
3803 retro_batch_id_tab(i),
3804 old_batch_id_tab(i));
3805
3806
3807 CLOSE pick_new_details;
3808
3809
3810 -- Case 5
3811 -- This cursor will pick up completely new entries
3812 -- from Time store, ie, new summaries.
3813 OPEN pick_new_sum(p_tim_id,p_batch_id);
3814 FETCH pick_new_sum BULK COLLECT INTO resource_id_tab,
3815 time_building_block_id_tab,
3816 approval_status_tab,
3817 start_time_tab,
3818 stop_time_tab,
3819 org_id_tab,
3820 business_group_id_tab,
3821 timecard_id_tab,
3822 attribute1_tab,
3823 attribute2_tab,
3824 attribute3_tab,
3825 measure_tab,
3826 object_version_number_tab,
3827 old_ovn_tab,
3828 old_measure_tab,
3829 old_attribute1_tab,
3830 old_attribute2_tab,
3831 old_attribute3_tab,
3832 pbl_id_tab,
3833 retro_pbl_id_tab,
3834 old_pbl_id_tab,
3835 request_id_tab,
3836 old_request_id_tab,
3837 batch_id_tab,
3838 retro_batch_id_tab,
3839 old_batch_id_tab,
3840 rowid_tab;
3841
3842 -- Delete the entries already there.
3843 FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
3844 DELETE FROM hxc_ret_pay_latest_details
3845 WHERE ROWID = CHARTOROWID(rowid_tab(i));
3846
3847 -- Insert the new entries.
3848 FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
3849 INSERT INTO hxc_ret_pay_latest_details
3850 ( resource_id,
3851 time_building_block_id,
3852 approval_status,
3853 start_time,
3854 stop_time,
3855 org_id,
3856 business_group_id,
3857 timecard_id,
3858 attribute1,
3859 attribute2,
3860 attribute3,
3861 measure,
3862 object_version_number,
3863 old_ovn,
3864 old_measure,
3865 old_attribute1,
3866 old_attribute2,
3867 old_attribute3,
3868 pbl_id,
3869 retro_pbl_id,
3870 old_pbl_id,
3871 request_id,
3872 old_request_id,
3873 batch_id,
3874 retro_batch_id,
3875 old_batch_id)
3876 VALUES ( resource_id_tab(i),
3877 time_building_block_id_tab(i),
3878 approval_status_tab(i),
3879 start_time_tab(i),
3880 stop_time_tab(i),
3881 org_id_tab(i),
3882 business_group_id_tab(i),
3883 timecard_id_tab(i),
3884 attribute1_tab(i),
3885 attribute2_tab(i),
3886 attribute3_tab(i),
3887 measure_tab(i),
3888 object_version_number_tab(i),
3889 old_ovn_tab(i),
3890 old_measure_tab(i),
3891 old_attribute1_tab(i),
3892 old_attribute2_tab(i),
3893 old_attribute3_tab(i),
3894 pbl_id_tab(i),
3895 retro_pbl_id_tab(i),
3896 old_pbl_id_tab(i),
3897 request_id_tab(i),
3898 old_request_id_tab(i),
3899 batch_id_tab(i),
3900 retro_batch_id_tab(i),
3901 old_batch_id_tab(i));
3902
3903
3904
3905
3906
3907
3908 END snap_retrieval_details;
3909
3910 */
3911
3912 -- Bug 12919783
3913 -- We had a lot of issues with the above procedure, so restructured it here.
3914
3915 -- Bug 13954828
3916 -- Added processing for p_tim_id parameter to work with multiple retro batches
3917 -- in the same batches.
3918 -- Added tim_id in all the cursors, and modified the last DELETE
3919
3920 PROCEDURE snap_retrieval_details(p_batch_id IN NUMBER,
3921 p_tim_id IN NUMBER)
3922 IS
3923
3924
3925 CURSOR get_backouts_not_touched_now
3926 IS SELECT ROWIDTOCHAR(ret.rowid),
3927 det.retro_pbl_line_id
3928 FROM hxt_det_hours_worked_f det,
3929 hxt_sum_hours_worked_f sum,
3930 pay_batch_lines pbl,
3931 hxc_ret_pay_latest_details ret
3932 WHERE pbl.batch_id = p_batch_id
3933 AND pbl.batch_line_id = det.retro_pbl_line_id
3934 AND det.tim_id = p_tim_id
3935 AND det.pay_status = 'B'
3936 AND det.parent_id = sum.id
3937 AND sum.time_building_block_id = ret.time_building_block_id
3938 AND ret.object_version_number = sum.time_building_block_ovn
3939 AND sum.effective_start_date <= det.effective_start_date
3940 AND sum.effective_end_date >= det.effective_end_date
3941 AND det.element_type_id = ret.attribute1;
3942
3943
3944 CURSOR get_backouts_touched_now
3945 IS SELECT ROWIDTOCHAR(ret.rowid),
3946 det.retro_pbl_line_id
3947 FROM hxt_det_hours_worked_f det,
3948 hxt_sum_hours_worked_f sum,
3949 pay_batch_lines pbl,
3950 hxc_ret_pay_latest_details ret
3951 WHERE pbl.batch_id = p_batch_id
3952 AND pbl.batch_line_id = det.retro_pbl_line_id
3953 AND det.tim_id = p_tim_id
3954 AND det.pay_status = 'B'
3955 AND det.parent_id = sum.id
3956 AND sum.time_building_block_id = ret.time_building_block_id
3957 AND ret.old_ovn = sum.time_building_block_ovn
3958 AND sum.effective_start_date <= det.effective_start_date
3959 AND sum.effective_end_date >= det.effective_end_date
3960 AND det.element_type_id = ret.old_attribute1
3961 AND ret.batch_id = p_batch_id;
3962
3963
3964
3965 CURSOR get_changed_hours
3966 IS SELECT ROWIDTOCHAR(ret.rowid)
3967 FROM hxt_det_hours_worked_f det,
3968 hxt_sum_hours_worked_f sum,
3969 hxc_ret_pay_latest_details ret
3970 WHERE det.retro_batch_id = p_batch_id
3971 AND det.pay_status = 'C'
3972 AND det.tim_id = p_tim_id
3973 AND det.effective_start_date >= sum.effective_start_date
3974 AND det.effective_end_date <= sum.effective_end_date
3975 AND det.parent_id = sum.id
3976 AND sum.time_building_block_id = ret.time_building_block_id
3977 AND sum.time_building_block_ovn = ret.object_version_number
3978 AND det.element_type_id = ret.old_attribute1
3979 AND ret.batch_id = p_batch_id
3980 AND ret.pbl_id IS NULL
3981 AND ret.request_id <> FND_GLOBAL.conc_request_id ;
3982
3983
3984 TYPE VARCHARTAB IS TABLE OF VARCHAR2(100);
3985 TYPE NUMBERTAB IS TABLE OF NUMBER;
3986 TYPE DATETAB IS TABLE OF DATE;
3987
3988 l_rowidtab VARCHARTAB;
3989 l_line_tab NUMBERTAB;
3990
3991 resource_id_tab NUMBERTAB;
3992 time_building_block_id_tab NUMBERTAB;
3993 approval_status_tab VARCHARTAB;
3994 start_time_tab DATETAB;
3995 stop_time_tab DATETAB;
3996 org_id_tab NUMBERTAB;
3997 business_group_id_tab NUMBERTAB;
3998 timecard_id_tab NUMBERTAB;
3999 attribute1_tab VARCHARTAB;
4000 attribute2_tab VARCHARTAB;
4001 attribute3_tab VARCHARTAB;
4002 measure_tab NUMBERTAB;
4003 object_version_number_tab NUMBERTAB;
4004 old_ovn_tab NUMBERTAB;
4005 old_measure_tab NUMBERTAB;
4006 old_attribute1_tab VARCHARTAB;
4007 old_attribute2_tab VARCHARTAB;
4008 old_attribute3_tab VARCHARTAB;
4009 pbl_id_tab NUMBERTAB;
4010 retro_pbl_id_tab NUMBERTAB;
4011 old_pbl_id_tab NUMBERTAB;
4012 request_id_tab NUMBERTAB;
4013 old_request_id_tab NUMBERTAB;
4014 batch_id_tab NUMBERTAB;
4015 retro_batch_id_tab NUMBERTAB;
4016 old_batch_id_tab NUMBERTAB;
4017 app_set_id_tab NUMBERTAB;
4018 rowid_tab VARCHARTAB;
4019 hxt_att1tab VARCHARTAB;
4020 hxt_reqtab NUMBERTAB;
4021 hxt_measuretab NUMBERTAB;
4022
4023
4024 CURSOR get_new_details
4025 IS SELECT DISTINCT
4026 ret.resource_id,
4027 ret.time_building_block_id,
4028 ret.approval_status,
4029 ret.start_time,
4030 ret.stop_time,
4031 ret.org_id,
4032 ret.business_group_id,
4033 ret.timecard_id,
4034 det.element_type_id,
4035 ret.attribute2,
4036 ret.attribute3,
4037 det.hours,
4038 ret.object_version_number,
4039 NULL old_ovn,
4040 NULL old_measure,
4041 NULL old_attribute1,
4042 NULL old_attribute2,
4043 NULL old_attribute3,
4044 det.retro_pbl_line_id pbl_id,
4045 NULL retro_pbl_id,
4046 NULL old_pbl_id,
4047 FND_GLOBAL.conc_request_id request_id,
4048 NULL old_request_id,
4049 det.retro_batch_id batch_id,
4050 NULL retro_batch_id,
4051 NULL old_batch_id,
4052 ret.application_set_id
4053 FROM hxt_det_hours_worked_f det,
4054 hxt_sum_hours_worked_f sum,
4055 hxc_ret_pay_latest_details ret,
4056 pay_batch_lines pbl
4057 WHERE det.retro_batch_id = p_batch_id
4058 AND det.retro_pbl_line_id IS NOT NULL
4059 AND det.pay_status = 'C'
4060 AND det.tim_id = p_tim_id
4061 AND det.parent_id = sum.id
4062 AND det.effective_start_date >= sum.effective_start_date
4063 AND det.effective_end_date <= sum.effective_end_date
4064 AND sum.time_building_block_id = ret.time_building_block_id
4065 AND sum.time_building_block_ovn = ret.object_version_number
4066 AND pbl.batch_id = p_batch_id
4067 AND pbl.batch_line_id = det.retro_pbl_line_id;
4068
4069
4070 CURSOR get_details_to_delete
4071 IS SELECT ROWIDTOCHAR(ret.rowid),
4072 ret.time_building_block_id,
4073 ret.object_version_number,
4074 ret.request_id,
4075 ret.measure,
4076 ret.attribute1,
4077 ret.attribute2,
4078 ret.attribute3
4079 FROM hxt_sum_hours_worked_f sum,
4080 hxc_ret_pay_latest_details ret
4081 WHERE sum.tim_id = p_tim_id
4082 AND ret.time_building_block_id = sum.time_building_block_id
4083 AND ret.batch_id = p_batch_id
4084 AND ret.request_id <> FND_GLOBAL.conc_request_id
4085 AND ret.pbl_id IS NULL;
4086
4087
4088
4089
4090
4091
4092 BEGIN
4093
4094
4095 hr_utility.trace('Started here ');
4096 IF g_debug
4097 THEN
4098 hr_utility.trace('Starting to Rollback batch '||p_batch_id);
4099 hr_utility.trace('Tim_id '||p_tim_id);
4100 END IF;
4101
4102
4103 -- First Pick up any backout that happened now, but not been touched when
4104 -- the latest change to explosion happened.
4105 -- Eg. Reg 8, Ovt 4 on Friday
4106 -- The friday detail was not changed at all, but because Thursday entry had a
4107 -- new 8 hrs, Friday now became Reg 0, Ovt 12. Reg 8 earlier would have been backed out
4108 -- now.
4109
4110 OPEN get_backouts_not_touched_now;
4111
4112 FETCH get_backouts_not_touched_now
4113 BULK COLLECT INTO l_rowidtab,
4114 l_line_tab;
4115
4116 CLOSE get_backouts_not_touched_now;
4117
4118 -- For these details, we move the original batch_id/pbl_id to old_batch_id/old_pbl_id
4119 -- and mark the retro_batch_id and retro_pbl_id values.
4120 IF l_rowidtab.COUNT > 0
4121 THEN
4122
4123 IF g_debug
4124 THEN
4125 hr_utility.trace('First kind of backouts exist ');
4126 hr_utility.trace('Count := '||l_rowidtab.COUNT);
4127 END IF;
4128
4129 FORALL i IN l_rowidtab.FIRST..l_line_tab.LAST
4130 UPDATE hxc_ret_pay_latest_details
4131 SET retro_batch_id = p_batch_id,
4132 retro_pbl_id = l_line_tab(i),
4133 old_batch_id = batch_id,
4134 old_pbl_id = pbl_id,
4135 old_request_id = request_id,
4136 old_attribute1= attribute1,
4137 old_measure = measure,
4138 request_id = FND_global.conc_request_id,
4139 attribute1 = NULL,
4140 attribute2 = NULL,
4141 measure = NULL,
4142 pbl_id = NULL,
4143 batch_id = NULL
4144 WHERE ROWID = CHARTOROWID(l_rowidtab(i));
4145 END IF;
4146
4147 hr_utility.trace('Reached here2 ');
4148
4149
4150 IF g_debug
4151 THEN
4152 hr_utility.trace('Marked first backouts ');
4153 END IF;
4154
4155
4156 -- Next, pickup any backouts due to changes that happened in the latest explosion.
4157 -- Eg. 12 hrs on Friday was exploded as Reg 8 and Ovt 4.
4158 -- This 12 hrs is now changed to 8 hrs, which explodes as Reg 8 hrs.
4159 -- The ovt entry has to be backed out.
4160
4161 OPEN get_backouts_touched_now;
4162
4163 FETCH get_backouts_touched_now
4164 BULK COLLECT INTO l_rowidtab,
4165 l_line_tab;
4166
4167 CLOSE get_backouts_touched_now;
4168
4169 IF l_rowidtab.COUNT > 0
4170 THEN
4171 IF g_debug
4172 THEN
4173 hr_utility.trace('Second type of backouts exist');
4174 hr_utility.trace('Count '||l_rowidtab.COUNT);
4175 END IF;
4176
4177 FORALL i IN l_rowidtab.FIRST..l_line_tab.LAST
4178 UPDATE hxc_ret_pay_latest_details
4179 SET retro_batch_id = p_batch_id,
4180 retro_pbl_id = l_line_tab(i),
4181 hxt_request_id = request_id,
4182 hxt_batch_id = p_batch_id,
4183 request_id = FND_global.conc_request_id,
4184 hxt_measure = measure,
4185 measure = NULL,
4186 batch_id = NULL,
4187 hxt_element_id = attribute1,
4188 hxt_attribute2 = attribute2,
4189 hxt_attribute3 = attribute3,
4190 attribute1 = NULL,
4191 attribute2 = NULL,
4192 attribute3 = NULL
4193 WHERE ROWID = CHARTOROWID(l_rowidtab(i));
4194 END IF;
4195
4196 hr_utility.trace('Reached here 3 ');
4197
4198 IF g_debug
4199 THEN
4200 hr_utility.trace('Marked Second backouts ');
4201 END IF;
4202
4203
4204 -- Next, get changed hours.
4205 -- In OTLR, we do not delete any hours already transferred, we just reverse them
4206 -- Likewise, in RDB we do not show any details as changed except when they are backed out
4207 -- So any detail already there in RDB and in OTLR, and which is being marked as 'A' or 'D'
4208 -- in the processing above, are those ones which had changes ( not backouts).
4209 -- If these had changes in Self service itself, Xfer time from OTL to BEE would have changed the RDB details
4210 -- We need to put these back, after recording the changes to hxt_XXX columns.
4211
4212 -- Eg. 8 hrs entered in SS
4213 -- Comes to OTLR as 8hrs, and explodes as Reg 8 hrs.
4214 -- Xferred to BEE
4215 -- Now changed in SS to 9 hrs.
4216 -- Comes to OTLR as 9 hrs, and explodes as Reg 9 hrs.
4217 -- What we do in Xfer to BEE(Retro) here is to add a new batch line of Reg 1 hrs.
4218 -- This will be a new batch line, and we are not updating the old record.
4219 -- Xfer time from OTL to BEE would have modified this RDB record to have Reg 8 hrs in old_batch_id,
4220 -- old_pbl_id, old_attribute, old_measure etc.
4221 -- Here we put that back, after recording that the hxt_measure here is 9 hrs instead of 8 hrs
4222 -- recorded earlier.
4223
4224
4225 OPEN get_changed_hours;
4226
4227 FETCH get_changed_hours
4228 BULK COLLECT INTO l_rowidtab;
4229
4230 CLOSE get_changed_hours;
4231
4232 IF l_rowidtab.COUNT > 0
4233 THEN
4234
4235 IF g_debug
4236 THEN
4237 hr_utility.trace('Changed hours exist');
4238 hr_utility.trace('Count '||l_rowidtab.COUNT);
4239 END IF;
4240
4241 FORALL i IN l_rowidtab.FIRST..l_rowidtab.LAST
4242 UPDATE hxc_ret_pay_latest_details
4243 SET hxt_element_id = attribute1,
4244 hxt_attribute2 = attribute2,
4245 hxt_measure = measure,
4246 hxt_request_id = request_id,
4247 hxt_batch_id = batch_id,
4248 attribute1 = old_attribute1,
4249 measure = old_measure,
4250 batch_id = old_batch_id,
4251 pbl_id = old_pbl_id,
4252 request_id = old_request_id,
4253 old_attribute1 = NULL,
4254 old_measure = NULL,
4255 old_batch_id = NULL,
4256 old_pbl_id = NULL,
4257 old_request_id = NULL
4258 WHERE ROWID = CHARTOROWID(l_rowidtab(i));
4259
4260 END IF;
4261
4262 hr_utility.trace('Reached here 4 ');
4263
4264 IF g_debug
4265 THEN
4266 hr_utility.trace('Marked Changed hours ');
4267 END IF;
4268
4269
4270 -- Here, get the new details entered.
4271 -- These might be completely new summaries entered -- eg. 8hrs entered on a saturday.
4272 -- or a change to existing entry -- Eg. 8 hrs on Monday, initially; now changed to 9 hrs,
4273 -- so a new 1 hr reg record has gone in.
4274 -- Note that in RDB tables, we only hold the measure that has gone in BEE, not the det hours.
4275
4276 OPEN get_new_details;
4277
4278 FETCH get_new_details BULK COLLECT INTO resource_id_tab,
4279 time_building_block_id_tab,
4280 approval_status_tab,
4281 start_time_tab,
4282 stop_time_tab,
4283 org_id_tab,
4284 business_group_id_tab,
4285 timecard_id_tab,
4286 attribute1_tab,
4287 attribute2_tab,
4288 attribute3_tab,
4289 measure_tab,
4290 object_version_number_tab,
4291 old_ovn_tab,
4292 old_measure_tab,
4293 old_attribute1_tab,
4294 old_attribute2_tab,
4295 old_attribute3_tab,
4296 pbl_id_tab,
4297 retro_pbl_id_tab,
4298 old_pbl_id_tab,
4299 request_id_tab,
4300 old_request_id_tab,
4301 batch_id_tab,
4302 retro_batch_id_tab,
4303 old_batch_id_tab,
4304 app_set_id_tab ;
4305
4306 CLOSE get_new_details;
4307
4308
4309
4310 IF resource_id_tab.COUNT > 0
4311 THEN
4312
4313 IF g_debug
4314 THEN
4315 hr_utility.trace('New Details Exist');
4316 hr_utility.trace('Count '||resource_id_tab.COUNT);
4317 END IF;
4318
4319
4320 FORALL i IN resource_id_tab.FIRST..resource_id_tab.LAST
4321 INSERT INTO hxc_ret_pay_latest_details
4322 ( resource_id,
4323 time_building_block_id,
4324 approval_status,
4325 start_time,
4326 stop_time,
4327 org_id,
4328 business_group_id,
4329 timecard_id,
4330 attribute1,
4331 attribute2,
4332 attribute3,
4333 measure,
4334 object_version_number,
4335 old_ovn,
4336 old_measure,
4337 old_attribute1,
4338 old_attribute2,
4339 old_attribute3,
4340 pbl_id,
4341 retro_pbl_id,
4342 old_pbl_id,
4343 request_id,
4344 old_request_id,
4345 batch_id,
4346 retro_batch_id,
4347 old_batch_id,
4348 application_set_id
4349 )
4350 VALUES ( resource_id_tab(i),
4351 time_building_block_id_tab(i),
4352 approval_status_tab(i),
4353 start_time_tab(i),
4354 stop_time_tab(i),
4355 org_id_tab(i),
4356 business_group_id_tab(i),
4357 timecard_id_tab(i),
4358 attribute1_tab(i),
4359 attribute2_tab(i),
4360 attribute3_tab(i),
4361 batch_line_hours(pbl_id_tab(i)),
4362 object_version_number_tab(i),
4363 NULL,
4364 NULL,
4365 NULL,
4366 NULL,
4367 NULL,
4368 pbl_id_tab(i),
4369 NULL,
4370 NULL,
4371 FND_GLOBAL.conc_request_id,
4372 NULL,
4373 p_batch_id,
4374 NULL,
4375 NULL,
4376 app_set_id_tab(i)
4377 );
4378 END IF;
4379
4380
4381
4382 -- Now we inserted the exploded details in the table;
4383 -- we have to delete the already existing dummy records created by Xfer time from OTL to BEE.
4384 -- Meanwhile, collect the actual attributes, measure, batch_id etc created by Xfer time process.
4385 /*
4386 DELETE FROM hxc_ret_pay_latest_details
4387 WHERE batch_id = p_batch_id
4388 AND request_id <> FND_GLOBAL.conc_request_id
4389 AND pbl_id IS NULL
4390 RETURNING time_building_block_id,
4391 object_version_number,
4392 request_id,
4393 measure,
4394 attribute1,
4395 attribute2,
4396 attribute3
4397 BULK COLLECT INTO time_building_block_id_tab,
4398 object_version_number_tab,
4399 hxt_reqtab,
4400 hxt_measuretab,
4401 hxt_att1tab,
4402 attribute2_tab,
4403 attribute3_tab
4404 ;
4405 */
4406
4407 OPEN get_details_to_delete;
4408 FETCH get_details_to_delete BULK COLLECT INTO l_rowidtab,
4409 time_building_block_id_tab,
4410 object_version_number_tab,
4411 hxt_reqtab,
4412 hxt_measuretab,
4413 hxt_att1tab,
4414 attribute2_tab,
4415 attribute3_tab;
4416
4417 CLOSE get_details_to_delete;
4418 IF time_building_block_id_tab.COUNT > 0
4419 THEN
4420
4421 FORALL i IN l_rowidtab.FIRST..l_rowidtab.LAST
4422 DELETE FROM hxc_ret_pay_latest_details
4423 WHERE rowid = CHARTOROWID(l_rowidtab(i));
4424
4425
4426 IF g_debug
4427 THEN
4428 hr_utility.trace('About to update all the orig details into the new records. ');
4429 hr_utility.trace('Count '||time_building_block_id_tab.COUNT);
4430 END IF;
4431
4432
4433 -- Update this back on to the RDB table. These actual values would be used by
4434 -- Rollback process.
4435 FORALL i IN time_building_block_id_tab.FIRST..time_building_block_id_tab.LAST
4436 UPDATE hxc_ret_pay_latest_details
4437 SET hxt_request_id = hxt_reqtab(i),
4438 hxt_measure = hxt_measuretab(i),
4439 hxt_element_id = hxt_att1tab(i),
4440 hxt_attribute2 = attribute2_tab(i),
4441 hxt_attribute3 = attribute3_tab(i),
4442 hxt_batch_id = p_batch_id
4443 WHERE time_building_block_id = time_building_block_id_tab(i)
4444 AND object_version_number = object_version_number_tab(i);
4445
4446 END IF;
4447
4448
4449
4450
4451 END snap_retrieval_details;
4452
4453
4454
4455
4456
4457
4458
4459 END hxt_retro_mix;