[Home] [Help]
PACKAGE BODY: APPS.HXT_RETRO_MIX
Source
1 PACKAGE BODY hxt_retro_mix AS
2 /* $Header: hxtrmix.pkb 120.7 2007/01/18 05:44:38 nissharm noship $ */
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 PROCEDURE mark_prev_hours_rows (p_tim_id IN NUMBER);
44
45 PROCEDURE mark_prev_amount_rows (p_tim_id IN NUMBER);
46
47 PROCEDURE back_out_leftover_hours (p_batch_id NUMBER, p_tim_id NUMBER);
48
49 PROCEDURE back_out_leftover_amount (p_batch_id NUMBER, p_tim_id NUMBER);
50
51 PROCEDURE mark_retro_rows_complete (p_tim_id NUMBER);
52
53 g_lookup_not_found EXCEPTION; --GLOBAL
54 g_error_ins_batch_lines EXCEPTION; --SIR517 PWM 18FEB00
55
56
57 --
58 -- This function created to get the lookup_code for translated input-value names
59 --
60 FUNCTION get_lookup_code (p_meaning IN VARCHAR2, p_date_active IN DATE)
61 RETURN VARCHAR2
62 IS
63 l_lookup_code hr_lookups.lookup_code%TYPE;
64
65 CURSOR get_lookup_code_cur
66 IS
67 SELECT lookup_code
68 FROM hr_lookups
69 WHERE meaning = p_meaning
70 AND lookup_type = 'NAME_TRANSLATIONS'
71 AND enabled_flag = 'Y'
72 AND p_date_active BETWEEN NVL (start_date_active, p_date_active)
73 AND NVL (end_date_active, p_date_active);
74 BEGIN
75 hxt_util.DEBUG (
76 'get_lookup_ code for meaning = '
77 || p_meaning
78 || ' type = '
79 || 'NAME_TRANSLATIONS'
80 || ' date = '
81 || fnd_date.date_to_chardate (p_date_active)
82 ); --FORMS60 --HXT115
83
84 IF p_meaning IS NOT NULL
85 THEN
86 OPEN get_lookup_code_cur;
87 FETCH get_lookup_code_cur INTO l_lookup_code;
88
89 IF get_lookup_code_cur%NOTFOUND
90 THEN
91
92 -- FND_MESSAGE.SET_NAME('HXT','HXT_39483_LOOKUP_NOT_FOUND');
93 -- FND_MESSAGE.SET_TOKEN('CODE', p_meaning); --SIR517 PWM 18FEB00
94 -- FND_MESSAGE.SET_TOKEN('TYPE', 'NAME_TRANSLATIONS'); --SIR517 PWM 18FEB00
95 -- RAISE g_lookup_not_found;
96 NULL; -- This is to fix bug 1761779. Fassadi 16-may-2001
97 END IF;
98 ELSE
99 l_lookup_code := p_meaning;
100 END IF;
101
102 RETURN l_lookup_code;
103 END get_lookup_code;
104
105
106 --BEGIN GLOBAL
107 FUNCTION convert_lookup (
108 p_lookup_code IN VARCHAR2,
109 p_lookup_type IN VARCHAR2,
110 p_date_active IN DATE
111 )
112 RETURN VARCHAR2
113 IS
114 l_meaning hr_lookups.meaning%TYPE;
115
116 CURSOR get_meaning_cur (p_code VARCHAR2, p_type VARCHAR2, p_date DATE)
117 IS
118 SELECT fcl.meaning
119 FROM hr_lookups fcl --FORMS60
120 WHERE fcl.lookup_code = p_code
121 AND fcl.lookup_type = p_type
122 AND fcl.enabled_flag = 'Y'
123 AND p_date BETWEEN NVL (fcl.start_date_active, p_date)
124 AND NVL (fcl.end_date_active, p_date);
125 BEGIN
126 hxt_util.DEBUG (
127 'convert_lookup - code = '
128 || p_lookup_code
129 || ' type = '
130 || p_lookup_type
131 || ' date = '
132 || fnd_date.date_to_chardate (p_date_active)
133 ); --FORMS60 --HXT115
134
135 IF p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL
136 THEN
137 OPEN get_meaning_cur (p_lookup_code, p_lookup_type, p_date_active);
138 FETCH get_meaning_cur INTO l_meaning;
139
140 IF get_meaning_cur%NOTFOUND
141 THEN
142 fnd_message.set_name ('HXT', 'HXT_39483_LOOKUP_NOT_FOUND');
143 fnd_message.set_token ('CODE', p_lookup_code);
144 fnd_message.set_token ('TYPE', p_lookup_type);
145 RAISE g_lookup_not_found;
146 END IF;
147 ELSE
148 l_meaning := p_lookup_code;
149 END IF;
150
151 RETURN l_meaning;
152 END convert_lookup;
153
154 PROCEDURE insert_pay_batch_lines (
155 p_batch_id NUMBER,
156 p_batch_line_id OUT NOCOPY NUMBER,
157 p_assignment_id NUMBER,
158 p_assignment_number VARCHAR2,
159 p_amount NUMBER,
160 p_cost_allocation_keyflex_id NUMBER,
161 p_concatenated_segments VARCHAR2,
162 p_segment1 VARCHAR2,
163 p_segment2 VARCHAR2,
164 p_segment3 VARCHAR2,
165 p_segment4 VARCHAR2,
166 p_segment5 VARCHAR2,
167 p_segment6 VARCHAR2,
168 p_segment7 VARCHAR2,
169 p_segment8 VARCHAR2,
170 p_segment9 VARCHAR2,
171 p_segment10 VARCHAR2,
172 p_segment11 VARCHAR2,
173 p_segment12 VARCHAR2,
174 p_segment13 VARCHAR2,
175 p_segment14 VARCHAR2,
176 p_segment15 VARCHAR2,
177 p_segment16 VARCHAR2,
178 p_segment17 VARCHAR2,
179 p_segment18 VARCHAR2,
180 p_segment19 VARCHAR2,
181 p_segment20 VARCHAR2,
182 p_segment21 VARCHAR2,
183 p_segment22 VARCHAR2,
184 p_segment23 VARCHAR2,
185 p_segment24 VARCHAR2,
186 p_segment25 VARCHAR2,
187 p_segment26 VARCHAR2,
188 p_segment27 VARCHAR2,
189 p_segment28 VARCHAR2,
190 p_segment29 VARCHAR2,
191 p_segment30 VARCHAR2,
192 p_element_type_id NUMBER,
193 p_element_name VARCHAR2,
194 p_hourly_rate NUMBER,
195 p_locality_worked VARCHAR2,
196 p_rate_code VARCHAR2,
197 p_rate_multiple NUMBER,
198 p_separate_check_flag VARCHAR2,
199 p_tax_separately_flag VARCHAR2,
200 p_hours NUMBER,
201 p_date_worked DATE,
202 p_reason VARCHAR2,
203 p_batch_sequence NUMBER,
204 p_state_name VARCHAR2 default null, --dd
205 p_county_name VARCHAR2 default null,
206 p_city_name VARCHAR2 default null,
207 p_zip_code varchar2 default null
208 )
209 IS
210 -- l_batch_sequence NUMBER;
211 l_return NUMBER; --SIR517 PWM 18FEB00
212 l_batch_line_id number;
213 l_batch_line_ovn number;
214
215 TYPE input_value_record IS RECORD (
216 SEQUENCE pay_input_values_f.input_value_id%TYPE,
217 NAME pay_input_values_f_tl.NAME%TYPE, --FORMS60
218 lookup pay_input_values_f.lookup_type%TYPE);
219
220 TYPE input_values_table IS TABLE OF input_value_record
221 INDEX BY BINARY_INTEGER;
222
223 hxt_value input_values_table;
224
225 TYPE pbl_values_table IS TABLE OF pay_batch_lines.value_1%TYPE
226 INDEX BY BINARY_INTEGER;
227
228 pbl_value pbl_values_table;
229 l_value_meaning hr_lookups.meaning%TYPE;
230
231 CURSOR c_date_input_value (
232 cp_element_type_id NUMBER,
233 cp_assignment_id NUMBER,
234 cp_effective_date DATE
235 )
236 IS
237 SELECT DISTINCT piv.NAME -- PIV.display_sequence
238 FROM --pay_element_types_f PET
239 pay_input_values_f piv,
240 pay_accrual_plans pap,
241 pay_net_calculation_rules pncr
242 WHERE --PET.element_type_id = cp_element_type_id
243
244 -- AND PET.element_type_id = PIV.element_type_id
245 piv.element_type_id = cp_element_type_id
246 AND cp_effective_date BETWEEN piv.effective_start_date
247 AND piv.effective_end_date
248 AND pncr.date_input_value_id = piv.input_value_id
249 AND pncr.input_value_id <> pap.pto_input_value_id
250 AND pncr.input_value_id <> pap.co_input_value_id
251 AND pncr.accrual_plan_id = pap.accrual_plan_id
252 AND pap.accrual_plan_id IN
253 (SELECT papl.accrual_plan_id
254 FROM pay_accrual_plans papl,
255 pay_element_links_f pel,
256 pay_element_entries_f pee
257 WHERE pel.element_type_id =
258 papl.accrual_plan_element_type_id
259 AND cp_effective_date
260 BETWEEN pel.effective_start_date
261 AND pel.effective_end_date
262 AND pee.element_link_id =
263 pel.element_link_id
264 AND pee.assignment_id = cp_assignment_id
265 AND cp_effective_date
266 BETWEEN pee.effective_start_date
267 AND pee.effective_end_date);
268
269 l_piv_name VARCHAR2 (30);
270 lv_pbl_flag VARCHAR2 (1) := 'N';
271 BEGIN
272
273 if g_debug then
274 hr_utility.set_location ('insert_pay_batch_lines', 10);
275 end if;
276
277 -- Initialize tables
278 FOR i IN 1 .. 15
279 LOOP
280 hxt_value (i).SEQUENCE := NULL;
281 hxt_value (i).NAME := NULL;
282 hxt_value (i).lookup := NULL;
283 pbl_value (i) := NULL;
284 END LOOP;
285
286 -- Get input values details for this element
287 pay_paywsqee_pkg.get_input_value_details (
288 p_element_type_id,
289 p_date_worked,
290 hxt_value (1).SEQUENCE,
291 hxt_value (2).SEQUENCE,
292 hxt_value (3).SEQUENCE,
293 hxt_value (4).SEQUENCE,
294 hxt_value (5).SEQUENCE,
295 hxt_value (6).SEQUENCE,
296 hxt_value (7).SEQUENCE,
297 hxt_value (8).SEQUENCE,
298 hxt_value (9).SEQUENCE,
299 hxt_value (10).SEQUENCE,
300 hxt_value (11).SEQUENCE,
301 hxt_value (12).SEQUENCE,
302 hxt_value (13).SEQUENCE,
303 hxt_value (14).SEQUENCE,
304 hxt_value (15).SEQUENCE,
305 hxt_value (1).NAME,
306 hxt_value (2).NAME,
307 hxt_value (3).NAME,
308 hxt_value (4).NAME,
309 hxt_value (5).NAME,
310 hxt_value (6).NAME,
311 hxt_value (7).NAME,
312 hxt_value (8).NAME,
313 hxt_value (9).NAME,
314 hxt_value (10).NAME,
315 hxt_value (11).NAME,
316 hxt_value (12).NAME,
317 hxt_value (13).NAME,
318 hxt_value (14).NAME,
319 hxt_value (15).NAME,
320 hxt_value (1).lookup,
321 hxt_value (2).lookup,
322 hxt_value (3).lookup,
323 hxt_value (4).lookup,
324 hxt_value (5).lookup,
325 hxt_value (6).lookup,
326 hxt_value (7).lookup,
327 hxt_value (8).lookup,
328 hxt_value (9).lookup,
329 hxt_value (10).lookup,
330 hxt_value (11).lookup,
331 hxt_value (12).lookup,
332 hxt_value (13).lookup,
333 hxt_value (14).lookup,
334 hxt_value (15).lookup
335 );
336 if g_debug then
337 hr_utility.set_location ('insert_pay_batch_lines', 20);
338 end if;
339 -- Place OTM data into BEE values per input values
340 hxt_util.DEBUG ('Putting OTM data into BEE values per input values'); --HXT115
341
342
343 --
344 -- In order to get the input-value logic work in diiferent legislations we need
345 -- to create (SEED) new lookups for 'Hours', 'Hourly Rate', 'Rate Multiple',
346 -- and 'Rate Code' with lookup_type of 'NAME_TRANSLATION' and lookup_code of
347 -- 'HOURS', 'HOURLY_RATE', 'RATE_MULTIPLE' and 'RATE_CODE' respectively.
348 -- Then the customers in different countries need to create the above input
349 -- values with the name which is directly translated from the above names for
350 -- OTM elements.
351 --
352 -- For example: In French the user must create an input value for 'Hours' to
353 -- be 'Heures' and then to determine which input value 'Heures' is associated
354 -- with we look at the hr_lookups and if we find an entry with lookup_type =
355 -- 'NAME_TRANSLATIONS' and lookup_code = 'HOURS' and Meaning to be 'Heures'
356 -- then we know that this input vale woul map to 'Hours'.
357 --
358 -- What need to be noted that it is the customer's responsibilty to create
359 -- input values which are the direct translation of 'Hours','Hourly Rate',
360 -- 'Pay Value' , 'Rate Multiple' and 'Rate Code'
361 --
362 FOR i IN 1 .. 15
363 LOOP
364
365 --
366 -- We need to get the lookup_code for the input_value names before processing
367 -- the further logic on the screen value for the input values.
368 --
369 lv_pbl_flag := 'N';
370 if g_debug then
371 hr_utility.set_location ('insert_pay_batch_lines', 30);
372
373 hr_utility.TRACE (
374 'hxt_value_name_'
375 || TO_CHAR (i)
376 || ' :'
377 || hxt_value (i).NAME
378 );
379 hr_utility.TRACE ( 'p_date_worked:'
380 || p_date_worked);
381 end if;
385 hr_utility.TRACE ( 'l_value_meaning :'
382 l_value_meaning :=
383 get_lookup_code (hxt_value (i).NAME, p_date_worked);
384 if g_debug then
386 || l_value_meaning);
387 end if;
388 --if hxt_value(i).name = 'Hours' then
389 IF l_value_meaning = 'HOURS'
390 THEN
391 if g_debug then
392 hr_utility.set_location ('insert_pay_batch_lines', 40);
393 end if;
394 pbl_value (i) :=
395 convert_lookup (p_hours, hxt_value (i).lookup, p_date_worked);
396 if g_debug then
397 hr_utility.TRACE (
398 'pbl_value_'
399 || TO_CHAR (i)
400 || ' :'
401 || pbl_value (i)
402 );
403 end if;
404 --elsif hxt_value(i).name = 'Pay Value' then
405 ELSIF l_value_meaning = 'AMOUNT'
406 THEN
407 if g_debug then
408 hr_utility.set_location ('insert_pay_batch_lines', 50);
409 end if;
410 pbl_value (i) :=
411 convert_lookup (p_amount, hxt_value (i).lookup, p_date_worked);
412 if g_debug then
413 hr_utility.TRACE (
414 'pbl_value_'
415 || TO_CHAR (i)
416 || ' :'
417 || pbl_value (i)
418 );
419 end if;
420 --elsif hxt_value(i).name = 'Multiple' then
421 ELSIF l_value_meaning = 'RATE_MULTIPLE'
422 THEN
423 if g_debug then
424 hr_utility.set_location ('insert_pay_batch_lines', 60);
425 end if;
426 pbl_value (i) := convert_lookup (
427 p_rate_multiple,
428 hxt_value (i).lookup,
429 p_date_worked
430 );
431
432 if g_debug then
433 hr_utility.TRACE (
434 'pbl_value_'
435 || TO_CHAR (i)
436 || ' :'
437 || pbl_value (i)
438 );
439 end if;
440
441 ELSIF l_value_meaning = 'HOURLY_RATE'
442 THEN
443 if g_debug then
444 hr_utility.set_location ('insert_pay_batch_lines', 70);
445 end if;
446 pbl_value (i) := convert_lookup (
447 p_hourly_rate,
448 hxt_value (i).lookup,
449 p_date_worked
450 );
451 if g_debug then
452
453 hr_utility.TRACE (
454 'pbl_value_'
455 || TO_CHAR (i)
456 || ' :'
457 || pbl_value (i)
458 );
459 end if;
460
461
462 --elsif hxt_value(i).name = 'Rate' then
463 ELSIF l_value_meaning = 'RATE'
464 THEN
465 if g_debug then
466 hr_utility.set_location ('insert_pay_batch_lines', 70);
467 end if;
468 pbl_value (i) := convert_lookup (
469 p_hourly_rate,
470 hxt_value (i).lookup,
471 p_date_worked
472 );
473 if g_debug then
474
475 hr_utility.TRACE (
476 'pbl_value_'
477 || TO_CHAR (i)
478 || ' :'
479 || pbl_value (i)
480 );
481 end if;
482 --elsif hxt_value(i).name = 'Rate Code' then
483 ELSIF l_value_meaning = 'RATE_CODE'
484 THEN
485 if g_debug then
486 hr_utility.set_location ('insert_pay_batch_lines', 80);
487 end if;
488 pbl_value (i) := convert_lookup (
489 p_rate_code,
490 hxt_value (i).lookup,
491 p_date_worked
492 );
493 if g_debug then
494 hr_utility.TRACE (
495 'pbl_value_'
496 || TO_CHAR (i)
497 || ' :'
498 || pbl_value (i)
499 );
500 end if;
501
502 -- BEGIN US localization
503 ELSIF hxt_value (i).NAME = 'Jurisdiction'
504 THEN
505 if g_debug then
506 hr_utility.set_location ('insert_pay_batch_lines', 90);
507 end if;
508
509 if(p_state_name is not null or
510 p_county_name is not null or
511 p_city_name is not null or
512 p_zip_code is not null) then
513 pbl_value(i):=convert_lookup(pay_ac_utility.get_geocode(p_state_name,
514 p_county_name,
515 p_city_name,
516 p_zip_code),
517 hxt_value(i).lookup,
518 p_date_worked);
519 else
520 pbl_value (i) := convert_lookup (
521 p_locality_worked,
522 hxt_value (i).lookup,
523 p_date_worked
524 );
525 end if;
526 if g_debug then
527 hr_utility.TRACE (
528 'pbl_value_'
529 || TO_CHAR (i)
530 || ' :'
534 ELSIF hxt_value (i).NAME = 'Deduction Processing'
531 || pbl_value (i)
532 );
533 end if;
535 THEN
536 if g_debug then
537 hr_utility.set_location ('insert_pay_batch_lines', 100);
538 end if;
539 pbl_value (i) := convert_lookup (
540 p_tax_separately_flag,
541 hxt_value (i).lookup,
542 p_date_worked
543 );
544 if g_debug then
545 hr_utility.TRACE (
546 'pbl_value_'
547 || TO_CHAR (i)
548 || ' :'
549 || pbl_value (i)
550 );
551 end if;
552 ELSIF hxt_value (i).NAME = 'Separate Check'
553 THEN
554 if g_debug then
555 hr_utility.set_location ('insert_pay_batch_lines', 110);
556 end if;
557 pbl_value (i) := convert_lookup (
558 p_separate_check_flag,
559 hxt_value (i).lookup,
560 p_date_worked
561 );
562 if g_debug then
563 hr_utility.TRACE (
564 'pbl_value_'
565 || TO_CHAR (i)
566 || ' :'
567 || pbl_value (i)
568 );
569 end if;
570
571 -- END US localization
572
573 ELSIF hxt_value (i).NAME IS NOT NULL
574 THEN -- pbl_value(i) := NULL;
575 if g_debug then
576 hr_utility.set_location ('insert_pay_batch_lines', 120);
577 hr_utility.TRACE ( 'p_element_type_id :'
578 || p_element_type_id);
579 hr_utility.TRACE ( 'p_assignment_id :'
580 || p_assignment_id);
581 hr_utility.TRACE ( 'p_date_worked :'
582 || p_date_worked);
583 end if;
584 OPEN c_date_input_value (
585 p_element_type_id,
586 p_assignment_id,
587 p_date_worked
588 );
589
590 LOOP
591 if g_debug then
592 hr_utility.set_location ('insert_pay_batch_lines', 130);
593 end if;
594 FETCH c_date_input_value INTO l_piv_name;
595 EXIT WHEN c_date_input_value%NOTFOUND;
596 if g_debug then
597 hr_utility.TRACE ( 'l_piv_name :'
598 || l_piv_name);
599 hr_utility.TRACE ( 'lv_pbl_flag :'
600 || lv_pbl_flag);
601 end if;
602
603 IF l_piv_name = hxt_value (i).NAME
604 THEN
605 if g_debug then
606 hr_utility.set_location ('insert_pay_batch_lines', 140);
607 end if;
608 --pbl_value(i) := to_char(p_date_worked,'DD-MON-YYYY');
609 pbl_value (i) := fnd_date.date_to_canonical (p_date_worked);
610 lv_pbl_flag := 'Y';
611 if g_debug then
612 hr_utility.TRACE (
613 'pbl_value_'
614 || TO_CHAR (i)
615 || ' :'
616 || pbl_value (i)
617 );
618 end if;
619 EXIT;
620 END IF;
621 END LOOP;
622
623 CLOSE c_date_input_value;
624
625 IF lv_pbl_flag = 'N'
626 THEN
627 if g_debug then
628 hr_utility.set_location ('insert_pay_batch_lines', 150);
629 end if;
630 pbl_value (i) := NULL;
631 if g_debug then
632 hr_utility.TRACE (
633 'pbl_value_'
634 || TO_CHAR (i)
635 || ' :'
636 || pbl_value (i)
637 );
638 end if;
639 END IF;
640
641 if g_debug then
642 hr_utility.TRACE ( 'lv_pbl_flag :'
643 || lv_pbl_flag);
644 end if;
645 ELSE
646 if g_debug then
647 hr_utility.set_location ('insert_pay_batch_lines', 160);
648 end if;
649 pbl_value (i) := NULL;
650 if g_debug then
651 hr_utility.TRACE (
652 'pbl_value_'
653 || TO_CHAR (i)
654 || ' :'
655 || pbl_value (i)
656 );
657 end if;
658 END IF;
659
660 if g_debug then
661 hr_utility.set_location ('insert_pay_batch_lines', 170);
662 end if;
663 hxt_util.DEBUG ( 'value_'
664 || TO_CHAR (i)
665 || ' = '
666 || pbl_value (i)); --HXT115
667 END LOOP;
668
669 -- Get next sequence number
670 -- l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence (p_batch_id);
671 hxt_util.DEBUG ( 'batch_sequence = '
672 || TO_CHAR (p_batch_sequence)); --HXT115
673
677 ,p_batch_id => p_batch_id
674 -- Add new hours data
675 PAY_BATCH_ELEMENT_ENTRY_API.create_batch_line
676 (p_session_date => sysdate
678 ,p_batch_line_status => 'U'
679 ,p_assignment_id => p_assignment_id
680 ,p_assignment_number => p_assignment_number
681 ,p_date_earned => p_date_worked
682 ,p_batch_sequence => p_batch_sequence
683 ,p_concatenated_segments => p_concatenated_segments
684 ,p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id
685 ,p_effective_date => p_date_worked
686 ,p_element_name => p_element_name
687 ,p_element_type_id => p_element_type_id
688 ,p_entry_type => 'E'
689 ,p_reason => p_reason
690 ,p_segment1 => p_segment1
691 ,p_segment2 => p_segment2
692 ,p_segment3 => p_segment3
693 ,p_segment4 => p_segment4
694 ,p_segment5 => p_segment5
695 ,p_segment6 => p_segment6
696 ,p_segment7 => p_segment7
697 ,p_segment8 => p_segment8
698 ,p_segment9 => p_segment9
699 ,p_segment10 => p_segment10
700 ,p_segment11 => p_segment11
701 ,p_segment12 => p_segment12
702 ,p_segment13 => p_segment13
703 ,p_segment14 => p_segment14
704 ,p_segment15 => p_segment15
705 ,p_segment16 => p_segment16
706 ,p_segment17 => p_segment17
707 ,p_segment18 => p_segment18
708 ,p_segment19 => p_segment19
709 ,p_segment20 => p_segment20
710 ,p_segment21 => p_segment21
711 ,p_segment22 => p_segment22
712 ,p_segment23 => p_segment23
713 ,p_segment24 => p_segment24
714 ,p_segment25 => p_segment25
715 ,p_segment26 => p_segment26
716 ,p_segment27 => p_segment27
717 ,p_segment28 => p_segment28
718 ,p_segment29 => p_segment29
719 ,p_segment30 => p_segment30
720 ,p_value_1 => pbl_value(1)
721 ,p_value_2 => pbl_value(2)
722 ,p_value_3 => pbl_value(3)
723 ,p_value_4 => pbl_value(4)
724 ,p_value_5 => pbl_value(5)
725 ,p_value_6 => pbl_value(6)
726 ,p_value_7 => pbl_value(7)
727 ,p_value_8 => pbl_value(8)
728 ,p_value_9 => pbl_value(9)
729 ,p_value_10 => pbl_value(10)
730 ,p_value_11 => pbl_value(11)
731 ,p_value_12 => pbl_value(12)
732 ,p_value_13 => pbl_value(13)
733 ,p_value_14 => pbl_value(14)
734 ,p_value_15 => pbl_value(15)
735 ,p_batch_line_id => l_batch_line_id
736 ,p_object_version_number => l_batch_line_ovn
737 );
738
739 p_batch_line_id := l_batch_line_id;
740 EXCEPTION
741 WHEN g_lookup_not_found
742 THEN
743 hxt_util.DEBUG (
744 'Oops...g_lookup_not_found in insert_pay_batch_lines'
745 ); --HXT115
746 RAISE g_lookup_not_found; --SIR517 PWM 18FEB00 Re-raise the exception for the calling procedure
747 WHEN OTHERS
748 THEN
749 hxt_util.DEBUG (SQLERRM); --HXT115
750 hxt_util.DEBUG ('Oops...others in insert_pay_batch_lines'); --HXT115
751 fnd_message.set_name ('HXT', 'HXT_39354_ERR_INS_PAYMX_INFO');
752 fnd_message.set_token ('SQLERR', SQLERRM);
753 RAISE g_error_ins_batch_lines; --SIR517 PWM 18FEB00 Re-raise the exception for the calling procedure
754 END insert_pay_batch_lines;
755
756
757 --END GLOBAL
758
759 PROCEDURE retro_sum_to_mix (
760 p_batch_id IN NUMBER,
761 p_tim_id IN NUMBER,
762 p_sum_retcode OUT NOCOPY NUMBER,
763 p_err_buf OUT NOCOPY VARCHAR2
764 )
765 IS
766
767 -- select current hours by detail where hours<>0 and amount=0
768 CURSOR current_hours (p_tim_id NUMBER)
769 IS
770 SELECT asm.assignment_number, elt.element_name, --FORMS60
771 eltv.hxt_premium_type, --SIR65
772 eltv.hxt_premium_amount, --SIR65
773 eltv.hxt_earning_category, --SIR65
774 DECODE (
775 SIGN (
776 DECODE (
777 SIGN (
778 ptp.start_date
779 - asm.effective_start_date
780 ),
781 1, ptp.start_date,
782 asm.effective_start_date
783 )
784 - elt.effective_start_date
785 ),
786 1, DECODE (
790 asm.effective_start_date
787 SIGN ( ptp.start_date
788 - asm.effective_start_date),
789 1, ptp.start_date,
791 ),
792 elt.effective_start_date
793 )
794 from_date,
795 DECODE (
796 SIGN (
797 DECODE (
798 SIGN ( ptp.end_date
799 - asm.effective_end_date),
800 -1, ptp.end_date,
801 asm.effective_end_date
802 )
803 - elt.effective_end_date
804 ),
805 -1, DECODE (
806 SIGN ( ptp.end_date
807 - asm.effective_end_date),
808 -1, ptp.end_date,
809 asm.effective_end_date
810 ),
811 elt.effective_end_date
812 ) TO_DATE,
813 rate_multiple, hrw.hourly_rate,
814
815
816 loct.location_code locality_worked, --FORMS60
817 ffvr.flex_value rate_code, hrw.separate_check_flag,
818 hrw.fcl_tax_rule_code tax_separately_flag, hrw.amount,
819 hrw.hours hours_worked, hrw.assignment_id,
820 /* fk - assignment_number */
821 hrw.ffv_cost_center_id, /* fk - cost_center_code */ pcak.concatenated_segments,
822 pcak.segment1, pcak.segment2, pcak.segment3, pcak.segment4,
823 pcak.segment5, pcak.segment6, pcak.segment7, pcak.segment8,
824 pcak.segment9, pcak.segment10, pcak.segment11,
825 pcak.segment12, pcak.segment13, pcak.segment14,
826 pcak.segment15, pcak.segment16, pcak.segment17,
827 pcak.segment18, pcak.segment19, pcak.segment20,
828 pcak.segment21, pcak.segment22, pcak.segment23,
829 pcak.segment24, pcak.segment25, pcak.segment26,
830 pcak.segment27, pcak.segment28, pcak.segment29,
831 pcak.segment30, hrw.element_type_id,
832 hrw.location_id,
833 hrw.ffv_rate_code_id,
834 asm.effective_end_date asm_effective_end_date,
835 elt.effective_end_date elt_effective_end_date,
836 hrw.parent_id, hrw.ROWID hrw_rowid, -- OHM180
837 hcl.meaning reason, --GLOBAL
838 hrw.date_worked, ptp.time_period_id,
839 hrw.state_name,
840 hrw.county_name,
841 hrw.city_name,
842 hrw.zip_code
843 FROM hxt_timecards_x tim,
844 per_time_periods ptp,
845 hxt_det_hours_worked_x hrw,
846 hr_lookups hcl, --GLOBAL
847 per_assignments_f asm,
848 pay_element_types_f elt,
849 hxt_pay_element_types_f_ddf_v eltv, --SIR65
850 pay_cost_allocation_keyflex pcak,
851 hr_locations_all_tl loct, --FORMS60
852 hr_locations_no_join loc, --FORMS60
853 fnd_flex_values ffvr
854 WHERE hrw.ffv_rate_code_id = ffvr.flex_value_id(+)
855 AND hrw.location_id = loc.location_id(+)
856
857 --BEGIN FORMS60
858 AND loc.location_id = loct.location_id(+)
859 AND DECODE (loct.location_id, NULL, '1', loct.LANGUAGE) =
860 DECODE (loct.location_id, NULL, '1', USERENV ('LANG'))
861
862 --END FORMS60
863
864 AND hrw.ffv_cost_center_id =
865 pcak.cost_allocation_keyflex_id(+)
866 AND hrw.date_worked BETWEEN elt.effective_start_date
867 AND elt.effective_end_date
868 AND hrw.element_type_id = elt.element_type_id
869 AND elt.element_type_id = eltv.element_type_id
870 AND hrw.date_worked BETWEEN eltv.effective_start_date
871
872 AND eltv.effective_end_date
873 AND hrw.date_worked BETWEEN asm.effective_start_date
874 AND asm.effective_end_date
875 AND hrw.assignment_id = asm.assignment_id
876 AND hrw.amount IS NULL
877
878
879 AND hrw.tim_id = tim.id
880 AND tim.id = p_tim_id
881 AND tim.time_period_id = ptp.time_period_id
882
883 --BEGIN GLOBAL
884 AND hrw.date_worked BETWEEN NVL (
885 hcl.start_date_active(+),
886 hrw.date_worked
887 )
888 AND NVL (
889 hcl.end_date_active(+),
890 hrw.date_worked
891 )
892 AND hcl.lookup_type(+) = 'ELE_ENTRY_REASON'
893 AND hcl.lookup_code(+) = hrw.fcl_earn_reason_code
894
895 --END GLOBAL
896 ORDER BY hrw.id; --SIR95
897
898
899 -- select current amounts by detail where hours=0 and amount is not null
903 DECODE (
900 CURSOR current_amount (p_tim_id NUMBER)
901 IS
902 SELECT asm.assignment_number, elt.element_name, --FORMS60
904 SIGN (
905 DECODE (
906 SIGN (
907 ptp.start_date
908 - asm.effective_start_date
909 ),
910 1, ptp.start_date,
911 asm.effective_start_date
912 )
913 - elt.effective_start_date
914 ),
915 1, DECODE (
916 SIGN ( ptp.start_date
917 - asm.effective_start_date),
918 1, ptp.start_date,
919 asm.effective_start_date
920 ),
921 elt.effective_start_date
922 )
923 from_date,
924 DECODE (
925 SIGN (
926 DECODE (
927 SIGN ( ptp.end_date
928 - asm.effective_end_date),
929 -1, ptp.end_date,
930 asm.effective_end_date
931 )
932 - elt.effective_end_date
933 ),
934 -1, DECODE (
935 SIGN ( ptp.end_date
936 - asm.effective_end_date),
937 -1, ptp.end_date,
938 asm.effective_end_date
939 ),
940 elt.effective_end_date
941 ) TO_DATE,
942 rate_multiple, hrw.hourly_rate,
943
944
945 loct.location_code locality_worked, --FORMS60
946 ffvr.flex_value rate_code, hrw.separate_check_flag,
947 hrw.fcl_tax_rule_code tax_separately_flag,
948 hrw.hours hours_worked, hrw.amount amount,
949 hrw.assignment_id, hrw.ffv_cost_center_id,
950
951 pcak.concatenated_segments, pcak.segment1, pcak.segment2,
952 pcak.segment3, pcak.segment4, pcak.segment5, pcak.segment6,
953 pcak.segment7, pcak.segment8, pcak.segment9, pcak.segment10,
954 pcak.segment11, pcak.segment12, pcak.segment13,
955 pcak.segment14, pcak.segment15, pcak.segment16,
956 pcak.segment17, pcak.segment18, pcak.segment19,
957 pcak.segment20, pcak.segment21, pcak.segment22,
958 pcak.segment23, pcak.segment24, pcak.segment25,
959 pcak.segment26, pcak.segment27, pcak.segment28,
960 pcak.segment29, pcak.segment30, hrw.element_type_id,
961
962
963
964 hrw.location_id, hrw.ffv_rate_code_id,
965
966 asm.effective_end_date asm_effective_end_date,
967 elt.effective_end_date elt_effective_end_date,
968 hrw.parent_id, hrw.ROWID hrw_rowid, -- OHM180
969 hcl.meaning reason, --GLOBAL
970 hrw.date_worked, ptp.time_period_id,
971 hrw.state_name,
972 hrw.county_name,
973 hrw.city_name,
974 hrw.zip_code
975 FROM hxt_timecards_x tim,
976 per_time_periods ptp,
977 hxt_det_hours_worked_x hrw,
978 hr_lookups hcl, --GLOBAL
979 per_assignments_f asm,
980 pay_element_types_f elt,
981 pay_cost_allocation_keyflex pcak,
982
983
984 hr_locations_all_tl loct, --FORMS60
985 hr_locations_no_join loc, --FORMS60
986 fnd_flex_values ffvr
987 WHERE hrw.ffv_rate_code_id = ffvr.flex_value_id(+)
988 AND hrw.location_id = loc.location_id(+)
989
990 --BEGIN FORMS60
991 AND loc.location_id = loct.location_id(+)
992 AND DECODE (loct.location_id, NULL, '1', loct.LANGUAGE) =
993 DECODE (loct.location_id, NULL, '1', USERENV ('LANG'))
994
995 --END FORMS60
996
997 AND hrw.ffv_cost_center_id =
998 pcak.cost_allocation_keyflex_id(+)
999 AND hrw.date_worked BETWEEN elt.effective_start_date
1000 AND elt.effective_end_date
1001 AND hrw.element_type_id = elt.element_type_id
1002 AND hrw.date_worked BETWEEN asm.effective_start_date
1003 AND asm.effective_end_date
1004 AND hrw.assignment_id = asm.assignment_id
1005 AND hrw.amount IS NOT NULL
1006
1007
1008 AND hrw.tim_id = tim.id
1009 AND tim.id = p_tim_id
1010 AND tim.time_period_id = ptp.time_period_id
1011
1012 --BEGIN GLOBAL
1013 AND hrw.date_worked BETWEEN NVL (
1014 hcl.start_date_active(+),
1015 hrw.date_worked
1016 )
1017 AND NVL (
1021 AND hcl.lookup_type(+) = 'ELE_ENTRY_REASON'
1018 hcl.end_date_active(+),
1019 hrw.date_worked
1020 )
1022 AND hcl.lookup_code(+) = hrw.fcl_earn_reason_code
1023
1024 --END GLOBAL
1025 ORDER BY hrw.id; --SIR95
1026
1027
1028 -- select previous (before retro) hours by detail where hours<>0 and amount is null
1029 CURSOR prev_hours (
1030 p_tim_id NUMBER,
1031 p_assignment_id NUMBER, -- fk - assignment_number
1032 p_asm_effective_end_date DATE,
1033 p_ffv_cost_center_id NUMBER, -- fk - cost_center_code
1034 p_element_type_id NUMBER, -- fk - element_name
1035 p_elt_effective_end_date DATE,
1036 p_from_date DATE,
1037 p_hourly_rate NUMBER,
1038
1039
1040 p_location_id NUMBER, -- fk - locality_worked
1041 p_ffv_rate_code_id NUMBER, -- fk - rate_code
1042 p_rate_multiple NUMBER,
1043 p_separate_check_flag VARCHAR2,
1044 p_fcl_tax_rule_code VARCHAR2,
1045 p_to_date DATE,
1046 p_parent_id NUMBER,
1047 p_state_name VARCHAR2,
1048 p_COUNTY_name VARCHAR2,
1049 p_CITY_name VARCHAR2,
1050 p_ZIP_CODE VARCHAR2 )
1051 IS
1052 SELECT hrw.hours hours_worked, hrw.ROWID hrw_rowid -- OHM199
1053 FROM hxt_timecards_x tim,
1054 per_time_periods ptp,
1055 hxt_det_hours_worked_f hrw,
1056 per_assignments_f asm,
1057 pay_element_types_f elt
1058 WHERE hrw.assignment_id = p_assignment_id
1059 AND asm.assignment_id = hrw.assignment_id
1060 AND asm.effective_end_date = p_asm_effective_end_date
1061 AND NVL (hrw.ffv_cost_center_id, 999999999999999) =
1062 NVL (p_ffv_cost_center_id, 999999999999999)
1063 AND hrw.element_type_id = p_element_type_id
1064 AND elt.element_type_id = hrw.element_type_id
1065 AND elt.effective_end_date = p_elt_effective_end_date
1066 AND p_from_date =
1067 DECODE (
1068 SIGN (
1069 DECODE (
1070 SIGN (
1071 ptp.start_date
1072 - asm.effective_start_date
1073 ),
1074 1, ptp.start_date,
1075 asm.effective_start_date
1076 )
1077 - elt.effective_start_date
1078 ),
1079 1, DECODE (
1080 SIGN (
1081 ptp.start_date
1082 - asm.effective_start_date
1083 ),
1084 1, ptp.start_date,
1085 asm.effective_start_date
1086 ),
1087 elt.effective_start_date
1088 )
1089 AND NVL (hrw.hourly_rate, 999999999999999) =
1090 NVL (p_hourly_rate, 999999999999999)
1091
1092 AND NVL (hrw.location_id, 999999999999999) =
1093 NVL (p_location_id, 999999999999999)
1094 AND NVL (hrw.ffv_rate_code_id, 999999999999999) =
1095 NVL (p_ffv_rate_code_id, 999999999999999)
1096 AND NVL (hrw.rate_multiple, 999999999999999) =
1097 NVL (p_rate_multiple, 999999999999999)
1098 AND NVL (hrw.separate_check_flag, 'ZZZZZZZZZZ') =
1099 NVL (p_separate_check_flag, 'ZZZZZZZZZZ')
1100 AND NVL (hrw.fcl_tax_rule_code, 'ZZZZZZZZZZ') =
1101 NVL (p_fcl_tax_rule_code, 'ZZZZZZZZZZ')
1102 AND p_to_date =
1103 DECODE (
1104 SIGN (
1105 DECODE (
1106 SIGN (
1107 ptp.end_date
1108 - asm.effective_end_date
1109 ),
1110 -1, ptp.end_date,
1111 asm.effective_end_date
1112 )
1113 - elt.effective_end_date
1114 ),
1115 -1, DECODE (
1116 SIGN (
1117 ptp.end_date
1118 - asm.effective_end_date
1119 ),
1120 -1, ptp.end_date,
1121 asm.effective_end_date
1122 ),
1123 elt.effective_end_date
1124 )
1125 AND hrw.date_worked BETWEEN elt.effective_start_date
1126 AND elt.effective_end_date
1127 AND hrw.date_worked BETWEEN asm.effective_start_date
1131 AND hrw.tim_id = tim.id
1128 AND asm.effective_end_date
1129 AND hrw.amount IS NULL
1130 AND hrw.parent_id = p_parent_id
1132 AND tim.id = p_tim_id
1133 AND tim.time_period_id = ptp.time_period_id
1134 AND hrw.pay_status = 'C'
1135 AND NVL (hrw.state_name, 'ZZZZZZZZZZ') =
1136 NVL (p_state_name, 'ZZZZZZZZZZ')
1137 AND NVL (hrw.county_name, 'ZZZZZZZZZZ') =
1138 NVL (p_county_name, 'ZZZZZZZZZZ')
1139 AND NVL (hrw.city_name, 'ZZZZZZZZZZ') =
1140 NVL (p_city_name, 'ZZZZZZZZZZ')
1141 AND NVL (hrw.zip_code, 'ZZZZZZZZZZ') =
1142 NVL (p_zip_code, 'ZZZZZZZZZZ')
1143
1144 ORDER BY hrw.id; --SIR95
1145
1146
1147 -- select previous (before retro) amounts by detail where hours=0 and amount<>0
1148 CURSOR prev_amount (
1149 p_tim_id NUMBER,
1150 p_assignment_id NUMBER, -- fk - assignment_number
1151 p_asm_effective_end_date DATE,
1152 p_ffv_cost_center_id NUMBER, -- fk - cost_center_code
1153 p_element_type_id NUMBER, -- fk - element_name
1154 p_elt_effective_end_date DATE,
1155 p_from_date DATE,
1156 p_hourly_rate NUMBER,
1157
1158
1159 p_location_id NUMBER, -- fk - locality_worked
1160 p_ffv_rate_code_id NUMBER, -- fk - rate_code
1161 p_rate_multiple NUMBER,
1162 p_separate_check_flag VARCHAR2,
1163 p_fcl_tax_rule_code VARCHAR2,
1164 p_to_date DATE,
1165 p_parent_id NUMBER,
1166 p_state_name VARCHAR2,
1167 p_COUNTY_name VARCHAR2,
1168 p_CITY_name VARCHAR2,
1169 p_ZIP_CODE VARCHAR2 )
1170
1171 IS
1172 SELECT hrw.amount amount, hrw.ROWID hrw_rowid -- OHM199
1173 FROM hxt_timecards_x tim,
1174 per_time_periods ptp,
1175 hxt_det_hours_worked_f hrw,
1176 per_assignments_f asm,
1177 pay_element_types_f elt
1178 WHERE hrw.assignment_id = p_assignment_id
1179 AND asm.assignment_id = hrw.assignment_id
1180 AND asm.effective_end_date = p_asm_effective_end_date
1181 AND NVL (hrw.ffv_cost_center_id, 999999999999999) =
1182 NVL (p_ffv_cost_center_id, 999999999999999)
1183 AND hrw.element_type_id = p_element_type_id
1184 AND elt.element_type_id = hrw.element_type_id
1185 AND elt.effective_end_date = p_elt_effective_end_date
1186 AND p_from_date =
1187 DECODE (
1188 SIGN (
1189 DECODE (
1190 SIGN (
1191 ptp.start_date
1192 - asm.effective_start_date
1193 ),
1194 1, ptp.start_date,
1195 asm.effective_start_date
1196 )
1197 - elt.effective_start_date
1198 ),
1199 1, DECODE (
1200 SIGN (
1201 ptp.start_date
1202 - asm.effective_start_date
1203 ),
1204 1, ptp.start_date,
1205 asm.effective_start_date
1206 ),
1207 elt.effective_start_date
1208 )
1209 AND NVL (hrw.hourly_rate, 999999999999999) =
1210 NVL (p_hourly_rate, 999999999999999)
1211
1212
1213 AND NVL (hrw.location_id, 999999999999999) =
1214 NVL (p_location_id, 999999999999999)
1215 AND NVL (hrw.ffv_rate_code_id, 999999999999999) =
1216 NVL (p_ffv_rate_code_id, 999999999999999)
1217 AND NVL (hrw.rate_multiple, 999999999999999) =
1218 NVL (p_rate_multiple, 999999999999999)
1219 AND NVL (hrw.separate_check_flag, 'ZZZZZZZZZZ') =
1220 NVL (p_separate_check_flag, 'ZZZZZZZZZZ')
1221 AND NVL (hrw.fcl_tax_rule_code, 'ZZZZZZZZZZ') =
1222 NVL (p_fcl_tax_rule_code, 'ZZZZZZZZZZ')
1223 AND p_to_date =
1224 DECODE (
1225 SIGN (
1226 DECODE (
1227 SIGN (
1228 ptp.end_date
1229 - asm.effective_end_date
1230 ),
1231 -1, ptp.end_date,
1232 asm.effective_end_date
1233 )
1234 - elt.effective_end_date
1235 ),
1239 - asm.effective_end_date
1236 -1, DECODE (
1237 SIGN (
1238 ptp.end_date
1240 ),
1241 -1, ptp.end_date,
1242 asm.effective_end_date
1243 ),
1244 elt.effective_end_date
1245 )
1246 AND hrw.date_worked BETWEEN elt.effective_start_date
1247 AND elt.effective_end_date
1248 AND hrw.date_worked BETWEEN asm.effective_start_date
1249 AND asm.effective_end_date
1250 AND hrw.amount IS NOT NULL
1251 AND hrw.parent_id > 0
1252 AND hrw.parent_id = p_parent_id
1253 AND hrw.tim_id = tim.id
1254 AND tim.id = p_tim_id
1255 AND tim.time_period_id = ptp.time_period_id
1256 AND hrw.pay_status = 'C'
1257 AND NVL (hrw.state_name, 'ZZZZZZZZZZ') =
1258 NVL (p_state_name, 'ZZZZZZZZZZ')
1259 AND NVL (hrw.county_name, 'ZZZZZZZZZZ') =
1260 NVL (p_county_name, 'ZZZZZZZZZZ')
1261 AND NVL (hrw.city_name, 'ZZZZZZZZZZ') =
1262 NVL (p_city_name, 'ZZZZZZZZZZ')
1263 AND NVL (hrw.zip_code, 'ZZZZZZZZZZ') =
1264 NVL (p_zip_code, 'ZZZZZZZZZZ')
1265
1266 ORDER BY hrw.id; --SIR95
1267
1268 l_return NUMBER;
1269
1270 --BSE128 l_hours_rec current_hours%ROWTYPE;
1271 l_prev_hours_rec prev_hours%ROWTYPE;
1272
1273 --BSE128 l_amount_rec current_amount%ROWTYPE;
1274 l_prev_amount_rec prev_amount%ROWTYPE;
1275 l_expired_pay_status CHAR (1);
1276 -- l_nextval NUMBER (15);
1277 l_batch_line_id NUMBER (15);
1278 l_hours_to_send NUMBER (7, 3);
1279 l_amount_to_send NUMBER (15, 5);
1280 l_retcode NUMBER; /* BSE107 */
1281 l_location VARCHAR2 (20);
1282
1283 l_batch_sequence PAY_BATCH_LINES.BATCH_SEQUENCE%TYPE;
1284
1285 BEGIN
1286 hxt_util.DEBUG ('retro_mix started.'); -- debug only --HXT115
1287
1288 /************************************************************/
1289 -- Step 1 - retro processing where hours<>0, amount=0
1290 /************************************************************/
1291 l_location := 'Step 1A';
1292
1293 -- bug 3217343 fix BEGIN
1294 -- get the next batch_seq for this batch_id only once and increment the
1295 -- sequence by 1 whenever insert_pay_batch_lines is called.
1296 -- This way the multiple expensive calls to
1297 -- pay_paywsqee_pkg.next_batch_sequence in insert_pay_batch_lines procedure
1298 -- can be avoided.
1299
1300 l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(p_batch_id);
1301
1302 -- bug 3217343 fix END
1303 FOR l_hours_rec IN current_hours (p_tim_id)
1304 LOOP
1305 hxt_util.DEBUG (
1306 'retro row is '
1307 || l_hours_rec.element_name
1308 || ' '
1309 || TO_CHAR (l_hours_rec.hours_worked)
1310 || ' '
1311 ); -- debug only --HXT115
1312 l_hours_to_send := l_hours_rec.hours_worked;
1313 OPEN prev_hours (
1314 p_tim_id,
1315 l_hours_rec.assignment_id, -- fk - assignment_number
1316 l_hours_rec.asm_effective_end_date,
1317 l_hours_rec.ffv_cost_center_id, -- fk - cost_center_code
1318 l_hours_rec.element_type_id, -- fk - element_name
1319 l_hours_rec.elt_effective_end_date,
1320 l_hours_rec.from_date,
1321 l_hours_rec.hourly_rate,
1322
1323 /*TA36 l_hours_rec.ffv_labor_account_id,*/ -- fk - labor_dist_code
1324 l_hours_rec.location_id, -- fk - locality_worked
1325 l_hours_rec.ffv_rate_code_id, -- fk - rate_code
1326 l_hours_rec.rate_multiple,
1327 l_hours_rec.separate_check_flag,
1328 l_hours_rec.tax_separately_flag, -- fcl_tax_rule_code
1329 l_hours_rec.TO_DATE,
1330 l_hours_rec.parent_id,
1331 l_hours_rec.state_name, --dd
1332 l_hours_rec.county_name,
1333 l_hours_rec.city_name,
1334 l_hours_rec.zip_code
1335 );
1336 FETCH prev_hours INTO l_prev_hours_rec;
1337
1338 IF prev_hours%FOUND
1339 THEN
1340 hxt_util.DEBUG (
1341 'orig row is '
1342 || TO_CHAR (l_prev_hours_rec.hours_worked)
1343 || ' '
1344 ); -- debug only --HXT115
1345 l_hours_to_send :=
1346 l_hours_rec.hours_worked
1347 - l_prev_hours_rec.hours_worked;
1348 l_location := 'Step 1B';
1349
1350
1351 -- begin OHM199
1352 IF l_hours_to_send = 0
1353 THEN
1354 UPDATE hxt_det_hours_worked_f
1355 SET pay_status = 'D',
1356 last_update_date = SYSDATE
1357 WHERE ROWID = l_prev_hours_rec.hrw_rowid
1361 ELSE
1358 -- ADDED BY MV: IF THERE IS ONLY ONE ROW, PREV and CURR records
1359 -- are the same; we should not update such records.
1360 AND l_prev_hours_rec.hrw_rowid <> l_hours_rec.hrw_rowid;
1362 UPDATE hxt_det_hours_worked_f
1363 SET pay_status = 'A',
1364 last_update_date = SYSDATE
1365 WHERE ROWID = l_prev_hours_rec.hrw_rowid;
1366 END IF;
1367
1368 -- end OHM199
1369 END IF;
1370
1371 hxt_util.DEBUG ( ' hours to send - '
1372 || TO_CHAR (l_hours_to_send)); -- debug only --HXT115
1373 l_location := 'Step 1C';
1374
1375 IF (l_hours_to_send <> 0)
1376 THEN
1377 --begin SIR65
1378 IF l_hours_rec.hxt_earning_category NOT IN ('REG', 'OVT', 'ABS')
1379 THEN
1380 IF l_hours_rec.hxt_premium_type = 'FACTOR'
1381 THEN
1382 IF l_hours_rec.rate_multiple IS NULL
1383 THEN
1384 l_hours_rec.rate_multiple :=
1385 l_hours_rec.hxt_premium_amount;
1386 END IF;
1387
1388 IF l_hours_rec.hourly_rate IS NULL
1389 THEN
1390 l_retcode :=
1391 hxt_td_util.get_hourly_rate (
1392 l_hours_rec.date_worked,
1393 l_hours_rec.time_period_id,
1394 l_hours_rec.assignment_id,
1395 l_hours_rec.hourly_rate
1396 );
1397 END IF;
1398 ELSIF l_hours_rec.hxt_premium_type = 'RATE'
1399 THEN
1400 IF l_hours_rec.hourly_rate IS NULL
1401 THEN
1402 l_hours_rec.hourly_rate :=
1403 l_hours_rec.hxt_premium_amount;
1404 END IF;
1405 END IF;
1406 ELSE
1407 --end SIR65
1408 --BEGIN BSE107 - OHM SPR200
1409 IF l_hours_rec.hourly_rate IS NULL
1410 THEN -- OHM205
1411 l_retcode :=
1412 hxt_td_util.get_hourly_rate (
1413 l_hours_rec.date_worked,
1414 l_hours_rec.time_period_id,
1415 l_hours_rec.assignment_id,
1416 l_hours_rec.hourly_rate
1417 );
1418 END IF; -- OHM205
1419 --END BSE107 - OHM SPR200
1420 END IF; --SIR65
1421
1422
1423 --BEGIN GLOBAL
1424 -- select pay_pdt_batch_lines_s.nextval
1425 -- SELECT pay_batch_lines_s.NEXTVAL
1426
1427 --END GLOBAL
1428 -- INTO l_nextval
1429 -- FROM DUAL;
1430
1431 l_location := 'Step 1D';
1432 hxt_util.DEBUG (' insert hours to paymix.'); -- debug only --HXT115
1433
1434 --BEGIN GLOBAL
1435 -- INSERT into pay_pdt_batch_lines
1436 -- (batch_id, line_id,
1437 -- assignment_number, adjustment_type_code,
1438 -- amount,
1439 -- apply_this_period,
1440 -- cost_allocation_keyflex_id,concatenated_segments,
1441 -- segment1,segment2,segment3,segment4,
1442 -- segment5,segment6,segment7,segment8,
1443 -- segment9,segment10,segment11,segment12,
1444 -- segment13,segment14,segment15,segment16,
1445 -- segment17,segment18,segment19,segment20,
1446 -- segment21,segment22,segment23,segment24,
1447 -- segment25,segment26,segment27,segment28,
1448 -- segment29,segment30,
1449 -- element_name, from_date,
1450 -- to_date, hourly_rate, inc_asc_balance,
1451 -- labor_dist_code,
1452 -- line_status, locality_worked, new_salary, pay_effective_date,
1453 -- pcnt_increase, rate_code, rate_multiple, rating_code,
1454 -- separate_check_flag, shift_type, state_worked,
1455 -- tax_separately_flag, vol_ded_proc_ovd,
1456 -- hours_worked)
1457 -- VALUES(
1458 -- p_batch_id, l_nextval,
1459 -- l_hours_rec.assignment_number, '',
1460 -- l_hours_rec.amount,
1461 -- '',
1462 -- l_hours_rec.ffv_cost_center_id,l_hours_rec.concatenated_segments,
1463 -- l_hours_rec.segment1,l_hours_rec.segment2,l_hours_rec.segment3,l_hours_rec.segment4,
1464 -- l_hours_rec.segment5,l_hours_rec.segment6,l_hours_rec.segment7,l_hours_rec.segment8,
1465 -- l_hours_rec.segment9,l_hours_rec.segment10,l_hours_rec.segment11,l_hours_rec.segment12,
1466 -- l_hours_rec.segment13,l_hours_rec.segment14,l_hours_rec.segment15,l_hours_rec.segment16,
1467 -- l_hours_rec.segment17,l_hours_rec.segment18,l_hours_rec.segment19,l_hours_rec.segment20,
1468 -- l_hours_rec.segment21,l_hours_rec.segment22,l_hours_rec.segment23,l_hours_rec.segment24,
1469 -- l_hours_rec.segment25,l_hours_rec.segment26,l_hours_rec.segment27,l_hours_rec.segment28,
1470 -- l_hours_rec.segment29,l_hours_rec.segment30,
1471 -- l_hours_rec.element_name, '',
1472 -- '', l_hours_rec.hourly_rate, '',
1473 -- /*TA36l_hours_rec.labor_dist_code*/ NULL,
1477 -- l_hours_rec.tax_separately_flag, '',
1474 -- '', l_hours_rec.locality_worked, '', '',
1475 -- '', l_hours_rec.rate_code, l_hours_rec.rate_multiple, '',
1476 -- l_hours_rec.separate_check_flag, '', '',
1478 -- l_hours_to_send
1479 -- );
1480 insert_pay_batch_lines (
1481 p_batch_id,
1482 l_batch_line_id,
1483 l_hours_rec.assignment_id,
1484 l_hours_rec.assignment_number,
1485 l_hours_rec.amount,
1486 l_hours_rec.ffv_cost_center_id,
1487 l_hours_rec.concatenated_segments,
1488 l_hours_rec.segment1,
1489 l_hours_rec.segment2,
1490 l_hours_rec.segment3,
1491 l_hours_rec.segment4,
1492 l_hours_rec.segment5,
1493 l_hours_rec.segment6,
1494 l_hours_rec.segment7,
1495 l_hours_rec.segment8,
1496 l_hours_rec.segment9,
1497 l_hours_rec.segment10,
1498 l_hours_rec.segment11,
1499 l_hours_rec.segment12,
1500 l_hours_rec.segment13,
1501 l_hours_rec.segment14,
1502 l_hours_rec.segment15,
1503 l_hours_rec.segment16,
1504 l_hours_rec.segment17,
1505 l_hours_rec.segment18,
1506 l_hours_rec.segment19,
1507 l_hours_rec.segment20,
1508 l_hours_rec.segment21,
1509 l_hours_rec.segment22,
1510 l_hours_rec.segment23,
1511 l_hours_rec.segment24,
1512 l_hours_rec.segment25,
1513 l_hours_rec.segment26,
1514 l_hours_rec.segment27,
1515 l_hours_rec.segment28,
1516 l_hours_rec.segment29,
1517 l_hours_rec.segment30,
1518 l_hours_rec.element_type_id,
1519 l_hours_rec.element_name,
1520 l_hours_rec.hourly_rate,
1521 l_hours_rec.locality_worked,
1522 l_hours_rec.rate_code,
1523 l_hours_rec.rate_multiple,
1524 l_hours_rec.separate_check_flag,
1525 l_hours_rec.tax_separately_flag,
1526 l_hours_to_send,
1527 l_hours_rec.date_worked,
1528 l_hours_rec.reason,
1529 l_batch_sequence,
1530 l_hours_rec.state_name,
1531 l_hours_rec.county_name,
1532 l_hours_rec.city_name,
1533 l_hours_rec.zip_code
1534 );
1535
1536 --END GLOBAL
1537 l_location := 'Step 1E';
1538
1539
1540 -- begin OHM180
1541 UPDATE hxt_det_hours_worked_f
1542 SET retro_pbl_line_id = l_batch_line_id
1543 WHERE ROWID = l_hours_rec.hrw_rowid;
1544
1545 -- end OHM180
1546
1547 l_batch_sequence := l_batch_sequence + 1;
1548
1549 END IF;
1550
1551 CLOSE prev_hours;
1552 END LOOP;
1553
1554
1555 /************************************************************/
1556 -- Step 2 - retro processing where hours=0, amount<>0
1557 /************************************************************/
1558 l_location := 'Step 2A';
1559
1560 l_batch_sequence := l_batch_sequence + 1;
1561
1562 FOR l_amount_rec IN current_amount (p_tim_id)
1563 LOOP
1564 hxt_util.DEBUG (
1565 'retro row is '
1566 || l_amount_rec.element_name
1567 || ' '
1568 || TO_CHAR (l_amount_rec.amount)
1569 || ' '
1570 ); -- debug only --HXT115
1571 l_amount_to_send := l_amount_rec.amount;
1572 OPEN prev_amount (
1573 p_tim_id,
1574 l_amount_rec.assignment_id, -- fk - assignment_number
1575 l_amount_rec.asm_effective_end_date,
1576 l_amount_rec.ffv_cost_center_id, -- fk - cost_center_code
1577 l_amount_rec.element_type_id, -- fk - element_name
1578 l_amount_rec.elt_effective_end_date,
1579 l_amount_rec.from_date,
1580 l_amount_rec.hourly_rate,
1581
1582 /*TA36 l_amount_rec.ffv_labor_account_id,*/ -- fk - labor_dist_code
1583 l_amount_rec.location_id, -- fk - locality_worked
1584 l_amount_rec.ffv_rate_code_id, -- fk - rate_code
1585 l_amount_rec.rate_multiple,
1586 l_amount_rec.separate_check_flag,
1587 l_amount_rec.tax_separately_flag, -- fcl_tax_rule_code
1588 l_amount_rec.TO_DATE,
1589 l_amount_rec.parent_id,
1590 l_amount_rec.state_name, --dd
1591 l_amount_rec.county_name,
1592 l_amount_rec.city_name,
1593 l_amount_rec.zip_code
1594
1595 );
1596 FETCH prev_amount INTO l_prev_amount_rec;
1597
1598 IF prev_amount%FOUND
1599 THEN
1600 hxt_util.DEBUG (
1601 'orig row is '
1602 || TO_CHAR (l_prev_amount_rec.amount)
1603 || ' '
1604 ); -- debug only --HXT115
1605 l_amount_to_send :=
1606 l_amount_rec.amount
1607 - l_prev_amount_rec.amount;
1608 l_location := 'Step 2B';
1609
1610
1614 UPDATE hxt_det_hours_worked_f
1611 -- begin OHM199
1612 IF l_amount_to_send = 0
1613 THEN
1615 SET pay_status = 'D',
1616 last_update_date = SYSDATE
1617 WHERE ROWID = l_prev_amount_rec.hrw_rowid
1618 -- ADDED BY MV: IF THERE IS ONLY ONE ROW, PREV and CURR records
1619 -- are the same; we should not update such records.
1620 AND l_prev_amount_rec.hrw_rowid <> l_amount_rec.hrw_rowid;
1621 ELSE
1622 UPDATE hxt_det_hours_worked_f
1623 SET pay_status = 'A',
1624 last_update_date = SYSDATE
1625 WHERE ROWID = l_prev_amount_rec.hrw_rowid;
1626 END IF;
1627
1628 -- end OHM199
1629 END IF;
1630
1631 hxt_util.DEBUG ( ' amount to send - '
1632 || TO_CHAR (l_amount_to_send)); -- debug only --HXT115
1633 l_location := 'Step 2C';
1634
1635 IF (l_amount_to_send <> 0)
1636 THEN
1637
1638 /* CODE ADDED PER BSE107 */
1639 --BSE130 l_retcode := HXT_TD_UTIL.get_hourly_rate(l_amount_rec.date_worked,
1640 --BSE130 l_amount_rec.time_period_id,
1641 --BSE130 l_amount_rec.assignment_id,
1642 --BSE130 l_amount_rec.hourly_rate);
1643
1644 /* END BSE107 */
1645 --BEGIN GLOBAL
1646 -- select pay_pdt_batch_lines_s.nextval
1647 -- SELECT pay_batch_lines_s.NEXTVAL
1648
1649 --END GLOBAL
1650 -- INTO l_nextval
1651 -- FROM DUAL;
1652
1653 l_location := 'Step 2D';
1654
1655 --BEGIN GLOBAL
1656 -- INSERT into pay_pdt_batch_lines
1657 -- (batch_id, line_id,
1658 -- assignment_number, adjustment_type_code,
1659 -- amount,
1660 -- apply_this_period,
1661 -- cost_allocation_keyflex_id,concatenated_segments,
1662 -- segment1,segment2,segment3,segment4,
1663 -- segment5,segment6,segment7,segment8,
1664 -- segment9,segment10,segment11,segment12,
1665 -- segment13,segment14,segment15,segment16,
1666 -- segment17,segment18,segment19,segment20,
1667 -- segment21,segment22,segment23,segment24,
1668 -- segment25,segment26,segment27,segment28,
1669 -- segment29,segment30,
1670 -- element_name, from_date,
1671 -- to_date, hourly_rate, inc_asc_balance,
1672 -- labor_dist_code,
1673 -- line_status, locality_worked, new_salary, pay_effective_date,
1674 -- pcnt_increase, rate_code, rate_multiple, rating_code,
1675 -- separate_check_flag, shift_type, state_worked,
1676 -- tax_separately_flag, vol_ded_proc_ovd,
1677 -- hours_worked)
1678 -- VALUES(
1679 -- p_batch_id, l_nextval,
1680 -- l_amount_rec.assignment_number, '',
1681 -- l_amount_to_send,
1682 -- '',
1683 -- l_amount_rec.ffv_cost_center_id,l_amount_rec.concatenated_segments,
1684 -- l_amount_rec.segment1,l_amount_rec.segment2,l_amount_rec.segment3,l_amount_rec.segment4,
1685 -- l_amount_rec.segment5,l_amount_rec.segment6,l_amount_rec.segment7,l_amount_rec.segment8,
1686 -- l_amount_rec.segment9,l_amount_rec.segment10,l_amount_rec.segment11,l_amount_rec.segment12,
1687 -- l_amount_rec.segment13,l_amount_rec.segment14,l_amount_rec.segment15,l_amount_rec.segment16,
1688 -- l_amount_rec.segment17,l_amount_rec.segment18,l_amount_rec.segment19,l_amount_rec.segment20,
1689 -- l_amount_rec.segment21,l_amount_rec.segment22,l_amount_rec.segment23,l_amount_rec.segment24,
1690 -- l_amount_rec.segment25,l_amount_rec.segment26,l_amount_rec.segment27,l_amount_rec.segment28,
1691 -- l_amount_rec.segment29,l_amount_rec.segment30,
1692 -- l_amount_rec.element_name, '',
1693 -- '', l_amount_rec.hourly_rate, '',
1694 --/*TA36 l_amount_rec.labor_dist_code*/NULL,
1695 -- '', l_amount_rec.locality_worked, '', '',
1696 -- '', l_amount_rec.rate_code, l_amount_rec.rate_multiple, '',
1697 -- l_amount_rec.separate_check_flag, '', '',
1698 -- l_amount_rec.tax_separately_flag, '',
1699 -- l_amount_rec.hours_worked
1700 -- );
1701 insert_pay_batch_lines (
1702 p_batch_id,
1703 l_batch_line_id,
1704 l_amount_rec.assignment_id,
1705 l_amount_rec.assignment_number,
1706 l_amount_to_send,
1707 l_amount_rec.ffv_cost_center_id,
1708 l_amount_rec.concatenated_segments,
1709 l_amount_rec.segment1,
1710 l_amount_rec.segment2,
1711 l_amount_rec.segment3,
1712 l_amount_rec.segment4,
1713 l_amount_rec.segment5,
1714 l_amount_rec.segment6,
1715 l_amount_rec.segment7,
1716 l_amount_rec.segment8,
1717 l_amount_rec.segment9,
1718 l_amount_rec.segment10,
1719 l_amount_rec.segment11,
1720 l_amount_rec.segment12,
1721 l_amount_rec.segment13,
1722 l_amount_rec.segment14,
1723 l_amount_rec.segment15,
1724 l_amount_rec.segment16,
1725 l_amount_rec.segment17,
1726 l_amount_rec.segment18,
1730 l_amount_rec.segment22,
1727 l_amount_rec.segment19,
1728 l_amount_rec.segment20,
1729 l_amount_rec.segment21,
1731 l_amount_rec.segment23,
1732 l_amount_rec.segment24,
1733 l_amount_rec.segment25,
1734 l_amount_rec.segment26,
1735 l_amount_rec.segment27,
1736 l_amount_rec.segment28,
1737 l_amount_rec.segment29,
1738 l_amount_rec.segment30,
1739 l_amount_rec.element_type_id,
1740 l_amount_rec.element_name,
1741 l_amount_rec.hourly_rate,
1742 l_amount_rec.locality_worked,
1743 l_amount_rec.rate_code,
1744 l_amount_rec.rate_multiple,
1745 l_amount_rec.separate_check_flag,
1746 l_amount_rec.tax_separately_flag,
1747 l_amount_rec.hours_worked,
1748 l_amount_rec.date_worked,
1749 l_amount_rec.reason,
1750 l_batch_sequence,
1751 l_amount_rec.state_name,
1752 l_amount_rec.county_name,
1753 l_amount_rec.city_name,
1754 l_amount_rec.zip_code
1755 );
1756
1757 --END GLOBAL
1758 l_location := 'Step 2E';
1759 hxt_util.DEBUG (' insert amount to paymix.'); -- debug only --HXT115
1760
1761
1762 -- begin OHM180
1763 UPDATE hxt_det_hours_worked_f
1764 SET retro_pbl_line_id = l_batch_line_id
1765 WHERE ROWID = l_amount_rec.hrw_rowid;
1766
1767 -- end OHM180
1768
1769 l_batch_sequence := l_batch_sequence + 1;
1770
1771 END IF;
1772
1773 CLOSE prev_amount;
1774 END LOOP;
1775
1776
1777 /************************************************************/
1778 -- Step 3 - loop thru retro rows, mark matching rows 'A' or 'D'
1779 /************************************************************/
1780 --OHM199 commented out because now we mark in loops above.
1781 --OHM199mark_prev_hours_rows (p_tim_id);
1782 --OHM199mark_prev_amount_rows (p_tim_id);
1783
1784
1785 /************************************************************/
1786 -- Step 4 - send whatever is left over as backout transactions
1787 /************************************************************/
1788 l_location := 'Step 4A';
1789 back_out_leftover_hours (p_batch_id, p_tim_id);
1790 l_location := 'Step 4B';
1791 back_out_leftover_amount (p_batch_id, p_tim_id);
1792
1793 /************************************************************/
1794 -- Step 5 - mark retro rows on timecard complete
1795 /************************************************************/
1796 l_location := 'Step 5A';
1797 mark_retro_rows_complete (p_tim_id);
1798 l_location := 'Step 5B';
1799 p_sum_retcode := 0;
1800 p_err_buf := '';
1801 RETURN;
1802 EXCEPTION
1803 WHEN g_lookup_not_found
1804 THEN --SIR517 PWM 18FEB00
1805 hxt_util.DEBUG (
1806 'Oops...g_lookup_not_found in procedure retro_sum_to_mix'
1807 ); --HXT115
1808 p_err_buf := SUBSTR (fnd_message.get, 1, 65); --HXT111
1809 hxt_batch_process.insert_pay_batch_errors (
1810 p_batch_id,
1811 'VE', -- RETROPAY
1812 '',
1813 l_return
1814 );
1815 RETURN;
1816 WHEN g_error_ins_batch_lines
1817 THEN --SIR517 PWM 18FEB00
1818 hxt_util.DEBUG ('Error attempting to insert paymix information'); -- debug only --HXT115
1819 fnd_message.set_name ('HXT', 'HXT_39354_ERR_INS_PAYMX_INFO'); --HXT111
1820 fnd_message.set_token ('SQLERR', SQLERRM); --HXT111
1821 p_err_buf := SUBSTR (fnd_message.get, 1, 65); --HXT111
1822 hxt_batch_process.insert_pay_batch_errors (
1823 p_batch_id,
1824 'VE', -- RETROPAY
1825 '',
1826 l_return
1827 );
1828 hxt_util.DEBUG (' back from calling insert_pay_batch_errors'); -- debug only --HXT115
1829 RETURN;
1830 WHEN OTHERS
1831 THEN
1832 hxt_util.DEBUG (
1833 ' exception received at '
1834 || l_location
1835 || '. '
1836 || SQLERRM
1837 ); -- debug only --HXT115
1838 p_sum_retcode := 3;
1839
1840 --HXT111 p_err_buf := substr(' exception received at '||l_location||'. '||sqlerrm,1,65);
1841 fnd_message.set_name ('HXT', 'HXT_39453_EXCPT_RCVD_AT'); --HXT111
1842 fnd_message.set_token ('LOCATION', l_location); --HXT111
1843 fnd_message.set_token ('SQLERR', SQLERRM); --HXT111
1844 p_err_buf := SUBSTR (fnd_message.get, 1, 65); --HXT111
1845 hxt_batch_process.insert_pay_batch_errors (
1846 p_batch_id,
1847 'VE', -- RETROPAY
1848
1849 --HXT111 'Error attempting to insert paymix information: (' || sqlerrm || ')',
1850 '', --HXT111
1851 l_return
1852 );
1853 hxt_util.DEBUG (' back from calling insert_pay_batch_errors'); -- debug only --HXT115
1854 RETURN;
1855 END retro_sum_to_mix;
1856
1857 PROCEDURE mark_prev_hours_rows (p_tim_id IN NUMBER)
1858 IS
1859 BEGIN
1860 UPDATE hxt_det_hours_worked_f
1861 SET pay_status = 'D',
1865 FROM hxt_det_hours_worked_f hrw
1862 last_update_date = SYSDATE
1863 WHERE ROWID IN
1864 (SELECT hrw.ROWID
1866 WHERE hrw.tim_id = p_tim_id
1867 AND hrw.pay_status = 'C'
1868 AND hrw.amount IS NULL
1869 AND hrw.parent_id > 0
1870 AND EXISTS ( SELECT 'X'
1871 FROM hxt_det_hours_worked_x retro
1872 WHERE hrw.parent_id = retro.parent_id
1873 AND retro.pay_status = 'R'
1874 AND hrw.hours = retro.hours
1875 AND hrw.amount IS NULL
1876 and NVL(hrw.state_name,'ZZZZZZZZZZ')=
1877 NVL(retro.state_name,'ZZZZZZZZZZ')
1878 and NVL(hrw.county_name,'ZZZZZZZZZZ')=
1879 NVL(retro.county_name,'ZZZZZZZZZZ')
1880 and NVL(hrw.city_name,'ZZZZZZZZZZ')=
1881 NVL(retro.city_name,'ZZZZZZZZZZ')
1882 and NVL(hrw.zip_code,'ZZZZZZZZZZ')=
1883 NVL(retro.zip_code,'ZZZZZZZZZZ')
1884 AND hrw.assignment_id =
1885 retro.assignment_id
1886 AND NVL (
1887 hrw.ffv_cost_center_id,
1888 999999999999999
1889 ) = NVL (
1890 retro.ffv_cost_center_id,
1891 999999999999999
1892 )
1893 AND hrw.element_type_id =
1894 retro.element_type_id
1895 AND NVL (
1896 hrw.hourly_rate,
1897 999999999999999
1898 ) = NVL (
1899 retro.hourly_rate,
1900 999999999999999
1901 )
1902
1903 AND NVL (
1904 hrw.location_id,
1905 999999999999999
1906 ) = NVL (
1907 retro.location_id,
1908 999999999999999
1909 )
1910 AND NVL (
1911 hrw.ffv_rate_code_id,
1912 999999999999999
1913 ) = NVL (
1914 retro.ffv_rate_code_id,
1915 999999999999999
1916 )
1917 AND NVL (
1918 hrw.rate_multiple,
1919 999999999999999
1920 ) = NVL (
1921 retro.rate_multiple,
1922 999999999999999
1923 )
1924 AND NVL (
1925 hrw.separate_check_flag,
1926 'ZZZZZZZZZZ'
1927 ) = NVL (
1928 retro.separate_check_flag,
1929 'ZZZZZZZZZZ'
1930 )
1931 AND NVL (
1932 hrw.fcl_tax_rule_code,
1933 'ZZZZZZZZZZ'
1934 ) = NVL (
1935 retro.fcl_tax_rule_code,
1936 'ZZZZZZZZZZ'
1937 )));
1938
1939 UPDATE hxt_det_hours_worked_f
1940 SET pay_status = 'A',
1941 last_update_date = SYSDATE
1942 WHERE ROWID IN
1943 (SELECT hrw.ROWID
1944 FROM hxt_det_hours_worked_f hrw
1945 WHERE hrw.tim_id = p_tim_id
1946 AND hrw.pay_status = 'C'
1947 AND hrw.amount IS NULL
1948 AND hrw.parent_id > 0
1949 AND EXISTS ( SELECT 'X'
1950 FROM hxt_det_hours_worked_x retro
1951 WHERE hrw.parent_id = retro.parent_id
1952 AND retro.pay_status = 'R'
1953 AND hrw.hours <> retro.hours
1954 AND hrw.amount IS NULL
1955 AND hrw.assignment_id =
1956 retro.assignment_id
1957 and NVL(hrw.state_name,'ZZZZZZZZZZ')=
1958 NVL(retro.state_name,'ZZZZZZZZZZ')
1962 NVL(retro.city_name,'ZZZZZZZZZZ')
1959 and NVL(hrw.county_name,'ZZZZZZZZZZ')=
1960 NVL(retro.county_name,'ZZZZZZZZZZ')
1961 and NVL(hrw.city_name,'ZZZZZZZZZZ')=
1963 and NVL(hrw.zip_code,'ZZZZZZZZZZ')=
1964 NVL(retro.zip_code,'ZZZZZZZZZZ')
1965 AND NVL (
1966 hrw.ffv_cost_center_id,
1967 999999999999999
1968 ) = NVL (
1969 retro.ffv_cost_center_id,
1970 999999999999999
1971 )
1972 AND hrw.element_type_id =
1973 retro.element_type_id
1974 AND NVL (
1975 hrw.hourly_rate,
1976 999999999999999
1977 ) = NVL (
1978 retro.hourly_rate,
1979 999999999999999
1980 )
1981
1982 AND NVL (
1983 hrw.location_id,
1984 999999999999999
1985 ) = NVL (
1986 retro.location_id,
1987 999999999999999
1988 )
1989 AND NVL (
1990 hrw.ffv_rate_code_id,
1991 999999999999999
1992 ) = NVL (
1993 retro.ffv_rate_code_id,
1994 999999999999999
1995 )
1996 AND NVL (
1997 hrw.rate_multiple,
1998 999999999999999
1999 ) = NVL (
2000 retro.rate_multiple,
2001 999999999999999
2002 )
2003 AND NVL (
2004 hrw.separate_check_flag,
2005 'ZZZZZZZZZZ'
2006 ) = NVL (
2007 retro.separate_check_flag,
2008 'ZZZZZZZZZZ'
2009 )
2010 AND NVL (
2011 hrw.fcl_tax_rule_code,
2012 'ZZZZZZZZZZ'
2013 ) = NVL (
2014 retro.fcl_tax_rule_code,
2015 'ZZZZZZZZZZ'
2016 )));
2017 END mark_prev_hours_rows;
2018
2019 PROCEDURE mark_prev_amount_rows (p_tim_id IN NUMBER)
2020 IS
2021 BEGIN
2022 UPDATE hxt_det_hours_worked_f
2023 SET pay_status = 'D',
2024 last_update_date = SYSDATE
2025 WHERE ROWID IN
2026 (SELECT hrw.ROWID
2027 FROM hxt_det_hours_worked_f hrw
2028 WHERE hrw.tim_id = p_tim_id
2029 AND hrw.pay_status = 'C'
2030 AND hrw.amount IS NOT NULL
2031 AND hrw.parent_id > 0
2032 AND EXISTS ( SELECT 'X'
2033 FROM hxt_det_hours_worked_x retro
2034 WHERE hrw.parent_id = retro.parent_id
2035 AND retro.pay_status = 'R'
2036 AND hrw.amount = retro.amount
2037 AND hrw.amount IS NOT NULL
2038 AND hrw.assignment_id =
2039 retro.assignment_id
2040 and NVL(hrw.state_name,'ZZZZZZZZZZ')=
2041 NVL(retro.state_name,'ZZZZZZZZZZ')
2042 and NVL(hrw.county_name,'ZZZZZZZZZZ')=
2043 NVL(retro.county_name,'ZZZZZZZZZZ')
2044 and NVL(hrw.city_name,'ZZZZZZZZZZ')=
2045 NVL(retro.city_name,'ZZZZZZZZZZ')
2046 and NVL(hrw.zip_code,'ZZZZZZZZZZ')=
2047 NVL(retro.zip_code,'ZZZZZZZZZZ')
2048
2049 AND NVL (
2050 hrw.ffv_cost_center_id,
2051 999999999999999
2052 ) = NVL (
2053 retro.ffv_cost_center_id,
2054 999999999999999
2055 )
2056 AND hrw.element_type_id =
2060 999999999999999
2057 retro.element_type_id
2058 AND NVL (
2059 hrw.hourly_rate,
2061 ) = NVL (
2062 retro.hourly_rate,
2063 999999999999999
2064 )
2065
2066 AND NVL (
2067 hrw.location_id,
2068 999999999999999
2069 ) = NVL (
2070 retro.location_id,
2071 999999999999999
2072 )
2073 AND NVL (
2074 hrw.ffv_rate_code_id,
2075 999999999999999
2076 ) = NVL (
2077 retro.ffv_rate_code_id,
2078 999999999999999
2079 )
2080 AND NVL (
2081 hrw.rate_multiple,
2082 999999999999999
2083 ) = NVL (
2084 retro.rate_multiple,
2085 999999999999999
2086 )
2087 AND NVL (
2088 hrw.separate_check_flag,
2089 'ZZZZZZZZZZ'
2090 ) = NVL (
2091 retro.separate_check_flag,
2092 'ZZZZZZZZZZ'
2093 )
2094 AND NVL (
2095 hrw.fcl_tax_rule_code,
2096 'ZZZZZZZZZZ'
2097 ) = NVL (
2098 retro.fcl_tax_rule_code,
2099 'ZZZZZZZZZZ'
2100 )));
2101
2102 UPDATE hxt_det_hours_worked_f
2103 SET pay_status = 'A',
2104 last_update_date = SYSDATE
2105 WHERE ROWID IN
2106 (SELECT hrw.ROWID
2107 FROM hxt_det_hours_worked_f hrw
2108 WHERE hrw.tim_id = p_tim_id
2109 AND hrw.pay_status = 'C'
2110 AND hrw.amount IS NOT NULL
2111 AND hrw.parent_id > 0
2112 AND EXISTS ( SELECT 'X'
2113 FROM hxt_det_hours_worked_x retro
2114 WHERE hrw.parent_id = retro.parent_id
2115 AND retro.pay_status = 'R'
2116 AND hrw.amount <> retro.amount
2117 AND hrw.amount IS NOT NULL
2118 AND hrw.assignment_id =
2119 retro.assignment_id
2120 and NVL(hrw.state_name,'ZZZZZZZZZZ')=
2121 NVL(retro.state_name,'ZZZZZZZZZZ')
2122 and NVL(hrw.county_name,'ZZZZZZZZZZ')=
2123 NVL(retro.county_name,'ZZZZZZZZZZ')
2124 and NVL(hrw.city_name,'ZZZZZZZZZZ')=
2125 NVL(retro.city_name,'ZZZZZZZZZZ')
2126 and NVL(hrw.zip_code,'ZZZZZZZZZZ')=
2127 NVL(retro.zip_code,'ZZZZZZZZZZ')
2128
2129 AND NVL (
2130 hrw.ffv_cost_center_id,
2131 999999999999999
2132 ) = NVL (
2133 retro.ffv_cost_center_id,
2134 999999999999999
2135 )
2136 AND hrw.element_type_id =
2137 retro.element_type_id
2138 AND NVL (
2139 hrw.hourly_rate,
2140 999999999999999
2141 ) = NVL (
2142 retro.hourly_rate,
2143 999999999999999
2144 )
2145
2146 AND NVL (
2147 hrw.location_id,
2148 999999999999999
2149 ) = NVL (
2150 retro.location_id,
2151 999999999999999
2152 )
2153 AND NVL (
2157 retro.ffv_rate_code_id,
2154 hrw.ffv_rate_code_id,
2155 999999999999999
2156 ) = NVL (
2158 999999999999999
2159 )
2160 AND NVL (
2161 hrw.rate_multiple,
2162 999999999999999
2163 ) = NVL (
2164 retro.rate_multiple,
2165 999999999999999
2166 )
2167 AND NVL (
2168 hrw.separate_check_flag,
2169 'ZZZZZZZZZZ'
2170 ) = NVL (
2171 retro.separate_check_flag,
2172 'ZZZZZZZZZZ'
2173 )
2174 AND NVL (
2175 hrw.fcl_tax_rule_code,
2176 'ZZZZZZZZZZ'
2177 ) = NVL (
2178 retro.fcl_tax_rule_code,
2179 'ZZZZZZZZZZ'
2180 )));
2181 END mark_prev_amount_rows;
2182
2183 PROCEDURE mark_retro_rows_complete (p_tim_id NUMBER)
2184 IS
2185 BEGIN
2186 UPDATE hxt_det_hours_worked_f
2187 SET pay_status = 'C',
2188 last_update_date = SYSDATE
2189 WHERE ROWID IN (SELECT hrw.ROWID
2190 FROM hxt_det_hours_worked_x hrw
2191 WHERE hrw.parent_id > 0
2192 AND hrw.pay_status = 'R'
2193 AND hrw.tim_id = p_tim_id);
2194 END mark_retro_rows_complete;
2195
2196 PROCEDURE back_out_leftover_hours (p_batch_id NUMBER, p_tim_id NUMBER)
2197 IS
2198 CURSOR leftover_hours (p_tim_id NUMBER)
2199 IS
2200 SELECT hrw.ROWID hrw_rowid,
2201 NVL (hrw.retro_pbl_line_id, hrw.pbl_line_id) line_id,
2202 /* TA36 01/09/98 */
2203 asm.assignment_number, elt.element_name, --FORMS60
2204 eltv.hxt_premium_type, --SIR65
2205 eltv.hxt_premium_amount, --SIR65
2206 eltv.hxt_earning_category, --SIR65
2207 DECODE (
2208 SIGN (
2209 DECODE (
2210 SIGN ( ptp.start_date
2211 - asm.effective_start_date),
2212 1, ptp.start_date,
2213 asm.effective_start_date
2214 )
2215 - elt.effective_start_date
2216 ),
2217 1, DECODE (
2218 SIGN ( ptp.start_date
2219 - asm.effective_start_date),
2220 1, ptp.start_date,
2221 asm.effective_start_date
2222 ),
2223 elt.effective_start_date
2224 )
2225 from_date,
2226 DECODE (
2227 SIGN (
2228 DECODE (
2229 SIGN ( ptp.end_date
2230 - asm.effective_end_date),
2231 -1, ptp.end_date,
2232 asm.effective_end_date
2233 )
2234 - elt.effective_end_date
2235 ),
2236 -1, DECODE (
2237 SIGN ( ptp.end_date
2238 - asm.effective_end_date),
2239 -1, ptp.end_date,
2240 asm.effective_end_date
2241 ),
2242 elt.effective_end_date
2243 ) TO_DATE,
2244 rate_multiple, hrw.hourly_rate, hrw.ffv_cost_center_id,
2245 /* fk - cost_center_code */
2246 pcak.concatenated_segments, pcak.segment1, pcak.segment2,
2247 pcak.segment3, pcak.segment4, pcak.segment5, pcak.segment6,
2248 pcak.segment7, pcak.segment8, pcak.segment9, pcak.segment10,
2249 pcak.segment11, pcak.segment12, pcak.segment13,
2250 pcak.segment14, pcak.segment15, pcak.segment16,
2251 pcak.segment17, pcak.segment18, pcak.segment19,
2252 pcak.segment20, pcak.segment21, pcak.segment22,
2253 pcak.segment23, pcak.segment24, pcak.segment25,
2254 pcak.segment26, pcak.segment27, pcak.segment28,
2255 pcak.segment29, pcak.segment30,
2256
2257 /*TA36 ffvl.flex_value labor_dist_code,*/
2258 loct.location_code locality_worked, --FORMS60
2259 ffvr.flex_value rate_code,
2260 hrw.separate_check_flag,
2261 hrw.fcl_tax_rule_code tax_separately_flag, hrw.amount,
2265 /* BSE107 */
2262 hrw.hours hours_worked, hrw.element_type_id, --GLOBAL
2263 hcl.meaning reason, --GLOBAL
2264 hrw.date_worked, /* BSE107 */ ptp.time_period_id,
2266 hrw.assignment_id, /* BSE107 */
2267 hrw.state_name,
2268 hrw.county_name,
2269 hrw.city_name,
2270 hrw.zip_code
2271 FROM hxt_timecards_x tim, /* SIR416 PWM 21MAR00 */
2272 per_time_periods ptp,
2273 hxt_det_hours_worked_f hrw,
2274 hr_lookups hcl, --GLOBAL
2275 per_assignments_f asm,
2276 pay_element_types_f elt,
2277 hxt_pay_element_types_f_ddf_v eltv, --SIR65
2278 pay_cost_allocation_keyflex pcak,
2279
2280 /*TA36fnd_flex_values ffvl, */
2281 hr_locations_all_tl loct, --FORMS60
2282 hr_locations_no_join loc, --FORMS60
2283 fnd_flex_values ffvr
2284 WHERE hrw.ffv_rate_code_id = ffvr.flex_value_id(+)
2285 AND hrw.location_id = loc.location_id(+)
2286
2287 --BEGIN FORMS60
2288 AND loc.location_id = loct.location_id(+)
2289 AND DECODE (loct.location_id, NULL, '1', loct.LANGUAGE) =
2290 DECODE (loct.location_id, NULL, '1', USERENV ('LANG'))
2291
2292 --END FORMS60
2293 /*TA36AND hrw.ffv_labor_account_id = ffvl.flex_value_id(+)*/
2294 AND hrw.ffv_cost_center_id = pcak.cost_allocation_keyflex_id(+)
2295 AND hrw.date_worked BETWEEN elt.effective_start_date
2296 AND elt.effective_end_date
2297 AND hrw.element_type_id = elt.element_type_id
2298
2299 /*GLOBAL AND elt.rowid=eltv.row_id --SIR65 */
2300 AND elt.element_type_id = eltv.element_type_id /* GLOBAL */
2301 AND hrw.date_worked BETWEEN eltv.effective_start_date /* GLOBAL */
2302 AND eltv.effective_end_date /* GLOBAL */
2303 AND hrw.date_worked BETWEEN asm.effective_start_date
2304 AND asm.effective_end_date
2305 AND hrw.assignment_id = asm.assignment_id
2306 AND hrw.amount IS NULL
2307
2308 /* GLOBAL AND hrw.parent_id > 0 */
2309 AND hrw.tim_id = tim.id
2310 AND tim.id = p_tim_id
2311 AND tim.time_period_id = ptp.time_period_id
2312
2313 --BEGIN GLOBAL
2314 AND hrw.date_worked BETWEEN NVL (
2315 hcl.start_date_active(+),
2316 hrw.date_worked
2317 )
2318 AND NVL (
2319 hcl.end_date_active(+),
2320 hrw.date_worked
2321 )
2322 AND hcl.lookup_type(+) = 'ELE_ENTRY_REASON'
2323 AND hcl.lookup_code(+) = hrw.fcl_earn_reason_code
2324
2325 --END GLOBAL
2326 AND hrw.pay_status = 'C'
2327 AND hrw.effective_end_date < hr_general.end_of_time; --SIR149 --FORMS60
2328
2329
2330 --BSE128 l_leftover leftover_hours %ROWTYPE;
2331 -- l_nextval NUMBER (15);
2332 l_batch_line_id NUMBER (15);
2333 l_retcode NUMBER; /* BSE107 */
2334
2335 --BEGIN GLOBAL
2336 -- l_hourly_rate pay_pdt_batch_lines.hourly_rate%TYPE := NULL; --SIR65
2337 l_hourly_rate hxt_det_hours_worked_f.hourly_rate%TYPE := NULL;
2338
2339
2340 --CURSOR rate_paid_cur(c_line_id NUMBER) IS --SIR65
2341 --SELECT pbl.hourly_rate --SIR65
2342 -- FROM pay_pdt_batch_lines pbl --SIR65
2343 -- WHERE pbl.line_id=c_line_id; --SIR65
2344 CURSOR rate_paid_cur (c_line_id NUMBER)
2345 IS
2346 SELECT pbl.value_1, pbl.value_2, pbl.value_3, pbl.value_4,
2347 pbl.value_5, pbl.value_6, pbl.value_7, pbl.value_8,
2348 pbl.value_9, pbl.value_10, pbl.value_11, pbl.value_12,
2349 pbl.value_13, pbl.value_14, pbl.value_15
2350 FROM pay_batch_lines pbl
2351 WHERE pbl.batch_line_id = c_line_id;
2352
2353 TYPE input_value_record IS RECORD (
2354 SEQUENCE pay_input_values_f.input_value_id%TYPE,
2355 NAME pay_input_values_f_tl.NAME%TYPE, --FORMS60
2356 lookup pay_input_values_f.lookup_type%TYPE);
2357
2358 TYPE input_values_table IS TABLE OF input_value_record
2359 INDEX BY BINARY_INTEGER;
2360
2361 hxt_value input_values_table;
2362
2363 TYPE pbl_values_table IS TABLE OF pay_batch_lines.value_1%TYPE
2364 INDEX BY BINARY_INTEGER;
2365
2366 pbl_value pbl_values_table;
2367
2368 --END GLOBAL
2369 l_value_meaning hr_lookups.meaning%TYPE;
2370
2371 l_batch_sequence PAY_BATCH_LINES.BATCH_SEQUENCE%TYPE;
2372
2373 BEGIN
2374
2375 l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(p_batch_id);
2376
2377 FOR l_leftover IN leftover_hours (p_tim_id)
2378 LOOP
2379 --begin SIR65
2380 -- Check PayMIX first for the Original rate paid,
2384 --BEGIN GLOBAL
2381 -- if one exists use it.
2382 -- Added to always back out the original rate sent.
2383
2385 -- Initialize tables
2386 FOR i IN 1 .. 15
2387 LOOP
2388 hxt_value (i).SEQUENCE := NULL;
2389 hxt_value (i).NAME := NULL;
2390 hxt_value (i).lookup := NULL;
2391 pbl_value (i) := NULL;
2392 END LOOP;
2393
2394
2395 -- OPEN rate_paid_cur(l_leftover.line_id);
2396 -- FETCH rate_paid_cur INTO l_hourly_rate;
2397 -- CLOSE rate_paid_cur;
2398 OPEN rate_paid_cur (l_leftover.line_id);
2399 FETCH rate_paid_cur INTO pbl_value (1),
2400 pbl_value (2),
2401 pbl_value (3),
2402 pbl_value (4),
2403 pbl_value (5),
2404 pbl_value (6),
2405 pbl_value (7),
2406 pbl_value (8),
2407 pbl_value (9),
2408 pbl_value (10),
2409 pbl_value (11),
2410 pbl_value (12),
2411 pbl_value (13),
2412 pbl_value (14),
2413 pbl_value (15);
2414 CLOSE rate_paid_cur;
2415 -- Get input values details for this element
2416 pay_paywsqee_pkg.get_input_value_details (
2417 l_leftover.element_type_id,
2418 l_leftover.date_worked,
2419 hxt_value (1).SEQUENCE,
2420 hxt_value (2).SEQUENCE,
2421 hxt_value (3).SEQUENCE,
2422 hxt_value (4).SEQUENCE,
2423 hxt_value (5).SEQUENCE,
2424 hxt_value (6).SEQUENCE,
2425 hxt_value (7).SEQUENCE,
2426 hxt_value (8).SEQUENCE,
2427 hxt_value (9).SEQUENCE,
2428 hxt_value (10).SEQUENCE,
2429 hxt_value (11).SEQUENCE,
2430 hxt_value (12).SEQUENCE,
2431 hxt_value (13).SEQUENCE,
2432 hxt_value (14).SEQUENCE,
2433 hxt_value (15).SEQUENCE,
2434 hxt_value (1).NAME,
2435 hxt_value (2).NAME,
2436 hxt_value (3).NAME,
2437 hxt_value (4).NAME,
2438 hxt_value (5).NAME,
2439 hxt_value (6).NAME,
2440 hxt_value (7).NAME,
2441 hxt_value (8).NAME,
2442 hxt_value (9).NAME,
2443 hxt_value (10).NAME,
2444 hxt_value (11).NAME,
2445 hxt_value (12).NAME,
2446 hxt_value (13).NAME,
2447 hxt_value (14).NAME,
2448 hxt_value (15).NAME,
2449 hxt_value (1).lookup,
2450 hxt_value (2).lookup,
2451 hxt_value (3).lookup,
2452 hxt_value (4).lookup,
2453 hxt_value (5).lookup,
2454 hxt_value (6).lookup,
2455 hxt_value (7).lookup,
2456 hxt_value (8).lookup,
2457 hxt_value (9).lookup,
2458 hxt_value (10).lookup,
2459 hxt_value (11).lookup,
2460 hxt_value (12).lookup,
2461 hxt_value (13).lookup,
2462 hxt_value (14).lookup,
2463 hxt_value (15).lookup
2464 );
2465
2466
2467 --
2468 -- In order to get the input-value logic work in diiferent legislations we need
2469 -- to create (SEED) new lookups for 'Hours', 'Hourly Rate', 'Rate Multiple',
2470 -- and 'Rate Code' with lookup_type of 'NAME_TRANSLATION' and lookup_code of
2471 -- 'HOURS', 'HOURLY_RATE', 'RATE_MULTIPLE' and 'RATE_CODE' respectively.
2472 -- Then the customers in different countries need to create the above input
2473 -- values with the name which is directly translated from the above names for
2474 -- OTM elements.
2475 --
2476 -- For example: In French the user must create an input value for 'Hours' to
2477 -- be 'Heures' and then to determine which input value 'Heures' is associated
2478 -- with we look at the hr_lookups and if we find an entry with lookup_type =
2479 -- 'NAME_TRANSLATIONS' and lookup_code = 'HOURS' and Meaning to be 'Heures'
2480 -- then we know that this input vale woul map to 'Hours'.
2481 --
2482 -- What need to be noted that it is the customer's responsibilty to create
2483 -- input values which are the direct translation of 'Hours','Hourly Rate',
2484 -- 'Pay Value' , 'Rate Multiple' and 'Rate Code'
2485 --
2486 FOR i IN 1 .. 15
2487 LOOP
2488
2489 -- We need to get the lookup_code for the input_value names before processing
2490 -- the further logic on the screen value for the input values.
2491 --
2492 l_value_meaning :=
2493 get_lookup_code (hxt_value (i).NAME, l_leftover.date_worked);
2494
2495 --IF hxt_value(i).name = 'Rate' THEN
2496 IF l_value_meaning = 'HOURLY_RATE'
2497 THEN
2498 l_hourly_rate := pbl_value (i);
2499 END IF;
2500 END LOOP;
2501
2502
2503 --END GLOBAL
2504
2505 IF ( l_hourly_rate IS NULL
2506 OR l_leftover.hxt_premium_type = 'FACTOR'
2507 )
2508 THEN
2512 THEN
2509 IF l_leftover.hxt_earning_category NOT IN ('REG', 'OVT', 'ABS')
2510 THEN
2511 IF l_leftover.hxt_premium_type = 'FACTOR'
2513 IF l_leftover.rate_multiple IS NULL
2514 THEN
2515 l_leftover.rate_multiple :=
2516 l_leftover.hxt_premium_amount;
2517 END IF;
2518
2519 IF l_leftover.hourly_rate IS NULL
2520 THEN
2521 l_retcode :=
2522 hxt_td_util.get_hourly_rate (
2523 l_leftover.date_worked,
2524 l_leftover.time_period_id,
2525 l_leftover.assignment_id,
2526 l_leftover.hourly_rate
2527 );
2528 END IF;
2529 ELSIF l_leftover.hxt_premium_type = 'RATE'
2530 THEN
2531 IF l_leftover.hourly_rate IS NULL
2532 THEN
2533 l_leftover.hourly_rate := l_leftover.hxt_premium_amount;
2534 END IF;
2535 END IF;
2536 ELSE
2537 --end SIR65
2538 --BEGIN BSE107 - OHM SPR200
2539 IF l_leftover.hourly_rate IS NULL
2540 THEN -- OHM205
2541 l_retcode :=
2542 hxt_td_util.get_hourly_rate (
2543 l_leftover.date_worked,
2544 l_leftover.time_period_id,
2545 l_leftover.assignment_id,
2546 l_leftover.hourly_rate
2547 );
2548 END IF; -- OHM SPR200
2549 --END BSE107 - OHM SPR200
2550 --begin SIR65
2551 END IF;
2552 ELSE
2553 l_leftover.hourly_rate := l_hourly_rate;
2554 END IF;
2555
2556 --end SIR65
2557
2558 --BEGIN GLOBAL
2559 -- select pay_pdt_batch_lines_s.nextval
2560 -- SELECT pay_batch_lines_s.NEXTVAL
2561
2562 --END GLOBAL
2563 -- INTO l_nextval
2564 -- FROM DUAL;
2565
2566
2567 --BEGIN GLOBAL
2568 -- INSERT into pay_pdt_batch_lines
2569 -- (batch_id, line_id,
2570 -- assignment_number, adjustment_type_code,
2571 -- amount,
2572 -- apply_this_period,
2573 -- cost_allocation_keyflex_id,concatenated_segments,
2574 -- segment1,segment2,segment3,segment4,
2575 -- segment5,segment6,segment7,segment8,
2576 -- segment9,segment10,segment11,segment12,
2577 -- segment13,segment14,segment15,segment16,
2578 -- segment17,segment18,segment19,segment20,
2579 -- segment21,segment22,segment23,segment24,
2580 -- segment25,segment26,segment27,segment28,
2581 -- segment29,segment30,
2582 -- element_name, from_date,
2583 -- to_date, hourly_rate, inc_asc_balance,
2584 -- labor_dist_code,
2585 -- line_status, locality_worked, new_salary, pay_effective_date,
2586 -- pcnt_increase, rate_code, rate_multiple, rating_code,
2587 -- separate_check_flag, shift_type, state_worked,
2588 -- tax_separately_flag, vol_ded_proc_ovd,
2589 -- hours_worked)
2590 -- VALUES(
2591 -- p_batch_id, l_nextval,
2592 -- l_leftover.assignment_number, '',
2593 -- l_leftover.amount,
2594 -- '',
2595 -- l_leftover.ffv_cost_center_id,l_leftover.concatenated_segments,
2596 -- l_leftover.segment1,l_leftover.segment2,l_leftover.segment3,l_leftover.segment4,
2597 -- l_leftover.segment5,l_leftover.segment6,l_leftover.segment7,l_leftover.segment8,
2598 -- l_leftover.segment9,l_leftover.segment10,l_leftover.segment11,l_leftover.segment12,
2599 -- l_leftover.segment13,l_leftover.segment14,l_leftover.segment15,l_leftover.segment16,
2600 -- l_leftover.segment17,l_leftover.segment18,l_leftover.segment19,l_leftover.segment20,
2601 -- l_leftover.segment21,l_leftover.segment22,l_leftover.segment23,l_leftover.segment24,
2602 -- l_leftover.segment25,l_leftover.segment26,l_leftover.segment27,l_leftover.segment28,
2603 -- l_leftover.segment29,l_leftover.segment30,
2604 -- l_leftover.element_name, '',
2605 -- '', l_leftover.hourly_rate, '',
2606 --/*TA36 l_leftover.labor_dist_code*/NULL,
2607 -- '', l_leftover.locality_worked, '', '',
2608 -- '', l_leftover.rate_code, l_leftover.rate_multiple, '',
2609 -- l_leftover.separate_check_flag, '', '',
2610 -- l_leftover.tax_separately_flag, '',
2611 -- 0 - l_leftover.hours_worked
2612 -- );
2613 insert_pay_batch_lines (
2614 p_batch_id,
2615 l_batch_line_id,
2616 l_leftover.assignment_id,
2617 l_leftover.assignment_number,
2618 l_leftover.amount,
2619 l_leftover.ffv_cost_center_id,
2620 l_leftover.concatenated_segments,
2621 l_leftover.segment1,
2622 l_leftover.segment2,
2623 l_leftover.segment3,
2624 l_leftover.segment4,
2625 l_leftover.segment5,
2626 l_leftover.segment6,
2627 l_leftover.segment7,
2628 l_leftover.segment8,
2629 l_leftover.segment9,
2630 l_leftover.segment10,
2634 l_leftover.segment14,
2631 l_leftover.segment11,
2632 l_leftover.segment12,
2633 l_leftover.segment13,
2635 l_leftover.segment15,
2636 l_leftover.segment16,
2637 l_leftover.segment17,
2638 l_leftover.segment18,
2639 l_leftover.segment19,
2640 l_leftover.segment20,
2641 l_leftover.segment21,
2642 l_leftover.segment22,
2643 l_leftover.segment23,
2644 l_leftover.segment24,
2645 l_leftover.segment25,
2646 l_leftover.segment26,
2647 l_leftover.segment27,
2648 l_leftover.segment28,
2649 l_leftover.segment29,
2650 l_leftover.segment30,
2651 l_leftover.element_type_id,
2652 l_leftover.element_name,
2653 l_leftover.hourly_rate,
2654 l_leftover.locality_worked,
2655 l_leftover.rate_code,
2656 l_leftover.rate_multiple,
2657 l_leftover.separate_check_flag,
2658 l_leftover.tax_separately_flag,
2659 0-l_leftover.hours_worked,
2660 l_leftover.date_worked,
2661 l_leftover.reason,
2662 l_batch_sequence,
2663 l_leftover.state_name,
2664 l_leftover.county_name,
2665 l_leftover.city_name,
2666 l_leftover.zip_code
2667 );
2668
2669
2670 --END GLOBAL
2671 UPDATE hxt_det_hours_worked_f
2672 SET pay_status = 'B',
2673 last_update_date = SYSDATE,
2674 retro_pbl_line_id = l_batch_line_id -- OHM180
2675 WHERE ROWID = l_leftover.hrw_rowid;
2676
2677 l_batch_sequence := l_batch_sequence + 1;
2678
2679 END LOOP;
2680 END back_out_leftover_hours;
2681
2682 PROCEDURE back_out_leftover_amount (p_batch_id NUMBER, p_tim_id NUMBER)
2683 IS
2684 CURSOR leftover_amount (p_tim_id NUMBER)
2685 IS
2686 SELECT hrw.ROWID hrw_rowid, asm.assignment_number, elt.element_name, --FORMS60
2687 DECODE (
2688 SIGN (
2689 DECODE (
2690 SIGN ( ptp.start_date
2691 - asm.effective_start_date),
2692 1, ptp.start_date,
2693 asm.effective_start_date
2694 )
2695 - elt.effective_start_date
2696 ),
2697 1, DECODE (
2698 SIGN ( ptp.start_date
2699 - asm.effective_start_date),
2700 1, ptp.start_date,
2701 asm.effective_start_date
2702 ),
2703 elt.effective_start_date
2704 )
2705 from_date,
2706 DECODE (
2707 SIGN (
2708 DECODE (
2709 SIGN ( ptp.end_date
2710 - asm.effective_end_date),
2711 -1, ptp.end_date,
2712 asm.effective_end_date
2713 )
2714 - elt.effective_end_date
2715 ),
2716 -1, DECODE (
2717 SIGN ( ptp.end_date
2718 - asm.effective_end_date),
2719 -1, ptp.end_date,
2720 asm.effective_end_date
2721 ),
2722 elt.effective_end_date
2723 ) TO_DATE,
2724 rate_multiple, hrw.hourly_rate, hrw.ffv_cost_center_id,
2725 /* fk - cost_center_code */
2726 pcak.concatenated_segments, pcak.segment1, pcak.segment2,
2727 pcak.segment3, pcak.segment4, pcak.segment5, pcak.segment6,
2728 pcak.segment7, pcak.segment8, pcak.segment9, pcak.segment10,
2729 pcak.segment11, pcak.segment12, pcak.segment13,
2730 pcak.segment14, pcak.segment15, pcak.segment16,
2731 pcak.segment17, pcak.segment18, pcak.segment19,
2732 pcak.segment20, pcak.segment21, pcak.segment22,
2733 pcak.segment23, pcak.segment24, pcak.segment25,
2734 pcak.segment26, pcak.segment27, pcak.segment28,
2735 pcak.segment29, pcak.segment30,
2736
2737 /*TA36 ffvl.flex_value labor_dist_code,*/
2738 loct.location_code locality_worked, --FORMS60
2739 ffvr.flex_value rate_code,
2740 hrw.separate_check_flag,
2741 hrw.fcl_tax_rule_code tax_separately_flag, hrw.amount,
2742 hrw.hours hours_worked, hrw.element_type_id, --GLOBAL
2743 hcl.meaning reason, --GLOBAL
2744 hrw.date_worked, /* BSE107 */ ptp.time_period_id,
2745 /* BSE107 */
2746 hrw.assignment_id ,
2747 hrw.state_name,
2748 hrw.county_name,
2749 hrw.city_name,
2750 hrw.zip_code
2751 /* BSE107 */
2752 FROM hxt_timecards_x tim, /* SIR416 PWM 21MAR00 */
2753 per_time_periods ptp,
2754 hxt_det_hours_worked_f hrw,
2755 hr_lookups hcl, --GLOBAL
2759
2756 per_assignments_f asm,
2757 pay_element_types_f elt,
2758 pay_cost_allocation_keyflex pcak,
2760 /*TA36fnd_flex_values ffvl, */
2761 hr_locations_all_tl loct, --FORMS60
2762 hr_locations_no_join loc, --FORMS60
2763 fnd_flex_values ffvr
2764 WHERE hrw.ffv_rate_code_id = ffvr.flex_value_id(+)
2765 AND hrw.location_id = loc.location_id(+)
2766
2767 --BEGIN FORMS60
2768 AND loc.location_id = loct.location_id(+)
2769 AND DECODE (loct.location_id, NULL, '1', loct.LANGUAGE) =
2770 DECODE (loct.location_id, NULL, '1', USERENV ('LANG'))
2771
2772 --END FORMS60
2773 /*TA36AND hrw.ffv_labor_account_id = ffvl.flex_value_id(+)*/
2774 AND hrw.ffv_cost_center_id = pcak.cost_allocation_keyflex_id(+)
2775 AND hrw.date_worked BETWEEN elt.effective_start_date
2776 AND elt.effective_end_date
2777 AND hrw.element_type_id = elt.element_type_id
2778 AND hrw.date_worked BETWEEN asm.effective_start_date
2779 AND asm.effective_end_date
2780 AND hrw.assignment_id = asm.assignment_id
2781 AND hrw.amount IS NOT NULL
2782
2783 /* GLOBAL AND hrw.parent_id > 0 */
2784 AND hrw.tim_id = tim.id
2785 AND tim.id = p_tim_id
2786 AND tim.time_period_id = ptp.time_period_id
2787
2788 --BEGIN GLOBAL
2789 AND hrw.date_worked BETWEEN NVL (
2790 hcl.start_date_active(+),
2791 hrw.date_worked
2792 )
2793 AND NVL (
2794 hcl.end_date_active(+),
2795 hrw.date_worked
2796 )
2797 AND hcl.lookup_type(+) = 'ELE_ENTRY_REASON'
2798 AND hcl.lookup_code(+) = hrw.fcl_earn_reason_code
2799
2800 --END GLOBAL
2801 AND hrw.pay_status = 'C'
2802 AND hrw.effective_end_date < hr_general.end_of_time; --SIR149 --FORMS60
2803
2804 l_leftover leftover_amount%ROWTYPE;
2805 -- l_nextval NUMBER (15);
2806 l_batch_line_id NUMBER (15);
2807 l_retcode NUMBER; /* BSE107 */
2808
2809 l_batch_sequence PAY_BATCH_LINES.BATCH_SEQUENCE%TYPE;
2810
2811 BEGIN
2812
2813 l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(p_batch_id);
2814
2815 FOR l_leftover IN leftover_amount (p_tim_id)
2816 LOOP
2817
2818 /* BEGIN BSE107 - OHM SPR200*/
2819 --not needed l_retcode := HXT_TD_UTIL.get_hourly_rate(l_leftover.date_worked,
2820 --not needed l_leftover.time_period_id,
2821 --not needed l_leftover.assignment_id,
2822 --not needed l_leftover.hourly_rate);
2823
2824 /* END BSE107 - OHM SPR200*/
2825
2826 --BEGIN GLOBAL
2827 -- select pay_pdt_batch_lines_s.nextval
2828 -- SELECT pay_batch_lines_s.NEXTVAL
2829
2830 --END GLOBAL
2831 -- INTO l_nextval
2832 -- FROM DUAL;
2833
2834
2835 --BEGIN GLOBAL
2836 -- INSERT into pay_pdt_batch_lines
2837 -- (batch_id, line_id,
2838 -- assignment_number, adjustment_type_code,
2839 -- amount,
2840 -- apply_this_period,
2841 -- cost_allocation_keyflex_id,concatenated_segments,
2842 -- segment1,segment2,segment3,segment4,
2843 -- segment5,segment6,segment7,segment8,
2844 -- segment9,segment10,segment11,segment12,
2845 -- segment13,segment14,segment15,segment16,
2846 -- segment17,segment18,segment19,segment20,
2847 -- segment21,segment22,segment23,segment24,
2848 -- segment25,segment26,segment27,segment28,
2849 -- segment29,segment30,
2850 -- element_name, from_date,
2851 -- to_date, hourly_rate, inc_asc_balance,
2852 -- labor_dist_code,
2853 -- line_status, locality_worked, new_salary, pay_effective_date,
2854 -- pcnt_increase, rate_code, rate_multiple, rating_code,
2855 -- separate_check_flag, shift_type, state_worked,
2856 -- tax_separately_flag, vol_ded_proc_ovd,
2857 -- hours_worked)
2858 -- VALUES(
2859 -- p_batch_id, l_nextval,
2860 -- l_leftover.assignment_number, '',
2861 -- 0 - l_leftover.amount,
2862 -- '',
2863 -- l_leftover.ffv_cost_center_id,l_leftover.concatenated_segments,
2864 -- l_leftover.segment1,l_leftover.segment2,l_leftover.segment3,l_leftover.segment4,
2865 -- l_leftover.segment5,l_leftover.segment6,l_leftover.segment7,l_leftover.segment8,
2866 -- l_leftover.segment9,l_leftover.segment10,l_leftover.segment11,l_leftover.segment12,
2867 -- l_leftover.segment13,l_leftover.segment14,l_leftover.segment15,l_leftover.segment16,
2868 -- l_leftover.segment17,l_leftover.segment18,l_leftover.segment19,l_leftover.segment20,
2869 -- l_leftover.segment21,l_leftover.segment22,l_leftover.segment23,l_leftover.segment24,
2870 -- l_leftover.segment25,l_leftover.segment26,l_leftover.segment27,l_leftover.segment28,
2871 -- l_leftover.segment29,l_leftover.segment30,
2872 -- l_leftover.element_name, '',
2873 -- '', l_leftover.hourly_rate, '',
2874 --/*TA36 l_leftover.labor_dist_code*/NULL,
2875 -- '', l_leftover.locality_worked, '', '',
2876 -- '', l_leftover.rate_code, l_leftover.rate_multiple, '',
2877 -- l_leftover.separate_check_flag, '', '',
2878 -- l_leftover.tax_separately_flag, '',
2879 -- l_leftover.hours_worked
2880 -- );
2881 insert_pay_batch_lines (
2882 p_batch_id,
2883 l_batch_line_id,
2884 l_leftover.assignment_id,
2885 l_leftover.assignment_number,
2886 0
2887 - l_leftover.amount,
2888 l_leftover.ffv_cost_center_id,
2889 l_leftover.concatenated_segments,
2890 l_leftover.segment1,
2891 l_leftover.segment2,
2892 l_leftover.segment3,
2893 l_leftover.segment4,
2894 l_leftover.segment5,
2895 l_leftover.segment6,
2896 l_leftover.segment7,
2897 l_leftover.segment8,
2898 l_leftover.segment9,
2899 l_leftover.segment10,
2900 l_leftover.segment11,
2901 l_leftover.segment12,
2902 l_leftover.segment13,
2903 l_leftover.segment14,
2904 l_leftover.segment15,
2905 l_leftover.segment16,
2906 l_leftover.segment17,
2907 l_leftover.segment18,
2908 l_leftover.segment19,
2909 l_leftover.segment20,
2910 l_leftover.segment21,
2911 l_leftover.segment22,
2912 l_leftover.segment23,
2913 l_leftover.segment24,
2914 l_leftover.segment25,
2915 l_leftover.segment26,
2916 l_leftover.segment27,
2917 l_leftover.segment28,
2918 l_leftover.segment29,
2919 l_leftover.segment30,
2920 l_leftover.element_type_id,
2921 l_leftover.element_name,
2922 l_leftover.hourly_rate,
2923 l_leftover.locality_worked,
2924 l_leftover.rate_code,
2925 l_leftover.rate_multiple,
2926 l_leftover.separate_check_flag,
2927 l_leftover.tax_separately_flag,
2928 l_leftover.hours_worked,
2929 l_leftover.date_worked,
2930 l_leftover.reason,
2931 l_batch_sequence,
2932 l_leftover.state_name,
2933 l_leftover.county_name,
2934 l_leftover.city_name,
2935 l_leftover.zip_code
2936 );
2937
2938
2939 --END GLOBAL
2940 UPDATE hxt_det_hours_worked_f
2941 SET pay_status = 'B',
2942 last_update_date = SYSDATE,
2943 retro_pbl_line_id = l_batch_line_id -- OHM180
2944 WHERE ROWID = l_leftover.hrw_rowid;
2945
2946 l_batch_sequence := l_batch_sequence +1;
2947
2948 END LOOP;
2949 END back_out_leftover_amount;
2950 --begin
2951
2952 END hxt_retro_mix;