[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_RL2_MAG
Source
1 PACKAGE BODY PAY_CA_RL2_MAG AS
2 /* $Header: pycarl2mg.pkb 120.55.12010000.3 2008/08/06 06:59:05 ubhat 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
24 PROCEDURE get_report_parameters
25 ( p_pactid IN NUMBER,
26 p_year_start IN OUT NOCOPY DATE,
27 p_year_end IN OUT NOCOPY DATE,
28 p_report_type IN OUT NOCOPY VARCHAR2,
29 p_business_group_id IN OUT NOCOPY NUMBER,
30 p_legislative_param IN OUT NOCOPY VARCHAR2
31 ) IS
32 BEGIN
33 --hr_utility.trace_on('Y','RL2MAG');
34 hr_utility.set_location('pay_ca_rl2_mag.get_report_parameters', 10);
35
36 SELECT ppa.start_date,
37 ppa.effective_date,
38 ppa.business_group_id,
39 ppa.report_type,
40 ppa.legislative_parameters
41 INTO p_year_start,
42 p_year_end,
43 p_business_group_id,
44 p_report_type,
45 p_legislative_param
46 FROM pay_payroll_actions ppa
47 WHERE payroll_action_id = p_pactid;
48
49 hr_utility.set_location('pay_ca_rl2_mag.get_report_parameters', 20);
50
51 END get_report_parameters;
52
53 ---------------------------------------------------------------------------
54 --Procedure Name : validate_transmitter_info
55 --Purpose
56 -- This procedure is used for checking if the correct transmitter
57 -- record details has been entered.If any of the following data
58 -- Package Type,Source Of Rl2 Slip ,Transmitter Number,Transmitter Name
59 -- is missing then the RL2 Electronic Interface is made to error out.
60 ----------------------------------------------------------------------------
61
62 PROCEDURE validate_transmitter_info(p_payroll_action_id IN NUMBER,
63 p_bg_id IN NUMBER,
64 p_effective_date IN DATE) IS
65 BEGIN
66
67 DECLARE
68
69 CURSOR cur_arch_pactid(p_transmitter_org_id NUMBER) IS
70 SELECT
71 ppa.payroll_action_id
72 FROM
73 pay_payroll_actions ppa
74 WHERE
75 ppa.business_group_id = p_bg_id AND
76 ppa.report_type = 'RL2' AND
77 ppa.report_qualifier = 'CAEOYRL2' AND
78 ppa.report_category = 'ARCHIVE' AND
79 ppa.effective_date = p_effective_date AND
80 p_transmitter_org_id =
81 pay_ca_rl2_mag.get_parameter('PRE_ORGANIZATION_ID',
82 ppa.legislative_parameters);
83
84 l_transmitter_number VARCHAR2(30);
85 l_transmitter_name VARCHAR2(100);
86 l_type_of_package VARCHAR2(30);
87 l_source_of_slips VARCHAR2(30);
88 dummy NUMBER;
89 dummy1 VARCHAR2(10);
90 l_transmitter_org_id NUMBER;
91 l_arch_pactid NUMBER;
92 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
93 l_address_line1 per_addresses.address_line1%TYPE;
94
95 CURSOR cur_ppa IS
96 SELECT
97 ppa.legislative_parameters
98 FROM
99 pay_payroll_actions ppa
100 WHERE
101 ppa.payroll_action_id = p_payroll_action_id;
102
103 BEGIN
104
105 OPEN cur_ppa;
106 FETCH cur_ppa
107 INTO l_legislative_parameters;
108 CLOSE cur_ppa;
109
110 l_transmitter_org_id := pay_ca_rl2_mag.get_parameter('TRANSMITTER_PRE',
111 l_legislative_parameters);
112
113 hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
114 hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
115 hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
116 hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
117
118 OPEN cur_arch_pactid(l_transmitter_org_id);
119 FETCH cur_arch_pactid
120 INTO l_arch_pactid;
121 CLOSE cur_arch_pactid;
122
123 l_transmitter_number := pay_ca_rl2_mag.get_transmitter_item( p_bg_id,
124 l_arch_pactid,
125 'CAEOY_RL2_TRANSMITTER_NUMBER');
126 l_transmitter_name := pay_ca_rl2_mag.get_transmitter_item( p_bg_id,
127 l_arch_pactid,
128 'CAEOY_RL2_TRANSMITTER_NAME');
129 BEGIN
130
131 hr_utility.trace('l_transmitter_number = ' || l_transmitter_number);
132 SELECT substr(l_transmitter_number,1,2)
133 INTO dummy1
134 FROM dual;
135
136 IF (dummy1 <> 'NP' OR
137 length(l_transmitter_number) <> 8) THEN
138 RAISE INVALID_NUMBER;
139 END IF;
140
141 SELECT to_number(substr(l_transmitter_number,3,6))
142 INTO dummy
143 FROM dual;
144
145 EXCEPTION
146 WHEN INVALID_NUMBER THEN
147 hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
148 hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
149 pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
150 pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
151 hr_utility.raise_error;
152 END;
153
154 l_type_of_package := pay_ca_rl2_mag.get_transmitter_item(p_bg_id,
155 l_arch_pactid,
156 'CAEOY_RL2_TRANSMITTER_PACKAGE_TYPE');
157
158 hr_utility.trace('l_type_of_package = ' || l_type_of_package);
159
160 IF l_type_of_package IS NULL THEN
161 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
162 hr_utility.raise_error;
163 END IF;
164
165 l_source_of_slips := pay_ca_rl2_mag.get_transmitter_item(p_bg_id,
166 l_arch_pactid,
167 'CAEOY_RL2_SOURCE_OF_SLIPS');
168 hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
169
170 IF l_source_of_slips IS NULL THEN
171 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
172 hr_utility.raise_error;
173 END IF;
174
175 l_address_line1 := pay_ca_rl2_mag.get_transmitter_item(p_bg_id,
176 l_arch_pactid,
177 'CAEOY_RL2_TRANSMITTER_ADDRESS_LINE1');
178 hr_utility.trace('l_address_line1 = ' || l_address_line1);
179
180 IF l_address_line1 IS NULL THEN
181 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
182 hr_utility.raise_error;
183 END IF;
184
185 END;
186
187 END validate_transmitter_info;
188
189
190 -----------------------------------------------------------------------------
191 --Name
192 -- range_cursor
193 --Purpose
194 -- This procedure defines a SQL statement
195 -- to fetch all the people to be included in the report. This SQL statement
196 -- is used to define the 'chunks' for multi-threaded operation
197 --Arguments
198 -- p_pactid payroll action id for the report
199 -- p_sqlstr the SQL statement to fetch the people
200 ------------------------------------------------------------------------------
201 PROCEDURE range_cursor (
202 p_pactid IN NUMBER,
203 p_sqlstr OUT NOCOPY VARCHAR2
204 )
205 IS
206 p_year_start DATE;
207 p_year_end DATE;
208 p_business_group_id NUMBER;
209 p_report_type VARCHAR2(30);
210 p_legislative_param pay_payroll_actions.legislative_parameters%type;
211
212 BEGIN
213
214 hr_utility.set_location( 'pay_ca_rl2_mag.range_cursor', 10);
215
216 get_report_parameters(
217 p_pactid,
218 p_year_start,
219 p_year_end,
220 p_report_type,
221 p_business_group_id,
222 p_legislative_param
223 );
224
225 hr_utility.set_location( 'pay_ca_rl2_mag.range_cursor', 20);
226
227 p_sqlstr := 'select distinct to_number(emp.person_id)
228 from pay_ca_eoy_rl2_employee_info_v emp,
229 pay_ca_eoy_rl2_trans_info_v tran,
230 pay_assignment_actions paa_arch,
231 pay_payroll_actions ppa_arch,
232 pay_payroll_actions ppa_mag,
233 hr_organization_information hoi
234 where ppa_mag.payroll_action_id = :payroll_action_id
235 and ppa_arch.business_group_id+0 = ppa_mag.business_group_id
236 and ppa_arch.effective_date = ppa_mag.effective_date
237 and ppa_arch.report_type = ''RL2''
238 and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
239 and tran.reporting_year = to_char(ppa_arch.effective_date,''YYYY'')
240 and tran.business_group_id = ppa_arch.business_group_id
241 and tran.reporting_year = pay_ca_rl2_mag.get_parameter(''REPORTING_YEAR'',ppa_mag.legislative_parameters)
242 and paa_arch.payroll_action_id = tran.payroll_action_id
243 and paa_arch.action_status = ''C''
244 and paa_arch.assignment_action_id = emp.assignment_action_id
245 and paa_arch.payroll_action_id = emp.payroll_action_id
246 and emp.business_group_id = ppa_arch.business_group_id
247 and decode(hoi.org_information3, ''Y'', hoi.organization_id, hoi.org_information20) =
248 pycadar_pkg.get_parameter(''TRANSMITTER_PRE'', ppa_mag.legislative_parameters )
249 and hoi.org_information_context =''Prov Reporting Est''
250 and to_char(hoi.organization_id) =
251 pycadar_pkg.get_parameter(''PRE_ORGANIZATION_ID'',ppa_arch.legislative_parameters)
252 order by to_number(emp.person_id)' ;
253
254 hr_utility.set_location( 'pay_ca_rl2_mag.range_cursor',30);
255
256 END range_cursor;
257
258 -------------------------------------------------------------------------------
259 --Name
260 -- create_assignment_act
261 --Purpose
262 -- Creates assignment actions for the payroll action associated with the
263 -- report
264 --Arguments
265 -- p_pactid payroll action for the report
266 -- p_stperson starting person id for the chunk
267 -- p_endperson last person id for the chunk
268 -- p_chunk size of the chunk
269 --Note
270 -- The procedure processes assignments in 'chunks' to facilitate
271 -- multi-threaded operation. The chunk is defined by the size and the
272 -- starting and ending person id. An interlock is also created against the
273 -- pre-processor assignment action to prevent rolling back of the archiver.
274 ------------------------------------------------------------------------------
275 PROCEDURE create_assignment_act(
276 p_pactid IN NUMBER,
277 p_stperson IN NUMBER,
278 p_endperson IN NUMBER,
279 p_chunk IN NUMBER )
280 IS
281 -- Cursor to retrieve all the assignments for all GRE's
282 -- archived in a reporting year
283
284 CURSOR c_all_asg(p_leg_param varchar2,
285 p_business_grpid number,
286 p_effective_dt date) IS
287 SELECT paf.person_id,
288 paf.assignment_id,
289 paa_arch.tax_unit_id,
290 paf.effective_end_date,
291 paa_arch.assignment_action_id,
292 ppa_arch.payroll_action_id
293 FROM pay_payroll_actions ppa_arch,
294 pay_assignment_actions paa_arch,
295 per_all_assignments_f paf,
296 hr_organization_information hoi
297 WHERE ppa_arch.report_type = 'RL2'
298 AND ppa_arch.business_group_id+0 = p_business_grpid
299 AND ppa_arch.effective_date = p_effective_dt
300 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
301 AND paa_arch.action_status = 'C'
302 AND paf.assignment_id = paa_arch.assignment_id
303 AND paf.person_id BETWEEN p_stperson AND p_endperson
304 AND paf.effective_start_date <= ppa_arch.effective_date
305 AND paf.effective_end_date >= ppa_arch.start_date
306 AND decode(hoi.org_information3, 'Y', hoi.organization_id, hoi.org_information20) =
307 substr(p_leg_param, instr(p_leg_param,'TRANSMITTER_PRE=')+16)
308 AND hoi.org_information_context = 'Prov Reporting Est'
309 AND hoi.organization_id =
310 substr(ppa_arch.legislative_parameters,
311 instr(ppa_arch.legislative_parameters,'PRE_ORGANIZATION_ID=')+20)
312 AND paf.effective_end_date = (SELECT max(paf1.effective_end_date)
313 FROM per_all_assignments_f paf1
314 WHERE paf1.assignment_id = paf.assignment_id
318 l_year_end DATE;
315 AND paf1.effective_start_date <= p_effective_dt);
316
317 l_year_start DATE;
319 l_effective_end_date DATE;
320 l_report_type VARCHAR2(30);
321 l_business_group_id NUMBER;
322 l_person_id NUMBER;
323 l_assignment_id NUMBER;
324 l_assignment_action_id NUMBER;
325 l_value NUMBER;
326 l_tax_unit_id NUMBER;
327 lockingactid NUMBER;
328 /* Added by ssmukher */
329 l_prev_payact NUMBER;
330 l_payroll_act NUMBER;
331 l_emplyer_name VARCHAR2(240);
332 l_quebec_no VARCHAR2(20);
333 l_file_no VARCHAR2(10);
334 l_return NUMBER;
335 l_addr_line VARCHAR2(240);
336 l_legislative_param pay_payroll_actions.legislative_parameters%type;
337
338 BEGIN
339
340 -- hr_utility.trace_on(NULL,'RL2MAG');
341 -- Get the report parameters. These define the report being run.
342 l_prev_payact := -1;
343 hr_utility.set_location( 'pay_ca_rl2_mag.create_assignment_act',10);
344
345 get_report_parameters(
346 p_pactid,
347 l_year_start,
348 l_year_end,
349 l_report_type,
350 l_business_group_id,
351 l_legislative_param
352 );
353 /* Validating Transmitter Information */
354 validate_transmitter_info(p_pactid,
355 l_business_group_id,
356 l_year_end);
357
358 --Open the appropriate cursor
359
360 hr_utility.set_location( 'pay_ca_rl2_mag.create_assignment_act',20);
361 hr_utility.trace('Report type '||l_report_type);
362 IF l_report_type = 'RL2_XML_MAG' THEN
363 OPEN c_all_asg(l_legislative_param,
364 l_business_group_id,
365 l_year_end);
366 LOOP
367 FETCH c_all_asg INTO l_person_id,
368 l_assignment_id,
369 l_tax_unit_id,
370 l_effective_end_date,
371 l_assignment_action_id,
372 l_payroll_act;
373
374 hr_utility.set_location('pay_ca_rl2_mag.create_assignment_act', 30);
375
376 EXIT WHEN c_all_asg%NOTFOUND;
377
378 --Create the assignment action for the record
379
380 /* Validating QIN Number information */
381 if l_prev_payact <> l_payroll_act then
382
383 hr_utility.trace('The payroll action id '||l_payroll_act);
384
385 l_prev_payact := l_payroll_act;
386 l_emplyer_name := pay_ca_rl2_mag.get_employer_item(l_business_group_id,
387 l_payroll_act,
388 'CAEOY_RL2_EMPLOYER_NAME');
389
390 l_quebec_no := pay_ca_rl2_mag.get_employer_item(l_business_group_id,
391 l_payroll_act,
392 'CAEOY_RL2_QUEBEC_BN');
393 hr_utility.trace('The Quebec Number is '||l_quebec_no);
394 l_file_no := substr(l_quebec_no,13,4);
395 l_quebec_no := substr(l_quebec_no ,1,10);
396
397 /* Fix for Bug# 4038551 */
398 if (l_file_no = '0000' and l_quebec_no = '0000000000') or
399 length(l_file_no) < 4
400 then
401 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
402 pay_core_utils.push_token('PRE_NAME',l_emplyer_name);
403 hr_utility.raise_error;
404 end if;
405
406
407 /* Erroring out the RL2 Electronic Interface if any of the
408 mandatory information is missing i.e Address Line 1 */
409
410 l_addr_line := pay_ca_rl2_mag.get_employer_item(l_business_group_id,
411 l_payroll_act,
412 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE1');
413 if l_addr_line = ' '
414 then
415 pay_core_utils.push_message(800,'PAY_CA_RL2_MISSING_ADDRESS','P');
416 hr_utility.raise_error;
417 end if;
418 hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
419 l_return := validate_quebec_number(l_quebec_no,l_emplyer_name);
420
421 end if ;
422 hr_utility.trace('Assignment Fetched - ');
423 hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
424 hr_utility.trace('Person Id : '|| to_char(l_person_id));
425 hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
426 hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
427
428 hr_utility.set_location('pay_ca_rl2_mag.create_assignment_act', 40);
429
430 SELECT pay_assignment_actions_s.nextval
431 INTO lockingactid
432 FROM dual;
433
437 p_pactid,
434 hr_utility.set_location('pay_ca_rl2_mag.create_assignment_act', 50);
435 hr_nonrun_asact.insact(lockingactid,
436 l_assignment_id,
438 p_chunk,
439 l_tax_unit_id);
440
441 hr_utility.set_location('pay_ca_rl2_mag.create_assignment_act', 60);
442
443 hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
444
445 hr_utility.set_location('pay_ca_rl2_mag.create_assignment_act', 70);
446 hr_utility.trace('Interlock Created - ');
447 hr_utility.trace('Locking Action : '|| to_char(lockingactid));
448 hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
449
450 END LOOP;
451 CLOSE c_all_asg;
452
453 END IF;
454
455 END create_assignment_act;
456
457 FUNCTION get_parameter(name IN varchar2, parameter_list varchar2)
458 RETURN varchar2 IS
459 start_ptr number;
460 end_ptr number;
461 token_val pay_payroll_actions.legislative_parameters%type;
462 par_value pay_payroll_actions.legislative_parameters%type;
463 BEGIN
464 --
465 token_val := name||'=';
466 --
467 start_ptr := instr(parameter_list, token_val) + length(token_val);
468 end_ptr := instr(parameter_list, ' ',start_ptr);
469 --
470 /* if there is no spaces use then length of the string */
471 IF end_ptr = 0 THEN
472 end_ptr := length(parameter_list)+1;
473 END IF;
474 --
475 /* Did we find the token */
476 IF instr(parameter_list, token_val) = 0 THEN
477 par_value := NULL;
478 ELSE
479 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
480 END IF;
481
482 RETURN par_value;
483
484 END get_parameter;
485
486
487 FUNCTION get_transmitter_item (p_business_group_id IN number,
488 p_pact_id IN number,
489 p_archived_item IN varchar2)
490 RETURN varchar2 IS
491
492 CURSOR c_trans_info IS
493 SELECT nvl(transmitter_number,' '),
494 nvl(reporting_year,'0000'),
495 nvl(transmitter_package_type,'0'),
496 nvl(transmitter_type_indicator,'0'),
497 nvl(transmitter_name,' '),
498 nvl(source_of_slips,' '),
499 nvl(transmitter_address_line1,' '),
500 nvl(transmitter_address_line2,' '),
501 nvl(transmitter_city,' '),
502 nvl(transmitter_province,' '),
503 nvl(transmitter_postal_code,' '),
504 nvl(transmitter_tech_contact_name,' '),
505 nvl(transmitter_tech_contact_code,'000'),
506 nvl(transmitter_tech_contact_phone,'0000000'),
507 nvl(transmitter_tech_contact_extn,'0000'),
508 nvl(transmitter_tech_contact_lang,' '),
509 nvl(transmitter_acct_contact_name,' '),
510 nvl(transmitter_acct_contact_code,'000'),
511 nvl(transmitter_acct_contact_phone,'0000000'),
512 nvl(transmitter_acct_contact_extn,'0000'),
513 nvl(transmitter_acct_contact_lang,' ')
514 FROM pay_ca_eoy_rl2_trans_info_v
515 WHERE business_group_id = p_business_group_id
516 AND payroll_action_id = p_pact_id;
517
518 l_trans_number varchar2(240);
519 l_reporting_year varchar2(240);
520 l_trans_package_type varchar2(240);
521 l_trans_type_indicator varchar2(240);
522 l_trans_name varchar2(240);
523 l_source_of_slips varchar2(240);
524 l_trans_address_line1 varchar2(240);
525 l_trans_address_line2 varchar2(240);
526 l_trans_city varchar2(240);
527 l_trans_province varchar2(240);
528 l_trans_postal_code varchar2(240);
529 l_trans_tech_contact_name varchar2(240);
530 l_trans_tech_contact_code varchar2(240);
531 l_trans_tech_contact_phone varchar2(240);
532 l_trans_tech_contact_extn varchar2(240);
533 l_trans_tech_contact_lang varchar2(240);
534 l_trans_acct_contact_name varchar2(240);
535 l_trans_acct_contact_code varchar2(240);
536 l_trans_acct_contact_phone varchar2(240);
537 l_trans_acct_contact_extn varchar2(240);
538 l_trans_acct_contact_lang varchar2(240);
539
540 l_return_value varchar2(240);
541
542 BEGIN
543
544 OPEN c_trans_info;
545 FETCH c_trans_info
546 INTO l_trans_number,
547 l_reporting_year,
548 l_trans_package_type,
549 l_trans_type_indicator,
550 l_trans_name,
551 l_source_of_slips,
552 l_trans_address_line1,
553 l_trans_address_line2,
554 l_trans_city,
555 l_trans_province,
556 l_trans_postal_code,
557 l_trans_tech_contact_name,
558 l_trans_tech_contact_code,
559 l_trans_tech_contact_phone,
560 l_trans_tech_contact_extn,
561 l_trans_tech_contact_lang,
562 l_trans_acct_contact_name,
563 l_trans_acct_contact_code,
564 l_trans_acct_contact_phone,
565 l_trans_acct_contact_extn,
566 l_trans_acct_contact_lang;
567
568 CLOSE c_trans_info;
572 ELSIF p_archived_item = 'CAEOY_TAXATION_YEAR' THEN
569
570 IF p_archived_item = 'CAEOY_RL2_TRANSMITTER_NUMBER' THEN
571 l_return_value := l_trans_number;
573 l_return_value := l_reporting_year;
574 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_PACKAGE_TYPE' THEN
575 l_return_value := l_trans_package_type;
576 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_TYPE' THEN
577 l_return_value := l_trans_type_indicator;
578 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_NAME' THEN
579 l_return_value := l_trans_name;
580 ELSIF p_archived_item = 'CAEOY_RL2_SOURCE_OF_SLIPS' THEN
581 l_return_value := l_source_of_slips;
582 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_ADDRESS_LINE1' THEN
583 l_return_value := l_trans_address_line1;
584 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_ADDRESS_LINE2' THEN
585 l_return_value := l_trans_address_line2;
586 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_CITY' THEN
587 l_return_value := l_trans_city;
588 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_PROVINCE' THEN
589 l_return_value := l_trans_province;
590 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_POSTAL_CODE' THEN
591 l_return_value := l_trans_postal_code;
592 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_NAME' THEN
593 l_return_value := l_trans_tech_contact_name;
594 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_AREA_CODE' THEN
595 l_return_value := l_trans_tech_contact_code;
596 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_PHONE' THEN
597 l_return_value := l_trans_tech_contact_phone;
598 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_EXTENSION' THEN
599 l_return_value := l_trans_tech_contact_extn;
600 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_LANGUAGE' THEN
601 l_return_value := l_trans_tech_contact_lang;
602 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_NAME' THEN
603 l_return_value := l_trans_acct_contact_name;
604 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_AREA_CODE' THEN
605 l_return_value := l_trans_acct_contact_code;
606 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_PHONE' THEN
607 l_return_value := l_trans_acct_contact_phone;
608 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_EXTENSION' THEN
609 l_return_value := l_trans_acct_contact_extn;
610 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_LANGUAGE' THEN
611 l_return_value := l_trans_acct_contact_lang;
612 END IF;
613
614 RETURN l_return_value;
615
616 END get_transmitter_item;
617
618 FUNCTION get_employer_item (p_business_group_id IN number,
619 p_pact_id IN number,
620 p_archived_item IN varchar2)
621 RETURN varchar2 IS
622
623 CURSOR c_employer_info IS
624 SELECT nvl(employer_name,' '),
625 nvl(quebec_business_number,'0000000000 0000'),
626 nvl(reporting_year,'0000'),
627 nvl(employer_add_line1,' '),
628 nvl(employer_add_line2,' '),
629 nvl(employer_city,' '),
630 nvl(employer_province,' '),
631 nvl(employer_postal_code,' ')
632 FROM pay_ca_eoy_rl2_trans_info_v
633 WHERE business_group_id = p_business_group_id
634 AND payroll_action_id = p_pact_id;
635
636 l_employer_name varchar2(240);
637 l_reporting_year varchar2(240);
638 l_quebec_business_number varchar2(240);
639 l_employer_add_line1 varchar2(240);
640 l_employer_add_line2 varchar2(240);
641 l_employer_city varchar2(240);
642 l_employer_province varchar2(240);
643 l_employer_postal_code varchar2(240);
644
645 l_return_value varchar2(240);
646
647 BEGIN
648
649 OPEN c_employer_info;
650 FETCH c_employer_info
651 INTO l_employer_name,
652 l_quebec_business_number,
653 l_reporting_year,
654 l_employer_add_line1,
655 l_employer_add_line2,
656 l_employer_city,
657 l_employer_province,
658 l_employer_postal_code;
659
660 CLOSE c_employer_info;
661
662 IF p_archived_item = 'CAEOY_RL2_QUEBEC_BN' THEN
663 l_return_value := l_quebec_business_number;
664 ELSIF p_archived_item = 'CAEOY_TAXATION_YEAR' THEN
665 l_return_value := l_reporting_year;
666 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_NAME' THEN
667 l_return_value := l_employer_name;
668 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE1' THEN
669 l_return_value := l_employer_add_line1;
670 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE2' THEN
671 l_return_value := l_employer_add_line2;
672 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_CITY' THEN
673 l_return_value := l_employer_city;
674 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_PROVINCE' THEN
675 l_return_value := l_employer_province;
676 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_POSTAL_CODE' THEN
677 l_return_value := l_employer_postal_code;
678 END IF;
679
680 RETURN l_return_value;
681
682 END get_employer_item;
683
684
685 PROCEDURE end_of_file is
686
687 BEGIN
688
689 DECLARE
690
691 l_final_xml_string VARCHAR2(32000);
692
696 pay_core_files.write_to_magtape_lob(l_final_xml_string);
693 BEGIN
694
695 l_final_xml_string := '</Transmission>';
697
698 END;
699
700 END;
701
702 PROCEDURE xml_transmitter_record IS
703 BEGIN
704
705 DECLARE
706
707 CURSOR c_trans_info(p_business_group_id IN number,
708 p_pact_id IN number) IS
709 SELECT nvl(transmitter_number,' '),
710 nvl(reporting_year,'0000'),
711 nvl(transmitter_package_type,'0'),
712 nvl(transmitter_type_indicator,'0'),
713 nvl(transmitter_name,' '),
714 nvl(source_of_slips,' '),
715 nvl(transmitter_address_line1,' '),
716 nvl(transmitter_address_line2,' '),
717 nvl(transmitter_city,' '),
718 nvl(transmitter_province,' '),
719 nvl(transmitter_postal_code,' '),
720 nvl(transmitter_tech_contact_name,' '),
721 nvl(transmitter_tech_contact_code,'000'),
722 nvl(transmitter_tech_contact_phone,'0000000'),
723 nvl(transmitter_tech_contact_extn,'0000'),
724 nvl(transmitter_tech_contact_lang,' '),
725 nvl(transmitter_acct_contact_name,' '),
726 nvl(transmitter_acct_contact_code,'000'),
727 nvl(transmitter_acct_contact_phone,'0000000'),
728 nvl(transmitter_acct_contact_extn,'0000'),
729 nvl(transmitter_acct_contact_lang,' ')
730 FROM pay_ca_eoy_rl2_trans_info_v
731 WHERE business_group_id = p_business_group_id
732 AND payroll_action_id = p_pact_id;
733
734 l_trans_number varchar2(240);
735 l_reporting_year varchar2(240);
736 l_trans_package_type varchar2(240);
737 l_trans_type_indicator varchar2(240);
738 l_trans_name varchar2(240);
739 l_source_of_slips varchar2(240);
740 l_trans_address_line1 varchar2(240);
741 l_trans_address_line2 varchar2(240);
742 l_trans_city varchar2(240);
743 l_trans_province varchar2(240);
744 l_trans_postal_code varchar2(240);
745 l_trans_tech_contact_name varchar2(240);
746 l_trans_tech_contact_code varchar2(240);
747 l_trans_tech_contact_phone varchar2(240);
748 l_trans_tech_contact_extn varchar2(240);
749 l_trans_tech_contact_lang varchar2(240);
750 l_trans_acct_contact_name varchar2(240);
751 l_trans_acct_contact_code varchar2(240);
752 l_trans_acct_contact_phone varchar2(240);
753 l_trans_acct_contact_extn varchar2(240);
754 l_trans_acct_contact_lang varchar2(240);
755
756 l_final_xml_string VARCHAR2(32000);
757 l_tech_accnt_info VARCHAR2(32000);
758
759 TYPE transmitter_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
760
761 tab_transmitter transmitter_info;
762
763 lAnnee NUMBER;
764 lTypeEnvoi NUMBER;
765 lProvenance NUMBER;
766 lNo NUMBER;
767 lType NUMBER;
768 lNom1 NUMBER;
769 lNom2 NUMBER;
770 lLigne1 NUMBER;
771 lLigne2 NUMBER;
772 lVille NUMBER;
773 lProvince NUMBER;
774 lCodePostal NUMBER;
775 lNom NUMBER;
776 lIndRegional NUMBER;
777 lTel NUMBER;
778 lPosteTel NUMBER;
779 lLangue NUMBER;
780 lANom NUMBER;
781 lAIndRegional NUMBER;
782 lATel NUMBER;
783 lAPosteTel NUMBER;
784 lALangue NUMBER;
785
786 EOL VARCHAR2(5);
787 l_transmitter_name VARCHAR2(100);
788 l_taxation_year VARCHAR2(4);
789 l_return VARCHAR2(60);
790 l_payroll_actid NUMBER;
791 l_year_start DATE;
792 l_year_end DATE;
793 l_report_type VARCHAR2(5);
794 l_business_grpid NUMBER;
795 l_legislative_param pay_payroll_actions.legislative_parameters%type;
796 /* Bug 4777374 Fix */
797 l_Informatique_tag CHAR(1);
798 l_Comptabilite_tag CHAR(1);
799 /* Bug 4906963 Fix */
800 l_authorization_no VARCHAR2(20);
801 lNoConcepteur NUMBER;
802 BEGIN
803
804 hr_utility.trace('XML Transmitter');
805
806
807 SELECT
808 fnd_global.local_chr(13) || fnd_global.local_chr(10)
809 INTO EOL
810 FROM dual;
811
812 lAnnee := 1;
813 lTypeEnvoi := 2;
814 lProvenance := 3;
815 lNo := 4;
816 lType := 5;
817 lNom1 := 6;
818 lNom2 := 7;
819 lLigne1 := 8;
820 lLigne2 := 9;
821 lVille := 10;
822 lProvince := 11;
823 lCodePostal := 12;
824 lNom := 13;
825 lIndRegional := 14;
826 lTel := 15;
827 lPosteTel := 16;
828 lLangue := 17;
829 lANom := 18;
830 lAIndRegional := 19;
831 lATel := 20;
832 lAPosteTel := 21;
833 lALangue := 22;
834 lNoConcepteur := 23;
835
836 l_Informatique_tag := 'N';
837 l_Comptabilite_tag := 'N';
838
839 l_taxation_year
840 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
841 l_payroll_actid
842 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
843
844 get_report_parameters(
848 l_report_type,
845 l_payroll_actid,
846 l_year_start,
847 l_year_end,
849 l_business_grpid,
850 l_legislative_param
851 );
852
853
854 hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
855 hr_utility.trace('XML Transmitter: l_payroll_Action_id = ' || to_char(l_payroll_actid));
856
857 OPEN c_trans_info(l_business_grpid,
858 l_payroll_actid);
859 FETCH c_trans_info
860 INTO l_trans_number,
861 l_reporting_year,
862 l_trans_package_type,
863 l_trans_type_indicator,
864 l_trans_name,
865 l_source_of_slips,
866 l_trans_address_line1,
867 l_trans_address_line2,
868 l_trans_city,
869 l_trans_province,
870 l_trans_postal_code,
871 l_trans_tech_contact_name,
872 l_trans_tech_contact_code,
873 l_trans_tech_contact_phone,
874 l_trans_tech_contact_extn,
875 l_trans_tech_contact_lang,
876 l_trans_acct_contact_name,
877 l_trans_acct_contact_code,
878 l_trans_acct_contact_phone,
879 l_trans_acct_contact_extn,
880 l_trans_acct_contact_lang;
881
882 CLOSE c_trans_info;
883 -- Annee
884 tab_transmitter(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' ||EOL;
885 hr_utility.trace('tab_transmitter(lAnnee) = ' || tab_transmitter(lAnnee));
886
887 -- TypeEnvoi
888 tab_transmitter(lTypeEnvoi) := '<TypeEnvoi>' ||
889 convert_special_char(l_trans_package_type) || '</TypeEnvoi>' || EOL;
890 hr_utility.trace('tab_transmitter(lTypeEnvoi) = ' ||
891 tab_transmitter(lTypeEnvoi));
892
893 tab_transmitter(lProvenance) := '<Provenance>' ||
894 convert_special_char(l_source_of_slips) || '</Provenance>' || EOL;
895
896 hr_utility.trace('tab_transmitter(lProvenance) = ' || tab_transmitter(lProvenance));
897
898 tab_transmitter(lNo) := '<No>' ||
899 convert_special_char(l_trans_number) || '</No>' || EOL;
900
901 hr_utility.trace('tab_transmitter(lNo) = ' || tab_transmitter(lNo));
902
903
904 IF l_trans_type_indicator IS NOT NULL AND
905 l_trans_type_indicator <> '0' THEN
906 tab_transmitter(lType) := '<Type>' ||
907 convert_special_char(l_trans_type_indicator) || '</Type>' || EOL;
908 ELSE
909 tab_transmitter(lType) := NULL;
910 END IF;
911
912 hr_utility.trace('tab_transmitter(lType) = ' || tab_transmitter(lType));
913
914 tab_transmitter(lNom1) := '<Nom1>' ||
915 convert_special_char(substr(l_trans_name,1,30)) || '</Nom1>' || EOL;
916
917 hr_utility.trace('tab_transmitter(lNom1) = ' || tab_transmitter(lNom1));
918
919 l_return := substr(l_trans_name,31,30);
920 IF l_return IS NOT NULL THEN
921 tab_transmitter(lNom2) := '<Nom2>' || convert_special_char(l_return) || '</Nom2>' || EOL;
922 ELSE
923 tab_transmitter(lNom2) := NULL;
924 END IF;
925
926 hr_utility.trace('tab_transmitter(lNom2) = ' || tab_transmitter(lNom2));
927
928
929 tab_transmitter(lLigne1) := '<Ligne1>' ||
930 convert_special_char(substr(l_trans_address_line1,1,30)) || '</Ligne1>' || EOL;
931
932 hr_utility.trace('tab_transmitter(lLigne1) = ' || tab_transmitter(lLigne1));
933
934
935 IF (l_trans_address_line2 IS NOT NULL AND
936 l_trans_address_line2 <> ' ') THEN
937 tab_transmitter(lLigne2) := '<Ligne2>' ||
938 convert_special_char(substr(l_trans_address_line2,1,30)) || '</Ligne2>' || EOL;
939 ELSE
940 tab_transmitter(lLigne2) := NULL;
941 END IF;
942
943 hr_utility.trace('tab_transmitter(lLigne2) = ' || tab_transmitter(lLigne2));
944
945
946 IF (l_trans_city IS NOT NULL AND
947 l_trans_city <> ' ') THEN
948 tab_transmitter(lVille) := '<Ville>' ||
949 convert_special_char(substr(l_trans_city,1,30)) || '</Ville>' || EOL;
950 ELSE
951 tab_transmitter(lVille) := NULL;
952 END IF;
953
954 hr_utility.trace('tab_transmitter(lVille) = ' || tab_transmitter(lVille));
955
956 IF (l_trans_province IS NOT NULL AND
957 l_trans_province <> ' ') THEN
958 tab_transmitter(lProvince) := '<Province>' ||
959 convert_special_char(SUBSTR(hr_general.decode_lookup(
960 'CA_PROVINCE',l_trans_province),1,20)) || '</Province>' || EOL;
961 ELSE
962 tab_transmitter(lProvince) := NULL;
963 END IF;
964 hr_utility.trace('tab_transmitter(lProvince) = ' || tab_transmitter(lProvince));
965
966 IF (l_trans_postal_code IS NOT NULL AND
967 l_trans_postal_code <> ' ') THEN
968 tab_transmitter(lCodePostal) := '<CodePostal>' ||
969 convert_special_char(substr(l_trans_postal_code,1,6)) || '</CodePostal>' || EOL;
970 ELSE
971 tab_transmitter(lCodePostal) := NULL;
972 END IF;
973 hr_utility.trace('tab_transmitter(lCodePostal) = ' || tab_transmitter(lCodePostal));
974
975
976 IF (l_trans_tech_contact_name IS NOT NULL AND
980 convert_special_char(substr(l_trans_tech_contact_name,1,30)) || '</Nom>' || EOL;
977 l_trans_tech_contact_name <> ' ' ) THEN
978 l_Informatique_tag := 'Y';
979 tab_transmitter(lNom) := '<Nom>' ||
981 ELSE
982 tab_transmitter(lNom) := NULL;
983 END IF;
984
985 hr_utility.trace('tab_transmitter(lNom) = ' || tab_transmitter(lNom));
986
987
988 IF (l_trans_tech_contact_code IS NOT NULL AND
989 l_trans_tech_contact_code <> '000' ) THEN
990 l_Informatique_tag := 'Y';
991 tab_transmitter(lIndRegional) := '<IndRegional>' ||
992 convert_special_char(l_trans_tech_contact_code) || '</IndRegional>' || EOL;
993 ELSE
994 tab_transmitter(lIndRegional) := NULL;
995 END IF;
996
997 hr_utility.trace('tab_transmitter(lIndRegional) = ' || tab_transmitter(lIndRegional));
998
999 IF (l_trans_tech_contact_phone IS NOT NULL AND
1000 l_trans_tech_contact_phone <> '0000000' ) THEN
1001 l_Informatique_tag := 'Y';
1002 l_trans_tech_contact_phone := substr(l_trans_tech_contact_phone,1,3) || '-' || substr(l_trans_tech_contact_phone,4,4);
1003 tab_transmitter(lTel) := '<Tel>' || convert_special_char(l_trans_tech_contact_phone) || '</Tel>' || EOL;
1004 ELSE
1005 tab_transmitter(lTel) := NULL;
1006 END IF;
1007 hr_utility.trace('tab_transmitter(lTel) = ' || tab_transmitter(lTel));
1008
1009
1010 IF (l_trans_tech_contact_extn IS NOT NULL AND
1011 l_trans_tech_contact_extn <> '0000' ) THEN
1012 l_Informatique_tag := 'Y';
1013 tab_transmitter(lPosteTel) := '<PosteTel>' || convert_special_char(l_trans_tech_contact_extn) ||
1014 '</PosteTel>' || EOL;
1015 ELSE
1016 tab_transmitter(lPosteTel) := NULL;
1017 END IF;
1018 hr_utility.trace('tab_transmitter(lPosteTel) = ' ||
1019 tab_transmitter(lPosteTel));
1020
1021
1022 IF (l_trans_tech_contact_lang IS NOT NULL AND
1023 l_trans_tech_contact_lang <> ' ' ) THEN
1024 l_Informatique_tag := 'Y';
1025 tab_transmitter(lLangue) := '<Langue>' ||convert_special_char(l_trans_tech_contact_lang) || '</Langue>' || EOL;
1026 ELSE
1027 tab_transmitter(lLangue) := NULL;
1028 END IF;
1029
1030
1031 IF (l_trans_acct_contact_name IS NOT NULL AND
1032 l_trans_acct_contact_name <> ' ') THEN
1033 l_Comptabilite_tag := 'Y';
1034 tab_transmitter(lANom) := '<Nom>' ||
1035 convert_special_char(substr(l_trans_acct_contact_name,1,30)) || '</Nom>' || EOL;
1036 ELSE
1037 tab_transmitter(lANom) := NULL;
1038 END IF;
1039 hr_utility.trace('tab_transmitter(lANom) = ' || tab_transmitter(lANom));
1040
1041
1042 IF (l_trans_acct_contact_code IS NOT NULL AND
1043 l_trans_acct_contact_code <> '000' ) THEN
1044 l_Comptabilite_tag := 'Y';
1045 tab_transmitter(lAIndRegional) := '<IndRegional>' || convert_special_char(l_trans_acct_contact_code) ||
1046 '</IndRegional>' || EOL;
1047 ELSE
1048 tab_transmitter(lAIndRegional) := NULL;
1049 END IF;
1050 hr_utility.trace('tab_transmitter(lAIndRegional) = ' || tab_transmitter(lAIndRegional));
1051
1052
1053 IF (l_trans_acct_contact_phone IS NOT NULL AND
1054 l_trans_acct_contact_phone <> '0000000' ) THEN
1055 l_Comptabilite_tag := 'Y';
1056 l_trans_acct_contact_phone := substr(l_trans_acct_contact_phone,1,3) || '-' || substr(l_trans_acct_contact_phone,4,4);
1057 tab_transmitter(lATel) := '<Tel>' || convert_special_char(l_trans_acct_contact_phone) || '</Tel>' || EOL;
1058 ELSE
1059 tab_transmitter(lATel) := NULL;
1060 END IF;
1061 hr_utility.trace('tab_transmitter(lATel) = ' || tab_transmitter(lATel));
1062
1063
1064 IF (l_trans_acct_contact_extn IS NOT NULL AND
1065 l_trans_acct_contact_extn <> '0000') THEN
1066 l_Comptabilite_tag := 'Y';
1067 tab_transmitter(lAPosteTel) := '<PosteTel>' || convert_special_char(l_trans_acct_contact_extn) ||
1068 '</PosteTel>' || EOL;
1069 ELSE
1070 tab_transmitter(lAPosteTel) := NULL;
1071 END IF;
1072 hr_utility.trace('tab_transmitter(lAPosteTel) = ' ||
1073 tab_transmitter(lAPosteTel));
1074
1075 IF (l_trans_acct_contact_lang IS NOT NULL AND
1076 l_trans_acct_contact_lang <> ' ' ) THEN
1077 l_Comptabilite_tag := 'Y';
1078 tab_transmitter(lALangue) := '<Langue>' || convert_special_char(l_trans_acct_contact_lang) ||
1079 '</Langue>' || EOL;
1080 ELSE
1081 tab_transmitter(lALangue) := NULL;
1082 END IF;
1083
1084 --- Bug 6736354
1085 IF ( l_reporting_year = '2006' ) then
1086 l_authorization_no := 'RQ-06-02-048';
1087 ELSE
1088 l_authorization_no := 'RQ-07-02-069';
1089 END IF;
1090 --- End 6736354
1091
1092 tab_transmitter(lNoConcepteur) := '<NoCertification>'||convert_special_char(l_authorization_no)||'</NoCertification>'||EOL;
1093
1094 hr_utility.trace('tab_transmitter(lALangue) = ' || tab_transmitter(lALangue));
1095
1096 IF l_Informatique_tag = 'Y' AND
1097 l_Comptabilite_tag = 'Y' THEN
1098 l_tech_accnt_info := '<Informatique>' || EOL ||
1102 tab_transmitter(lPosteTel) ||
1099 tab_transmitter(lNom) ||
1100 tab_transmitter(lIndRegional) ||
1101 tab_transmitter(lTel) ||
1103 tab_transmitter(lLangue) || '</Informatique>' || EOL ||
1104 '<Comptabilite>' || EOL ||
1105 tab_transmitter(lANom) ||
1106 tab_transmitter(lAIndRegional) ||
1107 tab_transmitter(lATel) ||
1108 tab_transmitter(lAPosteTel) ||
1109 tab_transmitter(lALangue) || '</Comptabilite>' ;
1110 ELSIF l_Informatique_tag = 'Y' AND
1111 l_Comptabilite_tag = 'N' THEN
1112 l_tech_accnt_info := '<Informatique>' || EOL ||
1113 tab_transmitter(lNom) ||
1114 tab_transmitter(lIndRegional) ||
1115 tab_transmitter(lTel) ||
1116 tab_transmitter(lPosteTel) ||
1117 tab_transmitter(lLangue) || '</Informatique>';
1118 ELSIF l_Comptabilite_tag = 'Y' AND
1119 l_Informatique_tag = 'N' THEN
1120 l_tech_accnt_info := '<Comptabilite>' || EOL ||
1121 tab_transmitter(lANom) ||
1122 tab_transmitter(lAIndRegional) ||
1123 tab_transmitter(lATel) ||
1124 tab_transmitter(lAPosteTel) ||
1125 tab_transmitter(lALangue) || '</Comptabilite>';
1126 ELSE
1127 l_tech_accnt_info := NULL;
1128 END IF;
1129
1130 l_final_xml_string :=
1131 '<Transmission VersionSchema="2007.1.1" ' ||
1132 'pxmlns="http://www.mrq.gouv.qc.ca/T5">' || EOL ||
1133 '<P>' || EOL ||
1134 tab_transmitter(lAnnee) ||
1135 tab_transmitter(lTypeEnvoi) ||
1136 tab_transmitter(lProvenance) || '<Preparateur>' || EOL ||
1137 tab_transmitter(lNo) ||
1138 tab_transmitter(lType) ||
1139 tab_transmitter(lNom1) ||
1140 tab_transmitter(lNom2) || '<Adresse>' || EOL ||
1141 tab_transmitter(lLigne1) ||
1142 tab_transmitter(lLigne2) ||
1143 tab_transmitter(lVille) ||
1144 tab_transmitter(lProvince) ||
1145 tab_transmitter(lCodePostal) || '</Adresse>' || EOL ||
1146 '</Preparateur>' || EOL ||
1147 l_tech_accnt_info || EOL ||
1148 tab_transmitter(lNoConcepteur) ||
1149 '</P>' || EOL;
1150
1151 --hr_utility.trace('l_final_xml_string = ' || l_final_xml_string);
1152
1153 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1154 END;
1155 END xml_transmitter_record;
1156
1157
1158 PROCEDURE xml_employee_record IS
1159 BEGIN
1160
1161 DECLARE
1162
1163 l_final_xml_string VARCHAR2(32000);
1164 l_final_xml_string1 VARCHAR2(32000);
1165 l_final_xml_string2 VARCHAR2(32000);
1166
1167 CURSOR c_get_payroll_asg_actid(p_payactid NUMBER) IS
1168 SELECT
1169 to_number(substr(paa.serial_number,3,14)) payactid,
1170 to_number(substr(paa.serial_number,17,14)) asgactid,
1171 paa.assignment_id asgid
1172 FROM
1173 pay_assignment_actions paa
1174 WHERE paa.assignment_action_id = p_payactid;
1175
1176 CURSOR c_get_report_type(p_payactid NUMBER) IS
1177 SELECT
1178 ppa.report_type,
1179 ppa.business_group_id,
1180 ppa.legislative_parameters
1181 FROM
1182 pay_payroll_actions ppa
1183 WHERE
1184 ppa.payroll_action_id = p_payactid;
1185
1186 CURSOR c_get_employer_info(p_pact_id NUMBER,
1187 p_business_group_id NUMBER) IS
1188 SELECT nvl(employer_name,' '),
1189 nvl(quebec_business_number,'0000000000 0000'),
1190 nvl(reporting_year,'0000'),
1191 nvl(employer_add_line1,' '),
1192 nvl(employer_add_line2,' '),
1193 nvl(employer_add_line3,' '),
1194 nvl(employer_city,' '),
1195 nvl(employer_province,' '),
1196 nvl(employer_country,' '),
1197 nvl(employer_postal_code,' ')
1198 FROM
1199 pay_ca_eoy_rl2_trans_info_v
1200 WHERE
1201 business_group_id = p_business_group_id
1202 AND payroll_action_id = p_pact_id;
1203
1204 CURSOR cur_parameters(p_mag_asg_action_id NUMBER) IS
1205 SELECT
1206 pai.locked_action_id, -- Archiver asg_action_id
1207 paa.assignment_id,
1208 pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
1209 -- date_earned
1210 FROM
1211 pay_action_interlocks pai,
1212 pay_assignment_actions paa,
1213 pay_payroll_actions ppa,
1214 per_all_people_f ppf,
1215 per_all_assignments_f paf,
1216 pay_action_information pact
1217 WHERE paa.assignment_action_id = pai.locking_action_id
1218 AND paa.assignment_action_id = p_mag_asg_action_id
1219 AND paf.assignment_id = paa.assignment_id
1220 AND ppf.person_id = paf.person_id
1221 AND ppa.payroll_action_id = paa.payroll_action_id
1225 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
1222 AND pai.locked_action_id = pact.action_context_id
1223 AND pact.action_information_category = 'CAEOY RL2 EMPLOYEE INFO'
1224 AND pact.assignment_id = paa.assignment_id
1226 between paf.effective_start_date and paf.effective_end_date
1227 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
1228 between ppf.effective_start_date and ppf.effective_end_date
1229 ORDER BY
1230 ppf.last_name,ppf.first_name,ppf.middle_names;
1231
1232 CURSOR c_employee_infor (p_asg_action_id IN number)
1233 IS
1234 SELECT nvl(tran.quebec_business_number,'0000000000 0000'),
1235 nvl(tran.reporting_year,'0000'),
1236 nvl(emp.rl2_slip_number,'000000000'),
1237 nvl(emp.employee_sin,'000000000'),
1238 nvl(emp.employee_last_name,' '),
1239 nvl(emp.employee_first_name,' '),
1240 nvl(emp.employee_middle_initial,' '),
1241 nvl(emp.employee_address_line1,' '),
1242 nvl(emp.employee_address_line2,' '),
1243 nvl(emp.employee_address_line3,' '),
1244 nvl(emp.employee_city,' '),
1245 nvl(emp.employee_province,' '),
1246 nvl(emp.employee_postal_code,' '),
1247 nvl(emp.employee_number,' '),
1248 emp.rl2_box_a,
1249 emp.rl2_box_b,
1250 emp.rl2_box_c,
1251 emp.rl2_box_d,
1252 emp.rl2_box_e,
1253 emp.rl2_box_f,
1254 emp.rl2_box_g,
1255 emp.rl2_box_h,
1256 emp.rl2_box_i,
1257 emp.rl2_box_j,
1258 emp.rl2_box_k,
1259 emp.rl2_box_l,
1260 emp.rl2_box_m,
1261 emp.rl2_box_n,
1262 emp.rl2_box_o,
1263 decode(substr(emp.rl2_source_of_income,1,5),'OTHER','AUTRE', emp.rl2_source_of_income),
1264 nvl(emp.negative_balance_flag, 'N'),
1265 emp.person_id
1266 FROM pay_ca_eoy_rl2_employee_info_v emp,
1267 pay_ca_eoy_rl2_trans_info_v tran
1268 WHERE emp.assignment_action_id = p_asg_action_id
1269 AND emp.payroll_action_id = tran.payroll_action_id
1270 AND nvl(emp.rl2_source_of_income,1) not in --6525968
1271 (select lookup_code from hr_lookups hl, fnd_sessions fs
1272 where fs.session_id = USERENV('SESSIONID')
1273 and hl.lookup_type = 'PAY_CA_RL2_SOURCE_OF_INCOME'
1274 and (fs.effective_date > nvl(hl.end_date_active,to_date('31/12/4712','dd/mm/yyyy'))
1275 or hl.enabled_flag='N')
1276 ); --End 6525968
1277
1278 l_quebec_business_number varchar2(240);
1279 l_reporting_year varchar2(240);
1280 l_rl2_slip_number varchar2(240);
1281 l_employee_sin varchar2(240);
1282 l_employee_sin1 varchar2(240);
1283 l_employee_sin2 varchar2(240);
1284 l_employee_sin3 varchar2(240);
1285 l_employee_last_name varchar2(240);
1286 l_employee_first_name varchar2(240);
1287 l_employee_middle_initial varchar2(240);
1288 l_employee_address_line1 varchar2(240);
1289 l_employee_address_line2 varchar2(240);
1290 l_employee_address_line3 varchar2(240);
1291 l_employee_city varchar2(240);
1292 l_employee_province varchar2(240);
1293 l_employee_postal_code varchar2(240);
1294 l_employee_number varchar2(240);
1295 l_per_id varchar2(50);
1296 l_rl2_box_a varchar2(240);
1297 l_rl2_box_b varchar2(240);
1298 l_rl2_box_c varchar2(240);
1299 l_rl2_box_d varchar2(240);
1300 l_rl2_box_e varchar2(240);
1301 l_rl2_box_f varchar2(240);
1302 l_rl2_box_g varchar2(240);
1303 l_rl2_box_h varchar2(240);
1304 l_rl2_box_i varchar2(240);
1305 l_rl2_box_j varchar2(240);
1306 l_rl2_box_k varchar2(240);
1307 l_rl2_box_l varchar2(240);
1308 l_rl2_box_m varchar2(240);
1309 l_rl2_box_n varchar2(240);
1310 l_rl2_box_o varchar2(240);
1311 l_rl2_source_of_income varchar2(240);
1312 l_negative_balance_flag varchar2(240);
1313
1314 l_mag_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
1315 l_arch_action_id pay_assignment_actions.assignment_action_id%TYPE;
1316 l_asg_id per_assignments_f.assignment_id%TYPE;
1317 l_date_earned DATE;
1318 l_province VARCHAR2(30);
1319
1320 TYPE employee_info IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
1321
1322 tab_employee employee_info;
1323
1324 lAnnee NUMBER;
1325 lNoReleve NUMBER;
1326 lNAS NUMBER;
1327 lNAS1 NUMBER;
1328 lNAS2 NUMBER;
1329 lNAS3 NUMBER;
1330 lNo NUMBER;
1331 lNomFamille NUMBER;
1332 lPrenom NUMBER;
1333 lInitiale NUMBER;
1334 lLigne1 NUMBER;
1335 lLigne2 NUMBER;
1336 lVille NUMBER;
1337 lProvince NUMBER;
1338 lCodePostal NUMBER;
1339 lA_PrestRPA_RPNA NUMBER;
1340 lB_PrestREER_FERR_RPDB NUMBER;
1341 lC_AutrePaiement NUMBER;
1342 lD_RembPrimeConjoint NUMBER;
1343 lE_PrestDeces NUMBER;
1344 lF_RembCotisInutilise NUMBER;
1345 lG_RevocationREER_FERR NUMBER;
1346 lH_AutreRevenu NUMBER;
1347 lI_DroitDeduction NUMBER;
1351 lM_LibereImpot NUMBER;
1348 lJ_ImpotQueRetenuSource NUMBER;
1349 lK_RevenuApresDeces NUMBER;
1350 lL_RetraitREEP NUMBER;
1352 lN_NASConjoint NUMBER;
1353 lN_NASConjoint1 NUMBER;
1354 lN_NASConjoint2 NUMBER;
1355 lO_RetraitRAP NUMBER;
1356 lProvenance1 VARCHAR2(10);
1357 lBoxA_UnregisterdPlan NUMBER;
1358 lDesg_BenefitExcAmt NUMBER;
1359 lBoxB_DesgBenefitTrnsAmt NUMBER;
1360 lBoxExcessAmt NUMBER;
1361 lAmount_Transferred NUMBER;
1362 lBoxC_SinglePayAccured NUMBER;
1363 lBoxC_SinglePayAccUnreg NUMBER;
1364 lBoxC_ExcessAmtSinPayTrans NUMBER;
1365 lCode_dereleve NUMBER;
1366
1367 l_person_id per_people_f.person_id%TYPE;
1368 l_address_line1 per_addresses.address_line1%TYPE;
1369 l_address_line2 per_addresses.address_line2%TYPE;
1370 l_address_line3 per_addresses.address_line3%TYPE;
1371 l_city per_addresses.town_or_city%TYPE;
1372 l_postal_code per_addresses.postal_code%TYPE;
1373 l_country VARCHAR2(60);
1374 l_emp_province per_addresses.region_1%TYPE;
1375 EOL VARCHAR2(5);
1376 l_taxation_year VARCHAR2(5);
1377 l_name VARCHAR2(60);
1378 l_return VARCHAR2(30);
1379 l_status VARCHAR2(10);
1380 l_addr_begin_tag VARCHAR2(10);
1381 l_addr_end_tag VARCHAR2(10);
1382 l_formatted_box VARCHAR2(20);
1383 l_boxO VARCHAR2(10);
1384 l_combined_addr VARCHAR2(500);
1385
1386 l_count NUMBER;
1387 lBoxR_14 NUMBER;
1388 lErrorDetails NUMBER;
1389
1390 CURSOR cur_get_meaning(p_lookup_code VARCHAR2) IS
1391 SELECT
1392 meaning
1393 FROM
1394 hr_lookups
1395 WHERE
1396 lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
1397 lookup_code = p_lookup_code;
1398
1399 l_meaning hr_lookups.meaning%TYPE;
1400 l_msg_code VARCHAR2(30);
1401 l_all_box_0 BOOLEAN;
1402
1403 /* Cursor for fetching the Footnote Codes */
1404 CURSOR c_footnote_codes ( p_assg_actid number) is
1405 SELECT hl.meaning code, fnd_number.canonical_to_number(FT.FOOTNOTE_AMOUNT) value
1406 FROM PAY_CA_EOY_RL2_FOOTNOTE_INFO_V FT,
1407 HR_LOOKUPS HL
1408 WHERE FT.ASSIGNMENT_ACTION_ID = p_assg_actid
1409 AND ((HL.LOOKUP_TYPE = 'PAY_CA_RL2_FOOTNOTES'
1410 AND HL.lookup_code = FT.FOOTNOTE_CODE)
1411 OR
1412 (HL.LOOKUP_TYPE = 'PAY_CA_RL2_AUTOMATIC_FOOTNOTES'
1413 AND HL.LOOKUP_CODE = FT.FOOTNOTE_CODE));
1414
1415 l_footnote_code VARCHAR2(100);
1416 l_footnote_amount NUMBER;
1417 l_format_mask VARCHAR2(30);
1418
1419 /* Added the following new variables for XML Paper report */
1420 tab_employee1 employee_info;
1421 tab_employee2 employee_info;
1422 l_rep_type pay_report_format_mappings_f.report_type%type;
1423 l_rl2pap_asg_actid NUMBER;
1424 l_rl2pap_pay_actid NUMBER;
1425 l_transfer_pay_actid NUMBER;
1426 l_business_group_id NUMBER;
1427
1428 TYPE employer_inf IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
1429 tab_emplyr employer_inf;
1430 tab_emplyr1 employer_inf;
1431 tab_emplyr2 employer_inf;
1432
1433 l_page_break VARCHAR2(100);
1434 l_full_empname VARCHAR2(100);
1435 l_full_empaddr VARCHAR2(100);
1436 l_empr_name VARCHAR2(240);
1437 l_empr_quebec_no VARCHAR2(240);
1438 l_empr_report_yr VARCHAR2(240);
1439 l_empr_addr1 VARCHAR2(240);
1440 l_empr_addr2 VARCHAR2(240);
1441 l_empr_addr3 VARCHAR2(240);
1442 l_empr_city VARCHAR2(240);
1443 l_empr_prov VARCHAR2(240);
1444 l_empr_postcode VARCHAR2(240);
1445 l_empr_country VARCHAR2(240);
1446 l_empr_fulladdr VARCHAR2(240);
1447 l_counter NUMBER;
1448 l_negative_box VARCHAR2(1);
1449 l_footnote_count NUMBER;
1450 l_footcode VARCHAR2(100);
1451 l_footamt NUMBER;
1452 l_footnotecode NUMBER;
1453 l_footnoteamt NUMBER;
1454 l_legislative_parameters pay_payroll_actions.legislative_parameters%type;
1455
1456 l_authorisation_no NUMBER;
1457 l_authorisation_tag NUMBER;
1458 l_sequence_no NUMBER;
1459 l_seq_num NUMBER;
1460 l_authorization_code VARCHAR2(100);
1461
1462 BEGIN
1463 --hr_utility.trace_on(null,'SATIRL2XML');
1464 hr_utility.trace('XML Employee');
1465 l_status := 'Success';
1466 l_all_box_0 := TRUE;
1467 l_count := 0;
1468 l_format_mask := '99999999999999990.99';
1469 l_counter := 0;
1470 l_negative_box := 'N';
1471 l_footnote_count := 0;
1472 SELECT
1473 fnd_global.local_chr(13) || fnd_global.local_chr(10)
1474 INTO EOL
1475 FROM dual;
1476
1477 lAnnee := 1;
1478 lNoReleve := 2;
1479 lNAS := 3;
1480 lNo := 4;
1481 lNomFamille := 5;
1482 lPrenom := 6;
1483 lInitiale := 7;
1484 lLigne1 := 8;
1485 lLigne2 := 9;
1486 lVille := 10;
1487 lProvince := 11;
1488 lCodePostal := 12;
1489 lA_PrestRPA_RPNA := 13;
1490 lB_PrestREER_FERR_RPDB := 14;
1494 lF_RembCotisInutilise := 18;
1491 lC_AutrePaiement := 15;
1492 lD_RembPrimeConjoint := 16;
1493 lE_PrestDeces := 17;
1495 lG_RevocationREER_FERR := 19;
1496 lH_AutreRevenu := 20;
1497 lI_DroitDeduction := 21;
1498 lJ_ImpotQueRetenuSource := 22;
1499 lK_RevenuApresDeces := 23;
1500 lL_RetraitREEP := 24;
1501 lM_LibereImpot := 25;
1502 lN_NASConjoint := 26;
1503 lO_RetraitRAP := 27;
1504 lProvenance1 := 28;
1505 lErrorDetails := 29;
1506 lBoxA_UnregisterdPlan := 30;
1507 lDesg_BenefitExcAmt := 31;
1508 lBoxB_DesgBenefitTrnsAmt := 32;
1509 lBoxExcessAmt := 33;
1510 lAmount_Transferred := 34;
1511 lBoxC_SinglePayAccured := 35;
1512 lBoxC_SinglePayAccUnreg := 36;
1513 lBoxC_ExcessAmtSinPayTrans := 37;
1514 l_footnotecode := 38;
1515 l_footnoteamt := 39;
1516 lNAS1 := 40;
1517 lNAS2 := 41;
1518 lNAS3 := 42;
1519 lN_NASConjoint1 := 43;
1520 lN_NASConjoint2 := 44;
1521 lCode_dereleve := 45;
1522 l_authorisation_no := 46;
1523 l_authorisation_tag := 47;
1524 l_sequence_no := 48;
1525 l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value
1526 ('TRANSFER_ACT_ID'));
1527 l_transfer_pay_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1528
1529 open c_get_report_type(l_transfer_pay_actid);
1530 fetch c_get_report_type
1531 into l_rep_type,
1532 l_business_group_id,
1533 l_legislative_parameters;
1534 close c_get_report_type;
1535
1536 if l_rep_type = 'RL2PAPERPDF' then
1537 open c_get_payroll_asg_actid(l_mag_asg_action_id);
1538 fetch c_get_payroll_asg_actid
1539 into l_rl2pap_asg_actid,
1540 l_rl2pap_pay_actid,
1541 l_asg_id;
1542 close c_get_payroll_asg_actid;
1543
1544 hr_utility.trace('The value of Payroll action id is '||l_rl2pap_pay_actid);
1545 hr_utility.trace('The value of Assignment action id is '||l_rl2pap_asg_actid);
1546 hr_utility.trace('The value of Assignment id is '||l_asg_id);
1547
1548 OPEN c_get_employer_info(l_rl2pap_pay_actid,l_business_group_id);
1549 FETCH c_get_employer_info
1550 INTO l_empr_name ,
1551 l_empr_quebec_no ,
1552 l_empr_report_yr ,
1553 l_empr_addr1 ,
1554 l_empr_addr2 ,
1555 l_empr_addr3,
1556 l_empr_city ,
1557 l_empr_prov ,
1558 l_empr_country,
1559 l_empr_postcode;
1560 CLOSE c_get_employer_info;
1561
1562 l_counter := l_counter + 1;
1563 IF l_empr_name IS NOT NULL AND
1564 l_empr_name <> ' ' THEN
1565 tab_emplyr(l_counter) :='<Nom>'|| convert_special_char(substr(l_empr_name,1,30))||'</Nom>'||EOL;
1566 tab_emplyr1(l_counter) :='<Nom1>'|| convert_special_char(substr(l_empr_name,1,30))||'</Nom1>'||EOL;
1567 tab_emplyr2(l_counter) :='<Nom2>'|| convert_special_char(substr(l_empr_name,1,30))||'</Nom2>'||EOL;
1568 ELSE
1569 tab_emplyr(l_counter) := NULL;
1570 tab_emplyr1(l_counter) := NULL;
1571 tab_emplyr2(l_counter) := NULL;
1572 END IF;
1573
1574 l_counter := l_counter + 1;
1575 IF l_empr_quebec_no IS NOT NULL AND
1576 l_empr_quebec_no <> '0000000000 0000' THEN
1577 tab_emplyr(l_counter) :='<NoDossier>'|| convert_special_char(substr(l_empr_quebec_no,1,30))||'</NoDossier>'||EOL;
1578 tab_emplyr1(l_counter) :='<NoDossier1>'|| convert_special_char(substr(l_empr_quebec_no,1,30))||'</NoDossier1>'||
1579 EOL;
1580 tab_emplyr2(l_counter) :='<NoDossier2>'|| convert_special_char(substr(l_empr_quebec_no,1,30))||'</NoDossier2>'||
1581 EOL;
1582 ELSE
1583 tab_emplyr(l_counter) := NULL;
1584 tab_emplyr1(l_counter) := NULL;
1585 tab_emplyr2(l_counter) := NULL;
1586 END IF;
1587
1588 l_counter := l_counter + 1;
1589 IF l_empr_report_yr IS NOT NULL AND
1590 l_empr_report_yr <> '0000' THEN
1591 tab_emplyr(l_counter) :='<AnneeEmplyr>'|| convert_special_char(substr(l_empr_report_yr,1,30))||'</AnneeEmplyr>'||EOL;
1592 tab_emplyr1(l_counter) :='<AnneeEmplyr1>'|| convert_special_char(substr(l_empr_report_yr,1,30))||'</AnneeEmplyr1>'||EOL;
1593 tab_emplyr2(l_counter) :='<AnneeEmplyr2>'|| convert_special_char(substr(l_empr_report_yr,1,30))||'</AnneeEmplyr2>'||EOL;
1594
1595 ELSE
1596 tab_emplyr(l_counter) := NULL;
1597 tab_emplyr1(l_counter) := NULL;
1598 tab_emplyr2(l_counter) := NULL;
1599 END IF;
1600
1601 l_counter := l_counter + 1;
1602 IF l_empr_addr1 IS NOT NULL AND
1603 l_empr_addr1 <> ' ' THEN
1604 tab_emplyr(l_counter) :='<Ligne1Emplyr>'|| convert_special_char(substr(l_empr_addr1,1,30))||'</Ligne1Emplyr>'||EOL;
1605 tab_emplyr1(l_counter) :='<Ligne1Emplyr1>'|| convert_special_char(substr(l_empr_addr1,1,30))||'</Ligne1Emplyr1>'||EOL;
1609 tab_emplyr1(l_counter) := NULL;
1606 tab_emplyr2(l_counter) :='<Ligne1Emplyr2>'|| convert_special_char(substr(l_empr_addr1,1,30))||'</Ligne1Emplyr2>'||EOL;
1607 ELSE
1608 tab_emplyr(l_counter) := NULL;
1610 tab_emplyr2(l_counter) := NULL;
1611 END IF;
1612
1613 l_counter := l_counter + 1;
1614 IF (l_empr_addr2 IS NOT NULL AND
1615 l_empr_addr2 <> ' ') OR
1616 (l_empr_addr3 IS NOT NULL AND
1617 l_empr_addr3 <> ' ') THEN
1618 tab_emplyr(l_counter) :='<Ligne2Emplyr>'||
1619 convert_special_char(substr(ltrim(rtrim(l_empr_addr2))||' '||
1620 (ltrim(rtrim(l_empr_addr3))),1,30))||'</Ligne2Emplyr>'||EOL;
1621 tab_emplyr1(l_counter) :='<Ligne2Emplyr1>'|| convert_special_char(substr(ltrim(rtrim(l_empr_addr2))||' '||
1622 (ltrim(rtrim(l_empr_addr3))),1,30))||'</Ligne2Emplyr1>'||EOL;
1623 tab_emplyr2(l_counter) :='<Ligne2Emplyr2>'|| convert_special_char(substr(ltrim(rtrim(l_empr_addr2))||' '||
1624 (ltrim(rtrim(l_empr_addr3))),1,30))||'</Ligne2Emplyr2>'||EOL;
1625 ELSE
1626 tab_emplyr(l_counter) := NULL;
1627 tab_emplyr1(l_counter) := NULL;
1628 tab_emplyr2(l_counter) := NULL;
1629 END IF;
1630
1631 l_counter := l_counter + 1;
1632 l_empr_fulladdr := l_empr_city ||' '||l_empr_prov||' '
1633 ||l_empr_country||' '||(substr(l_empr_postcode,1,3)||' '||
1634 substr(l_empr_postcode,4,3));
1635 IF l_empr_fulladdr IS NOT NULL AND
1636 l_empr_city <> ' ' AND
1637 tab_emplyr(l_counter-1) IS NOT NULL THEN
1638
1639 tab_emplyr(l_counter) :='<VilleEmplyr>'|| convert_special_char(substr(l_empr_fulladdr,1,60))||
1640 '</VilleEmplyr>'||EOL;
1641 tab_emplyr1(l_counter) :='<VilleEmplyr1>'|| convert_special_char(substr(l_empr_fulladdr,1,60))||
1642 '</VilleEmplyr1>'||EOL;
1643 tab_emplyr2(l_counter) :='<VilleEmplyr2>'|| convert_special_char(substr(l_empr_fulladdr,1,60))||
1644 '</VilleEmplyr2>'||EOL;
1645
1646 ELSIF l_empr_fulladdr IS NOT NULL AND
1647 l_empr_city <> ' ' AND
1648 tab_emplyr(l_counter-1) IS NULL THEN
1649 tab_emplyr(l_counter) :='<Ligne2Emplyr>'||convert_special_char(substr(l_empr_fulladdr,1,60))||
1650 '</Ligne2Emplyr>'||EOL;
1651 tab_emplyr1(l_counter) :='<Ligne2Emplyr1>'||convert_special_char(substr(l_empr_fulladdr,1,60))||
1652 '</Ligne2Emplyr1>'||EOL;
1653 tab_emplyr2(l_counter) :='<Ligne2Emplyr2>'||convert_special_char(substr(l_empr_fulladdr,1,60))||
1654 '</Ligne2Emplyr2>'||EOL;
1655 ELSE
1656 tab_emplyr(l_counter) := NULL;
1657 tab_emplyr1(l_counter) := NULL;
1658 tab_emplyr2(l_counter) := NULL;
1659 END IF;
1660
1661 end if;
1662 hr_utility.trace('XML Employee: l_mag_asg_action_id = '
1663 || to_char(l_mag_asg_action_id));
1664 hr_utility.trace('XML Employee: Transfer Payroll Action Id '||to_number(pay_magtape_generic.get_parameter_value
1665 ('TRANSFER_PAYROLL_ACTION_ID')));
1666 IF l_rep_type <> 'RL2PAPERPDF' THEN
1667 OPEN cur_parameters(l_mag_asg_action_id);
1668 FETCH cur_parameters
1669 INTO
1670 l_arch_action_id,
1671 l_asg_id,
1672 l_date_earned;
1673 CLOSE cur_parameters;
1674 ELSE
1675 l_arch_action_id := l_rl2pap_asg_actid;
1676 END IF;
1677
1678 hr_utility.trace('XML Employee: l_arch_action_id = '
1679 || to_char(l_arch_action_id));
1680 hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1681 hr_utility.trace('XML Employee: l_date_earned = '
1682 || to_char(l_date_earned));
1683 hr_utility.trace('XML Employee: l_province = ' || l_province);
1684
1685 if l_rep_type = 'RL2PAPERPDF' then
1686 l_taxation_year
1687 := pay_ca_rl2_mag.get_parameter('TAX_YEAR',
1688 l_legislative_parameters);
1689 else
1690 l_taxation_year := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
1691 end if;
1692
1693 --Annee
1694 tab_employee(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' || EOL;
1695 if l_rep_type = 'RL2PAPERPDF' then
1696 tab_employee1(lAnnee) := '<Annee1>' || l_taxation_year || '</Annee1>' || EOL;
1697 tab_employee2(lAnnee) := '<Annee2>' || l_taxation_year || '</Annee2>' || EOL;
1698 end if;
1699
1700 /* Added by ssmukher to remove frequent function call to fetch the employee info */
1701 open c_employee_infor(l_arch_action_id);
1702 fetch c_employee_infor
1703 into l_quebec_business_number,
1704 l_reporting_year,
1705 l_rl2_slip_number,
1709 l_employee_middle_initial,
1706 l_employee_sin,
1707 l_employee_last_name,
1708 l_employee_first_name,
1710 l_employee_address_line1,
1711 l_employee_address_line2,
1712 l_employee_address_line3,
1713 l_employee_city,
1714 l_employee_province,
1715 l_employee_postal_code,
1716 l_employee_number,
1717 l_rl2_box_a,
1718 l_rl2_box_b,
1719 l_rl2_box_c,
1720 l_rl2_box_d,
1721 l_rl2_box_e,
1722 l_rl2_box_f,
1723 l_rl2_box_g,
1724 l_rl2_box_h,
1725 l_rl2_box_i,
1726 l_rl2_box_j,
1727 l_rl2_box_k,
1728 l_rl2_box_l,
1729 l_rl2_box_m,
1730 l_rl2_box_n,
1731 l_rl2_box_o,
1732 l_rl2_source_of_income,
1733 l_negative_balance_flag,
1734 l_per_id;
1735
1736 CLOSE c_employee_infor;
1737
1738 IF l_rep_type = 'RL2PAPERPDF' THEN
1739
1740 IF (l_reporting_year = '2006' ) then
1741 l_authorization_code := 'FS-06-02-003';
1742 ELSE
1743 l_authorization_code := 'FS-07-02-068'; --Bug 6747924
1744 END IF;
1745
1746 tab_employee(l_authorisation_tag) := '<TagCertification>'||
1747 convert_special_char('No d''autorisation :')||
1748 '</TagCertification>';
1749 tab_employee(l_authorisation_no) := '<NoCertification>'||convert_special_char(l_authorization_code)
1750 ||'</NoCertification>';
1751 tab_employee1(l_authorisation_tag) := '<TagCertification1>'||
1752 convert_special_char('No d''autorisation :')||
1753 '</TagCertification1>';
1754 tab_employee1(l_authorisation_no) := '<NoCertification1>'||convert_special_char(l_authorization_code)
1755 || '</NoCertification1>';
1756 tab_employee2(l_authorisation_tag) := '<TagCertification2>'||
1757 convert_special_char('No d''autorisation :')||
1758 '</TagCertification2>';
1759 tab_employee2(l_authorisation_no) := '<NoCertification2>'||convert_special_char(l_authorization_code)
1760 || '</NoCertification2>';
1761
1762 select pay_ca_rl2_pdf_seq_s.nextval into l_seq_num from dual;
1763 tab_employee(l_sequence_no) := '<SequenceNum>'||getnext_seq_num(l_seq_num)
1764 ||'</SequenceNum>';
1765 tab_employee1(l_sequence_no) := '<SequenceNum1>'||getnext_seq_num(l_seq_num)
1766 ||'</SequenceNum1>';
1767 tab_employee2(l_sequence_no) := '<SequenceNum2>'||getnext_seq_num(l_seq_num)
1768 ||'</SequenceNum2>';
1769 END IF;
1770
1771 --NoReleve
1772 /* Check for Mandatory Information RL-2 Slip Number missing */
1773
1774 IF ( l_rl2_slip_number = '000000000' AND
1775 l_rl2_slip_number IS NOT NULL) THEN
1776 l_status := 'Failed';
1777 l_msg_code := 'MISSING_SLIP_NO';
1778 tab_employee(lNoReleve) := NULL;
1779 if l_rep_type = 'RL2PAPERPDF' then
1780 tab_employee1(lNoReleve) := NULL;
1781 tab_employee2(lNoReleve) := NULL;
1782 end if;
1783 ELSE
1784 tab_employee(lNoReleve) := '<NoReleve>' || convert_special_char(l_rl2_slip_number) ||
1785 '</NoReleve>' || EOL;
1786 if l_rep_type = 'RL2PAPERPDF' then
1787 tab_employee1(lNoReleve) := '<NoReleve1>' || convert_special_char(l_rl2_slip_number) ||
1788 '</NoReleve1>' || EOL;
1789 tab_employee2(lNoReleve) := '<NoReleve2>' || convert_special_char(l_rl2_slip_number) ||
1790 '</NoReleve2>' || EOL;
1791 end if;
1792 END IF;
1793 hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve));
1794
1795 -- NAS
1796 /* Bug Fix 4754891 */
1797 IF (l_employee_sin IS NOT NULL AND
1798 l_employee_sin <> '000000000') THEN
1799
1800 IF l_rep_type <> 'RL2PAPERPDF' THEN
1801 tab_employee(lNAS) := '<NAS>' || convert_special_char(l_employee_sin) || '</NAS>' || EOL;
1802 ELSE
1803 tab_employee(lNAS1) := '<NAS1>' || convert_special_char(substr(l_employee_sin,1,3)) || '</NAS1>' || EOL;
1804 tab_employee(lNAS2) := '<NAS2>' || convert_special_char(substr(l_employee_sin,4,3)) || '</NAS2>' || EOL;
1805 tab_employee(lNAS3) := '<NAS3>' || convert_special_char(substr(l_employee_sin,7,3)) || '</NAS3>' || EOL;
1806
1807 tab_employee1(lNAS1) := '<NAS21>' || convert_special_char(substr(l_employee_sin,1,3)) || '</NAS21>' || EOL;
1808 tab_employee1(lNAS2) := '<NAS22>' || convert_special_char(substr(l_employee_sin,4,3)) || '</NAS22>' || EOL;
1809 tab_employee1(lNAS3) := '<NAS23>' || convert_special_char(substr(l_employee_sin,7,3)) || '</NAS23>' || EOL;
1810
1811 tab_employee2(lNAS1) := '<NAS31>' || convert_special_char(substr(l_employee_sin,1,3)) || '</NAS31>' || EOL;
1812 tab_employee2(lNAS2) := '<NAS32>' || convert_special_char(substr(l_employee_sin,4,3)) || '</NAS32>' || EOL;
1813 tab_employee2(lNAS3) := '<NAS33>' || convert_special_char(substr(l_employee_sin,7,3)) || '</NAS33>' || EOL;
1814
1815 END IF;
1816 ELSE
1817 l_status := 'Failed';
1818 l_msg_code := 'SIN';
1819 tab_employee(lNAS) := NULL;
1823 tab_employee(lNAS2) := NULL;
1820 if l_rep_type = 'RL2PAPERPDF' then
1821
1822 tab_employee(lNAS1) := NULL;
1824 tab_employee(lNAS3) := NULL;
1825
1826 tab_employee1(lNAS1) := NULL;
1827 tab_employee1(lNAS2) := NULL;
1828 tab_employee1(lNAS3) := NULL;
1829
1830 tab_employee2(lNAS1) := NULL;
1831 tab_employee2(lNAS2) := NULL;
1832 tab_employee2(lNAS3) := NULL;
1833
1834 end if;
1835 END IF;
1836 -- hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
1837
1838 -- No
1839 IF (l_employee_number IS NOT NULL AND
1840 l_employee_number <> ' ' ) THEN
1841 tab_employee(lNo) := '<No>' || convert_special_char(l_employee_number) || '</No>' || EOL;
1842 if l_rep_type = 'RL2PAPERPDF' then
1843 tab_employee1(lNo) := '<No1>' || convert_special_char(l_employee_number) || '</No1>' || EOL;
1844 tab_employee2(lNo) := '<No2>' || convert_special_char(l_employee_number) || '</No2>' || EOL;
1845 end if;
1846 ELSE
1847 tab_employee(lNo) := NULL;
1848 if l_rep_type = 'RL2PAPERPDF' then
1849 tab_employee1(lNo) := NULL;
1850 tab_employee2(lNo) := NULL;
1851 end if;
1852 END IF;
1853 hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
1854
1855 -- NomFamille
1856
1857 tab_employee(lNomFamille) := '<NomFamille>' ||
1858 convert_special_char(substr(l_employee_last_name,1,30)) || '</NomFamille>' || EOL;
1859 hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
1860
1861 l_full_empname := convert_special_char(substr(l_employee_last_name,1,30));
1862
1863 -- Prenom
1864 IF (l_employee_first_name is NOT NULL AND
1865 l_employee_first_name <> ' ') THEN
1866 tab_employee(lPrenom) := '<Prenom>' || convert_special_char(substr(l_employee_first_name,1,30))
1867 || '</Prenom>' || EOL;
1868 l_full_empname := l_full_empname ||','||convert_special_char(substr(l_employee_first_name,1,30));
1869 ELSE
1870 l_msg_code := 'MISSING_EMP_FIRST_NAME';
1871 l_status := 'Failed';
1872 tab_employee(lPrenom) := NULL;
1873 END IF;
1874 hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
1875
1876 -- Initiale
1877
1878 IF (l_employee_middle_initial is NOT NULL AND
1879 l_employee_middle_initial <> ' ') THEN
1880 tab_employee(lInitiale) := '<Initiale>' || convert_special_char(substr(l_employee_middle_initial,1,1))
1881 || '</Initiale>' || EOL;
1882 l_full_empname := l_full_empname ||' '||convert_special_char(substr(l_employee_middle_initial,1,1));
1883 ELSE
1884 tab_employee(lInitiale) := NULL;
1885 END IF;
1886
1887 if l_rep_type = 'RL2PAPERPDF' then
1888 tab_employee(lNomFamille) := '<NomFamille>' ||l_full_empname || '</NomFamille>' || EOL;
1889 tab_employee1(lNomFamille) := '<NomFamille1>' ||l_full_empname || '</NomFamille1>' || EOL;
1890 tab_employee2(lNomFamille) := '<NomFamille2>' ||l_full_empname || '</NomFamille2>' || EOL;
1891 end if;
1892
1893 hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
1894
1895 l_person_id := to_number(l_per_id);
1896
1897 l_return := pay_ca_emp_address_dtls.get_emp_address(
1898 l_person_id,
1899 l_address_line1,
1900 l_address_line2,
1901 l_address_line3,
1902 l_city,
1903 l_postal_code,
1904 l_country,
1905 l_emp_province
1906 );
1907 -- If Address line 1 is NULL or ' ' then the employee is missing
1908 -- address information - as line 1 is mandatory in the Address form.
1909 -- Need to check data by SS transaction /API.
1910
1911 hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1912 hr_utility.trace('l_address_line1 = ' || l_address_line1);
1913 hr_utility.trace('l_address_line2 = ' || l_address_line2);
1914 hr_utility.trace('l_postal_code = ' || l_postal_code);
1915
1916 /* Bug Fix 4761782 */
1917 -- Address Line 1
1918 IF l_address_line1 IS NOT NULL AND
1919 l_address_line1 = ' ' THEN
1920
1921 l_status := 'Failed';
1922 l_msg_code := 'MISSING_EMP_ADDRESS';
1923
1924 l_addr_begin_tag := NULL;
1925 tab_employee(lLigne1) := NULL;
1926 tab_employee(lLigne2) := NULL;
1927 tab_employee(lVille) := NULL;
1928 tab_employee(lProvince) := NULL;
1929 tab_employee(lCodePostal) := NULL;
1930 l_addr_end_tag := NULL;
1931 if l_rep_type = 'RL2PAPERPDF' then
1932 tab_employee1(lLigne1) := NULL;
1933 tab_employee1(lLigne2) := NULL;
1934
1935 tab_employee2(lLigne1) := NULL;
1936 tab_employee2(lLigne2) := NULL;
1937
1938 end if;
1939 ELSE
1940
1941 l_addr_begin_tag := '<Adresse>';
1942
1943 tab_employee(lLigne1) := '<Ligne1>' ||
1944 convert_special_char(substr(l_address_line1,1,30)) || '</Ligne1>' || EOL;
1945 if l_rep_type = 'RL2PAPERPDF' then
1949 convert_special_char(substr(l_address_line1,1,30)) || '</Ligne12>' || EOL;
1946 tab_employee1(lLigne1) := '<Ligne11>' ||
1947 convert_special_char(substr(l_address_line1,1,30)) || '</Ligne11>' || EOL;
1948 tab_employee2(lLigne1) := '<Ligne12>' ||
1950 end if;
1951 hr_utility.trace('tab_employee(lLigne1) = ' || tab_employee(lLigne1));
1952
1953 -- Address Line 2
1954
1955 IF ((l_address_line2 IS NOT NULL AND
1956 l_address_line2 <> ' ' ) OR
1957 (l_address_line3 IS NOT NULL AND
1958 l_address_line3 <> ' ') ) THEN
1959 l_combined_addr := rtrim(ltrim(l_address_line2)) || rtrim(ltrim(l_address_line3));
1960 tab_employee(lLigne2) := '<Ligne2>' ||
1961 convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne2>' || EOL;
1962 if l_rep_type = 'RL2PAPERPDF' and l_combined_addr is not null then
1963 tab_employee1(lLigne2) := '<Ligne21>' ||
1964 convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne21>' || EOL;
1965 tab_employee2(lLigne2) := '<Ligne22>' ||
1966 convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne22>' || EOL;
1967 end if;
1968 ELSE
1969 IF l_rep_type <> 'RL2PAPERPDF' THEN
1970 tab_employee(lLigne2) := NULL;
1971 ELSE
1972 tab_employee(lVille) := NULL;
1973 tab_employee1(lVille) := NULL;
1974 tab_employee2(lVille) := NULL;
1975 END IF;
1976 END IF;
1977 --hr_utility.trace('tab_employee(lLigne2) = ' || tab_employee(lLigne2));
1978
1979 -- Ville (City)
1980 IF l_city IS NOT NULL AND
1981 l_city <> ' ' THEN
1982 tab_employee(lVille) := '<Ville>' ||
1983 convert_special_char(substr(l_city,1,30)) || '</Ville>' || EOL;
1984 l_full_empaddr := convert_special_char(substr(l_city,1,30));
1985
1986 ELSE
1987 tab_employee(lVille) := NULL;
1988
1989 END IF;
1990 --hr_utility.trace('tab_employee(lVille) = ' || tab_employee(lVille));
1991
1992 -- Province
1993 IF l_emp_province IS NOT NULL AND
1994 l_emp_province <> ' ' THEN
1995
1996 IF l_country = 'CA' THEN
1997 tab_employee(lProvince) := '<Province>' ||
1998 convert_special_char(SUBSTR(hr_general.decode_lookup(
1999 'CA_PROVINCE',l_emp_province),1,20)) || '</Province>' || EOL;
2000 l_full_empaddr := l_full_empaddr ||' '||convert_special_char(l_emp_province);
2001
2002
2003 ELSIF l_country = 'US' THEN
2004 tab_employee(lProvince) := '<Province>' ||l_emp_province || '</Province>' || EOL;
2005 l_full_empaddr := l_full_empaddr ||' '||l_emp_province;
2006
2007 ELSE
2008 tab_employee(lProvince) := '<Province>' ||l_country|| '</Province>' || EOL;
2009 l_full_empaddr := l_full_empaddr ||' '||l_country;
2010
2011 END IF;
2012 ELSE
2013 tab_employee(lProvince) := NULL;
2014
2015 END IF;
2016 hr_utility.trace('tab_employee(lProvince) = ' || tab_employee(lProvince));
2017
2018 -- Bug# 4754743 fix
2019 -- Postal Code
2020 IF l_postal_code IS NOT NULL AND
2021 l_postal_code <> ' ' THEN
2022 tab_employee(lCodePostal) := '<CodePostal>' ||
2023 convert_special_char(substr(replace(l_postal_code,' '),1,6)) || '</CodePostal>' || EOL;
2024 l_full_empaddr := l_full_empaddr ||' '||convert_special_char(substr(replace(l_country,' '),1,6))||' '||
2025 convert_special_char(l_postal_code);
2026
2027 ELSE
2028 tab_employee(lCodePostal) := NULL;
2029 END IF;
2030
2031 hr_utility.trace('tab_employee(lCodePostal) = ' || tab_employee(lCodePostal));
2032 l_addr_end_tag := '</Adresse>';
2033
2034 END IF;
2035
2036 if l_rep_type = 'RL2PAPERPDF' then
2037 IF ((l_full_empaddr IS NOT NULL AND
2038 l_full_empaddr <> ' ') AND
2039 ((l_address_line2 IS NOT NULL AND
2040 l_address_line2 <> ' ' ) OR
2041 (l_address_line3 IS NOT NULL AND
2042 l_address_line3 <> ' ') )) THEN
2043
2044 tab_employee(lVille) := '<Ville>' ||l_full_empaddr || '</Ville>' || EOL;
2045 tab_employee1(lVille) := '<Ville1>' ||l_full_empaddr || '</Ville1>' || EOL;
2046 tab_employee2(lVille) := '<Ville2>' ||l_full_empaddr || '</Ville2>' || EOL;
2047
2048 ELSIF l_full_empaddr IS NOT NULL AND
2049 (l_postal_code IS NOT NULL OR
2050 l_city IS NOT NULL OR
2051 l_emp_province IS NOT NULL) THEN
2052
2053 tab_employee(lVille) := NULL;
2054 tab_employee1(lVille) := NULL;
2055 tab_employee2(lVille) := NULL;
2056
2057 tab_employee(lLigne2) := '<Ligne2>' ||l_full_empaddr || '</Ligne2>' || EOL;
2058 tab_employee1(lLigne2) := '<Ligne21>' ||l_full_empaddr || '</Ligne21>' || EOL;
2059 tab_employee2(lLigne2) := '<Ligne22>' ||l_full_empaddr || '</Ligne22>' || EOL;
2060
2061 ELSE
2062
2063 tab_employee(lVille) := NULL;
2064 tab_employee1(lVille) := NULL;
2065 tab_employee2(lVille) := NULL;
2066 tab_employee(lLigne2) := NULL;
2067 tab_employee1(lLigne2) := NULL;
2068 tab_employee2(lLigne2) := NULL;
2072 end if;
2069
2070 END IF;
2071
2073
2074 -- Summ (Box A)
2075
2076 hr_utility.trace('The Value of Box A is '|| l_rl2_box_a);
2077 IF TO_NUMBER(l_rl2_box_a) > 9999999.99 THEN
2078 l_status := 'Failed';
2079 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2080 END IF;
2081
2082 IF l_rl2_box_a IS NOT NULL AND
2083 to_number(l_rl2_box_a) <> 0 THEN
2084
2085 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_a),l_format_mask)))
2086 INTO l_formatted_box
2087 FROM dual;
2088 tab_employee(lA_PrestRPA_RPNA) := '<A_PrestRPA_RPNA>' || l_formatted_box ||
2089 '</A_PrestRPA_RPNA>' || EOL;
2090 if l_rep_type = 'RL2PAPERPDF' then
2091 tab_employee1(lA_PrestRPA_RPNA) := '<A_PrestRPA_RPNA1>' || l_formatted_box ||
2092 '</A_PrestRPA_RPNA1>' || EOL;
2093 tab_employee2(lA_PrestRPA_RPNA) := '<A_PrestRPA_RPNA2>' || l_formatted_box ||
2094 '</A_PrestRPA_RPNA2>' || EOL;
2095 end if;
2096 l_all_box_0 := FALSE;
2097 ELSE
2098 tab_employee(lA_PrestRPA_RPNA ) := NULL;
2099 if l_rep_type = 'RL2PAPERPDF' then
2100 tab_employee1(lA_PrestRPA_RPNA ) := NULL;
2101 tab_employee2(lA_PrestRPA_RPNA ) := NULL;
2102 end if;
2103 END IF;
2104 hr_utility.trace('tab_employee(lA_PrestRPA_RPNA) = ' ||
2105 tab_employee(lA_PrestRPA_RPNA));
2106
2107 -- Summ (Box B)
2108
2109 IF TO_NUMBER(l_rl2_box_b) > 9999999.99 THEN
2110 l_status := 'Failed';
2111 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2112 END IF;
2113
2114 IF l_rl2_box_b IS NOT NULL AND
2115 to_number(l_rl2_box_b) <> 0 THEN
2116
2117 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_b),l_format_mask)))
2118 INTO l_formatted_box
2119 FROM dual;
2120
2121 tab_employee(lB_PrestREER_FERR_RPDB) := '<B_PrestREER_FERR_RPDB>' || l_formatted_box
2122 || '</B_PrestREER_FERR_RPDB>' || EOL;
2123 if l_rep_type = 'RL2PAPERPDF' then
2124 tab_employee1(lB_PrestREER_FERR_RPDB) := '<B_PrestREER_FERR_RPDB1>' || l_formatted_box
2125 || '</B_PrestREER_FERR_RPDB1>' || EOL;
2126 tab_employee2(lB_PrestREER_FERR_RPDB) := '<B_PrestREER_FERR_RPDB2>' || l_formatted_box
2127 || '</B_PrestREER_FERR_RPDB2>' || EOL;
2128 end if;
2129 l_all_box_0 := FALSE;
2130 ELSE
2131
2132 tab_employee(lB_PrestREER_FERR_RPDB) := NULL;
2133 if l_rep_type = 'RL2PAPERPDF' then
2134 tab_employee1(lB_PrestREER_FERR_RPDB) := NULL;
2135 tab_employee2(lB_PrestREER_FERR_RPDB) := NULL;
2136 end if;
2137
2138 END IF;
2139 hr_utility.trace('tab_employee(lB_PrestREER_FERR_RPDB) = ' ||
2140 tab_employee(lB_PrestREER_FERR_RPDB));
2141
2142 -- Summ (Box C)
2143
2144 IF TO_NUMBER(l_rl2_box_c) > 9999999.99 THEN
2145 l_status := 'Failed';
2146 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2147 END IF;
2148
2149 IF l_rl2_box_c IS NOT NULL AND
2150 to_number(l_rl2_box_c) <> 0 THEN
2151
2152 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_c),l_format_mask)))
2153 INTO l_formatted_box
2154 FROM dual;
2155
2156 tab_employee(lC_AutrePaiement ) := '<C_AutrePaiement>' ||
2157 l_formatted_box || '</C_AutrePaiement>' || EOL;
2158 if l_rep_type = 'RL2PAPERPDF' then
2159 tab_employee1(lC_AutrePaiement ) := '<C_AutrePaiement1>' ||
2160 l_formatted_box || '</C_AutrePaiement1>' || EOL;
2161 tab_employee2(lC_AutrePaiement ) := '<C_AutrePaiement2>' ||
2162 l_formatted_box || '</C_AutrePaiement2>' || EOL;
2163 end if;
2164 l_all_box_0 := FALSE;
2165 ELSE
2166 tab_employee(lC_AutrePaiement ) := NULL;
2167 if l_rep_type = 'RL2PAPERPDF' then
2168 tab_employee1(lC_AutrePaiement ) := NULL;
2169 tab_employee2(lC_AutrePaiement ) := NULL;
2170 end if;
2171 END IF;
2172
2173 hr_utility.trace('tab_employee(lC_AutrePaiement ) = ' ||
2174 tab_employee(lC_AutrePaiement ));
2175
2176 -- Summ (Box D)
2177
2178 IF TO_NUMBER(l_rl2_box_d) > 9999999.99 THEN
2179 l_status := 'Failed';
2180 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2181 END IF;
2182
2183 IF l_rl2_box_d IS NOT NULL AND
2184 to_number(l_rl2_box_d) <> 0 THEN
2185
2186 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_d),l_format_mask)))
2187 INTO l_formatted_box
2188 FROM dual;
2189
2190 tab_employee(lD_RembPrimeConjoint) := '<D_RembPrimeConjoint>' ||
2191 l_formatted_box || '</D_RembPrimeConjoint>' || EOL;
2192 if l_rep_type = 'RL2PAPERPDF' then
2193 tab_employee1(lD_RembPrimeConjoint) := '<D_RembPrimeConjoint1>' ||
2194 l_formatted_box || '</D_RembPrimeConjoint1>' || EOL;
2195 tab_employee2(lD_RembPrimeConjoint) := '<D_RembPrimeConjoint2>' ||
2196 l_formatted_box || '</D_RembPrimeConjoint2>' || EOL;
2197 end if;
2201 if l_rep_type = 'RL2PAPERPDF' then
2198 l_all_box_0 := FALSE;
2199 ELSE
2200 tab_employee(lD_RembPrimeConjoint) := NULL;
2202 tab_employee1(lD_RembPrimeConjoint) := NULL;
2203 tab_employee2(lD_RembPrimeConjoint) := NULL;
2204 end if;
2205 END IF;
2206 hr_utility.trace('tab_employee(lD_RembPrimeConjoint) = ' ||
2207 tab_employee(lD_RembPrimeConjoint));
2208
2209 -- (Box E)
2210
2211 IF TO_NUMBER(l_rl2_box_e) > 9999999.99 THEN
2212 l_status := 'Failed';
2213 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2214 END IF;
2215
2216 IF l_rl2_box_e IS NOT NULL AND
2217 to_number(l_rl2_box_e) <> 0 THEN
2218
2219 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_e),l_format_mask)))
2220 INTO l_formatted_box
2221 FROM dual;
2222
2223 tab_employee(lE_PrestDeces) := '<E_PrestDeces>' ||
2224 l_formatted_box || '</E_PrestDeces>' || EOL;
2225 if l_rep_type = 'RL2PAPERPDF' then
2226 tab_employee1(lE_PrestDeces) := '<E_PrestDeces1>' ||
2227 l_formatted_box || '</E_PrestDeces1>' || EOL;
2228 tab_employee2(lE_PrestDeces) := '<E_PrestDeces2>' ||
2229 l_formatted_box || '</E_PrestDeces2>' || EOL;
2230 end if;
2231 l_all_box_0 := FALSE;
2232 ELSE
2233 tab_employee(lE_PrestDeces) := NULL;
2234 if l_rep_type = 'RL2PAPERPDF' then
2235 tab_employee1(lE_PrestDeces) := NULL;
2236 tab_employee2(lE_PrestDeces) := NULL;
2237 end if;
2238 END IF;
2239
2240 hr_utility.trace('tab_employee(lE_PrestDeces) = ' ||
2241 tab_employee(lE_PrestDeces));
2242
2243 -- (Box F)
2244
2245 IF TO_NUMBER(l_rl2_box_f) > 9999999.99 THEN
2246 l_status := 'Failed';
2247 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2248 END IF;
2249
2250 IF l_rl2_box_f IS NOT NULL AND
2251 to_number(l_rl2_box_f) <> 0 THEN
2252
2253 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_f),l_format_mask)))
2254 INTO l_formatted_box
2255 FROM dual;
2256
2257 tab_employee(lF_RembCotisInutilise) := '<F_RembCotisInutilise>' ||
2258 l_formatted_box || '</F_RembCotisInutilise>' || EOL;
2259 if l_rep_type = 'RL2PAPERPDF' then
2260 tab_employee1(lF_RembCotisInutilise) := '<F_RembCotisInutilise1>' ||
2261 l_formatted_box || '</F_RembCotisInutilise1>' || EOL;
2262 tab_employee2(lF_RembCotisInutilise) := '<F_RembCotisInutilise2>' ||
2263 l_formatted_box || '</F_RembCotisInutilise2>' || EOL;
2264 end if;
2265 l_all_box_0 := FALSE;
2266 ELSE
2267 tab_employee(lF_RembCotisInutilise) := NULL;
2268 if l_rep_type = 'RL2PAPERPDF' then
2269 tab_employee1(lF_RembCotisInutilise) := NULL;
2270 tab_employee2(lF_RembCotisInutilise) := NULL;
2271 end if;
2272 END IF;
2273 hr_utility.trace('tab_employee(lF_RembCotisInutilise) = ' ||
2274 tab_employee(lF_RembCotisInutilise));
2275
2276 -- (Box G)
2277
2278 IF TO_NUMBER(l_rl2_box_g) > 9999999.99 THEN
2279 l_status := 'Failed';
2280 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2281 END IF;
2282
2283 IF l_rl2_box_g IS NOT NULL AND
2284 to_number(l_rl2_box_g) <> 0 THEN
2285
2286 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_g),l_format_mask)))
2287 INTO l_formatted_box
2288 FROM dual;
2289
2290 tab_employee(lG_RevocationREER_FERR ) := '<G_RevocationREER_FERR>' ||
2291 l_formatted_box || '</G_RevocationREER_FERR>' || EOL;
2292 if l_rep_type = 'RL2PAPERPDF' then
2293 tab_employee1(lG_RevocationREER_FERR ) := '<G_RevocationREER_FERR1>' ||
2294 l_formatted_box || '</G_RevocationREER_FERR1>' || EOL;
2295 tab_employee2(lG_RevocationREER_FERR ) := '<G_RevocationREER_FERR2>' ||
2296 l_formatted_box || '</G_RevocationREER_FERR2>' || EOL;
2297 end if;
2298 l_all_box_0 := FALSE;
2299 ELSE
2300 tab_employee(lG_RevocationREER_FERR) := NULL;
2301 if l_rep_type = 'RL2PAPERPDF' then
2302 tab_employee1(lG_RevocationREER_FERR) := NULL;
2303 tab_employee2(lG_RevocationREER_FERR) := NULL;
2304 end if;
2305 END IF;
2306 hr_utility.trace('tab_employee(lG_RevocationREER_FERR) = ' ||
2307 tab_employee(lG_RevocationREER_FERR));
2308
2309 -- (Box H)
2310
2311 IF TO_NUMBER(l_rl2_box_h) > 9999999.99 THEN
2312 l_status := 'Failed';
2313 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2314 END IF;
2315
2316 IF l_rl2_box_h IS NOT NULL AND
2317 to_number(l_rl2_box_h) <> 0 THEN
2318
2319 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_h),l_format_mask)))
2320 INTO l_formatted_box
2321 FROM dual;
2322
2323 tab_employee(lH_AutreRevenu) := '<H_AutreRevenu>' ||
2324 l_formatted_box || '</H_AutreRevenu>' || EOL;
2325 if l_rep_type = 'RL2PAPERPDF' then
2326 tab_employee1(lH_AutreRevenu) := '<H_AutreRevenu1>' ||
2327 l_formatted_box || '</H_AutreRevenu1>' || EOL;
2331 l_all_box_0 := FALSE;
2328 tab_employee2(lH_AutreRevenu) := '<H_AutreRevenu2>' ||
2329 l_formatted_box || '</H_AutreRevenu2>' || EOL;
2330 end if;
2332 ELSE
2333 tab_employee(lH_AutreRevenu) := NULL;
2334 if l_rep_type = 'RL2PAPERPDF' then
2335 tab_employee1(lH_AutreRevenu) := NULL;
2336 tab_employee2(lH_AutreRevenu) := NULL;
2337 end if;
2338 END IF;
2339
2340 hr_utility.trace('tab_employee(lH_AutreRevenu ) = ' ||
2341 tab_employee(lH_AutreRevenu ));
2342
2343 -- (Box I)
2344
2345 IF TO_NUMBER(l_rl2_box_i) > 9999999.99 THEN
2346 l_status := 'Failed';
2347 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2348 END IF;
2349
2350 IF l_rl2_box_i IS NOT NULL AND
2351 to_number(l_rl2_box_i) <> 0 THEN
2352
2353 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_i),l_format_mask)))
2354 INTO l_formatted_box
2355 FROM dual;
2356
2357 tab_employee(lI_DroitDeduction ) := '<I_DroitDeduction>' ||
2358 l_formatted_box || '</I_DroitDeduction>' || EOL;
2359 if l_rep_type = 'RL2PAPERPDF' then
2360 tab_employee1(lI_DroitDeduction ) := '<I_DroitDeduction1>' ||
2361 l_formatted_box || '</I_DroitDeduction1>' || EOL;
2362 tab_employee2(lI_DroitDeduction ) := '<I_DroitDeduction2>' ||
2363 l_formatted_box || '</I_DroitDeduction2>' || EOL;
2364 end if;
2365 l_all_box_0 := FALSE;
2366 ELSE
2367 tab_employee(lI_DroitDeduction ) := NULL;
2368 if l_rep_type = 'RL2PAPERPDF' then
2369 tab_employee1(lI_DroitDeduction ) := NULL;
2370 tab_employee2(lI_DroitDeduction ) := NULL;
2371 end if;
2372 END IF;
2373 hr_utility.trace('tab_employee(lI_DroitDeduction ) = ' ||
2374 tab_employee(lI_DroitDeduction ));
2375
2376 -- (Box J)
2377
2378 IF TO_NUMBER(l_rl2_box_j) > 9999999.99 THEN
2379 l_status := 'Failed';
2380 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2381 END IF;
2382
2383 IF l_rl2_box_j IS NOT NULL AND
2384 to_number(l_rl2_box_j) <> 0 THEN
2385
2386 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_j),l_format_mask)))
2387 INTO l_formatted_box
2388 FROM dual;
2389
2390 tab_employee(lJ_ImpotQueRetenuSource ) := '<J_ImpotQueRetenuSource>' ||
2391 l_formatted_box || '</J_ImpotQueRetenuSource>' || EOL;
2392 if l_rep_type = 'RL2PAPERPDF' then
2393 tab_employee1(lJ_ImpotQueRetenuSource ) := '<J_ImpotQueRetenuSource1>' ||
2394 l_formatted_box || '</J_ImpotQueRetenuSource1>' || EOL;
2395 tab_employee2(lJ_ImpotQueRetenuSource ) := '<J_ImpotQueRetenuSource2>' ||
2396 l_formatted_box || '</J_ImpotQueRetenuSource2>' || EOL;
2397 end if;
2398 l_all_box_0 := FALSE;
2399 ELSE
2400 tab_employee(lJ_ImpotQueRetenuSource ) := NULL;
2401 if l_rep_type = 'RL2PAPERPDF' then
2402 tab_employee1(lJ_ImpotQueRetenuSource ) := NULL;
2403 tab_employee2(lJ_ImpotQueRetenuSource ) := NULL;
2404 end if;
2405 END IF;
2406 hr_utility.trace('tab_employee(lJ_ImpotQueRetenuSource ) = ' ||
2407 tab_employee(lJ_ImpotQueRetenuSource ));
2408
2409 -- (Box K)
2410
2411 IF TO_NUMBER(l_rl2_box_k) > 9999999.99 THEN
2412 l_status := 'Failed';
2413 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2414 END IF;
2415
2416 IF l_rl2_box_k IS NOT NULL AND
2417 to_number(l_rl2_box_k) <> 0 THEN
2418
2419 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_k),l_format_mask)))
2420 INTO l_formatted_box
2421 FROM dual;
2422
2423 tab_employee(lK_RevenuApresDeces ) := '<K_RevenuApresDeces>' ||
2424 l_formatted_box || '</K_RevenuApresDeces>' || EOL;
2425 if l_rep_type = 'RL2PAPERPDF' then
2426 tab_employee1(lK_RevenuApresDeces ) := '<K_RevenuApresDeces1>' ||
2427 l_formatted_box || '</K_RevenuApresDeces1>' || EOL;
2428 tab_employee2(lK_RevenuApresDeces ) := '<K_RevenuApresDeces2>' ||
2429 l_formatted_box || '</K_RevenuApresDeces2>' || EOL;
2430 end if;
2431 l_all_box_0 := FALSE;
2432 ELSE
2433 tab_employee(lK_RevenuApresDeces ) := NULL;
2434 if l_rep_type = 'RL2PAPERPDF' then
2435 tab_employee1(lK_RevenuApresDeces ) := NULL;
2436 tab_employee2(lK_RevenuApresDeces ) := NULL;
2437 end if;
2438 END IF;
2439 hr_utility.trace('tab_employee(lK_RevenuApresDeces ) = ' ||
2440 tab_employee(lK_RevenuApresDeces ));
2441
2442 -- (Box L)
2443
2444 IF TO_NUMBER(l_rl2_box_l) > 9999999.99 THEN
2445 l_status := 'Failed';
2446 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2447 END IF;
2448
2449 IF l_rl2_box_l IS NOT NULL AND
2450 to_number(l_rl2_box_l) <> 0 THEN
2451
2452 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_l),l_format_mask)))
2453 INTO l_formatted_box
2454 FROM dual;
2455
2456 tab_employee(lL_RetraitREEP ) := '<L_RetraitREEP>' ||
2457 l_formatted_box || '</L_RetraitREEP>' || EOL;
2458 if l_rep_type = 'RL2PAPERPDF' then
2462 l_formatted_box || '</L_RetraitREEP2>' || EOL;
2459 tab_employee1(lL_RetraitREEP ) := '<L_RetraitREEP1>' ||
2460 l_formatted_box || '</L_RetraitREEP1>' || EOL;
2461 tab_employee2(lL_RetraitREEP ) := '<L_RetraitREEP2>' ||
2463 end if;
2464 l_all_box_0 := FALSE;
2465 ELSE
2466 tab_employee(lL_RetraitREEP ) := NULL;
2467 if l_rep_type = 'RL2PAPERPDF' then
2468 tab_employee1(lL_RetraitREEP ) := NULL;
2469 tab_employee2(lL_RetraitREEP ) := NULL;
2470 end if;
2471 END IF;
2472 hr_utility.trace('tab_employee(lL_RetraitREEP ) = ' ||
2473 tab_employee(lL_RetraitREEP ));
2474
2475 -- (Box M)
2476
2477 IF TO_NUMBER(l_rl2_box_m) > 9999999.99 THEN
2478 l_status := 'Failed';
2479 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2480 END IF;
2481
2482 IF l_rl2_box_m IS NOT NULL AND
2483 to_number(l_rl2_box_m) <> 0 THEN
2484
2485 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_m),l_format_mask)))
2486 INTO l_formatted_box
2487 FROM dual;
2488
2489 tab_employee(lM_LibereImpot) := '<M_LibereImpot>' ||
2490 l_formatted_box || '</M_LibereImpot>' || EOL;
2491 if l_rep_type = 'RL2PAPERPDF' then
2492 tab_employee1(lM_LibereImpot) := '<M_LibereImpot1>' ||
2493 l_formatted_box || '</M_LibereImpot1>' || EOL;
2494 tab_employee2(lM_LibereImpot) := '<M_LibereImpot2>' ||
2495 l_formatted_box || '</M_LibereImpot2>' || EOL;
2496 end if;
2497 l_all_box_0 := FALSE;
2498 ELSE
2499 tab_employee(lM_LibereImpot) := NULL;
2500 if l_rep_type = 'RL2PAPERPDF' then
2501 tab_employee1(lM_LibereImpot) := NULL;
2502 tab_employee2(lM_LibereImpot) := NULL;
2503 end if;
2504 END IF;
2505 hr_utility.trace('tab_employee(lM_LibereImpot) = ' ||
2506 tab_employee(lM_LibereImpot));
2507
2508 -- (Box N)
2509 -- Bug 5569097 Fix.
2510
2511 IF l_rl2_box_n IS NOT NULL THEN
2512
2513 if l_rep_type <> 'RL2PAPERPDF' then
2514
2515 tab_employee(lN_NASConjoint) := '<N_NASConjoint>' ||
2516 l_rl2_box_n || '</N_NASConjoint>' || EOL;
2517 else
2518 tab_employee(lN_NASConjoint) := '<N_NASConjoint1>' ||
2519 substr(l_rl2_box_n,1,3) || '</N_NASConjoint1>' || EOL;
2520 tab_employee(lN_NASConjoint1) := '<N_NASConjoint2>' ||
2521 substr(l_rl2_box_n,4,3) || '</N_NASConjoint2>' || EOL;
2522 tab_employee(lN_NASConjoint2) := '<N_NASConjoint3>' ||
2523 substr(l_rl2_box_n,7,3) || '</N_NASConjoint3>' || EOL;
2524 tab_employee1(lN_NASConjoint) := '<N_NASConjoint21>' ||
2525 substr(l_rl2_box_n,1,3) || '</N_NASConjoint21>' || EOL;
2526 tab_employee1(lN_NASConjoint1) := '<N_NASConjoint22>' ||
2527 substr(l_rl2_box_n,4,3) || '</N_NASConjoint22>' || EOL;
2528 tab_employee1(lN_NASConjoint2) := '<N_NASConjoint23>' ||
2529 substr(l_rl2_box_n,7,3) || '</N_NASConjoint23>' || EOL;
2530 tab_employee2(lN_NASConjoint) := '<N_NASConjoint31>' ||
2531 substr(l_rl2_box_n,1,3) || '</N_NASConjoint31>' || EOL;
2532 tab_employee2(lN_NASConjoint1) := '<N_NASConjoint32>' ||
2533 substr(l_rl2_box_n,4,3) || '</N_NASConjoint32>' || EOL;
2534 tab_employee2(lN_NASConjoint2) := '<N_NASConjoint33>' ||
2535 substr(l_rl2_box_n,7,3) || '</N_NASConjoint33>' || EOL;
2536 end if;
2537 ELSE
2538 if l_rep_type <> 'RL2PAPERPDF' then
2539 tab_employee(lN_NASConjoint) := NULL;
2540 else
2541 tab_employee(lN_NASConjoint) := NULL;
2542 tab_employee(lN_NASConjoint1) := NULL;
2543 tab_employee(lN_NASConjoint2) := NULL;
2544 tab_employee1(lN_NASConjoint) := NULL;
2545 tab_employee1(lN_NASConjoint1) := NULL;
2546 tab_employee1(lN_NASConjoint2) := NULL;
2547 tab_employee2(lN_NASConjoint) := NULL;
2548 tab_employee2(lN_NASConjoint1) := NULL;
2549 tab_employee2(lN_NASConjoint2) := NULL;
2550 end if;
2551 END IF;
2552 hr_utility.trace('tab_employee(lN_NASConjoint) = ' ||
2553 tab_employee(lN_NASConjoint));
2554
2555 -- Summ (Box O)
2556
2557 IF TO_NUMBER(l_rl2_box_o) > 999999999.99 THEN
2558 l_status := 'Failed';
2559 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2560 END IF;
2561
2562 IF l_rl2_box_o IS NOT NULL AND
2563 to_number(l_rl2_box_o) <> 0 THEN
2564
2565 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_o),l_format_mask)))
2566 INTO l_formatted_box
2567 FROM dual;
2568
2569 tab_employee(lO_RetraitRAP ) := '<O_RetraitRAP>' ||
2570 l_formatted_box || '</O_RetraitRAP>' || EOL;
2571 if l_rep_type = 'RL2PAPERPDF' then
2572 tab_employee1(lO_RetraitRAP ) := '<O_RetraitRAP1>' ||
2573 l_formatted_box || '</O_RetraitRAP1>' || EOL;
2574 tab_employee2(lO_RetraitRAP ) := '<O_RetraitRAP2>' ||
2575 l_formatted_box || '</O_RetraitRAP2>' || EOL;
2576 end if;
2577 l_all_box_0 := FALSE;
2578 ELSE
2582 tab_employee2(lO_RetraitRAP ) := NULL;
2579 tab_employee(lO_RetraitRAP ) := NULL;
2580 if l_rep_type = 'RL2PAPERPDF' then
2581 tab_employee1(lO_RetraitRAP ) := NULL;
2583 end if;
2584 END IF;
2585 hr_utility.trace('Value of Box O');
2586 hr_utility.trace('tab_employee(lO_RetraitRAP ) = ' ||
2587 tab_employee(lO_RetraitRAP ));
2588
2589 if l_rep_type = 'RL2PAPERPDF' then
2590 tab_employee(lCode_dereleve) := '<Code_De_Releve>' ||
2591 '0' || '</Code_De_Releve>' || EOL;
2592 tab_employee1(lCode_dereleve) := '<Code_De_Releve1>' ||
2593 '0' || '</Code_De_Releve1>' || EOL;
2594 tab_employee2(lCode_dereleve) := '<Code_De_Releve2>' ||
2595 '0' || '</Code_De_Releve2>' || EOL;
2596 end if;
2597 -- Negative Balance Exists
2598
2599 IF l_negative_balance_flag = 'Y' THEN
2600 l_negative_box := 'Y';
2601 l_status := 'Failed';
2602 l_msg_code := 'NEG';
2603 END IF;
2604
2605 IF l_all_box_0 THEN
2606 l_status := 'Failed';
2607 l_msg_code := 'ALL_BOXES_ZERO';
2608 END IF;
2609
2610 -- (Provenance1)
2611 hr_utility.trace('The checking for Provenance value ');
2612 hr_utility.trace('The value of Archiver Assignment Action Id '||l_arch_action_id);
2613 hr_utility.trace('The Value of Assignment Id '||l_asg_id);
2614
2615 hr_utility.trace('The value Of Provenenace : '|| l_rl2_source_of_income);
2616 IF l_rl2_source_of_income IS NOT NULL THEN
2617 tab_employee(lProvenance1 ) := '<Provenance1>' ||
2618 convert_special_char(l_rl2_source_of_income) || '</Provenance1>' || EOL;
2619 if l_rep_type = 'RL2PAPERPDF' then
2620 tab_employee1(lProvenance1 ) := '<Provenance11>' ||
2621 convert_special_char(l_rl2_source_of_income) || '</Provenance11>' || EOL;
2622 tab_employee2(lProvenance1 ) := '<Provenance12>' ||
2623 convert_special_char(l_rl2_source_of_income) || '</Provenance12>' || EOL;
2624 end if;
2625 ELSE
2626 /* Commented for Bug 6732992
2627 l_status := 'Failed';
2628 l_msg_code := 'MISSING_SOURCE_OF_INCOME';
2629 */
2630 tab_employee(lProvenance1) := NULL;
2631 if l_rep_type = 'RL2PAPERPDF' then
2632 tab_employee1(lProvenance1) := NULL;
2633 tab_employee2(lProvenance1) := NULL;
2634 end if;
2635 END IF;
2636 hr_utility.trace('tab_employee(lProvenance1) = ' ||
2637 tab_employee(lProvenance1));
2638
2639 OPEN cur_get_meaning(l_msg_code);
2640 FETCH cur_get_meaning
2641 INTO l_meaning;
2642 CLOSE cur_get_meaning;
2643
2644 /* Bug #4747251 Fix */
2645 IF l_status = 'Failed' OR l_rep_type = 'RL2PAPERPDF' THEN
2646
2647 tab_employee(lBoxA_UnregisterdPlan) := NULL;
2648 tab_employee(lDesg_BenefitExcAmt ) := NULL;
2649 tab_employee(lBoxB_DesgBenefitTrnsAmt) := NULL;
2650 tab_employee(lBoxExcessAmt) := NULL;
2651 tab_employee(lAmount_Transferred) := NULL;
2652 tab_employee(lBoxC_SinglePayAccured) := NULL;
2653 tab_employee(lBoxC_SinglePayAccUnreg) := NULL;
2654 tab_employee(lBoxC_ExcessAmtSinPayTrans) := NULL;
2655
2656 OPEN c_footnote_codes(l_arch_action_id);
2657 LOOP
2658
2659 FETCH c_footnote_codes
2660 INTO l_footnote_code,l_footnote_amount;
2661
2662 EXIT WHEN c_footnote_codes%notfound ;
2663
2664 l_footnote_count := l_footnote_count + 1;
2665
2666 IF l_footnote_code <> 'Box A - Unregistered Plan' THEN
2667 tab_employee(lBoxA_UnregisterdPlan) := NULL;
2668 ELSE
2669 IF l_footnote_amount IS NOT NULL AND
2670 l_footnote_amount <> 0 THEN
2671
2672 SELECT ltrim(rtrim(to_char(l_footnote_amount,l_format_mask)))
2673 INTO l_formatted_box
2674 FROM dual;
2675 tab_employee(lBoxA_UnregisterdPlan) := '<BoxA_UnregisteredPlan>'||l_formatted_box||
2676 '</BoxA_UnregisteredPlan>';
2677 END IF;
2678 END IF;
2679 IF l_footnote_code <> 'Box B - Designated benefit, excess amount' THEN
2680 tab_employee(lDesg_BenefitExcAmt ) := NULL;
2681 ELSE
2682 IF l_footnote_amount IS NOT NULL AND
2683 l_footnote_amount <> 0 THEN
2684
2685 SELECT ltrim(rtrim(to_char(l_footnote_amount,l_format_mask)))
2686 INTO l_formatted_box
2687 FROM dual;
2688 tab_employee(lDesg_BenefitExcAmt ) := '<Desg_BenefitExcAmt>'||l_formatted_box||'</Desg_BenefitExcAmt>';
2689 END IF;
2690 END IF;
2691
2692 IF l_footnote_code <> 'Designated benefit, amount transferred' THEN
2693 tab_employee(lBoxB_DesgBenefitTrnsAmt ) := NULL;
2694 ELSE
2695 IF l_footnote_amount IS NOT NULL AND
2696 l_footnote_amount <> 0 THEN
2697
2698 SELECT ltrim(rtrim(to_char(l_footnote_amount,l_format_mask)))
2699 INTO l_formatted_box
2700 FROM dual;
2704 END IF;
2701 tab_employee(lBoxB_DesgBenefitTrnsAmt) := '<BoxB_DesgBenefitTrnsAmt>'||l_formatted_box||
2702 '</BoxB_DesgBenefitTrnsAmt>';
2703 END IF;
2705
2706 IF l_footnote_code <> 'Box B - Excess Amount' THEN
2707 tab_employee(lBoxExcessAmt ) := NULL;
2708 ELSE
2709 IF l_footnote_amount IS NOT NULL AND
2710 l_footnote_amount <> 0 THEN
2711
2712 SELECT ltrim(rtrim(to_char(l_footnote_amount,l_format_mask)))
2713 INTO l_formatted_box
2714 FROM dual;
2715 tab_employee(lBoxExcessAmt) := '<BoxExcessAmt>'||l_formatted_box||'</BoxExcessAmt>';
2716 END IF;
2717 END IF;
2718
2719 IF l_footnote_code <> 'Amount Transferred' THEN
2720 tab_employee(lAmount_Transferred ) := NULL;
2721 ELSE
2722 IF l_footnote_amount IS NOT NULL AND
2723 l_footnote_amount <> 0 THEN
2724
2725 SELECT ltrim(rtrim(to_char(l_footnote_amount,l_format_mask)))
2726 INTO l_formatted_box
2727 FROM dual;
2728 tab_employee(lAmount_Transferred) := '<Amount_Transferred>'||l_formatted_box||'</Amount_Transferred>';
2729 END IF;
2730 END IF;
2731
2732 IF l_footnote_code <> 'Box C - Single payment accrued to December 31, 1971' THEN
2733 tab_employee(lBoxC_SinglePayAccured ) := NULL;
2734 ELSE
2735 IF l_footnote_amount IS NOT NULL AND
2736 l_footnote_amount <> 0 THEN
2737
2738 SELECT ltrim(rtrim(to_char(l_footnote_amount,l_format_mask)))
2739 INTO l_formatted_box
2740 FROM dual;
2741 tab_employee(lBoxC_SinglePayAccured) := '<BoxC_SinglePayAccured>'||l_formatted_box||
2742 '</BoxC_SinglePayAccured>';
2743 END IF;
2744 END IF;
2745
2746 IF l_footnote_code <> 'Box C - Single payment under an unregistered pension plan' THEN
2747 tab_employee(lBoxC_SinglePayAccUnreg) := NULL;
2748 ELSE
2749 IF l_footnote_amount IS NOT NULL AND
2750 l_footnote_amount <> 0 THEN
2751
2752 SELECT ltrim(rtrim(to_char(l_footnote_amount,l_format_mask)))
2753 INTO l_formatted_box
2754 FROM dual;
2755 tab_employee(lBoxC_SinglePayAccUnreg) := '<BoxC_SinglePayAccUnreg>'||l_formatted_box||
2756 '</BoxC_SinglePayAccUnreg>';
2757 END IF;
2758
2759 END IF;
2760
2761 IF l_footnote_code <> 'Box C - Excess amount of a single payment transferred' THEN
2762 tab_employee(lBoxC_ExcessAmtSinPayTrans) := NULL;
2763 ELSE
2764 IF l_footnote_amount IS NOT NULL AND
2765 l_footnote_amount <> 0 THEN
2766
2767 SELECT ltrim(rtrim(to_char(l_footnote_amount,l_format_mask)))
2768 INTO l_formatted_box
2769 FROM dual;
2770 tab_employee(lBoxC_ExcessAmtSinPayTrans) := '<BoxC_ExcessAmtSinPayTrans>'||l_formatted_box||
2771 '</BoxC_ExcessAmtSinPayTrans>';
2772 END IF;
2773 END IF;
2774 l_footcode := l_footnote_code;
2775 l_footamt := l_footnote_amount;
2776 IF l_footnote_amount < 0 THEN
2777 l_negative_box := 'Y';
2778 END IF;
2779 END LOOP;
2780 close c_footnote_codes;
2781 ELSE
2782 tab_employee(lBoxA_UnregisterdPlan) := NULL;
2783 tab_employee(lDesg_BenefitExcAmt ) := NULL;
2784 tab_employee(lBoxB_DesgBenefitTrnsAmt) := NULL;
2785 tab_employee(lBoxExcessAmt) := NULL;
2786 tab_employee(lAmount_Transferred) := NULL;
2787 tab_employee(lBoxC_SinglePayAccured) := NULL;
2788 tab_employee(lBoxC_SinglePayAccUnreg) := NULL;
2789 tab_employee(lBoxC_ExcessAmtSinPayTrans) := NULL;
2790 END IF;
2791
2792 IF l_status = 'Failed' THEN
2793 tab_employee(lErrorDetails) := '<ErrorDetails>' ||
2794 convert_special_char(l_meaning) || '</ErrorDetails>' || EOL;
2795 ELSE
2796 tab_employee(lErrorDetails) := NULL;
2797 END IF;
2798
2799 IF l_rep_type <> 'RL2PAPERPDF' THEN
2800 l_final_xml_string :=
2801 '<' || l_status || '>' || EOL ||
2802 '<R>' || EOL ||
2803 tab_employee(lAnnee) ||
2804 tab_employee(lNoReleve) || '<Beneficiaire>' || EOL ||
2805 tab_employee(lNAS) ||
2806 tab_employee(lNo) ||
2807 tab_employee(lNomFamille) ||
2808 tab_employee(lPrenom) ||
2809 tab_employee(lInitiale) || l_addr_begin_tag || EOL ||
2810 tab_employee(lLigne1) ||
2814 tab_employee(lCodePostal) ||
2811 tab_employee(lLigne2) ||
2812 tab_employee(lVille) ||
2813 tab_employee(lProvince) ||
2815 l_addr_end_tag || EOL || '</Beneficiaire>' || EOL ||
2816 '<Montants>' || EOL ||
2817 tab_employee(lA_PrestRPA_RPNA) ||
2818 tab_employee(lB_PrestREER_FERR_RPDB) ||
2819 tab_employee(lC_AutrePaiement) ||
2820 tab_employee(lD_RembPrimeConjoint) ||
2821 tab_employee(lE_PrestDeces) ||
2822 tab_employee(lF_RembCotisInutilise) ||
2823 tab_employee(lG_RevocationREER_FERR) ||
2824 tab_employee(lH_AutreRevenu) ||
2825 tab_employee(lI_DroitDeduction ) ||
2826 tab_employee(lJ_ImpotQueRetenuSource) ||
2827 tab_employee(lK_RevenuApresDeces) ||
2828 tab_employee(lL_RetraitREEP) ||
2829 tab_employee(lM_LibereImpot) ||
2830 tab_employee(lN_NASConjoint) ||
2831 tab_employee(lO_RetraitRAP) ||
2832 tab_employee(lProvenance1) ||
2833 tab_employee(lBoxA_UnregisterdPlan) ||
2834 tab_employee(lDesg_BenefitExcAmt) ||
2835 tab_employee(lBoxB_DesgBenefitTrnsAmt) ||
2836 tab_employee(lBoxExcessAmt) ||
2837 tab_employee(lAmount_Transferred) ||
2838 tab_employee(lBoxC_SinglePayAccured) ||
2839 tab_employee(lBoxC_SinglePayAccUnreg) ||
2840 tab_employee(lBoxC_ExcessAmtSinPayTrans) ||
2841 tab_employee(lErrorDetails)||
2842 '</Montants>' || EOL || '</R>' || EOL ||
2843 '</' || l_status || '>' ;
2844
2845 hr_utility.trace('Just before Printing the file details ');
2846 pay_core_files.write_to_magtape_lob(l_final_xml_string);
2847 ELSE
2848
2849 IF l_negative_box = 'N' THEN
2850
2851 l_final_xml_string := '<Empdata>'||EOL;
2852 l_final_xml_string1 := '<Empdata>'||EOL;
2853 l_final_xml_string2 := '<Empdata>'||EOL;
2854
2855 FOR i IN 1 .. l_counter LOOP
2856 l_final_xml_string := l_final_xml_string ||tab_emplyr(i);
2857 l_final_xml_string1 := l_final_xml_string1 ||tab_emplyr1(i);
2858 l_final_xml_string2 := l_final_xml_string2 ||tab_emplyr2(i);
2859 END LOOP;
2860
2861
2862 IF l_footnote_count > 1 THEN
2863 tab_employee(l_footnotecode) := '<Footnotecode>'||'See Attached'||'</Footnotecode>';
2864 tab_employee(l_footnoteamt) := NULL;
2865 tab_employee1(l_footnotecode) := '<Footnotecode1>'||'See Attached'||'</Footnotecode1>';
2866 tab_employee1(l_footnoteamt) := NULL;
2867 tab_employee2(l_footnotecode) := '<Footnotecode2>'||'See Attached'||'</Footnotecode2>';
2868 tab_employee2(l_footnoteamt) := NULL;
2869 ELSIF l_footnote_count = 1 THEN
2870 tab_employee(l_footnotecode) := '<Footnotecode>'||l_footcode||'</Footnotecode>';
2871 tab_employee(l_footnoteamt) := '<Footnoteamt>' ||l_footamt ||'</Footnoteamt>';
2872 tab_employee1(l_footnotecode) := '<Footnotecode1>'||l_footcode||'</Footnotecode1>';
2873 tab_employee1(l_footnoteamt) := '<Footnoteamt1>' ||l_footamt ||'</Footnoteamt1>';
2874 tab_employee2(l_footnotecode) := '<Footnotecode2>'||l_footcode||'</Footnotecode2>';
2875 tab_employee2(l_footnoteamt) := '<Footnoteamt2>' ||l_footamt ||'</Footnoteamt2>';
2876 ELSE
2877 tab_employee(l_footnotecode) := NULL;
2878 tab_employee(l_footnoteamt) := NULL;
2879 tab_employee1(l_footnotecode) := NULL;
2880 tab_employee1(l_footnoteamt) := NULL;
2881 tab_employee2(l_footnotecode) := NULL;
2882 tab_employee2(l_footnoteamt) := NULL;
2883 END IF;
2884 l_final_xml_string :=
2885 l_final_xml_string ||EOL||
2886 tab_employee(lAnnee) ||
2887 tab_employee(lNoReleve) ||
2888 tab_employee(lNo) ||
2889 tab_employee(lNomFamille) ||
2890 tab_employee(lLigne1) ||
2891 tab_employee(lLigne2) ||
2892 tab_employee(lVille) ||
2893 tab_employee(lA_PrestRPA_RPNA) ||
2894 tab_employee(lB_PrestREER_FERR_RPDB) ||
2895 tab_employee(lC_AutrePaiement) ||
2896 tab_employee(lD_RembPrimeConjoint) ||
2897 tab_employee(lE_PrestDeces) ||
2898 tab_employee(lF_RembCotisInutilise) ||
2899 tab_employee(lG_RevocationREER_FERR) ||
2900 tab_employee(lH_AutreRevenu) ||
2901 tab_employee(lI_DroitDeduction ) ||
2902 tab_employee(lJ_ImpotQueRetenuSource) ||
2903 tab_employee(lK_RevenuApresDeces) ||
2907 tab_employee(lO_RetraitRAP) ||
2904 tab_employee(lL_RetraitREEP) ||
2905 tab_employee(lM_LibereImpot) ||
2906 tab_employee(lN_NASConjoint) ||
2908 tab_employee(lProvenance1) ||
2909 tab_employee(lNAS1) ||
2910 tab_employee(lNAS2) ||
2911 tab_employee(lNAS3) ||
2912 tab_employee(lN_NASConjoint1) ||
2913 tab_employee(lN_NASConjoint2) ||
2914 tab_employee(lCode_dereleve) ||
2915 tab_employee(l_footnotecode)||EOL||
2916 tab_employee(l_footnoteamt)||EOL||
2917 tab_employee(l_authorisation_tag)||EOL||
2918 tab_employee(l_authorisation_no)||EOL||
2919 tab_employee(l_sequence_no)||EOL||
2920 '</Empdata>'||EOL;
2921 /* Looping the data twice to meet the template requirement */
2922 l_final_xml_string1 := l_final_xml_string1 ||EOL||
2923 tab_employee1(lAnnee) ||
2924 tab_employee1(lNoReleve) ||
2925 tab_employee1(lNo) ||
2926 tab_employee1(lNomFamille) ||
2927 tab_employee1(lLigne1) ||
2928 tab_employee1(lLigne2) ||
2929 tab_employee1(lVille) ||
2930 tab_employee1(lA_PrestRPA_RPNA) ||
2931 tab_employee1(lB_PrestREER_FERR_RPDB) ||
2932 tab_employee1(lC_AutrePaiement) ||
2933 tab_employee1(lD_RembPrimeConjoint) ||
2934 tab_employee1(lE_PrestDeces) ||
2935 tab_employee1(lF_RembCotisInutilise) ||
2936 tab_employee1(lG_RevocationREER_FERR) ||
2937 tab_employee1(lH_AutreRevenu) ||
2938 tab_employee1(lI_DroitDeduction ) ||
2939 tab_employee1(lJ_ImpotQueRetenuSource) ||
2940 tab_employee1(lK_RevenuApresDeces) ||
2941 tab_employee1(lL_RetraitREEP) ||
2942 tab_employee1(lM_LibereImpot) ||
2943 tab_employee1(lN_NASConjoint) ||
2944 tab_employee1(lO_RetraitRAP) ||
2945 tab_employee1(lProvenance1) ||
2946 tab_employee1(lNAS1) ||
2947 tab_employee1(lNAS2) ||
2948 tab_employee1(lNAS3) ||
2949 tab_employee1(lN_NASConjoint1) ||
2950 tab_employee1(lN_NASConjoint2) ||
2951 tab_employee1(lCode_dereleve) ||
2952 tab_employee1(l_footnotecode)||EOL||
2953 tab_employee1(l_footnoteamt)||EOL||
2954 tab_employee1(l_authorisation_tag)||EOL||
2955 tab_employee1(l_authorisation_no)||EOL||
2956 tab_employee1(l_sequence_no)||EOL||
2957 '</Empdata>'||EOL;
2958
2959 l_final_xml_string2 := l_final_xml_string2 ||EOL||
2960 tab_employee2(lAnnee) ||
2961 tab_employee2(lNoReleve) ||
2962 tab_employee2(lNo) ||
2963 tab_employee2(lNomFamille) ||
2964 tab_employee2(lLigne1) ||
2965 tab_employee2(lLigne2) ||
2966 tab_employee2(lVille) ||
2967 tab_employee2(lA_PrestRPA_RPNA) ||
2968 tab_employee2(lB_PrestREER_FERR_RPDB) ||
2969 tab_employee2(lC_AutrePaiement) ||
2970 tab_employee2(lD_RembPrimeConjoint) ||
2971 tab_employee2(lE_PrestDeces) ||
2972 tab_employee2(lF_RembCotisInutilise) ||
2973 tab_employee2(lG_RevocationREER_FERR) ||
2974 tab_employee2(lH_AutreRevenu) ||
2975 tab_employee2(lI_DroitDeduction ) ||
2976 tab_employee2(lJ_ImpotQueRetenuSource) ||
2977 tab_employee2(lK_RevenuApresDeces) ||
2978 tab_employee2(lL_RetraitREEP) ||
2979 tab_employee2(lM_LibereImpot) ||
2980 tab_employee2(lN_NASConjoint) ||
2981 tab_employee2(lO_RetraitRAP) ||
2982 tab_employee2(lProvenance1) ||
2983 tab_employee2(lNAS1) ||
2984 tab_employee2(lNAS2) ||
2985 tab_employee2(lNAS3) ||
2986 tab_employee2(lN_NASConjoint1) ||
2987 tab_employee2(lN_NASConjoint2) ||
2988 tab_employee2(lCode_dereleve) ||
2989 tab_employee2(l_footnotecode)||EOL||
2990 tab_employee2(l_footnoteamt)||EOL||
2991 tab_employee2(l_authorisation_tag)||EOL||
2992 tab_employee2(l_authorisation_no)||EOL||
2993 tab_employee2(l_sequence_no)||EOL||
2994 '</Empdata>'||EOL;
2995
2996 -- l_page_break := '<break_dummy>'||' '||'</break_dummy>'||EOL;
2997 l_final_xml_string := '<RL2_PDF_ASG>'||l_final_xml_string
3001
2998 ||l_final_xml_string1
2999 || l_final_xml_string2
3000 || '</RL2_PDF_ASG>';
3002 hr_utility.trace('Just before Printing the file details ');
3003 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3004
3005 ELSIF l_negative_box = 'Y' THEN
3006 l_final_xml_string :=
3007 '<FAILED_RL2_PDFASG>' ||EOL||
3008 tab_emplyr(1)||EOL||
3009 tab_employee(lNo) ||
3010 tab_employee(lNomFamille) ||
3011 tab_employee(lLigne1) ||
3012 tab_employee(lLigne2) ||
3013 tab_employee(lVille) ||
3014 tab_employee(lA_PrestRPA_RPNA) ||
3015 tab_employee(lB_PrestREER_FERR_RPDB) ||
3016 tab_employee(lC_AutrePaiement) ||
3017 tab_employee(lD_RembPrimeConjoint) ||
3018 tab_employee(lE_PrestDeces) ||
3019 tab_employee(lF_RembCotisInutilise) ||
3020 tab_employee(lG_RevocationREER_FERR) ||
3021 tab_employee(lH_AutreRevenu) ||
3022 tab_employee(lI_DroitDeduction ) ||
3023 tab_employee(lJ_ImpotQueRetenuSource) ||
3024 tab_employee(lK_RevenuApresDeces) ||
3025 tab_employee(lL_RetraitREEP) ||
3026 tab_employee(lM_LibereImpot) ||
3027 tab_employee(lN_NASConjoint) ||
3028 tab_employee(lO_RetraitRAP) ||
3029 tab_employee(lProvenance1) ||
3030 tab_employee(lNAS1) ||
3031 tab_employee(lNAS2) ||
3032 tab_employee(lNAS3) ||
3033 tab_employee(lN_NASConjoint1) ||
3034 tab_employee(lN_NASConjoint2) ||
3035 tab_employee(lNoReleve) ||
3036 tab_employee(lErrorDetails)||
3037 '</FAILED_RL2_PDFASG>'||EOL;
3038
3039 hr_utility.trace('Just before Printing the file details ');
3040 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3041 END IF;
3042 END IF;
3043
3044 END;
3045 END xml_employee_record;
3046
3047 PROCEDURE xml_report_start IS
3048 BEGIN
3049
3050 DECLARE
3051 l_final_xml_string VARCHAR2(32000);
3052
3053 BEGIN
3054
3055 l_final_xml_string := '<RL2PAPER>';
3056 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3057
3058 END;
3059 END xml_report_start;
3060
3061 PROCEDURE xml_report_end IS
3062 BEGIN
3063
3064 DECLARE
3065 l_final_xml_string VARCHAR2(32000);
3066
3067 BEGIN
3068
3069 l_final_xml_string := '</RL2PAPER>';
3070 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3071
3072 END;
3073 END xml_report_end;
3074
3075
3076 PROCEDURE xml_employer_start IS
3077 BEGIN
3078
3079 DECLARE
3080
3081 l_final_xml_string VARCHAR2(32000);
3082
3083 BEGIN
3084
3085 l_final_xml_string := '<Groupe02>';
3086 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3087
3088 END;
3089 END xml_employer_start;
3090
3091 PROCEDURE xml_employer_record IS
3092 BEGIN
3093
3094 DECLARE
3095
3096 l_final_xml_string VARCHAR2(32000);
3097
3098 TYPE employer_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
3099
3100 tab_employer employer_info;
3101
3102 lAnnee NUMBER;
3103 lNbReleves NUMBER;
3104 lNold NUMBER;
3105 lTypeDossier NUMBER;
3106 lNoDossier NUMBER;
3107 lNom1 NUMBER;
3108 lLigne1 NUMBER;
3109 lLigne2 NUMBER;
3110 lVille NUMBER;
3111 lProvince NUMBER;
3112 lCodePostal NUMBER;
3113 l_taxation_year varchar2(4);
3114 l_payroll_actid NUMBER;
3115 l_year_start DATE;
3116 l_year_end DATE;
3117 l_report_type VARCHAR2(5);
3118 l_business_grpid NUMBER;
3119 l_legislative_param pay_payroll_actions.legislative_parameters%type;
3120 EOL varchar2(5);
3121 l_employer_name varchar2(100);
3122 l_quebec_bn varchar2(20);
3123 l_address_line per_addresses.address_line1%TYPE;
3124 l_address_begin_tag varchar2(10);
3125 l_address_end_tag varchar2(10);
3126
3127 BEGIN
3128 hr_utility.trace('XML Employer');
3129 hr_utility.trace('XML Employer');
3130
3131 SELECT
3132 fnd_global.local_chr(13) || fnd_global.local_chr(10)
3133 INTO EOL
3134 FROM dual;
3135
3136 lAnnee := 1;
3137 lNbReleves := 2;
3138 lNold := 3;
3142 lLigne1 := 7;
3139 lTypeDossier := 4;
3140 lNoDossier := 5;
3141 lNom1 := 6;
3143 lLigne2 := 8;
3144 lVille := 9;
3145 lProvince := 10;
3146 lCodePostal := 11;
3147
3148 l_taxation_year
3149 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
3150 l_payroll_actid
3151 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
3152
3153 get_report_parameters(
3154 l_payroll_actid,
3155 l_year_start,
3156 l_year_end,
3157 l_report_type,
3158 l_business_grpid,
3159 l_legislative_param
3160 );
3161
3162 tab_employer(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' || EOL;
3163 tab_employer(lNbReleves) := '<NbReleves>' || 'Running Total' || '</NbReleves>' || EOL;
3164
3165 hr_utility.trace('The Payroll Action Id : '||l_payroll_actid);
3166 hr_utility.trace('The business group id : '||l_business_grpid);
3167 l_quebec_bn := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
3168 l_payroll_actid,
3169 'CAEOY_RL2_QUEBEC_BN');
3170 hr_utility.trace('The Quebec Number is '||l_quebec_bn);
3171 tab_employer(lNold) := '<NoId>' || convert_special_char(substr(l_quebec_bn,1,10)) ||
3172 '</NoId>' || EOL;
3173 tab_employer(lTypeDossier) := '<TypeDossier>' || 'RS' ||
3174 '</TypeDossier>' || EOL;
3175
3176 tab_employer(lNoDossier) := '<NoDossier>' || convert_special_char(substr(l_quebec_bn,13,4)) ||
3177 '</NoDossier>' || EOL;
3178 hr_utility.trace('The Employer File Number : '|| substr(l_quebec_bn,13,4));
3179 l_employer_name := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
3180 l_payroll_actid,
3181 'CAEOY_RL2_EMPLOYER_NAME');
3182
3183 tab_employer(lNom1) := '<Nom>' ||
3184 convert_special_char(substr(l_employer_name,1,30)) || '</Nom>' || EOL;
3185 hr_utility.trace('tab_employer(lNom) = ' || tab_employer(lNom1));
3186
3187 -- Address Line 1
3188
3189 l_address_line := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
3190 l_payroll_actid,
3191 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE1');
3192
3193 IF (l_address_line IS NULL AND
3194 l_address_line <> ' ' ) THEN
3195
3196 l_address_begin_tag := '';
3197 tab_employer(lLigne1) := NULL;
3198 tab_employer(lLigne2) := NULL;
3199 tab_employer(lVille) := NULL;
3200 tab_employer(lProvince) := NULL;
3201 tab_employer(lCodePostal) := NULL;
3202 l_address_end_tag := '';
3203
3204 ELSE
3205
3206 l_address_begin_tag := '<Adresse>';
3207
3208 tab_employer(lLigne1) := '<Ligne1>' ||
3209 convert_special_char(substr(l_address_line,1,30)) || '</Ligne1>' || EOL;
3210 hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
3211
3212
3213 -- Address Line 2
3214
3215 l_address_line := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
3216 l_payroll_actid,
3217 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE2');
3218
3219 IF (l_address_line IS NOT NULL AND
3220 l_address_line <> ' ' ) THEN
3221 tab_employer(lLigne2) := '<Ligne2>' ||
3222 convert_special_char(substr(l_address_line,1,30)) || '</Ligne2>' || EOL;
3223 ELSE
3224 tab_employer(lLigne2) := NULL;
3225 END IF;
3226 hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
3227
3228 -- Ville (City)
3229
3230 l_address_line := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
3231 l_payroll_actid,
3232 'CAEOY_RL2_EMPLOYER_CITY');
3233 IF ( l_address_line IS NOT NULL AND
3234 l_address_line <> ' ') THEN
3235 tab_employer(lVille) := '<Ville>' ||
3236 convert_special_char(substr(l_address_line,1,30)) || '</Ville>' || EOL;
3237 ELSE
3238 tab_employer(lVille) := NULL;
3239 END IF;
3240 hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
3241
3242 -- Province
3243
3244 l_address_line := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
3245 l_payroll_actid,
3246 'CAEOY_RL2_EMPLOYER_PROVINCE');
3247
3248 IF ( l_address_line IS NOT NULL AND
3249 l_address_line <> ' ' ) THEN
3250 tab_employer(lProvince) := '<Province>' ||
3251 convert_special_char(SUBSTR(hr_general.decode_lookup( 'CA_PROVINCE',
3252 l_address_line),1,20)) || '</Province>' || EOL;
3253 ELSE
3254 tab_employer(lProvince) := NULL;
3255 END IF;
3259
3256 hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
3257
3258 -- Postal Code
3260 l_address_line := pay_ca_rl2_mag.get_employer_item(l_business_grpid,
3261 l_payroll_actid,
3262 'CAEOY_RL2_EMPLOYER_POSTAL_CODE');
3263
3264 IF ( l_address_line IS NOT NULL AND
3265 l_address_line <> ' ' ) THEN
3266 tab_employer(lCodePostal) := '<CodePostal>' ||
3267 convert_special_char(substr(l_address_line,1,6)) || '</CodePostal>' || EOL;
3268 ELSE
3269 tab_employer(lCodePostal) := NULL;
3270 END IF;
3271 hr_utility.trace('tab_employer(lCodePostal) = ' ||
3272 tab_employer(lCodePostal));
3273
3274 l_address_end_tag := '</Adresse>';
3275
3276 END IF;
3277 l_final_xml_string := '<T>' || EOL ||
3278 tab_employer(lAnnee) ||
3279 tab_employer(lNbReleves)|| EOL || '<PayeurEmetteur>' || EOL ||
3280 tab_employer(lNold) ||
3281 tab_employer(lTypeDossier) ||
3282 tab_employer(lNoDossier) ||
3283 tab_employer(lNom1) || l_address_begin_tag || EOL ||
3284 tab_employer(lLigne1) ||
3285 tab_employer(lLigne2) ||
3286 tab_employer(lVille) ||
3287 tab_employer(lProvince) ||
3288 tab_employer(lCodePostal) ||
3289 l_address_end_tag || EOL || '</PayeurEmetteur>' || EOL ||
3290 '</T>' || EOL ||
3291 '</Groupe02>' || EOL;
3292
3293 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3294
3295 END;
3296 END xml_employer_record;
3297
3298 /* Added by ssmukher for Bug 4030973 */
3299 /* The check digit calculated by the method below
3300 must be the same as the 10th digit of the
3301 identification number or the number is invalid.
3302
3303 Example of the modulus 11 method:
3304 The identification number is 2345678908.
3305 Use the first nine digits to validate the identification number.
3306 The tenth digit is the check digit (in this case 8).
3307
3308 Formula:
3309 Beginning with the ninth digit (extreme right), multiply each digit by the
3310 factor indicated.
3311 The factors form a circular sequence of the values 2 through 7, beginning on
3312 the right.
3313 First nine digits of the identification number 2 3 4 5 6 7 8 9 0
3314 Multiply each digit by the factor indicated. x 4 x 3 x 2 x 7 x 6 x 5 x 4 x 3
3315 x 2
3316 Results 8 9 8 35 36 35 32 27 0
3317 Add the results
3318 (8 + 9 + 8 + 35 + 36 + 35 + 32 + 27 + 0). = 190
3319 Divide the result by 11 (190/11). = 17 remainder 3
3320 If the remainder is 0, the check digit is 1. If the remainder is 1, the check
3321 digit is 0.
3322 For any other remainder obtained, the check digit is the difference between
3323 11 and that remainder.
3324 Subtract the remainder obtained from 11 (11 - 3) = 8
3325 */
3326
3327 FUNCTION validate_quebec_number (p_quebec_no IN VARCHAR2,p_qin_name varchar2)
3328 RETURN NUMBER IS
3329
3330 l_quebec NUMBER;
3331 l_rem NUMBER;
3332 i NUMBER;
3333 l_max NUMBER;
3334 l_total NUMBER;
3335 l_min NUMBER;
3336 l_modulus NUMBER;
3337 l_chk_digit NUMBER;
3338 l_act_chk_number NUMBER;
3339
3340 BEGIN
3341 i := 1;
3342 l_min := 2;
3343 l_max := 7;
3344 l_total := 0;
3345 l_quebec := to_number(substr(p_quebec_no,1,9));
3346 l_act_chk_number := to_number(substr(p_quebec_no,10,1));
3347
3348 if TRANSLATE(substr(p_quebec_no,1,9),'0123456789','9999999999') = '999999999' then
3349
3350 loop
3351 if i > 9 then
3352 exit;
3353 end if;
3354
3355 if l_min > l_max then
3356 l_min := 2;
3357 end if;
3358
3359 l_rem := mod(l_quebec,10);
3360 l_total := l_total + (l_min * l_rem);
3361 l_min := l_min + 1;
3362 l_quebec := ((l_quebec - l_rem)/10);
3363 i := i+ 1;
3364
3365 end loop;
3366
3367 l_modulus := mod(l_total, 11);
3368 if l_modulus = 0 then
3369 l_chk_digit := 1;
3370 elsif l_modulus = 1 then
3371 l_chk_digit := 0;
3372 else
3373 l_chk_digit := 11 - l_modulus;
3374 end if;
3375
3376 if l_chk_digit <> l_act_chk_number then
3377 hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
3378 hr_utility.set_message_token('PRE_NAME',p_qin_name);
3379 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
3380 pay_core_utils.push_token('PRE_NAME',p_qin_name);
3381 hr_utility.raise_error;
3382 end if;
3383 else
3384
3385 hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
3386 hr_utility.set_message_token('PRE_NAME',p_qin_name);
3387 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
3388 pay_core_utils.push_token('PRE_NAME',p_qin_name);
3389 hr_utility.raise_error;
3390
3394
3391 end if;
3392
3393 return l_chk_digit;
3395 END;
3396
3397
3398 FUNCTION convert_special_char( p_data varchar2)
3399 RETURN varchar2 IS
3400 l_data VARCHAR2(2000);
3401 l_output varchar2(2000);
3402 cursor c_uppercase(p_input_string varchar2) is
3403 select
3404 replace(
3405 replace(
3406 replace(
3407 replace(
3408 replace(
3409 replace(
3410 replace(
3411 replace(
3412 replace(
3413 replace(
3414 replace(
3415 replace(
3416 replace(
3417 replace(
3418 replace(
3419 replace(convert(p_input_string,'UTF8'),
3420 utl_raw.cast_to_varchar2(hextoraw('C380')),'A'
3421 ),
3422 utl_raw.cast_to_varchar2(hextoraw('C38A')),'E'
3423 ),
3424 utl_raw.cast_to_varchar2(hextoraw('C387')),'C'
3425 ),
3426 utl_raw.cast_to_varchar2(hextoraw('C389')),'E'
3427 ),
3428 utl_raw.cast_to_varchar2(hextoraw('C39C')),'U'
3429 ),
3430 utl_raw.cast_to_varchar2(hextoraw('C399')),'U'
3431 ),
3432 utl_raw.cast_to_varchar2(hextoraw('C39B')),'U'
3433 ),
3434 utl_raw.cast_to_varchar2(hextoraw('C394')),'O'
3435 ),
3436 utl_raw.cast_to_varchar2(hextoraw('C38F')),'I'
3437 ),
3438 utl_raw.cast_to_varchar2(hextoraw('C38E')),'I'
3439 ),
3440 utl_raw.cast_to_varchar2(hextoraw('C388')),'E'
3441 ),
3442 utl_raw.cast_to_varchar2(hextoraw('C38B')),'E'
3443 ),
3444 utl_raw.cast_to_varchar2(hextoraw('C382')),'A'
3445 ),
3446 utl_raw.cast_to_varchar2(hextoraw('C592')),'OE'
3447 ),
3448 utl_raw.cast_to_varchar2(hextoraw('C386')),'AE'
3449 ),
3450 utl_raw.cast_to_varchar2(hextoraw('C3A9')),'e'
3451 )
3452 from dual;
3453
3454 BEGIN
3455 l_data := trim(p_data);
3456 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
3457 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
3458 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
3459 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
3460 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
3461
3462 open c_uppercase(l_data);
3463 fetch c_uppercase into l_output;
3464 if c_uppercase%NOTFOUND then
3465 l_output := l_data;
3466 end if;
3467 close c_uppercase;
3468
3469 RETURN l_output;
3470 END;
3471
3472
3473 FUNCTION get_employee_item (p_asg_action_id IN number,
3474 p_assignment_id IN number,
3475 p_archived_item IN varchar2)
3476 RETURN varchar2 IS
3477
3478 CURSOR c_employee_info IS
3479 SELECT nvl(tran.quebec_business_number,'0000000000 0000'),
3480 nvl(tran.reporting_year,'0000'),
3481 nvl(emp.rl2_slip_number,'000000000'),
3482 nvl(emp.employee_sin,'000000000'),
3483 nvl(emp.employee_last_name,' '),
3484 nvl(emp.employee_first_name,' '),
3485 nvl(emp.employee_middle_initial,' '),
3486 nvl(emp.employee_address_line1,' '),
3487 nvl(emp.employee_address_line2,' '),
3488 nvl(emp.employee_address_line3,' '),
3489 nvl(emp.employee_city,' '),
3490 nvl(emp.employee_province,' '),
3491 nvl(emp.employee_postal_code,' '),
3492 nvl(emp.employee_number,' '),
3493 emp.rl2_box_a,
3494 emp.rl2_box_b,
3495 emp.rl2_box_c,
3496 emp.rl2_box_d,
3497 emp.rl2_box_e,
3498 emp.rl2_box_f,
3499 emp.rl2_box_g,
3500 emp.rl2_box_h,
3501 emp.rl2_box_i,
3502 emp.rl2_box_j,
3503 emp.rl2_box_k,
3504 emp.rl2_box_l,
3505 emp.rl2_box_m,
3506 emp.rl2_box_n,
3507 emp.rl2_box_o,
3508 decode(substr(emp.rl2_source_of_income,1,5),'OTHER','AUTRE', emp.rl2_source_of_income),
3509 nvl(emp.negative_balance_flag, 'N'),
3510 emp.person_id
3511 FROM pay_ca_eoy_rl2_employee_info_v emp,
3512 pay_ca_eoy_rl2_trans_info_v tran
3513 WHERE emp.assignment_action_id = p_asg_action_id
3514 AND emp.assignment_id = p_assignment_id
3515 AND emp.payroll_action_id = tran.payroll_action_id;
3516
3517 l_quebec_business_number varchar2(240);
3518 l_reporting_year varchar2(240);
3519 l_rl2_slip_number varchar2(240);
3520 l_employee_sin varchar2(240);
3521 l_employee_last_name varchar2(240);
3522 l_employee_first_name varchar2(240);
3523 l_employee_middle_initial varchar2(240);
3524 l_employee_address_line1 varchar2(240);
3525 l_employee_address_line2 varchar2(240);
3526 l_employee_address_line3 varchar2(240);
3527 l_employee_city varchar2(240);
3528 l_employee_province varchar2(240);
3529 l_employee_postal_code varchar2(240);
3530 l_employee_number varchar2(240);
3531 l_person_id varchar2(50);
3532 l_rl2_box_a varchar2(240);
3533 l_rl2_box_b varchar2(240);
3534 l_rl2_box_c varchar2(240);
3535 l_rl2_box_d varchar2(240);
3536 l_rl2_box_e varchar2(240);
3537 l_rl2_box_f varchar2(240);
3541 l_rl2_box_j varchar2(240);
3538 l_rl2_box_g varchar2(240);
3539 l_rl2_box_h varchar2(240);
3540 l_rl2_box_i varchar2(240);
3542 l_rl2_box_k varchar2(240);
3543 l_rl2_box_l varchar2(240);
3544 l_rl2_box_m varchar2(240);
3545 l_rl2_box_n varchar2(240);
3546 l_rl2_box_o varchar2(240);
3547 l_rl2_source_of_income varchar2(240);
3548 l_negative_balance_flag varchar2(240);
3549
3550 l_return_value varchar2(240);
3551
3552 BEGIN
3553
3554 OPEN c_employee_info;
3555 FETCH c_employee_info
3556 INTO l_quebec_business_number,
3557 l_reporting_year,
3558 l_rl2_slip_number,
3559 l_employee_sin,
3560 l_employee_last_name,
3561 l_employee_first_name,
3562 l_employee_middle_initial,
3563 l_employee_address_line1,
3564 l_employee_address_line2,
3565 l_employee_address_line3,
3566 l_employee_city,
3567 l_employee_province,
3568 l_employee_postal_code,
3569 l_employee_number,
3570 l_rl2_box_a,
3571 l_rl2_box_b,
3572 l_rl2_box_c,
3573 l_rl2_box_d,
3574 l_rl2_box_e,
3575 l_rl2_box_f,
3576 l_rl2_box_g,
3577 l_rl2_box_h,
3578 l_rl2_box_i,
3579 l_rl2_box_j,
3580 l_rl2_box_k,
3581 l_rl2_box_l,
3582 l_rl2_box_m,
3583 l_rl2_box_n,
3584 l_rl2_box_o,
3585 l_rl2_source_of_income,
3586 l_negative_balance_flag,
3587 l_person_id;
3588
3589 CLOSE c_employee_info;
3590
3591 IF p_archived_item = 'CAEOY_RL2_QUEBEC_BN' THEN
3592 l_return_value := l_quebec_business_number;
3593 ELSIF p_archived_item = 'CAEOY_TAXATION_YEAR' THEN
3594 l_return_value := l_reporting_year;
3595 ELSIF p_archived_item = 'CAEOY_RL2_SLIP_NUMBER' THEN
3596 l_return_value := l_rl2_slip_number;
3597 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_SIN' THEN
3598 l_return_value := l_employee_sin;
3599 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_LAST_NAME' THEN
3600 l_return_value := l_employee_last_name;
3601 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_FIRST_NAME' THEN
3602 l_return_value := l_employee_first_name;
3603 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_INITIAL' THEN
3604 l_return_value := l_employee_middle_initial;
3605 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_ADDRESS_LINE1' THEN
3606 l_return_value := l_employee_address_line1;
3607 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_ADDRESS_LINE2' THEN
3608 l_return_value := l_employee_address_line2;
3609 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_ADDRESS_LINE3' THEN
3610 l_return_value := l_employee_address_line3;
3611 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_CITY' THEN
3612 l_return_value := l_employee_city;
3613 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_PROVINCE' THEN
3614 l_return_value := l_employee_province;
3615 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_POSTAL_CODE' THEN
3616 l_return_value := l_employee_postal_code;
3617 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYEE_NUMBER' THEN
3618 l_return_value := l_employee_number;
3619 ELSIF p_archived_item = 'CAEOY_RL2_BOX_A' THEN
3620 l_return_value := l_rl2_box_a;
3621 ELSIF p_archived_item = 'CAEOY_RL2_BOX_B' THEN
3622 l_return_value := l_rl2_box_b;
3623 ELSIF p_archived_item = 'CAEOY_RL2_BOX_C' THEN
3624 l_return_value := l_rl2_box_c;
3625 ELSIF p_archived_item = 'CAEOY_RL2_BOX_D' THEN
3626 l_return_value := l_rl2_box_d;
3627 ELSIF p_archived_item = 'CAEOY_RL2_BOX_E' THEN
3628 l_return_value := l_rl2_box_e;
3629 ELSIF p_archived_item = 'CAEOY_RL2_BOX_F' THEN
3630 l_return_value := l_rl2_box_f;
3631 ELSIF p_archived_item = 'CAEOY_RL2_BOX_G' THEN
3632 l_return_value := l_rl2_box_g;
3633 ELSIF p_archived_item = 'CAEOY_RL2_BOX_H' THEN
3634 l_return_value := l_rl2_box_h;
3635 ELSIF p_archived_item = 'CAEOY_RL2_BOX_I' THEN
3636 l_return_value := l_rl2_box_i;
3637 ELSIF p_archived_item = 'CAEOY_RL2_BOX_J' THEN
3638 l_return_value := l_rl2_box_j;
3639 ELSIF p_archived_item = 'CAEOY_RL2_BOX_K' THEN
3640 l_return_value := l_rl2_box_k;
3641 ELSIF p_archived_item = 'CAEOY_RL2_BOX_L' THEN
3642 l_return_value := l_rl2_box_l;
3643 ELSIF p_archived_item = 'CAEOY_RL2_BOX_M' THEN
3644 l_return_value := l_rl2_box_m;
3645 ELSIF p_archived_item = 'CAEOY_RL2_BOX_N' THEN
3646 l_return_value := l_rl2_box_n;
3647 ELSIF p_archived_item = 'CAEOY_RL2_BOX_O' THEN
3648 l_return_value := l_rl2_box_o;
3649 ELSIF p_archived_item = 'CAEOY_RL2_SOURCE_OF_INCOME' THEN
3650 l_return_value := l_rl2_source_of_income;
3651 ELSIF p_archived_item = 'CAEOY_RL2_NEGATIVE_BALANCE' THEN
3652 l_return_value := l_negative_balance_flag;
3653 ELSIF p_archived_item = 'CAEOY_PERSON_ID' THEN
3654 l_return_value := l_person_id;
3655 END IF;
3656
3657 RETURN l_return_value;
3658
3659 END get_employee_item;
3660
3661
3662 PROCEDURE archive_ca_deinit (p_pactid IN NUMBER) IS
3663
3664 CURSOR c_get_report_type ( p_pactid number) IS
3665 SELECT report_type
3666 FROM pay_payroll_actions
3667 WHERE payroll_action_id = p_pactid;
3668
3669 l_report_type pay_payroll_actions.report_type%type;
3670
3671 BEGIN
3672
3673 open c_get_report_type(p_pactid);
3674 fetch c_get_report_type
3675 into l_report_type;
3676 close c_get_report_type;
3677
3678 IF l_report_type = 'RL2PAPERPDF' THEN
3679 pay_ca_payroll_utils.delete_actionid(p_pactid);
3680 END IF;
3681
3682 END archive_ca_deinit;
3683
3684 FUNCTION getnext_seq_num (p_curr_seq IN NUMBER)
3685 RETURN NUMBER IS
3686 l_seq_number number;
3687 l_check_number number;
3688 BEGIN
3689
3690 l_check_number := mod(p_curr_seq,7);
3691 hr_utility.trace('l_check_number ='|| l_check_number);
3692 l_seq_number := (p_curr_seq * 10) + l_check_number;
3693 hr_utility.trace('l_seq_number ='|| l_seq_number);
3694 return l_seq_number;
3695 END;
3696 END pay_ca_rl2_mag;