1 PACKAGE BODY pay_mx_utility AS
2 /* $Header: pymxutil.pkb 120.6.12010000.3 2008/11/25 13:50:44 vvijayku ship $ */
3
4 --
5 -- Global Variables
6 --
7 g_package_name VARCHAR2(240);
8 g_debug BOOLEAN;
9
10
11 /**********************************************************************
12 ** Name : get_days_bal_type_id
13 ** Purpose : This function returns Balance Type ID of Days Balance
14 ** for Mexico.
15 ** Arguments : IN Parameters
16 ** p_balance_type_id -> Balance Type ID of Primary Balance
17 ** Notes :
18 **********************************************************************/
19
20 FUNCTION get_days_bal_type_id (p_balance_type_id IN NUMBER)
21 RETURN NUMBER IS
22
23 cursor c_days_bal_type_id (cp_balance_type_id NUMBER) is
24 select pbt.balance_type_id, pbt.balance_uom
25 from pay_balance_types pbt,
26 pay_defined_balances pdb,
27 pay_balance_attributes pba,
28 pay_bal_attribute_definitions pbad
29 where pbad.attribute_name = 'Earnings Days'
30 and pbad.business_group_id is null
31 and pbad.legislation_code = 'MX'
32 and pba.attribute_id = pbad.attribute_id
33 and pdb.defined_balance_id = pba.defined_balance_id
34 and pbt.balance_type_id = pdb.balance_type_id
35 and pbt.base_balance_type_id = cp_balance_type_id;
36
37 ln_days_bal_type_id NUMBER;
38 lv_balance_uom VARCHAR2(240);
39
40 ln_found NUMBER;
41 ln_index NUMBER;
42 BEGIN
43
44 --hr_utility.trace_on( NULL, 'BAL');
45
46 ln_found := 0;
47
48 hr_utility.trace( 'COUNT '|| pay_mx_utility.days_bal_tbl.count);
49
50 if pay_mx_utility.days_bal_tbl.count > 0 then
51
52 for i in pay_mx_utility.days_bal_tbl.first ..
53 pay_mx_utility.days_bal_tbl.last
54 loop
55
56 if pay_mx_utility.days_bal_tbl(i).bal_type_id = p_balance_type_id then
57
58 ln_days_bal_type_id :=
59 pay_mx_utility.days_bal_tbl(i).days_bal_type_id;
60 lv_balance_uom := pay_mx_utility.days_bal_tbl(i).days_bal_uom;
61 ln_found := 1;
62
63 end if;
64
65 hr_utility.trace( 'p_balance_type_id '||p_balance_type_id);
66 hr_utility.trace( 'BAL TYPE ID '||
67 pay_mx_utility.days_bal_tbl(i).days_bal_type_id);
68
69 end loop;
70
71 end if;
72
73 if ln_found = 0 then
74
75 open c_days_bal_type_id(p_balance_type_id);
76 fetch c_days_bal_type_id into ln_days_bal_type_id, lv_balance_uom;
77 close c_days_bal_type_id;
78
79 ln_index := pay_mx_utility.days_bal_tbl.count;
80
81 pay_mx_utility.days_bal_tbl(ln_index).bal_type_id := p_balance_type_id;
82 pay_mx_utility.days_bal_tbl(ln_index).days_bal_type_id :=
83 ln_days_bal_type_id;
84 pay_mx_utility.days_bal_tbl(ln_index).days_bal_uom := lv_balance_uom;
85
86 hr_utility.trace( 'DAYS BAL TYPE ID '||ln_days_bal_type_id);
87 end if;
88
89 return ln_days_bal_type_id;
90
91 END get_days_bal_type_id;
92
93 /**********************************************************************
94 ** Name : get_hours_bal_type_id
95 ** Purpose : This function returns Balance Type ID of Hours Balance
96 ** for Mexico.
97 ** Arguments : IN Parameters
98 ** p_balance_type_id -> Balance Type ID of Primary Balance
99 ** Notes :
100 **********************************************************************/
101
102 FUNCTION get_hours_bal_type_id (p_balance_type_id IN NUMBER)
103 RETURN NUMBER IS
104
105 cursor c_hours_bal_type_id (cp_balance_type_id NUMBER) is
106 select pbt.balance_type_id, pbt.balance_uom
107 from pay_balance_types pbt,
108 pay_defined_balances pdb,
109 pay_balance_attributes pba,
110 pay_bal_attribute_definitions pbad
111 where pbad.attribute_name = 'Earnings Hours'
112 and pbad.business_group_id is null
113 and pbad.legislation_code = 'MX'
114 and pba.attribute_id = pbad.attribute_id
115 and pdb.defined_balance_id = pba.defined_balance_id
116 and pbt.balance_type_id = pdb.balance_type_id
117 and pbt.base_balance_type_id = cp_balance_type_id;
118
119 ln_hours_bal_type_id NUMBER;
120 lv_balance_uom VARCHAR2(240);
121
122 ln_found NUMBER;
123 ln_index NUMBER;
124 BEGIN
125
126 --hr_utility.trace_on( NULL, 'BAL');
127
128 ln_found := 0;
129
130 hr_utility.trace( 'COUNT '|| pay_mx_utility.hours_bal_tbl.count);
131
132 /*
133 if pay_mx_utility.hours_bal_tbl.count > 0 then
134
135 for i in pay_mx_utility.hours_bal_tbl.first ..
136 pay_mx_utility.hours_bal_tbl.last
137 loop
138
139 if pay_mx_utility.hours_bal_tbl(i).bal_type_id = p_balance_type_id
140 then
141
142 ln_hours_bal_type_id :=
143 pay_mx_utility.hours_bal_tbl(i).hours_bal_type_id;
144 lv_balance_uom := pay_mx_utility.hours_bal_tbl(i).hours_bal_uom;
145 ln_found := 1;
146
147 end if;
148
149 hr_utility.trace( 'p_balance_type_id '||p_balance_type_id);
150 hr_utility.trace( 'BAL TYPE ID '||
151 pay_mx_utility.hours_bal_tbl(i).hours_bal_type_id);
152
153 end loop;
154
155 end if;
156 */
157
158 IF (pay_mx_utility.hours_bal_tbl.EXISTS(p_balance_type_id) = FALSE) THEN
159
160
161 open c_hours_bal_type_id(p_balance_type_id);
162 fetch c_hours_bal_type_id into ln_hours_bal_type_id, lv_balance_uom;
163 close c_hours_bal_type_id;
164
165 ln_index := pay_mx_utility.hours_bal_tbl.count;
166
167 pay_mx_utility.hours_bal_tbl(p_balance_type_id).bal_type_id :=
168 p_balance_type_id;
169 pay_mx_utility.hours_bal_tbl(p_balance_type_id).hours_bal_type_id :=
170 ln_hours_bal_type_id;
171 pay_mx_utility.hours_bal_tbl(p_balance_type_id).hours_bal_uom :=
172 lv_balance_uom;
173
174 hr_utility.trace( 'HOURS BAL TYPE ID '||ln_hours_bal_type_id);
175
176 END IF;
177
178 ln_hours_bal_type_id :=
179 pay_mx_utility.hours_bal_tbl(p_balance_type_id).hours_bal_type_id;
180 lv_balance_uom :=
181 pay_mx_utility.hours_bal_tbl(p_balance_type_id).hours_bal_uom;
182
183 return ln_hours_bal_type_id;
184
185 END get_hours_bal_type_id;
186
187
188 /**********************************************************************
189 ** Type : Procedure
190 ** Name : get_days_yr_for_pay_period
191 ** Purpose : This procedure populates payroll_period_type PL/SQL table
192 ** for the period type of the payroll and its number of
193 ** days. (PL/SQL table structure mentioned above)
194 **
195 ** Arguments : IN Parameters
196 ** p_payroll_id -> Payroll ID
197 **
198 ** OUT Parameters
199 ** p_period_type -> Period Type of the payroll
200 ** p_days_year -> No. of Days in Year for the payroll
201 **
202 ** Notes :
203 **********************************************************************/
204
205 PROCEDURE get_days_yr_for_pay_period( p_payroll_id IN NUMBER
206 ,p_period_type OUT NOCOPY VARCHAR2
207 ,p_days_year OUT NOCOPY NUMBER)
208 IS
209
210 CURSOR c_period_type IS
211 SELECT period_type, legislation_info2
212 FROM per_time_period_types ptpt,
213 pay_mx_legislation_info_f pmli
214 WHERE legislation_info_type = 'MX Annualization Factor'
215 AND instr(period_type,legislation_info1) > 0;
216
217 CURSOR c_pay_prd_type(cp_payroll_id NUMBER) IS
218 SELECT period_type
219 FROM pay_payrolls_f
220 WHERE payroll_id = cp_payroll_id;
221
222 lv_name varchar2(150);
223 ln_nod number;
224 i number;
225
226 lv_prd_type varchar2(150);
227 lv_proc VARCHAR2(240);
228 BEGIN
229
230 i := 0;
231
232 lv_proc := g_package_name || 'get_days_yr_for_pay_period';
233
234 IF (g_debug) THEN
235 hr_utility.trace('Entering '||lv_proc);
236 END IF;
237
238 IF py_prd_tp.count = 0 THEN
239
240 OPEN c_period_type;
241 LOOP
242
243 FETCH c_period_type INTO lv_name, ln_nod;
244 EXIT WHEN c_period_type%NOTFOUND;
245
246 py_prd_tp(i).name := lv_name;
247 py_prd_tp(i).days := ln_nod;
248
249 i := i + 1;
250
251 END LOOP;
252
253 CLOSE c_period_type;
254
255 END IF;
256
257 OPEN c_pay_prd_type(p_payroll_id);
258 FETCH c_pay_prd_type INTO lv_prd_type;
259 CLOSE c_pay_prd_type;
260
261 IF py_prd_tp.count <> 0 THEN
262
263 FOR i in py_prd_tp.FIRST..py_prd_tp.LAST
264 LOOP
265
266 -- Bug 4348355 - Modified condition since MX Annualization Factor
267 -- can only be 'Week' or 'Month'
268 --
269 -- IF py_prd_tp(i).name = lv_prd_type THEN
270
271 IF INSTR(lv_prd_type, py_prd_tp(i).name) > 0 THEN
272
273 p_period_type := py_prd_tp(i).name;
274 p_days_year := py_prd_tp(i).days;
275
276 END IF;
277
278 END LOOP;
279
280 END IF;
281
282 IF (g_debug) THEN
283 hr_utility.trace('Leaving '||lv_proc);
284 END IF;
285
286 END get_days_yr_for_pay_period;
287
288
289 /**********************************************************************
290 ** Type : Procedure
291 ** Name : get_no_of_days_for_org
292 ** Purpose : This procedure popuate number_of_days PL/SQL table
293 ** for the Month and the Year for GRE or Legal Employer.
294 ** (PL/SQL table structure mentioned above)
295 **
296 ** Arguments : IN Parameters
297 ** p_business_group_id -> Business Group ID
298 ** p_tax_unit_id -> Tax Unit ID
299 **
300 ** OUT Parameters
301 ** p_days_month -> No. of Days in Month
302 ** p_days_year -> No. of Days in Year
303 **
304 ** Notes :
305 **********************************************************************/
306
307 PROCEDURE get_no_of_days_for_org( p_business_group_id IN NUMBER
308 ,p_org_id IN NUMBER
309 ,p_gre_or_le IN VARCHAR2
310 ,p_days_month OUT NOCOPY NUMBER
311 ,p_days_year OUT NOCOPY NUMBER)
312 IS
313
314 CURSOR c_gre_days( cp_organization_id number ) IS
315 SELECT fnd_number.canonical_to_number(nvl(org_information8,'-999')),
316 fnd_number.canonical_to_number(nvl(org_information9,'-999'))
317 FROM hr_organization_information
318 WHERE organization_id = cp_organization_id
319 AND org_information_context = 'MX_SOC_SEC_DETAILS';
320
321 CURSOR c_le_days( cp_organization_id number ) IS
322 SELECT fnd_number.canonical_to_number(nvl(org_information4,'-999')),
323 fnd_number.canonical_to_number(nvl(org_information5,'-999'))
324 FROM hr_organization_information
325 WHERE organization_id = cp_organization_id
326 AND org_information_context = 'MX_TAX_REGISTRATION';
327
328 ln_leg_emplyr number;
329 ln_nod_month number;
330 ln_nod_year number;
331
332 lv_proc VARCHAR2(240);
333
334 BEGIN
335
336 p_days_month := 0;
337 p_days_year := 0;
338
339 lv_proc := g_package_name || 'get_no_of_days_for_org';
340
341 IF (g_debug) THEN
342 hr_utility.trace('Entering '||lv_proc);
343 END IF;
344
345 IF (p_gre_or_le = 'GRE') THEN
346
347 IF (gre_no_of_days.EXISTS(p_org_id) = FALSE) THEN
348
349 OPEN c_gre_days(p_org_id);
350 FETCH c_gre_days INTO ln_nod_month, ln_nod_year;
351
352 IF (c_gre_days%FOUND AND ln_nod_month <> -999 AND ln_nod_year <> -999)
353 THEN
354
355 gre_no_of_days(p_org_id).days_month := ln_nod_month;
356 gre_no_of_days(p_org_id).days_year := ln_nod_year;
357
358 END IF;
359
360 CLOSE c_gre_days;
361
362 END IF;
363
364 p_days_month := gre_no_of_days(p_org_id).days_month;
365 p_days_year := gre_no_of_days(p_org_id).days_year;
366
367 ELSE
368
369 IF (le_no_of_days.EXISTS(p_org_id) = FALSE) THEN
370
371 OPEN c_le_days(p_org_id);
372 FETCH c_le_days INTO ln_nod_month, ln_nod_year;
373
374 IF (c_le_days%FOUND AND ln_nod_month <> -999 AND ln_nod_year <> -999)
375 THEN
376
377 le_no_of_days(p_org_id).days_month := ln_nod_month;
378 le_no_of_days(p_org_id).days_year := ln_nod_year;
379
380 /* Bug 4348355*/
381 ELSIF (c_le_days%FOUND AND ln_nod_month = -999 AND ln_nod_year <> -999)
382 THEN
383
384 le_no_of_days(p_org_id).days_year := ln_nod_year;
385
386 ELSIF (c_le_days%FOUND AND ln_nod_month <> -999 AND ln_nod_year = -999)
387 THEN
388
389 le_no_of_days(p_org_id).days_month := ln_nod_month;
390
391 END IF;
392
393 CLOSE c_le_days;
394
395 END IF;
396
397 p_days_month := le_no_of_days(p_org_id).days_month;
398 p_days_year := le_no_of_days(p_org_id).days_year;
399
400 END IF;
401
402 IF (g_debug) THEN
403 hr_utility.trace('Leaving '||lv_proc);
404 END IF;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 p_days_month := NULL;
409 p_days_year := NULL;
410 END get_no_of_days_for_org;
411
412 /**********************************************************************
413 ** Type : Function
414 ** Name : get_days_month_year
415 ** Purpose : This function returns number of days based on p_mode.
416 ** If p_mode is 'MONTH', this function returns no of days
417 ** in month and if it is 'YEAR', this function return
418 ** returns no of days in year.
419 **
420 ** Arguments : IN Parameters
421 ** p_business_group_id -> Business Group ID
422 ** p_tax_unit_id -> Tax Unit ID
423 ** p_payroll_id -> Payroll ID
424 ** p_mode -> 'MONTH' or 'YEAR'
425 **
426 ** Notes :
427 **********************************************************************/
428
429 FUNCTION get_days_month_year( p_business_group_id IN NUMBER
433 RETURN NUMBER IS
430 ,p_tax_unit_id IN NUMBER
431 ,p_payroll_id IN NUMBER
432 ,p_mode IN VARCHAR2 )
434
435 ln_days_month NUMBER;
436 ln_days_year NUMBER;
437 ln_le_id hr_all_organization_units.organization_id%TYPE;
438
439 ln_days_month_year NUMBER;
440 lv_period_type VARCHAR2(150);
441 lv_proc VARCHAR2(240);
442
443 CURSOR c_actual_days_of_month IS
444 SELECT TO_NUMBER(TO_CHAR(LAST_DAY(effective_date),'DD'))
445 FROM fnd_sessions
446 WHERE session_id = USERENV('SESSIONID') ;
447
448 BEGIN
449
450 ln_days_month := NULL;
451 ln_days_year := NULL;
452
453 lv_proc := g_package_name || 'get_days_month_year';
454
455 IF (g_debug) THEN
456 hr_utility.trace('Entering '||lv_proc);
457 END IF;
458
459
460 get_no_of_days_for_org( p_business_group_id => p_business_group_id
461 ,p_org_id => p_tax_unit_id
462 ,p_gre_or_le => 'GRE'
463 ,p_days_month => ln_days_month
464 ,p_days_year => ln_days_year);
465
466
467 IF (p_mode = 'MONTH' AND ln_days_month IS NULL) OR
468 (p_mode = 'YEAR' AND ln_days_year IS NULL) THEN
469
470 ln_le_id := hr_mx_utility.get_legal_employer(
471 p_business_group_id => p_business_group_id
472 ,p_tax_unit_id => p_tax_unit_id);
473
474 get_no_of_days_for_org( p_business_group_id => p_business_group_id
475 ,p_org_id => ln_le_id
476 ,p_gre_or_le => 'LE'
477 ,p_days_month => ln_days_month
478 ,p_days_year => ln_days_year);
479
480 END IF;
481
482 IF (p_mode = 'YEAR' AND ln_days_year IS NULL) THEN
483
484 get_days_yr_for_pay_period( p_payroll_id => p_payroll_id
485 ,p_period_type => lv_period_type
486 ,p_days_year => ln_days_year);
487
488 ELSIF (p_mode = 'MONTH' AND ln_days_month IS NULL) THEN
489
490 -- Changed the logic to get the actual number of days of the month
491 -- actual number of days is taken from the effective date of fnd_sessions
492 -- as payroll being processed the effective_date will be inserted in the
493 -- fnd_sessions table
494
495 OPEN c_actual_days_of_month ;
496 FETCH c_actual_days_of_month INTO ln_days_month ;
497 -- some reason it gets null then defaulting it to 30 average number of days
498 IF ln_days_month IS NULL THEN
499 ln_days_month := 30 ;
500 END IF;
501 CLOSE c_actual_days_of_month;
502
503 END IF;
504
505 IF p_mode = 'YEAR' THEN
506
507 ln_days_month_year := ln_days_year;
508
509 ELSE
510
511 ln_days_month_year := ln_days_month;
512
513 END IF;
514
515 IF (g_debug) THEN
516 hr_utility.trace('Leaving '||lv_proc);
517 END IF;
518
519 RETURN ln_days_month_year;
520
521 END get_days_month_year;
522
523 /**********************************************************************
524 ** Type : Function
525 ** Name : get_days_in_year
526 ** Purpose : This function returns number of days based in year.
527 ** This function calls get_days_month_year function
528 ** with p_mode 'YEAR'.
529 **
530 ** Arguments : IN Parameters
531 ** p_business_group_id -> Business Group ID
532 ** p_tax_unit_id -> Tax Unit ID
533 ** p_payroll_id -> Payroll ID
534 **
535 ** Notes :
536 **********************************************************************/
537
538 FUNCTION get_days_in_year( p_business_group_id IN NUMBER
539 ,p_tax_unit_id IN NUMBER
540 ,p_payroll_id IN NUMBER)
541 RETURN NUMBER IS
542
543 ln_days NUMBER;
544 lv_proc VARCHAR2(240);
545
546 BEGIN
547
548 lv_proc := g_package_name || 'get_days_in_year';
549
550 IF (g_debug) THEN
551 hr_utility.trace('Entering '||lv_proc);
552 END IF;
553
554 ln_days := get_days_month_year(
555 p_business_group_id => p_business_group_id
556 ,p_tax_unit_id => p_tax_unit_id
557 ,p_payroll_id => p_payroll_id
558 ,p_mode => 'YEAR' );
559
560 IF (g_debug) THEN
561 hr_utility.trace('Leaving '||lv_proc);
562 END IF;
563
564 RETURN ln_days;
565
566 END get_days_in_year;
567
568 /**********************************************************************
569 ** Type : Function
570 ** Name : get_days_in_month
571 ** Purpose : This function returns number of days based in month.
572 ** This function calls get_days_month_year function
573 ** with p_mode 'MONTH'.
574 **
575 ** Arguments : IN Parameters
576 ** p_business_group_id -> Business Group ID
577 ** p_tax_unit_id -> Tax Unit ID
578 ** p_payroll_id -> Payroll ID
579 **
580 ** Notes :
581 **********************************************************************/
582
583 FUNCTION get_days_in_month( p_business_group_id IN NUMBER
584 ,p_tax_unit_id IN NUMBER
585 ,p_payroll_id IN NUMBER)
586 RETURN NUMBER IS
587
588 ln_days NUMBER;
589 lv_proc VARCHAR2(240);
590
591 BEGIN
592
593 lv_proc := g_package_name || 'get_days_in_month';
594
595 IF (g_debug) THEN
596 hr_utility.trace('Entering '||lv_proc);
597 END IF;
598
599 ln_days := get_days_month_year(
600 p_business_group_id => p_business_group_id
601 ,p_tax_unit_id => p_tax_unit_id
602 ,p_payroll_id => p_payroll_id
603 ,p_mode => 'MONTH' );
604
605 IF (g_debug) THEN
606 hr_utility.trace('Leaving '||lv_proc);
607 END IF;
608
609 RETURN ln_days;
610
611 END get_days_in_month;
612
613 /**********************************************************************
614 ** Type : Function
615 ** Name : get_days_in_pay_period
616 ** Purpose : This function returns number of days based on payroll
617 ** frequency.
618 ** Week -> 7 Days
619 ** Bi-Week -> 14 Days
620 ** Month -> Getting no of days using get_days_in_month
621 ** Semi-Month -> Month Days (above) / 2
622 **
623 ** Arguments : IN Parameters
624 ** p_business_group_id -> Business Group ID
625 ** p_tax_unit_id -> Tax Unit ID
626 ** p_payroll_id -> Payroll ID
627 **
628 ** Notes :
629 **********************************************************************/
630
631 FUNCTION get_days_in_pay_period( p_business_group_id IN NUMBER
632 ,p_tax_unit_id IN NUMBER
633 ,p_payroll_id IN NUMBER)
634 RETURN NUMBER IS
635
636
637 CURSOR c_pay_prd_type(cp_payroll_id NUMBER) IS
638 SELECT period_type
639 FROM pay_payrolls_f
640 WHERE payroll_id = cp_payroll_id;
641
642 CURSOR c_get_days_total IS
643 SELECT to_number(to_char(effective_date,'DD')),
644 to_number(to_char(last_day(effective_date),'DD'))
645 from fnd_sessions
646 where session_id = USERENV('sessionid') ;
647
648
649 lv_prd_type VARCHAR2(150);
650 ln_month_days NUMBER;
651 ln_days NUMBER;
652
653 ln_days_mth NUMBER;
654 ln_total_days_mth NUMBER ;
655
656 lv_proc VARCHAR2(240);
657
658 BEGIN
659
660 lv_proc := g_package_name || 'get_days_in_pay_period';
661
662 IF (g_debug) THEN
663 hr_utility.trace('Entering '||lv_proc);
664 END IF;
665
666 OPEN c_pay_prd_type(p_payroll_id);
667 FETCH c_pay_prd_type INTO lv_prd_type;
668 CLOSE c_pay_prd_type;
669
670 IF instr(lv_prd_type,'Month') > 0 THEN
671
672 ln_month_days := get_days_in_month(
673 p_business_group_id => p_business_group_id
677
674 ,p_tax_unit_id => p_tax_unit_id
675 ,p_payroll_id => p_payroll_id);
676
678 END IF;
679
680 IF lv_prd_type = 'Week' THEN
681
682 ln_days := 7;
683
684 ELSIF lv_prd_type = 'Ten Days' THEN
685
686 ln_days := 10;
687
688 ELSIF lv_prd_type = 'Bi-Week' THEN
689
690 ln_days := 14;
691
692 ELSIF lv_prd_type = 'Calendar Month' THEN
693
694 ln_days := ln_month_days;
695
696 ELSIF lv_prd_type = 'Semi-Month' THEN
697 --
698 -- ln_days := ln_month_days / 2;
699 --
700 -- Changed the logic to return 15 if date earned is less than or equal to 15
701 -- else it is the difference from total no of days in the month
702 -- ie payroll is processing on 15th of the month then return 15
703 -- else return the difference from total no of days in the month
704 -- ie if 15-Jan-2005 then 15
705 -- if 31-Jan-2005 then 31-15=16 days
706 -- if 28-Feb-2005 then 28-15=13 days
707 -- This should be considered only when ln_month_days equal to 30
708
709 OPEN c_get_days_total ;
710 fetch c_get_days_total into ln_days_mth, ln_total_days_mth ;
711 close C_get_days_total ;
712
713 if (ln_days_mth > 15 AND ln_month_days <> 30) then
714 ln_days := ln_total_days_mth - 15 ;
715 else
716 ln_days := 15 ;
717 end if;
718
719
720 END IF;
721
722 IF (g_debug) THEN
723 hr_utility.trace('Leaving '||lv_proc);
724 END IF;
725
726 RETURN ln_days;
727
728 END get_days_in_pay_period;
729
730 /**********************************************************************
731 ** Type : Function
732 ** Name : get_days_in_bimonth
733 ** Purpose : This function returns number of days for current and
734 ** previous month.
735 ** If payroll processsing is on 15-APR-2005 then this function
736 ** will return 30 (for april 2005) + 31 (for mar 2005) = 61
737 ** days.
738 ** Notes :
739 **********************************************************************/
740 FUNCTION get_days_in_bimonth
741 RETURN NUMBER IS
742
743 ln_days NUMBER;
744 lv_proc VARCHAR2(240);
745
746 CURSOR c_get_days_in_bimonth IS
747 SELECT to_number(to_char(last_day(ADD_MONTHS(effective_date,-1)),'DD')) +
748 to_number(to_char(last_day(effective_date),'DD'))
749 from fnd_sessions
750 where session_id = USERENV('sessionid') ;
751
752 BEGIN
753
754 lv_proc := g_package_name || 'get_days_in_bimonth';
755
756 IF (g_debug) THEN
757 hr_utility.trace('Entering '||lv_proc);
758 END IF;
759
760 open c_get_days_in_bimonth ;
761 fetch c_get_days_in_bimonth into ln_days ;
762 close c_get_days_in_bimonth ;
763
764 IF (g_debug) THEN
765 hr_utility.trace('Leaving '||lv_proc);
766 END IF;
767
768 RETURN ln_days;
769
770 END get_days_in_bimonth;
771
772
773
774 /**********************************************************************
775 ** Type : Function
776 ** Name : get_classification_id
777 ** Purpose : This function returns classification_id for Mexico.
778 **
779 ** Arguments : IN Parameters
780 ** p_classification_name -> Classification Name.
781 ** Notes :
782 **********************************************************************/
783 FUNCTION get_classification_id( p_classification_name IN VARCHAR2 )
784 RETURN NUMBER IS
785
786 CURSOR get_class_id( cp_classification_name VARCHAR2 ) IS
787 SELECT classification_id
788 FROM pay_element_classifications
789 WHERE legislation_code = 'MX'
790 AND classification_name = cp_classification_name;
791
792 l_classification_id NUMBER;
793
794 BEGIN -- get_classification_id
795
796 OPEN get_class_id( p_classification_name );
797 FETCH get_class_id INTO l_classification_id;
798 CLOSE get_class_id;
799
800 RETURN l_classification_id;
801
802 END get_classification_id;
803
804 /**********************************************************************
805 ** Type : Procedure
806 ** Name : create_ele_tmplt_class_usg
807 ** Purpose : This procedure creates records for
808 ** PAY_ELE_TMPLT_CLASS_USAGES table.
809 **
810 ** Arguments : IN Parameters
811 ** p_classification_id -> Classification ID
812 ** p_template_id -> Template ID
813 ** p_display_process_mode -> Display Process Mode
814 ** p_display_arrearage -> Display Arrearage
815 ** Notes :
816 **********************************************************************/
817 PROCEDURE create_ele_tmplt_class_usg( p_classification_id IN NUMBER
818 ,p_template_id IN NUMBER
819 ,p_display_process_mode IN VARCHAR2
820 ,p_display_arrearage IN VARCHAR2 )
821 IS
822
823 ln_exists NUMBER;
824 ln_ele_tmplt_class_id NUMBER;
825
826 BEGIN --create_ele_tmplt_class_usg
827
828 SELECT COUNT(*)
829 INTO ln_exists
830 FROM pay_ele_tmplt_class_usages
831 WHERE classification_id = p_classification_id
832 AND template_id = p_template_id;
833
834 hr_utility.trace('ln_exists ' ||ln_exists);
835
836 IF ln_exists = 0 THEN
837
838 SELECT pay_ele_tmplt_class_usg_s.nextval
839 INTO ln_ele_tmplt_class_id
840 FROM dual;
841
842 hr_utility.trace('ln_ele_tmplt_class_id ' ||ln_ele_tmplt_class_id);
843
844 INSERT INTO pay_ele_tmplt_class_usages
845 ( ele_template_classification_id
846 ,classification_id
847 ,template_id
848 ,display_process_mode
849 ,display_arrearage )
850 VALUES ( ln_ele_tmplt_class_id
851 ,p_classification_id
852 ,p_template_id
853 ,p_display_process_mode
854 ,p_display_arrearage );
855
856 END IF;
857
858 END create_ele_tmplt_class_usg;
859
860 /**********************************************************************
861 ** Type : Procedure
862 ** Name : create_template_classification
863 ** Purpose : This procedure is getting called from the template
864 ** with Template ID and Classification Type and will
865 ** decides how many record to be created for
866 ** PAY_ELE_TMPLT_CLASS_USAGES table.
867 **
868 ** Arguments : IN Parameters
869 ** p_template_id -> Template ID
870 ** p_classification_type -> Display Process Mode
871 ** Notes :
872 **********************************************************************/
873 PROCEDURE create_template_classification( p_template_id IN NUMBER
874 ,p_classification_type IN VARCHAR2)
875 IS
876 TYPE char_tabtype IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
877
878 l_classification_name char_tabtype;
879 l_display_proc_mode char_tabtype;
880 l_display_arrearage char_tabtype;
881
882 l_classification_id NUMBER;
883
884 BEGIN --create_template_classification
885
886 IF p_classification_type = 'Earnings' THEN
887
888 l_classification_name(1) := 'Earnings';
889 l_display_proc_mode(1) := 'Y';
890 l_display_arrearage(1) := NULL;
891
892 l_classification_name(2) := 'Supplemental Earnings';
893 l_display_proc_mode(2) := 'Y';
894 l_display_arrearage(2) := NULL;
895
896 l_classification_name(3) := 'Imputed Earnings';
897 l_display_proc_mode(3) := 'Y';
898 l_display_arrearage(3) := NULL;
899
900 l_classification_name(4) := 'Amends';
901 l_display_proc_mode(4) := 'Y';
902 l_display_arrearage(4) := NULL;
903
904 /* l_classification_name(5) := 'Employer Liabilities';
905 l_display_proc_mode(5) := 'Y';
906 l_display_arrearage(5) := NULL;
907 */
908 ELSIF p_classification_type = 'Deductions' THEN
909
910 l_classification_name(1) := 'Voluntary Deductions';
911 l_display_proc_mode(1) := NULL;
915 l_display_proc_mode(2) := NULL;
912 l_display_arrearage(1) := 'Y';
913
914 l_classification_name(2) := 'Pre-Tax Deductions';
916 l_display_arrearage(2) := 'Y';
917
918 l_classification_name(3) := 'Involuntary Deductions';
919 l_display_proc_mode(3) := NULL;
920 l_display_arrearage(3) := 'Y';
921
922 END IF;
923
924 --hr_utility.trace_on(null,'ETCU');
925
926 FOR i IN l_classification_name.FIRST..l_classification_name.LAST
927 LOOP
928
929 l_classification_id := get_classification_id( l_classification_name(i) );
930
931 hr_utility.trace('------------------------------------------------');
932 hr_utility.trace('i '|| i );
933 hr_utility.trace('l_classification_name '|| l_classification_name(i));
934 hr_utility.trace('l_classification_id '|| l_classification_id);
935 hr_utility.trace('p_template_id '|| p_template_id);
936 hr_utility.trace('l_display_proc_mode '|| l_display_proc_mode(i));
937 hr_utility.trace('l_display_arrearage '|| l_display_arrearage(i));
938
939
940 create_ele_tmplt_class_usg(
941 p_classification_id => l_classification_id
942 ,p_template_id => p_template_id
943 ,p_display_process_mode => l_display_proc_mode(i)
944 ,p_display_arrearage => l_display_arrearage(i) );
945
946 END LOOP;
947
948 END create_template_classification;
949
950 /**********************************************************************
951 ** Type : Function
952 ** Name : get_default_imp_date
953 ** Purpose : This function is returning Implementation Date.
954 ** Using in Social Security Archiver.
955 **
956 ** Arguments :
957 ** Notes :
958 **********************************************************************/
959 FUNCTION get_default_imp_date
960 RETURN VARCHAR2 IS
961
962 CURSOR c_get_def_imp_date IS
963 SELECT fnd_date.canonical_to_date(legislation_info1)
964 FROM pay_mx_legislation_info_f
965 WHERE legislation_info_type = 'MX Social Security Reporting' ;
966
967 ld_def_date date ;
968
969 BEGIN
970
971 OPEN c_get_def_imp_date;
972 FETCH c_get_def_imp_date INTO ld_def_date;
973 CLOSE c_get_def_imp_date;
974
975 RETURN fnd_date.date_to_canonical(ld_def_date) ;
976
977 END get_default_imp_date;
978
979 /**********************************************************************
980 ** Type : Function
981 ** Name : get_parameter
982 ** Purpose : This function gets Paramter Value from
983 ** legislation_parameters column of pay_payroll_actions
984 ** WHENEVER TWO PARAMETERS ARE SEPARATED BY A SPACE
985 **
986 ** WARNING : IF THERE IS A PIPE (OTHER THAN A SPACE)IN THE VALUE
987 ** THEN DONOT USE THIS FUNCTION
988 **
989 ** Arguments :
990 ** Notes :
991 **********************************************************************/
992 FUNCTION get_parameter(name IN VARCHAR2,
993 parameter_list IN VARCHAR2)
994 RETURN VARCHAR2 IS
995
996 par_value pay_payroll_actions.legislative_parameters%type;
997
998 BEGIN
999
1000 par_value := get_legi_param_val(name
1001 ,parameter_list
1002 ,' ');
1003
1004 RETURN par_value;
1005
1006 END get_parameter;
1007
1008
1009 /**********************************************************************
1010 ** Type : Function
1011 ** Name : get_legi_param_val
1012 ** Purpose : This function gets Paramter Value from
1013 ** legislation_parameters column of pay_payroll_actions
1014 ** WHENEVER TWO PARAMETERS ARE SEPARATED BY A PIPE (|)
1015 **
1016 ** WARNING : IF THERE IS A SPACE IN THE VALUE
1017 ** THEN DONOT USE THIS FUNCTION
1018 **
1019 ** Arguments :
1020 ** Notes :
1021 **********************************************************************/
1022 FUNCTION get_legi_param_val(name IN VARCHAR2,
1023 parameter_list IN VARCHAR2)
1024 RETURN VARCHAR2 IS
1025
1026 par_value pay_payroll_actions.legislative_parameters%type;
1027
1028 BEGIN
1029
1030 par_value := get_legi_param_val(name
1031 ,parameter_list
1032 ,'|');
1033
1034 RETURN par_value;
1035
1036 END get_legi_param_val;
1037
1038 /*************************************************************************
1039 ** Type : Function
1040 ** Name : get_legi_param_val
1041 ** Purpose : This is an overloaded function that gets paramter Value
1042 ** from legislation_parameters column of pay_payroll_actions
1043 ** WHENEVER TWO PARAMETERS ARE SEPARATED BY EITHER A PIPE (|)
1044 ** OR A SPACE.
1045 **
1046 ** Arguments :
1047 ** Notes :
1048 **********************************************************************/
1049 FUNCTION get_legi_param_val(name IN VARCHAR2,
1050 parameter_list IN VARCHAR2,
1051 tag IN VARCHAR2)
1052 RETURN VARCHAR2 IS
1053
1054 start_ptr number;
1055 end_ptr number;
1056 token_val pay_payroll_actions.legislative_parameters%type;
1057 par_value pay_payroll_actions.legislative_parameters%type;
1058
1059 BEGIN
1060
1061 token_val := name||'=';
1062
1063 start_ptr := instr(parameter_list, token_val) + length(token_val);
1064 end_ptr := instr(parameter_list, tag ,start_ptr);
1065
1066 /* if there is no spaces use then length of the string */
1067
1068 IF end_ptr = 0 THEN
1069 end_ptr := length(parameter_list)+1;
1070 END IF;
1071
1072 /* Did we find the token */
1073
1074 IF INSTR(parameter_list, token_val) = 0 THEN
1075 par_value := NULL;
1076 ELSE
1077 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1078 END IF;
1079
1080 RETURN par_value;
1081
1082 END get_legi_param_val;
1083
1084
1085
1086 /**********************************************************************
1087 ** Type : Function
1088 ** Name : get_process_parameters
1089 ** Purpose : Returns Legislative parameters for specified payroll
1090 ** action
1091 **********************************************************************/
1092 FUNCTION get_process_parameters(p_cntx_payroll_action_id IN NUMBER,
1093 p_parameter_name IN VARCHAR2)
1094 RETURN VARCHAR2 IS
1095
1096
1097 l_legislation_parameters pay_payroll_actions.legislative_parameters%type;
1098 par_value pay_payroll_actions.legislative_parameters%type;
1099
1100 CURSOR c_get_parameter_value IS
1101 SELECT legislative_parameters
1102 FROM pay_payroll_actions
1103 WHERE payroll_action_id = p_cntx_payroll_action_id;
1104
1105 BEGIN
1106
1107 hr_utility.trace('Entering ..get_process_parameters');
1108 OPEN c_get_parameter_value;
1109 FETCH c_get_parameter_value INTO l_legislation_parameters;
1110 CLOSE c_get_parameter_value;
1111
1112 par_value := get_legi_param_val(p_parameter_name,l_legislation_parameters);
1113
1114 hr_utility.trace('Parameter Name : '||p_parameter_name||' Value : '||par_value);
1115 hr_utility.trace('Leaving ..get_process_parameters');
1116 RETURN par_value;
1117
1118 END get_process_parameters;
1119
1120
1121 /****************************************************************************
1122 Name : GET_MX_ECON_ZONE
1123 Description : This function returns Economy Zone('A', 'B', 'C') for the
1124 given tax_unit_id
1125 *****************************************************************************/
1126
1127
1128 FUNCTION GET_MX_ECON_ZONE
1129 (
1130 P_CTX_TAX_UNIT_ID number,
1131 P_CTX_DATE_EARNED DATE
1132 ) RETURN varchar2 AS
1133
1134 CURSOR get_econ_zone
1135 IS
1136 SELECT hoi.org_information7
1137 FROM hr_organization_units hou,
1138 hr_organization_information hoi
1139 WHERE hou.organization_id = hoi.organization_id
1140 AND hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
1141 AND hou.organization_id = P_CTX_TAX_UNIT_ID
1142 AND P_CTX_DATE_EARNED BETWEEN hou.date_from
1143 AND NVL(hou.date_to, hr_general.end_of_time);
1144
1145 l_econ_zone varchar2(2);
1146
1147 BEGIN
1148
1149
1150 OPEN get_econ_zone;
1151 FETCH get_econ_zone INTO l_econ_zone;
1152 CLOSE get_econ_zone;
1153
1154 RETURN (l_econ_zone);
1155 END GET_MX_ECON_ZONE;
1156
1157
1158 /****************************************************************************
1159 Name : GET_MIN_WAGE
1160 Description : This function returns Minimum Wage for the Economy Zone
1161 *****************************************************************************/
1162
1163 FUNCTION GET_MIN_WAGE
1164 (
1165 P_CTX_DATE_EARNED DATE,
1166 P_TAX_BASIS varchar2,
1167 P_ECON_ZONE varchar2
1168
1169 ) RETURN varchar2 AS
1170
1171 CURSOR get_min_wage
1172 IS
1173 SELECT fnd_number.canonical_to_number(legislation_info2) FROM PAY_MX_LEGISLATION_INFO_F WHERE
1174 legislation_info1=
1175 DECODE(P_ECON_ZONE,'NONE','GMW','MW'||P_ECON_ZONE) AND
1176 legislation_info_type = 'MX Minimum Wage Information'
1177 AND P_CTX_DATE_EARNED BETWEEN effective_start_date AND effective_end_date;
1178
1179 l_min_wage number;
1180
1181 BEGIN
1182
1183 hr_utility.trace('Economy Zone '||P_ECON_ZONE);
1184 OPEN get_min_wage;
1185 FETCH get_min_wage INTO l_min_wage;
1186 CLOSE get_min_wage;
1187
1188
1189 RETURN (l_min_wage);
1190
1191 END GET_MIN_WAGE;
1192
1193
1194
1195 BEGIN
1196
1197 g_package_name := 'pay_mx_utility.';
1198 g_debug := hr_utility.debug_enabled;
1199
1200 END pay_mx_utility;
[Home] [Help]