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