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