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