[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_RL2_CAN_MAG
Source
1 PACKAGE BODY PAY_CA_RL2_CAN_MAG AS
2 /* $Header: pycarl2cmg.pkb 120.11.12020000.7 2012/12/18 09:21:06 sgotlasw 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 RL2
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_can_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_can_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,p_report_type VARCHAR2) 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 = decode(p_report_type,
77 'RL2_XML_MAG', 'RL2',
78 'RL2_AMEND_MAG', 'CAEOY_RL2_AMEND_PP') AND
79 ppa.effective_date = p_effective_date AND
80 p_transmitter_org_id =
81 pay_ca_rl2_can_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 l_locked_report_type pay_payroll_actions.report_type%TYPE;
96 l_mag_pactid NUMBER;
97
98 CURSOR cur_ppa IS
99 SELECT
100 ppa.legislative_parameters
101 FROM
102 pay_payroll_actions ppa
103 WHERE
104 ppa.payroll_action_id = p_payroll_action_id;
105
106 CURSOR c_ppa_report_type(p_pact_id number) IS
107 SELECT
108 ppa.report_type
109 FROM
110 pay_payroll_actions ppa
111 WHERE
112 ppa.payroll_action_id = p_pact_id;
113
114 BEGIN
115
116 OPEN cur_ppa;
117 FETCH cur_ppa
118 INTO l_legislative_parameters;
119 CLOSE cur_ppa;
120
121 l_transmitter_org_id := pay_ca_rl2_can_mag.get_parameter('TRANSMITTER_PRE',
122 l_legislative_parameters);
123
124 hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
125 hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
126 hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
127 hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
128
129 l_mag_pactid := pay_ca_rl1_can_mag.get_parameter('PAY_ACT',
130 l_legislative_parameters);
131
132 OPEN c_ppa_report_type(l_mag_pactid);
133 FETCH c_ppa_report_type
134 INTO l_locked_report_type;
135 CLOSE c_ppa_report_type;
136
137 hr_utility.trace('l_locked_report_type = ' || l_locked_report_type);
138
139 OPEN cur_arch_pactid(l_transmitter_org_id, l_locked_report_type);
140 FETCH cur_arch_pactid
141 INTO l_arch_pactid;
142 CLOSE cur_arch_pactid;
143
144 l_transmitter_number := pay_ca_rl2_can_mag.get_transmitter_item( p_bg_id,
145 l_arch_pactid,
146 'CAEOY_RL2_TRANSMITTER_NUMBER');
147 l_transmitter_name := pay_ca_rl2_can_mag.get_transmitter_item( p_bg_id,
148 l_arch_pactid,
149 'CAEOY_RL2_TRANSMITTER_NAME');
150 BEGIN
151
152 hr_utility.trace('l_transmitter_number = ' || l_transmitter_number);
153 SELECT substr(l_transmitter_number,1,2)
154 INTO dummy1
155 FROM dual;
156
157 IF (dummy1 <> 'NP' OR
158 length(l_transmitter_number) <> 8) THEN
159 RAISE INVALID_NUMBER;
160 END IF;
161
162 SELECT to_number(substr(l_transmitter_number,3,6))
163 INTO dummy
164 FROM dual;
165
166 EXCEPTION
167 WHEN INVALID_NUMBER THEN
168 hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
169 hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
170 pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
171 pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
172 hr_utility.raise_error;
173 END;
174
175 l_type_of_package := pay_ca_rl2_can_mag.get_transmitter_item(p_bg_id,
176 l_arch_pactid,
177 'CAEOY_RL2_TRANSMITTER_PACKAGE_TYPE');
178
179 hr_utility.trace('l_type_of_package = ' || l_type_of_package);
180
181 IF l_type_of_package IS NULL THEN
182 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
183 hr_utility.raise_error;
184 END IF;
185
186 l_source_of_slips := pay_ca_rl2_can_mag.get_transmitter_item(p_bg_id,
187 l_arch_pactid,
188 'CAEOY_RL2_SOURCE_OF_SLIPS');
189 hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
190
191 IF l_source_of_slips IS NULL THEN
192 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
193 hr_utility.raise_error;
194 END IF;
195
196 l_address_line1 := pay_ca_rl2_can_mag.get_transmitter_item(p_bg_id,
197 l_arch_pactid,
198 'CAEOY_RL2_TRANSMITTER_ADDRESS_LINE1');
199 hr_utility.trace('l_address_line1 = ' || l_address_line1);
200
201 IF l_address_line1 IS NULL THEN
202 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
203 hr_utility.raise_error;
204 END IF;
205
206 END;
207
208 END validate_transmitter_info;
209
210
211 -----------------------------------------------------------------------------
212 --Name
213 -- range_cursor
214 --Purpose
215 -- This procedure defines a SQL statement
216 -- to fetch all the people to be included in the report. This SQL statement
217 -- is used to define the 'chunks' for multi-threaded operation
218 --Arguments
219 -- p_pactid payroll action id for the report
220 -- p_sqlstr the SQL statement to fetch the people
221 ------------------------------------------------------------------------------
222 PROCEDURE range_cursor (
223 p_pactid IN NUMBER,
224 p_sqlstr OUT NOCOPY VARCHAR2
225 )
226 IS
227 p_year_start DATE;
228 p_year_end DATE;
229 p_business_group_id NUMBER;
230 p_report_type VARCHAR2(30);
231 p_legislative_param pay_payroll_actions.legislative_parameters%type;
232
233 BEGIN
234
235 hr_utility.set_location( 'pay_ca_rl2_can_mag.range_cursor', 10);
236
237 get_report_parameters(
238 p_pactid,
239 p_year_start,
240 p_year_end,
241 p_report_type,
242 p_business_group_id,
243 p_legislative_param
244 );
245
246 hr_utility.set_location( 'pay_ca_rl2_can_mag.range_cursor', 20);
247
248 p_sqlstr := 'select distinct paaf.person_id
249 from per_all_assignments_f paaf,
250 pay_payroll_actions ppa,
251 pay_payroll_actions ppa1,
252 pay_assignment_actions paa
253 where ppa.payroll_action_id = paa.payroll_action_id
254 and paa.assignment_id = paaf.assignment_id
255 and ppa1.payroll_action_id = :payroll_action_id
256 and ppa.payroll_action_id =
257 to_number(pay_ca_rl2_can_mag.get_parameter(''PAY_ACT'', ppa1.legislative_parameters))
258 and paaf.person_id =
259 nvl(pay_ca_rl2_can_mag.get_parameter(''PER_ID'',ppa1.legislative_parameters),paaf.person_id)
260 order by paaf.person_id';
261 -- order by paaf.person_id clause added for bug 9211063
262
263 hr_utility.set_location( 'pay_ca_rl2_can_mag.range_cursor',30);
264
265 END range_cursor;
266
267 -------------------------------------------------------------------------------
268 --Name
269 -- create_assignment_act
270 --Purpose
271 -- Creates assignment actions for the payroll action associated with the
272 -- report
273 --Arguments
274 -- p_pactid payroll action for the report
275 -- p_stperson starting person id for the chunk
276 -- p_endperson last person id for the chunk
277 -- p_chunk size of the chunk
278 --Note
279 -- The procedure processes assignments in 'chunks' to facilitate
280 -- multi-threaded operation. The chunk is defined by the size and the
281 -- starting and ending person id. An interlock is also created against the
282 -- pre-processor assignment action to prevent rolling back of the archiver.
283 ------------------------------------------------------------------------------
284 PROCEDURE create_assignment_act(
285 p_pactid IN NUMBER,
286 p_stperson IN NUMBER,
287 p_endperson IN NUMBER,
288 p_chunk IN NUMBER )
289 IS
290 -- Cursor to retrieve all the assignments for all GRE's
291 -- archived in a reporting year
292 l_asg_set_id NUMBER;
293
294 CURSOR c_all_asg IS
295 select paaf.person_id,
296 paaf.assignment_id,
297 paa1.tax_unit_id,
298 paaf.effective_end_date,
299 paa.assignment_action_id,
300 ppa2.payroll_action_id
301 from
302 per_all_assignments_f paaf,
303 pay_payroll_actions ppa,
304 pay_payroll_actions ppa1,
305 pay_payroll_actions ppa2,
306 pay_assignment_actions paa,
307 pay_assignment_actions paa1,
308 pay_action_interlocks int
309 where
310 ppa.payroll_action_id = paa.payroll_action_id
311 and paa.assignment_id = paaf.assignment_id
312 and ppa1.payroll_action_id = p_pactid
313 and paaf.person_id between p_stperson and p_endperson
314 and ppa.payroll_action_id =
315 to_number(pay_ca_rl2_can_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
316 and ppa2.report_type in ('RL2','CAEOY_RL2_AMEND_PP')
317 and int.locking_action_id = paa.assignment_action_id
318 and paa1.assignment_action_id = int.locked_action_id
319 and ppa2.payroll_action_id = paa1.payroll_action_id
320 and ppa2.action_status = 'C'
321 and paa.assignment_action_id
322 not in (select paa2.assignment_action_id
323 from pay_action_interlocks pai, pay_assignment_actions paa2
324 where paa2.assignment_action_id = pai.locked_action_id
325 and paa2.payroll_action_id =
326 to_number(pay_ca_rl2_can_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
327 )
328 -- and paaf.effective_start_date condition added for bug 9142143
329 and paaf.effective_start_date = (select max(paaf2.effective_start_date)
330 from per_all_assignments_f paaf2
331 where paaf2.assignment_id = paa.assignment_id
332 and paaf2.effective_start_date <= ppa.effective_date);
333
334 CURSOR c_all_asg_in_asgset IS
335 select paaf.person_id,
336 paaf.assignment_id,
337 paa1.tax_unit_id,
338 paaf.effective_end_date,
339 paa.assignment_action_id,
340 ppa2.payroll_action_id
341 from
342 per_all_assignments_f paaf,
343 pay_payroll_actions ppa,
344 pay_payroll_actions ppa1,
345 pay_payroll_actions ppa2,
346 pay_assignment_actions paa,
347 pay_assignment_actions paa1,
348 pay_action_interlocks int
349 where
350 ppa.payroll_action_id = paa.payroll_action_id
351 and paa.assignment_id = paaf.assignment_id
352 and ppa1.payroll_action_id = p_pactid
353 and paaf.person_id between p_stperson and p_endperson
354 and ppa.payroll_action_id =
355 to_number(pay_ca_rl2_can_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
356 and ppa2.report_type in ('RL2','CAEOY_RL2_AMEND_PP')
357 and int.locking_action_id = paa.assignment_action_id
358 and paa1.assignment_action_id = int.locked_action_id
359 and ppa2.payroll_action_id = paa1.payroll_action_id
360 and ppa2.action_status = 'C'
361 and exists (select 1
362 from hr_assignment_set_amendments hasa,
363 per_assignments_f paf
364 where hasa.assignment_set_id = l_asg_set_id
365 and upper(hasa.include_or_exclude) = 'I'
366 and hasa.assignment_id = paf.assignment_id
367 and paf.person_id = paaf.person_id)
368 and paa.assignment_action_id
369 not in (select paa2.assignment_action_id
370 from pay_action_interlocks pai, pay_assignment_actions paa2
371 where paa2.assignment_action_id = pai.locked_action_id
372 and paa2.payroll_action_id =
373 to_number(pay_ca_rl2_can_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
374 )
375 -- and paaf.effective_start_date condition added for bug 9142143
376 and paaf.effective_start_date = (select max(paaf2.effective_start_date)
377 from per_all_assignments_f paaf2
378 where paaf2.assignment_id = paa.assignment_id
379 and paaf2.effective_start_date <= ppa.effective_date);
380
381 l_year_start DATE;
382 l_year_end DATE;
383 l_effective_end_date DATE;
384 l_report_type VARCHAR2(30);
385 l_business_group_id NUMBER;
386 l_person_id NUMBER;
387 l_assignment_id NUMBER;
388 l_assignment_action_id NUMBER;
389 l_value NUMBER;
390 l_tax_unit_id NUMBER;
391 lockingactid NUMBER;
392
393 /* Added by ssmukher */
394 l_prev_payact NUMBER;
395 l_payroll_act NUMBER;
396 l_emplyer_name VARCHAR2(240);
397 l_quebec_no VARCHAR2(20);
398 l_file_no VARCHAR2(10);
399 l_return NUMBER;
400 l_addr_line VARCHAR2(240);
401 l_legislative_param pay_payroll_actions.legislative_parameters%type;
402
403 BEGIN
404
405 -- hr_utility.trace_on(NULL,'RL2MAG');
406 -- Get the report parameters. These define the report being run.
407 l_prev_payact := -1;
408 hr_utility.set_location( 'pay_ca_rl2_can_mag.create_assignment_act',10);
409
410 get_report_parameters(
411 p_pactid,
412 l_year_start,
413 l_year_end,
414 l_report_type,
415 l_business_group_id,
416 l_legislative_param
417 );
418 /* Validating Transmitter Information */
419 validate_transmitter_info(p_pactid,
420 l_business_group_id,
421 l_year_end);
422
423 hr_utility.set_location( 'pay_ca_rl2_can_mag.create_assignment_act',20);
424 hr_utility.trace('Report type '||l_report_type);
425
426 l_asg_set_id := to_number(pay_ca_rl2_can_mag.get_parameter('ASG_SET_ID',l_legislative_param));
427 hr_utility.trace('Assignment Set Id : '|| to_char(l_asg_set_id));
428
429 IF l_asg_set_id IS NOT NULL THEN
430 OPEN c_all_asg_in_asgset;
431 ELSE
432 OPEN c_all_asg;
433 END IF;
434
435 LOOP
436 IF l_asg_set_id IS NOT NULL THEN
437 FETCH c_all_asg_in_asgset INTO l_person_id,
438 l_assignment_id,
439 l_tax_unit_id,
440 l_effective_end_date,
441 l_assignment_action_id,
442 l_payroll_act;
443
444 hr_utility.set_location('pay_ca_rl2_can_mag.create_assignement_act', 25);
445
446 EXIT WHEN c_all_asg_in_asgset%NOTFOUND;
447 ELSE
448 FETCH c_all_asg INTO l_person_id,
449 l_assignment_id,
450 l_tax_unit_id,
451 l_effective_end_date,
452 l_assignment_action_id,
453 l_payroll_act;
454
455 hr_utility.set_location('pay_ca_rl2_can_mag.create_assignement_act', 30);
456
457 EXIT WHEN c_all_asg%NOTFOUND;
458 END IF;
459
460 --Create the assignment action for the record
461
462 /* Validating QIN Number information */
463 if l_prev_payact <> l_payroll_act then
464
465 hr_utility.trace('The payroll action id '||l_payroll_act);
466
467 l_prev_payact := l_payroll_act;
468 l_emplyer_name := pay_ca_rl2_can_mag.get_employer_item(l_business_group_id,
469 l_payroll_act,
470 'CAEOY_RL2_EMPLOYER_NAME');
471
472 l_quebec_no := pay_ca_rl2_can_mag.get_employer_item(l_business_group_id,
473 l_payroll_act,
474 'CAEOY_RL2_QUEBEC_BN');
475 hr_utility.trace('The Quebec Number is '||l_quebec_no);
476 l_file_no := substr(l_quebec_no,13,4);
477 l_quebec_no := substr(l_quebec_no ,1,10);
478
479 /* Fix for Bug# 4038551 */
480 if (l_file_no = '0000' and l_quebec_no = '0000000000') or
481 length(l_file_no) < 4
482 then
483 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
484 pay_core_utils.push_token('PRE_NAME',l_emplyer_name);
485 hr_utility.raise_error;
486 end if;
487
488
489 /* Erroring out the RL2 Electronic Interface if any of the
490 mandatory information is missing i.e Address Line 1 */
491
492 l_addr_line := pay_ca_rl2_can_mag.get_employer_item(l_business_group_id,
493 l_payroll_act,
494 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE1');
495 if l_addr_line = ' '
496 then
497 pay_core_utils.push_message(800,'PAY_CA_RL2_MISSING_ADDRESS','P');
498 hr_utility.raise_error;
499 end if;
500 hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
501 l_return := validate_quebec_number(l_quebec_no,l_emplyer_name);
502
503 end if ;
504
505 hr_utility.trace('Assignment Fetched - ');
506 hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
507 hr_utility.trace('Person Id : '|| to_char(l_person_id));
508 hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
509 hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
510
511 hr_utility.set_location('pay_ca_rl2_can_mag.create_assignment_act', 40);
512
513 SELECT pay_assignment_actions_s.nextval
514 INTO lockingactid
515 FROM dual;
516
517 hr_utility.set_location('pay_ca_rl2_can_mag.create_assignment_act', 50);
518 hr_nonrun_asact.insact(lockingactid,
519 l_assignment_id,
520 p_pactid,
521 p_chunk,
522 l_tax_unit_id);
523
524 hr_utility.set_location('pay_ca_rl2_can_mag.create_assignment_act', 60);
525
526 hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
527
528 hr_utility.set_location('pay_ca_rl2_can_mag.create_assignment_act', 70);
529 hr_utility.trace('Interlock Created - ');
530 hr_utility.trace('Locking Action : '|| to_char(lockingactid));
531 hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
532 END LOOP;
533
534 IF l_asg_set_id IS NOT NULL THEN
535 CLOSE c_all_asg_in_asgset;
536 ELSE
537 CLOSE c_all_asg;
538 END IF;
539
540
541 END create_assignment_act;
542
543 FUNCTION get_parameter(name IN varchar2, parameter_list varchar2)
544 RETURN varchar2 IS
545 start_ptr number;
546 end_ptr number;
547 token_val pay_payroll_actions.legislative_parameters%type;
548 par_value pay_payroll_actions.legislative_parameters%type;
549 BEGIN
550 --
551 token_val := name||'=';
552 --
553 start_ptr := instr(parameter_list, token_val) + length(token_val);
554 end_ptr := instr(parameter_list, ' ',start_ptr);
555 --
556 /* if there is no spaces use then length of the string */
557 IF end_ptr = 0 THEN
558 end_ptr := length(parameter_list)+1;
559 END IF;
560 --
561 /* Did we find the token */
562 IF instr(parameter_list, token_val) = 0 THEN
563 par_value := NULL;
564 ELSE
565 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
566 END IF;
567
568 RETURN par_value;
569
570 END get_parameter;
571
572
573 FUNCTION get_transmitter_item (p_business_group_id IN number,
574 p_pact_id IN number,
575 p_archived_item IN varchar2)
576 RETURN varchar2 IS
577
578 CURSOR c_trans_info IS
579 SELECT nvl(transmitter_number,' '),
580 nvl(reporting_year,'0000'),
581 nvl(transmitter_package_type,'0'),
582 nvl(transmitter_type_indicator,'0'),
583 nvl(transmitter_name,' '),
584 nvl(source_of_slips,' '),
585 nvl(transmitter_address_line1,' '),
586 nvl(transmitter_address_line2,' '),
587 nvl(transmitter_city,' '),
588 nvl(transmitter_province,' '),
589 nvl(transmitter_postal_code,' '),
590 nvl(transmitter_tech_contact_name,' '),
591 nvl(transmitter_tech_contact_code,'000'),
592 nvl(transmitter_tech_contact_phone,'0000000'),
593 nvl(transmitter_tech_contact_extn,'0000'),
594 nvl(transmitter_tech_contact_lang,' '),
595 nvl(transmitter_acct_contact_name,' '),
596 nvl(transmitter_acct_contact_code,'000'),
597 nvl(transmitter_acct_contact_phone,'0000000'),
598 nvl(transmitter_acct_contact_extn,'0000'),
599 nvl(transmitter_acct_contact_lang,' ')
600 FROM pay_ca_eoy_rl2_trans_info_v
601 WHERE business_group_id = p_business_group_id
602 AND payroll_action_id = p_pact_id;
603
604 l_trans_number varchar2(240);
605 l_reporting_year varchar2(240);
606 l_trans_package_type varchar2(240);
607 l_trans_type_indicator varchar2(240);
608 l_trans_name varchar2(240);
609 l_source_of_slips varchar2(240);
610 l_trans_address_line1 varchar2(240);
611 l_trans_address_line2 varchar2(240);
612 l_trans_city varchar2(240);
613 l_trans_province varchar2(240);
614 l_trans_postal_code varchar2(240);
615 l_trans_tech_contact_name varchar2(240);
616 l_trans_tech_contact_code varchar2(240);
617 l_trans_tech_contact_phone varchar2(240);
618 l_trans_tech_contact_extn varchar2(240);
619 l_trans_tech_contact_lang varchar2(240);
620 l_trans_acct_contact_name varchar2(240);
621 l_trans_acct_contact_code varchar2(240);
622 l_trans_acct_contact_phone varchar2(240);
623 l_trans_acct_contact_extn varchar2(240);
624 l_trans_acct_contact_lang varchar2(240);
625
626 l_return_value varchar2(240);
627
628 BEGIN
629
630 OPEN c_trans_info;
631 FETCH c_trans_info
632 INTO l_trans_number,
633 l_reporting_year,
634 l_trans_package_type,
635 l_trans_type_indicator,
636 l_trans_name,
637 l_source_of_slips,
638 l_trans_address_line1,
639 l_trans_address_line2,
640 l_trans_city,
641 l_trans_province,
642 l_trans_postal_code,
643 l_trans_tech_contact_name,
644 l_trans_tech_contact_code,
645 l_trans_tech_contact_phone,
646 l_trans_tech_contact_extn,
647 l_trans_tech_contact_lang,
648 l_trans_acct_contact_name,
649 l_trans_acct_contact_code,
650 l_trans_acct_contact_phone,
651 l_trans_acct_contact_extn,
652 l_trans_acct_contact_lang;
653
654 CLOSE c_trans_info;
655
656 IF p_archived_item = 'CAEOY_RL2_TRANSMITTER_NUMBER' THEN
657 l_return_value := l_trans_number;
658 ELSIF p_archived_item = 'CAEOY_TAXATION_YEAR' THEN
659 l_return_value := l_reporting_year;
660 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_PACKAGE_TYPE' THEN
661 l_return_value := l_trans_package_type;
662 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_TYPE' THEN
663 l_return_value := l_trans_type_indicator;
664 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_NAME' THEN
665 l_return_value := l_trans_name;
666 ELSIF p_archived_item = 'CAEOY_RL2_SOURCE_OF_SLIPS' THEN
667 l_return_value := l_source_of_slips;
668 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_ADDRESS_LINE1' THEN
669 l_return_value := l_trans_address_line1;
670 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_ADDRESS_LINE2' THEN
671 l_return_value := l_trans_address_line2;
672 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_CITY' THEN
673 l_return_value := l_trans_city;
674 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_PROVINCE' THEN
675 l_return_value := l_trans_province;
676 ELSIF p_archived_item = 'CAEOY_RL2_TRANSMITTER_POSTAL_CODE' THEN
677 l_return_value := l_trans_postal_code;
678 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_NAME' THEN
679 l_return_value := l_trans_tech_contact_name;
680 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_AREA_CODE' THEN
681 l_return_value := l_trans_tech_contact_code;
682 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_PHONE' THEN
683 l_return_value := l_trans_tech_contact_phone;
684 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_EXTENSION' THEN
685 l_return_value := l_trans_tech_contact_extn;
686 ELSIF p_archived_item = 'CAEOY_RL2_TECHNICAL_CONTACT_LANGUAGE' THEN
687 l_return_value := l_trans_tech_contact_lang;
688 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_NAME' THEN
689 l_return_value := l_trans_acct_contact_name;
690 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_AREA_CODE' THEN
691 l_return_value := l_trans_acct_contact_code;
692 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_PHONE' THEN
693 l_return_value := l_trans_acct_contact_phone;
694 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_EXTENSION' THEN
695 l_return_value := l_trans_acct_contact_extn;
696 ELSIF p_archived_item = 'CAEOY_RL2_ACCOUNTING_CONTACT_LANGUAGE' THEN
697 l_return_value := l_trans_acct_contact_lang;
698 END IF;
699
700 RETURN l_return_value;
701
702 END get_transmitter_item;
703
704 FUNCTION get_employer_item (p_business_group_id IN number,
705 p_pact_id IN number,
706 p_archived_item IN varchar2)
707 RETURN varchar2 IS
708
709 CURSOR c_employer_info IS
710 SELECT nvl(employer_name,' '),
711 nvl(quebec_business_number,'0000000000 0000'),
712 nvl(reporting_year,'0000'),
713 nvl(employer_add_line1,' '),
714 nvl(employer_add_line2,' '),
715 nvl(employer_city,' '),
716 nvl(employer_province,' '),
717 nvl(employer_postal_code,' ')
718 FROM pay_ca_eoy_rl2_trans_info_v
719 WHERE business_group_id = p_business_group_id
720 AND payroll_action_id = p_pact_id;
721
722 l_employer_name varchar2(240);
723 l_reporting_year varchar2(240);
724 l_quebec_business_number varchar2(240);
725 l_employer_add_line1 varchar2(240);
726 l_employer_add_line2 varchar2(240);
727 l_employer_city varchar2(240);
728 l_employer_province varchar2(240);
729 l_employer_postal_code varchar2(240);
730
731 l_return_value varchar2(240);
732
733 BEGIN
734
735 OPEN c_employer_info;
736 FETCH c_employer_info
737 INTO l_employer_name,
738 l_quebec_business_number,
739 l_reporting_year,
740 l_employer_add_line1,
741 l_employer_add_line2,
742 l_employer_city,
743 l_employer_province,
744 l_employer_postal_code;
745
746 CLOSE c_employer_info;
747
748 IF p_archived_item = 'CAEOY_RL2_QUEBEC_BN' THEN
749 l_return_value := l_quebec_business_number;
750 ELSIF p_archived_item = 'CAEOY_TAXATION_YEAR' THEN
751 l_return_value := l_reporting_year;
752 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_NAME' THEN
753 l_return_value := l_employer_name;
754 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE1' THEN
755 l_return_value := l_employer_add_line1;
756 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE2' THEN
757 l_return_value := l_employer_add_line2;
758 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_CITY' THEN
759 l_return_value := l_employer_city;
760 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_PROVINCE' THEN
761 l_return_value := l_employer_province;
762 ELSIF p_archived_item = 'CAEOY_RL2_EMPLOYER_POSTAL_CODE' THEN
763 l_return_value := l_employer_postal_code;
764 END IF;
765
766 RETURN l_return_value;
767
768 END get_employer_item;
769
770
771 PROCEDURE end_of_file is
772
773 BEGIN
774
775 DECLARE
776
777 l_final_xml_string VARCHAR2(32000);
778
779 BEGIN
780
781 l_final_xml_string := '</Transmission>';
782 pay_core_files.write_to_magtape_lob(l_final_xml_string);
783
784 END;
785
786 END;
787
788 PROCEDURE xml_transmitter_record IS
789 BEGIN
790
791 DECLARE
792
793 CURSOR c_trans_info(p_business_group_id IN number,
794 p_pact_id IN number) IS
795 SELECT nvl(transmitter_number,' '),
796 nvl(reporting_year,'0000'),
797 nvl(transmitter_package_type,'0'),
798 nvl(transmitter_type_indicator,'0'),
799 nvl(transmitter_name,' '),
800 nvl(source_of_slips,' '),
801 nvl(transmitter_address_line1,' '),
802 nvl(transmitter_address_line2,' '),
803 nvl(transmitter_city,' '),
804 nvl(transmitter_province,' '),
805 nvl(transmitter_postal_code,' '),
806 nvl(transmitter_tech_contact_name,' '),
807 nvl(transmitter_tech_contact_code,'000'),
808 nvl(transmitter_tech_contact_phone,'0000000'),
809 nvl(transmitter_tech_contact_extn,'0000'),
810 nvl(transmitter_tech_contact_lang,' '),
811 nvl(transmitter_acct_contact_name,' '),
812 nvl(transmitter_acct_contact_code,'000'),
813 nvl(transmitter_acct_contact_phone,'0000000'),
814 nvl(transmitter_acct_contact_extn,'0000'),
815 nvl(transmitter_acct_contact_lang,' ')
816 FROM pay_ca_eoy_rl2_trans_info_v
817 WHERE business_group_id = p_business_group_id
818 AND payroll_action_id = p_pact_id;
819
820 cursor c_rl_package_type is
821 select target1.ORG_INFORMATION6 Type_of_Package
822 from hr_organization_information target1
823 where target1.organization_id = pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
824 and target1.org_information_context = 'Prov Reporting Est';
825
826 l_trans_number varchar2(240);
827 l_reporting_year varchar2(240);
828 l_trans_package_type varchar2(240);
829 l_trans_type_indicator varchar2(240);
830 l_trans_name varchar2(240);
831 l_source_of_slips varchar2(240);
832 l_trans_address_line1 varchar2(240);
833 l_trans_address_line2 varchar2(240);
834 l_trans_city varchar2(240);
835 l_trans_province varchar2(240);
836 l_trans_postal_code varchar2(240);
837 l_trans_tech_contact_name varchar2(240);
838 l_trans_tech_contact_code varchar2(240);
839 l_trans_tech_contact_phone varchar2(240);
840 l_trans_tech_contact_extn varchar2(240);
841 l_trans_tech_contact_lang varchar2(240);
842 l_trans_acct_contact_name varchar2(240);
843 l_trans_acct_contact_code varchar2(240);
844 l_trans_acct_contact_phone varchar2(240);
845 l_trans_acct_contact_extn varchar2(240);
846 l_trans_acct_contact_lang varchar2(240);
847
848 l_final_xml_string VARCHAR2(32000);
849 l_tech_accnt_info VARCHAR2(32000);
850
851 TYPE transmitter_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
852
853 tab_transmitter transmitter_info;
854
855 lAnnee NUMBER;
856 lTypeEnvoi NUMBER;
857 lProvenance NUMBER;
858 lNo NUMBER;
859 lType NUMBER;
860 lNom1 NUMBER;
861 lNom2 NUMBER;
862 lLigne1 NUMBER;
863 lLigne2 NUMBER;
864 lVille NUMBER;
865 lProvince NUMBER;
866 lCodePostal NUMBER;
867 lNom NUMBER;
868 lIndRegional NUMBER;
869 lTel NUMBER;
870 lPosteTel NUMBER;
871 lLangue NUMBER;
872 lANom NUMBER;
873 lAIndRegional NUMBER;
874 lATel NUMBER;
875 lAPosteTel NUMBER;
876 lALangue NUMBER;
877
878 EOL VARCHAR2(5);
879 l_transmitter_name VARCHAR2(100);
880 l_taxation_year VARCHAR2(4);
881 l_return VARCHAR2(60);
882 l_payroll_actid NUMBER;
883 l_year_start DATE;
884 l_year_end DATE;
885 l_report_type VARCHAR2(30);
886 l_business_grpid NUMBER;
887 l_legislative_param pay_payroll_actions.legislative_parameters%type;
888 /* Bug 4777374 Fix */
889 l_Informatique_tag CHAR(1);
890 l_Comptabilite_tag CHAR(1);
891 /* Bug 4906963 Fix */
892 l_authorization_no VARCHAR2(20);
893 lNoConcepteur NUMBER;
894 l_VersionSchema VARCHAR2(20);
895 l_rl_package_type VARCHAR2(20);
896
897 BEGIN
898
899 hr_utility.trace('XML Transmitter');
900
901 SELECT
902 fnd_global.local_chr(13) || fnd_global.local_chr(10)
903 INTO EOL
904 FROM dual;
905
906 lAnnee := 1;
907 lTypeEnvoi := 2;
908 lProvenance := 3;
909 lNo := 4;
910 lType := 5;
911 lNom1 := 6;
912 lNom2 := 7;
913 lLigne1 := 8;
914 lLigne2 := 9;
915 lVille := 10;
916 lProvince := 11;
917 lCodePostal := 12;
918 lNom := 13;
919 lIndRegional := 14;
920 lTel := 15;
921 lPosteTel := 16;
922 lLangue := 17;
923 lANom := 18;
924 lAIndRegional := 19;
925 lATel := 20;
926 lAPosteTel := 21;
927 lALangue := 22;
928 lNoConcepteur := 23;
929
930 l_Informatique_tag := 'N';
931 l_Comptabilite_tag := 'N';
932
933 l_taxation_year
934 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
935
936 /* commented for bug 9142143
937 l_payroll_actid
938 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');*/
939
940 select max(paa1.payroll_action_id)
941 into l_payroll_actid
942 from pay_assignment_actions paa,
943 pay_assignment_actions paa1,
944 pay_action_interlocks int
945 where int.locking_action_id = paa.assignment_action_id
946 AND paa1.assignment_action_id = int.locked_action_id
947 and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
948
949 get_report_parameters(
950 l_payroll_actid,
951 l_year_start,
952 l_year_end,
953 l_report_type,
954 l_business_grpid,
955 l_legislative_param
956 );
957
958
959 hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
960 hr_utility.trace('XML Transmitter: l_payroll_Action_id = ' || to_char(l_payroll_actid));
961
962 OPEN c_trans_info(l_business_grpid,
963 l_payroll_actid);
964 FETCH c_trans_info
965 INTO l_trans_number,
966 l_reporting_year,
967 l_trans_package_type,
968 l_trans_type_indicator,
969 l_trans_name,
970 l_source_of_slips,
971 l_trans_address_line1,
972 l_trans_address_line2,
973 l_trans_city,
974 l_trans_province,
975 l_trans_postal_code,
976 l_trans_tech_contact_name,
977 l_trans_tech_contact_code,
978 l_trans_tech_contact_phone,
979 l_trans_tech_contact_extn,
980 l_trans_tech_contact_lang,
981 l_trans_acct_contact_name,
982 l_trans_acct_contact_code,
983 l_trans_acct_contact_phone,
984 l_trans_acct_contact_extn,
985 l_trans_acct_contact_lang;
986
987 CLOSE c_trans_info;
988 -- Annee
989 tab_transmitter(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' ||EOL;
990 hr_utility.trace('tab_transmitter(lAnnee) = ' || tab_transmitter(lAnnee));
991
992 -- TypeEnvoi
993 OPEN c_rl_package_type;
994 FETCH c_rl_package_type INTO l_rl_package_type;
995 CLOSE c_rl_package_type;
996
997 tab_transmitter(lTypeEnvoi) := '<TypeEnvoi>' ||l_rl_package_type|| '</TypeEnvoi>' || EOL;
998 hr_utility.trace('tab_transmitter(lTypeEnvoi) = ' ||
999 tab_transmitter(lTypeEnvoi));
1000
1001 tab_transmitter(lProvenance) := '<Provenance>' ||
1002 convert_special_char(l_source_of_slips) || '</Provenance>' || EOL;
1003
1004 hr_utility.trace('tab_transmitter(lProvenance) = ' || tab_transmitter(lProvenance));
1005
1006 tab_transmitter(lNo) := '<No>' ||
1007 convert_special_char(l_trans_number) || '</No>' || EOL;
1008
1009 hr_utility.trace('tab_transmitter(lNo) = ' || tab_transmitter(lNo));
1010
1011
1012 IF l_trans_type_indicator IS NOT NULL AND
1013 l_trans_type_indicator <> '0' THEN
1014 tab_transmitter(lType) := '<Type>' ||
1015 convert_special_char(l_trans_type_indicator) || '</Type>' || EOL;
1016 ELSE
1017 tab_transmitter(lType) := NULL;
1018 END IF;
1019
1020 hr_utility.trace('tab_transmitter(lType) = ' || tab_transmitter(lType));
1021
1022 tab_transmitter(lNom1) := '<Nom1>' ||
1023 convert_special_char(substr(l_trans_name,1,30)) || '</Nom1>' || EOL;
1024
1025 hr_utility.trace('tab_transmitter(lNom1) = ' || tab_transmitter(lNom1));
1026
1027 l_return := substr(l_trans_name,31,30);
1028 IF l_return IS NOT NULL THEN
1029 tab_transmitter(lNom2) := '<Nom2>' || convert_special_char(l_return) || '</Nom2>' || EOL;
1030 ELSE
1031 tab_transmitter(lNom2) := NULL;
1032 END IF;
1033
1034 hr_utility.trace('tab_transmitter(lNom2) = ' || tab_transmitter(lNom2));
1035
1036
1037 tab_transmitter(lLigne1) := '<Ligne1>' ||
1038 convert_special_char(substr(l_trans_address_line1,1,30)) || '</Ligne1>' || EOL;
1039
1040 hr_utility.trace('tab_transmitter(lLigne1) = ' || tab_transmitter(lLigne1));
1041
1042
1043 IF (l_trans_address_line2 IS NOT NULL AND
1044 l_trans_address_line2 <> ' ') THEN
1045 tab_transmitter(lLigne2) := '<Ligne2>' ||
1046 convert_special_char(substr(l_trans_address_line2,1,30)) || '</Ligne2>' || EOL;
1047 ELSE
1048 tab_transmitter(lLigne2) := NULL;
1049 END IF;
1050
1051 hr_utility.trace('tab_transmitter(lLigne2) = ' || tab_transmitter(lLigne2));
1052
1053
1054 IF (l_trans_city IS NOT NULL AND
1055 l_trans_city <> ' ') THEN
1056 tab_transmitter(lVille) := '<Ville>' ||
1057 convert_special_char(substr(l_trans_city,1,30)) || '</Ville>' || EOL;
1058 ELSE
1059 tab_transmitter(lVille) := NULL;
1060 END IF;
1061
1062 hr_utility.trace('tab_transmitter(lVille) = ' || tab_transmitter(lVille));
1063
1064 IF (l_trans_province IS NOT NULL AND
1065 l_trans_province <> ' ') THEN
1066 tab_transmitter(lProvince) := '<Province>' ||
1067 convert_special_char(SUBSTR(hr_general.decode_lookup(
1068 'CA_PROVINCE',l_trans_province),1,20)) || '</Province>' || EOL;
1069 ELSE
1070 tab_transmitter(lProvince) := NULL;
1071 END IF;
1072 hr_utility.trace('tab_transmitter(lProvince) = ' || tab_transmitter(lProvince));
1073
1074 IF (l_trans_postal_code IS NOT NULL AND
1075 l_trans_postal_code <> ' ') THEN
1076 tab_transmitter(lCodePostal) := '<CodePostal>' ||
1077 convert_special_char(substr(l_trans_postal_code,1,6)) || '</CodePostal>' || EOL;
1078 ELSE
1079 tab_transmitter(lCodePostal) := NULL;
1080 END IF;
1081 hr_utility.trace('tab_transmitter(lCodePostal) = ' || tab_transmitter(lCodePostal));
1082
1083
1084 IF (l_trans_tech_contact_name IS NOT NULL AND
1085 l_trans_tech_contact_name <> ' ' ) THEN
1086 l_Informatique_tag := 'Y';
1087 tab_transmitter(lNom) := '<Nom>' ||
1088 convert_special_char(substr(l_trans_tech_contact_name,1,30)) || '</Nom>' || EOL;
1089 ELSE
1090 tab_transmitter(lNom) := NULL;
1091 END IF;
1092
1093 hr_utility.trace('tab_transmitter(lNom) = ' || tab_transmitter(lNom));
1094
1095
1096 IF (l_trans_tech_contact_code IS NOT NULL AND
1097 l_trans_tech_contact_code <> '000' ) THEN
1098 l_Informatique_tag := 'Y';
1099 tab_transmitter(lIndRegional) := '<IndRegional>' ||
1100 convert_special_char(l_trans_tech_contact_code) || '</IndRegional>' || EOL;
1101 ELSE
1102 tab_transmitter(lIndRegional) := NULL;
1103 END IF;
1104
1105 hr_utility.trace('tab_transmitter(lIndRegional) = ' || tab_transmitter(lIndRegional));
1106
1107 IF (l_trans_tech_contact_phone IS NOT NULL AND
1108 l_trans_tech_contact_phone <> '0000000' ) THEN
1109 l_Informatique_tag := 'Y';
1110 l_trans_tech_contact_phone := substr(l_trans_tech_contact_phone,1,3) || '-' || substr(l_trans_tech_contact_phone,4,4);
1111 tab_transmitter(lTel) := '<Tel>' || convert_special_char(l_trans_tech_contact_phone) || '</Tel>' || EOL;
1112 ELSE
1113 tab_transmitter(lTel) := NULL;
1114 END IF;
1115 hr_utility.trace('tab_transmitter(lTel) = ' || tab_transmitter(lTel));
1116
1117
1118 IF (l_trans_tech_contact_extn IS NOT NULL AND
1119 l_trans_tech_contact_extn <> '0000' ) THEN
1120 l_Informatique_tag := 'Y';
1121 tab_transmitter(lPosteTel) := '<PosteTel>' || convert_special_char(l_trans_tech_contact_extn) ||
1122 '</PosteTel>' || EOL;
1123 ELSE
1124 tab_transmitter(lPosteTel) := NULL;
1125 END IF;
1126 hr_utility.trace('tab_transmitter(lPosteTel) = ' ||
1127 tab_transmitter(lPosteTel));
1128
1129
1130 IF (l_trans_tech_contact_lang IS NOT NULL AND
1131 l_trans_tech_contact_lang <> ' ' ) THEN
1132 l_Informatique_tag := 'Y';
1133 tab_transmitter(lLangue) := '<Langue>' ||convert_special_char(l_trans_tech_contact_lang) || '</Langue>' || EOL;
1134 ELSE
1135 tab_transmitter(lLangue) := NULL;
1136 END IF;
1137
1138
1139 IF (l_trans_acct_contact_name IS NOT NULL AND
1140 l_trans_acct_contact_name <> ' ') THEN
1141 l_Comptabilite_tag := 'Y';
1142 tab_transmitter(lANom) := '<Nom>' ||
1143 convert_special_char(substr(l_trans_acct_contact_name,1,30)) || '</Nom>' || EOL;
1144 ELSE
1145 tab_transmitter(lANom) := NULL;
1146 END IF;
1147 hr_utility.trace('tab_transmitter(lANom) = ' || tab_transmitter(lANom));
1148
1149
1150 IF (l_trans_acct_contact_code IS NOT NULL AND
1151 l_trans_acct_contact_code <> '000' ) THEN
1152 l_Comptabilite_tag := 'Y';
1153 tab_transmitter(lAIndRegional) := '<IndRegional>' || convert_special_char(l_trans_acct_contact_code) ||
1154 '</IndRegional>' || EOL;
1155 ELSE
1156 tab_transmitter(lAIndRegional) := NULL;
1157 END IF;
1158 hr_utility.trace('tab_transmitter(lAIndRegional) = ' || tab_transmitter(lAIndRegional));
1159
1160
1161 IF (l_trans_acct_contact_phone IS NOT NULL AND
1162 l_trans_acct_contact_phone <> '0000000' ) THEN
1163 l_Comptabilite_tag := 'Y';
1164 l_trans_acct_contact_phone := substr(l_trans_acct_contact_phone,1,3) || '-' || substr(l_trans_acct_contact_phone,4,4);
1165 tab_transmitter(lATel) := '<Tel>' || convert_special_char(l_trans_acct_contact_phone) || '</Tel>' || EOL;
1166 ELSE
1167 tab_transmitter(lATel) := NULL;
1168 END IF;
1169 hr_utility.trace('tab_transmitter(lATel) = ' || tab_transmitter(lATel));
1170
1171
1172 IF (l_trans_acct_contact_extn IS NOT NULL AND
1173 l_trans_acct_contact_extn <> '0000') THEN
1174 l_Comptabilite_tag := 'Y';
1175 tab_transmitter(lAPosteTel) := '<PosteTel>' || convert_special_char(l_trans_acct_contact_extn) ||
1176 '</PosteTel>' || EOL;
1177 ELSE
1178 tab_transmitter(lAPosteTel) := NULL;
1179 END IF;
1180 hr_utility.trace('tab_transmitter(lAPosteTel) = ' ||
1181 tab_transmitter(lAPosteTel));
1182
1183 IF (l_trans_acct_contact_lang IS NOT NULL AND
1184 l_trans_acct_contact_lang <> ' ' ) THEN
1185 l_Comptabilite_tag := 'Y';
1186 tab_transmitter(lALangue) := '<Langue>' || convert_special_char(l_trans_acct_contact_lang) ||
1187 '</Langue>' || EOL;
1188 ELSE
1189 tab_transmitter(lALangue) := NULL;
1190 END IF;
1191
1192 --- Bug 6736354
1193 IF ( l_reporting_year = '2006' ) then
1194 l_authorization_no := 'RQ-06-02-048';
1195 ELSIF ( l_reporting_year = '2007' ) then
1196 l_authorization_no := 'RQ-07-02-069';
1197 ELSIF (l_reporting_year = '2008' ) then
1198 l_authorization_no := 'RQ-08-02-048';
1199 ELSIF (l_reporting_year = '2009' ) then
1200 l_authorization_no := 'RQ-09-02-019'; -- Bug 9206939
1201 ELSIF (l_reporting_year = '2010' ) then
1202 l_authorization_no := 'RQ-10-02-020'; -- Bug 10364973
1203 ELSIF (l_reporting_year = '2011' ) then
1204 l_authorization_no := 'RQ-11-02-035'; -- Bug 13486709
1205 ELSIF (l_reporting_year = '2012' ) then
1206 l_authorization_no := 'RQ-12-02-051'; -- Bug 15997013
1207 ELSE
1208 -- l_authorization_no := 'RQ-09-99-999';
1209 /* Modified for bug 10134138
1210 For sending testing file format for certification no will be
1211 RQ-nn-99-999 nn stands for last two digit of reporting year.
1212 */
1213 l_authorization_no := 'RQ-'||substr(trim(l_reporting_year),3,2)||'-99-999';
1214 END IF;
1215 --- End 6736354
1216
1217 tab_transmitter(lNoConcepteur) := '<NoCertification>'||convert_special_char(l_authorization_no)||'</NoCertification>'||EOL;
1218
1219 hr_utility.trace('tab_transmitter(lALangue) = ' || tab_transmitter(lALangue));
1220
1221 IF l_Informatique_tag = 'Y' AND
1222 l_Comptabilite_tag = 'Y' THEN
1223 l_tech_accnt_info := '<Informatique>' || EOL ||
1224 tab_transmitter(lNom) ||
1225 tab_transmitter(lIndRegional) ||
1226 tab_transmitter(lTel) ||
1227 tab_transmitter(lPosteTel) ||
1228 tab_transmitter(lLangue) || '</Informatique>' || EOL ||
1229 '<Comptabilite>' || EOL ||
1230 tab_transmitter(lANom) ||
1231 tab_transmitter(lAIndRegional) ||
1232 tab_transmitter(lATel) ||
1233 tab_transmitter(lAPosteTel) ||
1234 tab_transmitter(lALangue) || '</Comptabilite>' ;
1235 ELSIF l_Informatique_tag = 'Y' AND
1236 l_Comptabilite_tag = 'N' THEN
1237 l_tech_accnt_info := '<Informatique>' || EOL ||
1238 tab_transmitter(lNom) ||
1239 tab_transmitter(lIndRegional) ||
1240 tab_transmitter(lTel) ||
1241 tab_transmitter(lPosteTel) ||
1242 tab_transmitter(lLangue) || '</Informatique>';
1243 ELSIF l_Comptabilite_tag = 'Y' AND
1244 l_Informatique_tag = 'N' THEN
1245 l_tech_accnt_info := '<Comptabilite>' || EOL ||
1246 tab_transmitter(lANom) ||
1247 tab_transmitter(lAIndRegional) ||
1248 tab_transmitter(lATel) ||
1249 tab_transmitter(lAPosteTel) ||
1250 tab_transmitter(lALangue) || '</Comptabilite>';
1251 ELSE
1252 l_tech_accnt_info := NULL;
1253 END IF;
1254
1255 -- Bug 7602718
1256 if(l_reporting_year = '2006') then
1257 l_VersionSchema := '2006.1.2';
1258 elsif(l_reporting_year = '2007') then
1259 l_VersionSchema := '2007.1.1';
1260 else
1261 l_VersionSchema := trim(l_reporting_year)||'.1';
1262 end if;
1263 -- End
1264
1265 l_final_xml_string :=
1266 '<Transmission VersionSchema="'||l_VersionSchema||'" ' ||
1267 'pxmlns="http://www.mrq.gouv.qc.ca/T5">' || EOL ||
1268 '<P>' || EOL ||
1269 tab_transmitter(lAnnee) ||
1270 tab_transmitter(lTypeEnvoi) ||
1271 tab_transmitter(lProvenance) || '<Preparateur>' || EOL ||
1272 tab_transmitter(lNo) ||
1273 tab_transmitter(lType) ||
1274 tab_transmitter(lNom1) ||
1275 tab_transmitter(lNom2) || '<Adresse>' || EOL ||
1276 tab_transmitter(lLigne1) ||
1277 tab_transmitter(lLigne2) ||
1278 tab_transmitter(lVille) ||
1279 tab_transmitter(lProvince) ||
1280 tab_transmitter(lCodePostal) || '</Adresse>' || EOL ||
1281 '</Preparateur>' || EOL ||
1282 l_tech_accnt_info || EOL ||
1283 tab_transmitter(lNoConcepteur) ||
1284 '</P>' || EOL;
1285
1286 --hr_utility.trace('l_final_xml_string = ' || l_final_xml_string);
1287
1288 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1289 END;
1290 END xml_transmitter_record;
1291
1292
1293 PROCEDURE xml_employee_record IS
1294 BEGIN
1295
1296 DECLARE
1297
1298 l_final_xml_string VARCHAR2(32000);
1299 l_final_xml_string1 VARCHAR2(32000);
1300 l_final_xml_string2 VARCHAR2(32000);
1301 l_other_details VARCHAR2(32000); --For bug number 13622665
1302 /* Bug 14701748 start */
1303 l_final_xml_string_fur_inf VARCHAR2(32000);
1304 counter number;
1305 count1 number;
1306 counter1 number;
1307 slip_count number;
1308
1309 further_info_count number;
1310 further_info_details VARCHAR2(32000);
1311 TYPE FURTHER_CODES IS VARRAY(80) OF VARCHAR2(10);
1312 TYPE FURTHER_AMOUNTS IS VARRAY(80) OF VARCHAR2(50);
1313 l_further_code FURTHER_CODES:=FURTHER_CODES('','','','','','','','','','','','','',
1314 '','','','','','','');
1315 l_further_amount FURTHER_AMOUNTS:=FURTHER_AMOUNTS('','','','','','','','','','','','','',
1316 '','','','','','','');
1317
1318 l_k number;
1319 lv_org_slip_no varchar2(240);
1320 /* Bug 14701748 end */
1321
1322 CURSOR c_get_payroll_asg_actid(p_payactid NUMBER) IS
1323 SELECT
1324 to_number(substr(paa.serial_number,3,14)) payactid,
1325 to_number(substr(paa.serial_number,17,14)) asgactid,
1326 paa.assignment_id asgid
1327 FROM
1328 pay_assignment_actions paa
1329 WHERE paa.assignment_action_id = p_payactid;
1330
1331 CURSOR c_get_report_type(p_payactid NUMBER) IS
1332 SELECT
1333 ppa.report_type,
1334 ppa.business_group_id,
1335 ppa.legislative_parameters
1336 FROM
1337 pay_payroll_actions ppa
1338 WHERE
1339 ppa.payroll_action_id = p_payactid;
1340
1341 CURSOR c_get_employer_info(p_pact_id NUMBER,
1342 p_business_group_id NUMBER) IS
1343 SELECT nvl(employer_name,' '),
1344 nvl(quebec_business_number,'0000000000 0000'),
1345 nvl(reporting_year,'0000'),
1346 nvl(employer_add_line1,' '),
1347 nvl(employer_add_line2,' '),
1348 nvl(employer_add_line3,' '),
1349 nvl(employer_city,' '),
1350 nvl(employer_province,' '),
1351 nvl(employer_country,' '),
1352 nvl(employer_postal_code,' ')
1353 FROM
1354 pay_ca_eoy_rl2_trans_info_v
1355 WHERE
1356 business_group_id = p_business_group_id
1357 AND payroll_action_id = p_pact_id;
1358
1359 CURSOR cur_rl2_slip_no( p_person_id number,
1360 p_year varchar2,
1361 p_pre number) IS
1362 SELECT
1363 pei_information7
1364 FROM
1365 per_people_extra_info pei
1366 WHERE
1367 to_number(pei.person_id) = p_person_id AND
1368 pei.information_type = 'PAY_CA_RL2_FORM_NO' AND
1369 to_number(pei.pei_information6) = p_pre AND
1370 substr(pei.pei_information5,1,4) = p_year
1371 order by pei_information7; -- Bug 14701748
1372
1373 CURSOR cur_parameters(p_mag_asg_action_id NUMBER) IS
1374 SELECT
1375 pai_arch.locked_action_id,
1376 paa_mag.assignment_id,
1377 pay_magtape_generic.date_earned(ppa.effective_date,paf.assignment_id),
1378 pay_ca_rl2_can_mag.get_parameter('PRE_ORGANIZATION_ID',ppa_arch.legislative_parameters)
1379 FROM
1380 per_all_people_f ppf,
1381 per_all_assignments_f paf,
1382 pay_action_interlocks pai_mag,
1383 pay_action_interlocks pai_arch,
1384 pay_payroll_actions ppa,
1385 pay_payroll_actions ppa_arch,
1386 pay_assignment_actions paa_mag,
1387 pay_assignment_actions paa_arch,
1388 pay_action_information pact
1389 WHERE
1390 paa_mag.assignment_action_id = p_mag_asg_action_id AND
1391 ppa.payroll_action_id = paa_mag.payroll_action_id AND
1392 pai_mag.locking_action_id = paa_mag.assignment_action_id AND
1393 pai_mag.locked_action_id = pai_arch.locking_action_id AND
1394 pai_arch.locked_action_id = paa_arch.assignment_action_id AND
1395 paf.assignment_id = paa_arch.assignment_id AND
1396 ppf.person_id = paf.person_id AND
1397 pay_magtape_generic.date_earned(ppa.effective_date,paf.assignment_id)
1398 between
1399 paf.effective_start_date and paf.effective_end_date AND
1400 pay_magtape_generic.date_earned(ppa.effective_date,paf.assignment_id)
1401 between
1402 ppf.effective_start_date and ppf.effective_end_date AND
1403 pact.action_information_category = 'CAEOY RL2 EMPLOYEE INFO' AND
1404 paa_arch.assignment_action_id = pact.action_context_id AND
1405 pact.assignment_id = paa_mag.assignment_id AND
1406 ppa_arch.payroll_action_id = paa_arch.payroll_action_id
1407 ORDER BY
1408 ppf.last_name,ppf.first_name,ppf.middle_names;
1409
1410
1411 CURSOR c_employee_infor (p_asg_action_id IN number) IS
1412 SELECT
1413 nvl(tran.quebec_business_number,'0000000000 0000'),
1414 nvl(tran.reporting_year,'0000'),
1415 nvl(emp.rl2_slip_number,'000000000'),
1416 nvl(emp.employee_sin,'000000000'),
1417 nvl(emp.employee_last_name,' '),
1418 nvl(emp.employee_first_name,' '),
1419 nvl(emp.employee_middle_initial,' '),
1420 nvl(emp.employee_address_line1,' '),
1421 nvl(emp.employee_address_line2,' '),
1422 nvl(emp.employee_address_line3,' '),
1423 nvl(emp.employee_city,' '),
1424 nvl(emp.employee_province,' '),
1425 nvl(emp.employee_postal_code,' '),
1426 nvl(emp.employee_number,' '),
1427 emp.rl2_box_a,
1428 emp.rl2_box_b,
1429 emp.rl2_box_c,
1430 emp.rl2_box_d,
1431 emp.rl2_box_e,
1432 emp.rl2_box_f,
1433 emp.rl2_box_g,
1434 emp.rl2_box_h,
1435 emp.rl2_box_i,
1436 emp.rl2_box_j,
1437 emp.rl2_box_k,
1438 emp.rl2_box_l,
1439 emp.rl2_box_m,
1440 emp.rl2_box_n,
1441 emp.rl2_box_o,
1442 decode(substr(emp.rl2_source_of_income,1,5),'OTHER','AUTRE', emp.rl2_source_of_income),
1443 nvl(emp.negative_balance_flag, 'N'),
1444 emp.person_id
1445 FROM
1446 pay_ca_eoy_rl2_employee_info_v emp,
1447 pay_ca_eoy_rl2_trans_info_v tran
1448 WHERE
1449 emp.assignment_action_id = p_asg_action_id
1450 AND emp.payroll_action_id = tran.payroll_action_id;
1451 /* Commented for bug 8888411
1452 AND nvl(emp.rl2_source_of_income,1) not in --6525968
1453 (select lookup_code from hr_lookups hl, fnd_sessions fs
1454 where fs.session_id = USERENV('SESSIONID')
1455 and hl.lookup_type = 'PAY_CA_RL2_SOURCE_OF_INCOME'
1456 and (fs.effective_date > nvl(hl.end_date_active,to_date('31/12/4712','dd/mm/yyyy'))
1457 or hl.enabled_flag='N')
1458 ); --End 6525968
1459 */
1460
1461 /* Added for bug 8888411 */
1462 CURSOR c_rl2_src_income(p_rl2_source_of_income varchar2, p_taxation_year varchar2) is
1463 select
1464 'X'
1465 from
1466 hr_lookups hl
1467 where
1468 hl.lookup_type = 'PAY_CA_RL2_SOURCE_OF_INCOME'
1469 and trim(hl.lookup_code) = trim(p_rl2_source_of_income)
1470 and to_date('31/12/'||p_taxation_year,'dd/mm/yyyy')<= nvl(hl.end_date_active,to_date('31/12/4712','dd/mm/yyyy'))
1471 and hl.enabled_flag='Y';
1472
1473 l_quebec_business_number varchar2(240);
1474 l_reporting_year varchar2(240);
1475 l_rl2_slip_number varchar2(240);
1476 l_rl2_org_slip_number varchar2(240);
1477 l_employee_sin varchar2(240);
1478 l_employee_last_name varchar2(240);
1479 l_employee_first_name varchar2(240);
1480 l_employee_middle_initial varchar2(240);
1481 l_employee_address_line1 varchar2(240);
1482 l_employee_address_line2 varchar2(240);
1483 l_employee_address_line3 varchar2(240);
1484 l_employee_city varchar2(240);
1485 l_employee_province varchar2(240);
1486 l_employee_postal_code varchar2(240);
1487 l_employee_number varchar2(240);
1488 l_per_id varchar2(50);
1489 l_rl2_box_a varchar2(240);
1490 l_rl2_box_b varchar2(240);
1491 l_rl2_box_c varchar2(240);
1492 l_rl2_box_d varchar2(240);
1493 l_rl2_box_e varchar2(240);
1494 l_rl2_box_f varchar2(240);
1495 l_rl2_box_g varchar2(240);
1496 l_rl2_box_h varchar2(240);
1497 l_rl2_box_i varchar2(240);
1498 l_rl2_box_j varchar2(240);
1499 l_rl2_box_k varchar2(240);
1500 l_rl2_box_l varchar2(240);
1501 l_rl2_box_m varchar2(240);
1502 l_rl2_box_n varchar2(240);
1503 l_rl2_box_o varchar2(240);
1504 l_rl2_source_of_income varchar2(240);
1505 l_negative_balance_flag varchar2(240);
1506
1507 l_mag_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
1508 l_arch_action_id pay_assignment_actions.assignment_action_id%TYPE;
1509 l_asg_id per_assignments_f.assignment_id%TYPE;
1510 l_date_earned DATE;
1511 l_province VARCHAR2(30);
1512
1513 TYPE employee_info IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
1514
1515 tab_employee employee_info;
1516
1517 lAnnee NUMBER;
1518 lNoReleve NUMBER;
1519 lNAS NUMBER;
1520 lNAS1 NUMBER;
1521 lNAS2 NUMBER;
1522 lNAS3 NUMBER;
1523 lNo NUMBER;
1524 lNomFamille NUMBER;
1525 lPrenom NUMBER;
1526 lInitiale NUMBER;
1527 lLigne1 NUMBER;
1528 lLigne2 NUMBER;
1529 lVille NUMBER;
1530 lProvince NUMBER;
1531 lCodePostal NUMBER;
1532 lA_PrestRPA_RPNA NUMBER;
1533 lB_PrestREER_FERR_RPDB NUMBER;
1534 lC_AutrePaiement NUMBER;
1535 lD_RembPrimeConjoint NUMBER;
1536 lE_PrestDeces NUMBER;
1537 lF_RembCotisInutilise NUMBER;
1538 lG_RevocationREER_FERR NUMBER;
1539 lH_AutreRevenu NUMBER;
1540 lI_DroitDeduction NUMBER;
1541 lJ_ImpotQueRetenuSource NUMBER;
1542 lK_RevenuApresDeces NUMBER;
1543 lL_RetraitREEP NUMBER;
1544 lM_LibereImpot NUMBER;
1545 lN_NASConjoint NUMBER;
1546 lN_NASConjoint1 NUMBER;
1547 lN_NASConjoint2 NUMBER;
1548 lO_RetraitRAP NUMBER;
1549 lProvenance1 VARCHAR2(10);
1550 lBoxA_UnregisterdPlan NUMBER;
1551 lDesg_BenefitExcAmt NUMBER;
1552 lBoxB_DesgBenefitTrnsAmt NUMBER;
1553 lBoxExcessAmt NUMBER;
1554 lAmount_Transferred NUMBER;
1555 lBoxC_SinglePayAccured NUMBER;
1556 lBoxC_SinglePayAccUnreg NUMBER;
1557 lBoxC_ExcessAmtSinPayTrans NUMBER;
1558 lCode_dereleve NUMBER;
1559
1560 l_person_id per_people_f.person_id%TYPE;
1561 l_address_line1 per_addresses.address_line1%TYPE;
1562 l_address_line2 per_addresses.address_line2%TYPE;
1563 l_address_line3 per_addresses.address_line3%TYPE;
1564 l_city per_addresses.town_or_city%TYPE;
1565 l_postal_code per_addresses.postal_code%TYPE;
1566 l_country VARCHAR2(60);
1567 l_emp_province per_addresses.region_1%TYPE;
1568 EOL VARCHAR2(5);
1569 l_taxation_year VARCHAR2(5);
1570 l_name VARCHAR2(60);
1571 l_return VARCHAR2(30);
1572 l_status VARCHAR2(10);
1573 l_addr_begin_tag VARCHAR2(10);
1574 l_addr_end_tag VARCHAR2(10);
1575 l_formatted_box VARCHAR2(20);
1576 l_formatted_code VARCHAR2(20); --For bug number 13622665
1577 l_boxO VARCHAR2(10);
1578 l_combined_addr VARCHAR2(500);
1579
1580 l_count NUMBER;
1581 lBoxR_14 NUMBER;
1582 lErrorDetails NUMBER;
1583 l_pre_id NUMBER;
1584
1585 CURSOR cur_get_meaning(p_lookup_code VARCHAR2) IS
1586 SELECT
1587 meaning
1588 FROM
1589 hr_lookups
1590 WHERE
1591 lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
1592 lookup_code = p_lookup_code;
1593
1594 l_meaning hr_lookups.meaning%TYPE;
1595 l_msg_code VARCHAR2(30);
1596 l_all_box_0 BOOLEAN;
1597
1598 /* Cursor for fetching the Footnote Codes */
1599 CURSOR c_footnote_codes ( p_assg_actid number) is
1600 SELECT hl.meaning code, fnd_number.canonical_to_number(FT.FOOTNOTE_AMOUNT) value
1601 FROM PAY_CA_EOY_RL2_FOOTNOTE_INFO_V FT,
1602 HR_LOOKUPS HL
1603 WHERE FT.ASSIGNMENT_ACTION_ID = p_assg_actid
1604 AND ((HL.LOOKUP_TYPE = 'PAY_CA_RL2_FOOTNOTES'
1605 AND HL.lookup_code = FT.FOOTNOTE_CODE)
1606 OR
1607 (HL.LOOKUP_TYPE = 'PAY_CA_RL2_AUTOMATIC_FOOTNOTES'
1608 AND HL.LOOKUP_CODE = FT.FOOTNOTE_CODE));
1609
1610
1611 /* Cursor for fetching Further information - For bug number 13622665 */
1612 CURSOR c_further_codes ( p_assg_actid number) is
1613 SELECT decode(FT.FURTHER_INFO_CODE,'RL2_FURTHER_INFO_AMOUNT_201','201'
1614 ,'RL2_FURTHER_INFO_AMOUNT_235','235'
1615 ,'RL2_FURTHER_INFO_AMOUNT_210','210' -- Bug 14701748
1616 ,substr(substr(FT.FURTHER_INFO_CODE,25),1,1)||'-'||substr(substr(FT.FURTHER_INFO_CODE,25),2)) code,
1617 FT.FURTHER_INFO_VALUE value
1618 FROM PAY_CA_EOY_RL2_FURTHER_INFO_V FT
1619 WHERE FT.ASSIGNMENT_ACTION_ID = p_assg_actid
1620 ORDER BY code;
1621
1622 l_footnote_code VARCHAR2(100);
1623 l_footnote_amount NUMBER;
1624 l_format_mask VARCHAR2(30);
1625
1626 l_rep_type pay_report_format_mappings_f.report_type%type;
1627 l_rl2pap_asg_actid NUMBER;
1628 l_rl2pap_pay_actid NUMBER;
1629 l_transfer_pay_actid NUMBER;
1630 l_business_group_id NUMBER;
1631
1632 TYPE employer_inf IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
1633 tab_emplyr employer_inf;
1634
1635 l_page_break VARCHAR2(100);
1636 l_full_empname VARCHAR2(100);
1637 l_full_empaddr VARCHAR2(100);
1638 l_empr_name VARCHAR2(240);
1639 l_empr_quebec_no VARCHAR2(240);
1640 l_empr_report_yr VARCHAR2(240);
1641 /* l_empr_addr1 VARCHAR2(240);
1642 l_empr_addr2 VARCHAR2(240);
1643 l_empr_addr3 VARCHAR2(240); */
1644 l_empr_city VARCHAR2(240);
1645 l_empr_prov VARCHAR2(240);
1646 l_empr_postcode VARCHAR2(240);
1647 l_empr_country VARCHAR2(240);
1648 l_empr_fulladdr VARCHAR2(240);
1649 l_negative_box VARCHAR2(1);
1650 l_footnote_count NUMBER;
1651 l_footcode VARCHAR2(100);
1652 l_footamt NUMBER;
1653 l_footnotecode NUMBER;
1654 l_footnoteamt NUMBER;
1655 l_legislative_parameters pay_payroll_actions.legislative_parameters%type;
1656
1657 BEGIN
1658
1659 hr_utility.trace('XML Employee');
1660 l_status := 'Success';
1661 l_all_box_0 := TRUE;
1662 l_count := 0;
1663 l_format_mask := '99999999999999990.99';
1664 l_negative_box := 'N';
1665 l_footnote_count := 0;
1666 SELECT
1667 fnd_global.local_chr(13) || fnd_global.local_chr(10)
1668 INTO EOL
1669 FROM dual;
1670
1671 lAnnee := 1;
1672 lNoReleve := 2;
1673 lNAS := 3;
1674 lNo := 4;
1675 lNomFamille := 5;
1676 lPrenom := 6;
1677 lInitiale := 7;
1678 lLigne1 := 8;
1679 lLigne2 := 9;
1680 lVille := 10;
1681 lProvince := 11;
1682 lCodePostal := 12;
1683 lA_PrestRPA_RPNA := 13;
1684 lB_PrestREER_FERR_RPDB := 14;
1685 lC_AutrePaiement := 15;
1686 lD_RembPrimeConjoint := 16;
1687 lE_PrestDeces := 17;
1688 lF_RembCotisInutilise := 18;
1689 lG_RevocationREER_FERR := 19;
1690 lH_AutreRevenu := 20;
1691 lI_DroitDeduction := 21;
1692 lJ_ImpotQueRetenuSource := 22;
1693 lK_RevenuApresDeces := 23;
1694 lL_RetraitREEP := 24;
1695 lM_LibereImpot := 25;
1696 lN_NASConjoint := 26;
1697 lO_RetraitRAP := 27;
1698 lProvenance1 := 28;
1699 lErrorDetails := 29;
1700 lBoxA_UnregisterdPlan := 30;
1701 lDesg_BenefitExcAmt := 31;
1702 lBoxB_DesgBenefitTrnsAmt := 32;
1703 lBoxExcessAmt := 33;
1704 lAmount_Transferred := 34;
1705 lBoxC_SinglePayAccured := 35;
1706 lBoxC_SinglePayAccUnreg := 36;
1707 lBoxC_ExcessAmtSinPayTrans := 37;
1708 l_footnotecode := 38;
1709 l_footnoteamt := 39;
1710 lNAS1 := 40;
1711 lNAS2 := 41;
1712 lNAS3 := 42;
1713 lN_NASConjoint1 := 43;
1714 lN_NASConjoint2 := 44;
1715 lCode_dereleve := 45;
1716
1717 l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID'));
1718 l_transfer_pay_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1719
1720 open c_get_report_type(l_transfer_pay_actid);
1721 fetch c_get_report_type
1722 into l_rep_type,
1723 l_business_group_id,
1724 l_legislative_parameters;
1725 close c_get_report_type;
1726
1727 hr_utility.trace('XML Employee: l_mag_asg_action_id = '
1728 || to_char(l_mag_asg_action_id));
1729 hr_utility.trace('XML Employee: Transfer Payroll Action Id '||to_number(pay_magtape_generic.get_parameter_value
1730 ('TRANSFER_PAYROLL_ACTION_ID')));
1731
1732 OPEN cur_parameters(l_mag_asg_action_id);
1733 FETCH cur_parameters
1734 INTO
1735 l_arch_action_id,
1736 l_asg_id,
1737 l_date_earned,
1738 l_pre_id;
1739 CLOSE cur_parameters;
1740
1741 hr_utility.trace('XML Employee: l_arch_action_id = '|| to_char(l_arch_action_id));
1742 hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1743 hr_utility.trace('XML Employee: l_date_earned = '|| to_char(l_date_earned));
1744 hr_utility.trace('XML Employee: l_province = '|| l_province);
1745
1746 l_taxation_year := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
1747
1748 tab_employee(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' || EOL;
1749
1750 open c_employee_infor(l_arch_action_id);
1751 fetch c_employee_infor
1752 into l_quebec_business_number,
1753 l_reporting_year,
1754 l_rl2_slip_number,
1755 l_employee_sin,
1756 l_employee_last_name,
1757 l_employee_first_name,
1758 l_employee_middle_initial,
1759 l_employee_address_line1,
1760 l_employee_address_line2,
1761 l_employee_address_line3,
1762 l_employee_city,
1763 l_employee_province,
1764 l_employee_postal_code,
1765 l_employee_number,
1766 l_rl2_box_a,
1767 l_rl2_box_b,
1768 l_rl2_box_c,
1769 l_rl2_box_d,
1770 l_rl2_box_e,
1771 l_rl2_box_f,
1772 l_rl2_box_g,
1773 l_rl2_box_h,
1774 l_rl2_box_i,
1775 l_rl2_box_j,
1776 l_rl2_box_k,
1777 l_rl2_box_l,
1778 l_rl2_box_m,
1779 l_rl2_box_n,
1780 l_rl2_box_o,
1781 l_rl2_source_of_income,
1782 l_negative_balance_flag,
1783 l_per_id;
1784
1785 CLOSE c_employee_infor;
1786
1787 --NoReleve
1788 /* OPEN cur_rl2_slip_no(to_number(l_per_id), l_taxation_year, l_pre_id);
1789 FETCH cur_rl2_slip_no into l_rl2_org_slip_number;
1790 CLOSE cur_rl2_slip_no; */
1791
1792 hr_utility.trace('l_person_id = '||l_person_id);
1793 hr_utility.trace('l_taxation_year = '||l_taxation_year);
1794 hr_utility.trace('l_pre_id = '||l_pre_id);
1795
1796 l_k := 0;
1797 lv_org_slip_no := NULL;
1798 OPEN cur_rl2_slip_no(to_number(l_per_id), l_taxation_year, l_pre_id);
1799 loop
1800 FETCH cur_rl2_slip_no into l_rl2_org_slip_number;
1801 exit when cur_rl2_slip_no%NOTFOUND;
1802 if l_k = 0 then
1803 l_k := 1;
1804 lv_org_slip_no := l_rl2_org_slip_number;
1805 else
1806 lv_org_slip_no := lv_org_slip_no || '|' || l_rl2_org_slip_number;
1807 end if;
1808 end loop;
1809 CLOSE cur_rl2_slip_no;
1810
1811 hr_utility.trace('lv_org_slip_no = '||lv_org_slip_no);
1812
1813 IF ( lv_org_slip_no IS NULL) THEN
1814 l_status := 'Failed';
1815 l_msg_code := 'MISSING_SLIP_NO';
1816 tab_employee(lNoReleve) := NULL;
1817 ELSE
1818 l_rl2_org_slip_number := pay_ca_rl2_mag.get_slip_seq_no('|',lv_org_slip_no,1);
1819 tab_employee(lNoReleve) := '<NoReleve>' || convert_special_char(l_rl2_org_slip_number) ||
1820 '</NoReleve>' || EOL;
1821 END IF;
1822 hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve));
1823
1824 -- NAS
1825 /* Bug Fix 4754891 */
1826 IF (l_employee_sin IS NOT NULL AND
1827 l_employee_sin <> '000000000') THEN
1828 tab_employee(lNAS) := '<NAS>' || convert_special_char(l_employee_sin) || '</NAS>' || EOL;
1829 ELSE
1830 l_status := 'Failed';
1831 l_msg_code := 'SIN';
1832 tab_employee(lNAS) := NULL;
1833 END IF;
1834 hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
1835
1836 -- No
1837 IF (l_employee_number IS NOT NULL AND
1838 l_employee_number <> ' ' ) THEN
1839 tab_employee(lNo) := '<No>' || convert_special_char(l_employee_number) || '</No>' || EOL;
1840 ELSE
1841 tab_employee(lNo) := NULL;
1842 END IF;
1843 hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
1844
1845 -- NomFamille
1846 tab_employee(lNomFamille) := '<NomFamille>' ||
1847 convert_special_char(substr(l_employee_last_name,1,30)) || '</NomFamille>' || EOL;
1848 hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
1849
1850 l_full_empname := convert_special_char(substr(l_employee_last_name,1,30));
1851
1852 -- Prenom
1853 IF (l_employee_first_name is NOT NULL AND
1854 l_employee_first_name <> ' ') THEN
1855 tab_employee(lPrenom) := '<Prenom>' || convert_special_char(substr(l_employee_first_name,1,30))
1856 || '</Prenom>' || EOL;
1857 l_full_empname := l_full_empname ||','||convert_special_char(substr(l_employee_first_name,1,30));
1858 ELSE
1859 l_msg_code := 'MISSING_EMP_FIRST_NAME';
1860 l_status := 'Failed';
1861 tab_employee(lPrenom) := NULL;
1862 END IF;
1863 hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
1864
1865 -- Initiale
1866 IF (l_employee_middle_initial is NOT NULL AND
1867 l_employee_middle_initial <> ' ') THEN
1868 tab_employee(lInitiale) := '<Initiale>' || convert_special_char(substr(l_employee_middle_initial,1,1))
1869 || '</Initiale>' || EOL;
1870 l_full_empname := l_full_empname ||' '||convert_special_char(substr(l_employee_middle_initial,1,1));
1871 ELSE
1872 tab_employee(lInitiale) := NULL;
1873 END IF;
1874
1875 hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
1876
1877 l_person_id := to_number(l_per_id);
1878
1879 l_return := pay_ca_emp_address_dtls.get_emp_address(
1880 l_person_id,
1881 l_address_line1,
1882 l_address_line2,
1883 l_address_line3,
1884 l_city,
1885 l_postal_code,
1886 l_country,
1887 l_emp_province
1888 );
1889 -- If Address line 1 is NULL or ' ' then the employee is missing
1890 -- address information - as line 1 is mandatory in the Address form.
1891 -- Need to check data by SS transaction /API.
1892
1893 hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1894 hr_utility.trace('l_address_line1 = ' || l_address_line1);
1895 hr_utility.trace('l_address_line2 = ' || l_address_line2);
1896 hr_utility.trace('l_postal_code = ' || l_postal_code);
1897
1898 /* Bug Fix 4761782 */
1899 -- Address Line 1
1900 IF l_address_line1 IS NOT NULL AND
1901 l_address_line1 = ' ' THEN
1902
1903 l_status := 'Failed';
1904 l_msg_code := 'MISSING_EMP_ADDRESS';
1905
1906 l_addr_begin_tag := NULL;
1907 tab_employee(lLigne1) := NULL;
1908 tab_employee(lLigne2) := NULL;
1909 tab_employee(lVille) := NULL;
1910 tab_employee(lProvince) := NULL;
1911 tab_employee(lCodePostal) := NULL;
1912 l_addr_end_tag := NULL;
1913 ELSE
1914
1915 l_addr_begin_tag := '<Adresse>';
1916
1917 tab_employee(lLigne1) := '<Ligne1>' ||
1918 convert_special_char(substr(l_address_line1,1,30)) || '</Ligne1>' || EOL;
1919 hr_utility.trace('tab_employee(lLigne1) = ' || tab_employee(lLigne1));
1920
1921 -- Address Line 2
1922 IF ((l_address_line2 IS NOT NULL AND
1923 l_address_line2 <> ' ' ) OR
1924 (l_address_line3 IS NOT NULL AND
1925 l_address_line3 <> ' ') ) THEN
1926 l_combined_addr := rtrim(ltrim(l_address_line2)) || rtrim(ltrim(l_address_line3));
1927 tab_employee(lLigne2) := '<Ligne2>' ||
1928 convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne2>' || EOL;
1929 ELSE
1930 tab_employee(lLigne2) := NULL;
1931 END IF;
1932 hr_utility.trace('tab_employee(lLigne2) = ' || tab_employee(lLigne2));
1933
1934 -- Ville (City)
1935 IF l_city IS NOT NULL AND
1936 l_city <> ' ' THEN
1937 tab_employee(lVille) := '<Ville>' ||
1938 convert_special_char(substr(l_city,1,30)) || '</Ville>' || EOL;
1939 l_full_empaddr := convert_special_char(substr(l_city,1,30));
1940 ELSE
1941 tab_employee(lVille) := NULL;
1942 END IF;
1943 hr_utility.trace('tab_employee(lVille) = ' || tab_employee(lVille));
1944
1945 -- Province
1946 IF l_emp_province IS NOT NULL AND
1947 l_emp_province <> ' ' THEN
1948
1949 IF l_country = 'CA' THEN
1950 tab_employee(lProvince) := '<Province>' ||
1951 convert_special_char(SUBSTR(hr_general.decode_lookup(
1952 'CA_PROVINCE',l_emp_province),1,20)) || '</Province>' || EOL;
1953 l_full_empaddr := l_full_empaddr ||' '||convert_special_char(l_emp_province);
1954
1955
1956 ELSIF l_country = 'US' THEN
1957 tab_employee(lProvince) := '<Province>' ||l_emp_province || '</Province>' || EOL;
1958 l_full_empaddr := l_full_empaddr ||' '||l_emp_province;
1959
1960 ELSE
1961 tab_employee(lProvince) := '<Province>' ||l_country|| '</Province>' || EOL;
1962 l_full_empaddr := l_full_empaddr ||' '||l_country;
1963
1964 END IF;
1965 ELSE
1966 tab_employee(lProvince) := NULL;
1967
1968 END IF;
1969 hr_utility.trace('tab_employee(lProvince) = ' || tab_employee(lProvince));
1970
1971 -- Bug# 4754743 fix
1972 -- Postal Code
1973 IF l_postal_code IS NOT NULL AND
1974 l_postal_code <> ' ' THEN
1975 tab_employee(lCodePostal) := '<CodePostal>' ||
1976 convert_special_char(substr(replace(l_postal_code,' '),1,6)) || '</CodePostal>' || EOL;
1977 l_full_empaddr := l_full_empaddr ||' '||convert_special_char(substr(replace(l_country,' '),1,6))||' '||
1978 convert_special_char(l_postal_code);
1979 ELSE
1980 tab_employee(lCodePostal) := NULL;
1981 END IF;
1982
1983 hr_utility.trace('tab_employee(lCodePostal) = ' || tab_employee(lCodePostal));
1984 l_addr_end_tag := '</Adresse>';
1985
1986 END IF;
1987
1988 -- Summ (Box A)
1989 hr_utility.trace('The Value of Box A is '|| l_rl2_box_a);
1990 IF TO_NUMBER(l_rl2_box_a) > 9999999.99 THEN
1991 l_status := 'Failed';
1992 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1993 END IF;
1994
1995 IF l_rl2_box_a IS NOT NULL AND
1996 to_number(l_rl2_box_a) <> 0 THEN
1997
1998 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_a),l_format_mask)))
1999 INTO l_formatted_box
2000 FROM dual;
2001 -- <A_PrestRPA_RPNA> changed to <A_PrestRPA> for bug 13083624
2002 tab_employee(lA_PrestRPA_RPNA) := '<A_PrestRPA>' || l_formatted_box ||
2003 '</A_PrestRPA>' || EOL;
2004 l_all_box_0 := FALSE;
2005 ELSE
2006 tab_employee(lA_PrestRPA_RPNA ) := NULL;
2007 END IF;
2008 hr_utility.trace('tab_employee(lA_PrestRPA_RPNA) = ' ||
2009 tab_employee(lA_PrestRPA_RPNA));
2010
2011 -- Summ (Box B)
2012 IF TO_NUMBER(l_rl2_box_b) > 9999999.99 THEN
2013 l_status := 'Failed';
2014 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2015 END IF;
2016
2017 IF l_rl2_box_b IS NOT NULL AND
2018 to_number(l_rl2_box_b) <> 0 THEN
2019
2020 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_b),l_format_mask)))
2021 INTO l_formatted_box
2022 FROM dual;
2023
2024 tab_employee(lB_PrestREER_FERR_RPDB) := '<B_PrestREER_FERR_RPDB>' || l_formatted_box
2025 || '</B_PrestREER_FERR_RPDB>' || EOL;
2026 l_all_box_0 := FALSE;
2027 ELSE
2028 tab_employee(lB_PrestREER_FERR_RPDB) := NULL;
2029 END IF;
2030 hr_utility.trace('tab_employee(lB_PrestREER_FERR_RPDB) = ' ||
2031 tab_employee(lB_PrestREER_FERR_RPDB));
2032
2033 -- Summ (Box C)
2034 IF TO_NUMBER(l_rl2_box_c) > 9999999.99 THEN
2035 l_status := 'Failed';
2036 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2037 END IF;
2038
2039 IF l_rl2_box_c IS NOT NULL AND
2040 to_number(l_rl2_box_c) <> 0 THEN
2041
2042 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_c),l_format_mask)))
2043 INTO l_formatted_box
2044 FROM dual;
2045
2046 tab_employee(lC_AutrePaiement ) := '<C_AutrePaiement>' ||
2047 l_formatted_box || '</C_AutrePaiement>' || EOL;
2048 l_all_box_0 := FALSE;
2049 ELSE
2050 tab_employee(lC_AutrePaiement ) := NULL;
2051 END IF;
2052
2053 hr_utility.trace('tab_employee(lC_AutrePaiement ) = ' ||
2054 tab_employee(lC_AutrePaiement ));
2055
2056 -- Summ (Box D)
2057 IF TO_NUMBER(l_rl2_box_d) > 9999999.99 THEN
2058 l_status := 'Failed';
2059 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2060 END IF;
2061
2062 IF l_rl2_box_d IS NOT NULL AND
2063 to_number(l_rl2_box_d) <> 0 THEN
2064
2065 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_d),l_format_mask)))
2066 INTO l_formatted_box
2067 FROM dual;
2068
2069 tab_employee(lD_RembPrimeConjoint) := '<D_RembPrimeConjoint>' ||
2070 l_formatted_box || '</D_RembPrimeConjoint>' || EOL;
2071 l_all_box_0 := FALSE;
2072 ELSE
2073 tab_employee(lD_RembPrimeConjoint) := NULL;
2074 END IF;
2075 hr_utility.trace('tab_employee(lD_RembPrimeConjoint) = ' ||
2076 tab_employee(lD_RembPrimeConjoint));
2077
2078 -- (Box E)
2079 IF TO_NUMBER(l_rl2_box_e) > 9999999.99 THEN
2080 l_status := 'Failed';
2081 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2082 END IF;
2083
2084 IF l_rl2_box_e IS NOT NULL AND
2085 to_number(l_rl2_box_e) <> 0 THEN
2086
2087 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_e),l_format_mask)))
2088 INTO l_formatted_box
2089 FROM dual;
2090
2091 tab_employee(lE_PrestDeces) := '<E_PrestDeces>' ||
2092 l_formatted_box || '</E_PrestDeces>' || EOL;
2093 l_all_box_0 := FALSE;
2094 ELSE
2095 tab_employee(lE_PrestDeces) := NULL;
2096 END IF;
2097
2098 hr_utility.trace('tab_employee(lE_PrestDeces) = ' ||
2099 tab_employee(lE_PrestDeces));
2100
2101 -- (Box F)
2102 IF TO_NUMBER(l_rl2_box_f) > 9999999.99 THEN
2103 l_status := 'Failed';
2104 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2105 END IF;
2106
2107 IF l_rl2_box_f IS NOT NULL AND
2108 to_number(l_rl2_box_f) <> 0 THEN
2109
2110 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_f),l_format_mask)))
2111 INTO l_formatted_box
2112 FROM dual;
2113
2114 tab_employee(lF_RembCotisInutilise) := '<F_RembCotisInutilise>' ||
2115 l_formatted_box || '</F_RembCotisInutilise>' || EOL;
2116 l_all_box_0 := FALSE;
2117 ELSE
2118 tab_employee(lF_RembCotisInutilise) := NULL;
2119 END IF;
2120 hr_utility.trace('tab_employee(lF_RembCotisInutilise) = ' ||
2121 tab_employee(lF_RembCotisInutilise));
2122
2123 -- (Box G)
2124 IF TO_NUMBER(l_rl2_box_g) > 9999999.99 THEN
2125 l_status := 'Failed';
2126 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2127 END IF;
2128
2129 IF l_rl2_box_g IS NOT NULL AND
2130 to_number(l_rl2_box_g) <> 0 THEN
2131
2132 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_g),l_format_mask)))
2133 INTO l_formatted_box
2134 FROM dual;
2135
2136 tab_employee(lG_RevocationREER_FERR ) := '<G_RevocationREER_FERR>' ||
2137 l_formatted_box || '</G_RevocationREER_FERR>' || EOL;
2138 l_all_box_0 := FALSE;
2139 ELSE
2140 tab_employee(lG_RevocationREER_FERR) := NULL;
2141
2142 END IF;
2143 hr_utility.trace('tab_employee(lG_RevocationREER_FERR) = ' ||
2144 tab_employee(lG_RevocationREER_FERR));
2145
2146 -- (Box H)
2147 IF TO_NUMBER(l_rl2_box_h) > 9999999.99 THEN
2148 l_status := 'Failed';
2149 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2150 END IF;
2151
2152 IF l_rl2_box_h IS NOT NULL AND
2153 to_number(l_rl2_box_h) <> 0 THEN
2154
2155 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_h),l_format_mask)))
2156 INTO l_formatted_box
2157 FROM dual;
2158
2159 tab_employee(lH_AutreRevenu) := '<H_AutreRevenu>' ||
2160 l_formatted_box || '</H_AutreRevenu>' || EOL;
2161 l_all_box_0 := FALSE;
2162 ELSE
2163 tab_employee(lH_AutreRevenu) := NULL;
2164 END IF;
2165
2166 hr_utility.trace('tab_employee(lH_AutreRevenu ) = ' ||
2167 tab_employee(lH_AutreRevenu ));
2168
2169 -- (Box I)
2170 IF TO_NUMBER(l_rl2_box_i) > 9999999.99 THEN
2171 l_status := 'Failed';
2172 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2173 END IF;
2174
2175 IF l_rl2_box_i IS NOT NULL AND
2176 to_number(l_rl2_box_i) <> 0 THEN
2177
2178 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_i),l_format_mask)))
2179 INTO l_formatted_box
2180 FROM dual;
2181
2182 tab_employee(lI_DroitDeduction ) := '<I_DroitDeduction>' ||
2183 l_formatted_box || '</I_DroitDeduction>' || EOL;
2184 l_all_box_0 := FALSE;
2185 ELSE
2186 tab_employee(lI_DroitDeduction ) := NULL;
2187 END IF;
2188 hr_utility.trace('tab_employee(lI_DroitDeduction ) = ' ||
2189 tab_employee(lI_DroitDeduction ));
2190
2191 -- (Box J)
2192 IF TO_NUMBER(l_rl2_box_j) > 9999999.99 THEN
2193 l_status := 'Failed';
2194 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2195 END IF;
2196
2197 IF l_rl2_box_j IS NOT NULL AND
2198 to_number(l_rl2_box_j) <> 0 THEN
2199
2200 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_j),l_format_mask)))
2201 INTO l_formatted_box
2202 FROM dual;
2203
2204 tab_employee(lJ_ImpotQueRetenuSource ) := '<J_ImpotQueRetenuSource>' ||
2205 l_formatted_box || '</J_ImpotQueRetenuSource>' || EOL;
2206 l_all_box_0 := FALSE;
2207 ELSE
2208 tab_employee(lJ_ImpotQueRetenuSource ) := NULL;
2209 END IF;
2210 hr_utility.trace('tab_employee(lJ_ImpotQueRetenuSource ) = ' ||
2211 tab_employee(lJ_ImpotQueRetenuSource ));
2212
2213 -- (Box K)
2214 IF TO_NUMBER(l_rl2_box_k) > 9999999.99 THEN
2215 l_status := 'Failed';
2216 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2217 END IF;
2218
2219 IF l_rl2_box_k IS NOT NULL AND
2220 to_number(l_rl2_box_k) <> 0 THEN
2221
2222 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_k),l_format_mask)))
2223 INTO l_formatted_box
2224 FROM dual;
2225
2226 tab_employee(lK_RevenuApresDeces ) := '<K_RevenuApresDeces>' ||
2227 l_formatted_box || '</K_RevenuApresDeces>' || EOL;
2228 l_all_box_0 := FALSE;
2229 ELSE
2230 tab_employee(lK_RevenuApresDeces ) := NULL;
2231 END IF;
2232 hr_utility.trace('tab_employee(lK_RevenuApresDeces ) = ' ||
2233 tab_employee(lK_RevenuApresDeces ));
2234
2235 -- (Box L)
2236 IF TO_NUMBER(l_rl2_box_l) > 9999999.99 THEN
2237 l_status := 'Failed';
2238 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2239 END IF;
2240
2241 IF l_rl2_box_l IS NOT NULL AND
2242 to_number(l_rl2_box_l) <> 0 THEN
2243
2244 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_l),l_format_mask)))
2245 INTO l_formatted_box
2246 FROM dual;
2247
2248 tab_employee(lL_RetraitREEP ) := '<L_RetraitREEP>' ||
2249 l_formatted_box || '</L_RetraitREEP>' || EOL;
2250 l_all_box_0 := FALSE;
2251 ELSE
2252 tab_employee(lL_RetraitREEP ) := NULL;
2253 END IF;
2254 hr_utility.trace('tab_employee(lL_RetraitREEP ) = ' ||
2255 tab_employee(lL_RetraitREEP ));
2256
2257 -- (Box M)
2258 IF TO_NUMBER(l_rl2_box_m) > 9999999.99 THEN
2259 l_status := 'Failed';
2260 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2261 END IF;
2262
2263 IF l_rl2_box_m IS NOT NULL AND
2264 to_number(l_rl2_box_m) <> 0 THEN
2265
2266 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_m),l_format_mask)))
2267 INTO l_formatted_box
2268 FROM dual;
2269
2270 tab_employee(lM_LibereImpot) := '<M_LibereImpot>' ||
2271 l_formatted_box || '</M_LibereImpot>' || EOL;
2272 l_all_box_0 := FALSE;
2273 ELSE
2274 tab_employee(lM_LibereImpot) := NULL;
2275 END IF;
2276 hr_utility.trace('tab_employee(lM_LibereImpot) = ' ||
2277 tab_employee(lM_LibereImpot));
2278
2279 -- (Box N)
2280 -- Bug 5569097 Fix.
2281
2282 IF l_rl2_box_n IS NOT NULL THEN
2283 tab_employee(lN_NASConjoint) := '<N_NASConjoint>' ||
2284 l_rl2_box_n || '</N_NASConjoint>' || EOL;
2285 ELSE
2286 tab_employee(lN_NASConjoint) := NULL;
2287 END IF;
2288 hr_utility.trace('tab_employee(lN_NASConjoint) = ' ||
2289 tab_employee(lN_NASConjoint));
2290
2291 -- Summ (Box O)
2292 IF TO_NUMBER(l_rl2_box_o) > 999999999.99 THEN
2293 l_status := 'Failed';
2294 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2295 END IF;
2296
2297 IF l_rl2_box_o IS NOT NULL AND
2298 to_number(l_rl2_box_o) <> 0 THEN
2299
2300 SELECT ltrim(rtrim(to_char(to_number(l_rl2_box_o),l_format_mask)))
2301 INTO l_formatted_box
2302 FROM dual;
2303
2304 tab_employee(lO_RetraitRAP ) := '<O_RetraitRAP>' ||
2305 l_formatted_box || '</O_RetraitRAP>' || EOL;
2306 l_all_box_0 := FALSE;
2307 ELSE
2308 tab_employee(lO_RetraitRAP ) := NULL;
2309 END IF;
2310 hr_utility.trace('Value of Box O');
2311 hr_utility.trace('tab_employee(lO_RetraitRAP ) = ' ||
2312 tab_employee(lO_RetraitRAP ));
2313
2314 -- Negative Balance Exists
2315 IF l_negative_balance_flag = 'Y' THEN
2316 l_negative_box := 'Y';
2317 l_status := 'Failed';
2318 l_msg_code := 'NEG';
2319 END IF;
2320
2321 IF l_all_box_0 and to_number(l_taxation_year) < 2011 THEN -- Bug 13633422
2322 l_status := 'Failed';
2323 l_msg_code := 'ALL_BOXES_ZERO';
2324 END IF;
2325
2326 -- (Provenance1)
2327 hr_utility.trace('The checking for Provenance value ');
2328 hr_utility.trace('The value of Archiver Assignment Action Id '||l_arch_action_id);
2329 hr_utility.trace('The Value of Assignment Id '||l_asg_id);
2330
2331 hr_utility.trace('The value Of Provenenace : '|| l_rl2_source_of_income);
2332 IF l_rl2_source_of_income IS NOT NULL THEN
2333 -- <Provenance1> changed to <Provenance> for bug 13083624
2334 /* 14701748 YE12P211I RL2 ELECTRONIC INTERFACE CHANGES FOR 2012 */
2335 IF ( l_rl2_source_of_income = 'AUTRE' ) THEN
2336 tab_employee(lProvenance1) := null;
2337 else
2338 tab_employee(lProvenance1) := '<Provenance>' ||
2339 convert_special_char(l_rl2_source_of_income) || '</Provenance>' || EOL;
2340 end if;
2341 ELSE
2342 tab_employee(lProvenance1) := NULL;
2343 END IF;
2344 hr_utility.trace('tab_employee(lProvenance1) = ' ||
2345 tab_employee(lProvenance1));
2346
2347 /* For bug 8888411 */
2348 OPEN c_rl2_src_income(replace(l_rl2_source_of_income,'AUTRE','OTHER'), l_taxation_year);
2349 FETCH c_rl2_src_income into l_meaning;
2350 IF c_rl2_src_income%notfound then
2351 l_status := 'Failed';
2352 l_msg_code := 'INVALID_SOURCE_OF_INCOME';
2353 END IF;
2354 CLOSE c_rl2_src_income;
2355 /* End 8888411 */
2356
2357 OPEN cur_get_meaning(l_msg_code);
2358 FETCH cur_get_meaning
2359 INTO l_meaning;
2360 CLOSE cur_get_meaning;
2361
2362
2363
2364 tab_employee(lBoxA_UnregisterdPlan) := NULL;
2365 tab_employee(lDesg_BenefitExcAmt ) := NULL;
2366 tab_employee(lBoxB_DesgBenefitTrnsAmt) := NULL;
2367 tab_employee(lBoxExcessAmt) := NULL;
2368 tab_employee(lAmount_Transferred) := NULL;
2369 tab_employee(lBoxC_SinglePayAccured) := NULL;
2370 tab_employee(lBoxC_SinglePayAccUnreg) := NULL;
2371 tab_employee(lBoxC_ExcessAmtSinPayTrans) := NULL;
2372
2373
2374 IF l_status = 'Failed' THEN
2375
2376 /* Modified the logic to get further information - For bug number 13622665 */
2377 for rec in c_further_codes(l_arch_action_id)
2378 loop
2379 if rec.value is not null then
2380 if(rec.code <> '201' and to_number(rec.value) <> 0) then
2381 SELECT ltrim(rtrim(to_char(rec.value,l_format_mask)))
2382 INTO l_formatted_box
2383 FROM dual;
2384
2385 select decode(rec.code,'235','F_2','210','F_3',replace(rec.code,'-','_')) -- Bug 14701748
2386 into l_formatted_code
2387 from dual;
2388 l_other_details:=l_other_details||'<'||l_formatted_code||'>'
2389 ||l_formatted_box||'</'||l_formatted_code||'>'||EOL;
2390 elsif rec.code = '201' then
2391 l_other_details:=l_other_details||'<F_1>'
2392 ||rec.value||'</F_1>'||EOL;
2393 end if;
2394 end if;
2395 end loop;
2396 ELSIF l_status = 'Success' THEN
2397 further_info_count := 0;
2398 for rec in c_further_codes(l_arch_action_id)
2399 loop
2400 if rec.value is not null then
2401 further_info_count := further_info_count+1;
2402 l_further_code(further_info_count):=rec.code;
2403 l_further_amount(further_info_count):=rec.value;
2404 end if;
2405 end loop;
2406 END IF;
2407
2408 IF l_status = 'Failed' THEN
2409 tab_employee(lErrorDetails) := '<ErrorDetails>' ||
2410 convert_special_char(l_meaning) || '</ErrorDetails>' || EOL;
2411 ELSE
2412 tab_employee(lErrorDetails) := NULL;
2413 END IF;
2414
2415 -- IF l_rep_type <> 'RL2PAPERPDF' THEN
2416 /* l_final_xml_string :=
2417 '<' || l_status || '>' || EOL ||
2418 '<D>' || EOL ||
2419 tab_employee(lAnnee) ||
2420 tab_employee(lNoReleve) || '<Beneficiaire>' || EOL ||
2421 tab_employee(lNAS) ||
2422 tab_employee(lNo) ||
2423 tab_employee(lNomFamille) ||
2424 tab_employee(lPrenom) ||
2425 tab_employee(lInitiale) || l_addr_begin_tag || EOL ||
2426 tab_employee(lLigne1) ||
2427 tab_employee(lLigne2) ||
2428 tab_employee(lVille) ||
2429 tab_employee(lProvince) ||
2430 tab_employee(lCodePostal) ||
2431 l_addr_end_tag || EOL || '</Beneficiaire>' || EOL ||
2432 '<Montants>' || EOL ||
2433 tab_employee(lA_PrestRPA_RPNA) ||
2434 tab_employee(lB_PrestREER_FERR_RPDB) ||
2435 tab_employee(lC_AutrePaiement) ||
2436 tab_employee(lD_RembPrimeConjoint) ||
2437 tab_employee(lE_PrestDeces) ||
2438 tab_employee(lF_RembCotisInutilise) ||
2439 tab_employee(lG_RevocationREER_FERR) ||
2440 tab_employee(lH_AutreRevenu) ||
2441 tab_employee(lI_DroitDeduction ) ||
2442 tab_employee(lJ_ImpotQueRetenuSource) ||
2443 tab_employee(lK_RevenuApresDeces) ||
2444 tab_employee(lL_RetraitREEP) ||
2445 tab_employee(lM_LibereImpot) ||
2446 tab_employee(lN_NASConjoint) ||
2447 tab_employee(lO_RetraitRAP) ||
2448 tab_employee(lProvenance1) ||
2449 /*tab_employee(lBoxA_UnregisterdPlan) ||
2450 tab_employee(lDesg_BenefitExcAmt) ||
2451 tab_employee(lBoxB_DesgBenefitTrnsAmt) ||
2452 tab_employee(lBoxExcessAmt) ||
2453 tab_employee(lAmount_Transferred) ||
2454 tab_employee(lBoxC_SinglePayAccured) ||
2455 tab_employee(lBoxC_SinglePayAccUnreg) ||
2456 tab_employee(lBoxC_ExcessAmtSinPayTrans) ||*/
2457 /* l_other_details|| --For bug number 13622665
2458 tab_employee(lErrorDetails)||
2459 '</Montants>' || EOL || '</D>' || EOL ||
2460 '</' || l_status || '>' ;
2461
2462 hr_utility.trace('Just before Printing the file details ');
2463 pay_core_files.write_to_magtape_lob(l_final_xml_string);*/
2464 /* Bug 14701748 */
2465 further_info_details:= null;
2466
2467 IF l_status = 'Failed' THEN --For Failed employees further info should be displayed in single row. 'l_other_details' should be displayed
2468 /* Bug 15972457 */
2469 tab_employee(lNoReleve) := '<NoReleve>' || convert_special_char(lv_org_slip_no) ||
2470 '</NoReleve>' || EOL;
2471 /* Bug 15972457 */
2472 l_final_xml_string :=
2473 '<' || l_status || '>' || EOL ||
2474 '<D>' || EOL ||
2475 tab_employee(lAnnee) ||
2476 tab_employee(lNoReleve) || '<Beneficiaire>' || EOL ||
2477 tab_employee(lNAS) ||
2478 tab_employee(lNo) ||
2479 tab_employee(lNomFamille) ||
2480 tab_employee(lPrenom) ||
2481 tab_employee(lInitiale) || l_addr_begin_tag || EOL ||
2482 tab_employee(lLigne1) ||
2483 tab_employee(lLigne2) ||
2484 tab_employee(lVille) ||
2485 tab_employee(lProvince) ||
2486 tab_employee(lCodePostal) ||
2487 l_addr_end_tag || EOL || '</Beneficiaire>' || EOL ||
2488 '<Montants>' || EOL ||
2489 tab_employee(lA_PrestRPA_RPNA) ||
2490 tab_employee(lB_PrestREER_FERR_RPDB) ||
2491 tab_employee(lC_AutrePaiement) ||
2492 tab_employee(lD_RembPrimeConjoint) ||
2493 tab_employee(lE_PrestDeces) ||
2494 tab_employee(lF_RembCotisInutilise) ||
2495 tab_employee(lG_RevocationREER_FERR) ||
2496 tab_employee(lH_AutreRevenu) ||
2497 tab_employee(lI_DroitDeduction ) ||
2498 tab_employee(lJ_ImpotQueRetenuSource) ||
2499 tab_employee(lK_RevenuApresDeces) ||
2500 tab_employee(lL_RetraitREEP) ||
2501 tab_employee(lM_LibereImpot) ||
2502 tab_employee(lN_NASConjoint) ||
2503 tab_employee(lO_RetraitRAP) ||
2504 tab_employee(lProvenance1) ||
2505 /*tab_employee(lBoxA_UnregisterdPlan) ||
2506 tab_employee(lDesg_BenefitExcAmt) ||
2507 tab_employee(lBoxB_DesgBenefitTrnsAmt) ||
2508 tab_employee(lBoxExcessAmt) ||
2509 tab_employee(lAmount_Transferred) ||
2510 tab_employee(lBoxC_SinglePayAccured) ||
2511 tab_employee(lBoxC_SinglePayAccUnreg) ||
2512 tab_employee(lBoxC_ExcessAmtSinPayTrans) ||*/
2513 l_other_details||
2514 tab_employee(lErrorDetails)||
2515 '</Montants>' || EOL ||
2516 '</D>' || EOL ||
2517 '</' || l_status || '>' ;
2518
2519 else -- For success employees
2520 counter := 1;
2521 counter1 := 1;
2522 for l_i in 1..further_info_count
2523 loop
2524 if l_further_amount(l_i) is not null then
2525 if ( counter <= 4 ) then
2526 if(l_further_code(l_i) <> '201' and to_number(l_further_amount(l_i)) <> 0) then
2527 SELECT ltrim(rtrim(to_char(l_further_amount(l_i),l_format_mask)))
2528 INTO l_formatted_box
2529 FROM dual;
2530
2531
2532 further_info_details:= further_info_details || '<CaseRensCompl>' ||EOL ||
2533 '<CodeRensCompl>' || l_further_code(l_i) || '</CodeRensCompl>' ||EOL ||
2534 '<DonneeRensCompl>' || l_formatted_box || '</DonneeRensCompl>' ||EOL ||
2535 '</CaseRensCompl>' ||EOL ;
2536 counter := counter+1;
2537 counter1 := l_i+1;
2538 elsif l_further_code(l_i) = '201' then
2539 further_info_details:= further_info_details || '<CaseRensCompl>' ||EOL ||
2540 '<CodeRensCompl>' || l_further_code(l_i) || '</CodeRensCompl>' ||EOL ||
2541 '<DonneeRensCompl>' || l_further_amount(l_i) || '</DonneeRensCompl>' ||EOL ||
2542 '</CaseRensCompl>' ||EOL ;
2543
2544 counter := counter+1;
2545 counter1 := l_i+1;
2546 end if;
2547 else -- ( counter <= 4 )
2548 if((l_further_code(l_i) <> '201' and to_number(l_further_amount(l_i)) <> 0)
2549 OR (l_further_code(l_i) = '201' )) then
2550 counter := counter+1;
2551 end if;
2552 end if;
2553 end if;
2554 end loop;
2555 l_final_xml_string :=
2556 '<' || l_status || '>' || EOL ||
2557 '<D>' || EOL ||
2558 tab_employee(lAnnee) ||
2559 tab_employee(lNoReleve) || '<Beneficiaire>' || EOL ||
2560 tab_employee(lNAS) ||
2561 tab_employee(lNo) ||
2562 tab_employee(lNomFamille) ||
2563 tab_employee(lPrenom) ||
2564 tab_employee(lInitiale) || l_addr_begin_tag || EOL ||
2565 tab_employee(lLigne1) ||
2566 tab_employee(lLigne2) ||
2567 tab_employee(lVille) ||
2568 tab_employee(lProvince) ||
2569 tab_employee(lCodePostal) ||
2570 l_addr_end_tag || EOL || '</Beneficiaire>' || EOL ||
2571 '<Montants>' || EOL ||
2572 tab_employee(lA_PrestRPA_RPNA) ||
2573 tab_employee(lB_PrestREER_FERR_RPDB) ||
2574 tab_employee(lC_AutrePaiement) ||
2575 tab_employee(lD_RembPrimeConjoint) ||
2576 tab_employee(lE_PrestDeces) ||
2577 tab_employee(lF_RembCotisInutilise) ||
2578 tab_employee(lG_RevocationREER_FERR) ||
2579 tab_employee(lH_AutreRevenu) ||
2580 tab_employee(lI_DroitDeduction ) ||
2581 tab_employee(lJ_ImpotQueRetenuSource) ||
2582 tab_employee(lK_RevenuApresDeces) ||
2583 tab_employee(lL_RetraitREEP) ||
2584 tab_employee(lM_LibereImpot) ||
2585 tab_employee(lN_NASConjoint) ||
2586 tab_employee(lO_RetraitRAP) ||
2587 tab_employee(lProvenance1) ||
2588 /*tab_employee(lBoxA_UnregisterdPlan) ||
2589 tab_employee(lDesg_BenefitExcAmt) ||
2590 tab_employee(lBoxB_DesgBenefitTrnsAmt) ||
2591 tab_employee(lBoxExcessAmt) ||
2592 tab_employee(lAmount_Transferred) ||
2593 tab_employee(lBoxC_SinglePayAccured) ||
2594 tab_employee(lBoxC_SinglePayAccUnreg) ||
2595 tab_employee(lBoxC_ExcessAmtSinPayTrans) || */
2596 '</Montants>' || EOL ||
2597 further_info_details || EOL ||
2598 '</D>' || EOL ||
2599 '</' || l_status || '>' ;
2600
2601 if ( further_info_count >= counter1 ) then
2602 further_info_details:= null;
2603 count1 :=0;
2604 slip_count :=2;
2605 for l_i in counter1..further_info_count
2606 loop
2607 if l_further_amount(l_i) is not null then
2608 if(l_further_code(l_i) <> '201' and to_number(l_further_amount(l_i)) <> 0) then
2609 SELECT ltrim(rtrim(to_char(l_further_amount(l_i),l_format_mask)))
2610 INTO l_formatted_box
2611 FROM dual;
2612
2613
2614 further_info_details:= further_info_details || '<CaseRensCompl>' ||EOL ||
2615 '<CodeRensCompl>' || l_further_code(l_i) || '</CodeRensCompl>' ||EOL ||
2616 '<DonneeRensCompl>' || l_formatted_box || '</DonneeRensCompl>' ||EOL ||
2617 '</CaseRensCompl>' ||EOL ;
2618 count1 := count1 + 1;
2619 elsif l_further_code(l_i) = '201' then
2620 further_info_details:= further_info_details || '<CaseRensCompl>' ||EOL ||
2621 '<CodeRensCompl>' || l_further_code(l_i) || '</CodeRensCompl>' ||EOL ||
2622 '<DonneeRensCompl>' || l_further_amount(l_i) || '</DonneeRensCompl>' ||EOL ||
2623 '</CaseRensCompl>' ||EOL ;
2624 count1 := count1 + 1;
2625 end if;
2626 end if;
2627 if ( (( count1 =4 ) or ( l_i = further_info_count )) and counter > 5 ) then
2628 l_final_xml_string_fur_inf :=
2629 '<' || l_status || '>' || EOL ||
2630 '<D>' || EOL ||
2631 tab_employee(lAnnee) ||
2632 '<NoReleve>' || pay_ca_rl2_mag.get_slip_seq_no('|',lv_org_slip_no,slip_count) || '</NoReleve>' ||
2633 '<Beneficiaire>' || EOL ||
2634 tab_employee(lNAS) ||
2635 tab_employee(lNo) ||
2636 tab_employee(lNomFamille) ||
2637 tab_employee(lPrenom) ||
2638 tab_employee(lInitiale) || l_addr_begin_tag || EOL ||
2639 tab_employee(lLigne1) ||
2640 tab_employee(lLigne2) ||
2641 tab_employee(lVille) ||
2642 tab_employee(lProvince) ||
2643 tab_employee(lCodePostal) ||
2644 l_addr_end_tag || EOL || '</Beneficiaire>' || EOL ||
2645 '<Montants>' ||
2646 '</Montants>' || EOL ||
2647 further_info_details || EOL ||
2648 '</D>' || EOL ||
2649 '</' || l_status || '>' ;
2650 count1 := 0;
2651 l_final_xml_string := l_final_xml_string || l_final_xml_string_fur_inf ;
2652 further_info_details := null;
2653 l_final_xml_string_fur_inf := null;
2654 slip_count := slip_count+1;
2655 end if;
2656 end loop;
2657 end if;
2658 end if;
2659 /* Bug 14701748 */
2660 hr_utility.trace('Just before Printing the file details ');
2661 pay_core_files.write_to_magtape_lob(l_final_xml_string);
2662 END;
2663 END xml_employee_record;
2664
2665 PROCEDURE xml_employer_start IS
2666 BEGIN
2667
2668 DECLARE
2669
2670 l_final_xml_string VARCHAR2(32000);
2671
2672 BEGIN
2673
2674 l_final_xml_string := '<Groupe02>';
2675 pay_core_files.write_to_magtape_lob(l_final_xml_string);
2676
2677 END;
2678 END xml_employer_start;
2679
2680 PROCEDURE xml_employer_record IS
2681 BEGIN
2682
2683 DECLARE
2684
2685 l_final_xml_string VARCHAR2(32000);
2686
2687 TYPE employer_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
2688
2689 tab_employer employer_info;
2690
2691 lAnnee NUMBER;
2692 lNbReleves NUMBER;
2693 lNold NUMBER;
2694 lTypeDossier NUMBER;
2695 lNoDossier NUMBER;
2696 lNom1 NUMBER;
2697 lLigne1 NUMBER;
2698 lLigne2 NUMBER;
2699 lVille NUMBER;
2700 lProvince NUMBER;
2701 lCodePostal NUMBER;
2702 l_taxation_year varchar2(4);
2703 l_payroll_actid NUMBER;
2704 l_year_start DATE;
2705 l_year_end DATE;
2706 l_report_type VARCHAR2(30);
2707 l_business_grpid NUMBER;
2708 l_legislative_param pay_payroll_actions.legislative_parameters%type;
2709 EOL varchar2(5);
2710 l_employer_name varchar2(100);
2711 l_quebec_bn varchar2(20);
2712 l_address_line per_addresses.address_line1%TYPE;
2713 l_address_begin_tag varchar2(10);
2714 l_address_end_tag varchar2(10);
2715
2716 BEGIN
2717 hr_utility.trace('XML Employer');
2718 hr_utility.trace('XML Employer');
2719
2720 SELECT
2721 fnd_global.local_chr(13) || fnd_global.local_chr(10)
2722 INTO EOL
2723 FROM dual;
2724
2725 lAnnee := 1;
2726 lNbReleves := 2;
2727 lNold := 3;
2728 lTypeDossier := 4;
2729 lNoDossier := 5;
2730 lNom1 := 6;
2731 lLigne1 := 7;
2732 lLigne2 := 8;
2733 lVille := 9;
2734 lProvince := 10;
2735 lCodePostal := 11;
2736
2737 l_taxation_year
2738 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
2739
2740 /* commented for bug 9142143
2741 l_payroll_actid
2742 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');*/
2743
2744 select max(paa1.payroll_action_id)
2745 into l_payroll_actid
2746 from pay_assignment_actions paa,
2747 pay_assignment_actions paa1,
2748 pay_action_interlocks int
2749 where int.locking_action_id = paa.assignment_action_id
2750 AND paa1.assignment_action_id = int.locked_action_id
2751 and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2752
2753 get_report_parameters(
2754 l_payroll_actid,
2755 l_year_start,
2756 l_year_end,
2757 l_report_type,
2758 l_business_grpid,
2759 l_legislative_param
2760 );
2761
2762 tab_employer(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' || EOL;
2763 tab_employer(lNbReleves) := '<NbReleves>' || 'Running Total' || '</NbReleves>' || EOL;
2764
2765 hr_utility.trace('The Payroll Action Id : '||l_payroll_actid);
2766 hr_utility.trace('The business group id : '||l_business_grpid);
2767 l_quebec_bn := pay_ca_rl2_can_mag.get_employer_item(l_business_grpid,
2768 l_payroll_actid,
2769 'CAEOY_RL2_QUEBEC_BN');
2770 hr_utility.trace('The Quebec Number is '||l_quebec_bn);
2771 tab_employer(lNold) := '<NoId>' || convert_special_char(substr(l_quebec_bn,1,10)) ||
2772 '</NoId>' || EOL;
2773 tab_employer(lTypeDossier) := '<TypeDossier>' || 'RS' ||
2774 '</TypeDossier>' || EOL;
2775
2776 tab_employer(lNoDossier) := '<NoDossier>' || convert_special_char(substr(l_quebec_bn,13,4)) ||
2777 '</NoDossier>' || EOL;
2778 hr_utility.trace('The Employer File Number : '|| substr(l_quebec_bn,13,4));
2779 l_employer_name := pay_ca_rl2_can_mag.get_employer_item(l_business_grpid,
2780 l_payroll_actid,
2781 'CAEOY_RL2_EMPLOYER_NAME');
2782
2783 tab_employer(lNom1) := '<Nom>' ||
2784 convert_special_char(substr(l_employer_name,1,30)) || '</Nom>' || EOL;
2785 hr_utility.trace('tab_employer(lNom) = ' || tab_employer(lNom1));
2786
2787 -- Address Line 1
2788
2789 l_address_line := pay_ca_rl2_can_mag.get_employer_item(l_business_grpid,
2790 l_payroll_actid,
2791 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE1');
2792
2793 IF (l_address_line IS NULL AND
2794 l_address_line <> ' ' ) THEN
2795
2796 l_address_begin_tag := '';
2797 tab_employer(lLigne1) := NULL;
2798 tab_employer(lLigne2) := NULL;
2799 tab_employer(lVille) := NULL;
2800 tab_employer(lProvince) := NULL;
2801 tab_employer(lCodePostal) := NULL;
2802 l_address_end_tag := '';
2803
2804 ELSE
2805
2806 l_address_begin_tag := '<Adresse>';
2807
2808 tab_employer(lLigne1) := '<Ligne1>' ||
2809 convert_special_char(substr(l_address_line,1,30)) || '</Ligne1>' || EOL;
2810 hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
2811
2812
2813 -- Address Line 2
2814 l_address_line := pay_ca_rl2_can_mag.get_employer_item(l_business_grpid,
2815 l_payroll_actid,
2816 'CAEOY_RL2_EMPLOYER_ADDRESS_LINE2');
2817
2818 IF (l_address_line IS NOT NULL AND
2819 l_address_line <> ' ' ) THEN
2820 tab_employer(lLigne2) := '<Ligne2>' ||
2821 convert_special_char(substr(l_address_line,1,30)) || '</Ligne2>' || EOL;
2822 ELSE
2823 tab_employer(lLigne2) := NULL;
2824 END IF;
2825 hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
2826
2827 -- Ville (City)
2828 l_address_line := pay_ca_rl2_can_mag.get_employer_item(l_business_grpid,
2829 l_payroll_actid,
2830 'CAEOY_RL2_EMPLOYER_CITY');
2831 IF ( l_address_line IS NOT NULL AND
2832 l_address_line <> ' ') THEN
2833 tab_employer(lVille) := '<Ville>' ||
2834 convert_special_char(substr(l_address_line,1,30)) || '</Ville>' || EOL;
2835 ELSE
2836 tab_employer(lVille) := NULL;
2837 END IF;
2838 hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
2839
2840 -- Province
2841 l_address_line := pay_ca_rl2_can_mag.get_employer_item(l_business_grpid,
2842 l_payroll_actid,
2843 'CAEOY_RL2_EMPLOYER_PROVINCE');
2844
2845 IF ( l_address_line IS NOT NULL AND
2846 l_address_line <> ' ' ) THEN
2847 tab_employer(lProvince) := '<Province>' ||
2848 convert_special_char(SUBSTR(hr_general.decode_lookup( 'CA_PROVINCE',
2849 l_address_line),1,20)) || '</Province>' || EOL;
2850 ELSE
2851 tab_employer(lProvince) := NULL;
2852 END IF;
2853 hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
2854
2855 -- Postal Code
2856 l_address_line := pay_ca_rl2_can_mag.get_employer_item(l_business_grpid,
2857 l_payroll_actid,
2858 'CAEOY_RL2_EMPLOYER_POSTAL_CODE');
2859
2860 IF ( l_address_line IS NOT NULL AND
2861 l_address_line <> ' ' ) THEN
2862 tab_employer(lCodePostal) := '<CodePostal>' ||
2863 convert_special_char(substr(l_address_line,1,6)) || '</CodePostal>' || EOL;
2864 ELSE
2865 tab_employer(lCodePostal) := NULL;
2866 END IF;
2867 hr_utility.trace('tab_employer(lCodePostal) = ' ||
2868 tab_employer(lCodePostal));
2869
2870 l_address_end_tag := '</Adresse>';
2871
2872 END IF;
2873 l_final_xml_string := '<T>' || EOL ||
2874 tab_employer(lAnnee) ||
2875 tab_employer(lNbReleves)|| EOL || '<PayeurEmetteur>' || EOL ||
2876 tab_employer(lNold) ||
2877 tab_employer(lTypeDossier) ||
2878 tab_employer(lNoDossier) ||
2879 tab_employer(lNom1) || l_address_begin_tag || EOL ||
2880 tab_employer(lLigne1) ||
2881 tab_employer(lLigne2) ||
2882 tab_employer(lVille) ||
2883 tab_employer(lProvince) ||
2884 tab_employer(lCodePostal) ||
2885 l_address_end_tag || EOL || '</PayeurEmetteur>' || EOL ||
2886 '</T>' || EOL ||
2887 '</Groupe02>' || EOL;
2888
2889 pay_core_files.write_to_magtape_lob(l_final_xml_string);
2890
2891 END;
2892 END xml_employer_record;
2893
2894 /* Added by ssmukher for Bug 4030973 */
2895 /* The check digit calculated by the method below
2896 must be the same as the 10th digit of the
2897 identification number or the number is invalid.
2898
2899 Example of the modulus 11 method:
2900 The identification number is 2345678908.
2901 Use the first nine digits to validate the identification number.
2902 The tenth digit is the check digit (in this case 8).
2903
2904 Formula:
2905 Beginning with the ninth digit (extreme right), multiply each digit by the
2906 factor indicated.
2907 The factors form a circular sequence of the values 2 through 7, beginning on
2908 the right.
2909 First nine digits of the identification number 2 3 4 5 6 7 8 9 0
2910 Multiply each digit by the factor indicated. x 4 x 3 x 2 x 7 x 6 x 5 x 4 x 3
2911 x 2
2912 Results 8 9 8 35 36 35 32 27 0
2913 Add the results
2914 (8 + 9 + 8 + 35 + 36 + 35 + 32 + 27 + 0). = 190
2915 Divide the result by 11 (190/11). = 17 remainder 3
2916 If the remainder is 0, the check digit is 1. If the remainder is 1, the check
2917 digit is 0.
2918 For any other remainder obtained, the check digit is the difference between
2919 11 and that remainder.
2920 Subtract the remainder obtained from 11 (11 - 3) = 8
2921 */
2922
2923 FUNCTION validate_quebec_number (p_quebec_no IN VARCHAR2,p_qin_name varchar2)
2924 RETURN NUMBER IS
2925
2926 l_quebec NUMBER;
2927 l_rem NUMBER;
2928 i NUMBER;
2929 l_max NUMBER;
2930 l_total NUMBER;
2931 l_min NUMBER;
2932 l_modulus NUMBER;
2933 l_chk_digit NUMBER;
2934 l_act_chk_number NUMBER;
2935
2936 BEGIN
2937 i := 1;
2938 l_min := 2;
2939 l_max := 7;
2940 l_total := 0;
2941 l_quebec := to_number(substr(p_quebec_no,1,9));
2942 l_act_chk_number := to_number(substr(p_quebec_no,10,1));
2943
2944 if TRANSLATE(substr(p_quebec_no,1,9),'0123456789','9999999999') = '999999999' then
2945
2946 loop
2947 if i > 9 then
2948 exit;
2949 end if;
2950
2951 if l_min > l_max then
2952 l_min := 2;
2953 end if;
2954
2955 l_rem := mod(l_quebec,10);
2956 l_total := l_total + (l_min * l_rem);
2957 l_min := l_min + 1;
2958 l_quebec := ((l_quebec - l_rem)/10);
2959 i := i+ 1;
2960
2961 end loop;
2962
2963 l_modulus := mod(l_total, 11);
2964 if l_modulus = 0 then
2965 l_chk_digit := 1;
2966 elsif l_modulus = 1 then
2967 l_chk_digit := 0;
2968 else
2969 l_chk_digit := 11 - l_modulus;
2970 end if;
2971
2972 if l_chk_digit <> l_act_chk_number then
2973 hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
2974 hr_utility.set_message_token('PRE_NAME',p_qin_name);
2975 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
2976 pay_core_utils.push_token('PRE_NAME',p_qin_name);
2977 hr_utility.raise_error;
2978 end if;
2979 else
2980
2981 hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
2982 hr_utility.set_message_token('PRE_NAME',p_qin_name);
2983 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
2984 pay_core_utils.push_token('PRE_NAME',p_qin_name);
2985 hr_utility.raise_error;
2986
2987 end if;
2988
2989 return l_chk_digit;
2990
2991 END;
2992
2993
2994 FUNCTION convert_special_char( p_data varchar2)
2995 RETURN varchar2 IS
2996 l_data VARCHAR2(2000);
2997 l_output varchar2(2000);
2998 cursor c_uppercase(p_input_string varchar2) is
2999 select
3000 replace(
3001 replace(
3002 replace(
3003 replace(
3004 replace(
3005 replace(
3006 replace(
3007 replace(
3008 replace(
3009 replace(
3010 replace(
3011 replace(
3012 replace(
3013 replace(
3014 replace(
3015 replace(convert(p_input_string,'UTF8'),
3016 utl_raw.cast_to_varchar2(hextoraw('C380')),'A'
3017 ),
3018 utl_raw.cast_to_varchar2(hextoraw('C38A')),'E'
3019 ),
3020 utl_raw.cast_to_varchar2(hextoraw('C387')),'C'
3021 ),
3022 utl_raw.cast_to_varchar2(hextoraw('C389')),'E'
3023 ),
3024 utl_raw.cast_to_varchar2(hextoraw('C39C')),'U'
3025 ),
3026 utl_raw.cast_to_varchar2(hextoraw('C399')),'U'
3027 ),
3028 utl_raw.cast_to_varchar2(hextoraw('C39B')),'U'
3029 ),
3030 utl_raw.cast_to_varchar2(hextoraw('C394')),'O'
3031 ),
3032 utl_raw.cast_to_varchar2(hextoraw('C38F')),'I'
3033 ),
3034 utl_raw.cast_to_varchar2(hextoraw('C38E')),'I'
3035 ),
3036 utl_raw.cast_to_varchar2(hextoraw('C388')),'E'
3037 ),
3038 utl_raw.cast_to_varchar2(hextoraw('C38B')),'E'
3039 ),
3040 utl_raw.cast_to_varchar2(hextoraw('C382')),'A'
3041 ),
3042 utl_raw.cast_to_varchar2(hextoraw('C592')),'OE'
3043 ),
3044 utl_raw.cast_to_varchar2(hextoraw('C386')),'AE'
3045 ),
3046 utl_raw.cast_to_varchar2(hextoraw('C3A9')),'e'
3047 )
3048 from dual;
3049
3050 BEGIN
3051 l_data := trim(p_data);
3052 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
3053 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
3054 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
3055 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
3056 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
3057
3058 open c_uppercase(l_data);
3059 fetch c_uppercase into l_output;
3060 if c_uppercase%NOTFOUND then
3061 l_output := l_data;
3062 end if;
3063 close c_uppercase;
3064
3065 RETURN l_output;
3066 END;
3067
3068 END pay_ca_rl2_can_mag;