[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4_XML
Source
1 package body pay_ca_t4_xml as
2 /* $Header: pycat4xml.pkb 120.18.12010000.2 2008/09/17 06:09:17 sneelapa ship $ */
3
4 /*
5 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
6
7 Description : Package to build procedure used for generation of T4 pdf
8
9 Change List
10 -----------
11 Date Name Vers Bug No Description
12 ----------- ---------- ----- ------- -----------------------------------
13 04-APR-2005 ssouresr 115.0 Created
14 12-OCT-2005 ssouresr 115.1 Using XML Publisher tags
15 14-OCT-2005 ssouresr 115.3 Using live employee address plus
16 various other formatting fixes
17 17-OCT-2005 ssouresr 115.4 Added employer address line 3 and
18 also changed the tags to prevent
19 the generation of instructions when
20 the report is run for employers
21 18-OCT-2005 ssouresr 115.5 Employee's first name will contain
22 15 characters only
23 24-OCT-2005 ssouresr 115.6 Made Instructions Template optional
24 and also added tag for CRA approval
25 code
26 02-NOV-2005 ssouresr 115.7 Removed temporary CRA code message
27 04-NOV-2005 ssouresr 115.8 Added the CRA approval code RC-05-1122
28 30-NOV-2005 ssouresr 115.9 Modified package to use the core procedure
29 pay_core_files.write_to_magtape_lob
30 19-APR-2006 ssouresr 115.10 Modified package to use the function
31 get_IANA_charset to get the character set
32 25-Jul-2006 ssmukher 115.11 Added code for implementing the
33 QPIP/PPIP taxes.
34 24-OCT-2006 meshah 115.12 5527030 commented the logic that would
35 make the ppip earnings as null.
36 15-Nov-2006 ssmukher 115.13 5661166 Added code to store Null when the
37 value of lv_ppip_insurable_earnings
38 is 0.
39 11-Jan-2007 ssmukher 115.14 5753150 Modified the cra_code value to
40 RC-06-1122 for year 2006.
41 08-Oct-2007 amigarg 115.15 6434602 Added a condition for lv_sin if null
42 18-Oct-2007 sapalani 115.16 6394992 Added other info codes 81- 85
43 28-nov-2007 amigarg 115.17 6653661 Modified the cra_code value to
44 RC-07-1122 for year 2007.
45
46 30-nov-2007 sneelapa 115.18 6434613 Modified the procedure "fetch_t4_xml"
47 uncommented the code for making
48 lv_ppip_insurable_earnings NULL.
49
50 16-SEP-2008 sneelapa 115.19 7392426 Modified fetch_t4_xml procedure,
51 In the ELSE condition for "if lv_i = 1 then"
52 Commented the code which is passing
53 data for BOX55(lv_employees_ppip)
54 and BOX56(lv_ppip_insurable_earnings).
55 */
56
57 PROCEDURE store_other_information(p_aa_id in number,
58 p_prov in varchar2)
59 is
60
61 cursor c_other_info_value is
62 select substr(fdi.user_name,27,2) code,
63 fai.value value
64 from ff_archive_items fai,
65 ff_database_items fdi,
66 ff_archive_item_contexts faic,
67 ff_contexts fc
68 where fai.user_entity_id = fdi.user_entity_id
69 and fai.archive_item_id = faic.archive_item_id
70 and fc.context_id = faic.context_id
71 and fc.context_name = 'JURISDICTION_CODE'
72 and faic.context = p_prov
73 and fai.context1 = p_aa_id
74 and nvl((trim(fai.value)),'0') <> '0'
75 and fdi.user_name in (
76 'CAEOY_T4_OTHER_INFO_AMOUNT30_PER_JD_GRE_YTD',
77 'CAEOY_T4_OTHER_INFO_AMOUNT31_PER_JD_GRE_YTD',
78 'CAEOY_T4_OTHER_INFO_AMOUNT32_PER_JD_GRE_YTD',
79 'CAEOY_T4_OTHER_INFO_AMOUNT33_PER_JD_GRE_YTD',
80 'CAEOY_T4_OTHER_INFO_AMOUNT34_PER_JD_GRE_YTD',
81 'CAEOY_T4_OTHER_INFO_AMOUNT35_PER_JD_GRE_YTD',
82 'CAEOY_T4_OTHER_INFO_AMOUNT36_PER_JD_GRE_YTD',
83 'CAEOY_T4_OTHER_INFO_AMOUNT37_PER_JD_GRE_YTD',
84 'CAEOY_T4_OTHER_INFO_AMOUNT38_PER_JD_GRE_YTD',
85 'CAEOY_T4_OTHER_INFO_AMOUNT39_PER_JD_GRE_YTD',
86 'CAEOY_T4_OTHER_INFO_AMOUNT40_PER_JD_GRE_YTD',
87 'CAEOY_T4_OTHER_INFO_AMOUNT41_PER_JD_GRE_YTD',
88 'CAEOY_T4_OTHER_INFO_AMOUNT42_PER_JD_GRE_YTD',
89 'CAEOY_T4_OTHER_INFO_AMOUNT43_PER_JD_GRE_YTD',
90 'CAEOY_T4_OTHER_INFO_AMOUNT53_PER_JD_GRE_YTD',
91 'CAEOY_T4_OTHER_INFO_AMOUNT70_PER_JD_GRE_YTD',
92 'CAEOY_T4_OTHER_INFO_AMOUNT71_PER_JD_GRE_YTD',
93 'CAEOY_T4_OTHER_INFO_AMOUNT72_PER_JD_GRE_YTD',
94 'CAEOY_T4_OTHER_INFO_AMOUNT73_PER_JD_GRE_YTD',
95 'CAEOY_T4_OTHER_INFO_AMOUNT74_PER_JD_GRE_YTD',
96 'CAEOY_T4_OTHER_INFO_AMOUNT75_PER_JD_GRE_YTD',
97 'CAEOY_T4_OTHER_INFO_AMOUNT76_PER_JD_GRE_YTD',
98 'CAEOY_T4_OTHER_INFO_AMOUNT77_PER_JD_GRE_YTD',
99 'CAEOY_T4_OTHER_INFO_AMOUNT78_PER_JD_GRE_YTD',
100 'CAEOY_T4_OTHER_INFO_AMOUNT79_PER_JD_GRE_YTD',
101 'CAEOY_T4_OTHER_INFO_AMOUNT80_PER_JD_GRE_YTD',
102 'CAEOY_T4_OTHER_INFO_AMOUNT81_PER_JD_GRE_YTD',
103 'CAEOY_T4_OTHER_INFO_AMOUNT82_PER_JD_GRE_YTD',
104 'CAEOY_T4_OTHER_INFO_AMOUNT83_PER_JD_GRE_YTD',
105 'CAEOY_T4_OTHER_INFO_AMOUNT84_PER_JD_GRE_YTD',
106 'CAEOY_T4_OTHER_INFO_AMOUNT85_PER_JD_GRE_YTD')
107 order by substr(fdi.user_name,27,2);
108
109 lv_index number;
110
111 begin
112 g_other_info_list.delete;
113 lv_index := 1;
114
115 for rec in c_other_info_value loop
116
117 g_other_info_list(lv_index).code := rec.code;
118 g_other_info_list(lv_index).amount := rec.value;
119 lv_index := lv_index + 1;
120
121 end loop;
122
123 end;
124
125
126 procedure get_other_information(p_index in number,
127 p_code in out nocopy varchar2,
128 p_amount in out nocopy varchar2)
129 is
130 begin
131
132 if g_other_info_list.exists(p_index) then
133
134 p_code := g_other_info_list(p_index).code;
135 p_amount := g_other_info_list(p_index).amount;
136
137 else
138
139 p_code := null;
140 p_amount := null;
141
142 end if;
143
144 end;
145
146 procedure get_asg_xml is
147
148 l_header_xml varchar2(32000);
149 l_trailer_xml varchar2(32000);
150
151 l_aa_id number;
152 l_pa_id number;
153 l_print varchar2(240);
154 l_prov varchar2(240);
155 l_type varchar2(240);
156 l_instructions varchar2(240);
157
158 l_output_location varchar2(100);
159 EOL varchar2(10);
160
161 l_iana_charset fnd_lookup_values.tag%type;
162 l_xml_version varchar2(100);
163
164 cursor c_get_params is
165 select paa1.assignment_action_id, -- archiver asg action
166 ppa1.payroll_action_id, -- archiver pact
167 pay_ca_t4_reg.get_parameter('TYPE',ppa.legislative_parameters),
168 pay_ca_t4_reg.get_parameter('PRINT',ppa.legislative_parameters),
169 pay_ca_t4_reg.get_parameter('INSTRUCTIONS',ppa.legislative_parameters),
170 substr(paa.serial_number,1,2)
171 from pay_assignment_actions paa,
172 pay_payroll_actions ppa,
173 pay_assignment_actions paa1,
174 pay_payroll_actions ppa1
175 where ppa.payroll_action_id = paa.payroll_action_id
176 and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
177 and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
178 and to_number(substr(paa.serial_number,3,14)) = paa1.assignment_action_id
179 and paa1.payroll_action_id = ppa1.payroll_action_id
180 and ppa1.report_type = 'T4'
181 and ppa1.action_type = 'X'
182 and ppa1.action_status = 'C'
183 and ppa1.effective_date = ppa.effective_date;
184
185 begin
186 hr_utility.trace('In get_asg_xml');
187
188 EOL := fnd_global.local_chr(13)||fnd_global.local_chr(10);
189
190 open c_get_params;
191 fetch c_get_params into
192 l_aa_id,
193 l_pa_id,
194 l_type,
195 l_print,
196 l_instructions,
197 l_prov;
198 close c_get_params;
199
200 hr_utility.trace('l_aa_id ' ||l_aa_id);
201 hr_utility.trace('l_pa_id ' ||l_pa_id);
202 hr_utility.trace('l_type ' ||l_type);
203 hr_utility.trace('l_print ' ||l_print);
204 hr_utility.trace('l_prov ' ||l_prov);
205
206 l_output_location := get_outfile;
207
208 l_iana_charset := get_IANA_charset;
209 l_xml_version := '<?xml version="1.0" encoding="'||l_iana_charset||'" ?>'|| EOL;
210
211 l_header_xml := l_xml_version ||
212 '<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">'||EOL||
213 '<xapi:request>'||EOL||
214 '<xapi:delivery>'||EOL||
215 '<xapi:filesystem output="'||l_output_location||'" />'||EOL||
216 '</xapi:delivery>'||EOL||
217 '<xapi:document output-type="pdf">'||EOL||
218 '<xapi:template type="pdf" location="${templateName1}">'||EOL;
219
220 pay_core_files.write_to_magtape_lob(l_header_xml);
221
222 fetch_t4_xml(l_aa_id,
223 l_pa_id,
224 l_type,
225 l_print,
226 l_prov);
227
228 if ((l_type = 'T4ERPAPER') or
229 (l_instructions is null)) then
230
231 l_trailer_xml := '</xapi:template>'||EOL||
232 '</xapi:document>'||EOL||
233 '</xapi:request>'||EOL||
234 '</xapi:requestset>'||EOL;
235 else
236
237 l_trailer_xml := '</xapi:template>'||EOL||
238 '<xapi:template type="pdf" location="${templateName2}">'||EOL||
239 '<xapi:data />'|| EOL||
240 '</xapi:template>'||EOL||
241 '</xapi:document>'||EOL||
242 '</xapi:request>'||EOL||
243 '</xapi:requestset>'||EOL;
244 end if;
245
246 pay_core_files.write_to_magtape_lob(l_trailer_xml);
247
248 exception
249 when others then
250 hr_utility.trace('sqleerm ' || SQLERRM);
251 raise;
252 end;
253
254
255 procedure fetch_t4_xml(p_aa_id number,
256 p_pa_id number,
257 p_type varchar2,
258 p_print varchar2,
259 p_prov varchar2) is
260
261 lv_max_cpp_earning number;
262 lv_max_ei_earning number;
263 /* Added by ssmukher for PPIP tax implementation */
264 lv_max_ppip_earning number;
265 lv_t4_slip_count number;
266 lv_other_info_count number;
267
268 lv_sin varchar2(20);
269
270 lv_employee_last_name varchar2(200);
271 lv_employee_name varchar2(200);
272 lv_employee_initial varchar2(200);
273 lv_employee_address1 varchar2(200);
274 lv_employee_address2 varchar2(200);
275 lv_employee_address3 varchar2(200);
276 lv_employee_city varchar2(200);
277 lv_employee_province varchar2(200);
278 lv_employee_country varchar2(200);
279 lv_employee_postal_code varchar2(10);
280 lv_employee_address varchar2(10000);
281 address pay_ca_rl1_reg.primaryaddress;
282 lv_person_id number;
283
284 lv_cpp_qpp_exempt varchar2(10);
285 lv_ei_exempt varchar2(10);
286 lv_ppip_exempt varchar2(10);
287 lv_employment_code varchar2(200);
288 lv_rpp_dpsp_reg_no varchar2(200);
289 lv_employment_income varchar2(200);
290 lv_income_tax_deducted varchar2(200);
291 lv_employees_cpp varchar2(200);
292 lv_employees_qpp varchar2(200);
293 lv_employees_ei varchar2(200);
294 lv_employees_ppip varchar2(200);
295 lv_rpp_contribution varchar2(200);
296 lv_pension_adjustment varchar2(200);
297 lv_ei_insurable_earnings varchar2(200);
298 lv_ppip_insurable_earnings varchar2(200);
299 lv_cpp_qpp_earnings varchar2(200);
300 lv_union_dues varchar2(200);
301 lv_charitable_donations varchar2(200);
302
303 lv_year varchar2(4);
304 lv_employer_name varchar2(200);
305 lv_employer_business_no varchar2(200);
306 lv_employer_address1 varchar2(200);
307 lv_employer_address2 varchar2(200);
308 lv_employer_address3 varchar2(200);
309 lv_employer_city varchar2(200);
310 lv_employer_province varchar2(200);
311 lv_employer_country varchar2(200);
312 lv_employer_postal_code varchar2(10);
313 lv_employer_address varchar2(10000);
314 lv_gre_name varchar2(100);
315
316 lv_other_code1 varchar2(3);
317 lv_other_amount1 varchar2(50);
318 lv_other_code2 varchar2(3);
319 lv_other_amount2 varchar2(50);
320 lv_other_code3 varchar2(3);
321 lv_other_amount3 varchar2(50);
322 lv_other_code4 varchar2(3);
323 lv_other_amount4 varchar2(50);
324 lv_other_code5 varchar2(3);
325 lv_other_amount5 varchar2(50);
326 lv_other_code6 varchar2(3);
327 lv_other_amount6 varchar2(50);
328 lv_code varchar2(3);
329 lv_amount varchar2(50);
330
331 lv_i number;
332 lv_j number;
333 lv_k number;
334
335 l_employee_xml varchar2(32767);
336
337 cursor cur_max_cpp_earning is
338 select information_value
339 from pay_ca_legislation_info
340 where information_type = 'MAX_CPP_EARNINGS'
341 and to_date(lv_year ||'/01/01','YYYY/MM/DD')
342 between start_date and end_date;
343
344 cursor cur_max_ei_earning is
345 select information_value
346 from pay_ca_legislation_info
347 where information_type = 'MAX_EI_EARNINGS'
348 and to_date(lv_year ||'/01/01','YYYY/MM/DD')
349 between start_date and end_date;
350
351 cursor cur_max_ppip_earning is
352 select information_value
353 from pay_ca_legislation_info
354 where information_type = 'MAX_PPIP_EARNINGS'
355 and to_date(lv_year ||'/01/01','YYYY/MM/DD')
356 between start_date and end_date;
357
358 cursor cur_gre_name is
359 select name
360 from hr_all_organization_units hou,
361 pay_assignment_actions paa
362 where paa.assignment_action_id = p_aa_id
363 and paa.tax_unit_id = hou.organization_id;
364
365 begin
366 lv_year :=
367 pay_ca_archive_utils.get_archive_value(p_pa_id,
368 'CAEOY_TAXATION_YEAR'); -- year
369
370 lv_employer_name :=
371 pay_ca_archive_utils.get_archive_value(p_pa_id,
372 'CAEOY_EMPLOYER_NAME'); -- employer name
373
374 if p_print = 'Y' then
375
376 lv_employer_address1 :=
377 pay_ca_archive_utils.get_archive_value(p_pa_id,
378 'CAEOY_EMPLOYER_ADDRESS_LINE1'); -- employer address1
379
380 lv_employer_address2 :=
381 pay_ca_archive_utils.get_archive_value(p_pa_id,
382 'CAEOY_EMPLOYER_ADDRESS_LINE2'); -- employer address2
383
384 lv_employer_address3 :=
385 pay_ca_archive_utils.get_archive_value(p_pa_id,
386 'CAEOY_EMPLOYER_ADDRESS_LINE3'); -- employer address3
387
388 lv_employer_city :=
389 pay_ca_archive_utils.get_archive_value(p_pa_id,
390 'CAEOY_EMPLOYER_CITY'); -- employer_city
391
392 lv_employer_province :=
393 pay_ca_archive_utils.get_archive_value(p_pa_id,
394 'CAEOY_EMPLOYER_PROVINCE'); -- employer_province
395
396 lv_employer_country :=
397 pay_ca_archive_utils.get_archive_value(p_pa_id,
398 'CAEOY_EMPLOYER_COUNTRY'); -- employer_country
399
400 lv_employer_postal_code :=
401 pay_ca_archive_utils.get_archive_value(p_pa_id,
402 'CAEOY_EMPLOYER_POSTAL_CODE'); -- employer_postal_code
403
404
405 if ((lv_employer_address2 is null) and
406 (lv_employer_address3 is null)) then
407
408 lv_employer_address := lv_employer_address1||'\r'||
409 lv_employer_city||' '||lv_employer_province||' '||
410 lv_employer_country||' '||lv_employer_postal_code;
411
412 else
413 lv_employer_address := lv_employer_address1||'\r'||
414 lv_employer_address2||' '||lv_employer_address3||'\r'||
415 lv_employer_city||' '||lv_employer_province||' '||
416 lv_employer_country||' '||lv_employer_postal_code;
417 end if;
418
419 else
420
421 open cur_gre_name;
422 fetch cur_gre_name into lv_gre_name;
423 close cur_gre_name;
424
425 end if;
426
427
428 if p_type = 'T4ERPAPER' then
429
430 lv_employer_business_no :=
431 pay_ca_archive_utils.get_archive_value(p_pa_id,
432 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER'); -- business number
433 end if;
434
435 lv_sin :=
436 pay_ca_archive_utils.get_archive_value(p_aa_id,
437 'CAEOY_EMPLOYEE_SIN'); -- sin
438
439 -- changes started for bug 6434602
440 if lv_sin is null then
441 lv_sin := '000000000';
442 end if;
443 -- changes ended for bug 6434602
444 lv_sin := ltrim(rtrim(replace(lv_sin, ' ')));
445 lv_sin := substr(lv_sin,1,3)||' '||substr(lv_sin,4,3)||' '||substr(lv_sin,7,3);
446
447 lv_employee_name :=
448 substr(pay_ca_archive_utils.get_archive_value(p_aa_id,
449 'CAEOY_EMPLOYEE_FIRST_NAME'),1,15); -- employee_name
450
451 lv_employee_initial :=
452 pay_ca_archive_utils.get_archive_value(p_aa_id,
453 'CAEOY_EMPLOYEE_INITIAL'); -- employee_initial
454
455 if lv_employee_initial is not null then
456 lv_employee_initial := upper(substr(lv_employee_initial,1,1));
457 end if;
458
459 lv_employee_last_name :=
460 upper(pay_ca_archive_utils.get_archive_value(p_aa_id,
461 'CAEOY_EMPLOYEE_LAST_NAME')); -- employee_last_name
462
463
464 lv_person_id := to_number(pay_ca_archive_utils.get_archive_value(p_aa_id,
465 'CAEOY_PERSON_ID'));
466
467 address := pay_ca_rl1_reg.get_primary_address(lv_person_id, sysdate());
468
469 lv_employee_address1 := substr(address.addr_line_1,1,44);
470 lv_employee_address2 := substr(address.addr_line_2,1,44);
471 lv_employee_address3 := substr(address.addr_line_3,1,44);
472 lv_employee_city := address.city;
473 lv_employee_province := address.province;
474 lv_employee_postal_code := address.postal_code;
475 lv_employee_country := address.addr_line_5;
476
477 if ((lv_employee_address2 is null) and
478 (lv_employee_address3 is null)) then
479
480 lv_employee_address := lv_employee_address1||'\r'||
481 lv_employee_city||' '||lv_employee_province||' '||
482 lv_employee_country||' '||lv_employee_postal_code;
483 else
484
485 lv_employee_address := lv_employee_address1||'\r'||
486 lv_employee_address2||' '||lv_employee_address3||'\r'||
487 lv_employee_city||' '||lv_employee_province||' '||
488 lv_employee_country||' '||lv_employee_postal_code;
489 end if;
490
491
492 lv_cpp_qpp_exempt :=
493 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
494 'JURISDICTION_CODE',
495 'CAEOY_CPP_QPP_EXEMPT'); -- cpp_qpp_exempt
496
497 lv_ei_exempt :=
498 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
499 'JURISDICTION_CODE',
500 'CAEOY_EI_EXEMPT'); -- ei_exempt
501
502
503 lv_employment_code :=
504 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
505 'JURISDICTION_CODE',
506 'CAEOY_EMPLOYMENT_CODE'); -- employment_code
507
508 lv_rpp_dpsp_reg_no :=
509 pay_ca_archive_utils.get_archive_value(p_aa_id,
510 'CAEOY_T4_EMPLOYEE_REGISTRATION_NO'); -- rpp_dpsp_reg_no
511
512 lv_employment_income :=
513 pay_ca_archive_utils.get_archive_value(p_aa_id,p_prov,
514 'JURISDICTION_CODE',
515 'CAEOY_GROSS_EARNINGS_PER_JD_GRE_YTD'); -- employment_income
516
517 if to_number(lv_employment_income) = 0 then
518 lv_employment_income := null;
519 end if;
520
521 lv_income_tax_deducted :=
522 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
523 'JURISDICTION_CODE',
524 'CAEOY_FED_WITHHELD_PER_JD_GRE_YTD'); -- income_tax_deducted
525
526 if to_number(lv_income_tax_deducted) = 0 then
527 lv_income_tax_deducted := null;
528 end if;
529
530 lv_employees_cpp :=
531 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
532 'JURISDICTION_CODE',
533 'CAEOY_CPP_EE_WITHHELD_PER_JD_GRE_YTD'); -- employees_cpp
534
535 if to_number(lv_employees_cpp) = 0 then
536 lv_employees_cpp := null;
537 end if;
538
539 lv_employees_qpp :=
540 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
541 'JURISDICTION_CODE',
542 'CAEOY_QPP_EE_WITHHELD_PER_JD_GRE_YTD'); -- employees_qpp
543
544 if to_number(lv_employees_qpp) = 0 then
545 lv_employees_qpp := null;
546 end if;
547
548 lv_employees_ei :=
549 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
550 'JURISDICTION_CODE',
551 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD'); -- employees_ei
552
553 if to_number(lv_employees_ei) = 0 then
554 lv_employees_ei := null;
555 end if;
556
557 /* Added by ssmukher for including PPIP taxes */
558 if p_prov = 'QC' then
559
560 lv_ppip_exempt :=
561 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
562 'JURISDICTION_CODE',
563 'CAEOY_PPIP_EXEMPT'); -- ppip_exempt
564
565 lv_employees_ppip :=
566 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
567 'JURISDICTION_CODE',
568 'CAEOY_PPIP_EE_WITHHELD_PER_JD_GRE_YTD'); -- employees_ppip
569
570 if to_number(lv_employees_ppip) = 0 then
571 lv_employees_ppip := null;
572 end if;
573
574 open cur_max_ppip_earning;
575 fetch cur_max_ppip_earning
576 into lv_max_ppip_earning;
577 close cur_max_ppip_earning;
578
579 lv_ppip_insurable_earnings :=
580 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
581 'JURISDICTION_CODE',
582 'CAEOY_PPIP_EE_TAXABLE_PER_JD_GRE_YTD'); -- ppip_insurable_earnings
583
584 -- commented by sneelapa for bug 6434613
585 /* if to_number(lv_ppip_insurable_earnings) = 0 then
586 lv_ppip_insurable_earnings := null;
587 end if;
588 */
589
590 /* commenting the below logic as per bug 5527030
591
592 if to_number(lv_ppip_insurable_earnings) = 0 then
593 lv_ppip_insurable_earnings := null;
594
595 elsif to_number(lv_ppip_insurable_earnings) >= lv_max_ppip_earning then
596 lv_ppip_insurable_earnings := null;
597
598 elsif to_number(lv_employment_income) = to_number(lv_ppip_insurable_earnings) then
599 lv_ppip_insurable_earnings := null;
600
601 end if;
602
603 End changes for bug 5527030 */
604
605 -- calling the commented code for bug 5527030 above,
606 -- for resolving issue for bug 6434613
607 -- changes for bug 6434613 starts here.
608
609 if to_number(lv_ppip_insurable_earnings) = 0 then
610 lv_ppip_insurable_earnings := null;
611
612 elsif to_number(lv_ppip_insurable_earnings) >= lv_max_ppip_earning then
613 lv_ppip_insurable_earnings := null;
614
615 elsif to_number(lv_employment_income) = to_number(lv_ppip_insurable_earnings) then
616 lv_ppip_insurable_earnings := null;
617
618 end if;
619 -- changes for bug 6434613 ends here.
620
621 else
622 lv_ppip_exempt := NULL;
623 lv_employees_ppip := NULL;
624 lv_ppip_insurable_earnings := null;
625 end if;
626
627 lv_rpp_contribution :=
628 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
629 'JURISDICTION_CODE',
630 'CAEOY_T4_BOX20_PER_JD_GRE_YTD'); -- rpp_contribution
631
632 if to_number(lv_rpp_contribution) = 0 then
633 lv_rpp_contribution := null;
634 end if;
635
636 lv_pension_adjustment :=
637 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
638 'JURISDICTION_CODE',
639 'CAEOY_T4_BOX52_PER_JD_GRE_YTD'); -- pension_adjustment
640
641 if to_number(lv_pension_adjustment) = 0 then
642 lv_pension_adjustment := null;
643 end if;
644
645 lv_ei_insurable_earnings :=
646 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
647 'JURISDICTION_CODE',
648 'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD'); -- ei_insurable_earnings
649
650 open cur_max_ei_earning;
651 fetch cur_max_ei_earning
652 into lv_max_ei_earning;
653 close cur_max_ei_earning;
654
655 if to_number(lv_ei_insurable_earnings) = 0 then
656 lv_ei_insurable_earnings := null;
657
658 elsif to_number(lv_ei_insurable_earnings) >= lv_max_ei_earning then
659 lv_ei_insurable_earnings := null;
660
661 elsif to_number(lv_employment_income) = to_number(lv_ei_insurable_earnings) then
662 lv_ei_insurable_earnings := null;
663
664 end if;
665
666
667 if p_prov = 'QC' then
668
669 lv_cpp_qpp_earnings :=
670 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
671 'JURISDICTION_CODE',
672 'CAEOY_QPP_EE_TAXABLE_PER_JD_GRE_YTD');
673 else
674 lv_cpp_qpp_earnings :=
675 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
676 'JURISDICTION_CODE',
677 'CAEOY_CPP_EE_TAXABLE_PER_JD_GRE_YTD');
678 end if;
679
680 open cur_max_cpp_earning;
681 fetch cur_max_cpp_earning
682 into lv_max_cpp_earning;
683 close cur_max_cpp_earning;
684
685 if to_number(lv_cpp_qpp_earnings) = 0 then
686 lv_cpp_qpp_earnings := null;
687
688 elsif to_number(lv_cpp_qpp_earnings) >= lv_max_cpp_earning then
689 lv_cpp_qpp_earnings := null;
690
691 elsif to_number(lv_employment_income) = to_number(lv_cpp_qpp_earnings) then
692 lv_cpp_qpp_earnings := null;
693
694 end if;
695
696 lv_union_dues :=
697 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
698 'JURISDICTION_CODE',
699 'CAEOY_T4_BOX44_PER_JD_GRE_YTD'); -- union_dues
700
701 if to_number(lv_union_dues) = 0 then
702 lv_union_dues := null;
703 end if;
704
705 lv_charitable_donations :=
706 pay_ca_archive_utils.get_archive_value(p_aa_id, p_prov,
707 'JURISDICTION_CODE',
708 'CAEOY_T4_BOX46_PER_JD_GRE_YTD'); -- charitable_donations
709
710 if to_number(lv_charitable_donations) = 0 then
711 lv_charitable_donations := null;
712 end if;
713
714 store_other_information (p_aa_id, p_prov);
715
716 lv_k := 0;
717 lv_t4_slip_count := ceil(g_other_info_list.count/6);
718
719 if lv_t4_slip_count = 0 then
720 lv_t4_slip_count := 1;
721 end if;
722
723 for lv_i in 1..lv_t4_slip_count
724 loop
725
726 lv_other_info_count := lv_i + lv_k;
727
728 for lv_j in lv_other_info_count..(lv_other_info_count + 5)
729 loop
730
731 get_other_information (lv_j,
732 lv_code,
733 lv_amount);
734
735 if lv_j = lv_other_info_count then
736
737 lv_other_code1 := lv_code;
738 lv_other_amount1 := lv_amount;
739
740 elsif lv_j = (lv_other_info_count + 1) then
741
742 lv_other_code2 := lv_code;
743 lv_other_amount2 := lv_amount;
744
745 elsif lv_j = (lv_other_info_count + 2) then
746
747 lv_other_code3 := lv_code;
748 lv_other_amount3 := lv_amount;
749
750 elsif lv_j = (lv_other_info_count + 3) then
751
752 lv_other_code4 := lv_code;
753 lv_other_amount4 := lv_amount;
754
755 elsif lv_j = (lv_other_info_count + 4) then
756
757 lv_other_code5 := lv_code;
758 lv_other_amount5 := lv_amount;
759
760 elsif lv_j = (lv_other_info_count + 5) then
761
762 lv_other_code6 := lv_code;
763 lv_other_amount6 := lv_amount;
764
765 end if;
766
767 end loop;
768
769 lv_k := lv_k + 5;
770
771 if lv_i = 1 then
772
773 l_employee_xml := create_xml_string(lv_employer_name,
774 lv_employer_business_no,
775 lv_employer_address,
776 lv_employee_name,
777 lv_employee_last_name,
778 lv_employee_initial,
779 lv_employee_address,
780 lv_sin,
781 lv_cpp_qpp_exempt,
782 lv_ei_exempt,
783 p_prov,
784 lv_employment_code,
785 lv_rpp_dpsp_reg_no,
786 lv_employment_income,
787 lv_employees_cpp,
788 lv_employees_qpp,
789 lv_employees_ei,
790 lv_rpp_contribution,
791 lv_pension_adjustment,
792 lv_income_tax_deducted,
793 lv_ei_insurable_earnings,
794 lv_cpp_qpp_earnings,
795 lv_union_dues,
796 lv_charitable_donations,
797 lv_other_code1,
798 lv_other_amount1,
799 lv_other_code2,
800 lv_other_amount2,
801 lv_other_code3,
802 lv_other_amount3,
803 lv_other_code4,
804 lv_other_amount4,
805 lv_other_code5,
806 lv_other_amount5,
807 lv_other_code6,
808 lv_other_amount6,
809 lv_year,
810 lv_ppip_exempt,
811 lv_employees_ppip,
812 lv_ppip_insurable_earnings,
813 lv_gre_name);
814
815
816 else
817
818 l_employee_xml := l_employee_xml ||
819 create_xml_string(lv_employer_name,
820 lv_employer_business_no,
821 lv_employer_address,
822 lv_employee_name,
823 lv_employee_last_name,
824 lv_employee_initial,
825 lv_employee_address,
826 lv_sin,
827 '',
828 '',
829 p_prov,
830 lv_employment_code,
831 '',
832 '',
833 '',
834 '',
835 '',
836 '',
837 '',
838 '',
839 '',
840 '',
841 '',
842 '',
843 lv_other_code1,
844 lv_other_amount1,
845 lv_other_code2,
846 lv_other_amount2,
847 lv_other_code3,
848 lv_other_amount3,
849 lv_other_code4,
850 lv_other_amount4,
851 lv_other_code5,
852 lv_other_amount5,
853 lv_other_code6,
854 lv_other_amount6,
855 lv_year,
856 lv_ppip_exempt,
857 --bug 7392426 fix start
858 --lv_employees_ppip,
859 '',
860 --lv_ppip_insurable_earnings,
861 '',
862 --bug 7392426 fix end
863 lv_gre_name);
864
865 end if;
866
867 lv_other_code1 := null;
868 lv_other_amount1 := null;
869 lv_other_code2 := null;
870 lv_other_amount2 := null;
871 lv_other_code3 := null;
872 lv_other_amount3 := null;
873 lv_other_code4 := null;
874 lv_other_amount4 := null;
875 lv_other_code5 := null;
876 lv_other_amount5 := null;
877 lv_other_code6 := null;
878 lv_other_amount6 := null;
879
880 end loop;
881
882 pay_core_files.write_to_magtape_lob(l_employee_xml);
883
884 exception
885 when others then
886 hr_utility.trace('sqleerm '|| sqlerrm);
887 hr_utility.raise_error;
888 end;
889
890
891 function create_xml_string (p_employer_name varchar2,
892 p_employer_bn varchar2,
893 p_employer_addr varchar2,
894 p_employee_name varchar2,
895 p_employee_last_name varchar2,
896 p_employee_init varchar2,
897 p_employee_addr varchar2,
898 p_sin varchar2,
899 p_cpp_qpp_exempt varchar2,
900 p_ei_exempt varchar2,
901 p_employment_prov varchar2,
902 p_employment_code varchar2,
903 p_registration_number varchar2,
904 p_employment_income varchar2,
905 p_cpp_contributions varchar2,
906 p_qpp_contributions varchar2,
907 p_ei_contributions varchar2,
908 p_rpp_contributions varchar2,
909 p_pension_adjustment varchar2,
910 p_tax_deducted varchar2,
911 p_ei_earnings varchar2,
912 p_cpp_qpp_earnings varchar2,
913 p_union_dues varchar2,
914 p_charitable_donations varchar2,
915 p_other_code1 varchar2,
916 p_other_amount1 varchar2,
917 p_other_code2 varchar2,
918 p_other_amount2 varchar2,
919 p_other_code3 varchar2,
920 p_other_amount3 varchar2,
921 p_other_code4 varchar2,
922 p_other_amount4 varchar2,
923 p_other_code5 varchar2,
924 p_other_amount5 varchar2,
925 p_other_code6 varchar2,
926 p_other_amount6 varchar2,
927 p_year varchar2,
928 p_ppip_exempt varchar2,
929 p_ppip_contributions varchar2,
930 p_ppip_earnings varchar2,
931 p_gre_name varchar2)
932 return varchar2 is
933
934 l_single_xml varchar2(32767);
935
936 begin
937
938 l_single_xml :=
939 '<xapi:data>'||fnd_global.local_chr(13)||fnd_global.local_chr(10)||
940 '<T4>'||fnd_global.local_chr(13)||fnd_global.local_chr(10)||
941 pay_ca_t4_mag.convert_2_xml(p_employee_name, 'snm','T','','Y')||
942 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
943 pay_ca_t4_mag.convert_2_xml(p_employee_last_name, 'gvn_nm','T','','Y')||
944 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
945 pay_ca_t4_mag.convert_2_xml(p_employee_init, 'init','T','','Y')||
946 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
947 pay_ca_t4_mag.convert_2_xml(p_employee_addr, 'empe_addr','T','','Y')||
948 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
949 pay_ca_t4_mag.convert_2_xml(p_employer_name, 'empr_nm','T','','Y')||
950 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
951 pay_ca_t4_mag.convert_2_xml(p_employer_addr, 'empr_addr','T','','Y')||
952 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
953 pay_ca_t4_mag.convert_2_xml(p_gre_name, 'gre','T','','Y')||
954 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
955 pay_ca_t4_mag.convert_2_xml(p_employer_bn, 'bn','T','','Y')||
956 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
957 pay_ca_t4_mag.convert_2_xml(p_sin, 'sin','T','','Y')||
958 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
959 pay_ca_t4_mag.convert_2_xml(p_cpp_qpp_exempt, 'cpp_qpp_xmpt_cd','T','','Y')||
960 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
961 pay_ca_t4_mag.convert_2_xml(p_ppip_exempt, 'ppip_xmpt_cd','T','','Y')||
962 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
963 pay_ca_t4_mag.convert_2_xml(p_ei_exempt, 'ei_xmpt_cd','T','','Y')||
964 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
965 pay_ca_t4_mag.convert_2_xml(p_year, 'tx_yr','T','','Y')||
966 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
967 pay_ca_t4_mag.convert_2_xml(p_employment_prov, 'empt_prov_cd','T','','Y')||
968 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
969 pay_ca_t4_mag.convert_2_xml(p_employment_code, 'empt_cd','T','','Y')||
970 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
971 pay_ca_t4_mag.convert_2_xml(p_employment_income, 'empt_incamt','T','','Y')||
972 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
973 pay_ca_t4_mag.convert_2_xml(p_registration_number, 'rpp_dpsp_rgst_nbr','T','','Y')||
974 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
975 pay_ca_t4_mag.convert_2_xml(p_cpp_contributions, 'cpp_cntrb_amt','T','','Y')||
976 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
977 pay_ca_t4_mag.convert_2_xml(p_qpp_contributions, 'qpp_cntrb_amt','T','','Y')||
978 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
979 pay_ca_t4_mag.convert_2_xml(p_ei_contributions, 'empe_eip_amt','T','','Y')||
980 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
981 pay_ca_t4_mag.convert_2_xml(p_rpp_contributions, 'rpp_cntrb_amt','T','','Y')||
982 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
983 pay_ca_t4_mag.convert_2_xml(p_pension_adjustment, 'padj_amt','T','','Y')||
984 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
985 pay_ca_t4_mag.convert_2_xml(p_tax_deducted, 'itx_ddct_amt','T','','Y')||
986 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
987 pay_ca_t4_mag.convert_2_xml(p_ei_earnings, 'ei_insu_ern_amt','T','','Y')||
988 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
989 pay_ca_t4_mag.convert_2_xml(p_cpp_qpp_earnings, 'cpp_qpp_ern_amt','T','','Y')||
990 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
991 pay_ca_t4_mag.convert_2_xml(p_union_dues, 'unn_dues_amt','T','','Y')||
992 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
993 pay_ca_t4_mag.convert_2_xml(p_charitable_donations, 'chrty_dons_amt','T','','Y')||
994 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
995 pay_ca_t4_mag.convert_2_xml(p_ppip_contributions, 'ppip_cntrb_amt','T','','Y')||
996 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
997 pay_ca_t4_mag.convert_2_xml(p_ppip_earnings, 'ppip_insu_ern_amt','T','','Y')||
998 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
999 pay_ca_t4_mag.convert_2_xml(p_other_code1, 'oth_code1','T','','Y')||
1000 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1001 pay_ca_t4_mag.convert_2_xml(p_other_amount1, 'oth_amnt1','T','','Y')||
1002 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1003 pay_ca_t4_mag.convert_2_xml(p_other_code2, 'oth_code2','T','','Y')||
1004 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1005 pay_ca_t4_mag.convert_2_xml(p_other_amount2, 'oth_amnt2','T','','Y')||
1006 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1007 pay_ca_t4_mag.convert_2_xml(p_other_code3, 'oth_code3','T','','Y')||
1008 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1009 pay_ca_t4_mag.convert_2_xml(p_other_amount3, 'oth_amnt3','T','','Y')||
1010 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1011 pay_ca_t4_mag.convert_2_xml(p_other_code4, 'oth_code4','T','','Y')||
1012 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1013 pay_ca_t4_mag.convert_2_xml(p_other_amount4, 'oth_amnt4','T','','Y')||
1014 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1015 pay_ca_t4_mag.convert_2_xml(p_other_code5, 'oth_code5','T','','Y')||
1016 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1017 pay_ca_t4_mag.convert_2_xml(p_other_amount5, 'oth_amnt5','T','','Y')||
1018 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1019 pay_ca_t4_mag.convert_2_xml(p_other_code6, 'oth_code6','T','','Y')||
1020 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1021 pay_ca_t4_mag.convert_2_xml(p_other_amount6, 'oth_amnt6','T','','Y')||
1022 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1023 pay_ca_t4_mag.convert_2_xml('RC-07-1122', 'cra_code','T','','Y')||
1024 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1025 '</T4>'||fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1026 '</xapi:data>'||fnd_global.local_chr(13)||fnd_global.local_chr(10);
1027
1028 hr_utility.trace('XML string :' ||l_single_xml);
1029
1030 return l_single_xml;
1031
1032 exception
1033 when others then
1034 hr_utility.trace('sqleerm ' || sqlerrm);
1035 hr_utility.raise_error;
1036
1037 end create_xml_string;
1038
1039
1040 procedure get_header_xml
1041 is
1042 l_header_xml_string varchar2(32000);
1043
1044 begin
1045
1046 l_header_xml_string :=
1047 '<EMPLOYEES>'||
1048 fnd_global.local_chr(13)||fnd_global.local_chr(10);
1049
1050 /* '<?xml version="1.0" encoding="UTF-8" ?>'||
1051 fnd_global.local_chr(13)||fnd_global.local_chr(10)||
1052 '<EMPLOYEES>'||
1053 fnd_global.local_chr(13)||fnd_global.local_chr(10);
1054 */
1055 pay_core_files.write_to_magtape_lob(l_header_xml_string);
1056
1057 end get_header_xml;
1058
1059
1060 procedure get_trailer_xml
1061 is
1062 l_trailer_xml_string varchar2(32000);
1063
1064 begin
1065
1066 l_trailer_xml_string :=
1067 '</EMPLOYEES>'||
1068 fnd_global.local_chr(13)||fnd_global.local_chr(10);
1069
1070 pay_core_files.write_to_magtape_lob(l_trailer_xml_string);
1071
1072 end get_trailer_xml;
1073
1074 function get_outfile return VARCHAR2 is
1075 TEMP_UTL varchar2(512);
1076 l_log varchar2(100);
1077 l_out varchar2(100);
1078 begin
1079 hr_utility.trace('In get_out_file,g_temp_dir ' ||g_temp_dir );
1080
1081 if g_temp_dir is null then
1082 -- use first entry of utl_file_dir as the g_temp_dir
1083 select translate(ltrim(value),',',' ')
1084 into TEMP_UTL
1085 from v$parameter
1086 where name = 'utl_file_dir';
1087
1088 if (instr(TEMP_UTL,' ') > 0 and TEMP_UTL is not null) then
1089 select substrb(TEMP_UTL, 1, instr(TEMP_UTL,' ') - 1)
1090 into g_temp_dir
1091 from dual ;
1092 elsif (TEMP_UTL is not null) then
1093 g_temp_dir := TEMP_UTL;
1094 end if;
1095
1096 if (TEMP_UTL is null or g_temp_dir is null ) then
1097 raise no_data_found;
1098 end if;
1099 end if;
1100 hr_utility.trace('In get_out_file,g_temp_dir ' ||g_temp_dir );
1101
1102 FND_FILE.get_names(l_log,l_out);
1103
1104 l_out := g_temp_dir ||'/'||l_out;
1105 hr_utility.trace('In get_out_file,l_out ' ||l_out );
1106
1107 return l_out;
1108
1109 exception
1110 when no_data_found then
1111 return null;
1112 when others then
1113 return null;
1114 end get_outfile;
1115
1116 function get_IANA_charset return VARCHAR2 is
1117 cursor csr_get_iana_charset is
1118 select tag
1119 from fnd_lookup_values
1120 where lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
1121 and lookup_code = SUBSTR(USERENV('LANGUAGE'),
1122 INSTR(USERENV('LANGUAGE'), '.') + 1)
1123 and language = 'US';
1124
1125 lv_iana_charset fnd_lookup_values.tag%type;
1126
1127 begin
1128 open csr_get_iana_charset;
1129 fetch csr_get_iana_charset into lv_iana_charset;
1130 close csr_get_iana_charset;
1131
1132 hr_utility.trace('IANA Charset = '||lv_iana_charset);
1133 return (lv_iana_charset);
1134
1135 end get_IANA_charset;
1136
1137
1138 end pay_ca_t4_xml;