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