[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_RL1_CAN_MAG
Source
1 PACKAGE BODY pay_ca_rl1_can_mag AS
2 /* $Header: pycarlcmg.pkb 120.12.12020000.6 2012/12/19 06:24:26 sbachu ship $ */
3
4 -- Name : get_report_parameters
5
6 -----------------------------------------------------------------------------
7 --
8 -- Purpose
9 -- The procedure gets the 'parameter' for which the report is being
13 -- p_pactid Payroll_action_id passed from pyugen process
10 -- run i.e., the period, state and business organization.
11 --
12 -- Arguments
14 -- p_year_start Start Date of the period for which the report
15 -- has been requested
16 -- p_year_end End date of the period
17 -- p_business_group_id Business group for which the report is being run
18 -- p_report_type Type of report being run RL1
19 --
20 -- Notes
21 ----------------------------------------------------------------------------
22
23
24 PROCEDURE get_report_parameters
25 ( p_pactid IN NUMBER,
26 p_year_start IN OUT NOCOPY DATE,
27 p_year_end IN OUT NOCOPY DATE,
28 p_report_type IN OUT NOCOPY VARCHAR2,
29 p_business_group_id IN OUT NOCOPY NUMBER,
30 p_legislative_parameters IN OUT NOCOPY VARCHAR2
31 ) IS
32 BEGIN
33 --hr_utility.trace_on('Y','RL1MAG');
34 hr_utility.set_location('pay_ca_rl1_can_mag.get_report_parameters', 10);
35
36 SELECT ppa.start_date,
37 ppa.effective_date,
38 ppa.business_group_id,
39 ppa.report_type,
40 ppa.legislative_parameters
41 INTO p_year_start,
42 p_year_end,
43 p_business_group_id,
44 p_report_type,
45 p_legislative_parameters
46 FROM pay_payroll_actions ppa
47 WHERE payroll_action_id = p_pactid;
48
49 hr_utility.set_location('pay_ca_rl1_can_mag.get_report_parameters', 20);
50
51 END get_report_parameters;
52
53 FUNCTION convert_special_char( p_data varchar2)
54 RETURN varchar2 IS
55 l_data VARCHAR2(2000);
56 l_output varchar2(2000);
57 cursor c_uppercase(p_input_string varchar2) is
58 select
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(
72 replace(
73 replace(
74 replace(convert(p_input_string,'UTF8'),
75 utl_raw.cast_to_varchar2(hextoraw('C380')),'A'
76 ),
77 utl_raw.cast_to_varchar2(hextoraw('C38A')),'E'
78 ),
79 utl_raw.cast_to_varchar2(hextoraw('C387')),'C'
80 ),
81 utl_raw.cast_to_varchar2(hextoraw('C389')),'E'
82 ),
83 utl_raw.cast_to_varchar2(hextoraw('C39C')),'U'
84 ),
85 utl_raw.cast_to_varchar2(hextoraw('C399')),'U'
86 ),
87 utl_raw.cast_to_varchar2(hextoraw('C39B')),'U'
88 ),
89 utl_raw.cast_to_varchar2(hextoraw('C394')),'O'
90 ),
91 utl_raw.cast_to_varchar2(hextoraw('C38F')),'I'
92 ),
93 utl_raw.cast_to_varchar2(hextoraw('C38E')),'I'
94 ),
95 utl_raw.cast_to_varchar2(hextoraw('C388')),'E'
96 ),
97 utl_raw.cast_to_varchar2(hextoraw('C38B')),'E'
98 ),
99 utl_raw.cast_to_varchar2(hextoraw('C382')),'A'
100 ),
101 utl_raw.cast_to_varchar2(hextoraw('C592')),'OE'
102 ),
103 utl_raw.cast_to_varchar2(hextoraw('C386')),'AE'
104 ),
105 utl_raw.cast_to_varchar2(hextoraw('C3A9')),'e'
106 )
107 from dual;
108
109 BEGIN
110 l_data := trim(p_data);
111 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
112 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
113 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
114 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
115 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
116
117 open c_uppercase(l_data);
118 fetch c_uppercase into l_output;
119 if c_uppercase%NOTFOUND then
120 l_output := l_data;
121 end if;
122 close c_uppercase;
123
124 RETURN l_output;
125 END;
126
127 PROCEDURE validate_transmitter_info(p_payroll_action_id IN NUMBER,
128 p_bg_id IN NUMBER,
129 p_effective_date IN DATE) IS
130 BEGIN
131
132 DECLARE
133
134 CURSOR cur_arch_pactid(p_transmitter_org_id NUMBER, p_report_type VARCHAR2) IS
135 SELECT
136 ppa.payroll_action_id
137 FROM
138 pay_payroll_actions ppa
139 WHERE
140 ppa.business_group_id = p_bg_id AND
141 ppa.report_type = decode(p_report_type,
142 'RL1_XML_MAG', 'RL1',
143 'RL1_AMEND_MAG', 'CAEOY_RL1_AMEND_PP') AND
144 ppa.effective_date = p_effective_date AND
145 p_transmitter_org_id =
146 pay_ca_rl1_can_mag.get_parameter('PRE_ORGANIZATION_ID',
147 ppa.legislative_parameters);
148
149 l_transmitter_number VARCHAR2(30);
150 l_transmitter_name VARCHAR2(100);
151 l_type_of_package VARCHAR2(30);
152 l_source_of_slips VARCHAR2(30);
153 dummy NUMBER;
154 dummy1 VARCHAR2(10);
155 l_transmitter_org_id NUMBER;
156 l_arch_pactid NUMBER;
157 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
158 l_address_line1 hr_locations.address_line_1%TYPE;
159 l_locked_report_type pay_payroll_actions.report_type%TYPE;
160 l_mag_pactid NUMBER;
161
162 CURSOR cur_ppa IS
163 SELECT
164 ppa.legislative_parameters
165 FROM
166 pay_payroll_actions ppa
167 WHERE
168 ppa.payroll_action_id = p_payroll_action_id;
169
170 CURSOR c_ppa_report_type(p_pact_id number) IS
171 SELECT
172 ppa.report_type
173 FROM
174 pay_payroll_actions ppa
175 WHERE
176 ppa.payroll_action_id = p_pact_id;
177
178 BEGIN
179
180 OPEN cur_ppa;
181 FETCH cur_ppa
182 INTO l_legislative_parameters;
183 CLOSE cur_ppa;
184
185 l_transmitter_org_id := pay_ca_rl1_can_mag.get_parameter('TRANSMITTER_PRE',
186 l_legislative_parameters);
187
188 hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
189 hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
190 hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
191 hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
192
193 l_mag_pactid := pay_ca_rl1_can_mag.get_parameter('PAY_ACT',
194 l_legislative_parameters);
195
196 OPEN c_ppa_report_type(l_mag_pactid);
197 FETCH c_ppa_report_type
198 INTO l_locked_report_type;
199 CLOSE c_ppa_report_type;
200
201 hr_utility.trace('l_locked_report_type = ' || l_locked_report_type);
202
203 OPEN cur_arch_pactid(l_transmitter_org_id, l_locked_report_type);
204 FETCH cur_arch_pactid
205 INTO l_arch_pactid;
206 CLOSE cur_arch_pactid;
207
208 l_transmitter_number := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NUMBER');
209 l_transmitter_name := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NAME');
210 BEGIN
211
212 hr_utility.trace('l_transmitter_number = ' || l_transmitter_number);
213 SELECT substr(l_transmitter_number,1,2)
214 INTO dummy1
215 FROM dual;
216
217 IF (dummy1 <> 'NP' OR
218 length(l_transmitter_number) <> 8) THEN
219 RAISE INVALID_NUMBER;
220 END IF;
221
222 SELECT to_number(substr(l_transmitter_number,3,6))
223 INTO dummy
224 FROM dual;
225
226 EXCEPTION
227 WHEN INVALID_NUMBER THEN
228 hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
229 hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
230 pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
231 pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
232 hr_utility.raise_error;
233 END;
234
235 l_type_of_package := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE');
236
237 hr_utility.trace('l_type_of_package = ' || l_type_of_package);
238
239 IF l_type_of_package IS NULL THEN
240 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
241 hr_utility.raise_error;
242 END IF;
243
244 l_source_of_slips := get_arch_val(l_arch_pactid,'CAEOY_RL1_SOURCE_OF_SLIPS');
245 hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
246
247 IF l_source_of_slips IS NULL THEN
248 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
249 hr_utility.raise_error;
250 END IF;
251
252 l_address_line1 := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
253 hr_utility.trace('l_address_line1 = ' || l_address_line1);
254
255 IF l_address_line1 IS NULL THEN
256 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
257 hr_utility.raise_error;
258 END IF;
259
260 END;
261
262 END validate_transmitter_info;
263
264 /*Bug 14701466 starts here*/
265 ----------------------------------------------------------------------------
266 --Name
267 -- store_further_information
268 --Purpose
269 -- This procedure stores all the further information codes and their
270 -- corresponding values in global variables so that we can access them
271 -- easily during processing
272 ------------------------------------------------------------------------------
273 procedure store_further_information(p_arch_assact_id IN NUMBER
274 ,p_assgn_id IN NUMBER)
275 is
276 l_cpp_withheld NUMBER;
277 l_cpp_taxable NUMBER;
278 i NUMBER :=0;
279
280 cursor cur_boxo is
281 SELECT 'RZ-'||substr(fdi.user_name,23,2) code,fai.value value
282 FROM ff_database_items fdi,
283 ff_archive_items fai
284 WHERE fai.user_entity_id = fdi.user_entity_id
285 AND fai.context1 = p_arch_assact_id
286 AND fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT____PER_JD_YTD'
287 ORDER BY substr(fdi.user_name,5,4);
288
289 cursor cur_further_info(p_cpp_withheld NUMBER,p_cpp_taxable NUMBER) is
290 select substr(ltrim(rtrim(code)),1,60) code, value value
291 from (
292 select 'B-1' code,to_char(p_cpp_withheld) value from dual
293 where p_cpp_withheld <> 0
294 union
295 select 'G-2' code,to_char(p_cpp_taxable) value from dual
296 where p_cpp_taxable <> 0
297 union
298 select decode(fdi.user_name,'CAEOY_RL1_FURTHER_INFO_AMOUNT_201_AMT_PER_JD_YTD','201',
299 'CAEOY_RL1_FURTHER_INFO_AMOUNT_211_AMT_PER_JD_YTD','211',
300 'CAEOY_RL1_FURTHER_INFO_AMOUNT_235_AMT_PER_JD_YTD','235',
301 substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),1,1)||'-'||
302 substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),2)) code,
303 fai.value value
304 from ff_database_items fdi,
308 and fdi.user_name LIKE 'CAEOY_RL1_FURTHER_INFO_AMOUNT_%_AMT_PER_JD_YTD'
305 ff_archive_items fai
306 where fai.user_entity_id=fdi.user_entity_id
307 and fai.context1= p_arch_assact_id
309 union
310 select 'G-1' code,
311 pai.action_information5 value
312 from pay_action_information pai
313 where pai.action_context_id = p_arch_assact_id
314 and pai.action_information4 = 'G-1'
315 ) order by code;
316
317 begin
318 l_cpp_withheld := pay_ca_archive_utils.get_archive_value(p_arch_assact_id
319 ,'CAEOY_CPP_EE_WITHHELD_PER_YTD');
320 l_cpp_taxable := pay_ca_archive_utils.get_archive_value(p_arch_assact_id
321 ,'CAEOY_CPP_EE_TAXABLE_PER_YTD');
322
323 hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
324 hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
325 g_further_info_list.delete;
326 for rec in cur_boxo
327 loop
328 i:=i+1;
329 hr_utility.trace('i '||i);
330 hr_utility.trace('rec.code '||rec.code);
331 hr_utility.trace('rec.value '||rec.value);
332 g_further_info_list(i).code := rec.code;
333 g_further_info_list(i).amount := rec.value;
334 end loop;
335
336 for rec in cur_further_info(l_cpp_withheld,l_cpp_taxable)
337 loop
338 i:=i+1;
339 hr_utility.trace('i '||i);
340 hr_utility.trace('rec.code '||rec.code);
341 hr_utility.trace('rec.value '||rec.value);
342 g_further_info_list(i).code := rec.code;
343 g_further_info_list(i).amount := rec.value;
344 end loop;
345
346 END store_further_information;
347
348 procedure get_further_information(p_index in number,
349 p_code in out nocopy varchar2,
350 p_amount in out nocopy varchar2)
351 is
352 begin
353
354 hr_utility.trace('p_index '||p_index);
355 if g_further_info_list.exists(p_index) then
356 p_code := g_further_info_list(p_index).code;
357 p_amount := g_further_info_list(p_index).amount;
358 else
359 p_code := null;
360 p_amount := null;
361 end if;
362
363 end get_further_information;
364 /*Bug 14701466 ends here*/
365 ----------------------------------------------------------------------------
366 --Name
367 -- range_cursor
368 --Purpose
369 -- This procedure defines a SQL statement
370 -- to fetch all the people to be included in the report. This SQL statement
371 -- is used to define the 'chunks' for multi-threaded operation
372 --Arguments
373 -- p_pactid payroll action id for the report
374 -- p_sqlstr the SQL statement to fetch the people
375 ------------------------------------------------------------------------------
376 PROCEDURE range_cursor (
377 p_pactid IN NUMBER,
378 p_sqlstr OUT NOCOPY VARCHAR2
379 )
380 IS
381 p_year_start DATE;
382 p_year_end DATE;
383 p_business_group_id NUMBER;
384 p_report_type VARCHAR2(30);
385
386 BEGIN
387 -- hr_utility.trace_on(null,'PDF');
388 hr_utility.set_location( 'pay_ca_rl1_can_mag.range_cursor', 10);
389
390 p_sqlstr := 'select distinct paaf.person_id
391 from per_all_assignments_f paaf,
392 pay_payroll_actions ppa,
393 pay_payroll_actions ppa1,
394 pay_assignment_actions paa
395 where ppa.payroll_action_id = paa.payroll_action_id
396 and paa.assignment_id = paaf.assignment_id
397 and ppa1.payroll_action_id = :payroll_action_id
398 and ppa.payroll_action_id =
399 to_number(pay_ca_rl1_can_mag.get_parameter(''PAY_ACT'', ppa1.legislative_parameters))
400 and paaf.person_id =
401 nvl(pay_ca_rl1_can_mag.get_parameter(''PER_ID'',ppa1.legislative_parameters),paaf.person_id)
402 order by paaf.person_id';
403 -- order by paaf.person_id clause added for bug 9211063
404
405 hr_utility.set_location( 'pay_ca_rl1_can_mag.range_cursor',20);
406
407 END range_cursor;
408
409 -------------------------------------------------------------------------------
410 --Name
411 -- create_assignment_act
412 --Purpose
413 -- Creates assignment actions for the payroll action associated with the
414 -- report
415 --Arguments
416 -- p_pactid payroll action for the report
417 -- p_stperson starting person id for the chunk
418 -- p_endperson last person id for the chunk
419 -- p_chunk size of the chunk
420 --Note
421 -- The procedure processes assignments in 'chunks' to facilitate
422 -- multi-threaded operation. The chunk is defined by the size and the
423 -- starting and ending person id. An interlock is also created against the
424 -- pre-processor assignment action to prevent rolling back of the archiver.
425 ------------------------------------------------------------------------------
426 PROCEDURE create_assignment_act(
427 p_pactid IN NUMBER,
428 p_stperson IN NUMBER,
429 p_endperson IN NUMBER,
430 p_chunk IN NUMBER )
431 IS
432
433 l_year_start DATE;
434 l_year_end DATE;
435 l_effective_end_date DATE;
436 l_report_type VARCHAR2(30);
437 l_legislative_parameters varchar2(240);
438 l_business_group_id NUMBER;
439 l_person_id NUMBER;
440 l_asg_set_id NUMBER;
444 l_tax_unit_id NUMBER;
441 l_assignment_id NUMBER;
442 l_assignment_action_id NUMBER;
443 l_value NUMBER;
445 lockingactid NUMBER;
446
447 l_prev_payact NUMBER;
448 l_payroll_act NUMBER;
449 l_quebec_val VARCHAR2(20);
450 l_quebec_no VARCHAR2(20);
451 l_quebec_name VARCHAR2(240);
452 l_return NUMBER;
453
454 CURSOR c_all_asg IS
455 select paaf.person_id,
456 paaf.assignment_id,
457 paa1.tax_unit_id,
458 paaf.effective_end_date,
459 paa.assignment_action_id,
460 ppa2.payroll_action_id
461 from
462 per_all_assignments_f paaf,
463 pay_payroll_actions ppa,
464 pay_payroll_actions ppa1,
465 pay_payroll_actions ppa2,
466 pay_assignment_actions paa,
467 pay_assignment_actions paa1,
468 pay_action_interlocks int
469 where
470 ppa.payroll_action_id = paa.payroll_action_id
471 and paa.assignment_id = paaf.assignment_id
472 and ppa1.payroll_action_id = p_pactid
473 and paaf.person_id between p_stperson and p_endperson
474 and ppa.payroll_action_id =
475 to_number(pay_ca_rl1_can_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
476 and ppa2.report_type in ('RL1','CAEOY_RL1_AMEND_PP')
477 and int.locking_action_id = paa.assignment_action_id
478 and paa1.assignment_action_id = int.locked_action_id
479 and ppa2.payroll_action_id = paa1.payroll_action_id
480 and ppa2.action_status = 'C'
481 and paa.assignment_action_id
482 not in (select paa2.assignment_action_id
483 from pay_action_interlocks pai, pay_assignment_actions paa2
484 where paa2.assignment_action_id = pai.locked_action_id
485 and paa2.payroll_action_id =
486 to_number(pay_ca_rl1_can_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
487 )
488 -- and paaf.effective_start_date condition added for bug 9142143
489 and paaf.effective_start_date = (select max(paaf2.effective_start_date)
490 from per_all_assignments_f paaf2
491 where paaf2.assignment_id = paa.assignment_id
492 and paaf2.effective_start_date <= ppa.effective_date);
493
494
495 CURSOR c_all_asg_in_asgset IS
496 select paaf.person_id,
497 paaf.assignment_id,
498 paa1.tax_unit_id,
499 paaf.effective_end_date,
500 paa.assignment_action_id,
501 ppa2.payroll_action_id
502 from
503 per_all_assignments_f paaf,
504 pay_payroll_actions ppa,
505 pay_payroll_actions ppa1,
506 pay_payroll_actions ppa2,
507 pay_assignment_actions paa,
508 pay_assignment_actions paa1,
509 pay_action_interlocks int
510 where
511 ppa.payroll_action_id = paa.payroll_action_id
512 and paa.assignment_id = paaf.assignment_id
513 and ppa1.payroll_action_id = p_pactid
514 and paaf.person_id between p_stperson and p_endperson
515 and ppa.payroll_action_id =
516 to_number(pay_ca_rl1_can_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
517 and ppa2.report_type in ('RL1','CAEOY_RL1_AMEND_PP')
518 and int.locking_action_id = paa.assignment_action_id
519 and paa1.assignment_action_id = int.locked_action_id
520 and ppa2.payroll_action_id = paa1.payroll_action_id
521 and ppa2.action_status = 'C'
522 and exists (select 1
523 from hr_assignment_set_amendments hasa,
524 per_assignments_f paf
525 where hasa.assignment_set_id = l_asg_set_id
526 and upper(hasa.include_or_exclude) = 'I'
527 and hasa.assignment_id = paf.assignment_id
528 and paf.person_id = paaf.person_id)
529 and paa.assignment_action_id
530 not in (select paa2.assignment_action_id
531 from pay_action_interlocks pai, pay_assignment_actions paa2
532 where paa2.assignment_action_id = pai.locked_action_id
533 and paa2.payroll_action_id =
534 to_number(pay_ca_rl1_can_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
535 )
536 -- and paaf.effective_start_date condition added for bug 9142143
537 and paaf.effective_start_date = (select max(paaf2.effective_start_date)
538 from per_all_assignments_f paaf2
539 where paaf2.assignment_id = paa.assignment_id
540 and paaf2.effective_start_date <= ppa.effective_date);
541
542
543 BEGIN
544
545 -- hr_utility.trace_on('Y','RL1MAG');
546
547 l_prev_payact := -1;
548 hr_utility.set_location( 'pay_ca_rl1_can_mag.create_assignement_act',10);
549
550 get_report_parameters(
551 p_pactid,
552 l_year_start,
553 l_year_end,
554 l_report_type,
555 l_business_group_id,
556 l_legislative_parameters
557 );
558
562
559 validate_transmitter_info(p_pactid,
560 l_business_group_id,
561 l_year_end);
563
564 hr_utility.set_location( 'pay_ca_rl1_can_mag.create_assignement_act',20);
565
566 l_asg_set_id := to_number(pay_ca_rl1_can_mag.get_parameter('ASG_SET_ID',l_legislative_parameters));
567 hr_utility.trace('Assignment Set Id : '|| to_char(l_asg_set_id));
568
569 IF l_asg_set_id IS NOT NULL THEN
570 OPEN c_all_asg_in_asgset;
571 ELSE
572 OPEN c_all_asg;
573 END IF;
574
575 hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 23);
576
577 LOOP
578 IF l_asg_set_id IS NOT NULL THEN
579 FETCH c_all_asg_in_asgset INTO l_person_id,
580 l_assignment_id,
581 l_tax_unit_id,
582 l_effective_end_date,
583 l_assignment_action_id,
584 l_payroll_act;
585
586 hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 25);
587
588 EXIT WHEN c_all_asg_in_asgset%NOTFOUND;
589 ELSE
590 FETCH c_all_asg INTO l_person_id,
591 l_assignment_id,
592 l_tax_unit_id,
593 l_effective_end_date,
594 l_assignment_action_id,
595 l_payroll_act;
596
597 hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 30);
598
599 EXIT WHEN c_all_asg%NOTFOUND;
600 END IF;
601
602 -- Added by ssmukher for validating the
603 -- Quebec Identification Number
604
605 if l_prev_payact <> l_payroll_act then
606 hr_utility.trace('The payroll action id '||l_payroll_act);
607
608 l_prev_payact := l_payroll_act;
609 l_quebec_val := get_arch_val(l_payroll_act,'CAEOY_RL1_QUEBEC_BN');
610 l_quebec_name := get_arch_val(l_payroll_act,'CAEOY_RL1_EMPLOYER_NAME');
611
612 hr_utility.trace('The Quebec Number is '||l_quebec_val);
613
614 l_quebec_no := substr(l_quebec_val ,1,10);
615
616 hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
617 hr_utility.trace('l_quebec_name ='|| l_quebec_name);
618 l_return := validate_quebec_number(l_quebec_val,l_quebec_name);
619
620 end if ;
621
622
623
624 --Create the assignment action for the record
625
626 hr_utility.trace('Assignment Fetched - ');
627 hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
628 hr_utility.trace('Person Id : '|| to_char(l_person_id));
629 hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
630 hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
631 hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 40);
632
633 SELECT pay_assignment_actions_s.nextval
634 INTO lockingactid
635 FROM dual;
636
637 hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 50);
638 hr_nonrun_asact.insact(lockingactid,
639 l_assignment_id,
640 p_pactid,
641 p_chunk,
642 l_tax_unit_id);
643
644 hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 60);
645
646 hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
647
648 hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 70);
649 hr_utility.trace('Interlock Created - ');
650 hr_utility.trace('Locking Action : '|| to_char(lockingactid));
651 hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
652 END LOOP;
653
654 IF l_asg_set_id IS NOT NULL THEN
655 CLOSE c_all_asg_in_asgset;
656 ELSE
657 CLOSE c_all_asg;
658 END IF;
659
660 END create_assignment_act;
661
662
663 function get_parameter(name in varchar2, parameter_list varchar2) return varchar2 is
664 start_ptr number;
665 end_ptr number;
666 token_val pay_payroll_actions.legislative_parameters%type;
667 par_value pay_payroll_actions.legislative_parameters%type;
668 begin
669 --
670 token_val := name||'=';
671 --
672 start_ptr := instr(parameter_list, token_val) + length(token_val);
673 end_ptr := instr(parameter_list, ' ',start_ptr);
674 --
675 /* if there is no spaces use then length of the string */
676 if end_ptr = 0 then
677 end_ptr := length(parameter_list)+1;
678 end if;
679 --
680 /* Did we find the token */
681 if instr(parameter_list, token_val) = 0 then
682 par_value := NULL;
683 else
684 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
685 end if;
686 --
687 return par_value;
688 --
689 end get_parameter;
690
691 /* Added by ssmukher for Bug 3353115 */
692 /* The check digit calculated by the method below
693 must be the same as the 10th digit of the
694 identification number or the number is invalid.
695
696 Example of the modulus 11 method:
697 The identification number is 2345678908.
698 Use the first nine digits to validate the identification number.
699 The tenth digit is the check digit (in this case 8).
700
701 Formula:
705 the right.
702 Beginning with the ninth digit (extreme right), multiply each digit by the
703 factor indicated.
704 The factors form a circular sequence of the values 2 through 7, beginning on
706 First nine digits of the identification number 2 3 4 5 6 7 8 9 0
707 Multiply each digit by the factor indicated. x 4 x 3 x 2 x 7 x 6 x 5 x 4 x 3
708 x 2
709 Results 8 9 8 35 36 35 32 27 0
710 Add the results
711 (8 + 9 + 8 + 35 + 36 + 35 + 32 + 27 + 0). = 190
712 Divide the result by 11 (190/11). = 17 remainder 3
713 If the remainder is 0, the check digit is 1. If the remainder is 1, the check
714 digit is 0.
715 For any other remainder obtained, the check digit is the difference between
716 11 and that remainder.
717 Subtract the remainder obtained from 11 (11 - 3) = 8
718 */
719
720 FUNCTION validate_quebec_number (p_quebec_no IN VARCHAR2,p_qin_name varchar2)
721 RETURN NUMBER IS
722
723 l_quebec NUMBER;
724 l_rem NUMBER;
725 i NUMBER;
726 l_max NUMBER;
727 l_total NUMBER;
728 l_min NUMBER;
729 l_modulus NUMBER;
730 l_chk_digit NUMBER;
731 l_act_chk_number NUMBER;
732
733 BEGIN
734 i := 1;
735 l_min := 2;
736 l_max := 7;
737 l_total := 0;
738
739 l_act_chk_number := to_number(substr(p_quebec_no,10,1));
740 hr_utility.trace('here1');
741 if TRANSLATE(p_quebec_no,'0123456789','9999999999') = '9999999999RS9999' then
742 l_quebec := to_number(substr(p_quebec_no,1,9));
743 loop
744 if i > 9 then
745 exit;
746 end if;
747
748 if l_min > l_max then
749 l_min := 2;
750 end if;
751
752 l_rem := mod(l_quebec,10);
753 l_total := l_total + (l_min * l_rem);
754 l_min := l_min + 1;
755 l_quebec := ((l_quebec - l_rem)/10);
756 i := i+ 1;
757
758 end loop;
759
760 l_modulus := mod(l_total, 11);
761 if l_modulus = 0 then
762 l_chk_digit := 1;
763 elsif l_modulus = 1 then
764 l_chk_digit := 0;
765 else
766 l_chk_digit := 11 - l_modulus;
767 end if;
768
769 if l_chk_digit <> l_act_chk_number then
770 hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
771 hr_utility.set_message_token('PRE_NAME',p_qin_name);
772 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
773 pay_core_utils.push_token('PRE_NAME',p_qin_name);
774 hr_utility.raise_error;
775 end if;
776 else
777
778 hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
779 hr_utility.set_message_token('PRE_NAME',p_qin_name);
780 pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
781 pay_core_utils.push_token('PRE_NAME',p_qin_name);
782 hr_utility.raise_error;
783
784 end if;
785
786 return l_chk_digit;
787
788 END;
789
790 FUNCTION get_arch_val(p_context_id IN NUMBER,
791 p_user_name IN VARCHAR2)
792 RETURN varchar2 IS
793
794 cursor cur_archive (b_context_id NUMBER, b_user_name VARCHAR2) is
795 select fai.value
796 from ff_archive_items fai,
797 ff_database_items fdi
798 where fai.user_entity_id = fdi.user_entity_id
799 and fai.context1 = b_context_id
800 and fdi.user_name = b_user_name;
801
802 l_return VARCHAR2(240);
803
804 BEGIN
805 open cur_archive(p_context_id,p_user_name);
806 fetch cur_archive into l_return;
807 close cur_archive;
808
809 return (l_return);
810 END;
811
812 PROCEDURE xml_transmitter_record IS
813 BEGIN
814
815 DECLARE
816
817 cursor c_rl_package_type is
818 select target1.ORG_INFORMATION6 Type_of_Package
819 from hr_organization_information target1
820 where target1.organization_id = pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
821 and target1.org_information_context = 'Prov Reporting Est';
822
823 l_final_xml CLOB;
824 l_final_xml_string VARCHAR2(32000);
825 l_is_temp_final_xml VARCHAR2(2);
826
827 TYPE transmitter_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
828
829 tab_transmitter transmitter_info;
830
831 lAnnee NUMBER;
832 lTypeEnvoi NUMBER;
833 lProvenance NUMBER;
834 lNo NUMBER;
835 lType NUMBER;
836 lNom1 NUMBER;
837 lNom2 NUMBER;
838 lLigne1 NUMBER;
839 lLigne2 NUMBER;
840 lVille NUMBER;
841 lProvince NUMBER;
842 lCodePostal NUMBER;
843 lNom NUMBER;
844 lIndRegional NUMBER;
845 lTel NUMBER;
846 lPosteTel NUMBER;
847 lLangue NUMBER;
848 lANom NUMBER;
849 lAIndRegional NUMBER;
850 lATel NUMBER;
851 lAPosteTel NUMBER;
852 lALangue NUMBER;
853 lNoConcepteur NUMBER;
854
855 EOL VARCHAR2(5);
856 l_address_line hr_locations.address_line_1%TYPE;
857 l_contact VARCHAR2(60);
858 l_transmitter_name VARCHAR2(100);
859 l_context1 ff_archive_items.context1%TYPE;
860 l_taxation_year VARCHAR2(4);
861 l_return VARCHAR2(60);
862 l_certification_no VARCHAR2(30);
866 BEGIN
863 l_VersionSchema VARCHAR2(20);
864 l_rl_package_type VARCHAR2(20);
865
867
868 hr_utility.trace('XML Transmitter');
869
870 SELECT
871 fnd_global.local_chr(13) || fnd_global.local_chr(10)
872 INTO EOL
873 FROM dual;
874
875 lAnnee := 1;
876 lTypeEnvoi := 2;
877 lProvenance := 3;
878 lNo := 4;
879 lType := 5;
880 lNom1 := 6;
881 lNom2 := 7;
882 lLigne1 := 8;
883 lLigne2 := 9;
884 lVille := 10;
885 lProvince := 11;
886 lCodePostal := 12;
887 lNom := 13;
888 lIndRegional := 14;
889 lTel := 15;
890 lPosteTel := 16;
891 lLangue := 17;
892 lANom := 18;
893 lAIndRegional := 19;
894 lATel := 20;
895 lAPosteTel := 21;
896 lALangue := 22;
897 lNoConcepteur := 23;
898
899
900 l_taxation_year
901 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
902
903 /* commented for bug 9142143
904 l_context1
905 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
906 */
907 select max(paa1.payroll_action_id)
908 into l_context1
909 from pay_assignment_actions paa,
910 pay_assignment_actions paa1,
911 pay_action_interlocks int
912 where int.locking_action_id = paa.assignment_action_id
913 AND paa1.assignment_action_id = int.locked_action_id
914 and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
915
916 hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
917 hr_utility.trace('XML Transmitter: l_context1 = ' || to_char(l_context1));
918 --hr_utility.trace('XML Transmitter: l_pre_id = ' || l_pre_id);
919
920 -- Annee
921 tab_transmitter(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' ||EOL;
922 hr_utility.trace('tab_transmitter(lAnnee) = ' || tab_transmitter(lAnnee));
923
924 -- TypeEnvoi
925 OPEN c_rl_package_type;
926 FETCH c_rl_package_type INTO l_rl_package_type;
927 CLOSE c_rl_package_type;
928
929 tab_transmitter(lTypeEnvoi) := '<TypeEnvoi>' ||l_rl_package_type|| '</TypeEnvoi>' || EOL;
930
931 hr_utility.trace('tab_transmitter(lTypeEnvoi) = ' ||
932 tab_transmitter(lTypeEnvoi));
933
934 tab_transmitter(lProvenance) := '<Provenance>' ||
935 pay_ca_archive_utils.get_archive_value(l_context1,
936 'CAEOY_RL1_SOURCE_OF_SLIPS') || '</Provenance>' || EOL;
937
938 hr_utility.trace('tab_transmitter(lProvenance) = ' || tab_transmitter(lProvenance));
939
940 tab_transmitter(lNo) := '<No>' ||
941 pay_ca_archive_utils.get_archive_value(l_context1,
942 'CAEOY_RL1_TRANSMITTER_NUMBER') || '</No>' || EOL;
943
944 hr_utility.trace('tab_transmitter(lNo) = ' || tab_transmitter(lNo));
945
946 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
947 'CAEOY_RL1_TRANSMITTER_TYPE');
948 IF l_return IS NOT NULL THEN
949 tab_transmitter(lType) := '<Type>' || l_return || '</Type>' || EOL;
950 ELSE
951 tab_transmitter(lType) := NULL;
952 END IF;
953
954 hr_utility.trace('tab_transmitter(lType) = ' || tab_transmitter(lType));
955
956 l_transmitter_name := pay_ca_archive_utils.get_archive_value(l_context1,
957 'CAEOY_RL1_TRANSMITTER_NAME');
958
959 tab_transmitter(lNom1) := '<Nom1>' ||
960 convert_special_char(substr(l_transmitter_name,1,30)) || '</Nom1>' || EOL;
961
962 hr_utility.trace('tab_transmitter(lNom1) = ' || tab_transmitter(lNom1));
963
964 l_return := substr(l_transmitter_name,31,30);
965 IF l_return IS NOT NULL THEN
966 tab_transmitter(lNom2) := '<Nom2>' || convert_special_char(l_return) || '</Nom2>' || EOL;
967 ELSE
968 tab_transmitter(lNom2) := NULL;
969 END IF;
970
971 hr_utility.trace('tab_transmitter(lNom2) = ' || tab_transmitter(lNom2));
972
973 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
974 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
975
976 tab_transmitter(lLigne1) := '<Ligne1>' ||
977 convert_special_char(substr(l_address_line,1,30)) || '</Ligne1>' || EOL;
978
979 hr_utility.trace('tab_transmitter(lLigne1) = ' || tab_transmitter(lLigne1));
980
981 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
982 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE2');
983
984 IF l_address_line IS NOT NULL THEN
985 tab_transmitter(lLigne2) := '<Ligne2>' ||
986 convert_special_char(substr(l_address_line,1,30)) || '</Ligne2>' || EOL;
987 ELSE
988 tab_transmitter(lLigne2) := NULL;
989 END IF;
990
991 hr_utility.trace('tab_transmitter(lLigne2) = ' || tab_transmitter(lLigne2));
992
993 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
994 'CAEOY_RL1_TRANSMITTER_CITY');
995
996 IF l_address_line IS NOT NULL THEN
997 tab_transmitter(lVille) := '<Ville>' ||
998 convert_special_char(substr(l_address_line,1,30)) || '</Ville>' || EOL;
999 ELSE
1000 tab_transmitter(lVille) := NULL;
1001 END IF;
1002
1003 hr_utility.trace('tab_transmitter(lVille) = ' || tab_transmitter(lVille));
1004
1008 tab_transmitter(lProvince) := '<Province>' ||
1005 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1006 'CAEOY_RL1_TRANSMITTER_PROVINCE');
1007
1009 SUBSTR(hr_general.decode_lookup(
1010 'CA_PROVINCE',l_address_line),1,20) || '</Province>' || EOL;
1011
1012 hr_utility.trace('tab_transmitter(lProvince) = ' || tab_transmitter(lProvince));
1013
1014 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1015 'CAEOY_RL1_TRANSMITTER_POSTAL_CODE');
1016
1017 tab_transmitter(lCodePostal) := '<CodePostal>' ||
1018 substr(replace(l_address_line,' '),1,6) || '</CodePostal>' || EOL;
1019
1020 hr_utility.trace('tab_transmitter(lCodePostal) = ' || tab_transmitter(lCodePostal));
1021
1022 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1023 'CAEOY_RL1_TECHNICAL_CONTACT_NAME');
1024
1025 IF l_contact IS NOT NULL THEN
1026 tab_transmitter(lNom) := '<Nom>' ||
1027 substr(l_contact,1,30) || '</Nom>' || EOL;
1028 ELSE
1029 tab_transmitter(lNom) := NULL;
1030 END IF;
1031
1032 hr_utility.trace('tab_transmitter(lNom) = ' || tab_transmitter(lNom));
1033
1034 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1035 'CAEOY_RL1_TECHNICAL_CONTACT_AREA_CODE');
1036
1037 IF l_contact IS NOT NULL THEN
1038 tab_transmitter(lIndRegional) := '<IndRegional>' ||
1039 l_contact || '</IndRegional>' || EOL;
1040 ELSE
1041 tab_transmitter(lIndRegional) := NULL;
1042 END IF;
1043
1044 hr_utility.trace('tab_transmitter(lIndRegional) = ' || tab_transmitter(lIndRegional));
1045
1046 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1047 'CAEOY_RL1_TECHNICAL_CONTACT_PHONE');
1048 IF l_contact IS NOT NULL THEN
1049 l_contact := substr(l_contact,1,3) || '-' || substr(l_contact,4,4);
1050 tab_transmitter(lTel) := '<Tel>' || l_contact || '</Tel>' || EOL;
1051 ELSE
1052 tab_transmitter(lTel) := NULL;
1053 END IF;
1054 hr_utility.trace('tab_transmitter(lTel) = ' || tab_transmitter(lTel));
1055
1056 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1057 'CAEOY_RL1_TECHNICAL_CONTACT_EXTENSION');
1058
1059 IF l_contact IS NOT NULL THEN
1060 tab_transmitter(lPosteTel) := '<PosteTel>' || l_contact ||
1061 '</PosteTel>' || EOL;
1062 ELSE
1063 tab_transmitter(lPosteTel) := NULL;
1064 END IF;
1065 hr_utility.trace('tab_transmitter(lPosteTel) = ' ||
1066 tab_transmitter(lPosteTel));
1067
1068 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1069 'CAEOY_RL1_TECHNICAL_CONTACT_LANGUAGE');
1070
1071 IF l_contact IS NOT NULL THEN
1072 tab_transmitter(lLangue) := '<Langue>' ||l_contact || '</Langue>' || EOL;
1073 ELSE
1074 tab_transmitter(lLangue) := NULL;
1075 END IF;
1076
1077 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1078 'CAEOY_RL1_ACCOUNTING_CONTACT_NAME');
1079
1080 IF l_contact IS NOT NULL THEN
1081 tab_transmitter(lANom) := '<Nom>' ||
1082 convert_special_char(substr(l_contact,1,30)) || '</Nom>' || EOL;
1083 ELSE
1084 tab_transmitter(lANom) := NULL;
1085 END IF;
1086 hr_utility.trace('tab_transmitter(lANom) = ' || tab_transmitter(lANom));
1087
1088 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1089 'CAEOY_RL1_ACCOUNTING_CONTACT_AREA_CODE');
1090
1091 IF l_contact IS NOT NULL THEN
1092 tab_transmitter(lAIndRegional) := '<IndRegional>' || l_contact ||
1093 '</IndRegional>' || EOL;
1094 ELSE
1095 tab_transmitter(lAIndRegional) := NULL;
1096 END IF;
1097 hr_utility.trace('tab_transmitter(lAIndRegional) = ' || tab_transmitter(lAIndRegional));
1098
1099 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1100 'CAEOY_RL1_ACCOUNTING_CONTACT_PHONE');
1101
1102 IF l_contact IS NOT NULL THEN
1103 l_contact := substr(l_contact,1,3) || '-' || substr(l_contact,4,4);
1104 tab_transmitter(lATel) := '<Tel>' || l_contact || '</Tel>' || EOL;
1105 ELSE
1106 tab_transmitter(lATel) := NULL;
1107 END IF;
1108 hr_utility.trace('tab_transmitter(lATel) = ' || tab_transmitter(lATel));
1109
1110 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1111 'CAEOY_RL1_ACCOUNTING_CONTACT_EXTENSION');
1112
1113 IF l_contact IS NOT NULL THEN
1114 tab_transmitter(lAPosteTel) := '<PosteTel>' || l_contact ||
1115 '</PosteTel>' || EOL;
1116 ELSE
1117 tab_transmitter(lAPosteTel) := NULL;
1118 END IF;
1119 hr_utility.trace('tab_transmitter(lAPosteTel) = ' ||
1120 tab_transmitter(lAPosteTel));
1121
1122 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1123 'CAEOY_RL1_ACCOUNTING_CONTACT_LANGUAGE');
1124 IF l_contact IS NOT NULL THEN
1125 tab_transmitter(lALangue) := '<Langue>' || l_contact ||
1126 '</Langue>' || EOL;
1127 ELSE
1128 tab_transmitter(lALangue) := NULL;
1129 END IF;
1130
1131 hr_utility.trace('tab_transmitter(lALangue) = ' || tab_transmitter(lALangue));
1132
1133 -- For bug 6738509
1134 if(l_taxation_year = '2006') then
1135 l_certification_no := 'RQ-06-01-098';
1136 elsif(l_taxation_year = '2007') then
1137 l_certification_no := 'RQ-07-01-146';
1138 elsif(l_taxation_year = '2008') then
1139 l_certification_no := 'RQ-08-01-114';
1140 elsif(l_taxation_year = '2009') then
1141 l_certification_no := 'RQ-09-01-047'; -- Bug 9206928
1142 elsif(l_taxation_year = '2010') then
1143 l_certification_no := 'RQ-10-01-058'; --Bug 10364963
1144 elsif(l_taxation_year = '2011') then
1145 l_certification_no := 'RQ-11-01-083'; --Bug 13486728
1146 elsif(l_taxation_year = '2012') then
1147 l_certification_no := 'RQ-12-01-111'; --Bug 15996999
1148 else
1149 -- l_certification_no := 'RQ-09-99-999';
1150 /* Modified for bug 10134138
1151 For sending testing file format for certification no will be
1152 RQ-nn-99-999 nn stands for last two digit of taxation year.
1153 */
1154 l_certification_no := 'RQ-'||substr(trim(l_taxation_year),3,2)||'-99-999';
1155 end if;
1156 -- End bug 6738509
1157
1158 tab_transmitter(lNoConcepteur) :=
1159 '<NoCertification>'|| convert_special_char(l_certification_no)
1160 ||'</NoCertification>'|| EOL;
1161
1162 -- Bug 7602718
1163 if(l_taxation_year = '2006') then
1164 l_VersionSchema := '2006.1.2';
1165 elsif(l_taxation_year = '2007') then
1166 l_VersionSchema := '2007.1.1';
1167 else
1168 l_VersionSchema := trim(l_taxation_year)||'.1';
1169 end if;
1170 -- End
1171
1172 l_final_xml_string :=
1173 '<Transmission VersionSchema="'||l_VersionSchema||'" ' ||
1174 'pxmlns="http://www.mrq.gouv.qc.ca/T5">' || EOL ||
1175 '<P>' || EOL ||
1176 tab_transmitter(lAnnee) ||
1177 tab_transmitter(lTypeEnvoi) ||
1178 tab_transmitter(lProvenance) || '<Preparateur>' || EOL ||
1179 tab_transmitter(lNo) ||
1180 tab_transmitter(lType) ||
1181 tab_transmitter(lNom1) ||
1182 tab_transmitter(lNom2) || '<Adresse>' || EOL ||
1183 tab_transmitter(lLigne1) ||
1184 tab_transmitter(lLigne2) ||
1185 tab_transmitter(lVille) ||
1186 tab_transmitter(lProvince) ||
1187 tab_transmitter(lCodePostal) || '</Adresse>' || EOL ||
1188 '</Preparateur>' || EOL || '<Informatique>' || EOL ||
1189 tab_transmitter(lNom) ||
1190 tab_transmitter(lIndRegional) ||
1191 tab_transmitter(lTel) ||
1192 tab_transmitter(lPosteTel) ||
1193 tab_transmitter(lLangue) || '</Informatique>' || EOL ||
1194 '<Comptabilite>' || EOL ||
1195 tab_transmitter(lANom) ||
1196 tab_transmitter(lAIndRegional) ||
1197 tab_transmitter(lATel) ||
1198 tab_transmitter(lAPosteTel) ||
1199 tab_transmitter(lALangue) || '</Comptabilite>' || EOL ||
1200 tab_transmitter(lNoConcepteur) ||
1201 '</P>' || EOL;
1202
1203 hr_utility.trace('xml_transmitter l_final_xml_string = ' ||
1204 l_final_xml_string);
1205 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1206
1207 END;
1208 END xml_transmitter_record;
1209
1210 PROCEDURE end_of_file is
1211 BEGIN
1212
1213 DECLARE
1214
1215 l_final_xml CLOB;
1216 l_final_xml_string VARCHAR2(32000);
1217 l_is_temp_final_xml VARCHAR2(2);
1218
1219 BEGIN
1220
1221 l_final_xml_string := '</Transmission>';
1222
1223 hr_utility.trace('end_of_file l_final_xml_string = '
1224 || l_final_xml_string );
1225 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1226
1230
1227 END;
1228
1229 END;
1231 /***************************************************************/
1232
1233 /*************************** *******************************/
1234
1235 PROCEDURE xml_employee_record IS
1236 BEGIN
1237
1238 DECLARE
1239 /****************************************************/
1240 l_payroll_actid NUMBER;
1241 l_rep_type VARCHAR2(30);
1242 l_business_group_id NUMBER;
1243 l_year_start DATE;
1244 l_year_end DATE;
1245 l_legislative_param pay_payroll_actions.legislative_parameters%type;
1246 l_arch_asg_actid NUMBER;
1247 l_arch_pay_actid NUMBER;
1248 -- l_asg_id NUMBER;
1249 l_emplyr_final1 VARCHAR2(5000);
1250 l_emplyr_final2 VARCHAR2(5000);
1251 l_emplyr_final3 VARCHAR2(5000);
1252
1253 l_footnote_boxo1 VARCHAR2(1000);
1254 l_footnote_boxo2 VARCHAR2(1000);
1255 l_footnote_boxo3 VARCHAR2(1000);
1256 l_person_id1 NUMBER;
1257 l_session_date DATE;
1258 lForm_number NUMBER;
1259 l_neg_bal_exists BOOlEAN := FALSE;
1260 /*Bug 14701466 sbachu*/
1261 lv_rl1_slip_no varchar2(240);
1262 lv_ident_addr_string VARCHAR2(32000);
1263 lv_montants_string VARCHAR2(32000);
1264 TYPE FURTHER_CODES IS VARRAY(80) OF VARCHAR2(10);
1265 TYPE FURTHER_AMOUNTS IS VARRAY(80) OF VARCHAR2(50);
1266 l_further_code FURTHER_CODES:=FURTHER_CODES('','','','','','','','','','','','','',
1267 '','','','','','','','','','','','','',
1268 '','','','','','','','','','','','','',
1269 '','','','','','','','','','','','',
1270 '','','','','','','','','','','','',
1271 '','','','','','','','','','','','',
1272 '','','','','');
1273 l_further_amount FURTHER_AMOUNTS:=FURTHER_AMOUNTS('','','','','','','','','','','','','',
1274 '','','','','','','','','','','','','',
1275 '','','','','','','','','','','','','',
1276 '','','','','','','','','','','','',
1277 '','','','','','','','','','','','',
1278 '','','','','','','','','','','','',
1279 '','','','','');
1280 l_code varchar2(10);
1281 l_amount varchar2(50);
1282 l_i number;
1283 l_j number;
1284 l_k number;
1285 l_m number;
1286 l_further_info_string VARCHAR2(2000);
1287 ln_no_fi_per_slip number;
1288 lv_org_slip_no varchar2(240);
1289
1290 /* added these CURSORs for bug 13360884, to get further information*/
1291 cursor cur_boxo(p_arch_assact_id in pay_assignment_actions.assignment_action_id%TYPE) is
1292 SELECT 'RZ-'||substr(fdi.user_name,23,2) code,to_number(fai.value) value
1293 FROM ff_database_items fdi,
1294 ff_archive_items fai
1295 WHERE fai.user_entity_id = fdi.user_entity_id
1296 AND fai.context1 = p_arch_assact_id
1297 AND fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT____PER_JD_YTD'
1298 ORDER BY substr(fdi.user_name,5,4);
1299
1300 cursor cur_further_info(p_arch_assact_id in pay_assignment_actions.assignment_action_id%TYPE) is
1301 select substr(ltrim(rtrim(code)),1,60) code,value value
1302 from (
1303 select decode(fdi.user_name,'CAEOY_RL1_FURTHER_INFO_AMOUNT_201_AMT_PER_JD_YTD','201',
1304 'CAEOY_RL1_FURTHER_INFO_AMOUNT_235_AMT_PER_JD_YTD','235',
1305 substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),1,1)||'-'||
1306 substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),2)) code,
1307 to_number(fai.value) value
1308 from ff_database_items fdi,
1309 ff_archive_items fai
1310 where fai.user_entity_id=fdi.user_entity_id
1311 and fai.context1= p_arch_assact_id
1312 and fdi.user_name LIKE 'CAEOY_RL1_FURTHER_INFO_AMOUNT_%_AMT_PER_JD_YTD'
1313 union
1314 select 'G-1' code,
1315 to_number(pai.action_information5) value
1316 from pay_action_information pai
1317 where pai.action_context_id = p_arch_assact_id
1318 and pai.action_information4 = 'G-1'
1319 ) order by code;
1320 /* ended these CURSORs for bug 13360884, to get further information*/
1321
1322 cursor c_province( p_arch_asact_id number ) is
1323 select fai.value
1324 from ff_archive_items fai,
1325 ff_database_items fdi
1326 where fai.user_entity_id = fdi.user_entity_id
1327 and fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT'
1328 and fai.context1 =p_arch_asact_id;
1329
1330 l_page_break VARCHAR2(50);
1331 l_final_xml_string1 VARCHAR2(32000);
1332 l_final_xml_string2 VARCHAR2(32000);
1333 l_final_xml_string3 VARCHAR2(32000);
1334 k NUMBER;
1335 addr pay_ca_rl1_reg.primaryaddress;
1336 /********************** ************************/
1337 l_final_xml CLOB;
1338 l_final_xml_string VARCHAR2(32000);
1339 l_is_temp_final_xml VARCHAR2(2);
1340
1341 CURSOR cur_parameters(p_mag_asg_action_id NUMBER) IS
1342 SELECT
1343 pai_arch.locked_action_id,
1344 paa_mag.assignment_id,
1345 pay_magtape_generic.date_earned(ppa.effective_date,paf.assignment_id),
1346 fai.value,
1347 get_parameter('PRE_ORGANIZATION_ID',ppa_arch.legislative_parameters)
1348 FROM
1349 ff_archive_items fai,
1350 ff_database_items fdi,
1354 pay_action_interlocks pai_arch,
1351 per_all_people_f ppf,
1352 per_all_assignments_f paf,
1353 pay_action_interlocks pai_mag,
1355 pay_payroll_actions ppa,
1356 pay_payroll_actions ppa_arch,
1357 pay_assignment_actions paa_mag,
1358 pay_assignment_actions paa_arch
1359 WHERE
1360 paa_mag.assignment_action_id = p_mag_asg_action_id AND
1361 ppa.payroll_action_id = paa_mag.payroll_action_id AND
1362 pai_mag.locking_action_id = paa_mag.assignment_action_id AND
1363 pai_mag.locked_action_id = pai_arch.locking_action_id AND
1364 pai_arch.locked_action_id = paa_arch.assignment_action_id AND
1365 paf.assignment_id = paa_arch.assignment_id AND
1366 ppf.person_id = paf.person_id AND
1367 pay_magtape_generic.date_earned(ppa.effective_date,paf.assignment_id)
1368 between
1369 paf.effective_start_date and paf.effective_end_date AND
1370 pay_magtape_generic.date_earned(ppa.effective_date,paf.assignment_id)
1371 between
1372 ppf.effective_start_date and ppf.effective_end_date AND
1373 fai.context1 = pai_arch.locked_action_id AND
1374 fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT' AND
1375 fai.user_entity_id = fdi.user_entity_id AND
1376 paa_arch.assignment_action_id = fai.context1 AND
1377 ppa_arch.payroll_action_id = paa_arch.payroll_action_id
1378 ORDER BY
1379 ppf.last_name,ppf.first_name,ppf.middle_names;
1380
1381 CURSOR cur_rl1_slip_no( p_person_id number,
1382 p_year varchar2,
1383 p_pre number) IS
1384 SELECT
1385 pei_information7
1386 FROM
1387 per_people_extra_info pei
1388 WHERE
1389 to_number(pei.person_id) = p_person_id AND
1390 pei.information_type = 'PAY_CA_RL1_FORM_NO' AND
1391 to_number(pei.pei_information6) = p_pre AND
1392 substr(pei.pei_information5,1,4) = p_year
1393 ORDER BY pei_information7; /*Bug 13564765*/
1394
1395 l_mag_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
1396 l_arch_action_id pay_assignment_actions.assignment_action_id%TYPE;
1397 l_asg_id per_assignments_f.assignment_id%TYPE;
1398 l_date_earned DATE;
1399 l_province VARCHAR2(30);
1400 l_O_AutreRevenu VARCHAR2(1000);
1401
1402 TYPE employee_info IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
1403
1404 tab_employee employee_info;
1405 tab_xml_employee employee_info;
1406
1407 lAnnee NUMBER;
1408 lNoReleve NUMBER;
1409 lNAS NUMBER;
1410 --SIN1 NUMBER;
1411 --SIN2 NUMBER;
1412 --SIN3 NUMBER;
1413 lNo NUMBER;
1414 lNomFamille NUMBER;
1415 lPrenom NUMBER;
1416 lInitiale NUMBER;
1417 lLigne1 NUMBER;
1418 lLigne2 NUMBER;
1419 lVille NUMBER;
1420 lProvince NUMBER;
1421 lCodePostal NUMBER;
1422 lA_RevenuEmploi NUMBER;
1423 lB_CotisationRRQ NUMBER;
1424 lC_CotisationAssEmploi NUMBER;
1425 lD_CotisationRPA NUMBER;
1426 lE_ImpotQue NUMBER;
1427 lF_CotisationSyndicale NUMBER;
1428 lG_SalaireAdmisRRQ NUMBER;
1429 lV_NourritureLogement NUMBER;
1430 lW_Vehicule NUMBER;
1431 lJ_RegimeAssMaladie NUMBER;
1432 lK_Voyage NUMBER;
1433 lL_AutreAvantage NUMBER;
1434 lM_Commission NUMBER;
1435 lN_DonBienfaisance NUMBER;
1436 lO_AutreRevenu NUMBER;
1437 l_SourceCase NUMBER;
1438 lP_RegimeAssInterEntr NUMBER;
1439 lQ_SalaireDiffere NUMBER;
1440 lR_RevenuIndien NUMBER;
1441 lS_PourboireRecu NUMBER;
1442 lT_PourboireAttribue NUMBER;
1443 lU_RetraiteProgressive NUMBER;
1444 l_ContisationRPC NUMBER;
1445 lH_CotisationRQAP NUMBER;
1446 lI_SalaireAdmisRQAP NUMBER;
1447 /* BUG 13567075 sbachu*/
1448 l_FraisGarde NUMBER;
1449 l_DeductionForce NUMBER;
1450
1451 l_person_id per_people_f.person_id%TYPE;
1452 l_address_line1 per_addresses.address_line1%TYPE;
1453 l_address_line2 per_addresses.address_line2%TYPE;
1454 l_address_line3 per_addresses.address_line3%TYPE;
1455 l_combined_addr VARCHAR2(500);
1456 l_city per_addresses.town_or_city%TYPE;
1457 l_postal_code per_addresses.postal_code%TYPE;
1458 l_country VARCHAR2(60);
1459 l_emp_province per_addresses.region_1%TYPE;
1460 EOL VARCHAR2(5);
1461 l_taxation_year VARCHAR2(5);
1462 l_name VARCHAR2(200);
1463 l_box VARCHAR2(20);
1464 l_boxA VARCHAR2(20);
1465 l_boxB VARCHAR2(20);
1466 l_boxU VARCHAR2(20);
1467 l_boxQ VARCHAR2(20);
1468 l_return VARCHAR2(30);
1469 l_status VARCHAR2(10);
1470 l_addr_begin_tag VARCHAR2(10);
1471 l_addr_end_tag VARCHAR2(10);
1472 l_formatted_box VARCHAR2(20);
1473 l_formatted_code VARCHAR2(20);
1474 l_boxO VARCHAR2(20);
1475 l_other_details VARCHAR2(32000);
1476 l_authorization_code VARCHAR2(100);
1477 l_authorization_header VARCHAR2(100);
1478 l_year VARCHAR2(5);
1479
1480 lBoxA_01 NUMBER;
1481 lBoxA_02 NUMBER;
1485 lBoxA_18 NUMBER;
1482 lBoxA_15 NUMBER;
1483 lBoxA_16 NUMBER;
1484 lBoxA_17 NUMBER;
1486 lBoxA_19 NUMBER;
1487 lBoxA_25 NUMBER;
1488 lBoxA_26 NUMBER;
1489 lBoxA_27 NUMBER;
1490 lBoxD_07 NUMBER;
1491 lBoxD_08 NUMBER;
1492 lBoxD_20 NUMBER;
1493 lBoxK_11 NUMBER;
1494 lBoxO_RA NUMBER;
1495 lBoxO_RB NUMBER;
1496 lBoxO_RC NUMBER;
1497 lBoxO_RD NUMBER;
1498 lBoxO_RE NUMBER;
1499 lBoxO_RF NUMBER;
1500 lBoxO_RG NUMBER;
1501 lBoxO_RH NUMBER;
1502 lBoxO_RI NUMBER;
1503 lBoxO_RJ NUMBER;
1504 lBoxO_RK NUMBER;
1505 lBoxO_RL NUMBER;
1506 lBoxO_RL22 NUMBER;
1507 lBoxO_RL28 NUMBER;
1508 lBoxO_RM NUMBER;
1509 lBoxO_RN NUMBER;
1510 lBoxO_RO NUMBER;
1511 lBoxO_RP NUMBER;
1512 lBoxO_RQ NUMBER;
1513 lBoxO_RR NUMBER;
1514 lBoxO_RS NUMBER;
1515 lBoxO_RT NUMBER;
1516 lBoxO_RU NUMBER;
1517 lBoxO_RV NUMBER;
1518 lBoxO_RW NUMBER;
1519 lBoxO_RX NUMBER;
1520 --Added for bug 9178892
1521 lBoxO_CA NUMBER;
1522 lBoxO_CB NUMBER;
1523 lBoxO_CC NUMBER;
1524 --
1525 lBoxQ_24 NUMBER;
1526 lBoxR_14 NUMBER;
1527 lErrorDetails NUMBER;
1528 lBoxA_29 NUMBER;
1529 lBoxA_30 NUMBER;
1530 lBoxO_RN_31 NUMBER;
1531
1532 CURSOR cur_get_meaning(p_lookup_code VARCHAR2) IS
1533 SELECT
1534 meaning
1535 FROM
1536 hr_lookups
1537 WHERE
1538 lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
1539 lookup_code = p_lookup_code;
1540
1541 l_meaning hr_lookups.meaning%TYPE;
1542 l_msg_code VARCHAR2(30);
1543 l_all_box_0 BOOLEAN;
1544 l_format_mask VARCHAR2(30);
1545 l_sequence_number NUMBER(9);
1546 l_original_slip_no varchar2(30);
1547 l_pre_id number;
1548
1549 BEGIN
1550 -- hr_utility.trace_on(null,'PDF');
1551 hr_utility.trace('inside xml_employee_record');
1552
1553 l_payroll_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1554 hr_utility.trace('l_payroll_actid='||l_payroll_actid);
1555
1556 SELECT ppa.report_type
1557 INTO l_rep_type
1558 from pay_payroll_actions ppa
1559 where payroll_action_id=l_payroll_actid;
1560 hr_utility.trace('report_type='||l_rep_type);
1561
1562 hr_utility.trace('XML Employee');
1563 l_status := 'Success';
1564 l_all_box_0 := TRUE;
1565 l_format_mask := '99999999999999990.99';
1566
1567 SELECT
1568 fnd_global.local_chr(13) || fnd_global.local_chr(10)
1569 INTO EOL
1570 FROM dual;
1571
1572 lAnnee := 1;
1573 lNoReleve := 2;
1574 lNAS := 3;
1575 lNo := 4;
1576 lNomFamille := 5;
1577 lPrenom := 6;
1578 lInitiale := 7;
1579 lLigne1 := 8;
1580 lLigne2 := 9;
1581 lVille := 10;
1582 lProvince := 11;
1583 lCodePostal := 12;
1584 lA_RevenuEmploi := 13;
1585 lB_CotisationRRQ := 14;
1586 lC_CotisationAssEmploi := 15;
1587 lD_CotisationRPA := 16;
1588 lE_ImpotQue := 17;
1589 lF_CotisationSyndicale := 18;
1590 lG_SalaireAdmisRRQ := 19;
1591 lV_NourritureLogement := 20;
1592 lW_Vehicule := 21;
1593 lJ_RegimeAssMaladie := 22;
1594 lK_Voyage := 23;
1595 lL_AutreAvantage := 24;
1596 lM_Commission := 25;
1597 lN_DonBienfaisance := 26;
1598 lO_AutreRevenu := 27;
1599 l_SourceCase := 28;
1600 lP_RegimeAssInterEntr := 29;
1601 lQ_SalaireDiffere := 30;
1602 lR_RevenuIndien := 31;
1603 lS_PourboireRecu := 32;
1604 lT_PourboireAttribue := 33;
1605 lU_RetraiteProgressive := 34;
1606 l_ContisationRPC := 35;
1607 lH_CotisationRQAP:=92;
1608 lI_SalaireAdmisRQAP := 93;
1609 --Added for bug 13567075 /*sbachu*/
1610 l_FraisGarde := 97;
1611 l_DeductionForce := 98;
1612
1613 lBoxA_01 := 36;
1614 lBoxA_02 := 37;
1615 lBoxA_15 := 38;
1616 lBoxA_16 := 39;
1617 lBoxA_17 := 40;
1618 lBoxA_18 := 41;
1619 lBoxA_19 := 42;
1620 lBoxA_25 := 43;
1621 lBoxA_26 := 44;
1622 lBoxA_27 := 45;
1623 lBoxD_07 := 46;
1624 lBoxD_08 := 47;
1625 lBoxD_20 := 48;
1626
1627 lBoxO_RA := 49;
1628 lBoxO_RB := 50;
1629 lBoxO_RC := 51;
1630 lBoxO_RD := 52;
1631 lBoxO_RE := 53;
1632 lBoxO_RF := 54;
1633 lBoxO_RG := 55;
1634 lBoxO_RH := 56;
1635 lBoxO_RI := 57;
1636 lBoxO_RJ := 58;
1637 lBoxO_RK := 59;
1638 lBoxO_RL := 60;
1639 lBoxO_RL22 := 61;
1640 lBoxO_RL28 := 62;
1641 lBoxO_RM := 63;
1642 lBoxO_RN := 64;
1643 lBoxO_RO := 65;
1644 lBoxO_RP := 66;
1645 lBoxO_RQ := 67;
1646 lBoxO_RR := 68;
1647 lBoxO_RS := 69;
1648 lBoxO_RT := 70;
1649 lBoxO_RU := 80;
1650 lBoxO_RV := 81;
1654 lBoxR_14 := 85;
1651 lBoxO_RW := 82;
1652 lBoxO_RX := 83;
1653 lBoxQ_24 := 84;
1655 lBoxK_11 := 86;
1656 lErrorDetails := 87;
1657 lBoxA_29 := 88;
1658 lBoxA_30 := 89;
1659 lBoxO_RN_31 := 90;
1660 lForm_number :=91;
1661 --Added for bug 9178892
1662 lBoxO_CA := 94;
1663 lBoxO_CB := 95;
1664 lBoxO_CC := 96;
1665 --
1666 l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID'));
1667
1668 hr_utility.trace('XML Employee: l_mag_asg_action_id = '
1669 || to_char(l_mag_asg_action_id));
1670
1671 OPEN cur_parameters(l_mag_asg_action_id);
1672 FETCH cur_parameters
1673 INTO
1674 l_arch_action_id,
1675 l_asg_id,
1676 l_date_earned,
1677 l_province,
1678 l_pre_id;
1679 CLOSE cur_parameters;
1680
1681 hr_utility.trace('XML Employee: l_arch_action_id = '
1682 || to_char(l_arch_action_id));
1683 hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1684 hr_utility.trace('XML Employee: l_date_earned = '
1685 || to_char(l_date_earned));
1686 hr_utility.trace('XML Employee: l_province = ' || l_province);
1687 hr_utility.trace('XML Employee: l_pre_id = ' || to_char(l_pre_id));
1688
1689 l_taxation_year
1690 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
1691
1692 l_authorization_header := 'No d''autorisation :';
1693
1694 l_year := pay_ca_archive_utils.get_archive_value(l_arch_pay_actid, 'CAEOY_TAXATION_YEAR');
1695
1696 --Annee
1697 tab_employee(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' || EOL;
1698 hr_utility.trace('tab_employee(lAnnee) = ' || tab_employee(lAnnee));
1699
1700 --NoReleve
1701 l_person_id := to_number(pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1702 'CAEOY_PERSON_ID'));
1703
1704 hr_utility.trace('XML Employee: l_person_id = ' || to_char(l_person_id));
1705 /*Bug 13564765 starts here*/
1706 l_k := 0;
1707 lv_org_slip_no := NULL;
1708 OPEN cur_rl1_slip_no(l_person_id, l_taxation_year, l_pre_id);
1709 loop
1710 FETCH cur_rl1_slip_no into l_original_slip_no;
1711 exit when cur_rl1_slip_no%NOTFOUND;
1712 if l_k = 0 then
1713 l_k := 1;
1714 lv_org_slip_no := l_original_slip_no;
1715 else
1716 lv_org_slip_no := lv_org_slip_no || '|' || l_original_slip_no;
1717 end if;
1718 end loop;
1719 CLOSE cur_rl1_slip_no;
1720
1721 IF lv_org_slip_no IS NULL THEN
1722 l_status := 'Failed';
1723 l_msg_code := 'MISSING_SLIP_NO';
1724 tab_employee(lNoReleve) := NULL;
1725 tab_xml_employee(lNoReleve) := NULL;
1726 ELSE
1727 l_original_slip_no := pay_ca_rl1_mag.get_slip_seq_no('|',lv_org_slip_no,1);
1728 tab_employee(lNoReleve) := '<NoReleve>' || l_original_slip_no ||
1729 '</NoReleve>' || EOL;
1730 tab_xml_employee(lNoReleve) := l_return; --
1731 END IF;
1732 /*Bug 13564765 ends here*/
1733 hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve)|| lv_org_slip_no);
1734 hr_utility.trace('tab_xml_employee(lNoReleve) = ' || tab_xml_employee(lNoReleve)); --
1735
1736 -- NAS
1737 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1738 'CAEOY_EMPLOYEE_SIN');
1739
1740 IF l_return IS NOT NULL THEN
1741 tab_employee(lNAS) := '<NAS>' || l_return || '</NAS>' || EOL;
1742 tab_xml_employee(lNAS) := l_return; --
1743
1744 ELSE
1745 l_Status := 'Failed';
1746 l_msg_code := 'SIN';
1747 tab_employee(lNAS) := NULL;
1748 tab_xml_employee(lNAS) := NULL; --
1749 END IF;
1750 hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
1751 hr_utility.trace('tab_xml_employee(lNAS) = ' || tab_xml_employee(lNAS));
1752
1753 -- No
1754 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1755 'CAEOY_EMPLOYEE_NUMBER');
1756 IF l_return IS NOT NULL THEN
1757 tab_employee(lNo) := '<No>' || convert_special_char(l_return) || '</No>' || EOL;
1758 tab_xml_employee(lNo) := convert_special_char(l_return); --
1759 ELSE
1760 tab_employee(lNo) := NULL;
1761 tab_xml_employee(lNo) := NULL; --
1762 END IF;
1763 hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
1764 hr_utility.trace('tab_xml_employee(lNo) = ' || tab_xml_employee(lNo)); --
1765
1766 -- NomFamille
1767 l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1768 'CAEOY_EMPLOYEE_LAST_NAME');
1769 tab_employee(lNomFamille) := '<NomFamille>' ||
1770 convert_special_char(substr(l_name,1,30)) || '</NomFamille>' || EOL;
1771 tab_xml_employee(lNomFamille) := convert_special_char(substr(l_name,1,20)); --
1772 hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
1773 hr_utility.trace('tab_xml_employee(lNomFamille) = ' || tab_xml_employee(lNomFamille)); --
1774
1775 -- Prenom
1776 l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1777 'CAEOY_EMPLOYEE_FIRST_NAME');
1778 IF l_name is NOT NULL THEN
1779 tab_employee(lPrenom) := '<Prenom>' || convert_special_char(substr(l_name,1,30))
1780 || '</Prenom>' || EOL;
1781 tab_xml_employee(lPrenom) := convert_special_char(substr(l_name,1,20)) ; --
1785 l_msg_code := 'MISSING_EMP_FIRST_NAME';
1782
1783 ELSE
1784 l_status := 'Failed';
1786 tab_employee(lPrenom) := NULL;
1787 tab_xml_employee(lPrenom) := NULL ; --
1788 END IF;
1789 hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
1790 hr_utility.trace('tab_xml_employee(lPrenom) = ' || tab_xml_employee(lPrenom)); --
1791
1792 -- Initiale
1793 l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1794 'CAEOY_EMPLOYEE_INITIAL');
1795 IF l_name is NOT NULL THEN
1796 tab_employee(lInitiale) := '<Initiale>' || substr(l_name,1,1)
1797 || '</Initiale>' || EOL;
1798 tab_xml_employee(lInitiale) := substr(l_name,1,1); --
1799
1800 ELSE
1801 tab_employee(lInitiale) := NULL;
1802 tab_xml_employee(lInitiale) := NULL; --
1803 END IF;
1804 hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
1805 hr_utility.trace('tab_xml_employee(lInitiale) = ' || tab_xml_employee(lInitiale)); --
1806
1807 l_return := pay_ca_emp_address_dtls.get_emp_address(
1808 l_person_id,
1809 l_address_line1,
1810 l_address_line2,
1811 l_address_line3,
1812 l_city,
1813 l_postal_code,
1814 l_country,
1815 l_emp_province
1816 );
1817 -- If Address line 1 is NULL or ' ' then the employee is missing
1818 -- address information - as line 1 is mandatory in the Address form.
1819 -- Need to check data by SS transaction /API.
1820
1821 hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1822 hr_utility.trace('l_address_line1 = ' || l_address_line1);
1823 hr_utility.trace('l_address_line2 = ' || l_address_line2);
1824 hr_utility.trace('l_postal_code = ' || l_postal_code);
1825
1826 -- Address Line 1
1827 IF l_address_line1 IS NULL OR
1828 l_address_line1 = ' ' THEN
1829
1830 l_status := 'Failed';
1831 l_msg_code := 'MISSING_EMP_ADDRESS';
1832
1833 l_addr_begin_tag := NULL;
1834 tab_employee(lLigne1) := NULL;
1835 tab_employee(lLigne2) := NULL;
1836 tab_employee(lVille) := NULL;
1837 tab_employee(lProvince) := NULL;
1838 tab_employee(lCodePostal) := NULL;
1839 tab_employee(lCodePostal) := NULL;
1840 l_addr_end_tag := NULL;
1841
1842 ELSE
1843
1844 l_addr_begin_tag := '<Adresse>';
1845
1846 tab_employee(lLigne1) := '<Ligne1>' ||
1847 convert_special_char(substr(l_address_line1,1,30)) || '</Ligne1>' || EOL;
1848 hr_utility.trace('tab_employee(lLigne1) = ' || tab_employee(lLigne1));
1849
1850 -- Address Line 2
1851 IF ((l_address_line2 IS NULL OR
1852 l_address_line2 <> ' ') OR
1853 (l_address_line3 IS NULL OR
1854 l_address_line3 <> ' ')) THEN
1855 l_combined_addr := rtrim(ltrim(l_address_line2)) || rtrim(ltrim(l_address_line3));
1856 tab_employee(lLigne2) := '<Ligne2>' ||
1857 convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne2>' || EOL;
1858 ELSE
1859 tab_employee(lLigne2) := NULL;
1860 END IF;
1861 hr_utility.trace('tab_employee(lLigne2) = ' || tab_employee(lLigne2));
1862
1863 -- Ville (City)
1864 IF l_city IS NULL OR
1865 l_city <> ' ' THEN
1866 tab_employee(lVille) := '<Ville>' ||
1867 substr(l_city,1,30) || '</Ville>' || EOL;
1868 ELSE
1869 tab_employee(lVille) := NULL;
1870 END IF;
1871 hr_utility.trace('tab_employee(lVille) = ' || tab_employee(lVille));
1872
1873 -- Province
1874 IF l_emp_province IS NULL OR
1875 l_emp_province <> ' ' THEN
1876 IF l_country = 'CA' THEN
1877 tab_employee(lProvince) := '<Province>' ||
1878 SUBSTR(hr_general.decode_lookup(
1879 'CA_PROVINCE',l_emp_province),1,20) || '</Province>' || EOL;
1880 ELSIF l_country = 'US' THEN
1881 tab_employee(lProvince) := '<Province>' || l_emp_province || '</Province>' || EOL;
1882 ELSE
1883 tab_employee(lProvince) := '<Province>' || l_country || '</Province>' || EOL;
1884 END IF;
1885 ELSE
1886 tab_employee(lProvince) := NULL;
1887 END IF;
1888 hr_utility.trace('tab_employee(lProvince) = ' || tab_employee(lProvince));
1889
1890 -- Postal Code
1891 IF l_postal_code IS NULL OR
1892 l_postal_code <> ' ' THEN
1893 tab_employee(lCodePostal) := '<CodePostal>' ||
1894 substr(replace(l_postal_code,' '),1,6) || '</CodePostal>' || EOL;
1895 ELSE
1896 tab_employee(lCodePostal) := NULL;
1897 END IF;
1898 hr_utility.trace('tab_employee(lCodePostal) = ' || tab_employee(lCodePostal));
1899 l_addr_end_tag := '</Adresse>';
1900
1901 END IF;
1902
1903 --end if;
1904 -- Summ (Box A)
1905
1906 l_box := pay_ca_archive_utils.get_archive_value(
1907 l_arch_action_id,
1908 l_province,
1909 'JURISDICTION_CODE',
1910 'CAEOY_GROSS_EARNINGS_PER_JD_YTD');
1911
1912 IF TO_NUMBER(l_box) > 9999999.99 THEN
1913 l_status := 'Failed';
1914 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1915 END IF;
1916
1917 IF l_box IS NOT NULL AND
1921 INTO l_formatted_box
1918 to_number(l_box) <> 0 THEN
1919
1920 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1922 FROM dual;
1923 tab_employee(lA_RevenuEmploi) := '<A_RevenuEmploi>' || l_formatted_box ||
1924 '</A_RevenuEmploi>' || EOL;
1925 tab_xml_employee(lA_RevenuEmploi) := l_formatted_box; --
1926 l_all_box_0 := FALSE;
1927 l_BoxA := l_formatted_box;
1928 ELSE
1929 tab_employee(lA_RevenuEmploi) := NULL;
1930 tab_xml_employee(lA_RevenuEmploi) := NULL; --
1931 END IF;
1932 hr_utility.trace('tab_employee(lA_RevenuEmploi) = ' || tab_employee(lA_RevenuEmploi));
1933 hr_utility.trace('tab_xml_employee(lA_RevenuEmploi) = ' || tab_xml_employee(lA_RevenuEmploi));
1934
1935 -- Summ (Box B)
1936 l_box := pay_ca_archive_utils.get_archive_value(
1937 l_arch_action_id,
1938 l_province,
1939 'JURISDICTION_CODE',
1940 'CAEOY_QPP_EE_WITHHELD_PER_JD_YTD');
1941
1942 IF TO_NUMBER(l_box) > 9999999.99 THEN
1943 l_status := 'Failed';
1944 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1945 END IF;
1946
1947 IF l_box IS NOT NULL AND
1948 to_number(l_box) <> 0 THEN
1949
1950 l_BoxB := l_box;
1951
1952 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1953 INTO l_formatted_box
1954 FROM dual;
1955
1956 tab_employee(lB_CotisationRRQ) := '<B_CotisationRRQ>' || l_formatted_box
1957 || '</B_CotisationRRQ>' || EOL;
1958 tab_xml_employee(lB_CotisationRRQ) := l_formatted_box; --
1959 l_all_box_0 := FALSE;
1960 ELSE
1961
1962 tab_employee(lB_CotisationRRQ) := NULL;
1963 tab_xml_employee(lB_CotisationRRQ) := NULL; --
1964
1965 END IF;
1966 hr_utility.trace('tab_employee(lB_CotisationRRQ) = ' ||
1967 tab_employee(lB_CotisationRRQ));
1968 hr_utility.trace('tab_xml_employee(lB_CotisationRRQ) = ' ||
1969 tab_xml_employee(lB_CotisationRRQ)); --
1970
1971 -- Summ (Box C)
1972 l_box := pay_ca_archive_utils.get_archive_value(
1973 l_arch_action_id,
1974 l_province,
1975 'JURISDICTION_CODE',
1976 'CAEOY_EI_EE_WITHHELD_PER_JD_YTD');
1977
1978 IF TO_NUMBER(l_box) > 9999999.99 THEN
1979 l_status := 'Failed';
1980 l_msg_code := 'AMT_GREATER_THAN_RANGE';
1981 END IF;
1982
1983 IF l_box IS NOT NULL AND
1984 to_number(l_box) <> 0 THEN
1985
1986 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
1987 INTO l_formatted_box
1988 FROM dual;
1989
1990 tab_employee(lC_CotisationAssEmploi) := '<C_CotisationAssEmploi>' ||
1991 l_formatted_box || '</C_CotisationAssEmploi>' || EOL;
1992 tab_xml_employee(lC_CotisationAssEmploi) := l_formatted_box; --
1993 l_all_box_0 := FALSE;
1994 ELSE
1995 tab_employee(lC_CotisationAssEmploi) := NULL;
1996 tab_xml_employee(lC_CotisationAssEmploi) := NULL; --
1997 END IF;
1998
1999 hr_utility.trace('tab_employee(lC_CotisationAssEmploi) = ' ||
2000 tab_employee(lC_CotisationAssEmploi));
2001
2002 hr_utility.trace('tab_xml_employee(lC_CotisationAssEmploi) = ' ||
2003 tab_xml_employee(lC_CotisationAssEmploi)); --
2004 -- Summ (Box D)
2005 l_box := pay_ca_archive_utils.get_archive_value(
2006 l_arch_action_id,
2007 l_province,
2008 'JURISDICTION_CODE',
2009 'CAEOY_RL1_BOXD_PER_JD_YTD');
2010
2011 IF TO_NUMBER(l_box) > 9999999.99 THEN
2012 l_status := 'Failed';
2013 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2014 END IF;
2015
2016 IF l_box IS NOT NULL AND
2017 to_number(l_box) <> 0 THEN
2018
2019 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2020 INTO l_formatted_box
2021 FROM dual;
2022
2023 tab_employee(lD_CotisationRPA) := '<D_CotisationRPA>' ||
2024 l_formatted_box || '</D_CotisationRPA>' || EOL;
2025 tab_xml_employee(lD_CotisationRPA) := l_formatted_box; --
2026 l_all_box_0 := FALSE;
2027 ELSE
2028 tab_employee(lD_CotisationRPA) := NULL;
2029 tab_xml_employee(lD_CotisationRPA) := NULL; --
2030 END IF;
2031 hr_utility.trace('tab_employee(lD_CotisationRPA) = ' ||
2032 tab_employee(lD_CotisationRPA));
2033 hr_utility.trace('tab_xml_employee(lD_CotisationRPA) = ' ||
2034 tab_xml_employee(lD_CotisationRPA));
2035
2036
2037 -- (Box E)
2038
2039 l_box := pay_ca_archive_utils.get_archive_value(
2040 l_arch_action_id,
2041 l_province,
2042 'JURISDICTION_CODE',
2043 'CAEOY_PROV_WITHHELD_PER_JD_YTD');
2044
2045 IF TO_NUMBER(l_box) > 9999999.99 THEN
2046 l_status := 'Failed';
2047 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2048 END IF;
2049
2050 IF l_box IS NOT NULL AND
2051 to_number(l_box) <> 0 THEN
2052
2053 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2054 INTO l_formatted_box
2055 FROM dual;
2056
2057 tab_employee(lE_ImpotQue) := '<E_ImpotQue>' ||
2058 l_formatted_box || '</E_ImpotQue>' || EOL;
2059 tab_xml_employee(lE_ImpotQue) := l_formatted_box ; --
2060 l_all_box_0 := FALSE;
2064 END IF;
2061 ELSE
2062 tab_employee(lE_ImpotQue) := NULL ;
2063 tab_xml_employee(lE_ImpotQue) := NULL ; --
2065
2066 hr_utility.trace('tab_employee(lE_ImpotQue) = ' ||
2067 tab_employee(lE_ImpotQue));
2068 hr_utility.trace('tab_xml_employee(lE_ImpotQue) = ' ||
2069 tab_xml_employee(lE_ImpotQue)); --
2070
2071 -- (Box F)
2072 l_box := pay_ca_archive_utils.get_archive_value(
2073 l_arch_action_id,
2074 l_province,
2075 'JURISDICTION_CODE',
2076 'CAEOY_RL1_BOXF_PER_JD_YTD');
2077
2078 IF TO_NUMBER(l_box) > 9999999.99 THEN
2079 l_status := 'Failed';
2080 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2081 END IF;
2082
2083 IF l_box IS NOT NULL AND
2084 to_number(l_box) <> 0 THEN
2085
2086 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2087 INTO l_formatted_box
2088 FROM dual;
2089
2090 tab_employee(lF_CotisationSyndicale) := '<F_CotisationSyndicale>' ||
2091 l_formatted_box || '</F_CotisationSyndicale>' || EOL;
2092 tab_xml_employee(lF_CotisationSyndicale) := l_formatted_box; --
2093 l_all_box_0 := FALSE;
2094 ELSE
2095 tab_employee(lF_CotisationSyndicale) := NULL;
2096 tab_xml_employee(lF_CotisationSyndicale) := NULL; --
2097 END IF;
2098 hr_utility.trace('tab_employee(lF_CotisationSyndicale) = ' ||
2099 tab_employee(lF_CotisationSyndicale));
2100
2101 hr_utility.trace('tab_xml_employee(lF_CotisationSyndicale) = ' ||
2102 tab_xml_employee(lF_CotisationSyndicale));
2103 -- (Box Q)
2104 l_box := pay_ca_archive_utils.get_archive_value(
2105 l_arch_action_id,
2106 l_province,
2107 'JURISDICTION_CODE',
2108 'CAEOY_RL1_BOXQ_PER_JD_YTD');
2109
2110 IF TO_NUMBER(l_box) > 9999999.99 THEN
2111 l_status := 'Failed';
2112 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2113 END IF;
2114
2115 IF l_box IS NOT NULL AND
2116 to_number(l_box) <> 0 THEN
2117
2118 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2119 INTO l_formatted_box
2120 FROM dual;
2121
2122 tab_employee(lQ_SalaireDiffere) := '<Q_SalaireDiffere>' ||
2123 l_formatted_box || '</Q_SalaireDiffere>' || EOL;
2124 tab_xml_employee(lQ_SalaireDiffere) := l_formatted_box; --
2125 l_all_box_0 := FALSE;
2126 l_BoxQ := l_formatted_box;
2127 ELSE
2128 tab_employee(lQ_SalaireDiffere) := NULL;
2129 tab_xml_employee(lQ_SalaireDiffere) := NULL; --
2130 END IF;
2131 hr_utility.trace('tab_employee(lQ_SalaireDiffere) = ' ||
2132 tab_employee(lQ_SalaireDiffere));
2133
2134 hr_utility.trace('tab_xml_employee(lQ_SalaireDiffere) = ' ||
2135 tab_xml_employee(lQ_SalaireDiffere));
2136 -- (Box U)
2137 l_box := pay_ca_archive_utils.get_archive_value(
2138 l_arch_action_id,
2139 l_province,
2140 'JURISDICTION_CODE',
2141 'CAEOY_RL1_BOXU_PER_JD_YTD');
2142
2143 IF TO_NUMBER(l_box) > 9999999.99 THEN
2144
2145 l_status := 'Failed';
2146 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2147
2148 END IF;
2149
2150 IF l_box IS NOT NULL AND
2151 to_number(l_box) <> 0 THEN
2152
2153 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2154 INTO l_formatted_box
2155 FROM dual;
2156
2157 tab_employee(lU_RetraiteProgressive) := '<U_RetraiteProgressive>' ||
2158 l_formatted_box || '</U_RetraiteProgressive>' || EOL;
2159 tab_xml_employee(lU_RetraiteProgressive) := l_formatted_box; --
2160 l_all_box_0 := FALSE;
2161 l_BoxU := l_formatted_box;
2162 ELSE
2163 tab_employee(lU_RetraiteProgressive) := NULL;
2164 tab_xml_employee(lU_RetraiteProgressive) := NULL; --
2165 END IF;
2166 hr_utility.trace('tab_employee(lU_RetraiteProgressive) = ' ||
2167 tab_employee(lU_RetraiteProgressive));
2168
2169 hr_utility.trace('tab_xml_employee(lU_RetraiteProgressive) = ' ||
2170 tab_xml_employee(lU_RetraiteProgressive));
2171 -- (Box G)
2172 l_box := pay_ca_archive_utils.get_archive_value(
2173 l_arch_action_id,
2174 l_province,
2175 'JURISDICTION_CODE',
2176 --commented for bug 13360872
2177 -- 'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD');
2178 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD');
2179
2180 hr_utility.trace('l_box = ' || l_box);
2181 hr_utility.trace('l_boxA = ' || l_BoxA);
2182 hr_utility.trace('l_boxQ = ' || l_BoxQ);
2183 hr_utility.trace('l_boxU = ' || l_boxU);
2184
2185 IF l_rep_type <>'RL1PAPERPDF' THEN
2186 IF l_box IS NOT NULL THEN
2187
2188 IF TO_NUMBER(l_box) > 9999999.99 THEN
2189
2190 l_status := 'Failed';
2191 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2192
2193 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2194 INTO l_formatted_box
2195 FROM dual;
2196
2197 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
2198 l_formatted_box || '</G_SalaireAdmisRRQ>' || EOL;
2199 --tab_xml_employee(lG_SalaireAdmisRRQ) := l_formatted_box; --
2200
2201 ELSIF to_number(l_box) = 0 THEN
2202
2206
2203 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
2204 '0.00</G_SalaireAdmisRRQ>' || EOL;
2205 --tab_xml_employee(lG_SalaireAdmisRRQ) := '0.00'; --
2207 ELSIF to_number(l_box) <> (NVL(to_number(l_BoxA),0) +
2208 NVL(to_number(l_BoxQ),0) +
2209 NVL(to_number(l_BoxU),0)) THEN
2210
2211 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2212 INTO l_formatted_box
2213 FROM dual;
2214
2215 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
2216 l_formatted_box || '</G_SalaireAdmisRRQ>' || EOL;
2217
2218 --tab_xml_employee(lG_SalaireAdmisRRQ) := l_formatted_box; --
2219 l_all_box_0 := FALSE;
2220
2221 ELSIF to_number(l_box) = (NVL(to_number(l_BoxA),0) +
2222 NVL(to_number(l_BoxQ),0) +
2223 NVL(to_number(l_BoxU),0)) THEN
2224
2225 --tab_employee(lG_SalaireAdmisRRQ) := NULL; --commented for bug 13501125, so that Box G value is displayed
2226 --tab_xml_employee(lG_SalaireAdmisRRQ) := NULL; --
2227
2228 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2229 INTO l_formatted_box
2230 FROM dual;
2231
2232 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
2233 l_formatted_box || '</G_SalaireAdmisRRQ>' || EOL;
2234
2235 END IF;
2236
2237 ELSE
2238 --tab_employee(lG_SalaireAdmisRRQ) := NULL; --commented for bug 13501125
2239 --tab_xml_employee(lG_SalaireAdmisRRQ) := NULL; --
2240
2241 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
2242 '0.00</G_SalaireAdmisRRQ>' || EOL;
2243 END IF;
2244 hr_utility.trace('tab_employee(lG_SalaireAdmisRRQ) = ' ||
2245 tab_employee(lG_SalaireAdmisRRQ));
2246 END IF;
2247
2248 -- (Box V)
2249 l_box := pay_ca_archive_utils.get_archive_value(
2250 l_arch_action_id,
2251 l_province,
2252 'JURISDICTION_CODE',
2253 'CAEOY_RL1_BOXV_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(lV_NourritureLogement) := '<V_NourritureLogement>' ||
2268 l_formatted_box || '</V_NourritureLogement>' || EOL;
2269 tab_xml_employee(lV_NourritureLogement) := l_formatted_box; --
2270 l_all_box_0 := FALSE;
2271 ELSE
2272 tab_employee(lV_NourritureLogement) := NULL;
2273 tab_xml_employee(lV_NourritureLogement) := NULL; --
2274 END IF;
2275
2276 hr_utility.trace('tab_employee(lV_NourritureLogement) = ' ||
2277 tab_employee(lV_NourritureLogement));
2278
2279 hr_utility.trace('tab_xml_employee(lV_NourritureLogement) = ' ||
2280 tab_xml_employee(lV_NourritureLogement));
2281 -- (Box W)
2282 l_box := pay_ca_archive_utils.get_archive_value(
2283 l_arch_action_id,
2284 l_province,
2285 'JURISDICTION_CODE',
2286 'CAEOY_RL1_BOXW_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 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2297 INTO l_formatted_box
2298 FROM dual;
2299
2300 tab_employee(lW_Vehicule) := '<W_Vehicule>' ||
2301 l_formatted_box || '</W_Vehicule>' || EOL;
2302 tab_xml_employee(lW_Vehicule) := l_formatted_box; --
2303 l_all_box_0 := FALSE;
2304 ELSE
2305 tab_employee(lW_Vehicule) := NULL;
2306 tab_xml_employee(lW_Vehicule) := NULL; --
2307 END IF;
2308 hr_utility.trace('tab_employee(lW_Vehicule) = ' ||
2309 tab_employee(lW_Vehicule));
2310
2311 hr_utility.trace('tab_xml_employee(lW_Vehicule) = ' ||
2312 tab_xml_employee(lW_Vehicule));
2313
2314 --(BOX H)
2315 l_box := pay_ca_archive_utils.get_archive_value(
2316 l_arch_action_id,
2317 l_province,
2318 'JURISDICTION_CODE',
2319 'CAEOY_PPIP_EE_WITHHELD_PER_JD_YTD');
2320 IF TO_NUMBER(l_box) > 9999999.99 THEN
2321 l_status := 'Failed';
2322 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2323 END IF;
2324
2325 IF l_box IS NOT NULL AND
2326 to_number(l_box) <> 0 THEN
2327
2328 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2329 INTO l_formatted_box
2330 FROM dual;
2331
2332 tab_employee(lH_CotisationRQAP) := '<H_CotisationRQAP>' ||
2333 l_formatted_box || '</H_CotisationRQAP>' || EOL;
2334 tab_xml_employee(lH_CotisationRQAP) := l_formatted_box; --
2335 l_all_box_0 := FALSE;
2336 ELSE
2337 tab_employee(lH_CotisationRQAP) := NULL;
2338 tab_xml_employee(lH_CotisationRQAP) := NULL; --
2339 END IF;
2340 hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
2344 tab_xml_employee(lH_CotisationRQAP));
2341 tab_employee(lH_CotisationRQAP));
2342
2343 hr_utility.trace('tab_xml_employee(lH_CotisationRQAP) = ' ||
2345
2346 --(BOX I)
2347 l_box := pay_ca_archive_utils.get_archive_value(
2348 l_arch_action_id,
2349 l_province,
2350 'JURISDICTION_CODE',
2351 -- commented for bug 6623199.
2352 -- 'CAEOY_PPIP_REDUCED_SUBJECT_PER_JD_YTD');
2353 'CAEOY_PPIP_EE_TAXABLE_PER_JD_YTD');
2354 IF TO_NUMBER(l_box) > 9999999.99 THEN
2355 l_status := 'Failed';
2356 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2357 END IF;
2358
2359 IF l_box IS NOT NULL AND
2360 to_number(l_box) <> 0 THEN
2361
2362 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2363 INTO l_formatted_box
2364 FROM dual;
2365
2366 tab_employee(lI_SalaireAdmisRQAP) := '<I_SalaireAdmisRQAP>' ||
2367 l_formatted_box || '</I_SalaireAdmisRQAP>' || EOL;
2368 tab_xml_employee(lI_SalaireAdmisRQAP) := l_formatted_box; --
2369 l_all_box_0 := FALSE;
2370 ELSE
2371 tab_employee(lI_SalaireAdmisRQAP) := '<I_SalaireAdmisRQAP>' ||
2372 '0.00' || '</I_SalaireAdmisRQAP>' || EOL;
2373 tab_xml_employee(lI_SalaireAdmisRQAP) := '0.00';--
2374 END IF;
2375 hr_utility.trace('tab_employee(lI_SalaireAdmisRQAP) = ' ||
2376 tab_employee(lI_SalaireAdmisRQAP));
2377
2378 hr_utility.trace('tab_xml_employee(lI_SalaireAdmisRQAP) = ' ||
2379 tab_xml_employee(lI_SalaireAdmisRQAP));
2380
2381 -- (Box J)
2382 l_box := pay_ca_archive_utils.get_archive_value(
2383 l_arch_action_id,
2384 l_province,
2385 'JURISDICTION_CODE',
2386 'CAEOY_RL1_BOXJ_PER_JD_YTD');
2387
2388 IF TO_NUMBER(l_box) > 9999999.99 THEN
2389 l_status := 'Failed';
2390 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2391 END IF;
2392
2393 IF l_box IS NOT NULL AND
2394 to_number(l_box) <> 0 THEN
2395
2396 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2397 INTO l_formatted_box
2398 FROM dual;
2399
2400 tab_employee(lJ_RegimeAssMaladie) := '<J_RegimeAssMaladie>' ||
2401 l_formatted_box || '</J_RegimeAssMaladie>' || EOL;
2402 tab_xml_employee(lJ_RegimeAssMaladie) := l_formatted_box; --
2403 l_all_box_0 := FALSE;
2404 ELSE
2405 tab_employee(lJ_RegimeAssMaladie) := NULL;
2406 tab_xml_employee(lJ_RegimeAssMaladie) := NULL; --
2407 END IF;
2408 hr_utility.trace('tab_employee(lJ_RegimeAssMaladie) = ' ||
2409 tab_employee(lJ_RegimeAssMaladie));
2410
2411 hr_utility.trace('tab_xml_employee(lJ_RegimeAssMaladie) = ' ||
2412 tab_xml_employee(lJ_RegimeAssMaladie));
2413
2414 -- (Box K)
2415 l_box := pay_ca_archive_utils.get_archive_value(
2416 l_arch_action_id,
2417 l_province,
2418 'JURISDICTION_CODE',
2419 'CAEOY_RL1_BOXK_PER_JD_YTD');
2420
2421 IF TO_NUMBER(l_box) > 9999999.99 THEN
2422 l_status := 'Failed';
2423 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2424 END IF;
2425
2426 IF l_box IS NOT NULL AND
2427 to_number(l_box) <> 0 THEN
2428
2429 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2430 INTO l_formatted_box
2431 FROM dual;
2432
2433 tab_employee(lK_Voyage) := '<K_Voyage>' ||
2434 l_formatted_box || '</K_Voyage>' || EOL;
2435 tab_xml_employee(lK_Voyage) := l_formatted_box; --
2436 l_all_box_0 := FALSE;
2437 ELSE
2438 tab_employee(lK_Voyage) := NULL;
2439 tab_xml_employee(lK_Voyage) := NULL; --
2440 END IF;
2441 hr_utility.trace('tab_employee(lK_Voyage) = ' ||
2442 tab_employee(lK_Voyage));
2443
2444 hr_utility.trace('tab_xml_employee(lK_Voyage) = ' ||
2445 tab_xml_employee(lK_Voyage));
2446 -- (Box L)
2447 l_box := pay_ca_archive_utils.get_archive_value(
2448 l_arch_action_id,
2449 l_province,
2450 'JURISDICTION_CODE',
2451 'CAEOY_RL1_BOXL_PER_JD_YTD');
2452
2453 IF TO_NUMBER(l_box) > 9999999.99 THEN
2454 l_status := 'Failed';
2455 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2456 END IF;
2457
2458 IF l_box IS NOT NULL AND
2459 to_number(l_box) <> 0 THEN
2460
2461 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2462 INTO l_formatted_box
2463 FROM dual;
2464
2465 tab_employee(lL_AutreAvantage) := '<L_AutreAvantage>' ||
2466 l_formatted_box || '</L_AutreAvantage>' || EOL;
2467 tab_xml_employee(lL_AutreAvantage) := l_formatted_box; --
2468 l_all_box_0 := FALSE;
2469 ELSE
2470 tab_employee(lL_AutreAvantage) := NULL;
2471 tab_xml_employee(lL_AutreAvantage) := NULL; --
2472 END IF;
2473 hr_utility.trace('tab_employee(lL_AutreAvantage) = ' ||
2474 tab_employee(lL_AutreAvantage));
2475
2476 hr_utility.trace('tab_xml_employee(lL_AutreAvantage) = ' ||
2477 tab_xml_employee(lL_AutreAvantage));
2478 -- (Box M)
2479 l_box := pay_ca_archive_utils.get_archive_value(
2480 l_arch_action_id,
2481 l_province,
2482 'JURISDICTION_CODE',
2486 l_status := 'Failed';
2483 'CAEOY_RL1_BOXM_PER_JD_YTD');
2484
2485 IF TO_NUMBER(l_box) > 9999999.99 THEN
2487 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2488 END IF;
2489
2490 IF l_box IS NOT NULL AND
2491 to_number(l_box) <> 0 THEN
2492
2493 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2494 INTO l_formatted_box
2495 FROM dual;
2496
2497 tab_employee(lM_Commission) := '<M_Commission>' ||
2498 l_formatted_box || '</M_Commission>' || EOL;
2499 tab_xml_employee(lM_Commission) := l_formatted_box; --
2500 l_all_box_0 := FALSE;
2501 ELSE
2502 tab_employee(lM_Commission) := NULL;
2503 tab_xml_employee(lM_Commission) := NULL; --
2504 END IF;
2505 hr_utility.trace('tab_employee(lM_Commission) = ' ||
2506 tab_employee(lM_Commission));
2507
2508 hr_utility.trace('tab_xml_employee(lM_Commission) = ' ||
2509 tab_xml_employee(lM_Commission));
2510 -- (Box N)
2511 l_box := pay_ca_archive_utils.get_archive_value(
2512 l_arch_action_id,
2513 l_province,
2514 'JURISDICTION_CODE',
2515 'CAEOY_RL1_BOXN_PER_JD_YTD');
2516
2517 IF TO_NUMBER(l_box) > 9999999.99 THEN
2518 l_status := 'Failed';
2519 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2520 END IF;
2521
2522 IF l_box IS NOT NULL AND
2523 to_number(l_box) <> 0 THEN
2524
2525 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2526 INTO l_formatted_box
2527 FROM dual;
2528
2529 tab_employee(lN_DonBienfaisance) := '<N_DonBienfaisance>' ||
2530 l_formatted_box || '</N_DonBienfaisance>' || EOL;
2531 tab_xml_employee(lN_DonBienfaisance) := l_formatted_box; --
2532 l_all_box_0 := FALSE;
2533 ELSE
2534 tab_employee(lN_DonBienfaisance) := NULL;
2535 tab_xml_employee(lN_DonBienfaisance) := NULL; --
2536 END IF;
2537
2538 hr_utility.trace('tab_employee(lN_DonBienfaisance) = ' ||
2539 tab_employee(lN_DonBienfaisance));
2540
2541 hr_utility.trace('tab_xml_employee(lN_DonBienfaisance) = ' ||
2542 tab_xml_employee(lN_DonBienfaisance));
2543 -- Summ (Box O)
2544 l_box := pay_ca_archive_utils.get_archive_value(
2545 l_arch_action_id,
2546 l_province,
2547 'JURISDICTION_CODE',
2548 'CAEOY_RL1_BOXO_PER_JD_YTD');
2549
2550 IF TO_NUMBER(l_box) > 9999999.99 THEN
2551 l_status := 'Failed';
2552 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2553 END IF;
2554
2555 IF l_box IS NOT NULL AND
2556 to_number(l_box) <> 0 THEN
2557
2558 l_boxO := l_box;
2559 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2560 INTO l_formatted_box
2561 FROM dual;
2562
2563 tab_employee(lO_AutreRevenu) := '<MontantCaseO>' ||
2564 l_formatted_box || '</MontantCaseO>' || EOL;
2565 tab_xml_employee(lO_AutreRevenu) := l_formatted_box; --
2566 l_all_box_0 := FALSE;
2567 ELSE
2568 tab_employee(lO_AutreRevenu) := NULL;
2569 tab_xml_employee(lO_AutreRevenu) := NULL; --
2570 END IF;
2571 hr_utility.trace('tab_employee(lO_AutreRevenu) = ' ||
2572 tab_employee(lO_AutreRevenu));
2573
2574 hr_utility.trace('tab_xml_employee(lO_AutreRevenu) = ' ||
2575 tab_xml_employee(lO_AutreRevenu));
2576
2577 -- SourceCasem
2578
2579 IF to_number(l_boxO) <> 0 THEN
2580 l_box := pay_ca_archive_utils.get_archive_value(
2581 l_arch_action_id,
2582 l_province,
2583 'JURISDICTION_CODE',
2584 'CAEOY_RL1_BOXO_CODE_PER_JD_YTD');
2585 tab_employee(l_SourceCase) := '<SourceCaseO>' ||
2586 l_box || '</SourceCaseO>' || EOL;
2587 tab_xml_employee(l_SourceCase) := l_box; --
2588 ELSE
2589 tab_employee(l_SourceCase) := NULL;
2590 tab_xml_employee(l_SourceCase) := NULL; --
2591 END IF;
2592 hr_utility.trace('tab_employee(l_SourceCase) = ' ||
2593 tab_employee(l_SourceCase));
2594
2595 hr_utility.trace('tab_xml_employee(l_SourceCase) = ' ||
2596 tab_xml_employee(l_SourceCase));
2597 -- (Box P)
2598 if tab_employee(lO_AutreRevenu) is not null
2599 and tab_employee(l_SourceCase)is not null then
2600 l_O_AutreRevenu := '<O_AutreRevenu>'||EOL
2601 ||tab_employee(lO_AutreRevenu)||EOL
2602 ||tab_employee(l_SourceCase)||EOL
2603 ||'</O_AutreRevenu>'||EOL;
2604 else
2605 l_O_AutreRevenu := null;
2606 end if;
2607 l_box := pay_ca_archive_utils.get_archive_value(
2608 l_arch_action_id,
2609 l_province,
2610 'JURISDICTION_CODE',
2611 'CAEOY_RL1_BOXP_PER_JD_YTD');
2612
2613 IF TO_NUMBER(l_box) > 9999999.99 THEN
2614 l_status := 'Failed';
2615 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2616 END IF;
2617
2618 IF l_box IS NOT NULL AND
2619 to_number(l_box) <> 0 THEN
2620
2621 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2622 INTO l_formatted_box
2623 FROM dual;
2624
2625 tab_employee(lP_RegimeAssInterEntr) := '<P_RegimeAssInterEntr>' ||
2626 l_formatted_box || '</P_RegimeAssInterEntr>' || EOL;
2627 tab_xml_employee(lP_RegimeAssInterEntr) := l_formatted_box; --
2628 l_all_box_0 := FALSE;
2629 ELSE
2630 tab_employee(lP_RegimeAssInterEntr) := NULL;
2631 tab_xml_employee(lP_RegimeAssInterEntr) := NULL; --
2632 END IF;
2633 hr_utility.trace('tab_employee(lP_RegimeAssInterEntr) = ' ||
2634 tab_employee(lP_RegimeAssInterEntr));
2635
2636 hr_utility.trace('tab_xml_employee(lP_RegimeAssInterEntr) = ' ||
2637 tab_xml_employee(lP_RegimeAssInterEntr));
2638 -- (Box R)
2639 l_box := pay_ca_archive_utils.get_archive_value(
2640 l_arch_action_id,
2641 l_province,
2642 'JURISDICTION_CODE',
2643 'CAEOY_RL1_BOXR_PER_JD_YTD');
2644
2645 IF TO_NUMBER(l_box) > 9999999.99 THEN
2646 l_status := 'Failed';
2647 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2648 END IF;
2649
2650 IF l_box IS NOT NULL AND
2651 to_number(l_box) <> 0 THEN
2652
2653 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2654 INTO l_formatted_box
2655 FROM dual;
2656
2657 tab_employee(lR_RevenuIndien) := '<R_RevenuIndien>' ||
2658 l_formatted_box || '</R_RevenuIndien>' || EOL;
2659 tab_xml_employee(lR_RevenuIndien) := l_formatted_box; --
2660 l_all_box_0 := FALSE;
2661 ELSE
2662 tab_employee(lR_RevenuIndien) := NULL;
2663 tab_xml_employee(lR_RevenuIndien) := NULL; --
2664 END IF;
2665 hr_utility.trace('tab_employee(lR_RevenuIndien) = ' ||
2666 tab_employee(lR_RevenuIndien));
2667
2668 hr_utility.trace('tab_xml_employee(lR_RevenuIndien) = ' ||
2669 tab_xml_employee(lR_RevenuIndien));
2670 -- (Box S)
2671 l_box := pay_ca_archive_utils.get_archive_value(
2672 l_arch_action_id,
2673 l_province,
2674 'JURISDICTION_CODE',
2675 'CAEOY_RL1_BOXS_PER_JD_YTD');
2676
2677 IF TO_NUMBER(l_box) > 9999999.99 THEN
2678 l_status := 'Failed';
2679 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2680 END IF;
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(lS_PourboireRecu) := '<S_PourboireRecu>' ||
2690 l_formatted_box || '</S_PourboireRecu>' || EOL;
2691 tab_xml_employee(lS_PourboireRecu) := l_formatted_box; --
2692 l_all_box_0 := FALSE;
2693 ELSE
2694 tab_employee(lS_PourboireRecu) := NULL;
2695 tab_xml_employee(lS_PourboireRecu) := NULL; --
2696 END IF;
2697 hr_utility.trace('tab_employee(lS_PourboireRecu) = ' ||
2698 tab_employee(lS_PourboireRecu));
2699
2700 hr_utility.trace('tab_xml_employee(lS_PourboireRecu) = ' ||
2701 tab_xml_employee(lS_PourboireRecu));
2702 -- (Box T)
2703 l_box := pay_ca_archive_utils.get_archive_value(
2704 l_arch_action_id,
2705 l_province,
2706 'JURISDICTION_CODE',
2707 'CAEOY_RL1_BOXT_PER_JD_YTD');
2708
2709 IF TO_NUMBER(l_box) > 9999999.99 THEN
2710 l_status := 'Failed';
2711 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2712 END IF;
2713
2714 IF l_box IS NOT NULL AND
2715 to_number(l_box) <> 0 THEN
2716
2717 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2718 INTO l_formatted_box
2719 FROM dual;
2720
2721 tab_employee(lT_PourboireAttribue) := '<T_PourboireAttribue>' ||
2722 l_formatted_box || '</T_PourboireAttribue>' || EOL;
2723 tab_xml_employee(lT_PourboireAttribue) := l_formatted_box; --
2724 l_all_box_0 := FALSE;
2725 ELSE
2726 tab_employee(lT_PourboireAttribue) := NULL;
2727 tab_xml_employee(lT_PourboireAttribue) := NULL; --
2728 END IF;
2729 hr_utility.trace('tab_employee(lT_PourboireAttribue) = ' ||
2730 tab_employee(lT_PourboireAttribue));
2731 hr_utility.trace('tab_xml_employee(lT_PourboireAttribue) = ' ||
2732 tab_xml_employee(lT_PourboireAttribue));
2733 -- (Box ContisationRPC)
2734 l_box := pay_ca_archive_utils.get_archive_value(
2735 l_arch_action_id,
2736 --l_province,
2737 --'JURISDICTION_CODE',
2738 'CAEOY_CPP_EE_WITHHELD_PER_YTD');
2739
2740 IF TO_NUMBER(l_box) > 9999999.99 THEN
2741 l_status := 'Failed';
2742 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2743 END IF;
2744
2745 IF l_box IS NOT NULL AND
2746 to_number(l_box) <> 0
2747 and l_taxation_year = '2011' THEN /*Bug 14701466 */
2748
2749 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2750 INTO l_formatted_box
2751 FROM dual;
2752
2753 tab_employee(l_ContisationRPC) := '<CotisationRPC>' ||
2754 l_formatted_box || '</CotisationRPC>' || EOL;
2755 l_all_box_0 := FALSE;
2756 ELSE
2757 tab_employee(l_ContisationRPC) := NULL;
2758 END IF;
2759 hr_utility.trace('tab_employee(lR_ContisationRPC) = ' ||
2760 tab_employee(l_ContisationRPC));
2761
2762 /* Changes for bug 13567075 starts here -- sbachu*/
2763
2764 l_box := pay_ca_archive_utils.get_archive_value(
2765 l_arch_action_id,
2766 l_province,
2767 'JURISDICTION_CODE',
2768 'CAEOY_RL1_FURTHER_INFO_AMOUNT_201_AMT_PER_JD_YTD');
2769
2770 IF TO_NUMBER(l_box) > 9999999.99 THEN
2771 l_status := 'Failed';
2772 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2773 END IF;
2774
2775 IF l_box IS NOT NULL AND
2776 to_number(l_box) <> 0
2777 and l_taxation_year = '2011' THEN /*Bug 14701466 here*/
2778 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2779 INTO l_formatted_box
2780 FROM dual;
2781
2782 tab_employee(l_FraisGarde) := '<FraisGarde>' ||
2783 l_formatted_box || '</FraisGarde>' || EOL;
2784 l_all_box_0 := FALSE;
2785 ELSE
2786 tab_employee(l_FraisGarde) := NULL;
2787 END IF;
2788 hr_utility.trace('tab_employee(l_FraisGarde) = ' ||
2789 tab_employee(l_FraisGarde));
2790
2791 l_box := fnd_number.number_to_canonical(fnd_number.canonical_to_number(pay_ca_archive_utils.get_archive_value(
2792 l_arch_action_id,
2793 l_province,
2794 'JURISDICTION_CODE',
2795 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A7_AMT_PER_JD_YTD')) +
2796 fnd_number.canonical_to_number(pay_ca_archive_utils.get_archive_value(
2797 l_arch_action_id,
2798 l_province,
2799 'JURISDICTION_CODE',
2800 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A8_AMT_PER_JD_YTD')));
2801
2802 IF TO_NUMBER(l_box) > 9999999.99 THEN
2803 l_status := 'Failed';
2804 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2805 END IF;
2806
2807 IF l_box IS NOT NULL AND
2808 to_number(l_box) <> 0
2809 and l_taxation_year = '2011' THEN /*Bug 14701466 */
2810
2811 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2812 INTO l_formatted_box
2813 FROM dual;
2814
2815 tab_employee(l_DeductionForce) := '<DeductionForce>' ||
2816 l_formatted_box || '</DeductionForce>' || EOL;
2817 l_all_box_0 := FALSE;
2818 ELSE
2819 tab_employee(l_DeductionForce) := NULL;
2820 END IF;
2821 hr_utility.trace('tab_employee(l_DeductionForce) = ' ||
2822 tab_employee(l_DeductionForce));
2823
2824 /*Changes for bug 13567075 ends here --sbachu*/
2825
2826
2827 -- Negative Balance Exists
2828 hr_utility.trace('finding if neg bal exists');
2829 l_box := pay_ca_archive_utils.get_archive_value(
2830 l_arch_action_id,
2831 l_province,
2832 'JURISDICTION_CODE',
2833 'CAEOY_RL1_NEGATIVE_BALANCE_EXISTS');
2834
2835 IF l_box = 'Y' THEN
2836 l_status := 'Failed';
2837 l_msg_code := 'NEG';
2838 l_neg_bal_exists := TRUE;
2839 hr_utility.trace('neg bal exists');
2840 END IF;
2841
2842 IF to_number(l_taxation_year) < 2011 AND l_all_box_0 THEN /*Bug 13633422*/
2843 l_status := 'Failed';
2844 l_msg_code := 'ALL_BOXES_ZERO';
2845 END IF;
2846 /*Bug 14701466 starts here*/
2847 store_further_information (l_arch_action_id,l_asg_id);
2848
2849 for l_j in 1..g_further_info_list.count
2850 loop
2851 get_further_information (l_j,
2852 l_code,
2853 l_amount);
2854 l_further_code(l_j):=l_code;
2855 l_further_amount(l_j):=l_amount;
2856 end loop;
2857
2858
2859 IF l_status = 'Failed' and l_rep_type <>'RL1PAPERPDF' THEN
2860
2861 l_other_details := NULL;
2862 for l_i in 1..g_further_info_list.count
2863 loop
2864 if l_further_amount(l_i) is not null and
2865 l_further_amount(l_i) <> '0' then
2866 SELECT ltrim(rtrim(to_char(to_number(l_further_amount(l_i)),l_format_mask)))
2867 INTO l_formatted_box
2868 FROM dual;
2869 select decode(l_further_code(l_i),'201','F_1','211','F_2','235','F_3',replace(l_further_code(l_i),'-','_'))
2870 into l_formatted_code
2871 from dual;
2872 l_other_details:=l_other_details||'<'||l_formatted_code||'>'
2873 ||l_formatted_box||'</'||l_formatted_code||'>'||EOL;
2874 end if;
2875 end loop;
2876 /*Bug 14701466 ends here*/
2877 OPEN cur_get_meaning(l_msg_code);
2878 FETCH cur_get_meaning
2879 INTO l_meaning;
2880 CLOSE cur_get_meaning;
2881 hr_utility.trace('l_meaning ='|| l_meaning);
2882 tab_employee(lErrorDetails) := '<ErrorDetails>' ||
2883 l_meaning || '</ErrorDetails>' || EOL;
2884
2885 l_other_details := l_other_details||tab_employee(lErrorDetails);
2886 END IF;
2887 hr_utility.trace('l_other_details ='||l_other_details);
2888 hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||tab_employee(lH_CotisationRQAP));
2889 hr_utility.trace('l_rep_type ='|| l_rep_type);
2890 /*bug 14701466 starts here sbachu*/
2891 lv_ident_addr_string := '<Identification>'|| EOL
2892 || '<Employe>' || EOL ||
2893 tab_employee(lNAS) ||
2894 tab_employee(lNo) ||
2895 tab_employee(lNomFamille) ||
2896 tab_employee(lPrenom) ||
2897 tab_employee(lInitiale) || '</Employe>' || EOL
2898 ||'</Identification>' || EOL
2899 || l_addr_begin_tag || EOL ||
2900 tab_employee(lLigne1) ||
2901 tab_employee(lLigne2) ||
2902 tab_employee(lVille) ||
2903 tab_employee(lProvince) ||
2904 tab_employee(lCodePostal) ||
2905 l_addr_end_tag || EOL ;
2906 lv_montants_string := '<Montants>' || EOL ||
2907 tab_employee(lA_RevenuEmploi) ||
2908 tab_employee(lB_CotisationRRQ) ||
2909 tab_employee(lC_CotisationAssEmploi) ||
2910 tab_employee(lD_CotisationRPA) ||
2911 tab_employee(lE_ImpotQue) ||
2912 tab_employee(lF_CotisationSyndicale) ||
2913 tab_employee(lG_SalaireAdmisRRQ) ||
2914 tab_employee(lH_CotisationRQAP)||
2915 tab_employee(lI_SalaireAdmisRQAP)||
2916 tab_employee(lJ_RegimeAssMaladie) ||
2917 tab_employee(lK_Voyage) ||
2918 tab_employee(lL_AutreAvantage) ||
2919 tab_employee(lM_Commission) ||
2920 tab_employee(lN_DonBienfaisance) ||
2921 l_O_AutreRevenu||
2922 tab_employee(lP_RegimeAssInterEntr) ||
2923 tab_employee(lQ_SalaireDiffere) ||
2924 tab_employee(lR_RevenuIndien) ||
2925 tab_employee(lS_PourboireRecu) ||
2926 tab_employee(lT_PourboireAttribue) ||
2927 tab_employee(lU_RetraiteProgressive) ||
2928 tab_employee(lV_NourritureLogement) ||
2929 tab_employee(lW_Vehicule) ||
2930 tab_employee(l_ContisationRPC) ||
2931 tab_employee(l_FraisGarde) ||
2932 tab_employee(l_DeductionForce) ||
2933 l_other_details ||
2934 '</Montants>' || EOL ;
2935 IF l_status = 'Failed' THEN
2936 l_final_xml_string := '<' || l_status || '>' || EOL ||
2937 '<D>' || EOL ||
2938 tab_employee(lAnnee) ||
2939 '<NoReleve>' || lv_org_slip_no || '</NoReleve>' || EOL
2940 || lv_ident_addr_string || lv_montants_string ||
2941 '</D>' || EOL ||
2942 '</' || l_status || '>' ;
2943 ELSE
2944 l_k := 0;
2945 l_m := 0;
2946 l_further_info_string := '';
2947 l_final_xml_string := '';
2948 ln_no_fi_per_slip := 4;
2949
2950 for l_i in 1..g_further_info_list.count
2951 loop
2952 if l_further_amount(l_i) is not null
2953 and l_further_amount(l_i) <> '0'
2954 and (NVL(tab_xml_employee(l_SourceCase),'RZ') = 'RZ' or l_further_code(l_i) not like 'RZ%') then
2955 l_k := l_k + 1;
2956 SELECT ltrim(rtrim(to_char(to_number(l_further_amount(l_i)),l_format_mask)))
2957 INTO l_formatted_box
2958 FROM dual;
2959 l_further_info_string:=l_further_info_string||'<CaseRensCompl>'||EOL
2960 ||'<CodeRensCompl>'||l_further_code(l_i)||'</CodeRensCompl>'||EOL
2961 ||'<DonneeRensCompl>'||l_formatted_box||'</DonneeRensCompl>'||EOL
2962 ||'</CaseRensCompl>';
2963
2964 if (mod(l_k,ln_no_fi_per_slip) = 0) then
2965
2966 if l_m = 0 then
2967 l_final_xml_string := '<' || l_status || '>' || EOL ||
2968 '<D>' || EOL ||
2969 tab_employee(lAnnee) ||
2970 '<NoReleve>' || pay_ca_rl1_mag.get_slip_seq_no('|',lv_org_slip_no,1) || '</NoReleve>' || EOL
2971 || lv_ident_addr_string || lv_montants_string || l_further_info_string ||
2972 '</D>' || EOL ||
2973 '</' || l_status || '>' ;
2974
2975 else
2976 l_final_xml_string := l_final_xml_string || EOL ||
2977 '<' || l_status || '>' || EOL ||
2978 '<D>' || EOL ||
2979 tab_employee(lAnnee) ||
2980 '<NoReleve>' || pay_ca_rl1_mag.get_slip_seq_no('|',lv_org_slip_no,trunc(l_k/ln_no_fi_per_slip)) || '</NoReleve>' || EOL
2981 || lv_ident_addr_string ||
2982 '<Montants>' || EOL ||
2983 '</Montants>' || EOL ||
2984 l_further_info_string || '</D>' || EOL ||
2985 '</' || l_status || '>' ;
2986
2987 end if;
2988
2989 l_m := 1;
2990 l_further_info_string :='';
2991 end if;
2992 end if;
2993 end loop;
2994
2995 if (mod(l_k,ln_no_fi_per_slip) <> 0) or l_k = 0 then
2996 if l_m = 0 then
2997 l_final_xml_string := '<' || l_status || '>' || EOL ||
2998 '<D>' || EOL ||
2999 tab_employee(lAnnee) ||
3000 '<NoReleve>' || pay_ca_rl1_mag.get_slip_seq_no('|',lv_org_slip_no,1) || '</NoReleve>' || EOL
3001 || lv_ident_addr_string || lv_montants_string || l_further_info_string ||
3002 '</D>' || EOL ||
3003 '</' || l_status || '>' ;
3004
3005 else
3006 l_final_xml_string := l_final_xml_string || EOL ||
3007 '<' || l_status || '>' || EOL ||
3008 '<D>' || EOL ||
3009 tab_employee(lAnnee) ||
3010 '<NoReleve>' || pay_ca_rl1_mag.get_slip_seq_no('|',lv_org_slip_no,trunc(l_k/ln_no_fi_per_slip)+1) || '</NoReleve>' || EOL
3011 || lv_ident_addr_string ||
3012 '<Montants>' || EOL ||
3013 '</Montants>' || EOL ||
3014 l_further_info_string || '</D>' || EOL ||
3015 '</' || l_status || '>' ;
3016 end if;
3017 end if;
3018 END IF; /* if l_status = 'Failed' */
3019 /*bug 14701466 ends here sbachu*/
3020 hr_utility.trace('rl1_xml_employee: l_final_xml_string = ' || l_final_xml_string);
3021 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3022 --end if;
3023 hr_utility.trace('end of xml_employee_record');
3024
3025 END;
3026 END xml_employee_record;
3027
3028 PROCEDURE xml_employer_start IS
3029 BEGIN
3030
3031 DECLARE
3032
3033 l_final_xml CLOB;
3034 l_final_xml_string VARCHAR2(32000);
3035 l_is_temp_final_xml VARCHAR2(2);
3036
3037 BEGIN
3038
3039 l_final_xml_string := '<Groupe01>';
3040
3041 hr_utility.trace('rl1_xml_employee_start: l_final_xml_string = ' || l_final_xml_string);
3042 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3043
3044
3045 END;
3046 END xml_employer_start;
3047
3048 PROCEDURE xml_employer_record IS
3049 BEGIN
3050 DECLARE
3051
3052 l_final_xml CLOB;
3053 l_final_xml_string VARCHAR2(32000);
3054 l_is_temp_final_xml VARCHAR2(2);
3055
3056 TYPE employer_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
3057
3058 tab_employer employer_info;
3059
3060 lAnnee NUMBER;
3061 lNbReleves NUMBER;
3062 lNoId NUMBER;
3063 lTypeDossier NUMBER;
3064 lNoDossier NUMBER;
3065 lNEQ NUMBER;
3066 lNom1 NUMBER;
3067 lNom2 NUMBER;
3068 lLigne1 NUMBER;
3069 lLigne2 NUMBER;
3070 lVille NUMBER;
3071 lProvince NUMBER;
3072 lCodePostal NUMBER;
3073
3074 l_taxation_year varchar2(4);
3075 l_context1 ff_archive_items.context1%TYPE;
3076 EOL varchar2(5);
3077 l_employer_name varchar2(100);
3078 l_quebec_bn varchar2(20);
3079 l_address_line hr_locations.address_line_1%TYPE;
3080 l_address_begin_tag varchar2(10);
3081 l_address_end_tag varchar2(10);
3082 --l_pre_id number;
3083 --l_pre_id_t number;
3084
3085 BEGIN
3086
3087 hr_utility.trace('XML Employer');
3088
3089 SELECT
3090 fnd_global.local_chr(13) || fnd_global.local_chr(10)
3091 INTO EOL
3092 FROM dual;
3093
3094 lAnnee := 1;
3095 lNbReleves := 2;
3096 lNoId := 3;
3097 lTypeDossier := 4;
3098 lNoDossier := 5;
3099 lNEQ := 6;
3100 lNom1 := 7;
3101 lNom2 := 8;
3102 lLigne1 := 9;
3103 lLigne2 := 10;
3104 lVille := 11;
3105 lProvince := 12;
3106 lCodePostal := 13;
3107
3108 l_taxation_year
3109 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
3110
3111 /* commented for bug 9142143
3112 l_context1
3113 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
3114 */
3115 select max(paa1.payroll_action_id)
3116 into l_context1
3117 from pay_assignment_actions paa,
3121 AND paa1.assignment_action_id = int.locked_action_id
3118 pay_assignment_actions paa1,
3119 pay_action_interlocks int
3120 where int.locking_action_id = paa.assignment_action_id
3122 and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
3123
3124 --l_pre_id := to_number(pay_magtape_generic.get_parameter_value('PRE_ID'));
3125 --l_pre_id_t := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PRE_ID'));
3126
3127 --hr_utility.trace('l_pre_id = ' || l_pre_id);
3128 --hr_utility.trace('l_pre_id_t = ' || l_pre_id_t);
3129
3130 hr_utility.trace ('l_context1 ='||l_context1);
3131
3132 tab_employer(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' || EOL;
3133 tab_employer(lNbReleves) := '<NbReleves>' || 'Running Total' || '</NbReleves>' || EOL;
3134
3135 l_quebec_bn := pay_ca_archive_utils.get_archive_value
3136 (l_context1,'CAEOY_RL1_QUEBEC_BN');
3137
3138 tab_employer(lNoId) := '<NoId>' || substr(l_quebec_bn,1,10) ||
3139 '</NoId>' || EOL;
3140 tab_employer(lTypeDossier) := '<TypeDossier>' || 'RS' ||
3141 '</TypeDossier>' || EOL;
3142 tab_employer(lNoDossier) := '<NoDossier>' || substr(l_quebec_bn,13,4) ||
3143 '</NoDossier>' || EOL;
3144 tab_employer(lNEQ) := '<NEQ>' || substr(l_quebec_bn,1,10) ||
3145 '</NEQ>' || EOL;
3146 l_employer_name := pay_ca_archive_utils.get_archive_value(l_context1,
3147 'CAEOY_RL1_EMPLOYER_NAME');
3148
3149 tab_employer(lNom1) := '<Nom1>' ||
3150 convert_special_char(substr(l_employer_name,1,30)) || '</Nom1>' || EOL;
3151 hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
3152 hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3153 hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3154 hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3155 hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3156 hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3157 hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3158
3159 IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
3160 tab_employer(lNom2) := '<Nom2>' ||
3161 convert_special_char(substr(l_employer_name,31,30)) || '</Nom2>' || EOL;
3162 ELSE
3163 tab_employer(lNom2) := NULL;
3164 END IF;
3165 hr_utility.trace('tab_employer(lNom2) = ' || tab_employer(lNom2));
3166
3167 -- Address Line 1
3168
3169 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3170 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1');
3171
3172 IF l_address_line IS NULL THEN
3173
3174 l_address_begin_tag := '';
3175 tab_employer(lLigne1) := NULL;
3176 tab_employer(lLigne2) := NULL;
3177 tab_employer(lVille) := NULL;
3178 tab_employer(lProvince) := NULL;
3179 tab_employer(lCodePostal) := NULL;
3180 l_address_end_tag := '';
3181
3182 ELSE
3183
3184 l_address_begin_tag := '<Adresse>';
3185
3186 tab_employer(lLigne1) := '<Ligne1>' ||
3187 convert_special_char(substr(l_address_line,1,30)) || '</Ligne1>' || EOL;
3188 hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
3189
3190
3191 -- Address Line 2
3192
3193 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3194 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2');
3195
3196 IF l_address_line IS NOT NULL THEN
3197 tab_employer(lLigne2) := '<Ligne2>' ||
3198 convert_special_char(substr(l_address_line,1,30)) || '</Ligne2>' || EOL;
3199 ELSE
3200 tab_employer(lLigne2) := NULL;
3201 END IF;
3202 hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
3203
3204 -- Ville (City)
3205
3206 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3207 'CAEOY_RL1_EMPLOYER_CITY');
3208 IF l_address_line IS NOT NULL THEN
3209 tab_employer(lVille) := '<Ville>' ||
3210 convert_special_char(substr(l_address_line,1,30)) || '</Ville>' || EOL;
3211 ELSE
3212 tab_employer(lVille) := NULL;
3213 END IF;
3214 hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
3215
3216 -- Province
3217
3218 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3219 'CAEOY_RL1_EMPLOYER_PROVINCE');
3220
3221 IF l_address_line IS NOT NULL THEN
3222 tab_employer(lProvince) := '<Province>' ||
3223 SUBSTR(hr_general.decode_lookup( 'CA_PROVINCE',
3224 l_address_line),1,20) || '</Province>' || EOL;
3225 ELSE
3226 tab_employer(lProvince) := NULL;
3227 END IF;
3228 hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
3229
3230 -- Postal Code
3231
3232 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3233 'CAEOY_RL1_EMPLOYER_POSTAL_CODE');
3234
3235 IF l_address_line IS NOT NULL THEN
3236 tab_employer(lCodePostal) := '<CodePostal>' ||
3237 substr(replace(l_address_line,' '),1,6) || '</CodePostal>' || EOL;
3238 ELSE
3239 tab_employer(lCodePostal) := NULL;
3240 END IF;
3241 hr_utility.trace('tab_employer(lCodePostal) = ' || tab_employer(lCodePostal));
3242
3243 l_address_end_tag := '</Adresse>';
3244
3245 END IF;
3246
3247 l_final_xml_string := '<T>' || EOL ||
3248 tab_employer(lAnnee) ||
3249 tab_employer(lNbReleves) || '<Employeur>' || EOL ||
3250 tab_employer(lNoId) ||
3251 tab_employer(lTypeDossier) ||
3252 tab_employer(lNoDossier) ||
3253 tab_employer(lNEQ) ||
3254 tab_employer(lNom1) ||
3255 tab_employer(lNom2) || l_address_begin_tag || EOL ||
3256 tab_employer(lLigne1) ||
3257 tab_employer(lLigne2) ||
3258 tab_employer(lVille) ||
3259 tab_employer(lProvince) ||
3260 tab_employer(lCodePostal) ||
3261 l_address_end_tag || EOL || '</Employeur>' || EOL ||
3262 '</T>' || EOL ||
3263 '</Groupe01>' || EOL;
3264
3265 hr_utility.trace('rl1_xml_employer_start: l_final_xml_string = ' || l_final_xml_string);
3266 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3267 END;
3268 END xml_employer_record;
3269
3270 END pay_ca_rl1_can_mag;