[Home] [Help]
74: ,get_parameters(c_payroll_action_id, 'Sequence_Number')
75: ,start_date
76: ,effective_date
77: ,business_group_id
78: --,NVL(to_char(fnd_date.canonical_to_date(pay_nl_wage_report_pkg.get_parameters(c_payroll_action_id, 'CORR_YR')),'RRRR'),-9999) --10100628
79: ,NVL(pay_nl_wage_report_pkg.get_parameters(c_payroll_action_id, 'CORR_YR'),'N') --10100628
80: ,NVL(pay_nl_wage_report_pkg.get_parameters(c_payroll_action_id, 'ER_ONLY'),'N') --10100628
81: FROM pay_payroll_actions
82: WHERE payroll_action_id = c_payroll_action_id;
2159: ,p_collXMLTable OUT NOCOPY tXMLTable) IS
2160: --
2161: CURSOR csr_get_org_tax_data(c_org_id NUMBER
2162: ,c_payroll_type VARCHAR2) IS
2163: SELECT fnd_date.canonical_to_date(org_information1) start_date
2164: ,fnd_date.canonical_to_date(org_information2) end_date
2165: ,org_information3
2166: ,fnd_number.canonical_to_number(org_information4) amount
2167: ,org_information5 frequency
2160: --
2161: CURSOR csr_get_org_tax_data(c_org_id NUMBER
2162: ,c_payroll_type VARCHAR2) IS
2163: SELECT fnd_date.canonical_to_date(org_information1) start_date
2164: ,fnd_date.canonical_to_date(org_information2) end_date
2165: ,org_information3
2166: ,fnd_number.canonical_to_number(org_information4) amount
2167: ,org_information5 frequency
2168: ,org_information6
2170: WHERE organization_id = c_org_id
2171: AND org_information_context = 'NL_ORG_FLAT_RATE_TAXATION'
2172: -- AND org_information3 = '1' -- Bug# 5754707
2173: AND org_information6 = c_payroll_type
2174: ORDER BY fnd_date.canonical_to_date(org_information1);
2175: --Bug 14125837 begin
2176: CURSOR csr_get_org_corr_data(c_org_id NUMBER
2177: ,c_payroll_type VARCHAR2
2178: ,c_balance_date DATE) IS
2175: --Bug 14125837 begin
2176: CURSOR csr_get_org_corr_data(c_org_id NUMBER
2177: ,c_payroll_type VARCHAR2
2178: ,c_balance_date DATE) IS
2179: SELECT fnd_date.canonical_to_date (org_information1) start_date
2180: ,fnd_date.canonical_to_date (org_information2) end_date
2181: ,org_information3 tag_name
2182: ,fnd_number.canonical_to_number (org_information4) amount
2183: ,org_information5 frequency
2176: CURSOR csr_get_org_corr_data(c_org_id NUMBER
2177: ,c_payroll_type VARCHAR2
2178: ,c_balance_date DATE) IS
2179: SELECT fnd_date.canonical_to_date (org_information1) start_date
2180: ,fnd_date.canonical_to_date (org_information2) end_date
2181: ,org_information3 tag_name
2182: ,fnd_number.canonical_to_number (org_information4) amount
2183: ,org_information5 frequency
2184: FROM hr_organization_information
2184: FROM hr_organization_information
2185: WHERE organization_id = c_org_id
2186: AND org_information_context = 'NL_ORG_WR_PREV_YR_CORRECTION'
2187: AND org_information6 = c_payroll_type
2188: AND c_balance_date BETWEEN fnd_date.canonical_to_date (org_information1)
2189: AND nvl (fnd_date.canonical_to_date (org_information2)
2190: ,hr_general.end_of_time)
2191: ORDER BY fnd_date.canonical_to_date (org_information1);
2192: --Bug 14125837 end
2185: WHERE organization_id = c_org_id
2186: AND org_information_context = 'NL_ORG_WR_PREV_YR_CORRECTION'
2187: AND org_information6 = c_payroll_type
2188: AND c_balance_date BETWEEN fnd_date.canonical_to_date (org_information1)
2189: AND nvl (fnd_date.canonical_to_date (org_information2)
2190: ,hr_general.end_of_time)
2191: ORDER BY fnd_date.canonical_to_date (org_information1);
2192: --Bug 14125837 end
2193: CURSOR csr_get_no_pay_period(c_payroll_type VARCHAR2) IS
2187: AND org_information6 = c_payroll_type
2188: AND c_balance_date BETWEEN fnd_date.canonical_to_date (org_information1)
2189: AND nvl (fnd_date.canonical_to_date (org_information2)
2190: ,hr_general.end_of_time)
2191: ORDER BY fnd_date.canonical_to_date (org_information1);
2192: --Bug 14125837 end
2193: CURSOR csr_get_no_pay_period(c_payroll_type VARCHAR2) IS
2194: SELECT number_per_fiscal_year
2195: FROM per_time_period_types
2216: --10100628
2217: CURSOR C_PREV_CORR_COLL_VAL(p_payroll_action_id NUMBER, p_date DATE)
2218: IS
2219: SELECT
2220: --fnd_date.canonical_to_date(paei.AEI_INFORMATION1) start_date
2221: --,fnd_date.canonical_to_date(paei.AEI_INFORMATION2) end_date
2222: SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION3)) LnLbPh
2223: ,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION4)) LnSV
2224: ,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION5)) LnTabBB
2217: CURSOR C_PREV_CORR_COLL_VAL(p_payroll_action_id NUMBER, p_date DATE)
2218: IS
2219: SELECT
2220: --fnd_date.canonical_to_date(paei.AEI_INFORMATION1) start_date
2221: --,fnd_date.canonical_to_date(paei.AEI_INFORMATION2) end_date
2222: SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION3)) LnLbPh
2223: ,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION4)) LnSV
2224: ,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION5)) LnTabBB
2225: ,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION6)) VakBsl
2252: --10100628
2253: FROM per_assignment_extra_info paei
2254: WHERE
2255: paei.aei_information_category = 'NL_WR_PREV_CORR'
2256: AND p_date BETWEEN fnd_date.canonical_to_date(paei.aei_information1)
2257: AND nvl(fnd_date.canonical_to_date(paei.aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
2258: AND EXISTS
2259: (
2260: SELECT 1
2253: FROM per_assignment_extra_info paei
2254: WHERE
2255: paei.aei_information_category = 'NL_WR_PREV_CORR'
2256: AND p_date BETWEEN fnd_date.canonical_to_date(paei.aei_information1)
2257: AND nvl(fnd_date.canonical_to_date(paei.aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
2258: AND EXISTS
2259: (
2260: SELECT 1
2261: from
2360: ,sum (CASE
2361: WHEN info.action_information2 = 'EhOvsFrfWrkkstrg'
2362: THEN fnd_number.canonical_to_number (nvl (action_information6
2363: ,0)) END) lcatax
2364: ,min (fnd_date.canonical_to_date (info.action_information3)) startdate
2365: ,max (fnd_date.canonical_to_date (info.action_information4)) enddate
2366: ,count (DISTINCT generator.request_id) wagereports
2367: FROM pay_action_information info
2368: ,pay_payroll_actions generator
2361: WHEN info.action_information2 = 'EhOvsFrfWrkkstrg'
2362: THEN fnd_number.canonical_to_number (nvl (action_information6
2363: ,0)) END) lcatax
2364: ,min (fnd_date.canonical_to_date (info.action_information3)) startdate
2365: ,max (fnd_date.canonical_to_date (info.action_information4)) enddate
2366: ,count (DISTINCT generator.request_id) wagereports
2367: FROM pay_action_information info
2368: ,pay_payroll_actions generator
2369: ,pay_payroll_actions datalock
2589: , p_action_information7 => fnd_number.number_to_canonical(NVL(l_lca_taxable,0)) --Taxable LCA
2590: , p_action_information8 => fnd_number.number_to_canonical(NVL(l_lca_threshold,0)) --LCA Tax free threshold
2591: , p_action_information9 => fnd_number.number_to_canonical(NVL(p_taxable_income,0)) --Period Taxable Income
2592: , p_action_information10 => fnd_number.number_to_canonical(NVL(r_get_ytd_values.taxableincome,0)) -- Taxable Income computed till prev period
2593: , p_action_information11 => fnd_date.date_to_canonical(r_get_ytd_values.startdate) --Previous Records Start Date
2594: , p_action_information12 => fnd_date.date_to_canonical(r_get_ytd_values.enddate) --Previous Records End Date
2595: , p_action_information13 => fnd_number.number_to_canonical(NVL(r_get_ytd_values.wagereports,0)) --Count of Prev. records
2596:
2597: , p_action_information26 => fnd_number.number_to_canonical(NVL(l_lca_threshold_rate,1)) --Threshold Rate
2590: , p_action_information8 => fnd_number.number_to_canonical(NVL(l_lca_threshold,0)) --LCA Tax free threshold
2591: , p_action_information9 => fnd_number.number_to_canonical(NVL(p_taxable_income,0)) --Period Taxable Income
2592: , p_action_information10 => fnd_number.number_to_canonical(NVL(r_get_ytd_values.taxableincome,0)) -- Taxable Income computed till prev period
2593: , p_action_information11 => fnd_date.date_to_canonical(r_get_ytd_values.startdate) --Previous Records Start Date
2594: , p_action_information12 => fnd_date.date_to_canonical(r_get_ytd_values.enddate) --Previous Records End Date
2595: , p_action_information13 => fnd_number.number_to_canonical(NVL(r_get_ytd_values.wagereports,0)) --Count of Prev. records
2596:
2597: , p_action_information26 => fnd_number.number_to_canonical(NVL(l_lca_threshold_rate,1)) --Threshold Rate
2598: , p_action_information27 => fnd_number.number_to_canonical(NVL(l_lca_tax_rate,1)) --LCA Tax Rate
2597: , p_action_information26 => fnd_number.number_to_canonical(NVL(l_lca_threshold_rate,1)) --Threshold Rate
2598: , p_action_information27 => fnd_number.number_to_canonical(NVL(l_lca_tax_rate,1)) --LCA Tax Rate
2599: , p_action_information28 => l_recalculation --Recalculation flag
2600: , p_action_information29 => fnd_number.number_to_canonical(NVL(l_seq_no,0)) --Sequence
2601: , p_action_information30 => fnd_date.date_to_canonical(l_start_date) --Start Date
2602: );
2603: hr_utility.set_location('Leaving get_lca_details',900);
2604: RETURN NVL(l_lca_tax,0);
2605: EXCEPTION
2666: l_flat_rate_taxation := 0;
2667: -- Bug# 5754707, For Loop modified for all types of Look-up codes.
2668: FOR csr_rec in csr_get_org_tax_data(p_tax_unit_id,p_payroll_type) LOOP
2669:
2670: --10100628 IF csr_rec.org_information3 = '1' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2671: --10100628
2672: IF csr_rec.org_information3 = '1' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2673: IF csr_rec.frequency = 'P' THEN
2674: l_flat_rate_taxation := csr_rec.amount;
2668: FOR csr_rec in csr_get_org_tax_data(p_tax_unit_id,p_payroll_type) LOOP
2669:
2670: --10100628 IF csr_rec.org_information3 = '1' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2671: --10100628
2672: IF csr_rec.org_information3 = '1' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2673: IF csr_rec.frequency = 'P' THEN
2674: l_flat_rate_taxation := csr_rec.amount;
2675: ELSIF csr_rec.frequency = 'A' THEN
2676: l_flat_rate_taxation := csr_rec.amount/l_no_pay_period;
2676: l_flat_rate_taxation := csr_rec.amount/l_no_pay_period;
2677: END IF;
2678: END IF;
2679:
2680: --10100628 IF csr_rec.org_information3 = '2' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2681: --10100628
2682: IF csr_rec.org_information3 = '2' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2683: IF csr_rec.frequency = 'P' THEN
2684: l_major_issue_flat_rate := csr_rec.amount;
2678: END IF;
2679:
2680: --10100628 IF csr_rec.org_information3 = '2' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2681: --10100628
2682: IF csr_rec.org_information3 = '2' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2683: IF csr_rec.frequency = 'P' THEN
2684: l_major_issue_flat_rate := csr_rec.amount;
2685: ELSIF csr_rec.frequency = 'A' THEN
2686: l_major_issue_flat_rate := csr_rec.amount/l_no_pay_period;
2686: l_major_issue_flat_rate := csr_rec.amount/l_no_pay_period;
2687: END IF;
2688: END IF;
2689:
2690: --10100628 IF csr_rec.org_information3 = '3' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2691: --10100628
2692: IF csr_rec.org_information3 = '3' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2693: IF csr_rec.frequency = 'P' THEN
2694: l_single_rate_exp_pay := csr_rec.amount;
2688: END IF;
2689:
2690: --10100628 IF csr_rec.org_information3 = '3' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2691: --10100628
2692: IF csr_rec.org_information3 = '3' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2693: IF csr_rec.frequency = 'P' THEN
2694: l_single_rate_exp_pay := csr_rec.amount;
2695: ELSIF csr_rec.frequency = 'A' THEN
2696: l_single_rate_exp_pay := csr_rec.amount/l_no_pay_period;
2696: l_single_rate_exp_pay := csr_rec.amount/l_no_pay_period;
2697: END IF;
2698: END IF;
2699:
2700: --10100628 IF csr_rec.org_information3 = '4' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2701: --10100628
2702: IF csr_rec.org_information3 = '4' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2703: IF csr_rec.frequency = 'P' THEN
2704: l_wage_tax_subsidy := csr_rec.amount;
2698: END IF;
2699:
2700: --10100628 IF csr_rec.org_information3 = '4' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2701: --10100628
2702: IF csr_rec.org_information3 = '4' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2703: IF csr_rec.frequency = 'P' THEN
2704: l_wage_tax_subsidy := csr_rec.amount;
2705: ELSIF csr_rec.frequency = 'A' THEN
2706: l_wage_tax_subsidy := csr_rec.amount/l_no_pay_period;
2707: END IF;
2708: END IF;
2709:
2710: /**** Bug 6610259 ****/
2711: --10100628 IF csr_rec.org_information3 = '5' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2712: --10100628
2713: IF csr_rec.org_information3 = '5' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2714: IF csr_rec.frequency = 'P' THEN
2715: l_flat_tax_holidays := csr_rec.amount;
2709:
2710: /**** Bug 6610259 ****/
2711: --10100628 IF csr_rec.org_information3 = '5' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2712: --10100628
2713: IF csr_rec.org_information3 = '5' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2714: IF csr_rec.frequency = 'P' THEN
2715: l_flat_tax_holidays := csr_rec.amount;
2716: ELSIF csr_rec.frequency = 'A' THEN
2717: l_flat_tax_holidays := csr_rec.amount/l_no_pay_period;
2718: END IF;
2719: END IF;
2720:
2721: /* 7533686 */
2722: --10100628 IF csr_rec.org_information3 = '6' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2723: --10100628
2724: IF csr_rec.org_information3 = '6' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2725: IF csr_rec.frequency = 'P' THEN
2726: l_flat_tax_pre_pension := csr_rec.amount;
2720:
2721: /* 7533686 */
2722: --10100628 IF csr_rec.org_information3 = '6' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2723: --10100628
2724: IF csr_rec.org_information3 = '6' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2725: IF csr_rec.frequency = 'P' THEN
2726: l_flat_tax_pre_pension := csr_rec.amount;
2727: ELSIF csr_rec.frequency = 'A' THEN
2728: l_flat_tax_pre_pension := csr_rec.amount/l_no_pay_period;
2729: END IF;
2730: END IF;
2731:
2732: --10370520
2733: IF csr_rec.org_information3 = '7' AND p_balance_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
2734: IF csr_rec.frequency = 'P' THEN
2735: l_flat_tax_lca := csr_rec.amount;
2736: ELSIF csr_rec.frequency = 'A' THEN
2737: l_flat_tax_lca := csr_rec.amount/l_no_pay_period;
3671: AND hou.organization_id = hoi.organization_id
3672: AND hou.organization_id = c_employer_id;
3673: --
3674: CURSOR csr_retro_date(c_business_group_id NUMBER) IS
3675: select fnd_date.canonical_to_date(org_information5)
3676: from hr_organization_information
3677: where organization_id = c_business_group_id
3678: AND org_information_context = 'NL_BG_INFO';
3679: --
3922: , p_tax_unit_id => p_tax_unit_id
3923: , p_action_information_category => 'NL_WR_EMPLOYER_INFO'
3924: , p_action_information1 => p_tax_unit_id
3925: , p_action_information2 => substr(l_empr_contact.sender_id||l_tax_details.tax_reg_num,1,32)
3926: , p_action_information3 => fnd_date.date_to_canonical(sysdate)
3927: , p_action_information4 => substr(l_empr_contact.contact_name,1,35)
3928: , p_action_information5 => substr(l_seq_no,1,6)
3929: , p_action_information6 => substr(l_empr_contact.contact_num,1,25) --abraghun--7668628-- LC 2009 : Format changed from X(14) to X(25)
3930: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
3929: , p_action_information6 => substr(l_empr_contact.contact_num,1,25) --abraghun--7668628-- LC 2009 : Format changed from X(14) to X(25)
3930: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
3931: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12)
3932: , p_action_information9 => substr(l_tax_details.tax_rep_name,1,200)
3933: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)
3934: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)
3935: , p_action_information12 => g_contract_code_mapping
3936: --LC2010--
3937: , p_action_information13 => 'SWO00361'
3930: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
3931: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12)
3932: , p_action_information9 => substr(l_tax_details.tax_rep_name,1,200)
3933: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)
3934: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)
3935: , p_action_information12 => g_contract_code_mapping
3936: --LC2010--
3937: , p_action_information13 => 'SWO00361'
3938: , p_action_information14 => 'Oracle Payroll');
4095: , p_tax_unit_id => p_tax_unit_id
4096: , p_action_information_category => 'NL_WR_EMPLOYER_INFO'
4097: , p_action_information1 => p_tax_unit_id
4098: , p_action_information2 => substr(l_empr_contact.sender_id||l_tax_details.tax_reg_num,1,32)
4099: , p_action_information3 => fnd_date.date_to_canonical(sysdate)
4100: , p_action_information4 => substr(l_empr_contact.contact_name,1,35)
4101: , p_action_information5 => substr(l_seq_no,1,6)
4102: , p_action_information6 => substr(l_empr_contact.contact_num,1,25) --abraghun--7668628-- LC 2009 : Format changed from X(14) to X(25)
4103: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
4102: , p_action_information6 => substr(l_empr_contact.contact_num,1,25) --abraghun--7668628-- LC 2009 : Format changed from X(14) to X(25)
4103: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
4104: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12)
4105: , p_action_information9 => substr(l_tax_details.tax_rep_name,1,200)
4106: , p_action_information10 => fnd_date.date_to_canonical(GREATEST(p_start_date,trunc(p_end_date,'Y')))
4107: , p_action_information11 => fnd_date.date_to_canonical(p_end_date)
4108: , p_action_information12 => g_contract_code_mapping
4109: --LC2010--
4110: , p_action_information13 => 'SWO00361'
4103: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
4104: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12)
4105: , p_action_information9 => substr(l_tax_details.tax_rep_name,1,200)
4106: , p_action_information10 => fnd_date.date_to_canonical(GREATEST(p_start_date,trunc(p_end_date,'Y')))
4107: , p_action_information11 => fnd_date.date_to_canonical(p_end_date)
4108: , p_action_information12 => g_contract_code_mapping
4109: --LC2010--
4110: , p_action_information13 => 'SWO00361'
4111: , p_action_information14 => 'Oracle Payroll');
4257: -- Cursor for Check0022
4258:
4259: CURSOR csr_period_overlap (c_payroll_action_id IN NUMBER) IS
4260: SELECT
4261: fnd_date.canonical_to_date(pai1.action_information2) start_date
4262: ,fnd_date.canonical_to_date(pai1.action_information3) end_date
4263: FROM pay_action_information pai1,
4264: pay_action_information pai2
4265: ,pay_assignment_actions paa
4258:
4259: CURSOR csr_period_overlap (c_payroll_action_id IN NUMBER) IS
4260: SELECT
4261: fnd_date.canonical_to_date(pai1.action_information2) start_date
4262: ,fnd_date.canonical_to_date(pai1.action_information3) end_date
4263: FROM pay_action_information pai1,
4264: pay_action_information pai2
4265: ,pay_assignment_actions paa
4266: WHERE pai1.action_context_type = 'AAP'
4336: AND hr_nl_org_info.Get_Tax_Org_Id(posv.ORG_STRUCTURE_VERSION_ID ,hoi2.organization_id) = c_employer_id
4337: AND posv.ORGANIZATION_STRUCTURE_ID = TO_NUMBER(hoi3.org_information1)
4338: AND c_effective_date BETWEEN posv.date_from
4339: AND nvl(posv.date_to,to_date('31-12-4712','dd-mm-yyyy'))
4340: AND c_effective_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information1)
4341: AND nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31-12-4712','dd-mm-yyyy'))
4342: AND EXISTS (SELECT 1
4343: FROM hr_organization_information hoi4
4344: WHERE hoi4.organization_id = hoi1.organization_id
4337: AND posv.ORGANIZATION_STRUCTURE_ID = TO_NUMBER(hoi3.org_information1)
4338: AND c_effective_date BETWEEN posv.date_from
4339: AND nvl(posv.date_to,to_date('31-12-4712','dd-mm-yyyy'))
4340: AND c_effective_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information1)
4341: AND nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31-12-4712','dd-mm-yyyy'))
4342: AND EXISTS (SELECT 1
4343: FROM hr_organization_information hoi4
4344: WHERE hoi4.organization_id = hoi1.organization_id
4345: AND hoi4.org_information_context = 'NL_SIT'
4347: ORDER BY 1,2;
4348: --
4349: -- Correction balance total periods
4350: CURSOR csr_ret_cor_period(c_pact_id NUMBER) IS
4351: SELECT DISTINCT fnd_date.canonical_to_date(pai.Action_Information2) Start_date
4352: ,fnd_date.canonical_to_date(pai.action_information3) End_date
4353: FROM pay_assignment_actions paa
4354: ,pay_action_information pai
4355: WHERE paa.payroll_action_id = c_pact_id
4348: --
4349: -- Correction balance total periods
4350: CURSOR csr_ret_cor_period(c_pact_id NUMBER) IS
4351: SELECT DISTINCT fnd_date.canonical_to_date(pai.Action_Information2) Start_date
4352: ,fnd_date.canonical_to_date(pai.action_information3) End_date
4353: FROM pay_assignment_actions paa
4354: ,pay_action_information pai
4355: WHERE paa.payroll_action_id = c_pact_id
4356: AND paa.assignment_action_id = pai.action_context_id
4401: --
4402: --#
4403: CURSOR csr_get_PA_exception_info(p_payroll_action_id IN NUMBER) IS
4404: SELECT pai_p.action_information4 Message
4405: ,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(pai_p.action_information5)) Dt
4406: ,pai_p.action_information6 Description
4407: ,substr(pai_p.action_information7,1,30) E_Name
4408: ,substr(pai_p.action_information8,1,30) E_Number
4409: ,pai_p.action_context_type cxt
4414: ORDER BY pai_p.action_information8 asc;
4415: --
4416: CURSOR csr_get_AAP_exception_info(p_payroll_action_id IN NUMBER) IS
4417: SELECT pai_p.action_information4 Message
4418: ,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(pai_p.action_information5)) Dt
4419: ,pai_p.action_information6 Description
4420: ,substr(pai_p.action_information7,1,30) E_Name
4421: ,substr(pai_p.action_information8,1,30) E_Number
4422: ,pai_p.action_context_type cxt
4576: CURSOR C_ER_ONLY_PD (p_tax_unit_id NUMBER, c_payroll_type VARCHAR2, p_start_date DATE)
4577: IS
4578: SELECT NVL(org_information7,'N')
4579: --10100628
4580: /*distinct fnd_date.canonical_to_date(org_information1) start_date
4581: ,fnd_date.canonical_to_date(org_information2) end_date
4582: ,org_information3
4583: ,fnd_number.canonical_to_number(org_information4) amount
4584: ,org_information5 frequency
4577: IS
4578: SELECT NVL(org_information7,'N')
4579: --10100628
4580: /*distinct fnd_date.canonical_to_date(org_information1) start_date
4581: ,fnd_date.canonical_to_date(org_information2) end_date
4582: ,org_information3
4583: ,fnd_number.canonical_to_number(org_information4) amount
4584: ,org_information5 frequency
4585: ,org_information6 */
4587: FROM hr_organization_information
4588: WHERE organization_id = p_tax_unit_id
4589: AND org_information_context = 'NL_ORG_FLAT_RATE_TAXATION'
4590: AND org_information6 = c_payroll_type
4591: AND p_start_date BETWEEN fnd_date.canonical_to_date(org_information1)
4592: AND NVL(fnd_date.canonical_to_date(org_information2),fnd_date.canonical_to_date('4712/12/31'))
4593: ORDER BY fnd_date.canonical_to_date(org_information1);
4594:
4595: CURSOR C_ER_PYRLS(p_tax_unit_id NUMBER, p_bg_id NUMBER, p_start_date DATE, p_payroll_type VARCHAR2)
4588: WHERE organization_id = p_tax_unit_id
4589: AND org_information_context = 'NL_ORG_FLAT_RATE_TAXATION'
4590: AND org_information6 = c_payroll_type
4591: AND p_start_date BETWEEN fnd_date.canonical_to_date(org_information1)
4592: AND NVL(fnd_date.canonical_to_date(org_information2),fnd_date.canonical_to_date('4712/12/31'))
4593: ORDER BY fnd_date.canonical_to_date(org_information1);
4594:
4595: CURSOR C_ER_PYRLS(p_tax_unit_id NUMBER, p_bg_id NUMBER, p_start_date DATE, p_payroll_type VARCHAR2)
4596: IS
4589: AND org_information_context = 'NL_ORG_FLAT_RATE_TAXATION'
4590: AND org_information6 = c_payroll_type
4591: AND p_start_date BETWEEN fnd_date.canonical_to_date(org_information1)
4592: AND NVL(fnd_date.canonical_to_date(org_information2),fnd_date.canonical_to_date('4712/12/31'))
4593: ORDER BY fnd_date.canonical_to_date(org_information1);
4594:
4595: CURSOR C_ER_PYRLS(p_tax_unit_id NUMBER, p_bg_id NUMBER, p_start_date DATE, p_payroll_type VARCHAR2)
4596: IS
4597: select PAYROLL_ID
4621: FROM hr_organization_information
4622: WHERE organization_id = p_tax_unit_id
4623: AND org_information_context = 'NL_ORG_FLAT_RATE_TAXATION'
4624: AND org_information6 = c_payroll_type
4625: AND START_DATE BETWEEN fnd_date.canonical_to_date(org_information1)
4626: AND NVL(fnd_date.canonical_to_date(org_information2),fnd_date.canonical_to_date('4712/12/31'))
4627: AND NVL(org_information7,'N') = 'Y'
4628: )
4629: ORDER by 1 asc; */
4622: WHERE organization_id = p_tax_unit_id
4623: AND org_information_context = 'NL_ORG_FLAT_RATE_TAXATION'
4624: AND org_information6 = c_payroll_type
4625: AND START_DATE BETWEEN fnd_date.canonical_to_date(org_information1)
4626: AND NVL(fnd_date.canonical_to_date(org_information2),fnd_date.canonical_to_date('4712/12/31'))
4627: AND NVL(org_information7,'N') = 'Y'
4628: )
4629: ORDER by 1 asc; */
4630: CURSOR C_ER_CORR_PD(p_payroll_id NUMBER, p_start_date DATE, p_end_date DATE, p_tax_unit_id NUMBER, c_payroll_type VARCHAR2) --10100628
4654: org_information_context = 'NL_ORG_WR_PREV_YR_CORRECTION'
4655: )
4656: )
4657: AND org_information6 = c_payroll_type
4658: AND start_date BETWEEN fnd_date.canonical_to_date (org_information1)
4659: AND nvl (fnd_date.canonical_to_date (org_information2)
4660: ,fnd_date.canonical_to_date ('4712/12/31'))
4661:
4662: )
4655: )
4656: )
4657: AND org_information6 = c_payroll_type
4658: AND start_date BETWEEN fnd_date.canonical_to_date (org_information1)
4659: AND nvl (fnd_date.canonical_to_date (org_information2)
4660: ,fnd_date.canonical_to_date ('4712/12/31'))
4661:
4662: )
4663: ORDER by 1 asc;
4656: )
4657: AND org_information6 = c_payroll_type
4658: AND start_date BETWEEN fnd_date.canonical_to_date (org_information1)
4659: AND nvl (fnd_date.canonical_to_date (org_information2)
4660: ,fnd_date.canonical_to_date ('4712/12/31'))
4661:
4662: )
4663: ORDER by 1 asc;
4664: --bug 14125837 end
4694: ,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION30)) PrLnPrSectFnds
4695: FROM per_assignment_extra_info paei
4696: WHERE
4697: paei.aei_information_category = 'NL_WR_PREV_CORR'
4698: AND p_date BETWEEN fnd_date.canonical_to_date(paei.aei_information1)
4699: AND nvl(fnd_date.canonical_to_date(paei.aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
4700: AND EXISTS
4701: (
4702: SELECT 1
4695: FROM per_assignment_extra_info paei
4696: WHERE
4697: paei.aei_information_category = 'NL_WR_PREV_CORR'
4698: AND p_date BETWEEN fnd_date.canonical_to_date(paei.aei_information1)
4699: AND nvl(fnd_date.canonical_to_date(paei.aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
4700: AND EXISTS
4701: (
4702: SELECT 1
4703: from
4807: , p_source_text => NULL
4808: , p_tax_unit_id => l_tax_unit_id
4809: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
4810: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_SENDER_ID')
4811: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4812: , p_action_information6 => 'Sender ID is null'
4813: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
4814: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
4815: END IF;
4827: , p_source_text => NULL
4828: , p_tax_unit_id => l_tax_unit_id
4829: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
4830: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_PERSON_NM')
4831: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4832: , p_action_information6 => 'Contact Person is null'
4833: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
4834: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
4835: END IF;
4847: , p_source_text => NULL
4848: , p_tax_unit_id => l_tax_unit_id
4849: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
4850: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_CONTACT_NO')
4851: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4852: , p_action_information6 => 'Contact Number is null'
4853: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
4854: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
4855: END IF;
4867: , p_source_text => NULL
4868: , p_tax_unit_id => l_tax_unit_id
4869: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
4870: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_TAX_NO')
4871: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4872: , p_action_information6 => 'Tax Registration Number is null'
4873: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
4874: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
4875: END IF;
4888: , p_source_text => NULL
4889: , p_tax_unit_id => l_tax_unit_id
4890: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
4891: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_TAX_FORMAT')
4892: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4893: , p_action_information6 => 'Tax Registration Number is not in the format 111111111L11'
4894: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
4895: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
4896: END IF;
4908: , p_source_text => NULL
4909: , p_tax_unit_id => l_tax_unit_id
4910: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
4911: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_TAX_NAME')
4912: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4913: , p_action_information6 => 'Tax Reporting Name is null'
4914: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
4915: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
4916: END IF;
4930: , p_tax_unit_id => l_tax_unit_id
4931: , p_action_information_category => 'NL_WR_EMPLOYER_INFO'
4932: , p_action_information1 => l_tax_unit_id
4933: , p_action_information2 => substr(l_empr_contact.sender_id||l_tax_details.tax_reg_num,1,32)
4934: , p_action_information3 => fnd_date.date_to_canonical(sysdate)
4935: , p_action_information4 => substr(l_empr_contact.contact_name,1,35)
4936: , p_action_information5 => substr(l_seq_no,1,6)
4937: , p_action_information6 => substr(l_empr_contact.contact_num,1,25) --abraghun--7668628 -- LC 2009: Phone format changed from X(14) to X(25)
4938: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
4937: , p_action_information6 => substr(l_empr_contact.contact_num,1,25) --abraghun--7668628 -- LC 2009: Phone format changed from X(14) to X(25)
4938: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
4939: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12)
4940: , p_action_information9 => substr(l_tax_details.tax_rep_name,1,200)
4941: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)
4942: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)
4943: , p_action_information12 => g_contract_code_mapping
4944: --LC2010--
4945: , p_action_information13 => 'SWO00361'
4938: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
4939: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12)
4940: , p_action_information9 => substr(l_tax_details.tax_rep_name,1,200)
4941: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)
4942: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)
4943: , p_action_information12 => g_contract_code_mapping
4944: --LC2010--
4945: , p_action_information13 => 'SWO00361'
4946: , p_action_information14 => 'Oracle Payroll');
4948: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
4949: --
4950: FOR l_period_dates_rec IN csr_period_dates(p_actid) LOOP
4951: --Fnd_file.put_line(FND_FILE.LOG,' In csr_period_dates '||l_period_dates_rec.End_date);
4952: l_date := fnd_date.canonical_to_date(l_period_dates_rec.End_date);
4953: IF l_date >= l_start_date AND l_date <= l_end_date THEN -- COMPLETE RECORD
4954: --Fnd_file.put_line(FND_FILE.LOG,' If Current Period -- COMPLETE ');
4955: --Fnd_file.put_line(FND_FILE.LOG,' Calling populate_coll_bal_table for COMPLETE ');
4956: populate_coll_bal_table(p_actid => p_actid
5183: , p_source_text => NULL
5184: , p_tax_unit_id => l_tax_unit_id
5185: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
5186: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_SECTOR')
5187: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
5188: , p_action_information6 => 'Mandatory check on Sector'
5189: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
5190: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
5191: --
5201: , p_source_text => NULL
5202: , p_tax_unit_id => l_tax_unit_id
5203: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
5204: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_RISK_GROUP')
5205: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
5206: , p_action_information6 => 'No Risk Group'
5207: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
5208: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
5209: END IF;
5221: , p_source_text => NULL
5222: , p_tax_unit_id => l_tax_unit_id
5223: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
5224: , p_action_information4 => fnd_message.get_string('PER','HR_NL_ZERO_BASE_WEWE') -- Message Code should have been '%_WEWA'
5225: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
5226: , p_action_information6 => 'Waiting Money Fund contribution base is zero'
5227: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
5228: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
5229: END IF;
5241: , p_source_text => NULL
5242: , p_tax_unit_id => l_tax_unit_id
5243: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
5244: , p_action_information4 => fnd_message.get_string('PER','HR_NL_ZERO_AWF')
5245: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
5246: , p_action_information6 => 'AWF contribution base is zero'
5247: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
5248: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
5249: END IF;
5254: --Fnd_file.put_line(FND_FILE.LOG,'Fetching prev period act_id -- CORR_BALANCE ');
5255: OPEN csr_get_prev_period(l_curr_ass_action_id);
5256: FETCH csr_get_prev_period INTO l_prev_ass_act_id, l_prev_end_date;
5257: CLOSE csr_get_prev_period;
5258: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));
5259: --Fnd_file.put_line(FND_FILE.LOG,'Prev period act_id -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_ass_act_id));
5260: FOR l_ret_cor_period IN csr_ret_cor_period(p_actid) LOOP
5261: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORR_BALANCE '||fnd_date.date_to_canonical(l_ret_cor_period.End_date));
5262: populate_coll_bal_table(p_actid => p_actid
5257: CLOSE csr_get_prev_period;
5258: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));
5259: --Fnd_file.put_line(FND_FILE.LOG,'Prev period act_id -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_ass_act_id));
5260: FOR l_ret_cor_period IN csr_ret_cor_period(p_actid) LOOP
5261: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORR_BALANCE '||fnd_date.date_to_canonical(l_ret_cor_period.End_date));
5262: populate_coll_bal_table(p_actid => p_actid
5263: ,p_tax_unit_id => l_tax_unit_id
5264: ,p_effective_date=> l_end_date
5265: ,p_balance_date => l_ret_cor_period.End_date
5318: , p_source_text => NULL
5319: , p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
5320: , p_action_information1 => 'CORR_BALANCE'
5321: , p_action_information2 => 'Saldo'
5322: , p_action_information3 => fnd_date.date_to_canonical(l_ret_cor_period.start_date)
5323: , p_action_information4 => fnd_date.date_to_canonical(l_ret_cor_period.End_date)
5324: , p_action_information5 => HR_GENERAL.decode_lookup('NL_FORM_LABELS',UPPER('Saldo'))
5325: , p_action_information6 => fnd_number.number_to_canonical(ROUND(l_corr_bal)));
5326: /*
5319: , p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
5320: , p_action_information1 => 'CORR_BALANCE'
5321: , p_action_information2 => 'Saldo'
5322: , p_action_information3 => fnd_date.date_to_canonical(l_ret_cor_period.start_date)
5323: , p_action_information4 => fnd_date.date_to_canonical(l_ret_cor_period.End_date)
5324: , p_action_information5 => HR_GENERAL.decode_lookup('NL_FORM_LABELS',UPPER('Saldo'))
5325: , p_action_information6 => fnd_number.number_to_canonical(ROUND(l_corr_bal)));
5326: /*
5327: --abraghun--7668628-- Validation Data Capture --Check0011 -Begins
5357: */
5358: ELSE
5359: --Fnd_file.put_line(FND_FILE.LOG,' ELSE (Correction Period) -- CORRECTION ');
5360: OPEN csr_payroll_get_action_id2(p_actid
5361: ,fnd_date.canonical_to_date(l_period_dates_rec.start_date)
5362: ,fnd_date.canonical_to_date(l_period_dates_rec.End_date));
5363: FETCH csr_payroll_get_action_id2 INTO l_period_ass_action_id;
5364: CLOSE csr_payroll_get_action_id2;
5365: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORRECTION ');
5358: ELSE
5359: --Fnd_file.put_line(FND_FILE.LOG,' ELSE (Correction Period) -- CORRECTION ');
5360: OPEN csr_payroll_get_action_id2(p_actid
5361: ,fnd_date.canonical_to_date(l_period_dates_rec.start_date)
5362: ,fnd_date.canonical_to_date(l_period_dates_rec.End_date));
5363: FETCH csr_payroll_get_action_id2 INTO l_period_ass_action_id;
5364: CLOSE csr_payroll_get_action_id2;
5365: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORRECTION ');
5366: populate_coll_bal_table(p_actid => p_actid
5591: FOR ret_period IN csr_ret_cor_period1(p_actid) LOOP
5592:
5593: --Fnd_file.put_line(FND_FILE.LOG,' ELSE (Correction Period) -- CORRECTION ');
5594: OPEN csr_payroll_get_action_id2(p_actid
5595: ,fnd_date.canonical_to_date(ret_period.start_date)
5596: ,fnd_date.canonical_to_date(ret_period.End_date));
5597: FETCH csr_payroll_get_action_id2 INTO l_period_ass_action_id;
5598: CLOSE csr_payroll_get_action_id2;
5599:
5592:
5593: --Fnd_file.put_line(FND_FILE.LOG,' ELSE (Correction Period) -- CORRECTION ');
5594: OPEN csr_payroll_get_action_id2(p_actid
5595: ,fnd_date.canonical_to_date(ret_period.start_date)
5596: ,fnd_date.canonical_to_date(ret_period.End_date));
5597: FETCH csr_payroll_get_action_id2 INTO l_period_ass_action_id;
5598: CLOSE csr_payroll_get_action_id2;
5599:
5600: --10100628
5597: FETCH csr_payroll_get_action_id2 INTO l_period_ass_action_id;
5598: CLOSE csr_payroll_get_action_id2;
5599:
5600: --10100628
5601: OPEN C_PREV_CORR_YR_WTFUND(p_actid, fnd_date.canonical_to_date(ret_period.End_date));
5602: FETCH C_PREV_CORR_YR_WTFUND INTO l_co_col_PrWgf, l_co_col_PrLnPrSectFnds;
5603: CLOSE C_PREV_CORR_YR_WTFUND;
5604: --10100628
5605:
5608:
5609: populate_coll_bal_table(p_actid => p_actid
5610: ,p_tax_unit_id => l_tax_unit_id
5611: ,p_effective_date => l_end_date
5612: ,p_balance_date => fnd_date.canonical_to_date(ret_period.End_date)
5613: ,p_type => 'CORRECTION'
5614: ,p_ass_action_id => l_period_ass_action_id -- pass that months action id
5615: ,p_payroll_type => l_payroll_type
5616: ,p_emp_total => l_emp_total
5630: l_con_mon_fd := 0;
5631: l_c_base_mon_fd_z := 'N';
5632:
5633:
5634: FOR l_get_sect_risk_grp in csr_get_sect_risk_grp(NVL(l_hr_tax_unit,l_tax_unit_id),fnd_date.canonical_to_date(ret_period.End_date),l_business_group_id) LOOP
5635: l_sip_sector := NVL(l_sip_sector,l_get_sect_risk_grp.sector);
5636: l_risk_grp := NVL(l_risk_grp,l_get_sect_risk_grp.risk_group);
5637: IF l_sip_sector <> l_get_sect_risk_grp.sector OR l_risk_grp <> l_get_sect_risk_grp.risk_group THEN
5638:
5670: END IF;
5671: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORRECTION');
5672:
5673:
5674: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_end_date,fnd_date.canonical_to_date(ret_period.End_date),'CORRECTION',l_get_sect_risk_grp.organization_id,l_period_ass_action_id);
5675:
5676:
5677: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
5678: l_c_base_mon_fd := 0;
5805: ,p_source_text => NULL
5806: ,p_tax_unit_id => l_tax_unit_id
5807: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
5808: ,p_action_information1 => 'CORRECT'
5809: ,p_action_information2 => fnd_date.date_to_canonical(rec_ER_CORR_PD.start_date)
5810: ,p_action_information3 => fnd_date.date_to_canonical(rec_ER_CORR_PD.end_date)
5811: );
5812:
5813: OPEN C_ASG_ACT_ID(l_business_group_id, l_tax_unit_id, rec_ER_CORR_PD.start_date, rec_ER_CORR_PD.end_date);
5806: ,p_tax_unit_id => l_tax_unit_id
5807: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
5808: ,p_action_information1 => 'CORRECT'
5809: ,p_action_information2 => fnd_date.date_to_canonical(rec_ER_CORR_PD.start_date)
5810: ,p_action_information3 => fnd_date.date_to_canonical(rec_ER_CORR_PD.end_date)
5811: );
5812:
5813: OPEN C_ASG_ACT_ID(l_business_group_id, l_tax_unit_id, rec_ER_CORR_PD.start_date, rec_ER_CORR_PD.end_date);
5814: FETCH C_ASG_ACT_ID INTO l_period_ass_action_id;
5856: ,p_tax_unit_id => l_tax_unit_id
5857: , p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
5858: , p_action_information1 => 'SWMF'
5859: , p_action_information2 => NULL
5860: , p_action_information5 => fnd_date.date_to_canonical(rec_ER_CORR_PD.start_date)
5861: , p_action_information6 => fnd_date.date_to_canonical(rec_ER_CORR_PD.End_date)
5862: , p_action_information7 => substr(l_sip_sector,1,3)
5863: , p_action_information8 => substr(l_risk_grp,1,2)
5864: , p_action_information9 => fnd_number.number_to_canonical(ROUND(l_c_base_mon_fd))
5857: , p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
5858: , p_action_information1 => 'SWMF'
5859: , p_action_information2 => NULL
5860: , p_action_information5 => fnd_date.date_to_canonical(rec_ER_CORR_PD.start_date)
5861: , p_action_information6 => fnd_date.date_to_canonical(rec_ER_CORR_PD.End_date)
5862: , p_action_information7 => substr(l_sip_sector,1,3)
5863: , p_action_information8 => substr(l_risk_grp,1,2)
5864: , p_action_information9 => fnd_number.number_to_canonical(ROUND(l_c_base_mon_fd))
5865: , p_action_information10 => fnd_number.number_to_canonical(ROUND(l_con_mon_fd)));
5910: ,p_tax_unit_id => l_tax_unit_id
5911: , p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
5912: , p_action_information1 => 'SWMF'
5913: , p_action_information2 => NULL
5914: , p_action_information5 => fnd_date.date_to_canonical(rec_ER_CORR_PD.start_date)
5915: , p_action_information6 => fnd_date.date_to_canonical(rec_ER_CORR_PD.End_date)
5916: , p_action_information7 => substr(l_sip_sector,1,3)
5917: , p_action_information8 => substr(l_risk_grp,1,2)
5918: , p_action_information9 => fnd_number.number_to_canonical(ROUND(l_c_base_mon_fd))
5911: , p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
5912: , p_action_information1 => 'SWMF'
5913: , p_action_information2 => NULL
5914: , p_action_information5 => fnd_date.date_to_canonical(rec_ER_CORR_PD.start_date)
5915: , p_action_information6 => fnd_date.date_to_canonical(rec_ER_CORR_PD.End_date)
5916: , p_action_information7 => substr(l_sip_sector,1,3)
5917: , p_action_information8 => substr(l_risk_grp,1,2)
5918: , p_action_information9 => fnd_number.number_to_canonical(ROUND(l_c_base_mon_fd))
5919: , p_action_information10 => fnd_number.number_to_canonical(ROUND(l_con_mon_fd)));
5951: , p_source_text => NULL
5952: , p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
5953: , p_action_information1 => 'CORRECTION'
5954: , p_action_information2 => collXMLTable(i).TagName
5955: , p_action_information3 => fnd_date.date_to_canonical(rec_ER_CORR_PD.start_date)
5956: , p_action_information4 => fnd_date.date_to_canonical(rec_ER_CORR_PD.End_date)
5957: , p_action_information5 => collXMLTable(i).TagDesc
5958: , p_action_information6 => fnd_number.number_to_canonical(ROUND(NVL(l_val,collXMLTable(i).TagValue))));
5959: IF collXMLTable(i).TagName = 'PrLnAWF' AND collXMLTable(i).TagValue <> 0 THEN
5952: , p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
5953: , p_action_information1 => 'CORRECTION'
5954: , p_action_information2 => collXMLTable(i).TagName
5955: , p_action_information3 => fnd_date.date_to_canonical(rec_ER_CORR_PD.start_date)
5956: , p_action_information4 => fnd_date.date_to_canonical(rec_ER_CORR_PD.End_date)
5957: , p_action_information5 => collXMLTable(i).TagDesc
5958: , p_action_information6 => fnd_number.number_to_canonical(ROUND(NVL(l_val,collXMLTable(i).TagValue))));
5959: IF collXMLTable(i).TagName = 'PrLnAWF' AND collXMLTable(i).TagValue <> 0 THEN
5960: l_awf := 'Y';
6003: , p_tax_unit_id => l_tax_unit_id
6004: , p_action_information_category => 'NL_WR_EMPLOYER_INFO'
6005: , p_action_information1 => csr_employer_info_rec.action_information1
6006: , p_action_information2 => csr_employer_info_rec.action_information2
6007: , p_action_information3 => fnd_date.date_to_canonical(sysdate)
6008: , p_action_information4 => csr_employer_info_rec.action_information4
6009: , p_action_information5 => substr(l_seq_no,1,6)
6010: , p_action_information6 => csr_employer_info_rec.action_information6
6011: , p_action_information7 => csr_employer_info_rec.action_information7
6010: , p_action_information6 => csr_employer_info_rec.action_information6
6011: , p_action_information7 => csr_employer_info_rec.action_information7
6012: , p_action_information8 => csr_employer_info_rec.action_information8
6013: , p_action_information9 => csr_employer_info_rec.action_information9
6014: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)
6015: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)
6016: , p_action_information12 => csr_employer_info_rec.action_information12);
6017: --
6018: CLOSE csr_employer_info;
6011: , p_action_information7 => csr_employer_info_rec.action_information7
6012: , p_action_information8 => csr_employer_info_rec.action_information8
6013: , p_action_information9 => csr_employer_info_rec.action_information9
6014: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)
6015: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)
6016: , p_action_information12 => csr_employer_info_rec.action_information12);
6017: --
6018: CLOSE csr_employer_info;
6019: --
6384: ,p_tax_unit_id => l_tax_unit_id
6385: ,p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
6386: ,p_action_information1 => 'SWMF'
6387: ,p_action_information2 => NULL
6388: ,p_action_information5 => fnd_date.date_to_canonical(l_start_date)
6389: ,p_action_information6 => fnd_date.date_to_canonical(l_end_date)
6390: ,p_action_information7 => l_sector
6391: ,p_action_information8 => l_risk_group
6392: ,p_action_information9 => fnd_number.number_to_canonical(ROUND(l_c_base_mon_fd))
6385: ,p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
6386: ,p_action_information1 => 'SWMF'
6387: ,p_action_information2 => NULL
6388: ,p_action_information5 => fnd_date.date_to_canonical(l_start_date)
6389: ,p_action_information6 => fnd_date.date_to_canonical(l_end_date)
6390: ,p_action_information7 => l_sector
6391: ,p_action_information8 => l_risk_group
6392: ,p_action_information9 => fnd_number.number_to_canonical(ROUND(l_c_base_mon_fd))
6393: ,p_action_information10 => fnd_number.number_to_canonical(ROUND(l_con_mon_fd )));
6407: , p_source_text => NULL
6408: , p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
6409: , p_action_information1 => 'COMPLETE'
6410: , p_action_information2 => collXMLTable(i).TagName
6411: , p_action_information3 => fnd_date.date_to_canonical(l_start_date)
6412: , p_action_information4 => fnd_date.date_to_canonical(l_end_date)
6413: , p_action_information5 => collXMLTable(i).TagDesc
6414: , p_action_information6 => fnd_number.number_to_canonical(ROUND(collXMLTable(i).TagValue)));
6415: END IF;
6408: , p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
6409: , p_action_information1 => 'COMPLETE'
6410: , p_action_information2 => collXMLTable(i).TagName
6411: , p_action_information3 => fnd_date.date_to_canonical(l_start_date)
6412: , p_action_information4 => fnd_date.date_to_canonical(l_end_date)
6413: , p_action_information5 => collXMLTable(i).TagDesc
6414: , p_action_information6 => fnd_number.number_to_canonical(ROUND(collXMLTable(i).TagValue)));
6415: END IF;
6416: END LOOP;
6427: ,p_source_text => NULL
6428: ,p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
6429: ,p_action_information1 => 'TOTAL'
6430: ,p_action_information2 => 'TotGen' -- TAG NAME
6431: ,p_action_information3 => fnd_date.date_to_canonical(l_start_date)
6432: ,p_action_information4 => fnd_date.date_to_canonical(l_end_date)
6433: ,p_action_information5 => HR_GENERAL.decode_lookup('NL_FORM_LABELS',UPPER('TotGen')) -- TAG DESCRIPTION
6434: ,p_action_information6 => fnd_number.number_to_canonical(ROUND(l_emp_total)));
6435: --
6428: ,p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
6429: ,p_action_information1 => 'TOTAL'
6430: ,p_action_information2 => 'TotGen' -- TAG NAME
6431: ,p_action_information3 => fnd_date.date_to_canonical(l_start_date)
6432: ,p_action_information4 => fnd_date.date_to_canonical(l_end_date)
6433: ,p_action_information5 => HR_GENERAL.decode_lookup('NL_FORM_LABELS',UPPER('TotGen')) -- TAG DESCRIPTION
6434: ,p_action_information6 => fnd_number.number_to_canonical(ROUND(l_emp_total)));
6435: --
6436: END IF;
6484: , p_source_text => NULL
6485: , p_tax_unit_id => er_mismatch.ER_tax_unit_id
6486: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6487: , p_action_information4 => 'Check0036 failed: Sofi('||er_mismatch.Sofi||') and NumIV('||er_mismatch.NumIV||') combination is not unique'
6488: , p_action_information5 => fnd_date.date_to_canonical(er_mismatch.ER_eff_date)
6489: , p_action_information6 => 'Check0036 failed'
6490: , p_action_information7 => er_mismatch.ER_name
6491: , p_action_information8 => er_mismatch.ER_assignment_number);
6492: END IF;
6504: , p_source_text => NULL
6505: , p_tax_unit_id => er_mismatch.ER_tax_unit_id
6506: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6507: , p_action_information4 => 'Check0037 failed: PersNr('||er_mismatch.PersNr||') and NumIV('||er_mismatch.NumIV||') combination is not unique'
6508: , p_action_information5 => fnd_date.date_to_canonical(er_mismatch.ER_eff_date)
6509: , p_action_information6 => 'Check0037 failed'
6510: , p_action_information7 => er_mismatch.ER_name
6511: , p_action_information8 => er_mismatch.ER_assignment_number);
6512: END IF;
6527: , p_source_text => NULL
6528: , p_tax_unit_id => er_mismatch.ER_tax_unit_id
6529: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6530: , p_action_information4 => 'Check1036 failed: In Withdrawal, Sofi('||er_mismatch.Sofi||') and NumIV('||er_mismatch.NumIV||') combination is not unique'
6531: , p_action_information5 => fnd_date.date_to_canonical(er_mismatch.ER_eff_date)
6532: , p_action_information6 => 'Check1036 failed'
6533: , p_action_information7 => er_mismatch.ER_name
6534: , p_action_information8 => er_mismatch.ER_assignment_number);
6535: END IF;
6547: , p_source_text => NULL
6548: , p_tax_unit_id => er_mismatch.ER_tax_unit_id
6549: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6550: , p_action_information4 => 'Check1037 failed: In Withdrawal, PersNr('||er_mismatch.PersNr||') and NumIV('||er_mismatch.NumIV||') combination is not unique'
6551: , p_action_information5 => fnd_date.date_to_canonical(er_mismatch.ER_eff_date)
6552: , p_action_information6 => 'Check1037 failed'
6553: , p_action_information7 => er_mismatch.ER_name
6554: , p_action_information8 => er_mismatch.ER_assignment_number);
6555: END IF;
6577: , p_source_text => NULL
6578: , p_tax_unit_id => l_tax_unit_id
6579: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6580: , p_action_information4 => '0001 - '||fnd_message.get
6581: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6582: , p_action_information6 => '0001 - '||'Tag TotLnLbPh, (Total wage for taxes). The total amount of wages should be the same as the sum of the individual amounts of wage for taxes.'
6583: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6584: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6585: END IF;
6604: , p_source_text => NULL
6605: , p_tax_unit_id => l_tax_unit_id
6606: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6607: , p_action_information4 => '0002 - '||fnd_message.get
6608: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6609: , p_action_information6 => '0002 - '||'Tag TotLnSV, (Total SI wage). The total amount of SI wages should be the same as the sum of the individual amounts of SI wages.'
6610: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6611: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6612: END IF;
6630: , p_source_text => NULL
6631: , p_tax_unit_id => l_tax_unit_id
6632: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6633: , p_action_information4 => '0003 - '||fnd_message.get
6634: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6635: , p_action_information6 => '0003 - '||'Tag IngLbPh, (Deducted Tax). The total amount of deducted tax should be the same as the sum of the individual amounts of deducted Tax.'
6636: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6637: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6638: END IF;
6656: , p_source_text => NULL
6657: , p_tax_unit_id => l_tax_unit_id
6658: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6659: , p_action_information4 => '0006 - '||fnd_message.get
6660: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6661: , p_action_information6 => '0006 - '||'Tag PrAWF, (Total Contribution WeWe (EE+ER)). The total amount of contribution should be the same as the sum of the individual amounts of WeWe contribution employer + employee.'
6662: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6663: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6664: END IF;
6682: , p_source_text => NULL
6683: , p_tax_unit_id => l_tax_unit_id
6684: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6685: , p_action_information4 => '0008 - '||fnd_message.get
6686: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6687: , p_action_information6 => '0008 - '||'Tag PrUFO, (Contribution UFO). The total amount of contribution should be the same as the sum of the individual amounts of contribution UFO.'
6688: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6689: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6690: END IF;
6715: , p_source_text => NULL
6716: , p_tax_unit_id => l_tax_unit_id
6717: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6718: , p_action_information4 => '0009 - '||fnd_message.get
6719: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6720: , p_action_information6 => '0009 - '||'Tag TotVergZvw/TotWghZvw, (Total amount Zvw allowance). The total amount of Zvw allowance should be the same as the sum of the individual amounts of Zvw allowance.'
6721: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6722: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6723: END IF;
6741: , p_source_text => NULL
6742: , p_tax_unit_id => l_tax_unit_id
6743: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6744: , p_action_information4 => '0010 - '||fnd_message.get
6745: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6746: , p_action_information6 => '0010 - '||'Tag TotTeBet, (Total amount to be paid payroll period). The total amount should be the correct sum of the individual total amounts.'
6747: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6748: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6749: END IF;
6772: , p_source_text => NULL
6773: , p_tax_unit_id => l_tax_unit_id
6774: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6775: , p_action_information4 => '0011 - '||fnd_message.get
6776: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6777: , p_action_information6 => '0011 - '||'Tag TotGen, (General Total). The total amount should be the correct sum of the total amounts of the normal period and the correction periods.'
6778: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6779: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6780: END IF;
6799: , p_source_text => NULL
6800: , p_tax_unit_id => l_tax_unit_id
6801: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6802: , p_action_information4 => '0012 - '||fnd_message.get
6803: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6804: , p_action_information6 => '0012 - '||'Tag IngBijdrZvw, (Total of deducted contribution Zvw). The total amount of contribution Zvw should be the same as the sum of the individual amounts of contribution Zvw.'
6805: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6806: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6807: END IF;
6825: , p_source_text => NULL
6826: , p_tax_unit_id => l_tax_unit_id
6827: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6828: , p_action_information4 => '0016 - '||fnd_message.get
6829: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6830: , p_action_information6 => '0016 - '||'Tag PrWAOAof, (Total contribution WAO/WGA/IVA). The total amount of contributions should be the same as the sum of the individual amounts of contribution WAO/WGA/IVA.'
6831: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6832: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6833: END IF;
6859: , p_source_text => NULL
6860: , p_tax_unit_id => l_tax_unit_id
6861: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6862: , p_action_information4 => '0021 - '||fnd_message.get
6863: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6864: , p_action_information6 => '0021 - '||'Tag PrWAOAok, (Total contribution general WAO/WGA Differentiated). The total amount of contributions should be the same as the sum of the individual amounts of WAO/WGA Differentiated.'
6865: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6866: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6867: END IF;
6885: , p_source_text => NULL
6886: , p_tax_unit_id => l_tax_unit_id
6887: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6888: , p_action_information4 => '0018 - '||fnd_message.get
6889: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6890: , p_action_information6 => '0018 - '||'Tag PrWgf, (Total contribution waiting money fund). The total amount of contribution should be the same as the sum of the individual amounts of contribution waiting money fund.'
6891: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6892: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6893: END IF;
6910: , p_source_text => NULL
6911: , p_tax_unit_id => l_tax_unit_id
6912: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6913: , p_action_information4 => '0022 - '||fnd_message.get
6914: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6915: , p_action_information6 => '0022 - '||'The start and end dates of the correction period cannot be the same as the start and end dates of the normal period.'
6916: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6917: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6918: END LOOP;
6940: , p_source_text => NULL
6941: , p_tax_unit_id => l_tax_unit_id
6942: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6943: , p_action_information4 => '0025 - '||fnd_message.get
6944: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6945: , p_action_information6 => '0025 - '||'The total amount of "Contribution base WAO/IVA/WGA" (Tag PrLnWAOAof) has to be equal to or less than the total amount of "Contribution base general WAO/WGA Differentiated" (Tag PrLnWAOAok).'
6946: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6947: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6948: END IF;
6967: , p_source_text => NULL
6968: , p_tax_unit_id => l_tax_unit_id
6969: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6970: , p_action_information4 => '0029 - '||fnd_message.get
6971: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6972: , p_action_information6 => '0029 - '||'The total amount of "Contribution base WeWe (AWF)" (Tag PrLnAWF) has to be equal to or less than the sum of the total amounts of "Contribution base Waiting money fund" (Tag PrLnWgf).'
6973: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
6974: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
6975: END IF;
6994: , p_source_text => NULL
6995: , p_tax_unit_id => l_tax_unit_id
6996: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
6997: , p_action_information4 => '0026 - '||fnd_message.get
6998: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
6999: , p_action_information6 => '0026 - '||'When the amount of "Total contribution WAO/WGA/IVA" (Tag PrWAOAof) is greater than zero, the amount of "Contribution base WAO/IVA/WGA" (Tag PrLnWAOAof) cannot be equal to zero.'
7000: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
7001: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
7002: END IF;
7027: , p_source_text => NULL
7028: , p_tax_unit_id => l_tax_unit_id
7029: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7030: , p_action_information4 => '0028 - '||fnd_message.get
7031: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
7032: , p_action_information6 => '0028 - '||'When the amount of "Total contribution general WAO/WGA Differentiated" (Tag PrWAOAok) is greater than zero,'||
7033: ' the amount of "Contribution base general WAO/WGA Differentiated" (Tag PrLnWAOAok) cannot be equal to zero.'
7034: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
7035: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
7053: , p_source_text => NULL
7054: , p_tax_unit_id => l_tax_unit_id
7055: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7056: , p_action_information4 => '0030 - '||fnd_message.get
7057: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
7058: , p_action_information6 => '0030 - '||'When the amount of "Total Contribution WeWe (AWF)" (Tag PrAWF) is greater than zero, the amount of "Contribution base WeWe (AWF)" (Tag PrLnAWF) cannot be equal to zero.'
7059: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
7060: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
7061: END IF;
7078: , p_source_text => NULL
7079: , p_tax_unit_id => l_tax_unit_id
7080: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7081: , p_action_information4 => '0031 - '||fnd_message.get
7082: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
7083: , p_action_information6 => '0031 - '||'When the amount of "Total contribution UFO" (Tag PrUFO) is greater than zero, the amount of "Contribution base UFO" (Tag PrLnUFO) cannot be equal to zero.'
7084: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
7085: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
7086: END IF;
7103: , p_source_text => NULL
7104: , p_tax_unit_id => l_tax_unit_id
7105: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7106: , p_action_information4 => '0035 - '||fnd_message.get
7107: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
7108: , p_action_information6 => '0035 - '||'When the amount of "Total contribution waiting money fund" (Tag PrWgf) is greater than zero, the amount of "Contribution base Waiting money fund" (Tag PrLnWgf) cannot be equal to zero.'
7109: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
7110: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
7111: END IF;
7422: SELECT 1
7423: FROM per_assignment_extra_info
7424: WHERE assignment_id = asl.assignment_id
7425: AND aei_information_category = 'NL_WR_PREV_CORR'
7426: AND ptp.start_date BETWEEN fnd_date.canonical_to_date(aei_information1)
7427: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
7428: )
7429: --10100628
7430: /* ( SELECT 1
7423: FROM per_assignment_extra_info
7424: WHERE assignment_id = asl.assignment_id
7425: AND aei_information_category = 'NL_WR_PREV_CORR'
7426: AND ptp.start_date BETWEEN fnd_date.canonical_to_date(aei_information1)
7427: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
7428: )
7429: --10100628
7430: /* ( SELECT 1
7431: FROM pay_run_results rr1 -- Retro element
7783: FROM per_addresses addr
7784: WHERE addr.person_id = c_person_id
7785: AND addr.primary_flag = 'Y'
7786: AND c_effective_date BETWEEN addr.date_from AND
7787: nvl(addr.date_to,fnd_date.canonical_to_date('4712/12/31'))
7788: ORDER BY 1 DESC;
7789: --
7790: l_addr csr_get_emp_address%ROWTYPE;
7791: l_ovn pay_action_information.object_version_number%type;
7830: , p_source_text => NULL
7831: , p_tax_unit_id => p_tax_unit_id
7832: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7833: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_STREET')
7834: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)
7835: , p_action_information6 => 'Street name missing in the address'
7836: , p_action_information7 => p_name
7837: , p_action_information8 => p_assignment_number);
7838: END IF;
7850: , p_source_text => NULL
7851: , p_tax_unit_id => p_tax_unit_id
7852: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7853: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_CITY')
7854: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)
7855: , p_action_information6 => 'City name missing in the address'
7856: , p_action_information7 => p_name
7857: , p_action_information8 => p_assignment_number);
7858: END IF;
7870: , p_source_text => NULL
7871: , p_tax_unit_id => p_tax_unit_id
7872: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7873: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_PO_CODE')
7874: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)
7875: , p_action_information6 => 'Postal Code missing in address'
7876: , p_action_information7 => p_name
7877: , p_action_information8 => p_assignment_number);
7878: END IF;
7890: , p_source_text => NULL
7891: , p_tax_unit_id => p_tax_unit_id
7892: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7893: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_COUNTRY')
7894: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)
7895: , p_action_information6 => 'Country is missing in address'
7896: , p_action_information7 => p_name
7897: , p_action_information8 => p_assignment_number);
7898: END IF;
7919: , p_source_text => NULL
7920: , p_tax_unit_id => p_tax_unit_id
7921: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7922: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_PO_FORMAT')
7923: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)
7924: , p_action_information6 => 'Postal Code is not in the format 9999XX'
7925: , p_action_information7 => p_name
7926: , p_action_information8 => p_assignment_number);
7927: END IF;
8108: , p_source_text => NULL
8109: , p_tax_unit_id => p_tax_unit_id
8110: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
8111: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_ADDRESS')
8112: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)
8113: , p_action_information6 => 'Address details are null'
8114: , p_action_information7 => p_name
8115: , p_action_information8 => p_assignment_number);
8116: END IF;
8134: CURSOR csr_get_emp_risk_grp(c_assignment_id NUMBER) IS
8135: SELECT DISTINCT hoi.organization_id
8136: ,hoi.org_information5 sector
8137: ,hoi.org_information6 risk_group
8138: ,fnd_date.canonical_to_date(aei.AEI_INFORMATION1) start_date
8139: ,fnd_date.canonical_to_date(aei.AEI_INFORMATION2) end_date
8140: FROM hr_organization_information hoi
8141: ,per_assignment_extra_info aei
8142: WHERE hoi.organization_id = nvl( aei.aei_information8,HR_NL_ORG_INFO.Get_SI_Provider_Info(aei.assignment_id,aei.AEI_INFORMATION3))
8135: SELECT DISTINCT hoi.organization_id
8136: ,hoi.org_information5 sector
8137: ,hoi.org_information6 risk_group
8138: ,fnd_date.canonical_to_date(aei.AEI_INFORMATION1) start_date
8139: ,fnd_date.canonical_to_date(aei.AEI_INFORMATION2) end_date
8140: FROM hr_organization_information hoi
8141: ,per_assignment_extra_info aei
8142: WHERE hoi.organization_id = nvl( aei.aei_information8,HR_NL_ORG_INFO.Get_SI_Provider_Info(aei.assignment_id,aei.AEI_INFORMATION3))
8143: AND aei.assignment_id = c_assignment_id
8214: ,p_tax_unit_id => p_tax_unit_id
8215: , p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
8216: , p_action_information1 => 'SECTOR_RISK_GROUP'
8217: , p_action_information2 => p_mas_act_info_id
8218: , p_action_information5 => fnd_date.date_to_canonical(l_start_date)
8219: , p_action_information6 => fnd_date.date_to_canonical(LEAST(l_end_date,NVL(l_rec.end_date,l_end_date)))
8220: , p_action_information7 => l_rec.sector
8221: , p_action_information8 => l_rec.risk_group
8222: , p_action_information9 => NULL
8215: , p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
8216: , p_action_information1 => 'SECTOR_RISK_GROUP'
8217: , p_action_information2 => p_mas_act_info_id
8218: , p_action_information5 => fnd_date.date_to_canonical(l_start_date)
8219: , p_action_information6 => fnd_date.date_to_canonical(LEAST(l_end_date,NVL(l_rec.end_date,l_end_date)))
8220: , p_action_information7 => l_rec.sector
8221: , p_action_information8 => l_rec.risk_group
8222: , p_action_information9 => NULL
8223: , p_action_information10 => NULL);
8248: ,p_tax_unit_id => p_tax_unit_id
8249: , p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
8250: , p_action_information1 => 'SECTOR_RISK_GROUP'
8251: , p_action_information2 => p_mas_act_info_id
8252: , p_action_information5 => fnd_date.date_to_canonical(p_start_date)
8253: , p_action_information6 => fnd_date.date_to_canonical(p_end_date)
8254: , p_action_information7 => l_sector_rec.sector
8255: , p_action_information8 => l_sector_rec.risk_group
8256: , p_action_information9 => NULL
8249: , p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
8250: , p_action_information1 => 'SECTOR_RISK_GROUP'
8251: , p_action_information2 => p_mas_act_info_id
8252: , p_action_information5 => fnd_date.date_to_canonical(p_start_date)
8253: , p_action_information6 => fnd_date.date_to_canonical(p_end_date)
8254: , p_action_information7 => l_sector_rec.sector
8255: , p_action_information8 => l_sector_rec.risk_group
8256: , p_action_information9 => NULL
8257: , p_action_information10 => NULL);
8498: --10100628
8499: CURSOR C_PREV_CORR_DATA(p_asg_id NUMBER, p_date DATE)
8500: IS
8501: SELECT
8502: fnd_date.canonical_to_date(AEI_INFORMATION1) start_date
8503: ,fnd_date.canonical_to_date(AEI_INFORMATION2) end_date
8504: ,fnd_number.canonical_to_number(AEI_INFORMATION3) LnLbPh
8505: ,fnd_number.canonical_to_number(AEI_INFORMATION4) LnSV
8506: ,fnd_number.canonical_to_number(AEI_INFORMATION5) LnTabBB
8499: CURSOR C_PREV_CORR_DATA(p_asg_id NUMBER, p_date DATE)
8500: IS
8501: SELECT
8502: fnd_date.canonical_to_date(AEI_INFORMATION1) start_date
8503: ,fnd_date.canonical_to_date(AEI_INFORMATION2) end_date
8504: ,fnd_number.canonical_to_number(AEI_INFORMATION3) LnLbPh
8505: ,fnd_number.canonical_to_number(AEI_INFORMATION4) LnSV
8506: ,fnd_number.canonical_to_number(AEI_INFORMATION5) LnTabBB
8507: ,fnd_number.canonical_to_number(AEI_INFORMATION6) VakBsl
8534: --10100628
8535: FROM per_assignment_extra_info
8536: WHERE assignment_id = p_asg_id
8537: AND aei_information_category = 'NL_WR_PREV_CORR'
8538: AND p_date BETWEEN fnd_date.canonical_to_date(aei_information1)
8539: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
8540: ORDER BY 1 ASC;
8541:
8542: l_co_start_date DATE;
8535: FROM per_assignment_extra_info
8536: WHERE assignment_id = p_asg_id
8537: AND aei_information_category = 'NL_WR_PREV_CORR'
8538: AND p_date BETWEEN fnd_date.canonical_to_date(aei_information1)
8539: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
8540: ORDER BY 1 ASC;
8541:
8542: l_co_start_date DATE;
8543: l_co_end_date DATE;
8590: , p_source_text => NULL
8591: , p_tax_unit_id => p_tax_unit_id
8592: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
8593: , p_action_information4 => fnd_message.get_string('PER','HR_NL_UFO_AND_AWF')
8594: , p_action_information5 => fnd_date.date_to_canonical(p_date)
8595: , p_action_information6 => 'UFO Contributions and AWF Contributions exist'
8596: , p_action_information7 => p_name
8597: , p_action_information8 => p_assignment_id);
8598: END IF;
8610: , p_source_text => NULL
8611: , p_tax_unit_id => p_tax_unit_id
8612: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
8613: , p_action_information4 => fnd_message.get_string('PER','HR_NL_ZERO_WEWA')
8614: , p_action_information5 => fnd_date.date_to_canonical(p_date)
8615: , p_action_information6 => 'Waiting Money Fund contribution is zero'
8616: , p_action_information7 => p_name
8617: , p_action_information8 => p_assignment_id);
8618: END IF;
8630: , p_source_text => NULL
8631: , p_tax_unit_id => p_tax_unit_id
8632: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
8633: , p_action_information4 => fnd_message.get_string('PER','HR_NL_ZERO_WEWE')
8634: , p_action_information5 => fnd_date.date_to_canonical(p_date)
8635: , p_action_information6 => 'AWF contribution is zero'
8636: , p_action_information7 => p_name
8637: , p_action_information8 => p_assignment_id);
8638: END IF;
8988: /*LC 2013*/ ,p_temp_tax_discount IN OUT NOCOPY PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION1%type
8989: ,p_chk IN OUT NOCOPY VARCHAR2) IS
8990: --
8991: CURSOR csr_get_ass_extra_info(c_a_extra_info_id NUMBER) IS
8992: SELECT fnd_date.canonical_to_date(aei_information1) start_date
8993: ,fnd_date.canonical_to_date(aei_information2) end_date
8994: ,aei_information3 info1
8995: ,aei_information15 info2
8996: ,aei_information_category
8989: ,p_chk IN OUT NOCOPY VARCHAR2) IS
8990: --
8991: CURSOR csr_get_ass_extra_info(c_a_extra_info_id NUMBER) IS
8992: SELECT fnd_date.canonical_to_date(aei_information1) start_date
8993: ,fnd_date.canonical_to_date(aei_information2) end_date
8994: ,aei_information3 info1
8995: ,aei_information15 info2
8996: ,aei_information_category
8997: FROM per_assignment_extra_info
8999: AND aei_information_category IN ('NL_LBR','NL_INF','NL_TML','NL_LHI','NL_SII',
9000: 'NL_MEI','NL_WMR','NL_TTD'); /*LC 2010 and LC 2013*/
9001: --
9002: CURSOR csr_get_all_ass_extra_info(c_assignment_id NUMBER) IS
9003: SELECT fnd_date.canonical_to_date(aei_information1) start_date
9004: ,fnd_date.canonical_to_date(aei_information2) end_date
9005: ,aei_information3 info1
9006: ,aei_information15 info2
9007: ,aei_information_category
9000: 'NL_MEI','NL_WMR','NL_TTD'); /*LC 2010 and LC 2013*/
9001: --
9002: CURSOR csr_get_all_ass_extra_info(c_assignment_id NUMBER) IS
9003: SELECT fnd_date.canonical_to_date(aei_information1) start_date
9004: ,fnd_date.canonical_to_date(aei_information2) end_date
9005: ,aei_information3 info1
9006: ,aei_information15 info2
9007: ,aei_information_category
9008: FROM per_assignment_extra_info
9009: WHERE assignment_id = c_assignment_id
9010: AND aei_information_category IN ('NL_LBR','NL_INF','NL_TML','NL_LHI','NL_SII',
9011: 'NL_MEI','NL_WMR','NL_TTD') /*LC 2010 and LC 2013*/
9012: --9257875
9013: /* AND p_end_date BETWEEN fnd_date.canonical_to_date(aei_information1)
9014: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy')) */
9015: --9257875
9016:
9017: --10377795 START. Commented above code.
9010: AND aei_information_category IN ('NL_LBR','NL_INF','NL_TML','NL_LHI','NL_SII',
9011: 'NL_MEI','NL_WMR','NL_TTD') /*LC 2010 and LC 2013*/
9012: --9257875
9013: /* AND p_end_date BETWEEN fnd_date.canonical_to_date(aei_information1)
9014: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy')) */
9015: --9257875
9016:
9017: --10377795 START. Commented above code.
9018: AND fnd_date.date_to_canonical (p_end_date) BETWEEN aei_information1
9014: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy')) */
9015: --9257875
9016:
9017: --10377795 START. Commented above code.
9018: AND fnd_date.date_to_canonical (p_end_date) BETWEEN aei_information1
9019: AND nvl(aei_information2,fnd_date.date_to_canonical(to_date('31-12-4712','dd-mm-yyyy')))
9020: --10377795 END
9021: ORDER BY 1 DESC;
9022: --
9015: --9257875
9016:
9017: --10377795 START. Commented above code.
9018: AND fnd_date.date_to_canonical (p_end_date) BETWEEN aei_information1
9019: AND nvl(aei_information2,fnd_date.date_to_canonical(to_date('31-12-4712','dd-mm-yyyy')))
9020: --10377795 END
9021: ORDER BY 1 DESC;
9022: --
9023: --rsahai--Labour Handicapped discount - New Code 7 Changes : 2009 changes - START
9025: SELECT aei_information3 info1
9026: FROM per_assignment_extra_info
9027: WHERE assignment_id = c_assignment_id
9028: AND aei_information_category = 'NL_LHI'
9029: AND p_end_date BETWEEN fnd_date.canonical_to_date(aei_information1)
9030: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
9031: AND aei_information3 not in ('0','1','2','3','4');
9032:
9033: CURSOR csr_get_ass_bdate(c_assignment_id NUMBER) IS
9026: FROM per_assignment_extra_info
9027: WHERE assignment_id = c_assignment_id
9028: AND aei_information_category = 'NL_LHI'
9029: AND p_end_date BETWEEN fnd_date.canonical_to_date(aei_information1)
9030: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
9031: AND aei_information3 not in ('0','1','2','3','4');
9032:
9033: CURSOR csr_get_ass_bdate(c_assignment_id NUMBER) IS
9034: select papf.date_of_birth, TRUNC(MONTHS_BETWEEN(p_end_date,papf.date_of_birth)/12) Age
9106: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ Inside get_assignment_extra_info'||p_assignment_id);
9107: IF p_surrogate_key IS NULL THEN
9108: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_surrogate_key is null');
9109: FOR i in csr_get_all_ass_extra_info(p_assignment_id) LOOP
9110: IF p_eff_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31'))
9111: OR p_end_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31')) --9257875
9112: OR (i.start_date <= p_end_date AND NVL(i.end_date,p_end_date) >= p_start_date AND p_eff_date < fnd_date.canonical_to_date('2006/01/01')) THEN -- SR 5531106.992
9113: --
9114: IF i.aei_information_category = 'NL_LBR' AND l_labour_rel_code IS NULL THEN
9107: IF p_surrogate_key IS NULL THEN
9108: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_surrogate_key is null');
9109: FOR i in csr_get_all_ass_extra_info(p_assignment_id) LOOP
9110: IF p_eff_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31'))
9111: OR p_end_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31')) --9257875
9112: OR (i.start_date <= p_end_date AND NVL(i.end_date,p_end_date) >= p_start_date AND p_eff_date < fnd_date.canonical_to_date('2006/01/01')) THEN -- SR 5531106.992
9113: --
9114: IF i.aei_information_category = 'NL_LBR' AND l_labour_rel_code IS NULL THEN
9115: l_labour_rel_code := i.info1;
9108: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_surrogate_key is null');
9109: FOR i in csr_get_all_ass_extra_info(p_assignment_id) LOOP
9110: IF p_eff_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31'))
9111: OR p_end_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31')) --9257875
9112: OR (i.start_date <= p_end_date AND NVL(i.end_date,p_end_date) >= p_start_date AND p_eff_date < fnd_date.canonical_to_date('2006/01/01')) THEN -- SR 5531106.992
9113: --
9114: IF i.aei_information_category = 'NL_LBR' AND l_labour_rel_code IS NULL THEN
9115: l_labour_rel_code := i.info1;
9116: ELSIF i.aei_information_category = 'NL_INF' AND l_ins_duty_code IS NULL THEN
9359: FROM pay_dated_tables
9360: WHERE TABLE_NAME = c_table_name;
9361: --
9362: CURSOR csr_get_eit_effective_date(c_a_extra_info_id NUMBER) IS
9363: SELECT fnd_date.canonical_to_date(aei_information1) start_date
9364: FROM per_assignment_extra_info
9365: WHERE assignment_extra_info_id = c_a_extra_info_id
9366: AND aei_information_category IN ('NL_LBR','NL_INF','NL_TML','NL_LHI','NL_SII'--);
9367: ,'NL_MEI','NL_WMR'); /*LC 2010*/
10146: SELECT 1
10147: FROM per_assignment_extra_info
10148: WHERE assignment_id = paa.assignment_id
10149: AND aei_information_category = 'NL_WR_PREV_CORR'
10150: AND ptp.start_date BETWEEN fnd_date.canonical_to_date(aei_information1)
10151: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
10152: )
10153: order by ptp.start_date Asc;
10154: --10100628
10147: FROM per_assignment_extra_info
10148: WHERE assignment_id = paa.assignment_id
10149: AND aei_information_category = 'NL_WR_PREV_CORR'
10150: AND ptp.start_date BETWEEN fnd_date.canonical_to_date(aei_information1)
10151: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
10152: )
10153: order by ptp.start_date Asc;
10154: --10100628
10155:
10234: l_retro_done := 'N';
10235: IF l_master_assignment_action_id IS NOT NULL THEN
10236: FOR l_element_rec in csr_get_element_details(l_master_assignment_action_id,p_chk_end_date) LOOP
10237: l_retro_type := 'HR';
10238: ele_end_date := fnd_date.canonical_to_date(l_element_rec.period);
10239: --
10240: IF l_element_rec.Retro_type = 'WITHDRAWAL' AND l_element_rec.period IS NOT NULL THEN
10241: OPEN csr_get_period(l_emp_rec.payroll_id, ele_end_date);
10242: FETCH csr_get_period INTO l_period_start_date,l_period_end_date;
10651: ELSE
10652: l_type := 'CORRECT';
10653: END IF;
10654: --Fnd_file.put_line(FND_FILE.LOG,' l_type :'||l_type);
10655: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));
10656: --
10657: /* 8328995 */
10658: l_numiv_override:=null;
10659: OPEN csr_numiv_override(p_assignment_id);
10671: ,p_source_text => NULL
10672: ,p_tax_unit_id => l_tax_unit_id
10673: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
10674: ,p_action_information1 => 'WITHDRAWAL'
10675: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_rec_changes(i).period_start_date,trunc(l_rec_changes(i).period_end_date,'Y')))
10676: ,p_action_information3 => fnd_date.date_to_canonical(l_rec_changes(i).period_end_date)
10677: ,p_action_information4 => l_emp_rec.assignment_number
10678: ,p_action_information5 => l_emp_rec.employee_number
10679: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
10672: ,p_tax_unit_id => l_tax_unit_id
10673: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
10674: ,p_action_information1 => 'WITHDRAWAL'
10675: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_rec_changes(i).period_start_date,trunc(l_rec_changes(i).period_end_date,'Y')))
10676: ,p_action_information3 => fnd_date.date_to_canonical(l_rec_changes(i).period_end_date)
10677: ,p_action_information4 => l_emp_rec.assignment_number
10678: ,p_action_information5 => l_emp_rec.employee_number
10679: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
10680: ,p_action_information8 => l_emp_rec.sofi_number
10681: ,p_action_information7 => l_emp_rec.person_id
10682: ,p_action_information9 => l_emp_rec.Initials
10683: ,p_action_information10 => l_emp_rec.prefix
10684: ,p_action_information11 => l_emp_rec.last_name
10685: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
10686: ,p_action_information13 => l_nationality
10687: ,p_action_information14 => l_emp_rec.gender
10688: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
10689: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
10684: ,p_action_information11 => l_emp_rec.last_name
10685: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
10686: ,p_action_information13 => l_nationality
10687: ,p_action_information14 => l_emp_rec.gender
10688: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
10689: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
10690: ,p_action_information17 => l_rec_changes(i).retro
10691: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
10692:
10685: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
10686: ,p_action_information13 => l_nationality
10687: ,p_action_information14 => l_emp_rec.gender
10688: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
10689: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
10690: ,p_action_information17 => l_rec_changes(i).retro
10691: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
10692:
10693: /* 9764359 */
10712: , p_source_text => NULL
10713: , p_tax_unit_id => l_tax_unit_id
10714: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
10715: , p_action_information4 => fnd_message.get_string('PER','HR_373547_NUMIV_OVERRIDE')
10716: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
10717: , p_action_information6 => 'Num IV override Already Used'
10718: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
10719: , p_action_information8 => l_emp_rec.assignment_number);
10720: END IF;
10762: ,p_source_text => NULL
10763: ,p_tax_unit_id => l_tax_unit_id
10764: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
10765: ,p_action_information1 => 'INITIAL'
10766: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_rec_changes(i).period_start_date,trunc(l_rec_changes(i).period_end_date,'Y')))
10767: ,p_action_information3 => fnd_date.date_to_canonical(l_rec_changes(i).period_end_date)
10768: ,p_action_information4 => l_emp_rec.assignment_number
10769: ,p_action_information5 => l_emp_rec.employee_number
10770: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
10763: ,p_tax_unit_id => l_tax_unit_id
10764: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
10765: ,p_action_information1 => 'INITIAL'
10766: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_rec_changes(i).period_start_date,trunc(l_rec_changes(i).period_end_date,'Y')))
10767: ,p_action_information3 => fnd_date.date_to_canonical(l_rec_changes(i).period_end_date)
10768: ,p_action_information4 => l_emp_rec.assignment_number
10769: ,p_action_information5 => l_emp_rec.employee_number
10770: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
10771: ,p_action_information8 => l_emp_rec.sofi_number
10772: ,p_action_information7 => l_emp_rec.person_id
10773: ,p_action_information9 => l_emp_rec.Initials
10774: ,p_action_information10 => l_emp_rec.prefix
10775: ,p_action_information11 => l_emp_rec.last_name
10776: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
10777: ,p_action_information13 => l_nationality
10778: ,p_action_information14 => l_emp_rec.gender
10779: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)
10780: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
10775: ,p_action_information11 => l_emp_rec.last_name
10776: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
10777: ,p_action_information13 => l_nationality
10778: ,p_action_information14 => l_emp_rec.gender
10779: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)
10780: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
10781: ,p_action_information17 => l_rec_changes(i).retro
10782: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
10783:
10776: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
10777: ,p_action_information13 => l_nationality
10778: ,p_action_information14 => l_emp_rec.gender
10779: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)
10780: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
10781: ,p_action_information17 => l_rec_changes(i).retro
10782: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
10783:
10784: END IF;
10795: ,p_source_text => NULL
10796: ,p_tax_unit_id => l_tax_unit_id
10797: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
10798: ,p_action_information1 => 'INITIAL'
10799: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_rec_changes(i).period_start_date,trunc(l_rec_changes(i).period_end_date,'Y')))
10800: ,p_action_information3 => fnd_date.date_to_canonical(l_rec_changes(i).period_end_date)
10801: ,p_action_information4 => l_emp_rec.assignment_number
10802: ,p_action_information5 => l_emp_rec.employee_number
10803: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
10796: ,p_tax_unit_id => l_tax_unit_id
10797: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
10798: ,p_action_information1 => 'INITIAL'
10799: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_rec_changes(i).period_start_date,trunc(l_rec_changes(i).period_end_date,'Y')))
10800: ,p_action_information3 => fnd_date.date_to_canonical(l_rec_changes(i).period_end_date)
10801: ,p_action_information4 => l_emp_rec.assignment_number
10802: ,p_action_information5 => l_emp_rec.employee_number
10803: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
10804: ,p_action_information8 => l_emp_rec.sofi_number
10805: ,p_action_information7 => l_emp_rec.person_id
10806: ,p_action_information9 => l_emp_rec.Initials
10807: ,p_action_information10 => l_emp_rec.prefix
10808: ,p_action_information11 => l_emp_rec.last_name
10809: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
10810: ,p_action_information13 => l_nationality
10811: ,p_action_information14 => l_emp_rec.gender
10812: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)
10813: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
10808: ,p_action_information11 => l_emp_rec.last_name
10809: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
10810: ,p_action_information13 => l_nationality
10811: ,p_action_information14 => l_emp_rec.gender
10812: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)
10813: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
10814: ,p_action_information17 => l_rec_changes(i).retro
10815: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
10816:
10809: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
10810: ,p_action_information13 => l_nationality
10811: ,p_action_information14 => l_emp_rec.gender
10812: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)
10813: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
10814: ,p_action_information17 => l_rec_changes(i).retro
10815: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
10816:
10817: END IF; --10100628
10837: , p_source_text => NULL
10838: , p_tax_unit_id => l_tax_unit_id
10839: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
10840: , p_action_information4 => fnd_message.get_string('PER','HR_373547_NUMIV_OVERRIDE')
10841: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
10842: , p_action_information6 => 'Num IV override Already Used'
10843: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
10844: , p_action_information8 => l_emp_rec.assignment_number);
10845: END IF;
10986: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
10987: , p_tax_unit_id => l_tax_unit_id
10988: , p_action_information1 => 'INITIAL'
10989: , p_action_information2 => l_master_action_info_id
10990: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(l_rec_changes(i).period_start_date,l_ass_start_date),trunc(l_rec_changes(i).period_end_date,'Y')))
10991: , p_action_information6 => l_income_code
10992: , p_action_information7 => l_labour_rel_code
10993: , p_action_information8 => l_ins_duty_code
10994: , p_action_information9 => l_assignment_catg
11027: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
11028: , p_tax_unit_id => l_tax_unit_id
11029: , p_action_information1 => 'INITIAL'
11030: , p_action_information2 => l_master_action_info_id
11031: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(l_rec_changes(i).period_start_date,l_ass_start_date),trunc(l_rec_changes(i).period_end_date,'Y')))
11032: , p_action_information6 => l_income_code
11033: , p_action_information7 => l_labour_rel_code
11034: , p_action_information8 => l_ins_duty_code
11035: , p_action_information9 => l_assignment_catg
11446: l_type := 'CORRECTION';
11447: END IF;
11448:
11449: --Fnd_file.put_line(FND_FILE.LOG,' l_type :'||l_type);
11450: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));
11451: --
11452: l_emp_end_date := NULL;
11453: IF l_ass_end_date <= l_period_end_date THEN
11454: l_emp_end_date := l_ass_end_date;
11492: ,p_source_text => NULL
11493: ,p_tax_unit_id => l_tax_unit_id
11494: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
11495: ,p_action_information1 => l_type
11496: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_period_start_date,trunc(l_period_end_date,'Y')))
11497: ,p_action_information3 => fnd_date.date_to_canonical(l_period_end_date)
11498: ,p_action_information4 => l_emp_rec.assignment_number
11499: ,p_action_information5 => l_emp_rec.employee_number
11500: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
11493: ,p_tax_unit_id => l_tax_unit_id
11494: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
11495: ,p_action_information1 => l_type
11496: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_period_start_date,trunc(l_period_end_date,'Y')))
11497: ,p_action_information3 => fnd_date.date_to_canonical(l_period_end_date)
11498: ,p_action_information4 => l_emp_rec.assignment_number
11499: ,p_action_information5 => l_emp_rec.employee_number
11500: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
11501: ,p_action_information8 => l_emp_rec.sofi_number
11502: ,p_action_information7 => l_emp_rec.person_id
11503: ,p_action_information9 => l_emp_rec.Initials
11504: ,p_action_information10 => l_emp_rec.prefix
11505: ,p_action_information11 => l_emp_rec.last_name
11506: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
11507: ,p_action_information13 => l_nationality
11508: ,p_action_information14 => l_emp_rec.gender
11509: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
11510: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
11505: ,p_action_information11 => l_emp_rec.last_name
11506: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
11507: ,p_action_information13 => l_nationality
11508: ,p_action_information14 => l_emp_rec.gender
11509: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
11510: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
11511: ,p_action_information17 => l_rec_changes(i).retro
11512: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
11513: END IF;
11506: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
11507: ,p_action_information13 => l_nationality
11508: ,p_action_information14 => l_emp_rec.gender
11509: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
11510: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
11511: ,p_action_information17 => l_rec_changes(i).retro
11512: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
11513: END IF;
11514: ELSE
11523: ,p_source_text => NULL
11524: ,p_tax_unit_id => l_tax_unit_id
11525: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
11526: ,p_action_information1 => 'CORRECT' --l_type --10100628
11527: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_period_start_date,trunc(l_period_end_date,'Y')))
11528: ,p_action_information3 => fnd_date.date_to_canonical(l_period_end_date)
11529: ,p_action_information4 => l_emp_rec.assignment_number
11530: ,p_action_information5 => l_emp_rec.employee_number
11531: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
11524: ,p_tax_unit_id => l_tax_unit_id
11525: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
11526: ,p_action_information1 => 'CORRECT' --l_type --10100628
11527: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_period_start_date,trunc(l_period_end_date,'Y')))
11528: ,p_action_information3 => fnd_date.date_to_canonical(l_period_end_date)
11529: ,p_action_information4 => l_emp_rec.assignment_number
11530: ,p_action_information5 => l_emp_rec.employee_number
11531: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
11532: ,p_action_information8 => l_emp_rec.sofi_number
11533: ,p_action_information7 => l_emp_rec.person_id
11534: ,p_action_information9 => l_emp_rec.Initials
11535: ,p_action_information10 => l_emp_rec.prefix
11536: ,p_action_information11 => l_emp_rec.last_name
11537: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
11538: ,p_action_information13 => l_nationality
11539: ,p_action_information14 => l_emp_rec.gender
11540: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
11541: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
11536: ,p_action_information11 => l_emp_rec.last_name
11537: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
11538: ,p_action_information13 => l_nationality
11539: ,p_action_information14 => l_emp_rec.gender
11540: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
11541: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
11542: ,p_action_information17 => l_rec_changes(i).retro
11543: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
11544: END IF;
11537: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
11538: ,p_action_information13 => l_nationality
11539: ,p_action_information14 => l_emp_rec.gender
11540: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
11541: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
11542: ,p_action_information17 => l_rec_changes(i).retro
11543: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
11544: END IF;
11545: ELSE
11555: ,p_source_text => NULL
11556: ,p_tax_unit_id => l_tax_unit_id
11557: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
11558: ,p_action_information1 => l_type
11559: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_period_start_date,trunc(l_period_end_date,'Y')))
11560: ,p_action_information3 => fnd_date.date_to_canonical(l_period_end_date)
11561: ,p_action_information4 => l_emp_rec.assignment_number
11562: ,p_action_information5 => l_emp_rec.employee_number
11563: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
11556: ,p_tax_unit_id => l_tax_unit_id
11557: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
11558: ,p_action_information1 => l_type
11559: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_period_start_date,trunc(l_period_end_date,'Y')))
11560: ,p_action_information3 => fnd_date.date_to_canonical(l_period_end_date)
11561: ,p_action_information4 => l_emp_rec.assignment_number
11562: ,p_action_information5 => l_emp_rec.employee_number
11563: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
11564: ,p_action_information8 => l_emp_rec.sofi_number
11565: ,p_action_information7 => l_emp_rec.person_id
11566: ,p_action_information9 => l_emp_rec.Initials
11567: ,p_action_information10 => l_emp_rec.prefix
11568: ,p_action_information11 => l_emp_rec.last_name
11569: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
11570: ,p_action_information13 => l_nationality
11571: ,p_action_information14 => l_emp_rec.gender
11572: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
11573: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
11568: ,p_action_information11 => l_emp_rec.last_name
11569: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
11570: ,p_action_information13 => l_nationality
11571: ,p_action_information14 => l_emp_rec.gender
11572: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
11573: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
11574: ,p_action_information17 => l_rec_changes(i).retro
11575: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
11576:
11569: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
11570: ,p_action_information13 => l_nationality
11571: ,p_action_information14 => l_emp_rec.gender
11572: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
11573: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
11574: ,p_action_information17 => l_rec_changes(i).retro
11575: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
11576:
11577: END IF; --10100628
11599: , p_source_text => NULL
11600: , p_tax_unit_id => l_tax_unit_id
11601: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
11602: , p_action_information4 => fnd_message.get_string('PER','HR_373547_NUMIV_OVERRIDE')
11603: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
11604: , p_action_information6 => 'Num IV override Already Used'
11605: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
11606: , p_action_information8 => l_emp_rec.assignment_number);
11607: END IF;
11621: , p_source_text => NULL
11622: , p_tax_unit_id => l_tax_unit_id
11623: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
11624: , p_action_information4 => '0044/1044 - '||fnd_message.get_string('PER','HR_373537_NL_EMPNO_MANDATORY')
11625: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
11626: , p_action_information6 => '0044/1044 - '||'When the "BSN/Sofi-number" (Tag SofiNr) is not reported the record "Employee number" (Tag PersNr) is mandatory.'
11627: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
11628: , p_action_information8 => l_emp_rec.assignment_number);
11629: --
11652: , p_source_text => NULL
11653: , p_tax_unit_id => l_tax_unit_id
11654: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
11655: , p_action_information4 => '0046 - '||fnd_message.get
11656: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
11657: , p_action_information6 => '0046 - '||'When "BSN/Sofi-number" (Tag SofiNr) is reported and "Code wage tax table" (Tag LbTab) is not equal to 940 the "Last Name" (Tag SignNm) cannot be empty / is mandatory.'
11658: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
11659: , p_action_information8 => l_emp_rec.assignment_number);
11660: --
11678: , p_source_text => NULL
11679: , p_tax_unit_id => l_tax_unit_id
11680: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
11681: , p_action_information4 => '0047 - '||fnd_message.get
11682: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
11683: , p_action_information6 => '0047 - '||'When "BSN/Sofi-number" (Tag SofiNr) is reported and "Code wage tax table" (Tag LbTab) is not equal to 940 the "Date of birth" (Tag Gebdat) cannot be empty / is mandatory.'
11684: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
11685: , p_action_information8 => l_emp_rec.assignment_number);
11686: --
11704: , p_source_text => NULL
11705: , p_tax_unit_id => l_tax_unit_id
11706: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
11707: , p_action_information4 => '0048 - '||fnd_message.get
11708: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
11709: , p_action_information6 => '0048 - '||'When "BSN/Sofi-number" (Tag SofiNr) is reported and "Code wage tax table" (Tag LbTab) is not equal to 940 the "Nationality" (Tag Nat) cannot be empty / is mandatory.'
11710: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
11711: , p_action_information8 => l_emp_rec.assignment_number);
11712: --
11730: , p_source_text => NULL
11731: , p_tax_unit_id => l_tax_unit_id
11732: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
11733: , p_action_information4 => '0049 - '||fnd_message.get
11734: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
11735: , p_action_information6 => '0049 - '||'When "BSN/Sofi-number" (Tag SofiNr) is reported and "Code wage tax table" (Tag LbTab) is not equal to 940 the "Gender" (Tag Gesl) cannot be empty / is mandatory.'
11736: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
11737: , p_action_information8 => l_emp_rec.assignment_number);
11738: --
11742: --abraghun--7668628--Check0039
11743: IF l_ass_start_date > l_period_end_date THEN
11744: fnd_message.set_name('PER','HR_373543_NL_DATE_LTEQ');
11745: fnd_message.set_token('TAG1','DatAanv');
11746: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_ass_start_date));
11747: fnd_message.set_token('TAG2','DatEindTv');
11748: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(l_period_end_date));
11749: --
11750: pay_action_information_api.create_action_information
11744: fnd_message.set_name('PER','HR_373543_NL_DATE_LTEQ');
11745: fnd_message.set_token('TAG1','DatAanv');
11746: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_ass_start_date));
11747: fnd_message.set_token('TAG2','DatEindTv');
11748: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(l_period_end_date));
11749: --
11750: pay_action_information_api.create_action_information
11751: (
11752: p_action_information_id => l_action_info_id
11759: , p_source_text => NULL
11760: , p_tax_unit_id => l_tax_unit_id
11761: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
11762: , p_action_information4 => '0039 - '||fnd_message.get
11763: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
11764: , p_action_information6 => '0039 - '||'The "Start date income relation" (Tag DatAanv) has to be lower than or equal to the "End date period" (Tag DatEindTv) within one report or one correction report.'
11765: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
11766: , p_action_information8 => l_emp_rec.assignment_number);
11767: --
11781:
11782: --fnd_message.set_name('PER','HR_373544_NL_DATE_GTEQ');
11783: fnd_message.set_name('PER','HR_373543_NL_DATE_LTEQ');
11784: fnd_message.set_token('TAG1','DatEind');
11785: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_emp_end_date));
11786: --fnd_message.set_token('TAG2','DatAanvTv');
11787: --fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(GREATEST(l_period_start_date,trunc(l_period_end_date,'Y'))));
11788: fnd_message.set_token('TAG2','DatEindTv');
11789: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(l_period_end_date));
11783: fnd_message.set_name('PER','HR_373543_NL_DATE_LTEQ');
11784: fnd_message.set_token('TAG1','DatEind');
11785: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_emp_end_date));
11786: --fnd_message.set_token('TAG2','DatAanvTv');
11787: --fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(GREATEST(l_period_start_date,trunc(l_period_end_date,'Y'))));
11788: fnd_message.set_token('TAG2','DatEindTv');
11789: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(l_period_end_date));
11790:
11791:
11785: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_emp_end_date));
11786: --fnd_message.set_token('TAG2','DatAanvTv');
11787: --fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(GREATEST(l_period_start_date,trunc(l_period_end_date,'Y'))));
11788: fnd_message.set_token('TAG2','DatEindTv');
11789: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(l_period_end_date));
11790:
11791:
11792: pay_action_information_api.create_action_information
11793: (
11801: , p_source_text => NULL
11802: , p_tax_unit_id => l_tax_unit_id
11803: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
11804: , p_action_information4 => '0040 - '||fnd_message.get
11805: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
11806: --, p_action_information6 => '0040 - '||'The "End date income relation" (Tag DatEind) has to be higher than or equal to the "Start date period" (Tag DatAanvTv) within one report or one correction report.'
11807: , p_action_information6 => '0040 - '||'The "End date income relation" (Tag DatEind) has to be lower than or equal to the "End date period" (Tag DatEindTv) within one report or one correction report.'
11808: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
11809: , p_action_information8 => l_emp_rec.assignment_number);
11814: IF l_emp_end_date is not null and l_emp_end_date < l_ass_start_date THEN
11815: --
11816: fnd_message.set_name('PER','HR_373544_NL_DATE_GTEQ');
11817: fnd_message.set_token('TAG1','DatEind');
11818: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_emp_end_date));
11819: fnd_message.set_token('TAG2','DatAanv');
11820: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(l_ass_start_date));
11821:
11822: pay_action_information_api.create_action_information
11816: fnd_message.set_name('PER','HR_373544_NL_DATE_GTEQ');
11817: fnd_message.set_token('TAG1','DatEind');
11818: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_emp_end_date));
11819: fnd_message.set_token('TAG2','DatAanv');
11820: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(l_ass_start_date));
11821:
11822: pay_action_information_api.create_action_information
11823: (
11824: p_action_information_id => l_action_info_id
11831: , p_source_text => NULL
11832: , p_tax_unit_id => l_tax_unit_id
11833: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
11834: , p_action_information4 => '0041 - '||fnd_message.get
11835: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
11836: , p_action_information6 => '0041 - '||'The "End date income relation" (Tag DatEind) has to be higher than or equal to the "Start date income relation" (Tag DatAanv).'
11837: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
11838: , p_action_information8 => l_emp_rec.assignment_number);
11839: --
11854: , p_source_text => NULL
11855: , p_tax_unit_id => l_tax_unit_id
11856: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
11857: , p_action_information4 => '0042 - '||fnd_message.get_string('PER','HR_373545_NL_RDNEINDFLX_CHECK1')
11858: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
11859: , p_action_information6 => '0042 - '||'The "Code reason end of Income relation Flex worker" is only allowed when the "Code kind of labour relation" (Tag CdAard) is equal to 11.'
11860: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
11861: , p_action_information8 => l_emp_rec.assignment_number);
11862: --
11877: , p_source_text => NULL
11878: , p_tax_unit_id => l_tax_unit_id
11879: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
11880: , p_action_information4 => '0043 - '||fnd_message.get_string('PER','HR_373546_NL_RDNEINDFLX_CHECK2')
11881: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
11882: , p_action_information6 => '0043 - '||'The "Code reason end of Income relation Flex worker" is only allowed when the "End date income relation" (Tag DatEind) is reported.'
11883: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
11884: , p_action_information8 => l_emp_rec.assignment_number);
11885: --
11945: , p_source_text => NULL
11946: , p_tax_unit_id => l_tax_unit_id
11947: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
11948: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_ASG_RISK_GRP')
11949: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
11950: , p_action_information6 => 'Assignment has no Sector or Risk Group'
11951: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
11952: , p_action_information8 => l_emp_rec.assignment_number);
11953: --
12011: , p_source_text => NULL
12012: , p_tax_unit_id => l_tax_unit_id
12013: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12014: , p_action_information4 => fnd_message.get_string('PER','HR_373549_NL_ZVW_J_DEFAULT')
12015: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
12016: , p_action_information6 => 'Contribution Exempt Small Job is applicable. Therefore, the ZVW code is set to J.'
12017: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12018: , p_action_information8 => l_emp_rec.assignment_number);
12019: END IF;
12030: , p_source_text => NULL
12031: , p_tax_unit_id => l_tax_unit_id
12032: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12033: , p_action_information4 => fnd_message.get_string('PER','HR_373550_NL_ZVW_J_WARNING')
12034: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
12035: , p_action_information6 => 'The ZVW code is set to J when Contribution Exempt Small Job is not applicable.'
12036: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12037: , p_action_information8 => l_emp_rec.assignment_number);
12038: END IF;
12054: , p_source_text => NULL
12055: , p_tax_unit_id => l_tax_unit_id
12056: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12057: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_ZVW_CODE_INSURED')
12058: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
12059: , p_action_information6 => 'Code ZVW is defaulted to - A'
12060: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12061: , p_action_information8 => l_emp_rec.assignment_number);
12062: END IF;
12076: , p_source_text => NULL
12077: , p_tax_unit_id => l_tax_unit_id
12078: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12079: , p_action_information4 => '0053 - '||fnd_message.get_string('PER','HR_NL_INVALID_LABOR_CODE')
12080: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
12081: , p_action_information6 => '0053 - '||'Labor Relation code is null'
12082: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12083: , p_action_information8 => l_emp_rec.assignment_number);
12084: END IF;
12098: , p_source_text => NULL
12099: , p_tax_unit_id => l_tax_unit_id
12100: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12101: , p_action_information4 => '0055 - '||fnd_message.get_string('PER','HR_NL_INVALID_CONTRACT_CODE')
12102: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
12103: , p_action_information6 => '0055 - '||'Code contract for limited or unlimited time is null'
12104: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12105: , p_action_information8 => l_emp_rec.assignment_number);
12106: END IF;
12118: , p_source_text => NULL
12119: , p_tax_unit_id => l_tax_unit_id
12120: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12121: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_WORK_PATTERN')
12122: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
12123: , p_action_information6 => 'Indication regular work pattern is null'
12124: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12125: , p_action_information8 => l_emp_rec.assignment_number);
12126: END IF;
12138: , p_source_text => NULL
12139: , p_tax_unit_id => l_tax_unit_id
12140: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12141: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_TEMP_LABOR_CODE')
12142: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
12143: , p_action_information6 => 'Temp Labor Code is null'
12144: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12145: , p_action_information8 => l_emp_rec.assignment_number);
12146: END IF;
12158: , p_source_text => NULL
12159: , p_tax_unit_id => l_tax_unit_id
12160: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12161: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_COMPANY_CAR_USE')
12162: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
12163: , p_action_information6 => 'Company Car Usage code invalid'
12164: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12165: , p_action_information8 => l_emp_rec.assignment_number);
12166: END IF;
12182: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
12183: , p_tax_unit_id => l_tax_unit_id
12184: , p_action_information1 => l_type
12185: , p_action_information2 => l_master_action_info_id
12186: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(l_rec_changes(i).effective_date,l_ass_start_date),trunc(l_rec_changes(i).period_end_date,'Y')))
12187: , p_action_information6 => l_income_code
12188: , p_action_information7 => l_labour_rel_code
12189: , p_action_information8 => l_ins_duty_code
12190: , p_action_information9 => l_assignment_catg
12221: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
12222: , p_tax_unit_id => l_tax_unit_id
12223: , p_action_information1 => 'CORRECT' --l_type 10100628
12224: , p_action_information2 => l_master_action_info_id
12225: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(l_rec_changes(i).effective_date,l_ass_start_date),trunc(l_rec_changes(i).period_end_date,'Y')))
12226: , p_action_information6 => l_income_code
12227: , p_action_information7 => l_labour_rel_code
12228: , p_action_information8 => l_ins_duty_code
12229: , p_action_information9 => l_assignment_catg
12261: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
12262: , p_tax_unit_id => l_tax_unit_id
12263: , p_action_information1 => l_type
12264: , p_action_information2 => l_master_action_info_id
12265: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(l_rec_changes(i).effective_date,l_ass_start_date),trunc(l_rec_changes(i).period_end_date,'Y')))
12266: , p_action_information6 => l_income_code
12267: , p_action_information7 => l_labour_rel_code
12268: , p_action_information8 => l_ins_duty_code
12269: , p_action_information9 => l_assignment_catg
12305: , p_source_text => NULL
12306: , p_tax_unit_id => l_tax_unit_id
12307: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12308: , p_action_information4 => '0054 - '||fnd_message.get_string('PER','HR_373539_NL_CDAARD_CHECK')
12309: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12310: , p_action_information6 => '0054 - '||'The "Code kind of labour relation" (Tag CdAard) has to be 18 when "Income Code" (Tag SrtIV) is equal to 18.'
12311: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12312: , p_action_information8 => l_emp_rec.assignment_number);
12313: END IF;
12334: , p_source_text => NULL
12335: , p_tax_unit_id => l_tax_unit_id
12336: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12337: , p_action_information4 => '0056 - '||fnd_message.get
12338: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12339: , p_action_information6 => '0056 - '||'When the "Income Code" (Tag SrtIV) is equal to 11, 12, 13, 14, 15, 17 or 18, the "Code CAO" (Tag CAO) is mandatory.'
12340: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12341: , p_action_information8 => l_emp_rec.assignment_number);
12342: END IF;
12363: , p_source_text => NULL
12364: , p_tax_unit_id => l_tax_unit_id
12365: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12366: , p_action_information4 => '0057 - '||fnd_message.get
12367: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12368: , p_action_information6 => '0057 - '||'When the employee reaches the age of 65 in the current period (the age is 65 on the last day of the period), the "Indication WAO/IVA/WGA insured" (Tag IndWAO) cannot be equal to "J".'
12369: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12370: , p_action_information8 => l_emp_rec.assignment_number);
12371: END IF;
12386: , p_source_text => NULL
12387: , p_tax_unit_id => l_tax_unit_id
12388: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12389: , p_action_information4 => '0058 - '||fnd_message.get
12390: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12391: , p_action_information6 => '0058 - '||'When the employee reaches the age of 65 in the current period (the age is 65 on the last day of the period), the "Indication WW insured" (Tag IndWW) cannot be equal to "J".'
12392: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12393: , p_action_information8 => l_emp_rec.assignment_number);
12394: END IF;
12409: , p_source_text => NULL
12410: , p_tax_unit_id => l_tax_unit_id
12411: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12412: , p_action_information4 => '0059 - '||fnd_message.get
12413: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12414: , p_action_information6 => '0059 - '||'When the employee reaches the age of 65 in the current period (the age is 65 on the last day of the period), the "Indication ZW insured" (Tag IndZW) cannot be equal to "J".'
12415: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12416: , p_action_information8 => l_emp_rec.assignment_number);
12417:
12441: , p_source_text => NULL
12442: , p_tax_unit_id => l_tax_unit_id
12443: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12444: , p_action_information4 => '0060 - '||fnd_message.get
12445: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12446: , p_action_information6 => '0060 - '||'The "Indication insurance situation Zvw" (Tag CdZvw) can only be equal to "G" when "Code wage tax table" (Tag LbTab) is equal to 221, 224 or 225.'
12447: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12448: , p_action_information8 => l_emp_rec.assignment_number);
12449: --abraghun--7668628--Check0061
12465: , p_source_text => NULL
12466: , p_tax_unit_id => l_tax_unit_id
12467: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12468: , p_action_information4 => '0061 - '||fnd_message.get
12469: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12470: , p_action_information6 => '0061 - '||'The "Indication insurance situation Zvw" (Tag CdZvw) can only be equal to "H" when "Code wage tax table" (Tag LbTab) is equal to 220.'
12471: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12472: , p_action_information8 => l_emp_rec.assignment_number);
12473:
12516: ,p_source_text => NULL
12517: ,p_tax_unit_id => l_tax_unit_id
12518: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
12519: ,p_action_information1 => l_type
12520: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))
12521: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)
12522: ,p_action_information4 => l_emp_rec.assignment_number
12523: ,p_action_information5 => l_emp_rec.employee_number
12524: ,p_action_information8 => l_emp_rec.sofi_number
12517: ,p_tax_unit_id => l_tax_unit_id
12518: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
12519: ,p_action_information1 => l_type
12520: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))
12521: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)
12522: ,p_action_information4 => l_emp_rec.assignment_number
12523: ,p_action_information5 => l_emp_rec.employee_number
12524: ,p_action_information8 => l_emp_rec.sofi_number
12525: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
12526: ,p_action_information7 => l_emp_rec.person_id
12527: ,p_action_information9 => l_emp_rec.Initials
12528: ,p_action_information10 => l_emp_rec.prefix
12529: ,p_action_information11 => l_emp_rec.last_name
12530: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
12531: ,p_action_information13 => l_nationality
12532: ,p_action_information14 => l_emp_rec.gender
12533: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
12534: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
12529: ,p_action_information11 => l_emp_rec.last_name
12530: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
12531: ,p_action_information13 => l_nationality
12532: ,p_action_information14 => l_emp_rec.gender
12533: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
12534: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
12535: ,p_action_information17 => NULL
12536: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
12537: END IF;
12530: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
12531: ,p_action_information13 => l_nationality
12532: ,p_action_information14 => l_emp_rec.gender
12533: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
12534: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
12535: ,p_action_information17 => NULL
12536: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
12537: END IF;
12538: ELSE
12547: ,p_source_text => NULL
12548: ,p_tax_unit_id => l_tax_unit_id
12549: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
12550: ,p_action_information1 => 'CORRECT' --l_type 10100628
12551: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))
12552: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)
12553: ,p_action_information4 => l_emp_rec.assignment_number
12554: ,p_action_information5 => l_emp_rec.employee_number
12555: ,p_action_information8 => l_emp_rec.sofi_number
12548: ,p_tax_unit_id => l_tax_unit_id
12549: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
12550: ,p_action_information1 => 'CORRECT' --l_type 10100628
12551: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))
12552: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)
12553: ,p_action_information4 => l_emp_rec.assignment_number
12554: ,p_action_information5 => l_emp_rec.employee_number
12555: ,p_action_information8 => l_emp_rec.sofi_number
12556: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
12557: ,p_action_information7 => l_emp_rec.person_id
12558: ,p_action_information9 => l_emp_rec.Initials
12559: ,p_action_information10 => l_emp_rec.prefix
12560: ,p_action_information11 => l_emp_rec.last_name
12561: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
12562: ,p_action_information13 => l_nationality
12563: ,p_action_information14 => l_emp_rec.gender
12564: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
12565: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
12560: ,p_action_information11 => l_emp_rec.last_name
12561: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
12562: ,p_action_information13 => l_nationality
12563: ,p_action_information14 => l_emp_rec.gender
12564: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
12565: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
12566: ,p_action_information17 => NULL
12567: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
12568: END IF;
12561: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
12562: ,p_action_information13 => l_nationality
12563: ,p_action_information14 => l_emp_rec.gender
12564: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
12565: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
12566: ,p_action_information17 => NULL
12567: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
12568: END IF;
12569: ELSE
12579: ,p_source_text => NULL
12580: ,p_tax_unit_id => l_tax_unit_id
12581: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
12582: ,p_action_information1 => l_type
12583: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))
12584: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)
12585: ,p_action_information4 => l_emp_rec.assignment_number
12586: ,p_action_information5 => l_emp_rec.employee_number
12587: ,p_action_information8 => l_emp_rec.sofi_number
12580: ,p_tax_unit_id => l_tax_unit_id
12581: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
12582: ,p_action_information1 => l_type
12583: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))
12584: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)
12585: ,p_action_information4 => l_emp_rec.assignment_number
12586: ,p_action_information5 => l_emp_rec.employee_number
12587: ,p_action_information8 => l_emp_rec.sofi_number
12588: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
12589: ,p_action_information7 => l_emp_rec.person_id
12590: ,p_action_information9 => l_emp_rec.Initials
12591: ,p_action_information10 => l_emp_rec.prefix
12592: ,p_action_information11 => l_emp_rec.last_name
12593: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
12594: ,p_action_information13 => l_nationality
12595: ,p_action_information14 => l_emp_rec.gender
12596: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
12597: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
12592: ,p_action_information11 => l_emp_rec.last_name
12593: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
12594: ,p_action_information13 => l_nationality
12595: ,p_action_information14 => l_emp_rec.gender
12596: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
12597: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
12598: ,p_action_information17 => NULL
12599: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
12600: END IF; --10100628
12593: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
12594: ,p_action_information13 => l_nationality
12595: ,p_action_information14 => l_emp_rec.gender
12596: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
12597: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
12598: ,p_action_information17 => NULL
12599: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
12600: END IF; --10100628
12601:
12623: , p_source_text => NULL
12624: , p_tax_unit_id => l_tax_unit_id
12625: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12626: , p_action_information4 => fnd_message.get_string('PER','HR_373547_NUMIV_OVERRIDE')
12627: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12628: , p_action_information6 => 'Num IV override Already Used'
12629: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12630: , p_action_information8 => l_emp_rec.assignment_number);
12631: END IF;
12648: , p_source_text => NULL
12649: , p_tax_unit_id => l_tax_unit_id
12650: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12651: , p_action_information4 => '0044/1044 - '||fnd_message.get_string('PER','HR_373537_NL_EMPNO_MANDATORY')
12652: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12653: , p_action_information6 => '0044/1044 - '||'When the "BSN/Sofi-number" (Tag SofiNr) is not reported the record "Employee number" (Tag PersNr) is mandatory.'
12654: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12655: , p_action_information8 => l_emp_rec.assignment_number);
12656: --
12678: , p_source_text => NULL
12679: , p_tax_unit_id => l_tax_unit_id
12680: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12681: , p_action_information4 => '0046 - '||fnd_message.get
12682: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12683: , p_action_information6 => '0046 - '||'When "BSN/Sofi-number" (Tag SofiNr) is reported and "Code wage tax table" (Tag LbTab) is not equal to 940 the "Last Name" (Tag SignNm) cannot be empty / is mandatory.'
12684: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12685: , p_action_information8 => l_emp_rec.assignment_number);
12686: --
12704: , p_source_text => NULL
12705: , p_tax_unit_id => l_tax_unit_id
12706: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12707: , p_action_information4 => '0047 - '||fnd_message.get
12708: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12709: , p_action_information6 => '0047 - '||'When "BSN/Sofi-number" (Tag SofiNr) is reported and "Code wage tax table" (Tag LbTab) is not equal to 940 the "Date of birth" (Tag Gebdat) cannot be empty / is mandatory.'
12710: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12711: , p_action_information8 => l_emp_rec.assignment_number);
12712: --
12730: , p_source_text => NULL
12731: , p_tax_unit_id => l_tax_unit_id
12732: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12733: , p_action_information4 => '0048 - '||fnd_message.get
12734: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12735: , p_action_information6 => '0048 - '||'When "BSN/Sofi-number" (Tag SofiNr) is reported and "Code wage tax table" (Tag LbTab) is not equal to 940 the "Nationality" (Tag Nat) cannot be empty / is mandatory.'
12736: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12737: , p_action_information8 => l_emp_rec.assignment_number);
12738: --
12756: , p_source_text => NULL
12757: , p_tax_unit_id => l_tax_unit_id
12758: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12759: , p_action_information4 => '0049 - '||fnd_message.get
12760: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12761: , p_action_information6 => '0049 - '||'When "BSN/Sofi-number" (Tag SofiNr) is reported and "Code wage tax table" (Tag LbTab) is not equal to 940 the "Gender" (Tag Gesl) cannot be empty / is mandatory.'
12762: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12763: , p_action_information8 => l_emp_rec.assignment_number);
12764: --
12769: IF l_ass_start_date > p_chk_end_date THEN
12770: --
12771: fnd_message.set_name('PER','HR_373543_NL_DATE_LTEQ');
12772: fnd_message.set_token('TAG1','DatAanv');
12773: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_ass_start_date));
12774: fnd_message.set_token('TAG2','DatEindTv');
12775: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(p_chk_end_date));
12776:
12777: pay_action_information_api.create_action_information
12771: fnd_message.set_name('PER','HR_373543_NL_DATE_LTEQ');
12772: fnd_message.set_token('TAG1','DatAanv');
12773: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_ass_start_date));
12774: fnd_message.set_token('TAG2','DatEindTv');
12775: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(p_chk_end_date));
12776:
12777: pay_action_information_api.create_action_information
12778: (
12779: p_action_information_id => l_action_info_id
12786: , p_source_text => NULL
12787: , p_tax_unit_id => l_tax_unit_id
12788: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12789: , p_action_information4 => '0039 - '||fnd_message.get
12790: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12791: , p_action_information6 => '0039 - '||'The "Start date income relation" (Tag DatAanv) has to be lower than or equal to the "End date period" (Tag DatEindTv) within one report or one correction report.'
12792: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12793: , p_action_information8 => l_emp_rec.assignment_number);
12794: --
12807:
12808: --fnd_message.set_name('PER','HR_373544_NL_DATE_GTEQ');
12809: fnd_message.set_name('PER','HR_373543_NL_DATE_LTEQ');
12810: fnd_message.set_token('TAG1','DatEind');
12811: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_emp_end_date));
12812: --fnd_message.set_token('TAG2','DatAanvTv');
12813: --fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y'))));
12814: fnd_message.set_token('TAG2','DatEindTv');
12815: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(p_chk_end_date));
12809: fnd_message.set_name('PER','HR_373543_NL_DATE_LTEQ');
12810: fnd_message.set_token('TAG1','DatEind');
12811: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_emp_end_date));
12812: --fnd_message.set_token('TAG2','DatAanvTv');
12813: --fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y'))));
12814: fnd_message.set_token('TAG2','DatEindTv');
12815: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(p_chk_end_date));
12816:
12817: --
12811: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_emp_end_date));
12812: --fnd_message.set_token('TAG2','DatAanvTv');
12813: --fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y'))));
12814: fnd_message.set_token('TAG2','DatEindTv');
12815: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(p_chk_end_date));
12816:
12817: --
12818: pay_action_information_api.create_action_information
12819: (
12827: , p_source_text => NULL
12828: , p_tax_unit_id => l_tax_unit_id
12829: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12830: , p_action_information4 => '0040 - '||fnd_message.get
12831: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12832: --, p_action_information6 => '0040 - '||'The "End date income relation" (Tag DatEind) has to be higher than or equal to the "Start date period" (Tag DatAanvTv) within one report or one correction report.'
12833: , p_action_information6 => '0040 - '||'The "End date income relation" (Tag DatEind) has to be lower than or equal to the "End date period" (Tag DatEindTv) within one report or one correction report.'
12834: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12835: , p_action_information8 => l_emp_rec.assignment_number);
12840: IF l_emp_end_date is not null and l_emp_end_date < l_ass_start_date THEN
12841: --
12842: fnd_message.set_name('PER','HR_373544_NL_DATE_GTEQ');
12843: fnd_message.set_token('TAG1','DatEind');
12844: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_emp_end_date));
12845: fnd_message.set_token('TAG2','DatAanv');
12846: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(l_ass_start_date));
12847:
12848: pay_action_information_api.create_action_information
12842: fnd_message.set_name('PER','HR_373544_NL_DATE_GTEQ');
12843: fnd_message.set_token('TAG1','DatEind');
12844: fnd_message.set_token('TAGVAL1',fnd_date.date_to_canonical(l_emp_end_date));
12845: fnd_message.set_token('TAG2','DatAanv');
12846: fnd_message.set_token('TAGVAL2',fnd_date.date_to_canonical(l_ass_start_date));
12847:
12848: pay_action_information_api.create_action_information
12849: (
12850: p_action_information_id => l_action_info_id
12857: , p_source_text => NULL
12858: , p_tax_unit_id => l_tax_unit_id
12859: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12860: , p_action_information4 => '0041 - '||fnd_message.get
12861: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12862: , p_action_information6 => '0041 - '||'The "End date income relation" (Tag DatEind) has to be higher than or equal to the "Start date income relation" (Tag DatAanv).'
12863: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12864: , p_action_information8 => l_emp_rec.assignment_number);
12865: --
12880: , p_source_text => NULL
12881: , p_tax_unit_id => l_tax_unit_id
12882: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12883: , p_action_information4 => '0042 - '||fnd_message.get_string('PER','HR_373545_NL_RDNEINDFLX_CHECK1')
12884: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12885: , p_action_information6 => '0042 - '||'The "Code reason end of Income relation Flex worker" is only allowed when the "Code kind of labour relation" (Tag CdAard) is equal to 11.'
12886: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12887: , p_action_information8 => l_emp_rec.assignment_number);
12888: --
12903: , p_source_text => NULL
12904: , p_tax_unit_id => l_tax_unit_id
12905: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12906: , p_action_information4 => '0043 - '||fnd_message.get_string('PER','HR_373546_NL_RDNEINDFLX_CHECK2')
12907: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12908: , p_action_information6 => '0043 - '||'The "Code reason end of Income relation Flex worker" is only allowed when the "End date income relation" (Tag DatEind) is reported.'
12909: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12910: , p_action_information8 => l_emp_rec.assignment_number);
12911: --
12954: , p_source_text => NULL
12955: , p_tax_unit_id => l_tax_unit_id
12956: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12957: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_ASG_RISK_GRP')
12958: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12959: , p_action_information6 => 'Assignment has no Sector or Risk Group'
12960: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12961: , p_action_information8 => l_emp_rec.assignment_number);
12962: --
12978: , p_source_text => NULL
12979: , p_tax_unit_id => l_tax_unit_id
12980: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
12981: , p_action_information4 => fnd_message.get_string('PER','HR_373549_NL_ZVW_J_DEFAULT')
12982: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
12983: , p_action_information6 => 'Contribution Exempt Small Job is applicable. Therefore, the ZVW code is set to J.'
12984: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12985: , p_action_information8 => l_emp_rec.assignment_number);
12986: END IF;
12997: , p_source_text => NULL
12998: , p_tax_unit_id => l_tax_unit_id
12999: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13000: , p_action_information4 => fnd_message.get_string('PER','HR_373550_NL_ZVW_J_WARNING')
13001: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13002: , p_action_information6 => 'The ZVW code is set to J when Contribution Exempt Small Job is not applicable.'
13003: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13004: , p_action_information8 => l_emp_rec.assignment_number);
13005: END IF;
13020: , p_source_text => NULL
13021: , p_tax_unit_id => l_tax_unit_id
13022: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13023: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_ZVW_CODE_INSURED')
13024: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13025: , p_action_information6 => 'Code ZVW is defaulted to - A'
13026: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13027: , p_action_information8 => l_emp_rec.assignment_number);
13028: END IF;
13041: , p_source_text => NULL
13042: , p_tax_unit_id => l_tax_unit_id
13043: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13044: , p_action_information4 => '0053 - '||fnd_message.get_string('PER','HR_NL_INVALID_LABOR_CODE')
13045: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13046: , p_action_information6 => '0053 - '||'Labor Relation code is null'
13047: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13048: , p_action_information8 => l_emp_rec.assignment_number);
13049: END IF;
13062: , p_source_text => NULL
13063: , p_tax_unit_id => l_tax_unit_id
13064: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13065: , p_action_information4 => '0055 - '||fnd_message.get_string('PER','HR_NL_INVALID_CONTRACT_CODE')
13066: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13067: , p_action_information6 => '0055 - '||'Code contract for limited or unlimited time is null'
13068: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13069: , p_action_information8 => l_emp_rec.assignment_number);
13070: END IF;
13082: , p_source_text => NULL
13083: , p_tax_unit_id => l_tax_unit_id
13084: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13085: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_WORK_PATTERN')
13086: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13087: , p_action_information6 => 'Indication regular work pattern is null'
13088: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13089: , p_action_information8 => l_emp_rec.assignment_number);
13090: END IF;
13102: , p_source_text => NULL
13103: , p_tax_unit_id => l_tax_unit_id
13104: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13105: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_TEMP_LABOR_CODE')
13106: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13107: , p_action_information6 => 'Temp Labor Code is null'
13108: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13109: , p_action_information8 => l_emp_rec.assignment_number);
13110: END IF;
13122: , p_source_text => NULL
13123: , p_tax_unit_id => l_tax_unit_id
13124: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13125: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_COMPANY_CAR_USE')
13126: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13127: , p_action_information6 => 'Company Car Usage code invalid'
13128: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13129: , p_action_information8 => l_emp_rec.assignment_number);
13130: END IF;
13147: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
13148: , p_tax_unit_id => l_tax_unit_id
13149: , p_action_information1 => l_type
13150: , p_action_information2 => l_master_action_info_id
13151: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(p_chk_start_date,l_ass_start_date),trunc(p_chk_end_date,'Y')))
13152: , p_action_information6 => l_income_code
13153: , p_action_information7 => l_labour_rel_code
13154: , p_action_information8 => l_ins_duty_code
13155: , p_action_information9 => l_assignment_catg
13186: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
13187: , p_tax_unit_id => l_tax_unit_id
13188: , p_action_information1 => 'CORRECT' --l_type 10100628
13189: , p_action_information2 => l_master_action_info_id
13190: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(p_chk_start_date,l_ass_start_date),trunc(p_chk_end_date,'Y')))
13191: , p_action_information6 => l_income_code
13192: , p_action_information7 => l_labour_rel_code
13193: , p_action_information8 => l_ins_duty_code
13194: , p_action_information9 => l_assignment_catg
13227: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
13228: , p_tax_unit_id => l_tax_unit_id
13229: , p_action_information1 => l_type
13230: , p_action_information2 => l_master_action_info_id
13231: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(p_chk_start_date,l_ass_start_date),trunc(p_chk_end_date,'Y')))
13232: , p_action_information6 => l_income_code
13233: , p_action_information7 => l_labour_rel_code
13234: , p_action_information8 => l_ins_duty_code
13235: , p_action_information9 => l_assignment_catg
13270: , p_source_text => NULL
13271: , p_tax_unit_id => l_tax_unit_id
13272: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13273: , p_action_information4 => '0054 - '||fnd_message.get_string('PER','HR_373539_NL_CDAARD_CHECK')
13274: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13275: , p_action_information6 => '0054 - '||'The "Code kind of labour relation" (Tag CdAard) has to be 18 when "Income Code" (Tag SrtIV) is equal to 18.'
13276: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13277: , p_action_information8 => l_emp_rec.assignment_number);
13278: END IF;
13295: , p_source_text => NULL
13296: , p_tax_unit_id => l_tax_unit_id
13297: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13298: , p_action_information4 => '0056 - '||fnd_message.get
13299: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13300: , p_action_information6 => '0056 - '||'When the "Income Code" (Tag SrtIV) is equal to 11, 12, 13, 14, 15, 17 or 18, the "Code CAO" (Tag CAO) is mandatory.'
13301: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13302: , p_action_information8 => l_emp_rec.assignment_number);
13303: END IF;
13326: , p_source_text => NULL
13327: , p_tax_unit_id => l_tax_unit_id
13328: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13329: , p_action_information4 => '0057 - '||fnd_message.get
13330: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13331: , p_action_information6 => '0057 - '||'When the employee reaches the age of 65 in the current period (the age is 65 on the last day of the period), the "Indication WAO/IVA/WGA insured" (Tag IndWAO) cannot be equal to "J".'
13332: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13333: , p_action_information8 => l_emp_rec.assignment_number);
13334: END IF;
13349: , p_source_text => NULL
13350: , p_tax_unit_id => l_tax_unit_id
13351: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13352: , p_action_information4 => '0058 - '||fnd_message.get
13353: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13354: , p_action_information6 => '0058 - '||'When the employee reaches the age of 65 in the current period (the age is 65 on the last day of the period), the "Indication WW insured" (Tag IndWW) cannot be equal to "J".'
13355: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13356: , p_action_information8 => l_emp_rec.assignment_number);
13357: END IF;
13372: , p_source_text => NULL
13373: , p_tax_unit_id => l_tax_unit_id
13374: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13375: , p_action_information4 => '0059 - '||fnd_message.get
13376: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13377: , p_action_information6 => '0059 - '||'When the employee reaches the age of 65 in the current period (the age is 65 on the last day of the period), the "Indication ZW insured" (Tag IndZW) cannot be equal to "J".'
13378: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13379: , p_action_information8 => l_emp_rec.assignment_number);
13380:
13402: , p_source_text => NULL
13403: , p_tax_unit_id => l_tax_unit_id
13404: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13405: , p_action_information4 => '0060 - '||fnd_message.get
13406: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13407: , p_action_information6 => '0060 - '||'The "Indication insurance situation Zvw" (Tag CdZvw) can only be equal to "G" when "Code wage tax table" (Tag LbTab) is equal to 221, 224 or 225.'
13408: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13409: , p_action_information8 => l_emp_rec.assignment_number);
13410: --abraghun--7668628--Check0061
13426: , p_source_text => NULL
13427: , p_tax_unit_id => l_tax_unit_id
13428: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
13429: , p_action_information4 => '0061 - '||fnd_message.get
13430: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
13431: , p_action_information6 => '0061 - '||'The "Indication insurance situation Zvw" (Tag CdZvw) can only be equal to "H" when "Code wage tax table" (Tag LbTab) is equal to 220.'
13432: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
13433: , p_action_information8 => l_emp_rec.assignment_number);
13434:
13502: ORDER BY pai.effective_date DESC;
13503: --
13504: CURSOR csr_get_income_start_date(c_assactid NUMBER
13505: ,c_assignment_id NUMBER) IS
13506: SELECT MIN(fnd_date.canonical_to_date(action_information5))
13507: FROM pay_action_interlocks pal
13508: ,pay_action_information pai
13509: WHERE pal.locking_action_id = c_assactid
13510: AND pal.locked_action_id = pai.action_context_id
13516: CURSOR csr_get_sector_start_date(c_assactid NUMBER
13517: ,c_assignment_id NUMBER
13518: ,c_sector VARCHAR2
13519: ,c_risk_grp VARCHAR2) IS
13520: SELECT MIN(fnd_date.canonical_to_date(action_information5))
13521: FROM pay_action_interlocks pal
13522: ,pay_action_information pai
13523: WHERE pal.locking_action_id = c_assactid
13524: AND pal.locked_action_id = pai.action_context_id
13623: ,p_source_text => NULL
13624: ,p_tax_unit_id => l_tax_unit_id
13625: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
13626: ,p_action_information1 => 'INITIAL'
13627: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_start_date,trunc(p_end_date,'Y')))
13628: ,p_action_information3 => fnd_date.date_to_canonical(p_end_date)
13629: ,p_action_information4 => csr_action_info_rec.action_information4
13630: ,p_action_information5 => csr_action_info_rec.action_information5
13631: ,p_action_information6 => csr_action_info_rec.action_information6
13624: ,p_tax_unit_id => l_tax_unit_id
13625: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
13626: ,p_action_information1 => 'INITIAL'
13627: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_start_date,trunc(p_end_date,'Y')))
13628: ,p_action_information3 => fnd_date.date_to_canonical(p_end_date)
13629: ,p_action_information4 => csr_action_info_rec.action_information4
13630: ,p_action_information5 => csr_action_info_rec.action_information5
13631: ,p_action_information6 => csr_action_info_rec.action_information6
13632: ,p_action_information7 => csr_action_info_rec.action_information7
13687: ,p_action_information1 => 'INITIAL'
13688: ,p_action_information2 => fnd_number.number_to_canonical(l_master_action_info_id)
13689: ,p_action_information3 => csr_ip_info_rec.action_information3
13690: ,p_action_information4 => csr_ip_info_rec.action_information4
13691: ,p_action_information5 => fnd_date.date_to_canonical(l_date) --fnd_date.date_to_canonical(p_start_date)
13692: ,p_action_information6 => csr_ip_info_rec.action_information6
13693: ,p_action_information7 => csr_ip_info_rec.action_information7
13694: ,p_action_information8 => csr_ip_info_rec.action_information8
13695: ,p_action_information9 => csr_ip_info_rec.action_information9
13732: ,p_tax_unit_id => l_tax_unit_id
13733: ,p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
13734: ,p_action_information1 => 'SECTOR_RISK_GROUP'
13735: ,p_action_information2 => fnd_number.number_to_canonical(l_master_action_info_id)
13736: ,p_action_information5 => fnd_date.date_to_canonical(l_date)--fnd_date.date_to_canonical(p_start_date)
13737: ,p_action_information6 => csr_srg_info_rec.action_information6--fnd_date.date_to_canonical(p_end_date)
13738: ,p_action_information7 => csr_srg_info_rec.action_information7
13739: ,p_action_information8 => csr_srg_info_rec.action_information8
13740: ,p_action_information9 => csr_srg_info_rec.action_information9
13733: ,p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
13734: ,p_action_information1 => 'SECTOR_RISK_GROUP'
13735: ,p_action_information2 => fnd_number.number_to_canonical(l_master_action_info_id)
13736: ,p_action_information5 => fnd_date.date_to_canonical(l_date)--fnd_date.date_to_canonical(p_start_date)
13737: ,p_action_information6 => csr_srg_info_rec.action_information6--fnd_date.date_to_canonical(p_end_date)
13738: ,p_action_information7 => csr_srg_info_rec.action_information7
13739: ,p_action_information8 => csr_srg_info_rec.action_information8
13740: ,p_action_information9 => csr_srg_info_rec.action_information9
13741: ,p_action_information10 => csr_srg_info_rec.action_information10);
14364: ,p_source_text => NULL
14365: ,p_tax_unit_id => p_tax_unit_id
14366: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
14367: ,p_action_information1 => l_type
14368: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))
14369: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)
14370: ,p_action_information4 => l_emp_rec.assignment_number
14371: ,p_action_information5 => l_emp_rec.employee_number
14372: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
14365: ,p_tax_unit_id => p_tax_unit_id
14366: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
14367: ,p_action_information1 => l_type
14368: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))
14369: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)
14370: ,p_action_information4 => l_emp_rec.assignment_number
14371: ,p_action_information5 => l_emp_rec.employee_number
14372: ,p_action_information6 => substr(l_emp_rec.change_reason,3)
14373: ,p_action_information8 => l_emp_rec.sofi_number
14374: ,p_action_information7 => l_emp_rec.person_id
14375: ,p_action_information9 => NULL -- l_emp_rec.Initials
14376: ,p_action_information10 => NULL -- l_emp_rec.prefix
14377: ,p_action_information11 => l_emp_rec.last_name
14378: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
14379: ,p_action_information13 => NULL -- l_nationality
14380: ,p_action_information14 => NULL -- l_emp_rec.gender
14381: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
14382: ,p_action_information16 => fnd_date.date_to_canonical(l_ass_end_date)
14377: ,p_action_information11 => l_emp_rec.last_name
14378: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
14379: ,p_action_information13 => NULL -- l_nationality
14380: ,p_action_information14 => NULL -- l_emp_rec.gender
14381: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
14382: ,p_action_information16 => fnd_date.date_to_canonical(l_ass_end_date)
14383: ,p_action_information17 => NULL
14384: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
14385:
14378: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
14379: ,p_action_information13 => NULL -- l_nationality
14380: ,p_action_information14 => NULL -- l_emp_rec.gender
14381: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
14382: ,p_action_information16 => fnd_date.date_to_canonical(l_ass_end_date)
14383: ,p_action_information17 => NULL
14384: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
14385:
14386: /* 9764359 */
14405: , p_source_text => NULL
14406: , p_tax_unit_id => p_tax_unit_id
14407: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
14408: , p_action_information4 => fnd_message.get_string('PER','HR_373547_NUMIV_OVERRIDE')
14409: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
14410: , p_action_information6 => 'Num IV override Already Used'
14411: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
14412: , p_action_information8 => l_emp_rec.assignment_number);
14413: END IF;
14564: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
14565: , p_tax_unit_id => p_tax_unit_id
14566: , p_action_information1 => l_type
14567: , p_action_information2 => l_master_action_info_id
14568: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(p_chk_start_date,l_ass_start_date),trunc(p_chk_end_date,'Y')))
14569: , p_action_information6 => l_income_code
14570: , p_action_information7 => l_labour_rel_code
14571: , p_action_information8 => l_ins_duty_code
14572: , p_action_information9 => l_assignment_catg
14700: SELECT 1
14701: FROM per_assignment_extra_info
14702: WHERE assignment_id = paa.assignment_id
14703: AND aei_information_category = 'NL_WR_PREV_CORR'
14704: AND ptp.start_date BETWEEN fnd_date.canonical_to_date(aei_information1)
14705: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
14706: )
14707: --10100628
14708: /*
14701: FROM per_assignment_extra_info
14702: WHERE assignment_id = paa.assignment_id
14703: AND aei_information_category = 'NL_WR_PREV_CORR'
14704: AND ptp.start_date BETWEEN fnd_date.canonical_to_date(aei_information1)
14705: AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
14706: )
14707: --10100628
14708: /*
14709: ( SELECT 1
14871: OPEN csr_chk_exception(p_actid);
14872: FETCH csr_chk_exception INTO l_exception;
14873: CLOSE csr_chk_exception;
14874: --
14875: l_return_string := rpad(fnd_date.date_to_displaydate(l_start_date),15)||'- '||rpad(fnd_date.date_to_displaydate(l_end_date),15);
14876: l_return_string := l_return_string ||'- '||rpad(HR_GENERAL.decode_lookup('NL_WR_PERIOD_TYPE',l_payroll_type),40);
14877: l_return_string := l_return_string ||'- '||rpad(l_tax_rep_name,40);
14878: l_return_string := l_return_string ||'- '||rpad(HR_GENERAL.decode_lookup('NL_FORM_LABELS',l_exception),25);
14879: --