[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_PSTR
Source
1 package body pay_fi_pstr as
2 /* $Header: pyfipstr.pkb 120.0.12000000.1 2007/04/26 12:12:32 dbehera noship $ */
3 procedure populate_details (
4 p_business_group_id in number,
5 p_payroll_action_id in varchar2,
6 p_template_name in varchar2,
7 p_xml out nocopy clob
8 ) is
9 --
10 --
11 /*Cursor to fetch Header Information */
12 cursor csr_get_hdr_info (
13 p_payroll_action_id number
14 ) is
15 select action_information2 business_group_id, action_information3 legal_employer_id,
16 action_information4
17 legal_employer_name, action_information5 legal_empl_y_num,
18 action_information6 local_unit_id, action_information7 local_unit_name, action_information8 local_unit_sd_no,
19 action_information9
20 year, effective_date
21 from pay_action_information pai
22 where action_context_type = 'PA'
23 and action_context_id = p_payroll_action_id
24 and action_information_category = 'EMEA REPORT DETAILS'
25 and action_information1 = 'PYFIPSTA';
26
27 rl_hdr_info csr_get_hdr_info%rowtype;
28
29 cursor csr_get_local_unit_details (
30 p_payroll_action_id number
31 ) is
32 select action_information3 business_group_id, action_information4 legal_employer_id,
33 action_information5
34 local_unit_id, action_information6 local_unit_name, action_information7 local_unit_sd_no,
35 action_information8
36 address_line_1, action_information9 address_line_2, action_information10 address_line_3,
37 action_information11
38 country, action_information12 postal_code, effective_date
39 from pay_action_information pai
40 where action_context_type = 'PA'
41 and action_context_id = p_payroll_action_id
42 and action_information_category = 'EMEA REPORT INFORMATION'
43 and action_information1 = 'PYFIPSTA'
44 -- and action_information6 = 'FI Local Unit1'
45 and action_information2 = 'LU_DETAILS';
46
47 rl_local_unit_detail csr_get_local_unit_details%rowtype;
48
49 cursor cst_get_emp_count (
50 p_payroll_action_id varchar2,
51 p_legal_employer varchar2,
52 p_local_unit_id varchar2
53 ) is
54 select count (*)
55 from pay_payroll_actions paa, pay_assignment_actions assg, pay_action_information pai
56 where paa.payroll_action_id = p_payroll_action_id
57 and assg.payroll_action_id = paa.payroll_action_id
58 and pai.action_context_id = assg.assignment_action_id
59 and pai.action_context_type = 'AAP'
60 and pai.action_information_category = 'EMEA REPORT INFORMATION'
61 and pai.action_information1 = 'PYFIPSTA'
62 and action_information2 = 'PERSON DETAILS'
63 and pai.action_information20 = p_local_unit_id;
64
65 /* Cursor to fetch Detail Information */
66 --
67 --
68 cursor csr_get_detail_info (
69 p_payroll_action_id number,
70 p_local_unit_id varchar2
71 ) is
72 select action_information3 person_id, action_information4 pin, action_information5 emp_name,
73 action_information6
74 employee_number, action_information7 salary_basis, action_information8 tax_card_type,
75 action_information9
76 tax_municipality, action_information10 base_rate, action_information11 additional_rate,
77 action_information12
78 yearly_income_limit, action_information13 actual_tax_days,
79 action_information14 insurance_salary, action_information15 address_line1,
80 action_information16
81 address_line2, action_information17 address_line3, action_information18 postal_code,
82 action_information19
83 country, fnd_date.canonical_to_date (action_information21) date_of_birth,
84 action_information22
85 job_name, action_information23 permanent_address_line1,
86 action_information24 permanent_address_line2, action_information25 permanent_address_line3,
87 action_information26
88 permanent_postal_code, action_information27 permanent_country
89 from pay_payroll_actions paa, pay_assignment_actions assg, pay_action_information pai
90 where paa.payroll_action_id = p_payroll_action_id
91 and assg.payroll_action_id = paa.payroll_action_id
92 and pai.action_context_id = assg.assignment_action_id
93 and pai.action_context_type = 'AAP'
94 and pai.action_information_category = 'EMEA REPORT INFORMATION'
95 and action_information1 = 'PYFIPSTA'
96 and action_information2 = 'PERSON DETAILS'
97 and action_information20 = p_local_unit_id
98 -- and action_information3 = to_char(20435)
99 order by to_number (person_id);
100
101 cursor csr_get_payroll_detais (
102 p_payroll_action_id number,
103 p_person_id varchar2
104 ) is
105 select action_information4 payroll_id, action_information5 pay_period,
106 fnd_date.canonical_to_date (action_information6)
107 pay_period_start_date,
108 fnd_date.canonical_to_date (action_information7) pay_period_end_date, action_information8 period_type,
109 fnd_number.canonical_to_number (action_information9)
110 salary_income,
111 fnd_number.canonical_to_number (action_information10) benefits_in_kind, action_information11 benefit_type,
112 fnd_number.canonical_to_number (action_information12)
113 benefit_monetary_value,
114 fnd_number.canonical_to_number (action_information13) sal_sub_tax,
115 fnd_number.canonical_to_number (action_information14)
116 tax_amount,
117 fnd_number.canonical_to_number (action_information15) net_salary,
118 fnd_number.canonical_to_number (action_information16)
119 deductions_b_tax,
120 fnd_number.canonical_to_number (action_information17) external_compensation,
121 fnd_number.canonical_to_number (action_information18)
122 time_period_id,
123 fnd_number.canonical_to_number (action_information19) pension,
124 fnd_number.canonical_to_number (action_information20)
125 unemployment_insurance,
126 fnd_number.canonical_to_number (action_information21) trade_union_fee,
127 fnd_number.canonical_to_number (action_information22)
128 car_benefit
129 from pay_payroll_actions paa, pay_assignment_actions assg, pay_action_information pai
130 where paa.payroll_action_id = p_payroll_action_id
131 and assg.payroll_action_id = paa.payroll_action_id
132 and pai.action_context_id = assg.assignment_action_id
133 and pai.action_context_type = 'AAP'
134 and pai.action_information_category = 'EMEA REPORT INFORMATION'
135 and action_information1 = 'PYFIPSTA'
136 and action_information2 = 'Payroll Details'
137 and action_information3 = p_person_id
138 order by time_period_id asc;
139
140 cursor csr_get_benefits (
141 p_payroll_action_id number,
142 p_person_id varchar2
143 ) is
144 select fnd_date.canonical_to_date (action_information6) pay_period_start_date,
145 fnd_date.canonical_to_date (action_information7)
146 pay_period_end_date, action_information11 benefit_type,
147 fnd_number.canonical_to_number (action_information12)
148 benefit_value
149 from pay_payroll_actions paa, pay_assignment_actions assg, pay_action_information pai
150 where paa.payroll_action_id = p_payroll_action_id
151 and assg.payroll_action_id = paa.payroll_action_id
152 and pai.action_context_id = assg.assignment_action_id
153 and pai.action_context_type = 'AAP'
154 and pai.action_information_category = 'EMEA REPORT INFORMATION'
155 and action_information1 = 'PYFIPSTA'
156 and action_information2 = 'Benefit Details'
157 and action_information3 = p_person_id
158 and action_information11 is not null
159 --group by action_information18,action_information5,action_information6,action_information7,action_information8,action_information11,action_information21
160 order by benefit_type; --, to_number(nvl(benefits_in_kind,0)) desc;
161 cursor csr_get_car_benefit (
162 p_payroll_action_id number,
163 p_person_id varchar2
164 ) is
165 select fnd_date.canonical_to_date (action_information6) car_pay_period_start_date,
166 fnd_date.canonical_to_date (action_information7)
167 car_pay_period_end_date,
168 action_information19 input_value_name, action_information20 input_value,
169 action_information22
170 input_value_uom
171 from pay_payroll_actions paa, pay_assignment_actions assg, pay_action_information pai
172 where paa.payroll_action_id = p_payroll_action_id
173 and assg.payroll_action_id = paa.payroll_action_id
174 and pai.action_context_id = assg.assignment_action_id
175 and pai.action_context_type = 'AAP'
176 and pai.action_information_category = 'EMEA REPORT INFORMATION'
177 and action_information1 = 'PYFIPSTA'
178 and action_information2 = 'Car Benefit Details'
179 and action_information3 = p_person_id
180 and action_information19 is not null
181 order by car_pay_period_start_date; --, to_number(nvl(benefits_in_kind,0)) desc;
182 -- rl_csr_get_payroll_total csr_get_payroll_total%rowtype;
183 l_counter number := 0;
184 l_count number := 0;
185 l_payroll_action_id number;
186 xml_ctr number;
187 l_report_date date;
188 l_total_count number;
189 l_pay_period number;
190 l_salary_income_total number;
191 l_benefits_in_kind_total number;
192 l_benefit_monetary_value_total number;
193 l_sal_sub_tax_total number;
194 l_tax_amount_total number;
195 l_deductions_b_tax_total number;
196 l_net_salary_total number;
197 l_pension_total number;
198 l_unemployment_insurance_total number;
199 l_trade_union_fee_total number;
200 l_external_compensation_total number;
201 l_car_benefit_start_date date;
202 l_car_benefit_total number;
203 begin
204 if p_payroll_action_id is null then
205 begin
206 select payroll_action_id
207 into l_payroll_action_id
208 from pay_payroll_actions ppa, fnd_conc_req_summary_v fcrs, fnd_conc_req_summary_v fcrs1
209 where fcrs.request_id = fnd_global.conc_request_id
210 and fcrs.priority_request_id = fcrs1.priority_request_id
211 and ppa.request_id between fcrs1.request_id and fcrs.request_id
212 and ppa.request_id = fcrs1.request_id;
213 exception
214 when others then
215 null;
216 end;
217 else
218 l_payroll_action_id := p_payroll_action_id;
219 end if;
220
221 select sysdate
222 into l_report_date
223 from dual;
224
225 open csr_get_hdr_info (l_payroll_action_id);
226 fetch csr_get_hdr_info into rl_hdr_info;
227 close csr_get_hdr_info;
228 --
229 xml_tab (l_counter).tagname := 'LEGAL_EMPLOYER_NAME';
230 xml_tab (l_counter).tagvalue := rl_hdr_info.legal_employer_name;
231 l_counter := l_counter + 1;
232 --
233 xml_tab (l_counter).tagname := 'EFFECTIVE_DATE';
234 xml_tab (l_counter).tagvalue := rl_hdr_info.effective_date;
235 l_counter := l_counter + 1;
236
237 if rl_hdr_info.local_unit_name is not null then
238 --
239 xml_tab (l_counter).tagname := 'ORG_NAME';
240 xml_tab (l_counter).tagvalue := rl_hdr_info.local_unit_name;
241 l_counter := l_counter + 1;
242 --
243 else
244 --
245 xml_tab (l_counter).tagname := 'ORG_NAME';
246 xml_tab (l_counter).tagvalue := rl_hdr_info.legal_employer_name;
247 l_counter := l_counter + 1;
248 --
249 end if;
250
251 --
252 xml_tab (l_counter).tagname := 'YEAR';
253 xml_tab (l_counter).tagvalue := rl_hdr_info.year;
254 l_counter := l_counter + 1;
255 --
256 xml_tab (l_counter).tagname := 'REPORT_DATE';
257 xml_tab (l_counter).tagvalue := l_report_date;
258 l_counter := l_counter + 1;
259 --
260 xml_tab (l_counter).tagname := 'LOCAL_UNIT_NAME';
261 xml_tab (l_counter).tagvalue := rl_hdr_info.local_unit_name;
262 l_counter := l_counter + 1;
263
264 --
265 for l_get_local_unit_detail in csr_get_local_unit_details (l_payroll_action_id)
266 loop
267 l_total_count := 0;
268 open cst_get_emp_count (l_payroll_action_id, rl_hdr_info.legal_employer_id, l_get_local_unit_detail.local_unit_id);
269 fetch cst_get_emp_count into l_total_count;
270 close cst_get_emp_count;
271
272 if l_total_count > 0 then
273 xml_tab (l_counter).tagname := 'LU_NAME';
274 xml_tab (l_counter).tagvalue := l_get_local_unit_detail.local_unit_name;
275 l_counter := l_counter + 1;
276 --
277 xml_tab (l_counter).tagname := 'LEGAL_EMP_NAME';
278 xml_tab (l_counter).tagvalue := rl_hdr_info.legal_employer_name;
279 l_counter := l_counter + 1;
280 --
281 xml_tab (l_counter).tagname := 'ORG_ADD_LINE1';
282 xml_tab (l_counter).tagvalue := l_get_local_unit_detail.address_line_1;
283 l_counter := l_counter + 1;
284 --
285 xml_tab (l_counter).tagname := 'ORG_ADD_LINE2';
286 xml_tab (l_counter).tagvalue := l_get_local_unit_detail.address_line_2;
287 l_counter := l_counter + 1;
288 --
289 xml_tab (l_counter).tagname := 'ORG_ADD_LINE3';
290 xml_tab (l_counter).tagvalue := l_get_local_unit_detail.address_line_3;
291 l_counter := l_counter + 1;
292 --
293 xml_tab (l_counter).tagname := 'ORG_POSTAL_CODE';
294 xml_tab (l_counter).tagvalue := l_get_local_unit_detail.postal_code;
295 l_counter := l_counter + 1;
296 --
297 xml_tab (l_counter).tagname := 'ORG_COUNTRY';
298 xml_tab (l_counter).tagvalue := l_get_local_unit_detail.country;
299 l_counter := l_counter + 1;
300
301 for i in csr_get_detail_info (l_payroll_action_id, l_get_local_unit_detail.local_unit_id)
302 loop
303 xml_tab (l_counter).tagname := 'EMPLOYEE_NUMBER';
304 xml_tab (l_counter).tagvalue := i.employee_number;
305 l_counter := l_counter + 1;
306 --
307 xml_tab (l_counter).tagname := 'EMP_PIN';
308 xml_tab (l_counter).tagvalue := i.pin;
309 l_counter := l_counter + 1;
310 --
311 xml_tab (l_counter).tagname := 'FULL_NAME';
312 xml_tab (l_counter).tagvalue := i.emp_name;
313 l_counter := l_counter + 1;
314 --
315 xml_tab (l_counter).tagname := 'DATE_OF_BIRTH';
316 xml_tab (l_counter).tagvalue := i.date_of_birth;
317 l_counter := l_counter + 1;
318 --
319 xml_tab (l_counter).tagname := 'JOB';
320 xml_tab (l_counter).tagvalue := i.job_name;
321 l_counter := l_counter + 1;
322 --
323 xml_tab (l_counter).tagname := 'SALARY_BASIS';
324 xml_tab (l_counter).tagvalue := i.salary_basis;
325 l_counter := l_counter + 1;
326 --
327 xml_tab (l_counter).tagname := 'TAX_CARD_TYPE';
328 xml_tab (l_counter).tagvalue := i.tax_card_type;
329 l_counter := l_counter + 1;
330 --
331 xml_tab (l_counter).tagname := 'TAX_MUNICIPALITY';
332 xml_tab (l_counter).tagvalue := i.tax_municipality;
333 l_counter := l_counter + 1;
334 --
335 xml_tab (l_counter).tagname := 'BASE_RATE';
336 xml_tab (l_counter).tagvalue := i.base_rate;
337 l_counter := l_counter + 1;
338 --
339 xml_tab (l_counter).tagname := 'ADDITIONAL_RATE';
340 xml_tab (l_counter).tagvalue := i.additional_rate;
341 l_counter := l_counter + 1;
342 --
343 xml_tab (l_counter).tagname := 'ADDITIONAL_RATE';
344 xml_tab (l_counter).tagvalue := i.additional_rate;
345 l_counter := l_counter + 1;
346 --
347 xml_tab (l_counter).tagname := 'YEARLY_INCOME_LIMIT';
348 xml_tab (l_counter).tagvalue := i.yearly_income_limit;
349 l_counter := l_counter + 1;
350 --
351 xml_tab (l_counter).tagname := 'ACTUAL_TAX_DAYS';
352 xml_tab (l_counter).tagvalue := i.actual_tax_days;
353 l_counter := l_counter + 1;
354 --
355 xml_tab (l_counter).tagname := 'INSURANCE_SALARY';
356 xml_tab (l_counter).tagvalue := i.insurance_salary;
357 l_counter := l_counter + 1;
358 --
359 xml_tab (l_counter).tagname := 'PER_ADDRESS_LINE1';
360 xml_tab (l_counter).tagvalue := i.permanent_address_line1;
361 l_counter := l_counter + 1;
362 --
363 xml_tab (l_counter).tagname := 'PER_ADDRESS_LINE2';
364 xml_tab (l_counter).tagvalue := i.permanent_address_line2;
365 l_counter := l_counter + 1;
366 --
367 xml_tab (l_counter).tagname := 'PER_ADDRESS_LINE3';
368 xml_tab (l_counter).tagvalue := i.permanent_address_line3;
369 l_counter := l_counter + 1;
370 --
371 xml_tab (l_counter).tagname := 'PER_COUNTRY_CODE';
372 xml_tab (l_counter).tagvalue := i.permanent_country;
373 l_counter := l_counter + 1;
374 --
375 xml_tab (l_counter).tagname := 'PER_POSTAL_CODE';
376 xml_tab (l_counter).tagvalue := i.permanent_postal_code;
377 l_counter := l_counter + 1;
378 --
379 xml_tab (l_counter).tagname := 'ADDRESS_LINE1';
380 xml_tab (l_counter).tagvalue := i.address_line1;
381 l_counter := l_counter + 1;
382 --
383 xml_tab (l_counter).tagname := 'ADDRESS_LINE2';
384 xml_tab (l_counter).tagvalue := i.address_line2;
385 l_counter := l_counter + 1;
386 --
387 xml_tab (l_counter).tagname := 'ADDRESS_LINE3';
388 xml_tab (l_counter).tagvalue := i.address_line3;
389 l_counter := l_counter + 1;
390 --
391 xml_tab (l_counter).tagname := 'COUNTRY_CODE';
392 xml_tab (l_counter).tagvalue := i.country;
393 l_counter := l_counter + 1;
394 --
395 xml_tab (l_counter).tagname := 'POSTAL_CODE';
396 xml_tab (l_counter).tagvalue := i.postal_code;
397 l_counter := l_counter + 1;
398 l_salary_income_total := 0;
399 l_benefits_in_kind_total := 0;
400 l_benefit_monetary_value_total := 0;
401 l_sal_sub_tax_total := 0;
402 l_tax_amount_total := 0;
403 l_deductions_b_tax_total := 0;
404 l_net_salary_total := 0;
405 l_pension_total := 0;
406 l_unemployment_insurance_total := 0;
407 l_trade_union_fee_total := 0;
408 l_external_compensation_total := 0;
409 l_car_benefit_total := 0;
410
411 for j in csr_get_payroll_detais (l_payroll_action_id, i.person_id)
412 loop
413 xml_tab (l_counter).tagname := 'PAY_PERIOD';
414 xml_tab (l_counter).tagvalue := j.pay_period;
415 l_counter := l_counter + 1;
416
417 --
418 xml_tab (l_counter).tagname := 'PAY_PERIOD_START_DATE';
419 xml_tab (l_counter).tagvalue := j.pay_period_start_date;
420 l_counter := l_counter + 1;
421 --
422 xml_tab (l_counter).tagname := 'PAY_PERIOD_END_DATE';
423 xml_tab (l_counter).tagvalue := j.pay_period_end_date;
424 l_counter := l_counter + 1;
425 --
426 xml_tab (l_counter).tagname := 'SALARY_INCOME';
427 xml_tab (l_counter).tagvalue := j.salary_income;
428 l_counter := l_counter + 1;
429 --
430 xml_tab (l_counter).tagname := 'BENEFITS_IN_KIND';
431 xml_tab (l_counter).tagvalue := j.benefits_in_kind;
432 l_counter := l_counter + 1;
433 --
434 xml_tab (l_counter).tagname := 'CAR_BENFIT';
435 xml_tab (l_counter).tagvalue := j.car_benefit;
436 l_counter := l_counter + 1;
437 --
438 xml_tab (l_counter).tagname := 'BENEFIT_TYPE';
439 xml_tab (l_counter).tagvalue := j.benefit_type;
440 l_counter := l_counter + 1;
441 --
442 xml_tab (l_counter).tagname := 'DEDUCTIONS_B_TAX';
443 xml_tab (l_counter).tagvalue := j.deductions_b_tax;
444 l_counter := l_counter + 1;
445 --
446 /* Standard Deductions */
447 xml_tab (l_counter).tagname := 'PENSION';
448 xml_tab (l_counter).tagvalue := j.pension;
449 l_counter := l_counter + 1;
450 --
451 xml_tab (l_counter).tagname := 'UNEMP_INSURANCE';
452 xml_tab (l_counter).tagvalue := j.unemployment_insurance;
453 l_counter := l_counter + 1;
454 --
455 xml_tab (l_counter).tagname := 'TRADE_UNION_FEE';
456 xml_tab (l_counter).tagvalue := j.trade_union_fee;
457 l_counter := l_counter + 1;
458 --
459 /* End of Standard Deductions */
460 xml_tab (l_counter).tagname := 'EXTERNAL_COMPENSATION';
461 xml_tab (l_counter).tagvalue := j.external_compensation;
462 l_counter := l_counter + 1;
463 --
464 xml_tab (l_counter).tagname := 'BENEFIT_MONETARY_VALUE';
465 xml_tab (l_counter).tagvalue := j.benefit_monetary_value;
466 l_counter := l_counter + 1;
467 --
468 xml_tab (l_counter).tagname := 'SAL_SUBJECT_TAX';
469 xml_tab (l_counter).tagvalue := j.sal_sub_tax;
470 l_counter := l_counter + 1;
471 --
472 xml_tab (l_counter).tagname := 'TAX_AMOUNT';
473 xml_tab (l_counter).tagvalue := j.tax_amount;
474 l_counter := l_counter + 1;
475 --
476 xml_tab (l_counter).tagname := 'NET_SALARY';
477 xml_tab (l_counter).tagvalue := j.net_salary;
478 l_counter := l_counter + 1;
479 l_salary_income_total := l_salary_income_total + j.salary_income;
480 l_benefits_in_kind_total := l_benefits_in_kind_total + j.benefits_in_kind;
481 -- l_benefit_monetary_value_total :=0;
482 l_sal_sub_tax_total := l_sal_sub_tax_total + j.sal_sub_tax;
483 l_tax_amount_total := l_tax_amount_total + j.tax_amount;
484 l_deductions_b_tax_total := l_deductions_b_tax_total + j.deductions_b_tax;
485 l_net_salary_total := l_net_salary_total + j.net_salary;
486 l_pension_total := l_pension_total + j.pension;
487 l_unemployment_insurance_total := l_unemployment_insurance_total + j.unemployment_insurance;
488 l_trade_union_fee_total := l_trade_union_fee_total + j.trade_union_fee;
489 l_external_compensation_total := l_external_compensation_total + j.external_compensation;
490 l_car_benefit_total := l_car_benefit_total + j.car_benefit;
491 end loop;
492
493 --
494 xml_tab (l_counter).tagname := 'SALARY_INCOME_TOTAL';
495 xml_tab (l_counter).tagvalue := l_salary_income_total;
496 l_counter := l_counter + 1;
497 --
498 xml_tab (l_counter).tagname := 'BENEFITS_IN_KIND_TOTAL';
499 xml_tab (l_counter).tagvalue := l_benefits_in_kind_total;
500 l_counter := l_counter + 1;
501 xml_tab (l_counter).tagname := 'CAR_BENEFIT_TOTAL';
502 xml_tab (l_counter).tagvalue := l_car_benefit_total;
503 l_counter := l_counter + 1;
504 --
505 /* xml_tab (l_counter).tagname := 'BENEFIT_MONETARY_VALUE_TOTAL';
506 xml_tab (l_counter).tagvalue :=
507 rl_csr_get_payroll_total.benefit_monetary_value_total;
508 l_counter := l_counter + 1;*/
509 --
510 xml_tab (l_counter).tagname := 'SAL_SUB_TAX_TOTAL';
511 xml_tab (l_counter).tagvalue := l_sal_sub_tax_total;
512 l_counter := l_counter + 1;
513 --
514 xml_tab (l_counter).tagname := 'TAX_AMOUNT_TOTAL';
515 xml_tab (l_counter).tagvalue := l_tax_amount_total;
516 l_counter := l_counter + 1;
517 --
518 xml_tab (l_counter).tagname := 'DEDUCTIONS_B_TAX_TOTAL';
519 xml_tab (l_counter).tagvalue := l_deductions_b_tax_total;
520 l_counter := l_counter + 1;
521 --
522 xml_tab (l_counter).tagname := 'NET_SALARY_TOTAL';
523 xml_tab (l_counter).tagvalue := l_net_salary_total;
524 l_counter := l_counter + 1;
525 --
526 xml_tab (l_counter).tagname := 'PENSION_TOTAL';
527 xml_tab (l_counter).tagvalue := l_pension_total;
528 l_counter := l_counter + 1;
529 --
530 xml_tab (l_counter).tagname := 'UNEMPLOYMENT_INSURANCE_TOTAL';
531 xml_tab (l_counter).tagvalue := l_unemployment_insurance_total;
532 l_counter := l_counter + 1;
533 --
534 xml_tab (l_counter).tagname := 'TRADE_UNION_FEE_TOTAL';
535 xml_tab (l_counter).tagvalue := l_trade_union_fee_total;
536 l_counter := l_counter + 1;
537 --
538 xml_tab (l_counter).tagname := 'EXTERNAL_COMPENSATION_TOTAL';
539 xml_tab (l_counter).tagvalue := l_external_compensation_total;
540 l_counter := l_counter + 1;
541
542 for l_get_benefit in csr_get_benefits (l_payroll_action_id, i.person_id)
543 loop -- Start Loop for Benefits
544 xml_tab (l_counter).tagname := 'BENEFIT_NAME';
545 xml_tab (l_counter).tagvalue := l_get_benefit.benefit_type;
546 l_counter := l_counter + 1;
547 --
548 xml_tab (l_counter).tagname := 'BENEFIT_PAY_PERIOD_START_DATE';
549 xml_tab (l_counter).tagvalue := l_get_benefit.pay_period_start_date;
550 l_counter := l_counter + 1;
551 --
552 xml_tab (l_counter).tagname := 'BENEFIT_PAY_PERIOD_END_DATE';
553 xml_tab (l_counter).tagvalue := l_get_benefit.pay_period_end_date;
554 l_counter := l_counter + 1;
555 --
556 xml_tab (l_counter).tagname := 'BENEFIT_VALUE';
557 xml_tab (l_counter).tagvalue := l_get_benefit.benefit_value;
558 l_counter := l_counter + 1;
559 --
560 end loop; --End loop for benefits
561 -- Start Loop for Car Benefit
562 l_car_benefit_start_date := null;
563
564 for l_car_benefit in csr_get_car_benefit (l_payroll_action_id, i.person_id)
565 loop
566 if l_car_benefit_start_date is null or l_car_benefit_start_date <> l_car_benefit.car_pay_period_start_date then
567 xml_tab (l_counter).tagname := 'CAR_PAY_PERIOD_START_DATE';
568 xml_tab (l_counter).tagvalue := l_car_benefit.car_pay_period_start_date;
569 l_counter := l_counter + 1;
570 --
571 xml_tab (l_counter).tagname := 'CAR_PAY_PERIOD_END_DATE';
572 xml_tab (l_counter).tagvalue := l_car_benefit.car_pay_period_end_date;
573 l_counter := l_counter + 1;
574 l_car_benefit_start_date := l_car_benefit.car_pay_period_start_date;
575 --
576 end if;
577
578 xml_tab (l_counter).tagname := 'INPUT_VALUE_NAME';
579 xml_tab (l_counter).tagvalue := l_car_benefit.input_value_name;
580 l_counter := l_counter + 1;
581 --
582 --
583 xml_tab (l_counter).tagname := 'UOM';
584 xml_tab (l_counter).tagvalue := l_car_benefit.input_value_uom;
585 l_counter := l_counter + 1;
586 --
587 --
588 xml_tab (l_counter).tagname := 'INPUT_VALUE';
589 xml_tab (l_counter).tagvalue := l_car_benefit.input_value;
590 l_counter := l_counter + 1;
591 end loop;
592 end loop;
593 end if;
594 end loop;
595
596 writetoclob (p_xml);
597 exception
598 when others then
599 fnd_file.put_line (fnd_file.log, 'Inside Exception');
600 fnd_file.put_line (fnd_file.log, 'Error is - ' || sqlcode);
601 end;
602
603 procedure writetoclob (
604 p_xfdf_clob out nocopy clob
605 ) is
606 l_xfdf_string clob;
607 l_str1 varchar2 (1000);
608 l_str2 varchar2 (20);
609 l_str3 varchar2 (20);
610 l_str4 varchar2 (20);
611 l_str5 varchar2 (20);
612 l_str6 varchar2 (30);
613 l_str7 varchar2 (1000);
614 l_str8 varchar2 (240);
615 l_str9 varchar2 (240);
616 l_str10 varchar2 (20);
617 l_str11 varchar2 (20);
618 l_str12 varchar2 (30);
619 l_str13 varchar2 (30);
620 l_str14 varchar2 (30);
621 l_str15 varchar2 (30);
622 l_str16 varchar2 (30);
623 l_str17 varchar2 (30);
624 l_str18 varchar2 (50);
625 l_str19 varchar2 (30);
626 l_str20 varchar2 (50);
627 l_str21 varchar2 (30);
628 l_str22 varchar2 (50);
629 l_str23 varchar2 (30);
630 l_str24 varchar2 (30);
631 l_str25 varchar2 (30);
632 l_str26 varchar2 (30);
633 l_str27 varchar2 (30);
634 l_str28 varchar2 (30);
635 l_str29 varchar2 (30);
636 l_str30 varchar2 (30);
637 l_str31 varchar2 (30);
638 l_iana_charset varchar2 (30);
639 current_index pls_integer;
640 begin
641 -- hr_utility.set_location ('Entering WritetoCLOB ', 10);
642 l_iana_charset := hr_fi_utility.get_iana_charset;
643 l_str1 := '<?xml version="1.0" encoding="' || l_iana_charset || '"?> <ROOT><PAACR>';
644 l_str2 := '<';
645 l_str3 := '>';
646 l_str4 := '</';
647 l_str5 := '>';
648 l_str6 := '</PAACR></ROOT>';
649 l_str7 := '<?xml version="1.0" encoding="' || l_iana_charset || '"?> <ROOT></ROOT>';
650 l_str10 := '<PAACR>';
651 l_str11 := '</PAACR>';
652 l_str12 := '<FILE_HEADER_START>';
653 l_str13 := '</FILE_HEADER_START>';
654 l_str14 := '<LE_RECORD>';
655 l_str15 := '</LE_RECORD>';
656 l_str16 := '<EMP_RECORD>';
657 l_str17 := '</EMP_RECORD>';
658 l_str18 := '<PAY_RECORD>';
659 l_str19 := '</PAY_RECORD>';
660 l_str20 := '<PAY_TOTAL_RECORD>';
661 l_str21 := '</PAY_TOTAL_RECORD>';
662 l_str22 := '<LU_DETAIL>';
663 l_str23 := '</LU_DETAIL>';
664 l_str24 := '<BENEFIT_TYPE_INFO>';
665 l_str25 := '</BENEFIT_TYPE_INFO>';
666 l_str26 := '<BENEFIT_DETAIL>';
667 l_str27 := '</BENEFIT_DETAIL>';
668 l_str28 := '<CAR_BEN_PAY_PERIOD>';
669 l_str29 := '</CAR_BEN_PAY_PERIOD>';
670 dbms_lob.createtemporary (l_xfdf_string, false , dbms_lob.call);
671 dbms_lob.open (l_xfdf_string, dbms_lob.lob_readwrite);
672 current_index := 0;
673
674 if xml_tab.count > 0 then
675 dbms_lob.writeappend (l_xfdf_string, length (l_str1), l_str1);
676 dbms_lob.writeappend (l_xfdf_string, length (l_str12), l_str12);
677
678 for table_counter in xml_tab.first .. xml_tab.last
679 loop
680 l_str8 := xml_tab (table_counter).tagname;
681 l_str9 := xml_tab (table_counter).tagvalue;
682
683 if l_str8 = 'LEGAL_EMPLOYER_NAME' then
684 dbms_lob.writeappend (l_xfdf_string, length (l_str14), l_str14);
685 elsif l_str8 = 'LU_NAME' then
686 dbms_lob.writeappend (l_xfdf_string, length (l_str22), l_str22);
687 elsif l_str8 = 'EMPLOYEE_NUMBER' then
688 dbms_lob.writeappend (l_xfdf_string, length (l_str16), l_str16);
689 elsif l_str8 = 'SALARY_INCOME_TOTAL' then
690 dbms_lob.writeappend (l_xfdf_string, length (l_str20), l_str20);
691 elsif l_str8 = 'PAY_PERIOD' then
692 dbms_lob.writeappend (l_xfdf_string, length (l_str18), l_str18);
693 elsif l_str8 = 'BENEFIT_NAME' then
694 dbms_lob.writeappend (l_xfdf_string, length (l_str24), l_str24);
695 elsif l_str8 = 'CAR_PAY_PERIOD_START_DATE' then
696 dbms_lob.writeappend (l_xfdf_string, length (l_str28), l_str28);
697 elsif l_str8 = 'INPUT_VALUE_NAME' then
698 dbms_lob.writeappend (l_xfdf_string, length (l_str26), l_str26);
699 end if;
700
701 if l_str9 is not null then
702 l_str9 := '<![CDATA[' || l_str9 || ']]>';
703 dbms_lob.writeappend (l_xfdf_string, length (l_str2), l_str2);
704 dbms_lob.writeappend (l_xfdf_string, length (l_str8), l_str8);
705 dbms_lob.writeappend (l_xfdf_string, length (l_str3), l_str3);
706 dbms_lob.writeappend (l_xfdf_string, length (l_str9), l_str9);
707 dbms_lob.writeappend (l_xfdf_string, length (l_str4), l_str4);
708 dbms_lob.writeappend (l_xfdf_string, length (l_str8), l_str8);
709 dbms_lob.writeappend (l_xfdf_string, length (l_str5), l_str5);
710 else
711 dbms_lob.writeappend (l_xfdf_string, length (l_str2), l_str2);
712 dbms_lob.writeappend (l_xfdf_string, length (l_str8), l_str8);
713 dbms_lob.writeappend (l_xfdf_string, length (l_str3), l_str3);
714 dbms_lob.writeappend (l_xfdf_string, length (l_str4), l_str4);
715 dbms_lob.writeappend (l_xfdf_string, length (l_str8), l_str8);
716 dbms_lob.writeappend (l_xfdf_string, length (l_str5), l_str5);
717 end if;
718
719 if l_str8 = 'LOCAL_UNIT_NAME' then
720 dbms_lob.writeappend (l_xfdf_string, length (l_str15), l_str15);
721 end if;
722
723 if l_str8 = 'NET_SALARY' then
724 dbms_lob.writeappend (l_xfdf_string, length (l_str19), l_str19);
725 elsif l_str8 = 'EXTERNAL_COMPENSATION_TOTAL' then
726 /* if xml_tab.last = table_counter
727 or xml_tab (table_counter + 1).tagname <> 'PAY_PERIOD' then
728 dbms_lob.writeappend (
729 l_xfdf_string,
730 length (l_str17),
731 l_str17
732 );
733 end if;*/
734 dbms_lob.writeappend (l_xfdf_string, length (l_str21), l_str21);
735
736 if xml_tab.last = table_counter then
737 dbms_lob.writeappend (l_xfdf_string, length (l_str17), l_str17);
738 dbms_lob.writeappend (l_xfdf_string, length (l_str23), l_str23);
739 elsif xml_tab (table_counter + 1).tagname <> 'BENEFIT_NAME' then
740 dbms_lob.writeappend (l_xfdf_string, length (l_str17), l_str17);
741
742 if xml_tab (table_counter + 1).tagname <> 'EMPLOYEE_NUMBER' then
743 dbms_lob.writeappend (l_xfdf_string, length (l_str23), l_str23);
744 end if;
745 /* dbms_lob.writeappend (
746 l_xfdf_string,
747 length (l_str23),
748 l_str23
749 );*/
750 end if;
751 elsif l_str8 = 'BENEFIT_VALUE' then
752 dbms_lob.writeappend (l_xfdf_string, length (l_str25), l_str25);
753
754 if xml_tab.last = table_counter then
755 dbms_lob.writeappend (l_xfdf_string, length (l_str17), l_str17);
756 dbms_lob.writeappend (l_xfdf_string, length (l_str23), l_str23);
757 elsif xml_tab (table_counter + 1).tagname not in ('BENEFIT_NAME', 'CAR_PAY_PERIOD_START_DATE') then --'EMPLOYEE_NUMBER' then
758 dbms_lob.writeappend (l_xfdf_string, length (l_str17), l_str17);
759 if xml_tab (table_counter + 1).tagname = 'LU_NAME' then
760 dbms_lob.writeappend (l_xfdf_string, length (l_str23), l_str23);
761 end if;
762 end if;
763 elsif l_str8 = 'INPUT_VALUE' then
764 dbms_lob.writeappend (l_xfdf_string, length (l_str27), l_str27);
765
766 if xml_tab.last = table_counter then
767 dbms_lob.writeappend (l_xfdf_string, length (l_str29), l_str29);
768 dbms_lob.writeappend (l_xfdf_string, length (l_str17), l_str17);
769 dbms_lob.writeappend (l_xfdf_string, length (l_str23), l_str23);
770 elsif xml_tab (table_counter + 1).tagname = 'CAR_PAY_PERIOD_START_DATE' then
771 /* dbms_lob.writeappend (
772 l_xfdf_string,
773 length (l_str25),
774 l_str25
775 );*/
776 dbms_lob.writeappend (l_xfdf_string, length (l_str29), l_str29);
777 elsif xml_tab (table_counter + 1).tagname not in ('INPUT_VALUE_NAME', 'CAR_PAY_PERIOD_START_DATE') then --'EMPLOYEE_NUMBER' then
778 dbms_lob.writeappend (l_xfdf_string, length (l_str29), l_str29);
779 dbms_lob.writeappend (l_xfdf_string, length (l_str17), l_str17);
780 if xml_tab (table_counter + 1).tagname = 'LU_NAME' then
781 dbms_lob.writeappend (l_xfdf_string, length (l_str23), l_str23);
782 end if;
783 end if;
784 end if;
785 end loop;
786
787 dbms_lob.writeappend (l_xfdf_string, length (l_str13), l_str13);
788 dbms_lob.writeappend (l_xfdf_string, length (l_str6), l_str6);
789 else
790 dbms_lob.writeappend (l_xfdf_string, length (l_str7), l_str7);
791 end if;
792
793 p_xfdf_clob := l_xfdf_string;
794 hr_utility.set_location ('Leaving WritetoCLOB ', 20);
795 fnd_file.put_line (fnd_file.log, 'XML Part');
796 end writetoclob;
797 end pay_fi_pstr;