[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4A_XML
Source
1 package body pay_ca_t4a_xml as
2 /* $Header: pycat4axml.pkb 120.22.12020000.4 2013/01/17 06:45:57 rgottipa ship $ */
3 /*
4 Copyright (c) Oracle Corporation 2009. All rights reserved
5
6 Description : Package to build XML used for generation of T4A pdf
7
8 Change List
9 -----------
10 Date Name Vers Bug No Description
11 ----------- ---------- ----- ------- -----------------------------------
12 25-Aug-2009 sapalani 115.0 4932662 Initial Version
13 09-Sep-2009 sapalani 115.2 4932662 Only first 2 characters of Footnote code
14 should be reported in Box38. Inserted
15 blank space between characters for
16 proper alignment in the field in PDF.
17 14-Sep-2009 sapalani 115.3 8899845 Business Number has to be reported
18 only in the first slip. Also made
19 changes to report only 2 characters
20 for box38 in error report.
21 27-Nov-2009 sapalani 115.4 9156528 Concatenated the address lines into
22 one string with line breaks
23 inbetween. This is reported under
24 XML tag <emp_addr_X>.
25 09-Sep-2010 rgottipa 115.5 9984889 Modified get_asg_xml
26 to create xml tags for 2010 template
27 17-Sep-2010 rgottipa 115.8 9984889 increased size of varray because PDF request
28 is failing if empolyee is having more
29 other info elements
30 20-Sep-2010 rgottipa 115.9 9984889 Format for Other inforamtion data is
31 changed in creating XML
32 21-Sep-2010 rgottipa 115.10 9984889 l_k variable is passed as parameter
33 for convert_2_xml function.
34 27-Oct-2010 nkjaladi 115.11 5046003 Modified package to support the
35 T4A amend PDF report.
36
37 12-Nov-2010 rgottipa 115.12 10174115 l_first_nm length is changed from
38 12 to 15 characters in get_asg_xml()
39 22-Nov-2010 nkjaladi 115.13 10312756 Modified cursor c_t4a_othinfo_amts
40 in procedure store_other_information
41 to exclude balance
42 CAEOY_T4A_OTHER_INFO_AMOUNT036_PER_GRE_YTD
43 as CAEOY_T4A_EMPLOYEE_REGISTRATION_NO is
44 displayed in code 36
45
46 06-Dec-2010 rgottipa 115.17 10162688 XML structure modified such that
47 employee data displayed between tags
48 <T4A> </T4A>
49
50 03-Jan-2011 nkjaladi 115.18 10162688 Modified package to support the
51 T4A Cancel PDF report.
52
53 10-Jan-2011 sneelapa 115.19 10624469 Modified package to create new XML
54 tag blank_type if EMPLOYEE is having
55 even number of data pages.
56
57 12-Jan-2011 sneelapa 115.20 10624469 Modified package to create new XML
58 tag blank_type if EMPLOYEE is having
59 even number of data pages.
60
61 15-Feb-2011 sneelapa 115.21 11775104 Modified get_asg_xml procedure
62 to create box61 tag only when request
63 is submitted with type as "Employer".
64 03-Nov-2011 sgotlasw 115.22 12943928 Code has been modified whether to diplay address
65 or GRE of employer on T4A PDF, T4A AMENDED, T4A
66 CANCELLED based on the input parameter from year 2011.
67 06-Jun-2012 rgottipa 115.23 Created the new function 'get_final_xml' which
68 is used to call from online.
69 17-Jan-2013 rgottipa 115.25 16055709 For EMPLOYEER option, blank page is not needed.
70 */
71
72
73 FUNCTION append_to_lob(p_text in varchar)
74 RETURN BLOB IS
75
76 text_size NUMBER;
77 raw_data RAW(32767);
78 temp_blob BLOB;
79 BEGIN
80
81 raw_data:=utl_raw.cast_to_raw(p_text);
82 text_size:=utl_raw.length(raw_data);
83
84 dbms_lob.createtemporary(temp_blob,false,DBMS_LOB.CALL);
85 dbms_lob.open(temp_blob,dbms_lob.lob_readwrite);
86
87 dbms_lob.writeappend(temp_blob,
88 text_size,
89 raw_data);
90
91 IF dbms_lob.ISOPEN(temp_blob)=1 THEN
92 hr_utility.trace('Closing temp_lob' );
93 dbms_lob.close(temp_blob);
94 hr_utility.trace('Closed temp_lob' );
95 END IF;
96
97 return temp_blob;
98 END;
99
100 procedure store_other_information(p_assignment_action_id in number)
101 is
102
103 cursor c_t4a_othinfo_amts_1 is
104 select decode(fdi.user_name, 'CAEOY_EMPLOYEE_NUMBER', '014',
105 'CAEOY_T4A_EMPLOYEE_REGISTRATION_NO', '036', substr(fdi.user_name,28,3)) code,
106 fai.value value
107 from ff_database_items fdi,
108 ff_archive_items fai
109 where fai.user_entity_id=fdi.user_entity_id
110 and fai.context1= p_assignment_action_id
111 and (((fdi.user_name LIKE 'CAEOY_T4A_OTHER_INFO_AMOUNT%_PER_GRE_YTD')
112 and (fdi.user_name <> 'CAEOY_T4A_OTHER_INFO_AMOUNT036_PER_GRE_YTD')) -- #10312756
113 or fdi.user_name in ('CAEOY_EMPLOYEE_NUMBER', 'CAEOY_T4A_EMPLOYEE_REGISTRATION_NO'))
114 -- and fai.value <> '0' by rgottipa for bug 6456662
115 order by code;
116
117 -- Added cursor for fix of #10312756
118 cursor c_t4a_othinfo_amts_2 is
119 select decode(fdi.user_name, 'CAEOY_EMPLOYEE_NUMBER', '014', substr(fdi.user_name,28,3)) code,
120 fai.value value
121 from ff_database_items fdi,
122 ff_archive_items fai
123 where fai.user_entity_id=fdi.user_entity_id
124 and fai.context1= p_assignment_action_id
125 and ((fdi.user_name LIKE 'CAEOY_T4A_OTHER_INFO_AMOUNT%_PER_GRE_YTD')
126 or fdi.user_name in ('CAEOY_EMPLOYEE_NUMBER'))
127 order by code;
128
129 lv_index number;
130 l_code36_exists varchar(1) :='N'; --#10312756
131 begin
132
133 begin
134 select 'Y'
135 into l_code36_exists
136 from ff_database_items fdi,
137 ff_archive_items fai
138 where fai.user_entity_id=fdi.user_entity_id
139 and fai.context1= p_assignment_action_id
140 and fdi.user_name ='CAEOY_T4A_EMPLOYEE_REGISTRATION_NO';
141 exception
142 when no_data_found then
143 l_code36_exists := 'N';
144 end;
145
146 g_other_info_list.delete;
147 lv_index := 1;
148
149 if l_code36_exists = 'Y' then
150 for rec in c_t4a_othinfo_amts_1 loop
151 g_other_info_list(lv_index).code := rec.code;
152 if rec.code = '034' then
153 g_other_info_list(lv_index).amount:=ROUND(to_number(rec.value));
154 else
155 g_other_info_list(lv_index).amount := rec.value;
156 end if;
157 lv_index := lv_index + 1;
158 end loop;
159 else
160 for rec in c_t4a_othinfo_amts_2 loop
161 g_other_info_list(lv_index).code := rec.code;
162 if rec.code = '034' then
163 g_other_info_list(lv_index).amount:=ROUND(to_number(rec.value));
164 else
165 g_other_info_list(lv_index).amount := rec.value;
166 end if;
167 lv_index := lv_index + 1;
168 end loop;
169 end if;
170
171 end store_other_information;
172
173
174 procedure get_other_information(p_index in number,
175 p_code in out nocopy varchar2,
176 p_amount in out nocopy varchar2)
177 is
178 begin
179
180 if g_other_info_list.exists(p_index) then
181
182 p_code := g_other_info_list(p_index).code;
183 p_amount := g_other_info_list(p_index).amount;
184
185 else
186
187 p_code := null;
188 p_amount := null;
189
190 end if;
191
192 end get_other_information;
193
194 /*
195 This function is used to call from SSHR, to generate
196 XML for online T4A
197 */
198 FUNCTION get_final_xml (p_assignment_action_id Number)
199 RETURN BLOB IS
200 p_xml_blob BLOB;
201 l_final_xml BLOB;
202 l_header_xml_string VARCHAR2(32000);
203 l_trailer_xml VARCHAR2(32000);
204 l_pa_id number;
205 l_temp_blob BLOB;
206 EOL varchar2(10);
207 l_xml_version varchar2(100);
208 l_is_temp_final_xml VARCHAR2(2);
209 lv_negative_bal_flag VARCHAR2(5);
210 l_err_msg hr_lookups.meaning%TYPE;
211
212 /* To get error message */
213 cursor cur_get_meaning(p_lookup_code VARCHAR2) IS
214 select
215 meaning
216 from
217 hr_lookups
218 where
219 lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
220 lookup_code = p_lookup_code;
221
222 begin
223
224 hr_utility.trace('In get_final_xml 10');
225 hr_utility.trace('p_assignment_action_id '||p_assignment_action_id);
226 -- hr_utility.trace('p_output_location '||p_output_location);
227
228 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
229
230 l_header_xml_string := '<T4APAPER>'||EOL;
231 hr_utility.trace('In get_final_xml 20');
232
233 select paa.payroll_action_id
234 into l_pa_id
235 from pay_assignment_actions paa
236 where paa.assignment_action_id = p_assignment_action_id;
237
238 lv_negative_bal_flag := 'N';
239 lv_negative_bal_flag := pay_ca_archive_utils.get_archive_value(p_assignment_action_id ,
240 'CAEOY_T4A_NEGATIVE_BALANCE_EXISTS');
241 hr_utility.trace('lv_negative_bal_flag '||lv_negative_bal_flag);
242
243 if (lv_negative_bal_flag is NULL or lv_negative_bal_flag = 'N') then
244 g_err_emp := NULL;
245 fetch_t4a_xml(p_assignment_action_id,
246 l_pa_id,
247 'Y',
248 'get_final_xml',
249 p_xml_blob
250 );
251 hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
252 hr_utility.trace('1. final 1. XML l_final_xml '||
253 dbms_lob.substr(l_final_xml,dbms_lob.getlength(l_final_xml),1));
254 hr_utility.trace('In get_final_xml 30');
255
256 l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
257 hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
258
259 IF l_is_temp_final_xml = 1 THEN
260 DBMS_LOB.FREETEMPORARY(l_final_xml);
261 END IF;
262
263 dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
264 dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
265 l_final_xml := append_to_lob(l_header_xml_string);
266 dbms_lob.append(l_final_xml,p_xml_blob);
267 hr_utility.trace('In get_final_xml 40');
268
269 l_trailer_xml := '</T4APAPER>'||EOL;
270
271 hr_utility.trace('In get_final_xml 50');
272 dbms_lob.createtemporary(l_temp_blob,false,DBMS_LOB.CALL);
273 dbms_lob.open(l_temp_blob,dbms_lob.lob_readwrite);
274 l_temp_blob := append_to_lob(l_trailer_xml);
275 dbms_lob.append(l_final_xml,l_temp_blob);
276
277 IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
278 hr_utility.trace('Closing l_final_xml' );
279 dbms_lob.close(l_final_xml);
280 END IF;
281 IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
282 hr_utility.trace('Closing p_xml_blob' );
283 dbms_lob.close(p_xml_blob);
284 END IF;
285 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
286 hr_utility.trace('Closing l_temp_blob' );
287 dbms_lob.close(l_temp_blob);
288 END IF;
289 hr_utility.trace('In get_final_xml 60');
290
291 else -- lv_negative_bal_flag = 'Y'
292 hr_utility.trace('Negative employee');
293 OPEN cur_get_meaning('NEG');
294 FETCH cur_get_meaning
295 INTO l_err_msg;
296 CLOSE cur_get_meaning;
297
298 l_err_msg := 'Error:'||l_err_msg;
299 l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
300 hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
301
302 IF l_is_temp_final_xml = 1 THEN
303 DBMS_LOB.FREETEMPORARY(l_final_xml);
304 END IF;
305
306 dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
307 dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
308 l_final_xml := append_to_lob(l_err_msg);
309 hr_utility.trace('In get_final_xml 61');
310 IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
311 hr_utility.trace('Closing l_final_xml' );
312 dbms_lob.close(l_final_xml);
313 END IF;
314 hr_utility.trace('In get_final_xml 62');
315 end if;
316 hr_utility.trace('In get_final_xml 65');
317 return l_final_xml;
318
319 exception
320 when others then
321 hr_utility.trace('In get_final_xml 70');
322
323 IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
324 hr_utility.trace('Raising exception and Closing l_final_xml' );
325 dbms_lob.close(l_final_xml);
326 END IF;
327 IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
328 hr_utility.trace('Raising exception and Closing p_xml_string' );
329 dbms_lob.close(p_xml_blob);
330 END IF;
331 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
332 hr_utility.trace('Closing l_temp_blob' );
333 dbms_lob.close(l_temp_blob);
334 END IF;
335
336 hr_utility.trace('sqleerm ' || SQLERRM);
337 raise;
338 end get_final_xml;
339
340 procedure get_asg_xml is
341
342 l_aa_id number;
343 l_pa_id number;
344 p_print varchar2(240);
345 l_xml_blob BLOB;
346 cursor c_get_params is
347 select paa1.assignment_action_id, -- archiver asg action
348 ppa1.payroll_action_id, -- archiver pact
349 pay_ca_t4_reg.get_parameter('PRINT',ppa.legislative_parameters) -- Added for Bug 12943928
350 from pay_assignment_actions paa,
351 pay_payroll_actions ppa,
352 pay_assignment_actions paa1,
353 pay_payroll_actions ppa1
354 where ppa.payroll_action_id = paa.payroll_action_id
355 and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
356 and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
357 and fnd_number.canonical_to_number(substr(paa.serial_number,1,14)) = paa1.assignment_action_id
358 and paa1.payroll_action_id = ppa1.payroll_action_id
359 --and ppa1.report_type = 'T4A'
360 and ( ((ppa.report_type = 'PAYCAT4ACLPDF')
361 and ppa1.report_type in ('CAEOY_T4A_AMEND_PP','T4A'))
362 or
363 (ppa1.report_type = decode(ppa.report_type,'PAYCAT4AAMPDF','CAEOY_T4A_AMEND_PP','T4A')) --#5046003 included PAYCAT4AAMPDF
364 ) --# 10359119 Added the or condition.
365 and ppa1.action_type = 'X'
366 and ppa1.action_status = 'C'
367 and ppa1.effective_date = ppa.effective_date;
368
369 begin
370 hr_utility.trace('Entering in get_asg_xml');
371 open c_get_params;
372 fetch c_get_params into
373 l_aa_id,
374 l_pa_id,
375 p_print; -- Added for Bug 12943928
376 close c_get_params;
377 hr_utility.trace('l_aa_id '||l_aa_id);
378 hr_utility.trace('l_pa_id '||l_pa_id);
379 hr_utility.trace('p_print '||p_print);
380 fetch_t4a_xml(l_aa_id,
381 l_pa_id,
382 p_print,
383 'get_asg_xml',
384 l_xml_blob);
385 hr_utility.trace('Leaving from get_asg_xml');
386 end get_asg_xml;
387
388 /* this procedure used to genarate XML data for both
389 Concurrent program and Online T4A */
390 procedure fetch_t4a_xml(p_aa_id in number,
391 p_pa_id in number,
392 p_print in varchar2,
393 called_from in varchar2,
394 p_xml_blob out NOCOPY BLOB)
395 is
396
397 l_aa_id number;
398 l_pa_id number;
399
400 EOL varchar2(10);
401
402 l_employee_xml varchar2(32767);
403 l_box61_xml varchar2(200);
404 l_year varchar2(5);
405 l_box16 varchar2(50);
406 l_box18 varchar2(50);
407 l_box20 varchar2(50);
408 l_box22 varchar2(50);
409 l_box24 varchar2(50);
410 -- these _1 boxes added by rgottipa for bug 6456662
411 l_box16_1 varchar2(50);
412 l_box18_1 varchar2(50);
413 l_box20_1 varchar2(50);
414 l_box22_1 varchar2(50);
415 l_box24_1 varchar2(50);
416 l_box48_1 varchar2(50);
417 l_box26 varchar2(50);
418 l_box27 varchar2(50);
419 l_box28 varchar2(50);
420 l_box30 varchar2(50);
421 l_box32 varchar2(50);
422 l_box34 varchar2(50);
423 l_box36 varchar2(50);
424 l_box40 varchar2(50);
425 l_box42 varchar2(50);
426 l_box46 varchar2(50);
427 l_box48 varchar2(50);
428 l_box12 varchar2(50);
429 l_box38 varchar2(10);
430 l_box13 varchar2(50);
431 l_box14 varchar2(50);
432 l_box61 varchar2(50);
433 l_payer_nm varchar2(200);
434 l_last_nm varchar2(150);
435 l_first_nm varchar2(150);
436 l_init varchar2(30);
437 l_person_id varchar2(30);
438
439 address pay_ca_rl1_reg.primaryaddress;
440 lv_employee_address1 varchar2(200);
441 lv_employee_address2 varchar2(200);
442 lv_employee_address3 varchar2(200);
443 lv_employee_city varchar2(200);
444 lv_employee_province varchar2(200);
445 lv_employee_country varchar2(200);
446 lv_employee_postal_code varchar2(10);
447 l_emp_addr varchar2(10000);
448
449 l_organization_id varchar2(50);
450 l_gre_id varchar2(50);
451 l_location_id varchar2(50);
452 l_sort1 varchar2(200);
453 l_sort2 varchar2(200);
454 l_sort3 varchar2(200);
455 l_sort varchar2(1000);
456
457 /* Bug 12943928 - Employer address, GRE related variables have been
458 added to display address on T4A PDf, T4A AMENDED,
459 T4A CANCELLED from year 2011 */
460 lv_employer_address1 varchar2(200);
461 lv_employer_address2 varchar2(200);
462 lv_employer_address3 varchar2(200);
463 lv_employer_city varchar2(200);
464 lv_employer_province varchar2(200);
465 lv_employer_country varchar2(200);
466 lv_employer_postal_code varchar2(10);
467 lv_employer_address varchar2(10000);
468 lv_gre_name varchar2(100);
469
470 l_footnote_code1 varchar2(200) := '';
471 l_footnote_value1 varchar2(30) := '';
472 l_footnote_code2 varchar2(200) := '';
473 l_footnote_value2 varchar2(30) := '';
474 l_footnote_code3 varchar2(200) := '';
475 l_footnote_value3 varchar2(30) := '';
476 l_footnote_code4 varchar2(200) := '';
477 l_footnote_value4 varchar2(30) := '';
478 --l_fncodes varchar2(1000);
479 --l_fnvalues varchar2(1000);
480
481 --lv_negative_bal_flag varchar2(5);
482 l_lang varchar2(5);
483 l_date date;
484 i number := 0;
485 l_count number := 0;
486 l_msg_code varchar2(30);
490
487 l_err_msg hr_lookups.meaning%TYPE;
488 l_is_temp_final_xml VARCHAR2(2);
489
491 --array introduced by rgottipa for bug 6456662
492
493 TYPE OTHER_CODES IS VARRAY(70) OF VARCHAR2(3);
494 TYPE OTHER_AMOUNTS IS VARRAY(70) OF VARCHAR2(50);
495 l_other_code OTHER_CODES:=OTHER_CODES('','','','','','','','','','','','','',
496 '','','','','','','','','','','','','',
497 '','','','','','','','','','','','','',
498 '','','','','','','','','','','','',
499 '','','','','','','','','','','','',
500 '','','','','','','');
501 l_other_amount OTHER_AMOUNTS:=OTHER_AMOUNTS('','','','','','','','','','','','','',
502 '','','','','','','','','','','','','',
503 '','','','','','','','','','','','','',
504 '','','','','','','','','','','','',
505 '','','','','','','','','','','','',
506 '','','','','','','');
507
508 --l_show_page3 varchar2(10); -- bug 10174115
509 num_other_info number:=0; --end
510
511 l_code varchar2(3);
512 l_amount varchar2(50);
513
514 l_i number;
515 l_j number;
516 l_k number;
517 l_m number;
518
519 l_t4a_slip_count number;
520 l_other_info_count number;
521 l_type varchar2(50); -- Added for #5046003
522
523 -- Variables introduced for bug 6456662
524
525
526 /* T4A_FOOTNOTE */
527 cursor c_t4a_footnote(p_assignment_action_id number) is
528 select
529 code,
530 value
531 from ( select decode(l_box38,'13',hl.lookup_code,
532 SUBSTR(fdi.user_name,11,5)||': '||
533 SUBSTR(pay_ca_t4a_reg.get_label('PAY_CA_T4A_FOOTNOTES',
534 hl.lookup_code, l_lang),1,46)) code,
535 to_number(fai.value) value
536 from hr_lookups HL,
537 ff_database_items fdi,
538 ff_archive_items fai
539 where fai.user_entity_id=fdi.user_entity_id
540 and fai.context1= p_assignment_action_id
541 and fdi.user_name like 'CAEOY_T4A_BOX%_%_AMT_PER_GRE_YTD'
542 and fai.value <> '0'
543 and hl.lookup_type = 'PAY_CA_T4A_FOOTNOTES'
544 and decode(HL.LOOKUP_CODE,'10(BOX24)','10A',hl.lookup_code) =
545 SUBSTR(FDI.USER_NAME, 17, instr(fdi.user_name,'AMT') - 18 )
546 union all
547 select
548 decode(l_box38,'13',hl.lookup_code,
549 pay_ca_t4a_reg.get_label('PAY_CA_T4A_NONBOX_FOOTNOTES',
550 hl.lookup_code, l_lang)),
551 to_number(pai.action_information5)
552 from pay_action_information pai,
553 hr_lookups hl
554 where pai.action_context_id = p_assignment_action_id
555 and hl.lookup_type = 'PAY_CA_T4A_NONBOX_FOOTNOTES'
556 and hl.lookup_code = pai.action_information4
557 and pai.action_information6 = 'T4A')
558 where rownum < 5
559 order by code;
560
561 /* To get error message */
562 cursor cur_get_meaning(p_lookup_code VARCHAR2) IS
563 select
564 meaning
565 from
566 hr_lookups
567 where
568 lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
569 lookup_code = p_lookup_code;
570
571 /* To get person language */
572 cursor c_get_language(p_effective_date DATE) is
573 select decode(correspondence_language,NULL,'US',correspondence_language)
574 from per_all_people_f
575 where person_id = to_number(pay_ca_archive_utils.get_archive_value(
576 l_aa_id, 'CAEOY_PERSON_ID'))
577 and p_effective_date between effective_start_date and effective_end_date;
578
579 /* Bug 12943928 - Following is the cursor to fetch GRE Name of the Employer.
580 This will get displayed in output PDF if the input parameter
581 for 'Employer Address' is 'N' */
582 cursor cur_gre_name is
583 select name
584 from hr_all_organization_units hou,
585 pay_assignment_actions paa
586 where paa.assignment_action_id = l_aa_id
587 and paa.tax_unit_id = hou.organization_id;
588
589 begin
590
591 hr_utility.trace('Entering in fetch_t4a_xml');
592 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
593 l_aa_id := p_aa_id;
594 l_pa_id := p_pa_id;
595 hr_utility.trace('l_aa_id ' ||l_aa_id);
596 hr_utility.trace('l_pa_id ' ||l_pa_id);
597 hr_utility.trace('p_print ' ||p_print);
598
599 if ( g_err_emp = 'Y' ) then
600 l_aa_id := g_aa_id;
601 l_pa_id := g_pa_id;
602 l_msg_code := 'NEG';
603 end if;
604
605 /* Bug 12943928 - Address variables are populated with archived values.
606 If the input parameter for 'Employer Address' is Y, then
607 address is displayed else GRE of the Employer is displayed. */
608
609 if p_print = 'Y' then
610
611 lv_employer_address1 :=
612 pay_ca_archive_utils.get_archive_value(l_pa_id,
613 'CAEOY_EMPLOYER_ADDRESS_LINE1'); -- employer address1
614
615 lv_employer_address2 :=
616 pay_ca_archive_utils.get_archive_value(l_pa_id,
617 'CAEOY_EMPLOYER_ADDRESS_LINE2'); -- employer address2
618
619 lv_employer_address3 :=
620 pay_ca_archive_utils.get_archive_value(l_pa_id,
621 'CAEOY_EMPLOYER_ADDRESS_LINE3'); -- employer address3
622
623 lv_employer_city :=
624 pay_ca_archive_utils.get_archive_value(l_pa_id,
625 'CAEOY_EMPLOYER_CITY'); -- employer_city
626
627 lv_employer_province :=
628 pay_ca_archive_utils.get_archive_value(l_pa_id,
629 'CAEOY_EMPLOYER_PROVINCE'); -- employer_province
630
631 lv_employer_country :=
632 pay_ca_archive_utils.get_archive_value(l_pa_id,
633 'CAEOY_EMPLOYER_COUNTRY'); -- employer_country
634
635 lv_employer_postal_code :=
636 pay_ca_archive_utils.get_archive_value(l_pa_id,
637 'CAEOY_EMPLOYER_POSTAL_CODE'); -- employer_postal_code
638
639
640 if ((lv_employer_address2 is null) and
641 (lv_employer_address3 is null)) then
642
643 lv_employer_address := lv_employer_address1||'\r'||
644 lv_employer_city||' '||lv_employer_province||' '||
645 lv_employer_country||' '||lv_employer_postal_code;
646
647 elsif (lv_employer_address2 is not null) then
648 lv_employer_address := lv_employer_address1||'\r'||
649 lv_employer_address2||' '||lv_employer_address3||'\r'||
650 lv_employer_city||' '||lv_employer_province||' '||
651 lv_employer_country||' '||lv_employer_postal_code;
652 else
653 lv_employer_address := lv_employer_address1||'\r'||
654 lv_employer_address3||'\r'||
655 lv_employer_city||' '||lv_employer_province||' '||
656 lv_employer_country||' '||lv_employer_postal_code;
657 end if;
658 hr_utility.trace('lv_employer_address ' ||lv_employer_address);
659
660 else
661
662 open cur_gre_name;
663 fetch cur_gre_name into lv_gre_name;
664 close cur_gre_name;
665
666 end if;
667
668 l_year := pay_ca_archive_utils.get_archive_value(l_pa_id,
669 'CAEOY_TAXATION_YEAR');
670
671 l_person_id := pay_ca_archive_utils.get_archive_value(l_aa_id,'CAEOY_PERSON_ID');
672
673 address := pay_ca_rl1_reg.get_primary_address(l_person_id, sysdate());
674
675 lv_employee_address1 := substr(address.addr_line_1,1,60);
676 lv_employee_address2 := substr(address.addr_line_2,1,60);
677 lv_employee_address3 := substr(address.addr_line_3,1,60);
678 lv_employee_city := substr(address.city,1,30);
679 lv_employee_province := address.province;
680 lv_employee_postal_code := address.postal_code;
681 lv_employee_country := address.addr_line_5;
682
683
684 if lv_employee_province = 'NF' then
685 lv_employee_province := 'NL';
686 end if;
687
688 /* Added for bug 9156528 */
689 if lv_employee_address1 is not null then
690 l_emp_addr := lv_employee_address1||EOL;
691 end if;
692 if lv_employee_address2 is not null then
693 l_emp_addr := l_emp_addr||lv_employee_address2||EOL;
694 end if;
695 if lv_employee_address3 is not null then
696 l_emp_addr := l_emp_addr||lv_employee_address3||EOL;
697 end if;
698
699 l_emp_addr := l_emp_addr
700 || lv_employee_city || ' '
701 || lv_employee_province || ' '
702 || lv_employee_country || ' '
703 || lv_employee_postal_code;
704
708 || lv_employee_country || ' '
705 /* Commented for bug 9156528
706 l_emp_addr := lv_employee_city || ' '
707 || lv_employee_province || ' '
709 || lv_employee_postal_code;
710 */
711 l_last_nm := upper(substr(pay_ca_archive_utils.get_archive_value(l_aa_id ,
712 'CAEOY_EMPLOYEE_LAST_NAME'),1,27)); -- # 10174115 Changed from 22 to 27
713 l_first_nm := substr(pay_ca_archive_utils.get_archive_value(l_aa_id ,
714 'CAEOY_EMPLOYEE_FIRST_NAME'),1,15);--# 10174115 Changed from 12 to 15
715 l_init := substr(pay_ca_archive_utils.get_archive_value(l_aa_id ,
716 'CAEOY_EMPLOYEE_INITIAL'),1,1);
717
718 l_box16 := pay_ca_archive_utils.get_archive_value(l_aa_id
719 ,'CAEOY_T4A_BOX16_PER_GRE_YTD');
720 if (fnd_number.canonical_to_number(l_box16) = 0 ) then
721 l_box16 := null;
722 end if;
723
724 l_box18 := pay_ca_archive_utils.get_archive_value(l_aa_id
725 ,'CAEOY_T4A_BOX18_PER_GRE_YTD');
726 if (fnd_number.canonical_to_number(l_box18) = 0 ) then
727 l_box18 := null;
728 end if;
729
730 l_box20 := pay_ca_archive_utils.get_archive_value(l_aa_id
731 ,'CAEOY_T4A_BOX20_PER_GRE_YTD');
732 if (fnd_number.canonical_to_number(l_box20) = 0 ) then
733 l_box20 := null;
734 end if;
735
736 l_box22 := pay_ca_archive_utils.get_archive_value(l_aa_id
737 ,'CAEOY_FED_WITHHELD_PER_GRE_YTD');
738 if (fnd_number.canonical_to_number(l_box22) = 0 ) then
739 l_box22 := null;
740 end if;
741
742 l_box24 := pay_ca_archive_utils.get_archive_value(l_aa_id
743 ,'CAEOY_T4A_BOX24_PER_GRE_YTD');
744 if (fnd_number.canonical_to_number(l_box24) = 0 ) then
745 l_box24 := null;
746 end if;
747
748 l_box26 := pay_ca_archive_utils.get_archive_value(l_aa_id
749 ,'CAEOY_T4A_BOX26_PER_GRE_YTD');
750 if (fnd_number.canonical_to_number(l_box26) = 0 ) then
751 l_box26 := null;
752 end if;
753
754 l_box27 := pay_ca_archive_utils.get_archive_value(l_aa_id
755 ,'CAEOY_T4A_BOX27_PER_GRE_YTD');
756 if (fnd_number.canonical_to_number(l_box27) = 0 ) then
757 l_box27 := null;
758 end if;
759
760 l_box28 := pay_ca_archive_utils.get_archive_value(l_aa_id
761 ,'CAEOY_T4A_BOX28_PER_GRE_YTD');
762 if (fnd_number.canonical_to_number(l_box28) = 0 ) then
763 l_box28 := null;
764 end if;
765
766 l_box30 := pay_ca_archive_utils.get_archive_value(l_aa_id
767 ,'CAEOY_T4A_BOX30_PER_GRE_YTD');
768 if (fnd_number.canonical_to_number(l_box30) = 0 ) then
769 l_box30 := null;
770 end if;
771
772 l_box32 := pay_ca_archive_utils.get_archive_value(l_aa_id
773 ,'CAEOY_T4A_BOX32_PER_GRE_YTD');
774 if (fnd_number.canonical_to_number(l_box32) = 0 ) then
775 l_box32 := null;
776 end if;
777
778 l_box34 := pay_ca_archive_utils.get_archive_value(l_aa_id
779 ,'CAEOY_T4A_BOX34_PER_GRE_YTD');
780 if (fnd_number.canonical_to_number(l_box34) = 0 ) then
781 l_box34 := null;
782 end if;
783
784 l_box36 := pay_ca_archive_utils.get_archive_value(l_aa_id
785 ,'CAEOY_T4A_EMPLOYEE_REGISTRATION_NO');
786
787 l_box40 := pay_ca_archive_utils.get_archive_value(l_aa_id
788 ,'CAEOY_T4A_BOX40_PER_GRE_YTD');
789 if (fnd_number.canonical_to_number(l_box40) = 0 ) then
790 l_box40 := null;
791 end if;
792
793 l_box42 := pay_ca_archive_utils.get_archive_value(l_aa_id
794 ,'CAEOY_T4A_BOX42_PER_GRE_YTD');
795 if (fnd_number.canonical_to_number(l_box42) = 0 ) then
796 l_box42 := null;
797 end if;
798
799 l_box46 := pay_ca_archive_utils.get_archive_value(l_aa_id
800 ,'CAEOY_T4A_BOX46_PER_GRE_YTD');
801 if (fnd_number.canonical_to_number(l_box46) = 0 ) then
802 l_box46 := null;
803 end if;
804
805 l_box48 := pay_ca_archive_utils.get_archive_value(l_aa_id
806 ,'CAEOY_T4A_BOX48_PER_GRE_YTD');
807 if (fnd_number.canonical_to_number(l_box48) = 0 ) then
808 l_box48 := null;
809 end if;
810 --added by rgottipa for bug 6456662
811 l_box16_1:=l_box16;
812 l_box18_1:=l_box18;
813 l_box20_1:=l_box20;
814 l_box22_1:=l_box22;
815 l_box24_1:=l_box24;
816 l_box48_1:=l_box48; --end
817
818 l_box12 := pay_ca_archive_utils.get_archive_value(l_aa_id
819 ,'CAEOY_EMPLOYEE_SIN');
820 if l_box12 is null then
821 l_box12 := '000000000';
822 end if;
823 l_box12 := substr(l_box12,1,3)||' '||
824 substr(l_box12,4,3)||' '||
825 substr(l_box12,7,3);
826
827 l_box38 := pay_ca_archive_utils.get_archive_value(l_aa_id
828 ,'CAEOY_T4A_FOOTNOTE_CODE');
829
830 /* l_box13 := pay_ca_archive_utils.get_archive_value(l_aa_id
831 ,'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER'); */
832
833 l_box14 := pay_ca_archive_utils.get_archive_value(l_aa_id
834 ,'CAEOY_EMPLOYEE_NUMBER');
835
839 /*l_organization_id := pay_ca_archive_utils.get_archive_value(l_pa_id
836 l_box61 := pay_ca_archive_utils.get_archive_value(l_pa_id
837 ,'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
838
840 ,'CAEOY_T4_ORGANIZATION_ID'); */
841
842 l_payer_nm := pay_ca_archive_utils.get_archive_value(l_pa_id
843 ,'CAEOY_EMPLOYER_NAME');
844
845 --lv_negative_bal_flag := pay_ca_archive_utils.get_archive_value(l_aa_id ,
846 -- 'CAEOY_T4A_NEGATIVE_BALANCE_EXISTS');
847
848 l_date := ADD_MONTHS(TRUNC(TO_DATE(l_year,'YYYY'),'Y'), 12)-1;
849
850 OPEN c_get_language(l_date);
851 FETCH c_get_language INTO l_lang;
852 CLOSE c_get_language;
853
854
855
856 for c_t4a_footnote_rec in c_t4a_footnote(l_aa_id)
857 loop
858 l_count := c_t4a_footnote%rowcount;
859 if l_count = 1 then
860 l_footnote_code1 := c_t4a_footnote_rec.code;
861 l_footnote_value1 := c_t4a_footnote_rec.value;
862 elsif l_count = 2 then
863 l_footnote_code2 := c_t4a_footnote_rec.code;
864 l_footnote_value2 := c_t4a_footnote_rec.value;
865 elsif l_count = 3 then
866 l_footnote_code3 := c_t4a_footnote_rec.code;
867 l_footnote_value3 := c_t4a_footnote_rec.value;
868 elsif l_count = 4 then
869 l_footnote_code4 := c_t4a_footnote_rec.code;
870 l_footnote_value4 := c_t4a_footnote_rec.value;
871 end if;
872 end loop;
873
874 hr_utility.trace('g_err_emp ='||g_err_emp);
875
876 OPEN cur_get_meaning(l_msg_code);
877 FETCH cur_get_meaning
878 INTO l_err_msg;
879 CLOSE cur_get_meaning;
880
881 -- store_other_information is called for bug 6456662
882 -- for populating Other Information Element data.
883
884
885 store_other_information (l_aa_id);
886
887 --added by rgottipa
888
889 for l_j in 1..g_other_info_list.count --hard coded
890 loop
891
892 get_other_information (l_j,
893 l_code,
894 l_amount);
895 l_other_code(l_j):=l_code;
896 l_other_amount(l_j):=l_amount;
897
898 if l_amount is not null and l_amount <> '0' then
899 num_other_info:=num_other_info+1;
900 end if;
901
902 end loop;
903
904 if (g_err_emp <> 'Y' or g_err_emp is null ) then
905 l_employee_xml :='<T4A>'||EOL;
906
907 l_k := 0;
908
909 for i in 1..3
910 loop
911 if (i=1) then --Bug 8899845 - to report business number only in firs slip
912 l_box61_xml := pay_ca_t4_mag.convert_2_xml(l_box61, 'box61_'||i,'T');
913 else
914 l_box61_xml := null;
915 end if;
916
917 l_employee_xml := l_employee_xml ||
918 pay_ca_t4_mag.convert_2_xml(l_year, 'year_'||i,'T')||
919 pay_ca_t4_mag.convert_2_xml(l_last_nm, 'last_nm_'||i,'T')||
920 pay_ca_t4_mag.convert_2_xml(l_first_nm, 'first_nm_'||i,'T')||
921 pay_ca_t4_mag.convert_2_xml(l_init, 'init_'||i,'T')||
922 /* Commented for bug 9156528
923 pay_ca_t4_mag.convert_2_xml(lv_employee_address1, 'addrline1_'||i,'T')||
924 pay_ca_t4_mag.convert_2_xml(lv_employee_address2, 'addrline2_'||i,'T')||
925 pay_ca_t4_mag.convert_2_xml(lv_employee_address3, 'addrline3_'||i,'T')||*/
926 pay_ca_t4_mag.convert_2_xml(l_emp_addr, 'emp_addr_'||i,'T')||
927 pay_ca_t4_mag.convert_2_xml(l_box16, 'box16_'||i,'T')||
928 pay_ca_t4_mag.convert_2_xml(l_box18, 'box18_'||i,'T')||
929 pay_ca_t4_mag.convert_2_xml(l_box20, 'box20_'||i,'T')||
930 pay_ca_t4_mag.convert_2_xml(l_box22, 'box22_'||i,'T')||
931 pay_ca_t4_mag.convert_2_xml(l_box24, 'box24_'||i,'T')||
932 pay_ca_t4_mag.convert_2_xml(l_box26, 'box26_'||i,'T')||
933 pay_ca_t4_mag.convert_2_xml(l_box27, 'box27_'||i,'T')||
934 pay_ca_t4_mag.convert_2_xml(l_box28, 'box28_'||i,'T')||
935 pay_ca_t4_mag.convert_2_xml(l_box30, 'box30_'||i,'T')||
936 pay_ca_t4_mag.convert_2_xml(l_box32, 'box32_'||i,'T')||
937 pay_ca_t4_mag.convert_2_xml(l_box34, 'box34_'||i,'T')||
938 pay_ca_t4_mag.convert_2_xml(l_box36, 'box36_'||i,'T')||
939 pay_ca_t4_mag.convert_2_xml(l_box40, 'box40_'||i,'T')||
940 pay_ca_t4_mag.convert_2_xml(l_box42, 'box42_'||i,'T')||
941 pay_ca_t4_mag.convert_2_xml(l_box46, 'box46_'||i,'T')||
942 pay_ca_t4_mag.convert_2_xml(l_box48, 'box48_'||i,'T')||
943 pay_ca_t4_mag.convert_2_xml(l_box12, 'box12_'||i,'T')||
944 pay_ca_t4_mag.convert_2_xml(substr(l_box38,1,1)||' '||substr(l_box38,2,1),
945 'box38_'||i,'T')||
946 pay_ca_t4_mag.convert_2_xml(l_box13, 'box13_'||i,'T')||
947 pay_ca_t4_mag.convert_2_xml(l_box14, 'box14_'||i,'T')||l_box61_xml||
951 pay_ca_t4_mag.convert_2_xml(l_footnote_code2, 'fncode2_'||i,'T')||
948 pay_ca_t4_mag.convert_2_xml(l_payer_nm, 'payer_nm_'||i,'T')||
949 pay_ca_t4_mag.convert_2_xml(l_footnote_code1, 'fncode1_'||i,'T')||
950 pay_ca_t4_mag.convert_2_xml(l_footnote_value1, 'fnvalue1_'||i,'T')||
952 pay_ca_t4_mag.convert_2_xml(l_footnote_value2, 'fnvalue2_'||i,'T')||
953 pay_ca_t4_mag.convert_2_xml(l_footnote_code3, 'fncode3_'||i,'T')||
954 pay_ca_t4_mag.convert_2_xml(l_footnote_value3, 'fnvalue3_'||i,'T')||
955 pay_ca_t4_mag.convert_2_xml(l_footnote_code4, 'fncode4_'||i,'T')||
956 pay_ca_t4_mag.convert_2_xml(l_footnote_value4, 'fnvalue4_'||i,'T');
957
958 end loop;
959
960 --l_show_page3 := 'N';
961
962 --if other information is existed
963 hr_utility.trace('correct employee count '||g_other_info_list.count||' '||num_other_info);
964
965 l_type := pay_magtape_generic.get_parameter_value('TYPE'); -- Added for #5046003
966
967 if trim(called_from) = 'get_final_xml' then
968 l_type := 'T4PAPER';
969 end if;
970
971 hr_utility.trace('l_type value is '||l_type);
972
973 -- format was changed for other info data and new datatype 'X' was used for code
974 -- '034' and for code '036'
975 -- for 2010 T4A PDF template
976 l_k:=1;l_i:=0; l_m:=1;
977
978 -- Added l_other_info_count for bug 10624469
979 l_other_info_count := 0;
980
981 for l_j in 1..g_other_info_list.count
982 loop
983 if l_other_amount(l_j) <> '0' and l_other_amount(l_j) is not null then
984
985 if l_i = 0 and mod(l_m,12) = 1 then
986 hr_utility.trace('Entering another page '||'l_i '||l_i||' l_m '||l_m);
987
988 -- Added l_other_info_count for bug 10624469
989 l_other_info_count := l_other_info_count +1;
990
991 for i in 4..5
992 loop
993 -- if l_type condition added by sneelapa for bug 11775104
994 if l_type = 'T4ERPAPER' then
995 l_box61_xml := pay_ca_t4_mag.convert_2_xml(l_box61, 'box61_'||i,'T');
996 else
997 l_box61_xml := null;
998 end if;
999
1000 hr_utility.trace('l_box61_xml value is '||l_box61_xml);
1001
1002 l_employee_xml := l_employee_xml ||
1003 l_box61_xml||
1004 pay_ca_t4_mag.convert_2_xml(l_year, 'year_'||i,'T')||
1005 pay_ca_t4_mag.convert_2_xml(l_last_nm, 'last_nm_'||i,'T')||
1006 pay_ca_t4_mag.convert_2_xml(l_first_nm, 'first_nm_'||i,'T')||
1007 pay_ca_t4_mag.convert_2_xml(l_init, 'init_'||i,'T')||
1008 pay_ca_t4_mag.convert_2_xml(l_emp_addr, 'emp_addr_'||i,'T')||
1009 pay_ca_t4_mag.convert_2_xml(l_box16, 'box16_'||i,'T')||
1010 pay_ca_t4_mag.convert_2_xml(l_box18, 'box18_'||i,'T')||
1011 pay_ca_t4_mag.convert_2_xml(l_box20, 'box20_'||i,'T')||
1012 pay_ca_t4_mag.convert_2_xml(l_box22, 'box22_'||i,'T')||
1013 pay_ca_t4_mag.convert_2_xml(l_box24, 'box24_'||i,'T')||
1014 pay_ca_t4_mag.convert_2_xml(l_box48, 'box48_'||i,'T')||
1015 pay_ca_t4_mag.convert_2_xml(l_box12, 'box12_'||i,'T')||
1016 pay_ca_t4_mag.convert_2_xml(l_box13, 'box13_'||i,'T')||
1017 pay_ca_t4_mag.convert_2_xml(l_payer_nm, 'payer_nm_'||i,'T')||
1018 /* Bug 12943928 - Address and GRE of the Employer are included in output XML */
1019 pay_ca_t4_mag.convert_2_xml(lv_gre_name, 'gre_'||i,'T') ||
1020 pay_ca_t4_mag.convert_2_xml(lv_employer_address, 'payer_addr_'||i,'T');
1021
1022 end loop;
1023
1024 end if; --l_i = 0 and l_m%12 = 1
1025
1026 if l_i > 0 and mod(l_m,12) = 1 then
1027 hr_utility.trace('Entering another page '||'l_i '||l_i||' l_m '||l_m);
1028
1029 -- Added l_other_info_count for bug 10624469
1030 l_other_info_count := l_other_info_count +1;
1031
1032 for i in 4..5
1033 loop
1034
1035 -- if l_type condition added by sneelapa for bug 11775104
1036 if l_type = 'T4ERPAPER' then
1037 l_box61_xml := pay_ca_t4_mag.convert_2_xml(l_box61, 'box61_'||i,'T');
1038 else
1039 l_box61_xml := null;
1040 end if;
1041
1042 hr_utility.trace('l_box61_xml value is '||l_box61_xml);
1043
1044 l_employee_xml := l_employee_xml ||
1045 l_box61_xml||
1046 pay_ca_t4_mag.convert_2_xml(l_year, 'year_'||i,'T')||
1047 pay_ca_t4_mag.convert_2_xml(l_last_nm, 'last_nm_'||i,'T')||
1048 pay_ca_t4_mag.convert_2_xml(l_first_nm, 'first_nm_'||i,'T')||
1049 pay_ca_t4_mag.convert_2_xml(l_init, 'init_'||i,'T')||
1050 pay_ca_t4_mag.convert_2_xml(l_emp_addr, 'emp_addr_'||i,'T')||
1051 pay_ca_t4_mag.convert_2_xml(l_box12, 'box12_'||i,'T')||
1052 pay_ca_t4_mag.convert_2_xml(l_box13, 'box13_'||i,'T')||
1053 pay_ca_t4_mag.convert_2_xml(l_payer_nm, 'payer_nm_'||i,'T') ||
1054 /* Bug 12943928 - Address and GRE of the Employer are included in output XML */
1055 pay_ca_t4_mag.convert_2_xml(lv_gre_name, 'gre_'||i,'T') ||
1056 pay_ca_t4_mag.convert_2_xml(lv_employer_address, 'payer_addr_'||i,'T');
1057
1058 end loop;
1059
1060 end if; --l_i > 0 and l_m%12 = 1
1061
1062 l_employee_xml := l_employee_xml||
1063 pay_ca_t4_mag.convert_2_xml(l_other_code(l_j), 'oth_code'||l_k||'_1','T')
1064 ||pay_ca_t4_mag.convert_2_xml(l_other_code(l_j), 'oth_code'||l_k||'_2','T');
1065
1066 if l_other_code(l_j) = '034'
1067 then
1068 l_employee_xml := l_employee_xml
1069 ||pay_ca_t4_mag.convert_2_xml(to_char(l_other_amount(l_j),'9,999,999,999')
1070 ,'oth_amnt'||l_k||'_1','X')
1071 ||pay_ca_t4_mag.convert_2_xml(to_char(l_other_amount(l_j),'9,999,999,999')
1072 ,'oth_amnt'||l_k||'_2','X');
1073 elsif l_other_code(l_j) = '036' then
1074 l_employee_xml := l_employee_xml
1075 ||pay_ca_t4_mag.convert_2_xml(to_char(l_other_amount(l_j)),'oth_amnt'||l_k||'_1','X')
1076 ||pay_ca_t4_mag.convert_2_xml(to_char(l_other_amount(l_j)),'oth_amnt'||l_k||'_2','X');
1077 elsif l_other_code(l_j) = '014' then
1078 l_employee_xml := l_employee_xml
1079 ||pay_ca_t4_mag.convert_2_xml(to_char(l_other_amount(l_j)), 'oth_amnt'||l_k||'_1','X')
1080 ||pay_ca_t4_mag.convert_2_xml(to_char(l_other_amount(l_j)), 'oth_amnt'||l_k||'_2','X');
1081 else
1082 l_employee_xml := l_employee_xml
1083 ||pay_ca_t4_mag.convert_2_xml(to_char(l_other_amount(l_j),'9,999,999,999.00'),
1084 'oth_amnt'||l_k||'_1','T')
1085 ||pay_ca_t4_mag.convert_2_xml(to_char(l_other_amount(l_j),'9,999,999,999.00'),
1086 'oth_amnt'||l_k||'_2','T');
1087 end if; -- if l_other_code(l_j) = '034'
1088 l_k:=l_k+1; l_m:=l_m+1;
1089 if l_k > 12 then
1090 hr_utility.trace('l_k ='||l_k||' l_i ='||l_i);
1091 l_k := 1;
1092 l_i := l_i+1;
1093 end if;
1094 end if; -- l_other_amount(l_j) <> '0' and l_other_amount(l_j) is not null
1095 end loop;
1096
1097 -- Code modification for bug 6456662 ends here, by sneelapa
1098 -- l_show_page3 := 'Y';
1099 --l_employee_xml := l_employee_xml||pay_ca_t4_mag.convert_2_xml(l_show_page3, 'l_show_page3','T');
1100
1101 l_employee_xml := trim(l_employee_xml);
1102
1103 -- for bug 10624469
1104 hr_utility.trace('No of other info pages count '||l_other_info_count);
1105
1106 -- l_other_info_count > 1 for bug 10624469
1107 hr_utility.trace('l_other_info_count before if cond '||l_other_info_count);
1108 hr_utility.trace('mod l_other_info_count before if cond '||mod(l_other_info_count,2));
1109
1110 /* if l_other_info_count > 1 and trim(called_from) <> 'get_final_xml' then
1111 if l_type ='T4PAPER' and mod(l_other_info_count,2)=0 then
1112 l_employee_xml := l_employee_xml||'<blank_type>'||'T4ABLANKPAPER'||'</blank_type>'||EOL;
1113 elsif l_type ='T4ERPAPER' and mod(l_other_info_count,2)=1 then
1114 l_employee_xml := l_employee_xml||'<blank_type>'||'T4ABLANKPAPER'||'</blank_type>'||EOL;
1115 end if;
1116 end if; */
1117
1118 /* bug 16055709, assuming that if print_instructions is 'N' (Employeer option) means customer has
1119 pre printed instructions hence he will go for single side printing. Blank page is not needed */
1120
1121 if l_type ='T4PAPER' and mod(l_other_info_count,2)=0 and trim(called_from) <> 'get_final_xml'then
1122 l_employee_xml := l_employee_xml||'<blank_type>'||'T4ABLANKPAPER'||'</blank_type>'||EOL;
1123 end if;
1124
1125 l_employee_xml := l_employee_xml||'<type>'||l_type||'</type>'||EOL -- Added <TYPE> for #5046003
1126 ||'</T4A>'||EOL;
1127 elsif trim(called_from) = 'get_final_xml' then
1128 l_employee_xml := pay_ca_t4_mag.convert_2_xml(l_err_msg, 'Errmsg','T')||EOL;
1129 else
1130 hr_utility.trace('ERROR employee count '||g_other_info_list.count||' '||num_other_info);
1131 l_employee_xml := '<FAILED_T4A>' || EOL||
1132 pay_ca_t4_mag.convert_2_xml(l_year, 'year_f','T')||
1133 pay_ca_t4_mag.convert_2_xml(l_last_nm, 'last_nm_f','T')||
1134 pay_ca_t4_mag.convert_2_xml(l_first_nm, 'first_nm_f','T')||
1135 pay_ca_t4_mag.convert_2_xml(l_init, 'init_f','T')||
1136 pay_ca_t4_mag.convert_2_xml(lv_employee_address1, 'addrline1_f','T')||
1137 pay_ca_t4_mag.convert_2_xml(lv_employee_address2, 'addrline2_f','T')||
1138 pay_ca_t4_mag.convert_2_xml(lv_employee_address3, 'addrline3_f','T')||
1139 pay_ca_t4_mag.convert_2_xml(lv_employee_city, 'city_f','T')||
1140 pay_ca_t4_mag.convert_2_xml(lv_employee_postal_code, 'postal_code_f','T')||
1141 pay_ca_t4_mag.convert_2_xml(l_box16, 'box16_f','T')||
1142 pay_ca_t4_mag.convert_2_xml(l_box18, 'box18_f','T')||
1143 pay_ca_t4_mag.convert_2_xml(l_box20, 'box20_f','T')||
1144 pay_ca_t4_mag.convert_2_xml(l_box22, 'box22_f','T')||
1145 pay_ca_t4_mag.convert_2_xml(l_box24, 'box24_f','T')||
1146 pay_ca_t4_mag.convert_2_xml(l_box26, 'box26_f','T')||
1147 pay_ca_t4_mag.convert_2_xml(l_box27, 'box27_f','T')||
1148 pay_ca_t4_mag.convert_2_xml(l_box28, 'box28_f','T')||
1149 pay_ca_t4_mag.convert_2_xml(l_box30, 'box30_f','T')||
1150 pay_ca_t4_mag.convert_2_xml(l_box32, 'box32_f','T')||
1151 pay_ca_t4_mag.convert_2_xml(l_box34, 'box34_f','T')||
1152 pay_ca_t4_mag.convert_2_xml(l_box36, 'box36_f','T')||
1153 pay_ca_t4_mag.convert_2_xml(l_box40, 'box40_f','T')||
1154 pay_ca_t4_mag.convert_2_xml(l_box42, 'box42_f','T')||
1155 pay_ca_t4_mag.convert_2_xml(l_box48, 'box48_f','T')||
1156 pay_ca_t4_mag.convert_2_xml(l_box46, 'box46_f','T')||
1157 pay_ca_t4_mag.convert_2_xml(l_box16_1, 'box16_1_f','T')||
1158 pay_ca_t4_mag.convert_2_xml(l_box18_1, 'box18_1_f','T')||
1159 pay_ca_t4_mag.convert_2_xml(l_box20_1, 'box20_1_f','T')||
1160 pay_ca_t4_mag.convert_2_xml(l_box22_1, 'box22_1_f','T')||
1161 pay_ca_t4_mag.convert_2_xml(l_box24_1, 'box24_1_f','T')||
1162 pay_ca_t4_mag.convert_2_xml(l_box48_1, 'box48_1_f','T')||
1163
1164 pay_ca_t4_mag.convert_2_xml(substr(l_box38,1,2), 'box38_f','T')|| --Bug 8899845
1165 pay_ca_t4_mag.convert_2_xml(l_box13, 'box13_f','T')||
1166 pay_ca_t4_mag.convert_2_xml(l_box14, 'box14_f','T')||
1167 pay_ca_t4_mag.convert_2_xml(l_box12, 'box12_f','T')||
1168 pay_ca_t4_mag.convert_2_xml(l_box61, 'box61_f','T')||
1169 pay_ca_t4_mag.convert_2_xml(l_payer_nm, 'payer_nm_f','T')||
1170 pay_ca_t4_mag.convert_2_xml(l_footnote_code1, 'fncode1_f','T')||
1171 pay_ca_t4_mag.convert_2_xml(l_footnote_value1, 'fnvalue1_f','T')||
1172 pay_ca_t4_mag.convert_2_xml(l_footnote_code2, 'fncode2_f','T')||
1173 pay_ca_t4_mag.convert_2_xml(l_footnote_value2, 'fnvalue2_f','T')||
1174 pay_ca_t4_mag.convert_2_xml(l_footnote_code3, 'fncode3_f','T')||
1175 pay_ca_t4_mag.convert_2_xml(l_footnote_value3, 'fnvalue3_f','T')||
1176 pay_ca_t4_mag.convert_2_xml(l_footnote_code4, 'fncode4_f','T')||
1177 pay_ca_t4_mag.convert_2_xml(l_footnote_value4, 'fnvalue4_f','T');
1178
1179 for l_j in 1..g_other_info_list.count
1180 loop
1181 l_employee_xml := l_employee_xml||
1182 pay_ca_t4_mag.convert_2_xml(l_other_code(l_j), 'Oth_code'||l_j,'T')||
1183 pay_ca_t4_mag.convert_2_xml(l_other_amount(l_j), 'Oth_amt'||l_j,'T');
1184 end loop;
1185 l_employee_xml :=l_employee_xml||pay_ca_t4_mag.convert_2_xml(l_err_msg, 'Errmsg','T')||'</FAILED_T4A>'||EOL;
1186 end if;
1187
1188 hr_utility.trace('calling from '||called_from);
1189 if trim(called_from) = 'get_asg_xml' then
1190 hr_utility.trace('before writing in magtape');
1191 pay_core_files.write_to_magtape_lob(l_employee_xml);
1192 hr_utility.trace('after writing in magtape');
1193 end if;
1194
1195 if trim(called_from) = 'get_final_xml' then
1196 l_is_temp_final_xml := dbms_lob.istemporary(p_xml_blob);
1197 IF l_is_temp_final_xml = 1 THEN
1198 DBMS_LOB.FREETEMPORARY(p_xml_blob);
1199 END IF;
1200
1201 dbms_lob.createtemporary(p_xml_blob,false,DBMS_LOB.CALL);
1202 dbms_lob.open(p_xml_blob,dbms_lob.lob_readwrite);
1203 p_xml_blob := append_to_lob(l_employee_xml);
1204
1205 IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1206 hr_utility.trace('Closing p_xml_blob' );
1207 dbms_lob.close(p_xml_blob);
1208 hr_utility.trace('closed p_xml_blob');
1209 END IF;
1210 end if;
1211 hr_utility.trace(l_employee_xml);
1212 hr_utility.trace('Leaving fetch_t4a_xml');
1213
1214 exception
1215 when others then
1216 hr_utility.trace('sqleerm ' || SQLERRM);
1217 raise;
1218 end fetch_t4a_xml;
1219
1220
1221 procedure get_header_xml
1222 is
1223 l_header_xml_string varchar2(32000);
1224
1225 begin
1226
1227 l_header_xml_string :=
1228 '<T4APAPER>'||
1229 fnd_global.local_chr(13)||fnd_global.local_chr(10);
1230
1231 pay_core_files.write_to_magtape_lob(l_header_xml_string);
1232
1233 end get_header_xml;
1234
1235
1236 procedure get_trailer_xml
1237 is
1238 l_trailer_xml_string varchar2(32000);
1239
1240 cursor c_get_params is
1241 select paa1.assignment_action_id, -- archiver asg action
1242 ppa1.payroll_action_id -- archiver pact
1243 from pay_assignment_actions paa,
1244 pay_payroll_actions ppa,
1245 pay_assignment_actions paa1,
1246 pay_payroll_actions ppa1
1247 where ppa.payroll_action_id = paa.payroll_action_id
1248 and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
1249 and substr(paa.serial_number,29,1) = 'Y'
1250 --and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
1251 and fnd_number.canonical_to_number(substr(paa.serial_number,1,14)) = paa1.assignment_action_id
1252 and paa1.payroll_action_id = ppa1.payroll_action_id
1253 --and ppa1.report_type = 'T4A'
1254 and ( ((ppa.report_type = 'PAYCAT4ACLPDF')
1255 and ppa1.report_type in ('CAEOY_T4A_AMEND_PP','T4A'))
1256 or
1257 (ppa1.report_type = decode(ppa.report_type,'PAYCAT4AAMPDF','CAEOY_T4A_AMEND_PP','T4A')) --#5046003 included PAYCAT4AAMPDF
1258 ) --# 10359119 Added the or condition.
1259 and ppa1.action_type = 'X'
1260 and ppa1.action_status = 'C'
1261 and ppa1.effective_date = ppa.effective_date;
1262
1263 begin
1264
1265 open c_get_params;
1266 loop
1267 fetch c_get_params into g_aa_id, g_pa_id;
1268 exit when c_get_params%notfound;
1269 g_err_emp := 'Y';
1270 get_asg_xml;
1271 end loop;
1272
1273 l_trailer_xml_string :=
1274 '</T4APAPER>'||
1275 fnd_global.local_chr(13)||fnd_global.local_chr(10);
1276
1277 pay_core_files.write_to_magtape_lob(l_trailer_xml_string);
1278
1279 end get_trailer_xml;
1280
1281 end pay_ca_t4a_xml;