[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_FF_UDFS
Source
1 PACKAGE BODY pay_mx_ff_udfs AS
2 /* $Header: pymxudfs.pkb 120.16.12010000.4 2008/08/20 00:25:41 nragavar ship $ */
3
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
8 * Chertsey, England. *
9 * *
10 * All rights reserved. *
11 * *
12 * This material has been provided pursuant to an agreement *
13 * containing restrictions on its use. The material is also *
14 * protected by copyright law. No part of this material may *
15 * be copied or distributed, transmitted or transcribed, in *
16 * any form or by any means, electronic, mechanical, magnetic, *
17 * manual, or otherwise, or disclosed to third parties without *
18 * the express written permission of Oracle Corporation UK Ltd, *
19 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
20 * England. *
21 * *
22 ******************************************************************
23
24 Change List
25 -----------
26 Date Name Vers Bug No Description
27 ----------- ---------- ----- ------- -----------------------------------
28 15-Nov-2004 vpandya 115.0 Created.
29 28-Nov-2004 vpandya 115.1 Changed pkg name to pay_mx_ff..
30 from hr_mx_ff_udfs.
31 30-Nov-2004 vmehta 115.2 Added get_idw function
32 02-Dec-2004 vmehta 115.2 Corrected the definition of
33 lv_period_type
34 21-Jan-2005 ardsouza 115.6 4129001 hr_mx_utility.get_gre_from_location
35 call modified to pass BG.
36 24-Feb-2005 vmehta 115.7 Changed effective_start_date to
37 1900 for user tables etc.
38 13-Apr-2005 vmehta 115.8 4283684 Modified create_idw_contract to
39 use GRE: as a prefix when creating
40 a GRE level contract.
41 28-Apr-2005 kthirmiy 115.9 Added idw method B Factor
42 Table method code logic in get_idw.
43 17-Jun-2005 vmehta 115.10 4434889 round idw values up to two decimal
44 places
45 20-Jun-2005 vmehta 115.11 4444691 Round the seniority years to whole numbers
46 18-Jul-2005 kthirmiy 115.13 4493980 Round the seniority years to the
47 ceiling.
48 17-Aug-2005 vmehta 115.14 Check for NO_DATA_FOUND when
49 fetching run_results for variable
50 IDW
51 17-Aug-2005 vmehta 115.15 4559484 Passing translated meaning instead
52 of English to get_historic_rates
53 function
54 03-Dec-2005 vmehta 115.16 4779627 Changes to get_idw function:
55 derive idw_start_date so that
56 we only look for run results within
57 the reporting period.
58 get_idw_last_action only looks
59 within start date and report
60 effective date (end of bi-month
61 period)
62 06-Dec-2005 vpandya 115.18 Added following functions:
63 - get_base_pay
64 - get_mx_historic_rate
65 21-Dec-2005 vpandya 115.19 Added following functions:
66 - get_base_pay_for_tax_calc
67 Renamed function get_base_pay to
68 get_daily_base_pay
69 06-Jan-2006 vpandya 115.20 Using get_seniority_social_security
70 function to get seniority years for
71 IDW (changed get_idw).
72 24-Apr-2006 vpandya 115.21 5179475 Changed get_idw and commented out
73 raise_error when
74 lv_idw_factor_tab_name is null.
75 29-Jun-2006 vpandya 115.22 5365301 Added clean_dupl_user_table_rows
76 into get_mx_historic_rate.
77 07-Jun-2007 vpandya 115.23 6120352 Changed get_idw procedure:
78 added c_idw_factor_table_US and
79 c_idw_user_table_check cursor.
80 15-Feb-2008 sivanara 115.24 6815180 Added fnd_number.canonical_to_number
81 in tht function get_idw.
82 15-Apr-2008 sivanara 115.25 6969326 Added the missed out parameter call
83 while calling core package
84 pay_user_row_api.create_user_row
85 13-Jun-2008 nragavar 115.26 7047220 Added fnd_number.canonical_to_number
86 in tht function get_idw.
87 09-Jul-2008 sivanara 115.27 7208623 Added fnd_number.canonical_to_number
88 in tht function get_idw.get_contract_name
89 04-Aug-2008 nragavar 115.28 7042174 Done changes as part of 10 day
90 payroll frequency.
91 20-Aug-2008 nragavar 115.29 7336646 no of days in pay period for 10 day
92 added in get_contract_name procedure.
93 */
94
95
96 FUNCTION standard_hours_worked(
97 p_std_hrs in NUMBER,
98 p_range_start in DATE,
99 p_range_end in DATE,
100 p_std_freq in VARCHAR2) RETURN NUMBER IS
101
102 c_wkdays_per_week NUMBER(5,2) ;
103 c_wkdays_per_month NUMBER(5,2) ;
104 c_wkdays_per_year NUMBER(5,2) ;
105
106 /* 353434, 368242 : Fixed number width for total hours */
107 v_total_hours NUMBER(15,7) ;
108 v_wrkday_hours NUMBER(15,7) ; -- std hrs/wk divided by 5 workdays/wk
109 v_curr_date DATE;
110 v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
111 v_day_no NUMBER;
112
113 BEGIN -- standard_hours_worked
114
115 /* Init */
116 c_wkdays_per_week := 5;
117 c_wkdays_per_month := 20;
118 c_wkdays_per_year := 250;
119 v_total_hours := 0;
120 v_wrkday_hours :=0;
121 v_curr_date := NULL;
122 v_curr_day :=NULL;
123
124 -- Check for valid range
125 hr_utility.trace('Entered standard_hours_worked');
126
127 IF p_range_start > p_range_end THEN
128 hr_utility.trace('p_range_start greater than p_range_end');
129 RETURN v_total_hours;
130 -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
131 -- hr_utility.raise_error;
132 END IF;
133 --
134
135 IF UPPER(p_std_freq) = 'WEEK' THEN
136 hr_utility.trace('p_std_freq = WEEK ');
137
138 v_wrkday_hours := p_std_hrs / c_wkdays_per_week;
139
140 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
141 hr_utility.trace('c_wkdays_per_week ='||to_number(c_wkdays_per_week));
142 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
143
144 ELSIF UPPER(p_std_freq) = 'MONTH' THEN
145
146 hr_utility.trace('p_std_freq = MONTH ');
147
148 v_wrkday_hours := p_std_hrs / c_wkdays_per_month;
149
150
151 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
152 hr_utility.trace('c_wkdays_per_month ='||to_number(c_wkdays_per_month));
153 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
154
155 ELSIF UPPER(p_std_freq) = 'YEAR' THEN
156
157 hr_utility.trace('p_std_freq = YEAR ');
158 v_wrkday_hours := p_std_hrs / c_wkdays_per_year;
159
160 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
161 hr_utility.trace('c_wkdays_per_year ='||to_number(c_wkdays_per_year));
162 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
163
164 ELSE
165 hr_utility.trace('p_std_freq in ELSE ');
166 v_wrkday_hours := p_std_hrs;
167 END IF;
168
169 v_curr_date := p_range_start;
170
171 hr_utility.trace('v_curr_date is range start'||to_char(v_curr_date));
172
173
174 LOOP
175
176 v_day_no := TO_CHAR(v_curr_date, 'D');
177
178
179 IF v_day_no > 1 and v_day_no < 7 then
180
181
182 v_total_hours := nvl(v_total_hours,0) + v_wrkday_hours;
183
184 hr_utility.trace(' v_day_no = '||to_char(v_day_no));
185 hr_utility.trace(' v_total_hours = '||to_char(v_total_hours));
186 END IF;
187
188 v_curr_date := v_curr_date + 1;
189 EXIT WHEN v_curr_date > p_range_end;
190 END LOOP;
191 hr_utility.trace(' Final v_total_hours = '||to_char(v_total_hours));
192 hr_utility.trace(' Leaving standard_hours_worked' );
193 --
194 RETURN v_total_hours;
195 --
196 END standard_hours_worked;
197 --
198
199 -- **********************************************************************
200 FUNCTION Convert_Period_Type(
201 p_bus_grp_id in NUMBER,
202 p_payroll_id in NUMBER,
203 p_tax_unit_id in NUMBER,
204 p_asst_work_schedule in VARCHAR2,
205 p_asst_std_hours in NUMBER,
206 p_figure in NUMBER,
207 p_from_freq in VARCHAR2,
208 p_to_freq in VARCHAR2,
209 p_period_start_date in DATE,
210 p_period_end_date in DATE,
211 p_asst_std_freq in VARCHAR2)
212 RETURN NUMBER IS
213
214 -- local vars
215 v_calc_type VARCHAR2(50);
216 v_from_stnd_factor NUMBER(30,7);
217 v_stnd_start_date DATE;
218
219 v_converted_figure NUMBER(27,7);
220 v_from_annualizing_factor NUMBER(30,7);
221 v_to_annualizing_factor NUMBER(30,7);
222
223 -- local fun
224
225 FUNCTION Get_Annualizing_Factor(p_bg in NUMBER,
226 p_payroll in NUMBER,
227 p_txu_id in NUMBER,
228 p_freq in VARCHAR2,
229 p_asg_work_sched in VARCHAR2,
230 p_asg_std_hrs in NUMBER,
231 p_asg_std_freq in VARCHAR2)
232 RETURN NUMBER IS
233
234 CURSOR c_period_type( cp_payroll_id NUMBER ) IS
235 SELECT period_type
236 FROM pay_payrolls_f
237 WHERE payroll_id = cp_payroll_id;
238
239 -- local constants
240
241 c_weeks_per_year NUMBER(3);
242 c_days_per_year NUMBER(3);
243 c_months_per_year NUMBER(3);
244
245 -- local vars
246
247 v_annualizing_factor NUMBER(30,7);
248 v_periods_per_fiscal_yr NUMBER(5);
249 v_hrs_per_wk NUMBER(15,7);
250 v_hrs_per_range NUMBER(15,7);
251 v_days_per_range NUMBER(15,7);
252 v_use_pay_basis NUMBER(1);
253 v_pay_basis VARCHAR2(80);
254 v_range_start DATE;
255 v_range_end DATE;
256 v_work_sched_name VARCHAR2(80);
257 v_ws_id NUMBER(9);
258 v_period_hours BOOLEAN;
259
260 lv_period_type varchar2(150);
261
262 BEGIN -- Get_Annualizing_Factor
263
264 /* Init */
265
266 c_weeks_per_year := 52;
267 c_days_per_year := 200;
268 c_months_per_year := 12;
269 v_use_pay_basis := 0;
270
271 --
272 -- Check for use of salary admin (ie. pay basis) as frequency.
273 -- Selecting "count" because we want to continue processing even if
274 -- the from_freq is not a pay basis.
275 --
276
277 hr_utility.trace(' Entered Get_Annualizing_Factor ');
278
279 BEGIN -- Is Freq pay basis?
280
281 --
282 -- Decode pay basis and set v_annualizing_factor accordingly.
283 -- PAY_BASIS "Meaning" is passed from FF !
284 --
285
286 hr_utility.trace(' Getting lookup code for lookup_type = PAY_BASIS');
287 hr_utility.trace(' p_freq = '||p_freq);
288
289 SELECT lookup_code
290 INTO v_pay_basis
291 FROM hr_lookups lkp
292 WHERE lkp.application_id = 800
293 AND lkp.lookup_type = 'PAY_BASIS'
294 AND lkp.meaning = p_freq;
295
296 hr_utility.trace(' Lookup_code ie v_pay_basis ='||v_pay_basis);
297
298 v_use_pay_basis := 1;
299
300 IF v_pay_basis = 'MONTHLY' THEN
301
302 hr_utility.trace(' Entered for MONTHLY v_pay_basis');
303
304 v_annualizing_factor := 12;
305
306 hr_utility.trace(' v_annualizing_factor = 12 ');
307
308 ELSIF v_pay_basis = 'HOURLY' THEN
309
310 hr_utility.trace(' Entered for HOURLY v_pay_basis');
311
312 IF p_period_start_date IS NOT NULL THEN
313
314 hr_utility.trace(' p_period_start_date IS NOT NULL ' ||
315 ' v_period_hours=T');
316
317 v_range_start := p_period_start_date;
318 v_range_end := p_period_end_date;
319 v_period_hours := TRUE;
320
321 ELSE
322
323 hr_utility.trace(' p_period_start_date IS NULL');
324
325 v_range_start := sysdate;
326 v_range_end := sysdate + 6;
327 v_period_hours := FALSE;
328
329 END IF;
330
331 IF UPPER(p_asg_work_sched) <> 'NOT ENTERED' THEN
332
333 -- Hourly employee using work schedule.
334 -- Get work schedule name
335
336 hr_utility.trace(' Hourly employee using work schedule');
337 hr_utility.trace(' Get work schedule name');
338
339 v_ws_id := fnd_number.canonical_to_number(p_asg_work_sched);
340
341 hr_utility.trace(' v_ws_id ='||to_number(v_ws_id));
342
343
344 SELECT user_column_name
345 INTO v_work_sched_name
346 FROM pay_user_columns
347 WHERE user_column_id = v_ws_id
348 AND NVL(business_group_id, p_bg) = p_bg
349 AND NVL(legislation_code,'MX') = 'MX';
350
351 hr_utility.trace(' v_work_sched_name ='||v_work_sched_name);
352 hr_utility.trace(' Calling Work_Sch_Total_Hours_or_Days');
353
354 v_hrs_per_range :=
355 Work_Sch_Total_Hours_or_Days(p_bg,
356 v_work_sched_name,
357 v_range_start,
358 v_range_end);
359
360 ELSE-- Hourly emp using Standard Hours on asg.
361
362 hr_utility.trace(' Hourly emp using Standard Hours on asg');
363 hr_utility.trace(' calling Standard_Hours_Worked');
364
365 v_hrs_per_range := Standard_Hours_Worked(p_asg_std_hrs,
366 v_range_start,
367 v_range_end,
368 p_asg_std_freq);
369
370 END IF;
371
372 IF v_period_hours THEN
373
374 hr_utility.trace(' v_period_hours is TRUE');
375
376 SELECT TPT.number_per_fiscal_year
377 INTO v_periods_per_fiscal_yr
378 FROM pay_payrolls_f PPF,
379 per_time_period_types TPT,
380 fnd_sessions fs
381 WHERE PPF.payroll_id = p_payroll
382 AND fs.session_id = USERENV('SESSIONID')
383 AND fs.effective_date between PPF.effective_start_date
384 and PPF.effective_end_date
385 AND TPT.period_type = PPF.period_type;
386
387 v_annualizing_factor :=
388 v_hrs_per_range * v_periods_per_fiscal_yr;
389
390 ELSE
391
392 v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
393
394 END IF;
395
396 ELSIF v_pay_basis = 'PERIOD' THEN
397
398 hr_utility.trace(' v_pay_basis = PERIOD');
399
400 SELECT TPT.number_per_fiscal_year
401 INTO v_annualizing_factor
402 FROM pay_payrolls_f PRL,
403 per_time_period_types TPT,
404 fnd_sessions fs
405 WHERE TPT.period_type = PRL.period_type
406 and fs.session_id = USERENV('SESSIONID')
407 and fs.effective_date BETWEEN PRL.effective_start_date
408 AND PRL.effective_end_date
409 AND PRL.payroll_id = p_payroll
410 AND PRL.business_group_id + 0 = p_bg;
411
412
413 ELSIF v_pay_basis = 'ANNUAL' THEN
414
415
416 hr_utility.trace(' v_pay_basis = ANNUAL');
417
418 v_annualizing_factor := 1;
419
420 ELSE
421
422 -- Did not recognize "pay basis", return -999 as annualizing factor.
423 -- Remember this for debugging when zeroes come out as results!!!
424
425 hr_utility.trace(' Did not recognize pay basis');
426
427 v_annualizing_factor := 0;
428
429 RETURN v_annualizing_factor;
430
431 END IF;
432
433 EXCEPTION
434
435 WHEN NO_DATA_FOUND THEN
436
437 hr_utility.trace(' When no data found' );
438 v_use_pay_basis := 0;
439
440 END; /* SELECT LOOKUP CODE */
441
442 IF v_use_pay_basis = 0 THEN
443
444 hr_utility.trace(' Not using pay basis as frequency');
445
446 -- Not using pay basis as frequency...
447
448 IF (p_freq IS NULL) OR
449 (UPPER(p_freq) = 'PERIOD') OR
450 (UPPER(p_freq) = 'NOT ENTERED')
451 THEN
452
453 -- Get "annuallizing factor" from period type of the payroll.
454
455 hr_utility.trace('Get annuallizing factor from period '||
456 'type of the payroll');
457
458 SELECT TPT.number_per_fiscal_year
459 INTO v_annualizing_factor
460 FROM pay_payrolls_f PRL,
461 per_time_period_types TPT,
462 fnd_sessions fs
463 WHERE TPT.period_type = PRL.period_type
464 AND fs.session_id = USERENV('SESSIONID')
465 AND fs.effective_date BETWEEN PRL.effective_start_date
466 AND PRL.effective_end_date
467 AND PRL.payroll_id = p_payroll
468 AND PRL.business_group_id + 0 = p_bg;
469
470 hr_utility.trace('v_annualizing_factor ='||
471 to_number(v_annualizing_factor));
472
473 ELSIF UPPER(p_freq) = 'DAILY' THEN
474
475 hr_utility.trace(' Daily Employee');
476
477 v_annualizing_factor :=
478 pay_mx_utility.get_days_in_year(p_bg, p_txu_id, p_payroll);
479
480
481 ELSIF UPPER(p_freq) = 'HOURLY' THEN -- Hourly employee...
482
483 hr_utility.trace(' Hourly Employee');
484
485 IF p_period_start_date IS NOT NULL THEN
486 v_range_start := p_period_start_date;
487 v_range_end := p_period_end_date;
488 v_period_hours := TRUE;
489 ELSE
490 v_range_start := sysdate;
491 v_range_end := sysdate + 6;
492 v_period_hours := FALSE;
493 END IF;
494
495 IF UPPER(p_asg_work_sched) <> 'NOT ENTERED' THEN
496
497 -- Hourly emp using work schedule.
498 -- Get work schedule name:
499
500 v_ws_id := fnd_number.canonical_to_number(p_asg_work_sched);
501
502 SELECT user_column_name
503 INTO v_work_sched_name
504 FROM pay_user_columns
505 WHERE user_column_id = v_ws_id
506 AND NVL(business_group_id, p_bg) = p_bg
507 AND NVL(legislation_code,'MX') = 'MX';
508
509
510 v_hrs_per_range := Work_Sch_Total_Hours_or_Days(
511 p_bg,
512 v_work_sched_name,
513 v_range_start,
514 v_range_end);
515
516 ELSE-- Hourly emp using Standard Hours on asg.
517
518 hr_utility.trace(' Hourly emp using Standard Hours on asg');
519
520 hr_utility.trace('calling Standard_Hours_Worked');
521
522 v_hrs_per_range := Standard_Hours_Worked(p_asg_std_hrs,
523 v_range_start,
524 v_range_end,
525 p_asg_std_freq);
526
527 hr_utility.trace('returned Standard_Hours_Worked');
528 END IF;
529
530
531 IF v_period_hours THEN
532
533 hr_utility.trace('v_period_hours = TRUE');
534
535 SELECT TPT.number_per_fiscal_year
536 INTO v_periods_per_fiscal_yr
537 FROM pay_payrolls_f ppf,
538 per_time_period_types tpt,
539 fnd_sessions fs
540 WHERE ppf.payroll_id = p_payroll
541 AND fs.session_id = USERENV('SESSIONID')
542 AND fs.effective_date BETWEEN ppf.effective_start_date
543 AND ppf.effective_end_date
544 AND tpt.period_type = ppf.period_type;
545
546 v_annualizing_factor :=
547 v_hrs_per_range * v_periods_per_fiscal_yr;
548
549 hr_utility.trace('v_hrs_per_range ='||
550 to_number(v_hrs_per_range));
551 hr_utility.trace('v_periods_per_fiscal_yr ='||
552 to_number(v_periods_per_fiscal_yr));
553 hr_utility.trace('v_annualizing_factor ='||
554 to_number(v_annualizing_factor));
555
556 ELSE
557
558 hr_utility.trace('v_period_hours = FALSE');
559
560 v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
561
562 hr_utility.trace('v_hrs_per_range ='||
563 to_number(v_hrs_per_range));
564 hr_utility.trace('c_weeks_per_year ='||
565 to_number(c_weeks_per_year));
566 hr_utility.trace('v_annualizing_factor ='||
567 to_number(v_annualizing_factor));
568
569 END IF;
570
571 ELSE
572
573 -- Not hourly, an actual time period type!
574
575 hr_utility.trace('Not hourly - an actual time period type');
576
577 BEGIN
578
579 hr_utility.trace(' selecting from per_time_period_types');
580
581 SELECT PT.number_per_fiscal_year
582 INTO v_annualizing_factor
583 FROM per_time_period_types PT
584 WHERE UPPER(PT.period_type) = UPPER(p_freq);
585
586 hr_utility.trace('v_annualizing_factor ='||
587 to_number(v_annualizing_factor));
588
589 EXCEPTION WHEN no_data_found THEN
590
591 -- Added as part of SALLY CLEANUP.
592 -- Could have been passed in an ASG_FREQ dbi which
593 -- might have the values of
594 -- 'Day' or 'Month' which do not map to a time period type.
595 -- So we'll do these by hand.
596
597 IF UPPER(p_freq) = 'DAY' THEN
598 hr_utility.trace(' p_freq = DAY');
599 v_annualizing_factor := c_days_per_year;
600 ELSIF UPPER(p_freq) = 'MONTH' THEN
601 v_annualizing_factor := c_months_per_year;
602 hr_utility.trace(' p_freq = MONTH');
603 END IF;
604
605 END;
606
607 END IF;
608
609 END IF; -- (v_use_pay_basis = 0)
610
611
612 hr_utility.trace(' Getting out of Get_Annualizing_Factor for '||
613 v_pay_basis);
614 RETURN v_annualizing_factor;
615
616 END Get_Annualizing_Factor;
617
618
619 BEGIN -- Convert Figure
620
621 --begin_convert_period_type
622
623 --hr_utility.trace_on(null,'UDFS');
624
625 hr_utility.trace('UDFS Entered Convert_Period_Type');
626
627 hr_utility.trace(' p_bus_grp_id: '|| p_bus_grp_id);
628 hr_utility.trace(' p_payroll_id: '||p_payroll_id);
629 hr_utility.trace(' p_tax_unit_id: '||p_tax_unit_id);
630 hr_utility.trace(' p_asst_work_schedule: '||p_asst_work_schedule);
631 hr_utility.trace(' p_asst_std_hours: '||p_asst_std_hours);
632 hr_utility.trace(' p_figure: '||p_figure);
633 hr_utility.trace(' p_from_freq : '||p_from_freq);
634 hr_utility.trace(' p_to_freq: '||p_to_freq);
635 hr_utility.trace(' p_period_start_date: '||p_period_start_date);
636
637 hr_utility.trace(' p_period_end_date: '||p_period_end_date);
638 hr_utility.trace(' p_asst_std_freq: '||p_asst_std_freq);
639
640 --
641 -- If From_Freq and To_Freq are the same, then we're done.
642 --
643
644 IF NVL(p_from_freq, 'NOT ENTERED') = NVL(p_to_freq, 'NOT ENTERED')
645 THEN
646
647 RETURN p_figure;
648
649 END IF;
650
651 hr_utility.trace('Calling Get_Annualizing_Factor for FROM case');
652
653 v_from_annualizing_factor := Get_Annualizing_Factor(
654 p_bg => p_bus_grp_id,
655 p_payroll => p_payroll_id,
656 p_txu_id => p_tax_unit_id,
657 p_freq => p_from_freq,
658 p_asg_work_sched => p_asst_work_schedule,
659 p_asg_std_hrs => p_asst_std_hours,
660 p_asg_std_freq => p_asst_std_freq);
661
662 hr_utility.trace('Calling Get_Annualizing_Factor for TO case');
663
664 v_to_annualizing_factor := Get_Annualizing_Factor(
665 p_bg => p_bus_grp_id,
666 p_payroll => p_payroll_id,
667 p_txu_id => p_tax_unit_id,
668 p_freq => p_to_freq,
669 p_asg_work_sched => p_asst_work_schedule,
670 p_asg_std_hrs => p_asst_std_hours,
671 p_asg_std_freq => p_asst_std_freq);
672
673 --
674 -- Annualize "Figure" and convert to To_Freq.
675 --
676
677 hr_utility.trace('v_from_annualizing_factor ='||
678 to_char(v_from_annualizing_factor));
679 hr_utility.trace('v_to_annualizing_factor ='||
680 to_char(v_to_annualizing_factor));
681
682 IF v_to_annualizing_factor = 0 OR
683 v_to_annualizing_factor = -999 OR
684 v_from_annualizing_factor = -999
685 THEN
686
687 hr_utility.trace(' v_to_ann =0 or -999 or v_from = -999');
688
689 v_converted_figure := 0;
690
691 ELSE
692
693 hr_utility.trace(' v_to_ann NOT 0 or -999 or v_from = -999');
694
695 hr_utility.trace('p_figure Monthly Salary = '||p_figure);
696 hr_utility.trace('v_from_annualizing_factor = '||
697 v_from_annualizing_factor);
698 hr_utility.trace('v_to_annualizing_factor = '||
699 v_to_annualizing_factor);
700
701 v_converted_figure :=
702 (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
703
704 hr_utility.trace('conv figure is monthly_sal * ann_from div by ann to');
705
706 END IF;
707
708
709 hr_utility.trace('UDFS v_converted_figure := '||v_converted_figure);
710
711 --hr_utility.trace_off;
712
713 RETURN v_converted_figure;
714
715 END Convert_Period_Type;
716
717 --
718 -- **********************************************************************
719 --
720
721 FUNCTION Work_Sch_Total_Hours_or_Days( p_bg_id in NUMBER
722 ,p_ws_name in VARCHAR2
723 ,p_range_start in DATE
724 ,p_range_end in DATE
725 ,p_mode in VARCHAR2 )
726 RETURN NUMBER IS
727
728 -- local constants
729
730 c_ws_tab_name VARCHAR2(80) ;
731
732 -- local variables
733
734 v_total_units NUMBER(15,7);
735 v_unit NUMBER(15,7);
736 v_week_work_days NUMBER(15,7);
737 v_range_start DATE;
738 v_range_end DATE;
739 v_curr_date DATE;
740 v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
741 v_ws_name VARCHAR2(80); -- Work Schedule Name.
742 v_gtv_hours VARCHAR2(80); -- get_table_value returns varchar2
743 -- Remember to FND_NUMBER.CANONICAL_TO_NUMBER result.
744 v_fnd_sess_row VARCHAR2(1);
745 l_exists VARCHAR2(1);
746 v_day_no NUMBER;
747
748 BEGIN -- Work_Sch_Total_Hours_or_Days
749
750 --hr_utility.trace_on(null,'UDFS');
751 hr_utility.trace('p_bg_id '||p_bg_id);
752 hr_utility.trace('p_ws_name '||p_ws_name);
753 hr_utility.trace('p_range_start '||p_range_start);
754 hr_utility.trace('p_range_end '||p_range_end);
755 hr_utility.trace('p_mode '||p_mode);
756
757 /* Init */
758
759 v_total_units := 0;
760 c_ws_tab_name := 'COMPANY WORK SCHEDULES';
761
762 -- Changed to select the work schedule defined
763 -- at the Organization level the default work
764 -- schedule (COMPANY WORK SCHEDULES ) to the
765 -- variable c_ws_tab_name
766
767 BEGIN
768 SELECT put.user_table_name
769 INTO c_ws_tab_name
770 FROM hr_organization_information hoi
771 ,pay_user_tables put
772 WHERE hoi.organization_id = p_bg_id
773 AND hoi.org_information_context = 'Work Schedule'
774 AND hoi.org_information1 = put.user_table_id ;
775
776 EXCEPTION WHEN no_data_found THEN
777 null;
778 END;
779
780
781 v_range_start := NVL(p_range_start, sysdate);
782 v_range_end := NVL(p_range_end, sysdate + 6);
783
784 IF v_range_start > v_range_end THEN
785 --
786 RETURN v_total_units;
787 --
788 END IF;
789
790 --
791 -- Get_Table_Value requires row in FND_SESSIONS. We must insert this
792 -- record if one doe not already exist.
793 --
794
795 SELECT DECODE(COUNT(session_id), 0, 'N', 'Y')
796 INTO v_fnd_sess_row
797 FROM fnd_sessions
798 WHERE session_id = userenv('sessionid');
799
800 --
801
802 IF v_fnd_sess_row = 'N' THEN
803
804 dt_fndate.set_effective_date(trunc(sysdate));
805
806 END IF;
807
808 --
809 -- Track range dates:
810 --
811 -- Check if the work schedule is an id or a name. If the work
812 -- schedule does not exist, then return 0.
813 --
814 BEGIN
815
816 SELECT 'Y'
817 INTO l_exists
818 FROM pay_user_tables put,
819 pay_user_columns puc
820 WHERE puc.user_column_name = p_ws_name
821 AND nvl(puc.business_group_id, p_bg_id) = p_bg_id
822 AND nvl(puc.legislation_code,'MX') = 'MX'
823 AND puc.user_table_id = put.user_table_id
824 AND put.user_table_name = c_ws_tab_name;
825
826
827 EXCEPTION WHEN no_data_found THEN
828 NULL;
829
830 END;
831
832 IF l_exists = 'Y' then
833 v_ws_name := p_ws_name;
834 ELSE
835
836 BEGIN
837 SELECT puc.user_column_name
838 INTO v_ws_name
839 FROM pay_user_tables put,
840 pay_user_columns puc
841 WHERE puc.user_column_id = p_ws_name
842 AND nvl(puc.business_group_id, p_bg_id) = p_bg_id
843 AND nvl(puc.legislation_code,'MX') = 'MX'
844 AND puc.user_table_id = PUT.user_table_id
845 AND put.user_table_name = c_ws_tab_name;
846
847
848 EXCEPTION WHEN NO_DATA_FOUND THEN
849 RETURN v_total_units;
850 END;
851
852 END IF;
853
854 --
855
856 v_curr_date := v_range_start;
857
858 --
859 --
860 LOOP
861
862 v_day_no := TO_CHAR(v_curr_date, 'D');
863
864
865 SELECT decode(v_day_no,1,'SUN',2,'MON',3,'TUE',
866 4,'WED',5,'THU',6,'FRI',7,'SAT')
867 INTO v_curr_day
868 FROM DUAL;
869
870 --
871 --
872
873 v_unit := FND_NUMBER.CANONICAL_TO_NUMBER(
874 hruserdt.get_table_value(p_bg_id
875 ,c_ws_tab_name
876 ,v_ws_name
877 ,v_curr_day));
878
879 /***********************************************************
880 ** Consider 1 day when v_unit is non zero FOR Days X Rate
881 ** i.e. p_mode = DAYS
882 ***********************************************************/
883
884 IF p_mode = 'DAYS' AND v_unit <> 0 THEN
885
886 v_unit := 1;
887
888 END IF;
889
890 v_total_units := v_total_units + v_unit;
891
892 hr_utility.trace('v_day_no '||v_day_no);
893 hr_utility.trace('v_unit '||v_unit);
894 hr_utility.trace('v_total_units '||v_total_units);
895
896 v_curr_date := v_curr_date + 1;
897
898 --
899 --
900
901 EXIT WHEN v_curr_date > v_range_end;
902
903 --
904
905 END LOOP;
906
907 --
908
909 --hr_utility.trace_off;
910
911 RETURN v_total_units;
912
913 --
914
915 END Work_Sch_Total_Hours_or_Days;
916
917
918 FUNCTION Work_Sch_Total_Hours_or_Days( p_bg_id in NUMBER,
919 p_ws_name in VARCHAR2,
920 p_range_start in DATE,
921 p_range_end in DATE)
922 RETURN NUMBER IS
923
924 ln_days number;
925
926 BEGIN --Work_Sch_Total_Hours_or_Days
927
928 ln_days:= Work_Sch_Total_Hours_or_Days( p_bg_id => p_bg_id
929 ,p_ws_name => p_ws_name
930 ,p_range_start => p_range_start
931 ,p_range_end => p_range_end
932 ,p_mode => 'HOURS' );
933
934 RETURN ln_days;
935
936 END Work_Sch_Total_Hours_or_Days;
937
938 --
939
940 FUNCTION get_idw (p_assignment_id per_all_assignments_f.assignment_id%TYPE,
941 p_tax_unit_id hr_organization_units.organization_id%TYPE,
942 p_effective_date DATE,
943 p_mode VARCHAR2,
944 p_fixed_idw OUT NOCOPY NUMBER,
945 p_variable_idw OUT NOCOPY NUMBER)
946 RETURN NUMBER IS
947
948 CURSOR c_get_all_assignments
949 IS
950 SELECT a.assignment_id,
951 a.soft_coding_keyflex_id,
952 a.location_id,
953 a.payroll_id,
954 a.business_group_id,
955 a.person_id
956 FROM per_all_assignments_f a,
957 per_all_assignments_f b
958 WHERE b.person_id = a.person_id
959 AND b.assignment_id = p_assignment_id
960 AND p_effective_date BETWEEN a.effective_start_date
961 AND a.effective_end_date
962 AND p_effective_date BETWEEN b.effective_start_date
963 AND b.effective_end_date;
964
965 CURSOR
966 c_get_last_idw_action(cp_asg_id pay_assignment_actions.assignment_id%TYPE,
967 cp_idw_report_date DATE,
968 cp_idw_start_date DATE) IS
969 SELECT assignment_action_id
970 FROM pay_assignment_actions aa,
971 pay_payroll_actions pa
972 WHERE assignment_id = cp_asg_id
973 AND tax_unit_id = p_tax_unit_id
974 AND aa.source_action_id IS NOT NULL
975 AND aa.payroll_action_id = pa.payroll_action_id
976 AND pa.effective_date BETWEEN cp_idw_start_date AND cp_idw_report_date
977 ORDER BY aa.action_sequence desc;
978
979 -- cursor to get the IDW Calc method
980 CURSOR c_get_idw_calc_method (cp_org_id hr_organization_units.organization_id%TYPE,
981 cp_eff_date DATE )
982 IS
983 select hoi.org_information10
984 from hr_organization_units hou,
985 hr_organization_information hoi
986 where hou.organization_id = cp_org_id
987 and hoi.org_information_context ='MX_SOC_SEC_DETAILS'
988 and hou.organization_id = hoi.organization_id
989 and cp_eff_date between hou.date_from and nvl(hou.date_to,cp_eff_date) ;
990
991 -- cursor to get the IDW factor table name
992 CURSOR c_get_idw_factor_tab_name (cp_asg_id pay_element_entries_f.assignment_id%TYPE,
993 cp_eff_date DATE )
994 IS
995 select hrl.lookup_code
996 ,hrl.meaning
997 from pay_element_types_f pet,
998 pay_input_values_f piv,
999 pay_element_entries_f pee,
1000 pay_element_entry_values_f pev,
1001 hr_lookups hrl
1002 where pet.element_name='Integrated Daily Wage'
1003 and piv.element_type_id = pet.element_type_id
1004 and piv.name ='IDW Factor Table'
1005 and pee.element_type_id = pet.element_type_id
1006 and pee.assignment_id = cp_asg_id
1007 and pev.element_entry_id = pee.element_entry_id
1008 and pev.input_value_id = piv.input_value_id
1009 and hrl.lookup_type = 'MX_IDW_FACTOR_TABLES'
1010 and hrl.lookup_code = pev.screen_entry_value
1011 and cp_eff_date between pet.effective_start_date and pet.effective_end_date
1012 and cp_eff_date between piv.effective_start_date and piv.effective_end_date
1013 and cp_eff_date between pee.effective_start_date and pee.effective_end_date
1014 and cp_eff_date between pev.effective_start_date and pev.effective_end_date ;
1015
1016 CURSOR c_idw_user_table_check( cp_idw_user_table_name IN VARCHAR2 ) IS
1017 SELECT 'Y'
1018 FROM pay_user_tables
1019 WHERE user_table_name = cp_idw_user_table_name;
1020
1021 CURSOR c_idw_factor_table_US ( cp_idw_lookup_code IN VARCHAR2 ) IS
1022 SELECT meaning
1023 FROM fnd_lookup_values flv
1024 WHERE flv.lookup_type = 'MX_IDW_FACTOR_TABLES'
1025 AND flv.lookup_code = cp_idw_lookup_code
1026 AND flv.language = 'US';
1027
1028 lv_idw_user_table_found VARCHAR2(80);
1029 lv_idw_factor_table_US VARCHAR2(240);
1030
1031 rn_idw NUMBER;
1032 ln_rate NUMBER;
1033 ln_variable_idw NUMBER;
1034 ln_last_idw_action pay_assignment_actions.assignment_action_id%TYPE;
1035 ln_asg_tuid pay_assignment_actions.tax_unit_id%TYPE;
1036 ln_idw_ele_id pay_element_types_f.element_type_id%TYPE;
1037 ln_idw_inp_id pay_input_values_f.input_value_id%TYPE;
1038 lb_gre_ambiguous BOOLEAN;
1039 lb_gre_missing BOOLEAN;
1040 lv_period_type pay_all_payrolls_f.period_type%TYPE;
1041 lv_contract_name VARCHAR2(240);
1042 ld_idw_report_date DATE;
1043 ld_idw_start_date DATE;
1044
1045 lv_idw_calc_method VARCHAR2(30);
1046 lv_idw_factor_tab_name VARCHAR2(80);
1047 lv_idw_lookup_code VARCHAR2(80);
1048 ld_adj_svc_date DATE ;
1049 ld_seniority_from DATE ;
1050 ln_seniority_years NUMBER;
1051 ln_idw_factor NUMBER;
1052 ln_basepay_rate NUMBER;
1053 lv_basepay_rate_name hr_lookups.meaning%TYPE;
1054 lv_fixedidw_rate_name hr_lookups.meaning%TYPE;
1055
1056 FUNCTION get_fixed_idw (p_asg_id per_all_assignments_f.assignment_id%TYPE,
1057 p_calculation_date DATE,
1058 p_name VARCHAR2,
1059 p_contract_name VARCHAR2)
1060 RETURN NUMBER IS
1061
1062 ln_retstat NUMBER;
1063 rn_rate NUMBER;
1064 lv_err_mesg VARCHAR2(240);
1065 BEGIN
1066 rn_rate := pqp_rates_history_calc.get_historic_rate(
1067 p_assignment_id => p_asg_id,
1068 p_rate_name => p_name,
1069 p_effective_date => p_calculation_date,
1070 p_time_dimension => 'D',
1071 p_rate_type_or_element => 'R',
1072 p_contract_type => p_contract_name);
1073
1074
1075 RETURN rn_rate;
1076
1077 EXCEPTION WHEN OTHERS
1078 THEN
1079 hr_utility.raise_error;
1080 RETURN rn_rate;
1081 END get_fixed_idw;
1082
1083 BEGIN
1084 --{
1085 rn_idw := 0;
1086 p_fixed_idw := 0;
1087 p_variable_idw := 0;
1088 FOR asg_rec in c_get_all_assignments
1089 LOOP
1090 --{
1091 ln_asg_tuid := NULL;
1092 ln_asg_tuid :=
1093 hr_mx_utility.get_gre_from_scl(
1094 p_soft_coding_keyflex_id => asg_rec.soft_coding_keyflex_id);
1095
1096 IF (ln_asg_tuid IS NULL)
1097 THEN
1098 --{
1099 -- Bug 4129001 - Added p_business_group_id parameter
1100 --
1101 ln_asg_tuid := hr_mx_utility.get_gre_from_location(
1102 p_location_id => asg_rec.location_id,
1103 p_business_group_id => asg_rec.business_group_id,
1104 p_session_date => p_effective_date,
1105 p_is_ambiguous => lb_gre_ambiguous,
1106 p_missing_gre => lb_gre_missing);
1107
1108 IF (lb_gre_ambiguous = TRUE OR lb_gre_missing = TRUE)
1109 THEN
1110 --{
1111 ln_asg_tuid := NULL;
1112 --}
1113 END IF;
1114 --}
1115 END IF;
1116 IF (ln_asg_tuid = p_tax_unit_id)
1117 THEN
1118 --{
1119
1120 --
1121 -- IDW Factor Table Method Modification
1122 --
1123 -- Get the idw calc method
1124 hr_utility.trace('Get IDW Calc Method ');
1125 hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
1126 hr_utility.trace('p_effective_date ='||to_char(p_effective_date));
1127
1128 lv_idw_calc_method := 'A';
1129 OPEN c_get_idw_calc_method (p_tax_unit_id,
1130 p_effective_date );
1131 FETCH c_get_idw_calc_method INTO lv_idw_calc_method;
1132 CLOSE c_get_idw_calc_method;
1133
1134 hr_utility.trace('lv_idw_calc_method = '|| nvl(lv_idw_calc_method,'null'));
1135
1136 IF lv_idw_calc_method is null or lv_idw_calc_method ='A' then
1137
1138 hr_utility.trace('calculating using Method A Earnings Method' );
1139
1140 -- calculate using Method A Earnings Method
1141 ln_rate := 0;
1142 ln_rate := get_mx_historic_rate (
1143 p_business_group_id => asg_rec.business_group_id
1144 ,p_assignment_id => asg_rec.assignment_id
1145 ,p_tax_unit_id => p_tax_unit_id
1146 ,p_payroll_id => asg_rec.payroll_id
1147 ,p_effective_date => p_effective_date
1148 ,p_rate_code => 'MX_IDWF' );
1149
1150 ELSIF lv_idw_calc_method ='B' then
1151
1152 hr_utility.trace('calculating using Method B Factor Table Method' );
1153 hr_utility.trace('Get IDW Factor Table Name' );
1154 hr_utility.trace('assignment_id ='||to_char(asg_rec.assignment_id));
1155
1156 -- calculate using Method B IDW Factor Method
1157 -- Get the IDW Factor table name entered in
1158 -- Integrated Daily Wage element
1159 OPEN c_get_idw_factor_tab_name (asg_rec.assignment_id,
1160 p_effective_date );
1161 FETCH c_get_idw_factor_tab_name INTO lv_idw_lookup_code
1162 ,lv_idw_factor_tab_name;
1163 CLOSE c_get_idw_factor_tab_name ;
1164
1165 hr_utility.trace('lv_idw_factor_tab_name='||lv_idw_factor_tab_name);
1166
1167 IF lv_idw_factor_tab_name is null then
1168 --hr_utility.raise_error;
1169 RETURN rn_idw;
1170 END IF;
1171
1172 -- Check user table exists or not for lv_idw_factor_tab_name
1173 -- if exists then use lv_idw_factor_tab_name otherwise
1174 -- get idw factor table name fnd_lookups for 'US' languge
1175 -- Return 0 if idw factor table for 'US' not exists
1176
1177 lv_idw_user_table_found := 'N';
1178
1179 OPEN c_idw_user_table_check( lv_idw_factor_tab_name );
1180 FETCH c_idw_user_table_check INTO lv_idw_user_table_found;
1181 CLOSE c_idw_user_table_check;
1182
1183 IF lv_idw_user_table_found = 'N' THEN
1184
1185 lv_idw_factor_table_US := NULL;
1186
1187 OPEN c_idw_factor_table_US( lv_idw_lookup_code );
1188 FETCH c_idw_factor_table_US INTO lv_idw_factor_table_US;
1189 CLOSE c_idw_factor_table_US;
1190
1191
1192 IF lv_idw_factor_table_US IS NOT NULL THEN
1193
1194 lv_idw_factor_tab_name := lv_idw_factor_table_US;
1195
1196 ELSE
1197
1198 -- Incorrect setup as IDW Factor Table is not found
1199 -- in US English or Spanish or any other language
1200
1201 RETURN rn_idw;
1202
1203 END IF;
1204
1205 END IF;
1206
1207 -- get the seniority
1208 hr_utility.trace('Get Seniority' );
1209
1210 ln_seniority_years := hr_mx_utility.get_seniority_social_security(
1211 p_person_id => asg_rec.person_id
1212 ,p_effective_date => p_effective_date);
1213
1214 hr_utility.trace('ln_seniority_years = '||ln_seniority_years);
1215
1216 -- get the FACTOR from the table
1217 -- by passing seniority years,
1218 -- Added fnd_number.canonical_to_number for bug 6815180
1219 ln_idw_factor := FND_NUMBER.CANONICAL_TO_NUMBER(hruserdt.get_table_value(
1220 p_bus_group_id => asg_rec.business_group_id,
1221 p_table_name => lv_idw_factor_tab_name,
1222 p_col_name => 'Factor',
1223 p_row_value => ln_seniority_years,
1224 p_effective_date => p_effective_date));
1225
1226 hr_utility.trace('ln_idw_factor = '||to_char(ln_idw_factor));
1227
1228 hr_utility.trace('Get Base Pay ');
1229 hr_utility.trace('lv_contract_name =' || lv_contract_name );
1230
1231 -- Get the Base Pay using historic rates
1232 ln_basepay_rate := 0;
1233 ln_basepay_rate := get_daily_base_pay (
1234 p_business_group_id => asg_rec.business_group_id
1235 ,p_assignment_id => asg_rec.assignment_id
1236 ,p_tax_unit_id => p_tax_unit_id
1237 ,p_payroll_id => asg_rec.payroll_id
1238 ,p_effective_date => p_effective_date);
1239
1240
1241 hr_utility.trace('ln_basepay_rate = '||to_char(ln_basepay_rate));
1242
1243 -- Calculate the fixed portion of idw
1244 ln_rate := ln_basepay_rate * ln_idw_factor ;
1245
1246 hr_utility.trace('fixed portion of idw ln_rate = '||to_char(ln_rate));
1247
1248 END IF ; -- lv_idw_calc_method
1249
1250 p_fixed_idw := p_fixed_idw + ln_rate;
1251 rn_idw := rn_idw + ln_rate;
1252
1253 IF (p_mode LIKE '%REPORT')
1254 THEN
1255 --{
1256 SELECT
1257 DECODE(p_mode,
1258 'REPORT',
1259 ADD_MONTHS(TRUNC(p_effective_date, 'Y'),
1260 TO_CHAR(p_effective_date, 'MM') -
1261 DECODE(MOD(TO_NUMBER(TO_CHAR(p_effective_date,'MM')),2),
1262 1, 1,
1263 0, 2)
1264 ) - 1,
1265 'BIMONTH_REPORT',
1266 p_effective_date)
1267 INTO ld_idw_report_date
1268 FROM DUAL;
1269
1270 SELECT ADD_MONTHS(ld_idw_report_date, -2) + 1
1271 INTO ld_idw_start_date
1272 FROM DUAL;
1273
1274 ln_last_idw_action := -1;
1275
1276 OPEN c_get_last_idw_action(asg_rec.assignment_id,
1277 ld_idw_report_date,
1278 ld_idw_start_date);
1279
1280 FETCH c_get_last_idw_action
1281 INTO ln_last_idw_action;
1282 CLOSE c_get_last_idw_action;
1283
1284 IF (ln_last_idw_action <> -1)
1285 THEN
1286 --{
1287 ln_idw_ele_id := -1;
1288 ln_idw_inp_id := -1;
1289 SELECT iv.element_type_id,
1290 input_value_id
1291 INTO ln_idw_ele_id,
1292 ln_idw_inp_id
1293 FROM pay_element_types_f et,
1294 pay_input_values_f iv
1295 WHERE element_name = 'Integrated Daily Wage'
1296 AND et.legislation_code = 'MX'
1297 AND p_effective_date BETWEEN et.effective_start_date
1298 AND et.effective_end_date
1299 AND et.element_type_id = iv.element_type_id
1300 AND iv.name = 'Variable IDW'
1301 AND p_effective_date BETWEEN iv.effective_start_date
1302 AND iv.effective_end_date;
1303 BEGIN
1304
1305 ln_variable_idw := 0;
1306 SELECT fnd_number.canonical_to_number(result_value)
1307 INTO ln_variable_idw
1308 FROM pay_run_result_values rrv,
1309 pay_run_results rr
1310 WHERE assignment_action_id = ln_last_idw_action
1311 AND element_type_id = ln_idw_ele_id
1312 AND rr.run_result_id = rrv.run_result_id
1313 AND rrv.input_value_id = ln_idw_inp_id;
1314
1315 EXCEPTION
1316 WHEN NO_DATA_FOUND THEN
1317 /*
1318 * This can happen when earnings that contribute to
1319 * Variable IDW have never been processed for a person
1320 */
1321 NULL;
1322 END;
1323
1324 rn_idw := rn_idw + ln_variable_idw;
1325 p_variable_idw := p_variable_idw + ln_variable_idw;
1326 --}
1327 END IF;
1328 --}
1329 ELSIF (p_mode = 'CALC')
1330 THEN
1331 --{
1332 p_variable_idw := 0;
1333 --}
1334 END IF;
1335 --}
1336 END IF;
1337 --}
1338 END LOOP;
1339
1340 /*
1341 * Need to maintain IDW accuracy up to 2 decimal places - Bug 4434889
1342 */
1343 p_variable_idw := round(p_variable_idw, 2);
1344 p_fixed_idw := round(p_fixed_idw, 2);
1345 RETURN round(rn_idw, 2);
1346 --}
1347
1348 EXCEPTION
1349 WHEN others THEN
1350 RAISE;
1351
1352 END get_idw;
1353
1354 FUNCTION get_mx_historic_rate (
1355 p_business_group_id NUMBER
1356 ,p_assignment_id NUMBER
1357 ,p_tax_unit_id NUMBER
1358 ,p_payroll_id NUMBER
1359 ,p_effective_date DATE
1360 ,p_rate_code VARCHAR2)
1361 RETURN NUMBER IS
1362
1363 /*
1364 * Cursor to get Rate Name based on Code
1365 */
1366 CURSOR c_get_rate_name(cp_lookup_code VARCHAR2) IS
1367 SELECT meaning
1368 FROM hr_lookups
1369 WHERE lookup_type = 'PQP_RATE_TYPE'
1370 AND lookup_code = cp_lookup_code;
1371
1372 lv_rate_name VARCHAR2(240);
1373 lv_contract_name VARCHAR2(240);
1374 ln_rate NUMBER;
1375
1376 PROCEDURE clean_dupl_user_table_rows ( p_user_table_name IN VARCHAR2
1377 ,p_row_value IN VARCHAR2)
1378 IS
1379
1380 CURSOR c_usr_tbl_rows ( cp_contract_name VARCHAR2
1381 ,cp_user_table_id NUMBER) IS
1382 SELECT user_row_id
1383 FROM pay_user_rows_f
1384 WHERE row_low_range_or_name = cp_contract_name
1385 AND user_table_id = cp_user_table_id
1386 ORDER BY user_row_id;
1387
1388
1389 ln_user_table_id NUMBER;
1390 ln_count NUMBER;
1391 i NUMBER;
1392
1393 BEGIN
1394
1395 SELECT user_table_id
1396 INTO ln_user_table_id
1397 FROM pay_user_tables
1398 WHERE user_table_name = p_user_table_name
1399 AND ( legislation_code is NULL OR
1400 legislation_code = 'MX');
1401
1402 SELECT count(*)
1403 INTO ln_count
1404 FROM pay_user_rows_f
1405 WHERE row_low_range_or_name = p_row_value
1406 AND user_table_id = ln_user_table_id;
1407
1408
1409 IF ln_count > 1 THEN
1410
1411 i := 1;
1412
1413 FOR rw in c_usr_tbl_rows( p_row_value, ln_user_table_id )
1414 LOOP
1415
1416 IF ( i <> ln_count ) THEN
1417
1418 DELETE pay_user_column_instances_f
1419 WHERE user_row_id = rw.user_row_id;
1420
1421 DELETE pay_user_rows_f
1422 WHERE user_row_id = rw.user_row_id;
1423
1424 END IF;
1425
1426 i := i + 1;
1427
1428 END LOOP;
1429
1430 END IF;
1431
1432 END clean_dupl_user_table_rows;
1433
1434 PROCEDURE create_contract (p_business_group_id IN NUMBER,
1435 p_contract_name IN VARCHAR2,
1436 p_days_in_year IN NUMBER,
1437 p_exists IN BOOLEAN)
1438 IS
1439
1440 TYPE user_col_rec is RECORD (
1441 col_name pay_user_columns.user_column_name%TYPE,
1442 value pay_user_column_instances_f.value%TYPE);
1443
1444 TYPE col_tab IS TABLE OF user_col_rec
1445 INDEX BY BINARY_INTEGER;
1446
1447 lt_col_det_tab col_tab;
1448
1449 ld_eff_date DATE;
1450 ld_eff_start_date DATE;
1451 ld_eff_end_date DATE;
1452
1453 ln_user_table_id pay_user_tables.user_table_id%TYPE;
1454 ln_usr_col_inst_id pay_user_column_instances.user_column_instance_id%TYPE;
1455 ln_user_row_id pay_user_rows_f.user_row_id%TYPE;
1456 ln_dsp_seq pay_user_rows_f.display_sequence%TYPE;
1457 ln_user_column_id pay_user_columns.user_column_id%TYPE;
1458 ln_ovn NUMBER;
1459
1460 BEGIN
1461 --{
1462
1463 ld_eff_date := fnd_date.canonical_to_date('1900/01/01 00:00:00');
1464
1465 lt_col_det_tab(1).col_name := 'Monthly Payroll Divisor';
1466 lt_col_det_tab(1).value := 12;
1467 lt_col_det_tab(2).col_name := 'Weekly Payroll Divisor';
1468 lt_col_det_tab(2).value := 52;
1469 lt_col_det_tab(3).col_name := 'Days Divisor';
1470 lt_col_det_tab(3).value := p_days_in_year;
1471 lt_col_det_tab(4).col_name := 'Annual Hours';
1472 lt_col_det_tab(4).value := p_days_in_year * 8;
1473
1474 SELECT user_table_id
1475 INTO ln_user_table_id
1476 FROM pay_user_tables
1477 WHERE user_table_name = 'PQP_CONTRACT_TYPES'
1478 AND (legislation_code is NULL
1479 OR legislation_code = 'MX');
1480
1481 IF (p_exists = FALSE)
1482 THEN
1483 --{
1484 SELECT NVL(max(display_sequence), 0)+1
1485 INTO ln_dsp_seq
1486 FROM pay_user_rows_f
1487 WHERE user_table_id = ln_user_table_id;
1488
1489 pay_user_row_api.create_user_row(
1490 p_validate => FALSE,
1491 p_effective_date => ld_eff_date,
1492 p_user_table_id => ln_user_table_id,
1493 p_row_low_range_or_name => p_contract_name,
1494 p_display_sequence => ln_dsp_seq,
1495 p_business_group_id => p_business_group_id,
1496 p_legislation_code => NULL,
1497 p_disable_range_overlap_check => FALSE,
1498 p_disable_units_check => FALSE,
1499 p_row_high_range => NULL,
1500 p_user_row_id => ln_user_row_id,
1501 p_object_version_number => ln_ovn,
1502 p_effective_start_date => ld_eff_start_date,
1503 p_effective_end_date => ld_eff_end_date,
1504 p_base_row_low_range_or_name => p_contract_name);
1505 --}
1506 ELSE
1507 --{
1508 SELECT user_row_id
1509 INTO ln_user_row_id
1510 FROM pay_user_rows_f
1511 WHERE row_low_range_or_name = p_contract_name
1512 AND user_table_id = ln_user_table_id
1513 AND ROWNUM = 1;
1514
1515 DELETE pay_user_column_instances_f
1516 WHERE user_row_id = ln_user_row_id;
1517
1518 --}
1519 END IF;
1520
1521
1522 FOR i in lt_col_det_tab.FIRST..lt_col_det_tab.LAST
1523 LOOP
1524 --{
1525 SELECT user_column_id
1526 INTO ln_user_column_id
1527 FROM pay_user_columns
1528 WHERE user_table_id = ln_user_table_id
1529 AND user_column_name = lt_col_det_tab(i).col_name;
1530
1531 pay_user_column_instance_api.create_user_column_instance(
1532 p_effective_date => ld_eff_date,
1533 p_user_row_id => ln_user_row_id,
1534 p_user_column_id => ln_user_column_id,
1535 p_value => lt_col_det_tab(i).value,
1536 p_business_group_id => p_business_group_id,
1537 p_user_column_instance_id => ln_usr_col_inst_id,
1538 p_object_version_number => ln_ovn,
1539 p_effective_start_date => ld_eff_start_date,
1540 p_effective_end_date => ld_eff_end_date);
1541 --}
1542 END LOOP;
1543 --}
1544 END create_contract;
1545
1546 FUNCTION get_contract_name(p_business_group_id NUMBER,
1547 p_tax_unit_id NUMBER,
1548 p_payroll_id NUMBER,
1549 p_calculation_date DATE)
1550
1551 RETURN VARCHAR2 IS
1552
1553 ln_days_year NUMBER;
1554 ln_days_month NUMBER;
1555 ln_legal_emp_id hr_all_organization_units.organization_id%TYPE;
1556 ln_contract_days pay_user_column_instances_f.value%TYPE;
1557
1558 lv_period_type pay_all_payrolls_f.period_type%TYPE;
1559 rv_contract_name VARCHAR2(80);
1560
1561 lb_contract_exists BOOLEAN;
1562 BEGIN
1563 --{
1564
1565 lb_contract_exists := TRUE;
1566 hr_utility.trace('Entering pay_mx_ff_udfs.get_contract_name');
1567 pay_mx_utility.get_no_of_days_for_org(
1568 p_business_group_id => p_business_group_id,
1569 p_org_id => p_tax_unit_id,
1570 p_gre_or_le => 'GRE',
1571 p_days_year => ln_days_year,
1572 p_days_month => ln_days_month);
1573
1574
1575 IF (ln_days_year is NULL)
1576 THEN
1577 --{
1578 ln_legal_emp_id := hr_mx_utility.get_legal_employer(
1579 p_business_group_id => p_business_group_id,
1580 p_tax_unit_id => p_tax_unit_id);
1581
1582 pay_mx_utility.get_no_of_days_for_org(
1583 p_business_group_id => p_business_group_id,
1584 p_org_id => ln_legal_emp_id,
1585 p_gre_or_le => 'LE',
1586 p_days_year => ln_days_year,
1587 p_days_month => ln_days_month);
1588
1589 hr_utility.trace('ln_days_year = '|| to_char(ln_days_year));
1590 IF (ln_days_year IS NULL)
1591 THEN
1592 --{
1593 SELECT period_type
1594 INTO lv_period_type
1595 FROM pay_all_payrolls_f ppf,
1596 fnd_sessions fs
1597 WHERE payroll_id = p_payroll_id
1598 AND fs.effective_date BETWEEN ppf.effective_start_date
1599 AND ppf.effective_end_date
1600 AND fs.session_id = USERENV('sessionid');
1601
1602 IF (lv_period_type like '%Week%')
1603 THEN
1604 --{
1605 rv_contract_name := 'IDW CALCULATION (WEEKLY PAYROLL)';
1606 --}
1607 ELSIF (lv_period_type like '%Month%')
1608 THEN
1609 --{
1610 rv_contract_name := 'IDW CALCULATION (MONTHLY PAYROLL)';
1611 --}
1612 ELSIF (lv_period_type = 'Ten Days')
1613 THEN
1614 --{
1615 rv_contract_name := 'IDW CALCULATION (Ten Days PAYROLL)';
1616 --}
1617 ELSE
1618 --{
1619 hr_utility.raise_error;
1620 --}
1621 END IF;
1622
1623 --}
1624 ELSE
1625 --{
1626 rv_contract_name := 'IDW CALCULATION (LE:'||
1627 TO_CHAR(ln_legal_emp_id)||')';
1628 --}
1629 END IF;
1630 --{
1631 ELSE
1632 --{
1633 rv_contract_name := 'IDW CALCULATION (GRE:'||
1634 TO_CHAR(p_tax_unit_id)||')';
1635 --}
1636 END IF;
1637
1638 clean_dupl_user_table_rows( p_user_table_name => 'PQP_CONTRACT_TYPES'
1639 ,p_row_value => rv_contract_name );
1640
1641 BEGIN
1642 --{
1643 ln_contract_days := NULL;
1644 hr_utility.trace('Getting contract days..');
1645 ln_contract_days := fnd_number.canonical_to_number(hruserdt.get_table_value(
1646 p_bus_group_id => p_business_group_id,
1647 p_table_name => 'PQP_CONTRACT_TYPES',
1648 p_col_name => 'Days Divisor',
1649 p_row_value => rv_contract_name,
1650 p_effective_date => p_calculation_date));
1651 hr_utility.trace('ln_contract_days = '|| TO_CHAR (ln_contract_days));
1652 EXCEPTION
1653 WHEN NO_DATA_FOUND THEN
1654 lb_contract_exists := FALSE;
1655 --}
1656 END;
1657
1658 IF (lb_contract_exists = FALSE OR ln_contract_days <> ln_days_year)
1659 THEN
1660 --{
1661 create_contract(p_business_group_id => p_business_group_id,
1662 p_contract_name => rv_contract_name,
1663 p_days_in_year => ln_days_year,
1664 p_exists => lb_contract_exists);
1665 --}
1666 END IF;
1667 hr_utility.trace('leaving pay_mx_ff_udfs.get_contract_name');
1668 RETURN rv_contract_name;
1669 --}
1670
1671 END get_contract_name;
1672
1673 BEGIN
1674
1675 OPEN c_get_rate_name(p_rate_code);
1676 FETCH c_get_rate_name INTO lv_rate_name;
1677 CLOSE c_get_rate_name;
1678
1679 lv_contract_name := get_contract_name(
1680 p_business_group_id => p_business_group_id,
1681 p_tax_unit_id => p_tax_unit_id,
1682 p_payroll_id => p_payroll_id,
1683 p_calculation_date => p_effective_date);
1684 hr_utility.trace('before getting the rate from pqp..');
1685 ln_rate := pqp_rates_history_calc.get_historic_rate(
1686 p_assignment_id => p_assignment_id,
1687 p_rate_name => lv_rate_name,
1688 p_effective_date => p_effective_date,
1689 p_time_dimension => 'D',
1690 p_rate_type_or_element => 'R',
1691 p_contract_type => lv_contract_name);
1692 hr_utility.trace('pqp_rates_history_calc.get_historic_rate');
1693 RETURN ln_rate;
1694
1695 EXCEPTION
1696 WHEN others THEN
1697 RAISE;
1698
1699 END get_mx_historic_rate;
1700
1701 FUNCTION get_daily_base_pay ( p_business_group_id NUMBER
1702 ,p_assignment_id NUMBER
1703 ,p_tax_unit_id NUMBER
1704 ,p_payroll_id NUMBER
1705 ,p_effective_date DATE )
1706 RETURN NUMBER IS
1707
1708 ln_daily_base_pay NUMBER;
1709
1710 BEGIN
1711
1712 hr_utility.trace('Get Daily Base Pay ');
1713
1714 -- Get the Base Pay using historic rates
1715 ln_daily_base_pay := 0;
1716 ln_daily_base_pay := get_mx_historic_rate (
1717 p_business_group_id => p_business_group_id
1718 ,p_assignment_id => p_assignment_id
1719 ,p_tax_unit_id => p_tax_unit_id
1720 ,p_payroll_id => p_payroll_id
1721 ,p_effective_date => p_effective_date
1722 ,p_rate_code => 'MX_BASE' );
1723
1724 hr_utility.trace('ln_daily_base_pay = '||to_char(ln_daily_base_pay));
1725
1726 RETURN ln_daily_base_pay;
1727
1728 EXCEPTION
1729 WHEN others THEN
1730 RAISE;
1731
1732 END get_daily_base_pay;
1733
1734 FUNCTION get_base_pay_for_tax_calc ( p_business_group_id NUMBER
1735 ,p_assignment_id NUMBER
1736 ,p_tax_unit_id NUMBER
1737 ,p_payroll_id NUMBER
1738 ,p_effective_date DATE
1739 ,p_month_or_pay_period VARCHAR2 )
1740 RETURN NUMBER IS
1741
1742 ln_base_pay NUMBER;
1743 ln_daily_base_pay NUMBER;
1744 ln_days_in_a_month NUMBER;
1745 lv_period_type pay_all_payrolls_f.period_type%TYPE;
1746
1747 BEGIN
1748 hr_utility.trace('Begin Get Base Pay for Tax Calculation');
1749
1750 -- Get the Base Pay using historic rates
1751 ln_daily_base_pay := 0;
1752 ln_daily_base_pay := get_daily_base_pay (
1753 p_business_group_id => p_business_group_id
1754 ,p_assignment_id => p_assignment_id
1755 ,p_tax_unit_id => p_tax_unit_id
1756 ,p_payroll_id => p_payroll_id
1757 ,p_effective_date => p_effective_date);
1758
1759 hr_utility.trace('ln_daily_base_pay = '||ln_daily_base_pay);
1760
1761 IF p_month_or_pay_period = 'MONTH' THEN
1762
1763 ln_days_in_a_month := pay_mx_utility.get_days_in_month(
1764 p_business_group_id => p_business_group_id
1765 ,p_tax_unit_id => p_tax_unit_id
1766 ,p_payroll_id => p_payroll_id);
1767
1768 ln_base_pay := ln_daily_base_pay * ln_days_in_a_month;
1769
1770 ELSE
1771
1772 SELECT period_type
1773 INTO lv_period_type
1774 FROM pay_all_payrolls_f ppf,
1775 fnd_sessions fs
1776 WHERE payroll_id = p_payroll_id
1777 AND fs.effective_date BETWEEN ppf.effective_start_date
1778 AND ppf.effective_end_date
1779 AND fs.session_id = USERENV('sessionid');
1780
1781 IF lv_period_type = 'Week' THEN
1782
1783 ln_base_pay := ln_daily_base_pay * 7;
1784
1785 ELSIF lv_period_type = 'Bi-Week' THEN
1786
1787 ln_base_pay := ln_daily_base_pay * 14;
1788
1789 ELSIF lv_period_type = 'Calendar Month' THEN
1790
1791 ln_days_in_a_month := pay_mx_utility.get_days_in_month(
1792 p_business_group_id => p_business_group_id
1793 ,p_tax_unit_id => p_tax_unit_id
1794 ,p_payroll_id => p_payroll_id);
1795
1796 ln_base_pay := ln_daily_base_pay * ln_days_in_a_month;
1797
1798 ELSIF lv_period_type = 'Semi-Month' THEN
1799
1800 ln_base_pay := ln_daily_base_pay * 15;
1801
1802 ELSIF lv_period_type = 'Ten Days' THEN
1803
1804 ln_base_pay := ln_daily_base_pay * 10;
1805
1806 END IF;
1807
1808
1809 END IF;
1810
1811 hr_utility.trace('ln_base_pay = '|| ln_base_pay);
1812 hr_utility.trace('End Get Base Pay for Tax Calculation');
1813
1814 RETURN ( ln_base_pay );
1815
1816 EXCEPTION
1817 WHEN others THEN
1818 RAISE;
1819 END get_base_pay_for_tax_calc;
1820
1821
1822 END pay_mx_ff_udfs;