[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_RL2_MAG
Source
1 PACKAGE BODY PAY_CA_RL2_MAG AS
2 /* $Header: pycarl2mg.pkb 120.84.12020000.14 2013/01/17 09:01:32 rgottipa ship $ */
3
4 -- Name : get_report_parameters
5
6 -----------------------------------------------------------------------------
7 --
8 -- Purpose
9 -- The procedure gets the 'parameter' for which the report is being
10 -- run i.e., the period, state and business organization.
11 --
12 -- Arguments
13 -- p_pactid Payroll_action_id passed from pyugen process
14 -- p_year_start Start Date of the period for which the report
15 -- has been requested
16 -- p_year_end End date of the period
17 -- p_business_group_id Business group for which the report is being run
18 -- p_report_type Type of report being run RL1
19 --
20 -- Notes
21 ----------------------------------------------------------------------------
22
23 FUNCTION append_to_lob(p_text in varchar)
24 RETURN BLOB IS
25
26 text_size NUMBER;
27 raw_data RAW(32767);
28 temp_blob BLOB;
29 BEGIN
30
31 raw_data:=utl_raw.cast_to_raw(p_text);
32 text_size:=utl_raw.length(raw_data);
33
34 dbms_lob.createtemporary(temp_blob,false,DBMS_LOB.CALL);
35 dbms_lob.open(temp_blob,dbms_lob.lob_readwrite);
36
37 dbms_lob.writeappend(temp_blob,
38 text_size,
39 raw_data);
40
41 IF dbms_lob.ISOPEN(temp_blob)=1 THEN
42 hr_utility.trace('Closing temp_lob' );
43 dbms_lob.close(temp_blob);
44 hr_utility.trace('Closed temp_lob' );
45 END IF;
46
47 return temp_blob;
48 END;
49
50
51 procedure store_further_information( p_assg_actid IN NUMBER) IS
52
53 i NUMBER := 0;
54
55 CURSOR c_further_codes ( p_assg_actid number) is
56 SELECT decode(FT.FURTHER_INFO_CODE,
57 'RL2_FURTHER_INFO_AMOUNT_201','201',
58 'RL2_FURTHER_INFO_AMOUNT_235','235',
59 'RL2_FURTHER_INFO_AMOUNT_210','210', /* 14701748 */
60 substr(substr(FT.FURTHER_INFO_CODE,25),1,1)||'-'||substr(substr(FT.FURTHER_INFO_CODE,25),2)) code,
61 FT.FURTHER_INFO_VALUE value
62 FROM PAY_CA_EOY_RL2_FURTHER_INFO_V FT
63 WHERE FT.ASSIGNMENT_ACTION_ID = p_assg_actid
64 ORDER BY code;
65
66 begin
67 for rec in c_further_codes(p_assg_actid)
68 loop
69 i:=i+1;
70 hr_utility.trace('i '||i);
71 hr_utility.trace('rec.code '||rec.code);
72 hr_utility.trace('rec.value '||rec.value);
73 g_further_info_list(i).code := rec.code;
74 g_further_info_list(i).amount := rec.value;
75 end loop;
76 end store_further_information;
77
78 procedure get_further_information(p_index in number,
79 p_code in out nocopy varchar2,
80 p_amount in out nocopy varchar2)
81 is
82 begin
83
84 hr_utility.trace('p_index '||p_index);
85 if g_further_info_list.exists(p_index) then
86 p_code := g_further_info_list(p_index).code;
87 p_amount := g_further_info_list(p_index).amount;
88 else
89 p_code := null;
90 p_amount := null;
91 end if;
92
93 end get_further_information;
94
95 PROCEDURE get_report_parameters
96 ( p_pactid IN NUMBER,
97 p_year_start IN OUT NOCOPY DATE,
98 p_year_end IN OUT NOCOPY DATE,
99 p_report_type IN OUT NOCOPY VARCHAR2,
100 p_business_group_id IN OUT NOCOPY NUMBER,
101 p_legislative_param IN OUT NOCOPY VARCHAR2
102 ) IS
103 BEGIN
104 --hr_utility.trace_on('Y','RL2MAG');
105 hr_utility.set_location('pay_ca_rl2_mag.get_report_parameters', 10);
106
107 SELECT ppa.start_date,
108 ppa.effective_date,
109 ppa.business_group_id,
110 ppa.report_type,
111 ppa.legislative_parameters
112 INTO p_year_start,
113 p_year_end,
114 p_business_group_id,
115 p_report_type,
116 p_legislative_param
117 FROM pay_payroll_actions ppa
118 WHERE payroll_action_id = p_pactid;
119
120 hr_utility.set_location('pay_ca_rl2_mag.get_report_parameters', 20);
121
122 END get_report_parameters;
123
124 ---------------------------------------------------------------------------
125 --Procedure Name : validate_transmitter_info
126 --Purpose
127 -- This procedure is used for checking if the correct transmitter
128 -- record details has been entered.If any of the following data
129 -- Package Type,Source Of Rl2 Slip ,Transmitter Number,Transmitter Name
130 -- is missing then the RL2 Electronic Interface is made to error out.
131 ----------------------------------------------------------------------------
132
133 PROCEDURE validate_transmitter_info(p_payroll_action_id IN NUMBER,
134 p_bg_id IN NUMBER,
135 p_effective_date IN DATE) IS
136 BEGIN
137
138 DECLARE
139
140 CURSOR cur_arch_pactid(p_transmitter_org_id NUMBER) IS
141 SELECT
142 ppa.payroll_action_id
143 FROM
144 pay_payroll_actions ppa
145 WHERE
146 ppa.business_group_id = p_bg_id AND
147 ppa.report_type = 'RL2' AND
148 ppa.report_qualifier = 'CAEOYRL2' AND
149 ppa.report_category = 'ARCHIVE' AND
150 ppa.effective_date = p_effective_date AND
151 p_transmitter_org_id =
152 pay_ca_rl2_mag.get_parameter('PRE_ORGANIZATION_ID',
153 ppa.legislative_parameters);
154
155 l_transmitter_number VARCHAR2(30);
156 l_transmitter_name VARCHAR2(100);
157 l_type_of_package VARCHAR2(30);
158 l_source_of_slips VARCHAR2(30);
159 dummy NUMBER;
160 dummy1 VARCHAR2(10);
161 l_transmitter_org_id NUMBER;
162 l_arch_pactid NUMBER;
163 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
164 l_address_line1 per_addresses.address_line1%TYPE;
165
166 CURSOR cur_ppa IS
167 SELECT
168 ppa.legislative_parameters
169 FROM
170 pay_payroll_actions ppa
171 WHERE
172 ppa.payroll_action_id = p_payroll_action_id;
173
174 BEGIN
175
176 OPEN cur_ppa;
177 FETCH cur_ppa
178 INTO l_legislative_parameters;
179 CLOSE cur_ppa;
180
181 l_transmitter_org_id := pay_ca_rl2_mag.get_parameter('TRANSMITTER_PRE',
182 l_legislative_parameters);
183
184 hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
185 hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
186 hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
187 hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
188
189 OPEN cur_arch_pactid(l_transmitter_org_id);
190 FETCH cur_arch_pactid
191 INTO l_arch_pactid;
192 CLOSE cur_arch_pactid;
193
194 l_transmitter_number := pay_ca_rl2_mag.get_transmitter_item( p_bg_id,
195 l_arch_pactid,
196 'CAEOY_RL2_TRANSMITTER_NUMBER');
197 l_transmitter_name := pay_ca_rl2_mag.get_transmitter_item( p_bg_id,
198 l_arch_pactid,
199 'CAEOY_RL2_TRANSMITTER_NAME');
200 BEGIN
201
202 hr_utility.trace('l_transmitter_number = ' || l_transmitter_number);
203 SELECT substr(l_transmitter_number,1,2)
204 INTO dummy1
205 FROM dual;
206
207 IF (dummy1 <> 'NP' OR
208 length(l_transmitter_number) <> 8) THEN
209 RAISE INVALID_NUMBER;
210 END IF;
211
212 SELECT to_number(substr(l_transmitter_number,3,6))
213 INTO dummy
214 FROM dual;
215
216 EXCEPTION
217 WHEN INVALID_NUMBER THEN
218 hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
219 hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
220 pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
221 pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
222 hr_utility.raise_error;
223 END;
224
225 l_type_of_package := pay_ca_rl2_mag.get_transmitter_item(p_bg_id,
226 l_arch_pactid,
227 'CAEOY_RL2_TRANSMITTER_PACKAGE_TYPE');
228
229 hr_utility.trace('l_type_of_package = ' || l_type_of_package);
230
231 IF l_type_of_package IS NULL THEN
232 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
233 hr_utility.raise_error;
234 END IF;
235
236 l_source_of_slips := pay_ca_rl2_mag.get_transmitter_item(p_bg_id,
237 l_arch_pactid,
238 'CAEOY_RL2_SOURCE_OF_SLIPS');
239 hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
240
241 IF l_source_of_slips IS NULL THEN
242 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
243 hr_utility.raise_error;
244 END IF;
245
246 l_address_line1 := pay_ca_rl2_mag.get_transmitter_item(p_bg_id,
247 l_arch_pactid,
248 'CAEOY_RL2_TRANSMITTER_ADDRESS_LINE1');
249 hr_utility.trace('l_address_line1 = ' || l_address_line1);
250
251 IF l_address_line1 IS NULL THEN
252 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
253 hr_utility.raise_error;
254 END IF;
255
256 END;
257
258 END validate_transmitter_info;
259
260
261 -----------------------------------------------------------------------------
262 --Name
263 -- range_cursor
264 --Purpose
265 -- This procedure defines a SQL statement
266 -- to fetch all the people to be included in the report. This SQL statement
267 -- is used to define the 'chunks' for multi-threaded operation
268 --Arguments
269 -- p_pactid payroll action id for the report
270 -- p_sqlstr the SQL statement to fetch the people
271 ------------------------------------------------------------------------------
272 PROCEDURE range_cursor (
273 p_pactid IN NUMBER,
274 p_sqlstr OUT NOCOPY VARCHAR2
275 )
276 IS
277 p_year_start DATE;
278 p_year_end DATE;
279 p_business_group_id NUMBER;
280 p_report_type VARCHAR2(30);
281 p_legislative_param pay_payroll_actions.legislative_parameters%type;
282
283 BEGIN
284
285 hr_utility.set_location( 'pay_ca_rl2_mag.range_cursor', 10);
286
287 get_report_parameters(
288 p_pactid,
289 p_year_start,
290 p_year_end,
291 p_report_type,
292 p_business_group_id,
293 p_legislative_param
294 );
295
296 hr_utility.set_location( 'pay_ca_rl2_mag.range_cursor', 20);
297
298 p_sqlstr := 'select /*+ leading(ppa_mag,ppa_arch,paa_arch)
299 index(emp.paa_arch,PAY_ASSIGNMENT_ACTIONS_PK)
300 use_hash(emp.ppa_arch,hoi,tran.ppa_arch)
301 */ distinct to_number(emp.person_id)
302 from pay_ca_eoy_rl2_employee_info_v emp,
303 pay_ca_eoy_rl2_trans_info_v tran,
304 pay_assignment_actions paa_arch,
305 pay_payroll_actions ppa_arch,
306 pay_payroll_actions ppa_mag,
307 hr_organization_information hoi
308 where ppa_mag.payroll_action_id = :payroll_action_id
309 and ppa_arch.business_group_id+0 = ppa_mag.business_group_id
310 and ppa_arch.effective_date = ppa_mag.effective_date
311 and ppa_arch.report_type = ''RL2''
312 and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
313 and tran.reporting_year = to_char(ppa_arch.effective_date,''YYYY'')
314 and tran.business_group_id = ppa_arch.business_group_id
315 and tran.reporting_year = pay_ca_rl2_mag.get_parameter(''REPORTING_YEAR'',ppa_mag.legislative_parameters)
316 and paa_arch.payroll_action_id = tran.payroll_action_id
317 and paa_arch.action_status = ''C''
318 and paa_arch.assignment_action_id = emp.assignment_action_id
319 and paa_arch.payroll_action_id = emp.payroll_action_id
320 and emp.business_group_id = ppa_arch.business_group_id
321 and decode(hoi.org_information3, ''Y'', hoi.organization_id, hoi.org_information20) =
322 pycadar_pkg.get_parameter(''TRANSMITTER_PRE'', ppa_mag.legislative_parameters )
323 and hoi.org_information_context =''Prov Reporting Est''
324 and to_char(hoi.organization_id) =
325 pycadar_pkg.get_parameter(''PRE_ORGANIZATION_ID'',ppa_arch.legislative_parameters)
326 order by to_number(emp.person_id)' ;
327
328 hr_utility.set_location( 'pay_ca_rl2_mag.range_cursor',30);
329
330 END range_cursor;
331
332 -------------------------------------------------------------------------------
333 --Name
334 -- create_assignment_act
335 --Purpose
336 -- Creates assignment actions for the payroll action associated with the
337 -- report
338 --Arguments
339 -- p_pactid payroll action for the report
340 -- p_stperson starting person id for the chunk
341 -- p_endperson last person id for the chunk
342 -- p_chunk size of the chunk
343 --Note
344 -- The procedure processes assignments in 'chunks' to facilitate
345 -- multi-threaded operation. The chunk is defined by the size and the
346 -- starting and ending person id. An interlock is also created against the
347 -- pre-processor assignment action to prevent rolling back of the archiver.
348 ------------------------------------------------------------------------------
349 PROCEDURE create_assignment_act(
350 p_pactid IN NUMBER,
351 p_stperson IN NUMBER,
352 p_endperson IN NUMBER,
353 p_chunk IN NUMBER )
354 IS
355 -- Cursor to retrieve all the assignments for all GRE's
356 -- archived in a reporting year
357
358 CURSOR c_all_asg(p_leg_param varchar2,
359 p_business_grpid number,
360 p_effective_dt date) IS
361 SELECT paf.person_id,
362 paf.assignment_id,
363 paa_arch.tax_unit_id,
364 paf.effective_end_date,
365 paa_arch.assignment_action_id,
366 ppa_arch.payroll_action_id
367 FROM pay_payroll_actions ppa_arch,
368 pay_assignment_actions paa_arch,
369 per_all_assignments_f paf,
370 hr_organization_information hoi
371 WHERE ppa_arch.report_type = 'RL2'
372 AND ppa_arch.business_group_id+0 = p_business_grpid
373 AND ppa_arch.effective_date = p_effective_dt
374 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
375 AND paa_arch.action_status = 'C'
376 AND paf.assignment_id = paa_arch.assignment_id
377 AND paf.person_id BETWEEN p_stperson AND p_endperson
378 AND paf.effective_start_date <= ppa_arch.effective_date
379 AND paf.effective_end_date >= ppa_arch.start_date
380 AND decode(hoi.org_information3, 'Y', hoi.organization_id, hoi.org_information20) =
381 substr(p_leg_param, instr(p_leg_param,'TRANSMITTER_PRE=')+16)
382 AND hoi.org_information_context = 'Prov Reporting Est'
383 AND hoi.organization_id =
384 substr(ppa_arch.legislative_parameters,
385 instr(ppa_arch.legislative_parameters,'PRE_ORGANIZATION_ID=')+20)
386 AND paf.effective_end_date = (SELECT max(paf1.effective_end_date)
387 FROM per_all_assignments_f paf1
388 WHERE paf1.assignment_id = paf.assignment_id
389 AND paf1.effective_start_date <= p_effective_dt);
390
391 l_year_start DATE;
392 l_year_end DATE;
393 l_effective_end_date DATE;
394 l_report_type VARCHAR2(30);
395 l_business_group_id NUMBER;
396 l_person_id NUMBER;
397 l_assignment_id NUMBER;
398 l_assignment_action_id NUMBER;
399 l_value NUMBER;
400 l_tax_unit_id NUMBER;
401 lockingactid NUMBER;
402 /* Added by ssmukher */
403 l_prev_payact NUMBER;
404 l_payroll_act NUMBER;
405 l_emplyer_name VARCHAR2(240);
406 l_quebec_no VARCHAR2(20);
407 l_file_no VARCHAR2(10);
408 l_return NUMBER;
409 l_addr_line VARCHAR2(240);
410 l_legislative_param pay_payroll_actions.legislative_parameters%type;
411
412 BEGIN
413
414 -- hr_utility.trace_on(NULL,'RL2MAG');
415 -- Get the report parameters. These define the report being run.
416 l_prev_payact := -1;
417 hr_utility.set_location( 'pay_ca_rl2_mag.create_assignment_act',10);
418
419 get_report_parameters(
420 p_pactid,
421 l_year_start,
422 l_year_end,
423 l_report_type,
424 l_business_group_id,
425 l_legislative_param
426 );
427 /* Validating Transmitter Information */
428 validate_transmitter_info(p_pactid,
429 l_business_group_id,
430 l_year_end);
431
432 --Open the appropriate cursor
433
434 hr_utility.set_location( 'pay_ca_rl2_mag.create_assignment_act',20);
435 hr_utility.trace('Report type '||l_report_type);
436 IF l_report_type = 'RL2_XML_MAG' THEN
437 OPEN c_all_asg(l_legislative_param,
438 l_business_group_id,
439 l_year_end);
440 LOOP
441 FETCH c_all_asg INTO l_person_id,
442 l_assignment_id,
443 l_tax_unit_id,
444 l_effective_end_date,
445 l_assignment_action_id,
446 l_payroll_act;
447
448 hr_utility.set_location('pay_ca_rl2_mag.create_assignment_act', 30);
449
450 EXIT WHEN c_all_asg%NOTFOUND;
451
452 --Create the assignment action for the record
453
454 /* Validating QIN Number information */
455 if l_prev_payact <> l_payroll_act then
456
457 hr_utility.trace('The payroll action id '||l_payroll_act);
458
459 l_prev_payact := l_payroll_act;
460 l_emplyer_name := pay_ca_rl2_mag.get_employer_item(l_business_group_id,
461 l_payroll_act,
462 'CAEOY_RL2_EMPLOYER_NAME');
463
464 l_quebec_no := pay_ca_rl2_mag.get_employer_item(l_business_group_id,
465 l_payroll_act,
466 'CAEOY_RL2_QUEBEC_BN');
467 hr_utility.trace('The Quebec Number is '||l_quebec_no);
468 l_file_no := substr(l_quebec_no,13,4);
469 l_quebec_no := substr(l_quebec_no ,1,10);
470
471 /* Fix for Bug# 4038551 */
472 if (l_file_no = '0000' and l_quebec_no = '0000000000') or
473 length(l_file_no) < 4
474 then
475 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
476 pay_core_utils.push_token('PRE_NAME',l_emplyer_name);
477 hr_utility.raise_error;
478 end if;
479
480
481 /* Erroring out the RL2 Electronic Interface if any of the
482 mandatory information is missing i.e Address Line 1 */
483
484 l_addr_line := pay_ca_rl2_mag.get_employer_item(l_business_group_id,
485 l_payroll_act,
486 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE1');
487 if l_addr_line = ' '
488 then
489 pay_core_utils.push_message(800,'PAY_CA_RL2_MISSING_ADDRESS','P');
490 hr_utility.raise_error;
491 end if;
492 hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
493 l_return := validate_quebec_number(l_quebec_no,l_emplyer_name);
494
495 end if ;
496 hr_utility.trace('Assignment Fetched - ');
497 hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
498 hr_utility.trace('Person Id : '|| to_char(l_person_id));
499 hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
500 hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
501
502 hr_utility.set_location('pay_ca_rl2_mag.create_assignment_act', 40);
503
504 SELECT pay_assignment_actions_s.nextval
505 INTO lockingactid
506 FROM dual;
507
508 hr_utility.set_location('pay_ca_rl2_mag.create_assignment_act', 50);
509 hr_nonrun_asact.insact(lockingactid,
510 l_assignment_id,
511 p_pactid,
512 p_chunk,
513 l_tax_unit_id);
514
515 /* Update the serial number column with the person id
516 */
517
518 -- hr_utility.trace('updating asg. action');
519
520 update pay_assignment_actions aa
521 set aa.serial_number = to_char(l_person_id)
522 where aa.assignment_action_id = lockingactid;
523
524 hr_utility.set_location('pay_ca_rl2_mag.create_assignment_act', 60);
525
526 hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
527
528 hr_utility.set_location('pay_ca_rl2_mag.create_assignment_act', 70);
529 hr_utility.trace('Interlock Created - ');
530 hr_utility.trace('Locking Action : '|| to_char(lockingactid));
531 hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
532
533 END LOOP;
534 CLOSE c_all_asg;
535
536 END IF;
537
538 END create_assignment_act;
539
540 FUNCTION get_parameter(name IN varchar2, parameter_list varchar2)
541 RETURN varchar2 IS
542 start_ptr number;
543 end_ptr number;
544 token_val pay_payroll_actions.legislative_parameters%type;
545 par_value pay_payroll_actions.legislative_parameters%type;
546 BEGIN
547 --
548 token_val := name||'=';
549 --
550 start_ptr := instr(parameter_list, token_val) + length(token_val);
551 end_ptr := instr(parameter_list, ' ',start_ptr);
552 --
553 /* if there is no spaces use then length of the string */
554 IF end_ptr = 0 THEN
555 end_ptr := length(parameter_list)+1;
556 END IF;
557 --
558 /* Did we find the token */
559 IF instr(parameter_list, token_val) = 0 THEN
560 par_value := NULL;
561 ELSE
562 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
563 END IF;
564
565 RETURN par_value;
566
567 END get_parameter;
568
569 /* 14701748 */
570 function get_slip_seq_no(name in varchar2, parameter_list varchar2, position number) return varchar2 is
571 start_ptr number;
572 end_ptr number;
573 begin
574
575 if position = 1 then
576 start_ptr := 1;
577 else
578 /* if the required position is greater than the number of positions(slip no's)
579 then return last number*/
580 if instr(parameter_list, name,1, position-1) = 0 then
581 start_ptr := instr(parameter_list, name, -1, 1) + 1;
582 else
583 start_ptr := instr(parameter_list, name,1, position-1) + 1;
584 end if;
585 end if;
586 end_ptr := instr(parameter_list, name, 1, position);
587 --
588 /* if there is no special character or required position is greater than the
589 number of positions(slip no's) use then length of the string */
590 if end_ptr = 0 then
591 end_ptr := length(parameter_list)+1;
592 end if;
593
594 return substr(parameter_list, start_ptr, end_ptr - start_ptr);
595 --
596 end get_slip_seq_no;
597
598 FUNCTION get_transmitter_item (p_business_group_id IN number,
599 p_pact_id IN number,
600 p_archived_item IN varchar2)
601 RETURN varchar2 IS
602
603 CURSOR c_trans_info IS
604 SELECT nvl(transmitter_number,' '),
605 nvl(reporting_year,'0000'),
606 nvl(transmitter_package_type,'0'),
607 nvl(transmitter_type_indicator,'0'),
608 nvl(transmitter_name,' '),
609 nvl(source_of_slips,' '),
610 nvl(transmitter_address_line1,' '),
611 nvl(transmitter_address_line2,' '),
612 nvl(transmitter_city,' '),
613 nvl(transmitter_province,' '),
614 nvl(transmitter_postal_code,' '),
615 nvl(transmitter_tech_contact_name,' '),
616 nvl(transmitter_tech_contact_code,'000'),
617 nvl(transmitter_tech_contact_phone,'0000000'),
618 nvl(transmitter_tech_contact_extn,'0000'),
619 nvl(transmitter_tech_contact_lang,' '),
620 nvl(transmitter_acct_contact_name,' '),
621 nvl(transmitter_acct_contact_code,'000'),
622 nvl(transmitter_acct_contact_phone,'0000000'),
623 nvl(transmitter_acct_contact_extn,'0000'),
624 nvl(transmitter_acct_contact_lang,' ')
625 FROM pay_ca_eoy_rl2_trans_info_v
626 WHERE business_group_id = p_business_group_id
627 AND payroll_action_id = p_pact_id;
628
629 l_trans_number varchar2(240);
630 l_reporting_year varchar2(240);
631 l_trans_package_type varchar2(240);
632 l_trans_type_indicator varchar2(240);
633 l_trans_name varchar2(240);
634 l_source_of_slips varchar2(240);
635 l_trans_address_line1 varchar2(240);
636 l_trans_address_line2 varchar2(240);
637 l_trans_city varchar2(240);
638 l_trans_province varchar2(240);
639 l_trans_postal_code varchar2(240);
640 l_trans_tech_contact_name varchar2(240);
641 l_trans_tech_contact_code varchar2(240);
642 l_trans_tech_contact_phone varchar2(240);
643 l_trans_tech_contact_extn varchar2(240);
644 l_trans_tech_contact_lang varchar2(240);
645 l_trans_acct_contact_name varchar2(240);
646 l_trans_acct_contact_code varchar2(240);
647 l_trans_acct_contact_phone varchar2(240);
648 l_trans_acct_contact_extn varchar2(240);
649 l_trans_acct_contact_lang varchar2(240);
650
651 l_return_value varchar2(240);
652
653 BEGIN
654
655 OPEN c_trans_info;
656 FETCH c_trans_info
657 INTO l_trans_number,
658 l_reporting_year,
659 l_trans_package_type,
660 l_trans_type_indicator,
661 l_trans_name,
662 l_source_of_slips,
663 l_trans_address_line1,
664 l_trans_address_line2,
665 l_trans_city,
666 l_trans_province,
667 l_trans_postal_code,
668 l_trans_tech_contact_name,
669 l_trans_tech_contact_code,
670 l_trans_tech_contact_phone,
671 l_trans_tech_contact_extn,
672 l_trans_tech_contact_lang,
673 l_trans_acct_contact_name,
674 l_trans_acct_contact_code,
675 l_trans_acct_contact_phone,
676 l_trans_acct_contact_extn,
677 l_trans_acct_contact_lang;
678
679 CLOSE c_trans_info;
680
681 IF p_archived_item = 'CAEOY_RL2_TRANSMITTER_NUMBER' THEN
682 l_return_value := l_trans_number;
683 ELSIF p_archived_item = 'CAEOY_TAXATION_YEAR' THEN
684 l_return_value := l_reporting_year;
685 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_PACKAGE_TYPE' THEN
686 l_return_value := l_trans_package_type;
687 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_TYPE' THEN
688 l_return_value := l_trans_type_indicator;
689 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_NAME' THEN
690 l_return_value := l_trans_name;
691 ELSIF p_archived_item = 'CAEOY_RL2_SOURCE_OF_SLIPS' THEN
692 l_return_value := l_source_of_slips;
693 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_ADDRESS_LINE1' THEN
694 l_return_value := l_trans_address_line1;
695 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_ADDRESS_LINE2' THEN
696 l_return_value := l_trans_address_line2;
697 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_CITY' THEN
698 l_return_value := l_trans_city;
699 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_PROVINCE' THEN
700 l_return_value := l_trans_province;
701 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_POSTAL_CODE' THEN
702 l_return_value := l_trans_postal_code;
703 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_NAME' THEN
704 l_return_value := l_trans_tech_contact_name;
705 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_AREA_CODE' THEN
706 l_return_value := l_trans_tech_contact_code;
707 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_PHONE' THEN
708 l_return_value := l_trans_tech_contact_phone;
709 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_EXTENSION' THEN
710 l_return_value := l_trans_tech_contact_extn;
711 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_LANGUAGE' THEN
712 l_return_value := l_trans_tech_contact_lang;
713 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_NAME' THEN
714 l_return_value := l_trans_acct_contact_name;
715 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_AREA_CODE' THEN
716 l_return_value := l_trans_acct_contact_code;
717 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_PHONE' THEN
718 l_return_value := l_trans_acct_contact_phone;
719 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_EXTENSION' THEN
720 l_return_value := l_trans_acct_contact_extn;
721 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_LANGUAGE' THEN
722 l_return_value := l_trans_acct_contact_lang;
723 END IF;
724
725 RETURN l_return_value;
726
727 END get_transmitter_item;
728
729 FUNCTION get_employer_item (p_business_group_id IN number,
730 p_pact_id IN number,
731 p_archived_item IN varchar2)
732 RETURN varchar2 IS
733
734 CURSOR c_employer_info IS
735 SELECT nvl(employer_name,' '),
736 nvl(quebec_business_number,'0000000000 0000'),
737 nvl(reporting_year,'0000'),
738 nvl(employer_add_line1,' '),
739 nvl(employer_add_line2,' '),
740 nvl(employer_city,' '),
741 nvl(employer_province,' '),
742 nvl(employer_postal_code,' ')
743 FROM pay_ca_eoy_rl2_trans_info_v
744 WHERE business_group_id = p_business_group_id
745 AND payroll_action_id = p_pact_id;
746
747 l_employer_name varchar2(240);
748 l_reporting_year varchar2(240);
749 l_quebec_business_number varchar2(240);
750 l_employer_add_line1 varchar2(240);
751 l_employer_add_line2 varchar2(240);
752 l_employer_city varchar2(240);
753 l_employer_province varchar2(240);
754 l_employer_postal_code varchar2(240);
755
756 l_return_value varchar2(240);
757
758 BEGIN
759
760 OPEN c_employer_info;
761 FETCH c_employer_info
762 INTO l_employer_name,
763 l_quebec_business_number,
764 l_reporting_year,
765 l_employer_add_line1,
766 l_employer_add_line2,
767 l_employer_city,
768 l_employer_province,
769 l_employer_postal_code;
770
771 CLOSE c_employer_info;
772
773 IF p_archived_item = 'CAEOY_RL2_QUEBEC_BN' THEN
774 l_return_value := l_quebec_business_number;
775 ELSIF p_archived_item = 'CAEOY_TAXATION_YEAR' THEN
776 l_return_value := l_reporting_year;
777 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_NAME' THEN
778 l_return_value := l_employer_name;
779 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE1' THEN
780 l_return_value := l_employer_add_line1;
781 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE2' THEN
782 l_return_value := l_employer_add_line2;
783 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_CITY' THEN
784 l_return_value := l_employer_city;
785 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_PROVINCE' THEN
786 l_return_value := l_employer_province;
787 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_POSTAL_CODE' THEN
788 l_return_value := l_employer_postal_code;
789 END IF;
790
791 RETURN l_return_value;
792
793 END get_employer_item;
794
795
796 PROCEDURE end_of_file is
797
798 BEGIN
799
800 DECLARE
801
802 l_final_xml_string VARCHAR2(32000);
803
804 BEGIN
805
806 l_final_xml_string := '</Transmission>';
807 pay_core_files.write_to_magtape_lob(l_final_xml_string);
808
809 END;
810
811 END;
812
813 PROCEDURE xml_transmitter_record IS
814 BEGIN
815
816 DECLARE
817
818 CURSOR c_trans_info(p_business_group_id IN number,
819 p_pact_id IN number) IS
820 SELECT nvl(transmitter_number,' '),
821 nvl(reporting_year,'0000'),
822 nvl(transmitter_package_type,'0'),
823 nvl(transmitter_type_indicator,'0'),
824 nvl(transmitter_name,' '),
825 nvl(source_of_slips,' '),
826 nvl(transmitter_address_line1,' '),
827 nvl(transmitter_address_line2,' '),
828 nvl(transmitter_city,' '),
829 nvl(transmitter_province,' '),
830 nvl(transmitter_postal_code,' '),
831 nvl(transmitter_tech_contact_name,' '),
832 nvl(transmitter_tech_contact_code,'000'),
833 nvl(transmitter_tech_contact_phone,'0000000'),
834 nvl(transmitter_tech_contact_extn,'0000'),
835 nvl(transmitter_tech_contact_lang,' '),
836 nvl(transmitter_acct_contact_name,' '),
837 nvl(transmitter_acct_contact_code,'000'),
838 nvl(transmitter_acct_contact_phone,'0000000'),
839 nvl(transmitter_acct_contact_extn,'0000'),
840 nvl(transmitter_acct_contact_lang,' ')
841 FROM pay_ca_eoy_rl2_trans_info_v
842 WHERE business_group_id = p_business_group_id
843 AND payroll_action_id = p_pact_id;
844
845 l_trans_number varchar2(240);
846 l_reporting_year varchar2(240);
847 l_trans_package_type varchar2(240);
848 l_trans_type_indicator varchar2(240);
849 l_trans_name varchar2(240);
850 l_source_of_slips varchar2(240);
851 l_trans_address_line1 varchar2(240);
852 l_trans_address_line2 varchar2(240);
853 l_trans_city varchar2(240);
854 l_trans_province varchar2(240);
855 l_trans_postal_code varchar2(240);
856 l_trans_tech_contact_name varchar2(240);
857 l_trans_tech_contact_code varchar2(240);
858 l_trans_tech_contact_phone varchar2(240);
859 l_trans_tech_contact_extn varchar2(240);
860 l_trans_tech_contact_lang varchar2(240);
861 l_trans_acct_contact_name varchar2(240);
862 l_trans_acct_contact_code varchar2(240);
863 l_trans_acct_contact_phone varchar2(240);
864 l_trans_acct_contact_extn varchar2(240);
865 l_trans_acct_contact_lang varchar2(240);
866
867 l_final_xml_string VARCHAR2(32000);
868 l_tech_accnt_info VARCHAR2(32000);
869
870 TYPE transmitter_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
871
872 tab_transmitter transmitter_info;
873
874 lAnnee NUMBER;
875 lTypeEnvoi NUMBER;
876 lProvenance NUMBER;
877 lNo NUMBER;
878 lType NUMBER;
879 lNom1 NUMBER;
880 lNom2 NUMBER;
881 lLigne1 NUMBER;
882 lLigne2 NUMBER;
883 lVille NUMBER;
884 lProvince NUMBER;
885 lCodePostal NUMBER;
886 lNom NUMBER;
887 lIndRegional NUMBER;
888 lTel NUMBER;
889 lPosteTel NUMBER;
890 lLangue NUMBER;
891 lANom NUMBER;
892 lAIndRegional NUMBER;
893 lATel NUMBER;
894 lAPosteTel NUMBER;
895 lALangue NUMBER;
896
897 EOL VARCHAR2(5);
898 l_transmitter_name VARCHAR2(100);
899 l_taxation_year VARCHAR2(4);
900 l_return VARCHAR2(60);
901 l_payroll_actid NUMBER;
902 l_year_start DATE;
903 l_year_end DATE;
904 l_report_type VARCHAR2(5);
905 l_business_grpid NUMBER;
906 l_legislative_param pay_payroll_actions.legislative_parameters%type;
907 /* Bug 4777374 Fix */
908 l_Informatique_tag CHAR(1);
909 l_Comptabilite_tag CHAR(1);
910 /* Bug 4906963 Fix */
911 l_authorization_no VARCHAR2(20);
912 lNoConcepteur NUMBER;
913 l_VersionSchema VARCHAR2(20);
914 BEGIN
915
916 hr_utility.trace('XML Transmitter');
917
918
919 SELECT
920 fnd_global.local_chr(13) || fnd_global.local_chr(10)
921 INTO EOL
922 FROM dual;
923
924 lAnnee := 1;
925 lTypeEnvoi := 2;
926 lProvenance := 3;
927 lNo := 4;
928 lType := 5;
929 lNom1 := 6;
930 lNom2 := 7;
931 lLigne1 := 8;
932 lLigne2 := 9;
933 lVille := 10;
934 lProvince := 11;
935 lCodePostal := 12;
936 lNom := 13;
937 lIndRegional := 14;
938 lTel := 15;
939 lPosteTel := 16;
940 lLangue := 17;
941 lANom := 18;
942 lAIndRegional := 19;
943 lATel := 20;
944 lAPosteTel := 21;
945 lALangue := 22;
946 lNoConcepteur := 23;
947
948 l_Informatique_tag := 'N';
949 l_Comptabilite_tag := 'N';
950
951 l_taxation_year
952 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
953 l_payroll_actid
954 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
955
956 get_report_parameters(
957 l_payroll_actid,
958 l_year_start,
959 l_year_end,
960 l_report_type,
961 l_business_grpid,
962 l_legislative_param
963 );
964
965
966 hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
967 hr_utility.trace('XML Transmitter: l_payroll_Action_id = ' || to_char(l_payroll_actid));
968
969 OPEN c_trans_info(l_business_grpid,
970 l_payroll_actid);
971 FETCH c_trans_info
972 INTO l_trans_number,
973 l_reporting_year,
974 l_trans_package_type,
975 l_trans_type_indicator,
976 l_trans_name,
977 l_source_of_slips,
978 l_trans_address_line1,
979 l_trans_address_line2,
980 l_trans_city,
981 l_trans_province,
982 l_trans_postal_code,
983 l_trans_tech_contact_name,
984 l_trans_tech_contact_code,
985 l_trans_tech_contact_phone,
986 l_trans_tech_contact_extn,
987 l_trans_tech_contact_lang,
988 l_trans_acct_contact_name,
989 l_trans_acct_contact_code,
990 l_trans_acct_contact_phone,
991 l_trans_acct_contact_extn,
992 l_trans_acct_contact_lang;
993
994 CLOSE c_trans_info;
995 -- Annee
996 tab_transmitter(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' ||EOL;
997 hr_utility.trace('tab_transmitter(lAnnee) = ' || tab_transmitter(lAnnee));
998
999 -- TypeEnvoi
1000 tab_transmitter(lTypeEnvoi) := '<TypeEnvoi>' ||
1001 convert_special_char(l_trans_package_type) || '</TypeEnvoi>' || EOL;
1002 hr_utility.trace('tab_transmitter(lTypeEnvoi) = ' ||
1003 tab_transmitter(lTypeEnvoi));
1004
1005 tab_transmitter(lProvenance) := '<Provenance>' ||
1006 convert_special_char(l_source_of_slips) || '</Provenance>' || EOL;
1007
1008 hr_utility.trace('tab_transmitter(lProvenance) = ' || tab_transmitter(lProvenance));
1009
1010 tab_transmitter(lNo) := '<No>' ||
1011 convert_special_char(l_trans_number) || '</No>' || EOL;
1012
1013 hr_utility.trace('tab_transmitter(lNo) = ' || tab_transmitter(lNo));
1014
1015
1016 IF l_trans_type_indicator IS NOT NULL AND
1017 l_trans_type_indicator <> '0' THEN
1018 tab_transmitter(lType) := '<Type>' ||
1019 convert_special_char(l_trans_type_indicator) || '</Type>' || EOL;
1020 ELSE
1021 tab_transmitter(lType) := NULL;
1022 END IF;
1023
1024 hr_utility.trace('tab_transmitter(lType) = ' || tab_transmitter(lType));
1025
1026 tab_transmitter(lNom1) := '<Nom1>' ||
1027 convert_special_char(substr(l_trans_name,1,30)) || '</Nom1>' || EOL;
1028
1029 hr_utility.trace('tab_transmitter(lNom1) = ' || tab_transmitter(lNom1));
1030
1031 l_return := substr(l_trans_name,31,30);
1032 IF l_return IS NOT NULL THEN
1033 tab_transmitter(lNom2) := '<Nom2>' || convert_special_char(l_return) || '</Nom2>' || EOL;
1034 ELSE
1035 tab_transmitter(lNom2) := NULL;
1036 END IF;
1037
1038 hr_utility.trace('tab_transmitter(lNom2) = ' || tab_transmitter(lNom2));
1039
1040
1041 tab_transmitter(lLigne1) := '<Ligne1>' ||
1042 convert_special_char(substr(l_trans_address_line1,1,30)) || '</Ligne1>' || EOL;
1043
1044 hr_utility.trace('tab_transmitter(lLigne1) = ' || tab_transmitter(lLigne1));
1045
1046
1047 IF (l_trans_address_line2 IS NOT NULL AND
1048 l_trans_address_line2 <> ' ') THEN
1049 tab_transmitter(lLigne2) := '<Ligne2>' ||
1050 convert_special_char(substr(l_trans_address_line2,1,30)) || '</Ligne2>' || EOL;
1051 ELSE
1052 tab_transmitter(lLigne2) := NULL;
1053 END IF;
1054
1055 hr_utility.trace('tab_transmitter(lLigne2) = ' || tab_transmitter(lLigne2));
1056
1057
1058 IF (l_trans_city IS NOT NULL AND
1059 l_trans_city <> ' ') THEN
1060 tab_transmitter(lVille) := '<Ville>' ||
1061 convert_special_char(substr(l_trans_city,1,30)) || '</Ville>' || EOL;
1062 ELSE
1063 tab_transmitter(lVille) := NULL;
1064 END IF;
1065
1066 hr_utility.trace('tab_transmitter(lVille) = ' || tab_transmitter(lVille));
1067
1068 IF (l_trans_province IS NOT NULL AND
1069 l_trans_province <> ' ') THEN
1070 tab_transmitter(lProvince) := '<Province>' ||
1071 convert_special_char(SUBSTR(hr_general.decode_lookup(
1072 'CA_PROVINCE',l_trans_province),1,20)) || '</Province>' || EOL;
1073 ELSE
1074 tab_transmitter(lProvince) := NULL;
1075 END IF;
1076 hr_utility.trace('tab_transmitter(lProvince) = ' || tab_transmitter(lProvince));
1077
1078 IF (l_trans_postal_code IS NOT NULL AND
1079 l_trans_postal_code <> ' ') THEN
1080 tab_transmitter(lCodePostal) := '<CodePostal>' ||
1081 convert_special_char(substr(l_trans_postal_code,1,6)) || '</CodePostal>' || EOL;
1082 ELSE
1083 tab_transmitter(lCodePostal) := NULL;
1084 END IF;
1085 hr_utility.trace('tab_transmitter(lCodePostal) = ' || tab_transmitter(lCodePostal));
1086
1087
1088 IF (l_trans_tech_contact_name IS NOT NULL AND
1089 l_trans_tech_contact_name <> ' ' ) THEN
1090 l_Informatique_tag := 'Y';
1091 tab_transmitter(lNom) := '<Nom>' ||
1092 convert_special_char(substr(l_trans_tech_contact_name,1,30)) || '</Nom>' || EOL;
1093 ELSE
1094 tab_transmitter(lNom) := NULL;
1095 END IF;
1096
1097 hr_utility.trace('tab_transmitter(lNom) = ' || tab_transmitter(lNom));
1098
1099
1100 IF (l_trans_tech_contact_code IS NOT NULL AND
1101 l_trans_tech_contact_code <> '000' ) THEN
1102 l_Informatique_tag := 'Y';
1103 tab_transmitter(lIndRegional) := '<IndRegional>' ||
1104 convert_special_char(l_trans_tech_contact_code) || '</IndRegional>' || EOL;
1105 ELSE
1106 tab_transmitter(lIndRegional) := NULL;
1107 END IF;
1108
1109 hr_utility.trace('tab_transmitter(lIndRegional) = ' || tab_transmitter(lIndRegional));
1110
1111 IF (l_trans_tech_contact_phone IS NOT NULL AND
1112 l_trans_tech_contact_phone <> '0000000' ) THEN
1113 l_Informatique_tag := 'Y';
1114 l_trans_tech_contact_phone := substr(l_trans_tech_contact_phone,1,3) || '-' || substr(l_trans_tech_contact_phone,4,4);
1115 tab_transmitter(lTel) := '<Tel>' || convert_special_char(l_trans_tech_contact_phone) || '</Tel>' || EOL;
1116 ELSE
1117 tab_transmitter(lTel) := NULL;
1118 END IF;
1119 hr_utility.trace('tab_transmitter(lTel) = ' || tab_transmitter(lTel));
1120
1121
1122 IF (l_trans_tech_contact_extn IS NOT NULL AND
1123 l_trans_tech_contact_extn <> '0000' ) THEN
1124 l_Informatique_tag := 'Y';
1125 tab_transmitter(lPosteTel) := '<PosteTel>' || convert_special_char(l_trans_tech_contact_extn) ||
1126 '</PosteTel>' || EOL;
1127 ELSE
1128 tab_transmitter(lPosteTel) := NULL;
1129 END IF;
1130 hr_utility.trace('tab_transmitter(lPosteTel) = ' ||
1131 tab_transmitter(lPosteTel));
1132
1133
1134 IF (l_trans_tech_contact_lang IS NOT NULL AND
1135 l_trans_tech_contact_lang <> ' ' ) THEN
1136 l_Informatique_tag := 'Y';
1137 tab_transmitter(lLangue) := '<Langue>' ||convert_special_char(l_trans_tech_contact_lang) || '</Langue>' || EOL;
1138 ELSE
1139 tab_transmitter(lLangue) := NULL;
1140 END IF;
1141
1142
1143 IF (l_trans_acct_contact_name IS NOT NULL AND
1144 l_trans_acct_contact_name <> ' ') THEN
1145 l_Comptabilite_tag := 'Y';
1146 tab_transmitter(lANom) := '<Nom>' ||
1147 convert_special_char(substr(l_trans_acct_contact_name,1,30)) || '</Nom>' || EOL;
1148 ELSE
1149 tab_transmitter(lANom) := NULL;
1150 END IF;
1151 hr_utility.trace('tab_transmitter(lANom) = ' || tab_transmitter(lANom));
1152
1153
1154 IF (l_trans_acct_contact_code IS NOT NULL AND
1155 l_trans_acct_contact_code <> '000' ) THEN
1156 l_Comptabilite_tag := 'Y';
1157 tab_transmitter(lAIndRegional) := '<IndRegional>' || convert_special_char(l_trans_acct_contact_code) ||
1158 '</IndRegional>' || EOL;
1159 ELSE
1160 tab_transmitter(lAIndRegional) := NULL;
1161 END IF;
1162 hr_utility.trace('tab_transmitter(lAIndRegional) = ' || tab_transmitter(lAIndRegional));
1163
1164
1165 IF (l_trans_acct_contact_phone IS NOT NULL AND
1166 l_trans_acct_contact_phone <> '0000000' ) THEN
1167 l_Comptabilite_tag := 'Y';
1168 l_trans_acct_contact_phone := substr(l_trans_acct_contact_phone,1,3) || '-' || substr(l_trans_acct_contact_phone,4,4);
1169 tab_transmitter(lATel) := '<Tel>' || convert_special_char(l_trans_acct_contact_phone) || '</Tel>' || EOL;
1170 ELSE
1171 tab_transmitter(lATel) := NULL;
1172 END IF;
1173 hr_utility.trace('tab_transmitter(lATel) = ' || tab_transmitter(lATel));
1174
1175
1176 IF (l_trans_acct_contact_extn IS NOT NULL AND
1177 l_trans_acct_contact_extn <> '0000') THEN
1178 l_Comptabilite_tag := 'Y';
1179 tab_transmitter(lAPosteTel) := '<PosteTel>' || convert_special_char(l_trans_acct_contact_extn) ||
1180 '</PosteTel>' || EOL;
1181 ELSE
1182 tab_transmitter(lAPosteTel) := NULL;
1183 END IF;
1184 hr_utility.trace('tab_transmitter(lAPosteTel) = ' ||
1185 tab_transmitter(lAPosteTel));
1186
1187 IF (l_trans_acct_contact_lang IS NOT NULL AND
1188 l_trans_acct_contact_lang <> ' ' ) THEN
1189 l_Comptabilite_tag := 'Y';
1190 tab_transmitter(lALangue) := '<Langue>' || convert_special_char(l_trans_acct_contact_lang) ||
1191 '</Langue>' || EOL;
1192 ELSE
1193 tab_transmitter(lALangue) := NULL;
1194 END IF;
1195
1196 --- Bug 6736354
1197 IF ( l_reporting_year = '2006' ) then
1198 l_authorization_no := 'RQ-06-02-048';
1199 ELSIF ( l_reporting_year = '2007' ) then
1200 l_authorization_no := 'RQ-07-02-069';
1201 ELSIF (l_reporting_year = '2008' ) then
1202 l_authorization_no := 'RQ-08-02-048';
1203 ELSIF (l_reporting_year = '2009' ) then
1204 l_authorization_no := 'RQ-09-02-019'; -- Bug 9206939
1205 ELSIF (l_reporting_year = '2010' ) then
1206 l_authorization_no := 'RQ-10-02-020'; -- Bug 10364973
1207 ELSIF (l_reporting_year = '2011' ) then
1208 l_authorization_no := 'RQ-11-02-035'; -- Bug 13486709
1209 ELSIF (l_reporting_year = '2012' ) then
1210 l_authorization_no := 'RQ-12-02-051'; -- Bug 15997013
1211 ELSE
1212 -- l_authorization_no := 'RQ-09-99-999'; --Bug 9041046
1213 /* Modified for bug 10134138
1214 For sending testing file format for certification no will be
1215 RQ-nn-99-999 nn stands for last two digit of reporting year.
1216 */
1217 l_authorization_no := 'RQ-'||substr(trim(l_reporting_year),3,2)||'-99-999';
1218 END IF;
1219 --- End 6736354
1220
1221 tab_transmitter(lNoConcepteur) := '<NoCertification>'||convert_special_char(l_authorization_no)||'</NoCertification>'||EOL;
1222
1223 hr_utility.trace('tab_transmitter(lALangue) = ' || tab_transmitter(lALangue));
1224
1225 IF l_Informatique_tag = 'Y' AND
1226 l_Comptabilite_tag = 'Y' THEN
1227 l_tech_accnt_info := '<Informatique>' || EOL ||
1228 tab_transmitter(lNom) ||
1229 tab_transmitter(lIndRegional) ||
1230 tab_transmitter(lTel) ||
1231 tab_transmitter(lPosteTel) ||
1232 tab_transmitter(lLangue) || '</Informatique>' || EOL ||
1233 '<Comptabilite>' || EOL ||
1234 tab_transmitter(lANom) ||
1235 tab_transmitter(lAIndRegional) ||
1236 tab_transmitter(lATel) ||
1237 tab_transmitter(lAPosteTel) ||
1238 tab_transmitter(lALangue) || '</Comptabilite>' ;
1239 ELSIF l_Informatique_tag = 'Y' AND
1240 l_Comptabilite_tag = 'N' THEN
1241 l_tech_accnt_info := '<Informatique>' || EOL ||
1242 tab_transmitter(lNom) ||
1243 tab_transmitter(lIndRegional) ||
1244 tab_transmitter(lTel) ||
1245 tab_transmitter(lPosteTel) ||
1246 tab_transmitter(lLangue) || '</Informatique>';
1247 ELSIF l_Comptabilite_tag = 'Y' AND
1248 l_Informatique_tag = 'N' THEN
1249 l_tech_accnt_info := '<Comptabilite>' || EOL ||
1250 tab_transmitter(lANom) ||
1251 tab_transmitter(lAIndRegional) ||
1252 tab_transmitter(lATel) ||
1253 tab_transmitter(lAPosteTel) ||
1254 tab_transmitter(lALangue) || '</Comptabilite>';
1255 ELSE
1256 l_tech_accnt_info := NULL;
1257 END IF;
1258
1259 -- Bug 7602718
1260 if(l_reporting_year = '2006') then
1261 l_VersionSchema := '2006.1.2';
1262 elsif(l_reporting_year = '2007') then
1263 l_VersionSchema := '2007.1.1';
1264 else
1265 l_VersionSchema := trim(l_reporting_year)||'.1';
1266 end if;
1267 -- End
1268
1269 l_final_xml_string :=
1270 '<Transmission VersionSchema="'||l_VersionSchema||'" ' ||
1271 'pxmlns="http://www.mrq.gouv.qc.ca/T5">' || EOL ||
1272 '<P>' || EOL ||
1273 tab_transmitter(lAnnee) ||
1274 tab_transmitter(lTypeEnvoi) ||
1275 tab_transmitter(lProvenance) || '<Preparateur>' || EOL ||
1276 tab_transmitter(lNo) ||
1277 tab_transmitter(lType) ||
1278 tab_transmitter(lNom1) ||
1279 tab_transmitter(lNom2) || '<Adresse>' || EOL ||
1280 tab_transmitter(lLigne1) ||
1281 tab_transmitter(lLigne2) ||
1282 tab_transmitter(lVille) ||
1283 tab_transmitter(lProvince) ||
1284 tab_transmitter(lCodePostal) || '</Adresse>' || EOL ||
1285 '</Preparateur>' || EOL ||
1286 l_tech_accnt_info || EOL ||
1287 tab_transmitter(lNoConcepteur) ||
1288 '</P>' || EOL;
1289
1290 --hr_utility.trace('l_final_xml_string = ' || l_final_xml_string);
1291
1292 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1293 END;
1294 END xml_transmitter_record;
1295
1296
1297 /*
1298 This function is used to call from SSHR, to generate
1299 XML for online RL2
1300 */
1301 FUNCTION get_final_xml(p_arch_action_id in pay_assignment_actions.assignment_action_id%TYPE,
1302 p_taxation_year in varchar2,
1303 p_rep_type in varchar2)
1304 RETURN BLOB IS
1305
1306 l_arch_pay_actid pay_assignment_actions.payroll_action_id%TYPE;
1307 l_asg_id per_assignments_f.assignment_id%TYPE;
1308 lv_negative_bal_flag VARCHAR2(5);
1309 l_is_temp_final_xml VARCHAR2(2);
1310 p_xml_blob BLOB;
1311 l_final_xml BLOB;
1312 l_temp_blob BLOB;
1313 l_header_xml_string VARCHAR2(32000);
1314 l_trailer_xml VARCHAR2(32000);
1315 l_err_msg hr_lookups.meaning%TYPE;
1316 EOL varchar2(10);
1317
1318 CURSOR c_payroll_asgid(p_arch_asact_id number) is
1319 select paa.payroll_action_id, paa.assignment_id
1320 from pay_assignment_actions paa
1321 where paa.assignment_action_id = p_arch_asact_id;
1322
1323 /* To get error message */
1324 CURSOR cur_get_meaning(p_lookup_code VARCHAR2) IS
1325 select meaning
1326 from hr_lookups
1327 where
1328 lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
1329 lookup_code = p_lookup_code;
1330
1331 CURSOR c_employee_infor (p_asg_action_id IN number) IS
1332 SELECT nvl(emp.negative_balance_flag, 'N')
1333 FROM pay_ca_eoy_rl2_employee_info_v emp,
1334 pay_ca_eoy_rl2_trans_info_v tran
1335 WHERE emp.assignment_action_id = p_asg_action_id
1336 AND emp.payroll_action_id = tran.payroll_action_id;
1337
1338 CURSOR c_employee_amend_infor (p_asg_action_id IN number) IS
1339 SELECT nvl(emp.negative_balance_flag, 'N')
1340 FROM pay_ca_eoy_rl2_employee_info_v emp,
1341 pay_ca_eoy_rl2_emp_info2_v emp2,
1342 pay_ca_eoy_rl2_trans_info_v tran
1343 WHERE emp.assignment_action_id = p_asg_action_id
1344 AND emp.assignment_action_id = emp2.assignment_action_id
1345 AND emp.payroll_action_id = emp2.payroll_action_id
1346 AND emp.assignment_id = emp2.assignment_id
1347 AND emp.person_id = emp2.person_id
1348 AND emp.business_group_id = emp2.business_group_id
1349 AND emp.payroll_action_id = tran.payroll_action_id;
1350
1351 BEGIN
1352 --hr_utility.trace_on(null,'Raaji');
1353 hr_utility.trace('In get_final_xml 10');
1354 hr_utility.trace('p_arch_action_id '||p_arch_action_id);
1355 hr_utility.trace('p_taxation_year '||p_taxation_year);
1356 hr_utility.trace('p_rep_type '||p_rep_type);
1357
1358 EOL := fnd_global.local_chr(13) || fnd_global.local_chr(10);
1359 hr_utility.trace('In get_final_xml 20');
1360
1361 open c_payroll_asgid(p_arch_action_id);
1362 fetch c_payroll_asgid into l_arch_pay_actid,l_asg_id;
1363 close c_payroll_asgid;
1364
1365 if p_rep_type <> 'PAYCARL2AMPDF' THEN
1366 OPEN c_employee_infor(p_arch_action_id);
1367 FETCH c_employee_infor INTO lv_negative_bal_flag;
1368 CLOSE c_employee_infor;
1369 else
1370 OPEN c_employee_amend_infor(p_arch_action_id);
1371 FETCH c_employee_amend_infor INTO lv_negative_bal_flag;
1372 CLOSE c_employee_amend_infor;
1373 end if;
1374
1375 if (lv_negative_bal_flag is NULL or lv_negative_bal_flag = 'N') then
1376 l_header_xml_string := '<RL2PAPER>'||EOL;
1377 fetch_rl2_xml(p_arch_action_id,
1378 l_asg_id,
1379 p_rep_type,
1380 p_taxation_year,
1381 'Y',
1382 'get_final_xml',
1383 p_xml_blob);
1384
1385 hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
1386 hr_utility.trace('1. final 1. XML l_final_xml '||
1387 dbms_lob.substr(l_final_xml,dbms_lob.getlength(l_final_xml),1));
1388 hr_utility.trace('In get_final_xml 30');
1389
1390 l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1391 hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
1392
1393 IF l_is_temp_final_xml = 1 THEN
1394 DBMS_LOB.FREETEMPORARY(l_final_xml);
1395 END IF;
1396
1397 dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
1398 dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1399 l_final_xml := append_to_lob(l_header_xml_string);
1400 dbms_lob.append(l_final_xml,p_xml_blob);
1401 hr_utility.trace('In get_final_xml 40');
1402
1403 l_trailer_xml := '</RL2PAPER>'||EOL;
1404
1405 hr_utility.trace('In get_final_xml 50');
1406 dbms_lob.createtemporary(l_temp_blob,false,DBMS_LOB.CALL);
1407 dbms_lob.open(l_temp_blob,dbms_lob.lob_readwrite);
1408 l_temp_blob := append_to_lob(l_trailer_xml);
1409 dbms_lob.append(l_final_xml,l_temp_blob);
1410
1411 IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
1412 hr_utility.trace('Closing l_final_xml' );
1413 dbms_lob.close(l_final_xml);
1414 END IF;
1415 IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1416 hr_utility.trace('Closing p_xml_blob' );
1417 dbms_lob.close(p_xml_blob);
1418 END IF;
1419 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1420 hr_utility.trace('Closing l_temp_blob' );
1421 dbms_lob.close(l_temp_blob);
1422 END IF;
1423 hr_utility.trace('In get_final_xml 60');
1424 else
1425 hr_utility.trace('Negative employee');
1426 OPEN cur_get_meaning('NEG');
1427 FETCH cur_get_meaning
1428 INTO l_err_msg;
1429 CLOSE cur_get_meaning;
1430
1431 l_err_msg := 'Error:'||l_err_msg;
1432 l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1433 hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
1434
1435 IF l_is_temp_final_xml = 1 THEN
1436 DBMS_LOB.FREETEMPORARY(l_final_xml);
1437 END IF;
1438
1439 dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
1440 dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1441 l_final_xml := append_to_lob(l_err_msg);
1442 hr_utility.trace('In get_final_xml 61');
1443 IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
1444 hr_utility.trace('Closing l_final_xml' );
1445 dbms_lob.close(l_final_xml);
1446 END IF;
1447 hr_utility.trace('In get_final_xml 62');
1448 end if;
1449 hr_utility.trace('In get_final_xml 65');
1450 --hr_utility.trace_off();
1451 return l_final_xml;
1452
1453 exception
1454 when others then
1455 hr_utility.trace('In get_final_xml 70');
1456
1457 IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
1458 hr_utility.trace('Raising exception and Closing l_final_xml' );
1459 dbms_lob.close(l_final_xml);
1460 END IF;
1461 IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1462 hr_utility.trace('Raising exception and Closing p_xml_string' );
1463 dbms_lob.close(p_xml_blob);
1464 END IF;
1465 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1466 hr_utility.trace('Closing l_temp_blob' );
1467 dbms_lob.close(l_temp_blob);
1468 END IF;
1469 hr_utility.trace('sqleerm ' || SQLERRM);
1470 raise;
1471 END get_final_xml;
1472
1473
1474 PROCEDURE xml_employee_record IS
1475
1476 l_mag_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
1477 l_arch_action_id pay_assignment_actions.assignment_action_id%TYPE;
1478 l_asg_id per_assignments_f.assignment_id%TYPE;
1479 l_rep_type pay_report_format_mappings_f.report_type%type;
1480 l_date_earned DATE;
1481 l_taxation_year VARCHAR2(4);
1482 l_print_instruction VARCHAR2(1);
1483 l_rl2pap_asg_actid pay_assignment_actions.assignment_action_id%TYPE;
1484 l_rl2pap_pay_actid pay_assignment_actions.payroll_action_id%TYPE;
1485 l_transfer_pay_actid NUMBER;
1486 l_business_group_id NUMBER;
1487 l_legislative_parameters pay_payroll_actions.legislative_parameters%type;
1488 l_xml_blob BLOB;
1489
1490 ----------------------------------------------------------------
1491 CURSOR c_get_report_type(p_payactid NUMBER) IS
1492 SELECT
1493 ppa.report_type,
1494 ppa.business_group_id,
1495 ppa.legislative_parameters
1496 FROM
1497 pay_payroll_actions ppa
1498 WHERE
1499 ppa.payroll_action_id = p_payactid;
1500 ----------------------------------------------------------------
1501 CURSOR cur_parameters(p_mag_asg_action_id NUMBER) IS
1502 SELECT
1503 pai.locked_action_id, -- Archiver asg_action_id
1504 paa.assignment_id,
1505 pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
1506 -- date_earned
1507 FROM
1508 pay_action_interlocks pai,
1509 pay_assignment_actions paa,
1510 pay_payroll_actions ppa,
1511 per_all_people_f ppf,
1512 per_all_assignments_f paf,
1513 pay_action_information pact
1514 WHERE paa.assignment_action_id = pai.locking_action_id
1515 AND paa.assignment_action_id = p_mag_asg_action_id
1516 AND paf.assignment_id = paa.assignment_id
1517 AND ppf.person_id = paf.person_id
1518 AND ppa.payroll_action_id = paa.payroll_action_id
1519 AND pai.locked_action_id = pact.action_context_id
1520 AND pact.action_information_category = 'CAEOY RL2 EMPLOYEE INFO'
1521 AND pact.assignment_id = paa.assignment_id
1522 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
1523 between paf.effective_start_date and paf.effective_end_date
1524 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
1525 between ppf.effective_start_date and ppf.effective_end_date
1526 ORDER BY
1527 ppf.last_name,ppf.first_name,ppf.middle_names;
1528 -----------------------------------------------------------------------
1529
1530 CURSOR c_get_payroll_asg_actid(p_payactid NUMBER) IS
1531 SELECT
1532 to_number(substr(paa.serial_number,3,14)) asgactid,
1533 --to_number(substr(paa.serial_number,17,14)) payactid,
1534 paa_arch.payroll_action_id payactid, -- bug 10324391
1535 paa.assignment_id asgid
1536 FROM
1537 pay_assignment_actions paa,
1538 pay_assignment_actions paa_arch -- archiver payroll action_id
1539 WHERE paa.assignment_action_id = p_payactid
1540 AND paa_arch.assignment_action_id = to_number(substr(paa.serial_number,3,14));
1541
1542 ----------------------------------------------------------------------------------
1543
1544 BEGIN
1545 l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value
1546 ('TRANSFER_ACT_ID'));
1547 l_transfer_pay_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1548
1549 open c_get_report_type(l_transfer_pay_actid);
1550 fetch c_get_report_type
1551 into l_rep_type,
1552 l_business_group_id,
1553 l_legislative_parameters;
1554 close c_get_report_type;
1555
1556 if l_rep_type = 'RL2PAPERPDF' or l_rep_type='PAYCARL2AMPDF' or l_rep_type = 'PAYCARL2CLPDF'
1557 then
1558 open c_get_payroll_asg_actid(l_mag_asg_action_id);
1559 fetch c_get_payroll_asg_actid
1560 into l_rl2pap_asg_actid,
1561 l_rl2pap_pay_actid,
1562 l_asg_id;
1563 close c_get_payroll_asg_actid;
1564
1565 hr_utility.trace('The value of Payroll action id is '||l_rl2pap_pay_actid);
1566 hr_utility.trace('The value of Assignment action id is '||l_rl2pap_asg_actid);
1567 hr_utility.trace('The value of Assignment id is '||l_asg_id);
1568 end if;
1569
1570 IF l_rep_type NOT IN ('RL2PAPERPDF','PAYCARL2AMPDF','PAYCARL2CLPDF') THEN
1571 OPEN cur_parameters(l_mag_asg_action_id);
1572 FETCH cur_parameters
1573 INTO
1574 l_arch_action_id,
1575 l_asg_id,
1576 l_date_earned;
1577 CLOSE cur_parameters;
1578 ELSE
1579 l_arch_action_id := l_rl2pap_asg_actid;
1580 END IF;
1581
1582 hr_utility.trace('XML Employee: l_arch_action_id = '
1583 || to_char(l_arch_action_id));
1584 hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1585 hr_utility.trace('XML Employee: l_date_earned = '
1586 || to_char(l_date_earned));
1587
1588 if l_rep_type = 'RL2PAPERPDF' or l_rep_type='PAYCARL2AMPDF' or l_rep_type='PAYCARL2CLPDF'
1589 then
1590 l_taxation_year
1591 := pay_ca_rl2_mag.get_parameter('TAX_YEAR',
1592 l_legislative_parameters);
1593 l_print_instruction := pay_magtape_generic.get_parameter_value('print_instruction');
1594 else
1595 l_taxation_year := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
1596 end if;
1597
1598 fetch_rl2_xml(l_arch_action_id,
1599 l_asg_id,
1600 l_rep_type,
1601 l_taxation_year,
1602 l_print_instruction,
1603 'xml_employee_record',
1604 l_xml_blob);
1605
1606 END xml_employee_record;
1607
1608 PROCEDURE fetch_rl2_xml(p_arch_action_id in pay_assignment_actions.assignment_action_id%TYPE,
1609 p_asg_id in per_assignments_f.assignment_id%TYPE,
1610 p_rep_type in varchar2,
1611 p_taxation_year in varchar2,
1612 p_print_instruction in varchar2,
1613 called_from in varchar2,
1614 p_xml_blob out NOCOPY BLOB) IS
1615 BEGIN
1616
1617 DECLARE
1618
1619 l_final_xml_string VARCHAR2(32000);
1620 l_final_xml_string1 VARCHAR2(32000);
1621 l_final_xml_string2 VARCHAR2(32000);
1622 /* 14701748 */
1623 l_final_xml_string_fur_inf VARCHAR2(32000);
1624 counter number;
1625 count1 number;
1626 counter1 number;
1627 further_info_count_perpage number;
1628 slip_count number;
1629 l_rl2_first_slip_number varchar2(240);
1630 l_first_seq_num varchar2(240);
1631 l_rl2_first_form_number varchar2(240);
1632 lNoReleve_3 varchar2(80);
1633 lNoReleve_4 varchar2(80);
1634 lNoReleve_5 varchar2(80);
1635 l_sequence_no_3 varchar2(80);
1636 l_sequence_no_4 varchar2(80);
1637 l_sequence_no_5 varchar2(80);
1638 lNoOrigReleve_3 varchar2(80);
1639 lNoOrigReleve_4 varchar2(80);
1640 lNoOrigReleve_5 varchar2(80);
1641 rl2_final_slip_number varchar2(20);
1642 rl2_final_seq_number varchar2(20);
1643 rl2_final_form_number varchar2(20);
1644 /* 14701748 */
1645
1646 CURSOR c_get_payroll_asg_actid IS
1647 select ppa.payroll_action_id,
1648 ppa.business_group_id
1649 from pay_payroll_actions ppa,
1650 pay_assignment_actions paa
1651 where paa.assignment_action_id = p_arch_action_id
1652 and paa.payroll_action_id = ppa.payroll_action_id;
1653
1654
1655 CURSOR c_get_employer_info(p_pact_id NUMBER,
1656 p_business_group_id NUMBER) IS
1657 SELECT nvl(employer_name,' '),
1658 nvl(quebec_business_number,'0000000000 0000'),
1659 nvl(reporting_year,'0000'),
1660 nvl(employer_add_line1,' '),
1661 nvl(employer_add_line2,' '),
1662 nvl(employer_add_line3,' '),
1663 nvl(employer_city,' '),
1664 nvl(employer_province,' '),
1665 nvl(employer_country,' '),
1666 nvl(employer_postal_code,' ')
1667 FROM
1668 pay_ca_eoy_rl2_trans_info_v
1669 WHERE
1670 business_group_id = p_business_group_id
1671 AND payroll_action_id = p_pact_id;
1672
1673
1674
1675 CURSOR c_employee_infor (p_asg_action_id IN number)
1676 IS
1677 SELECT nvl(tran.quebec_business_number,'0000000000 0000'),
1678 nvl(tran.reporting_year,'0000'),
1679 nvl(emp.rl2_slip_number,'000000000'),
1680 nvl(emp.employee_sin,'000000000'),
1681 nvl(emp.employee_last_name,' '),
1682 nvl(emp.employee_first_name,' '),
1683 nvl(emp.employee_middle_initial,' '),
1684 nvl(emp.employee_address_line1,' '),
1685 nvl(emp.employee_address_line2,' '),
1686 nvl(emp.employee_address_line3,' '),
1687 nvl(emp.employee_city,' '),
1688 nvl(emp.employee_province,' '),
1689 nvl(emp.employee_postal_code,' '),
1690 nvl(emp.employee_number,' '),
1691 emp.rl2_box_a,
1692 emp.rl2_box_b,
1693 emp.rl2_box_c,
1694 emp.rl2_box_d,
1695 emp.rl2_box_e,
1696 emp.rl2_box_f,
1697 emp.rl2_box_g,
1698 emp.rl2_box_h,
1699 emp.rl2_box_i,
1700 emp.rl2_box_j,
1701 emp.rl2_box_k,
1702 emp.rl2_box_l,
1703 emp.rl2_box_m,
1704 emp.rl2_box_n,
1705 emp.rl2_box_o,
1706 decode(substr(emp.rl2_source_of_income,1,5),'OTHER','AUTRE', emp.rl2_source_of_income),
1707 nvl(emp.negative_balance_flag, 'N'),
1708 emp.person_id
1709 FROM pay_ca_eoy_rl2_employee_info_v emp,
1710 pay_ca_eoy_rl2_trans_info_v tran
1711 WHERE emp.assignment_action_id = p_asg_action_id
1712 AND emp.payroll_action_id = tran.payroll_action_id;
1713
1714 /*Created new cursor for RL2 Amendment PDF Purpose Bug#5046010*/
1715 CURSOR c_employee_amend_infor (p_asg_action_id IN number)
1716 IS
1717 SELECT nvl(tran.quebec_business_number,'0000000000 0000'),
1718 nvl(tran.reporting_year,'0000'),
1719 nvl(emp.rl2_slip_number,'000000000'),
1720 nvl(emp2.rl2_form_number,'000000000'),
1721 nvl(emp.employee_sin,'000000000'),
1722 nvl(emp.employee_last_name,' '),
1723 nvl(emp.employee_first_name,' '),
1724 nvl(emp.employee_middle_initial,' '),
1725 nvl(emp.employee_address_line1,' '),
1726 nvl(emp.employee_address_line2,' '),
1727 nvl(emp.employee_address_line3,' '),
1728 nvl(emp.employee_city,' '),
1729 nvl(emp.employee_province,' '),
1730 nvl(emp.employee_postal_code,' '),
1731 nvl(emp.employee_number,' '),
1732 emp.rl2_box_a,
1733 emp.rl2_box_b,
1734 emp.rl2_box_c,
1735 emp.rl2_box_d,
1736 emp.rl2_box_e,
1737 emp.rl2_box_f,
1738 emp.rl2_box_g,
1739 emp.rl2_box_h,
1740 emp.rl2_box_i,
1741 emp.rl2_box_j,
1742 emp.rl2_box_k,
1743 emp.rl2_box_l,
1744 emp.rl2_box_m,
1745 emp.rl2_box_n,
1746 emp.rl2_box_o,
1747 decode(substr(emp.rl2_source_of_income,1,5),'OTHER','AUTRE', emp.rl2_source_of_income),
1748 nvl(emp.negative_balance_flag, 'N'),
1749 emp.person_id
1750 FROM pay_ca_eoy_rl2_employee_info_v emp,
1751 pay_ca_eoy_rl2_emp_info2_v emp2,
1752 pay_ca_eoy_rl2_trans_info_v tran
1753 WHERE emp.assignment_action_id = p_asg_action_id
1754 AND emp.assignment_action_id = emp2.assignment_action_id
1755 AND emp.payroll_action_id = emp2.payroll_action_id
1756 AND emp.assignment_id = emp2.assignment_id
1757 AND emp.person_id = emp2.person_id
1758 AND emp.business_group_id = emp2.business_group_id
1759 AND emp.payroll_action_id = tran.payroll_action_id;
1760 /*End Bug#5046010*/
1761
1762 /* Added for bug 8888411 */
1763 CURSOR c_rl2_src_income(p_rl2_source_of_income varchar2, p_taxation_year varchar2) is
1764 select
1765 'X'
1766 from
1767 hr_lookups hl
1768 where
1769 hl.lookup_type = 'PAY_CA_RL2_SOURCE_OF_INCOME'
1770 and trim(hl.lookup_code) = trim(p_rl2_source_of_income)
1771 and to_date('31/12/'||p_taxation_year,'dd/mm/yyyy')<= nvl(hl.end_date_active,to_date('31/12/4712','dd/mm/yyyy'))
1772 and hl.enabled_flag='Y';
1773
1774 l_quebec_business_number varchar2(240);
1775 l_reporting_year varchar2(240);
1776 l_rl2_slip_number varchar2(240);
1777 l_rl2_form_number varchar2(240); /*Added for Bug#5046010*/
1778 l_employee_sin varchar2(240);
1779 l_employee_sin1 varchar2(240);
1780 l_employee_sin2 varchar2(240);
1781 l_employee_sin3 varchar2(240);
1782 l_employee_last_name varchar2(240);
1783 l_employee_first_name varchar2(240);
1784 l_employee_middle_initial varchar2(240);
1785 l_employee_address_line1 varchar2(240);
1786 l_employee_address_line2 varchar2(240);
1787 l_employee_address_line3 varchar2(240);
1788 l_employee_city varchar2(240);
1789 l_employee_province varchar2(240);
1790 l_employee_postal_code varchar2(240);
1791 l_employee_number varchar2(240);
1792 l_per_id varchar2(50);
1793 l_rl2_box_a varchar2(240);
1794 l_rl2_box_b varchar2(240);
1795 l_rl2_box_c varchar2(240);
1796 l_rl2_box_d varchar2(240);
1797 l_rl2_box_e varchar2(240);
1798 l_rl2_box_f varchar2(240);
1799 l_rl2_box_g varchar2(240);
1800 l_rl2_box_h varchar2(240);
1801 l_rl2_box_i varchar2(240);
1802 l_rl2_box_j varchar2(240);
1803 l_rl2_box_k varchar2(240);
1804 l_rl2_box_l varchar2(240);
1805 l_rl2_box_m varchar2(240);
1806 l_rl2_box_n varchar2(240);
1807 l_rl2_box_o varchar2(240);
1808 l_rl2_source_of_income varchar2(240);
1809 l_negative_balance_flag varchar2(240);
1810
1811 l_temp_blob BLOB;
1812 l_is_temp_final_xml VARCHAR2(2);
1813
1814 TYPE employee_info IS TABLE OF VARCHAR2(1500) INDEX BY BINARY_INTEGER;
1815
1816 tab_employee employee_info;
1817
1818 lAnnee NUMBER;
1819 lNoReleve NUMBER;
1820 lNoOrigReleve NUMBER; /*Added for Bug#5046010*/
1821 lNAS NUMBER;
1822 lNAS1 NUMBER;
1823 lNAS2 NUMBER;
1824 lNAS3 NUMBER;
1825 lNo NUMBER;
1826 lNomFamille NUMBER;
1827 lPrenom NUMBER;
1828 lInitiale NUMBER;
1829 lLigne1 NUMBER;
1830 lLigne2 NUMBER;
1831 lVille NUMBER;
1832 lProvince NUMBER;
1833 lCodePostal NUMBER;
1834 lA_PrestRPA_RPNA NUMBER;
1835 lA_PrestRPA_RPNA1 NUMBER; -- bug 12973513
1836 lB_PrestREER_FERR_RPDB NUMBER;
1837 lC_AutrePaiement NUMBER;
1838 lD_RembPrimeConjoint NUMBER;
1839 lE_PrestDeces NUMBER;
1840 lF_RembCotisInutilise NUMBER;
1841 lG_RevocationREER_FERR NUMBER;
1842 lH_AutreRevenu NUMBER;
1843 lI_DroitDeduction NUMBER;
1844 lJ_ImpotQueRetenuSource NUMBER;
1845 lK_RevenuApresDeces NUMBER;
1846 lL_RetraitREEP NUMBER;
1847 lM_LibereImpot NUMBER;
1848 lN_NASConjoint NUMBER;
1849 lN_NASConjoint1 NUMBER;
1850 lN_NASConjoint2 NUMBER;
1851 lO_RetraitRAP NUMBER;
1852 lProvenance1 VARCHAR2(10);
1853 lProvenance VARCHAR2(10); -- bug 12973513
1854 lBoxA_UnregisterdPlan NUMBER;
1855 lDesg_BenefitExcAmt NUMBER;
1856 lBoxB_DesgBenefitTrnsAmt NUMBER;
1857 lBoxExcessAmt NUMBER;
1858 lAmount_Transferred NUMBER;
1859 lBoxC_SinglePayAccured NUMBER;
1860 lBoxC_SinglePayAccUnreg NUMBER;
1861 lBoxC_ExcessAmtSinPayTrans NUMBER;
1862 lCode_dereleve NUMBER;
1863 /* 14701748 */
1864 CaseRensCompl NUMBER;
1865 /* 13460512 */
1866 NomFamille_Ligne12_Ville NUMBER;
1867
1868 l_person_id per_people_f.person_id%TYPE;
1869 l_address_line1 per_addresses.address_line1%TYPE;
1870 l_address_line2 per_addresses.address_line2%TYPE;
1871 l_address_line3 per_addresses.address_line3%TYPE;
1872 l_city per_addresses.town_or_city%TYPE;
1873 l_postal_code per_addresses.postal_code%TYPE;
1874 l_country VARCHAR2(60);
1875 l_emp_province per_addresses.region_1%TYPE;
1876 EOL VARCHAR2(5);
1877 l_name VARCHAR2(60);
1878 l_return VARCHAR2(30);
1879 l_status VARCHAR2(10);
1880 l_addr_begin_tag VARCHAR2(10);
1881 l_addr_end_tag VARCHAR2(10);
1882 l_formatted_box VARCHAR2(20);
1883 l_boxO VARCHAR2(10);
1884 l_combined_addr VARCHAR2(500);
1885
1886 l_count NUMBER;
1887 lBoxR_14 NUMBER;
1888 lErrorDetails NUMBER;
1889
1890 CURSOR cur_get_meaning(p_lookup_code VARCHAR2) IS
1891 SELECT
1892 meaning
1893 FROM
1894 hr_lookups
1895 WHERE
1896 lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
1897 lookup_code = p_lookup_code;
1898
1899 l_meaning hr_lookups.meaning%TYPE;
1900 l_msg_code VARCHAR2(30);
1901 l_all_box_0 BOOLEAN;
1902
1903 /* Cursor for fetching the Footnote Codes */
1904 CURSOR c_footnote_codes ( p_assg_actid number) is
1905 SELECT hl.meaning code, fnd_number.canonical_to_number(FT.FOOTNOTE_AMOUNT) value
1906 FROM PAY_CA_EOY_RL2_FOOTNOTE_INFO_V FT,
1907 HR_LOOKUPS HL
1908 WHERE FT.ASSIGNMENT_ACTION_ID = p_assg_actid
1909 AND ((HL.LOOKUP_TYPE = 'PAY_CA_RL2_FOOTNOTES'
1910 AND HL.lookup_code = FT.FOOTNOTE_CODE)
1911 OR
1912 (HL.LOOKUP_TYPE = 'PAY_CA_RL2_AUTOMATIC_FOOTNOTES'
1913 AND HL.LOOKUP_CODE = FT.FOOTNOTE_CODE));
1914
1915 /* Cursor for fetching authorisation code */
1916 CURSOR c_get_auth_code(p_reporting_year varchar2) IS
1917 SELECT meaning
1918 FROM hr_lookups
1919 WHERE trim(lookup_code) = p_reporting_year
1920 AND lookup_type = 'PAY_CA_RL2_PDF_AUTH'
1921 AND enabled_flag='Y';
1922
1923
1924 /* 14701748 */
1925 CURSOR pre_printed_form_no (arch_action_id IN number)
1926 IS
1927 SELECT ACTION_INFORMATION1
1928 FROM pay_action_information
1929 WHERE action_context_id = arch_action_id
1930 and ACTION_INFORMATION_CATEGORY = 'CAEOY RL2 EMPLOYEE INFO2';
1931
1932 /* 14701748 */
1933
1934 l_footnote_code VARCHAR2(100);
1935 l_footnote_amount NUMBER;
1936 l_format_mask VARCHAR2(30);
1937 l_rl2pap_pay_actid pay_assignment_actions.payroll_action_id%TYPE;
1938
1939 /* Added the following new variables for XML Paper report */
1940 tab_employee1 employee_info;
1941 tab_employee2 employee_info;
1942 l_business_group_id NUMBER;
1943
1944 TYPE employer_inf IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
1945 tab_emplyr employer_inf;
1946 tab_emplyr1 employer_inf;
1947 tab_emplyr2 employer_inf;
1948
1949 l_page_break VARCHAR2(100);
1950 l_full_empname VARCHAR2(100);
1951 l_full_empaddr VARCHAR2(100);
1952 l_empr_name VARCHAR2(240);
1953 l_empr_quebec_no VARCHAR2(240);
1954 l_empr_report_yr VARCHAR2(240);
1955 l_empr_addr1 VARCHAR2(240);
1956 l_empr_addr2 VARCHAR2(240);
1957 l_empr_addr3 VARCHAR2(240);
1958 l_empr_city VARCHAR2(240);
1959 l_empr_prov VARCHAR2(240);
1960 l_empr_postcode VARCHAR2(240);
1961 l_empr_country VARCHAR2(240);
1962 l_empr_fulladdr VARCHAR2(240);
1963 l_counter NUMBER;
1964 l_negative_box VARCHAR2(1);
1965 l_footnote_count NUMBER;
1966 l_footcode VARCHAR2(100);
1967 l_footamt NUMBER;
1968 l_footnotecode NUMBER;
1969 l_footnoteamt NUMBER;
1970
1971
1972 l_authorisation_no NUMBER;
1973 l_authorisation_tag NUMBER;
1974 l_sequence_no NUMBER;
1975 --l_seq_num NUMBER;
1976 l_seq_num VARCHAR2(240); -- bug 14701748, -- bug 10420152
1977 l_authorization_code VARCHAR2(100);
1978
1979 /*Added for bug 13545861*/
1980 l_code varchar2(10);
1981 l_amount varchar2(50);
1982 l_i number;
1983 l_j number;
1984 l_k number;
1985 l_m number;
1986 l_data_page_count number;
1987 l_blank_page_flg varchar2(5);
1988 l_formatted_code varchar2(20);
1989
1990 tab_employee3 employee_info;
1991 tab_employee4 employee_info;
1992 tab_employee5 employee_info;
1993
1994 l_final_xml_string3 VARCHAR2(32000);
1995 l_final_xml_string4 VARCHAR2(32000);
1996 l_final_xml_string5 VARCHAR2(32000);
1997 l_final_xml_string6 VARCHAR2(32000);
1998 l_other_details VARCHAR2(32000);
1999 /* 14701748 */
2000 further_info_details VARCHAR2(32000);
2001
2002 tab_emplyr3 employer_inf;
2003 tab_emplyr4 employer_inf;
2004 tab_emplyr5 employer_inf;
2005
2006 TYPE FURTHER_CODES IS VARRAY(80) OF VARCHAR2(10);
2007 TYPE FURTHER_AMOUNTS IS VARRAY(80) OF VARCHAR2(50);
2008 l_further_code FURTHER_CODES:=FURTHER_CODES('','','','','','','','','','','','','',
2009 '','','','','','','');
2010 l_further_amount FURTHER_AMOUNTS:=FURTHER_AMOUNTS('','','','','','','','','','','','','',
2011 '','','','','','','');
2012 num_further_info number:=0; --ends bug 13545861
2013
2014 BEGIN
2015 --hr_utility.trace_on(null,'SATIRL2XML');
2016 hr_utility.trace('XML Employee');
2017 l_status := 'Success';
2018 l_all_box_0 := TRUE;
2019 l_count := 0;
2020 l_format_mask := '99999999999999990.99';
2021 l_counter := 0;
2022 l_negative_box := 'N';
2023 l_footnote_count := 0;
2024 SELECT
2025 fnd_global.local_chr(13) || fnd_global.local_chr(10)
2026 INTO EOL
2027 FROM dual;
2028
2029 lAnnee := 1;
2030 lNoReleve := 2;
2031 lNAS := 3;
2032 lNo := 4;
2033 lNomFamille := 5;
2034 lPrenom := 6;
2035 lInitiale := 7;
2036 lLigne1 := 8;
2037 lLigne2 := 9;
2038 lVille := 10;
2039 lProvince := 11;
2040 lCodePostal := 12;
2041 lA_PrestRPA_RPNA := 13;
2042 lB_PrestREER_FERR_RPDB := 14;
2043 lC_AutrePaiement := 15;
2044 lD_RembPrimeConjoint := 16;
2045 lE_PrestDeces := 17;
2046 lF_RembCotisInutilise := 18;
2047 lG_RevocationREER_FERR := 19;
2048 lH_AutreRevenu := 20;
2049 lI_DroitDeduction := 21;
2050 lJ_ImpotQueRetenuSource := 22;
2051 lK_RevenuApresDeces := 23;
2052 lL_RetraitREEP := 24;
2053 lM_LibereImpot := 25;
2054 lN_NASConjoint := 26;
2055 lO_RetraitRAP := 27;
2056 lProvenance1 := 28;
2057 lErrorDetails := 29;
2058 lBoxA_UnregisterdPlan := 30;
2059 lDesg_BenefitExcAmt := 31;
2060 lBoxB_DesgBenefitTrnsAmt := 32;
2061 lBoxExcessAmt := 33;
2062 lAmount_Transferred := 34;
2063 lBoxC_SinglePayAccured := 35;
2064 lBoxC_SinglePayAccUnreg := 36;
2065 lBoxC_ExcessAmtSinPayTrans := 37;
2066 l_footnotecode := 38;
2067 l_footnoteamt := 39;
2068 lNAS1 := 40;
2069 lNAS2 := 41;
2070 lNAS3 := 42;
2071 lN_NASConjoint1 := 43;
2072 lN_NASConjoint2 := 44;
2073 lCode_dereleve := 45;
2074 l_authorisation_no := 46;
2075 l_authorisation_tag := 47;
2076 l_sequence_no := 48;
2077 lNoOrigReleve := 49; /*Added for Bug#5046010*/
2078 lA_PrestRPA_RPNA1 := 50; -- bug 12973513
2079 lProvenance := 51; -- bug 12973513
2080 /* 14701748 */
2081 CaseRensCompl := 52;
2082 /* 13460512 */
2083 NomFamille_Ligne12_Ville := 53;
2084
2085
2086 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type = 'PAYCARL2CLPDF'
2087 then
2088 OPEN c_get_payroll_asg_actid;
2089 FETCH c_get_payroll_asg_actid
2090 INTO l_rl2pap_pay_actid,
2091 l_business_group_id;
2092 CLOSE c_get_payroll_asg_actid;
2093
2094 hr_utility.trace('The value of Payroll action id is '||l_rl2pap_pay_actid);
2095 hr_utility.trace('The value of l_business_group_id is '||l_business_group_id);
2096
2097 OPEN c_get_employer_info(l_rl2pap_pay_actid,l_business_group_id);
2098 FETCH c_get_employer_info
2099 INTO l_empr_name ,
2100 l_empr_quebec_no ,
2101 l_empr_report_yr ,
2102 l_empr_addr1 ,
2103 l_empr_addr2 ,
2104 l_empr_addr3,
2105 l_empr_city ,
2106 l_empr_prov ,
2107 l_empr_country,
2108 l_empr_postcode;
2109 CLOSE c_get_employer_info;
2110
2111 l_counter := l_counter + 1;
2112 IF l_empr_name IS NOT NULL AND
2113 l_empr_name <> ' ' THEN
2114 tab_emplyr(l_counter) :='<Nom>'|| convert_special_char(substr(l_empr_name,1,30))||'</Nom>'||EOL;
2115 tab_emplyr1(l_counter) :='<Nom1>'|| convert_special_char(substr(l_empr_name,1,30))||'</Nom1>'||EOL;
2116 tab_emplyr2(l_counter) :='<Nom2>'|| convert_special_char(substr(l_empr_name,1,30))||'</Nom2>'||EOL;
2117 tab_emplyr3(l_counter) :='<Nom3>'|| convert_special_char(substr(l_empr_name,1,30))||'</Nom3>'||EOL;
2118 tab_emplyr4(l_counter) :='<Nom4>'|| convert_special_char(substr(l_empr_name,1,30))||'</Nom4>'||EOL;
2119 tab_emplyr5(l_counter) :='<Nom5>'|| convert_special_char(substr(l_empr_name,1,30))||'</Nom5>'||EOL;
2120 ELSE
2121 tab_emplyr(l_counter) := NULL;
2122 tab_emplyr1(l_counter) := NULL;
2123 tab_emplyr2(l_counter) := NULL;
2124 tab_emplyr3(l_counter) := NULL;
2125 tab_emplyr4(l_counter) := NULL;
2126 tab_emplyr5(l_counter) := NULL;
2127 END IF;
2128
2129 l_counter := l_counter + 1;
2130 IF l_empr_quebec_no IS NOT NULL AND
2131 l_empr_quebec_no <> '0000000000 0000' THEN
2132 tab_emplyr(l_counter) :='<NoDossier>'|| convert_special_char(substr(l_empr_quebec_no,1,30))||'</NoDossier>'||EOL;
2133 tab_emplyr1(l_counter) :='<NoDossier1>'|| convert_special_char(substr(l_empr_quebec_no,1,30))||'</NoDossier1>'||
2134 EOL;
2135 tab_emplyr2(l_counter) :='<NoDossier2>'|| convert_special_char(substr(l_empr_quebec_no,1,30))||'</NoDossier2>'||
2136 EOL;
2137 tab_emplyr3(l_counter) :='<NoDossier3>'|| convert_special_char(substr(l_empr_quebec_no,1,30))||'</NoDossier3>'||EOL;
2138 tab_emplyr4(l_counter) :='<NoDossier4>'|| convert_special_char(substr(l_empr_quebec_no,1,30))||'</NoDossier4>'||
2139 EOL;
2140 tab_emplyr5(l_counter) :='<NoDossier5>'|| convert_special_char(substr(l_empr_quebec_no,1,30))||'</NoDossier5>'||
2141 EOL;
2142 ELSE
2143 tab_emplyr(l_counter) := NULL;
2144 tab_emplyr1(l_counter) := NULL;
2145 tab_emplyr2(l_counter) := NULL;
2146 tab_emplyr3(l_counter) := NULL;
2147 tab_emplyr4(l_counter) := NULL;
2148 tab_emplyr5(l_counter) := NULL;
2149 END IF;
2150
2151 l_counter := l_counter + 1;
2152 IF l_empr_report_yr IS NOT NULL AND
2153 l_empr_report_yr <> '0000' THEN
2154 tab_emplyr(l_counter) :='<AnneeEmplyr>'|| convert_special_char(substr(l_empr_report_yr,1,30))||'</AnneeEmplyr>'||EOL;
2155 tab_emplyr1(l_counter) :='<AnneeEmplyr1>'|| convert_special_char(substr(l_empr_report_yr,1,30))||'</AnneeEmplyr1>'||EOL;
2156 tab_emplyr2(l_counter) :='<AnneeEmplyr2>'|| convert_special_char(substr(l_empr_report_yr,1,30))||'</AnneeEmplyr2>'||EOL;
2157 tab_emplyr3(l_counter) :='<AnneeEmplyr3>'|| convert_special_char(substr(l_empr_report_yr,1,30))||'</AnneeEmplyr3>'||EOL;
2158 tab_emplyr4(l_counter) :='<AnneeEmplyr4>'|| convert_special_char(substr(l_empr_report_yr,1,30))||'</AnneeEmplyr4>'||EOL;
2159 tab_emplyr5(l_counter) :='<AnneeEmplyr5>'|| convert_special_char(substr(l_empr_report_yr,1,30))||'</AnneeEmplyr5>'||EOL;
2160
2161 ELSE
2162 tab_emplyr(l_counter) := NULL;
2163 tab_emplyr1(l_counter) := NULL;
2164 tab_emplyr2(l_counter) := NULL;
2165 tab_emplyr3(l_counter) := NULL;
2166 tab_emplyr4(l_counter) := NULL;
2167 tab_emplyr5(l_counter) := NULL;
2168 END IF;
2169
2170 l_counter := l_counter + 1;
2171 IF l_empr_addr1 IS NOT NULL AND
2172 l_empr_addr1 <> ' ' THEN
2173 tab_emplyr(l_counter) :='<Ligne1Emplyr>'|| convert_special_char(substr(l_empr_addr1,1,30))||'</Ligne1Emplyr>'||EOL;
2174 tab_emplyr1(l_counter) :='<Ligne1Emplyr1>'|| convert_special_char(substr(l_empr_addr1,1,30))||'</Ligne1Emplyr1>'||EOL;
2175 tab_emplyr2(l_counter) :='<Ligne1Emplyr2>'|| convert_special_char(substr(l_empr_addr1,1,30))||'</Ligne1Emplyr2>'||EOL;
2176 tab_emplyr3(l_counter) :='<Ligne1Emplyr3>'|| convert_special_char(substr(l_empr_addr1,1,30))||'</Ligne1Emplyr3>'||EOL;
2177 tab_emplyr4(l_counter) :='<Ligne1Emplyr4>'|| convert_special_char(substr(l_empr_addr1,1,30))||'</Ligne1Emplyr4>'||EOL;
2178 tab_emplyr5(l_counter) :='<Ligne1Emplyr5>'|| convert_special_char(substr(l_empr_addr1,1,30))||'</Ligne1Emplyr5>'||EOL;
2179 ELSE
2180 tab_emplyr(l_counter) := NULL;
2181 tab_emplyr1(l_counter) := NULL;
2182 tab_emplyr2(l_counter) := NULL;
2183 tab_emplyr3(l_counter) := NULL;
2184 tab_emplyr4(l_counter) := NULL;
2185 tab_emplyr5(l_counter) := NULL;
2186 END IF;
2187
2188 l_counter := l_counter + 1;
2189 IF (l_empr_addr2 IS NOT NULL AND
2190 l_empr_addr2 <> ' ') OR
2191 (l_empr_addr3 IS NOT NULL AND
2192 l_empr_addr3 <> ' ') THEN
2193 tab_emplyr(l_counter) :='<Ligne2Emplyr>'||
2194 convert_special_char(substr(ltrim(rtrim(l_empr_addr2))||' '||
2195 (ltrim(rtrim(l_empr_addr3))),1,30))||'</Ligne2Emplyr>'||EOL;
2196 tab_emplyr1(l_counter) :='<Ligne2Emplyr1>'|| convert_special_char(substr(ltrim(rtrim(l_empr_addr2))||' '||
2197 (ltrim(rtrim(l_empr_addr3))),1,30))||'</Ligne2Emplyr1>'||EOL;
2198 tab_emplyr2(l_counter) :='<Ligne2Emplyr2>'|| convert_special_char(substr(ltrim(rtrim(l_empr_addr2))||' '||
2199 (ltrim(rtrim(l_empr_addr3))),1,30))||'</Ligne2Emplyr2>'||EOL;
2200 tab_emplyr3(l_counter) :='<Ligne2Emplyr3>'||
2201 convert_special_char(substr(ltrim(rtrim(l_empr_addr2))||' '||
2202 (ltrim(rtrim(l_empr_addr3))),1,30))||'</Ligne2Emplyr3>'||EOL;
2203 tab_emplyr4(l_counter) :='<Ligne2Emplyr4>'|| convert_special_char(substr(ltrim(rtrim(l_empr_addr2))||' '||
2204 (ltrim(rtrim(l_empr_addr3))),1,30))||'</Ligne2Emplyr4>'||EOL;
2205 tab_emplyr5(l_counter) :='<Ligne2Emplyr5>'|| convert_special_char(substr(ltrim(rtrim(l_empr_addr2))||' '||
2206 (ltrim(rtrim(l_empr_addr3))),1,30))||'</Ligne2Emplyr5>'||EOL;
2207 ELSE
2208 tab_emplyr(l_counter) := NULL;
2209 tab_emplyr1(l_counter) := NULL;
2210 tab_emplyr2(l_counter) := NULL;
2211 tab_emplyr3(l_counter) := NULL;
2212 tab_emplyr4(l_counter) := NULL;
2213 tab_emplyr5(l_counter) := NULL;
2214 END IF;
2215
2216 l_counter := l_counter + 1;
2217 l_empr_fulladdr := l_empr_city ||' '||l_empr_prov||' '
2218 ||l_empr_country||' '||(substr(l_empr_postcode,1,3)||' '||
2219 substr(l_empr_postcode,4,3));
2220 IF l_empr_fulladdr IS NOT NULL AND
2221 l_empr_city <> ' ' AND
2222 tab_emplyr(l_counter-1) IS NOT NULL THEN
2223
2224 tab_emplyr(l_counter) :='<VilleEmplyr>'|| convert_special_char(substr(l_empr_fulladdr,1,60))||
2225 '</VilleEmplyr>'||EOL;
2226 tab_emplyr1(l_counter) :='<VilleEmplyr1>'|| convert_special_char(substr(l_empr_fulladdr,1,60))||
2227 '</VilleEmplyr1>'||EOL;
2228 tab_emplyr2(l_counter) :='<VilleEmplyr2>'|| convert_special_char(substr(l_empr_fulladdr,1,60))||
2229 '</VilleEmplyr2>'||EOL;
2230 tab_emplyr3(l_counter) :='<VilleEmplyr3>'|| convert_special_char(substr(l_empr_fulladdr,1,60))||
2231 '</VilleEmplyr3>'||EOL;
2232 tab_emplyr4(l_counter) :='<VilleEmplyr4>'|| convert_special_char(substr(l_empr_fulladdr,1,60))||
2233 '</VilleEmplyr4>'||EOL;
2234 tab_emplyr5(l_counter) :='<VilleEmplyr5>'|| convert_special_char(substr(l_empr_fulladdr,1,60))||
2235 '</VilleEmplyr5>'||EOL;
2236
2237 ELSIF l_empr_fulladdr IS NOT NULL AND
2238 l_empr_city <> ' ' AND
2239 tab_emplyr(l_counter-1) IS NULL THEN
2240 tab_emplyr(l_counter) :='<Ligne2Emplyr>'||convert_special_char(substr(l_empr_fulladdr,1,60))||
2241 '</Ligne2Emplyr>'||EOL;
2242 tab_emplyr1(l_counter) :='<Ligne2Emplyr1>'||convert_special_char(substr(l_empr_fulladdr,1,60))||
2243 '</Ligne2Emplyr1>'||EOL;
2244 tab_emplyr2(l_counter) :='<Ligne2Emplyr2>'||convert_special_char(substr(l_empr_fulladdr,1,60))||
2245 '</Ligne2Emplyr2>'||EOL;
2246 tab_emplyr3(l_counter) :='<Ligne2Emplyr3>'||convert_special_char(substr(l_empr_fulladdr,1,60))||
2247 '</Ligne2Emplyr3>'||EOL;
2248 tab_emplyr4(l_counter) :='<Ligne2Emplyr4>'||convert_special_char(substr(l_empr_fulladdr,1,60))||
2249 '</Ligne2Emplyr4>'||EOL;
2250 tab_emplyr5(l_counter) :='<Ligne2Emplyr5>'||convert_special_char(substr(l_empr_fulladdr,1,60))||
2251 '</Ligne2Emplyr5>'||EOL;
2252 ELSE
2253 tab_emplyr(l_counter) := NULL;
2254 tab_emplyr1(l_counter) := NULL;
2255 tab_emplyr2(l_counter) := NULL;
2256 tab_emplyr3(l_counter) := NULL;
2257 tab_emplyr4(l_counter) := NULL;
2258 tab_emplyr5(l_counter) := NULL;
2259 END IF;
2260
2261 end if;
2262
2263 hr_utility.trace('XML Employee: p_arch_action_id = '
2264 || to_char(p_arch_action_id));
2265 hr_utility.trace('XML Employee: p_asg_id = ' || to_char(p_asg_id));
2266
2267 --Annee
2268 tab_employee(lAnnee) := '<Annee>' || p_taxation_year || '</Annee>' || EOL;
2269 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
2270 then
2271 tab_employee1(lAnnee) := '<Annee1>' || p_taxation_year || '</Annee1>' || EOL;
2272 tab_employee2(lAnnee) := '<Annee2>' || p_taxation_year || '</Annee2>' || EOL;
2273 tab_employee3(lAnnee) := '<Annee3>' || p_taxation_year || '</Annee3>' || EOL;
2274 tab_employee4(lAnnee) := '<Annee4>' || p_taxation_year || '</Annee4>' || EOL;
2275 tab_employee5(lAnnee) := '<Annee5>' || p_taxation_year || '</Annee5>' || EOL;
2276 end if;
2277
2278 IF p_rep_type <> 'PAYCARL2AMPDF' THEN
2279
2280 /* Added by ssmukher to remove frequent function call to fetch the employee info */
2281 open c_employee_infor(p_arch_action_id);
2282 fetch c_employee_infor
2283 into l_quebec_business_number,
2284 l_reporting_year,
2285 l_rl2_slip_number,
2286 l_employee_sin,
2287 l_employee_last_name,
2288 l_employee_first_name,
2289 l_employee_middle_initial,
2290 l_employee_address_line1,
2291 l_employee_address_line2,
2292 l_employee_address_line3,
2293 l_employee_city,
2294 l_employee_province,
2295 l_employee_postal_code,
2296 l_employee_number,
2297 l_rl2_box_a,
2298 l_rl2_box_b,
2299 l_rl2_box_c,
2300 l_rl2_box_d,
2301 l_rl2_box_e,
2302 l_rl2_box_f,
2303 l_rl2_box_g,
2304 l_rl2_box_h,
2305 l_rl2_box_i,
2306 l_rl2_box_j,
2307 l_rl2_box_k,
2308 l_rl2_box_l,
2309 l_rl2_box_m,
2310 l_rl2_box_n,
2311 l_rl2_box_o,
2312 l_rl2_source_of_income,
2313 l_negative_balance_flag,
2314 l_per_id;
2315
2316 CLOSE c_employee_infor;
2317
2318 ELSE
2319 /*Data will be fetched using new cursor for RL2 Amendment PDF Purpose Bug#5046010*/
2320 open c_employee_amend_infor(p_arch_action_id);
2321 fetch c_employee_amend_infor
2322 into l_quebec_business_number,
2323 l_reporting_year,
2324 l_rl2_slip_number,
2325 l_rl2_form_number,
2326 l_employee_sin,
2327 l_employee_last_name,
2328 l_employee_first_name,
2329 l_employee_middle_initial,
2330 l_employee_address_line1,
2331 l_employee_address_line2,
2332 l_employee_address_line3,
2333 l_employee_city,
2334 l_employee_province,
2335 l_employee_postal_code,
2336 l_employee_number,
2337 l_rl2_box_a,
2338 l_rl2_box_b,
2339 l_rl2_box_c,
2340 l_rl2_box_d,
2341 l_rl2_box_e,
2342 l_rl2_box_f,
2343 l_rl2_box_g,
2344 l_rl2_box_h,
2345 l_rl2_box_i,
2346 l_rl2_box_j,
2347 l_rl2_box_k,
2348 l_rl2_box_l,
2349 l_rl2_box_m,
2350 l_rl2_box_n,
2351 l_rl2_box_o,
2352 l_rl2_source_of_income,
2353 l_negative_balance_flag,
2354 l_per_id;
2355
2356 CLOSE c_employee_amend_infor;
2357
2358 END IF;
2359
2360 IF p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
2361 THEN
2362
2363 /* IF (l_reporting_year = '2006' ) then
2364 l_authorization_code := 'FS-06-02-003';
2365 ELSIF (l_reporting_year = '2007' ) then
2366 l_authorization_code := 'FS-07-02-068'; --Bug 6747924
2367 ELSIF (l_reporting_year = '2008' ) then
2368 l_authorization_code := 'FS-08-02-011'; --Bug 7503515
2369 END IF; */
2370
2371 open c_get_auth_code(l_reporting_year);
2372 fetch c_get_auth_code into l_authorization_code;
2373 close c_get_auth_code;
2374
2375 tab_employee(l_authorisation_tag) := '<TagCertification>'||
2376 convert_special_char('No d''autorisation :')||
2377 '</TagCertification>';
2378 tab_employee(l_authorisation_no) := '<NoCertification>'||convert_special_char(l_authorization_code)
2379 ||'</NoCertification>';
2380 tab_employee1(l_authorisation_tag) := '<TagCertification1>'||
2381 convert_special_char('No d''autorisation :')||
2382 '</TagCertification1>';
2383 tab_employee1(l_authorisation_no) := '<NoCertification1>'||convert_special_char(l_authorization_code)
2384 || '</NoCertification1>';
2385 tab_employee2(l_authorisation_tag) := '<TagCertification2>'||
2386 convert_special_char('No d''autorisation :')||
2387 '</TagCertification2>';
2388 tab_employee2(l_authorisation_no) := '<NoCertification2>'||convert_special_char(l_authorization_code)
2389 || '</NoCertification2>';
2390 tab_employee3(l_authorisation_tag) := '<TagCertification3>'||
2391 convert_special_char('No d''autorisation :')||
2392 '</TagCertification3>';
2393 tab_employee3(l_authorisation_no) := '<NoCertification3>'||convert_special_char(l_authorization_code)
2394 ||'</NoCertification3>';
2395 tab_employee4(l_authorisation_tag) := '<TagCertification4>'||
2396 convert_special_char('No d''autorisation :')||
2397 '</TagCertification4>';
2398 tab_employee4(l_authorisation_no) := '<NoCertification4>'||convert_special_char(l_authorization_code)
2399 || '</NoCertification4>';
2400 tab_employee5(l_authorisation_tag) := '<TagCertification5>'||
2401 convert_special_char('No d''autorisation :')||
2402 '</TagCertification5>';
2403 tab_employee5(l_authorisation_no) := '<NoCertification5>'||convert_special_char(l_authorization_code)
2404 || '</NoCertification5>';
2405
2406 --select pay_ca_rl2_pdf_seq_s.nextval into l_seq_num from dual;
2407
2408 l_seq_num := pay_ca_eoy_rl2_archive.gen_rl2_pdf_seq(p_arch_action_id, --Bug 6768167
2409 l_reporting_year,
2410 'XMLPROC');
2411 l_first_seq_num := get_slip_seq_no('|',l_seq_num,1); -- 14701748
2412 -- for bug 10420152
2413 hr_utility.trace('l_empr_report_yr '||l_empr_report_yr);
2414 hr_utility.trace('l_seq_num '||l_seq_num);
2415 if (ltrim(rtrim(l_empr_report_yr)) >= '2010') then
2416 hr_utility.trace('p_taxation_year is greater than or equal 2010');
2417 hr_utility.trace('l_first_seq_num (befire) --'||l_first_seq_num);
2418 l_first_seq_num := substr(l_first_seq_num,1,3)||'-'||substr(l_first_seq_num,4,3)||
2419 '-'||substr(l_first_seq_num,7);
2420 hr_utility.trace('l_first_seq_num (after) --'||l_first_seq_num);
2421 end if;
2422
2423 tab_employee(l_sequence_no) := '<SequenceNum>'||l_first_seq_num
2424 ||'</SequenceNum>';
2425 tab_employee1(l_sequence_no) := '<SequenceNum1>'||l_first_seq_num
2426 ||'</SequenceNum1>';
2427 tab_employee2(l_sequence_no) := '<SequenceNum2>'||l_first_seq_num
2428 ||'</SequenceNum2>';
2429 tab_employee3(l_sequence_no) := '<SequenceNum3>'||l_first_seq_num
2430 ||'</SequenceNum3>';
2431 tab_employee4(l_sequence_no) := '<SequenceNum4>'||l_first_seq_num
2432 ||'</SequenceNum4>';
2433 tab_employee5(l_sequence_no) := '<SequenceNum5>'||l_first_seq_num
2434 ||'</SequenceNum5>';
2435 END IF;
2436
2437 --NoReleve
2438 /* Check for Mandatory Information RL-2 Slip Number missing */
2439 l_rl2_first_slip_number := get_slip_seq_no('|',l_rl2_slip_number,1); --14701748
2440
2441 IF ( l_rl2_first_slip_number = '000000000' AND
2442 l_rl2_first_slip_number IS NOT NULL) THEN
2443 l_status := 'Failed';
2444 l_msg_code := 'MISSING_SLIP_NO';
2445 tab_employee(lNoReleve) := NULL;
2446 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
2447 then
2448 tab_employee1(lNoReleve) := NULL;
2449 tab_employee2(lNoReleve) := NULL;
2450 tab_employee3(lNoReleve) := NULL;
2451 tab_employee4(lNoReleve) := NULL;
2452 tab_employee5(lNoReleve) := NULL;
2453 end if;
2454 ELSE
2455 --for bug 10420152
2456 if (ltrim(rtrim(l_empr_report_yr)) >= '2010') then
2457 hr_utility.trace('p_taxation_year is greater than or equal 2010');
2458 l_rl2_first_slip_number := substr(l_rl2_first_slip_number,1,3)||
2459 '-'||substr(l_rl2_first_slip_number,4,3)||
2460 '-'||substr(l_rl2_first_slip_number,7);
2461 end if;
2462 tab_employee(lNoReleve) := '<NoReleve>' || convert_special_char(l_rl2_first_slip_number) ||
2463 '</NoReleve>' || EOL;
2464 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
2465 then
2466 tab_employee1(lNoReleve) := '<NoReleve1>' || convert_special_char(l_rl2_first_slip_number) ||
2467 '</NoReleve1>' || EOL;
2468 tab_employee2(lNoReleve) := '<NoReleve2>' || convert_special_char(l_rl2_first_slip_number) ||
2469 '</NoReleve2>' || EOL;
2470 tab_employee3(lNoReleve) := '<NoReleve3>' || convert_special_char(l_rl2_first_slip_number) ||
2471 '</NoReleve3>' || EOL;
2472 tab_employee4(lNoReleve) := '<NoReleve4>' || convert_special_char(l_rl2_first_slip_number) ||
2473 '</NoReleve4>' || EOL;
2474 tab_employee5(lNoReleve) := '<NoReleve5>' || convert_special_char(l_rl2_first_slip_number) ||
2475 '</NoReleve5>' || EOL;
2476 end if;
2477 END IF;
2478
2479 --Added for bug 5046010
2480 --NoOrigReleve
2481
2482 /* 14701748 */
2483 if p_rep_type = 'PAYCARL2CLPDF' then
2484 if l_rl2_form_number is null then
2485 open pre_printed_form_no (p_arch_action_id);
2486 fetch pre_printed_form_no into l_rl2_form_number;
2487 CLOSE pre_printed_form_no;
2488 end if;
2489 end if;
2490 /* 14701748 */
2491
2492 l_rl2_first_form_number := get_slip_seq_no('|',l_rl2_form_number,1);
2493
2494 IF p_rep_type in ('PAYCARL2AMPDF','PAYCARL2CLPDF') THEN
2495 IF ( l_rl2_first_form_number = '000000000' AND l_rl2_first_form_number IS NOT NULL) THEN
2496 l_status := 'Failed';
2497 l_msg_code := 'MISSING_ORIGINAL_SLIP_NO';
2498 tab_employee(lNoOrigReleve) := NULL;
2499 tab_employee1(lNoOrigReleve) := NULL;
2500 tab_employee2(lNoOrigReleve) := NULL;
2501 tab_employee3(lNoOrigReleve) := NULL;
2502 tab_employee4(lNoOrigReleve) := NULL;
2503 tab_employee5(lNoOrigReleve) := NULL;
2504 ELSE
2505 if (ltrim(rtrim(l_empr_report_yr)) >= '2010') then
2506 if l_rl2_first_form_number IS NOT NULL then
2507 l_rl2_first_form_number := substr(l_rl2_first_form_number,1,3)||
2508 '-'||substr(l_rl2_first_form_number,4,3)||
2509 '-'||substr(l_rl2_first_form_number,7);
2510 end if;
2511 end if;
2512
2513 tab_employee(lNoOrigReleve) := '<NoOrigReleve>' || convert_special_char(l_rl2_first_form_number) ||
2514 '</NoOrigReleve>' || EOL;
2515 tab_employee1(lNoOrigReleve) := '<NoOrigReleve1>' || convert_special_char(l_rl2_first_form_number) ||
2516 '</NoOrigReleve1>' || EOL;
2517 tab_employee2(lNoOrigReleve) := '<NoOrigReleve2>' || convert_special_char(l_rl2_first_form_number) ||
2518 '</NoOrigReleve2>' || EOL;
2519 tab_employee3(lNoOrigReleve) := '<NoOrigReleve3>' || convert_special_char(l_rl2_first_form_number) ||
2520 '</NoOrigReleve3>' || EOL;
2521 tab_employee4(lNoOrigReleve) := '<NoOrigReleve4>' || convert_special_char(l_rl2_first_form_number) ||
2522 '</NoOrigReleve4>' || EOL;
2523 tab_employee5(lNoOrigReleve) := '<NoOrigReleve5>' || convert_special_char(l_rl2_first_form_number) ||
2524 '</NoOrigReleve5>' || EOL;
2525 END IF;
2526 hr_utility.trace('tab_employee(lNoOrigReleve) = ' || tab_employee(lNoOrigReleve));
2527 END IF;
2528
2529 -- End bug 5046010
2530
2531 -- NAS
2532 /* Bug Fix 4754891 */
2533 IF (l_employee_sin IS NOT NULL AND
2534 l_employee_sin <> '000000000') THEN
2535
2536 IF p_rep_type NOT IN ('RL2PAPERPDF','PAYCARL2AMPDF','PAYCARL2CLPDF') THEN
2537 tab_employee(lNAS) := '<NAS>' || convert_special_char(l_employee_sin) || '</NAS>' || EOL;
2538 ELSE
2539 tab_employee(lNAS1) := '<NAS1>' || convert_special_char(substr(l_employee_sin,1,3)) || '</NAS1>' || EOL;
2540 tab_employee(lNAS2) := '<NAS2>' || convert_special_char(substr(l_employee_sin,4,3)) || '</NAS2>' || EOL;
2541 tab_employee(lNAS3) := '<NAS3>' || convert_special_char(substr(l_employee_sin,7,3)) || '</NAS3>' || EOL;
2542
2543 tab_employee3(lNAS1) := '<NAS41>' || convert_special_char(substr(l_employee_sin,1,3)) || '</NAS41>' || EOL;
2544 tab_employee3(lNAS2) := '<NAS42>' || convert_special_char(substr(l_employee_sin,4,3)) || '</NAS42>' || EOL;
2545 tab_employee3(lNAS3) := '<NAS43>' || convert_special_char(substr(l_employee_sin,7,3)) || '</NAS43>' || EOL;
2546
2547 tab_employee1(lNAS1) := '<NAS21>' || convert_special_char(substr(l_employee_sin,1,3)) || '</NAS21>' || EOL;
2548 tab_employee1(lNAS2) := '<NAS22>' || convert_special_char(substr(l_employee_sin,4,3)) || '</NAS22>' || EOL;
2549 tab_employee1(lNAS3) := '<NAS23>' || convert_special_char(substr(l_employee_sin,7,3)) || '</NAS23>' || EOL;
2550
2551 tab_employee4(lNAS1) := '<NAS51>' || convert_special_char(substr(l_employee_sin,1,3)) || '</NAS51>' || EOL;
2552 tab_employee4(lNAS2) := '<NAS52>' || convert_special_char(substr(l_employee_sin,4,3)) || '</NAS52>' || EOL;
2553 tab_employee4(lNAS3) := '<NAS53>' || convert_special_char(substr(l_employee_sin,7,3)) || '</NAS53>' || EOL;
2554
2555 tab_employee2(lNAS1) := '<NAS31>' || convert_special_char(substr(l_employee_sin,1,3)) || '</NAS31>' || EOL;
2556 tab_employee2(lNAS2) := '<NAS32>' || convert_special_char(substr(l_employee_sin,4,3)) || '</NAS32>' || EOL;
2557 tab_employee2(lNAS3) := '<NAS33>' || convert_special_char(substr(l_employee_sin,7,3)) || '</NAS33>' || EOL;
2558
2559 tab_employee5(lNAS1) := '<NAS61>' || convert_special_char(substr(l_employee_sin,1,3)) || '</NAS61>' || EOL;
2560 tab_employee5(lNAS2) := '<NAS62>' || convert_special_char(substr(l_employee_sin,4,3)) || '</NAS62>' || EOL;
2561 tab_employee5(lNAS3) := '<NAS63>' || convert_special_char(substr(l_employee_sin,7,3)) || '</NAS63>' || EOL;
2562
2563 END IF;
2564 ELSE
2565 l_status := 'Failed';
2566 l_msg_code := 'SIN';
2567 tab_employee(lNAS) := NULL;
2568 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
2569 then
2570
2571 tab_employee(lNAS1) := NULL;
2572 tab_employee(lNAS2) := NULL;
2573 tab_employee(lNAS3) := NULL;
2574
2575 tab_employee3(lNAS1) := NULL;
2576 tab_employee3(lNAS2) := NULL;
2577 tab_employee3(lNAS3) := NULL;
2578
2579 tab_employee1(lNAS1) := NULL;
2580 tab_employee1(lNAS2) := NULL;
2581 tab_employee1(lNAS3) := NULL;
2582
2583 tab_employee4(lNAS1) := NULL;
2584 tab_employee4(lNAS2) := NULL;
2585 tab_employee4(lNAS3) := NULL;
2586
2587 tab_employee2(lNAS1) := NULL;
2588 tab_employee2(lNAS2) := NULL;
2589 tab_employee2(lNAS3) := NULL;
2590
2591 tab_employee5(lNAS1) := NULL;
2592 tab_employee5(lNAS2) := NULL;
2593 tab_employee5(lNAS3) := NULL;
2594
2595 end if;
2596 END IF;
2597 -- hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
2598
2599 -- No
2600 IF (l_employee_number IS NOT NULL AND
2601 l_employee_number <> ' ' ) THEN
2602 tab_employee(lNo) := '<No>' || convert_special_char(l_employee_number) || '</No>' || EOL;
2603 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
2604 then
2605 tab_employee1(lNo) := '<No1>' || convert_special_char(l_employee_number) || '</No1>' || EOL;
2606 tab_employee2(lNo) := '<No2>' || convert_special_char(l_employee_number) || '</No2>' || EOL;
2607 tab_employee3(lNo) := '<No3>' || convert_special_char(l_employee_number) || '</No3>' || EOL;
2608 tab_employee4(lNo) := '<No4>' || convert_special_char(l_employee_number) || '</No4>' || EOL;
2609 tab_employee5(lNo) := '<No5>' || convert_special_char(l_employee_number) || '</No5>' || EOL;
2610 end if;
2611 ELSE
2612 tab_employee(lNo) := NULL;
2613 tab_employee3(lNo) := NULL;
2614 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
2615 then
2616 tab_employee1(lNo) := NULL;
2617 tab_employee2(lNo) := NULL;
2618 tab_employee4(lNo) := NULL;
2619 tab_employee5(lNo) := NULL;
2620 end if;
2621 END IF;
2622 hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
2623
2624 -- NomFamille
2625
2626 tab_employee(lNomFamille) := '<NomFamille>' ||
2627 convert_special_char(substr(l_employee_last_name,1,30)) || '</NomFamille>' || EOL;
2628 hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
2629
2630 l_full_empname := convert_special_char(substr(l_employee_last_name,1,30));
2631
2632 -- Prenom
2633 IF (l_employee_first_name is NOT NULL AND
2634 l_employee_first_name <> ' ') THEN
2635 tab_employee(lPrenom) := '<Prenom>' || convert_special_char(substr(l_employee_first_name,1,30))
2636 || '</Prenom>' || EOL;
2637 l_full_empname := l_full_empname ||','||convert_special_char(substr(l_employee_first_name,1,30));
2638 ELSE
2639 l_msg_code := 'MISSING_EMP_FIRST_NAME';
2640 l_status := 'Failed';
2641 tab_employee(lPrenom) := NULL;
2642 END IF;
2643 hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
2644
2645 -- Initiale
2646
2647 IF (l_employee_middle_initial is NOT NULL AND
2648 l_employee_middle_initial <> ' ') THEN
2649 tab_employee(lInitiale) := '<Initiale>' || convert_special_char(substr(l_employee_middle_initial,1,1))
2650 || '</Initiale>' || EOL;
2651 l_full_empname := l_full_empname ||' '||convert_special_char(substr(l_employee_middle_initial,1,1));
2652 ELSE
2653 tab_employee(lInitiale) := NULL;
2654 END IF;
2655
2656 tab_employee(NomFamille_Ligne12_Ville) := '';
2657 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
2658 then
2659 tab_employee(lNomFamille) := '<NomFamille>' ||l_full_empname || '</NomFamille>' || EOL;
2660 tab_employee1(lNomFamille) := '<NomFamille1>' ||l_full_empname || '</NomFamille1>' || EOL;
2661 tab_employee2(lNomFamille) := '<NomFamille2>' ||l_full_empname || '</NomFamille2>' || EOL;
2662 tab_employee3(lNomFamille) := '<NomFamille3>' ||l_full_empname || '</NomFamille3>' || EOL;
2663 tab_employee4(lNomFamille) := '<NomFamille4>' ||l_full_empname || '</NomFamille4>' || EOL;
2664 tab_employee5(lNomFamille) := '<NomFamille5>' ||l_full_empname || '</NomFamille5>' || EOL;
2665
2666 /* Bug 13460512 Start */
2667 tab_employee(NomFamille_Ligne12_Ville) := '<NomFamille_Ligne12_Ville>' ||l_full_empname || EOL;
2668 tab_employee1(NomFamille_Ligne12_Ville) := '<NomFamille_Ligne12_Ville1>' ||l_full_empname || EOL;
2669 tab_employee2(NomFamille_Ligne12_Ville) := '<NomFamille_Ligne12_Ville2>' ||l_full_empname || EOL;
2670 tab_employee3(NomFamille_Ligne12_Ville) := '<NomFamille_Ligne12_Ville3>' ||l_full_empname || EOL;
2671 tab_employee4(NomFamille_Ligne12_Ville) := '<NomFamille_Ligne12_Ville4>' ||l_full_empname || EOL;
2672 tab_employee5(NomFamille_Ligne12_Ville) := '<NomFamille_Ligne12_Ville5>' ||l_full_empname || EOL;
2673 /* Bug 13460512 End */
2674
2675 end if;
2676
2677 hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
2678
2679 l_person_id := to_number(l_per_id);
2680
2681 l_return := pay_ca_emp_address_dtls.get_emp_address(
2682 l_person_id,
2683 l_address_line1,
2684 l_address_line2,
2685 l_address_line3,
2686 l_city,
2687 l_postal_code,
2688 l_country,
2689 l_emp_province
2690 );
2691 -- If Address line 1 is NULL or ' ' then the employee is missing
2692 -- address information - as line 1 is mandatory in the Address form.
2693 -- Need to check data by SS transaction /API.
2694
2695 hr_utility.trace('l_person_id = ' || to_char(l_person_id));
2696 hr_utility.trace('l_address_line1 = ' || l_address_line1);
2697 hr_utility.trace('l_address_line2 = ' || l_address_line2);
2698 hr_utility.trace('l_postal_code = ' || l_postal_code);
2699
2700 /* Bug Fix 4761782 */
2701 -- Address Line 1
2702 IF l_address_line1 IS NOT NULL AND
2703 l_address_line1 = ' ' THEN
2704
2705 l_status := 'Failed';
2706 l_msg_code := 'MISSING_EMP_ADDRESS';
2707
2708 l_addr_begin_tag := NULL;
2709 tab_employee(lLigne1) := NULL;
2710 tab_employee(lLigne2) := NULL;
2711 tab_employee(lVille) := NULL;
2712 tab_employee(lProvince) := NULL;
2713 tab_employee(lCodePostal) := NULL;
2714 l_addr_end_tag := NULL;
2715 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
2716 then
2717 tab_employee1(lLigne1) := NULL;
2718 tab_employee1(lLigne2) := NULL;
2719
2720 tab_employee2(lLigne1) := NULL;
2721 tab_employee2(lLigne2) := NULL;
2722
2723 tab_employee3(lLigne1) := NULL;
2724 tab_employee3(lLigne2) := NULL;
2725 tab_employee4(lLigne1) := NULL;
2726 tab_employee4(lLigne2) := NULL;
2727 tab_employee5(lLigne1) := NULL;
2728 tab_employee5(lLigne2) := NULL;
2729
2730 end if;
2731 ELSE
2732
2733 l_addr_begin_tag := '<Adresse>';
2734
2735 tab_employee(lLigne1) := '<Ligne1>' ||
2736 convert_special_char(substr(l_address_line1,1,30)) || '</Ligne1>' || EOL;
2737
2738 /* Start Bug 13460512 */
2739 tab_employee(NomFamille_Ligne12_Ville) := tab_employee(NomFamille_Ligne12_Ville) ||
2740 convert_special_char(substr(l_address_line1,1,30)) || EOL;
2741 /* End Bug 13460512 */
2742
2743 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
2744 then
2745 tab_employee1(lLigne1) := '<Ligne11>' ||
2746 convert_special_char(substr(l_address_line1,1,30)) || '</Ligne11>' || EOL;
2747 tab_employee2(lLigne1) := '<Ligne12>' ||
2748 convert_special_char(substr(l_address_line1,1,30)) || '</Ligne12>' || EOL;
2749 tab_employee3(lLigne1) := '<Ligne13>' ||
2750 convert_special_char(substr(l_address_line1,1,30)) || '</Ligne13>' || EOL;
2751 tab_employee4(lLigne1) := '<Ligne14>' ||
2752 convert_special_char(substr(l_address_line1,1,30)) || '</Ligne14>' || EOL;
2753 tab_employee5(lLigne1) := '<Ligne15>' ||
2754 convert_special_char(substr(l_address_line1,1,30)) || '</Ligne15>' || EOL;
2755
2756 /* Start Bug 13460512 */
2757 tab_employee1(NomFamille_Ligne12_Ville) := tab_employee1(NomFamille_Ligne12_Ville) ||
2758 convert_special_char(substr(l_address_line1,1,30)) || EOL;
2759 tab_employee2(NomFamille_Ligne12_Ville) := tab_employee2(NomFamille_Ligne12_Ville) ||
2760 convert_special_char(substr(l_address_line1,1,30)) || EOL;
2761 tab_employee3(NomFamille_Ligne12_Ville) := tab_employee3(NomFamille_Ligne12_Ville) ||
2762 convert_special_char(substr(l_address_line1,1,30)) || EOL;
2763 tab_employee4(NomFamille_Ligne12_Ville) := tab_employee4(NomFamille_Ligne12_Ville) ||
2764 convert_special_char(substr(l_address_line1,1,30)) || EOL;
2765 tab_employee5(NomFamille_Ligne12_Ville) := tab_employee5(NomFamille_Ligne12_Ville) ||
2766 convert_special_char(substr(l_address_line1,1,30)) || EOL;
2767 /* End Bug 13460512 */
2768
2769 end if;
2770 hr_utility.trace('tab_employee(lLigne1) = ' || tab_employee(lLigne1));
2771
2772 -- Address Line 2
2773
2774 IF ((l_address_line2 IS NOT NULL AND
2775 l_address_line2 <> ' ' ) OR
2776 (l_address_line3 IS NOT NULL AND
2777 l_address_line3 <> ' ') ) THEN
2778 l_combined_addr := rtrim(ltrim(l_address_line2)) || rtrim(ltrim(l_address_line3));
2779 tab_employee(lLigne2) := '<Ligne2>' ||
2780 convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne2>' || EOL;
2781
2782 /* Start Bug 13460512 */
2783 tab_employee(NomFamille_Ligne12_Ville) := tab_employee(NomFamille_Ligne12_Ville) ||
2784 convert_special_char(substr(l_combined_addr,1,30)) || EOL;
2785 /* End Bug 13460512 */
2786
2787 if p_rep_type in ('RL2PAPERPDF','PAYCARL2AMPDF','PAYCARL2CLPDF') and l_combined_addr is not null then
2788 tab_employee1(lLigne2) := '<Ligne21>' ||
2789 convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne21>' || EOL;
2790 tab_employee2(lLigne2) := '<Ligne22>' ||
2791 convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne22>' || EOL;
2792 tab_employee3(lLigne2) := '<Ligne23>' ||
2793 convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne23>' || EOL;
2794 tab_employee4(lLigne2) := '<Ligne24>' ||
2795 convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne24>' || EOL;
2796 tab_employee5(lLigne2) := '<Ligne25>' ||
2797 convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne25>' || EOL;
2798
2799
2800 /* Start Bug 13460512 */
2801 tab_employee1(NomFamille_Ligne12_Ville) := tab_employee1(NomFamille_Ligne12_Ville) ||
2802 convert_special_char(substr(l_combined_addr,1,30)) || EOL;
2803 tab_employee2(NomFamille_Ligne12_Ville) := tab_employee2(NomFamille_Ligne12_Ville) ||
2804 convert_special_char(substr(l_combined_addr,1,30)) || EOL;
2805 tab_employee3(NomFamille_Ligne12_Ville) := tab_employee3(NomFamille_Ligne12_Ville) ||
2806 convert_special_char(substr(l_combined_addr,1,30)) || EOL;
2807 tab_employee4(NomFamille_Ligne12_Ville) := tab_employee4(NomFamille_Ligne12_Ville) ||
2808 convert_special_char(substr(l_combined_addr,1,30)) || EOL;
2809 tab_employee5(NomFamille_Ligne12_Ville) := tab_employee5(NomFamille_Ligne12_Ville) ||
2810 convert_special_char(substr(l_combined_addr,1,30)) || EOL;
2811 /* End Bug 13460512 */
2812
2813 end if;
2814 ELSE
2815 IF p_rep_type NOT IN ('RL2PAPERPDF','PAYCARL2AMPDF','PAYCARL2CLPDF') THEN
2816 tab_employee(lLigne2) := NULL;
2817 ELSE
2818 tab_employee(lVille) := NULL;
2819 tab_employee1(lVille) := NULL;
2820 tab_employee2(lVille) := NULL;
2821 tab_employee3(lVille) := NULL;
2822 tab_employee4(lVille) := NULL;
2823 tab_employee5(lVille) := NULL;
2824 END IF;
2825 END IF;
2826 --hr_utility.trace('tab_employee(lLigne2) = ' || tab_employee(lLigne2));
2827
2828 -- Ville (City)
2829 IF l_city IS NOT NULL AND
2830 l_city <> ' ' THEN
2831 tab_employee(lVille) := '<Ville>' ||
2832 convert_special_char(substr(l_city,1,30)) || '</Ville>' || EOL;
2833 l_full_empaddr := convert_special_char(substr(l_city,1,30));
2834
2835 ELSE
2836 tab_employee(lVille) := NULL;
2837
2838 END IF;
2839 --hr_utility.trace('tab_employee(lVille) = ' || tab_employee(lVille));
2840
2841 -- Province
2842 IF l_emp_province IS NOT NULL AND
2843 l_emp_province <> ' ' THEN
2844
2845 IF l_country = 'CA' THEN
2846 tab_employee(lProvince) := '<Province>' ||
2847 convert_special_char(SUBSTR(hr_general.decode_lookup(
2848 'CA_PROVINCE',l_emp_province),1,20)) || '</Province>' || EOL;
2849 l_full_empaddr := l_full_empaddr ||' '||convert_special_char(l_emp_province);
2850
2851
2852 ELSIF l_country = 'US' THEN
2853 tab_employee(lProvince) := '<Province>' ||l_emp_province || '</Province>' || EOL;
2854 l_full_empaddr := l_full_empaddr ||' '||l_emp_province;
2855
2856 ELSE
2857 tab_employee(lProvince) := '<Province>' ||l_country|| '</Province>' || EOL;
2858 l_full_empaddr := l_full_empaddr ||' '||l_country;
2859
2860 END IF;
2861 ELSE
2862 tab_employee(lProvince) := NULL;
2863
2864 END IF;
2865 hr_utility.trace('tab_employee(lProvince) = ' || tab_employee(lProvince));
2866
2867 -- Bug# 4754743 fix
2868 -- Postal Code
2869 IF l_postal_code IS NOT NULL AND
2870 l_postal_code <> ' ' THEN
2871 tab_employee(lCodePostal) := '<CodePostal>' ||
2872 convert_special_char(substr(replace(l_postal_code,' '),1,6)) || '</CodePostal>' || EOL;
2873 l_full_empaddr := l_full_empaddr ||' '||convert_special_char(substr(replace(l_country,' '),1,6))||' '||
2874 convert_special_char(l_postal_code);
2875
2876 ELSE
2877 tab_employee(lCodePostal) := NULL;
2878 END IF;
2879
2880 hr_utility.trace('tab_employee(lCodePostal) = ' || tab_employee(lCodePostal));
2881 l_addr_end_tag := '</Adresse>';
2882
2883 END IF;
2884
2885 if p_rep_type = 'RL2PAPERPDF' OR p_rep_type='PAYCARL2AMPDF' OR p_rep_type='PAYCARL2CLPDF'
2886 then
2887 IF ((l_full_empaddr IS NOT NULL AND
2888 l_full_empaddr <> ' ') AND
2889 ((l_address_line2 IS NOT NULL AND
2890 l_address_line2 <> ' ' ) OR
2891 (l_address_line3 IS NOT NULL AND
2892 l_address_line3 <> ' ') )) THEN
2893
2894 tab_employee(lVille) := '<Ville>' ||l_full_empaddr || '</Ville>' || EOL;
2895 tab_employee1(lVille) := '<Ville1>' ||l_full_empaddr || '</Ville1>' || EOL;
2896 tab_employee2(lVille) := '<Ville2>' ||l_full_empaddr || '</Ville2>' || EOL;
2897 tab_employee3(lVille) := '<Ville3>' ||l_full_empaddr || '</Ville3>' || EOL;
2898 tab_employee4(lVille) := '<Ville4>' ||l_full_empaddr || '</Ville4>' || EOL;
2899 tab_employee5(lVille) := '<Ville5>' ||l_full_empaddr || '</Ville5>' || EOL;
2900
2901 /* Start 13460512 */
2902 tab_employee(NomFamille_Ligne12_Ville) := tab_employee(NomFamille_Ligne12_Ville) ||l_full_empaddr || '</NomFamille_Ligne12_Ville>' || EOL;
2903 tab_employee1(NomFamille_Ligne12_Ville) := tab_employee1(NomFamille_Ligne12_Ville) ||l_full_empaddr || '</NomFamille_Ligne12_Ville1>' || EOL;
2904 tab_employee2(NomFamille_Ligne12_Ville) := tab_employee2(NomFamille_Ligne12_Ville) ||l_full_empaddr || '</NomFamille_Ligne12_Ville2>' || EOL;
2905 tab_employee3(NomFamille_Ligne12_Ville) := tab_employee3(NomFamille_Ligne12_Ville) ||l_full_empaddr || '</NomFamille_Ligne12_Ville3>' || EOL;
2906 tab_employee4(NomFamille_Ligne12_Ville) := tab_employee4(NomFamille_Ligne12_Ville) ||l_full_empaddr || '</NomFamille_Ligne12_Ville4>' || EOL;
2907 tab_employee5(NomFamille_Ligne12_Ville) := tab_employee5(NomFamille_Ligne12_Ville) ||l_full_empaddr || '</NomFamille_Ligne12_Ville5>' || EOL;
2908 /* End 13460512 */
2909
2910 ELSIF l_full_empaddr IS NOT NULL AND
2911 (l_postal_code IS NOT NULL OR
2912 l_city IS NOT NULL OR
2913 l_emp_province IS NOT NULL) THEN
2914
2915 tab_employee(lVille) := NULL;
2916 tab_employee1(lVille) := NULL;
2917 tab_employee2(lVille) := NULL;
2918 tab_employee3(lVille) := NULL;
2919 tab_employee4(lVille) := NULL;
2920 tab_employee5(lVille) := NULL;
2921
2922 tab_employee(lLigne2) := '<Ligne2>' ||l_full_empaddr || '</Ligne2>' || EOL;
2923 tab_employee1(lLigne2) := '<Ligne21>' ||l_full_empaddr || '</Ligne21>' || EOL;
2924 tab_employee2(lLigne2) := '<Ligne22>' ||l_full_empaddr || '</Ligne22>' || EOL;
2925 tab_employee3(lLigne2) := '<Ligne23>' ||l_full_empaddr || '</Ligne23>' || EOL;
2926 tab_employee4(lLigne2) := '<Ligne24>' ||l_full_empaddr || '</Ligne24>' || EOL;
2927 tab_employee5(lLigne2) := '<Ligne25>' ||l_full_empaddr || '</Ligne25>' || EOL;
2928
2929 /* Start 13460512 */
2930 tab_employee(NomFamille_Ligne12_Ville) := tab_employee(NomFamille_Ligne12_Ville) ||l_full_empaddr || '</NomFamille_Ligne12_Ville>' || EOL;
2931 tab_employee1(NomFamille_Ligne12_Ville) := tab_employee1(NomFamille_Ligne12_Ville) ||l_full_empaddr || '</NomFamille_Ligne12_Ville1>' || EOL;
2932 tab_employee2(NomFamille_Ligne12_Ville) := tab_employee2(NomFamille_Ligne12_Ville) ||l_full_empaddr || '</NomFamille_Ligne12_Ville2>' || EOL;
2933 tab_employee3(NomFamille_Ligne12_Ville) := tab_employee3(NomFamille_Ligne12_Ville) ||l_full_empaddr || '</NomFamille_Ligne12_Ville3>' || EOL;
2934 tab_employee4(NomFamille_Ligne12_Ville) := tab_employee4(NomFamille_Ligne12_Ville) ||l_full_empaddr || '</NomFamille_Ligne12_Ville4>' || EOL;
2935 tab_employee5(NomFamille_Ligne12_Ville) := tab_employee5(NomFamille_Ligne12_Ville) ||l_full_empaddr || '</NomFamille_Ligne12_Ville5>' || EOL;
2936 /* End 13460512 */
2937
2938 ELSE
2939
2940 tab_employee(lVille) := NULL;
2941 tab_employee1(lVille) := NULL;
2942 tab_employee2(lVille) := NULL;
2943 tab_employee(lLigne2) := NULL;
2944 tab_employee1(lLigne2) := NULL;
2945 tab_employee2(lLigne2) := NULL;
2946 tab_employee3(lVille) := NULL;
2947 tab_employee4(lVille) := NULL;
2948 tab_employee5(lVille) := NULL;
2949 tab_employee3(lLigne2) := NULL;
2950 tab_employee4(lLigne2) := NULL;
2951 tab_employee5(lLigne2) := NULL;
2952
2953 /* Start 13460512 */
2954 tab_employee(NomFamille_Ligne12_Ville) := tab_employee(NomFamille_Ligne12_Ville) || '</NomFamille_Ligne12_Ville>' || EOL;
2955 tab_employee1(NomFamille_Ligne12_Ville) := tab_employee1(NomFamille_Ligne12_Ville) || '</NomFamille_Ligne12_Ville1>' || EOL;
2956 tab_employee2(NomFamille_Ligne12_Ville) := tab_employee2(NomFamille_Ligne12_Ville) || '</NomFamille_Ligne12_Ville2>' || EOL;
2957 tab_employee3(NomFamille_Ligne12_Ville) := tab_employee3(NomFamille_Ligne12_Ville) || '</NomFamille_Ligne12_Ville3>' || EOL;
2958 tab_employee4(NomFamille_Ligne12_Ville) := tab_employee4(NomFamille_Ligne12_Ville) || '</NomFamille_Ligne12_Ville4>' || EOL;
2959 tab_employee5(NomFamille_Ligne12_Ville) := tab_employee5(NomFamille_Ligne12_Ville) || '</NomFamille_Ligne12_Ville5>' || EOL;
2960 /* End 13460512 */
2961
2962 END IF;
2963
2964 end if;
2965
2966 -- Summ (Box A)
2967
2968 hr_utility.trace('The Value of Box A is '|| l_rl2_box_a);
2969 IF TO_NUMBER(l_rl2_box_a) > 9999999.99 THEN
2970 l_status := 'Failed';
2971 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2972 END IF;
2973
2974 IF l_rl2_box_a IS NOT NULL AND
2975 to_number(l_rl2_box_a) <> 0 THEN
2976
2977 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_a),l_format_mask)))
2978 INTO l_formatted_box
2979 FROM dual;
2980 -- <A_PrestRPA_RPNA> changed to <A_PrestRPA> for bug 13029973
2981 tab_employee(lA_PrestRPA_RPNA) := '<A_PrestRPA>' || l_formatted_box ||
2982 '</A_PrestRPA>' || EOL;
2983 /* All pdf templates using tag <A_PrestRPA_RPNA> to display Box A value in
2984 in first slip, bug 12973513*/
2985 tab_employee(lA_PrestRPA_RPNA1) := '<A_PrestRPA_RPNA>' || l_formatted_box ||
2986 '</A_PrestRPA_RPNA>' || EOL;
2987 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
2988 then
2989 tab_employee1(lA_PrestRPA_RPNA) := '<A_PrestRPA_RPNA1>' || l_formatted_box ||
2990 '</A_PrestRPA_RPNA1>' || EOL;
2991 tab_employee2(lA_PrestRPA_RPNA) := '<A_PrestRPA_RPNA2>' || l_formatted_box ||
2992 '</A_PrestRPA_RPNA2>' || EOL;
2993 tab_employee3(lA_PrestRPA_RPNA) := '<A_PrestRPA_RPNA3>' || l_formatted_box ||
2994 '</A_PrestRPA_RPNA3>' || EOL;
2995 tab_employee4(lA_PrestRPA_RPNA) := '<A_PrestRPA_RPNA4>' || l_formatted_box ||
2996 '</A_PrestRPA_RPNA4>' || EOL;
2997 tab_employee5(lA_PrestRPA_RPNA) := '<A_PrestRPA_RPNA5>' || l_formatted_box ||
2998 '</A_PrestRPA_RPNA5>' || EOL;
2999 end if;
3000 l_all_box_0 := FALSE;
3001 ELSE
3002 tab_employee(lA_PrestRPA_RPNA ) := NULL;
3003 tab_employee(lA_PrestRPA_RPNA1) := NULL; -- bug 12973513
3004 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3005 then
3006 tab_employee1(lA_PrestRPA_RPNA ) := NULL;
3007 tab_employee2(lA_PrestRPA_RPNA ) := NULL;
3008 tab_employee3(lA_PrestRPA_RPNA ) := NULL;
3009 tab_employee4(lA_PrestRPA_RPNA ) := NULL;
3010 tab_employee5(lA_PrestRPA_RPNA ) := NULL;
3011 end if;
3012 END IF;
3013 hr_utility.trace('tab_employee(lA_PrestRPA_RPNA) = ' ||
3014 tab_employee(lA_PrestRPA_RPNA));
3015
3016 -- Summ (Box B)
3017
3018 IF TO_NUMBER(l_rl2_box_b) > 9999999.99 THEN
3019 l_status := 'Failed';
3020 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3021 END IF;
3022
3023 IF l_rl2_box_b IS NOT NULL AND
3024 to_number(l_rl2_box_b) <> 0 THEN
3025
3026 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_b),l_format_mask)))
3027 INTO l_formatted_box
3028 FROM dual;
3029
3030 tab_employee(lB_PrestREER_FERR_RPDB) := '<B_PrestREER_FERR_RPDB>' || l_formatted_box
3031 || '</B_PrestREER_FERR_RPDB>' || EOL;
3032 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3033 then
3034 tab_employee1(lB_PrestREER_FERR_RPDB) := '<B_PrestREER_FERR_RPDB1>' || l_formatted_box
3035 || '</B_PrestREER_FERR_RPDB1>' || EOL;
3036 tab_employee2(lB_PrestREER_FERR_RPDB) := '<B_PrestREER_FERR_RPDB2>' || l_formatted_box
3037 || '</B_PrestREER_FERR_RPDB2>' || EOL;
3038 tab_employee3(lB_PrestREER_FERR_RPDB) := '<B_PrestREER_FERR_RPDB3>' || l_formatted_box
3039 || '</B_PrestREER_FERR_RPDB3>' || EOL;
3040 tab_employee4(lB_PrestREER_FERR_RPDB) := '<B_PrestREER_FERR_RPDB4>' || l_formatted_box
3041 || '</B_PrestREER_FERR_RPDB4>' || EOL;
3042 tab_employee5(lB_PrestREER_FERR_RPDB) := '<B_PrestREER_FERR_RPDB5>' || l_formatted_box
3043 || '</B_PrestREER_FERR_RPDB5>' || EOL;
3044 end if;
3045 l_all_box_0 := FALSE;
3046 ELSE
3047
3048 tab_employee(lB_PrestREER_FERR_RPDB) := NULL;
3049 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3050 then
3051 tab_employee1(lB_PrestREER_FERR_RPDB) := NULL;
3052 tab_employee2(lB_PrestREER_FERR_RPDB) := NULL;
3053 tab_employee3(lB_PrestREER_FERR_RPDB) := NULL;
3054 tab_employee4(lB_PrestREER_FERR_RPDB) := NULL;
3055 tab_employee5(lB_PrestREER_FERR_RPDB) := NULL;
3056 end if;
3057
3058 END IF;
3059 hr_utility.trace('tab_employee(lB_PrestREER_FERR_RPDB) = ' ||
3060 tab_employee(lB_PrestREER_FERR_RPDB));
3061
3062 -- Summ (Box C)
3063
3064 IF TO_NUMBER(l_rl2_box_c) > 9999999.99 THEN
3065 l_status := 'Failed';
3066 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3067 END IF;
3068
3069 IF l_rl2_box_c IS NOT NULL AND
3070 to_number(l_rl2_box_c) <> 0 THEN
3071
3072 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_c),l_format_mask)))
3073 INTO l_formatted_box
3074 FROM dual;
3075
3076 tab_employee(lC_AutrePaiement ) := '<C_AutrePaiement>' ||
3077 l_formatted_box || '</C_AutrePaiement>' || EOL;
3078 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3079 then
3080 tab_employee1(lC_AutrePaiement ) := '<C_AutrePaiement1>' ||
3081 l_formatted_box || '</C_AutrePaiement1>' || EOL;
3082 tab_employee2(lC_AutrePaiement ) := '<C_AutrePaiement2>' ||
3083 l_formatted_box || '</C_AutrePaiement2>' || EOL;
3084 tab_employee3(lC_AutrePaiement ) := '<C_AutrePaiement3>' ||
3085 l_formatted_box || '</C_AutrePaiement3>' || EOL;
3086 tab_employee4(lC_AutrePaiement ) := '<C_AutrePaiement4>' ||
3087 l_formatted_box || '</C_AutrePaiement4>' || EOL;
3088 tab_employee5(lC_AutrePaiement ) := '<C_AutrePaiement5>' ||
3089 l_formatted_box || '</C_AutrePaiement5>' || EOL;
3090 end if;
3091 l_all_box_0 := FALSE;
3092 ELSE
3093 tab_employee(lC_AutrePaiement ) := NULL;
3094 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3095 then
3096 tab_employee1(lC_AutrePaiement ) := NULL;
3097 tab_employee2(lC_AutrePaiement ) := NULL;
3098 tab_employee3(lC_AutrePaiement ) := NULL;
3099 tab_employee4(lC_AutrePaiement ) := NULL;
3100 tab_employee5(lC_AutrePaiement ) := NULL;
3101 end if;
3102 END IF;
3103
3104 hr_utility.trace('tab_employee(lC_AutrePaiement ) = ' ||
3105 tab_employee(lC_AutrePaiement ));
3106
3107 -- Summ (Box D)
3108
3109 IF TO_NUMBER(l_rl2_box_d) > 9999999.99 THEN
3110 l_status := 'Failed';
3111 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3112 END IF;
3113
3114 IF l_rl2_box_d IS NOT NULL AND
3115 to_number(l_rl2_box_d) <> 0 THEN
3116
3117 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_d),l_format_mask)))
3118 INTO l_formatted_box
3119 FROM dual;
3120
3121 tab_employee(lD_RembPrimeConjoint) := '<D_RembPrimeConjoint>' ||
3122 l_formatted_box || '</D_RembPrimeConjoint>' || EOL;
3123 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3124 then
3125 tab_employee1(lD_RembPrimeConjoint) := '<D_RembPrimeConjoint1>' ||
3126 l_formatted_box || '</D_RembPrimeConjoint1>' || EOL;
3127 tab_employee2(lD_RembPrimeConjoint) := '<D_RembPrimeConjoint2>' ||
3128 l_formatted_box || '</D_RembPrimeConjoint2>' || EOL;
3129 tab_employee3(lD_RembPrimeConjoint) := '<D_RembPrimeConjoint3>' ||
3130 l_formatted_box || '</D_RembPrimeConjoint3>' || EOL;
3131 tab_employee4(lD_RembPrimeConjoint) := '<D_RembPrimeConjoint4>' ||
3132 l_formatted_box || '</D_RembPrimeConjoint4>' || EOL;
3133 tab_employee5(lD_RembPrimeConjoint) := '<D_RembPrimeConjoint5>' ||
3134 l_formatted_box || '</D_RembPrimeConjoint5>' || EOL;
3135 end if;
3136 l_all_box_0 := FALSE;
3137 ELSE
3138 tab_employee(lD_RembPrimeConjoint) := NULL;
3139 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3140 then
3141 tab_employee1(lD_RembPrimeConjoint) := NULL;
3142 tab_employee2(lD_RembPrimeConjoint) := NULL;
3143 tab_employee3(lD_RembPrimeConjoint) := NULL;
3144 tab_employee4(lD_RembPrimeConjoint) := NULL;
3145 tab_employee5(lD_RembPrimeConjoint) := NULL;
3146 end if;
3147 END IF;
3148 hr_utility.trace('tab_employee(lD_RembPrimeConjoint) = ' ||
3149 tab_employee(lD_RembPrimeConjoint));
3150
3151 -- (Box E)
3152
3153 IF TO_NUMBER(l_rl2_box_e) > 9999999.99 THEN
3154 l_status := 'Failed';
3155 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3156 END IF;
3157
3158 IF l_rl2_box_e IS NOT NULL AND
3159 to_number(l_rl2_box_e) <> 0 THEN
3160
3161 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_e),l_format_mask)))
3162 INTO l_formatted_box
3163 FROM dual;
3164
3165 tab_employee(lE_PrestDeces) := '<E_PrestDeces>' ||
3166 l_formatted_box || '</E_PrestDeces>' || EOL;
3167 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3168 then
3169 tab_employee1(lE_PrestDeces) := '<E_PrestDeces1>' ||
3170 l_formatted_box || '</E_PrestDeces1>' || EOL;
3171 tab_employee2(lE_PrestDeces) := '<E_PrestDeces2>' ||
3172 l_formatted_box || '</E_PrestDeces2>' || EOL;
3173 tab_employee3(lE_PrestDeces) := '<E_PrestDeces3>' ||
3174 l_formatted_box || '</E_PrestDeces3>' || EOL;
3175 tab_employee4(lE_PrestDeces) := '<E_PrestDeces4>' ||
3176 l_formatted_box || '</E_PrestDeces4>' || EOL;
3177 tab_employee5(lE_PrestDeces) := '<E_PrestDeces5>' ||
3178 l_formatted_box || '</E_PrestDeces5>' || EOL;
3179 end if;
3180 l_all_box_0 := FALSE;
3181 ELSE
3182 tab_employee(lE_PrestDeces) := NULL;
3183 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3184 then
3185 tab_employee1(lE_PrestDeces) := NULL;
3186 tab_employee2(lE_PrestDeces) := NULL;
3187 tab_employee3(lE_PrestDeces) := NULL;
3188 tab_employee4(lE_PrestDeces) := NULL;
3189 tab_employee5(lE_PrestDeces) := NULL;
3190 end if;
3191 END IF;
3192
3193 hr_utility.trace('tab_employee(lE_PrestDeces) = ' ||
3194 tab_employee(lE_PrestDeces));
3195
3196 -- (Box F)
3197
3198 IF TO_NUMBER(l_rl2_box_f) > 9999999.99 THEN
3199 l_status := 'Failed';
3200 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3201 END IF;
3202
3203 IF l_rl2_box_f IS NOT NULL AND
3204 to_number(l_rl2_box_f) <> 0 THEN
3205
3206 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_f),l_format_mask)))
3207 INTO l_formatted_box
3208 FROM dual;
3209
3210 tab_employee(lF_RembCotisInutilise) := '<F_RembCotisInutilise>' ||
3211 l_formatted_box || '</F_RembCotisInutilise>' || EOL;
3212 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3213 then
3214 tab_employee1(lF_RembCotisInutilise) := '<F_RembCotisInutilise1>' ||
3215 l_formatted_box || '</F_RembCotisInutilise1>' || EOL;
3216 tab_employee2(lF_RembCotisInutilise) := '<F_RembCotisInutilise2>' ||
3217 l_formatted_box || '</F_RembCotisInutilise2>' || EOL;
3218 tab_employee3(lF_RembCotisInutilise) := '<F_RembCotisInutilise3>' ||
3219 l_formatted_box || '</F_RembCotisInutilise3>' || EOL;
3220 tab_employee4(lF_RembCotisInutilise) := '<F_RembCotisInutilise4>' ||
3221 l_formatted_box || '</F_RembCotisInutilise4>' || EOL;
3222 tab_employee5(lF_RembCotisInutilise) := '<F_RembCotisInutilise5>' ||
3223 l_formatted_box || '</F_RembCotisInutilise5>' || EOL;
3224 end if;
3225 l_all_box_0 := FALSE;
3226 ELSE
3227 tab_employee(lF_RembCotisInutilise) := NULL;
3228 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3229 then
3230 tab_employee1(lF_RembCotisInutilise) := NULL;
3231 tab_employee2(lF_RembCotisInutilise) := NULL;
3232 tab_employee3(lF_RembCotisInutilise) := NULL;
3233 tab_employee4(lF_RembCotisInutilise) := NULL;
3234 tab_employee5(lF_RembCotisInutilise) := NULL;
3235 end if;
3236 END IF;
3237 hr_utility.trace('tab_employee(lF_RembCotisInutilise) = ' ||
3238 tab_employee(lF_RembCotisInutilise));
3239
3240 -- (Box G)
3241
3242 IF TO_NUMBER(l_rl2_box_g) > 9999999.99 THEN
3243 l_status := 'Failed';
3244 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3245 END IF;
3246
3247 IF l_rl2_box_g IS NOT NULL AND
3248 to_number(l_rl2_box_g) <> 0 THEN
3249
3250 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_g),l_format_mask)))
3251 INTO l_formatted_box
3252 FROM dual;
3253
3254 tab_employee(lG_RevocationREER_FERR ) := '<G_RevocationREER_FERR>' ||
3255 l_formatted_box || '</G_RevocationREER_FERR>' || EOL;
3256 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3257 then
3258 tab_employee1(lG_RevocationREER_FERR ) := '<G_RevocationREER_FERR1>' ||
3259 l_formatted_box || '</G_RevocationREER_FERR1>' || EOL;
3260 tab_employee2(lG_RevocationREER_FERR ) := '<G_RevocationREER_FERR2>' ||
3261 l_formatted_box || '</G_RevocationREER_FERR2>' || EOL;
3262 tab_employee3(lG_RevocationREER_FERR) := '<G_RevocationREER_FERR3>' ||
3263 l_formatted_box || '</G_RevocationREER_FERR3>' || EOL;
3264 tab_employee4(lG_RevocationREER_FERR) := '<G_RevocationREER_FERR4>' ||
3265 l_formatted_box || '</G_RevocationREER_FERR4>' || EOL;
3266 tab_employee5(lG_RevocationREER_FERR) := '<G_RevocationREER_FERR5>' ||
3267 l_formatted_box || '</G_RevocationREER_FERR5>' || EOL;
3268 end if;
3269 l_all_box_0 := FALSE;
3270 ELSE
3271 tab_employee(lG_RevocationREER_FERR) := NULL;
3272 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3273 then
3274 tab_employee1(lG_RevocationREER_FERR) := NULL;
3275 tab_employee2(lG_RevocationREER_FERR) := NULL;
3276 tab_employee3(lG_RevocationREER_FERR) := NULL;
3277 tab_employee4(lG_RevocationREER_FERR) := NULL;
3278 tab_employee5(lG_RevocationREER_FERR) := NULL;
3279 end if;
3280 END IF;
3281 hr_utility.trace('tab_employee(lG_RevocationREER_FERR) = ' ||
3282 tab_employee(lG_RevocationREER_FERR));
3283
3284 -- (Box H)
3285
3286 IF TO_NUMBER(l_rl2_box_h) > 9999999.99 THEN
3287 l_status := 'Failed';
3288 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3289 END IF;
3290
3291 IF l_rl2_box_h IS NOT NULL AND
3292 to_number(l_rl2_box_h) <> 0 THEN
3293
3294 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_h),l_format_mask)))
3295 INTO l_formatted_box
3296 FROM dual;
3297
3298 tab_employee(lH_AutreRevenu) := '<H_AutreRevenu>' ||
3299 l_formatted_box || '</H_AutreRevenu>' || EOL;
3300 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3301 then
3302 tab_employee1(lH_AutreRevenu) := '<H_AutreRevenu1>' ||
3303 l_formatted_box || '</H_AutreRevenu1>' || EOL;
3304 tab_employee2(lH_AutreRevenu) := '<H_AutreRevenu2>' ||
3305 l_formatted_box || '</H_AutreRevenu2>' || EOL;
3306 tab_employee3(lH_AutreRevenu) := '<H_AutreRevenu3>' ||
3307 l_formatted_box || '</H_AutreRevenu3>' || EOL;
3308 tab_employee4(lH_AutreRevenu) := '<H_AutreRevenu4>' ||
3309 l_formatted_box || '</H_AutreRevenu4>' || EOL;
3310 tab_employee5(lH_AutreRevenu) := '<H_AutreRevenu5>' ||
3311 l_formatted_box || '</H_AutreRevenu5>' || EOL;
3312 end if;
3313 l_all_box_0 := FALSE;
3314 ELSE
3315 tab_employee(lH_AutreRevenu) := NULL;
3316 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3317 then
3318 tab_employee1(lH_AutreRevenu) := NULL;
3319 tab_employee2(lH_AutreRevenu) := NULL;
3320 tab_employee3(lH_AutreRevenu) := NULL;
3321 tab_employee4(lH_AutreRevenu) := NULL;
3322 tab_employee5(lH_AutreRevenu) := NULL;
3323 end if;
3324 END IF;
3325
3326 hr_utility.trace('tab_employee(lH_AutreRevenu ) = ' ||
3327 tab_employee(lH_AutreRevenu ));
3328
3329 -- (Box I)
3330
3331 IF TO_NUMBER(l_rl2_box_i) > 9999999.99 THEN
3332 l_status := 'Failed';
3333 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3334 END IF;
3335
3336 IF l_rl2_box_i IS NOT NULL AND
3337 to_number(l_rl2_box_i) <> 0 THEN
3338
3339 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_i),l_format_mask)))
3340 INTO l_formatted_box
3341 FROM dual;
3342
3343 tab_employee(lI_DroitDeduction ) := '<I_DroitDeduction>' ||
3344 l_formatted_box || '</I_DroitDeduction>' || EOL;
3345 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3346 then
3347 tab_employee1(lI_DroitDeduction ) := '<I_DroitDeduction1>' ||
3348 l_formatted_box || '</I_DroitDeduction1>' || EOL;
3349 tab_employee2(lI_DroitDeduction ) := '<I_DroitDeduction2>' ||
3350 l_formatted_box || '</I_DroitDeduction2>' || EOL;
3351 tab_employee3(lI_DroitDeduction ) := '<I_DroitDeduction3>' ||
3352 l_formatted_box || '</I_DroitDeduction3>' || EOL;
3353 tab_employee4(lI_DroitDeduction ) := '<I_DroitDeduction4>' ||
3354 l_formatted_box || '</I_DroitDeduction4>' || EOL;
3355 tab_employee5(lI_DroitDeduction ) := '<I_DroitDeduction5>' ||
3356 l_formatted_box || '</I_DroitDeduction5>' || EOL;
3357 end if;
3358 l_all_box_0 := FALSE;
3359 ELSE
3360 tab_employee(lI_DroitDeduction ) := NULL;
3361 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3362 then
3363 tab_employee1(lI_DroitDeduction ) := NULL;
3364 tab_employee2(lI_DroitDeduction ) := NULL;
3365 tab_employee3(lI_DroitDeduction ) := NULL;
3366 tab_employee4(lI_DroitDeduction ) := NULL;
3367 tab_employee5(lI_DroitDeduction ) := NULL;
3368 end if;
3369 END IF;
3370 hr_utility.trace('tab_employee(lI_DroitDeduction ) = ' ||
3371 tab_employee(lI_DroitDeduction ));
3372
3373 -- (Box J)
3374
3375 IF TO_NUMBER(l_rl2_box_j) > 9999999.99 THEN
3376 l_status := 'Failed';
3377 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3378 END IF;
3379
3380 IF l_rl2_box_j IS NOT NULL AND
3381 to_number(l_rl2_box_j) <> 0 THEN
3382
3383 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_j),l_format_mask)))
3384 INTO l_formatted_box
3385 FROM dual;
3386
3387 tab_employee(lJ_ImpotQueRetenuSource ) := '<J_ImpotQueRetenuSource>' ||
3388 l_formatted_box || '</J_ImpotQueRetenuSource>' || EOL;
3389 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3390 then
3391 tab_employee1(lJ_ImpotQueRetenuSource ) := '<J_ImpotQueRetenuSource1>' ||
3392 l_formatted_box || '</J_ImpotQueRetenuSource1>' || EOL;
3393 tab_employee2(lJ_ImpotQueRetenuSource ) := '<J_ImpotQueRetenuSource2>' ||
3394 l_formatted_box || '</J_ImpotQueRetenuSource2>' || EOL;
3395 tab_employee3(lJ_ImpotQueRetenuSource ) := '<J_ImpotQueRetenuSource3>' ||
3396 l_formatted_box || '</J_ImpotQueRetenuSource3>' || EOL;
3397 tab_employee4(lJ_ImpotQueRetenuSource ) := '<J_ImpotQueRetenuSource4>' ||
3398 l_formatted_box || '</J_ImpotQueRetenuSource4>' || EOL;
3399 tab_employee5(lJ_ImpotQueRetenuSource ) := '<J_ImpotQueRetenuSource5>' ||
3400 l_formatted_box || '</J_ImpotQueRetenuSource5>' || EOL;
3401 end if;
3402 l_all_box_0 := FALSE;
3403 ELSE
3404 tab_employee(lJ_ImpotQueRetenuSource ) := NULL;
3405 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3406 then
3407 tab_employee1(lJ_ImpotQueRetenuSource ) := NULL;
3408 tab_employee2(lJ_ImpotQueRetenuSource ) := NULL;
3409 tab_employee3(lJ_ImpotQueRetenuSource ) := NULL;
3410 tab_employee4(lJ_ImpotQueRetenuSource ) := NULL;
3411 tab_employee5(lJ_ImpotQueRetenuSource ) := NULL;
3412 end if;
3413 END IF;
3414 hr_utility.trace('tab_employee(lJ_ImpotQueRetenuSource ) = ' ||
3415 tab_employee(lJ_ImpotQueRetenuSource ));
3416
3417 -- (Box K)
3418
3419 IF TO_NUMBER(l_rl2_box_k) > 9999999.99 THEN
3420 l_status := 'Failed';
3421 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3422 END IF;
3423
3424 IF l_rl2_box_k IS NOT NULL AND
3425 to_number(l_rl2_box_k) <> 0 THEN
3426
3427 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_k),l_format_mask)))
3428 INTO l_formatted_box
3429 FROM dual;
3430
3431 tab_employee(lK_RevenuApresDeces ) := '<K_RevenuApresDeces>' ||
3432 l_formatted_box || '</K_RevenuApresDeces>' || EOL;
3433 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3434 then
3435 tab_employee1(lK_RevenuApresDeces ) := '<K_RevenuApresDeces1>' ||
3436 l_formatted_box || '</K_RevenuApresDeces1>' || EOL;
3437 tab_employee2(lK_RevenuApresDeces ) := '<K_RevenuApresDeces2>' ||
3438 l_formatted_box || '</K_RevenuApresDeces2>' || EOL;
3439 tab_employee3(lK_RevenuApresDeces ) := '<K_RevenuApresDeces3>' ||
3440 l_formatted_box || '</K_RevenuApresDeces3>' || EOL;
3441 tab_employee4(lK_RevenuApresDeces ) := '<K_RevenuApresDeces4>' ||
3442 l_formatted_box || '</K_RevenuApresDeces4>' || EOL;
3443 tab_employee5(lK_RevenuApresDeces ) := '<K_RevenuApresDeces5>' ||
3444 l_formatted_box || '</K_RevenuApresDeces5>' || EOL;
3445 end if;
3446 l_all_box_0 := FALSE;
3447 ELSE
3448 tab_employee(lK_RevenuApresDeces ) := NULL;
3449 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3450 then
3451 tab_employee1(lK_RevenuApresDeces ) := NULL;
3452 tab_employee2(lK_RevenuApresDeces ) := NULL;
3453 tab_employee3(lK_RevenuApresDeces ) := NULL;
3454 tab_employee4(lK_RevenuApresDeces ) := NULL;
3455 tab_employee5(lK_RevenuApresDeces ) := NULL;
3456 end if;
3457 END IF;
3458 hr_utility.trace('tab_employee(lK_RevenuApresDeces ) = ' ||
3459 tab_employee(lK_RevenuApresDeces ));
3460
3461 -- (Box L)
3462
3463 IF TO_NUMBER(l_rl2_box_l) > 9999999.99 THEN
3464 l_status := 'Failed';
3465 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3466 END IF;
3467
3468 IF l_rl2_box_l IS NOT NULL AND
3469 to_number(l_rl2_box_l) <> 0 THEN
3470
3471 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_l),l_format_mask)))
3472 INTO l_formatted_box
3473 FROM dual;
3474
3475 tab_employee(lL_RetraitREEP ) := '<L_RetraitREEP>' ||
3476 l_formatted_box || '</L_RetraitREEP>' || EOL;
3477 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3478 then
3479 tab_employee1(lL_RetraitREEP ) := '<L_RetraitREEP1>' ||
3480 l_formatted_box || '</L_RetraitREEP1>' || EOL;
3481 tab_employee2(lL_RetraitREEP ) := '<L_RetraitREEP2>' ||
3482 l_formatted_box || '</L_RetraitREEP2>' || EOL;
3483 tab_employee3(lL_RetraitREEP ) := '<L_RetraitREEP3>' ||
3484 l_formatted_box || '</L_RetraitREEP3>' || EOL;
3485 tab_employee4(lL_RetraitREEP ) := '<L_RetraitREEP4>' ||
3486 l_formatted_box || '</L_RetraitREEP4>' || EOL;
3487 tab_employee5(lL_RetraitREEP ) := '<L_RetraitREEP5>' ||
3488 l_formatted_box || '</L_RetraitREEP5>' || EOL;
3489 end if;
3490 l_all_box_0 := FALSE;
3491 ELSE
3492 tab_employee(lL_RetraitREEP ) := NULL;
3493 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3494 then
3495 tab_employee1(lL_RetraitREEP ) := NULL;
3496 tab_employee2(lL_RetraitREEP ) := NULL;
3497 tab_employee3(lL_RetraitREEP ) := NULL;
3498 tab_employee4(lL_RetraitREEP ) := NULL;
3499 tab_employee5(lL_RetraitREEP ) := NULL;
3500 end if;
3501 END IF;
3502 hr_utility.trace('tab_employee(lL_RetraitREEP ) = ' ||
3503 tab_employee(lL_RetraitREEP ));
3504
3505 -- (Box M)
3506
3507 IF TO_NUMBER(l_rl2_box_m) > 9999999.99 THEN
3508 l_status := 'Failed';
3509 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3510 END IF;
3511
3512 IF l_rl2_box_m IS NOT NULL AND
3513 to_number(l_rl2_box_m) <> 0 THEN
3514
3515 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_m),l_format_mask)))
3516 INTO l_formatted_box
3517 FROM dual;
3518
3519 tab_employee(lM_LibereImpot) := '<M_LibereImpot>' ||
3520 l_formatted_box || '</M_LibereImpot>' || EOL;
3521 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3522 then
3523 tab_employee1(lM_LibereImpot) := '<M_LibereImpot1>' ||
3524 l_formatted_box || '</M_LibereImpot1>' || EOL;
3525 tab_employee2(lM_LibereImpot) := '<M_LibereImpot2>' ||
3526 l_formatted_box || '</M_LibereImpot2>' || EOL;
3527 tab_employee3(lM_LibereImpot) := '<M_LibereImpot3>' ||
3528 l_formatted_box || '</M_LibereImpot3>' || EOL;
3529 tab_employee4(lM_LibereImpot) := '<M_LibereImpot4>' ||
3530 l_formatted_box || '</M_LibereImpot4>' || EOL;
3531 tab_employee5(lM_LibereImpot) := '<M_LibereImpot5>' ||
3532 l_formatted_box || '</M_LibereImpot5>' || EOL;
3533 end if;
3534 l_all_box_0 := FALSE;
3535 ELSE
3536 tab_employee(lM_LibereImpot) := NULL;
3537 if p_rep_type = 'RL2PAPERPDF' or p_rep_type='PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3538 then
3539 tab_employee1(lM_LibereImpot) := NULL;
3540 tab_employee2(lM_LibereImpot) := NULL;
3541 tab_employee3(lM_LibereImpot) := NULL;
3542 tab_employee4(lM_LibereImpot) := NULL;
3543 tab_employee5(lM_LibereImpot) := NULL;
3544 end if;
3545 END IF;
3546 hr_utility.trace('tab_employee(lM_LibereImpot) = ' ||
3547 tab_employee(lM_LibereImpot));
3548
3549 -- (Box N)
3550 -- Bug 5569097 Fix.
3551
3552 IF l_rl2_box_n IS NOT NULL THEN
3553
3554 if p_rep_type not in ('RL2PAPERPDF','PAYCARL2AMPDF','PAYCARL2CLPDF') then
3555
3556 tab_employee(lN_NASConjoint) := '<N_NASConjoint>' ||
3557 l_rl2_box_n || '</N_NASConjoint>' || EOL;
3558 else
3559 tab_employee(lN_NASConjoint) := '<N_NASConjoint1>' ||
3560 substr(l_rl2_box_n,1,3) || '</N_NASConjoint1>' || EOL;
3561 tab_employee(lN_NASConjoint1) := '<N_NASConjoint2>' ||
3562 substr(l_rl2_box_n,4,3) || '</N_NASConjoint2>' || EOL;
3563 tab_employee(lN_NASConjoint2) := '<N_NASConjoint3>' ||
3564 substr(l_rl2_box_n,7,3) || '</N_NASConjoint3>' || EOL;
3565 tab_employee1(lN_NASConjoint) := '<N_NASConjoint21>' ||
3566 substr(l_rl2_box_n,1,3) || '</N_NASConjoint21>' || EOL;
3567 tab_employee1(lN_NASConjoint1) := '<N_NASConjoint22>' ||
3568 substr(l_rl2_box_n,4,3) || '</N_NASConjoint22>' || EOL;
3569 tab_employee1(lN_NASConjoint2) := '<N_NASConjoint23>' ||
3570 substr(l_rl2_box_n,7,3) || '</N_NASConjoint23>' || EOL;
3571 tab_employee2(lN_NASConjoint) := '<N_NASConjoint31>' ||
3572 substr(l_rl2_box_n,1,3) || '</N_NASConjoint31>' || EOL;
3573 tab_employee2(lN_NASConjoint1) := '<N_NASConjoint32>' ||
3574 substr(l_rl2_box_n,4,3) || '</N_NASConjoint32>' || EOL;
3575 tab_employee2(lN_NASConjoint2) := '<N_NASConjoint33>' ||
3576 substr(l_rl2_box_n,7,3) || '</N_NASConjoint33>' || EOL;
3577 tab_employee3(lN_NASConjoint) := '<N_NASConjoint41>' ||
3578 substr(l_rl2_box_n,1,3) || '</N_NASConjoint41>' || EOL;
3579 tab_employee3(lN_NASConjoint1) := '<N_NASConjoint42>' ||
3580 substr(l_rl2_box_n,4,3) || '</N_NASConjoint42>' || EOL;
3581 tab_employee3(lN_NASConjoint2) := '<N_NASConjoint43>' ||
3582 substr(l_rl2_box_n,7,3) || '</N_NASConjoint43>' || EOL;
3583 tab_employee4(lN_NASConjoint) := '<N_NASConjoint51>' ||
3584 substr(l_rl2_box_n,1,3) || '</N_NASConjoint51>' || EOL;
3585 tab_employee4(lN_NASConjoint1) := '<N_NASConjoint52>' ||
3586 substr(l_rl2_box_n,4,3) || '</N_NASConjoint52>' || EOL;
3587 tab_employee4(lN_NASConjoint2) := '<N_NASConjoint53>' ||
3588 substr(l_rl2_box_n,7,3) || '</N_NASConjoint53>' || EOL;
3589 tab_employee5(lN_NASConjoint) := '<N_NASConjoint61>' ||
3590 substr(l_rl2_box_n,1,3) || '</N_NASConjoint61>' || EOL;
3591 tab_employee5(lN_NASConjoint1) := '<N_NASConjoint62>' ||
3592 substr(l_rl2_box_n,4,3) || '</N_NASConjoint62>' || EOL;
3593 tab_employee5(lN_NASConjoint2) := '<N_NASConjoint63>' ||
3594 substr(l_rl2_box_n,7,3) || '</N_NASConjoint63>' || EOL;
3595 end if;
3596 ELSE
3597 if p_rep_type not in ('RL2PAPERPDF','PAYCARL2AMPDF','PAYCARL2CLPDF') then
3598 tab_employee(lN_NASConjoint) := NULL;
3599 else
3600 tab_employee(lN_NASConjoint) := NULL;
3601 tab_employee(lN_NASConjoint1) := NULL;
3602 tab_employee(lN_NASConjoint2) := NULL;
3603 tab_employee1(lN_NASConjoint) := NULL;
3604 tab_employee1(lN_NASConjoint1) := NULL;
3605 tab_employee1(lN_NASConjoint2) := NULL;
3606 tab_employee2(lN_NASConjoint) := NULL;
3607 tab_employee2(lN_NASConjoint1) := NULL;
3608 tab_employee2(lN_NASConjoint2) := NULL;
3609 tab_employee3(lN_NASConjoint) := NULL;
3610 tab_employee3(lN_NASConjoint1) := NULL;
3611 tab_employee3(lN_NASConjoint2) := NULL;
3612 tab_employee4(lN_NASConjoint) := NULL;
3613 tab_employee4(lN_NASConjoint1) := NULL;
3614 tab_employee4(lN_NASConjoint2) := NULL;
3615 tab_employee5(lN_NASConjoint) := NULL;
3616 tab_employee5(lN_NASConjoint1) := NULL;
3617 tab_employee5(lN_NASConjoint2) := NULL;
3618 end if;
3619 END IF;
3620 hr_utility.trace('tab_employee(lN_NASConjoint) = ' ||
3621 tab_employee(lN_NASConjoint));
3622
3623 -- Summ (Box O)
3624
3625 IF TO_NUMBER(l_rl2_box_o) > 999999999.99 THEN
3626 l_status := 'Failed';
3627 l_msg_code := 'AMT_GREATER_THAN_RANGE';
3628 END IF;
3629
3630 IF l_rl2_box_o IS NOT NULL AND
3631 to_number(l_rl2_box_o) <> 0 THEN
3632
3633 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_o),l_format_mask)))
3634 INTO l_formatted_box
3635 FROM dual;
3636
3637 tab_employee(lO_RetraitRAP ) := '<O_RetraitRAP>' ||
3638 l_formatted_box || '</O_RetraitRAP>' || EOL;
3639 if p_rep_type = 'RL2PAPERPDF' or p_rep_type = 'PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3640 then
3641 tab_employee1(lO_RetraitRAP ) := '<O_RetraitRAP1>' ||
3642 l_formatted_box || '</O_RetraitRAP1>' || EOL;
3643 tab_employee2(lO_RetraitRAP ) := '<O_RetraitRAP2>' ||
3644 l_formatted_box || '</O_RetraitRAP2>' || EOL;
3645 tab_employee3(lO_RetraitRAP ) := '<O_RetraitRAP3>' ||
3646 l_formatted_box || '</O_RetraitRAP3>' || EOL;
3647 tab_employee4(lO_RetraitRAP ) := '<O_RetraitRAP4>' ||
3648 l_formatted_box || '</O_RetraitRAP4>' || EOL;
3649 tab_employee5(lO_RetraitRAP ) := '<O_RetraitRAP5>' ||
3650 l_formatted_box || '</O_RetraitRAP5>' || EOL;
3651 end if;
3652 l_all_box_0 := FALSE;
3653 ELSE
3654 tab_employee(lO_RetraitRAP ) := NULL;
3655 if p_rep_type = 'RL2PAPERPDF' or p_rep_type = 'PAYCARL2AMPDF' or p_rep_type='PAYCARL2CLPDF'
3656 then
3657 tab_employee1(lO_RetraitRAP ) := NULL;
3658 tab_employee2(lO_RetraitRAP ) := NULL;
3659 tab_employee3(lO_RetraitRAP ) := NULL;
3660 tab_employee4(lO_RetraitRAP ) := NULL;
3661 tab_employee5(lO_RetraitRAP ) := NULL;
3662 end if;
3663 END IF;
3664 hr_utility.trace('Value of Box O');
3665 hr_utility.trace('tab_employee(lO_RetraitRAP ) = ' ||
3666 tab_employee(lO_RetraitRAP ));
3667
3668 if p_rep_type = 'RL2PAPERPDF' then
3669 tab_employee(lCode_dereleve) := '<Code_De_Releve>' ||
3670 'R' || '</Code_De_Releve>' || EOL;
3671 tab_employee1(lCode_dereleve) := '<Code_De_Releve1>' ||
3672 'R' || '</Code_De_Releve1>' || EOL;
3673 tab_employee2(lCode_dereleve) := '<Code_De_Releve2>' ||
3674 'R' || '</Code_De_Releve2>' || EOL;
3675 tab_employee3(lCode_dereleve) := '<Code_De_Releve3>' ||
3676 'R' || '</Code_De_Releve3>' || EOL;
3677 tab_employee4(lCode_dereleve) := '<Code_De_Releve4>' ||
3678 'R' || '</Code_De_Releve4>' || EOL;
3679 tab_employee5(lCode_dereleve) := '<Code_De_Releve5>' ||
3680 'R' || '</Code_De_Releve5>' || EOL;
3681 end if;
3682 /*Added for 5046010 to show A for RL2 Amendment PDF*/
3683 if p_rep_type = 'PAYCARL2AMPDF' then
3684 tab_employee(lCode_dereleve) := '<Code_De_Releve>' ||
3685 'A' || '</Code_De_Releve>' || EOL;
3686 tab_employee1(lCode_dereleve) := '<Code_De_Releve1>' ||
3687 'A' || '</Code_De_Releve1>' || EOL;
3688 tab_employee2(lCode_dereleve) := '<Code_De_Releve2>' ||
3689 'A' || '</Code_De_Releve2>' || EOL;
3690 tab_employee3(lCode_dereleve) := '<Code_De_Releve3>' ||
3691 'A' || '</Code_De_Releve3>' || EOL;
3692 tab_employee4(lCode_dereleve) := '<Code_De_Releve4>' ||
3693 'A' || '</Code_De_Releve4>' || EOL;
3694 tab_employee5(lCode_dereleve) := '<Code_De_Releve5>' ||
3695 'A' || '</Code_De_Releve5>' || EOL;
3696 end if;
3697 /*End 5046010*/
3698 /*Added for 10324391 to show D for RL2 Cancel PDF*/
3699 if p_rep_type='PAYCARL2CLPDF' then
3700 tab_employee(lCode_dereleve) := '<Code_De_Releve>' ||
3701 'D' || '</Code_De_Releve>' || EOL;
3702 tab_employee1(lCode_dereleve) := '<Code_De_Releve1>' ||
3703 'D' || '</Code_De_Releve1>' || EOL;
3704 tab_employee2(lCode_dereleve) := '<Code_De_Releve2>' ||
3705 'D' || '</Code_De_Releve2>' || EOL;
3706 tab_employee3(lCode_dereleve) := '<Code_De_Releve3>' ||
3707 'D' || '</Code_De_Releve3>' || EOL;
3708 tab_employee4(lCode_dereleve) := '<Code_De_Releve4>' ||
3709 'D' || '</Code_De_Releve4>' || EOL;
3710 tab_employee5(lCode_dereleve) := '<Code_De_Releve5>' ||
3711 'D' || '</Code_De_Releve5>' || EOL;
3712 end if;
3713 /*End 10324391*/
3714 -- Negative Balance Exists
3715
3716 IF l_negative_balance_flag = 'Y' THEN
3717 l_negative_box := 'Y';
3718 l_status := 'Failed';
3719 l_msg_code := 'NEG';
3720 END IF;
3721
3722 IF l_all_box_0 and to_number(p_taxation_year) < 2011 THEN -- Bug 13633422
3723 l_status := 'Failed';
3724 l_msg_code := 'ALL_BOXES_ZERO';
3725 END IF;
3726
3727 -- (Provenance1)
3728 hr_utility.trace('The checking for Provenance value ');
3729 hr_utility.trace('The value of Archiver Assignment Action Id '||p_arch_action_id);
3730 hr_utility.trace('The Value of Assignment Id '||p_asg_id);
3731
3732 hr_utility.trace('The value Of Provenenace : '|| l_rl2_source_of_income);
3733 IF l_rl2_source_of_income IS NOT NULL THEN
3734 -- <Provenance1> changed to <Provenance> for bug 13029973
3735 /* 14701748 YE12P211I RL2 ELECTRONIC INTERFACE CHANGES FOR 2012 */
3736 IF ( p_rep_type ='RL2_XML_MAG' and l_rl2_source_of_income = 'AUTRE' ) THEN
3737 tab_employee(lProvenance1) := null;
3738 else
3739 tab_employee(lProvenance1) := '<Provenance>' ||
3740 convert_special_char(l_rl2_source_of_income) || '</Provenance>' || EOL;
3741 end if;
3742 /* All pdf templates using tag <Provenance1 to display source of income value in
3743 in first slip, bug 12973513*/
3744 tab_employee(lProvenance ) := '<Provenance1>' ||
3745 convert_special_char(l_rl2_source_of_income) || '</Provenance1>' || EOL;
3746 if p_rep_type = 'RL2PAPERPDF' or p_rep_type = 'PAYCARL2AMPDF' or p_rep_type = 'PAYCARL2CLPDF'
3747 then
3748 tab_employee1(lProvenance1 ) := '<Provenance11>' ||
3749 convert_special_char(l_rl2_source_of_income) || '</Provenance11>' || EOL;
3750 tab_employee2(lProvenance1 ) := '<Provenance12>' ||
3751 convert_special_char(l_rl2_source_of_income) || '</Provenance12>' || EOL;
3752 tab_employee3(lProvenance1 ) := '<Provenance13>' ||
3753 convert_special_char(l_rl2_source_of_income) || '</Provenance13>' || EOL;
3754 tab_employee4(lProvenance1 ) := '<Provenance14>' ||
3755 convert_special_char(l_rl2_source_of_income) || '</Provenance14>' || EOL;
3756 tab_employee5(lProvenance1 ) := '<Provenance15>' ||
3757 convert_special_char(l_rl2_source_of_income) || '</Provenance15>' || EOL;
3758 end if;
3759 ELSE
3760 /* Commented for Bug 6732992
3761 l_status := 'Failed';
3762 l_msg_code := 'MISSING_SOURCE_OF_INCOME';
3763 */
3764 tab_employee(lProvenance1) := NULL;
3765 tab_employee(lProvenance) := NULL; -- bug 12973513
3766 if p_rep_type = 'RL2PAPERPDF' or p_rep_type = 'PAYCARL2AMPDF' or p_rep_type = 'PAYCARL2CLPDF'
3767 then
3768 tab_employee1(lProvenance1) := NULL;
3769 tab_employee2(lProvenance1) := NULL;
3770 tab_employee3(lProvenance1) := NULL;
3771 tab_employee4(lProvenance1) := NULL;
3772 tab_employee5(lProvenance1) := NULL;
3773 end if;
3774 END IF;
3775 hr_utility.trace('tab_employee(lProvenance1) = ' ||
3776 tab_employee(lProvenance1));
3777
3778 /* For bug 8888411 */
3779 OPEN c_rl2_src_income(replace(l_rl2_source_of_income,'AUTRE','OTHER'), p_taxation_year);
3780 FETCH c_rl2_src_income into l_meaning;
3781 IF c_rl2_src_income%notfound then
3782 l_status := 'Failed';
3783 l_msg_code := 'INVALID_SOURCE_OF_INCOME';
3784 END IF;
3785 CLOSE c_rl2_src_income;
3786 /* End 8888411 */
3787
3788 OPEN cur_get_meaning(l_msg_code);
3789 FETCH cur_get_meaning
3790 INTO l_meaning;
3791 CLOSE cur_get_meaning;
3792
3793 -- store_further_information is called for bug 13545861
3794 -- for populating Further Information Element data.
3795 store_further_information (p_arch_action_id);
3796
3797 for l_j in 1..g_further_info_list.count
3798 loop
3799 get_further_information (l_j,
3800 l_code,
3801 l_amount);
3802 l_further_code(l_j):=l_code;
3803 l_further_amount(l_j):=l_amount;
3804
3805 if l_amount is not null and l_amount <> '0' then
3806 num_further_info:=num_further_info+1;
3807 end if;
3808 end loop;
3809
3810 /* /* 14701748: YE12P2:11I: RL2 ELECTRONIC INTERFACE CHANGES FOR 2012
3811
3812
3813 further_info_details:= null;
3814
3815 IF p_rep_type NOT IN ('RL2PAPERPDF','PAYCARL2AMPDF','PAYCARL2CLPDF') THEN
3816 for l_i in 1..g_further_info_list.count
3817 loop
3818 if l_further_amount(l_i) is not null then
3819 if(l_further_code(l_i) <> '201' and to_number(l_further_amount(l_i)) <> 0) then
3820 SELECT ltrim(rtrim(to_char(l_further_amount(l_i),l_format_mask)))
3821 INTO l_formatted_box
3822 FROM dual;
3823 further_info_details:= further_info_details ||
3824 '<CaseRensCompl>' ||EOL ||
3825 '<CodeRensCompl>' || l_further_code(l_i) || '</CodeRensCompl>' ||EOL ||
3826 '<DonneeRensCompl>' || l_formatted_box || '</DonneeRensCompl>' ||EOL ||
3827 '</CaseRensCompl>' ||EOL ;
3828
3829 elsif l_further_code(l_i) = '201' then
3830 further_info_details:= further_info_details ||
3831 '<CaseRensCompl>' ||EOL ||
3832 '<CodeRensCompl>' || l_further_code(l_i) || '</CodeRensCompl>' ||EOL ||
3833 '<DonneeRensCompl>' || l_further_amount(l_i) || '</DonneeRensCompl>' ||EOL ||
3834 '</CaseRensCompl>' ||EOL ;
3835 end if;
3836 end if;
3837 end loop;
3838 tab_employee(CaseRensCompl) := further_info_details;
3839 end if;
3840 */
3841
3842 /* Bug #4747251 Fix */
3843 IF l_status = 'Failed' OR p_rep_type in ('RL2PAPERPDF','PAYCARL2AMPDF','PAYCARL2CLPDF') THEN
3844
3845 tab_employee(lBoxA_UnregisterdPlan) := NULL;
3846 tab_employee(lDesg_BenefitExcAmt ) := NULL;
3847 tab_employee(lBoxB_DesgBenefitTrnsAmt) := NULL;
3848 tab_employee(lBoxExcessAmt) := NULL;
3849 tab_employee(lAmount_Transferred) := NULL;
3850 tab_employee(lBoxC_SinglePayAccured) := NULL;
3851 tab_employee(lBoxC_SinglePayAccUnreg) := NULL;
3852 tab_employee(lBoxC_ExcessAmtSinPayTrans) := NULL;
3853 /* 14701748 */
3854 --tab_employee(CaseRensCompl) := NULL;
3855 l_other_details := NULL;
3856 for l_i in 1..g_further_info_list.count
3857 loop
3858 if l_further_amount(l_i) is not null then
3859 if(l_further_code(l_i) <> '201' and to_number(l_further_amount(l_i)) <> 0) then
3860 SELECT ltrim(rtrim(to_char(l_further_amount(l_i),l_format_mask)))
3861 INTO l_formatted_box
3862 FROM dual;
3863
3864 select decode(l_further_code(l_i),'235','F_2','210','F_3',replace(l_further_code(l_i),'-','_')) -- bug 14701748
3865 into l_formatted_code
3866 from dual;
3867 if l_formatted_box < 0 then
3868 l_negative_box := 'Y';
3869 end if;
3870 l_other_details:=l_other_details||'<'||l_formatted_code||'>'
3871 ||l_formatted_box||'</'||l_formatted_code||'>'||EOL;
3872
3873 elsif l_further_code(l_i) = '201' then
3874 l_other_details:=l_other_details||'<F_1>'
3875 ||l_further_amount(l_i)||'</F_1>'||EOL;
3876 end if;
3877 end if;
3878 end loop;
3879 ELSE
3880 tab_employee(lBoxA_UnregisterdPlan) := NULL;
3881 tab_employee(lDesg_BenefitExcAmt ) := NULL;
3882 tab_employee(lBoxB_DesgBenefitTrnsAmt) := NULL;
3883 tab_employee(lBoxExcessAmt) := NULL;
3884 tab_employee(lAmount_Transferred) := NULL;
3885 tab_employee(lBoxC_SinglePayAccured) := NULL;
3886 tab_employee(lBoxC_SinglePayAccUnreg) := NULL;
3887 tab_employee(lBoxC_ExcessAmtSinPayTrans) := NULL;
3888 END IF;
3889
3890 IF l_status = 'Failed' THEN
3891 tab_employee(lErrorDetails) := '<ErrorDetails>' ||
3892 convert_special_char(l_meaning) || '</ErrorDetails>' || EOL;
3893 ELSE
3894 tab_employee(lErrorDetails) := NULL;
3895 END IF;
3896
3897 IF p_rep_type NOT IN ('RL2PAPERPDF','PAYCARL2AMPDF','PAYCARL2CLPDF') THEN
3898 /* 14701748 */
3899 further_info_details:= null;
3900 IF l_status = 'Failed' THEN --For Failed employees further info should be displayed in single row. 'l_other_details' should be displayed
3901 /* Bug 15972457 */
3902 tab_employee(lNoReleve) := '<NoReleve>' || convert_special_char(l_rl2_slip_number) ||
3903 '</NoReleve>' || EOL;
3904 /* Bug 15972457 */
3905 l_final_xml_string :=
3906 '<' || l_status || '>' || EOL ||
3907 '<R>' || EOL ||
3908 tab_employee(lAnnee) ||
3909 tab_employee(lNoReleve) || '<Beneficiaire>' || EOL ||
3910 tab_employee(lNAS) ||
3911 tab_employee(lNo) ||
3912 tab_employee(lNomFamille) ||
3913 tab_employee(lPrenom) ||
3914 tab_employee(lInitiale) || l_addr_begin_tag || EOL ||
3915 tab_employee(lLigne1) ||
3916 tab_employee(lLigne2) ||
3917 tab_employee(lVille) ||
3918 tab_employee(lProvince) ||
3919 tab_employee(lCodePostal) ||
3920 l_addr_end_tag || EOL || '</Beneficiaire>' || EOL ||
3921 '<Montants>' || EOL ||
3922 tab_employee(lA_PrestRPA_RPNA) ||
3923 tab_employee(lB_PrestREER_FERR_RPDB) ||
3924 tab_employee(lC_AutrePaiement) ||
3925 tab_employee(lD_RembPrimeConjoint) ||
3926 tab_employee(lE_PrestDeces) ||
3927 tab_employee(lF_RembCotisInutilise) ||
3928 tab_employee(lG_RevocationREER_FERR) ||
3929 tab_employee(lH_AutreRevenu) ||
3930 tab_employee(lI_DroitDeduction ) ||
3931 tab_employee(lJ_ImpotQueRetenuSource) ||
3932 tab_employee(lK_RevenuApresDeces) ||
3933 tab_employee(lL_RetraitREEP) ||
3934 tab_employee(lM_LibereImpot) ||
3935 tab_employee(lN_NASConjoint) ||
3936 tab_employee(lO_RetraitRAP) ||
3937 tab_employee(lProvenance1) ||
3938 /*tab_employee(lBoxA_UnregisterdPlan) ||
3939 tab_employee(lDesg_BenefitExcAmt) ||
3940 tab_employee(lBoxB_DesgBenefitTrnsAmt) ||
3941 tab_employee(lBoxExcessAmt) ||
3942 tab_employee(lAmount_Transferred) ||
3943 tab_employee(lBoxC_SinglePayAccured) ||
3944 tab_employee(lBoxC_SinglePayAccUnreg) ||
3945 tab_employee(lBoxC_ExcessAmtSinPayTrans) ||*/
3946 l_other_details||
3947 tab_employee(lErrorDetails)||
3948 '</Montants>' || EOL ||
3949 '</R>' || EOL ||
3950 '</' || l_status || '>' ;
3951
3952 else -- For success employees
3953
3954 counter := 1;
3955 counter1 := 1;
3956 for l_i in 1..g_further_info_list.count
3957 loop
3958 if l_further_amount(l_i) is not null then
3959 if ( counter <= 4 ) then
3960 if(l_further_code(l_i) <> '201' and to_number(l_further_amount(l_i)) <> 0) then
3961 SELECT ltrim(rtrim(to_char(l_further_amount(l_i),l_format_mask)))
3962 INTO l_formatted_box
3963 FROM dual;
3964
3965 further_info_details:= further_info_details || '<CaseRensCompl>' ||EOL ||
3966 '<CodeRensCompl>' || l_further_code(l_i) || '</CodeRensCompl>' ||EOL ||
3967 '<DonneeRensCompl>' || l_formatted_box || '</DonneeRensCompl>' ||EOL ||
3968 '</CaseRensCompl>' ||EOL ;
3969 counter := counter+1;
3970 counter1 := l_i+1;
3971 elsif l_further_code(l_i) = '201' then
3972 further_info_details:= further_info_details || '<CaseRensCompl>' ||EOL ||
3973 '<CodeRensCompl>' || l_further_code(l_i) || '</CodeRensCompl>' ||EOL ||
3974 '<DonneeRensCompl>' || l_further_amount(l_i) || '</DonneeRensCompl>' ||EOL ||
3975 '</CaseRensCompl>' ||EOL ;
3976
3977 counter := counter+1;
3978 counter1 := l_i+1;
3979 end if;
3980 else -- ( counter <= 4 )
3981 if((l_further_code(l_i) <> '201' and to_number(l_further_amount(l_i)) <> 0)
3982 OR (l_further_code(l_i) = '201' )) then
3983 counter := counter+1;
3984 end if;
3985 end if;
3986 end if;
3987 end loop;
3988 l_final_xml_string :=
3989 '<' || l_status || '>' || EOL ||
3990 '<R>' || EOL ||
3991 tab_employee(lAnnee) ||
3992 tab_employee(lNoReleve) || '<Beneficiaire>' || EOL ||
3993 tab_employee(lNAS) ||
3994 tab_employee(lNo) ||
3995 tab_employee(lNomFamille) ||
3996 tab_employee(lPrenom) ||
3997 tab_employee(lInitiale) || l_addr_begin_tag || EOL ||
3998 tab_employee(lLigne1) ||
3999 tab_employee(lLigne2) ||
4000 tab_employee(lVille) ||
4001 tab_employee(lProvince) ||
4002 tab_employee(lCodePostal) ||
4003 l_addr_end_tag || EOL || '</Beneficiaire>' || EOL ||
4004 '<Montants>' || EOL ||
4005 tab_employee(lA_PrestRPA_RPNA) ||
4006 tab_employee(lB_PrestREER_FERR_RPDB) ||
4007 tab_employee(lC_AutrePaiement) ||
4008 tab_employee(lD_RembPrimeConjoint) ||
4009 tab_employee(lE_PrestDeces) ||
4010 tab_employee(lF_RembCotisInutilise) ||
4011 tab_employee(lG_RevocationREER_FERR) ||
4012 tab_employee(lH_AutreRevenu) ||
4013 tab_employee(lI_DroitDeduction ) ||
4014 tab_employee(lJ_ImpotQueRetenuSource) ||
4015 tab_employee(lK_RevenuApresDeces) ||
4016 tab_employee(lL_RetraitREEP) ||
4017 tab_employee(lM_LibereImpot) ||
4018 tab_employee(lN_NASConjoint) ||
4019 tab_employee(lO_RetraitRAP) ||
4020 tab_employee(lProvenance1) ||
4021 /*tab_employee(lBoxA_UnregisterdPlan) ||
4022 tab_employee(lDesg_BenefitExcAmt) ||
4023 tab_employee(lBoxB_DesgBenefitTrnsAmt) ||
4024 tab_employee(lBoxExcessAmt) ||
4025 tab_employee(lAmount_Transferred) ||
4026 tab_employee(lBoxC_SinglePayAccured) ||
4027 tab_employee(lBoxC_SinglePayAccUnreg) ||
4028 tab_employee(lBoxC_ExcessAmtSinPayTrans) || */
4029 '</Montants>' || EOL ||
4030 further_info_details || EOL ||
4031 '</R>' || EOL ||
4032 '</' || l_status || '>' ;
4033
4034 if ( g_further_info_list.count >= counter1 ) then
4035 further_info_details:= null;
4036 count1 :=0;
4037 slip_count :=2;
4038 for l_i in counter1..g_further_info_list.count
4039 loop
4040 if l_further_amount(l_i) is not null then
4041 if(l_further_code(l_i) <> '201' and to_number(l_further_amount(l_i)) <> 0) then
4042 SELECT ltrim(rtrim(to_char(l_further_amount(l_i),l_format_mask)))
4043 INTO l_formatted_box
4044 FROM dual;
4045
4046 further_info_details:= further_info_details || '<CaseRensCompl>' ||EOL ||
4047 '<CodeRensCompl>' || l_further_code(l_i) || '</CodeRensCompl>' ||EOL ||
4048 '<DonneeRensCompl>' || l_formatted_box || '</DonneeRensCompl>' ||EOL ||
4049 '</CaseRensCompl>' ||EOL ;
4050 count1 := count1 + 1;
4051 elsif l_further_code(l_i) = '201' then
4052 further_info_details:= further_info_details || '<CaseRensCompl>' ||EOL ||
4053 '<CodeRensCompl>' || l_further_code(l_i) || '</CodeRensCompl>' ||EOL ||
4054 '<DonneeRensCompl>' || l_further_amount(l_i) || '</DonneeRensCompl>' ||EOL ||
4055 '</CaseRensCompl>' ||EOL ;
4056 count1 := count1 + 1;
4057 end if;
4058 end if;
4059 if ( (( count1 =4 ) or ( l_i = g_further_info_list.count )) and counter > 5 ) then
4060 l_final_xml_string_fur_inf :=
4061 '<' || l_status || '>' || EOL ||
4062 '<R>' || EOL ||
4063 tab_employee(lAnnee) ||
4064 '<NoReleve>' || get_slip_seq_no('|',l_rl2_slip_number,slip_count) || '</NoReleve>' ||
4065 '<Beneficiaire>' || EOL ||
4066 tab_employee(lNAS) ||
4067 tab_employee(lNo) ||
4068 tab_employee(lNomFamille) ||
4069 tab_employee(lPrenom) ||
4070 tab_employee(lInitiale) || l_addr_begin_tag || EOL ||
4071 tab_employee(lLigne1) ||
4072 tab_employee(lLigne2) ||
4073 tab_employee(lVille) ||
4074 tab_employee(lProvince) ||
4075 tab_employee(lCodePostal) ||
4076 l_addr_end_tag || EOL || '</Beneficiaire>' || EOL ||
4077 '<Montants>' ||
4078 '</Montants>' || EOL ||
4079 further_info_details || EOL ||
4080 '</R>' || EOL ||
4081 '</' || l_status || '>' ;
4082 count1 := 0;
4083 l_final_xml_string := l_final_xml_string || l_final_xml_string_fur_inf ;
4084 further_info_details := null;
4085 l_final_xml_string_fur_inf := null;
4086 slip_count := slip_count+1;
4087 end if;
4088 end loop;
4089 end if;
4090 end if;
4091 --l_final_xml_string := l_final_xml_string || l_final_xml_string_fur_inf ;
4092 hr_utility.trace('Just before Printing the file details ');
4093 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4094 /* bug 14701748 */
4095 ELSE
4096
4097 IF l_negative_box = 'N' THEN
4098
4099 l_final_xml_string := '<Empdata>'||EOL;
4100 l_final_xml_string1 := '<Empdata>'||EOL;
4101 l_final_xml_string2 := '<Empdata>'||EOL;
4102 l_final_xml_string3 := '<Empdata>'||EOL;
4103 l_final_xml_string4 := '<Empdata>'||EOL;
4104 l_final_xml_string5 := '<Empdata>'||EOL;
4105
4106 FOR i IN 1 .. l_counter LOOP
4107 l_final_xml_string := l_final_xml_string ||tab_emplyr(i);
4108 l_final_xml_string1 := l_final_xml_string1 ||tab_emplyr1(i);
4109 l_final_xml_string2 := l_final_xml_string2 ||tab_emplyr2(i);
4110 l_final_xml_string3 := l_final_xml_string3 ||tab_emplyr3(i);
4111 l_final_xml_string4 := l_final_xml_string4 ||tab_emplyr4(i);
4112 l_final_xml_string5 := l_final_xml_string5 ||tab_emplyr5(i);
4113 END LOOP;
4114
4115
4116 IF l_footnote_count > 1 THEN
4117 tab_employee(l_footnotecode) := '<Footnotecode>'||'See Attached'||'</Footnotecode>';
4118 tab_employee(l_footnoteamt) := NULL;
4119 tab_employee1(l_footnotecode) := '<Footnotecode1>'||'See Attached'||'</Footnotecode1>';
4120 tab_employee1(l_footnoteamt) := NULL;
4121 tab_employee2(l_footnotecode) := '<Footnotecode2>'||'See Attached'||'</Footnotecode2>';
4122 tab_employee2(l_footnoteamt) := NULL;
4123 ELSIF l_footnote_count = 1 THEN
4124 tab_employee(l_footnotecode) := '<Footnotecode>'||l_footcode||'</Footnotecode>';
4125 tab_employee(l_footnoteamt) := '<Footnoteamt>' ||l_footamt ||'</Footnoteamt>';
4126 tab_employee1(l_footnotecode) := '<Footnotecode1>'||l_footcode||'</Footnotecode1>';
4127 tab_employee1(l_footnoteamt) := '<Footnoteamt1>' ||l_footamt ||'</Footnoteamt1>';
4128 tab_employee2(l_footnotecode) := '<Footnotecode2>'||l_footcode||'</Footnotecode2>';
4129 tab_employee2(l_footnoteamt) := '<Footnoteamt2>' ||l_footamt ||'</Footnoteamt2>';
4130 ELSE
4131 tab_employee(l_footnotecode) := NULL;
4132 tab_employee(l_footnoteamt) := NULL;
4133 tab_employee1(l_footnotecode) := NULL;
4134 tab_employee1(l_footnoteamt) := NULL;
4135 tab_employee2(l_footnotecode) := NULL;
4136 tab_employee2(l_footnoteamt) := NULL;
4137 END IF;
4138
4139 l_final_xml_string :=
4140 l_final_xml_string ||EOL||
4141 tab_employee(lAnnee) ||
4142 tab_employee(lNoReleve) ||
4143 tab_employee(lNo) ||
4144 tab_employee(lNomFamille) ||
4145 tab_employee(lLigne1) ||
4146 tab_employee(lLigne2) ||
4147 tab_employee(lVille) ||
4148 tab_employee(NomFamille_Ligne12_Ville) || -- 13460512
4149 tab_employee(lA_PrestRPA_RPNA) ||
4150 tab_employee(lA_PrestRPA_RPNA1) || -- bug 12973513
4151 tab_employee(lB_PrestREER_FERR_RPDB) ||
4152 tab_employee(lC_AutrePaiement) ||
4153 tab_employee(lD_RembPrimeConjoint) ||
4154 tab_employee(lE_PrestDeces) ||
4155 tab_employee(lF_RembCotisInutilise) ||
4156 tab_employee(lG_RevocationREER_FERR) ||
4157 tab_employee(lH_AutreRevenu) ||
4158 tab_employee(lI_DroitDeduction ) ||
4159 tab_employee(lJ_ImpotQueRetenuSource) ||
4160 tab_employee(lK_RevenuApresDeces) ||
4161 tab_employee(lL_RetraitREEP) ||
4162 tab_employee(lM_LibereImpot) ||
4163 tab_employee(lN_NASConjoint) ||
4164 tab_employee(lO_RetraitRAP) ||
4165 tab_employee(lProvenance1) ||
4166 tab_employee(lProvenance) || -- bug 12973513
4167 tab_employee(lNAS1) ||
4168 tab_employee(lNAS2) ||
4169 tab_employee(lNAS3) ||
4170 tab_employee(lN_NASConjoint1) ||
4171 tab_employee(lN_NASConjoint2) ||
4172 tab_employee(lCode_dereleve) ||
4173 tab_employee(l_footnotecode)||EOL||
4174 tab_employee(l_footnoteamt)||EOL||
4175 tab_employee(l_authorisation_tag)||EOL||
4176 tab_employee(l_authorisation_no)||EOL||
4177 tab_employee(l_sequence_no)||EOL;
4178
4179 /* Looping the data twice to meet the template requirement */
4180 l_final_xml_string1 := l_final_xml_string1 ||EOL||
4181 tab_employee1(lAnnee) ||
4182 tab_employee1(lNoReleve) ||
4183 tab_employee1(lNo) ||
4184 tab_employee1(lNomFamille) ||
4185 tab_employee1(lLigne1) ||
4186 tab_employee1(lLigne2) ||
4187 tab_employee1(lVille) ||
4188 tab_employee1(NomFamille_Ligne12_Ville) || -- 13460512
4189 tab_employee1(lA_PrestRPA_RPNA) ||
4190 tab_employee1(lB_PrestREER_FERR_RPDB) ||
4191 tab_employee1(lC_AutrePaiement) ||
4192 tab_employee1(lD_RembPrimeConjoint) ||
4193 tab_employee1(lE_PrestDeces) ||
4194 tab_employee1(lF_RembCotisInutilise) ||
4195 tab_employee1(lG_RevocationREER_FERR) ||
4196 tab_employee1(lH_AutreRevenu) ||
4197 tab_employee1(lI_DroitDeduction ) ||
4198 tab_employee1(lJ_ImpotQueRetenuSource) ||
4199 tab_employee1(lK_RevenuApresDeces) ||
4200 tab_employee1(lL_RetraitREEP) ||
4201 tab_employee1(lM_LibereImpot) ||
4202 tab_employee1(lN_NASConjoint) ||
4203 tab_employee1(lO_RetraitRAP) ||
4204 tab_employee1(lProvenance1) ||
4205 tab_employee1(lNAS1) ||
4206 tab_employee1(lNAS2) ||
4207 tab_employee1(lNAS3) ||
4208 tab_employee1(lN_NASConjoint1) ||
4209 tab_employee1(lN_NASConjoint2) ||
4210 tab_employee1(lCode_dereleve) ||
4211 tab_employee1(l_footnotecode)||EOL||
4212 tab_employee1(l_footnoteamt)||EOL||
4213 tab_employee1(l_authorisation_tag)||EOL||
4214 tab_employee1(l_authorisation_no)||EOL||
4215 tab_employee1(l_sequence_no)||EOL;
4216
4217 l_final_xml_string2 := l_final_xml_string2 ||EOL||
4218 tab_employee2(lAnnee) ||
4219 tab_employee2(lNoReleve) ||
4220 tab_employee2(lNo) ||
4221 tab_employee2(lNomFamille) ||
4222 tab_employee2(lLigne1) ||
4223 tab_employee2(lLigne2) ||
4224 tab_employee2(lVille) ||
4225 tab_employee2(NomFamille_Ligne12_Ville) || -- 13460512
4226 tab_employee2(lA_PrestRPA_RPNA) ||
4227 tab_employee2(lB_PrestREER_FERR_RPDB) ||
4228 tab_employee2(lC_AutrePaiement) ||
4229 tab_employee2(lD_RembPrimeConjoint) ||
4230 tab_employee2(lE_PrestDeces) ||
4231 tab_employee2(lF_RembCotisInutilise) ||
4232 tab_employee2(lG_RevocationREER_FERR) ||
4233 tab_employee2(lH_AutreRevenu) ||
4234 tab_employee2(lI_DroitDeduction ) ||
4235 tab_employee2(lJ_ImpotQueRetenuSource) ||
4236 tab_employee2(lK_RevenuApresDeces) ||
4237 tab_employee2(lL_RetraitREEP) ||
4238 tab_employee2(lM_LibereImpot) ||
4239 tab_employee2(lN_NASConjoint) ||
4240 tab_employee2(lO_RetraitRAP) ||
4241 tab_employee2(lProvenance1) ||
4242 tab_employee2(lNAS1) ||
4243 tab_employee2(lNAS2) ||
4244 tab_employee2(lNAS3) ||
4245 tab_employee2(lN_NASConjoint1) ||
4246 tab_employee2(lN_NASConjoint2) ||
4247 tab_employee2(lCode_dereleve) ||
4248 tab_employee2(l_footnotecode)||EOL||
4249 tab_employee2(l_footnoteamt)||EOL||
4250 tab_employee2(l_authorisation_tag)||EOL||
4251 tab_employee2(l_authorisation_no)||EOL||
4252 tab_employee2(l_sequence_no)||EOL;
4253 -- changes for bug 13545861
4254 l_final_xml_string3 := l_final_xml_string3 ||EOL||
4255 tab_employee3(lAnnee) ||
4256 tab_employee3(lNoReleve) ||
4257 tab_employee3(lNo) ||
4258 tab_employee3(lNomFamille) ||
4259 tab_employee3(lLigne1) ||
4260 tab_employee3(lLigne2) ||
4261 tab_employee3(lVille) ||
4262 tab_employee3(NomFamille_Ligne12_Ville) || -- 13460512
4263 tab_employee3(lA_PrestRPA_RPNA) ||
4264 tab_employee3(lB_PrestREER_FERR_RPDB) ||
4265 tab_employee3(lC_AutrePaiement) ||
4266 tab_employee3(lD_RembPrimeConjoint) ||
4267 tab_employee3(lE_PrestDeces) ||
4268 tab_employee3(lF_RembCotisInutilise) ||
4269 tab_employee3(lG_RevocationREER_FERR) ||
4270 tab_employee3(lH_AutreRevenu) ||
4271 tab_employee3(lI_DroitDeduction ) ||
4272 tab_employee3(lJ_ImpotQueRetenuSource) ||
4273 tab_employee3(lK_RevenuApresDeces) ||
4274 tab_employee3(lL_RetraitREEP) ||
4275 tab_employee3(lM_LibereImpot) ||
4276 tab_employee3(lN_NASConjoint) ||
4277 tab_employee3(lO_RetraitRAP) ||
4278 tab_employee3(lProvenance1) ||
4279 tab_employee3(lNAS1) ||
4280 tab_employee3(lNAS2) ||
4281 tab_employee3(lNAS3) ||
4282 tab_employee3(lN_NASConjoint1) ||
4283 tab_employee3(lN_NASConjoint2) ||
4284 tab_employee3(lCode_dereleve) ||
4285 tab_employee3(l_authorisation_tag)||EOL||
4286 tab_employee3(l_authorisation_no)||EOL||
4287 tab_employee3(l_sequence_no)||EOL;
4288
4289 l_final_xml_string4 := l_final_xml_string4 ||EOL||
4290 tab_employee4(lAnnee) ||
4291 tab_employee4(lNoReleve) ||
4292 tab_employee4(lNo) ||
4293 tab_employee4(lNomFamille) ||
4294 tab_employee4(lLigne1) ||
4295 tab_employee4(lLigne2) ||
4296 tab_employee4(lVille) ||
4297 tab_employee4(NomFamille_Ligne12_Ville) || -- 13460512
4298 tab_employee4(lA_PrestRPA_RPNA) ||
4299 tab_employee4(lB_PrestREER_FERR_RPDB) ||
4300 tab_employee4(lC_AutrePaiement) ||
4301 tab_employee4(lD_RembPrimeConjoint) ||
4302 tab_employee4(lE_PrestDeces) ||
4303 tab_employee4(lF_RembCotisInutilise) ||
4304 tab_employee4(lG_RevocationREER_FERR) ||
4305 tab_employee4(lH_AutreRevenu) ||
4306 tab_employee4(lI_DroitDeduction ) ||
4307 tab_employee4(lJ_ImpotQueRetenuSource) ||
4308 tab_employee4(lK_RevenuApresDeces) ||
4309 tab_employee4(lL_RetraitREEP) ||
4310 tab_employee4(lM_LibereImpot) ||
4311 tab_employee4(lN_NASConjoint) ||
4312 tab_employee4(lO_RetraitRAP) ||
4313 tab_employee4(lProvenance1) ||
4314 tab_employee4(lNAS1) ||
4315 tab_employee4(lNAS2) ||
4316 tab_employee4(lNAS3) ||
4317 tab_employee4(lN_NASConjoint1) ||
4318 tab_employee4(lN_NASConjoint2) ||
4319 tab_employee4(lCode_dereleve) ||
4320 tab_employee4(l_authorisation_tag)||EOL||
4321 tab_employee4(l_authorisation_no)||EOL||
4322 tab_employee4(l_sequence_no)||EOL;
4323
4324 l_final_xml_string5 := l_final_xml_string5 ||EOL||
4325 tab_employee5(lAnnee) ||
4326 tab_employee5(lNoReleve) ||
4327 tab_employee5(lNo) ||
4328 tab_employee5(lNomFamille) ||
4329 tab_employee5(lLigne1) ||
4330 tab_employee5(lLigne2) ||
4331 tab_employee5(lVille) ||
4332 tab_employee5(NomFamille_Ligne12_Ville) || -- 13460512
4333 tab_employee5(lA_PrestRPA_RPNA) ||
4334 tab_employee5(lB_PrestREER_FERR_RPDB) ||
4335 tab_employee5(lC_AutrePaiement) ||
4336 tab_employee5(lD_RembPrimeConjoint) ||
4337 tab_employee5(lE_PrestDeces) ||
4338 tab_employee5(lF_RembCotisInutilise) ||
4339 tab_employee5(lG_RevocationREER_FERR) ||
4340 tab_employee5(lH_AutreRevenu) ||
4341 tab_employee5(lI_DroitDeduction ) ||
4342 tab_employee5(lJ_ImpotQueRetenuSource) ||
4343 tab_employee5(lK_RevenuApresDeces) ||
4344 tab_employee5(lL_RetraitREEP) ||
4345 tab_employee5(lM_LibereImpot) ||
4346 tab_employee5(lN_NASConjoint) ||
4347 tab_employee5(lO_RetraitRAP) ||
4348 tab_employee5(lProvenance1) ||
4349 tab_employee5(lNAS1) ||
4350 tab_employee5(lNAS2) ||
4351 tab_employee5(lNAS3) ||
4352 tab_employee5(lN_NASConjoint1) ||
4353 tab_employee5(lN_NASConjoint2) ||
4354 tab_employee5(lCode_dereleve) ||
4355 tab_employee5(l_authorisation_tag)||EOL||
4356 tab_employee5(l_authorisation_no)||EOL||
4357 tab_employee5(l_sequence_no)||EOL;
4358
4359 l_data_page_count :=1;
4360 l_blank_page_flg := 'TRUE';
4361 l_k:=1;l_i:=1; l_m:=0;
4362 hr_utility.trace('l_data_page_count1 '||l_data_page_count);
4363
4364 if g_further_info_list.count > 0 then
4365 l_final_xml_string6 := '<Empdata>'||EOL;
4366 end if;
4367
4368 if ( p_taxation_year > '2011') then
4369 further_info_count_perpage := 4;
4370 else
4371 further_info_count_perpage := 5;
4372 end if;
4373 hr_utility.trace('(0) l_final_xml_string6--'||l_final_xml_string6);
4374 for l_j in 1..g_further_info_list.count
4375 loop
4376 if l_further_amount(l_j) is not null and (((l_further_code(l_j) <> '201' and to_number(l_further_amount(l_j)) <> 0))
4377 or (l_further_code(l_j)) = '201') then
4378 if l_k > further_info_count_perpage then -- if l_k > 5 then 14701748
4379 l_k :=1;
4380 l_m := 1;
4381 l_data_page_count := l_data_page_count+1;
4382 hr_utility.trace('l_data_page_count2 '||l_data_page_count);
4383 end if;
4384 if l_m = 1 then
4385 for i in 1..l_counter loop
4386 hr_utility.trace('(1)l_final_xml_string6--'||l_final_xml_string6);
4387 l_final_xml_string6:= l_final_xml_string6||tab_emplyr3(i)
4388 ||tab_emplyr4(i)
4389 ||tab_emplyr5(i);
4390 end loop;
4391 hr_utility.trace('(2) l_final_xml_string6--'||l_final_xml_string6);
4392 /* 14701748 */
4393 rl2_final_slip_number := get_slip_seq_no('|',l_rl2_slip_number,l_data_page_count);
4394 rl2_final_slip_number := substr(rl2_final_slip_number,1,3)||'-'||substr(rl2_final_slip_number,4,3)||
4395 '-'||substr(rl2_final_slip_number,7);
4396 rl2_final_seq_number := get_slip_seq_no('|',l_seq_num,l_data_page_count);
4397 rl2_final_seq_number := substr(rl2_final_seq_number,1,3)||'-'||substr(rl2_final_seq_number,4,3)||
4398 '-'||substr(rl2_final_seq_number,7);
4399
4400 lNoReleve_3 := '<NoReleve3>'||rl2_final_slip_number||'</NoReleve3>' || EOL;
4401 lNoReleve_4 := '<NoReleve4>'||rl2_final_slip_number||'</NoReleve4>' || EOL;
4402 lNoReleve_5 := '<NoReleve5>'||rl2_final_slip_number||'</NoReleve5>' || EOL;
4403 l_sequence_no_3 := '<SequenceNum3>'||rl2_final_seq_number||'</SequenceNum3>'|| EOL;
4404 l_sequence_no_4 := '<SequenceNum4>'||rl2_final_seq_number||'</SequenceNum4>'|| EOL;
4405 l_sequence_no_5 := '<SequenceNum5>'||rl2_final_seq_number||'</SequenceNum5>'|| EOL;
4406 if (p_rep_type = 'PAYCARL2AMPDF' or p_rep_type = 'PAYCARL2CLPDF')
4407 and tab_employee(lNoOrigReleve) is not null then
4408
4409 rl2_final_form_number := get_slip_seq_no('|',l_rl2_form_number,l_data_page_count);
4410 if l_rl2_first_form_number IS NOT NULL then
4411 rl2_final_form_number := substr(rl2_final_form_number,1,3)||'-'||substr(rl2_final_form_number,4,3)||
4412 '-'||substr(rl2_final_form_number,7);
4413 end if;
4414 lNoOrigReleve_3 := '<NoOrigReleve3>'||rl2_final_form_number||'</NoOrigReleve3>';
4415 lNoOrigReleve_4 := '<NoOrigReleve4>'||rl2_final_form_number||'</NoOrigReleve4>';
4416 lNoOrigReleve_5 := '<NoOrigReleve5>'||rl2_final_form_number||'</NoOrigReleve5>';
4417 end if;
4418 /* 14701748 */
4419 l_final_xml_string6 := l_final_xml_string6||EOL||
4420 tab_employee3(lAnnee) ||
4421 lNoReleve_3 || /* 14701748 */
4422 tab_employee3(lNo) ||
4423 tab_employee3(lNomFamille) ||
4424 tab_employee3(lLigne1) ||
4425 tab_employee3(lLigne2) ||
4426 tab_employee3(lVille) ||
4427 tab_employee3(NomFamille_Ligne12_Ville) || -- 13460512
4428 tab_employee3(lProvenance1) ||
4429 tab_employee3(lNAS1) ||
4430 tab_employee3(lNAS2) ||
4431 tab_employee3(lNAS3) ||
4432 tab_employee3(lCode_dereleve) ||
4433 tab_employee3(l_authorisation_tag)||EOL||
4434 tab_employee3(l_authorisation_no)||EOL||
4435 l_sequence_no_3 || /* 14701748 */
4436 tab_employee4(lAnnee) ||
4437 lNoReleve_4 || /* 14701748 */
4438 tab_employee4(lNo) ||
4439 tab_employee4(lNomFamille) ||
4440 tab_employee4(lLigne1) ||
4441 tab_employee4(lLigne2) ||
4442 tab_employee4(lVille) ||
4443 tab_employee4(NomFamille_Ligne12_Ville) || -- 13460512
4444 tab_employee4(lProvenance1) ||
4445 tab_employee4(lNAS1) ||
4446 tab_employee4(lNAS2) ||
4447 tab_employee4(lNAS3) ||
4448 tab_employee4(lCode_dereleve) ||
4449 tab_employee4(l_authorisation_tag)||EOL||
4450 tab_employee4(l_authorisation_no)||EOL||
4451 l_sequence_no_4 || /* 14701748 */
4452 tab_employee5(lAnnee) ||
4453 lNoReleve_5 || /* 14701748 */
4454 tab_employee5(lNo) ||
4455 tab_employee5(lNomFamille) ||
4456 tab_employee5(lLigne1) ||
4457 tab_employee5(lLigne2) ||
4458 tab_employee5(lVille) ||
4459 tab_employee5(NomFamille_Ligne12_Ville) || -- 13460512
4460 tab_employee5(lProvenance1) ||
4461 tab_employee5(lNAS1) ||
4462 tab_employee5(lNAS2) ||
4463 tab_employee5(lNAS3) ||
4464 tab_employee5(lCode_dereleve) ||
4465 tab_employee5(l_authorisation_tag)||EOL||
4466 tab_employee5(l_authorisation_no)||EOL||
4467 l_sequence_no_5 ; /* 14701748 */
4468 if (p_rep_type = 'PAYCARL2AMPDF' or p_rep_type = 'PAYCARL2CLPDF')
4469 and tab_employee(lNoOrigReleve) is not null then
4470 l_final_xml_string6 := l_final_xml_string6||
4471 lNoOrigReleve_3 ||EOL|| -- 14701748
4472 lNoOrigReleve_4 ||EOL|| -- 14701748
4473 lNoOrigReleve_5 ||EOL; -- 14701748 /* Edited for bug 13615110*/
4474 end if;
4475 /* 14701748 */
4476 lNoReleve_3 := ' ';
4477 lNoReleve_4 := ' ';
4478 lNoReleve_5 := ' ';
4479 l_sequence_no_3 := ' ';
4480 l_sequence_no_4 := ' ';
4481 l_sequence_no_5 := ' ';
4482 /* 14701748 */
4483 end if;
4484 for l_i in 1..3 loop -- for l_i in 1..3 loop 14701748
4485 l_final_xml_string6 := l_final_xml_string6||'<Further_Info_Code'||l_k||'_'||l_i||'>'||
4486 l_further_code(l_j)||'</Further_Info_Code'||l_k||'_'||l_i||'>'||EOL;
4487 if l_further_code(l_j) = '201' then
4488 l_final_xml_string6 := l_final_xml_string6||'<Further_Info_Amnt'||l_k||'_'||l_i||'>'||
4489 substr(l_further_amount(l_j),1,12)||'</Further_Info_Amnt'||l_k||'_'||l_i||'>'||EOL;
4490 else
4491 l_final_xml_string6 := l_final_xml_string6||'<Further_Info_Amnt'||l_k||'_'||l_i||'>'||
4492 l_further_amount(l_j)||'</Further_Info_Amnt'||l_k||'_'||l_i||'>'||EOL;
4493 end if;
4494 end loop;
4495 l_k := l_k+1; l_m := 0;
4496 end if;
4497 end loop;
4498
4499 /* bug 16055709, assuming that if print_instructions is 'N' (null) means customer
4500 has pre printed instructions hence he will go for single side printing.
4501 Blank page is not needed */
4502
4503 if(((p_print_instruction = 'N') OR (p_print_instruction is null)) OR
4504 ((mod(l_data_page_count,2)=1) and p_print_instruction = 'Y' )) or trim(called_from) = 'get_final_xml' then
4505 l_blank_page_flg := 'FALSE';
4506 end if;
4507
4508 l_final_xml_string6 := l_final_xml_string6||'<BLANK_TYPE>'||l_blank_page_flg||'</BLANK_TYPE>'||EOL;
4509
4510 if g_further_info_list.count > 0 then
4511 l_final_xml_string6 := l_final_xml_string6||'</Empdata>'||EOL;
4512 end if;
4513 -- ends changes for bug 13545861
4514 /*Added for Bug#5046010*/
4515 IF p_rep_type = 'PAYCARL2AMPDF' THEN
4516 l_final_xml_string := l_final_xml_string||
4517 tab_employee(lNoOrigReleve)||EOL||
4518 '</Empdata>'||EOL;
4519 l_final_xml_string1 := l_final_xml_string1||
4520 tab_employee1(lNoOrigReleve)||EOL||
4521 '</Empdata>'||EOL;
4522 l_final_xml_string2 := l_final_xml_string2||
4523 tab_employee2(lNoOrigReleve)||EOL||
4524 '</Empdata>'||EOL;
4525 l_final_xml_string3 := l_final_xml_string3||
4526 tab_employee3(lNoOrigReleve)||EOL||
4527 '</Empdata>'||EOL;
4528 l_final_xml_string4 := l_final_xml_string4||
4529 tab_employee4(lNoOrigReleve)||EOL||
4530 '</Empdata>'||EOL;
4531 l_final_xml_string5 := l_final_xml_string5||
4532 tab_employee5(lNoOrigReleve)||EOL||
4533 '</Empdata>'||EOL;
4534 /*Added for bug 10324391*/
4535 ELSIF p_rep_type = 'PAYCARL2CLPDF' and tab_employee(lNoOrigReleve) is not null
4536 then
4537 l_final_xml_string := l_final_xml_string||
4538 tab_employee(lNoOrigReleve)||EOL||
4539 '</Empdata>'||EOL;
4540 l_final_xml_string1 := l_final_xml_string1||
4541 tab_employee1(lNoOrigReleve)||EOL||
4542 '</Empdata>'||EOL;
4543 l_final_xml_string2 := l_final_xml_string2||
4544 tab_employee2(lNoOrigReleve)||EOL||
4545 '</Empdata>'||EOL;
4546 l_final_xml_string3 := l_final_xml_string3||
4547 tab_employee3(lNoOrigReleve)||EOL||
4548 '</Empdata>'||EOL;
4549 l_final_xml_string4 := l_final_xml_string4||
4550 tab_employee4(lNoOrigReleve)||EOL||
4551 '</Empdata>'||EOL;
4552 l_final_xml_string5 := l_final_xml_string5||
4553 tab_employee5(lNoOrigReleve)||EOL||
4554 '</Empdata>'||EOL;
4555 ELSE
4556 l_final_xml_string := l_final_xml_string||
4557 '</Empdata>'||EOL;
4558 l_final_xml_string1 := l_final_xml_string1||
4559 '</Empdata>'||EOL;
4560 l_final_xml_string2 := l_final_xml_string2||
4561 '</Empdata>'||EOL;
4562 l_final_xml_string3 := l_final_xml_string3||
4563 '</Empdata>'||EOL;
4564 l_final_xml_string4 := l_final_xml_string4||
4565 '</Empdata>'||EOL;
4566 l_final_xml_string5 := l_final_xml_string5||
4567 '</Empdata>'||EOL;
4568 END IF;
4569 /*End Bug#5046010*/
4570
4571 -- l_page_break := '<break_dummy>'||' '||'</break_dummy>'||EOL;
4572 l_final_xml_string := '<RL2_PDF_ASG>'||l_final_xml_string
4573 ||l_final_xml_string1
4574 ||l_final_xml_string2;
4575
4576 if trim(called_from) = 'xml_employee_record' then
4577 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4578 else
4579 l_is_temp_final_xml := dbms_lob.istemporary(p_xml_blob);
4580 IF l_is_temp_final_xml = 1 THEN
4581 DBMS_LOB.FREETEMPORARY(p_xml_blob);
4582 END IF;
4583 dbms_lob.createtemporary(p_xml_blob,false,DBMS_LOB.CALL);
4584 dbms_lob.open(p_xml_blob,dbms_lob.lob_readwrite);
4585 p_xml_blob := append_to_lob(l_final_xml_string);
4586 end if;
4587
4588 l_final_xml_string := l_final_xml_string3
4589 ||l_final_xml_string4
4590 ||l_final_xml_string5
4591 ||l_final_xml_string6
4592 ||'<PRINT-INSTRUCTION>'||p_print_instruction||'</PRINT-INSTRUCTION>' --Added for 5850067
4593 ||EOL||'</RL2_PDF_ASG>';
4594 hr_utility.trace('Just before Printing the file details ');
4595 if trim(called_from) = 'xml_employee_record' then
4596 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4597 else
4598 dbms_lob.createtemporary(l_temp_blob,false,DBMS_LOB.CALL);
4599 dbms_lob.open(l_temp_blob,dbms_lob.lob_readwrite);
4600
4601 l_temp_blob := append_to_lob(l_final_xml_string);
4602 dbms_lob.append(p_xml_blob,l_temp_blob);
4603 IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
4604 hr_utility.trace('Closing l_temp_blob' );
4605 dbms_lob.close(l_temp_blob);
4606 END IF;
4607 IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
4608 hr_utility.trace('Closing p_xml_blob' );
4609 dbms_lob.close(p_xml_blob);
4610 hr_utility.trace('closed p_xml_blob');
4611 END IF;
4612 end if;
4613
4614 ELSIF l_negative_box = 'Y' THEN
4615 --Bug 7495973: Appended '_F' to all xml tags of failed records
4616 /* Bug 15972457 */
4617 tab_employee(lNoReleve) := '<NoReleve>' || convert_special_char(l_rl2_slip_number) ||
4618 '</NoReleve>' || EOL;
4619 /* Bug 15972457 */
4620 /* Bug 16015535 */
4621 tab_employee(lNoOrigReleve) := '<NoOrigReleve>' || convert_special_char(l_rl2_form_number) ||
4622 '</NoOrigReleve>' || EOL;
4623 /* Bug 16015535 */
4624 l_final_xml_string :=
4625 '<FAILED_RL2_PDFASG>' ||EOL||
4626 replace(tab_emplyr(1),'Nom','Nom_F')||EOL||
4627 replace(tab_employee(lNo),'No','No_F') ||
4628 replace(tab_employee(lNomFamille),'NomFamille','NomFamille_F') ||
4629 replace(tab_employee(lLigne1),'Ligne1','Ligne1_F') ||
4630 replace(tab_employee(lLigne2),'Ligne2','Ligne2_F') ||
4631 replace(tab_employee(lVille),'Ville','Ville_F') ||
4632 replace(tab_employee(lA_PrestRPA_RPNA),'A_PrestRPA_RPNA','A_PrestRPA_RPNA_F') ||
4633 replace(tab_employee(lA_PrestRPA_RPNA1),'A_PrestRPA_RPNA','A_PrestRPA_RPNA_F') ||
4634 replace(tab_employee(lB_PrestREER_FERR_RPDB),'B_PrestREER_FERR_RPDB','B_PrestREER_FERR_RPDB_F') ||
4635 replace(tab_employee(lC_AutrePaiement),'C_AutrePaiement','C_AutrePaiement_F') ||
4636 replace(tab_employee(lD_RembPrimeConjoint),'D_RembPrimeConjoint','D_RembPrimeConjoint_F') ||
4637 replace(tab_employee(lE_PrestDeces),'E_PrestDeces','E_PrestDeces_F') ||
4638 replace(tab_employee(lF_RembCotisInutilise),'F_RembCotisInutilise','F_RembCotisInutilise_F') ||
4639 replace(tab_employee(lG_RevocationREER_FERR),'G_RevocationREER_FERR','G_RevocationREER_FERR_F') ||
4640 replace(tab_employee(lH_AutreRevenu),'H_AutreRevenu','H_AutreRevenu_F') ||
4641 replace(tab_employee(lI_DroitDeduction ),'I_DroitDeduction','I_DroitDeduction_F') ||
4642 replace(tab_employee(lJ_ImpotQueRetenuSource),'J_ImpotQueRetenuSource','J_ImpotQueRetenuSource_F') ||
4643 replace(tab_employee(lK_RevenuApresDeces),'K_RevenuApresDeces','K_RevenuApresDeces_F') ||
4644 replace(tab_employee(lL_RetraitREEP),'L_RetraitREEP','L_RetraitREEP_F') ||
4645 replace(tab_employee(lM_LibereImpot),'M_LibereImpot','M_LibereImpot_F') ||
4646 replace(tab_employee(lN_NASConjoint),'N_NASConjoint1','N_NASConjoint1_F') ||
4647 replace(tab_employee(lN_NASConjoint1),'N_NASConjoint2','N_NASConjoint2_F') ||
4648 replace(tab_employee(lN_NASConjoint2),'N_NASConjoint3','N_NASConjoint3_F') ||
4649 replace(tab_employee(lO_RetraitRAP),'O_RetraitRAP','O_RetraitRAP_F') ||
4650 replace(tab_employee(lProvenance1),'Provenance1','Provenance1_F') ||
4651 replace(tab_employee(lProvenance),'Provenance1','Provenance1_F') || -- bug 12973513
4652 replace(tab_employee(lNAS1),'NAS1','NAS1_F') ||
4653 replace(tab_employee(lNAS2),'NAS2','NAS2_F') ||
4654 replace(tab_employee(lNAS3),'NAS3','NAS3_F') ||
4655 replace(tab_employee(lN_NASConjoint1),'N_NASConjoint1','N_NASConjoint1_F') ||
4656 replace(tab_employee(lN_NASConjoint2),'N_NASConjoint2','N_NASConjoint2_F') ||
4657 replace(tab_employee(lNoReleve),'NoReleve','NoReleve_F') ||
4658 replace(tab_employee(lNoOrigReleve),'NoOrigReleve','NoOrigReleve_F') || -- Bug 16015535
4659 l_other_details||
4660 tab_employee(lErrorDetails)||
4661 '</FAILED_RL2_PDFASG>'||EOL;
4662
4663 hr_utility.trace('Just before Printing the file details ');
4664 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4665 END IF;
4666 END IF;
4667
4668 END;
4669 END fetch_rl2_xml;
4670
4671 PROCEDURE xml_report_start IS
4672 BEGIN
4673
4674 DECLARE
4675 l_final_xml_string VARCHAR2(32000);
4676
4677 BEGIN
4678
4679 l_final_xml_string := '<RL2PAPER>';
4680 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4681
4682 END;
4683 END xml_report_start;
4684
4685 PROCEDURE xml_report_end IS
4686 BEGIN
4687
4688 DECLARE
4689 l_final_xml_string VARCHAR2(32000);
4690
4691 BEGIN
4692
4693 l_final_xml_string := '</RL2PAPER>';
4694 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4695
4696 END;
4697 END xml_report_end;
4698
4699
4700 PROCEDURE xml_employer_start IS
4701 BEGIN
4702
4703 DECLARE
4704
4705 l_final_xml_string VARCHAR2(32000);
4706
4707 BEGIN
4708
4709 l_final_xml_string := '<Groupe02>';
4710 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4711
4712 END;
4713 END xml_employer_start;
4714
4715 PROCEDURE xml_employer_record IS
4716 BEGIN
4717
4718 DECLARE
4719
4720 l_final_xml_string VARCHAR2(32000);
4721
4722 TYPE employer_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
4723
4724 tab_employer employer_info;
4725
4726 lAnnee NUMBER;
4727 lNbReleves NUMBER;
4728 lNold NUMBER;
4729 lTypeDossier NUMBER;
4730 lNoDossier NUMBER;
4731 lNom1 NUMBER;
4732 lLigne1 NUMBER;
4733 lLigne2 NUMBER;
4734 lVille NUMBER;
4735 lProvince NUMBER;
4736 lCodePostal NUMBER;
4737 l_taxation_year varchar2(4);
4738 l_payroll_actid NUMBER;
4739 l_year_start DATE;
4740 l_year_end DATE;
4741 l_report_type VARCHAR2(5);
4742 l_business_grpid NUMBER;
4743 l_legislative_param pay_payroll_actions.legislative_parameters%type;
4744 EOL varchar2(5);
4745 l_employer_name varchar2(100);
4746 l_quebec_bn varchar2(20);
4747 l_address_line per_addresses.address_line1%TYPE;
4748 l_address_begin_tag varchar2(10);
4749 l_address_end_tag varchar2(10);
4750
4751 BEGIN
4752 hr_utility.trace('XML Employer');
4753 hr_utility.trace('XML Employer');
4754
4755 SELECT
4756 fnd_global.local_chr(13) || fnd_global.local_chr(10)
4757 INTO EOL
4758 FROM dual;
4759
4760 lAnnee := 1;
4761 lNbReleves := 2;
4762 lNold := 3;
4763 lTypeDossier := 4;
4764 lNoDossier := 5;
4765 lNom1 := 6;
4766 lLigne1 := 7;
4767 lLigne2 := 8;
4768 lVille := 9;
4769 lProvince := 10;
4770 lCodePostal := 11;
4771
4772 l_taxation_year
4773 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
4774 l_payroll_actid
4775 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
4776
4777 get_report_parameters(
4778 l_payroll_actid,
4779 l_year_start,
4780 l_year_end,
4781 l_report_type,
4782 l_business_grpid,
4783 l_legislative_param
4784 );
4785
4786 tab_employer(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' || EOL;
4787 tab_employer(lNbReleves) := '<NbReleves>' || 'Running Total' || '</NbReleves>' || EOL;
4788
4789 hr_utility.trace('The Payroll Action Id : '||l_payroll_actid);
4790 hr_utility.trace('The business group id : '||l_business_grpid);
4791 l_quebec_bn := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
4792 l_payroll_actid,
4793 'CAEOY_RL2_QUEBEC_BN');
4794 hr_utility.trace('The Quebec Number is '||l_quebec_bn);
4795 tab_employer(lNold) := '<NoId>' || convert_special_char(substr(l_quebec_bn,1,10)) ||
4796 '</NoId>' || EOL;
4797 tab_employer(lTypeDossier) := '<TypeDossier>' || 'RS' ||
4798 '</TypeDossier>' || EOL;
4799
4800 tab_employer(lNoDossier) := '<NoDossier>' || convert_special_char(substr(l_quebec_bn,13,4)) ||
4801 '</NoDossier>' || EOL;
4802 hr_utility.trace('The Employer File Number : '|| substr(l_quebec_bn,13,4));
4803 l_employer_name := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
4804 l_payroll_actid,
4805 'CAEOY_RL2_EMPLOYER_NAME');
4806
4807 tab_employer(lNom1) := '<Nom>' ||
4808 convert_special_char(substr(l_employer_name,1,30)) || '</Nom>' || EOL;
4809 hr_utility.trace('tab_employer(lNom) = ' || tab_employer(lNom1));
4810
4811 -- Address Line 1
4812
4813 l_address_line := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
4814 l_payroll_actid,
4815 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE1');
4816
4817 IF (l_address_line IS NULL AND
4818 l_address_line <> ' ' ) THEN
4819
4820 l_address_begin_tag := '';
4821 tab_employer(lLigne1) := NULL;
4822 tab_employer(lLigne2) := NULL;
4823 tab_employer(lVille) := NULL;
4824 tab_employer(lProvince) := NULL;
4825 tab_employer(lCodePostal) := NULL;
4826 l_address_end_tag := '';
4827
4828 ELSE
4829
4830 l_address_begin_tag := '<Adresse>';
4831
4832 tab_employer(lLigne1) := '<Ligne1>' ||
4833 convert_special_char(substr(l_address_line,1,30)) || '</Ligne1>' || EOL;
4834 hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
4835
4836
4837 -- Address Line 2
4838
4839 l_address_line := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
4840 l_payroll_actid,
4841 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE2');
4842
4843 IF (l_address_line IS NOT NULL AND
4844 l_address_line <> ' ' ) THEN
4845 tab_employer(lLigne2) := '<Ligne2>' ||
4846 convert_special_char(substr(l_address_line,1,30)) || '</Ligne2>' || EOL;
4847 ELSE
4848 tab_employer(lLigne2) := NULL;
4849 END IF;
4850 hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
4851
4852 -- Ville (City)
4853
4854 l_address_line := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
4855 l_payroll_actid,
4856 'CAEOY_RL2_EMPLOYER_CITY');
4857 IF ( l_address_line IS NOT NULL AND
4858 l_address_line <> ' ') THEN
4859 tab_employer(lVille) := '<Ville>' ||
4860 convert_special_char(substr(l_address_line,1,30)) || '</Ville>' || EOL;
4861 ELSE
4862 tab_employer(lVille) := NULL;
4863 END IF;
4864 hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
4865
4866 -- Province
4867
4868 l_address_line := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
4869 l_payroll_actid,
4870 'CAEOY_RL2_EMPLOYER_PROVINCE');
4871
4872 IF ( l_address_line IS NOT NULL AND
4873 l_address_line <> ' ' ) THEN
4874 tab_employer(lProvince) := '<Province>' ||
4875 convert_special_char(SUBSTR(hr_general.decode_lookup( 'CA_PROVINCE',
4876 l_address_line),1,20)) || '</Province>' || EOL;
4877 ELSE
4878 tab_employer(lProvince) := NULL;
4879 END IF;
4880 hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
4881
4882 -- Postal Code
4883
4884 l_address_line := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
4885 l_payroll_actid,
4886 'CAEOY_RL2_EMPLOYER_POSTAL_CODE');
4887
4888 IF ( l_address_line IS NOT NULL AND
4889 l_address_line <> ' ' ) THEN
4890 tab_employer(lCodePostal) := '<CodePostal>' ||
4891 convert_special_char(substr(l_address_line,1,6)) || '</CodePostal>' || EOL;
4892 ELSE
4893 tab_employer(lCodePostal) := NULL;
4894 END IF;
4895 hr_utility.trace('tab_employer(lCodePostal) = ' ||
4896 tab_employer(lCodePostal));
4897
4898 l_address_end_tag := '</Adresse>';
4899
4900 END IF;
4901 l_final_xml_string := '<T>' || EOL ||
4902 tab_employer(lAnnee) ||
4903 tab_employer(lNbReleves)|| EOL || '<PayeurEmetteur>' || EOL ||
4904 tab_employer(lNold) ||
4905 tab_employer(lTypeDossier) ||
4906 tab_employer(lNoDossier) ||
4907 tab_employer(lNom1) || l_address_begin_tag || EOL ||
4908 tab_employer(lLigne1) ||
4909 tab_employer(lLigne2) ||
4910 tab_employer(lVille) ||
4911 tab_employer(lProvince) ||
4912 tab_employer(lCodePostal) ||
4913 l_address_end_tag || EOL || '</PayeurEmetteur>' || EOL ||
4914 '</T>' || EOL ||
4915 '</Groupe02>' || EOL;
4916
4917 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4918
4919 END;
4920 END xml_employer_record;
4921
4922 /* Added by ssmukher for Bug 4030973 */
4923 /* The check digit calculated by the method below
4924 must be the same as the 10th digit of the
4925 identification number or the number is invalid.
4926
4927 Example of the modulus 11 method:
4928 The identification number is 2345678908.
4929 Use the first nine digits to validate the identification number.
4930 The tenth digit is the check digit (in this case 8).
4931
4932 Formula:
4933 Beginning with the ninth digit (extreme right), multiply each digit by the
4934 factor indicated.
4935 The factors form a circular sequence of the values 2 through 7, beginning on
4936 the right.
4937 First nine digits of the identification number 2 3 4 5 6 7 8 9 0
4938 Multiply each digit by the factor indicated. x 4 x 3 x 2 x 7 x 6 x 5 x 4 x 3
4939 x 2
4940 Results 8 9 8 35 36 35 32 27 0
4941 Add the results
4942 (8 + 9 + 8 + 35 + 36 + 35 + 32 + 27 + 0). = 190
4943 Divide the result by 11 (190/11). = 17 remainder 3
4944 If the remainder is 0, the check digit is 1. If the remainder is 1, the check
4945 digit is 0.
4946 For any other remainder obtained, the check digit is the difference between
4947 11 and that remainder.
4948 Subtract the remainder obtained from 11 (11 - 3) = 8
4949 */
4950
4951 FUNCTION validate_quebec_number (p_quebec_no IN VARCHAR2,p_qin_name varchar2)
4952 RETURN NUMBER IS
4953
4954 l_quebec NUMBER;
4955 l_rem NUMBER;
4956 i NUMBER;
4957 l_max NUMBER;
4958 l_total NUMBER;
4959 l_min NUMBER;
4960 l_modulus NUMBER;
4961 l_chk_digit NUMBER;
4962 l_act_chk_number NUMBER;
4963
4964 BEGIN
4965 i := 1;
4966 l_min := 2;
4967 l_max := 7;
4968 l_total := 0;
4969 l_quebec := to_number(substr(p_quebec_no,1,9));
4970 l_act_chk_number := to_number(substr(p_quebec_no,10,1));
4971
4972 if TRANSLATE(substr(p_quebec_no,1,9),'0123456789','9999999999') = '999999999' then
4973
4974 loop
4975 if i > 9 then
4976 exit;
4977 end if;
4978
4979 if l_min > l_max then
4980 l_min := 2;
4981 end if;
4982
4983 l_rem := mod(l_quebec,10);
4984 l_total := l_total + (l_min * l_rem);
4985 l_min := l_min + 1;
4986 l_quebec := ((l_quebec - l_rem)/10);
4987 i := i+ 1;
4988
4989 end loop;
4990
4991 l_modulus := mod(l_total, 11);
4992 if l_modulus = 0 then
4993 l_chk_digit := 1;
4994 elsif l_modulus = 1 then
4995 l_chk_digit := 0;
4996 else
4997 l_chk_digit := 11 - l_modulus;
4998 end if;
4999
5000 if l_chk_digit <> l_act_chk_number then
5001 hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
5002 hr_utility.set_message_token('PRE_NAME',p_qin_name);
5003 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
5004 pay_core_utils.push_token('PRE_NAME',p_qin_name);
5005 hr_utility.raise_error;
5006 end if;
5007 else
5008
5009 hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
5010 hr_utility.set_message_token('PRE_NAME',p_qin_name);
5011 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
5012 pay_core_utils.push_token('PRE_NAME',p_qin_name);
5013 hr_utility.raise_error;
5014
5015 end if;
5016
5017 return l_chk_digit;
5018
5019 END;
5020
5021
5022 FUNCTION convert_special_char( p_data varchar2)
5023 RETURN varchar2 IS
5024 l_data VARCHAR2(2000);
5025 l_output varchar2(2000);
5026 cursor c_uppercase(p_input_string varchar2) is
5027 select
5028 replace(
5029 replace(
5030 replace(
5031 replace(
5032 replace(
5033 replace(
5034 replace(
5035 replace(
5036 replace(
5037 replace(
5038 replace(
5039 replace(
5040 replace(
5041 replace(
5042 replace(
5043 replace(convert(p_input_string,'UTF8'),
5044 utl_raw.cast_to_varchar2(hextoraw('C380')),'A'
5045 ),
5046 utl_raw.cast_to_varchar2(hextoraw('C38A')),'E'
5047 ),
5048 utl_raw.cast_to_varchar2(hextoraw('C387')),'C'
5049 ),
5050 utl_raw.cast_to_varchar2(hextoraw('C389')),'E'
5051 ),
5052 utl_raw.cast_to_varchar2(hextoraw('C39C')),'U'
5053 ),
5054 utl_raw.cast_to_varchar2(hextoraw('C399')),'U'
5055 ),
5056 utl_raw.cast_to_varchar2(hextoraw('C39B')),'U'
5057 ),
5058 utl_raw.cast_to_varchar2(hextoraw('C394')),'O'
5059 ),
5060 utl_raw.cast_to_varchar2(hextoraw('C38F')),'I'
5061 ),
5062 utl_raw.cast_to_varchar2(hextoraw('C38E')),'I'
5063 ),
5064 utl_raw.cast_to_varchar2(hextoraw('C388')),'E'
5065 ),
5066 utl_raw.cast_to_varchar2(hextoraw('C38B')),'E'
5067 ),
5068 utl_raw.cast_to_varchar2(hextoraw('C382')),'A'
5069 ),
5070 utl_raw.cast_to_varchar2(hextoraw('C592')),'OE'
5071 ),
5072 utl_raw.cast_to_varchar2(hextoraw('C386')),'AE'
5073 ),
5074 utl_raw.cast_to_varchar2(hextoraw('C3A9')),'e'
5075 )
5076 from dual;
5077
5078 BEGIN
5079 l_data := trim(p_data);
5080 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
5081 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
5082 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
5083 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
5084 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
5085
5086 open c_uppercase(l_data);
5087 fetch c_uppercase into l_output;
5088 if c_uppercase%NOTFOUND then
5089 l_output := l_data;
5090 end if;
5091 close c_uppercase;
5092
5093 RETURN l_output;
5094 END;
5095
5096
5097 FUNCTION get_employee_item (p_asg_action_id IN number,
5098 p_assignment_id IN number,
5099 p_archived_item IN varchar2)
5100 RETURN varchar2 IS
5101
5102 CURSOR c_employee_info IS
5103 SELECT nvl(tran.quebec_business_number,'0000000000 0000'),
5104 nvl(tran.reporting_year,'0000'),
5105 nvl(emp.rl2_slip_number,'000000000'),
5106 nvl(emp.employee_sin,'000000000'),
5107 nvl(emp.employee_last_name,' '),
5108 nvl(emp.employee_first_name,' '),
5109 nvl(emp.employee_middle_initial,' '),
5110 nvl(emp.employee_address_line1,' '),
5111 nvl(emp.employee_address_line2,' '),
5112 nvl(emp.employee_address_line3,' '),
5113 nvl(emp.employee_city,' '),
5114 nvl(emp.employee_province,' '),
5115 nvl(emp.employee_postal_code,' '),
5116 nvl(emp.employee_number,' '),
5117 emp.rl2_box_a,
5118 emp.rl2_box_b,
5119 emp.rl2_box_c,
5120 emp.rl2_box_d,
5121 emp.rl2_box_e,
5122 emp.rl2_box_f,
5123 emp.rl2_box_g,
5124 emp.rl2_box_h,
5125 emp.rl2_box_i,
5126 emp.rl2_box_j,
5127 emp.rl2_box_k,
5128 emp.rl2_box_l,
5129 emp.rl2_box_m,
5130 emp.rl2_box_n,
5131 emp.rl2_box_o,
5132 decode(substr(emp.rl2_source_of_income,1,5),'OTHER','AUTRE', emp.rl2_source_of_income),
5133 nvl(emp.negative_balance_flag, 'N'),
5134 emp.person_id
5135 FROM pay_ca_eoy_rl2_employee_info_v emp,
5136 pay_ca_eoy_rl2_trans_info_v tran
5137 WHERE emp.assignment_action_id = p_asg_action_id
5138 AND emp.assignment_id = p_assignment_id
5139 AND emp.payroll_action_id = tran.payroll_action_id;
5140
5141 l_quebec_business_number varchar2(240);
5142 l_reporting_year varchar2(240);
5143 l_rl2_slip_number varchar2(240);
5144 l_employee_sin varchar2(240);
5145 l_employee_last_name varchar2(240);
5146 l_employee_first_name varchar2(240);
5147 l_employee_middle_initial varchar2(240);
5148 l_employee_address_line1 varchar2(240);
5149 l_employee_address_line2 varchar2(240);
5150 l_employee_address_line3 varchar2(240);
5151 l_employee_city varchar2(240);
5152 l_employee_province varchar2(240);
5153 l_employee_postal_code varchar2(240);
5154 l_employee_number varchar2(240);
5155 l_person_id varchar2(50);
5156 l_rl2_box_a varchar2(240);
5157 l_rl2_box_b varchar2(240);
5158 l_rl2_box_c varchar2(240);
5159 l_rl2_box_d varchar2(240);
5160 l_rl2_box_e varchar2(240);
5161 l_rl2_box_f varchar2(240);
5162 l_rl2_box_g varchar2(240);
5163 l_rl2_box_h varchar2(240);
5164 l_rl2_box_i varchar2(240);
5165 l_rl2_box_j varchar2(240);
5166 l_rl2_box_k varchar2(240);
5167 l_rl2_box_l varchar2(240);
5168 l_rl2_box_m varchar2(240);
5169 l_rl2_box_n varchar2(240);
5170 l_rl2_box_o varchar2(240);
5171 l_rl2_source_of_income varchar2(240);
5172 l_negative_balance_flag varchar2(240);
5173
5174 l_return_value varchar2(240);
5175
5176 BEGIN
5177
5178 OPEN c_employee_info;
5179 FETCH c_employee_info
5180 INTO l_quebec_business_number,
5181 l_reporting_year,
5182 l_rl2_slip_number,
5183 l_employee_sin,
5184 l_employee_last_name,
5185 l_employee_first_name,
5186 l_employee_middle_initial,
5187 l_employee_address_line1,
5188 l_employee_address_line2,
5189 l_employee_address_line3,
5190 l_employee_city,
5191 l_employee_province,
5192 l_employee_postal_code,
5193 l_employee_number,
5194 l_rl2_box_a,
5195 l_rl2_box_b,
5196 l_rl2_box_c,
5197 l_rl2_box_d,
5198 l_rl2_box_e,
5199 l_rl2_box_f,
5200 l_rl2_box_g,
5201 l_rl2_box_h,
5202 l_rl2_box_i,
5203 l_rl2_box_j,
5204 l_rl2_box_k,
5205 l_rl2_box_l,
5206 l_rl2_box_m,
5207 l_rl2_box_n,
5208 l_rl2_box_o,
5209 l_rl2_source_of_income,
5210 l_negative_balance_flag,
5211 l_person_id;
5212
5213 CLOSE c_employee_info;
5214
5215 IF p_archived_item = 'CAEOY_RL2_QUEBEC_BN' THEN
5216 l_return_value := l_quebec_business_number;
5217 ELSIF p_archived_item = 'CAEOY_TAXATION_YEAR' THEN
5218 l_return_value := l_reporting_year;
5219 ELSIF p_archived_item = 'CAEOY_RL2_SLIP_NUMBER' THEN
5220 l_return_value := l_rl2_slip_number;
5221 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_SIN' THEN
5222 l_return_value := l_employee_sin;
5223 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_LAST_NAME' THEN
5224 l_return_value := l_employee_last_name;
5225 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_FIRST_NAME' THEN
5226 l_return_value := l_employee_first_name;
5227 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_INITIAL' THEN
5228 l_return_value := l_employee_middle_initial;
5229 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_ADDRESS_LINE1' THEN
5230 l_return_value := l_employee_address_line1;
5231 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_ADDRESS_LINE2' THEN
5232 l_return_value := l_employee_address_line2;
5233 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_ADDRESS_LINE3' THEN
5234 l_return_value := l_employee_address_line3;
5235 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_CITY' THEN
5236 l_return_value := l_employee_city;
5237 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_PROVINCE' THEN
5238 l_return_value := l_employee_province;
5239 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_POSTAL_CODE' THEN
5240 l_return_value := l_employee_postal_code;
5241 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_NUMBER' THEN
5242 l_return_value := l_employee_number;
5243 ELSIF p_archived_item = 'CAEOY_RL2_BOX_A' THEN
5244 l_return_value := l_rl2_box_a;
5245 ELSIF p_archived_item = 'CAEOY_RL2_BOX_B' THEN
5246 l_return_value := l_rl2_box_b;
5247 ELSIF p_archived_item = 'CAEOY_RL2_BOX_C' THEN
5248 l_return_value := l_rl2_box_c;
5249 ELSIF p_archived_item = 'CAEOY_RL2_BOX_D' THEN
5250 l_return_value := l_rl2_box_d;
5251 ELSIF p_archived_item = 'CAEOY_RL2_BOX_E' THEN
5252 l_return_value := l_rl2_box_e;
5253 ELSIF p_archived_item = 'CAEOY_RL2_BOX_F' THEN
5254 l_return_value := l_rl2_box_f;
5255 ELSIF p_archived_item = 'CAEOY_RL2_BOX_G' THEN
5256 l_return_value := l_rl2_box_g;
5257 ELSIF p_archived_item = 'CAEOY_RL2_BOX_H' THEN
5258 l_return_value := l_rl2_box_h;
5259 ELSIF p_archived_item = 'CAEOY_RL2_BOX_I' THEN
5260 l_return_value := l_rl2_box_i;
5261 ELSIF p_archived_item = 'CAEOY_RL2_BOX_J' THEN
5262 l_return_value := l_rl2_box_j;
5263 ELSIF p_archived_item = 'CAEOY_RL2_BOX_K' THEN
5264 l_return_value := l_rl2_box_k;
5265 ELSIF p_archived_item = 'CAEOY_RL2_BOX_L' THEN
5266 l_return_value := l_rl2_box_l;
5267 ELSIF p_archived_item = 'CAEOY_RL2_BOX_M' THEN
5268 l_return_value := l_rl2_box_m;
5269 ELSIF p_archived_item = 'CAEOY_RL2_BOX_N' THEN
5270 l_return_value := l_rl2_box_n;
5271 ELSIF p_archived_item = 'CAEOY_RL2_BOX_O' THEN
5272 l_return_value := l_rl2_box_o;
5273 ELSIF p_archived_item = 'CAEOY_RL2_SOURCE_OF_INCOME' THEN
5274 l_return_value := l_rl2_source_of_income;
5275 ELSIF p_archived_item = 'CAEOY_RL2_NEGATIVE_BALANCE' THEN
5276 l_return_value := l_negative_balance_flag;
5277 ELSIF p_archived_item = 'CAEOY_PERSON_ID' THEN
5278 l_return_value := l_person_id;
5279 END IF;
5280
5281 RETURN l_return_value;
5282
5283 END get_employee_item;
5284
5285
5286 PROCEDURE archive_ca_deinit (p_pactid IN NUMBER) IS
5287
5288 CURSOR c_get_report_type ( p_pactid number) IS
5289 SELECT report_type
5290 FROM pay_payroll_actions
5291 WHERE payroll_action_id = p_pactid;
5292
5293 l_report_type pay_payroll_actions.report_type%type;
5294
5295 BEGIN
5296
5297 open c_get_report_type(p_pactid);
5298 fetch c_get_report_type
5299 into l_report_type;
5300 close c_get_report_type;
5301
5302 IF l_report_type = 'RL2PAPERPDF' OR l_report_type = 'PAYCARL2AMPDF' OR l_report_type = 'PAYCARL2CLPDF'
5303 THEN
5304 pay_ca_payroll_utils.delete_actionid(p_pactid);
5305 END IF;
5306
5307 END archive_ca_deinit;
5308
5309 /* Commented for bug 8500723
5310 FUNCTION getnext_seq_num (p_curr_seq IN NUMBER)
5311 RETURN NUMBER IS
5312 l_seq_number number;
5313 l_check_number number;
5314 BEGIN
5315
5316 l_check_number := mod(p_curr_seq,7);
5317 hr_utility.trace('l_check_number ='|| l_check_number);
5318 l_seq_number := (p_curr_seq * 10) + l_check_number;
5319 hr_utility.trace('l_seq_number ='|| l_seq_number);
5320 return l_seq_number;
5321 END; */
5322
5323 END pay_ca_rl2_mag;