[Home] [Help]
1: PACKAGE BODY PAY_IE_P35 AS
2: /* $Header: pyiep35x.pkb 120.33.12010000.6 2008/08/22 11:31:07 rsahai ship $ */
3:
4: --
5: -- Constants
3:
4: --
5: -- Constants
6: --
7: l_package VARCHAR2 (31) := 'pay_ie_p35.';
8: --
9: -- Global Variables
10: --
11: -- Bug c_context_name - PRSI Context Balance Design Change
314: FETCH c1 into l_child_assignemnt_action;
315: CLOSE c1;
316:
317: p_insurable_weeks := to_char(p_tot_insurable_weeks - NVL (
318: pay_ie_p35.get_total_insurable_weeks (p_person_id
319: ,to_number(l_segment4)
320: ,p_assignment_action_id
321: ,p_Act_Context_id --6633719
322: ,p_Act_Context_value --6633719
330: p_this_emp_prsi_cont := to_char(p_prsi_employee_cont -
331: ROUND (
332: NVL (
333: pay_balance_pkg.get_value (
334: pay_ie_p35.get_defined_balance_id (
335: p_dimension_name, --'_PER_PAYE_REF_YTD', --6633719
336: 'IE PRSI Employee'
337: ),
338: p_assignment_action_id,
348: 0
349: )
350: + NVL (
351: pay_balance_pkg.get_value (
352: pay_ie_p35.get_defined_balance_id (
353: p_dimension_name, --'_PER_PAYE_REF_YTD', --6633719
354: 'IE PRSI K Employee Lump Sum'
355: ),
356: p_assignment_action_id,
366: 0
367: )
368: + NVL (
369: pay_balance_pkg.get_value (
370: pay_ie_p35.get_defined_balance_id (
371: '_PER_YTD',
372: 'IE PRSI M Employee Lump Sum'
373: ),
374: p_assignment_action_id,
391: p_this_tot_prsi := to_char(p_prsi_tot_cont -
392: ROUND (
393: NVL (
394: pay_balance_pkg.get_value (
395: pay_ie_p35.get_defined_balance_id (
396: p_dimension_name, --'_PER_PAYE_REF_YTD', --6633719
397: 'IE PRSI Employee'
398: ),
399: p_assignment_action_id,
409: 0
410: )
411: + NVL (
412: pay_balance_pkg.get_value (
413: pay_ie_p35.get_defined_balance_id (
414: p_dimension_name, --'_PER_PAYE_REF_YTD', --6633719
415: 'IE PRSI Employer'
416: ),
417: p_assignment_action_id,
427: 0
428: )
429: + NVL (
430: pay_balance_pkg.get_value (
431: pay_ie_p35.get_defined_balance_id (
432: p_dimension_name, --'_PER_PAYE_REF_YTD', --6633719
433: 'IE PRSI K Employee Lump Sum'
434: ),
435: p_assignment_action_id,
445: 0
446: )
447: + NVL (
448: pay_balance_pkg.get_value (
449: pay_ie_p35.get_defined_balance_id (
450: p_dimension_name, --'_PER_PAYE_REF_YTD', --6633719
451: 'IE PRSI M Employee Lump Sum'
452: ),
453: p_assignment_action_id,
463: 0
464: )
465: + NVL (
466: pay_balance_pkg.get_value (
467: pay_ie_p35.get_defined_balance_id (
468: p_dimension_name, --'_PER_PAYE_REF_YTD', --6633719
469: 'IE PRSI K Employer Lump Sum'
470: ),
471: p_assignment_action_id,
481: 0
482: )
483: + NVL (
484: pay_balance_pkg.get_value (
485: pay_ie_p35.get_defined_balance_id (
486: p_dimension_name, --'_PER_PAYE_REF_YTD', --6633719
487: 'IE PRSI M Employer Lump Sum'
488: ),
489: p_assignment_action_id,
678: FROM pay_payroll_actions ppa
679: WHERE ppa.report_type = 'IEP30_PRGLOCK'
680: AND ppa.action_status = 'C'
681: AND TO_DATE (
682: pay_ie_p35.get_parameter (
683: ppa.payroll_action_id,
684: 'END_DATE'
685: ),
686: 'YYYY/MM/DD'
690: BEGIN
691: --hr_utility.trace_on(null,'vikp35');
692:
693: hr_utility.set_location (l_proc_name, 1);
694: l_out_var := pay_ie_p35.get_parameter (
695: p_payroll_action_id=> p_payroll_action_id,
696: p_token_name=> 'END_DATE'
697: );
698: l_end_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
695: p_payroll_action_id=> p_payroll_action_id,
696: p_token_name=> 'END_DATE'
697: );
698: l_end_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
699: l_out_var := pay_ie_p35.get_parameter (
700: p_payroll_action_id=> p_payroll_action_id,
701: p_token_name=> 'START_DATE'
702: );
703: l_start_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
700: p_payroll_action_id=> p_payroll_action_id,
701: p_token_name=> 'START_DATE'
702: );
703: l_start_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
704: l_out_var := pay_ie_p35.get_parameter (
705: p_payroll_action_id=> p_payroll_action_id,
706: p_token_name=> 'BG_ID'
707: );
708: l_bg_id := TO_NUMBER (l_out_var);
914: pay_action_interlocks plock
915: WHERE ppa_p35.report_type = 'IEP35'
916: AND paa_p35.action_status = 'C'
917: AND TO_CHAR ( TO_DATE (
918: pay_ie_p35.get_parameter (
919: ppa_p35.payroll_action_id,
920: 'END_DATE'
921: ),'YYYY/MM/DD'),'YYYY') = TO_CHAR(l_end_date,'YYYY') --4641756
922: AND ppa_p35.payroll_action_id = paa_p35.payroll_action_id
996: l_flag_action_id pay_assignment_actions.assignment_action_id%TYPE;
997: BEGIN
998:
999: --
1000: l_segment4 := pay_ie_p35.get_parameter( p_payroll_action_id=> pactid,
1001: p_token_name=> 'EMP_NO');
1002: l_out_var := pay_ie_p35.get_parameter (
1003: p_payroll_action_id=> pactid,
1004: p_token_name=> 'ASSIGNMENT_SET_ID'
998:
999: --
1000: l_segment4 := pay_ie_p35.get_parameter( p_payroll_action_id=> pactid,
1001: p_token_name=> 'EMP_NO');
1002: l_out_var := pay_ie_p35.get_parameter (
1003: p_payroll_action_id=> pactid,
1004: p_token_name=> 'ASSIGNMENT_SET_ID'
1005: );
1006: l_assignment_set_id := to_number(l_out_var);
1004: p_token_name=> 'ASSIGNMENT_SET_ID'
1005: );
1006: l_assignment_set_id := to_number(l_out_var);
1007:
1008: l_out_var := pay_ie_p35.get_parameter (
1009: p_payroll_action_id=> pactid,
1010: p_token_name=> 'PAYROLL'
1011: );
1012: l_payroll_id := to_number(l_out_var);
1010: p_token_name=> 'PAYROLL'
1011: );
1012: l_payroll_id := to_number(l_out_var);
1013:
1014: l_out_var := pay_ie_p35.get_parameter (
1015: p_payroll_action_id=> pactid,
1016: p_token_name=> 'END_DATE'
1017: );
1018: l_end_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
1015: p_payroll_action_id=> pactid,
1016: p_token_name=> 'END_DATE'
1017: );
1018: l_end_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
1019: l_out_var := pay_ie_p35.get_parameter (
1020: p_payroll_action_id=> pactid,
1021: p_token_name=> 'START_DATE'
1022: );
1023: l_start_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
1020: p_payroll_action_id=> pactid,
1021: p_token_name=> 'START_DATE'
1022: );
1023: l_start_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
1024: l_out_var := pay_ie_p35.get_parameter (
1025: p_payroll_action_id=> pactid,
1026: p_token_name=> 'BG_ID'
1027: );
1028: l_bg_id := TO_NUMBER (l_out_var);
1386: TO_DATE ('31-12-4712', 'DD-MM-YYYY')
1387: ),
1388: 'YYYY'
1389: ),
1390: TO_CHAR (pay_ie_p35.get_end_date, 'RRRR') --Bug fix 3745861
1391: , TO_CHAR (pps.actual_termination_date, 'dd-mm-yyyy'),
1392: '31-12-4712'
1393: ), -- TERM_DATE
1394: NVL (TRIM (RPAD (ppf.per_information1, 30)), ' '), -- MOTHERS_NAME
1396: NVL (
1397: ROUND (
1398: TO_NUMBER (
1399: pay_balance_pkg.get_value (
1400: pay_ie_p35.get_defined_balance_id (
1401: '_ASG_YTD',
1402: 'IE P45 Pay'
1403: ),
1404: p_rr_action
1411: NVL (
1412: ROUND (
1413: TO_NUMBER (
1414: pay_balance_pkg.get_value (
1415: pay_ie_p35.get_defined_balance_id (
1416: '_ASG_YTD',
1417: 'IE P45 Tax Deducted'
1418: ),
1419: p_rr_action
1449: (SELECT MAX (asg.effective_end_date)
1450: FROM per_assignments_f asg
1451: WHERE asg.assignment_id = paf.assignment_id
1452: AND asg.effective_start_date <=
1453: l_end_date --pay_ie_p35.get_start_date()
1454: AND asg.effective_end_date >=
1455: l_start_date -- pay_ie_p35.get_end_date()
1456: )
1457: AND paf.period_of_service_id = pps.period_of_service_id
1451: WHERE asg.assignment_id = paf.assignment_id
1452: AND asg.effective_start_date <=
1453: l_end_date --pay_ie_p35.get_start_date()
1454: AND asg.effective_end_date >=
1455: l_start_date -- pay_ie_p35.get_end_date()
1456: )
1457: AND paf.period_of_service_id = pps.period_of_service_id
1458: AND paf.person_id = pps.person_id
1459: AND ppf.person_id = paf.person_id
1461: (SELECT MAX (per.effective_end_date)
1462: FROM per_people_f per
1463: WHERE per.person_id = ppf.person_id
1464: AND per.effective_start_date <=
1465: l_end_date --pay_ie_p35.get_start_date()
1466: AND per.effective_end_date >=
1467: l_start_date --pay_ie_p35.get_end_date()
1468: );
1469:
1463: WHERE per.person_id = ppf.person_id
1464: AND per.effective_start_date <=
1465: l_end_date --pay_ie_p35.get_start_date()
1466: AND per.effective_end_date >=
1467: l_start_date --pay_ie_p35.get_end_date()
1468: );
1469:
1470: -- Prepayment cursor
1471: -- p_locked_action is pre-payments locked action.
1485: DECODE (
1486: SIGN (
1487: NVL (
1488: pay_balance_pkg.get_value (
1489: pay_ie_p35.get_defined_balance_id (
1490: p_dimension_name, --'_PER_PAYE_REF_YTD', -- changes made --6633719
1491: 'IE Net Tax'
1492: ),
1493: p_rr_action, -- paa.assignment_action_id,
1580: NVL (TRIM (RPAD (ppf.per_information1, 30)), ' '), --MOTHERS_NAME
1581: DECODE (
1582: SIGN (
1583: pay_balance_pkg.get_value (
1584: pay_ie_p35.get_defined_balance_id (
1585: p_dimension_name, --'_PER_PAYE_REF_YTD', -- changes made--6633719
1586: 'IE Net Tax'
1587: ),
1588: p_rr_action, --paa.assignment_action_id,
1602: NVL (
1603: ROUND (
1604: TO_NUMBER (
1605: pay_balance_pkg.get_value (
1606: pay_ie_p35.get_defined_balance_id (
1607: '_ASG_YTD',
1608: 'IE P45 Pay'
1609: ),
1610: p_rr_action
1617: NVL (
1618: ROUND (
1619: TO_NUMBER (
1620: pay_balance_pkg.get_value (
1621: pay_ie_p35.get_defined_balance_id (
1622: '_ASG_YTD',
1623: 'IE P45 Tax Deducted'
1624: ),
1625: p_rr_action
1688: DECODE (
1689: SIGN (
1690: NVL (
1691: pay_balance_pkg.get_value (
1692: pay_ie_p35.get_defined_balance_id (
1693: p_dimension_name, --'_PER_PAYE_REF_YTD', -- changes made--6633719
1694: 'IE Net Tax'
1695: ),
1696: paa.assignment_action_id,
1784: NVL (TRIM (RPAD (ppf.per_information1, 30)), ' '), -- MOTHERS_NAME
1785: DECODE (
1786: SIGN (
1787: pay_balance_pkg.get_value (
1788: pay_ie_p35.get_defined_balance_id (
1789: p_dimension_name, ---'_PER_PAYE_REF_YTD', -- changes made--6633719
1790: 'IE Net Tax'
1791: ),
1792: paa.assignment_action_id,
1806: NVL (
1807: ROUND (
1808: TO_NUMBER (
1809: pay_balance_pkg.get_value (
1810: pay_ie_p35.get_defined_balance_id (
1811: '_ASG_YTD',
1812: 'IE P45 Pay'
1813: ),
1814: p_locked_action
1821: NVL (
1822: ROUND (
1823: TO_NUMBER (
1824: pay_balance_pkg.get_value (
1825: pay_ie_p35.get_defined_balance_id (
1826: '_ASG_YTD',
1827: 'IE P45 Tax Deducted'
1828: ),
1829: p_locked_action
2212: */
2213: --6633719
2214:
2215: l_out_var :=
2216: pay_ie_p35.get_parameter (
2217: p_payroll_action_id=> v_payroll_action_id,
2218: p_token_name=> 'END_DATE'
2219: );
2220: l_end_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
2218: p_token_name=> 'END_DATE'
2219: );
2220: l_end_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
2221: l_out_var :=
2222: pay_ie_p35.get_parameter (
2223: p_payroll_action_id=> v_payroll_action_id,
2224: p_token_name=> 'START_DATE'
2225: );
2226: l_start_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
2224: p_token_name=> 'START_DATE'
2225: );
2226: l_start_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
2227: l_out_var :=
2228: pay_ie_p35.get_parameter (
2229: p_payroll_action_id=> v_payroll_action_id,
2230: p_token_name=> 'BG_ID'
2231: );
2232: l_bg_id := TO_NUMBER (l_out_var);
2230: p_token_name=> 'BG_ID'
2231: );
2232: l_bg_id := TO_NUMBER (l_out_var);
2233:
2234: l_segment4 := pay_ie_p35.get_parameter( p_payroll_action_id=> v_payroll_action_id,
2235: p_token_name=> 'EMP_NO');
2236: l_out_var := pay_ie_p35.get_parameter (
2237: p_payroll_action_id=> v_payroll_action_id,
2238: p_token_name=> 'ASSIGNMENT_SET_ID'
2232: l_bg_id := TO_NUMBER (l_out_var);
2233:
2234: l_segment4 := pay_ie_p35.get_parameter( p_payroll_action_id=> v_payroll_action_id,
2235: p_token_name=> 'EMP_NO');
2236: l_out_var := pay_ie_p35.get_parameter (
2237: p_payroll_action_id=> v_payroll_action_id,
2238: p_token_name=> 'ASSIGNMENT_SET_ID'
2239: );
2240: l_assignment_set_id := to_number(l_out_var);
2238: p_token_name=> 'ASSIGNMENT_SET_ID'
2239: );
2240: l_assignment_set_id := to_number(l_out_var);
2241:
2242: l_out_var := pay_ie_p35.get_parameter (
2243: p_payroll_action_id=> v_payroll_action_id,
2244: p_token_name=> 'PAYROLL'
2245: );
2246: l_payroll_id := to_number(l_out_var);
2356: TO_CHAR (
2357: ROUND (
2358: NVL (
2359: pay_balance_pkg.get_value (
2360: pay_ie_p35.get_defined_balance_id (
2361: l_dimension_name,
2362: 'IE Taxable Pay'
2363: ),
2364: csr_interlocks.locked_action_id,
2393: TO_CHAR (
2394: ROUND (
2395: NVL (
2396: pay_balance_pkg.get_value (
2397: pay_ie_p35.get_defined_balance_id (
2398: l_dimension_name,
2399: 'IE Net Tax'
2400: ),
2401: csr_interlocks.locked_action_id,
2430: TO_CHAR (
2431: ROUND (
2432: NVL (
2433: pay_balance_pkg.get_value (
2434: pay_ie_p35.get_defined_balance_id (
2435: l_dimension_name,
2436: 'IE PRSI Employee'
2437: ),
2438: csr_interlocks.locked_action_id,
2473: TO_CHAR (
2474: ROUND (
2475: NVL (
2476: pay_balance_pkg.get_value (
2477: pay_ie_p35.get_defined_balance_id (
2478: l_dimension_name,
2479: 'IE PRSI K Employee Lump Sum'
2480: ),
2481: csr_interlocks.locked_action_id,
2518: TO_CHAR (
2519: ROUND (
2520: NVL (
2521: pay_balance_pkg.get_value (
2522: pay_ie_p35.get_defined_balance_id (
2523: l_dimension_name,
2524: 'IE PRSI M Employee Lump Sum'
2525: ),
2526: csr_interlocks.locked_action_id,
2563: TO_CHAR (
2564: ROUND (
2565: NVL (
2566: pay_balance_pkg.get_value (
2567: pay_ie_p35.get_defined_balance_id (
2568: l_dimension_name,
2569: 'IE PRSI Employer'
2570: ),
2571: csr_interlocks.locked_action_id,
2601: TO_CHAR (
2602: ROUND (
2603: NVL (
2604: pay_balance_pkg.get_value (
2605: pay_ie_p35.get_defined_balance_id (
2606: l_dimension_name,
2607: 'IE PRSI K Employer Lump Sum'
2608: ),
2609: csr_interlocks.locked_action_id,
2646: TO_CHAR (
2647: ROUND (
2648: NVL (
2649: pay_balance_pkg.get_value (
2650: pay_ie_p35.get_defined_balance_id (
2651: l_dimension_name,
2652: 'IE PRSI M Employer Lump Sum'
2653: ),
2654: csr_interlocks.locked_action_id,
2693: TO_CHAR (
2694: ROUND (
2695: NVL (
2696: pay_balance_pkg.get_value (
2697: pay_ie_p35.get_defined_balance_id (
2698: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
2699: 'IE BIK Taxable and PRSIable Pay'
2700: ),
2701: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
2720: TO_CHAR (
2721: ROUND (
2722: NVL (
2723: pay_balance_pkg.get_value (
2724: pay_ie_p35.get_defined_balance_id (
2725: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
2726: 'IE RBS EE Contribution'
2727: ),
2728: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
2742: +
2743: ROUND (
2744: NVL (
2745: pay_balance_pkg.get_value (
2746: pay_ie_p35.get_defined_balance_id (
2747: l_dimension_name, --'_PER_PAYE_REF_YTD', --6633719
2748: 'IE RBS EE AVC Contribution'
2749: ),
2750: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
2767: TO_CHAR (
2768: ROUND (
2769: NVL (
2770: pay_balance_pkg.get_value (
2771: pay_ie_p35.get_defined_balance_id (
2772: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
2773: 'IE RBS ER Contribution'
2774: ),
2775: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
2792: TO_CHAR (
2793: ROUND (
2794: NVL (
2795: pay_balance_pkg.get_value (
2796: pay_ie_p35.get_defined_balance_id (
2797: l_dimension_name, ---'_PER_PAYE_REF_YTD',--6633719
2798: 'IE PRSA EE Contribution'
2799: ),
2800: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
2814: +
2815: ROUND (
2816: NVL (
2817: pay_balance_pkg.get_value (
2818: pay_ie_p35.get_defined_balance_id (
2819: l_dimension_name, ---'_PER_PAYE_REF_YTD',--6633719
2820: 'IE PRSA EE AVC Contribution'
2821: ),
2822: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
2839: TO_CHAR (
2840: ROUND (
2841: NVL (
2842: pay_balance_pkg.get_value (
2843: pay_ie_p35.get_defined_balance_id (
2844: l_dimension_name, ---'_PER_PAYE_REF_YTD',--6633719
2845: 'IE PRSA ER Contribution'
2846: ),
2847: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
2864: TO_CHAR (
2865: ROUND (
2866: NVL (
2867: pay_balance_pkg.get_value (
2868: pay_ie_p35.get_defined_balance_id (
2869: l_dimension_name, ---'_PER_PAYE_REF_YTD',--6633719
2870: 'IE RAC EE Contribution'
2871: ),
2872: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
2891: TO_CHAR (
2892: ROUND (
2893: NVL (
2894: pay_balance_pkg.get_value (
2895: pay_ie_p35.get_defined_balance_id (
2896: l_dimension_name, ---'_PER_PAYE_REF_YTD',--6633719
2897: 'IE BIK Medical Insurance'
2898: ),
2899: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
2919: TO_CHAR (
2920: ROUND (
2921: NVL (
2922: pay_balance_pkg.get_value (
2923: pay_ie_p35.get_defined_balance_id (
2924: l_dimension_name, ---'_PER_PAYE_REF_YTD',--6633719
2925: 'Total Pay'
2926: ),
2927: csr_interlocks.locked_action_id,
3039: TO_CHAR (
3040: ROUND (
3041: NVL (
3042: pay_balance_pkg.get_value (
3043: pay_ie_p35.get_defined_balance_id (
3044: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3045: 'IE Net Tax'
3046: ),
3047: csr_interlocks.locked_action_id,
3064: TO_CHAR (
3065: ROUND (
3066: NVL (
3067: pay_balance_pkg.get_value (
3068: pay_ie_p35.get_defined_balance_id (
3069: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3070: 'IE PRSI Employee'
3071: ),
3072: csr_interlocks.locked_action_id,
3082: 0
3083: )
3084: + NVL (
3085: pay_balance_pkg.get_value (
3086: pay_ie_p35.get_defined_balance_id (
3087: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3088: 'IE PRSI K Employee Lump Sum'
3089: ),
3090: csr_interlocks.locked_action_id,
3100: 0
3101: )
3102: + NVL (
3103: pay_balance_pkg.get_value (
3104: pay_ie_p35.get_defined_balance_id (
3105: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3106: 'IE PRSI M Employee Lump Sum'
3107: ),
3108: csr_interlocks.locked_action_id,
3125: TO_CHAR (
3126: ROUND (
3127: NVL (
3128: pay_balance_pkg.get_value (
3129: pay_ie_p35.get_defined_balance_id (
3130: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3131: 'IE PRSI Employee'
3132: ),
3133: csr_interlocks.locked_action_id,
3143: 0
3144: )
3145: + NVL (
3146: pay_balance_pkg.get_value (
3147: pay_ie_p35.get_defined_balance_id (
3148: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3149: 'IE PRSI Employer'
3150: ),
3151: csr_interlocks.locked_action_id,
3161: 0
3162: )
3163: + NVL (
3164: pay_balance_pkg.get_value (
3165: pay_ie_p35.get_defined_balance_id (
3166: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3167: 'IE PRSI K Employee Lump Sum'
3168: ),
3169: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3179: 0
3180: )
3181: + NVL (
3182: pay_balance_pkg.get_value (
3183: pay_ie_p35.get_defined_balance_id (
3184: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3185: 'IE PRSI M Employee Lump Sum'
3186: ),
3187: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3197: 0
3198: )
3199: + NVL (
3200: pay_balance_pkg.get_value (
3201: pay_ie_p35.get_defined_balance_id (
3202: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3203: 'IE PRSI K Employer Lump Sum'
3204: ),
3205: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3215: 0
3216: )
3217: + NVL (
3218: pay_balance_pkg.get_value (
3219: pay_ie_p35.get_defined_balance_id (
3220: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3221: 'IE PRSI M Employer Lump Sum'
3222: ),
3223: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3240: TO_CHAR (
3241: ROUND (
3242: NVL (
3243: pay_balance_pkg.get_value (
3244: pay_ie_p35.get_defined_balance_id (
3245: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3246: 'IE Taxable Pay'
3247: ),
3248: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3265: TO_CHAR (
3266: ROUND (
3267: NVL (
3268: pay_balance_pkg.get_value (
3269: pay_ie_p35.get_defined_balance_id (
3270: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3271: 'IE BIK Taxable and PRSIable Pay'
3272: ),
3273: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3291: TO_CHAR (
3292: ROUND (
3293: NVL (
3294: pay_balance_pkg.get_value (
3295: pay_ie_p35.get_defined_balance_id (
3296: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3297: 'IE RBS EE Contribution'
3298: ),
3299: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3313: +
3314: ROUND (
3315: NVL (
3316: pay_balance_pkg.get_value (
3317: pay_ie_p35.get_defined_balance_id (
3318: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3319: 'IE RBS EE AVC Contribution'
3320: ),
3321: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3338: TO_CHAR (
3339: ROUND (
3340: NVL (
3341: pay_balance_pkg.get_value (
3342: pay_ie_p35.get_defined_balance_id (
3343: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3344: 'IE RBS ER Contribution'
3345: ),
3346: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3363: TO_CHAR (
3364: ROUND (
3365: NVL (
3366: pay_balance_pkg.get_value (
3367: pay_ie_p35.get_defined_balance_id (
3368: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3369: 'IE PRSA EE Contribution'
3370: ),
3371: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3385: +
3386: ROUND (
3387: NVL (
3388: pay_balance_pkg.get_value (
3389: pay_ie_p35.get_defined_balance_id (
3390: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3391: 'IE PRSA EE AVC Contribution'
3392: ),
3393: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3410: TO_CHAR (
3411: ROUND (
3412: NVL (
3413: pay_balance_pkg.get_value (
3414: pay_ie_p35.get_defined_balance_id (
3415: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3416: 'IE PRSA ER Contribution'
3417: ),
3418: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3435: TO_CHAR (
3436: ROUND (
3437: NVL (
3438: pay_balance_pkg.get_value (
3439: pay_ie_p35.get_defined_balance_id (
3440: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3441: 'IE RAC EE Contribution'
3442: ),
3443: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3462: TO_CHAR (
3463: ROUND (
3464: NVL (
3465: pay_balance_pkg.get_value (
3466: pay_ie_p35.get_defined_balance_id (
3467: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3468: 'IE BIK Medical Insurance'
3469: ),
3470: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3490: TO_CHAR (
3491: ROUND (
3492: NVL (
3493: pay_balance_pkg.get_value (
3494: pay_ie_p35.get_defined_balance_id (
3495: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3496: 'Total Pay'
3497: ),
3498: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3562: TO_CHAR (
3563: ROUND (
3564: NVL (
3565: pay_balance_pkg.get_value (
3566: pay_ie_p35.get_defined_balance_id (
3567: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3568: 'IE Net Tax'
3569: ),
3570: l_arch_assmt_action_id_bal,
3587: TO_CHAR (
3588: ROUND (
3589: NVL (
3590: pay_balance_pkg.get_value (
3591: pay_ie_p35.get_defined_balance_id (
3592: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3593: 'IE PRSI Employee'
3594: ),
3595: l_arch_assmt_action_id_bal,
3605: 0
3606: )
3607: + NVL (
3608: pay_balance_pkg.get_value (
3609: pay_ie_p35.get_defined_balance_id (
3610: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3611: 'IE PRSI K Employee Lump Sum'
3612: ),
3613: l_arch_assmt_action_id_bal,
3623: 0
3624: )
3625: + NVL (
3626: pay_balance_pkg.get_value (
3627: pay_ie_p35.get_defined_balance_id (
3628: '_PER_YTD',
3629: 'IE PRSI M Employee Lump Sum'
3630: ),
3631: l_arch_assmt_action_id_bal,
3649: TO_CHAR (
3650: ROUND (
3651: NVL (
3652: pay_balance_pkg.get_value (
3653: pay_ie_p35.get_defined_balance_id (
3654: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3655: 'IE PRSI Employee'
3656: ),
3657: l_arch_assmt_action_id_bal,
3667: 0
3668: )
3669: + NVL (
3670: pay_balance_pkg.get_value (
3671: pay_ie_p35.get_defined_balance_id (
3672: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3673: 'IE PRSI Employer'
3674: ),
3675: l_arch_assmt_action_id_bal,
3685: 0
3686: )
3687: + NVL (
3688: pay_balance_pkg.get_value (
3689: pay_ie_p35.get_defined_balance_id (
3690: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3691: 'IE PRSI K Employee Lump Sum'
3692: ),
3693: l_arch_assmt_action_id_bal,
3703: 0
3704: )
3705: + NVL (
3706: pay_balance_pkg.get_value (
3707: pay_ie_p35.get_defined_balance_id (
3708: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3709: 'IE PRSI M Employee Lump Sum'
3710: ),
3711: l_arch_assmt_action_id_bal,
3721: 0
3722: )
3723: + NVL (
3724: pay_balance_pkg.get_value (
3725: pay_ie_p35.get_defined_balance_id (
3726: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3727: 'IE PRSI K Employer Lump Sum'
3728: ),
3729: l_arch_assmt_action_id_bal,
3739: 0
3740: )
3741: + NVL (
3742: pay_balance_pkg.get_value (
3743: pay_ie_p35.get_defined_balance_id (
3744: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3745: 'IE PRSI M Employer Lump Sum'
3746: ),
3747: l_arch_assmt_action_id_bal,
3764: TO_CHAR (
3765: ROUND (
3766: NVL (
3767: pay_balance_pkg.get_value (
3768: pay_ie_p35.get_defined_balance_id (
3769: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3770: 'IE Taxable Pay'
3771: ),
3772: l_arch_assmt_action_id_bal,
3793: TO_CHAR (
3794: ROUND (
3795: NVL (
3796: pay_balance_pkg.get_value (
3797: pay_ie_p35.get_defined_balance_id (
3798: l_dimension_name, ---'_PER_PAYE_REF_YTD',--6633719
3799: 'IE BIK Taxable and PRSIable Pay'
3800: ),
3801: l_arch_assmt_action_id_bal,
3819: TO_CHAR (
3820: ROUND (
3821: NVL (
3822: pay_balance_pkg.get_value (
3823: pay_ie_p35.get_defined_balance_id (
3824: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3825: 'IE RBS EE Contribution'
3826: ),
3827: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3841: +
3842: ROUND (
3843: NVL (
3844: pay_balance_pkg.get_value (
3845: pay_ie_p35.get_defined_balance_id (
3846: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3847: 'IE RBS EE AVC Contribution'
3848: ),
3849: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3866: TO_CHAR (
3867: ROUND (
3868: NVL (
3869: pay_balance_pkg.get_value (
3870: pay_ie_p35.get_defined_balance_id (
3871: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3872: 'IE RBS ER Contribution'
3873: ),
3874: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3891: TO_CHAR (
3892: ROUND (
3893: NVL (
3894: pay_balance_pkg.get_value (
3895: pay_ie_p35.get_defined_balance_id (
3896: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3897: 'IE PRSA EE Contribution'
3898: ),
3899: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3913: +
3914: ROUND (
3915: NVL (
3916: pay_balance_pkg.get_value (
3917: pay_ie_p35.get_defined_balance_id (
3918: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3919: 'IE PRSA EE AVC Contribution'
3920: ),
3921: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3938: TO_CHAR (
3939: ROUND (
3940: NVL (
3941: pay_balance_pkg.get_value (
3942: pay_ie_p35.get_defined_balance_id (
3943: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3944: 'IE PRSA ER Contribution'
3945: ),
3946: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3963: TO_CHAR (
3964: ROUND (
3965: NVL (
3966: pay_balance_pkg.get_value (
3967: pay_ie_p35.get_defined_balance_id (
3968: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3969: 'IE RAC EE Contribution'
3970: ),
3971: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
3990: TO_CHAR (
3991: ROUND (
3992: NVL (
3993: pay_balance_pkg.get_value (
3994: pay_ie_p35.get_defined_balance_id (
3995: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
3996: 'IE BIK Medical Insurance'
3997: ),
3998: csr_interlocks.locked_action_id, --l_arch_assmt_action_id_bal,
4018: TO_CHAR (
4019: ROUND (
4020: NVL (
4021: pay_balance_pkg.get_value (
4022: pay_ie_p35.get_defined_balance_id (
4023: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
4024: 'Total Pay'
4025: ),
4026: l_arch_assmt_action_id_bal,
4073: l_arch_previous_emp_pay := NVL (
4074: ROUND (
4075: TO_NUMBER (
4076: pay_balance_pkg.get_value (
4077: pay_ie_p35.get_defined_balance_id (
4078: '_ASG_YTD',
4079: 'IE P45 Pay'
4080: ),
4081: csr_interlocks.locked_action_id
4089: l_arch_previous_emp_tax := NVL (
4090: ROUND (
4091: TO_NUMBER (
4092: pay_balance_pkg.get_value (
4093: pay_ie_p35.get_defined_balance_id (
4094: '_ASG_YTD',
4095: 'IE P45 Tax Deducted'
4096: ),
4097: csr_interlocks.locked_action_id
4165: hr_utility.set_location ('Starting on other common values', 10);
4166: l_arch_totwks_insurble_emplmnt :=
4167: TO_CHAR (
4168: NVL (
4169: pay_ie_p35.get_total_insurable_weeks (l_arch_person_id
4170: ,to_number(l_segment4)
4171: ,l_max_action_id
4172: ,l_Act_Context_id --6633719
4173: ,l_Act_Context_value --6633719
4179: hr_utility.set_location('l_arch_totwks_insurble_emplmnt.'||l_arch_totwks_insurble_emplmnt,5001);
4180: --
4181:
4182: l_arch_initial_class :=
4183: NVL (pay_ie_p35.get_initial_class (l_max_action_id,to_number(l_segment4),l_ppsn_override), ' '); --6633719
4184: -- class A is displayed when PRSIable pay is zero.
4185: -- Eliminate class A.
4186: IF l_arch_pay = 0 or l_arch_initial_class = 'A' THEN
4187: l_arch_initial_class := NULL;
4338: hr_utility.set_location('l_aact_id..'||l_aact_id,5004);
4339: IF l_aact_id IS NOT NULL THEN
4340: -- for bug 5435931.
4341: l_this_pay := l_arch_pay - pay_balance_pkg.get_value (
4342: pay_ie_p35.get_defined_balance_id (
4343: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
4344: 'IE Taxable Pay'
4345: ),
4346: l_aact_id,
4355:
4356: );
4357: hr_utility.set_location('l_this_pay..'||l_this_pay,5004);
4358: l_this_tax := l_arch_net_tax - pay_balance_pkg.get_value (
4359: pay_ie_p35.get_defined_balance_id (
4360: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
4361: 'IE Net Tax'
4362: ),
4363: l_aact_id,
4574: hr_organization_information hoi1,
4575: hr_organization_information hoi2
4576: WHERE ppa.payroll_action_id = p_payroll_action_id
4577: AND hou.business_group_id = ppa.business_group_id
4578: AND hou.organization_id = pay_ie_p35.get_parameter (ppa.payroll_action_id, 'EMP_NO')
4579: AND hl.location_id(+) = hou.location_id
4580: AND hou.organization_id=hoi1.organization_id
4581: AND hoi2.organization_id(+)= hoi1.organization_id
4582: AND hoi1.org_information_context='CLASS'
4589: OPEN csr_check_payroll_action;
4590: FETCH csr_check_payroll_action into l_archived;
4591: CLOSE csr_check_payroll_action;
4592: IF l_archived = 0 THEN
4593: l_out_var := pay_ie_p35.get_parameter (
4594: p_payroll_action_id=> p_payroll_action_id,
4595: p_token_name=> 'END_DATE'
4596: );
4597: l_end_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
4594: p_payroll_action_id=> p_payroll_action_id,
4595: p_token_name=> 'END_DATE'
4596: );
4597: l_end_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
4598: l_out_var := pay_ie_p35.get_parameter (
4599: p_payroll_action_id=> p_payroll_action_id,
4600: p_token_name=> 'START_DATE'
4601: );
4602: l_start_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
4599: p_payroll_action_id=> p_payroll_action_id,
4600: p_token_name=> 'START_DATE'
4601: );
4602: l_start_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
4603: l_out_var := pay_ie_p35.get_parameter (
4604: p_payroll_action_id=> p_payroll_action_id,
4605: p_token_name=> 'BG_ID'
4606: );
4607: l_bg_id := TO_NUMBER (l_out_var);
5178: CURSOR c_p60_second_class
5179: IS
5180: SELECT SUBSTR (piw.combined_class, 1, 2),
5181: NVL (piw.insurable_weeks, 0)
5182: FROM pay_ie_p35_insurable_weeks_v piw
5183: WHERE piw.person_id =
5184: (SELECT asg.person_id
5185: FROM per_assignments_f asg
5186: WHERE asg.assignment_id = p_assignment_id
5190: WHERE asg.assignment_id =
5191: asg2.assignment_id))
5192: AND piw.insurable_weeks =
5193: (SELECT MAX (piw2.insurable_weeks)
5194: FROM pay_ie_p35_insurable_weeks_v piw2
5195: WHERE piw2.person_id = piw.person_id
5196: AND SUBSTR (piw2.combined_class, 1, 2) <>
5197: RPAD (l_initial_class, 2))
5198: AND piw.minimum_effective_date =
5196: AND SUBSTR (piw2.combined_class, 1, 2) <>
5197: RPAD (l_initial_class, 2))
5198: AND piw.minimum_effective_date =
5199: (SELECT MIN (piw3.minimum_effective_date)
5200: FROM pay_ie_p35_insurable_weeks_v piw3
5201: WHERE piw3.person_id = piw.person_id
5202: AND piw3.insurable_weeks = piw.insurable_weeks
5203: AND SUBSTR (piw3.combined_class, 1, 2) <>
5204: RPAD (l_initial_class, 2));
5564: l_total_weeks := 0;
5565: --Bug fix 4004470
5566: l_total_weeks :=l_total_weeks +
5567: nvl(pay_balance_pkg.get_value (
5568: pay_ie_p35.get_defined_balance_id (
5569: p_dimension_name, --'_PER_PAYE_REF_YTD', -- changes made --6633719
5570: 'IE PRSI Insurable Weeks'
5571: ),
5572: p_assignment_action_id, --paa.assignment_action_id,
5580: 'TRUE'
5581: ),0)
5582: +
5583: nvl(pay_balance_pkg.get_value (
5584: pay_ie_p35.get_defined_balance_id (
5585: p_dimension_name, --'_PER_PAYE_REF_YTD', -- changes made --6633719
5586: 'IE PRSI K Term Insurable Weeks'
5587: ),
5588: p_assignment_action_id, --paa.assignment_action_id,
5595: null,
5596: 'TRUE'
5597: ),0)
5598: + nvl(pay_balance_pkg.get_value (
5599: pay_ie_p35.get_defined_balance_id (
5600: p_dimension_name, --'_PER_PAYE_REF_YTD', -- changes made --6633719
5601: 'IE PRSI M Term Insurable Weeks'
5602: ),
5603: p_assignment_action_id, --paa.assignment_action_id,
5641: INTO l_start_date
5642: FROM fnd_profile_option_values fpov, fnd_profile_options fpo
5643: WHERE fpo.profile_option_id = fpov.profile_option_id
5644: AND fpo.application_id = fpov.application_id
5645: AND fpo.profile_option_name = 'PAY_IE_P35_REPORTING_YEAR'
5646: AND fpov.level_id = 10001
5647: AND fpov.level_value = 0;
5648:
5649: RETURN l_start_date;
5663: INTO l_end_date
5664: FROM fnd_profile_option_values fpov, fnd_profile_options fpo
5665: WHERE fpo.profile_option_id = fpov.profile_option_id
5666: AND fpo.application_id = fpov.application_id
5667: AND fpo.profile_option_name = 'PAY_IE_P35_REPORTING_YEAR'
5668: AND fpov.level_id = 10001
5669: AND fpov.level_value = 0;
5670:
5671: RETURN l_end_date;
5737: l_string := replace(l_string, '>', '>');
5738: l_string := replace(l_string, '''',''');
5739: l_string := replace(l_string, '"', '"');
5740: -- bug 6275544, called
5741: l_string := pay_ie_p35_magtape.test_XML(l_string);
5742: -- bug 5867343, special characters.
5743: /*l_string := replace(l_string, fnd_global.local_chr(193),'Á');
5744: l_string := replace(l_string, fnd_global.local_chr(201),'É');
5745: l_string := replace(l_string, fnd_global.local_chr(205),'Í');
5758: EXCEPTION when no_data_found then
5759: null;
5760: END replace_xml_symbols;
5761:
5762: END pay_ie_p35;