1 PACKAGE BODY HXT_TIME_PAY AS
2 /* $Header: hxttpay.pkb 120.8.12010000.4 2010/02/17 12:11:27 asrajago ship $ */
3 g_debug boolean := hr_utility.debug_enabled;
4
5 -- Bug 8855103
6 -- This table will manipulate a cache mechanism.
7 TYPE NUMBERTAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
8 g_abstab NUMBERTAB;
9
10 -- begin SIR020
11 PROCEDURE get_retro_fields( p_tim_id IN NUMBER
12 ,p_batch_name IN VARCHAR2
13 ,p_batch_ref IN VARCHAR2
14 ,p_pay_status OUT NOCOPY VARCHAR2
15 ,p_pa_status OUT NOCOPY VARCHAR2
16 ,p_retro_batch_id OUT NOCOPY NUMBER
17 ,p_error_status OUT NOCOPY NUMBER
18 ,p_sqlerrm OUT NOCOPY VARCHAR2) IS
19
20 --first,
21 -- 1. look for detail rows where pay_status = R
22 -- set p_pay_status = R
23 -- set p_retro_batch_id = retro_batch_from_row; -- don't gen another.
24 -- done
25 -- 2. else look for detail rows where pay_status = C
26 -- set p_pay_status = R
27 -- set p_retro_batch_id = create batch; --gen a new one for this timecard.
28 -- done
29 -- 3. else -- timecard is all 'P', no retro pay transactions
30 -- set p_pay_status = P
31 -- set p_retro_batch_id = NULL; -- not a retro trans
32 -- done
33 --
34 --second,
35 -- 1. look for detail rows where pa_status = R
36 -- set p_pa_status = R
37 -- done
38 -- 2. else look for detail rows where pa_status = C
39 -- set p_pa_status = R
40 -- done
41 -- 3. else -- timecard is all 'P', no retro PA transactions
42 -- set p_pa_status = P
43 -- done
44 --
45
46
47 -- Bug 7359347
48 -- Added a session date parameter and changed
49 -- the view to base table
50 CURSOR retro_pay_trans(session_date DATE) IS
51 SELECT 'R', retro_batch_id
52 FROM hxt_det_hours_worked_f
53 WHERE tim_id = p_tim_id
54 AND session_date BETWEEN effective_start_date
55 AND effective_end_date
56 AND pay_status = 'R';
57
58 CURSOR complete_pay_trans IS
59 SELECT 'R'
60 FROM hxt_det_hours_worked_f
61 WHERE tim_id = p_tim_id
62 AND pay_status = 'C';
63
64 CURSOR retro_pa_trans IS
65 SELECT 'R'
66 FROM hxt_det_hours_worked_f
67 WHERE tim_id = p_tim_id
68 AND pa_status = 'R';
69
70 CURSOR complete_pa_trans IS
71 SELECT 'R'
72 FROM hxt_det_hours_worked_f
73 WHERE tim_id = p_tim_id
74 AND pa_status = 'C';
75
76 CURSOR check_for_details IS
77 SELECT 'R'
78 FROM hxt_batch_states tbs,
79 hxt_timecards_f tim
80 WHERE tim.id = p_tim_id
81 AND tbs.batch_id = tim.batch_id
82 AND tbs.status = 'VT'
83 AND NOT EXISTS (SELECT '1'
84 FROM hxt_det_hours_worked_f det
85 WHERE det.tim_id = tim.id
86 AND NVL(det.hours,0) > 0);
87
88 -- Bug 9367730
89 l_no_pay_trans BOOLEAN := FALSE;
90
91
92 BEGIN
93
94
95 -- Bug 7359347
96 -- Setting the session date to the global variable
97 IF g_pay_session_date IS NULL
98 THEN
99 g_pay_session_date := hxt_tim_col_util.return_session_date;
100 END IF;
101
102 g_debug :=hr_utility.debug_enabled;
103 if g_debug then
104 hr_utility.set_location('hxt_time_pay.get_retro_fields',10);
105 end if;
106
107 -- Bug 7359347
108 -- Added session date parameter.
109 OPEN retro_pay_trans(g_pay_session_date);
110 FETCH retro_pay_trans INTO p_pay_status, p_retro_batch_id;
111 if g_debug then
112 hr_utility.trace('p_pay_status :'||p_pay_status);
113 hr_utility.trace('p_retro_batch_id :'||p_retro_batch_id);
114 end if;
115
116 IF retro_pay_trans%NOTFOUND THEN
117 if g_debug then
118 hr_utility.set_location('hxt_time_pay.get_retro_fields',20);
119 end if;
120 OPEN complete_pay_trans;
121 FETCH complete_pay_trans INTO p_pay_status;
122 if g_debug then
123 hr_utility.trace('p_pay_status :'||p_pay_status);
124 end if;
125
126 IF complete_pay_trans%NOTFOUND THEN
127 if g_debug then
128 hr_utility.set_location('hxt_time_pay.get_retro_fields',30);
129 end if;
130 p_pay_status := 'P';
131 p_retro_batch_id := NULL;
132 -- Bug 9367730
133 l_no_pay_trans := TRUE;
134 ELSE
135 if g_debug then
136 hr_utility.set_location('hxt_time_pay.get_retro_fields',40);
137 end if;
138 p_retro_batch_id := hxt_UTIL.Get_Retro_Batch_Id(p_tim_id
139 ,p_batch_name
140 ,p_batch_ref);
141 if g_debug then
142 hr_utility.trace('p_retro_batch_id :'||p_retro_batch_id);
143 end if;
144 END IF;
145 if g_debug then
146 hr_utility.set_location('hxt_time_pay.get_retro_fields',50);
147 end if;
148 CLOSE complete_pay_trans;
149 END IF;
150 if g_debug then
151 hr_utility.set_location('hxt_time_pay.get_retro_fields',60);
152 end if;
153 CLOSE retro_pay_trans;
154
155 OPEN retro_pa_trans;
156 FETCH retro_pa_trans INTO p_pa_status;
157 if g_debug then
158 hr_utility.trace('p_pa_status :'||p_pa_status);
159 end if;
160
161 IF retro_pa_trans%NOTFOUND THEN
162 if g_debug then
163 hr_utility.set_location('hxt_time_pay.get_retro_fields',70);
164 end if;
165 OPEN complete_pa_trans;
166 FETCH complete_pa_trans INTO p_pa_status;
167 if g_debug then
168 hr_utility.trace('p_pa_status :'||p_pa_status);
169 end if;
170
171 IF complete_pa_trans%NOTFOUND THEN
172 if g_debug then
173 hr_utility.set_location('hxt_time_pay.get_retro_fields',80);
174 end if;
175 p_pa_status := 'P';
176 END IF;
177 if g_debug then
178 hr_utility.set_location('hxt_time_pay.get_retro_fields',90);
179 end if;
180 CLOSE complete_pa_trans;
181 END IF;
182 if g_debug then
183 hr_utility.set_location('hxt_time_pay.get_retro_fields',100);
184 end if;
185 CLOSE retro_pa_trans;
186
187
188 -- Bug No 7359347
189 -- Do it only if p_pay_status is not already retrieved.
190 -- Bug 9367730
191 -- Added an OR condition, just in case there are no details greater than zero.
192 IF p_pay_status IS NULL
193 OR l_no_pay_trans = TRUE
194 THEN
195 OPEN check_for_details ;
196 FETCH check_for_details INTO p_pay_status ;
197 if g_debug then
198 hr_utility.trace('p_pay_status :'||p_pay_status);
199 end if;
200
201 IF check_for_details%FOUND THEN
202 if g_debug then
203 hr_utility.set_location('hxt_time_pay.get_retro_fields',110);
204 end if;
205 p_retro_batch_id := HXT_UTIL.Get_Retro_Batch_Id(p_tim_id
206 ,p_batch_name
207 ,p_batch_ref);
208 if g_debug then
209 hr_utility.trace('p_retro_batch_id :'||p_retro_batch_id);
210 end if;
211 END IF ;
212 if g_debug then
213 hr_utility.set_location('hxt_time_pay.get_retro_fields',120);
214 end if;
215 close check_for_details ;
216
217 END IF;
218
219
220 p_error_status := 0;
221 p_sqlerrm := '';
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 if g_debug then
226 hr_utility.set_location('hxt_time_pay.get_retro_fields',130);
227 end if;
228 p_error_status := 2;
229 p_sqlerrm := sqlerrm;
230
231 END; --get_retro_fields
232 --end RETROPAY
233
234 FUNCTION PAY (
235 g_ep_id IN NUMBER,
236 g_ep_type IN VARCHAR2,
237 g_egt_id IN NUMBER,
238 g_sdf_id IN NUMBER,
239 g_hdp_id IN NUMBER,
240 g_hol_id IN NUMBER,
241 g_pep_id IN NUMBER,
242 g_pip_id IN NUMBER,
243 g_sdovr_id IN NUMBER,
244 g_osp_id IN NUMBER,
245 g_hol_yn IN VARCHAR2,
246 g_person_id IN NUMBER,
247 g_location IN VARCHAR2,
248 g_ID IN NUMBER,
249 g_TIM_ID IN NUMBER,
250 g_DATE_WORKED IN DATE,
251 g_ASSIGNMENT_ID IN NUMBER,
252 g_HOURS IN NUMBER,
253 g_TIME_IN IN DATE,
254 g_TIME_OUT IN DATE,
255 g_ELEMENT_TYPE_ID IN NUMBER,
256 g_FCL_EARN_REASON_CODE IN VARCHAR2,
257 g_FFV_COST_CENTER_ID IN NUMBER,
258 g_FFV_LABOR_ACCOUNT_ID IN NUMBER,
259 g_TAS_ID IN NUMBER,
260 g_LOCATION_ID IN NUMBER,
261 g_SHT_ID IN NUMBER,
262 g_HRW_COMMENT IN VARCHAR2,
263 g_FFV_RATE_CODE_ID IN NUMBER,
264 g_RATE_MULTIPLE IN NUMBER,
265 g_HOURLY_RATE IN NUMBER,
266 g_AMOUNT IN NUMBER,
267 g_FCL_TAX_RULE_CODE IN VARCHAR2,
268 g_SEPARATE_CHECK_FLAG IN VARCHAR2,
269 g_SEQNO IN NUMBER,
270 g_CREATED_BY IN NUMBER,
271 g_CREATION_DATE IN DATE,
272 g_LAST_UPDATED_BY IN NUMBER,
273 g_LAST_UPDATE_DATE IN DATE,
274 g_LAST_UPDATE_LOGIN IN NUMBER,
275 g_EFFECTIVE_START_DATE IN DATE,
276 g_EFFECTIVE_END_DATE IN DATE,
277 g_PROJECT_ID IN NUMBER,
278 g_PAY_STATUS IN VARCHAR2,
279 g_PA_STATUS IN VARCHAR2,
280 g_RETRO_BATCH_ID IN NUMBER,
281 g_STATE_NAME IN VARCHAR2 DEFAULT NULL,
282 g_COUNTY_NAME IN VARCHAR2 DEFAULT NULL,
283 g_CITY_NAME IN VARCHAR2 DEFAULT NULL,
284 g_ZIP_CODE IN VARCHAR2 DEFAULT NULL
285 --g_GROUP_ID IN NUMBER
286 )
287 RETURN NUMBER IS
288
289 location VARCHAR2(120) := g_location||':PAY';
290
291 -- Bug 8855103
292 -- Variable used below.
293 l_abs NUMBER;
294
295 FUNCTION call_gen_error(p_location IN varchar2
296 ,p_error_text IN VARCHAR2
297 ,p_oracle_error_text IN VARCHAR2 default NULL)
298 RETURN NUMBER IS
299
300 -- calls error processing procedure --
301
302 BEGIN
303
304 if g_debug then
305 hr_utility.set_location('hxt_time_pay.call_gen_error',10);
306 end if;
307 hxt_util.gen_error(g_tim_id,
308 g_id,
309 NULL,
310 p_error_text,
311 p_location,
312 p_oracle_error_text,
313 g_EFFECTIVE_START_DATE,
314 g_EFFECTIVE_END_DATE,
315 'ERR');
316 if g_debug then
317 hr_utility.set_location('hxt_time_pay.call_gen_error',20);
318 end if;
319 RETURN 2;
320 END;
321
322 FUNCTION call_hxthxc_gen_error
323 (p_app_short_name IN VARCHAR2,
324 p_msg_name IN VARCHAR2,
325 P_msg_token IN VARCHAR2,
326 p_location IN varchar2,
327 p_error_text IN VARCHAR2,
328 p_oracle_error_text IN VARCHAR2 default NULL)
329 RETURN NUMBER IS
330 -- calls error processing procedure --
331 BEGIN
332
333 if g_debug then
334 hr_utility.set_location('hxt_time_pay.call_gen_error',10);
335 end if;
336 hxt_util.gen_error(g_tim_id,
337 g_id,
338 NULL,
339 p_error_text,
340 p_location,
341 p_oracle_error_text,
342 g_EFFECTIVE_START_DATE,
343 g_EFFECTIVE_END_DATE,
344 'ERR');
345
346 hxc_time_entry_rules_utils_pkg.add_error_to_table (
347 p_message_table=> hxt_hxc_retrieval_process.g_otm_messages,
348 p_message_name=> p_msg_name,
349 p_message_token=> NULL,
350 p_message_level=> 'ERROR',
351 p_message_field=> NULL,
352 p_application_short_name=> p_app_short_name,
353 p_timecard_bb_id=> null,
354 p_time_attribute_id=> NULL,
355 p_timecard_bb_ovn=> NULL,
356 p_time_attribute_ovn=> NULL
357 );
358 if g_debug then
359 hr_utility.trace('Adding to g_otm_messages'||p_msg_name);
360 hr_utility.set_location('hxt_time_pay.call_gen_error',20);
361 end if;
362 RETURN 2;
363 END;
364
365 PROCEDURE INSERT_HRS( p_return_code OUT NOCOPY NUMBER,
366 p_id OUT NOCOPY NUMBER,
367 p_hours IN NUMBER,
368 p_time_in IN DATE,
369 p_time_out IN DATE,
370 p_element_type_id IN NUMBER,
371 p_seqno IN NUMBER,
372 p_location IN VARCHAR2 ) IS
373
374 -- Procedure INSERT_HRS
375 --
376 -- Purpose
377 -- Insert a record in HXT_HOURS_WORKED.
378 --
379 -- Returns
380 -- p_return_code - the record code (0 - no errors 2 - errors occured.
381 -- p_id - ID of record inserted
382 --
383 -- Arguments
384 -- base record columns - The values to be inserted.
385 -- Modifications
386 -- 2/15/96 Changed line_status field write to always be null as children
387 -- hours worked records do not need their parents' status. AVS
388 -- 4/23/97 Added the get_ovt_rates_cur to fetch premium types and amounts
389 -- which need to be inserted into hxt_det_hours_worked table.
390 -- Fixed under Oracle Bugs #465434 & #464850.
391 -- 1/07/98 SIR69 Cursor get_ovt_rates_cur now handles all premiums and
392 -- not just overtime. Was ignoring earn types of OTH etc.
393 -- 1/22/98 SIR092 Hours are not written to premium types of FIXED.
394 --
395 v_amount hxt_det_hours_worked.amount%type := null; --SIR029
396 l_costable_type PAY_ELEMENT_LINKS_F.COSTABLE_TYPE%TYPE;
397 l_ffv_cost_center_id HXT_DET_HOURS_WORKED_F.FFV_COST_CENTER_ID%TYPE;
398
399 CURSOR next_id_cur IS
400 SELECT hxt_seqno.nextval next_id
401 FROM dual;
402
403 l_id NUMBER;
404 l_pay_status CHAR(1);
405 l_pa_status CHAR(1);
406 l_retro_batch_id NUMBER(15);
407 l_error_status NUMBER(15);
408 l_sqlerrm CHAR(200);
409 l_rowid ROWID;
410 l_object_version_number NUMBER DEFAULT NULL;
411
412 -- ************************************
413 -- ORACLE START Bugs #465434 & #464850
414 -- ************************************
415 CURSOR get_ovt_rates_cur IS
416 SELECT eltv.hxt_premium_type,
417 eltv.hxt_premium_amount,
418 eltv.hxt_processing_order
419 FROM hxt_pay_element_types_f_ddf_v eltv
420 WHERE eltv.hxt_earning_category NOT IN ('REG', 'ABS')
421 AND g_DATE_WORKED between eltv.effective_start_date
422 and eltv.effective_end_date
423 AND eltv.element_type_id = p_element_type_id
424 ORDER by eltv.hxt_processing_order;
425
426 premium_type hxt_pay_element_types_f_ddf_v.hxt_premium_type%TYPE ;
427 premium_amount hxt_pay_element_types_f_ddf_v.hxt_premium_amount%TYPE ;
428 processing_order hxt_pay_element_types_f_ddf_v.hxt_processing_order%TYPE ;
429
430 l_rate_multiple hxt_pay_element_types_f_ddf_v.hxt_premium_amount%TYPE ;
431 l_hourly_rate hxt_pay_element_types_f_ddf_v.hxt_premium_amount%TYPE ;
432 l_amount hxt_pay_element_types_f_ddf_v.hxt_premium_amount%TYPE ;
433
434 l_hours hxt_det_hours_worked_f.hours%TYPE ; -- SIR092
435 -- *********************************
436 -- ORACLE END Bugs #465434 & #464850
437 -- *********************************
438
439 BEGIN
440
441 if g_debug then
442
443 hr_utility.set_location('hxt_time_pay.INSERT_HRS',10);
444 end if;
445
446 --Set return code and get ID.
447 p_return_code := 0;
448
449 OPEN next_id_cur;
450 FETCH next_id_cur INTO l_id;
451 if g_debug then
452
453 hr_utility.trace('l_id :'||l_id);
454 end if;
455 CLOSE next_id_cur;
456
457 p_id := l_id;
458
459 get_retro_fields( g_tim_id
460 ,HXT_TIME_COLLECTION.g_batch_name
461 ,HXT_TIME_COLLECTION.g_batch_ref
462 ,l_pay_status
463 ,l_pa_status
464 ,l_retro_batch_id
465 ,l_error_status
466 ,l_sqlerrm);
467
468 IF l_error_status = 0 THEN
469 if g_debug then
470 hr_utility.set_location('hxt_time_pay.INSERT_HRS',20);
471 end if;
472
473 BEGIN
474 -- ***********************************
475 -- ORACLE START Bugs #465434 & #464850
476 -- ***********************************
477
478 if g_debug then
479 hr_utility.set_location('hxt_time_pay.INSERT_HRS',30);
480 end if;
481
482 OPEN get_ovt_rates_cur ;
483 FETCH get_ovt_rates_cur
484 INTO premium_type, premium_amount, processing_order ;
485 if g_debug then
486 hr_utility.trace('premium_type :'||premium_type);
487 hr_utility.trace('premium_amount :'||premium_amount);
488 hr_utility.trace('processing_order :'||processing_order);
489 end if;
490
491 CLOSE get_ovt_rates_cur ;
492 --
493 -- Default the hours worked to those passed in
494 --
495 l_hours := p_hours ; -- SIR092
496 if g_debug then
497 hr_utility.trace('l_hours :'||l_hours);
498 end if;
499 --
500 -- Determine if a premium amount should apply.
501 --
502 IF premium_type = 'FACTOR' THEN
503 if g_debug then
504 hr_utility.set_location('hxt_time_pay.INSERT_HRS',40);
505 end if;
506 l_rate_multiple := premium_amount ;
507 ELSIF premium_type = 'RATE' THEN
508 if g_debug then
509 hr_utility.set_location('hxt_time_pay.INSERT_HRS',50);
510 end if;
511 l_hourly_rate := premium_amount ;
512 ELSIF premium_type = 'FIXED' THEN
513 if g_debug then
514 hr_utility.set_location('hxt_time_pay.INSERT_HRS',60);
515 end if;
516 l_amount := premium_amount ;
517 l_hours := 0 ; -- SIR092 Hours have no meaning with
518 -- flat amount premiums
519 ELSE
520 if g_debug then
521 hr_utility.set_location('hxt_time_pay.INSERT_HRS',70);
522 end if;
523 NULL ;
524 END IF ;
525 --
526 -- Any values passed in from globals will override retrieved values.
527 --
528 IF g_rate_multiple IS NOT NULL THEN
529 if g_debug then
530 hr_utility.set_location('hxt_time_pay.INSERT_HRS',80);
531 end if;
532 l_rate_multiple := g_rate_multiple ;
533 if g_debug then
534 hr_utility.trace('l_rate_multiple :'||l_rate_multiple);
535 end if;
536 END IF ;
537 --
538 IF g_hourly_rate IS NOT NULL THEN
539 if g_debug then
540 hr_utility.set_location('hxt_time_pay.INSERT_HRS',90);
541 end if;
542 l_hourly_rate := g_hourly_rate ;
543 END IF ;
544 --
545 IF g_amount IS NOT NULL THEN
546 if g_debug then
547 hr_utility.set_location('hxt_time_pay.INSERT_HRS',100);
548 end if;
549 l_amount := g_amount ;
550 if g_debug then
551 hr_utility.trace('l_amount :'||l_amount);
552 end if;
553 END IF ;
554
555 -- *********************************
556 -- ORACLE END Bugs #465434 & #464850
557 -- *********************************
558 --
559 if g_debug then
560 hr_utility.set_location('hxt_time_pay.INSERT_HRS',110);
561 end if;
562 l_costable_type := HXT_UTIL.get_costable_type(p_element_type_id,
563 g_date_worked,
564 g_assignment_id);
565 if g_debug then
566 hr_utility.trace('l_costable_type :'||l_costable_type);
567 end if;
568
569 IF l_costable_type in ('C','F') THEN
570 if g_debug then
571 hr_utility.set_location('hxt_time_pay.INSERT_HRS',120);
572 end if;
573 l_ffv_cost_center_id := g_FFV_COST_CENTER_ID;
574 if g_debug then
575 hr_utility.trace('l_ffv_cost_center_id :'||l_ffv_cost_center_id);
576 end if;
577 ELSE
578 if g_debug then
579 hr_utility.set_location('hxt_time_pay.INSERT_HRS',130);
580 end if;
581 l_ffv_cost_center_id := NULL;
582 if g_debug then
583 hr_utility.trace('l_ffv_cost_center_id :'||l_ffv_cost_center_id);
584 end if;
585 END IF;
586
587 /* INSERT INTO hxt_det_hours_worked_f(id,
588 parent_id,
589 tim_id,
590 date_worked,
591 assignment_id,
592 hours,
593 time_in,
594 time_out,
595 element_type_id,
596 fcl_earn_reason_code,
597 ffv_cost_center_id,
598 tas_id,
599 location_id,
600 sht_id,
601 hrw_comment,
602 ffv_rate_code_id,
603 rate_multiple,
604 hourly_rate,
605 amount,
606 fcl_tax_rule_code,
607 separate_check_flag,
608 seqno,
609 created_by,
610 creation_date,
611 last_updated_by,
612 last_update_date,
613 last_update_login,
614 effective_start_date,
615 effective_end_date,
616 project_id,
617 pay_status,
618 pa_status,
619 retro_batch_id
620 --group_id
621 )
622 VALUES(p_id,
623 g_id,
624 g_tim_id,
625 g_date_worked,
626 g_assignment_id,
627 l_hours,
628 p_time_in,
629 p_time_out,
630 p_element_type_id,
631 g_fcl_earn_reason_code,
632 l_ffv_cost_center_id,
633 g_tas_id,
634 g_location_id,
635 g_sht_id,
636 g_hrw_comment,
637 g_ffv_rate_code_id,
638 l_rate_multiple,
639 l_hourly_rate,
640 l_amount,
641 g_fcl_tax_rule_code,
642 g_separate_check_flag,
643 p_seqno,
644 g_created_by,
645 g_creation_date,
646 g_last_updated_by,
647 g_last_update_date,
648 g_last_update_login,
649 g_effective_start_date,
650 g_effective_end_date,
651 g_project_id,
652 l_pay_status,
653 l_pa_status,
654 l_retro_batch_id
655 --g_group_id
656 ); */
657
658
659 /* Call dml to insert hours into hxt_det_hours_worked_f */
660 if g_debug then
661 hr_utility.set_location('hxt_time_pay.INSERT_HRS',140);
662 end if;
663
664 hxt_dml.insert_HXT_DET_HOURS_WORKED(
665 p_rowid => l_rowid,
666 p_id => p_id,
667 p_parent_id => g_id,
668 p_tim_id => g_tim_id,
669 p_date_worked => g_date_worked,
670 p_assignment_id => g_assignment_id,
671 p_hours => l_hours,
672 p_time_in => p_time_in,
673 p_time_out => p_time_out,
674 p_element_type_id => p_element_type_id,
675 p_fcl_earn_reason_code => g_fcl_earn_reason_code,
676 p_ffv_cost_center_id => l_ffv_cost_center_id,
677 p_ffv_labor_account_id => NULL,
678 p_tas_id => g_tas_id,
679 p_location_id => g_location_id,
680 p_sht_id => g_sht_id,
681 p_hrw_comment => g_hrw_comment,
682 p_ffv_rate_code_id => g_ffv_rate_code_id,
683 p_rate_multiple => l_rate_multiple,
684 p_hourly_rate => l_hourly_rate,
685 p_amount => l_amount,
686 p_fcl_tax_rule_code => g_fcl_tax_rule_code,
687 p_separate_check_flag => g_separate_check_flag,
688 p_seqno => p_seqno,
689 p_created_by => g_created_by,
690 p_creation_date => g_creation_date,
691 p_last_updated_by => g_last_updated_by,
692 p_last_update_date => g_last_update_date,
693 p_last_update_login => g_last_update_login,
694 p_actual_time_in => NULL,
695 p_actual_time_out => NULL,
696 p_effective_start_date => g_effective_start_date,
697 p_effective_end_date => g_effective_end_date,
698 p_project_id => g_project_id,
699 p_job_id => NULL,
700 p_earn_pol_id => NULL,
701 p_retro_batch_id => l_retro_batch_id,
702 p_pa_status => l_pa_status,
703 p_pay_status => l_pay_status,
704 --p_group_id => g_group_id,
705 p_object_version_number => l_object_version_number,
706 p_STATE_NAME => g_STATE_NAME,
707 p_COUNTY_NAME => g_COUNTY_NAME,
708 p_CITY_NAME => g_CITY_NAME,
709 p_ZIP_CODE => g_ZIP_CODE);
710
711 if g_debug then
712 hr_utility.set_location('hxt_time_pay.INSERT_HRS',150);
713 end if;
714
715 EXCEPTION
716
717 WHEN OTHERS THEN
718 if g_debug then
719 hr_utility.set_location('hxt_time_pay.INSERT_HRS',160);
720 end if;
721 -- Insert record in error table.
722 FND_MESSAGE.SET_NAME('HXT','HXT_39313_OR_ERR_INS_REC');
723 p_return_code := call_hxthxc_gen_error('HXT','HXT_39313_OR_ERR_INS_REC',NULL,p_location||':INS', '',sqlerrm);
724 --2278400 p_return_code := call_gen_error(p_location||':INS', '',sqlerrm);
725 if g_debug then
726 hr_utility.trace('p_return_code :'||p_return_code);
727 end if;
728 END;
729
730 ELSE
731 if g_debug then
732 hr_utility.set_location('hxt_time_pay.INSERT_HRS',170);
733 end if;
734 FND_MESSAGE.SET_NAME('HXT','HXT_39421_GET_RETRO_ERR');
735 p_return_code := call_hxthxc_gen_error('HXT','HXT_39421_GET_RETRO_ERR',NULL,p_location||':INS', '',sqlerrm);
736 --2278400 p_return_code := call_gen_error(p_location||':INS', '',sqlerrm);
737 if g_debug then
738 hr_utility.trace('p_return_code :'||p_return_code);
739 end if;
740 END IF;
741
742 END INSERT_HRS;
743 ---------------------------------------------------------------------------
744 FUNCTION Gen_Premiums(
745 p_sdf_id NUMBER,
746 p_sdovr_id NUMBER,
747 p_location VARCHAR2,
748 p_base_id NUMBER,
749 p_hours NUMBER,
750 p_time_in DATE,
751 p_time_out DATE,
752 p_element_type_id NUMBER,
753 p_seqno NUMBER )
754
755 RETURN NUMBER IS
756 --
757 -- Function HXT_GEN_PREMIUMS
758 --
759 -- Updated 01-09-96 by Bob ... Incorporated Error handling
760 --
761 -- Purpose
762 -- Create all premium records for a base-hour entry that a person is
763 -- entitled to based on earning category rules, premium eligibility for
764 -- a base hour, and being linked to a person.
765 --
766 -- Function will:
767 -- 1. insert premiums for a non-overtime base-hour record
768 -- 2. update the override multiple or hourly rate for an
769 -- overtime base-hour record
770 -- Derives override multiple and fixed rate if premium is type Factor
771 -- or Fixed Rate by applying premium interaction rules against premiums
772 -- of lower priority that have already been inserted for this
773 -- calling base-hour detail record. Thus, we avoid re-applying the
774 -- earning-category based rules when calculating the multiple.
775 -- The hours on base record apply to only one shift.
776 --
777 -- Arguments
778 -- p_base_id - The base hours worked detail record for which to
779 -- generate premiums.
780 -- p_sdf_id - The id of any applicable shift differential premium
781 -- incl override sdf.
782 -- p_location - The Procedure and/or Function path where an error occurred
783 -- base_hour_columns - Columns of the base-hour record for which we
784 -- are creating premiums.
785 --
786 -- Modifications
787 -- 04/23/97 Oracle Bugs #465434 & #464850. User no longer has to prefix
788 -- premiums with a '1' that were meant to be a percentage.
789 -- (Ex. User can enter .15 for 15% instead of entering 1.15.)
790 -- User can still enter 1.5 for overtime hours of time and a
791 -- half. PWM
792 -- 09/15/97 ORA131 Function will now handle Fixed Rate premiums as
793 -- well as Factor Multiple premiums when it comes to the premium
794 -- interaction rules. PWM
795 -- 01/16/98 SIR#9 Formula for calculting premiums amounts has been
796 -- modified so that it is no longer necessary to subtract 1 from
797 -- overtime premiums.
798 -- 01/22/98 SIR092 Hours are not written to premium types of FIXED. PWM
799 -- 01/30/98 Peformance tuned cursor cur_elig_prem. PWM
800 -- 02/27/98 SIR103 Still need to subtract 1 from overtime premiums
801 -- before interaction with other premiums. PWM
802 --
803 -- Define variables
804 --
805 l_pay_status CHAR(1);
806 l_pa_status CHAR(1);
807 l_retro_batch_id NUMBER(15);
808 l_error_status NUMBER(15);
809 l_sqlerrm CHAR(200);
810
811 l_mult hxt_pay_element_types_f_ddf_v.hxt_premium_amount%TYPE;
812 -- Premium override multiple
813 l_rate hxt_pay_element_types_f_ddf_v.hxt_premium_amount%TYPE;
814 -- Premium override rate ORA131
815
816 l_hours hxt_det_hours_worked_f.hours%TYPE ; -- SIR092
817 l_time_in hxt_det_hours_worked_f.time_in%TYPE ;
818 l_time_out hxt_det_hours_worked_f.time_out%TYPE ;
819 l_seqno NUMBER; -- Line seqno
820 l_error_code NUMBER DEFAULT 0; -- Default to no error
821 l_location VARCHAR2(120); -- Current Path for locating source
822 -- of errors
823 hrw_rowid ROWID;
824
825 l_min_detail_seqno NUMBER;
826 l_max_detail_seqno NUMBER;
827
828 l_costable_type pay_element_links_f.costable_type%TYPE;
829 l_ffv_cost_center_id hxt_det_hours_worked_f.ffv_cost_center_id%TYPE;
830
831 l_rowid ROWID;
832 l_object_version_number NUMBER DEFAULT NULL;
833 l_id NUMBER;
834
835 l_rate_multiple hxt_pay_element_types_f_ddf_v.hxt_premium_amount%TYPE ;
836 l_hourly_rate hxt_pay_element_types_f_ddf_v.hxt_premium_amount%TYPE ;
837 l_amount hxt_pay_element_types_f_ddf_v.hxt_premium_amount%TYPE ;
838 --
839 -- Cursor to return all premiums for a base hour entry that a person is
840 -- entitled to based on earning category rules, premium eligibility
841 -- for a base hour, and being linked to the person's assignment.
842 -- Earning category rules enforced in where clause decode:
843 -- Returns only shift diff premium p_sdf_id when processing category SDF.
844 -- Returns only off shift premium g_osp_id when processing category OSP.
845 -- Returns holiday premium if g_hol_yn = 'Y' when processing category HOL.
846
847 CURSOR cur_elig_prem IS
848 SELECT /* +INDEX (ell pay_pk) +INDEX(per per_pk) */
849 per.elt_premium_id, eltt.element_name, eltv.hxt_earning_category,
850 eltv.hxt_premium_type, eltv.hxt_premium_amount,
851 eltv.hxt_processing_order
852 FROM pay_element_links_f ell,
853 hxt_pay_element_types_f_ddf_v eltv,
854 pay_element_types_f elt,
855 pay_element_types_f_tl eltt,
856 hxt_prem_eligblty_rules per,
857 per_all_assignments_f asm
858 WHERE asm.assignment_id = g_ASSIGNMENT_ID
859 AND g_DATE_WORKED between asm.effective_start_date
860 and asm.effective_end_date
861 AND per.pep_id = g_PEP_ID -- prem eligibility policy
862 AND per.elt_base_id = p_element_type_id -- base-hour earning
863 AND g_DATE_WORKED between per.effective_start_date
864 and per.effective_end_date
865 AND per.elt_premium_id = elt.element_type_id -- element type
866 AND g_DATE_WORKED between elt.effective_start_date
867 and elt.effective_end_date
868 AND elt.element_type_id = -- allow hol prem if holiday
869 decode(eltv.hxt_earning_category, 'HOL',
870 decode(g_HOL_YN,'Y',elt.element_type_id,-1),elt.element_type_id)
871 AND( elt.element_type_id = -- restrict to earnings passed, if any
872 decode(eltv.hxt_earning_category, 'SDF', p_sdf_id,
873 'OSP', g_OSP_ID,
874 elt.element_type_id)
875 -- Added the OR clause to check if the earning type
876 -- is the Override earning.
877 OR elt.element_type_id = -- restrict to earnings passed, if any
878 decode(eltv.hxt_earning_category, 'SDF', p_sdovr_id,
879 'OSP', g_OSP_ID,
880 elt.element_type_id) )
881 AND eltt.element_type_id = elt.element_type_id
882 AND eltt.language = userenv('LANG')
883 AND elt.element_type_id = eltv.element_type_id
884 AND g_DATE_WORKED between eltv.effective_start_date
885 and eltv.effective_end_date
886 AND eltv.hxt_earning_category NOT in('ABS','REG','OVT')
887 AND elt.element_type_id = ell.element_type_id
888 AND g_DATE_WORKED between ell.effective_start_date
889 and ell.effective_end_date
890 AND nvl(ell.pay_basis_id,nvl(asm.pay_basis_id,-1)) =
891 nvl(asm.pay_basis_id,-1)
892 AND nvl(ell.employment_category,nvl(asm.employment_category,-1)) =
893 nvl(asm.employment_category,-1)
894 AND nvl(ell.payroll_id,nvl(asm.payroll_id,-1)) =
895 nvl(asm.payroll_id,-1)
896 AND nvl(ell.location_id,nvl(asm.location_id,-1)) =
897 nvl(asm.location_id,-1)
898 AND nvl(ell.grade_id,nvl(asm.grade_id,-1)) = nvl(asm.grade_id,-1)
899 AND nvl(ell.position_id,nvl(asm.position_id,-1)) = nvl(asm.position_id,-1)
900 AND (nvl(ell.job_id, nvl(asm.job_id,-1)) = nvl(asm.job_id,-1))
901 --
902 -- We need to link to pay_assignment_link_usages for people_group eligibility.
903 --
904 AND (ell.people_group_id is null
905 OR EXISTS ( SELECT 1 FROM PAY_ASSIGNMENT_LINK_USAGES_F USAGE
906 WHERE USAGE.ASSIGNMENT_ID = ASM.ASSIGNMENT_ID
907 AND USAGE.ELEMENT_LINK_ID = ELL.ELEMENT_LINK_ID
908 AND G_DATE_WORKED BETWEEN USAGE.EFFECTIVE_START_DATE
909 AND USAGE.EFFECTIVE_END_DATE))
910 AND nvl(ell.organization_id,nvl(asm.organization_id,-1)) =
911 nvl(asm.organization_id,-1)
912 AND nvl(ell.business_group_id,nvl(asm.business_group_id,-1)) =
913 nvl(asm.business_group_id,-1) -- link to assignment
914
915 UNION ALL
916
917 -- Second part of union handles possible overtime
918 SELECT elt.element_type_id
919 ,eltt.element_name
920 ,eltv.hxt_earning_category --FORMS60
921 ,eltv.hxt_premium_type
922 ,eltv.hxt_premium_amount
923 ,eltv.hxt_processing_order
924 FROM hxt_pay_element_types_f_ddf_v eltv
925 ,pay_element_types_f elt
926 ,pay_element_types_f_tl eltt
927 WHERE elt.element_type_id = p_element_type_id
928 AND g_DATE_WORKED between elt.effective_start_date
929 and elt.effective_end_date
930 AND eltt.element_type_id = elt.element_type_id
931 AND eltt.language = userenv('LANG')
932 AND elt.element_type_id = eltv.element_type_id
933 AND g_DATE_WORKED between eltv.effective_start_date
934 and eltv.effective_end_date
935 AND eltv.hxt_earning_category = 'OVT' -- overtime only
936 ORDER by 6;
937 --
938 -- Get the minimum sequence number of the current detail row
939 --
940
941 -- Bug 7359347
942 -- Changed the below cursor to work with HXT_DET_HOURS_WORKED_F
943 -- rather than HXT_DET_HOURS_WORKED to avoid contention on FND_SESSIONS
944 /*
945 CURSOR get_min_detail_seqno IS
946 SELECT seqno
947 FROM hxt_det_hours_worked
948 WHERE id = p_BASE_ID;
949 --
950 -- Get the maximum sequence number of the current detail row
951 --
952 CURSOR get_max_detail_seqno IS
953 SELECT nvl(min(hrw.seqno),9999)
954 FROM hxt_pay_element_types_f_ddf_v eltv
955 ,pay_element_types_f elt
956 ,hxt_det_hours_worked hrw
957 WHERE hrw.tim_id = g_TIM_ID
958 AND hrw.date_worked = g_DATE_WORKED
959 AND hrw.parent_id = g_ID
960 AND hrw.seqno > l_min_detail_seqno
961 AND elt.element_type_id = hrw.element_type_id
962 AND eltv.element_type_id = elt.element_type_id
963 AND g_DATE_WORKED BETWEEN eltv.effective_start_date
964 AND eltv.effective_end_date
965 AND eltv.hxt_earning_category IN ('REG','OVT','ABS');
966
967 -- not needed. declared in for loop.
968 -- elig_prem_rec cur_elig_prem%ROWTYPE;
969
970 -- Step through the premium detail records of type factor that were
971 -- already inserted into the hours worked table. All are lower processing
972 -- order than the current premium with the possible exception of an overtime
973 -- base-hour which also has a processing order. Premium must be of type
974 -- Factor to exist in hxt_prem_interact_rules. Match them up against premium
975 -- interaction rules to calculate premium. Will need to get factor if not
976 -- overridden.
977 -- SIR189 - To assure that a rate or multiple gets applied to the correct
978 -- row of the the detail table, we must make sure that the hrw.seqno is
979 -- greater than the starting earning item's seqno and that the hrw.seqno
980 -- is lesser than any other earning items of type REG, OVT or ABS. This
981 -- gives us an association between interacting premiums and the earning
982 -- elements they are acting upon. PWM 08/19/98
983
984 CURSOR cur_prem_intr( p_earn_id NUMBER
985 , p_process_order NUMBER ) IS
986 SELECT --+INDEX(pir pir_pk)
987 eltt.element_name,
988 eltv.hxt_premium_amount multiple,
989 hrw.element_type_id, --FORMS60
990 hrw.rowid hrwrowid,
991 eltv.hxt_earning_category,
992 eltv.hxt_premium_type
993 FROM hxt_pay_element_types_f_ddf_v eltv,
994 pay_element_types_f elt,
995 pay_element_types_f_tl eltt,
996 hxt_prem_interact_rules pir,
997 hxt_det_hours_worked hrw -- C421
998 WHERE hrw.tim_id = g_TIM_ID
999 AND hrw.date_worked = g_DATE_WORKED
1000 AND hrw.parent_id = g_ID -- same parent as base record
1001 AND pir.elt_prior_prem_id = hrw.element_type_id
1002 AND pir.pip_id = g_PIP_ID -- prem interaction policy
1003 AND pir.elt_earned_prem_id = p_earn_id -- driving premium
1004 AND g_DATE_WORKED between pir.effective_start_date
1005 and pir.effective_end_date
1006 AND pir.apply_prior_prem_yn = 'Y'
1007 AND pir.elt_prior_prem_id = elt.element_type_id -- element type
1008 AND g_DATE_WORKED between elt.effective_start_date
1009 and elt.effective_end_date
1010 AND eltt.element_type_id = elt.element_type_id
1011 AND eltt.language = userenv('LANG')
1012 AND eltv.element_type_id = elt.element_type_id
1013 AND g_DATE_WORKED between eltv.effective_start_date
1014 and eltv.effective_end_date
1015 AND eltv.hxt_processing_order < p_process_order -- ovt may exist already
1016 AND hrw.seqno > l_min_detail_seqno
1017 AND hrw.seqno < l_max_detail_seqno
1018 ORDER BY eltv.hxt_processing_order;
1019
1020 */
1021
1022 CURSOR get_min_detail_seqno IS
1023 SELECT seqno
1024 FROM hxt_det_hours_worked_f
1025 WHERE id = p_BASE_ID
1026 AND g_pay_session_date BETWEEN effective_start_date
1027 AND effective_end_date;
1028 --
1029 -- Get the maximum sequence number of the current detail row
1030 --
1031 CURSOR get_max_detail_seqno IS
1032 SELECT nvl(min(hrw.seqno),9999)
1033 FROM hxt_pay_element_types_f_ddf_v eltv
1034 ,pay_element_types_f elt
1035 ,hxt_det_hours_worked_f hrw
1036 WHERE hrw.tim_id = g_TIM_ID
1037 AND hrw.date_worked = g_DATE_WORKED
1038 AND hrw.parent_id = g_ID
1039 AND g_pay_session_date BETWEEN hrw.effective_start_date
1040 AND hrw.effective_end_date
1041 AND hrw.seqno > l_min_detail_seqno
1042 AND elt.element_type_id = hrw.element_type_id
1043 AND eltv.element_type_id = elt.element_type_id
1044 AND g_DATE_WORKED BETWEEN eltv.effective_start_date
1045 AND eltv.effective_end_date
1046 AND eltv.hxt_earning_category IN ('REG','OVT','ABS');
1047
1048 CURSOR cur_prem_intr( p_earn_id NUMBER
1049 , p_process_order NUMBER ) IS
1050 SELECT --+INDEX(pir pir_pk)
1051 eltt.element_name,
1052 eltv.hxt_premium_amount multiple,
1053 hrw.element_type_id, --FORMS60
1054 hrw.rowid hrwrowid,
1055 eltv.hxt_earning_category,
1056 eltv.hxt_premium_type
1057 FROM hxt_pay_element_types_f_ddf_v eltv,
1058 pay_element_types_f elt,
1059 pay_element_types_f_tl eltt,
1060 hxt_prem_interact_rules pir,
1061 hxt_det_hours_worked_f hrw -- C421
1062 WHERE hrw.tim_id = g_TIM_ID
1063 AND hrw.date_worked = g_DATE_WORKED
1064 AND hrw.parent_id = g_ID -- same parent as base record
1065 AND pir.elt_prior_prem_id = hrw.element_type_id
1066 AND pir.pip_id = g_PIP_ID -- prem interaction policy
1067 AND pir.elt_earned_prem_id = p_earn_id -- driving premium
1068 AND g_pay_session_date BETWEEN hrw.effective_start_date
1069 AND hrw.effective_end_date
1070 AND g_DATE_WORKED between pir.effective_start_date
1071 and pir.effective_end_date
1072 AND pir.apply_prior_prem_yn = 'Y'
1073 AND pir.elt_prior_prem_id = elt.element_type_id -- element type
1074 AND g_DATE_WORKED between elt.effective_start_date
1075 and elt.effective_end_date
1076 AND eltt.element_type_id = elt.element_type_id
1077 AND eltt.language = userenv('LANG')
1078 AND eltv.element_type_id = elt.element_type_id
1079 AND g_DATE_WORKED between eltv.effective_start_date
1080 and eltv.effective_end_date
1081 AND eltv.hxt_processing_order < p_process_order -- ovt may exist already
1082 AND hrw.seqno > l_min_detail_seqno
1083 AND hrw.seqno < l_max_detail_seqno
1084 ORDER BY eltv.hxt_processing_order;
1085
1086
1087
1088 /* CURSOR Get_Shift_Info (p_assignment_id NUMBER
1089 ,p_date_worked DATE ) IS
1090 SELECT aeiv.hxt_rotation_plan
1091 ,rts.tws_id
1092 ,hws.week_day
1093 ,hs.standard_start
1094 ,hs.standard_stop
1095 FROM hxt_shifts hs
1096 ,hxt_work_shifts hws
1097 ,hxt_per_aei_ddf_v aeiv
1098 ,hxt_rotation_schedules rts
1099 WHERE aeiv.assignment_id = p_ASSIGNMENT_ID
1100 AND p_DATE_WORKED between aeiv.effective_start_date
1101 and aeiv.effective_end_date
1102 AND rts.rtp_id = aeiv.hxt_rotation_plan
1103 AND rts.start_date = (SELECT MAX(start_date)
1104 FROM hxt_rotation_schedules
1105 WHERE rtp_id = rts.rtp_id
1106 AND start_date <= p_DATE_WORKED
1107 )
1108 AND hws.tws_id = rts.tws_id
1109 AND hws.week_day = to_char(p_DATE_WORKED,'DY')
1110 AND hws.sht_id = hs.id;
1111
1112
1113 lv_rotation_plan hxt_rotation_schedules.rtp_id%TYPE;
1114 lv_tws_id hxt_rotation_schedules.tws_id%TYPE;
1115 lv_week_day hxt_work_shifts.week_day%TYPE;
1116 lv_standard_start hxt_shifts.standard_start%TYPE;
1117 lv_standard_stop hxt_shifts.standard_stop%TYPE;
1118
1119 lv_elig_for_prem BOOLEAN := FALSE; */
1120
1121 -- Not needed.Declared in FOR loop.
1122 -- prem_intr_rec cur_prem_intr%ROWTYPE;
1123
1124 BEGIN
1125
1126 if g_debug then
1127 hr_utility.set_location('hxt_time_pay.Gen_Premiums',10);
1128 end if;
1129
1130
1131 -- Bug 7359347
1132 -- Setting session date to global variable
1133 IF g_pay_session_date IS NULL
1134 THEN
1135 g_pay_session_date := hxt_tim_col_util.return_session_date;
1136 END IF;
1137
1138 --Update location path with GEN_PREMIUMS function.
1139 l_location := p_location||':PREM';
1140 --Increment line seqno
1141 l_seqno := p_seqno + 10;
1142 if g_debug then
1143 hr_utility.trace('l_seqno :'||l_seqno);
1144 hr_utility.set_location('hxt_time_pay.Gen_Premiums',20);
1145 end if;
1146
1147 open get_min_detail_seqno;
1148 fetch get_min_detail_seqno into l_min_detail_seqno;
1149 if g_debug then
1150 hr_utility.trace('l_min_detail_seqno:'||l_min_detail_seqno);
1151 end if;
1152 close get_min_detail_seqno;
1153
1154 if g_debug then
1155 hr_utility.set_location('hxt_time_pay.Gen_Premiums',30);
1156 end if;
1157
1158 open get_max_detail_seqno;
1159 fetch get_max_detail_seqno into l_max_detail_seqno;
1160 if g_debug then
1161 hr_utility.trace('l_max_detail_seqno:'||l_max_detail_seqno);
1162 end if;
1163 close get_max_detail_seqno;
1164
1165 --Loop through premiums person is eligible for
1166 --in order of priority
1167
1168 if g_debug then
1169 hr_utility.set_location('hxt_time_pay.Gen_Premiums',40);
1170 end if;
1171
1172 <<elig_prem>>
1173
1174 if g_debug then
1175 hr_utility.set_location('hxt_time_pay.Gen_Premiums',50);
1176 hr_utility.trace('g_ASSIGNMENT_ID :'||g_ASSIGNMENT_ID);
1177 hr_utility.trace('g_DATE_WORKED :'||g_DATE_WORKED);
1178 hr_utility.trace('g_PEP_ID :'||g_PEP_ID);
1179 hr_utility.trace('p_element_type_id:'||p_element_type_id);
1180 hr_utility.trace('g_HOL_YN :'||g_HOL_YN);
1181 hr_utility.trace('p_sdf_id :'||p_sdf_id);
1182 hr_utility.trace('g_OSP_ID :'||g_OSP_ID);
1183 hr_utility.trace('p_sdovr_id :'||p_sdovr_id);
1184 hr_utility.trace('p_time_in :'||to_char(p_time_in,'DD-MON-YYYY HH24:MI:SS'));
1185 hr_utility.trace('p_time_out :'||to_char(p_time_out,'DD-MON-YYYY HH24:MI:SS'));
1186 end if;
1187 FOR elig_prem_rec IN cur_elig_prem
1188 LOOP
1189
1190 if g_debug then
1191 hr_utility.set_location('hxt_time_pay.Gen_Premiums',60);
1192 end if;
1193 /*
1194 -- Check if the hxt_earning_category is Shift Differential Override.
1195 -- If yes,then check whether the employee has worked other than their
1196 -- regular shift ,because the employee gets paid premium only for hours
1197 -- worked other than their regular shifts.
1198
1199 IF elig_prem_rec.hxt_earning_category <> 'SDF' THEN
1200 if g_debug then
1201 hr_utility.set_location('hxt_time_pay.Gen_Premiums',61);
1202 end if;
1203 lv_elig_for_prem := TRUE;
1204 END IF;
1205
1206 IF elig_prem_rec.hxt_earning_category = 'SDF' THEN
1207 if g_debug then
1208 hr_utility.set_location('hxt_time_pay.Gen_Premiums',62);
1209 end if;
1210
1211 open Get_Shift_Info( g_ASSIGNMENT_ID,g_DATE_WORKED );
1212 fetch Get_Shift_Info into lv_rotation_plan ,lv_tws_id ,lv_week_day
1213 ,lv_standard_start ,lv_standard_stop;
1214
1215 if g_debug then
1216 hr_utility.set_location('hxt_time_pay.Gen_Premiums',63);
1217 end if;
1218 close Get_Shift_Info;
1219
1220 if g_debug then
1221 hr_utility.trace('lv_rotation_plan :'||lv_rotation_plan);
1222 hr_utility.trace('lv_tws_id :'||lv_tws_id);
1223 hr_utility.trace('lv_week_day :'||lv_week_day);
1224 hr_utility.trace('lv_standard_start:'||lv_standard_start);
1225 hr_utility.trace('lv_standard_stop :'||lv_standard_stop);
1226 hr_utility.set_location('hxt_time_pay.Gen_Premiums',64);
1227
1228 hr_utility.trace('p_time_in :'||to_char(p_time_in,'HH24MI'));
1229 hr_utility.trace('p_time_out :'||to_char(p_time_out,'HH24MI'));
1230
1231 hr_utility.trace('p_time_in :'||to_number(to_char(p_time_in,'HH24MI')));
1232 hr_utility.trace('p_time_out:'||to_number(to_char(p_time_out,'HH24MI')));
1233 end if;
1234 IF lv_standard_start = 0 AND lv_standard_stop = 0
1235 AND g_osp_id is NOT NULL THEN -- for off shift days
1236
1237 if g_debug then
1238 hr_utility.set_location('hxt_time_pay.Gen_Premiums',64.1);
1239 end if;
1240 lv_elig_for_prem := TRUE; -- Thus for an off day shift the
1241 -- shift prem gets paid too
1242 -- along with the off shift premium
1243 END IF;
1244
1245 -- now the logic for Working days
1246 -- Checking for the time in and outs for which the employee is
1247 -- eligible for the Shift Differential Premium
1248
1249 -- IF shift diff Override is defined for the day but is out of
1250 -- range of time_in and time_out OR there are no shift Overrides for
1251 -- the day and the shift diff policy is applicable to time_in and
1252 -- time_out
1253 IF (p_sdf_id <> g_sdovr_id OR g_sdovr_id is NULL)
1254 AND to_number(to_char(p_time_in,'HH24MI')) >= lv_standard_start
1255 AND to_number(to_char(p_time_out,'HH24MI')) <= lv_standard_stop THEN
1256
1257 -- i.e., If shift differential policy is applicable to time_in and
1258 -- time_outs
1259 if g_debug then
1260 hr_utility.set_location('hxt_time_pay.Gen_Premiums',64.2);
1261 end if;
1262 lv_elig_for_prem := TRUE;
1263
1264 ELSIF p_sdf_id = g_sdovr_id THEN
1265
1266 -- i.e.,IF the shift differential override is applicable to the time_in and
1267 -- time_outs ,then p_sdf_id has been set to g_sdovr_id n hxt_time_summary.
1268 -- gen_details ,so that the cursor cur_elig_prem returns a row and the
1269 -- logic gets called to insert the data into hxt_det_hours_worked
1270
1271 if g_debug then
1272 hr_utility.set_location('hxt_time_pay.Gen_Premiums',64.3);
1273 end if;
1274 lv_elig_for_prem := TRUE;
1275 END IF;
1276
1277 if g_debug then
1278 hr_utility.set_location('hxt_time_pay.Gen_Premiums',67);
1279 end if;
1280 END IF;
1281
1282 IF lv_elig_for_prem = TRUE THEN
1283
1284 if g_debug then
1285 hr_utility.set_location('hxt_time_pay.Gen_Premiums',68);
1286 end if;
1287 */
1288 -- Default override multiple to override multiple in summary hour
1289 -- record (usually null)
1290 l_mult := g_RATE_MULTIPLE;
1291 l_rate := g_HOURLY_RATE; -- ORA131
1292 if g_debug then
1293 hr_utility.trace('l_mult :'||l_mult);
1294 hr_utility.trace('l_rate :'||l_rate);
1295 end if;
1296 --
1297 -- Default the hours worked zero if prem type is fixed -- SIR092
1298 --
1299 IF elig_prem_rec.hxt_premium_type = 'FIXED' and p_hours <> 0
1300 THEN
1301 IF g_debug
1302 THEN
1303 hr_utility.set_location ('hxt_time_pay.Gen_Premiums', 70);
1304 END IF;
1305
1306 l_hours := 0;
1307 l_time_in := NULL;
1308 l_time_out := NULL;
1309
1310 IF g_debug
1311 THEN
1312 hr_utility.TRACE ('l_hours :' || l_hours);
1313 hr_utility.TRACE ('l_time_in:' || l_time_in);
1314 hr_utility.TRACE ('l_time_out:' || l_time_out);
1315 END IF;
1316 ELSIF elig_prem_rec.hxt_premium_type = 'FIXED' and p_hours = 0
1317 THEN
1318 return 0;
1319 ELSE
1320 IF g_debug
1321 THEN
1322 hr_utility.set_location ('hxt_time_pay.Gen_Premiums', 80);
1323 END IF;
1324
1325 l_hours := p_hours;
1326 l_time_in := p_time_in;
1327 l_time_out := p_time_out;
1328
1329 IF g_debug
1330 THEN
1331 hr_utility.TRACE ('l_hours :' || l_hours);
1332 hr_utility.TRACE ( 'l_time_in:'
1333 || TO_CHAR (l_time_in,
1334 'dd-mon-yyyy hh24:mi:ss'
1335 )
1336 );
1337 hr_utility.TRACE ( 'l_time_out:'
1338 || TO_CHAR (l_time_out,
1339 'dd-mon-yyyy hh24:mi:ss'
1340 )
1341 );
1342 END IF;
1343 END IF;
1344
1345 -- Check if override multiple is applicable
1346 IF elig_prem_rec.hxt_premium_type = 'FACTOR' THEN
1347 if g_debug then
1348 hr_utility.set_location('hxt_time_pay.Gen_Premiums',90);
1349 end if;
1350
1351 -- Step through the premium detail records of type factor that
1352 -- were already inserted into the hours worked table - all are
1353 -- lower priority than the current premium. By matching up against
1354 -- the interaction rules, we can deal only with the interacting
1355 -- premiums.
1356 -- Derive override multiple
1357
1358 <<prem_intr>>
1359
1360 if g_debug then
1361 hr_utility.set_location('hxt_time_pay.Gen_Premiums',100);
1362 end if;
1363
1364 FOR prem_intr_rec IN cur_prem_intr(elig_prem_rec.elt_premium_id
1365 ,elig_prem_rec.hxt_processing_order)
1366 LOOP
1367 if g_debug then
1368 hr_utility.set_location('hxt_time_pay.Gen_Premiums',110);
1369 end if;
1370
1371 IF prem_intr_rec.hxt_premium_type = 'FACTOR' THEN --SIR190
1372 if g_debug then
1373 hr_utility.set_location('hxt_time_pay.Gen_Premiums',120);
1374 end if;
1375
1376 -- SIR103 We must strip off the 1 from overtime premium
1377 -- so that it will be treated like any other premium in the
1378 -- interaction calculations. PWM
1379
1380 IF elig_prem_rec.hxt_earning_category = 'OVT' THEN --SIR103 ORA131
1381 if g_debug then
1382 hr_utility.set_location('hxt_time_pay.Gen_Premiums',130);
1383 end if;
1384 l_mult := nvl(g_RATE_MULTIPLE
1385 ,elig_prem_rec.hxt_premium_amount) -1 ; --SIR103
1386 if g_debug then
1387 hr_utility.trace('l_mult :'||l_mult);
1388 end if;
1389 ELSE --SIR103
1390 if g_debug then
1391 hr_utility.set_location('hxt_time_pay.Gen_Premiums',140);
1392 end if;
1393 l_mult := nvl(g_RATE_MULTIPLE
1394 ,elig_prem_rec.hxt_premium_amount) ; --SIR103
1395 if g_debug then
1396 hr_utility.trace('l_mult :'||l_mult);
1397 end if;
1398 END IF; --SIR103
1399
1400 -- SIR103 We must strip off the 1 from overtime premium
1401 -- so that it will be treated like any other premium in
1402 -- the interaction calculations. PWM
1403
1404 IF prem_intr_rec.hxt_earning_category = 'OVT' THEN --SIR103
1405 if g_debug then
1406 hr_utility.set_location('hxt_time_pay.Gen_Premiums',150);
1407 end if;
1408 prem_intr_rec.multiple := prem_intr_rec.multiple -1 ;--SIR103
1409 END IF; --SIR103
1410 --
1411 -- ORACLE #464850 & #465434 don't add 1 for overtime premiums
1412 --
1413 -- If any matches are found, we switch from a null value to the
1414 -- percentage portion of the value. For example, if the factor
1415 -- is 1.15, then the premium is 15% of the base. We then adjust
1416 -- the multiple by applying the factors of the premium earned
1417 -- previously.
1418
1419 if g_debug then
1420 hr_utility.set_location('hxt_time_pay.Gen_Premiums',160);
1421 end if;
1422 l_mult := nvl(prem_intr_rec.multiple,0) + l_mult * nvl(prem_intr_rec.multiple,1); --SIR103
1423 if g_debug then
1424 hr_utility.trace('l_mult :'||l_mult);
1425 end if;
1426
1427 -- SIR103 We must add back the 1 that was stripped off before
1428 -- the calculation. PWM
1429
1430 IF prem_intr_rec.hxt_earning_category = 'OVT' THEN
1431 if g_debug then
1432 hr_utility.set_location('hxt_time_pay.Gen_Premiums',170);
1433 end if;
1434 l_mult := l_mult + 1 ;
1435 if g_debug then
1436 hr_utility.trace('l_mult :'||l_mult);
1437 end if;
1438 END IF;
1439
1440 ELSE
1441 if g_debug then
1442 hr_utility.set_location('hxt_time_pay.Gen_Premiums',180);
1443 end if;
1444 -- Premium was RATE or FIXED, but not FACTOR so don't
1445 -- change the multiple
1446
1447 IF elig_prem_rec.hxt_earning_category = 'OVT' THEN
1448 if g_debug then
1449 hr_utility.set_location('hxt_time_pay.Gen_Premiums',190);
1450 end if;
1451 l_mult := nvl(g_RATE_MULTIPLE
1452 ,elig_prem_rec.hxt_premium_amount) -1 ;
1453 if g_debug then
1454 hr_utility.trace('l_mult :'||l_mult);
1455 end if;
1456 ELSE
1457 if g_debug then
1458 hr_utility.set_location('hxt_time_pay.Gen_Premiums',200);
1459 end if;
1460 l_mult := nvl(g_RATE_MULTIPLE
1461 ,elig_prem_rec.hxt_premium_amount);
1462 if g_debug then
1463 hr_utility.trace('l_mult :'||l_mult);
1464 end if;
1465 END IF;
1466
1467 if g_debug then
1468 hr_utility.set_location('hxt_time_pay.Gen_Premiums',210);
1469 end if;
1470 l_rate := prem_intr_rec.multiple + (l_mult * prem_intr_rec.multiple);
1471 l_mult := NULL;
1472 if g_debug then
1473 hr_utility.trace('l_rate :'||l_rate);
1474 hr_utility.trace('l_mult :'||l_mult);
1475 end if;
1476 END IF;
1477
1478 if g_debug then
1479 hr_utility.set_location('hxt_time_pay.Gen_Premiums',220);
1480 end if;
1481 hrw_rowid := prem_intr_rec.hrwrowid;
1482 if g_debug then
1483 hr_utility.trace('hrw_rowid :'||hrw_rowid);
1484 end if;
1485 END LOOP prem_intr;
1486
1487 if g_debug then
1488 hr_utility.set_location('hxt_time_pay.Gen_Premiums',240);
1489 end if;
1490 END IF; -- Factor premium
1491
1492 --
1493 -- ORA131 MOD START(all code between start and end mark was added forORA131)
1494 --
1495 if g_debug then
1496 hr_utility.set_location('hxt_time_pay.Gen_Premiums',250);
1497 end if;
1498 --
1499 -- Check if override rate is applicable
1500 --
1501 IF elig_prem_rec.hxt_premium_type = 'RATE' THEN
1502 if g_debug then
1503 hr_utility.set_location('hxt_time_pay.Gen_Premiums',260);
1504 end if;
1505 -- Step through the premium detail records of type rate that
1506 -- were already inserted into the hours worked table - all are
1507 -- lower priority than the current premium. By matching up against
1508 -- the interaction rules, we can deal only with the interacting
1509 -- premiums.
1510 -- Derive override rate
1511
1512 <<prem_intr>>
1513
1514 if g_debug then
1515 hr_utility.set_location('hxt_time_pay.Gen_Premiums',270);
1516 end if;
1517
1518 FOR prem_intr_rec IN cur_prem_intr(elig_prem_rec.elt_premium_id
1519 ,elig_prem_rec.hxt_processing_order)
1520 LOOP
1521 if g_debug then
1522 hr_utility.set_location('hxt_time_pay.Gen_Premiums',280);
1523 end if;
1524
1525 -- SIR190 No calculations are needed for Rate premiums
1526 -- since these cannot interact with another premium,
1527 -- we simply write these out. PWM 08/20/98
1528 -- Reset override rate to override in summary hour record
1529 -- (usually null)
1530
1531 l_rate := nvl(g_HOURLY_RATE
1532 ,elig_prem_rec.hxt_premium_amount); --SIR190
1533 if g_debug then
1534 hr_utility.trace('l_rate :'||l_rate);
1535 hr_utility.set_location('hxt_time_pay.Gen_Premiums',290);
1536 end if;
1537 -- The new rate is derived by multiplying the premium amount
1538 -- by the multiple of the interactive premium (which is most
1539 -- likely overtime)
1540
1541 -- SIR103 We must strip off the 1 from overtime premium so that it
1542 -- will be treated like any other premium in the interaction
1543 -- calculations. PWM
1544
1545 -- SIR103 We must add back the 1 that was stripped off before the
1546 -- calculation. PWM
1547
1548 hrw_rowid := prem_intr_rec.hrwrowid;
1549 if g_debug then
1550 hr_utility.trace('hrw_rowid :'||hrw_rowid);
1551 hr_utility.set_location('hxt_time_pay.Gen_Premiums',300);
1552 end if;
1553 END LOOP prem_intr;
1554
1555 if g_debug then
1556 hr_utility.set_location('hxt_time_pay.Gen_Premiums',320);
1557 end if;
1558 END IF; -- Rate premium
1559
1560 -- Check if OVT premium and override multiple was derived
1561 IF (l_mult is not null) THEN
1562 if g_debug then
1563 hr_utility.set_location('hxt_time_pay.Gen_Premiums',330);
1564 end if;
1565 -- Update existing base-hour detail record
1566
1567 BEGIN
1568
1569 if g_debug then
1570 hr_utility.set_location('hxt_time_pay.Gen_Premiums',340);
1571 end if;
1572 UPDATE hxt_det_hours_worked_f hrw
1573 SET rate_multiple = l_mult
1574 WHERE hrw.rowid = hrw_rowid;
1575 EXCEPTION
1576 WHEN OTHERS THEN
1577 if g_debug then
1578 hr_utility.set_location('hxt_time_pay.Gen_Premiums',350);
1579 end if;
1580 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1581 l_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location,'', sqlerrm);
1582 --2278400 l_error_code := call_gen_error(l_location,'', sqlerrm);
1583 if g_debug then
1584 hr_utility.trace('l_error_code :'||l_error_code);
1585 end if;
1586 END;
1587 if g_debug then
1588 hr_utility.set_location('hxt_time_pay.Gen_Premiums',360);
1589 end if;
1590 END IF ;
1591 --
1592 -- ORA131 MOD START (all code between start and end mark was added for ORA131)
1593 --
1594 IF (l_rate is not null) THEN
1595 if g_debug then
1596 hr_utility.set_location('hxt_time_pay.Gen_Premiums',370);
1597 end if;
1598
1599 -- Update existing base-hour detail record
1600 BEGIN
1601
1602 if g_debug then
1603 hr_utility.set_location('hxt_time_pay.Gen_Premiums',380);
1604 end if;
1605 UPDATE hxt_det_hours_worked_f hrw
1606 SET hourly_rate = l_rate
1607 WHERE hrw.rowid = hrw_rowid;
1608 EXCEPTION
1609 WHEN OTHERS THEN
1610 if g_debug then
1611 hr_utility.set_location('hxt_time_pay.Gen_Premiums',390);
1612 end if;
1613 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1614 l_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location,'', sqlerrm);
1615 --2278400 l_error_code := call_gen_error(l_location,'', sqlerrm);
1616 if g_debug then
1617 hr_utility.trace('l_error_code :'||l_error_code);
1618 end if;
1619 END;
1620
1621 if g_debug then
1622 hr_utility.set_location('hxt_time_pay.Gen_Premiums',400);
1623 end if;
1624 END IF ;
1625
1626 IF elig_prem_rec.hxt_earning_category <> 'OVT' THEN
1627
1628 if g_debug then
1629 hr_utility.set_location('hxt_time_pay.Gen_Premiums',410);
1630 end if;
1631 get_retro_fields( g_tim_id
1632 ,HXT_TIME_COLLECTION.g_batch_name
1633 ,HXT_TIME_COLLECTION.g_batch_ref
1634 ,l_pay_status
1635 ,l_pa_status
1636 ,l_retro_batch_id
1637 ,l_error_status
1638 ,l_sqlerrm);
1639
1640 IF l_error_status = 0 THEN
1641
1642 if g_debug then
1643 hr_utility.set_location('hxt_time_pay.Gen_Premiums',420);
1644 end if;
1645 l_costable_type := HXT_UTIL.get_costable_type(
1646 elig_prem_rec.elt_premium_id
1647 ,g_DATE_WORKED
1648 ,g_ASSIGNMENT_ID);
1649 if g_debug then
1650 hr_utility.trace('l_costable_type :'||l_costable_type);
1651 end if;
1652
1653 IF l_costable_type in ('C','F') THEN
1654 if g_debug then
1655 hr_utility.set_location('hxt_time_pay.Gen_Premiums',430);
1656 end if;
1657 l_ffv_cost_center_id := g_FFV_COST_CENTER_ID;
1658 if g_debug then
1659 hr_utility.trace('l_ffv_cost_center_id :'||l_ffv_cost_center_id);
1660 end if;
1661 ELSE
1662 if g_debug then
1663 hr_utility.set_location('hxt_time_pay.Gen_Premiums',440);
1664 end if;
1665 l_ffv_cost_center_id := NULL;
1666 if g_debug then
1667 hr_utility.trace('l_ffv_cost_center_id :'||l_ffv_cost_center_id);
1668 end if;
1669 END IF;
1670 --
1671 -- ORACLE Bug #464850 & #465434 Be sure the detail for time records
1672 -- displays the premium amount for Fixed, Rate and Factor premium types.
1673 --
1674 BEGIN
1675
1676 if g_debug then
1677 hr_utility.set_location('hxt_time_pay.Gen_Premiums',450);
1678 end if;
1679
1680
1681 /* INSERT into hxt_det_hours_worked_f
1682 (ID,PARENT_ID,TIM_ID,
1683 DATE_WORKED,ASSIGNMENT_ID,
1684 HOURS,TIME_IN,TIME_OUT,
1685 ELEMENT_TYPE_ID,FCL_EARN_REASON_CODE,
1686 FFV_COST_CENTER_ID,
1687 TAS_ID,LOCATION_ID,SHT_ID,
1688 HRW_COMMENT,FFV_RATE_CODE_ID,
1689 RATE_MULTIPLE,HOURLY_RATE,AMOUNT,
1690 FCL_TAX_RULE_CODE,SEPARATE_CHECK_FLAG,
1691 SEQNO,CREATED_BY,CREATION_DATE,
1692 LAST_UPDATED_BY,LAST_UPDATE_DATE,
1693 LAST_UPDATE_LOGIN,
1694 EFFECTIVE_START_DATE,
1695 EFFECTIVE_END_DATE,
1696 PROJECT_ID,
1697 PAY_STATUS,
1698 PA_STATUS,
1699 RETRO_BATCH_ID
1700 --GROUP_ID
1701 )
1702 VALUES
1703 (hxt_seqno.nextval, g_ID, g_TIM_ID,
1704 g_DATE_WORKED, g_ASSIGNMENT_ID,
1705 l_hours, -- SIR092
1706 p_time_in, p_time_out,
1707 elig_prem_rec.elt_premium_id, '',
1708 l_ffv_cost_center_id,
1709 g_TAS_ID, g_LOCATION_ID, g_SHT_ID,
1710 g_HRW_COMMENT, g_FFV_RATE_CODE_ID,
1711 decode(elig_prem_rec.hxt_premium_type, 'FACTOR',
1712 NVL(g_RATE_MULTIPLE, elig_prem_rec.hxt_premium_amount),
1713 g_RATE_MULTIPLE),
1714 decode(elig_prem_rec.hxt_premium_type,
1715 'RATE', NVL(g_HOURLY_RATE, elig_prem_rec.hxt_premium_amount),
1716 g_HOURLY_RATE),
1717 decode(elig_prem_rec.hxt_premium_type,
1718 'FIXED', NVL(g_AMOUNT, elig_prem_rec.hxt_premium_amount),
1719 g_AMOUNT),
1720 g_FCL_TAX_RULE_CODE, g_SEPARATE_CHECK_FLAG,
1721 l_seqno, g_CREATED_BY, g_CREATION_DATE,
1722 g_LAST_UPDATED_BY, g_LAST_UPDATE_DATE,
1723 g_LAST_UPDATE_LOGIN,
1724 g_EFFECTIVE_START_DATE,
1725 g_EFFECTIVE_END_DATE,
1726 g_project_id,
1727 l_pay_status,
1728 l_pa_status,
1729 l_retro_batch_id
1730 --g_group_id
1731 ); */
1732
1733
1734
1735 /* Call dml to insert hours */
1736
1737 SELECT hxt_seqno.nextval,
1738 decode(elig_prem_rec.hxt_premium_type, 'FACTOR',
1739 NVL(g_RATE_MULTIPLE, elig_prem_rec.hxt_premium_amount),
1740 g_RATE_MULTIPLE),
1741 decode(elig_prem_rec.hxt_premium_type, 'RATE', NVL(g_HOURLY_RATE,
1742 elig_prem_rec.hxt_premium_amount), g_HOURLY_RATE),
1743 decode(elig_prem_rec.hxt_premium_type, 'FIXED', NVL(g_AMOUNT,
1744 elig_prem_rec.hxt_premium_amount), g_AMOUNT)
1745 INTO l_id,
1746 l_rate_multiple,
1747 l_hourly_rate,
1748 l_amount
1749 FROM dual;
1750
1751 if g_debug then
1752 hr_utility.trace('l_id :'||l_id);
1753 hr_utility.trace('l_rate_multiple :'||l_rate_multiple);
1754 hr_utility.trace('l_hourly_rate :'||l_hourly_rate);
1755 hr_utility.trace('l_amount :'||l_amount);
1756 hr_utility.set_location('hxt_time_pay.Gen_Premiums',460);
1757 end if;
1758 hxt_dml.insert_HXT_DET_HOURS_WORKED(
1759 p_rowid => l_rowid,
1760 p_id => l_id,
1761 p_parent_id => g_id,
1762 p_tim_id => g_tim_id,
1763 p_date_worked => g_date_worked,
1764 p_assignment_id => g_assignment_id,
1765 p_hours => l_hours,
1766 p_time_in => p_time_in,
1767 p_time_out => p_time_out,
1768 p_element_type_id => elig_prem_rec.elt_premium_id,
1769 p_fcl_earn_reason_code => NULL,
1770 p_ffv_cost_center_id => l_ffv_cost_center_id,
1771 p_ffv_labor_account_id => NULL,
1772 p_tas_id => g_tas_id,
1773 p_location_id => g_location_id,
1774 p_sht_id => g_sht_id,
1775 p_hrw_comment => g_hrw_comment,
1776 p_ffv_rate_code_id => g_ffv_rate_code_id,
1777 p_rate_multiple => l_rate_multiple,
1778 p_hourly_rate => l_hourly_rate,
1779 p_amount => l_amount,
1780 p_fcl_tax_rule_code => g_fcl_tax_rule_code,
1781 p_separate_check_flag => g_separate_check_flag,
1782 p_seqno => l_seqno,
1783 p_created_by => g_created_by,
1784 p_creation_date => g_creation_date,
1785 p_last_updated_by => g_last_updated_by,
1786 p_last_update_date => g_last_update_date,
1787 p_last_update_login => g_last_update_login,
1788 p_actual_time_in => NULL,
1789 p_actual_time_out => NULL,
1790 p_effective_start_date => g_effective_start_date,
1791 p_effective_end_date => g_effective_end_date,
1792 p_project_id => g_project_id,
1793 p_job_id => NULL,
1794 p_earn_pol_id => NULL,
1795 p_retro_batch_id => l_retro_batch_id,
1796 p_pa_status => l_pa_status,
1797 p_pay_status => l_pay_status,
1798 --p_group_id => g_group_id,
1799 p_object_version_number => l_object_version_number,
1800 p_STATE_NAME => g_STATE_NAME,
1801 p_COUNTY_NAME => g_COUNTY_NAME,
1802 p_CITY_NAME => g_CITY_NAME,
1803 p_ZIP_CODE => g_ZIP_CODE);
1804
1805 if g_debug then
1806 hr_utility.set_location('hxt_time_pay.Gen_Premiums',470);
1807 end if;
1808
1809
1810 EXCEPTION
1811 WHEN OTHERS THEN
1812 if g_debug then
1813 hr_utility.set_location('hxt_time_pay.Gen_Premiums',480);
1814 end if;
1815 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1816 l_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location,'', sqlerrm);
1817 --2278400 l_error_code := call_gen_error(l_location,'', sqlerrm);
1818 if g_debug then
1819 hr_utility.trace('l_error_code :'||l_error_code);
1820 end if;
1821 END;
1822 ELSE
1823 if g_debug then
1824 hr_utility.set_location('hxt_time_pay.Gen_Premiums',490);
1825 end if;
1826 FND_MESSAGE.SET_NAME('HXT','HXT_39421_GET_RETRO_ERR');
1827 l_error_code := call_hxthxc_gen_error('HXT','HXT_39421_GET_RETRO_ERR',NULL,p_location||':INS', '',sqlerrm);
1828 --2278400 l_error_code := call_gen_error(p_location||':INS', '',sqlerrm);
1829 if g_debug then
1830 hr_utility.trace('l_error_code :'||l_error_code);
1831 end if;
1832 END IF;
1833
1834 if g_debug then
1835 hr_utility.set_location('hxt_time_pay.Gen_Premiums',500);
1836 end if;
1837 -- Increment line sequence number
1838 l_seqno := l_seqno + 10;
1839 if g_debug then
1840 hr_utility.trace('l_seqno :'||l_seqno);
1841 end if;
1842 END IF;
1843
1844 if g_debug then
1845 hr_utility.set_location('hxt_time_pay.Gen_Premiums',505);
1846 end if;
1847 -- END IF; -- END IF lv_elig_for_prem := TRUE
1848 if g_debug then
1849 hr_utility.set_location('hxt_time_pay.Gen_Premiums',510);
1850 end if;
1851 END LOOP elig_prem;
1852
1853 if g_debug then
1854 hr_utility.set_location('hxt_time_pay.Gen_Premiums',520);
1855 end if;
1856 RETURN l_error_code;
1857
1858 EXCEPTION
1859 WHEN OTHERS THEN
1860 if g_debug then
1861 hr_utility.set_location('hxt_time_pay.Gen_Premiums',530);
1862 end if;
1863 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1864 RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location,'', sqlerrm);
1865 --2278400 RETURN call_gen_error(l_location,'', sqlerrm);
1866 END; -- gen_premiums
1867 --
1868
1869 --
1870 FUNCTION local_pay( p_hours_to_pay IN NUMBER,
1871 p_pay_element_type_id IN NUMBER,
1872 p_time_in IN DATE,
1873 p_time_out IN DATE) RETURN NUMBER IS
1874
1875 -- Returns 0 for normal 2 for errors
1876 -- Calls insert hours and gen premiums with times, hours, etc
1877
1878 -- Bug 7359347
1879 -- Changed the below cursor to use base table instead of view.
1880 /*
1881 CURSOR next_seq_cur IS
1882 SELECT nvl(max(seqno),0) next_seq
1883 FROM hxt_det_hours_worked --C421
1884 WHERE parent_id = g_id
1885 AND tim_id = g_tim_id
1886 AND date_worked = g_date_worked;
1887 */
1888 CURSOR next_seq_cur IS
1889 SELECT nvl(max(seqno),0) next_seq
1890 FROM hxt_det_hours_worked_f --C421
1891 WHERE parent_id = g_id
1892 AND tim_id = g_tim_id
1893 AND g_pay_session_date BETWEEN effective_start_date
1894 AND effective_end_date
1895 AND date_worked = g_date_worked;
1896
1897
1898 new_record_id NUMBER;
1899 pay_return_code NUMBER;
1900 next_seq NUMBER;
1901 override_sd_id NUMBER;
1902
1903 BEGIN
1904
1905 if g_debug then
1906 hr_utility.set_location('hxt_time_pay.local_pay',10);
1907 end if;
1908
1909
1910 -- Bug 7359347
1911 -- Setting session date
1912 IF g_pay_session_date IS NULL
1913 THEN
1914 g_pay_session_date := hxt_tim_col_util.return_session_date;
1915 END IF;
1916
1917 -- Get next sequence number for SEQNO.
1918 OPEN next_seq_cur;
1919 FETCH next_seq_cur INTO next_seq;
1920 if g_debug then
1921 hr_utility.trace('next_seq :'||next_seq);
1922 hr_utility.set_location('hxt_time_pay.local_pay',20);
1923 end if;
1924 IF next_seq_cur%NOTFOUND THEN
1925 if g_debug then
1926 hr_utility.set_location('hxt_time_pay.local_pay',30);
1927 end if;
1928 FND_MESSAGE.SET_NAME('HXT','HXT_39290_NO_SEQ_NO_F_HRS_WKED');
1929 RETURN call_hxthxc_gen_error('HXT','HXT_39290_NO_SEQ_NO_F_HRS_WKED',NULL,location, '');
1930 --RETURN call_gen_error(location, '');
1931 END IF;
1932
1933 if g_debug then
1934 hr_utility.set_location('hxt_time_pay.local_pay',40);
1935 end if;
1936 next_seq := next_seq + 10;
1937 if g_debug then
1938 hr_utility.trace('next_seq :'||next_seq);
1939 end if;
1940
1941 CLOSE next_seq_cur;
1942
1943 if g_debug then
1944 hr_utility.trace('p_hours_to_pay :'||p_hours_to_pay);
1945 hr_utility.trace('p_time_in :'||p_time_in);
1946 hr_utility.trace('p_time_out :'||p_time_out);
1947 hr_utility.trace('p_pay_element_type_id :'||p_pay_element_type_id);
1948 hr_utility.trace('next_seq :'||next_seq);
1949 hr_utility.trace('location :'||location);
1950 end if;
1951 -- Call function to insert record in to hours worked table.
1952 if g_debug then
1953 hr_utility.set_location('hxt_time_pay.local_pay',50);
1954 end if;
1955 insert_hrs(pay_return_code,
1956 new_record_id,
1957 p_hours_to_pay,
1958 p_time_in,
1959 p_time_out,
1960 p_pay_element_type_id,
1961 next_seq,
1962 location);
1963
1964 if g_debug then
1965 hr_utility.trace('pay_return_code :'||pay_return_code);
1966 hr_utility.trace('new_record_id :'||new_record_id);
1967 hr_utility.set_location('hxt_time_pay.local_pay',60);
1968 end if;
1969 -- Now generate all premiums for the record just inserted if
1970 -- premiums are to be paid.
1971 if g_debug then
1972 hr_utility.trace('g_pep_id :'||g_pep_id);
1973 hr_utility.trace('pay_return_code :'||pay_return_code);
1974 hr_utility.trace('g_sdovr_id :'||g_sdovr_id);
1975 hr_utility.trace('g_sdf_id :'||g_sdf_id);
1976 end if;
1977 IF g_pep_id IS NOT NULL AND pay_return_code = 0 THEN
1978 if g_debug then
1979 hr_utility.set_location('hxt_time_pay.local_pay',70);
1980 end if;
1981 -- Commented out this and passing both sdf_id and sdovr_id to GEN_PREMIUMS
1982 -- because we need both the values to determine which gets applied to the
1983 -- incoming time_in and time_outs
1984 -- override_sd_id := NVL(g_sdovr_id, g_sdf_id);
1985 if g_debug then
1986 hr_utility.trace('override_sd_id :'||override_sd_id);
1987
1988 hr_utility.trace('g_sdf_id :'||g_sdf_id);
1989 hr_utility.trace('g_sdovr_id:'||g_sdovr_id);
1990 end if;
1991 pay_return_code := gen_premiums(g_sdf_id,
1992 g_sdovr_id,
1993 location,
1994 new_record_id,
1995 p_hours_to_pay,
1996 p_time_in,
1997 p_time_out,
1998 p_pay_element_type_id,
1999 next_seq);
2000 if g_debug then
2001 hr_utility.set_location('hxt_time_pay.local_pay',80);
2002 end if;
2003 END IF;
2004 if g_debug then
2005 hr_utility.set_location('hxt_time_pay.local_pay',90);
2006 end if;
2007 RETURN pay_return_code;
2008 EXCEPTION
2009 WHEN OTHERS THEN
2010 if g_debug then
2011 hr_utility.set_location('hxt_time_pay.local_pay',100);
2012 end if;
2013 FND_MESSAGE.SET_NAME('HXT','HXT_39273_OR_ERR_IN_PAY_MDLE');
2014 RETURN call_hxthxc_gen_error('HXT','HXT_39273_OR_ERR_IN_PAY_MDLE',NULL,location, '', sqlerrm);
2015 -- RETURN call_gen_error(location, '', sqlerrm);
2016 END; --local_pay
2017
2018
2019
2020 -- Bug 8855103
2021 -- New function added to check whether this element is configured
2022 -- for absences.
2023 FUNCTION check_abs_elements( p_element_type_id IN NUMBER)
2024 RETURN NUMBER
2025 IS
2026
2027 CURSOR get_abs_elem
2028 IS SELECT 1
2029 FROM hxc_absence_type_elements
2030 WHERE element_type_id = p_element_type_id;
2031 l_element NUMBER;
2032
2033 BEGIN
2034
2035 IF g_abstab.EXISTS(p_element_type_id)
2036 THEN
2037 RETURN g_abstab(p_element_type_id);
2038 ELSE
2039 OPEN get_abs_elem;
2040 FETCH get_abs_elem INTO l_element;
2041 CLOSE get_abs_elem;
2042
2043 IF l_element = 1
2044 THEN
2045 g_abstab(p_element_type_id) := 1;
2046 RETURN 1;
2047 ELSE
2048 g_abstab(p_element_type_id) := 0;
2049 RETURN 0;
2050 END IF;
2051 END IF;
2052
2053 END check_abs_elements ;
2054
2055
2056
2057 BEGIN
2058
2059 g_debug :=hr_utility.debug_enabled;
2060 if g_debug then
2061 hr_utility.set_location('hxt_time_pay.pay',10);
2062 end if;
2063
2064 -- Bug 8855103
2065 -- Below construct added to throw error when
2066 -- configured absence type is included in OTLR explosion.
2067 IF g_debug
2068 THEN
2069 hr_utility.trace('ABS:g_element_type_id :'||g_element_type_id);
2070 END IF;
2071
2072 l_abs := check_abs_elements(g_element_type_id);
2073
2074 hr_utility.trace('ABS: l_abs :'||l_abs);
2075
2076 IF l_abs = 1
2077 THEN
2078 FND_MESSAGE.SET_NAME('HXC','HXC_HXT_ABS_NO_OTLR');
2079 RETURN call_hxthxc_gen_error('HXC','HXC_HXT_ABS_NO_OTLR',NULL,location||':INS', FND_MESSAGE.GET, NULL);
2080 END IF;
2081
2082
2083 RETURN local_pay(g_hours,
2084 g_element_type_id,
2085 g_time_in,
2086 g_time_out);
2087 if g_debug then
2088 hr_utility.set_location('hxt_time_pay.pay',20);
2089 end if;
2090
2091 EXCEPTION
2092 WHEN OTHERS THEN
2093 if g_debug then
2094 hr_utility.set_location('hxt_time_pay.pay',30);
2095 end if;
2096 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
2097 RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm);
2098 --2278400 RETURN call_gen_error(location, '', sqlerrm);
2099 END; -- END pay
2100
2101 --begin
2102
2103 END; -- package hxt_time_pay
[Home] [Help]