[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_RL1_MAG
Source
1 PACKAGE BODY pay_ca_rl1_mag AS
2 /* $Header: pycarlmg.pkb 120.73.12010000.3 2008/09/02 07:09:17 sapalani ship $ */
3
4 -- Name : get_report_parameters
5
6 -----------------------------------------------------------------------------
7 --
8 -- Purpose
9 -- The procedure gets the 'parameter' for which the report is being
10 -- run i.e., the period, state and business organization.
11 --
12 -- Arguments
13 -- p_pactid Payroll_action_id passed from pyugen process
14 -- p_year_start Start Date of the period for which the report
15 -- has been requested
16 -- p_year_end End date of the period
17 -- p_business_group_id Business group for which the report is being run
18 -- p_report_type Type of report being run RL1
19 --
20 -- Notes
21 ----------------------------------------------------------------------------
22
23
24 PROCEDURE get_report_parameters
25 ( p_pactid IN NUMBER,
26 p_year_start IN OUT NOCOPY DATE,
27 p_year_end IN OUT NOCOPY DATE,
28 p_report_type IN OUT NOCOPY VARCHAR2,
29 p_business_group_id IN OUT NOCOPY NUMBER
30 ) IS
31 BEGIN
32 --hr_utility.trace_on('Y','RL1MAG');
33 hr_utility.set_location('pay_ca_rl1_mag.get_report_parameters', 10);
34
35 SELECT ppa.start_date,
36 ppa.effective_date,
37 ppa.business_group_id,
38 ppa.report_type
39 INTO p_year_start,
40 p_year_end,
41 p_business_group_id,
42 p_report_type
43 FROM pay_payroll_actions ppa
44 WHERE payroll_action_id = p_pactid;
45
46 hr_utility.set_location('pay_ca_rl1_mag.get_report_parameters', 20);
47
48 END get_report_parameters;
49
50 FUNCTION convert_special_char( p_data varchar2)
51 RETURN varchar2 IS
52 l_data VARCHAR2(2000);
53 l_output varchar2(2000);
54 cursor c_uppercase(p_input_string varchar2) is
55 select
56 replace(
57 replace(
58 replace(
59 replace(
60 replace(
61 replace(
62 replace(
63 replace(
64 replace(
65 replace(
66 replace(
67 replace(
68 replace(
69 replace(
70 replace(
71 replace(convert(p_input_string,'UTF8'),
72 utl_raw.cast_to_varchar2(hextoraw('C380')),'A'
73 ),
74 utl_raw.cast_to_varchar2(hextoraw('C38A')),'E'
75 ),
76 utl_raw.cast_to_varchar2(hextoraw('C387')),'C'
77 ),
78 utl_raw.cast_to_varchar2(hextoraw('C389')),'E'
79 ),
80 utl_raw.cast_to_varchar2(hextoraw('C39C')),'U'
81 ),
82 utl_raw.cast_to_varchar2(hextoraw('C399')),'U'
83 ),
84 utl_raw.cast_to_varchar2(hextoraw('C39B')),'U'
85 ),
86 utl_raw.cast_to_varchar2(hextoraw('C394')),'O'
87 ),
88 utl_raw.cast_to_varchar2(hextoraw('C38F')),'I'
89 ),
90 utl_raw.cast_to_varchar2(hextoraw('C38E')),'I'
91 ),
92 utl_raw.cast_to_varchar2(hextoraw('C388')),'E'
93 ),
94 utl_raw.cast_to_varchar2(hextoraw('C38B')),'E'
95 ),
96 utl_raw.cast_to_varchar2(hextoraw('C382')),'A'
97 ),
98 utl_raw.cast_to_varchar2(hextoraw('C592')),'OE'
99 ),
100 utl_raw.cast_to_varchar2(hextoraw('C386')),'AE'
101 ),
102 utl_raw.cast_to_varchar2(hextoraw('C3A9')),'e'
103 )
104 from dual;
105
106 BEGIN
107 l_data := trim(p_data);
108 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
109 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
110 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
111 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
112 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
113
114 open c_uppercase(l_data);
115 fetch c_uppercase into l_output;
116 if c_uppercase%NOTFOUND then
117 l_output := l_data;
118 end if;
119 close c_uppercase;
120
121 RETURN l_output;
122 END;
123
124 PROCEDURE validate_transmitter_info(p_payroll_action_id IN NUMBER,
125 p_bg_id IN NUMBER,
126 p_effective_date IN DATE) IS
127 BEGIN
128
129 DECLARE
130
131 CURSOR cur_arch_pactid(p_transmitter_org_id NUMBER) IS
132 SELECT
133 ppa.payroll_action_id
134 FROM
135 pay_payroll_actions ppa
136 WHERE
137 ppa.business_group_id = p_bg_id AND
138 ppa.report_type = 'RL1' AND
139 ppa.report_qualifier = 'CAEOYRL1' AND
140 ppa.report_category = 'CAEOYRL1' AND
141 ppa.effective_date = p_effective_date AND
142 p_transmitter_org_id =
143 pay_ca_rl1_mag.get_parameter('PRE_ORGANIZATION_ID',
144 ppa.legislative_parameters);
145
146 l_transmitter_number VARCHAR2(30);
147 l_transmitter_name VARCHAR2(100);
148 l_type_of_package VARCHAR2(30);
149 l_source_of_slips VARCHAR2(30);
150 dummy NUMBER;
151 dummy1 VARCHAR2(10);
152 l_transmitter_org_id NUMBER;
153 l_arch_pactid NUMBER;
154 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
155 l_address_line1 hr_locations.address_line_1%TYPE;
156
157 CURSOR cur_ppa IS
158 SELECT
159 ppa.legislative_parameters
160 FROM
161 pay_payroll_actions ppa
162 WHERE
163 ppa.payroll_action_id = p_payroll_action_id;
164
165 BEGIN
166
167 OPEN cur_ppa;
168 FETCH cur_ppa
169 INTO l_legislative_parameters;
170 CLOSE cur_ppa;
171
172 l_transmitter_org_id := pay_ca_rl1_mag.get_parameter('TRANSMITTER_PRE',
173 l_legislative_parameters);
174
175 hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
176 hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
177 hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
178 hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
179
180 OPEN cur_arch_pactid(l_transmitter_org_id);
181 FETCH cur_arch_pactid
182 INTO l_arch_pactid;
183 CLOSE cur_arch_pactid;
184
185 l_transmitter_number := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NUMBER');
186 l_transmitter_name := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NAME');
187 BEGIN
188
189 hr_utility.trace('l_transmitter_number = ' || l_transmitter_number);
190 SELECT substr(l_transmitter_number,1,2)
191 INTO dummy1
192 FROM dual;
193
194 IF (dummy1 <> 'NP' OR
195 length(l_transmitter_number) <> 8) THEN
196 RAISE INVALID_NUMBER;
197 END IF;
198
199 SELECT to_number(substr(l_transmitter_number,3,6))
200 INTO dummy
201 FROM dual;
202
203 EXCEPTION
204 WHEN INVALID_NUMBER THEN
205 hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
206 hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
207 pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
208 pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
209 hr_utility.raise_error;
210 END;
211
212 l_type_of_package := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE');
213
214 hr_utility.trace('l_type_of_package = ' || l_type_of_package);
215
216 IF l_type_of_package IS NULL THEN
217 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
218 hr_utility.raise_error;
219 END IF;
220
221 l_source_of_slips := get_arch_val(l_arch_pactid,'CAEOY_RL1_SOURCE_OF_SLIPS');
222 hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
223
224 IF l_source_of_slips IS NULL THEN
225 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
226 hr_utility.raise_error;
227 END IF;
228
229 l_address_line1 := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
230 hr_utility.trace('l_address_line1 = ' || l_address_line1);
231
232 IF l_address_line1 IS NULL THEN
233 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
234 hr_utility.raise_error;
235 END IF;
236
237 END;
238
239 END validate_transmitter_info;
240
241
242 ----------------------------------------------------------------------------
243 --Name
244 -- range_cursor
245 --Purpose
246 -- This procedure defines a SQL statement
247 -- to fetch all the people to be included in the report. This SQL statement
248 -- is used to define the 'chunks' for multi-threaded operation
249 --Arguments
250 -- p_pactid payroll action id for the report
251 -- p_sqlstr the SQL statement to fetch the people
252 ------------------------------------------------------------------------------
253 PROCEDURE range_cursor (
254 p_pactid IN NUMBER,
255 p_sqlstr OUT NOCOPY VARCHAR2
256 )
257 IS
258 p_year_start DATE;
259 p_year_end DATE;
260 p_business_group_id NUMBER;
261 p_report_type VARCHAR2(30);
262
263 BEGIN
264 -- hr_utility.trace_on(null,'PDF');
265 hr_utility.set_location( 'pay_ca_rl1_mag.range_cursor', 10);
266
267 get_report_parameters(
268 p_pactid,
269 p_year_start,
270 p_year_end,
271 p_report_type,
272 p_business_group_id
273 );
274
275 hr_utility.set_location( 'pay_ca_rl1_mag.range_cursor', 20);
276
277
278 p_sqlstr := 'select distinct to_number(fai1.value)
279 from ff_archive_items fai1,
280 ff_database_items fdi1,
281 ff_archive_items fai2,
282 ff_database_items fdi2,
283 pay_assignment_actions paa,
284 pay_payroll_actions ppa,
285 pay_payroll_actions ppa1,
286 hr_organization_information hoi
287 where ppa1.payroll_action_id = :payroll_action_id
288 and ppa.business_group_id+0 = ppa1.business_group_id
289 and ppa.effective_date = ppa1.effective_date
290 and ppa.report_type = ''RL1''
291 and ppa.payroll_action_id = paa.payroll_action_id
292 and fdi2.user_name = ''CAEOY_TAXATION_YEAR''
293 and fai2.user_entity_id = fdi2.user_entity_id
294 and fai2.value = pay_ca_rl1_mag.get_parameter(''REPORTING_YEAR'',ppa1.legislative_parameters)
295 and paa.payroll_action_id= fai2.context1
296 and paa.action_status = ''C''
297 and paa.assignment_action_id = fai1.context1
298 and fai1.user_entity_id = fdi1.user_entity_id
299 and fdi1.user_name = ''CAEOY_PERSON_ID''
300 and decode(hoi.org_information3, ''Y'', hoi.organization_id, hoi.org_information20) =
301 pay_ca_rl1_mag.get_parameter(''TRANSMITTER_PRE'', ppa1.legislative_parameters )
302 and hoi.org_information_context =''Prov Reporting Est''
303 and hoi.organization_id = pay_ca_rl1_mag.get_parameter(''PRE_ORGANIZATION_ID'', ppa.legislative_parameters )
304 order by to_number(fai1.value)' ;
305
306 hr_utility.set_location( 'pay_ca_rl1_mag.range_cursor',40);
307
308 END range_cursor;
309
310 -------------------------------------------------------------------------------
311 --Name
312 -- create_assignment_act
313 --Purpose
314 -- Creates assignment actions for the payroll action associated with the
315 -- report
316 --Arguments
317 -- p_pactid payroll action for the report
318 -- p_stperson starting person id for the chunk
319 -- p_endperson last person id for the chunk
320 -- p_chunk size of the chunk
321 --Note
322 -- The procedure processes assignments in 'chunks' to facilitate
323 -- multi-threaded operation. The chunk is defined by the size and the
324 -- starting and ending person id. An interlock is also created against the
325 -- pre-processor assignment action to prevent rolling back of the archiver.
326 ------------------------------------------------------------------------------
327 PROCEDURE create_assignment_act(
328 p_pactid IN NUMBER,
329 p_stperson IN NUMBER,
330 p_endperson IN NUMBER,
331 p_chunk IN NUMBER )
332 IS
333
334 -- Cursor to retrieve all the assignments for all GRE's
335 -- archived in a reporting year
336
337 CURSOR c_all_asg IS
338 SELECT paf.person_id,
339 paf.assignment_id,
340 paa.tax_unit_id,
341 paf.effective_end_date,
342 paa.assignment_action_id,
343 ppa.payroll_action_id -- Added by ssmukher for Bug 3353115
344 FROM pay_payroll_actions ppa,
345 pay_assignment_actions paa,
346 per_all_assignments_f paf,
347 pay_payroll_actions ppa1,
348 hr_organization_information hoi
349 WHERE ppa1.payroll_action_id = p_pactid
350 AND ppa.report_type = 'RL1'
351 AND ppa.business_group_id+0 = ppa1.business_group_id
352 AND ppa.effective_date = ppa1.effective_date
353 AND paa.payroll_action_id = ppa.payroll_action_id
354 AND paa.action_status = 'C'
355 AND paf.assignment_id = paa.assignment_id
356 AND paf.person_id BETWEEN p_stperson AND p_endperson
357 -- AND paf.assignment_type = 'E'
358 AND paf.effective_start_date <= ppa.effective_date
359 AND paf.effective_end_date >= ppa.start_date
360 and decode(hoi.org_information3, 'Y', hoi.organization_id, hoi.org_information20) =
361 substr(ppa1.legislative_parameters,
362 instr(ppa1.legislative_parameters,'TRANSMITTER_PRE=')+16)
363 and hoi.org_information_context = 'Prov Reporting Est'
364 and hoi.organization_id =
365 substr(ppa.legislative_parameters,
366 instr(ppa.legislative_parameters,'PRE_ORGANIZATION_ID=')+20)
367 and paf.effective_end_date = (select max(paf1.effective_end_date)
368 from per_all_assignments_f paf1
369 where paf1.assignment_id = paf.assignment_id
370 and paf1.effective_start_date <= ppa1.effective_date);
371
372 --local variables
373
374 l_year_start DATE;
375 l_year_end DATE;
376 l_effective_end_date DATE;
377 l_report_type VARCHAR2(30);
378 l_business_group_id NUMBER;
379 l_person_id NUMBER;
380 l_assignment_id NUMBER;
381 l_assignment_action_id NUMBER;
382 l_value NUMBER;
383 l_tax_unit_id NUMBER;
384 lockingactid NUMBER;
385
386 /* Added by ssmukher for Bug 3353115 */
387 l_prev_payact NUMBER;
388 l_payroll_act NUMBER;
389 l_quebec_val VARCHAR2(20);
390 l_quebec_no VARCHAR2(20);
391 l_quebec_name VARCHAR2(240);
392 l_return NUMBER;
393
394 BEGIN
395
396 -- hr_utility.trace_on('Y','RL1MAG');
397
398 l_prev_payact := -1;
399 hr_utility.set_location( 'pay_ca_rl1_mag.create_assignement_act',10);
400
401 get_report_parameters(
402 p_pactid,
403 l_year_start,
404 l_year_end,
405 l_report_type,
406 l_business_group_id
407 );
408
409 validate_transmitter_info(p_pactid,
410 l_business_group_id,
411 l_year_end);
412
413 hr_utility.set_location( 'pay_ca_rl1_mag.create_assignement_act',20);
414
415 --IF l_report_type = 'PYRL1MAG' THEN
416
417 OPEN c_all_asg;
418 LOOP
419 FETCH c_all_asg INTO l_person_id,
420 l_assignment_id,
421 l_tax_unit_id,
422 l_effective_end_date,
423 l_assignment_action_id,
424 l_payroll_act;
425
426 hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 30);
427
428 EXIT WHEN c_all_asg%NOTFOUND;
429
430 /* Added by ssmukher for validating the
431 Quebec Identification Number */
432
433 if l_prev_payact <> l_payroll_act then
434 hr_utility.trace('The payroll action id '||l_payroll_act);
435
436 l_prev_payact := l_payroll_act;
437 l_quebec_val := get_arch_val(l_payroll_act,'CAEOY_RL1_QUEBEC_BN');
438 l_quebec_name := get_arch_val(l_payroll_act,'CAEOY_RL1_EMPLOYER_NAME');
439
440 hr_utility.trace('The Quebec Number is '||l_quebec_val);
441
442 l_quebec_no := substr(l_quebec_val ,1,10);
443
444 hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
445 hr_utility.trace('l_quebec_name ='|| l_quebec_name);
446 l_return := validate_quebec_number(l_quebec_val,l_quebec_name);
447
448 end if ;
449
450 --Create the assignment action for the record
451
452 hr_utility.trace('Assignment Fetched - ');
453 hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
454 hr_utility.trace('Person Id : '|| to_char(l_person_id));
455 hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
456 hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
457 hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 40);
458
459 SELECT pay_assignment_actions_s.nextval
460 INTO lockingactid
461 FROM dual;
462
463 hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 50);
464 hr_nonrun_asact.insact(lockingactid,
465 l_assignment_id,
466 p_pactid,
467 p_chunk,
468 l_tax_unit_id);
469
470 hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 60);
471 hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
472 hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 70);
473 hr_utility.trace('Interlock Created - ');
474 hr_utility.trace('Locking Action : '|| to_char(lockingactid));
475 hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
476 END LOOP;
477 CLOSE c_all_asg;
478
479 -- END IF;
480
481 END create_assignment_act;
482
483 function get_parameter(name in varchar2, parameter_list varchar2) return varchar2 is
484 start_ptr number;
485 end_ptr number;
486 token_val pay_payroll_actions.legislative_parameters%type;
487 par_value pay_payroll_actions.legislative_parameters%type;
488 begin
489 --
490 token_val := name||'=';
491 --
492 start_ptr := instr(parameter_list, token_val) + length(token_val);
493 end_ptr := instr(parameter_list, ' ',start_ptr);
494 --
495 /* if there is no spaces use then length of the string */
496 if end_ptr = 0 then
497 end_ptr := length(parameter_list)+1;
498 end if;
499 --
500 /* Did we find the token */
501 if instr(parameter_list, token_val) = 0 then
502 par_value := NULL;
503 else
504 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
505 end if;
506 --
507 return par_value;
508 --
509 end get_parameter;
510
511 /* Added by ssmukher for Bug 3353115 */
512 /* The check digit calculated by the method below
513 must be the same as the 10th digit of the
514 identification number or the number is invalid.
515
516 Example of the modulus 11 method:
517 The identification number is 2345678908.
518 Use the first nine digits to validate the identification number.
519 The tenth digit is the check digit (in this case 8).
520
521 Formula:
522 Beginning with the ninth digit (extreme right), multiply each digit by the
523 factor indicated.
524 The factors form a circular sequence of the values 2 through 7, beginning on
525 the right.
526 First nine digits of the identification number 2 3 4 5 6 7 8 9 0
527 Multiply each digit by the factor indicated. x 4 x 3 x 2 x 7 x 6 x 5 x 4 x 3
528 x 2
529 Results 8 9 8 35 36 35 32 27 0
530 Add the results
531 (8 + 9 + 8 + 35 + 36 + 35 + 32 + 27 + 0). = 190
532 Divide the result by 11 (190/11). = 17 remainder 3
533 If the remainder is 0, the check digit is 1. If the remainder is 1, the check
534 digit is 0.
535 For any other remainder obtained, the check digit is the difference between
536 11 and that remainder.
537 Subtract the remainder obtained from 11 (11 - 3) = 8
538 */
539
540 FUNCTION validate_quebec_number (p_quebec_no IN VARCHAR2,p_qin_name varchar2)
541 RETURN NUMBER IS
542
543 l_quebec NUMBER;
544 l_rem NUMBER;
545 i NUMBER;
546 l_max NUMBER;
547 l_total NUMBER;
548 l_min NUMBER;
549 l_modulus NUMBER;
550 l_chk_digit NUMBER;
551 l_act_chk_number NUMBER;
552
553 BEGIN
554 i := 1;
555 l_min := 2;
556 l_max := 7;
557 l_total := 0;
558
559 l_act_chk_number := to_number(substr(p_quebec_no,10,1));
560 hr_utility.trace('here1');
561 if TRANSLATE(p_quebec_no,'0123456789','9999999999') = '9999999999RS9999' then
562 l_quebec := to_number(substr(p_quebec_no,1,9));
563 loop
564 if i > 9 then
565 exit;
566 end if;
567
568 if l_min > l_max then
569 l_min := 2;
570 end if;
571
572 l_rem := mod(l_quebec,10);
573 l_total := l_total + (l_min * l_rem);
574 l_min := l_min + 1;
575 l_quebec := ((l_quebec - l_rem)/10);
576 i := i+ 1;
577
578 end loop;
579
580 l_modulus := mod(l_total, 11);
581 if l_modulus = 0 then
582 l_chk_digit := 1;
583 elsif l_modulus = 1 then
584 l_chk_digit := 0;
585 else
586 l_chk_digit := 11 - l_modulus;
587 end if;
588
589 if l_chk_digit <> l_act_chk_number then
590 hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
591 hr_utility.set_message_token('PRE_NAME',p_qin_name);
592 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
593 pay_core_utils.push_token('PRE_NAME',p_qin_name);
594 hr_utility.raise_error;
595 end if;
596 else
597
598 hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
599 hr_utility.set_message_token('PRE_NAME',p_qin_name);
600 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
601 pay_core_utils.push_token('PRE_NAME',p_qin_name);
602 hr_utility.raise_error;
603
604 end if;
605
606 return l_chk_digit;
607
608 END;
609
610 FUNCTION get_arch_val(p_context_id IN NUMBER,
611 p_user_name IN VARCHAR2)
612 RETURN varchar2 IS
613
614 cursor cur_archive (b_context_id NUMBER, b_user_name VARCHAR2) is
615 select fai.value
616 from ff_archive_items fai,
617 ff_database_items fdi
618 where fai.user_entity_id = fdi.user_entity_id
619 and fai.context1 = b_context_id
620 and fdi.user_name = b_user_name;
621
622 l_return VARCHAR2(240);
623
624 BEGIN
625 open cur_archive(p_context_id,p_user_name);
626 fetch cur_archive into l_return;
627 close cur_archive;
628
629 return (l_return);
630 END;
631
632 PROCEDURE xml_transmitter_record IS
633 BEGIN
634
635 DECLARE
636
637 l_final_xml CLOB;
638 l_final_xml_string VARCHAR2(32000);
639 l_is_temp_final_xml VARCHAR2(2);
640
641 TYPE transmitter_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
642
643 tab_transmitter transmitter_info;
644
645 lAnnee NUMBER;
646 lTypeEnvoi NUMBER;
647 lProvenance NUMBER;
648 lNo NUMBER;
649 lType NUMBER;
650 lNom1 NUMBER;
651 lNom2 NUMBER;
652 lLigne1 NUMBER;
653 lLigne2 NUMBER;
654 lVille NUMBER;
655 lProvince NUMBER;
656 lCodePostal NUMBER;
657 lNom NUMBER;
658 lIndRegional NUMBER;
659 lTel NUMBER;
660 lPosteTel NUMBER;
661 lLangue NUMBER;
662 lANom NUMBER;
663 lAIndRegional NUMBER;
664 lATel NUMBER;
665 lAPosteTel NUMBER;
666 lALangue NUMBER;
667 lNoConcepteur NUMBER;
668
669 EOL VARCHAR2(5);
670 l_address_line hr_locations.address_line_1%TYPE;
671 l_contact VARCHAR2(60);
672 l_transmitter_name VARCHAR2(100);
673 l_context1 ff_archive_items.context1%TYPE;
674 l_taxation_year VARCHAR2(4);
675 l_return VARCHAR2(60);
676 l_certification_no VARCHAR2(50);
677
678 BEGIN
679
680 hr_utility.trace('XML Transmitter');
681
682 SELECT
683 fnd_global.local_chr(13) || fnd_global.local_chr(10)
684 INTO EOL
685 FROM dual;
686
687 lAnnee := 1;
688 lTypeEnvoi := 2;
689 lProvenance := 3;
690 lNo := 4;
691 lType := 5;
692 lNom1 := 6;
693 lNom2 := 7;
694 lLigne1 := 8;
695 lLigne2 := 9;
696 lVille := 10;
697 lProvince := 11;
698 lCodePostal := 12;
699 lNom := 13;
700 lIndRegional := 14;
701 lTel := 15;
702 lPosteTel := 16;
703 lLangue := 17;
704 lANom := 18;
705 lAIndRegional := 19;
706 lATel := 20;
707 lAPosteTel := 21;
708 lALangue := 22;
709 lNoConcepteur := 23;
710
711 l_taxation_year
712 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
713 l_context1
714 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
715
716 hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
717 hr_utility.trace('XML Transmitter: l_context1 = ' || to_char(l_context1));
718
719
720 -- Annee
721 tab_transmitter(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' ||EOL;
722 hr_utility.trace('tab_transmitter(lAnnee) = ' || tab_transmitter(lAnnee));
723
724 -- TypeEnvoi
725 tab_transmitter(lTypeEnvoi) := '<TypeEnvoi>' ||
726 pay_ca_archive_utils.get_archive_value(l_context1,
727 'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE') || '</TypeEnvoi>' || EOL;
728 hr_utility.trace('tab_transmitter(lTypeEnvoi) = ' ||
729 tab_transmitter(lTypeEnvoi));
730
731 tab_transmitter(lProvenance) := '<Provenance>' ||
732 pay_ca_archive_utils.get_archive_value(l_context1,
733 'CAEOY_RL1_SOURCE_OF_SLIPS') || '</Provenance>' || EOL;
734
735 hr_utility.trace('tab_transmitter(lProvenance) = ' || tab_transmitter(lProvenance));
736
737 tab_transmitter(lNo) := '<No>' ||
738 pay_ca_archive_utils.get_archive_value(l_context1,
739 'CAEOY_RL1_TRANSMITTER_NUMBER') || '</No>' || EOL;
740
741 hr_utility.trace('tab_transmitter(lNo) = ' || tab_transmitter(lNo));
742
743 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
744 'CAEOY_RL1_TRANSMITTER_TYPE');
745 IF l_return IS NOT NULL THEN
746 tab_transmitter(lType) := '<Type>' || l_return || '</Type>' || EOL;
747 ELSE
748 tab_transmitter(lType) := NULL;
749 END IF;
750
751 hr_utility.trace('tab_transmitter(lType) = ' || tab_transmitter(lType));
752
753 l_transmitter_name := pay_ca_archive_utils.get_archive_value(l_context1,
754 'CAEOY_RL1_TRANSMITTER_NAME');
755
756 tab_transmitter(lNom1) := '<Nom1>' ||
757 convert_special_char(substr(l_transmitter_name,1,30)) || '</Nom1>' || EOL;
758
759 hr_utility.trace('tab_transmitter(lNom1) = ' || tab_transmitter(lNom1));
760
761 l_return := substr(l_transmitter_name,31,30);
762 IF l_return IS NOT NULL THEN
763 tab_transmitter(lNom2) := '<Nom2>' || convert_special_char(l_return) || '</Nom2>' || EOL;
764 ELSE
765 tab_transmitter(lNom2) := NULL;
766 END IF;
767
768 hr_utility.trace('tab_transmitter(lNom2) = ' || tab_transmitter(lNom2));
769
770 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
771 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
772
773 tab_transmitter(lLigne1) := '<Ligne1>' ||
774 convert_special_char(substr(l_address_line,1,30)) || '</Ligne1>' || EOL;
775
776 hr_utility.trace('tab_transmitter(lLigne1) = ' || tab_transmitter(lLigne1));
777
778 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
779 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE2');
780
781 IF l_address_line IS NOT NULL THEN
782 tab_transmitter(lLigne2) := '<Ligne2>' ||
783 convert_special_char(substr(l_address_line,1,30)) || '</Ligne2>' || EOL;
784 ELSE
785 tab_transmitter(lLigne2) := NULL;
786 END IF;
787
788 hr_utility.trace('tab_transmitter(lLigne2) = ' || tab_transmitter(lLigne2));
789
790 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
791 'CAEOY_RL1_TRANSMITTER_CITY');
792
793 IF l_address_line IS NOT NULL THEN
794 tab_transmitter(lVille) := '<Ville>' ||
795 convert_special_char(substr(l_address_line,1,30)) || '</Ville>' || EOL;
796 ELSE
797 tab_transmitter(lVille) := NULL;
798 END IF;
799
800 hr_utility.trace('tab_transmitter(lVille) = ' || tab_transmitter(lVille));
801
802 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
803 'CAEOY_RL1_TRANSMITTER_PROVINCE');
804
805 tab_transmitter(lProvince) := '<Province>' ||
806 SUBSTR(hr_general.decode_lookup(
807 'CA_PROVINCE',l_address_line),1,20) || '</Province>' || EOL;
808
809 hr_utility.trace('tab_transmitter(lProvince) = ' || tab_transmitter(lProvince));
810
811 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
812 'CAEOY_RL1_TRANSMITTER_POSTAL_CODE');
813
814 tab_transmitter(lCodePostal) := '<CodePostal>' ||
815 substr(replace(l_address_line,' '),1,6) || '</CodePostal>' || EOL;
816
817 hr_utility.trace('tab_transmitter(lCodePostal) = ' || tab_transmitter(lCodePostal));
818
819 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
820 'CAEOY_RL1_TECHNICAL_CONTACT_NAME');
821
822 IF l_contact IS NOT NULL THEN
823 tab_transmitter(lNom) := '<Nom>' ||
824 substr(l_contact,1,30) || '</Nom>' || EOL;
825 ELSE
826 tab_transmitter(lNom) := NULL;
827 END IF;
828
829 hr_utility.trace('tab_transmitter(lNom) = ' || tab_transmitter(lNom));
830
831 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
832 'CAEOY_RL1_TECHNICAL_CONTACT_AREA_CODE');
833
834 IF l_contact IS NOT NULL THEN
835 tab_transmitter(lIndRegional) := '<IndRegional>' ||
836 l_contact || '</IndRegional>' || EOL;
837 ELSE
838 tab_transmitter(lIndRegional) := NULL;
839 END IF;
840
841 hr_utility.trace('tab_transmitter(lIndRegional) = ' || tab_transmitter(lIndRegional));
842
843 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
844 'CAEOY_RL1_TECHNICAL_CONTACT_PHONE');
845 IF l_contact IS NOT NULL THEN
846 l_contact := substr(l_contact,1,3) || '-' || substr(l_contact,4,4);
847 tab_transmitter(lTel) := '<Tel>' || l_contact || '</Tel>' || EOL;
848 ELSE
849 tab_transmitter(lTel) := NULL;
850 END IF;
851 hr_utility.trace('tab_transmitter(lTel) = ' || tab_transmitter(lTel));
852
853 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
854 'CAEOY_RL1_TECHNICAL_CONTACT_EXTENSION');
855
856 IF l_contact IS NOT NULL THEN
857 tab_transmitter(lPosteTel) := '<PosteTel>' || l_contact ||
858 '</PosteTel>' || EOL;
859 ELSE
860 tab_transmitter(lPosteTel) := NULL;
861 END IF;
862 hr_utility.trace('tab_transmitter(lPosteTel) = ' ||
863 tab_transmitter(lPosteTel));
864
865 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
866 'CAEOY_RL1_TECHNICAL_CONTACT_LANGUAGE');
867
868 IF l_contact IS NOT NULL THEN
869 tab_transmitter(lLangue) := '<Langue>' ||l_contact || '</Langue>' || EOL;
870 ELSE
871 tab_transmitter(lLangue) := NULL;
872 END IF;
873
874 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
875 'CAEOY_RL1_ACCOUNTING_CONTACT_NAME');
876
877 IF l_contact IS NOT NULL THEN
878 tab_transmitter(lANom) := '<Nom>' ||
879 convert_special_char(substr(l_contact,1,30)) || '</Nom>' || EOL;
880 ELSE
881 tab_transmitter(lANom) := NULL;
882 END IF;
883 hr_utility.trace('tab_transmitter(lANom) = ' || tab_transmitter(lANom));
884
885 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
886 'CAEOY_RL1_ACCOUNTING_CONTACT_AREA_CODE');
887
888 IF l_contact IS NOT NULL THEN
889 tab_transmitter(lAIndRegional) := '<IndRegional>' || l_contact ||
890 '</IndRegional>' || EOL;
891 ELSE
892 tab_transmitter(lAIndRegional) := NULL;
893 END IF;
894 hr_utility.trace('tab_transmitter(lAIndRegional) = ' || tab_transmitter(lAIndRegional));
895
896 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
897 'CAEOY_RL1_ACCOUNTING_CONTACT_PHONE');
898
899 IF l_contact IS NOT NULL THEN
900 l_contact := substr(l_contact,1,3) || '-' || substr(l_contact,4,4);
901 tab_transmitter(lATel) := '<Tel>' || l_contact || '</Tel>' || EOL;
902 ELSE
903 tab_transmitter(lATel) := NULL;
904 END IF;
905 hr_utility.trace('tab_transmitter(lATel) = ' || tab_transmitter(lATel));
906
907 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
908 'CAEOY_RL1_ACCOUNTING_CONTACT_EXTENSION');
909
910 IF l_contact IS NOT NULL THEN
911 tab_transmitter(lAPosteTel) := '<PosteTel>' || l_contact ||
912 '</PosteTel>' || EOL;
913 ELSE
914 tab_transmitter(lAPosteTel) := NULL;
915 END IF;
916 hr_utility.trace('tab_transmitter(lAPosteTel) = ' ||
917 tab_transmitter(lAPosteTel));
918
919 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
920 'CAEOY_RL1_ACCOUNTING_CONTACT_LANGUAGE');
921 IF l_contact IS NOT NULL THEN
922 tab_transmitter(lALangue) := '<Langue>' || l_contact ||
923 '</Langue>' || EOL;
924 ELSE
925 tab_transmitter(lALangue) := NULL;
926 END IF;
927
928 hr_utility.trace('tab_transmitter(lALangue) = ' || tab_transmitter(lALangue));
929
930 -- For bug 6738509
931 if(l_taxation_year = '2006') then
932 l_certification_no := 'RQ-06-01-098';
933 else
934 l_certification_no := 'RQ-07-01-146';
935 end if;
936 -- End bug 6738509
937
938 tab_transmitter(lNoConcepteur) :=
939 '<NoCertification>'|| convert_special_char(l_certification_no)
940 ||'</NoCertification>'|| EOL;
941
942 l_final_xml_string :=
943 '<Transmission VersionSchema="2007.1.1" ' ||
944 'pxmlns="http://www.mrq.gouv.qc.ca/T5">' || EOL ||
945 '<P>' || EOL ||
946 tab_transmitter(lAnnee) ||
947 tab_transmitter(lTypeEnvoi) ||
948 tab_transmitter(lProvenance) || '<Preparateur>' || EOL ||
949 tab_transmitter(lNo) ||
950 tab_transmitter(lType) ||
951 tab_transmitter(lNom1) ||
952 tab_transmitter(lNom2) || '<Adresse>' || EOL ||
953 tab_transmitter(lLigne1) ||
954 tab_transmitter(lLigne2) ||
955 tab_transmitter(lVille) ||
956 tab_transmitter(lProvince) ||
957 tab_transmitter(lCodePostal) || '</Adresse>' || EOL ||
958 '</Preparateur>' || EOL || '<Informatique>' || EOL ||
959 tab_transmitter(lNom) ||
960 tab_transmitter(lIndRegional) ||
961 tab_transmitter(lTel) ||
962 tab_transmitter(lPosteTel) ||
963 tab_transmitter(lLangue) || '</Informatique>' || EOL ||
964 '<Comptabilite>' || EOL ||
965 tab_transmitter(lANom) ||
966 tab_transmitter(lAIndRegional) ||
967 tab_transmitter(lATel) ||
968 tab_transmitter(lAPosteTel) ||
969 tab_transmitter(lALangue) || '</Comptabilite>' || EOL ||
970 tab_transmitter(lNoConcepteur) ||
971 '</P>' || EOL;
972
973 hr_utility.trace('xml_transmitter l_final_xml_string = ' ||
974 l_final_xml_string);
975 pay_core_files.write_to_magtape_lob(l_final_xml_string);
976
977 END;
978 END xml_transmitter_record;
979
980 PROCEDURE end_of_file is
981 BEGIN
982
983 DECLARE
984
985 l_final_xml CLOB;
986 l_final_xml_string VARCHAR2(32000);
987 l_is_temp_final_xml VARCHAR2(2);
988
989 BEGIN
990
991 l_final_xml_string := '</Transmission>';
992
993 hr_utility.trace('end_of_file l_final_xml_string = '
994 || l_final_xml_string );
995 pay_core_files.write_to_magtape_lob(l_final_xml_string);
996
997 END;
998
999 END;
1000
1001 /***************************************************************/
1002
1003 /*************************** *******************************/
1004
1005 PROCEDURE xml_employee_record IS
1006 BEGIN
1007
1008 DECLARE
1009 /****************************************************/
1010 l_payroll_actid NUMBER;
1011 l_rep_type VARCHAR2(30);
1012 l_business_group_id NUMBER;
1013 l_year_start DATE;
1014 l_year_end DATE;
1015 l_legislative_param pay_payroll_actions.legislative_parameters%type;
1016 l_arch_asg_actid NUMBER;
1017 l_arch_pay_actid NUMBER;
1018 -- l_asg_id NUMBER;
1019 l_emplyr_final1 VARCHAR2(5000);
1020 l_emplyr_final2 VARCHAR2(5000);
1021 l_emplyr_final3 VARCHAR2(5000);
1022 --l_boxo_amount_cnt NUMBER :=0;
1023 --l_xml_boxo_amount VARCHAR2(200);
1024 --l_flag_seeattch BOOLEAN;
1025 -- l_footnote VARCHAR2(500);
1026 l_footnote_boxo1 VARCHAR2(1000);
1027 l_footnote_boxo2 VARCHAR2(1000);
1028 l_footnote_boxo3 VARCHAR2(1000);
1029 l_person_id1 NUMBER;
1030 l_session_date DATE;
1031 lForm_number NUMBER;
1032 l_neg_bal_exists BOOlEAN := FALSE;
1033
1034
1035 CURSOR c_get_payroll_asg_actid(p_assg_actid NUMBER) IS
1036 SELECT
1037 to_number(substr(paa.serial_number,3,14)) asgactid , --archiver assignment action id
1038 to_number(substr(paa.serial_number,17,14)) payactid, --archiver payroll action id
1039 paa.assignment_id asgid
1040 FROM
1041 pay_assignment_actions paa
1042 WHERE paa.assignment_action_id = p_assg_actid;
1043
1044 cursor c_province( p_arch_asact_id number ) is
1045 select fai.value
1046 from ff_archive_items fai,
1047 ff_database_items fdi
1048 where fai.user_entity_id = fdi.user_entity_id
1049 and fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT'
1050 and fai.context1 =p_arch_asact_id;
1051
1052 l_page_break VARCHAR2(50);
1053 l_final_xml_string1 VARCHAR2(32000);
1054 l_final_xml_string2 VARCHAR2(32000);
1055 l_final_xml_string3 VARCHAR2(32000);
1056 k NUMBER;
1057 addr pay_ca_rl1_reg.primaryaddress;
1058 /********************** ************************/
1059 l_final_xml CLOB;
1060 l_final_xml_string VARCHAR2(32000);
1061 l_is_temp_final_xml VARCHAR2(2);
1062
1063 CURSOR cur_parameters(p_mag_asg_action_id NUMBER) IS
1064 SELECT
1065 pai.locked_action_id, -- Archiver asg_action_id
1066 paa.assignment_id,
1067 pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id),
1068 -- date_earned
1069 fai.value -- Jurisdiction
1070 FROM
1071 ff_archive_items fai,
1072 ff_database_items fdi,
1073 per_all_people_f ppf,
1074 per_all_assignments_f paf,
1075 pay_action_interlocks pai,
1076 pay_assignment_actions paa,
1077 pay_payroll_actions ppa,
1078 pay_assignment_actions paa_arch
1079 WHERE
1080 paa.assignment_action_id = p_mag_asg_action_id AND
1081 ppa.payroll_action_id = paa.payroll_action_id AND
1082 pai.locking_action_id = paa.assignment_action_id AND
1083 paf.assignment_id = paa.assignment_id AND
1084 ppf.person_id = paf.person_id AND
1085 pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
1086 between
1087 paf.effective_start_date and paf.effective_end_date AND
1088 pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
1089 between
1090 ppf.effective_start_date and ppf.effective_end_date AND
1091 fai.context1 = pai.locked_action_id AND
1092 fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT' AND
1093 fai.user_entity_id = fdi.user_entity_id AND
1094 paa_arch.assignment_action_id = fai.context1 AND
1095 --paa_arch.payroll_action_id =
1096 -- to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')) AND
1097 paa_arch.assignment_action_id = pai.locked_action_id
1098 ORDER BY
1099 ppf.last_name,ppf.first_name,ppf.middle_names;
1100
1101 l_mag_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
1102 l_arch_action_id pay_assignment_actions.assignment_action_id%TYPE;
1103 l_asg_id per_assignments_f.assignment_id%TYPE;
1104 l_date_earned DATE;
1105 l_province VARCHAR2(30);
1106 l_O_AutreRevenu VARCHAR2(1000);
1107
1108 TYPE employee_info IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
1109
1110 tab_employee employee_info;
1111 tab_xml_employee employee_info; --
1112
1113 lAnnee NUMBER;
1114 lNoReleve NUMBER;
1115 lNAS NUMBER;
1116 --SIN1 NUMBER;
1117 --SIN2 NUMBER;
1118 --SIN3 NUMBER;
1119 lNo NUMBER;
1120 lNomFamille NUMBER;
1121 lPrenom NUMBER;
1122 lInitiale NUMBER;
1123 lLigne1 NUMBER;
1124 lLigne2 NUMBER;
1125 lVille NUMBER;
1126 lProvince NUMBER;
1127 lCodePostal NUMBER;
1128 lA_RevenuEmploi NUMBER;
1129 lB_CotisationRRQ NUMBER;
1130 lC_CotisationAssEmploi NUMBER;
1131 lD_CotisationRPA NUMBER;
1132 lE_ImpotQue NUMBER;
1133 lF_CotisationSyndicale NUMBER;
1134 lG_SalaireAdmisRRQ NUMBER;
1135 lV_NourritureLogement NUMBER;
1136 lW_Vehicule NUMBER;
1137 lJ_RegimeAssMaladie NUMBER;
1138 lK_Voyage NUMBER;
1139 lL_AutreAvantage NUMBER;
1140 lM_Commission NUMBER;
1141 lN_DonBienfaisance NUMBER;
1142 lO_AutreRevenu NUMBER;
1143 l_SourceCase NUMBER;
1144 lP_RegimeAssInterEntr NUMBER;
1145 lQ_SalaireDiffere NUMBER;
1146 lR_RevenuIndien NUMBER;
1147 lS_PourboireRecu NUMBER;
1148 lT_PourboireAttribue NUMBER;
1149 lU_RetraiteProgressive NUMBER;
1150 l_ContisationRPC NUMBER;
1151 lH_CotisationRQAP NUMBER;
1152 lI_SalaireAdmisRQAP NUMBER;
1153
1154 l_person_id per_people_f.person_id%TYPE;
1155 l_address_line1 per_addresses.address_line1%TYPE;
1156 l_address_line2 per_addresses.address_line2%TYPE;
1157 l_address_line3 per_addresses.address_line3%TYPE;
1158 l_combined_addr VARCHAR2(500);
1159 l_city per_addresses.town_or_city%TYPE;
1160 l_postal_code per_addresses.postal_code%TYPE;
1161 l_country VARCHAR2(60);
1162 l_emp_province per_addresses.region_1%TYPE;
1163 EOL VARCHAR2(5);
1164 l_taxation_year VARCHAR2(5);
1165 l_name VARCHAR2(200);
1166 l_box VARCHAR2(20);
1167 l_boxA VARCHAR2(20);
1168 l_boxB VARCHAR2(20);
1169 l_boxU VARCHAR2(20);
1170 l_boxQ VARCHAR2(20);
1171 l_return VARCHAR2(30);
1172 l_status VARCHAR2(10);
1173 l_addr_begin_tag VARCHAR2(10);
1174 l_addr_end_tag VARCHAR2(10);
1175 l_formatted_box VARCHAR2(20);
1176 l_boxO VARCHAR2(20);
1177 l_other_details VARCHAR2(32000);
1178 l_authorization_code VARCHAR2(100);
1179 l_authorization_header VARCHAR2(100);
1180
1181 lBoxA_01 NUMBER;
1182 lBoxA_02 NUMBER;
1183 lBoxA_15 NUMBER;
1184 lBoxA_16 NUMBER;
1185 lBoxA_17 NUMBER;
1186 lBoxA_18 NUMBER;
1187 lBoxA_19 NUMBER;
1188 lBoxA_25 NUMBER;
1189 lBoxA_26 NUMBER;
1190 lBoxA_27 NUMBER;
1191 lBoxD_07 NUMBER;
1192 lBoxD_08 NUMBER;
1193 lBoxD_20 NUMBER;
1194 lBoxK_11 NUMBER;
1195 lBoxO_RA NUMBER;
1196 lBoxO_RB NUMBER;
1197 lBoxO_RC NUMBER;
1198 lBoxO_RD NUMBER;
1199 lBoxO_RE NUMBER;
1200 lBoxO_RF NUMBER;
1201 lBoxO_RG NUMBER;
1202 lBoxO_RH NUMBER;
1203 lBoxO_RI NUMBER;
1204 lBoxO_RJ NUMBER;
1205 lBoxO_RK NUMBER;
1206 lBoxO_RL NUMBER;
1207 lBoxO_RL22 NUMBER;
1208 lBoxO_RL28 NUMBER;
1209 lBoxO_RM NUMBER;
1210 lBoxO_RN NUMBER;
1211 lBoxO_RO NUMBER;
1212 lBoxO_RP NUMBER;
1213 lBoxO_RQ NUMBER;
1214 lBoxO_RR NUMBER;
1215 lBoxO_RS NUMBER;
1216 lBoxO_RT NUMBER;
1217 lBoxO_RU NUMBER;
1218 lBoxO_RV NUMBER;
1219 lBoxO_RW NUMBER;
1220 lBoxQ_24 NUMBER;
1221 lBoxR_14 NUMBER;
1222 lErrorDetails NUMBER;
1223 lBoxA_29 NUMBER;
1224 lBoxA_30 NUMBER;
1225 lBoxO_RN_31 NUMBER;
1226
1227 CURSOR cur_get_meaning(p_lookup_code VARCHAR2) IS
1228 SELECT
1229 meaning
1230 FROM
1231 hr_lookups
1232 WHERE
1233 lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
1234 lookup_code = p_lookup_code;
1235
1236 l_meaning hr_lookups.meaning%TYPE;
1237 l_msg_code VARCHAR2(30);
1238 l_all_box_0 BOOLEAN;
1239 l_format_mask VARCHAR2(30);
1240 l_sequence_number NUMBER(9);
1241 l_sequence_number1 NUMBER(9);
1242 FUNCTION getnext_seq_num (p_curr_seq IN NUMBER)
1243 RETURN NUMBER IS
1244 l_seq_number number;
1245 l_check_number number;
1246 BEGIN
1247
1248 l_check_number := mod(p_curr_seq,7);
1249 hr_utility.trace('l_check_number ='|| l_check_number);
1250 l_seq_number := (p_curr_seq * 10) + l_check_number;
1251 hr_utility.trace('l_seq_number ='|| l_seq_number);
1252 return l_seq_number;
1253 END;
1254
1255 BEGIN
1256 -- hr_utility.trace_on(null,'PDF');
1257 hr_utility.trace('inside xml_employee_record');
1258 /*******************************************************************************/
1259 --l_rep_type:=pay_magtape_generic.get_parameter_value('REPORT_TYPE'); --
1260 l_payroll_actid
1261 := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1262 hr_utility.trace('l_payroll_actid='||l_payroll_actid);
1263 SELECT ppa.report_type
1264 INTO l_rep_type
1265 from pay_payroll_actions ppa
1266 where payroll_action_id=l_payroll_actid;
1267 hr_utility.trace('report_type='||l_rep_type);
1268 /******************************* ************************************/
1269
1270
1271 hr_utility.trace('XML Employee');
1272 l_status := 'Success';
1273 l_all_box_0 := TRUE;
1274 l_format_mask := '99999999999999990.99';
1275
1276 SELECT
1277 fnd_global.local_chr(13) || fnd_global.local_chr(10)
1278 INTO EOL
1279 FROM dual;
1280
1281 lAnnee := 1;
1282 lNoReleve := 2;
1283 lNAS := 3;
1284 lNo := 4;
1285 lNomFamille := 5;
1286 lPrenom := 6;
1287 lInitiale := 7;
1288 lLigne1 := 8;
1289 lLigne2 := 9;
1290 lVille := 10;
1291 lProvince := 11;
1292 lCodePostal := 12;
1293 lA_RevenuEmploi := 13;
1294 lB_CotisationRRQ := 14;
1295 lC_CotisationAssEmploi := 15;
1296 lD_CotisationRPA := 16;
1297 lE_ImpotQue := 17;
1298 lF_CotisationSyndicale := 18;
1299 lG_SalaireAdmisRRQ := 19;
1300 lV_NourritureLogement := 20;
1301 lW_Vehicule := 21;
1302 lJ_RegimeAssMaladie := 22;
1303 lK_Voyage := 23;
1304 lL_AutreAvantage := 24;
1305 lM_Commission := 25;
1306 lN_DonBienfaisance := 26;
1307 lO_AutreRevenu := 27;
1308 l_SourceCase := 28;
1309 lP_RegimeAssInterEntr := 29;
1310 lQ_SalaireDiffere := 30;
1311 lR_RevenuIndien := 31;
1312 lS_PourboireRecu := 32;
1313 lT_PourboireAttribue := 33;
1314 lU_RetraiteProgressive := 34;
1315 l_ContisationRPC := 35;
1316 lH_CotisationRQAP:=91;
1317 lI_SalaireAdmisRQAP := 92;
1318
1319 lBoxA_01 := 36;
1320 lBoxA_02 := 37;
1321 lBoxA_15 := 38;
1322 lBoxA_16 := 39;
1323 lBoxA_17 := 40;
1324 lBoxA_18 := 41;
1325 lBoxA_19 := 42;
1326 lBoxA_25 := 43;
1327 lBoxA_26 := 44;
1328 lBoxA_27 := 45;
1329 lBoxD_07 := 46;
1330 lBoxD_08 := 47;
1331 lBoxD_20 := 48;
1332
1333 lBoxO_RA := 49;
1334 lBoxO_RB := 50;
1335 lBoxO_RC := 51;
1336 lBoxO_RD := 52;
1337 lBoxO_RE := 53;
1338 lBoxO_RF := 54;
1339 lBoxO_RG := 55;
1340 lBoxO_RH := 56;
1341 lBoxO_RI := 57;
1342 lBoxO_RJ := 58;
1343 lBoxO_RK := 59;
1344 lBoxO_RL := 60;
1345 lBoxO_RL22 := 61;
1346 lBoxO_RL28 := 62;
1347 lBoxO_RM := 63;
1348 lBoxO_RN := 64;
1349 lBoxO_RO := 65;
1350 lBoxO_RP := 66;
1351 lBoxO_RQ := 67;
1352 lBoxO_RR := 68;
1353 lBoxO_RS := 69;
1354 lBoxO_RT := 70;
1355 lBoxO_RU := 80;
1356 lBoxO_RV := 81;
1357 lBoxO_RW := 82;
1358 lBoxQ_24 := 83;
1359 lBoxR_14 := 84;
1360 lBoxK_11 := 85;
1361 lErrorDetails := 86;
1362 lBoxA_29 := 87;
1363 lBoxA_30 := 88;
1364 lBoxO_RN_31 := 89;
1365 lForm_number :=90; --
1366 --SIN1 :=93;
1367 --SIN2 :=94;
1368 --SIN3 :=95;
1369
1370 l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value
1371 ('TRANSFER_ACT_ID'));
1372
1373 hr_utility.trace('XML Employee: l_mag_asg_action_id = '
1374 || to_char(l_mag_asg_action_id));
1375
1376 /**********************************************************************/
1377 IF l_rep_type <> 'RL1PAPERPDF' THEN
1378 OPEN cur_parameters(l_mag_asg_action_id);
1379 FETCH cur_parameters
1380 INTO
1381 l_arch_action_id,
1382 l_asg_id,
1383 l_date_earned,
1384 l_province;
1385 CLOSE cur_parameters;
1386 ELSE
1387 open c_get_payroll_asg_actid(l_mag_asg_action_id);
1388 fetch c_get_payroll_asg_actid
1389 into l_arch_action_id,
1390 l_arch_pay_actid,
1391 l_asg_id;
1392 close c_get_payroll_asg_actid;
1393 -- l_arch_action_id := l_arch_asg_actid;
1394
1395 open c_province(l_arch_action_id);
1396 fetch c_province into l_province;
1397 close c_province;
1398 END IF;
1399 /**************************************** ******************/
1400
1401 hr_utility.trace('XML Employee: l_arch_action_id = '
1402 || to_char(l_arch_action_id));
1403 hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1404 hr_utility.trace('XML Employee: l_date_earned = '
1405 || to_char(l_date_earned));
1406 hr_utility.trace('XML Employee: l_province = ' || l_province);
1407
1408 l_taxation_year
1409 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
1410
1411 l_authorization_header := 'No d''autorisation :';
1412
1413 if( pay_ca_archive_utils.get_archive_value(l_arch_pay_actid, 'CAEOY_TAXATION_YEAR')='2006' ) then
1414 l_authorization_code := 'FS-06-01-103';
1415 else
1416 l_authorization_code := 'FS-07-01-107'; --Bug 6747916
1417 end if;
1418
1419 --Annee
1420 tab_employee(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' || EOL;
1421 hr_utility.trace('tab_employee(lAnnee) = ' || tab_employee(lAnnee));
1422 --NoReleve
1423 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1424 'CAEOY_RL1_SLIP_NUMBER');
1425
1426 IF l_return IS NULL THEN
1427 l_status := 'Failed';
1428 tab_employee(lNoReleve) := NULL;
1429 tab_xml_employee(lNoReleve) := NULL; --
1430 ELSE
1431 tab_employee(lNoReleve) := '<NoReleve>' || l_return ||
1432 '</NoReleve>' || EOL;
1433 tab_xml_employee(lNoReleve) := l_return; --
1434 END IF;
1435 hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve));
1436 hr_utility.trace('tab_xml_employee(lNoReleve) = ' || tab_xml_employee(lNoReleve)); --
1437
1438 -- NAS
1439 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1440 'CAEOY_EMPLOYEE_SIN');
1441
1442 IF l_return IS NOT NULL THEN
1443 tab_employee(lNAS) := '<NAS>' || l_return || '</NAS>' || EOL;
1444 tab_xml_employee(lNAS) := l_return; --
1445
1446 ELSE
1447 l_Status := 'Failed';
1448 l_msg_code := 'SIN';
1449 tab_employee(lNAS) := NULL;
1450 tab_xml_employee(lNAS) := NULL; --
1451 END IF;
1452 hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
1453 hr_utility.trace('tab_xml_employee(lNAS) = ' || tab_xml_employee(lNAS));
1454
1455 -- No
1456 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1457 'CAEOY_EMPLOYEE_NUMBER');
1458 IF l_return IS NOT NULL THEN
1459 tab_employee(lNo) := '<No>' || convert_special_char(l_return) || '</No>' || EOL;
1460 tab_xml_employee(lNo) := convert_special_char(l_return); --
1461 ELSE
1462 tab_employee(lNo) := NULL;
1463 tab_xml_employee(lNo) := NULL; --
1464 END IF;
1465 hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
1466 hr_utility.trace('tab_xml_employee(lNo) = ' || tab_xml_employee(lNo)); --
1467
1468 -- NomFamille
1469 l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1470 'CAEOY_EMPLOYEE_LAST_NAME');
1471 tab_employee(lNomFamille) := '<NomFamille>' ||
1472 convert_special_char(substr(l_name,1,30)) || '</NomFamille>' || EOL;
1473 tab_xml_employee(lNomFamille) := convert_special_char(substr(l_name,1,20)); --
1474 hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
1475 hr_utility.trace('tab_xml_employee(lNomFamille) = ' || tab_xml_employee(lNomFamille)); --
1476
1477 -- Prenom
1478 l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1479 'CAEOY_EMPLOYEE_FIRST_NAME');
1480 IF l_name is NOT NULL THEN
1481 tab_employee(lPrenom) := '<Prenom>' || convert_special_char(substr(l_name,1,30))
1482 || '</Prenom>' || EOL;
1483 tab_xml_employee(lPrenom) := convert_special_char(substr(l_name,1,20)) ; --
1484
1485 ELSE
1486 l_status := 'Failed';
1487 l_msg_code := 'MISSING_EMP_FIRST_NAME';
1488 tab_employee(lPrenom) := NULL;
1489 tab_xml_employee(lPrenom) := NULL ; --
1490 END IF;
1491 hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
1492 hr_utility.trace('tab_xml_employee(lPrenom) = ' || tab_xml_employee(lPrenom)); --
1493
1494 -- Initiale
1495 l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1496 'CAEOY_EMPLOYEE_INITIAL');
1497 IF l_name is NOT NULL THEN
1498 tab_employee(lInitiale) := '<Initiale>' || substr(l_name,1,1)
1499 || '</Initiale>' || EOL;
1500 tab_xml_employee(lInitiale) := substr(l_name,1,1); --
1501
1502 ELSE
1503 tab_employee(lInitiale) := NULL;
1504 tab_xml_employee(lInitiale) := NULL; --
1505 END IF;
1506 hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
1507 hr_utility.trace('tab_xml_employee(lInitiale) = ' || tab_xml_employee(lInitiale)); --
1508
1509 l_person_id := to_number(pay_ca_archive_utils.get_archive_value(
1510 l_arch_action_id,
1511 'CAEOY_PERSON_ID'));
1512 /***************************************************************/
1513 if(l_rep_type='RL1PAPERPDF') then
1514 l_person_id1 := to_number(pay_ca_archive_utils.get_archive_value(
1515 l_arch_action_id,
1516 'CAEOY_PERSON_ID'));
1517
1518 select trunc(sysdate) into l_session_date from dual;
1519 hr_utility.trace('l_person_id1 ='|| l_person_id1);
1520 hr_utility.trace('l_session_date ='|| l_session_date);
1521 addr := pay_ca_rl1_reg.get_primary_address(l_person_id1, l_session_date);
1522 tab_xml_employee(lLigne1) := convert_special_char(substr(addr.addr_line_1,1,40));
1523 tab_xml_employee(lLigne2) := convert_special_char(substr(addr.addr_line_2,1,40)||' '||substr(addr.addr_line_3,1,40));
1524 tab_xml_employee(lVille) := convert_special_char(substr(addr.addr_line_4,1,40));
1525 tab_xml_employee(lForm_number) := pay_ca_archive_utils.get_archive_value(l_mag_asg_action_id, --l_arch_action_id,
1526 'CAEOY_RL1_PRE_PRINTED_FORM_NO');
1527 hr_utility.trace('tab_xml_employee(lForm_number) = ' || tab_xml_employee(lForm_number));
1528
1529
1530 tab_xml_employee(lAnnee) := pay_ca_archive_utils.get_archive_value(l_arch_pay_actid,
1531 'CAEOY_TAXATION_YEAR');
1532 hr_utility.trace('tab_xml_employee(lAnnee) = ' || tab_xml_employee(lAnnee)); --
1533 /***************************** *****************************/
1534 else /***if not paper report***/
1535 l_return := pay_ca_emp_address_dtls.get_emp_address(
1536 l_person_id,
1537 l_address_line1,
1538 l_address_line2,
1539 l_address_line3,
1540 l_city,
1541 l_postal_code,
1542 l_country,
1543 l_emp_province
1544 );
1545 -- If Address line 1 is NULL or ' ' then the employee is missing
1546 -- address information - as line 1 is mandatory in the Address form.
1547 -- Need to check data by SS transaction /API.
1548
1549 hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1550 hr_utility.trace('l_address_line1 = ' || l_address_line1);
1551 hr_utility.trace('l_address_line2 = ' || l_address_line2);
1552 hr_utility.trace('l_postal_code = ' || l_postal_code);
1553
1554 -- Address Line 1
1555 IF l_address_line1 IS NULL OR
1556 l_address_line1 = ' ' THEN
1557
1558 l_status := 'Failed';
1559 l_msg_code := 'MISSING_EMP_ADDRESS';
1560
1561 l_addr_begin_tag := NULL;
1562 tab_employee(lLigne1) := NULL;
1563 tab_employee(lLigne2) := NULL;
1564 tab_employee(lVille) := NULL;
1565 tab_employee(lProvince) := NULL;
1566 tab_employee(lCodePostal) := NULL;
1567 tab_employee(lCodePostal) := NULL;
1568 l_addr_end_tag := NULL;
1569
1570 ELSE
1571
1572 l_addr_begin_tag := '<Adresse>';
1573
1574 tab_employee(lLigne1) := '<Ligne1>' ||
1575 convert_special_char(substr(l_address_line1,1,30)) || '</Ligne1>' || EOL;
1576 hr_utility.trace('tab_employee(lLigne1) = ' || tab_employee(lLigne1));
1577
1578 -- Address Line 2
1579 IF ((l_address_line2 IS NULL OR
1580 l_address_line2 <> ' ') OR
1581 (l_address_line3 IS NULL OR
1582 l_address_line3 <> ' ')) THEN
1583 l_combined_addr := rtrim(ltrim(l_address_line2)) || rtrim(ltrim(l_address_line3));
1584 tab_employee(lLigne2) := '<Ligne2>' ||
1585 convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne2>' || EOL;
1586 ELSE
1587 tab_employee(lLigne2) := NULL;
1588 END IF;
1589 hr_utility.trace('tab_employee(lLigne2) = ' || tab_employee(lLigne2));
1590
1591 -- Ville (City)
1592 IF l_city IS NULL OR
1593 l_city <> ' ' THEN
1594 tab_employee(lVille) := '<Ville>' ||
1595 substr(l_city,1,30) || '</Ville>' || EOL;
1596 ELSE
1597 tab_employee(lVille) := NULL;
1598 END IF;
1599 hr_utility.trace('tab_employee(lVille) = ' || tab_employee(lVille));
1600
1601 -- Province
1602 IF l_emp_province IS NULL OR
1603 l_emp_province <> ' ' THEN
1604 IF l_country = 'CA' THEN
1605 tab_employee(lProvince) := '<Province>' ||
1606 SUBSTR(hr_general.decode_lookup(
1607 'CA_PROVINCE',l_emp_province),1,20) || '</Province>' || EOL;
1608 ELSIF l_country = 'US' THEN
1609 tab_employee(lProvince) := '<Province>' || l_emp_province || '</Province>' || EOL;
1610 ELSE
1611 tab_employee(lProvince) := '<Province>' || l_country || '</Province>' || EOL;
1612 END IF;
1613 ELSE
1614 tab_employee(lProvince) := NULL;
1615 END IF;
1616 hr_utility.trace('tab_employee(lProvince) = ' || tab_employee(lProvince));
1617
1618 -- Postal Code
1619 IF l_postal_code IS NULL OR
1620 l_postal_code <> ' ' THEN
1621 tab_employee(lCodePostal) := '<CodePostal>' ||
1622 substr(replace(l_postal_code,' '),1,6) || '</CodePostal>' || EOL;
1623 ELSE
1624 tab_employee(lCodePostal) := NULL;
1625 END IF;
1626 hr_utility.trace('tab_employee(lCodePostal) = ' || tab_employee(lCodePostal));
1627 l_addr_end_tag := '</Adresse>';
1628
1629 END IF;
1630
1631 end if; /***************end of address******/ --
1632 -- Summ (Box A)
1633
1634 l_box := pay_ca_archive_utils.get_archive_value(
1635 l_arch_action_id,
1636 l_province,
1637 'JURISDICTION_CODE',
1638 'CAEOY_GROSS_EARNINGS_PER_JD_YTD');
1639
1640 IF TO_NUMBER(l_box) > 9999999.99 THEN
1641 l_status := 'Failed';
1642 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1643 END IF;
1644
1645 IF l_box IS NOT NULL AND
1646 to_number(l_box) <> 0 THEN
1647
1648 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1649 INTO l_formatted_box
1650 FROM dual;
1651 tab_employee(lA_RevenuEmploi) := '<A_RevenuEmploi>' || l_formatted_box ||
1652 '</A_RevenuEmploi>' || EOL;
1653 tab_xml_employee(lA_RevenuEmploi) := l_formatted_box; --
1654 l_all_box_0 := FALSE;
1655 l_BoxA := l_formatted_box;
1656 ELSE
1657 tab_employee(lA_RevenuEmploi) := NULL;
1658 tab_xml_employee(lA_RevenuEmploi) := NULL; --
1659 END IF;
1660 hr_utility.trace('tab_employee(lA_RevenuEmploi) = ' || tab_employee(lA_RevenuEmploi));
1661 hr_utility.trace('tab_xml_employee(lA_RevenuEmploi) = ' || tab_xml_employee(lA_RevenuEmploi));
1662
1663 -- Summ (Box B)
1664 l_box := pay_ca_archive_utils.get_archive_value(
1665 l_arch_action_id,
1666 l_province,
1667 'JURISDICTION_CODE',
1668 'CAEOY_QPP_EE_WITHHELD_PER_JD_YTD');
1669
1670 IF TO_NUMBER(l_box) > 9999999.99 THEN
1671 l_status := 'Failed';
1672 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1673 END IF;
1674
1675 IF l_box IS NOT NULL AND
1676 to_number(l_box) <> 0 THEN
1677
1678 l_BoxB := l_box;
1679
1680 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1681 INTO l_formatted_box
1682 FROM dual;
1683
1684 tab_employee(lB_CotisationRRQ) := '<B_CotisationRRQ>' || l_formatted_box
1685 || '</B_CotisationRRQ>' || EOL;
1686 tab_xml_employee(lB_CotisationRRQ) := l_formatted_box; --
1687 l_all_box_0 := FALSE;
1688 ELSE
1689
1690 tab_employee(lB_CotisationRRQ) := NULL;
1691 tab_xml_employee(lB_CotisationRRQ) := NULL; --
1692
1693 END IF;
1694 hr_utility.trace('tab_employee(lB_CotisationRRQ) = ' ||
1695 tab_employee(lB_CotisationRRQ));
1696 hr_utility.trace('tab_xml_employee(lB_CotisationRRQ) = ' ||
1697 tab_xml_employee(lB_CotisationRRQ)); --
1698
1699 -- Summ (Box C)
1700 l_box := pay_ca_archive_utils.get_archive_value(
1701 l_arch_action_id,
1702 l_province,
1703 'JURISDICTION_CODE',
1704 'CAEOY_EI_EE_WITHHELD_PER_JD_YTD');
1705
1706 IF TO_NUMBER(l_box) > 9999999.99 THEN
1707 l_status := 'Failed';
1708 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1709 END IF;
1710
1711 IF l_box IS NOT NULL AND
1712 to_number(l_box) <> 0 THEN
1713
1714 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1715 INTO l_formatted_box
1716 FROM dual;
1717
1718 tab_employee(lC_CotisationAssEmploi) := '<C_CotisationAssEmploi>' ||
1719 l_formatted_box || '</C_CotisationAssEmploi>' || EOL;
1720 tab_xml_employee(lC_CotisationAssEmploi) := l_formatted_box; --
1721 l_all_box_0 := FALSE;
1722 ELSE
1723 tab_employee(lC_CotisationAssEmploi) := NULL;
1724 tab_xml_employee(lC_CotisationAssEmploi) := NULL; --
1725 END IF;
1726
1727 hr_utility.trace('tab_employee(lC_CotisationAssEmploi) = ' ||
1728 tab_employee(lC_CotisationAssEmploi));
1729
1730 hr_utility.trace('tab_xml_employee(lC_CotisationAssEmploi) = ' ||
1731 tab_xml_employee(lC_CotisationAssEmploi)); --
1732 -- Summ (Box D)
1733 l_box := pay_ca_archive_utils.get_archive_value(
1734 l_arch_action_id,
1735 l_province,
1736 'JURISDICTION_CODE',
1737 'CAEOY_RL1_BOXD_PER_JD_YTD');
1738
1739 IF TO_NUMBER(l_box) > 9999999.99 THEN
1740 l_status := 'Failed';
1741 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1742 END IF;
1743
1744 IF l_box IS NOT NULL AND
1745 to_number(l_box) <> 0 THEN
1746
1747 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1748 INTO l_formatted_box
1749 FROM dual;
1750
1751 tab_employee(lD_CotisationRPA) := '<D_CotisationRPA>' ||
1752 l_formatted_box || '</D_CotisationRPA>' || EOL;
1753 tab_xml_employee(lD_CotisationRPA) := l_formatted_box; --
1754 l_all_box_0 := FALSE;
1755 ELSE
1756 tab_employee(lD_CotisationRPA) := NULL;
1757 tab_xml_employee(lD_CotisationRPA) := NULL; --
1758 END IF;
1759 hr_utility.trace('tab_employee(lD_CotisationRPA) = ' ||
1760 tab_employee(lD_CotisationRPA));
1761 hr_utility.trace('tab_xml_employee(lD_CotisationRPA) = ' ||
1762 tab_xml_employee(lD_CotisationRPA));
1763
1764
1765 -- (Box E)
1766
1767 l_box := pay_ca_archive_utils.get_archive_value(
1768 l_arch_action_id,
1769 l_province,
1770 'JURISDICTION_CODE',
1771 'CAEOY_PROV_WITHHELD_PER_JD_YTD');
1772
1773 IF TO_NUMBER(l_box) > 9999999.99 THEN
1774 l_status := 'Failed';
1775 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1776 END IF;
1777
1778 IF l_box IS NOT NULL AND
1779 to_number(l_box) <> 0 THEN
1780
1781 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1782 INTO l_formatted_box
1783 FROM dual;
1784
1785 tab_employee(lE_ImpotQue) := '<E_ImpotQue>' ||
1786 l_formatted_box || '</E_ImpotQue>' || EOL;
1787 tab_xml_employee(lE_ImpotQue) := l_formatted_box ; --
1788 l_all_box_0 := FALSE;
1789 ELSE
1790 tab_employee(lE_ImpotQue) := NULL ;
1791 tab_xml_employee(lE_ImpotQue) := NULL ; --
1792 END IF;
1793
1794 hr_utility.trace('tab_employee(lE_ImpotQue) = ' ||
1795 tab_employee(lE_ImpotQue));
1796 hr_utility.trace('tab_xml_employee(lE_ImpotQue) = ' ||
1797 tab_xml_employee(lE_ImpotQue)); --
1798
1799 -- (Box F)
1800 l_box := pay_ca_archive_utils.get_archive_value(
1801 l_arch_action_id,
1802 l_province,
1803 'JURISDICTION_CODE',
1804 'CAEOY_RL1_BOXF_PER_JD_YTD');
1805
1806 IF TO_NUMBER(l_box) > 9999999.99 THEN
1807 l_status := 'Failed';
1808 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1809 END IF;
1810
1811 IF l_box IS NOT NULL AND
1812 to_number(l_box) <> 0 THEN
1813
1814 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1815 INTO l_formatted_box
1816 FROM dual;
1817
1818 tab_employee(lF_CotisationSyndicale) := '<F_CotisationSyndicale>' ||
1819 l_formatted_box || '</F_CotisationSyndicale>' || EOL;
1820 tab_xml_employee(lF_CotisationSyndicale) := l_formatted_box; --
1821 l_all_box_0 := FALSE;
1822 ELSE
1823 tab_employee(lF_CotisationSyndicale) := NULL;
1824 tab_xml_employee(lF_CotisationSyndicale) := NULL; --
1825 END IF;
1826 hr_utility.trace('tab_employee(lF_CotisationSyndicale) = ' ||
1827 tab_employee(lF_CotisationSyndicale));
1828
1829 hr_utility.trace('tab_xml_employee(lF_CotisationSyndicale) = ' ||
1830 tab_xml_employee(lF_CotisationSyndicale));
1831 -- (Box Q)
1832 l_box := pay_ca_archive_utils.get_archive_value(
1833 l_arch_action_id,
1834 l_province,
1835 'JURISDICTION_CODE',
1836 'CAEOY_RL1_BOXQ_PER_JD_YTD');
1837
1838 IF TO_NUMBER(l_box) > 9999999.99 THEN
1839 l_status := 'Failed';
1840 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1841 END IF;
1842
1843 IF l_box IS NOT NULL AND
1844 to_number(l_box) <> 0 THEN
1845
1846 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1847 INTO l_formatted_box
1848 FROM dual;
1849
1850 tab_employee(lQ_SalaireDiffere) := '<Q_SalaireDiffere>' ||
1851 l_formatted_box || '</Q_SalaireDiffere>' || EOL;
1852 tab_xml_employee(lQ_SalaireDiffere) := l_formatted_box; --
1853 l_all_box_0 := FALSE;
1854 l_BoxQ := l_formatted_box;
1855 ELSE
1856 tab_employee(lQ_SalaireDiffere) := NULL;
1857 tab_xml_employee(lQ_SalaireDiffere) := NULL; --
1858 END IF;
1859 hr_utility.trace('tab_employee(lQ_SalaireDiffere) = ' ||
1860 tab_employee(lQ_SalaireDiffere));
1861
1862 hr_utility.trace('tab_xml_employee(lQ_SalaireDiffere) = ' ||
1863 tab_xml_employee(lQ_SalaireDiffere));
1864 -- (Box U)
1865 l_box := pay_ca_archive_utils.get_archive_value(
1866 l_arch_action_id,
1867 l_province,
1868 'JURISDICTION_CODE',
1869 'CAEOY_RL1_BOXU_PER_JD_YTD');
1870
1871 IF TO_NUMBER(l_box) > 9999999.99 THEN
1872
1873 l_status := 'Failed';
1874 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1875
1876 END IF;
1877
1878 IF l_box IS NOT NULL AND
1879 to_number(l_box) <> 0 THEN
1880
1881 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1882 INTO l_formatted_box
1883 FROM dual;
1884
1885 tab_employee(lU_RetraiteProgressive) := '<U_RetraiteProgressive>' ||
1886 l_formatted_box || '</U_RetraiteProgressive>' || EOL;
1887 tab_xml_employee(lU_RetraiteProgressive) := l_formatted_box; --
1888 l_all_box_0 := FALSE;
1889 l_BoxU := l_formatted_box;
1890 ELSE
1891 tab_employee(lU_RetraiteProgressive) := NULL;
1892 tab_xml_employee(lU_RetraiteProgressive) := NULL; --
1893 END IF;
1894 hr_utility.trace('tab_employee(lU_RetraiteProgressive) = ' ||
1895 tab_employee(lU_RetraiteProgressive));
1896
1897 hr_utility.trace('tab_xml_employee(lU_RetraiteProgressive) = ' ||
1898 tab_xml_employee(lU_RetraiteProgressive));
1899 -- (Box G)
1900 l_box := pay_ca_archive_utils.get_archive_value(
1901 l_arch_action_id,
1902 l_province,
1903 'JURISDICTION_CODE',
1904 'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD');
1905
1906 hr_utility.trace('l_box = ' || l_box);
1907 hr_utility.trace('l_boxA = ' || l_BoxA);
1908 hr_utility.trace('l_boxQ = ' || l_BoxQ);
1909 hr_utility.trace('l_boxU = ' || l_boxU);
1910
1911 IF l_rep_type <>'RL1PAPERPDF' THEN
1912 IF l_box IS NOT NULL THEN
1913
1914 IF TO_NUMBER(l_box) > 9999999.99 THEN
1915
1916 l_status := 'Failed';
1917 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1918
1919 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1920 INTO l_formatted_box
1921 FROM dual;
1922
1923 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
1924 l_formatted_box || '</G_SalaireAdmisRRQ>' || EOL;
1925 --tab_xml_employee(lG_SalaireAdmisRRQ) := l_formatted_box; --
1926
1927 ELSIF to_number(l_box) = 0 THEN
1928
1929 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
1930 '0.00</G_SalaireAdmisRRQ>' || EOL;
1931 --tab_xml_employee(lG_SalaireAdmisRRQ) := '0.00'; --
1932
1933 ELSIF to_number(l_box) <> (NVL(to_number(l_BoxA),0) +
1934 NVL(to_number(l_BoxQ),0) +
1935 NVL(to_number(l_BoxU),0)) THEN
1936
1937 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1938 INTO l_formatted_box
1939 FROM dual;
1940
1941 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
1942 l_formatted_box || '</G_SalaireAdmisRRQ>' || EOL;
1943
1944 --tab_xml_employee(lG_SalaireAdmisRRQ) := l_formatted_box; --
1945 l_all_box_0 := FALSE;
1946
1947 ELSIF to_number(l_box) = (NVL(to_number(l_BoxA),0) +
1948 NVL(to_number(l_BoxQ),0) +
1949 NVL(to_number(l_BoxU),0)) THEN
1950
1951 tab_employee(lG_SalaireAdmisRRQ) := NULL;
1952 --tab_xml_employee(lG_SalaireAdmisRRQ) := NULL; --
1953
1954 END IF;
1955
1956 ELSE
1957 tab_employee(lG_SalaireAdmisRRQ) := NULL;
1958 --tab_xml_employee(lG_SalaireAdmisRRQ) := NULL; --
1959 END IF;
1960 hr_utility.trace('tab_employee(lG_SalaireAdmisRRQ) = ' ||
1961 tab_employee(lG_SalaireAdmisRRQ));
1962 END IF;
1963
1964 IF l_rep_type ='RL1PAPERPDF' THEN
1965 IF l_box IS NOT NULL THEN
1966 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1967 INTO l_formatted_box
1968 FROM dual;
1969 tab_xml_employee(lG_SalaireAdmisRRQ) := l_formatted_box;
1970 IF to_number(l_box)= NVL(to_number(l_BoxA),0) THEN
1971 tab_xml_employee(lG_SalaireAdmisRRQ) := NULL;
1972 END IF;
1973 IF l_BoxA is NULL and to_number(l_box)=0 THEN
1974 tab_xml_employee(lG_SalaireAdmisRRQ) := NULL;
1975 END IF;
1976 ELSE
1977 tab_xml_employee(lG_SalaireAdmisRRQ) := NULL;
1978 END IF;
1979
1980 hr_utility.trace('tab_xml_employee(lG_SalaireAdmisRRQ) = ' ||
1981 tab_xml_employee(lG_SalaireAdmisRRQ));
1982
1983 END IF;
1984
1985
1986 -- (Box V)
1987 l_box := pay_ca_archive_utils.get_archive_value(
1988 l_arch_action_id,
1989 l_province,
1990 'JURISDICTION_CODE',
1991 'CAEOY_RL1_BOXV_PER_JD_YTD');
1992
1993 IF TO_NUMBER(l_box) > 9999999.99 THEN
1994 l_status := 'Failed';
1995 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1996 END IF;
1997
1998 IF l_box IS NOT NULL AND
1999 to_number(l_box) <> 0 THEN
2000
2001 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2002 INTO l_formatted_box
2003 FROM dual;
2004
2005 tab_employee(lV_NourritureLogement) := '<V_NourritureLogement>' ||
2006 l_formatted_box || '</V_NourritureLogement>' || EOL;
2007 tab_xml_employee(lV_NourritureLogement) := l_formatted_box; --
2008 l_all_box_0 := FALSE;
2009 ELSE
2010 tab_employee(lV_NourritureLogement) := NULL;
2011 tab_xml_employee(lV_NourritureLogement) := NULL; --
2012 END IF;
2013
2014 hr_utility.trace('tab_employee(lV_NourritureLogement) = ' ||
2015 tab_employee(lV_NourritureLogement));
2016
2017 hr_utility.trace('tab_xml_employee(lV_NourritureLogement) = ' ||
2018 tab_xml_employee(lV_NourritureLogement));
2019 -- (Box W)
2020 l_box := pay_ca_archive_utils.get_archive_value(
2021 l_arch_action_id,
2022 l_province,
2023 'JURISDICTION_CODE',
2024 'CAEOY_RL1_BOXW_PER_JD_YTD');
2025
2026 IF TO_NUMBER(l_box) > 9999999.99 THEN
2027 l_status := 'Failed';
2028 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2029 END IF;
2030
2031 IF l_box IS NOT NULL AND
2032 to_number(l_box) <> 0 THEN
2033
2034 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2035 INTO l_formatted_box
2036 FROM dual;
2037
2038 tab_employee(lW_Vehicule) := '<W_Vehicule>' ||
2039 l_formatted_box || '</W_Vehicule>' || EOL;
2040 tab_xml_employee(lW_Vehicule) := l_formatted_box; --
2041 l_all_box_0 := FALSE;
2042 ELSE
2043 tab_employee(lW_Vehicule) := NULL;
2044 tab_xml_employee(lW_Vehicule) := NULL; --
2045 END IF;
2046 hr_utility.trace('tab_employee(lW_Vehicule) = ' ||
2047 tab_employee(lW_Vehicule));
2048
2049 hr_utility.trace('tab_xml_employee(lW_Vehicule) = ' ||
2050 tab_xml_employee(lW_Vehicule));
2051
2052 --(BOX H)
2053 l_box := pay_ca_archive_utils.get_archive_value(
2054 l_arch_action_id,
2055 l_province,
2056 'JURISDICTION_CODE',
2057 'CAEOY_PPIP_EE_WITHHELD_PER_JD_YTD');
2058 IF TO_NUMBER(l_box) > 9999999.99 THEN
2059 l_status := 'Failed';
2060 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2061 END IF;
2062
2063 IF l_box IS NOT NULL AND
2064 to_number(l_box) <> 0 THEN
2065
2066 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2067 INTO l_formatted_box
2068 FROM dual;
2069
2070 tab_employee(lH_CotisationRQAP) := '<H_CotisationRQAP>' ||
2071 l_formatted_box || '</H_CotisationRQAP>' || EOL;
2072 tab_xml_employee(lH_CotisationRQAP) := l_formatted_box; --
2073 l_all_box_0 := FALSE;
2074 ELSE
2075 tab_employee(lH_CotisationRQAP) := NULL;
2076 tab_xml_employee(lH_CotisationRQAP) := NULL; --
2077 END IF;
2078 hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
2079 tab_employee(lH_CotisationRQAP));
2080
2081 hr_utility.trace('tab_xml_employee(lH_CotisationRQAP) = ' ||
2082 tab_xml_employee(lH_CotisationRQAP));
2083
2084 --(BOX I)
2085 l_box := pay_ca_archive_utils.get_archive_value(
2086 l_arch_action_id,
2087 l_province,
2088 'JURISDICTION_CODE',
2089 -- commented for bug 6623199.
2090 -- 'CAEOY_PPIP_REDUCED_SUBJECT_PER_JD_YTD');
2091 'CAEOY_PPIP_EE_TAXABLE_PER_JD_YTD');
2092 IF TO_NUMBER(l_box) > 9999999.99 THEN
2093 l_status := 'Failed';
2094 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2095 END IF;
2096
2097 IF l_box IS NOT NULL AND
2098 to_number(l_box) <> 0 THEN
2099
2100 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2101 INTO l_formatted_box
2102 FROM dual;
2103
2104 tab_employee(lI_SalaireAdmisRQAP) := '<I_SalaireAdmisRQAP>' ||
2105 l_formatted_box || '</I_SalaireAdmisRQAP>' || EOL;
2106 tab_xml_employee(lI_SalaireAdmisRQAP) := l_formatted_box; --
2107 l_all_box_0 := FALSE;
2108 ELSE
2109 tab_employee(lI_SalaireAdmisRQAP) := '<I_SalaireAdmisRQAP>' ||
2110 '0.00' || '</I_SalaireAdmisRQAP>' || EOL;
2111 tab_xml_employee(lI_SalaireAdmisRQAP) := '0.00';--
2112 END IF;
2113 hr_utility.trace('tab_employee(lI_SalaireAdmisRQAP) = ' ||
2114 tab_employee(lI_SalaireAdmisRQAP));
2115
2116 hr_utility.trace('tab_xml_employee(lI_SalaireAdmisRQAP) = ' ||
2117 tab_xml_employee(lI_SalaireAdmisRQAP));
2118
2119 -- (Box J)
2120 l_box := pay_ca_archive_utils.get_archive_value(
2121 l_arch_action_id,
2122 l_province,
2123 'JURISDICTION_CODE',
2124 'CAEOY_RL1_BOXJ_PER_JD_YTD');
2125
2126 IF TO_NUMBER(l_box) > 9999999.99 THEN
2127 l_status := 'Failed';
2128 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2129 END IF;
2130
2131 IF l_box IS NOT NULL AND
2132 to_number(l_box) <> 0 THEN
2133
2134 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2135 INTO l_formatted_box
2136 FROM dual;
2137
2138 tab_employee(lJ_RegimeAssMaladie) := '<J_RegimeAssMaladie>' ||
2139 l_formatted_box || '</J_RegimeAssMaladie>' || EOL;
2140 tab_xml_employee(lJ_RegimeAssMaladie) := l_formatted_box; --
2141 l_all_box_0 := FALSE;
2142 ELSE
2143 tab_employee(lJ_RegimeAssMaladie) := NULL;
2144 tab_xml_employee(lJ_RegimeAssMaladie) := NULL; --
2145 END IF;
2146 hr_utility.trace('tab_employee(lJ_RegimeAssMaladie) = ' ||
2147 tab_employee(lJ_RegimeAssMaladie));
2148
2149 hr_utility.trace('tab_xml_employee(lJ_RegimeAssMaladie) = ' ||
2150 tab_xml_employee(lJ_RegimeAssMaladie));
2151
2152 -- (Box K)
2153 l_box := pay_ca_archive_utils.get_archive_value(
2154 l_arch_action_id,
2155 l_province,
2156 'JURISDICTION_CODE',
2157 'CAEOY_RL1_BOXK_PER_JD_YTD');
2158
2159 IF TO_NUMBER(l_box) > 9999999.99 THEN
2160 l_status := 'Failed';
2161 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2162 END IF;
2163
2164 IF l_box IS NOT NULL AND
2165 to_number(l_box) <> 0 THEN
2166
2167 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2168 INTO l_formatted_box
2169 FROM dual;
2170
2171 tab_employee(lK_Voyage) := '<K_Voyage>' ||
2172 l_formatted_box || '</K_Voyage>' || EOL;
2173 tab_xml_employee(lK_Voyage) := l_formatted_box; --
2174 l_all_box_0 := FALSE;
2175 ELSE
2176 tab_employee(lK_Voyage) := NULL;
2177 tab_xml_employee(lK_Voyage) := NULL; --
2178 END IF;
2179 hr_utility.trace('tab_employee(lK_Voyage) = ' ||
2180 tab_employee(lK_Voyage));
2181
2182 hr_utility.trace('tab_xml_employee(lK_Voyage) = ' ||
2183 tab_xml_employee(lK_Voyage));
2184 -- (Box L)
2185 l_box := pay_ca_archive_utils.get_archive_value(
2186 l_arch_action_id,
2187 l_province,
2188 'JURISDICTION_CODE',
2189 'CAEOY_RL1_BOXL_PER_JD_YTD');
2190
2191 IF TO_NUMBER(l_box) > 9999999.99 THEN
2192 l_status := 'Failed';
2193 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2194 END IF;
2195
2196 IF l_box IS NOT NULL AND
2197 to_number(l_box) <> 0 THEN
2198
2199 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2200 INTO l_formatted_box
2201 FROM dual;
2202
2203 tab_employee(lL_AutreAvantage) := '<L_AutreAvantage>' ||
2204 l_formatted_box || '</L_AutreAvantage>' || EOL;
2205 tab_xml_employee(lL_AutreAvantage) := l_formatted_box; --
2206 l_all_box_0 := FALSE;
2207 ELSE
2208 tab_employee(lL_AutreAvantage) := NULL;
2209 tab_xml_employee(lL_AutreAvantage) := NULL; --
2210 END IF;
2211 hr_utility.trace('tab_employee(lL_AutreAvantage) = ' ||
2212 tab_employee(lL_AutreAvantage));
2213
2214 hr_utility.trace('tab_xml_employee(lL_AutreAvantage) = ' ||
2215 tab_xml_employee(lL_AutreAvantage));
2216 -- (Box M)
2217 l_box := pay_ca_archive_utils.get_archive_value(
2218 l_arch_action_id,
2219 l_province,
2220 'JURISDICTION_CODE',
2221 'CAEOY_RL1_BOXM_PER_JD_YTD');
2222
2223 IF TO_NUMBER(l_box) > 9999999.99 THEN
2224 l_status := 'Failed';
2225 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2226 END IF;
2227
2228 IF l_box IS NOT NULL AND
2229 to_number(l_box) <> 0 THEN
2230
2231 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2232 INTO l_formatted_box
2233 FROM dual;
2234
2235 tab_employee(lM_Commission) := '<M_Commission>' ||
2236 l_formatted_box || '</M_Commission>' || EOL;
2237 tab_xml_employee(lM_Commission) := l_formatted_box; --
2238 l_all_box_0 := FALSE;
2239 ELSE
2240 tab_employee(lM_Commission) := NULL;
2241 tab_xml_employee(lM_Commission) := NULL; --
2242 END IF;
2243 hr_utility.trace('tab_employee(lM_Commission) = ' ||
2244 tab_employee(lM_Commission));
2245
2246 hr_utility.trace('tab_xml_employee(lM_Commission) = ' ||
2247 tab_xml_employee(lM_Commission));
2248 -- (Box N)
2249 l_box := pay_ca_archive_utils.get_archive_value(
2250 l_arch_action_id,
2251 l_province,
2252 'JURISDICTION_CODE',
2253 'CAEOY_RL1_BOXN_PER_JD_YTD');
2254
2255 IF TO_NUMBER(l_box) > 9999999.99 THEN
2256 l_status := 'Failed';
2257 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2258 END IF;
2259
2260 IF l_box IS NOT NULL AND
2261 to_number(l_box) <> 0 THEN
2262
2263 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2264 INTO l_formatted_box
2265 FROM dual;
2266
2267 tab_employee(lN_DonBienfaisance) := '<N_DonBienfaisance>' ||
2268 l_formatted_box || '</N_DonBienfaisance>' || EOL;
2269 tab_xml_employee(lN_DonBienfaisance) := l_formatted_box; --
2270 l_all_box_0 := FALSE;
2271 ELSE
2272 tab_employee(lN_DonBienfaisance) := NULL;
2273 tab_xml_employee(lN_DonBienfaisance) := NULL; --
2274 END IF;
2275
2276 hr_utility.trace('tab_employee(lN_DonBienfaisance) = ' ||
2277 tab_employee(lN_DonBienfaisance));
2278
2279 hr_utility.trace('tab_xml_employee(lN_DonBienfaisance) = ' ||
2280 tab_xml_employee(lN_DonBienfaisance));
2281 -- Summ (Box O)
2282 l_box := pay_ca_archive_utils.get_archive_value(
2283 l_arch_action_id,
2284 l_province,
2285 'JURISDICTION_CODE',
2286 'CAEOY_RL1_BOXO_PER_JD_YTD');
2287
2288 IF TO_NUMBER(l_box) > 9999999.99 THEN
2289 l_status := 'Failed';
2290 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2291 END IF;
2292
2293 IF l_box IS NOT NULL AND
2294 to_number(l_box) <> 0 THEN
2295
2296 l_boxO := l_box;
2297 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2298 INTO l_formatted_box
2299 FROM dual;
2300
2301 tab_employee(lO_AutreRevenu) := '<MontantCaseO>' ||
2302 l_formatted_box || '</MontantCaseO>' || EOL;
2303 tab_xml_employee(lO_AutreRevenu) := l_formatted_box; --
2304 l_all_box_0 := FALSE;
2305 ELSE
2306 tab_employee(lO_AutreRevenu) := NULL;
2307 tab_xml_employee(lO_AutreRevenu) := NULL; --
2308 END IF;
2309 hr_utility.trace('tab_employee(lO_AutreRevenu) = ' ||
2310 tab_employee(lO_AutreRevenu));
2311
2312 hr_utility.trace('tab_xml_employee(lO_AutreRevenu) = ' ||
2313 tab_xml_employee(lO_AutreRevenu));
2314
2315 -- SourceCasem
2316
2317 IF to_number(l_boxO) <> 0 THEN
2318 l_box := pay_ca_archive_utils.get_archive_value(
2319 l_arch_action_id,
2320 l_province,
2321 'JURISDICTION_CODE',
2322 'CAEOY_RL1_BOXO_CODE_PER_JD_YTD');
2323 tab_employee(l_SourceCase) := '<SourceCaseO>' ||
2324 l_box || '</SourceCaseO>' || EOL;
2325 tab_xml_employee(l_SourceCase) := l_box; --
2326 ELSE
2327 tab_employee(l_SourceCase) := NULL;
2328 tab_xml_employee(l_SourceCase) := NULL; --
2329 END IF;
2330 hr_utility.trace('tab_employee(l_SourceCase) = ' ||
2331 tab_employee(l_SourceCase));
2332
2333 hr_utility.trace('tab_xml_employee(l_SourceCase) = ' ||
2334 tab_xml_employee(l_SourceCase));
2335 -- (Box P)
2336 if tab_employee(lO_AutreRevenu) is not null
2337 and tab_employee(l_SourceCase)is not null then
2338 l_O_AutreRevenu := '<O_AutreRevenu>'||EOL
2339 ||tab_employee(lO_AutreRevenu)||EOL
2340 ||tab_employee(l_SourceCase)||EOL
2341 ||'</O_AutreRevenu>'||EOL;
2342 else
2343 l_O_AutreRevenu := null;
2344 end if;
2345 l_box := pay_ca_archive_utils.get_archive_value(
2346 l_arch_action_id,
2347 l_province,
2348 'JURISDICTION_CODE',
2349 'CAEOY_RL1_BOXP_PER_JD_YTD');
2350
2351 IF TO_NUMBER(l_box) > 9999999.99 THEN
2352 l_status := 'Failed';
2353 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2354 END IF;
2355
2356 IF l_box IS NOT NULL AND
2357 to_number(l_box) <> 0 THEN
2358
2359 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2360 INTO l_formatted_box
2361 FROM dual;
2362
2363 tab_employee(lP_RegimeAssInterEntr) := '<P_RegimeAssInterEntr>' ||
2364 l_formatted_box || '</P_RegimeAssInterEntr>' || EOL;
2365 tab_xml_employee(lP_RegimeAssInterEntr) := l_formatted_box; --
2366 l_all_box_0 := FALSE;
2367 ELSE
2368 tab_employee(lP_RegimeAssInterEntr) := NULL;
2369 tab_xml_employee(lP_RegimeAssInterEntr) := NULL; --
2370 END IF;
2371 hr_utility.trace('tab_employee(lP_RegimeAssInterEntr) = ' ||
2372 tab_employee(lP_RegimeAssInterEntr));
2373
2374 hr_utility.trace('tab_xml_employee(lP_RegimeAssInterEntr) = ' ||
2375 tab_xml_employee(lP_RegimeAssInterEntr));
2376 -- (Box R)
2377 l_box := pay_ca_archive_utils.get_archive_value(
2378 l_arch_action_id,
2379 l_province,
2380 'JURISDICTION_CODE',
2381 'CAEOY_RL1_BOXR_PER_JD_YTD');
2382
2383 IF TO_NUMBER(l_box) > 9999999.99 THEN
2384 l_status := 'Failed';
2385 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2386 END IF;
2387
2388 IF l_box IS NOT NULL AND
2389 to_number(l_box) <> 0 THEN
2390
2391 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2392 INTO l_formatted_box
2393 FROM dual;
2394
2395 tab_employee(lR_RevenuIndien) := '<R_RevenuIndien>' ||
2396 l_formatted_box || '</R_RevenuIndien>' || EOL;
2397 tab_xml_employee(lR_RevenuIndien) := l_formatted_box; --
2398 l_all_box_0 := FALSE;
2399 ELSE
2400 tab_employee(lR_RevenuIndien) := NULL;
2401 tab_xml_employee(lR_RevenuIndien) := NULL; --
2402 END IF;
2403 hr_utility.trace('tab_employee(lR_RevenuIndien) = ' ||
2404 tab_employee(lR_RevenuIndien));
2405
2406 hr_utility.trace('tab_xml_employee(lR_RevenuIndien) = ' ||
2407 tab_xml_employee(lR_RevenuIndien));
2408 -- (Box S)
2409 l_box := pay_ca_archive_utils.get_archive_value(
2410 l_arch_action_id,
2411 l_province,
2412 'JURISDICTION_CODE',
2413 'CAEOY_RL1_BOXS_PER_JD_YTD');
2414
2415 IF TO_NUMBER(l_box) > 9999999.99 THEN
2416 l_status := 'Failed';
2417 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2418 END IF;
2419
2420 IF l_box IS NOT NULL AND
2421 to_number(l_box) <> 0 THEN
2422
2423 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2424 INTO l_formatted_box
2425 FROM dual;
2426
2427 tab_employee(lS_PourboireRecu) := '<S_PourboireRecu>' ||
2428 l_formatted_box || '</S_PourboireRecu>' || EOL;
2429 tab_xml_employee(lS_PourboireRecu) := l_formatted_box; --
2430 l_all_box_0 := FALSE;
2431 ELSE
2432 tab_employee(lS_PourboireRecu) := NULL;
2433 tab_xml_employee(lS_PourboireRecu) := NULL; --
2434 END IF;
2435 hr_utility.trace('tab_employee(lS_PourboireRecu) = ' ||
2436 tab_employee(lS_PourboireRecu));
2437
2438 hr_utility.trace('tab_xml_employee(lS_PourboireRecu) = ' ||
2439 tab_xml_employee(lS_PourboireRecu));
2440 -- (Box T)
2441 l_box := pay_ca_archive_utils.get_archive_value(
2442 l_arch_action_id,
2443 l_province,
2444 'JURISDICTION_CODE',
2445 'CAEOY_RL1_BOXT_PER_JD_YTD');
2446
2447 IF TO_NUMBER(l_box) > 9999999.99 THEN
2448 l_status := 'Failed';
2449 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2450 END IF;
2451
2452 IF l_box IS NOT NULL AND
2453 to_number(l_box) <> 0 THEN
2454
2455 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2456 INTO l_formatted_box
2457 FROM dual;
2458
2459 tab_employee(lT_PourboireAttribue) := '<T_PourboireAttribue>' ||
2460 l_formatted_box || '</T_PourboireAttribue>' || EOL;
2461 tab_xml_employee(lT_PourboireAttribue) := l_formatted_box; --
2462 l_all_box_0 := FALSE;
2463 ELSE
2464 tab_employee(lT_PourboireAttribue) := NULL;
2465 tab_xml_employee(lT_PourboireAttribue) := NULL; --
2466 END IF;
2467 hr_utility.trace('tab_employee(lT_PourboireAttribue) = ' ||
2468 tab_employee(lT_PourboireAttribue));
2469 hr_utility.trace('tab_xml_employee(lT_PourboireAttribue) = ' ||
2470 tab_xml_employee(lT_PourboireAttribue));
2471 -- (Box ContisationRPC)
2472 l_box := pay_ca_archive_utils.get_archive_value(
2473 l_arch_action_id,
2474 --l_province,
2475 --'JURISDICTION_CODE',
2476 'CAEOY_CPP_EE_WITHHELD_PER_YTD');
2477
2478 IF TO_NUMBER(l_box) > 9999999.99 THEN
2479 l_status := 'Failed';
2480 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2481 END IF;
2482
2483 IF l_box IS NOT NULL AND
2484 to_number(l_box) <> 0 THEN
2485
2486 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2487 INTO l_formatted_box
2488 FROM dual;
2489
2490 tab_employee(l_ContisationRPC) := '<CotisationRPC>' ||
2491 l_formatted_box || '</CotisationRPC>' || EOL;
2492 l_all_box_0 := FALSE;
2493 ELSE
2494 tab_employee(l_ContisationRPC) := NULL;
2495 END IF;
2496 hr_utility.trace('tab_employee(lR_ContisationRPC) = ' ||
2497 tab_employee(l_ContisationRPC));
2498
2499 -- Negative Balance Exists
2500 hr_utility.trace('finding if neg bal exists');
2501 l_box := pay_ca_archive_utils.get_archive_value(
2502 l_arch_action_id,
2503 l_province,
2504 'JURISDICTION_CODE',
2505 'CAEOY_RL1_NEGATIVE_BALANCE_EXISTS');
2506
2507 IF l_box = 'Y' THEN
2508 l_status := 'Failed';
2509 l_msg_code := 'NEG';
2510 l_neg_bal_exists := TRUE;
2511 hr_utility.trace('neg bal exists');
2512 END IF;
2513
2514 IF l_all_box_0 THEN
2515 l_status := 'Failed';
2516 l_msg_code := 'ALL_BOXES_ZERO';
2517 END IF;
2518
2519 IF l_status = 'Failed' and l_rep_type <>'RL1PAPERPDF' THEN
2520
2521 -- Box A, 01
2522 l_box := pay_ca_archive_utils.get_archive_value(
2523 l_arch_action_id,
2524 l_province,
2525 'JURISDICTION_CODE',
2526 'CAEOY_RL1_BOXA_01_AMT_PER_JD_YTD');
2527
2528 IF l_box IS NOT NULL AND
2529 to_number(l_box) <> 0 THEN
2530
2531 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2532 INTO l_formatted_box
2533 FROM dual;
2534
2535 tab_employee(lBoxA_01) := '<BoxA_01>' ||
2536 l_formatted_box || '</BoxA_01>' || EOL;
2537 ELSE
2538 tab_employee(lBoxA_01) := NULL;
2539 END IF;
2540 hr_utility.trace('tab_employee(lBoxA_01) = ' ||
2541 tab_employee(lBoxA_01));
2542
2543 -- Box A, 02
2544 l_box := pay_ca_archive_utils.get_archive_value(
2545 l_arch_action_id,
2546 l_province,
2547 'JURISDICTION_CODE',
2548 'CAEOY_RL1_BOXA_02_AMT_PER_JD_YTD');
2549
2550 IF l_box IS NOT NULL AND
2551 to_number(l_box) <> 0 THEN
2552
2553 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2554 INTO l_formatted_box
2555 FROM dual;
2556
2557 tab_employee(lBoxA_02) := '<BoxA_02>' ||
2558 l_formatted_box || '</BoxA_02>' || EOL;
2559 ELSE
2560 tab_employee(lBoxA_02) := NULL;
2561 END IF;
2562 hr_utility.trace('tab_employee(lBoxA_02) = ' ||
2563 tab_employee(lBoxA_02));
2564
2565 -- Box A, 15
2566 l_box := pay_ca_archive_utils.get_archive_value(
2567 l_arch_action_id,
2568 l_province,
2569 'JURISDICTION_CODE',
2570 'CAEOY_RL1_BOXA_15_AMT_PER_JD_YTD');
2571
2572 IF l_box IS NOT NULL AND
2573 to_number(l_box) <> 0 THEN
2574
2575 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2576 INTO l_formatted_box
2577 FROM dual;
2578
2579 tab_employee(lBoxA_15) := '<BoxA_15>' ||
2580 l_formatted_box || '</BoxA_15>' || EOL;
2581 ELSE
2582 tab_employee(lBoxA_15) := NULL;
2583 END IF;
2584 hr_utility.trace('tab_employee(lBoxA_15) = ' ||
2585 tab_employee(lBoxA_15));
2586
2587 -- Box A, 16
2588 l_box := pay_ca_archive_utils.get_archive_value(
2589 l_arch_action_id,
2590 l_province,
2591 'JURISDICTION_CODE',
2592 'CAEOY_RL1_BOXA_16_AMT_PER_JD_YTD');
2593
2594 IF l_box IS NOT NULL AND
2595 to_number(l_box) <> 0 THEN
2596
2597 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2598 INTO l_formatted_box
2599 FROM dual;
2600
2601 tab_employee(lBoxA_16) := '<BoxA_16>' ||
2602 l_formatted_box || '</BoxA_16>' || EOL;
2603 ELSE
2604 tab_employee(lBoxA_16) := NULL;
2605 END IF;
2606 hr_utility.trace('tab_employee(lBoxA_16) = ' ||
2607 tab_employee(lBoxA_16));
2608
2609 -- Box A, 17
2610 l_box := pay_ca_archive_utils.get_archive_value(
2611 l_arch_action_id,
2612 l_province,
2613 'JURISDICTION_CODE',
2614 'CAEOY_RL1_BOXA_17_AMT_PER_JD_YTD');
2615
2616 IF l_box IS NOT NULL AND
2617 to_number(l_box) <> 0 THEN
2618
2619 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2620 INTO l_formatted_box
2621 FROM dual;
2622
2623 tab_employee(lBoxA_17) := '<BoxA_17>' ||
2624 l_formatted_box || '</BoxA_17>' || EOL;
2625 ELSE
2626 tab_employee(lBoxA_17) := NULL;
2627 END IF;
2628 hr_utility.trace('tab_employee(lBoxA_17) = ' ||
2629 tab_employee(lBoxA_17));
2630
2631 -- Box A, 18
2632 l_box := pay_ca_archive_utils.get_archive_value(
2633 l_arch_action_id,
2634 l_province,
2635 'JURISDICTION_CODE',
2636 'CAEOY_RL1_BOXA_18_AMT_PER_JD_YTD');
2637
2638 IF l_box IS NOT NULL AND
2639 to_number(l_box) <> 0 THEN
2640
2641 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2642 INTO l_formatted_box
2643 FROM dual;
2644
2645 tab_employee(lBoxA_18) := '<BoxA_18>' ||
2646 l_formatted_box || '</BoxA_18>' || EOL;
2647 ELSE
2648 tab_employee(lBoxA_18) := NULL;
2649 END IF;
2650 hr_utility.trace('tab_employee(lBoxA_18) = ' ||
2651 tab_employee(lBoxA_18));
2652
2653 -- Box A, 19
2654 l_box := pay_ca_archive_utils.get_archive_value(
2655 l_arch_action_id,
2656 l_province,
2657 'JURISDICTION_CODE',
2658 'CAEOY_RL1_BOXA_19_AMT_PER_JD_YTD');
2659
2660 IF l_box IS NOT NULL AND
2661 to_number(l_box) <> 0 THEN
2662
2663 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2664 INTO l_formatted_box
2665 FROM dual;
2666
2667 tab_employee(lBoxA_19) := '<BoxA_19>' ||
2668 l_formatted_box || '</BoxA_19>' || EOL;
2669 ELSE
2670 tab_employee(lBoxA_19) := NULL;
2671 END IF;
2672 hr_utility.trace('tab_employee(lBoxA_19) = ' ||
2673 tab_employee(lBoxA_19));
2674
2675 -- Box A, 25
2676 l_box := pay_ca_archive_utils.get_archive_value(
2677 l_arch_action_id,
2678 l_province,
2679 'JURISDICTION_CODE',
2680 'CAEOY_RL1_BOXA_25_AMT_PER_JD_YTD');
2681
2682 IF l_box IS NOT NULL AND
2683 to_number(l_box) <> 0 THEN
2684
2685 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2686 INTO l_formatted_box
2687 FROM dual;
2688
2689 tab_employee(lBoxA_25) := '<BoxA_25>' ||
2690 l_formatted_box || '</BoxA_25>' || EOL;
2691 ELSE
2692 tab_employee(lBoxA_25) := NULL;
2693 END IF;
2694 hr_utility.trace('tab_employee(lBoxA_25) = ' ||
2695 tab_employee(lBoxA_25));
2696
2697 -- Box A, 26
2698 l_box := pay_ca_archive_utils.get_archive_value(
2699 l_arch_action_id,
2700 l_province,
2701 'JURISDICTION_CODE',
2702 'CAEOY_RL1_BOXA_26_AMT_PER_JD_YTD');
2703
2704 IF l_box IS NOT NULL AND
2705 to_number(l_box) <> 0 THEN
2706
2707 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2708 INTO l_formatted_box
2709 FROM dual;
2710
2711 tab_employee(lBoxA_26) := '<BoxA_26>' ||
2712 l_formatted_box || '</BoxA_26>' || EOL;
2713 ELSE
2714 tab_employee(lBoxA_26) := NULL;
2715 END IF;
2716 hr_utility.trace('tab_employee(lBoxA_26) = ' ||
2717 tab_employee(lBoxA_26));
2718
2719 -- Box A, 27
2720 l_box := pay_ca_archive_utils.get_archive_value(
2721 l_arch_action_id,
2722 l_province,
2723 'JURISDICTION_CODE',
2724 'CAEOY_RL1_BOXA_27_AMT_PER_JD_YTD');
2725
2726 IF l_box IS NOT NULL AND
2727 to_number(l_box) <> 0 THEN
2728
2729 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2730 INTO l_formatted_box
2731 FROM dual;
2732
2733 tab_employee(lBoxA_27) := '<BoxA_27>' ||
2734 l_formatted_box || '</BoxA_27>' || EOL;
2735 ELSE
2736 tab_employee(lBoxA_27) := NULL;
2737 END IF;
2738 hr_utility.trace('tab_employee(lBoxA_27) = ' ||
2739 tab_employee(lBoxA_27));
2740
2741 -- Box A, 29
2742 l_box := pay_ca_archive_utils.get_archive_value(
2743 l_arch_action_id,
2744 l_province,
2745 'JURISDICTION_CODE',
2746 'CAEOY_RL1_BOXA_29_AMT_PER_JD_YTD');
2747
2748 IF l_box IS NOT NULL AND
2749 to_number(l_box) <> 0 THEN
2750
2751 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2752 INTO l_formatted_box
2753 FROM dual;
2754
2755 tab_employee(lBoxA_29) := '<BoxA_29>' ||
2756 l_formatted_box || '</BoxA_29>' || EOL;
2757 ELSE
2758 tab_employee(lBoxA_29) := NULL;
2759 END IF;
2760 hr_utility.trace('tab_employee(lBoxA_27) = ' ||
2761 tab_employee(lBoxA_29));
2762
2763 -- Box A, 30
2764 l_box := pay_ca_archive_utils.get_archive_value(
2765 l_arch_action_id,
2766 l_province,
2767 'JURISDICTION_CODE',
2768 'CAEOY_RL1_BOXA_30_AMT_PER_JD_YTD');
2769
2770 IF l_box IS NOT NULL AND
2771 to_number(l_box) <> 0 THEN
2772
2773 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2774 INTO l_formatted_box
2775 FROM dual;
2776
2777 tab_employee(lBoxA_30) := '<BoxA_30>' ||
2778 l_formatted_box || '</BoxA_30>' || EOL;
2779 ELSE
2780 tab_employee(lBoxA_30) := NULL;
2781 END IF;
2782 hr_utility.trace('tab_employee(lBoxA_30) = ' ||
2783 tab_employee(lBoxA_30));
2784
2785 -- Box D, 07
2786 l_box := pay_ca_archive_utils.get_archive_value(
2787 l_arch_action_id,
2788 l_province,
2789 'JURISDICTION_CODE',
2790 'CAEOY_RL1_BOXD_07_AMT_PER_JD_YTD');
2791
2792 IF l_box IS NOT NULL AND
2793 to_number(l_box) <> 0 THEN
2794
2795 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2796 INTO l_formatted_box
2797 FROM dual;
2798
2799 tab_employee(lBoxD_07) := '<BoxD_07>' ||
2800 l_formatted_box || '</BoxD_07>' || EOL;
2801 ELSE
2802 tab_employee(lBoxD_07) := NULL;
2803 END IF;
2804 hr_utility.trace('tab_employee(lBoxD_07) = ' ||
2805 tab_employee(lBoxD_07));
2806
2807 -- Box D, 08
2808 l_box := pay_ca_archive_utils.get_archive_value(
2809 l_arch_action_id,
2810 l_province,
2811 'JURISDICTION_CODE',
2812 'CAEOY_RL1_BOXD_08_AMT_PER_JD_YTD');
2813
2814 IF l_box IS NOT NULL AND
2815 to_number(l_box) <> 0 THEN
2816
2817 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2818 INTO l_formatted_box
2819 FROM dual;
2820
2821 tab_employee(lBoxD_08) := '<BoxD_08>' ||
2822 l_formatted_box || '</BoxD_08>' || EOL;
2823 ELSE
2824 tab_employee(lBoxD_08) := NULL;
2825 END IF;
2826 hr_utility.trace('tab_employee(lBoxD_08) = ' ||
2827 tab_employee(lBoxD_08));
2828
2829 -- Box D, 20
2830 l_box := pay_ca_archive_utils.get_archive_value(
2831 l_arch_action_id,
2832 l_province,
2833 'JURISDICTION_CODE',
2834 'CAEOY_RL1_BOXD_20_AMT_PER_JD_YTD');
2835
2836 IF l_box IS NOT NULL AND
2837 to_number(l_box) <> 0 THEN
2838
2839 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2840 INTO l_formatted_box
2841 FROM dual;
2842
2843 tab_employee(lBoxD_20) := '<BoxD_20>' ||
2844 l_formatted_box || '</BoxD_20>' || EOL;
2845 ELSE
2846 tab_employee(lBoxD_20) := NULL;
2847 END IF;
2848 hr_utility.trace('tab_employee(lBoxD_20) = ' ||
2849 tab_employee(lBoxD_20));
2850
2851 -- Box K, 11
2852 l_box := pay_ca_archive_utils.get_archive_value(
2853 l_arch_action_id,
2854 l_province,
2855 'JURISDICTION_CODE',
2856 'CAEOY_RL1_BOXK_11_AMT_PER_JD_YTD');
2857
2858 IF l_box IS NOT NULL AND
2859 to_number(l_box) <> 0 THEN
2860
2861 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2862 INTO l_formatted_box
2863 FROM dual;
2864
2865 tab_employee(lBoxK_11) := '<BoxK_11>' ||
2866 l_formatted_box || '</BoxK_11>' || EOL;
2867 ELSE
2868 tab_employee(lBoxK_11) := NULL;
2869 END IF;
2870 hr_utility.trace('tab_employee(lBoxK_11) = ' ||
2871 tab_employee(lBoxK_11));
2872
2873 -- Box O, RA
2874 l_box := pay_ca_archive_utils.get_archive_value(
2875 l_arch_action_id,
2876 l_province,
2877 'JURISDICTION_CODE',
2878 'CAEOY_RL1_BOXO_AMOUNT_RA_PER_JD_YTD');
2879
2880 IF l_box IS NOT NULL AND
2881 to_number(l_box) <> 0 THEN
2882
2883 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2884 INTO l_formatted_box
2885 FROM dual;
2886
2887 tab_employee(lBoxO_RA) := '<BoxO_RA>' ||
2888 l_formatted_box || '</BoxO_RA>' || EOL;
2889 ELSE
2890 tab_employee(lBoxO_RA) := NULL;
2891 END IF;
2892 hr_utility.trace('tab_employee(lBoxO_RA) = ' ||
2893 tab_employee(lBoxO_RA));
2894
2895 -- Box O, RB
2896 l_box := pay_ca_archive_utils.get_archive_value(
2897 l_arch_action_id,
2898 l_province,
2899 'JURISDICTION_CODE',
2900 'CAEOY_RL1_BOXO_AMOUNT_RB_PER_JD_YTD');
2901
2902 IF l_box IS NOT NULL AND
2903 to_number(l_box) <> 0 THEN
2904
2905 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2906 INTO l_formatted_box
2907 FROM dual;
2908
2909 tab_employee(lBoxO_RB) := '<BoxO_RB>' ||
2910 l_formatted_box || '</BoxO_RB>' || EOL;
2911 ELSE
2912 tab_employee(lBoxO_RB) := NULL;
2913 END IF;
2914 hr_utility.trace('tab_employee(lBoxO_RB) = ' ||
2915 tab_employee(lBoxO_RB));
2916
2917 -- Box O, RC
2918 l_box := pay_ca_archive_utils.get_archive_value(
2919 l_arch_action_id,
2920 l_province,
2921 'JURISDICTION_CODE',
2922 'CAEOY_RL1_BOXC_AMOUNT_RA_PER_JD_YTD');
2923
2924 IF l_box IS NOT NULL AND
2925 to_number(l_box) <> 0 THEN
2926
2927 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2928 INTO l_formatted_box
2929 FROM dual;
2930
2931 tab_employee(lBoxO_RC) := '<BoxO_RC>' ||
2932 l_formatted_box || '</BoxO_RC>' || EOL;
2933 ELSE
2934 tab_employee(lBoxO_RC) := NULL;
2935 END IF;
2936 hr_utility.trace('tab_employee(lBoxO_RC) = ' ||
2937 tab_employee(lBoxO_RC));
2938
2939 -- Box O, RD
2940 l_box := pay_ca_archive_utils.get_archive_value(
2941 l_arch_action_id,
2942 l_province,
2943 'JURISDICTION_CODE',
2944 'CAEOY_RL1_BOXO_AMOUNT_RD_PER_JD_YTD');
2945
2946 IF l_box IS NOT NULL AND
2947 to_number(l_box) <> 0 THEN
2948
2949 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2950 INTO l_formatted_box
2951 FROM dual;
2952
2953 tab_employee(lBoxO_RD) := '<BoxO_RD>' ||
2954 l_formatted_box || '</BoxO_RD>' || EOL;
2955 ELSE
2956 tab_employee(lBoxO_RD) := NULL;
2957 END IF;
2958 hr_utility.trace('tab_employee(lBoxO_RD) = ' ||
2959 tab_employee(lBoxO_RD));
2960
2961 -- Box O, RE
2962 l_box := pay_ca_archive_utils.get_archive_value(
2963 l_arch_action_id,
2964 l_province,
2965 'JURISDICTION_CODE',
2966 'CAEOY_RL1_BOXO_AMOUNT_RE_PER_JD_YTD');
2967
2968 IF l_box IS NOT NULL AND
2969 to_number(l_box) <> 0 THEN
2970
2971 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2972 INTO l_formatted_box
2973 FROM dual;
2974
2975 tab_employee(lBoxO_RE) := '<BoxO_RE>' ||
2976 l_formatted_box || '</BoxO_RE>' || EOL;
2977 ELSE
2978 tab_employee(lBoxO_RE) := NULL;
2979 END IF;
2980 hr_utility.trace('tab_employee(lBoxO_RE) = ' ||
2981 tab_employee(lBoxO_RE));
2982
2983 -- Box O, RF
2984 l_box := pay_ca_archive_utils.get_archive_value(
2985 l_arch_action_id,
2986 l_province,
2987 'JURISDICTION_CODE',
2988 'CAEOY_RL1_BOXO_AMOUNT_RF_PER_JD_YTD');
2989
2990 IF l_box IS NOT NULL AND
2991 to_number(l_box) <> 0 THEN
2992
2993 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2994 INTO l_formatted_box
2995 FROM dual;
2996
2997 tab_employee(lBoxO_RF) := '<BoxO_RF>' ||
2998 l_formatted_box || '</BoxO_RF>' || EOL;
2999 ELSE
3000 tab_employee(lBoxO_RF) := NULL;
3001 END IF;
3002 hr_utility.trace('tab_employee(lBoxO_RF) = ' ||
3003 tab_employee(lBoxO_RF));
3004
3005 -- Box O, RG
3006 l_box := pay_ca_archive_utils.get_archive_value(
3007 l_arch_action_id,
3008 l_province,
3009 'JURISDICTION_CODE',
3010 'CAEOY_RL1_BOXO_AMOUNT_RG_PER_JD_YTD');
3011
3012 IF l_box IS NOT NULL AND
3013 to_number(l_box) <> 0 THEN
3014
3015 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3016 INTO l_formatted_box
3017 FROM dual;
3018
3019 tab_employee(lBoxO_RG) := '<BoxO_RG>' ||
3020 l_formatted_box || '</BoxO_RG>' || EOL;
3021 ELSE
3022 tab_employee(lBoxO_RG) := NULL;
3023 END IF;
3024 hr_utility.trace('tab_employee(lBoxO_RG) = ' ||
3025 tab_employee(lBoxO_RG));
3026
3027 -- Box O, RH
3028 l_box := pay_ca_archive_utils.get_archive_value(
3029 l_arch_action_id,
3030 l_province,
3031 'JURISDICTION_CODE',
3032 'CAEOY_RL1_BOXO_AMOUNT_RH_PER_JD_YTD');
3033
3034 IF l_box IS NOT NULL AND
3035 to_number(l_box) <> 0 THEN
3036
3037 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3038 INTO l_formatted_box
3039 FROM dual;
3040
3041 tab_employee(lBoxO_RH) := '<BoxO_RH>' ||
3042 l_formatted_box || '</BoxO_RH>' || EOL;
3043 ELSE
3044 tab_employee(lBoxO_RH) := NULL;
3045 END IF;
3046 hr_utility.trace('tab_employee(lBoxO_RH) = ' ||
3047 tab_employee(lBoxO_RH));
3048
3049 -- Box O, RI
3050 l_box := pay_ca_archive_utils.get_archive_value(
3051 l_arch_action_id,
3052 l_province,
3053 'JURISDICTION_CODE',
3054 'CAEOY_RL1_BOXO_AMOUNT_RI_PER_JD_YTD');
3055
3056 IF l_box IS NOT NULL AND
3057 to_number(l_box) <> 0 THEN
3058
3059 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3060 INTO l_formatted_box
3061 FROM dual;
3062
3063 tab_employee(lBoxO_RI) := '<BoxO_RI>' ||
3064 l_formatted_box || '</BoxO_RI>' || EOL;
3065 ELSE
3066 tab_employee(lBoxO_RI) := NULL;
3067 END IF;
3068 hr_utility.trace('tab_employee(lBoxO_RI) = ' ||
3069 tab_employee(lBoxO_RI));
3070
3071 -- Box O, RJ
3072 l_box := pay_ca_archive_utils.get_archive_value(
3073 l_arch_action_id,
3074 l_province,
3075 'JURISDICTION_CODE',
3076 'CAEOY_RL1_BOXO_AMOUNT_RJ_PER_JD_YTD');
3077
3078 IF l_box IS NOT NULL AND
3079 to_number(l_box) <> 0 THEN
3080
3081 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3082 INTO l_formatted_box
3083 FROM dual;
3084
3085 tab_employee(lBoxO_RJ) := '<BoxO_RJ>' ||
3086 l_formatted_box || '</BoxO_RJ>' || EOL;
3087 ELSE
3088 tab_employee(lBoxO_RJ) := NULL;
3089 END IF;
3090 hr_utility.trace('tab_employee(lBoxO_RJ) = ' ||
3091 tab_employee(lBoxO_RJ));
3092
3093 -- Box O, RK
3094 l_box := pay_ca_archive_utils.get_archive_value(
3095 l_arch_action_id,
3096 l_province,
3097 'JURISDICTION_CODE',
3098 'CAEOY_RL1_BOXO_AMOUNT_RK_PER_JD_YTD');
3099
3100 IF l_box IS NOT NULL AND
3101 to_number(l_box) <> 0 THEN
3102
3103 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3104 INTO l_formatted_box
3105 FROM dual;
3106
3107 tab_employee(lBoxO_RK) := '<BoxO_RK>' ||
3108 l_formatted_box || '</BoxO_RK>' || EOL;
3109 ELSE
3110 tab_employee(lBoxO_RK) := NULL;
3111 END IF;
3112 hr_utility.trace('tab_employee(lBoxO_RK) = ' ||
3113 tab_employee(lBoxO_RK));
3114
3115 -- Box O, RL
3116 l_box := pay_ca_archive_utils.get_archive_value(
3117 l_arch_action_id,
3118 l_province,
3119 'JURISDICTION_CODE',
3120 'CAEOY_RL1_BOXO_AMOUNT_RL_PER_JD_YTD');
3121
3122 IF l_box IS NOT NULL AND
3123 to_number(l_box) <> 0 THEN
3124
3125 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3126 INTO l_formatted_box
3127 FROM dual;
3128
3129 tab_employee(lBoxO_RL) := '<BoxO_RL>' ||
3130 l_formatted_box || '</BoxO_RL>' || EOL;
3131 ELSE
3132 tab_employee(lBoxO_RL) := NULL;
3133 END IF;
3134 hr_utility.trace('tab_employee(lBoxO_RL) = ' ||
3135 tab_employee(lBoxO_RL));
3136
3137 -- Box O, RL(22)
3138 l_box := pay_ca_archive_utils.get_archive_value(
3139 l_arch_action_id,
3140 l_province,
3141 'JURISDICTION_CODE',
3142 'CAEOY_RL1_BOXO_AMOUNT_RL_22_AMT_PER_JD_YTD');
3143
3144 IF l_box IS NOT NULL AND
3145 to_number(l_box) <> 0 THEN
3146
3147 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3148 INTO l_formatted_box
3149 FROM dual;
3150
3151 tab_employee(lBoxO_RL22) := '<BoxO_RL22>' ||
3152 l_formatted_box || '</BoxO_RL22>' || EOL;
3153 ELSE
3154 tab_employee(lBoxO_RL22) := NULL;
3155 END IF;
3156 hr_utility.trace('tab_employee(lBoxO_RL22) = ' ||
3157 tab_employee(lBoxO_RL22));
3158
3159 -- Box O, RL(28)
3160 l_box := pay_ca_archive_utils.get_archive_value(
3161 l_arch_action_id,
3162 l_province,
3163 'JURISDICTION_CODE',
3164 'CAEOY_RL1_BOXO_AMOUNT_RL_28_AMT_PER_JD_YTD');
3165
3166 IF l_box IS NOT NULL AND
3167 to_number(l_box) <> 0 THEN
3168
3169 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3170 INTO l_formatted_box
3171 FROM dual;
3172
3173 tab_employee(lBoxO_RL28) := '<BoxO_RL28>' ||
3174 l_formatted_box || '</BoxO_RL28>' || EOL;
3175 ELSE
3176 tab_employee(lBoxO_RL28) := NULL;
3177 END IF;
3178 hr_utility.trace('tab_employee(lBoxO_RL28) = ' ||
3179 tab_employee(lBoxO_RL28));
3180
3181 -- Box O, RM
3182 l_box := pay_ca_archive_utils.get_archive_value(
3183 l_arch_action_id,
3184 l_province,
3185 'JURISDICTION_CODE',
3186 'CAEOY_RL1_BOXO_AMOUNT_RM_PER_JD_YTD');
3187
3188 IF l_box IS NOT NULL AND
3189 to_number(l_box) <> 0 THEN
3190
3191 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3192 INTO l_formatted_box
3193 FROM dual;
3194
3195 tab_employee(lBoxO_RM) := '<BoxO_RM>' ||
3196 l_formatted_box || '</BoxO_RM>' || EOL;
3197 ELSE
3198 tab_employee(lBoxO_RM) := NULL;
3199 END IF;
3200 hr_utility.trace('tab_employee(lBoxO_RM) = ' ||
3201 tab_employee(lBoxO_RM));
3202
3203 -- Box O, RN
3204 l_box := pay_ca_archive_utils.get_archive_value(
3205 l_arch_action_id,
3206 l_province,
3207 'JURISDICTION_CODE',
3208 'CAEOY_RL1_BOXO_AMOUNT_RN_PER_JD_YTD');
3209
3210 IF l_box IS NOT NULL AND
3211 to_number(l_box) <> 0 THEN
3212
3213 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3214 INTO l_formatted_box
3215 FROM dual;
3216
3217 tab_employee(lBoxO_RN) := '<BoxO_RN>' ||
3218 l_formatted_box || '</BoxO_RN>' || EOL;
3219 ELSE
3220 tab_employee(lBoxO_RN) := NULL;
3221 END IF;
3222 hr_utility.trace('tab_employee(lBoxO_RN) = ' ||
3223 tab_employee(lBoxO_RN));
3224
3225 hr_utility.trace('l_person_id = ' || to_char(l_person_id));
3226
3227 -- Box O, RN 31
3228 l_box := pay_ca_archive_utils.get_archive_value(
3229 l_arch_action_id,
3230 l_province,
3231 'JURISDICTION_CODE',
3232 'CAEOY_RL1_BOXO_AMOUNT_RN_31_AMT_PER_JD_YTD');
3233
3234 IF l_box IS NOT NULL AND
3235 to_number(l_box) <> 0 THEN
3236
3237 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3238 INTO l_formatted_box
3239 FROM dual;
3240
3241 tab_employee(lBoxO_RN_31) := '<BoxO_RN_31>' ||
3242 l_formatted_box || '</BoxO_RN_31>' || EOL;
3243 ELSE
3244 tab_employee(lBoxO_RN_31) := NULL;
3245 END IF;
3246 hr_utility.trace('tab_employee(lBoxO_RN_31) = ' ||
3247 tab_employee(lBoxO_RN_31));
3248 -- Box O, RO
3249 l_box := pay_ca_archive_utils.get_archive_value(
3250 l_arch_action_id,
3251 l_province,
3252 'JURISDICTION_CODE',
3253 'CAEOY_RL1_BOXO_AMOUNT_RO_PER_JD_YTD');
3254
3255 IF l_box IS NOT NULL AND
3256 to_number(l_box) <> 0 THEN
3257
3258 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3259 INTO l_formatted_box
3260 FROM dual;
3261
3262 tab_employee(lBoxO_RO) := '<BoxO_RO>' ||
3263 l_formatted_box || '</BoxO_RO>' || EOL;
3264 ELSE
3265 tab_employee(lBoxO_RO) := NULL;
3266 END IF;
3267 hr_utility.trace('tab_employee(lBoxO_RO) = ' ||
3268 tab_employee(lBoxO_RO));
3269
3270 -- Box O, RP
3271 l_box := pay_ca_archive_utils.get_archive_value(
3272 l_arch_action_id,
3273 l_province,
3274 'JURISDICTION_CODE',
3275 'CAEOY_RL1_BOXO_AMOUNT_RP_PER_JD_YTD');
3276
3277 IF l_box IS NOT NULL AND
3278 to_number(l_box) <> 0 THEN
3279
3280 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3281 INTO l_formatted_box
3282 FROM dual;
3283
3284 tab_employee(lBoxO_RP) := '<BoxO_RP>' ||
3285 l_formatted_box || '</BoxO_RP>' || EOL;
3286 ELSE
3287 tab_employee(lBoxO_RP) := NULL;
3288 END IF;
3289 hr_utility.trace('tab_employee(lBoxO_RP) = ' ||
3290 tab_employee(lBoxO_RP));
3291
3292 -- Box O, RQ
3293 l_box := pay_ca_archive_utils.get_archive_value(
3294 l_arch_action_id,
3295 l_province,
3296 'JURISDICTION_CODE',
3297 'CAEOY_RL1_BOXO_AMOUNT_RQ_PER_JD_YTD');
3298
3299 IF l_box IS NOT NULL AND
3300 to_number(l_box) <> 0 THEN
3301
3302 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3303 INTO l_formatted_box
3304 FROM dual;
3305
3306 tab_employee(lBoxO_RQ) := '<BoxO_RQ>' ||
3307 l_formatted_box || '</BoxO_RQ>' || EOL;
3308 ELSE
3309 tab_employee(lBoxO_RQ) := NULL;
3310 END IF;
3311 hr_utility.trace('tab_employee(lBoxO_RQ) = ' ||
3312 tab_employee(lBoxO_RQ));
3313
3314 -- Box O, RR
3315 l_box := pay_ca_archive_utils.get_archive_value(
3316 l_arch_action_id,
3317 l_province,
3318 'JURISDICTION_CODE',
3319 'CAEOY_RL1_BOXO_AMOUNT_RR_PER_JD_YTD');
3320
3321 IF l_box IS NOT NULL AND
3322 to_number(l_box) <> 0 THEN
3323
3324 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3325 INTO l_formatted_box
3326 FROM dual;
3327
3328 tab_employee(lBoxO_RR) := '<BoxO_RR>' ||
3329 l_formatted_box || '</BoxO_RR>' || EOL;
3330 ELSE
3331 tab_employee(lBoxO_RR) := NULL;
3332 END IF;
3333 hr_utility.trace('tab_employee(lBoxO_RR) = ' ||
3334 tab_employee(lBoxO_RR));
3335
3336 -- Box O, RS
3337 l_box := pay_ca_archive_utils.get_archive_value(
3338 l_arch_action_id,
3339 l_province,
3340 'JURISDICTION_CODE',
3341 'CAEOY_RL1_BOXO_AMOUNT_RS_PER_JD_YTD');
3342
3343 IF l_box IS NOT NULL AND
3344 to_number(l_box) <> 0 THEN
3345
3346 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3347 INTO l_formatted_box
3348 FROM dual;
3349
3350 tab_employee(lBoxO_RS) := '<BoxO_RS>' ||
3351 l_formatted_box || '</BoxO_RS>' || EOL;
3352 ELSE
3353 tab_employee(lBoxO_RS) := NULL;
3354 END IF;
3355 hr_utility.trace('tab_employee(lBoxO_RS) = ' ||
3356 tab_employee(lBoxO_RS));
3357
3358 -- Box O, RT
3359 l_box := pay_ca_archive_utils.get_archive_value(
3360 l_arch_action_id,
3361 l_province,
3362 'JURISDICTION_CODE',
3363 'CAEOY_RL1_BOXO_AMOUNT_RT_PER_JD_YTD');
3364
3365 IF l_box IS NOT NULL AND
3366 to_number(l_box) <> 0 THEN
3367
3368 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3369 INTO l_formatted_box
3370 FROM dual;
3371
3372 tab_employee(lBoxO_RT) := '<BoxO_RT>' ||
3373 l_formatted_box || '</BoxO_RT>' || EOL;
3374 ELSE
3375 tab_employee(lBoxO_RT) := NULL;
3376 END IF;
3377 hr_utility.trace('tab_employee(lBoxO_RT) = ' ||
3378 tab_employee(lBoxO_RT));
3379
3380 -- Box O, RU
3381 l_box := pay_ca_archive_utils.get_archive_value(
3382 l_arch_action_id,
3383 l_province,
3384 'JURISDICTION_CODE',
3385 'CAEOY_RL1_BOXO_AMOUNT_RU_PER_JD_YTD');
3386
3387 IF l_box IS NOT NULL AND
3388 to_number(l_box) <> 0 THEN
3389
3390 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3391 INTO l_formatted_box
3392 FROM dual;
3393
3394 tab_employee(lBoxO_RU) := '<BoxO_RU>' ||
3395 l_formatted_box || '</BoxO_RU>' || EOL;
3396 ELSE
3397 tab_employee(lBoxO_RU) := NULL;
3398 END IF;
3399 hr_utility.trace('tab_employee(lBoxO_RU) = ' ||
3400 tab_employee(lBoxO_RU));
3401
3402 -- Box O, RV
3403 l_box := pay_ca_archive_utils.get_archive_value(
3404 l_arch_action_id,
3405 l_province,
3406 'JURISDICTION_CODE',
3407 'CAEOY_RL1_BOXO_AMOUNT_RV_PER_JD_YTD');
3408
3409 IF l_box IS NOT NULL AND
3410 to_number(l_box) <> 0 THEN
3411
3412 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3413 INTO l_formatted_box
3414 FROM dual;
3415
3416 tab_employee(lBoxO_RV) := '<BoxO_RV>' ||
3417 l_formatted_box || '</BoxO_RV>' || EOL;
3418 ELSE
3419 tab_employee(lBoxO_RV) := NULL;
3420 END IF;
3421 hr_utility.trace('tab_employee(lBoxO_RV) = ' ||
3422 tab_employee(lBoxO_RV));
3423
3424 -- Box O, RW
3425 l_box := pay_ca_archive_utils.get_archive_value(
3426 l_arch_action_id,
3427 l_province,
3428 'JURISDICTION_CODE',
3429 'CAEOY_RL1_BOXO_AMOUNT_RW_PER_JD_YTD');
3430
3431 IF l_box IS NOT NULL AND
3432 to_number(l_box) <> 0 THEN
3433
3434 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3435 INTO l_formatted_box
3436 FROM dual;
3437
3438 tab_employee(lBoxO_RW) := '<BoxO_RW>' ||
3439 l_formatted_box || '</BoxO_RW>' || EOL;
3440 ELSE
3441 tab_employee(lBoxO_RW) := NULL;
3442 END IF;
3443 hr_utility.trace('tab_employee(lBoxO_RW) = ' ||
3444 tab_employee(lBoxO_RW));
3445
3446 -- Box Q, 24
3447 l_box := pay_ca_archive_utils.get_archive_value(
3448 l_arch_action_id,
3449 l_province,
3450 'JURISDICTION_CODE',
3451 'CAEOY_RL1_BOXQ_24_AMT_PER_JD_YTD');
3452
3453 IF l_box IS NOT NULL AND
3454 to_number(l_box) <> 0 THEN
3455
3456 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3457 INTO l_formatted_box
3458 FROM dual;
3459
3460 tab_employee(lBoxQ_24) := '<BoxQ_24>' ||
3461 l_formatted_box || '</BoxQ_24>' || EOL;
3462 ELSE
3463 tab_employee(lBoxQ_24) := NULL;
3464 END IF;
3465 hr_utility.trace('tab_employee(lBoxQ_24) = ' ||
3466 tab_employee(lBoxQ_24));
3467
3468 -- Box R, 14
3469 l_box := pay_ca_archive_utils.get_archive_value(
3470 l_arch_action_id,
3471 l_province,
3472 'JURISDICTION_CODE',
3473 'CAEOY_RL1_BOXR_14_AMT_PER_JD_YTD');
3474
3475 IF l_box IS NOT NULL AND
3476 to_number(l_box) <> 0 THEN
3477
3478 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
3479 INTO l_formatted_box
3480 FROM dual;
3481
3482 tab_employee(lBoxR_14) := '<BoxR_14>' ||
3483 l_formatted_box || '</BoxR_14>' || EOL;
3484 ELSE
3485 tab_employee(lBoxR_14) := NULL;
3486 END IF;
3487 hr_utility.trace('tab_employee(lBoxR_14) = ' ||
3488 tab_employee(lBoxR_14));
3489 hr_utility.trace('l_msg_code ='|| l_msg_code);
3490
3491 OPEN cur_get_meaning(l_msg_code);
3492 FETCH cur_get_meaning
3493 INTO l_meaning;
3494 CLOSE cur_get_meaning;
3495 hr_utility.trace('l_meaning ='|| l_meaning);
3496 tab_employee(lErrorDetails) := '<ErrorDetails>' ||
3497 l_meaning || '</ErrorDetails>' || EOL;
3498
3499 l_other_details := tab_employee(lBoxA_01) ||
3500 tab_employee(lBoxA_02) ||
3501 tab_employee(lBoxA_15) ||
3502 tab_employee(lBoxA_16) ||
3503 tab_employee(lBoxA_17) ||
3504 tab_employee(lBoxA_18) ||
3505 tab_employee(lBoxA_19) ||
3506 tab_employee(lBoxA_25) ||
3507 tab_employee(lBoxA_26) ||
3508 tab_employee(lBoxA_27) ||
3509 tab_employee(lBoxA_29) ||
3510 tab_employee(lBoxA_30) ||
3511 tab_employee(lBoxD_07) ||
3512 tab_employee(lBoxD_08) ||
3513 tab_employee(lBoxD_20) ||
3514 tab_employee(lBoxK_11) ||
3515 tab_employee(lBoxO_RA) ||
3516 tab_employee(lBoxO_RB) ||
3517 tab_employee(lBoxO_RC) ||
3518 tab_employee(lBoxO_RD) ||
3519 tab_employee(lBoxO_RE) ||
3520 tab_employee(lBoxO_RF) ||
3521 tab_employee(lBoxO_RG) ||
3522 tab_employee(lBoxO_RH) ||
3523 tab_employee(lBoxO_RI) ||
3524 tab_employee(lBoxO_RJ) ||
3525 tab_employee(lBoxO_RK) ||
3526 tab_employee(lBoxO_RL) ||
3527 tab_employee(lBoxO_RM) ||
3528 tab_employee(lBoxO_RN) ||
3529 tab_employee(lBoxO_RN_31) ||
3530 tab_employee(lBoxO_RO) ||
3531 tab_employee(lBoxO_RP) ||
3532 tab_employee(lBoxO_RQ) ||
3533 tab_employee(lBoxO_RR) ||
3534 tab_employee(lBoxO_RS) ||
3535 tab_employee(lBoxO_RT) ||
3536 tab_employee(lBoxO_RU) ||
3537 tab_employee(lBoxO_RV) ||
3538 tab_employee(lBoxO_RW) ||
3539 tab_employee(lBoxQ_24) ||
3540 tab_employee(lBoxR_14) ||
3541 tab_employee(lErrorDetails) ;
3542 END IF;
3543 hr_utility.trace('l_other_details ='||l_other_details);
3544 hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3545 tab_employee(lH_CotisationRQAP));
3546 hr_utility.trace('l_rep_type ='|| l_rep_type);
3547 --hr_utility.trace('l_neg_bal_exists ='|| l_neg_bal_exists);
3548 --
3549
3550 if l_rep_type = 'RL1PAPERPDF' then
3551 /********************************/
3552 if l_neg_bal_exists = TRUE then
3553 hr_utility.trace('inside if');
3554 xml_footnote_boxo(l_arch_action_id
3555 ,l_asg_id
3556 ,l_footnote_boxo1
3557 ,l_footnote_boxo2
3558 ,l_footnote_boxo3);
3559 if tab_xml_employee(lNomFamille) is not null then
3560 l_final_xml_string := '<NomFamille>'
3561 ||tab_xml_employee(lNomFamille)
3562 ||'</NomFamille>'||EOL;
3563 end if;
3564 if tab_xml_employee(lPrenom) is not null then
3565 l_final_xml_string := l_final_xml_string||'<Prenom>'
3566 ||tab_xml_employee(lPrenom)
3567 ||'</Prenom>'||EOL;
3568 end if;
3569 if tab_xml_employee(lInitiale) is not null then
3570 l_final_xml_string := l_final_xml_string||'<Initiale>'
3571 ||tab_xml_employee(lInitiale)
3572 ||'</Initiale>'||EOL;
3573 end if;
3574 l_final_xml_string :=l_final_xml_string||'<Annee>'||tab_xml_employee(lAnnee)
3575 ||'</Annee>'||EOL;
3576
3577 if tab_xml_employee(lNoReleve) is not null then
3578 l_final_xml_string := l_final_xml_string
3579 ||'<NoReleve>'
3580 ||tab_xml_employee(lNoReleve)
3581 ||'</NoReleve>'||EOL;
3582 end if;
3583 if tab_xml_employee(lLigne1) is not null then
3584 l_final_xml_string := l_final_xml_string
3585 ||'<Ligne1>'
3586 ||substr(tab_xml_employee(lLigne1),1,45)
3587 ||'</Ligne1>'||EOL;
3588 end if;
3589 if tab_xml_employee(lLigne2) is not null then
3590 l_final_xml_string := l_final_xml_string
3591 ||'<Ligne2>'
3592 ||substr(tab_xml_employee(lLigne2),1,45)
3593 ||'</Ligne2>'||EOL;
3594 end if;
3595 if tab_xml_employee(lVille) is not NULL then
3596 l_final_xml_string := l_final_xml_string
3597 ||'<Ville>'
3598 ||substr(tab_xml_employee(lVille),1,45)
3599 ||'</Ville>'||EOL;
3600 end if;
3601 if tab_xml_employee(lNAS) is not null then
3602 l_final_xml_string := l_final_xml_string
3603 ||'<NAS>'
3604 ||tab_xml_employee(lNAS)
3605 ||'</NAS>'||EOL;
3606
3607 end if;
3608 l_final_xml_string := l_final_xml_string
3609 ||'<No>'||tab_xml_employee(lNo)
3610 ||'</No>'||EOL;
3611
3612 if tab_xml_employee(lA_RevenuEmploi) is not null then
3613 l_final_xml_string := l_final_xml_string
3614 ||'<A_RevenuEmploi>'
3615 ||tab_xml_employee(lA_RevenuEmploi)
3616 ||'</A_RevenuEmploi>'||EOL;
3617 end if;
3618 if tab_xml_employee(lB_CotisationRRQ) is not null then
3619 l_final_xml_string := l_final_xml_string
3620 ||'<B_CotisationRRQ>'
3621 ||tab_xml_employee(lB_CotisationRRQ)
3622 ||'</B_CotisationRRQ>'||EOL;
3623 end if;
3624 if tab_xml_employee(lC_CotisationAssEmploi) is not null then
3625 l_final_xml_string := l_final_xml_string
3626 ||'<C_CotisationAssEmploi>'
3627 ||tab_xml_employee(lC_CotisationAssEmploi)
3628 ||'</C_CotisationAssEmploi>'||EOL;
3629 end if;
3630 if tab_xml_employee(lD_CotisationRPA) is not null then
3631 l_final_xml_string := l_final_xml_string
3632 ||'<D_CotisationRPA>'
3633 ||tab_xml_employee(lD_CotisationRPA)
3634 ||'</D_CotisationRPA>'||EOL;
3635 end if;
3636 if tab_xml_employee(lE_ImpotQue) is not null then
3637 l_final_xml_string := l_final_xml_string
3638 ||'<E_ImpotQue>'
3639 ||tab_xml_employee(lE_ImpotQue)
3640 ||'</E_ImpotQue>'||EOL;
3641 end if;
3642 if tab_xml_employee(lF_CotisationSyndicale) is not null then
3643 l_final_xml_string := l_final_xml_string
3644 ||'<F_CotisationSyndicale>'
3645 ||tab_xml_employee(lF_CotisationSyndicale)
3646 ||'</F_CotisationSyndicale>'||EOL;
3647 end if;
3648 if tab_xml_employee(lG_SalaireAdmisRRQ) is not null then
3649 l_final_xml_string := l_final_xml_string
3650 ||'<G_SalaireAdmisRRQ>'
3651 ||tab_xml_employee(lG_SalaireAdmisRRQ)
3652 ||'</G_SalaireAdmisRRQ>'||EOL;
3653 end if;
3654
3655 if tab_xml_employee(lH_CotisationRQAP) is not null then
3656 l_final_xml_string := l_final_xml_string
3657 ||'<H_CotisationRQAP>'
3658 ||tab_xml_employee(lH_CotisationRQAP)
3659 ||'</H_CotisationRQAP>'||EOL;
3660 end if;
3661
3662 if tab_xml_employee(lI_SalaireAdmisRQAP) is not null then
3663 l_final_xml_string := l_final_xml_string
3664 ||'<I_SalaireAdmisRQAP>'
3665 ||tab_xml_employee(lI_SalaireAdmisRQAP)
3666 ||'</I_SalaireAdmisRQAP>'||EOL;
3667 end if;
3668
3669 if tab_xml_employee(lJ_RegimeAssMaladie) is not null then
3670 l_final_xml_string := l_final_xml_string
3671 ||'<J_RegimeAssMaladie>'
3672 ||tab_xml_employee(lJ_RegimeAssMaladie)
3673 ||'</J_RegimeAssMaladie>'||EOL;
3674 end if;
3675 if tab_xml_employee(lK_Voyage) is not null then
3676 l_final_xml_string := l_final_xml_string
3677 ||'<K_Voyage>'
3678 ||tab_xml_employee(lK_Voyage)
3679 ||'</K_Voyage>'||EOL;
3680 end if;
3681 if tab_xml_employee(lL_AutreAvantage) is not null then
3682 l_final_xml_string := l_final_xml_string
3683 ||'<L_AutreAvantage>'
3684 ||tab_xml_employee(lL_AutreAvantage)
3685 ||'</L_AutreAvantage>'||EOL;
3686 end if;
3687 if tab_xml_employee(lM_Commission) is not null then
3688 l_final_xml_string := l_final_xml_string
3689 ||'<M_Commission>'
3690 ||tab_xml_employee(lM_Commission)
3691 ||'</M_Commission>'||EOL;
3692 end if;
3693 if tab_xml_employee(lN_DonBienfaisance) is not null then
3694 l_final_xml_string := l_final_xml_string
3695 ||'<N_DonBienfaisance>'
3696 ||tab_xml_employee(lN_DonBienfaisance)
3697 ||'</N_DonBienfaisance>'||EOL;
3698 end if;
3699 if tab_xml_employee(lO_AutreRevenu) is not null then
3700 l_final_xml_string := l_final_xml_string
3701 ||'<O_AutreRevenu>'
3702 ||tab_xml_employee(lO_AutreRevenu)
3703 ||'</O_AutreRevenu>'||EOL;
3704 end if;
3705 if tab_xml_employee(lP_RegimeAssInterEntr) is not null then
3706 l_final_xml_string := l_final_xml_string
3707 ||'<P_RegimeAssInterEntr>'
3708 ||tab_xml_employee(lP_RegimeAssInterEntr)
3709 ||'</P_RegimeAssInterEntr>'||EOL;
3710 end if;
3711 if tab_xml_employee(lQ_SalaireDiffere) is not null then
3712 l_final_xml_string := l_final_xml_string
3713 ||'<Q_SalaireDiffere>'
3714 ||tab_xml_employee(lQ_SalaireDiffere)
3715 ||'</Q_SalaireDiffere>'||EOL;
3716 end if;
3717 if tab_xml_employee(lR_RevenuIndien) is not null then
3718 l_final_xml_string := l_final_xml_string
3719 ||'<R_RevenuIndien>'
3720 ||tab_xml_employee(lR_RevenuIndien)
3721 ||'</R_RevenuIndien>'||EOL;
3722 end if;
3723 if tab_xml_employee(lS_PourboireRecu) is not null then
3724 l_final_xml_string := l_final_xml_string
3725 ||'<S_PourboireRecu>'
3726 ||tab_xml_employee(lS_PourboireRecu)
3727 ||'</S_PourboireRecu>'||EOL;
3728 end if;
3729 if tab_xml_employee(lT_PourboireAttribue) is not null then
3730 l_final_xml_string := l_final_xml_string
3731 ||'<T_PourboireAttribue>'
3732 ||tab_xml_employee(lT_PourboireAttribue)
3733 ||'</T_PourboireAttribue>'||EOL;
3734 end if;
3735 if tab_xml_employee(lU_RetraiteProgressive) is not null then
3736 l_final_xml_string := l_final_xml_string
3737 ||'<U_RetraiteProgressive>'
3738 ||tab_xml_employee(lU_RetraiteProgressive)
3739 ||'</U_RetraiteProgressive>'||EOL;
3740 end if;
3741
3742 if tab_xml_employee(lV_NourritureLogement) is not null then
3743 l_final_xml_string := l_final_xml_string
3744 ||'<V_NourritureLogement>'
3745 ||tab_xml_employee(lV_NourritureLogement)
3746 ||'</V_NourritureLogement>'||EOL;
3747 end if;
3748 if tab_xml_employee(lW_Vehicule) is not null then
3749 l_final_xml_string := l_final_xml_string
3750 ||'<W_Vehicule>'
3751 ||tab_xml_employee(lW_Vehicule)
3752 ||'</W_Vehicule>'||EOL;
3753 end if;
3754 if tab_xml_employee(l_SourceCase) is not null then
3755 l_final_xml_string := l_final_xml_string
3756 ||'<SourceCase>'
3757 ||tab_xml_employee(l_SourceCase)
3758 ||'</SourceCase>'||EOL;
3759 end if;
3760 l_final_xml_string := '<FAILED_RL1_PDFASG>'
3761 ||l_final_xml_string
3762 ||l_footnote_boxo1
3763 ||'<Errmsg>'
3764 ||'Negative Balance exists'
3765 ||'</Errmsg>'||EOL
3766 ||'</FAILED_RL1_PDFASG>'||EOL;
3767 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3768 end if; /*if l_neg_balance_exists is TRUE*/
3769 /************/
3770 if l_neg_bal_exists = FALSE then
3771 hr_utility.trace('inside if');
3772 xml_footnote_boxo(l_arch_action_id
3773 ,l_asg_id
3774 ,l_footnote_boxo1
3775 ,l_footnote_boxo2
3776 ,l_footnote_boxo3);
3777 RL1XML_emplyer_data(l_mag_asg_action_id
3778 ,l_emplyr_final1
3779 ,l_emplyr_final2
3780 ,l_emplyr_final3);
3781
3782 hr_utility.trace('l_emplyr_final1='||l_emplyr_final1);
3783 hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3784 hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3785 hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3786 hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3787 hr_utility.trace('l_footnote_boxo3 ='|| l_footnote_boxo3);
3788 tab_xml_employee(lNomFamille) := tab_xml_employee(lNomFamille)||','||tab_xml_employee(lPrenom)||' '||tab_xml_employee(lInitiale);
3789
3790 select pay_ca_rl1_pdf_seq_s.nextval into l_sequence_number from dual;
3791 l_sequence_number1:=getnext_seq_num(l_sequence_number);
3792
3793 for k in 1..3 loop
3794 l_final_xml_string :=
3795 '<Annee'||k||'>'||tab_xml_employee(lAnnee)||'</Annee'||k||'>'
3796 ||'<Authorization_header'||K||'>'||l_authorization_header
3797 ||'</Authorization_header'||K||'>'||EOL
3798 ||'<Authorization_code'||K||'>'||l_authorization_code
3799 ||'</Authorization_code'||K||'>'
3800 ||'<Sequence_number'||K||'>'||l_sequence_number1
3801 ||'</Sequence_number'||K||'>'||EOL;
3802 if tab_xml_employee(lForm_number) is not null then
3803 l_final_xml_string := l_final_xml_string
3804 ||'<Form_number'||k||'>'
3805 ||tab_xml_employee(lForm_number)
3806 ||'</Form_number'||k||'>'||EOL;
3807 end if;
3808 if tab_xml_employee(lNoReleve) is not null then
3809 l_final_xml_string := l_final_xml_string
3810 ||'<NoReleve'||k||'>'||tab_xml_employee(lNoReleve)||'</NoReleve'||k||'>'||EOL;
3811 end if;
3812 if tab_xml_employee(lNAS) is not null then
3813 /*l_final_xml_string := l_final_xml_string
3814 ||'<NAS'||k||'>'||tab_xml_employee(lNAS)||'</NAS'||k||'>'||EOL;*/
3815 l_final_xml_string := l_final_xml_string
3816 ||'<SIN1'||k||'>'||substr(tab_xml_employee(lNAS),1,3)||'</SIN1'||k||'>'||EOL
3817 ||'<SIN2'||k||'>'||substr(tab_xml_employee(lNAS),4,3)||'</SIN2'||k||'>'||EOL
3818 ||'<SIN3'||k||'>'||substr(tab_xml_employee(lNAS),7,3)||'</SIN3'||k||'>'||EOL;
3819 end if;
3820 l_final_xml_string := l_final_xml_string
3821 ||'<No'||k||'>'||tab_xml_employee(lNo)||'</No'||k||'>'||EOL
3822 ||'<NomFamille'||k||'>'||substr(tab_xml_employee(lNomFamille),1,45)
3823 ||'</NomFamille'||k||'>'||EOL;
3824
3825 /***********************************************/
3826 if tab_xml_employee(lLigne1) is not null
3827 or tab_xml_employee(lLigne2) is not null
3828 or tab_xml_employee(lVille) is not NULL then
3829
3830 l_final_xml_string := l_final_xml_string||'<Ligne1'||k||'>';
3831 if tab_xml_employee(lLigne1) is not null then
3832 l_final_xml_string := l_final_xml_string||substr(tab_xml_employee(lLigne1),1,45)||EOL;
3833 end if;
3834 if tab_xml_employee(lLigne2) is not null then
3835 l_final_xml_string := l_final_xml_string||substr(tab_xml_employee(lLigne2),1,45)||EOL;
3836 end if;
3837 if tab_xml_employee(lVille) is not NULL then
3838 l_final_xml_string := l_final_xml_string||substr(tab_xml_employee(lVille),1,45);
3839 end if;
3840 l_final_xml_string := l_final_xml_string||'</Ligne1'||k||'>'||EOL;
3841 end if;
3842 /************************************************/
3843
3844 /**************************************************
3845 if tab_xml_employee(lLigne1) is not null then
3846 l_final_xml_string := l_final_xml_string
3847 ||'<Ligne1'||k||'>'
3848 ||substr(tab_xml_employee(lLigne1),1,45)
3849 ||'</Ligne1'||k||'>'||EOL;
3850 end if;
3851 if tab_xml_employee(lLigne2) is not null then
3852 l_final_xml_string := l_final_xml_string
3853 ||'<Ligne2'||k||'>'
3854 ||substr(tab_xml_employee(lLigne2),1,45)
3855 ||'</Ligne2'||k||'>'||EOL;
3856 end if;
3857 if tab_xml_employee(lVille) is not NULL then
3858 l_final_xml_string := l_final_xml_string
3859 ||'<Ville'||k||'>'
3860 ||substr(tab_xml_employee(lVille),1,45)
3861 ||'</Ville'||k||'>'||EOL;
3862 end if;
3863 *******************************************************/
3864
3865 if tab_xml_employee(lA_RevenuEmploi) is not null then
3866 l_final_xml_string := l_final_xml_string
3867 ||'<A_RevenuEmploi'||k||'>'
3868 ||tab_xml_employee(lA_RevenuEmploi)
3869 ||'</A_RevenuEmploi'||k||'>'||EOL;
3870 end if;
3871 if tab_xml_employee(lB_CotisationRRQ) is not null then
3872 l_final_xml_string := l_final_xml_string
3873 ||'<B_CotisationRRQ'||k||'>'
3874 ||tab_xml_employee(lB_CotisationRRQ)
3875 ||'</B_CotisationRRQ'||k||'>'||EOL;
3876 end if;
3877 if tab_xml_employee(lC_CotisationAssEmploi) is not null then
3878 l_final_xml_string := l_final_xml_string
3879 ||'<C_CotisationAssEmploi'||k||'>'
3880 ||tab_xml_employee(lC_CotisationAssEmploi)
3881 ||'</C_CotisationAssEmploi'||k||'>'||EOL;
3882 end if;
3883 if tab_xml_employee(lD_CotisationRPA) is not null then
3884 l_final_xml_string := l_final_xml_string
3885 ||'<D_CotisationRPA'||k||'>'
3886 ||tab_xml_employee(lD_CotisationRPA)
3887 ||'</D_CotisationRPA'||k||'>'||EOL;
3888 end if;
3889 if tab_xml_employee(lE_ImpotQue) is not null then
3890 l_final_xml_string := l_final_xml_string
3891 ||'<E_ImpotQue'||k||'>'
3892 ||tab_xml_employee(lE_ImpotQue)
3893 ||'</E_ImpotQue'||k||'>'||EOL;
3894 end if;
3895 if tab_xml_employee(lF_CotisationSyndicale) is not null then
3896 l_final_xml_string := l_final_xml_string
3897 ||'<F_CotisationSyndicale'||k||'>'
3898 ||tab_xml_employee(lF_CotisationSyndicale)
3899 ||'</F_CotisationSyndicale'||k||'>'||EOL;
3900 end if;
3901 if tab_xml_employee(lG_SalaireAdmisRRQ) is not null then
3902 l_final_xml_string := l_final_xml_string
3903 ||'<G_SalaireAdmisRRQ'||k||'>'
3904 ||tab_xml_employee(lG_SalaireAdmisRRQ)
3905 ||'</G_SalaireAdmisRRQ'||k||'>'||EOL;
3906 end if;
3907
3908 if tab_xml_employee(lH_CotisationRQAP) is not null then
3909 l_final_xml_string := l_final_xml_string
3910 ||'<H_CotisationRQAP'||k||'>'
3911 ||tab_xml_employee(lH_CotisationRQAP)
3912 ||'</H_CotisationRQAP'||k||'>'||EOL;
3913 end if;
3914
3915 if tab_xml_employee(lI_SalaireAdmisRQAP) is not null then
3916 l_final_xml_string := l_final_xml_string
3917 ||'<I_SalaireAdmisRQAP'||k||'>'
3918 ||tab_xml_employee(lI_SalaireAdmisRQAP)
3919 ||'</I_SalaireAdmisRQAP'||k||'>'||EOL;
3920 end if;
3921
3922 if tab_xml_employee(lJ_RegimeAssMaladie) is not null then
3923 l_final_xml_string := l_final_xml_string
3924 ||'<J_RegimeAssMaladie'||k||'>'
3925 ||tab_xml_employee(lJ_RegimeAssMaladie)
3926 ||'</J_RegimeAssMaladie'||k||'>'||EOL;
3927 end if;
3928 if tab_xml_employee(lK_Voyage) is not null then
3929 l_final_xml_string := l_final_xml_string
3930 ||'<K_Voyage'||k||'>'
3931 ||tab_xml_employee(lK_Voyage)
3932 ||'</K_Voyage'||k||'>'||EOL;
3933 end if;
3934 if tab_xml_employee(lL_AutreAvantage) is not null then
3935 l_final_xml_string := l_final_xml_string
3936 ||'<L_AutreAvantage'||k||'>'
3937 ||tab_xml_employee(lL_AutreAvantage)
3938 ||'</L_AutreAvantage'||k||'>'||EOL;
3939 end if;
3940 if tab_xml_employee(lM_Commission) is not null then
3941 l_final_xml_string := l_final_xml_string
3942 ||'<M_Commission'||k||'>'
3943 ||tab_xml_employee(lM_Commission)
3944 ||'</M_Commission'||k||'>'||EOL;
3945 end if;
3946 if tab_xml_employee(lN_DonBienfaisance) is not null then
3947 l_final_xml_string := l_final_xml_string
3948 ||'<N_DonBienfaisance'||k||'>'
3949 ||tab_xml_employee(lN_DonBienfaisance)
3950 ||'</N_DonBienfaisance'||k||'>'||EOL;
3951 end if;
3952 if tab_xml_employee(lO_AutreRevenu) is not null then
3953 l_final_xml_string := l_final_xml_string
3954 ||'<O_AutreRevenu'||k||'>'
3955 ||tab_xml_employee(lO_AutreRevenu)
3956 ||'</O_AutreRevenu'||k||'>'||EOL;
3957 end if;
3958 if tab_xml_employee(l_SourceCase) is not null then
3959 l_final_xml_string := l_final_xml_string
3960 ||'<SourceCase'||k||'>'
3961 ||tab_xml_employee(l_SourceCase)
3962 ||'</SourceCase'||k||'>'||EOL;
3963 end if;
3964 if tab_xml_employee(lP_RegimeAssInterEntr) is not null then
3965 l_final_xml_string := l_final_xml_string
3966 ||'<P_RegimeAssInterEntr'||k||'>'
3967 ||tab_xml_employee(lP_RegimeAssInterEntr)
3968 ||'</P_RegimeAssInterEntr'||k||'>'||EOL;
3969 end if;
3970 if tab_xml_employee(lQ_SalaireDiffere) is not null then
3971 l_final_xml_string := l_final_xml_string
3972 ||'<Q_SalaireDiffere'||k||'>'
3973 ||tab_xml_employee(lQ_SalaireDiffere)
3974 ||'</Q_SalaireDiffere'||k||'>'||EOL;
3975 end if;
3976 if tab_xml_employee(lR_RevenuIndien) is not null then
3977 l_final_xml_string := l_final_xml_string
3978 ||'<R_RevenuIndien'||k||'>'
3979 ||tab_xml_employee(lR_RevenuIndien)
3980 ||'</R_RevenuIndien'||k||'>'||EOL;
3981 end if;
3982 if tab_xml_employee(lS_PourboireRecu) is not null then
3983 l_final_xml_string := l_final_xml_string
3984 ||'<S_PourboireRecu'
3985 ||k||'>'||tab_xml_employee(lS_PourboireRecu)
3986 ||'</S_PourboireRecu'||k||'>'||EOL;
3987 end if;
3988 if tab_xml_employee(lT_PourboireAttribue) is not null then
3989 l_final_xml_string := l_final_xml_string
3990 ||'<T_PourboireAttribue'||k||'>'
3991 ||tab_xml_employee(lT_PourboireAttribue)
3992 ||'</T_PourboireAttribue'||k||'>'||EOL;
3993 end if;
3994 if tab_xml_employee(lU_RetraiteProgressive) is not null then
3995 l_final_xml_string := l_final_xml_string
3996 ||'<U_RetraiteProgressive'||k||'>'
3997 ||tab_xml_employee(lU_RetraiteProgressive)
3998 ||'</U_RetraiteProgressive'||k||'>'||EOL;
3999 end if;
4000
4001 if tab_xml_employee(lV_NourritureLogement) is not null then
4002 l_final_xml_string := l_final_xml_string
4003 ||'<V_NourritureLogement'||k||'>'
4004 ||tab_xml_employee(lV_NourritureLogement)
4005 ||'</V_NourritureLogement'||k||'>'||EOL;
4006 end if;
4007 if tab_xml_employee(lW_Vehicule) is not null then
4008 l_final_xml_string := l_final_xml_string
4009 ||'<W_Vehicule'||k||'>'
4010 ||tab_xml_employee(lW_Vehicule)
4011 ||'</W_Vehicule'||k||'>'||EOL;
4012 end if;
4013
4014
4015 if k=1 then
4016 l_final_xml_string1 := '<RL1_PDF_ASG>'||EOL
4017 ||'<Empdata>'||EOL
4018 ||l_emplyr_final1
4019 ||l_final_xml_string
4020 ||l_footnote_boxo1
4021 ||'</Empdata>'||EOL;
4022 end if;
4023 if k=2 then
4024 l_final_xml_string2 := '<Empdata>'||EOL
4025 ||l_emplyr_final2
4026 ||l_final_xml_string
4027 ||l_footnote_boxo2
4028 ||'</Empdata>'||EOL;
4029 end if;
4030 if k=3 then
4031 l_final_xml_string3 := '<Empdata>'||EOL
4032 ||l_emplyr_final3
4033 ||l_final_xml_string
4034 ||l_footnote_boxo3
4035 ||'</Empdata>'||EOL
4036 ||'</RL1_PDF_ASG>'||EOL;
4037 end if;
4038 end loop;
4039 hr_utility.trace('final l_final_xml_string1 ='|| l_final_xml_string1);
4040 hr_utility.trace('final l_final_xml_string2 ='|| l_final_xml_string2);
4041 hr_utility.trace('final l_final_xml_string3 ='|| l_final_xml_string3);
4042 pay_core_files.write_to_magtape_lob(l_final_xml_string1);
4043 pay_core_files.write_to_magtape_lob(l_final_xml_string2);
4044 pay_core_files.write_to_magtape_lob(l_final_xml_string3);
4045 --l_page_break :=' ';
4046 --l_page_break :='<page_break>'||l_page_break||'</page_break>';
4047 -- pay_core_files.write_to_magtape_lob(l_page_break);
4048 end if; /* end if of ifl_neg_balance_exists */
4049 else
4050 hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
4051 tab_employee(lH_CotisationRQAP));
4052 l_final_xml_string :=
4053 '<' || l_status || '>' || EOL ||
4054 '<R>' || EOL ||
4055 -- '<ok>ok</ok>'||EOL||
4056 tab_employee(lAnnee) ||
4057 tab_employee(lNoReleve)
4058 ||'<Identification>'|| EOL
4059 || '<Employe>' || EOL ||
4060 tab_employee(lNAS) ||
4061 tab_employee(lNo) ||
4062 tab_employee(lNomFamille) ||
4063 tab_employee(lPrenom) ||
4064 tab_employee(lInitiale) || '</Employe>' || EOL
4065 ||'</Identification>' || EOL
4066 || l_addr_begin_tag || EOL ||
4067 tab_employee(lLigne1) ||
4068 tab_employee(lLigne2) ||
4069 tab_employee(lVille) ||
4070 tab_employee(lProvince) ||
4071 tab_employee(lCodePostal) ||
4072 l_addr_end_tag || EOL ||
4073 '<Montants>' || EOL ||
4074 tab_employee(lA_RevenuEmploi) ||
4075 tab_employee(lB_CotisationRRQ) ||
4076 tab_employee(lC_CotisationAssEmploi) ||
4077 tab_employee(lD_CotisationRPA) ||
4078 tab_employee(lE_ImpotQue) ||
4079 tab_employee(lF_CotisationSyndicale) ||
4080 tab_employee(lG_SalaireAdmisRRQ) ||
4081 tab_employee(lH_CotisationRQAP)||
4082 tab_employee(lI_SalaireAdmisRQAP)||
4083 tab_employee(lJ_RegimeAssMaladie) ||
4084 tab_employee(lK_Voyage) ||
4085 tab_employee(lL_AutreAvantage) ||
4086 tab_employee(lM_Commission) ||
4087 tab_employee(lN_DonBienfaisance) ||
4088 l_O_AutreRevenu||
4089 tab_employee(lP_RegimeAssInterEntr) ||
4090 tab_employee(lQ_SalaireDiffere) ||
4091 tab_employee(lR_RevenuIndien) ||
4092 tab_employee(lS_PourboireRecu) ||
4093 tab_employee(lT_PourboireAttribue) ||
4094 tab_employee(lU_RetraiteProgressive) ||
4095 tab_employee(lV_NourritureLogement) ||
4096 tab_employee(lW_Vehicule) ||
4097 tab_employee(l_ContisationRPC) ||
4098 l_other_details ||
4099 '</Montants>' || EOL || '</R>' || EOL ||
4100 '</' || l_status || '>' ;
4101
4102
4103 hr_utility.trace('rl1_xml_employee: l_final_xml_string = ' || l_final_xml_string);
4104 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4105 end if;
4106 hr_utility.trace('end of xml_employee_record');
4107
4108 END;
4109 END xml_employee_record;
4110
4111 PROCEDURE xml_employer_start IS
4112 BEGIN
4113
4114 DECLARE
4115
4116 l_final_xml CLOB;
4117 l_final_xml_string VARCHAR2(32000);
4118 l_is_temp_final_xml VARCHAR2(2);
4119
4120 BEGIN
4121
4122 l_final_xml_string := '<Groupe01>';
4123
4124 hr_utility.trace('rl1_xml_employee_start: l_final_xml_string = ' || l_final_xml_string);
4125 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4126
4127
4128 END;
4129 END xml_employer_start;
4130
4131 PROCEDURE xml_employer_record IS
4132 BEGIN
4133 DECLARE
4134
4135 l_final_xml CLOB;
4136 l_final_xml_string VARCHAR2(32000);
4137 l_is_temp_final_xml VARCHAR2(2);
4138
4139 TYPE employer_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
4140
4141 tab_employer employer_info;
4142
4143 lAnnee NUMBER;
4144 lNbReleves NUMBER;
4145 lNoId NUMBER;
4146 lTypeDossier NUMBER;
4147 lNoDossier NUMBER;
4148 lNEQ NUMBER;
4149 lNom1 NUMBER;
4150 lNom2 NUMBER;
4151 lLigne1 NUMBER;
4152 lLigne2 NUMBER;
4153 lVille NUMBER;
4154 lProvince NUMBER;
4155 lCodePostal NUMBER;
4156
4157 l_taxation_year varchar2(4);
4158 l_context1 ff_archive_items.context1%TYPE;
4159 EOL varchar2(5);
4160 l_employer_name varchar2(100);
4161 l_quebec_bn varchar2(20);
4162 l_address_line hr_locations.address_line_1%TYPE;
4163 l_address_begin_tag varchar2(10);
4164 l_address_end_tag varchar2(10);
4165
4166 BEGIN
4167
4168 hr_utility.trace('XML Employer');
4169
4170 SELECT
4171 fnd_global.local_chr(13) || fnd_global.local_chr(10)
4172 INTO EOL
4173 FROM dual;
4174
4175 lAnnee := 1;
4176 lNbReleves := 2;
4177 lNoId := 3;
4178 lTypeDossier := 4;
4179 lNoDossier := 5;
4180 lNEQ := 6;
4181 lNom1 := 7;
4182 lNom2 := 8;
4183 lLigne1 := 9;
4184 lLigne2 := 10;
4185 lVille := 11;
4186 lProvince := 12;
4187 lCodePostal := 13;
4188
4189 l_taxation_year
4190 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
4191 l_context1 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
4192
4193 hr_utility.trace ('l_cvontext1 ='||l_context1);
4194
4195 tab_employer(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' || EOL;
4196 tab_employer(lNbReleves) := '<NbReleves>' || 'Running Total' || '</NbReleves>' || EOL;
4197
4198 l_quebec_bn := pay_ca_archive_utils.get_archive_value
4199 (l_context1,'CAEOY_RL1_QUEBEC_BN');
4200
4201 tab_employer(lNoId) := '<NoId>' || substr(l_quebec_bn,1,10) ||
4202 '</NoId>' || EOL;
4203 tab_employer(lTypeDossier) := '<TypeDossier>' || 'RS' ||
4204 '</TypeDossier>' || EOL;
4205 tab_employer(lNoDossier) := '<NoDossier>' || substr(l_quebec_bn,13,4) ||
4206 '</NoDossier>' || EOL;
4207 tab_employer(lNEQ) := '<NEQ>' || substr(l_quebec_bn,1,10) ||
4208 '</NEQ>' || EOL;
4209 l_employer_name := pay_ca_archive_utils.get_archive_value(l_context1,
4210 'CAEOY_RL1_EMPLOYER_NAME');
4211
4212 tab_employer(lNom1) := '<Nom1>' ||
4213 convert_special_char(substr(l_employer_name,1,30)) || '</Nom1>' || EOL;
4214 hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
4215 hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4216 hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4217 hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4218 hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4219 hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4220 hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4221
4222 IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
4223 tab_employer(lNom2) := '<Nom2>' ||
4224 convert_special_char(substr(l_employer_name,31,30)) || '</Nom2>' || EOL;
4225 ELSE
4226 tab_employer(lNom2) := NULL;
4227 END IF;
4228 hr_utility.trace('tab_employer(lNom2) = ' || tab_employer(lNom2));
4229
4230 -- Address Line 1
4231
4232 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4233 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1');
4234
4235 IF l_address_line IS NULL THEN
4236
4237 l_address_begin_tag := '';
4238 tab_employer(lLigne1) := NULL;
4239 tab_employer(lLigne2) := NULL;
4240 tab_employer(lVille) := NULL;
4241 tab_employer(lProvince) := NULL;
4242 tab_employer(lCodePostal) := NULL;
4243 l_address_end_tag := '';
4244
4245 ELSE
4246
4247 l_address_begin_tag := '<Adresse>';
4248
4249 tab_employer(lLigne1) := '<Ligne1>' ||
4250 convert_special_char(substr(l_address_line,1,30)) || '</Ligne1>' || EOL;
4251 hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
4252
4253
4254 -- Address Line 2
4255
4256 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4257 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2');
4258
4259 IF l_address_line IS NOT NULL THEN
4260 tab_employer(lLigne2) := '<Ligne2>' ||
4261 convert_special_char(substr(l_address_line,1,30)) || '</Ligne2>' || EOL;
4262 ELSE
4263 tab_employer(lLigne2) := NULL;
4264 END IF;
4265 hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
4266
4267 -- Ville (City)
4268
4269 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4270 'CAEOY_RL1_EMPLOYER_CITY');
4271 IF l_address_line IS NOT NULL THEN
4272 tab_employer(lVille) := '<Ville>' ||
4273 convert_special_char(substr(l_address_line,1,30)) || '</Ville>' || EOL;
4274 ELSE
4275 tab_employer(lVille) := NULL;
4276 END IF;
4277 hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
4278
4279 -- Province
4280
4281 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4282 'CAEOY_RL1_EMPLOYER_PROVINCE');
4283
4284 IF l_address_line IS NOT NULL THEN
4285 tab_employer(lProvince) := '<Province>' ||
4286 SUBSTR(hr_general.decode_lookup( 'CA_PROVINCE',
4287 l_address_line),1,20) || '</Province>' || EOL;
4288 ELSE
4289 tab_employer(lProvince) := NULL;
4290 END IF;
4291 hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
4292
4293 -- Postal Code
4294
4295 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4296 'CAEOY_RL1_EMPLOYER_POSTAL_CODE');
4297
4298 IF l_address_line IS NOT NULL THEN
4299 tab_employer(lCodePostal) := '<CodePostal>' ||
4300 substr(replace(l_address_line,' '),1,6) || '</CodePostal>' || EOL;
4301 ELSE
4302 tab_employer(lCodePostal) := NULL;
4303 END IF;
4304 hr_utility.trace('tab_employer(lCodePostal) = ' || tab_employer(lCodePostal));
4305
4306 l_address_end_tag := '</Adresse>';
4307
4308 END IF;
4309
4310 l_final_xml_string := '<T>' || EOL ||
4311 tab_employer(lAnnee) ||
4312 tab_employer(lNbReleves) || '<Employeur>' || EOL ||
4313 tab_employer(lNoId) ||
4314 tab_employer(lTypeDossier) ||
4315 tab_employer(lNoDossier) ||
4316 tab_employer(lNEQ) ||
4317 tab_employer(lNom1) ||
4318 tab_employer(lNom2) || l_address_begin_tag || EOL ||
4319 tab_employer(lLigne1) ||
4320 tab_employer(lLigne2) ||
4321 tab_employer(lVille) ||
4322 tab_employer(lProvince) ||
4323 tab_employer(lCodePostal) ||
4324 l_address_end_tag || EOL || '</Employeur>' || EOL ||
4325 '</T>' || EOL ||
4326 '</Groupe01>' || EOL;
4327
4328 hr_utility.trace('rl1_xml_employer_start: l_final_xml_string = ' || l_final_xml_string);
4329 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4330 END;
4331 END xml_employer_record;
4332
4333
4334 /**********************************************************************************************************/
4335 PROCEDURE xml_report_end IS
4336 BEGIN
4337
4338 DECLARE
4339 l_final_xml_string VARCHAR2(32000);
4340
4341 BEGIN
4342 hr_utility.trace('report ends here..closing RL1PAPER tag');
4343 l_final_xml_string := '</RL1PAPER>';
4344 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4345
4346 END;
4347 END xml_report_end;
4348
4349 PROCEDURE xml_rl1_report_start IS
4350 BEGIN
4351
4352 DECLARE
4353 l_final_xml_string VARCHAR2(32000);
4354
4355 BEGIN
4356 --hr_utility.trace_on(null,'SATI');
4357 hr_utility.trace('inside xml_rl1_report_start');
4358 l_final_xml_string := '<RL1PAPER>';
4359 pay_core_files.write_to_magtape_lob(l_final_xml_string);
4360
4361 END;
4362 END xml_rl1_report_start;
4363
4364 PROCEDURE xml_footnote_boxo(p_arch_assact_id IN NUMBER
4365 ,p_assgn_id IN NUMBER
4366 ,p_footnote_boxo1 OUT NOCOPY VARCHAR2
4367 ,p_footnote_boxo2 OUT NOCOPY VARCHAR2
4368 ,p_footnote_boxo3 OUT NOCOPY VARCHAR2
4369 ) is
4370
4371 l_person_lang VARCHAR2(10);
4372 l_cpp_withheld NUMBER;
4373 l_footnote1 VARCHAR2(200);
4374 l_footnote2 VARCHAR2(200);
4375 l_footnote3 VARCHAR2(200);
4376 l_count NUMBER;
4377 l_count_boxo NUMBER;
4378 l_boxo1 VARCHAR(400);
4379 l_boxo2 VARCHAR(400);
4380 l_boxo3 VARCHAR(400);
4381 l_footnote_value VARCHAR(30);
4382
4383 type t_footnote_record is record ( code varchar2(100)
4384 ,value VARCHAR2(30));
4385 type t_footnote_table is table of t_footnote_record index by BINARY_INTEGER;
4386
4387 t_footnote t_footnote_table;
4388 type t_boxo_record is record ( code varchar2(100)
4389 ,value NUMBER);
4390 type t_boxo_table is table of t_boxo_record index by BINARY_INTEGER;
4391 t_boxo t_boxo_table;
4392 i NUMBER :=0;
4393 EOL varchar2(5);
4394
4395 cursor c_get_language(p_assgn_id number) is
4396 select decode(correspondence_language,NULL,'US',correspondence_language)
4397 from per_all_people_f
4398 where person_id = to_number(pay_ca_archive_utils.get_archive_value(
4399 p_arch_assact_id,
4400 'CAEOY_PERSON_ID'));
4401 cursor cur_boxo is
4402 SELECT 'BOXO-'||substr(fdi.user_name,23,2) DB_Name,to_number(fai.value) value
4403 FROM ff_database_items fdi,
4404 ff_archive_items fai
4405 WHERE fai.user_entity_id = fdi.user_entity_id
4406 AND fai.context1 = p_arch_assact_id
4407 AND fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT_R__PER_JD_YTD'
4408 and fai.value <> '0'
4409 ORDER BY substr(fdi.user_name,5,4);
4410
4411 cursor cur_ftnt(p_cpp_withheld NUMBER
4412 ,p_person_lang VARCHAR2
4413 ,p_arch_assactid NUMBER)is
4414 select substr(ltrim(rtrim(code)),1,60) code,to_char(value,'999,999.99') value
4415 from (
4416
4417 select pay_ca_rl1_reg.get_label(lookup_type,lookup_code,p_person_lang) code, p_cpp_withheld value
4418 from hr_lookups
4419 where lookup_type = 'PAY_CA_RL1_FOOTNOTES'
4420 and lookup_code = 'CPP'
4421 and p_cpp_withheld <> 0
4422 union
4423 select SUBSTR(fdi.user_name,11,4)||', '||pay_ca_rl1_reg.get_label(hl.lookup_type,hl.lookup_code,p_person_lang) code,
4424 to_number(fai.value) value
4425 from HR_LOOKUPS HL,
4426 ff_database_items fdi,
4427 ff_archive_items fai
4428 where fai.user_entity_id=fdi.user_entity_id
4429 and fai.context1= p_arch_assactid
4430 and fdi.user_name like 'CAEOY_RL1_BOX%_AMT_PER_JD_YTD'
4431 and fai.value <> '0'
4432 and hl.lookup_type = 'PAY_CA_RL1_FOOTNOTES'
4433 AND HL.LOOKUP_CODE = SUBSTR(replace(FDI.USER_NAME,'_AMT_PER_JD_YTD'),-2)
4434 union all
4435 select pay_ca_rl1_reg.get_label(hl.lookup_type,hl.lookup_code,p_person_lang) code,
4436 to_number(pai.action_information5) value
4437 from pay_action_information pai
4438 , hr_lookups hl
4439 where pai.action_context_id = p_arch_assactid
4440 and hl.lookup_type = 'PAY_CA_RL1_NONBOX_FOOTNOTES'
4441 and hl.lookup_code = pai.action_information4
4442 );
4443
4444 l_see_attached Varchar2(100);
4445
4446 begin
4447 l_footnote1 :=NULL;
4448 l_footnote2 :=NULL;
4449 l_footnote3 :=NULL;
4450 l_boxo1 :=NULL;
4451 l_boxo2 :=NULL;
4452 l_boxo3 :=NULL;
4453
4454 l_see_attached := hr_general.decode_lookup('PAY_CA_LABELS'
4455 ,'SEE_ATTACHED');
4456 hr_utility.trace('l_see_attached = '||l_see_attached);
4457 SELECT
4458 fnd_global.local_chr(13) || fnd_global.local_chr(10)
4459 INTO EOL
4460 from dual;
4461 l_cpp_withheld := pay_ca_archive_utils.get_archive_value(p_arch_assact_id
4462 ,'CAEOY_CPP_EE_WITHHELD_PER_YTD');
4463 open c_get_language(p_assgn_id);
4464 fetch c_get_language into l_person_lang;
4465 close c_get_language;
4466 hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
4467 hr_utility.trace(' l_person_lang ='|| l_person_lang);
4468 hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
4469 for rec in cur_ftnt(l_cpp_withheld,l_person_lang,p_arch_assact_id)
4470 loop
4471 i:=i+1;
4472 t_footnote(i) := rec;
4473 end loop;
4474 i:=0;
4475 for rec in cur_boxo
4476 loop
4477 i:=i+1;
4478 t_boxo(i) :=rec;
4479 end loop;
4480 l_count_boxo:=t_boxo.count;
4481 l_count := t_footnote.count;
4482 hr_utility.trace(' l_count_boxo ='|| l_count_boxo);
4483 hr_utility.trace(' l_count ='|| l_count);
4484 if l_count > 1 then --more than 1 footnote
4485 p_footnote_boxo1 :='<Seeattached1>'|| l_see_attached ||'</Seeattached1>';
4486 p_footnote_boxo2 :='<Seeattached2>'|| l_see_attached ||'</Seeattached2>';
4487 p_footnote_boxo3 :='<Seeattached3>'|| l_see_attached ||'</Seeattached3>';
4488 elsif l_count=1 and l_count_boxo > 1 then --accomodate 1 box o
4489 p_footnote_boxo1 :='<Seeattached1>'|| l_see_attached ||'</Seeattached1>';
4490 p_footnote_boxo2 :='<Seeattached2>'|| l_see_attached ||'</Seeattached2>';
4491 p_footnote_boxo3 :='<Seeattached3>'|| l_see_attached ||'</Seeattached3>';
4492 elsif l_count=1 and l_count_boxo<=1 then
4493 if(t_footnote(1).code = 'Volunteer-Allow not incl in A and L:$1,000') then --Bug 6748011
4494 l_footnote_value := null;
4495 else
4496 l_footnote_value := t_footnote(1).value;
4497 end if;
4498 l_footnote1 :='<Footnote_Code1>'||t_footnote(1).code||'</Footnote_Code1>'||EOL
4499 ||'<Footnote_value1>'|| l_footnote_value || '</Footnote_value1>'||EOL;
4500 l_footnote2 :='<Footnote_Code2>'||t_footnote(1).code||'</Footnote_Code2>'||EOL
4501 ||'<Footnote_value2>'|| l_footnote_value || '</Footnote_value2>'||EOL;
4502 l_footnote3 :='<Footnote_Code3>'||t_footnote(1).code||'</Footnote_Code3>'||EOL
4503 ||'<Footnote_value3>'|| l_footnote_value || '</Footnote_value3>'||EOL;
4504 hr_utility.trace(' l_footnote1 ='|| l_footnote1);
4505 hr_utility.trace(' l_footnote2 ='|| l_footnote2);
4506 hr_utility.trace(' l_footnote3 ='|| l_footnote3);
4507 if (l_count_boxo=1) then
4508 l_boxo1 :='<Boxo_code_31>'||t_boxo(1).code||'</Boxo_code_31>'||EOL
4509 ||'<Boxo_value_31>'||t_boxo(1).value||'</Boxo_value_31>'||EOL;
4510 l_boxo2 :='<Boxo_code_32>'||t_boxo(1).code||'</Boxo_code_32>'||EOL
4511 ||'<Boxo_value_32>'||t_boxo(1).value||'</Boxo_value_32>'||EOL;
4512 l_boxo3 :='<Boxo_code_33>'||t_boxo(1).code||'</Boxo_code_33>'||EOL
4513 ||'<Boxo_value_33>'||t_boxo(1).value||'</Boxo_value_33>'||EOL;
4514
4515 end if;
4516
4517 p_footnote_boxo1 :=l_footnote1||l_boxo1;
4518 p_footnote_boxo2 :=l_footnote2||l_boxo2;
4519 p_footnote_boxo3 :=l_footnote3||l_boxo3;
4520 elsif l_count = 0 and l_count_boxo < 4 then
4521 hr_utility.trace('boxo count less than 4');
4522 for k in 1.. l_count_boxo
4523 loop
4524 l_boxo1 :=l_boxo1||'<Boxo_code_'||k||'1>'||t_boxo(k).code||'</Boxo_code_'||k||'1>'||EOL
4525 ||'<Boxo_value_'||k||'1>'||t_boxo(k).value||'</Boxo_value_'||k||'1>'||EOL;
4526 l_boxo2 :=l_boxo2||'<Boxo_code_'||k||'2>'||t_boxo(k).code||'</Boxo_code_'||k||'2>'||EOL
4527 ||'<Boxo_value_'||k||'2>'||t_boxo(k).value||'</Boxo_value_'||k||'2>'||EOL;
4528 l_boxo3 :=l_boxo3||'<Boxo_code_'||k||'3>'||t_boxo(k).code||'</Boxo_code_'||k||'3>'||EOL
4529 ||'<Boxo_value_'||k||'3>'||t_boxo(k).value||'</Boxo_value_'||k||'3>'||EOL;
4530
4531 end loop;
4532 hr_utility.trace(' l_boxo1 ='|| l_boxo1);
4533 hr_utility.trace(' l_boxo2 ='|| l_boxo2);
4534 hr_utility.trace(' l_boxo3 ='|| l_boxo3);
4535
4536 p_footnote_boxo1 :=l_boxo1;
4537 p_footnote_boxo2 :=l_boxo2;
4538 p_footnote_boxo3 :=l_boxo3;
4539
4540 elsif l_count_boxo > 3 then
4541 p_footnote_boxo1 :='<Seeattached1>'|| l_see_attached ||'</Seeattached1>';
4542 p_footnote_boxo2 :='<Seeattached2>'|| l_see_attached ||'</Seeattached2>';
4543 p_footnote_boxo3 :='<Seeattached3>'|| l_see_attached ||'</Seeattached3>';
4544 end if;
4545
4546 hr_utility.trace(' p_footnote_boxo1 ='|| p_footnote_boxo1);
4547 hr_utility.trace(' p_footnote_boxo2 ='|| p_footnote_boxo2);
4548 hr_utility.trace(' p_footnote_boxo3 ='|| p_footnote_boxo3);
4549
4550 end xml_footnote_boxo;
4551
4552
4553 PROCEDURE RL1XML_emplyer_data(p_assact_id IN NUMBER
4554 ,p_emplyr_final1 OUT NOCOPY VARCHAR2
4555 ,p_emplyr_final2 OUT NOCOPY VARCHAR2
4556 ,p_emplyr_final3 OUT NOCOPY VARCHAR2
4557 ) is
4558
4559 CURSOR c_get_arch_pay_actid IS
4560 SELECT to_number(substr(paa.serial_number,17,14)) payactid --archiver payroll action id
4561 FROM pay_assignment_actions paa
4562 WHERE paa.assignment_action_id = p_assact_id;
4563 l_final_xml_string VARCHAR2(32000);
4564 l_index NUMBER;
4565 l_address_line hr_locations.address_line_1%TYPE;
4566 TYPE employer_info IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
4567
4568 tab_employer employer_info;
4569 lNoId NUMBER;
4570 lNom1 NUMBER;
4571 lLigne1 NUMBER;
4572 lLigne2 NUMBER;
4573 lVille NUMBER;
4574 lProvince NUMBER;
4575 lCodePostal NUMBER;
4576 lLigne3 NUMBER;
4577 lCountry NUMBER;
4578 l_context1 ff_archive_items.context1%TYPE;
4579 EOL varchar2(5);
4580 l_employer_name varchar2(100);
4581 l_quebec_bn varchar2(20);
4582
4583 BEGIN
4584
4585 hr_utility.trace('XML Employer');
4586
4587 SELECT
4588 fnd_global.local_chr(13) || fnd_global.local_chr(10)
4589 INTO EOL
4590 FROM dual;
4591
4592 lNoId := 1;
4593 lNom1 := 2;
4594 lLigne1 := 3;
4595 lLigne2 := 4;
4596 lVille := 5;
4597 lProvince := 6;
4598 lCodePostal := 7;
4599 lLigne3 := 8;
4600 lCountry := 9;
4601
4602 open c_get_arch_pay_actid;
4603 fetch c_get_arch_pay_actid
4604 into l_context1;
4605 close c_get_arch_pay_actid;
4606 hr_utility.trace ('l_cvontext1 ='||l_context1);
4607
4608 l_quebec_bn := pay_ca_archive_utils.get_archive_value
4609 (l_context1,'CAEOY_RL1_QUEBEC_BN');
4610 tab_employer(lNoId) := substr(l_quebec_bn,1,10); -- employer id
4611
4612 l_employer_name := pay_ca_archive_utils.get_archive_value(l_context1,
4613 'CAEOY_RL1_EMPLOYER_NAME');
4614
4615 tab_employer(lNom1) := convert_special_char(l_employer_name);
4616 hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4617 hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4618
4619 -- Address Line 1
4620 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4621 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1');
4622 tab_employer(lLigne1) := convert_special_char(substr(l_address_line,1,40));
4623 hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
4624
4625 -- Address Line 2
4626 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4627 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2');
4628 tab_employer(lLigne2) := convert_special_char(substr(l_address_line,1,40));
4629 hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
4630
4631 -- Address Line 3
4632 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4633 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE3');
4634 tab_employer(lLigne3) := convert_special_char(substr(l_address_line,1,40));
4635 hr_utility.trace('tab_employer(lLigne3) = ' || tab_employer(lLigne3));
4636
4637 if(tab_employer(lLigne2) is not null or tab_employer(lLigne3) is not null) then
4638 tab_employer(lLigne2) := tab_employer(lLigne2) ||' '|| tab_employer(lLigne3) ; /*******/
4639 end if;
4640
4641 -- Ville (City)
4642 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4643 'CAEOY_RL1_EMPLOYER_CITY');
4644 tab_employer(lVille) := convert_special_char(substr(l_address_line,1,30));
4645 hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
4646
4647 -- Province
4648
4649 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4650 'CAEOY_RL1_EMPLOYER_PROVINCE');
4651
4652 tab_employer(lProvince) :=l_address_line;
4653 hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
4654
4655 --Country
4656 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4657 'CAEOY_RL1_EMPLOYER_COUNTRY');
4658 tab_employer(lCountry) :=l_address_line;
4659 hr_utility.trace('tab_employer(lCountry) = ' || tab_employer(lCountry));
4660
4661 -- Postal Code
4662 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4663 'CAEOY_RL1_EMPLOYER_POSTAL_CODE');
4664 tab_employer(lCodePostal) :=substr(replace(l_address_line,' '),1,6);
4665
4666 --final city
4667 tab_employer(lVille):=tab_employer(lVille)||' '||
4668 tab_employer(lProvince)||' '||
4669 tab_employer(lCountry) ||' '||
4670 substr(tab_employer(lCodePostal),1,3)||' '||
4671 substr(tab_employer(lCodePostal),4,3);
4672
4673 hr_utility.trace('tab_employer(lCodePostal) = ' || tab_employer(lCodePostal));
4674 for l_index in 1..3 loop
4675 l_final_xml_string := '<Employer_name'||l_index||'>' ||substr(tab_employer(lNom1),1,40) || '</Employer_name'||l_index||'>' || EOL;
4676
4677 /*********************************************/
4678 if tab_employer(lLigne1) is not null
4679 or tab_employer(lLigne2) is not null
4680 or tab_employer(lVille) is not null then
4681
4682 l_final_xml_string := l_final_xml_string||'<emplr_Ligne1'||l_index||'>' ;
4683
4684
4685 if tab_employer(lLigne1) is not null then
4686 l_final_xml_string := l_final_xml_string||substr(tab_employer(lLigne1),1,40)||EOL;
4687 end if;
4688 if tab_employer(lLigne2) is not null then
4689 l_final_xml_string := l_final_xml_string||substr(tab_employer(lLigne2),1,40)||EOL;
4690 end if;
4691 if tab_employer(lVille) is not null then
4692 l_final_xml_string := l_final_xml_string||substr(tab_employer(lVille),1,40)||EOL;
4693 end if;
4694
4695 l_final_xml_string := l_final_xml_string|| '</emplr_Ligne1'||l_index||'>' || EOL;
4696 end if;
4697 /********************************************/
4698
4699 /*****************************************************
4700 if tab_employer(lLigne1) is not null then
4701 l_final_xml_string := l_final_xml_string
4702 ||'<emplr_Ligne1'||l_index||'>' ||substr(tab_employer(lLigne1),1,40) || '</emplr_Ligne1'||l_index||'>' || EOL;
4703 end if;
4704 if tab_employer(lLigne2) is not null then
4705 l_final_xml_string := l_final_xml_string
4706 ||'<emplr_Ligne2'||l_index||'>' ||substr(tab_employer(lLigne2),1,40) || '</emplr_Ligne2'||l_index||'>' || EOL;
4707 end if;
4708 if tab_employer(lVille) is not null then
4709 l_final_xml_string := l_final_xml_string
4710 ||'<emplr_Ville'||l_index||'>' ||substr(tab_employer(lVille),1,40) || '</emplr_Ville'||l_index||'>' || EOL;
4711 end if;
4712 ******************************************************/
4713
4714
4715 hr_utility.trace('rl1_xml_employer_start: l_final_xml_string = ' || l_final_xml_string);
4716 if l_index=1 then
4717 p_emplyr_final1 := l_final_xml_string;
4718 end if;
4719 if l_index=2 then
4720 p_emplyr_final2 := l_final_xml_string;
4721 end if;
4722 if l_index=3 then
4723 p_emplyr_final3 := l_final_xml_string;
4724 end if;
4725 end loop;
4726
4727 END RL1XML_emplyer_data;
4728
4729 PROCEDURE archive_ca_deinit (p_pactid IN NUMBER) IS
4730
4731 CURSOR c_get_report_type ( p_pactid number) IS
4732 SELECT report_type
4733 FROM pay_payroll_actions
4734 WHERE payroll_action_id = p_pactid;
4735
4736 l_report_type pay_payroll_actions.report_type%type;
4737
4738 BEGIN
4739
4740 open c_get_report_type(p_pactid);
4741 fetch c_get_report_type
4742 into l_report_type;
4743 close c_get_report_type;
4744
4745 IF l_report_type = 'RL1PAPERPDF' THEN
4746 pay_ca_payroll_utils.delete_actionid(p_pactid);
4747 END IF;
4748
4749 END archive_ca_deinit;
4750 /******************************************** *************************************************************/
4751
4752 END pay_ca_rl1_mag;