[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_ATS_REPORT
Source
1 PACKAGE BODY PAY_NL_ATS_REPORT AS
2 /* $Header: paynlats.pkb 120.21 2012/01/19 08:14:10 rpahune ship $ */
3
4 level_cnt NUMBER;
5
6
7
8 /*Counter for accessing the values in PAY_NL_XDO_REPORT.vXMLTable*/
9 vCtr NUMBER;
10
11 /*-------------------------------------------------------------------------------
12 |Name : populate_ats_report_data |
13 |Type : Procedure |
14 |Description : Procedure to generate the Annual Tax Statement Report |
15 ------------------------------------------------------------------------------*/
16
17 procedure populate_ats_report_data
18 (p_person_id IN NUMBER,
19 p_year IN VARCHAR2,
20 p_bg_id IN NUMBER,
21 p_employer_id IN NUMBER,
22 p_agg_flag IN VARCHAR2,
23 p_xfdf_blob OUT NOCOPY BLOB) IS
24
25 /*Cursor to pick up necessary details*/
26
27 cursor get_infos(lp_tax_year_end_date date,lp_tax_year_start_date date,lp_yes varchar2,lp_no varchar2,lp_archive_action number) is
28 select /*+ ORDERED */ pap.full_name Employee_name,
29 hou.name Employer_name,
30 hoi.org_information4 Tax_registration_number,
31 pap.person_id Person_Id,
32 paa.assignment_id Assignment_Id,
33 pap.employee_number,
34 paa.assignment_number,
35 to_char(pap.Date_Of_Birth,'DD/MM/YYYY') Date_Of_Birth,
36 ppos.date_start Date_Start,
37 nvl(ppos.actual_termination_date,lp_tax_year_end_date) Date_End,
38 to_char(greatest(ppos.date_start,lp_tax_year_start_date),'DD/MM/YYYY')||' - '||to_char(least(nvl(ppos.actual_termination_date,lp_tax_year_end_date),lp_tax_year_end_date),'DD/MM/YYYY') Period_Of_Service,
39 pap.national_identifier SOFI_number,
40 DECODE(SUBSTR(pai.action_information10,2,1),1,hr_general.decode_lookup('NL_TAX_TABLE','1'),2,hr_general.decode_lookup('NL_TAX_TABLE','2'),'') Wage_Tax_Table,
41 decode(substr(pai.action_information9,1,1),'1',lp_yes,lp_no) Wage_Tax_Discount1,
42 '(' || substr(pai.action_information9,2,2)||'/'||substr(pai.action_information9,4,2)||'/'||to_char(lp_tax_year_end_date,'YYYY') || ')' Date1,
43 decode(substr(pai.action_information9,6,1),'1',lp_yes,lp_no) Wage_Tax_Discount2,
44 '(' || substr(pai.action_information9,7,2)||'/'||substr(pai.action_information9,9,2)||'/'||to_char(lp_tax_year_end_date,'YYYY') || ')' Date2,
45 decode(substr(pai.action_information9,11,1),'1',lp_yes,lp_no) Wage_Tax_Discount3,
46 '(' || substr(pai.action_information9,12,2)||'/'||substr(pai.action_information9,14,2)||'/'||to_char(lp_tax_year_end_date,'YYYY') || ')' Date3,
47 --11886537 begins
48 --NVL(pai.action_information18,pai.action_information4) Taxable_Income,
49 --pai.action_Information5 Deducted_Wage_Tax,
50 --pai.action_information8 Labour_Tax_Reduction,
51 NVL(fnd_number.canonical_to_number(pai.action_information18),fnd_number.canonical_to_number(pai.action_information4)) Taxable_Income,
52 fnd_number.canonical_to_number(pai.action_Information5) Deducted_Wage_Tax,
53 fnd_number.canonical_to_number(pai.action_information8) Labour_Tax_Reduction,
54 --11886537 ends
55 decode(substr(pai.action_information14,1,1),'1',lp_yes,2,lp_yes,3,lp_yes,lp_no) Insured_For_WAO,
56 --decode(substr(pai.action_information14,2,1),'1',lp_yes,2,lp_yes,3,lp_yes,lp_no) Insured_For_ZFW,
57 --pai.action_information15 ZVW_Cont, --11886537
58 fnd_number.canonical_to_number(pai.action_information15) ZVW_Cont, --11886537
59 substr(pai.action_information12,13,1) Company_Car,
60 --11886537 begins
61 --pai.action_information17 Private_Use_Car,
62 --pai.action_information16 Net_Expense_Allowance,
63 --pai.action_information19 ZVW_Basis,
64 --pai.action_information20 Value_Private_Use_Car,
65 --pai.action_information21 Saved_Amount_LSS,
66 --pai.action_information22 Employer_Child_Care,
67 --pai.action_information23 Allowance_on_Disability,
68 --pai.action_information24 Applied_LCLD,
69 fnd_number.canonical_to_number(pai.action_information17) Private_Use_Car,
70 fnd_number.canonical_to_number(pai.action_information16) Net_Expense_Allowance,
71 fnd_number.canonical_to_number(pai.action_information19) ZVW_Basis,
72 fnd_number.canonical_to_number(pai.action_information20) Value_Private_Use_Car,
73 fnd_number.canonical_to_number(pai.action_information21) Saved_Amount_LSS,
74 fnd_number.canonical_to_number(pai.action_information22) Employer_Child_Care,
75 fnd_number.canonical_to_number(pai.action_information23) Allowance_on_Disability,
76 fnd_number.canonical_to_number(pai.action_information24) Applied_LCLD,
77 --11886537 ends
78 pai.action_information25 User_Bal_String
79 from
80 pay_assignment_actions assact,
81 pay_action_information pai,
82 per_all_assignments_f paa,
83 per_all_people_f pap,
84 per_periods_of_service ppos,
85 hr_organization_units hou,
86 hr_organization_information hoi
87 where
88 pai.action_context_type = 'AAP'
89 and assact.payroll_action_id = lp_archive_action
90 and pai.action_context_id = assact.assignment_action_id
91 and pai.action_information_category = 'NL ATS EMPLOYEE DETAILS'
92 and hoi.org_information_context = 'NL_ORG_INFORMATION'
93 and pap.person_id = nvl(p_person_id,pap.person_id)
94 and ppos.person_id = pap.person_id
95 and ppos.date_start <= lp_Tax_Year_End_Date
96 --and nvl(ppos.actual_termination_date, lp_Tax_Year_End_Date) >= lp_Tax_Year_Start_Date
97 and pai.action_information1 = to_char(p_employer_id)
98 and decode(pai.action_information_category,'NL ATS EMPLOYEE DETAILS',fnd_number.canonical_to_number(pai.action_information2),null) = nvl(p_person_id,pap.person_id)
99 and decode(pai.action_information_category,'NL ATS EMPLOYEE DETAILS',fnd_number.canonical_to_number(pai.action_information3),null) = paa.assignment_id
100 and pai.effective_date = lp_Tax_Year_End_Date
101 and paa.effective_start_date =
102 (
103 SELECT MIN(asg.effective_start_date)
104 FROM per_all_assignments_f asg
105 WHERE asg.assignment_id = paa.assignment_id
106 and asg.payroll_id is not NULL
107 and asg.effective_start_date <= least(nvl(ppos.actual_termination_date, lp_Tax_Year_End_Date), lp_Tax_Year_End_Date)
108 and nvl(asg.effective_end_date, lp_Tax_Year_End_Date) >= ppos.date_start
109
110 )
111 and lp_Tax_Year_End_Date between pap.effective_start_date and pap.effective_end_date
112 and lp_Tax_Year_End_Date between hou.date_from and nvl(hou.date_to,hr_general.end_of_time)
113 and paa.person_id = pap.person_id
114 and pap.business_group_id = p_bg_id
115 and paa.business_group_id = p_bg_id
116 and hou.organization_id = p_employer_id
117 and hoi.organization_id = p_employer_id
118 order by get_Address_Style(pap.person_id,lp_tax_year_end_date) desc, get_Post_Code(pap.person_id,lp_tax_year_end_date) asc, pap.person_id asc, paa.assignment_id asc;
119
120 /* CURSOR csr_org_hierarchy_name(lp_business_group_id number,lp_tax_year_end_date date) IS
121 select
122 pos.name
123 from
124 per_organization_structures pos,
125 per_org_structure_versions posv
126 where pos.organization_structure_id = posv.organization_structure_id
127 and to_char(pos.organization_structure_id) IN (select org_information1
128 from hr_organization_information hoi where hoi.org_information_context='NL_BG_INFO'
129 and hoi.organization_id=lp_business_group_id)
130 and lp_tax_year_end_date between posv.date_from and nvl(posv.date_to,hr_general.End_of_time);*/
131
132 CURSOR csr_get_org_name(lp_org_id number) IS
133 select name
134 from hr_organization_units
135 where organization_id = lp_org_id;
136
137 CURSOR csr_get_person_name(lp_person_id number,lp_effective_date date) IS
138 select full_name from per_all_people_f
139 where person_id = lp_person_id
140 and lp_effective_date between effective_start_date and effective_end_date;
141
142 CURSOR csr_org_glb_address(p_bg_id NUMBER, p_org_id NUMBER) IS
143 select hlc.loc_information14 house_number,
144 hlc.loc_information15 house_no_add,
145 hr_general.decode_lookup('NL_REGION',hlc.region_1) street_name,
146 hlc.address_line_1 address_line1,
147 hlc.address_line_2 address_line2,
148 hlc.address_line_3 address_line3,
149 hlc.postal_code postcode,
150 hlc.town_or_city city,
151 pay_nl_general.get_country_name(hlc.country) country,
152 hlc.style add_style
153 from hr_locations hlc,
154 hr_organization_units hou
155 where hou.business_group_id = p_bg_id
156 and hou.organization_id = p_org_id
157 and hlc.location_id = hou.location_id;
158
159 CURSOR csr_emp_glb_address(p_person_id NUMBER, p_effective_date DATE) IS
160 select pad.add_information13 house_number,
161 pad.add_information14 house_no_add,
162 hr_general.decode_lookup('NL_REGION',pad.region_1) street_name,
163 pad.address_line1 address_line1,
164 pad.address_line2 address_line2,
165 pad.address_line3 address_line3,
166 pad.postal_code postcode,
167 pad.town_or_city city,
168 pay_nl_general.get_country_name(pad.country) country,
169 pad.style add_style
170 from per_addresses pad
171 where pad.person_id = p_person_id
172 and p_effective_date between pad.date_from and nvl(pad.date_to,hr_general.end_of_time)
173 and pad.primary_flag = 'Y';
174
175 CURSOR csr_get_leg_employer(p_assignment_id NUMBER, p_tax_year_start_date DATE, p_tax_year_end_date DATE) IS
176 select hou.organization_id leg_emp_id,
177 hoi.org_information1 leg_tax_ref
178 from hr_organization_units hou,
179 hr_organization_information hoi,
180 hr_organization_information hoi1,
181 per_all_assignments_f paa
182 where paa.assignment_id = p_assignment_id
183 and hou.organization_id = nvl(paa.establishment_id,-1)
184 and hoi.organization_id = hou.organization_id
185 and hoi1.organization_id = hou.organization_id
186 and hoi1.org_information_context = 'CLASS'
187 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
188 and hoi1.org_information2 = 'Y'
189 and hoi.org_information_context = 'NL_LE_TAX_DETAILS'
190 and hoi.org_information1 IS NOT NULL
191 and hoi.org_information2 IS NOT NULL
192 and paa.effective_start_date =
193 (
194 SELECT MIN(asg.effective_start_date)
195 FROM per_all_assignments_f asg
196 WHERE asg.assignment_id = paa.assignment_id
197 and asg.effective_start_date <= p_tax_year_end_date
198 and nvl(asg.effective_end_date, p_tax_year_end_date) >= p_tax_year_start_date
199
200 );
201
202
203 l_output_fname varchar2(1000);
204 l_return_value number;
205 l_effective_date date;
206 l_tax_year_start_date date;
207 l_employee varchar2(255);
208 l_Employer_Name varchar2(255);
209 l_Legal_Employer_Name varchar2(255);
210 l_business_group_name varchar2(240);
211 -- l_Organization_Hierarchy_Name varchar2(255);
212 l_addr1_er varchar2(500) := null;
213 l_addr1_ee varchar2(500) := null;
214 l_addr2_er varchar2(500) := null;
215 l_addr2_ee varchar2(500) := null;
216 l_house_number_er varchar2(255);
217 l_house_add_no_er varchar2(255);
218 l_street_name_er varchar2(255);
219 l_line1_er varchar2(255);
220 l_line2_er varchar2(255);
221 l_line3_er varchar2(255);
222 l_city_er varchar2(255);
223 l_country_er varchar2(255);
224 l_po_code_er varchar2(255);
225 l_add_style_er varchar2(255);
226 l_add_style_ee varchar2(255);
227 l_return_value1 number;
228 l_house_number_ee varchar2(255);
229 l_house_add_no_ee varchar2(255);
230 l_street_name_ee varchar2(255);
231 l_line1_ee varchar2(255);
232 l_line2_ee varchar2(255);
233 l_line3_ee varchar2(255);
234 l_city_ee varchar2(255);
235 l_country_ee varchar2(255);
236 l_po_code_ee varchar2(255);
237 l_person_id number;
238 l_previous_person_id number;
239 l_date1 varchar2(20);
240 l_date2 varchar2(20);
241 l_date3 varchar2(20);
242 l_wtd1 varchar2(20);
243 l_wtd2 varchar2(20);
244 l_wtd3 varchar2(20);
245 l_year varchar2(20);
246 l_year_msg varchar2(255);
247 l_field_count number;
248 l_yes varchar2(20);
249 l_no varchar2(20);
250 l_taxable_income number;
251 l_deducted_wage_tax number;
252 l_labour_tax_reduction number;
253 l_ZVW number;
254 l_ZVW_Basis number;
255 l_Private_Use_Car number;
256 l_Net_Expense_Allowance number;
257 l_Value_Private_Use_Car number;
258 l_Saved_Amount_LSS number;
259 l_Employer_Child_Care number;
260 l_Allowance_on_Disability number;
261 l_Applied_LCLD number;
262 l_agg_flag varchar2(20);
263 l_leg_emp_id number := null;
264 l_leg_tax_ref varchar2(100) := null;
265 l_period_of_service varchar2(100) := null;
266 l_prev_period_of_service varchar2(100) := null;
267 l_archive_action number;
268 lCtr number;
269
270
271
272 /*Make calls to suppoting procedures to form the XML file*/
273
274 begin
275
276 --hr_utility.trace_on(NULL,'ATS_TAB');
277 --hr_utility.set_location('inside ATS',10);
278 l_archive_action:=0;
279 l_taxable_income:=0;
280 l_deducted_wage_tax:=0;
281 l_labour_tax_reduction:=0;
282 l_ZVW:=0;
283 l_ZVW_Basis:=0;
284 l_Private_Use_Car:=0;
285 l_Net_Expense_Allowance:=0;
286 l_Value_Private_Use_Car:=0;
287 l_Saved_Amount_LSS:=0;
288 l_Employer_Child_Care:=0;
289 l_Allowance_on_Disability:=0;
290 l_Applied_LCLD:=0;
291 vUserBalVal.DELETE;
292 l_previous_person_id:=-1;
293
294 l_effective_date := fnd_date.canonical_to_date(p_year);
295 l_year := to_char(l_effective_date,'YYYY');
296 l_tax_year_start_date := to_date('01-01-'||l_year,'DD-MM-YYYY');
297 l_yes :=hr_general.decode_lookup('HR_NL_YES_NO','Y');
298 l_no :=hr_general.decode_lookup('HR_NL_YES_NO','N');
299
300 PAY_NL_TAXOFFICE_ARCHIVE.populate_UserBal(p_bg_id,l_effective_date);
301 hr_utility.set_location('Table populated, count-'||PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable.COUNT,10);
302
303 if p_agg_flag = 'Y' THEN
304 l_agg_flag := hr_general.decode_lookup('HR_NL_YES_NO','Y');
305 else
306 l_agg_flag := hr_general.decode_lookup('HR_NL_YES_NO','N');
307 end if;
308
309 BEGIN
310
311 select max(ppa.payroll_action_id)
312 into l_archive_action
313 from pay_payroll_actions ppa
314 where ppa.report_qualifier='NL'
315 and ppa.business_group_id=p_bg_id
316 and ppa.report_type='NL_TAXOFFICE_ARCHIVE'
317 and ppa.report_category='ARCHIVE'
318 and pay_nl_taxoffice_archive.get_parameter(ppa.legislative_parameters,'EMPLOYER_ID')=to_char(p_employer_id)
319 and effective_date between l_tax_year_start_date and l_effective_date;
320
321 EXCEPTION
322
323 WHEN NO_DATA_FOUND
324 THEN l_archive_action := 0;
325
326 WHEN OTHERS
327 THEN l_archive_action := 0;
328
329 END;
330
331 OPEN csr_get_org_name(p_bg_id);
332 FETCH csr_get_org_name into l_business_group_name;
333 CLOSE csr_get_org_name;
334
335
336 OPEN csr_get_org_name(p_employer_id);
337 FETCH csr_get_org_name into l_Employer_Name;
338 CLOSE csr_get_org_name;
339
340 /*OPEN csr_org_glb_address(p_bg_id, p_employer_id);
341 FETCH csr_org_glb_address
342 INTO l_house_number_er,
343 l_house_add_no_er,
344 l_street_name_er,
345 l_line1_er,
346 l_line2_er,
347 l_line3_er,
348 l_po_code_er,
349 l_city_er,
350 l_country_er,
351 l_add_style_er;
352 CLOSE csr_org_glb_address;
353
354 IF l_add_style_er = 'NL' THEN
355
356 l_return_value := pay_nl_general.get_org_address(p_employer_id
357 ,p_bg_id
358 ,l_house_number_er
359 ,l_house_add_no_er
360 ,l_street_name_er
361 ,l_line1_er
362 ,l_line2_er
363 ,l_line3_er
364 ,l_city_er
365 ,l_country_er
366 ,l_po_code_er);
367
368 END IF;
369
370 IF l_street_name_er is not NULL THEN
371 l_addr1_er := l_street_name_er;
372 END IF;
373
374 IF l_house_number_er is not NULL THEN
375 IF l_addr1_er is not NULL THEN
376 l_addr1_er := l_addr1_er||', '||l_house_number_er;
377 ELSE
378 l_addr1_er := l_house_number_er;
379 END IF;
380 END IF;
381
382 IF l_house_add_no_er is not NULL THEN
383 IF l_addr1_er is not NULL THEN
384 l_addr1_er := l_addr1_er||', '||l_house_add_no_er;
385 ELSE
386 l_addr1_er := l_house_add_no_er;
387 END IF;
388 END IF;
389
390
391 IF l_po_code_er is not NULL THEN
392 l_addr2_er := l_po_code_er;
393 END IF;
394
395 IF l_city_er is not NULL THEN
396 IF l_addr2_er is not NULL THEN
397 l_addr2_er := l_addr2_er||', '||l_city_er;
398 ELSE
399 l_addr2_er := l_city_er;
400 END IF;
401 END IF;*/
402
403
404 if p_person_id is not null then
405 OPEN csr_get_person_name(p_person_id,l_effective_date);
406 FETCH csr_get_person_name into l_employee;
407 CLOSE csr_get_person_name;
408 end if;
409
410
411 /* OPEN csr_org_hierarchy_name(p_bg_id,l_effective_date);
412 FETCH csr_org_hierarchy_name INTO l_Organization_Hierarchy_Name;
413 CLOSE csr_org_hierarchy_name;*/
414
415 PAY_NL_XDO_REPORT.vXMLTable.DELETE;
416 vCtr := 0;
417
418 /*Get all the XML tags and values*/
419
420 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date_Header';
421 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_effective_date,'DD/MM/YYYY');
422 vCtr := vCtr + 1;
423 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Year_Header';
424 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_effective_date,'YYYY');
425 vCtr := vCtr + 1;
426 -- PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Org_Hierarchy_Header';
427 -- PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Organization_Hierarchy_Name;
428 -- vCtr := vCtr + 1;
429 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Header';
430 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Employer_Name;
431 vCtr := vCtr + 1;
432 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employee_Header';
433 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Employee;
434 vCtr := vCtr + 1;
435 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Business_Group_Header';
436 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_business_group_name;
437 vCtr := vCtr+1;
438 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Aggregate_Flag';
439 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_agg_flag;
440 vCtr := vCtr+1;
441
442 for info_rec in get_infos(l_effective_date,l_tax_year_start_date,l_yes,l_no,l_archive_action)
443 LOOP
444 l_person_id:=info_rec.Person_Id;
445 IF info_rec.Date_End > l_tax_year_start_date THEN
446 l_period_of_service := info_rec.period_of_service;
447 ELSE
448 l_period_of_service := to_char(info_rec.date_start,'DD/MM/YYYY')||' - '||to_char(info_rec.date_end,'DD/MM/YYYY');
449 END IF;
450
451 OPEN csr_get_leg_employer(info_rec.Assignment_Id, l_tax_year_start_date, l_effective_date);
452 FETCH csr_get_leg_employer INTO l_leg_emp_id, l_leg_tax_ref;
453 IF csr_get_leg_employer%NOTFOUND THEN
454 l_leg_emp_id := null;
455 l_leg_tax_ref := null;
456 l_Legal_Employer_Name := null;
457 END IF;
458 CLOSE csr_get_leg_employer;
459
460 IF l_leg_emp_id is not NULL and l_leg_tax_ref is not NULL THEN
461
462 OPEN csr_get_org_name(l_leg_emp_id);
463 FETCH csr_get_org_name into l_Legal_Employer_Name;
464 CLOSE csr_get_org_name;
465
466 OPEN csr_org_glb_address(p_bg_id, l_leg_emp_id);
467 FETCH csr_org_glb_address
468 INTO l_house_number_er,
469 l_house_add_no_er,
470 l_street_name_er,
471 l_line1_er,
472 l_line2_er,
473 l_line3_er,
474 l_po_code_er,
475 l_city_er,
476 l_country_er,
477 l_add_style_er;
478 CLOSE csr_org_glb_address;
479
480 IF l_add_style_er = 'NL' THEN
481
482 l_return_value := pay_nl_general.get_org_address(l_leg_emp_id
483 ,p_bg_id
484 ,l_house_number_er
485 ,l_house_add_no_er
486 ,l_street_name_er
487 ,l_line1_er
488 ,l_line2_er
489 ,l_line3_er
490 ,l_city_er
491 ,l_country_er
492 ,l_po_code_er);
493
494 END IF;
495
496 IF l_street_name_er is not NULL THEN
497 l_addr1_er := l_street_name_er;
498 END IF;
499
500 IF l_house_number_er is not NULL THEN
501 IF l_addr1_er is not NULL THEN
502 l_addr1_er := l_addr1_er||' '||l_house_number_er;
503 ELSE
504 l_addr1_er := l_house_number_er;
505 END IF;
506 END IF;
507
508 IF l_house_add_no_er is not NULL THEN
509 IF l_addr1_er is not NULL THEN
510 l_addr1_er := l_addr1_er||' '||l_house_add_no_er;
511 ELSE
512 l_addr1_er := l_house_add_no_er;
513 END IF;
514 END IF;
515
516
517 IF l_po_code_er is not NULL THEN
518 l_addr2_er := l_po_code_er;
519 END IF;
520
521 IF l_city_er is not NULL THEN
522 IF l_addr2_er is not NULL THEN
523 l_addr2_er := l_addr2_er||' '||l_city_er;
524 ELSE
525 l_addr2_er := l_city_er;
526 END IF;
527 END IF;
528
529 ELSE
530
531 /*OPEN csr_get_org_name(p_employer_id);
532 FETCH csr_get_org_name into l_Legal_Employer_Name;
533 CLOSE csr_get_org_name;*/
534
535 OPEN csr_org_glb_address(p_bg_id, p_employer_id);
536 FETCH csr_org_glb_address
537 INTO l_house_number_er,
538 l_house_add_no_er,
539 l_street_name_er,
540 l_line1_er,
541 l_line2_er,
542 l_line3_er,
543 l_po_code_er,
544 l_city_er,
545 l_country_er,
546 l_add_style_er;
547 CLOSE csr_org_glb_address;
548
549 IF l_add_style_er = 'NL' THEN
550
551 l_return_value := pay_nl_general.get_org_address(p_employer_id
552 ,p_bg_id
553 ,l_house_number_er
554 ,l_house_add_no_er
555 ,l_street_name_er
556 ,l_line1_er
557 ,l_line2_er
558 ,l_line3_er
559 ,l_city_er
560 ,l_country_er
561 ,l_po_code_er);
562
563 END IF;
564
565 IF l_street_name_er is not NULL THEN
566 l_addr1_er := l_street_name_er;
567 END IF;
568
569 IF l_house_number_er is not NULL THEN
570 IF l_addr1_er is not NULL THEN
571 l_addr1_er := l_addr1_er||' '||l_house_number_er;
572 ELSE
573 l_addr1_er := l_house_number_er;
574 END IF;
575 END IF;
576
577 IF l_house_add_no_er is not NULL THEN
578 IF l_addr1_er is not NULL THEN
579 l_addr1_er := l_addr1_er||' '||l_house_add_no_er;
580 ELSE
581 l_addr1_er := l_house_add_no_er;
582 END IF;
583 END IF;
584
585
586 IF l_po_code_er is not NULL THEN
587 l_addr2_er := l_po_code_er;
588 END IF;
589
590 IF l_city_er is not NULL THEN
591 IF l_addr2_er is not NULL THEN
592 l_addr2_er := l_addr2_er||' '||l_city_er;
593 ELSE
594 l_addr2_er := l_city_er;
595 END IF;
596 END IF;
597
598
599 END IF;
600
601 if p_agg_flag = 'Y' then
602
603 if l_person_id = l_previous_person_id AND l_period_of_service = l_prev_period_of_service then
604
605 l_taxable_income:=fnd_number.canonical_to_number(info_rec.taxable_income) + l_taxable_income;
606 l_deducted_wage_tax:=fnd_number.canonical_to_number(info_rec.deducted_wage_tax) + l_deducted_wage_tax;
607 l_labour_tax_reduction:=fnd_number.canonical_to_number(info_rec.labour_tax_reduction) + l_labour_tax_reduction;
608 l_ZVW:=fnd_number.canonical_to_number(info_rec.ZVW_Cont) + l_ZVW;
609 l_ZVW_Basis:=fnd_number.canonical_to_number(info_rec.ZVW_Basis) + l_ZVW_Basis;
610 l_Private_Use_Car:=fnd_number.canonical_to_number(info_rec.Private_Use_Car) + l_Private_Use_Car;
611 l_Net_Expense_Allowance:=fnd_number.canonical_to_number(info_rec.Net_Expense_Allowance) + l_Net_Expense_Allowance;
612 l_Value_Private_Use_Car:=fnd_number.canonical_to_number(info_rec.Value_Private_Use_Car) + l_Value_Private_Use_Car;
613 l_Saved_Amount_LSS:=fnd_number.canonical_to_number(info_rec.Saved_Amount_LSS) + l_Saved_Amount_LSS;
614 l_Employer_Child_Care:=fnd_number.canonical_to_number(info_rec.Employer_Child_Care) + l_Employer_Child_Care;
615 l_Allowance_on_Disability:=fnd_number.canonical_to_number(info_rec.Allowance_on_Disability) + l_Allowance_on_Disability;
616 l_Applied_LCLD:=fnd_number.canonical_to_number(info_rec.Applied_LCLD) + l_Applied_LCLD;
617 populate_UserBalVal(info_rec.User_Bal_String,'Y');
618
619 end if;
620
621
622 if l_person_id <> l_previous_person_id OR l_period_of_service <> l_prev_period_of_service then
623
624 if l_previous_person_id <> -1 then
625
626 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Taxable_Income';
627 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Taxable_Income; --11886537
628 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := floor(l_Taxable_Income); --11886537
629 vCtr := vCtr + 1;
630
631 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Deducted_Wage_Tax';
632 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Deducted_Wage_Tax; --11886537
633 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(l_Deducted_Wage_Tax); --11886537
634 vCtr := vCtr + 1;
635
636 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Labour_Tax_Reduction';
637 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Labour_Tax_Reduction; --11886537
638 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(l_Labour_Tax_Reduction); --11886537
639 vCtr := vCtr + 1;
640
641 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_for_ZVW';
642 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_ZVW_Basis; --11886537
643 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := floor(l_ZVW_Basis); --11886537
644 vCtr := vCtr + 1;
645
646 /*hr_utility.set_location('Tag name: '||PAY_NL_XDO_REPORT.vXMLTable(vCtr-1).TagName,100);
647 hr_utility.set_location('Tag name: '||PAY_NL_XDO_REPORT.vXMLTable(vCtr-1).TagValue,110);*/
648
649 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ZVW_Cont';
650 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_ZVW; --11886537
651 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(l_ZVW); --11886537
652 vCtr := vCtr + 1;
653
654 /*hr_utility.set_location('Tag name: '||PAY_NL_XDO_REPORT.vXMLTable(vCtr-1).TagName,100);
655 hr_utility.set_location('Tag name: '||PAY_NL_XDO_REPORT.vXMLTable(vCtr-1).TagValue,110);*/
656
657
658 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Private_Use_Car';
659 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Private_Use_Car;
660 vCtr := vCtr + 1;
661
662 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Net_Expense_Allowance';
663 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Net_Expense_Allowance;
664 vCtr := vCtr + 1;
665
666 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Value_Private_Use_Car';
667 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Value_Private_Use_Car;
668 vCtr := vCtr + 1;
669
670 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Saved_Amount_LSS';
671 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Saved_Amount_LSS;
672 vCtr := vCtr + 1;
673
674 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Child_Care';
675 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Employer_Child_Care;
676 vCtr := vCtr + 1;
677
678 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Allowance_on_Disability';
679 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Allowance_on_Disability;
680 vCtr := vCtr + 1;
681
682 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Applied_LCLD';
683 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Applied_LCLD;
684 vCtr := vCtr + 1;
685
686 IF vUserBalVal.COUNT > 0 THEN
687
688 FOR lCtr IN vUserBalVal.FIRST .. vUserBalVal.LAST LOOP
689
690 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := vUserBalVal(lCtr).TagName;
691 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := vUserBalVal(lCtr).BalValue;
692 vCtr := vCtr + 1;
693
694 END LOOP;
695
696 END IF;
697
698 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
699 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
700 vCtr := vCtr + 1;
701
702 l_taxable_income:=0;
703 l_deducted_wage_tax:=0;
704 l_labour_tax_reduction:=0;
705 l_ZVW:=0;
706 l_ZVW_Basis:=0;
707 l_Private_Use_Car:=0;
708 l_Net_Expense_Allowance:=0;
709 l_Value_Private_Use_Car:=0;
710 l_Saved_Amount_LSS:=0;
711 l_Employer_Child_Care:=0;
712 l_Allowance_on_Disability:=0;
713 l_Applied_LCLD:=0;
714 vUserBalVal.DELETE;
715
716 end if;
717
718 l_taxable_income:=fnd_number.canonical_to_number(info_rec.taxable_income);
719 l_deducted_wage_tax:=fnd_number.canonical_to_number(info_rec.deducted_wage_tax);
720 l_labour_tax_reduction:=fnd_number.canonical_to_number(info_rec.labour_tax_reduction);
721 l_ZVW:=fnd_number.canonical_to_number(info_rec.ZVW_Cont);
722 l_ZVW_Basis:=fnd_number.canonical_to_number(info_rec.ZVW_Basis);
723 l_Private_Use_Car:=fnd_number.canonical_to_number(info_rec.Private_Use_Car);
724 l_Net_Expense_Allowance:=fnd_number.canonical_to_number(info_rec.Net_Expense_Allowance);
725 l_Value_Private_Use_Car:=fnd_number.canonical_to_number(info_rec.Value_Private_Use_Car);
726 l_Saved_Amount_LSS:=fnd_number.canonical_to_number(info_rec.Saved_Amount_LSS);
727 l_Employer_Child_Care:=fnd_number.canonical_to_number(info_rec.Employer_Child_Care);
728 l_Allowance_on_Disability:=fnd_number.canonical_to_number(info_rec.Allowance_on_Disability);
729 l_Applied_LCLD:=fnd_number.canonical_to_number(info_rec.Applied_LCLD);
730 populate_UserBalVal(info_rec.User_Bal_String,'N');
731
732
733
734 l_date1:=info_rec.Date1;
735 l_date2:=info_rec.Date2;
736 l_date3:=info_rec.Date3;
737 l_wtd1:=info_rec.Wage_Tax_Discount1;
738 l_wtd2:=info_rec.Wage_Tax_Discount2;
739 l_wtd3:=info_rec.Wage_Tax_Discount3;
740 l_field_count:=1;
741
742 if l_date1 like '%00/%' then
743 l_date1:=null;
744 l_wtd1:=null;
745 end if;
746
747 if l_date2 like '%00/%' then
748 l_date2:=null;
749 l_wtd2:=null;
750 end if;
751
752 if l_date3 like '%00/%' then
753 l_date3:=null;
754 l_wtd3:=null;
755 end if;
756
757 fnd_message.set_name('PAY','PAY_NL_ATS_YEAR');
758 fnd_message.set_token('YEAR',l_year);
759 l_year_msg:=fnd_message.get();
760
761 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
762 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
763 vCtr := vCtr + 1;
764
765 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Year_Detail';
766 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_year_msg;
767 vCtr := vCtr + 1;
768
769 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Name';
770 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := nvl(l_Legal_Employer_Name,info_rec.Employer_Name);
771 vCtr := vCtr + 1;
772
773
774 /*if l_street_name_er is not null then
775 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
776 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_street_name_er||']]>';
777 l_field_count:=l_field_count+1;
778 vCtr := vCtr + 1;
779 end if;
780
781 if l_house_number_er is not null then
782 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
783 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_number_er||']]>';
784 l_field_count:=l_field_count+1;
785 vCtr := vCtr + 1;
786 end if;
787
788 if l_house_add_no_er is not null then
789 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
790 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_add_no_er||']]>';
791 l_field_count:=l_field_count+1;
792 vCtr := vCtr + 1;
793 end if;*/
794
795
796 if l_addr1_er is not null then
797 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
798 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr1_er||']]>';
799 l_field_count:=l_field_count+1;
800 vCtr := vCtr + 1;
801 end if;
802
803
804 if l_line1_er is not null then
805 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
806 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line1_er||']]>';
807 l_field_count:=l_field_count+1;
808 vCtr := vCtr + 1;
809 end if;
810
811
812 if l_line2_er is not null then
813 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
814 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line2_er||']]>';
815 l_field_count:=l_field_count+1;
816 vCtr := vCtr + 1;
817 end if;
818
819 if l_line3_er is not null then
820 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
821 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line3_er||']]>';
822 l_field_count:=l_field_count+1;
823 vCtr := vCtr + 1;
824 end if;
825
826
827 /*if l_po_code_er is not null then
828 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
829 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_po_code_er||']]>';
830 l_field_count:=l_field_count+1;
831 vCtr := vCtr + 1;
832 end if;
833
834
835 if l_city_er is not null then
836 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
837 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_city_er||']]>';
838 l_field_count:=l_field_count+1;
839 vCtr := vCtr + 1;
840 end if;*/
841
842
843 if l_addr2_er is not null then
844 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
845 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr2_er||']]>';
846 l_field_count:=l_field_count+1;
847 vCtr := vCtr + 1;
848 end if;
849
850
851 if l_country_er is not null then
852 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
853 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_country_er||']]>';
854 l_field_count:=l_field_count+1;
855 vCtr := vCtr + 1;
856 end if;
857
858
859 l_field_count:=1;
860
861
862 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employee_Name';
863 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Employee_Name;
864 vCtr := vCtr + 1;
865
866 OPEN csr_emp_glb_address(l_person_id, fnd_date.canonical_to_date(p_year));
867 FETCH csr_emp_glb_address
868 INTO l_house_number_ee,
869 l_house_add_no_ee,
870 l_street_name_ee,
871 l_line1_ee,
872 l_line2_ee,
873 l_line3_ee,
874 l_po_code_ee,
875 l_city_ee,
876 l_country_ee,
877 l_add_style_ee;
878 CLOSE csr_emp_glb_address;
879
880 IF l_add_style_ee = 'NL' THEN
881
882 l_return_value1 := pay_nl_general.get_emp_address(l_person_id
883 ,fnd_date.canonical_to_date(p_year)
884 ,l_house_number_ee
885 ,l_house_add_no_ee
886 ,l_street_name_ee
887 ,l_line1_ee
888 ,l_line2_ee
889 ,l_line3_ee
890 ,l_city_ee
891 ,l_country_ee
892 ,l_po_code_ee
893 );
894
895 END IF;
896
897
898 IF l_street_name_ee is not NULL THEN
899 l_addr1_ee := l_street_name_ee;
900 END IF;
901
902 IF l_house_number_ee is not NULL THEN
903 IF l_addr1_ee is not NULL THEN
904 l_addr1_ee := l_addr1_ee||' '||l_house_number_ee;
905 ELSE
906 l_addr1_ee := l_house_number_ee;
907 END IF;
908 END IF;
909
910 IF l_house_add_no_ee is not NULL THEN
911 IF l_addr1_ee is not NULL THEN
912 l_addr1_ee := l_addr1_ee||' '||l_house_add_no_ee;
913 ELSE
914 l_addr1_ee := l_house_add_no_ee;
915 END IF;
916 END IF;
917
918
919 IF l_po_code_ee is not NULL THEN
920 l_addr2_ee := l_po_code_ee;
921 END IF;
922
923 IF l_city_ee is not NULL THEN
924 IF l_addr2_ee is not NULL THEN
925 l_addr2_ee := l_addr2_ee||' '||l_city_ee;
926 ELSE
927 l_addr2_ee := l_city_ee;
928 END IF;
929 END IF;
930
931
932
933 /*if l_street_name_ee is not null then
934 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
935 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_street_name_ee||']]>';
936 l_field_count:=l_field_count+1;
937 vCtr := vCtr + 1;
938 end if;
939
940
941 if l_house_number_ee is not null then
942 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
943 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_number_ee||']]>';
944 l_field_count:=l_field_count+1;
945 vCtr := vCtr + 1;
946 end if;
947
948 if l_house_add_no_ee is not null then
949 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
950 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_add_no_ee||']]>';
951 l_field_count:=l_field_count+1;
952 vCtr := vCtr + 1;
953 end if;*/
954
955
956 if l_addr1_ee is not null then
957 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
958 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr1_ee||']]>';
959 l_field_count:=l_field_count+1;
960 vCtr := vCtr + 1;
961 end if;
962
963
964 if l_line1_ee is not null then
965 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
966 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line1_ee||']]>';
967 l_field_count:=l_field_count+1;
968 vCtr := vCtr + 1;
969 end if;
970
971
972 if l_line2_ee is not null then
973 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
974 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line2_ee||']]>';
975 l_field_count:=l_field_count+1;
976 vCtr := vCtr + 1;
977 end if;
978
979
980 if l_line3_ee is not null then
981 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
982 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line3_ee||']]>';
983 l_field_count:=l_field_count+1;
984 vCtr := vCtr + 1;
985 end if;
986
987
988 /*if l_po_code_ee is not null then
989 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
990 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_po_code_ee||']]>';
991 l_field_count:=l_field_count+1;
992 vCtr := vCtr + 1;
993 end if;
994
995
996 if l_city_ee is not null then
997 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
998 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_city_ee||']]>';
999 l_field_count:=l_field_count+1;
1000 vCtr := vCtr + 1;
1001 end if;*/
1002
1003
1004 if l_addr2_ee is not null then
1005 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1006 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr2_ee||']]>';
1007 l_field_count:=l_field_count+1;
1008 vCtr := vCtr + 1;
1009 end if;
1010
1011
1012 if l_country_ee is not null then
1013 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1014 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_country_ee||']]>';
1015 l_field_count:=l_field_count+1;
1016 vCtr := vCtr + 1;
1017 end if;
1018
1019
1020
1021 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Tax_Registration_Number';
1022 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||nvl(l_leg_tax_ref,info_rec.Tax_Registration_Number)||']]>';
1023 vCtr := vCtr + 1;
1024
1025 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employee_Number';
1026 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Employee_Number;
1027 vCtr := vCtr + 1;
1028
1029 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Assignment_Number';
1030 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.assignment_Number;
1031 vCtr := vCtr + 1;
1032
1033 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SOFI_Number';
1034 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.SOFI_Number;
1035 vCtr := vCtr + 1;
1036
1037 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date_Of_Birth';
1038 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Date_Of_Birth;
1039 vCtr := vCtr + 1;
1040
1041 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Period_Of_Service';
1042 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_period_of_service;
1043 vCtr := vCtr + 1;
1044
1045 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Table';
1046 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Wage_Tax_Table;
1047 vCtr := vCtr + 1;
1048
1049 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Discount1';
1050 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_wtd1;
1051 vCtr := vCtr + 1;
1052
1053 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date1';
1054 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_date1;
1055 vCtr := vCtr + 1;
1056
1057 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Discount2';
1058 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_wtd2;
1059 vCtr := vCtr + 1;
1060
1061 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date2';
1062 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_date2;
1063 vCtr := vCtr + 1;
1064
1065 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Discount3';
1066 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_wtd3;
1067 vCtr := vCtr + 1;
1068
1069 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date3';
1070 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_date3;
1071 vCtr := vCtr + 1;
1072
1073 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Insured_for_WAO';
1074 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Insured_for_WAO;
1075 vCtr := vCtr + 1;
1076
1077 /*PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Insured_for_ZFW';
1078 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Insured_for_ZFW;
1079 vCtr := vCtr + 1;*/
1080
1081 /*PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Company_Car';
1082 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Company_Car;
1083 vCtr := vCtr + 1;*/
1084
1085 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Code_Reason_No';
1086 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := hr_general.decode_lookup('NL_COMPANY_CAR_USAGE_CODE',info_rec.Company_Car);
1087 vCtr := vCtr + 1;
1088
1089 end if;
1090
1091
1092 else
1093
1094 l_date1:=info_rec.Date1;
1095 l_date2:=info_rec.Date2;
1096 l_date3:=info_rec.Date3;
1097 l_wtd1:=info_rec.Wage_Tax_Discount1;
1098 l_wtd2:=info_rec.Wage_Tax_Discount2;
1099 l_wtd3:=info_rec.Wage_Tax_Discount3;
1100 l_field_count:=1;
1101
1102 if l_date1 like '%00/%' then
1103 l_date1:=null;
1104 l_wtd1:=null;
1105 end if;
1106
1107 if l_date2 like '%00/%' then
1108 l_date2:=null;
1109 l_wtd2:=null;
1110 end if;
1111
1112 if l_date3 like '%00/%' then
1113 l_date3:=null;
1114 l_wtd3:=null;
1115 end if;
1116
1117 fnd_message.set_name('PAY','PAY_NL_ATS_YEAR');
1118 fnd_message.set_token('YEAR',l_year);
1119 l_year_msg:=fnd_message.get();
1120
1121 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
1122 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
1123 vCtr := vCtr + 1;
1124
1125 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Year_Detail';
1126 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_year_msg;
1127 vCtr := vCtr + 1;
1128
1129 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Name';
1130 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := nvl(l_Legal_Employer_Name,info_rec.Employer_Name);
1131 vCtr := vCtr + 1;
1132
1133
1134 /*if l_street_name_er is not null then
1135 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
1136 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_street_name_er||']]>';
1137 l_field_count:=l_field_count+1;
1138 vCtr := vCtr + 1;
1139 end if;
1140
1141 if l_house_number_er is not null then
1142 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
1143 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_number_er||']]>';
1144 l_field_count:=l_field_count+1;
1145 vCtr := vCtr + 1;
1146 end if;
1147
1148 if l_house_add_no_er is not null then
1149 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
1150 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_add_no_er||']]>';
1151 l_field_count:=l_field_count+1;
1152 vCtr := vCtr + 1;
1153 end if;*/
1154
1155
1156 if l_addr1_er is not null then
1157 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
1158 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr1_er||']]>';
1159 l_field_count:=l_field_count+1;
1160 vCtr := vCtr + 1;
1161 end if;
1162
1163
1164 if l_line1_er is not null then
1165 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
1166 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line1_er||']]>';
1167 l_field_count:=l_field_count+1;
1168 vCtr := vCtr + 1;
1169 end if;
1170
1171
1172
1173 if l_line2_er is not null then
1174 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
1175 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line2_er||']]>';
1176 l_field_count:=l_field_count+1;
1177 vCtr := vCtr + 1;
1178 end if;
1179
1180 if l_line3_er is not null then
1181 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
1182 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line3_er||']]>';
1183 l_field_count:=l_field_count+1;
1184 vCtr := vCtr + 1;
1185 end if;
1186
1187
1188 if l_addr2_er is not null then
1189 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
1190 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr2_er||']]>';
1191 l_field_count:=l_field_count+1;
1192 vCtr := vCtr + 1;
1193 end if;
1194
1195
1196 /*if l_po_code_er is not null then
1197 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
1198 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_po_code_er||']]>';
1199 l_field_count:=l_field_count+1;
1200 vCtr := vCtr + 1;
1201 end if;
1202
1203 if l_city_er is not null then
1204 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
1205 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_city_er||']]>';
1206 l_field_count:=l_field_count+1;
1207 vCtr := vCtr + 1;
1208 end if;*/
1209
1210
1211 if l_country_er is not null then
1212 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
1213 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_country_er||']]>';
1214 l_field_count:=l_field_count+1;
1215 vCtr := vCtr + 1;
1216 end if;
1217
1218
1219
1220 l_field_count:=1;
1221
1222
1223 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employee_Name';
1224 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Employee_Name;
1225 vCtr := vCtr + 1;
1226
1227 OPEN csr_emp_glb_address(l_person_id, fnd_date.canonical_to_date(p_year));
1228 FETCH csr_emp_glb_address
1229 INTO l_house_number_ee,
1230 l_house_add_no_ee,
1231 l_street_name_ee,
1232 l_line1_ee,
1233 l_line2_ee,
1234 l_line3_ee,
1235 l_po_code_ee,
1236 l_city_ee,
1237 l_country_ee,
1238 l_add_style_ee;
1239 CLOSE csr_emp_glb_address;
1240
1241 IF l_add_style_ee = 'NL' THEN
1242
1243 l_return_value1 := pay_nl_general.get_emp_address(l_person_id
1244 ,fnd_date.canonical_to_date(p_year)
1245 ,l_house_number_ee
1246 ,l_house_add_no_ee
1247 ,l_street_name_ee
1248 ,l_line1_ee
1249 ,l_line2_ee
1250 ,l_line3_ee
1251 ,l_city_ee
1252 ,l_country_ee
1253 ,l_po_code_ee
1254 );
1255
1256 END IF;
1257
1258 IF l_street_name_ee is not NULL THEN
1259 l_addr1_ee := l_street_name_ee;
1260 END IF;
1261
1262 IF l_house_number_ee is not NULL THEN
1263 IF l_addr1_ee is not NULL THEN
1264 l_addr1_ee := l_addr1_ee||' '||l_house_number_ee;
1265 ELSE
1266 l_addr1_ee := l_house_number_ee;
1267 END IF;
1268 END IF;
1269
1270 IF l_house_add_no_ee is not NULL THEN
1271 IF l_addr1_ee is not NULL THEN
1272 l_addr1_ee := l_addr1_ee||' '||l_house_add_no_ee;
1273 ELSE
1274 l_addr1_ee := l_house_add_no_ee;
1275 END IF;
1276 END IF;
1277
1278 IF l_po_code_ee is not NULL THEN
1279 l_addr2_ee := l_po_code_ee;
1280 END IF;
1281
1282 IF l_city_ee is not NULL THEN
1283 IF l_addr2_ee is not NULL THEN
1284 l_addr2_ee := l_addr2_ee||' '||l_city_ee;
1285 ELSE
1286 l_addr2_ee := l_city_ee;
1287 END IF;
1288 END IF;
1289
1290
1291
1292 /*if l_street_name_ee is not null then
1293 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1294 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_street_name_ee||']]>';
1295 l_field_count:=l_field_count+1;
1296 vCtr := vCtr + 1;
1297 end if;
1298
1299 if l_house_number_ee is not null then
1300 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1301 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_number_ee||']]>';
1302 l_field_count:=l_field_count+1;
1303 vCtr := vCtr + 1;
1304 end if;
1305
1306 if l_house_add_no_ee is not null then
1307 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1308 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_add_no_ee||']]>';
1309 l_field_count:=l_field_count+1;
1310 vCtr := vCtr + 1;
1311 end if;*/
1312
1313
1314 if l_addr1_ee is not null then
1315 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1316 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr1_ee||']]>';
1317 l_field_count:=l_field_count+1;
1318 vCtr := vCtr + 1;
1319 end if;
1320
1321
1322 if l_line1_ee is not null then
1323 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1324 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line1_ee||']]>';
1325 l_field_count:=l_field_count+1;
1326 vCtr := vCtr + 1;
1327 end if;
1328
1329
1330 if l_line2_ee is not null then
1331 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1332 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line2_ee||']]>';
1333 l_field_count:=l_field_count+1;
1334 vCtr := vCtr + 1;
1335 end if;
1336
1337
1338 if l_line3_ee is not null then
1339 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1340 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line3_ee||']]>';
1341 l_field_count:=l_field_count+1;
1342 vCtr := vCtr + 1;
1343 end if;
1344
1345 /*if l_po_code_ee is not null then
1346 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1347 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_po_code_ee||']]>';
1348 l_field_count:=l_field_count+1;
1349 vCtr := vCtr + 1;
1350 end if;
1351
1352 if l_city_ee is not null then
1353 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1354 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_city_ee||']]>';
1355 l_field_count:=l_field_count+1;
1356 vCtr := vCtr + 1;
1357 end if;*/
1358
1359
1360 if l_addr2_ee is not null then
1361 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1362 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr2_ee||']]>';
1363 l_field_count:=l_field_count+1;
1364 vCtr := vCtr + 1;
1365 end if;
1366
1367
1368 if l_country_ee is not null then
1369 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
1370 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_country_ee||']]>';
1371 l_field_count:=l_field_count+1;
1372 vCtr := vCtr + 1;
1373 end if;
1374
1375
1376
1377 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Tax_Registration_Number';
1378 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||nvl(l_leg_tax_ref,info_rec.Tax_Registration_Number)||']]>';
1379 vCtr := vCtr + 1;
1380
1381 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employee_Number';
1382 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Employee_Number;
1383 vCtr := vCtr + 1;
1384
1385 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Assignment_Number';
1386 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.assignment_Number;
1387 vCtr := vCtr + 1;
1388
1389 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SOFI_Number';
1390 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.SOFI_Number;
1391 vCtr := vCtr + 1;
1392
1393 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date_Of_Birth';
1394 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Date_Of_Birth;
1395 vCtr := vCtr + 1;
1396
1397 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Period_Of_Service';
1398 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_period_of_service;
1399 vCtr := vCtr + 1;
1400
1401 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Table';
1402 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Wage_Tax_Table;
1403 vCtr := vCtr + 1;
1404
1405 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Discount1';
1406 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_wtd1;
1407 vCtr := vCtr + 1;
1408
1409 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date1';
1410 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_date1;
1411 vCtr := vCtr + 1;
1412
1413 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Discount2';
1414 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_wtd2;
1415 vCtr := vCtr + 1;
1416
1417 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date2';
1418 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_date2;
1419 vCtr := vCtr + 1;
1420
1421 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Discount3';
1422 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_wtd3;
1423 vCtr := vCtr + 1;
1424
1425 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date3';
1426 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_date3;
1427 vCtr := vCtr + 1;
1428
1429 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Taxable_Income';
1430 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Taxable_Income; --11886537
1431 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := floor(info_rec.Taxable_Income); --11886537
1432 vCtr := vCtr + 1;
1433
1434 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Deducted_Wage_Tax';
1435 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Deducted_Wage_Tax; --11886537
1436 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(info_rec.Deducted_Wage_Tax); --11886537
1437 vCtr := vCtr + 1;
1438
1439 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Labour_Tax_Reduction';
1440 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Labour_Tax_Reduction; --11886537
1441 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(info_rec.Labour_Tax_Reduction); --11886537
1442 vCtr := vCtr + 1;
1443
1444 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Insured_for_WAO';
1445 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Insured_for_WAO;
1446 vCtr := vCtr + 1;
1447
1448 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_for_ZVW';
1449 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.ZVW_Basis; --11886537
1450 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := floor(info_rec.ZVW_Basis); --11886537
1451 vCtr := vCtr + 1;
1452
1453 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ZVW_Cont';
1454 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.ZVW_Cont; --11886537
1455 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(info_rec.ZVW_Cont); --11886537
1456 vCtr := vCtr + 1;
1457
1458 /*PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Company_Car';
1459 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Company_Car;
1460 vCtr := vCtr + 1;*/
1461
1462 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Private_Use_Car';
1463 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Private_Use_Car;
1464 vCtr := vCtr + 1;
1465
1466 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Code_Reason_No';
1467 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := hr_general.decode_lookup('NL_COMPANY_CAR_USAGE_CODE',info_rec.Company_Car);
1468 vCtr := vCtr + 1;
1469
1470 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Value_Private_Use_Car';
1471 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Value_Private_Use_Car;
1472 vCtr := vCtr + 1;
1473
1474 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Net_Expense_Allowance';
1475 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Net_Expense_Allowance;
1476 vCtr := vCtr + 1;
1477
1478 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Saved_Amount_LSS';
1479 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Saved_Amount_LSS;
1480 vCtr := vCtr + 1;
1481
1482 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Child_Care';
1483 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Employer_Child_Care;
1484 vCtr := vCtr + 1;
1485
1486 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Allowance_on_Disability';
1487 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Allowance_on_Disability;
1488 vCtr := vCtr + 1;
1489
1490 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Applied_LCLD';
1491 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Applied_LCLD;
1492 vCtr := vCtr + 1;
1493
1494 hr_utility.set_location('Calling bal table',10);
1495
1496 populate_UserBalVal(info_rec.User_Bal_String,'N');
1497
1498 --hr_utility.set_location('FIRST-'||vUserBalVal.FIRST||' LAST-'||vUserBalVal.LAST||' COUNT-'||vUserBalVal.COUNT,20);
1499
1500 IF vUserBalVal.COUNT > 0 THEN
1501
1502 FOR lCtr IN vUserBalVal.FIRST .. vUserBalVal.LAST LOOP
1503
1504 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := vUserBalVal(lCtr).TagName;
1505 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := vUserBalVal(lCtr).BalValue;
1506 vCtr := vCtr + 1;
1507
1508 END LOOP;
1509
1510 END IF;
1511
1512 vUserBalVal.DELETE;
1513
1514 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
1515 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
1516 vCtr := vCtr + 1;
1517
1518 end if;
1519
1520
1521 if l_previous_person_id <> l_person_id OR l_prev_period_of_service <> l_period_of_service then
1522 l_previous_person_id:=l_person_id;
1523 l_prev_period_of_service:=l_period_of_service;
1524 end if;
1525
1526 l_house_number_ee := NULL;
1527 l_house_add_no_ee := NULL;
1528 l_street_name_ee := NULL;
1529 l_line1_ee := NULL;
1530 l_line2_ee := NULL;
1531 l_line3_ee := NULL;
1532 l_city_ee := NULL;
1533 l_country_ee := NULL;
1534 l_po_code_ee := NULL;
1535 l_addr1_ee := NULL;
1536 l_addr2_ee := NULL;
1537
1538
1539 end loop;
1540
1541 if l_previous_person_id <> -1 and p_agg_flag = 'Y' then
1542
1543 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Taxable_Income';
1544 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Taxable_Income; --11886537
1545 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := floor(l_Taxable_Income); --11886537
1546 vCtr := vCtr + 1;
1547
1548 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Deducted_Wage_Tax';
1549 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Deducted_Wage_Tax; --11886537
1550 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(l_Deducted_Wage_Tax); --11886537
1551 vCtr := vCtr + 1;
1552
1553 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Labour_Tax_Reduction';
1554 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Labour_Tax_Reduction; --11886537
1555 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(l_Labour_Tax_Reduction); --11886537
1556 vCtr := vCtr + 1;
1557
1558 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_for_ZVW';
1559 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_ZVW_Basis; --11886537
1560 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := floor(l_ZVW_Basis); --11886537
1561 vCtr := vCtr + 1;
1562
1563 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ZVW_Cont';
1564 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_ZVW; --11886537
1565 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(l_ZVW); --11886537
1566 vCtr := vCtr + 1;
1567
1568 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Private_Use_Car';
1569 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Private_Use_Car;
1570 vCtr := vCtr + 1;
1571
1572 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Net_Expense_Allowance';
1573 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Net_Expense_Allowance;
1574 vCtr := vCtr + 1;
1575
1576 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Value_Private_Use_Car';
1577 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Value_Private_Use_Car;
1578 vCtr := vCtr + 1;
1579
1580 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Saved_Amount_LSS';
1581 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Saved_Amount_LSS;
1582 vCtr := vCtr + 1;
1583
1584 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Child_Care';
1585 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Employer_Child_Care;
1586 vCtr := vCtr + 1;
1587
1588 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Allowance_on_Disability';
1589 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Allowance_on_Disability;
1590 vCtr := vCtr + 1;
1591
1592 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Applied_LCLD';
1593 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Applied_LCLD;
1594 vCtr := vCtr + 1;
1595
1596 IF vUserBalVal.COUNT > 0 THEN
1597
1598 FOR lCtr IN vUserBalVal.FIRST .. vUserBalVal.LAST LOOP
1599
1600 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := vUserBalVal(lCtr).TagName;
1601 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := vUserBalVal(lCtr).BalValue;
1602 vCtr := vCtr + 1;
1603
1604 --hr_utility.set_location('tag name-'||vUserBalVal(lCtr).TagName||' tag value-'||vUserBalVal(lCtr).BalValue,90);
1605
1606 END LOOP;
1607
1608 END IF;
1609
1610 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
1611 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
1612 vCtr := vCtr + 1;
1613
1614 l_taxable_income:=0;
1615 l_deducted_wage_tax:=0;
1616 l_labour_tax_reduction:=0;
1617 l_ZVW:=0;
1618 l_ZVW_Basis:=0;
1619 l_Private_Use_Car:=0;
1620 l_Net_Expense_Allowance:=0;
1621 l_Value_Private_Use_Car:=0;
1622 l_Saved_Amount_LSS:=0;
1623 l_Employer_Child_Care:=0;
1624 l_Allowance_on_Disability:=0;
1625 l_Applied_LCLD:=0;
1626 vUserBalVal.DELETE;
1627
1628 end if;
1629
1630 /*Fetch XML file as a BLOB*/
1631
1632 pay_nl_xdo_Report.WritetoCLOB_rtf (p_xfdf_blob );
1633
1634
1635 pay_nl_xdo_Report.WritetoXML_rtf (fnd_global.conc_request_id,l_output_fname);
1636
1637 end populate_ats_report_data;
1638
1639 procedure populate_ats_report_data_new
1640 (p_person_id IN NUMBER,
1641 p_year IN VARCHAR2,
1642 p_bg_id IN NUMBER,
1643 p_employer_id IN NUMBER,
1644 p_agg_flag IN VARCHAR2,
1645 p_aggregate_multi_assign IN VARCHAR2,
1646 p_employee_name IN VARCHAR2,
1647 p_top_hr_organization_name IN VARCHAR2,
1648 p_eff_date IN VARCHAR2,
1649 p_template_format IN VARCHAR2,
1650 p_template_name IN VARCHAR2,
1651 p_xml OUT NOCOPY CLOB) IS
1652
1653 /*Cursor to pick up necessary details*/
1654
1655 cursor get_infos(lp_tax_year_end_date date,lp_tax_year_start_date date,lp_yes varchar2,lp_no varchar2,lp_archive_action number) is
1656 select pap.full_name Employee_name,
1657 hou.name Employer_name,
1658 hoi.org_information4 Tax_registration_number,
1659 pap.person_id Person_Id,
1660 paa.assignment_id Assignment_Id,
1661 pap.employee_number,
1662 paa.assignment_number,
1663 to_char(pap.Date_Of_Birth,'DD/MM/YYYY') Date_Of_Birth,
1664 ppos.date_start Date_Start,
1665 nvl(ppos.actual_termination_date,lp_tax_year_end_date) Date_End,
1666 to_char(greatest(ppos.date_start,lp_tax_year_start_date),'DD/MM/YYYY')||' - '||to_char(least(nvl(ppos.actual_termination_date,lp_tax_year_end_date),lp_tax_year_end_date),'DD/MM/YYYY') Period_Of_Service,
1667 pap.national_identifier SOFI_number,
1668 DECODE(SUBSTR(pai.action_information10,2,1),1,hr_general.decode_lookup('NL_TAX_TABLE','1'),2,hr_general.decode_lookup('NL_TAX_TABLE','2'),'') Wage_Tax_Table,
1669 decode(substr(pai.action_information9,1,1),'1',lp_yes,lp_no) Wage_Tax_Discount1,
1670 '(' || substr(pai.action_information9,2,2)||'/'||substr(pai.action_information9,4,2)||'/'||to_char(lp_tax_year_end_date,'YYYY') || ')' Date1,
1671 decode(substr(pai.action_information9,6,1),'1',lp_yes,lp_no) Wage_Tax_Discount2,
1672 '(' || substr(pai.action_information9,7,2)||'/'||substr(pai.action_information9,9,2)||'/'||to_char(lp_tax_year_end_date,'YYYY') || ')' Date2,
1673 decode(substr(pai.action_information9,11,1),'1',lp_yes,lp_no) Wage_Tax_Discount3,
1674 '(' || substr(pai.action_information9,12,2)||'/'||substr(pai.action_information9,14,2)||'/'||to_char(lp_tax_year_end_date,'YYYY') || ')' Date3,
1675 --11886537 begins
1676 --NVL(pai.action_information18,pai.action_information4) Taxable_Income,
1677 --pai.action_Information5 Deducted_Wage_Tax,
1678 --pai.action_information8 Labour_Tax_Reduction,
1679 NVL(fnd_number.canonical_to_number(pai.action_information18),fnd_number.canonical_to_number(pai.action_information4)) Taxable_Income,
1680 fnd_number.canonical_to_number(pai.action_Information5) Deducted_Wage_Tax,
1681 fnd_number.canonical_to_number(pai.action_information8) Labour_Tax_Reduction,
1682 --11886537 ends
1683 decode(substr(pai.action_information14,1,1),'1',lp_yes,2,lp_yes,3,lp_yes,lp_no) Insured_For_WAO,
1684 --decode(substr(pai.action_information14,2,1),'1',lp_yes,2,lp_yes,3,lp_yes,lp_no) Insured_For_ZFW,
1685 --pai.action_information15 ZVW_Cont, --11886537
1686 fnd_number.canonical_to_number(pai.action_information15) ZVW_Cont, --11886537
1687 substr(pai.action_information12,13,1) Company_Car,
1688 --11886537 begins
1689 --pai.action_information17 Private_Use_Car,
1690 --pai.action_information16 Net_Expense_Allowance,
1691 --pai.action_information19 ZVW_Basis,
1692 --pai.action_information20 Value_Private_Use_Car,
1693 --pai.action_information21 Saved_Amount_LSS,
1694 --pai.action_information22 Employer_Child_Care,
1695 --pai.action_information23 Allowance_on_Disability,
1696 --pai.action_information24 Applied_LCLD,
1697 fnd_number.canonical_to_number(pai.action_information17) Private_Use_Car,
1698 fnd_number.canonical_to_number(pai.action_information16) Net_Expense_Allowance,
1699 fnd_number.canonical_to_number(pai.action_information19) ZVW_Basis,
1700 fnd_number.canonical_to_number(pai.action_information20) Value_Private_Use_Car,
1701 fnd_number.canonical_to_number(pai.action_information21) Saved_Amount_LSS,
1702 fnd_number.canonical_to_number(pai.action_information22) Employer_Child_Care,
1703 fnd_number.canonical_to_number(pai.action_information23) Allowance_on_Disability,
1704 fnd_number.canonical_to_number(pai.action_information24) Applied_LCLD,
1705 --11886537 ends
1706 pai.action_information25 User_Bal_String
1707 from
1708 pay_assignment_actions assact,
1709 pay_action_information pai,
1710 per_all_assignments_f paa,
1711 per_all_people_f pap,
1712 per_periods_of_service ppos,
1713 hr_organization_units hou,
1714 hr_organization_information hoi
1715 where
1716 pai.action_context_type = 'AAP'
1717 and assact.payroll_action_id = lp_archive_action
1718 and pai.action_context_id = assact.assignment_action_id
1719 and pai.action_information_category = 'NL ATS EMPLOYEE DETAILS'
1720 and hoi.org_information_context = 'NL_ORG_INFORMATION'
1721 and pap.person_id = nvl(p_person_id,pap.person_id)
1722 and ppos.person_id = pap.person_id
1723 and ppos.date_start <= lp_Tax_Year_End_Date
1724 --and nvl(ppos.actual_termination_date, lp_Tax_Year_End_Date) >= lp_Tax_Year_Start_Date
1725 and pai.action_information1 = to_char(p_employer_id)
1726 and decode(pai.action_information_category,'NL ATS EMPLOYEE DETAILS',fnd_number.canonical_to_number(pai.action_information2),null) = nvl(p_person_id,pap.person_id)
1727 and decode(pai.action_information_category,'NL ATS EMPLOYEE DETAILS',fnd_number.canonical_to_number(pai.action_information3),null) = paa.assignment_id
1728 and pai.effective_date = lp_Tax_Year_End_Date
1729 and paa.effective_start_date =
1730 (
1731 SELECT MIN(asg.effective_start_date)
1732 FROM per_all_assignments_f asg
1733 WHERE asg.assignment_id = paa.assignment_id
1734 and asg.payroll_id is not NULL
1735 and asg.effective_start_date <= least(nvl(ppos.actual_termination_date, lp_Tax_Year_End_Date), lp_Tax_Year_End_Date)
1736 and nvl(asg.effective_end_date, lp_Tax_Year_End_Date) >= ppos.date_start
1737
1738 )
1739 and lp_Tax_Year_End_Date between pap.effective_start_date and pap.effective_end_date
1740 and lp_Tax_Year_End_Date between hou.date_from and nvl(hou.date_to,hr_general.end_of_time)
1741 and paa.person_id = pap.person_id
1742 and pap.business_group_id = p_bg_id
1743 and paa.business_group_id = p_bg_id
1744 and hou.organization_id = p_employer_id
1745 and hoi.organization_id = p_employer_id
1746 order by get_Address_Style(pap.person_id,lp_tax_year_end_date) desc, get_Post_Code(pap.person_id,lp_tax_year_end_date) asc, pap.person_id asc, paa.assignment_id asc;
1747
1748 /* CURSOR csr_org_hierarchy_name(lp_business_group_id number,lp_tax_year_end_date date) IS
1749 select
1750 pos.name
1751 from
1752 per_organization_structures pos,
1753 per_org_structure_versions posv
1754 where pos.organization_structure_id = posv.organization_structure_id
1755 and to_char(pos.organization_structure_id) IN (select org_information1
1756 from hr_organization_information hoi where hoi.org_information_context='NL_BG_INFO'
1757 and hoi.organization_id=lp_business_group_id)
1758 and lp_tax_year_end_date between posv.date_from and nvl(posv.date_to,hr_general.End_of_time);*/
1759
1760 CURSOR csr_get_org_name(lp_org_id number) IS
1761 select name
1762 from hr_organization_units
1763 where organization_id = lp_org_id;
1764
1765 CURSOR csr_get_person_name(lp_person_id number,lp_effective_date date) IS
1766 select full_name from per_all_people_f
1767 where person_id = lp_person_id
1768 and lp_effective_date between effective_start_date and effective_end_date;
1769
1770 CURSOR csr_org_glb_address(p_bg_id NUMBER, p_org_id NUMBER) IS
1771 select hlc.loc_information14 house_number,
1772 hlc.loc_information15 house_no_add,
1773 hr_general.decode_lookup('NL_REGION',hlc.region_1) street_name,
1774 hlc.address_line_1 address_line1,
1775 hlc.address_line_2 address_line2,
1776 hlc.address_line_3 address_line3,
1777 hlc.postal_code postcode,
1778 hlc.town_or_city city,
1779 pay_nl_general.get_country_name(hlc.country) country,
1780 hlc.style add_style
1781 from hr_locations hlc,
1782 hr_organization_units hou
1783 where hou.business_group_id = p_bg_id
1784 and hou.organization_id = p_org_id
1785 and hlc.location_id = hou.location_id;
1786
1787 CURSOR csr_emp_glb_address(p_person_id NUMBER, p_effective_date DATE) IS
1788 select pad.add_information13 house_number,
1789 pad.add_information14 house_no_add,
1790 hr_general.decode_lookup('NL_REGION',pad.region_1) street_name,
1791 pad.address_line1 address_line1,
1792 pad.address_line2 address_line2,
1793 pad.address_line3 address_line3,
1794 pad.postal_code postcode,
1795 pad.town_or_city city,
1796 pay_nl_general.get_country_name(pad.country) country,
1797 pad.style add_style
1798 from per_addresses pad
1799 where pad.person_id = p_person_id
1800 and p_effective_date between pad.date_from and nvl(pad.date_to,hr_general.end_of_time)
1801 and pad.primary_flag = 'Y';
1802
1803 CURSOR csr_get_leg_employer(p_assignment_id NUMBER, p_tax_year_start_date DATE, p_tax_year_end_date DATE) IS
1804 select hou.organization_id leg_emp_id,
1805 hoi.org_information1 leg_tax_ref
1806 from hr_organization_units hou,
1807 hr_organization_information hoi,
1808 hr_organization_information hoi1,
1809 per_all_assignments_f paa
1810 where paa.assignment_id = p_assignment_id
1811 and hou.organization_id = nvl(paa.establishment_id,-1)
1812 and hoi.organization_id = hou.organization_id
1813 and hoi1.organization_id = hou.organization_id
1814 and hoi1.org_information_context = 'CLASS'
1815 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1816 and hoi1.org_information2 = 'Y'
1817 and hoi.org_information_context = 'NL_LE_TAX_DETAILS'
1818 and hoi.org_information1 IS NOT NULL
1819 and hoi.org_information2 IS NOT NULL
1820 and paa.effective_start_date =
1821 (
1822 SELECT MIN(asg.effective_start_date)
1823 FROM per_all_assignments_f asg
1824 WHERE asg.assignment_id = paa.assignment_id
1825 and asg.effective_start_date <= p_tax_year_end_date
1826 and nvl(asg.effective_end_date, p_tax_year_end_date) >= p_tax_year_start_date
1827
1828 );
1829
1830
1831 l_output_fname varchar2(1000);
1832 l_return_value number;
1833 l_effective_date date;
1834 l_tax_year_start_date date;
1835 l_employee varchar2(255);
1836 l_Employer_Name varchar2(255);
1837 l_Legal_Employer_Name varchar2(255);
1838 l_business_group_name varchar2(240);
1839 -- l_Organization_Hierarchy_Name varchar2(255);
1840 l_addr1_er varchar2(500) := null;
1841 l_addr1_ee varchar2(500) := null;
1842 l_addr2_er varchar2(500) := null;
1843 l_addr2_ee varchar2(500) := null;
1844 l_house_number_er varchar2(255);
1845 l_house_add_no_er varchar2(255);
1846 l_street_name_er varchar2(255);
1847 l_line1_er varchar2(255);
1848 l_line2_er varchar2(255);
1849 l_line3_er varchar2(255);
1850 l_city_er varchar2(255);
1851 l_country_er varchar2(255);
1852 l_po_code_er varchar2(255);
1853 l_add_style_er varchar2(255);
1854 l_add_style_ee varchar2(255);
1855 l_return_value1 number;
1856 l_house_number_ee varchar2(255);
1857 l_house_add_no_ee varchar2(255);
1858 l_street_name_ee varchar2(255);
1859 l_line1_ee varchar2(255);
1860 l_line2_ee varchar2(255);
1861 l_line3_ee varchar2(255);
1862 l_city_ee varchar2(255);
1863 l_country_ee varchar2(255);
1864 l_po_code_ee varchar2(255);
1865 l_person_id number;
1866 l_previous_person_id number;
1867 l_date1 varchar2(20);
1868 l_date2 varchar2(20);
1869 l_date3 varchar2(20);
1870 l_wtd1 varchar2(20);
1871 l_wtd2 varchar2(20);
1872 l_wtd3 varchar2(20);
1873 l_year varchar2(20);
1874 l_year_msg varchar2(255);
1875 l_field_count number;
1876 l_yes varchar2(20);
1877 l_no varchar2(20);
1878 l_taxable_income number;
1879 l_deducted_wage_tax number;
1880 l_labour_tax_reduction number;
1881 l_ZVW number;
1882 l_ZVW_Basis number;
1883 l_Private_Use_Car number;
1884 l_Net_Expense_Allowance number;
1885 l_Value_Private_Use_Car number;
1886 l_Saved_Amount_LSS number;
1887 l_Employer_Child_Care number;
1888 l_Allowance_on_Disability number;
1889 l_Applied_LCLD number;
1890 l_agg_flag varchar2(20);
1891 l_leg_emp_id number := null;
1892 l_leg_tax_ref varchar2(100) := null;
1893 l_period_of_service varchar2(100) := null;
1894 l_prev_period_of_service varchar2(100) := null;
1895 l_archive_action number;
1896 lCtr number;
1897
1898
1899
1900 /*Make calls to suppoting procedures to form the XML file*/
1901
1902 begin
1903
1904 --hr_utility.trace_on(NULL,'ATS_TAB');
1905 --hr_utility.set_location('inside ATS',10);
1906 l_archive_action:=0;
1907 l_taxable_income:=0;
1908 l_deducted_wage_tax:=0;
1909 l_labour_tax_reduction:=0;
1910 l_ZVW:=0;
1911 l_ZVW_Basis:=0;
1912 l_Private_Use_Car:=0;
1913 l_Net_Expense_Allowance:=0;
1914 l_Value_Private_Use_Car:=0;
1915 l_Saved_Amount_LSS:=0;
1916 l_Employer_Child_Care:=0;
1917 l_Allowance_on_Disability:=0;
1918 l_Applied_LCLD:=0;
1919 vUserBalVal.DELETE;
1920 l_previous_person_id:=-1;
1921
1922 l_effective_date := fnd_date.canonical_to_date(p_year);
1923 l_year := to_char(l_effective_date,'YYYY');
1924 l_tax_year_start_date := to_date('01-01-'||l_year,'DD-MM-YYYY');
1925 l_yes :=hr_general.decode_lookup('HR_NL_YES_NO','Y');
1926 l_no :=hr_general.decode_lookup('HR_NL_YES_NO','N');
1927
1928 PAY_NL_TAXOFFICE_ARCHIVE.populate_UserBal(p_bg_id,l_effective_date);
1929 hr_utility.set_location('Table populated, count-'||PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable.COUNT,10);
1930
1931 if p_agg_flag = 'Y' THEN
1932 l_agg_flag := hr_general.decode_lookup('HR_NL_YES_NO','Y');
1933 else
1934 l_agg_flag := hr_general.decode_lookup('HR_NL_YES_NO','N');
1935 end if;
1936
1937 BEGIN
1938
1939 select max(ppa.payroll_action_id)
1940 into l_archive_action
1941 from pay_payroll_actions ppa
1942 where ppa.report_qualifier='NL'
1943 and ppa.business_group_id=p_bg_id
1944 and ppa.report_type='NL_TAXOFFICE_ARCHIVE'
1945 and ppa.report_category='ARCHIVE'
1946 and pay_nl_taxoffice_archive.get_parameter(ppa.legislative_parameters,'EMPLOYER_ID')=to_char(p_employer_id)
1947 and effective_date between l_tax_year_start_date and l_effective_date;
1948
1949 EXCEPTION
1950
1951 WHEN NO_DATA_FOUND
1952 THEN l_archive_action := 0;
1953
1954 WHEN OTHERS
1955 THEN l_archive_action := 0;
1956
1957 END;
1958
1959 OPEN csr_get_org_name(p_bg_id);
1960 FETCH csr_get_org_name into l_business_group_name;
1961 CLOSE csr_get_org_name;
1962
1963
1964 OPEN csr_get_org_name(p_employer_id);
1965 FETCH csr_get_org_name into l_Employer_Name;
1966 CLOSE csr_get_org_name;
1967
1968 /*OPEN csr_org_glb_address(p_bg_id, p_employer_id);
1969 FETCH csr_org_glb_address
1970 INTO l_house_number_er,
1971 l_house_add_no_er,
1972 l_street_name_er,
1973 l_line1_er,
1974 l_line2_er,
1975 l_line3_er,
1976 l_po_code_er,
1977 l_city_er,
1978 l_country_er,
1979 l_add_style_er;
1980 CLOSE csr_org_glb_address;
1981
1982 IF l_add_style_er = 'NL' THEN
1983
1984 l_return_value := pay_nl_general.get_org_address(p_employer_id
1985 ,p_bg_id
1986 ,l_house_number_er
1987 ,l_house_add_no_er
1988 ,l_street_name_er
1989 ,l_line1_er
1990 ,l_line2_er
1991 ,l_line3_er
1992 ,l_city_er
1993 ,l_country_er
1994 ,l_po_code_er);
1995
1996 END IF;
1997
1998 IF l_street_name_er is not NULL THEN
1999 l_addr1_er := l_street_name_er;
2000 END IF;
2001
2002 IF l_house_number_er is not NULL THEN
2003 IF l_addr1_er is not NULL THEN
2004 l_addr1_er := l_addr1_er||', '||l_house_number_er;
2005 ELSE
2006 l_addr1_er := l_house_number_er;
2007 END IF;
2008 END IF;
2009
2010 IF l_house_add_no_er is not NULL THEN
2011 IF l_addr1_er is not NULL THEN
2012 l_addr1_er := l_addr1_er||', '||l_house_add_no_er;
2013 ELSE
2014 l_addr1_er := l_house_add_no_er;
2015 END IF;
2016 END IF;
2017
2018
2019 IF l_po_code_er is not NULL THEN
2020 l_addr2_er := l_po_code_er;
2021 END IF;
2022
2023 IF l_city_er is not NULL THEN
2024 IF l_addr2_er is not NULL THEN
2025 l_addr2_er := l_addr2_er||', '||l_city_er;
2026 ELSE
2027 l_addr2_er := l_city_er;
2028 END IF;
2029 END IF;*/
2030
2031
2032 if p_person_id is not null then
2033 OPEN csr_get_person_name(p_person_id,l_effective_date);
2034 FETCH csr_get_person_name into l_employee;
2035 CLOSE csr_get_person_name;
2036 end if;
2037
2038
2039 /* OPEN csr_org_hierarchy_name(p_bg_id,l_effective_date);
2040 FETCH csr_org_hierarchy_name INTO l_Organization_Hierarchy_Name;
2041 CLOSE csr_org_hierarchy_name;*/
2042
2043 PAY_NL_XDO_REPORT.vXMLTable.DELETE;
2044 vCtr := 0;
2045
2046 /*Get all the XML tags and values*/
2047
2048 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date_Header';
2049 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_effective_date,'DD/MM/YYYY');
2050 vCtr := vCtr + 1;
2051 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Year_Header';
2052 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_effective_date,'YYYY');
2053 vCtr := vCtr + 1;
2054 -- PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Org_Hierarchy_Header';
2055 -- PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Organization_Hierarchy_Name;
2056 -- vCtr := vCtr + 1;
2057 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Header';
2058 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Employer_Name;
2059 vCtr := vCtr + 1;
2060 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employee_Header';
2061 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Employee;
2062 vCtr := vCtr + 1;
2063 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Business_Group_Header';
2064 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_business_group_name;
2065 vCtr := vCtr+1;
2066 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Aggregate_Flag';
2067 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_agg_flag;
2068 vCtr := vCtr+1;
2069
2070 for info_rec in get_infos(l_effective_date,l_tax_year_start_date,l_yes,l_no,l_archive_action)
2071 LOOP
2072 l_person_id:=info_rec.Person_Id;
2073 IF info_rec.Date_End > l_tax_year_start_date THEN
2074 l_period_of_service := info_rec.period_of_service;
2075 ELSE
2076 l_period_of_service := to_char(info_rec.date_start,'DD/MM/YYYY')||' - '||to_char(info_rec.date_end,'DD/MM/YYYY');
2077 END IF;
2078
2079 OPEN csr_get_leg_employer(info_rec.Assignment_Id, l_tax_year_start_date, l_effective_date);
2080 FETCH csr_get_leg_employer INTO l_leg_emp_id, l_leg_tax_ref;
2081 IF csr_get_leg_employer%NOTFOUND THEN
2082 l_leg_emp_id := null;
2083 l_leg_tax_ref := null;
2084 l_Legal_Employer_Name := null;
2085 END IF;
2086 CLOSE csr_get_leg_employer;
2087
2088 IF l_leg_emp_id is not NULL and l_leg_tax_ref is not NULL THEN
2089
2090 OPEN csr_get_org_name(l_leg_emp_id);
2091 FETCH csr_get_org_name into l_Legal_Employer_Name;
2092 CLOSE csr_get_org_name;
2093
2094 OPEN csr_org_glb_address(p_bg_id, l_leg_emp_id);
2095 FETCH csr_org_glb_address
2096 INTO l_house_number_er,
2097 l_house_add_no_er,
2098 l_street_name_er,
2099 l_line1_er,
2100 l_line2_er,
2101 l_line3_er,
2102 l_po_code_er,
2103 l_city_er,
2104 l_country_er,
2105 l_add_style_er;
2106 CLOSE csr_org_glb_address;
2107
2108 IF l_add_style_er = 'NL' THEN
2109
2110 l_return_value := pay_nl_general.get_org_address(l_leg_emp_id
2111 ,p_bg_id
2112 ,l_house_number_er
2113 ,l_house_add_no_er
2114 ,l_street_name_er
2115 ,l_line1_er
2116 ,l_line2_er
2117 ,l_line3_er
2118 ,l_city_er
2119 ,l_country_er
2120 ,l_po_code_er);
2121
2122 END IF;
2123
2124 IF l_street_name_er is not NULL THEN
2125 l_addr1_er := l_street_name_er;
2126 END IF;
2127
2128 IF l_house_number_er is not NULL THEN
2129 IF l_addr1_er is not NULL THEN
2130 l_addr1_er := l_addr1_er||' '||l_house_number_er;
2131 ELSE
2132 l_addr1_er := l_house_number_er;
2133 END IF;
2134 END IF;
2135
2136 IF l_house_add_no_er is not NULL THEN
2137 IF l_addr1_er is not NULL THEN
2138 l_addr1_er := l_addr1_er||' '||l_house_add_no_er;
2139 ELSE
2140 l_addr1_er := l_house_add_no_er;
2141 END IF;
2142 END IF;
2143
2144
2145 IF l_po_code_er is not NULL THEN
2146 l_addr2_er := l_po_code_er;
2147 END IF;
2148
2149 IF l_city_er is not NULL THEN
2150 IF l_addr2_er is not NULL THEN
2151 l_addr2_er := l_addr2_er||' '||l_city_er;
2152 ELSE
2153 l_addr2_er := l_city_er;
2154 END IF;
2155 END IF;
2156
2157 ELSE
2158
2159 /*OPEN csr_get_org_name(p_employer_id);
2160 FETCH csr_get_org_name into l_Legal_Employer_Name;
2161 CLOSE csr_get_org_name;*/
2162
2163 OPEN csr_org_glb_address(p_bg_id, p_employer_id);
2164 FETCH csr_org_glb_address
2165 INTO l_house_number_er,
2166 l_house_add_no_er,
2167 l_street_name_er,
2168 l_line1_er,
2169 l_line2_er,
2170 l_line3_er,
2171 l_po_code_er,
2172 l_city_er,
2173 l_country_er,
2174 l_add_style_er;
2175 CLOSE csr_org_glb_address;
2176
2177 IF l_add_style_er = 'NL' THEN
2178
2179 l_return_value := pay_nl_general.get_org_address(p_employer_id
2180 ,p_bg_id
2181 ,l_house_number_er
2182 ,l_house_add_no_er
2183 ,l_street_name_er
2184 ,l_line1_er
2185 ,l_line2_er
2186 ,l_line3_er
2187 ,l_city_er
2188 ,l_country_er
2189 ,l_po_code_er);
2190
2191 END IF;
2192
2193 IF l_street_name_er is not NULL THEN
2194 l_addr1_er := l_street_name_er;
2195 END IF;
2196
2197 IF l_house_number_er is not NULL THEN
2198 IF l_addr1_er is not NULL THEN
2199 l_addr1_er := l_addr1_er||' '||l_house_number_er;
2200 ELSE
2201 l_addr1_er := l_house_number_er;
2202 END IF;
2203 END IF;
2204
2205 IF l_house_add_no_er is not NULL THEN
2206 IF l_addr1_er is not NULL THEN
2207 l_addr1_er := l_addr1_er||' '||l_house_add_no_er;
2208 ELSE
2209 l_addr1_er := l_house_add_no_er;
2210 END IF;
2211 END IF;
2212
2213
2214 IF l_po_code_er is not NULL THEN
2215 l_addr2_er := l_po_code_er;
2216 END IF;
2217
2218 IF l_city_er is not NULL THEN
2219 IF l_addr2_er is not NULL THEN
2220 l_addr2_er := l_addr2_er||' '||l_city_er;
2221 ELSE
2222 l_addr2_er := l_city_er;
2223 END IF;
2224 END IF;
2225
2226
2227 END IF;
2228
2229 if p_agg_flag = 'Y' then
2230
2231 if l_person_id = l_previous_person_id AND l_period_of_service = l_prev_period_of_service then
2232
2233 l_taxable_income:=fnd_number.canonical_to_number(info_rec.taxable_income) + l_taxable_income;
2234 l_deducted_wage_tax:=fnd_number.canonical_to_number(info_rec.deducted_wage_tax) + l_deducted_wage_tax;
2235 l_labour_tax_reduction:=fnd_number.canonical_to_number(info_rec.labour_tax_reduction) + l_labour_tax_reduction;
2236 l_ZVW:=fnd_number.canonical_to_number(info_rec.ZVW_Cont) + l_ZVW;
2237 l_ZVW_Basis:=fnd_number.canonical_to_number(info_rec.ZVW_Basis) + l_ZVW_Basis;
2238 l_Private_Use_Car:=fnd_number.canonical_to_number(info_rec.Private_Use_Car) + l_Private_Use_Car;
2239 l_Net_Expense_Allowance:=fnd_number.canonical_to_number(info_rec.Net_Expense_Allowance) + l_Net_Expense_Allowance;
2240 l_Value_Private_Use_Car:=fnd_number.canonical_to_number(info_rec.Value_Private_Use_Car) + l_Value_Private_Use_Car;
2241 l_Saved_Amount_LSS:=fnd_number.canonical_to_number(info_rec.Saved_Amount_LSS) + l_Saved_Amount_LSS;
2242 l_Employer_Child_Care:=fnd_number.canonical_to_number(info_rec.Employer_Child_Care) + l_Employer_Child_Care;
2243 l_Allowance_on_Disability:=fnd_number.canonical_to_number(info_rec.Allowance_on_Disability) + l_Allowance_on_Disability;
2244 l_Applied_LCLD:=fnd_number.canonical_to_number(info_rec.Applied_LCLD) + l_Applied_LCLD;
2245 populate_UserBalVal(info_rec.User_Bal_String,'Y');
2246
2247 end if;
2248
2249
2250 if l_person_id <> l_previous_person_id OR l_period_of_service <> l_prev_period_of_service then
2251
2252 if l_previous_person_id <> -1 then
2253
2254 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Taxable_Income';
2255 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Taxable_Income; --11886537
2256 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := floor(l_Taxable_Income); --11886537
2257 vCtr := vCtr + 1;
2258
2259 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Deducted_Wage_Tax';
2260 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Deducted_Wage_Tax; --11886537
2261 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(l_Deducted_Wage_Tax); --11886537
2262 vCtr := vCtr + 1;
2263
2264 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Labour_Tax_Reduction';
2265 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Labour_Tax_Reduction; --11886537
2266 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(l_Labour_Tax_Reduction); --11886537
2267 vCtr := vCtr + 1;
2268
2269 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_for_ZVW';
2270 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_ZVW_Basis; --11886537
2271 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := floor(l_ZVW_Basis); --11886537
2272 vCtr := vCtr + 1;
2273
2274 /*hr_utility.set_location('Tag name: '||PAY_NL_XDO_REPORT.vXMLTable(vCtr-1).TagName,100);
2275 hr_utility.set_location('Tag name: '||PAY_NL_XDO_REPORT.vXMLTable(vCtr-1).TagValue,110);*/
2276
2277 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ZVW_Cont';
2278 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_ZVW; --11886537
2279 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(l_ZVW); --11886537
2280 vCtr := vCtr + 1;
2281
2282 /*hr_utility.set_location('Tag name: '||PAY_NL_XDO_REPORT.vXMLTable(vCtr-1).TagName,100);
2283 hr_utility.set_location('Tag name: '||PAY_NL_XDO_REPORT.vXMLTable(vCtr-1).TagValue,110);*/
2284
2285
2286 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Private_Use_Car';
2287 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Private_Use_Car;
2288 vCtr := vCtr + 1;
2289
2290 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Net_Expense_Allowance';
2291 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Net_Expense_Allowance;
2292 vCtr := vCtr + 1;
2293
2294 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Value_Private_Use_Car';
2295 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Value_Private_Use_Car;
2296 vCtr := vCtr + 1;
2297
2298 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Saved_Amount_LSS';
2299 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Saved_Amount_LSS;
2300 vCtr := vCtr + 1;
2301
2302 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Child_Care';
2303 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Employer_Child_Care;
2304 vCtr := vCtr + 1;
2305
2306 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Allowance_on_Disability';
2307 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Allowance_on_Disability;
2308 vCtr := vCtr + 1;
2309
2310 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Applied_LCLD';
2311 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Applied_LCLD;
2312 vCtr := vCtr + 1;
2313
2314 IF vUserBalVal.COUNT > 0 THEN
2315
2316 FOR lCtr IN vUserBalVal.FIRST .. vUserBalVal.LAST LOOP
2317
2318 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := vUserBalVal(lCtr).TagName;
2319 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := vUserBalVal(lCtr).BalValue;
2320 vCtr := vCtr + 1;
2321
2322 END LOOP;
2323
2324 END IF;
2325
2326 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
2327 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
2328 vCtr := vCtr + 1;
2329
2330 l_taxable_income:=0;
2331 l_deducted_wage_tax:=0;
2332 l_labour_tax_reduction:=0;
2333 l_ZVW:=0;
2334 l_ZVW_Basis:=0;
2335 l_Private_Use_Car:=0;
2336 l_Net_Expense_Allowance:=0;
2337 l_Value_Private_Use_Car:=0;
2338 l_Saved_Amount_LSS:=0;
2339 l_Employer_Child_Care:=0;
2340 l_Allowance_on_Disability:=0;
2341 l_Applied_LCLD:=0;
2342 vUserBalVal.DELETE;
2343
2344 end if;
2345
2346 l_taxable_income:=fnd_number.canonical_to_number(info_rec.taxable_income);
2347 l_deducted_wage_tax:=fnd_number.canonical_to_number(info_rec.deducted_wage_tax);
2348 l_labour_tax_reduction:=fnd_number.canonical_to_number(info_rec.labour_tax_reduction);
2349 l_ZVW:=fnd_number.canonical_to_number(info_rec.ZVW_Cont);
2350 l_ZVW_Basis:=fnd_number.canonical_to_number(info_rec.ZVW_Basis);
2351 l_Private_Use_Car:=fnd_number.canonical_to_number(info_rec.Private_Use_Car);
2352 l_Net_Expense_Allowance:=fnd_number.canonical_to_number(info_rec.Net_Expense_Allowance);
2353 l_Value_Private_Use_Car:=fnd_number.canonical_to_number(info_rec.Value_Private_Use_Car);
2354 l_Saved_Amount_LSS:=fnd_number.canonical_to_number(info_rec.Saved_Amount_LSS);
2355 l_Employer_Child_Care:=fnd_number.canonical_to_number(info_rec.Employer_Child_Care);
2356 l_Allowance_on_Disability:=fnd_number.canonical_to_number(info_rec.Allowance_on_Disability);
2357 l_Applied_LCLD:=fnd_number.canonical_to_number(info_rec.Applied_LCLD);
2358 populate_UserBalVal(info_rec.User_Bal_String,'N');
2359
2360
2361
2362 l_date1:=info_rec.Date1;
2363 l_date2:=info_rec.Date2;
2364 l_date3:=info_rec.Date3;
2365 l_wtd1:=info_rec.Wage_Tax_Discount1;
2366 l_wtd2:=info_rec.Wage_Tax_Discount2;
2367 l_wtd3:=info_rec.Wage_Tax_Discount3;
2368 l_field_count:=1;
2369
2370 if l_date1 like '%00/%' then
2371 l_date1:=null;
2372 l_wtd1:=null;
2373 end if;
2374
2375 if l_date2 like '%00/%' then
2376 l_date2:=null;
2377 l_wtd2:=null;
2378 end if;
2379
2380 if l_date3 like '%00/%' then
2381 l_date3:=null;
2382 l_wtd3:=null;
2383 end if;
2384
2385 fnd_message.set_name('PAY','PAY_NL_ATS_YEAR');
2386 fnd_message.set_token('YEAR',l_year);
2387 l_year_msg:=fnd_message.get();
2388
2389 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
2390 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
2391 vCtr := vCtr + 1;
2392
2393 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Year_Detail';
2394 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_year_msg;
2395 vCtr := vCtr + 1;
2396
2397 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Name';
2398 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := nvl(l_Legal_Employer_Name,info_rec.Employer_Name);
2399 vCtr := vCtr + 1;
2400
2401
2402 /*if l_street_name_er is not null then
2403 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2404 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_street_name_er||']]>';
2405 l_field_count:=l_field_count+1;
2406 vCtr := vCtr + 1;
2407 end if;
2408
2409 if l_house_number_er is not null then
2410 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2411 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_number_er||']]>';
2412 l_field_count:=l_field_count+1;
2413 vCtr := vCtr + 1;
2414 end if;
2415
2416 if l_house_add_no_er is not null then
2417 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2418 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_add_no_er||']]>';
2419 l_field_count:=l_field_count+1;
2420 vCtr := vCtr + 1;
2421 end if;*/
2422
2423
2424 if l_addr1_er is not null then
2425 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2426 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr1_er||']]>';
2427 l_field_count:=l_field_count+1;
2428 vCtr := vCtr + 1;
2429 end if;
2430
2431
2432 if l_line1_er is not null then
2433 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2434 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line1_er||']]>';
2435 l_field_count:=l_field_count+1;
2436 vCtr := vCtr + 1;
2437 end if;
2438
2439
2440 if l_line2_er is not null then
2441 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2442 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line2_er||']]>';
2443 l_field_count:=l_field_count+1;
2444 vCtr := vCtr + 1;
2445 end if;
2446
2447 if l_line3_er is not null then
2448 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2449 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line3_er||']]>';
2450 l_field_count:=l_field_count+1;
2451 vCtr := vCtr + 1;
2452 end if;
2453
2454
2455 /*if l_po_code_er is not null then
2456 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2457 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_po_code_er||']]>';
2458 l_field_count:=l_field_count+1;
2459 vCtr := vCtr + 1;
2460 end if;
2461
2462
2463 if l_city_er is not null then
2464 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2465 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_city_er||']]>';
2466 l_field_count:=l_field_count+1;
2467 vCtr := vCtr + 1;
2468 end if;*/
2469
2470
2471 if l_addr2_er is not null then
2472 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2473 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr2_er||']]>';
2474 l_field_count:=l_field_count+1;
2475 vCtr := vCtr + 1;
2476 end if;
2477
2478
2479 if l_country_er is not null then
2480 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2481 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_country_er||']]>';
2482 l_field_count:=l_field_count+1;
2483 vCtr := vCtr + 1;
2484 end if;
2485
2486
2487 l_field_count:=1;
2488
2489
2490 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employee_Name';
2491 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Employee_Name;
2492 vCtr := vCtr + 1;
2493
2494 OPEN csr_emp_glb_address(l_person_id, fnd_date.canonical_to_date(p_year));
2495 FETCH csr_emp_glb_address
2496 INTO l_house_number_ee,
2497 l_house_add_no_ee,
2498 l_street_name_ee,
2499 l_line1_ee,
2500 l_line2_ee,
2501 l_line3_ee,
2502 l_po_code_ee,
2503 l_city_ee,
2504 l_country_ee,
2505 l_add_style_ee;
2506 CLOSE csr_emp_glb_address;
2507
2508 IF l_add_style_ee = 'NL' THEN
2509
2510 l_return_value1 := pay_nl_general.get_emp_address(l_person_id
2511 ,fnd_date.canonical_to_date(p_year)
2512 ,l_house_number_ee
2513 ,l_house_add_no_ee
2514 ,l_street_name_ee
2515 ,l_line1_ee
2516 ,l_line2_ee
2517 ,l_line3_ee
2518 ,l_city_ee
2519 ,l_country_ee
2520 ,l_po_code_ee
2521 );
2522
2523 END IF;
2524
2525
2526 IF l_street_name_ee is not NULL THEN
2527 l_addr1_ee := l_street_name_ee;
2528 END IF;
2529
2530 IF l_house_number_ee is not NULL THEN
2531 IF l_addr1_ee is not NULL THEN
2532 l_addr1_ee := l_addr1_ee||' '||l_house_number_ee;
2533 ELSE
2534 l_addr1_ee := l_house_number_ee;
2535 END IF;
2536 END IF;
2537
2538 IF l_house_add_no_ee is not NULL THEN
2539 IF l_addr1_ee is not NULL THEN
2540 l_addr1_ee := l_addr1_ee||' '||l_house_add_no_ee;
2541 ELSE
2542 l_addr1_ee := l_house_add_no_ee;
2543 END IF;
2544 END IF;
2545
2546
2547 IF l_po_code_ee is not NULL THEN
2548 l_addr2_ee := l_po_code_ee;
2549 END IF;
2550
2551 IF l_city_ee is not NULL THEN
2552 IF l_addr2_ee is not NULL THEN
2553 l_addr2_ee := l_addr2_ee||' '||l_city_ee;
2554 ELSE
2555 l_addr2_ee := l_city_ee;
2556 END IF;
2557 END IF;
2558
2559
2560
2561 /*if l_street_name_ee is not null then
2562 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2563 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_street_name_ee||']]>';
2564 l_field_count:=l_field_count+1;
2565 vCtr := vCtr + 1;
2566 end if;
2567
2568
2569 if l_house_number_ee is not null then
2570 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2571 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_number_ee||']]>';
2572 l_field_count:=l_field_count+1;
2573 vCtr := vCtr + 1;
2574 end if;
2575
2576 if l_house_add_no_ee is not null then
2577 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2578 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_add_no_ee||']]>';
2579 l_field_count:=l_field_count+1;
2580 vCtr := vCtr + 1;
2581 end if;*/
2582
2583
2584 if l_addr1_ee is not null then
2585 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2586 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr1_ee||']]>';
2587 l_field_count:=l_field_count+1;
2588 vCtr := vCtr + 1;
2589 end if;
2590
2591
2592 if l_line1_ee is not null then
2593 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2594 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line1_ee||']]>';
2595 l_field_count:=l_field_count+1;
2596 vCtr := vCtr + 1;
2597 end if;
2598
2599
2600 if l_line2_ee is not null then
2601 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2602 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line2_ee||']]>';
2603 l_field_count:=l_field_count+1;
2604 vCtr := vCtr + 1;
2605 end if;
2606
2607
2608 if l_line3_ee is not null then
2609 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2610 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line3_ee||']]>';
2611 l_field_count:=l_field_count+1;
2612 vCtr := vCtr + 1;
2613 end if;
2614
2615
2616 /*if l_po_code_ee is not null then
2617 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2618 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_po_code_ee||']]>';
2619 l_field_count:=l_field_count+1;
2620 vCtr := vCtr + 1;
2621 end if;
2622
2623
2624 if l_city_ee is not null then
2625 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2626 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_city_ee||']]>';
2627 l_field_count:=l_field_count+1;
2628 vCtr := vCtr + 1;
2629 end if;*/
2630
2631
2632 if l_addr2_ee is not null then
2633 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2634 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr2_ee||']]>';
2635 l_field_count:=l_field_count+1;
2636 vCtr := vCtr + 1;
2637 end if;
2638
2639
2640 if l_country_ee is not null then
2641 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2642 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_country_ee||']]>';
2643 l_field_count:=l_field_count+1;
2644 vCtr := vCtr + 1;
2645 end if;
2646
2647
2648
2649 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Tax_Registration_Number';
2650 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||nvl(l_leg_tax_ref,info_rec.Tax_Registration_Number)||']]>';
2651 vCtr := vCtr + 1;
2652
2653 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employee_Number';
2654 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Employee_Number;
2655 vCtr := vCtr + 1;
2656
2657 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Assignment_Number';
2658 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.assignment_Number;
2659 vCtr := vCtr + 1;
2660
2661 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SOFI_Number';
2662 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.SOFI_Number;
2663 vCtr := vCtr + 1;
2664
2665 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date_Of_Birth';
2666 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Date_Of_Birth;
2667 vCtr := vCtr + 1;
2668
2669 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Period_Of_Service';
2670 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_period_of_service;
2671 vCtr := vCtr + 1;
2672
2673 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Table';
2674 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Wage_Tax_Table;
2675 vCtr := vCtr + 1;
2676
2677 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Discount1';
2678 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_wtd1;
2679 vCtr := vCtr + 1;
2680
2681 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date1';
2682 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_date1;
2683 vCtr := vCtr + 1;
2684
2685 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Discount2';
2686 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_wtd2;
2687 vCtr := vCtr + 1;
2688
2689 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date2';
2690 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_date2;
2691 vCtr := vCtr + 1;
2692
2693 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Discount3';
2694 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_wtd3;
2695 vCtr := vCtr + 1;
2696
2697 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date3';
2698 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_date3;
2699 vCtr := vCtr + 1;
2700
2701 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Insured_for_WAO';
2702 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Insured_for_WAO;
2703 vCtr := vCtr + 1;
2704
2705 /*PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Insured_for_ZFW';
2706 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Insured_for_ZFW;
2707 vCtr := vCtr + 1;*/
2708
2709 /*PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Company_Car';
2710 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Company_Car;
2711 vCtr := vCtr + 1;*/
2712
2713 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Code_Reason_No';
2714 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := hr_general.decode_lookup('NL_COMPANY_CAR_USAGE_CODE',info_rec.Company_Car);
2715 vCtr := vCtr + 1;
2716
2717 end if;
2718
2719
2720 else
2721
2722 l_date1:=info_rec.Date1;
2723 l_date2:=info_rec.Date2;
2724 l_date3:=info_rec.Date3;
2725 l_wtd1:=info_rec.Wage_Tax_Discount1;
2726 l_wtd2:=info_rec.Wage_Tax_Discount2;
2727 l_wtd3:=info_rec.Wage_Tax_Discount3;
2728 l_field_count:=1;
2729
2730 if l_date1 like '%00/%' then
2731 l_date1:=null;
2732 l_wtd1:=null;
2733 end if;
2734
2735 if l_date2 like '%00/%' then
2736 l_date2:=null;
2737 l_wtd2:=null;
2738 end if;
2739
2740 if l_date3 like '%00/%' then
2741 l_date3:=null;
2742 l_wtd3:=null;
2743 end if;
2744
2745 fnd_message.set_name('PAY','PAY_NL_ATS_YEAR');
2746 fnd_message.set_token('YEAR',l_year);
2747 l_year_msg:=fnd_message.get();
2748
2749 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
2750 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
2751 vCtr := vCtr + 1;
2752
2753 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Year_Detail';
2754 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_year_msg;
2755 vCtr := vCtr + 1;
2756
2757 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Name';
2758 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := nvl(l_Legal_Employer_Name,info_rec.Employer_Name);
2759 vCtr := vCtr + 1;
2760
2761
2762 /*if l_street_name_er is not null then
2763 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2764 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_street_name_er||']]>';
2765 l_field_count:=l_field_count+1;
2766 vCtr := vCtr + 1;
2767 end if;
2768
2769 if l_house_number_er is not null then
2770 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2771 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_number_er||']]>';
2772 l_field_count:=l_field_count+1;
2773 vCtr := vCtr + 1;
2774 end if;
2775
2776 if l_house_add_no_er is not null then
2777 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2778 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_add_no_er||']]>';
2779 l_field_count:=l_field_count+1;
2780 vCtr := vCtr + 1;
2781 end if;*/
2782
2783
2784 if l_addr1_er is not null then
2785 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2786 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr1_er||']]>';
2787 l_field_count:=l_field_count+1;
2788 vCtr := vCtr + 1;
2789 end if;
2790
2791
2792 if l_line1_er is not null then
2793 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2794 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line1_er||']]>';
2795 l_field_count:=l_field_count+1;
2796 vCtr := vCtr + 1;
2797 end if;
2798
2799
2800
2801 if l_line2_er is not null then
2802 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2803 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line2_er||']]>';
2804 l_field_count:=l_field_count+1;
2805 vCtr := vCtr + 1;
2806 end if;
2807
2808 if l_line3_er is not null then
2809 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2810 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line3_er||']]>';
2811 l_field_count:=l_field_count+1;
2812 vCtr := vCtr + 1;
2813 end if;
2814
2815
2816 if l_addr2_er is not null then
2817 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2818 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr2_er||']]>';
2819 l_field_count:=l_field_count+1;
2820 vCtr := vCtr + 1;
2821 end if;
2822
2823
2824 /*if l_po_code_er is not null then
2825 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2826 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_po_code_er||']]>';
2827 l_field_count:=l_field_count+1;
2828 vCtr := vCtr + 1;
2829 end if;
2830
2831 if l_city_er is not null then
2832 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2833 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_city_er||']]>';
2834 l_field_count:=l_field_count+1;
2835 vCtr := vCtr + 1;
2836 end if;*/
2837
2838
2839 if l_country_er is not null then
2840 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ER_Address_Line' || to_char(l_field_count);
2841 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_country_er||']]>';
2842 l_field_count:=l_field_count+1;
2843 vCtr := vCtr + 1;
2844 end if;
2845
2846
2847
2848 l_field_count:=1;
2849
2850
2851 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employee_Name';
2852 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Employee_Name;
2853 vCtr := vCtr + 1;
2854
2855 OPEN csr_emp_glb_address(l_person_id, fnd_date.canonical_to_date(p_year));
2856 FETCH csr_emp_glb_address
2857 INTO l_house_number_ee,
2858 l_house_add_no_ee,
2859 l_street_name_ee,
2860 l_line1_ee,
2861 l_line2_ee,
2862 l_line3_ee,
2863 l_po_code_ee,
2864 l_city_ee,
2865 l_country_ee,
2866 l_add_style_ee;
2867 CLOSE csr_emp_glb_address;
2868
2869 IF l_add_style_ee = 'NL' THEN
2870
2871 l_return_value1 := pay_nl_general.get_emp_address(l_person_id
2872 ,fnd_date.canonical_to_date(p_year)
2873 ,l_house_number_ee
2874 ,l_house_add_no_ee
2875 ,l_street_name_ee
2876 ,l_line1_ee
2877 ,l_line2_ee
2878 ,l_line3_ee
2879 ,l_city_ee
2880 ,l_country_ee
2881 ,l_po_code_ee
2882 );
2883
2884 END IF;
2885
2886 IF l_street_name_ee is not NULL THEN
2887 l_addr1_ee := l_street_name_ee;
2888 END IF;
2889
2890 IF l_house_number_ee is not NULL THEN
2891 IF l_addr1_ee is not NULL THEN
2892 l_addr1_ee := l_addr1_ee||' '||l_house_number_ee;
2893 ELSE
2894 l_addr1_ee := l_house_number_ee;
2895 END IF;
2896 END IF;
2897
2898 IF l_house_add_no_ee is not NULL THEN
2899 IF l_addr1_ee is not NULL THEN
2900 l_addr1_ee := l_addr1_ee||' '||l_house_add_no_ee;
2901 ELSE
2902 l_addr1_ee := l_house_add_no_ee;
2903 END IF;
2904 END IF;
2905
2906 IF l_po_code_ee is not NULL THEN
2907 l_addr2_ee := l_po_code_ee;
2908 END IF;
2909
2910 IF l_city_ee is not NULL THEN
2911 IF l_addr2_ee is not NULL THEN
2912 l_addr2_ee := l_addr2_ee||' '||l_city_ee;
2913 ELSE
2914 l_addr2_ee := l_city_ee;
2915 END IF;
2916 END IF;
2917
2918
2919
2920 /*if l_street_name_ee is not null then
2921 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2922 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_street_name_ee||']]>';
2923 l_field_count:=l_field_count+1;
2924 vCtr := vCtr + 1;
2925 end if;
2926
2927 if l_house_number_ee is not null then
2928 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2929 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_number_ee||']]>';
2930 l_field_count:=l_field_count+1;
2931 vCtr := vCtr + 1;
2932 end if;
2933
2934 if l_house_add_no_ee is not null then
2935 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2936 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_house_add_no_ee||']]>';
2937 l_field_count:=l_field_count+1;
2938 vCtr := vCtr + 1;
2939 end if;*/
2940
2941
2942 if l_addr1_ee is not null then
2943 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2944 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr1_ee||']]>';
2945 l_field_count:=l_field_count+1;
2946 vCtr := vCtr + 1;
2947 end if;
2948
2949
2950 if l_line1_ee is not null then
2951 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2952 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line1_ee||']]>';
2953 l_field_count:=l_field_count+1;
2954 vCtr := vCtr + 1;
2955 end if;
2956
2957
2958 if l_line2_ee is not null then
2959 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2960 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line2_ee||']]>';
2961 l_field_count:=l_field_count+1;
2962 vCtr := vCtr + 1;
2963 end if;
2964
2965
2966 if l_line3_ee is not null then
2967 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2968 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_line3_ee||']]>';
2969 l_field_count:=l_field_count+1;
2970 vCtr := vCtr + 1;
2971 end if;
2972
2973 /*if l_po_code_ee is not null then
2974 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2975 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_po_code_ee||']]>';
2976 l_field_count:=l_field_count+1;
2977 vCtr := vCtr + 1;
2978 end if;
2979
2980 if l_city_ee is not null then
2981 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2982 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_city_ee||']]>';
2983 l_field_count:=l_field_count+1;
2984 vCtr := vCtr + 1;
2985 end if;*/
2986
2987
2988 if l_addr2_ee is not null then
2989 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2990 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_addr2_ee||']]>';
2991 l_field_count:=l_field_count+1;
2992 vCtr := vCtr + 1;
2993 end if;
2994
2995
2996 if l_country_ee is not null then
2997 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EE_Address_Line' || to_char(l_field_count);
2998 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||l_country_ee||']]>';
2999 l_field_count:=l_field_count+1;
3000 vCtr := vCtr + 1;
3001 end if;
3002
3003
3004
3005 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Tax_Registration_Number';
3006 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := '<![CDATA['||nvl(l_leg_tax_ref,info_rec.Tax_Registration_Number)||']]>';
3007 vCtr := vCtr + 1;
3008
3009 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employee_Number';
3010 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Employee_Number;
3011 vCtr := vCtr + 1;
3012
3013 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Assignment_Number';
3014 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.assignment_Number;
3015 vCtr := vCtr + 1;
3016
3017 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SOFI_Number';
3018 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.SOFI_Number;
3019 vCtr := vCtr + 1;
3020
3021 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date_Of_Birth';
3022 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Date_Of_Birth;
3023 vCtr := vCtr + 1;
3024
3025 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Period_Of_Service';
3026 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_period_of_service;
3027 vCtr := vCtr + 1;
3028
3029 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Table';
3030 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Wage_Tax_Table;
3031 vCtr := vCtr + 1;
3032
3033 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Discount1';
3034 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_wtd1;
3035 vCtr := vCtr + 1;
3036
3037 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date1';
3038 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_date1;
3039 vCtr := vCtr + 1;
3040
3041 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Discount2';
3042 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_wtd2;
3043 vCtr := vCtr + 1;
3044
3045 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date2';
3046 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_date2;
3047 vCtr := vCtr + 1;
3048
3049 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_Tax_Discount3';
3050 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_wtd3;
3051 vCtr := vCtr + 1;
3052
3053 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Date3';
3054 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_date3;
3055 vCtr := vCtr + 1;
3056
3057 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Taxable_Income';
3058 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Taxable_Income; --11886537
3059 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := floor(info_rec.Taxable_Income); --11886537
3060 vCtr := vCtr + 1;
3061
3062 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Deducted_Wage_Tax';
3063 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Deducted_Wage_Tax; --11886537
3064 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(info_rec.Deducted_Wage_Tax); --11886537
3065 vCtr := vCtr + 1;
3066
3067 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Labour_Tax_Reduction';
3068 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Labour_Tax_Reduction; --11886537
3069 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(info_rec.Labour_Tax_Reduction); --11886537
3070 vCtr := vCtr + 1;
3071
3072 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Insured_for_WAO';
3073 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Insured_for_WAO;
3074 vCtr := vCtr + 1;
3075
3076 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_for_ZVW';
3077 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.ZVW_Basis; --11886537
3078 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := floor(info_rec.ZVW_Basis); --11886537
3079 vCtr := vCtr + 1;
3080
3081 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ZVW_Cont';
3082 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.ZVW_Cont; --11886537
3083 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(info_rec.ZVW_Cont); --11886537
3084 vCtr := vCtr + 1;
3085
3086 /*PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Company_Car';
3087 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Company_Car;
3088 vCtr := vCtr + 1;*/
3089
3090 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Private_Use_Car';
3091 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Private_Use_Car;
3092 vCtr := vCtr + 1;
3093
3094 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Code_Reason_No';
3095 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := hr_general.decode_lookup('NL_COMPANY_CAR_USAGE_CODE',info_rec.Company_Car);
3096 vCtr := vCtr + 1;
3097
3098 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Value_Private_Use_Car';
3099 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Value_Private_Use_Car;
3100 vCtr := vCtr + 1;
3101
3102 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Net_Expense_Allowance';
3103 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Net_Expense_Allowance;
3104 vCtr := vCtr + 1;
3105
3106 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Saved_Amount_LSS';
3107 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Saved_Amount_LSS;
3108 vCtr := vCtr + 1;
3109
3110 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Child_Care';
3111 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Employer_Child_Care;
3112 vCtr := vCtr + 1;
3113
3114 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Allowance_on_Disability';
3115 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Allowance_on_Disability;
3116 vCtr := vCtr + 1;
3117
3118 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Applied_LCLD';
3119 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := info_rec.Applied_LCLD;
3120 vCtr := vCtr + 1;
3121
3122 hr_utility.set_location('Calling bal table',10);
3123
3124 populate_UserBalVal(info_rec.User_Bal_String,'N');
3125
3126 --hr_utility.set_location('FIRST-'||vUserBalVal.FIRST||' LAST-'||vUserBalVal.LAST||' COUNT-'||vUserBalVal.COUNT,20);
3127
3128 IF vUserBalVal.COUNT > 0 THEN
3129
3130 FOR lCtr IN vUserBalVal.FIRST .. vUserBalVal.LAST LOOP
3131
3132 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := vUserBalVal(lCtr).TagName;
3133 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := vUserBalVal(lCtr).BalValue;
3134 vCtr := vCtr + 1;
3135
3136 END LOOP;
3137
3138 END IF;
3139
3140 vUserBalVal.DELETE;
3141
3142 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
3143 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
3144 vCtr := vCtr + 1;
3145
3146 end if;
3147
3148
3149 if l_previous_person_id <> l_person_id OR l_prev_period_of_service <> l_period_of_service then
3150 l_previous_person_id:=l_person_id;
3151 l_prev_period_of_service:=l_period_of_service;
3152 end if;
3153
3154 l_house_number_ee := NULL;
3155 l_house_add_no_ee := NULL;
3156 l_street_name_ee := NULL;
3157 l_line1_ee := NULL;
3158 l_line2_ee := NULL;
3159 l_line3_ee := NULL;
3160 l_city_ee := NULL;
3161 l_country_ee := NULL;
3162 l_po_code_ee := NULL;
3163 l_addr1_ee := NULL;
3164 l_addr2_ee := NULL;
3165
3166
3167 end loop;
3168
3169 if l_previous_person_id <> -1 and p_agg_flag = 'Y' then
3170
3171 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Taxable_Income';
3172 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Taxable_Income; --11886537
3173 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := floor(l_Taxable_Income); --11886537
3174 vCtr := vCtr + 1;
3175
3176 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Deducted_Wage_Tax';
3177 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Deducted_Wage_Tax; --11886537
3178 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(l_Deducted_Wage_Tax); --11886537
3179 vCtr := vCtr + 1;
3180
3181 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Labour_Tax_Reduction';
3182 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Labour_Tax_Reduction; --11886537
3183 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(l_Labour_Tax_Reduction); --11886537
3184 vCtr := vCtr + 1;
3185
3186 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Wage_for_ZVW';
3187 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_ZVW_Basis; --11886537
3188 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := floor(l_ZVW_Basis); --11886537
3189 vCtr := vCtr + 1;
3190
3191 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ZVW_Cont';
3192 --PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_ZVW; --11886537
3193 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := ceil(l_ZVW); --11886537
3194 vCtr := vCtr + 1;
3195
3196 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Private_Use_Car';
3197 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Private_Use_Car;
3198 vCtr := vCtr + 1;
3199
3200 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Net_Expense_Allowance';
3201 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Net_Expense_Allowance;
3202 vCtr := vCtr + 1;
3203
3204 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Value_Private_Use_Car';
3205 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Value_Private_Use_Car;
3206 vCtr := vCtr + 1;
3207
3208 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Saved_Amount_LSS';
3209 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Saved_Amount_LSS;
3210 vCtr := vCtr + 1;
3211
3212 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Employer_Child_Care';
3213 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Employer_Child_Care;
3214 vCtr := vCtr + 1;
3215
3216 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Allowance_on_Disability';
3217 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Allowance_on_Disability;
3218 vCtr := vCtr + 1;
3219
3220 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'Applied_LCLD';
3221 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_Applied_LCLD;
3222 vCtr := vCtr + 1;
3223
3224 IF vUserBalVal.COUNT > 0 THEN
3225
3226 FOR lCtr IN vUserBalVal.FIRST .. vUserBalVal.LAST LOOP
3227
3228 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := vUserBalVal(lCtr).TagName;
3229 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := vUserBalVal(lCtr).BalValue;
3230 vCtr := vCtr + 1;
3231
3232 --hr_utility.set_location('tag name-'||vUserBalVal(lCtr).TagName||' tag value-'||vUserBalVal(lCtr).BalValue,90);
3233
3234 END LOOP;
3235
3236 END IF;
3237
3238 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
3239 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
3240 vCtr := vCtr + 1;
3241
3242 l_taxable_income:=0;
3243 l_deducted_wage_tax:=0;
3244 l_labour_tax_reduction:=0;
3245 l_ZVW:=0;
3246 l_ZVW_Basis:=0;
3247 l_Private_Use_Car:=0;
3248 l_Net_Expense_Allowance:=0;
3249 l_Value_Private_Use_Car:=0;
3250 l_Saved_Amount_LSS:=0;
3251 l_Employer_Child_Care:=0;
3252 l_Allowance_on_Disability:=0;
3253 l_Applied_LCLD:=0;
3254 vUserBalVal.DELETE;
3255
3256 end if;
3257
3258 /*Fetch XML file as a CLOB*/
3259
3260 pay_nl_xdo_Report.WritetoCLOB_rtf_1(p_xml );
3261
3262 pay_nl_xdo_Report.WritetoXML_rtf (fnd_global.conc_request_id,l_output_fname);
3263
3264 end populate_ats_report_data_new;
3265
3266 PROCEDURE record_4712(p_file_id NUMBER) IS
3267
3268 l_upload_name VARCHAR2(1000);
3269 l_file_name VARCHAR2(1000);
3270 l_start_date DATE := TO_DATE('01/01/0001', 'dd/mm/yyyy');
3271 l_end_date DATE := TO_DATE('31/12/4712', 'dd/mm/yyyy');
3272
3273 BEGIN
3274 -- program_name will be used to store the file_name
3275 -- this is bcos the file_name in fnd_lobs contains
3276 -- the full patch of the doc and not just the file name
3277 SELECT program_name
3278 INTO l_file_name
3279 FROM fnd_lobs
3280 WHERE file_id = p_file_id;
3281
3282 -- the delete will ensure that the patch is rerunnable
3283 DELETE FROM per_gb_xdo_templates
3284 WHERE file_name = l_file_name AND
3285 effective_start_date = l_start_date AND
3286 effective_end_date = l_end_date;
3287
3288 INSERT INTO per_gb_xdo_templates
3289 (file_id,
3290 file_name,
3291 file_description,
3292 effective_start_date,
3293 effective_end_date)
3294 SELECT p_file_id, l_file_name, 'Template for year 0001-4712',
3295 l_start_date, l_end_date
3296 FROM fnd_lobs
3297 WHERE file_id = p_file_id;
3298 END;
3299
3300 /*-----------------------------------------------------------------------------
3301 |Name : populate_UserBalVal |
3302 |Type : Procedure |
3303 |Description: Procedure which populates pl/sql table with user defined balance |
3304 | values and tag names |
3305 -------------------------------------------------------------------------------*/
3306
3307 PROCEDURE populate_UserBalVal(p_User_Bal_String VARCHAR2, p_agg_flag VARCHAR2) IS
3308
3309 l_start NUMBER;
3310 l_end NUMBER;
3311 lCtr NUMBER;
3312
3313 BEGIN
3314
3315 l_start := 1;
3316 lCtr := 0;
3317
3318 hr_utility.set_location('Entered populate_UserBalVal',10);
3319
3320 IF PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable.count > 0 AND instr(p_User_Bal_String,'|',1,PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable.count)=length(p_User_Bal_String) THEN
3321
3322 IF p_agg_flag='N' THEN
3323
3324 --hr_utility.set_location('Agg flag N in function',20);
3325 --hr_utility.set_location('User table first-'||PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable.FIRST||' last-'||PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable.LAST||' count-'||PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable.COUNT,20);
3326
3327 FOR lCtr IN PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable.FIRST .. PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable.LAST LOOP
3328
3329 l_end := instr(p_User_Bal_String,'|',l_start,1);
3330
3331 vUserBalVal(lCtr).BalValue := fnd_number.canonical_to_number(substr(p_User_Bal_String,l_start,l_end-l_start));
3332 vUserBalVal(lCtr).TagName := PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable(lCtr).TagName;
3333
3334 hr_utility.set_location('tag-'||vUserBalVal(lCtr).TagName,13);
3335 hr_utility.set_location('bal-'||vUserBalVal(lCtr).BalValue,23);
3336
3337 l_start := l_end + 1;
3338
3339 END LOOP;
3340
3341 ELSIF p_agg_flag='Y' THEN
3342
3343 FOR lCtr IN PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable.FIRST .. PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable.LAST LOOP
3344
3345 l_end := instr(p_User_Bal_String,'|',l_start,1);
3346
3347 vUserBalVal(lCtr).BalValue := vUserBalVal(lCtr).BalValue + fnd_number.canonical_to_number(substr(p_User_Bal_String,l_start,l_end-l_start));
3348 vUserBalVal(lCtr).TagName := PAY_NL_TAXOFFICE_ARCHIVE.vUserBalTable(lCtr).TagName;
3349
3350 l_start := l_end + 1;
3351
3352 END LOOP;
3353
3354 END IF;
3355
3356 END IF;
3357
3358 END populate_UserBalVal;
3359
3360
3361 /*-----------------------------------------------------------------------------
3362 |Name : get_Address_Style |
3363 |Type : Function |
3364 |Description: Function that returns the address style of the address record of |
3365 | a person at a given date |
3366 -------------------------------------------------------------------------------*/
3367
3368 FUNCTION get_Address_Style(p_person_id NUMBER, p_effective_date DATE) RETURN VARCHAR2 IS
3369
3370 l_address_style VARCHAR2(50) := 'N';
3371
3372 BEGIN
3373
3374 BEGIN
3375
3376 SELECT pad.style INTO l_address_style
3377 FROM per_addresses pad
3378 WHERE pad.person_id = p_person_id
3379 AND pad.primary_flag = 'Y'
3380 AND p_effective_date between pad.date_from and nvl(pad.date_to,hr_general.end_of_time);
3381
3382 EXCEPTION
3383
3384 WHEN NO_DATA_FOUND
3385 THEN null;
3386
3387 WHEN OTHERS
3388 THEN null;
3389
3390 END;
3391
3392 return l_address_style;
3393
3394 END get_Address_Style;
3395
3396 /*-----------------------------------------------------------------------------
3397 |Name : get_Post_Code |
3398 |Type : Function |
3399 |Description: Function that returns the postal code of the address record of |
3400 | a person at a given date |
3401 -------------------------------------------------------------------------------*/
3402
3403 FUNCTION get_Post_Code(p_person_id NUMBER, p_effective_date DATE) RETURN VARCHAR2 IS
3404
3405 l_post_code VARCHAR2(50) := '';
3406
3407 BEGIN
3408
3409 BEGIN
3410
3411 SELECT pad.postal_code INTO l_post_code
3412 FROM per_addresses pad
3413 WHERE pad.person_id = p_person_id
3414 AND pad.primary_flag = 'Y'
3415 AND p_effective_date between pad.date_from and nvl(pad.date_to,hr_general.end_of_time);
3416
3417 EXCEPTION
3418
3419 WHEN NO_DATA_FOUND
3420 THEN null;
3421
3422 WHEN OTHERS
3423 THEN null;
3424
3425 END;
3426
3427 return l_post_code;
3428
3429 END get_Post_Code;
3430
3431 END PAY_NL_ATS_REPORT;