[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_RL1_AMEND_MAG
Source
1 PACKAGE BODY PAY_CA_RL1_AMEND_MAG AS
2 /* $Header: pycarlamd.pkb 120.18.12020000.7 2012/12/19 06:23:00 sbachu ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1997 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9 Package Body Name : pay_ca_rl1_amend_mag
10 Package File Name : pycarlamd.pkb
11 Description : This package declares functions and procedures to support
12 the generation of magnetic RL1 reports for CA legislative requirements
13 incorporating magtape resilience and the new end-of-year processing.
14
15 Change List:
16 ------------
17
18 Name Date Version Bug Text
19 ----------- - ---------- ------ ------- ------------------------------
20 aneghosh 18-Jun-2009 115.0 8316783 Created.
21 aneghosh 01-Jul-2009 115.1 8316783 Added code for missing slip number.
22 Modified cursor c_original_slipno.
23 aneghosh 14-Jul-2009 115.2 8316783 Removed locking of Amendment Paper Report.
24 Reused function convert_special_char defined
25 in pay_ca_rl1_mag.
26 Added the feature to show only those emloyees
27 whose assignamnents got amended since the
28 previous run of amendment mag media.
29 aneghosh 24-Sep-2009 115.4 8932754 Modified the cursor
30 get_latest_rl1_amend_dtls.
31 aneghosh 08-Oct-2009 115.5 8932598 Modified procedure create_assignment_act
32 to prevent creation of duplicate
33 assignment actions for the same employee.
34 aneghosh 26-Oct-2009 115.6 9037784 Added certification no for 2009 test file.
35 aneghosh 20-Nov-2009 115.7 9132270 Modified the cursor
36 get_latest_rl1_amend_dtls.
37 aneghosh 25-Nov-2009 115.8 9154497 Modified the code to accept the type of package
38 value from the transmitter details instead
39 of hard-coding it to 4.
40 sapalani 04-Dec-2009 115.9 9178892 Added code to generate XML tags for
41 new BOX O codes CA, CB and CC for
42 error report.
43 sapalani 23-Dec-2009 115.10 9206928 Added 2009 Certification No.
44 RQ-09-01-047 for RL1 Amendment
45 Electronic Interface.
46 sneelapa 26-Oct-2010 115.11 10134138 Modified code in xml_transmitter_record,
47 procedure to generate l_certification_no value,
48 based on taxation year.
49 rgottipa 23-Nov-2010 115.12 10283923 BoxG value has to display in all cases
50 except if it is null then display '0'
51 For this xml_employee_record
52 procedure is modified
53 rgottipa 17-Dec-2010 115.13 10364963 Added 2010 Certification No.
54 RQ-10-01-058 for RL1 Amendment
55 Electronic Interface.
56 sneelapa 07-Jan-2011 115.14 10648144 Modified the cursor get_latest_rl1_amend_dtls
57 Added Order by clause.
58 rgottipa 22-Nov-2011 115.15 13360872 Using 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD'
59 DBI for Box G
60 pracagra 20-Dec-2011 115.16 13486728 Added 2011 Certification No.
61 RQ-11-01-083 for RL1 Amendment
62 Electronic Interface.
63 rgottipa 03-Jan-2012 115.17 13360884 Added logic to get further information
64 in A02 file.
65 rgottipa 16-Jan-2012 115.18 13593331 Removed to to_char function from the
66 cursor cur_further_info
67 sbachu 18-Sep-2012 115.19 13567075 Added tags <FraisGarde> (code 201),
68 <DeductionForce> (codes A-7 + A-8)
69 sbachu 20-Nov-2012 115.20 14701466 RL1 EI, slip number and seq number
70 115.21 13564765 changes for 2012.
71 sbachu 29-Nov-2012 115.22 13633422 ALL_BOXES_ZERO messages is not
72 required from 2011.
73 sbachu 10-Dec-2012 115.23 15972601 Removed per_people_f join condition
74 in the cursor c_original_slipno.
75 sbachu 19-Dec-2012 115.24 15996999 Added 2012 Certification No.
76 RQ-12-01-011.
77 */
78
79
80
81 -- Name : get_report_parameters
82
83 -----------------------------------------------------------------------------
84 --
85 -- Purpose
86 -- The procedure gets the 'parameter' for which the report is being
87 -- run i.e., the period, state and business organization.
88 --
89 -- Arguments
90 -- p_pactid Payroll_action_id passed from pyugen process
91 -- p_year_start Start Date of the period for which the report
92 -- has been requested
93 -- p_year_end End date of the period
94 -- p_business_group_id Business group for which the report is being run
95 -- p_report_type Type of report being run RL1
96 --
97 -- Notes
98
99 ----------------------------------------------------------------------------
100
101 PROCEDURE get_report_parameters
102 ( p_pactid IN NUMBER,
103 p_year_start IN OUT NOCOPY DATE,
104 p_year_end IN OUT NOCOPY DATE,
105 p_report_type IN OUT NOCOPY VARCHAR2,
106 p_business_group_id IN OUT NOCOPY NUMBER
107 ) IS
108 BEGIN
109 --hr_utility.trace_on('Y','RL1AMD');
110 hr_utility.set_location('pay_ca_rl1_amend_mag.get_report_parameters', 10);
111
112 SELECT ppa.start_date,
113 ppa.effective_date,
114 ppa.business_group_id,
115 ppa.report_type
116 INTO p_year_start,
117 p_year_end,
118 p_business_group_id,
119 p_report_type
120 FROM pay_payroll_actions ppa
121 WHERE payroll_action_id = p_pactid;
122
123 hr_utility.set_location('pay_ca_rl1_amend_mag.get_report_parameters', 20);
124
125 END get_report_parameters;
126
127 PROCEDURE validate_transmitter_info(p_payroll_action_id IN NUMBER,
128 p_bg_id IN NUMBER,
129 p_effective_date IN DATE) IS
130 BEGIN
131
132 DECLARE
133
134 CURSOR cur_arch_pactid(p_transmitter_org_id NUMBER) IS
135 SELECT
136 ppa.payroll_action_id
137 FROM
138 pay_payroll_actions ppa
139 WHERE
140 ppa.business_group_id = p_bg_id AND
141 ppa.report_type = 'CAEOY_RL1_AMEND_PP'
142 AND ppa.report_qualifier = 'CAEOY_RL1_AMEND_PPQ'
143 AND ppa.report_category = 'ARCHIVE'
144 AND ppa.effective_date = p_effective_date AND
145 p_transmitter_org_id =
146 pay_ca_rl1_mag.get_parameter('PRE_ORGANIZATION_ID',
147 ppa.legislative_parameters);
148
149 l_transmitter_number VARCHAR2(30);
150 l_transmitter_name VARCHAR2(100);
151 l_type_of_package VARCHAR2(30);
152 l_source_of_slips VARCHAR2(30);
153 dummy NUMBER;
154 dummy1 VARCHAR2(10);
155 l_transmitter_org_id NUMBER;
156 l_arch_pactid NUMBER;
157 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
158 l_address_line1 hr_locations.address_line_1%TYPE;
159
160 CURSOR cur_ppa IS
161 SELECT
162 ppa.legislative_parameters
163 FROM
164 pay_payroll_actions ppa
165 WHERE
166 ppa.payroll_action_id = p_payroll_action_id;
167
168 BEGIN
169
170 OPEN cur_ppa;
171 FETCH cur_ppa
172 INTO l_legislative_parameters;
173 CLOSE cur_ppa;
174
175 l_transmitter_org_id := pay_ca_rl1_mag.get_parameter('TRANSMITTER_PRE',
176 l_legislative_parameters);
177
178 hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
179 hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
180 hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
181 hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
182
183 OPEN cur_arch_pactid(l_transmitter_org_id);
184 FETCH cur_arch_pactid
185 INTO l_arch_pactid;
186 CLOSE cur_arch_pactid;
187
188 l_transmitter_number := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NUMBER');
189 l_transmitter_name := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NAME');
190 BEGIN
191
192 hr_utility.trace('l_transmitter_number = ' || l_transmitter_number);
193 SELECT substr(l_transmitter_number,1,2)
194 INTO dummy1
195 FROM dual;
196
197 IF (dummy1 <> 'NP' OR
198 length(l_transmitter_number) <> 8) THEN
199 RAISE INVALID_NUMBER;
200 END IF;
201
202 SELECT to_number(substr(l_transmitter_number,3,6))
203 INTO dummy
204 FROM dual;
205
206 EXCEPTION
207 WHEN INVALID_NUMBER THEN
208 hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
209 hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
210 pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
211 pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
212 hr_utility.raise_error;
213 END;
214
215 l_type_of_package := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE');
216
217 hr_utility.trace('l_type_of_package = ' || l_type_of_package);
218
219 IF l_type_of_package IS NULL THEN
220 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
221 hr_utility.raise_error;
222 END IF;
223
224 l_source_of_slips := get_arch_val(l_arch_pactid,'CAEOY_RL1_SOURCE_OF_SLIPS');
225 hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
226
227 IF l_source_of_slips IS NULL THEN
228 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
229 hr_utility.raise_error;
230 END IF;
231
232 l_address_line1 := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
233 hr_utility.trace('l_address_line1 = ' || l_address_line1);
234
235 IF l_address_line1 IS NULL THEN
236 pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
237 hr_utility.raise_error;
238 END IF;
239
240 END;
241
242 END validate_transmitter_info;
243 /*Bug 14701466 starts here*/
244 ----------------------------------------------------------------------------
245 --Name
246 -- store_further_information
247 --Purpose
248 -- This procedure stores all the further information codes and their
249 -- corresponding values in global variables so that we can access them
250 -- easily during processing
251 ------------------------------------------------------------------------------
252
253 procedure store_further_information(p_arch_assact_id IN NUMBER
254 ,p_assgn_id IN NUMBER)
255 is
256 l_cpp_withheld NUMBER;
257 l_cpp_taxable NUMBER;
258 i NUMBER :=0;
259
260 cursor cur_boxo is
261 SELECT 'RZ-'||substr(fdi.user_name,23,2) code,fai.value value
262 FROM ff_database_items fdi,
263 ff_archive_items fai
264 WHERE fai.user_entity_id = fdi.user_entity_id
265 AND fai.context1 = p_arch_assact_id
266 AND fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT____PER_JD_YTD'
267 ORDER BY substr(fdi.user_name,5,4);
268
269 cursor cur_further_info(p_cpp_withheld NUMBER,p_cpp_taxable NUMBER) is
270 select substr(ltrim(rtrim(code)),1,60) code, value value
271 from (
272 select 'B-1' code,to_char(p_cpp_withheld) value from dual
273 where p_cpp_withheld <> 0
274 union
275 select 'G-2' code,to_char(p_cpp_taxable) value from dual
276 where p_cpp_taxable <> 0
277 union
278 select decode(fdi.user_name,'CAEOY_RL1_FURTHER_INFO_AMOUNT_201_AMT_PER_JD_YTD','201',
279 'CAEOY_RL1_FURTHER_INFO_AMOUNT_211_AMT_PER_JD_YTD','211',
280 'CAEOY_RL1_FURTHER_INFO_AMOUNT_235_AMT_PER_JD_YTD','235',
281 substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),1,1)||'-'||
282 substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),2)) code,
283 fai.value value
284 from ff_database_items fdi,
285 ff_archive_items fai
286 where fai.user_entity_id=fdi.user_entity_id
287 and fai.context1= p_arch_assact_id
288 and fdi.user_name LIKE 'CAEOY_RL1_FURTHER_INFO_AMOUNT_%_AMT_PER_JD_YTD'
289 union
290 select 'G-1' code,
291 pai.action_information5 value
292 from pay_action_information pai
293 where pai.action_context_id = p_arch_assact_id
294 and pai.action_information4 = 'G-1'
295 ) order by code;
296
297 begin
298 l_cpp_withheld := pay_ca_archive_utils.get_archive_value(p_arch_assact_id
299 ,'CAEOY_CPP_EE_WITHHELD_PER_YTD');
300 l_cpp_taxable := pay_ca_archive_utils.get_archive_value(p_arch_assact_id
301 ,'CAEOY_CPP_EE_TAXABLE_PER_YTD');
302
303 hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
304 hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
305 g_further_info_list.delete;
306 for rec in cur_boxo
307 loop
308 i:=i+1;
309 hr_utility.trace('i '||i);
310 hr_utility.trace('rec.code '||rec.code);
311 hr_utility.trace('rec.value '||rec.value);
312 g_further_info_list(i).code := rec.code;
313 g_further_info_list(i).amount := rec.value;
314 end loop;
315
316 for rec in cur_further_info(l_cpp_withheld,l_cpp_taxable)
317 loop
318 i:=i+1;
319 hr_utility.trace('i '||i);
320 hr_utility.trace('rec.code '||rec.code);
321 hr_utility.trace('rec.value '||rec.value);
322 g_further_info_list(i).code := rec.code;
323 g_further_info_list(i).amount := rec.value;
324 end loop;
325
326 END store_further_information;
327
328 ----------------------------------------------------------------------------
329 --Name
330 -- get_further_information
331 --Purpose
332 -- This procedure gets all the values that were stored using
333 -- store_further_information by index.
334 ------------------------------------------------------------------------------
335
336 procedure get_further_information(p_index in number,
337 p_code in out nocopy varchar2,
338 p_amount in out nocopy varchar2)
339 is
340 begin
341
342 hr_utility.trace('p_index '||p_index);
343 if g_further_info_list.exists(p_index) then
344 p_code := g_further_info_list(p_index).code;
345 p_amount := g_further_info_list(p_index).amount;
346 else
347 p_code := null;
348 p_amount := null;
349 end if;
350
351 end get_further_information;
352 /*Bug 14701466 ends here*/
353
354 ----------------------------------------------------------------------------
355 --Name
356 -- range_cursor
357 --Purpose
358 -- This procedure defines a SQL statement
359 -- to fetch all the people to be included in the report. This SQL statement
360 -- is used to define the 'chunks' for multi-threaded operation
361 --Arguments
362 -- p_pactid payroll action id for the report
363 -- p_sqlstr the SQL statement to fetch the people
364 ------------------------------------------------------------------------------
365 PROCEDURE range_cursor (
366 p_pactid IN NUMBER,
367 p_sqlstr OUT NOCOPY VARCHAR2
368 )
369 IS
370 p_year_start DATE;
371 p_year_end DATE;
372 p_business_group_id NUMBER;
373 p_report_type VARCHAR2(30);
374
375 BEGIN
376 -- hr_utility.trace_on(null,'PDF');
377 hr_utility.set_location( 'pay_ca_rl1_amend_mag.range_cursor', 10);
378
379 get_report_parameters(
380 p_pactid,
381 p_year_start,
382 p_year_end,
383 p_report_type,
384 p_business_group_id
385 );
386
387 hr_utility.set_location( 'pay_ca_rl1_amend_mag.range_cursor', 20);
388
389
390 p_sqlstr := 'select distinct to_number(fai1.value)
391 from ff_archive_items fai1,
392 ff_database_items fdi1,
393 ff_archive_items fai2,
394 ff_database_items fdi2,
395 pay_assignment_actions paa,
396 pay_payroll_actions ppa,
397 pay_payroll_actions ppa1,
398 hr_organization_information hoi
399 where ppa1.payroll_action_id = :payroll_action_id
400 and ppa.business_group_id+0 = ppa1.business_group_id
401 and ppa.effective_date = ppa1.effective_date
402 and ppa.report_type = ''CAEOY_RL1_AMEND_PP''
403 and ppa.payroll_action_id = paa.payroll_action_id
404 and fdi2.user_name = ''CAEOY_TAXATION_YEAR''
405 and fai2.user_entity_id = fdi2.user_entity_id
406 and fai2.value = pay_ca_rl1_mag.get_parameter(''REPORTING_YEAR'',ppa1.legislative_parameters)
407 and paa.payroll_action_id= fai2.context1
408 and paa.action_status = ''C''
409 and paa.assignment_action_id = fai1.context1
410 and fai1.user_entity_id = fdi1.user_entity_id
411 and fdi1.user_name = ''CAEOY_PERSON_ID''
412 and decode(hoi.org_information3, ''Y'', hoi.organization_id, hoi.org_information20) =
413 pay_ca_rl1_mag.get_parameter(''TRANSMITTER_PRE'', ppa1.legislative_parameters )
414 and hoi.org_information_context =''Prov Reporting Est''
415 and hoi.organization_id = pay_ca_rl1_mag.get_parameter(''PRE_ORGANIZATION_ID'', ppa.legislative_parameters )
416 order by to_number(fai1.value)' ;
417
418 hr_utility.set_location( 'pay_ca_rl1_amend_mag.range_cursor',40);
419
420 END range_cursor;
421
422 -------------------------------------------------------------------------------
423 --Name
424 -- create_assignment_act
425 --Purpose
426 -- Creates assignment actions for the payroll action associated with the
427 -- report
428 --Arguments
429 -- p_pactid payroll action for the report
430 -- p_stperson starting person id for the chunk
431 -- p_endperson last person id for the chunk
432 -- p_chunk size of the chunk
433 --Note
434 -- The procedure processes assignments in 'chunks' to facilitate
435 -- multi-threaded operation. The chunk is defined by the size and the
436 -- starting and ending person id. An interlock is also created against the
437 -- pre-processor assignment action to prevent rolling back of the archiver.
438 ------------------------------------------------------------------------------
439 PROCEDURE create_assignment_act(
440 p_pactid IN NUMBER,
441 p_stperson IN NUMBER,
442 p_endperson IN NUMBER,
443 p_chunk IN NUMBER )
444 IS
445
446 -- Cursor to retrieve all the assignments for all GRE's
447 -- archived in a reporting year
448
449 CURSOR c_all_asg IS
450 SELECT paf.person_id,
451 paf.assignment_id,
452 hoi.organization_id,
453 paf.effective_end_date,
454 max(paa.assignment_action_id),
455 max(ppa.payroll_action_id) -- Added by ssmukher for Bug 3353115
456 FROM pay_payroll_actions ppa,
457 pay_assignment_actions paa,
458 per_all_assignments_f paf,
459 pay_payroll_actions ppa1,
460 hr_organization_information hoi
461 WHERE ppa1.payroll_action_id = p_pactid
462 AND ppa.report_type ='CAEOY_RL1_AMEND_PP'
463 AND ppa.business_group_id+0 = ppa1.business_group_id
464 AND ppa.effective_date = ppa1.effective_date
465 AND paa.payroll_action_id = ppa.payroll_action_id
466 AND paa.action_status = 'C'
467 AND paf.assignment_id = paa.assignment_id
468 AND paf.person_id BETWEEN p_stperson AND p_endperson
469 AND paf.effective_start_date <= ppa.effective_date
470 AND paf.effective_end_date >= ppa.start_date
471 AND decode(hoi.org_information3, 'Y', hoi.organization_id, hoi.org_information20) =
472 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
473 AND hoi.org_information_context = 'Prov Reporting Est'
474 AND hoi.organization_id = pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
475 AND paf.effective_end_date = (select max(paf1.effective_end_date)
476 from per_all_assignments_f paf1
477 where paf1.assignment_id = paf.assignment_id
478 and paf1.effective_start_date <= ppa1.effective_date)
479
480 AND not exists
481 ( SELECT pail.locked_action_id
482 FROM pay_action_interlocks pail,
483 pay_payroll_actions pact,
484 pay_assignment_actions passt
485 WHERE pact.report_type = 'RL1_AMEND_MAG'
486 AND pact.payroll_action_id = passt.payroll_action_id
487 AND passt.assignment_action_id = pail.locking_action_id
488 AND pail.locked_action_id = paa.assignment_action_id)
489
490 AND not exists
491 ( SELECT pail.locked_action_id
492 FROM pay_action_interlocks pail,
493 pay_payroll_actions pact,
494 pay_assignment_actions passt
495 WHERE pact.report_type = 'RL1_XML_MAG'
496 AND pact.payroll_action_id = passt.payroll_action_id
497 AND passt.assignment_action_id = pail.locking_action_id
498 AND pail.locked_action_id = paa.assignment_action_id)
499
500 AND not exists
501 ( SELECT pail.locked_action_id
502 FROM pay_action_interlocks pail,
503 pay_payroll_actions pact,
504 pay_assignment_actions passt
505 WHERE pact.report_type = 'CAEOY_RL1_AMEND_PP'
506 AND pact.payroll_action_id = passt.payroll_action_id
507 AND passt.assignment_action_id = pail.locking_action_id
508 AND pail.locked_action_id = paa.assignment_action_id)
509
510 GROUP BY
511 paf.person_id,
512 paf.assignment_id,
513 hoi.organization_id,
514 paf.effective_end_date;
515
516 CURSOR get_latest_rl1_amend_dtls (cp_person_id in number
517 --,cp_pre_org_id in number
518 ,cp_effective_date in date
519 ,cp_business_group_id in number) is
520 select ppa.report_type,
521 paa.assignment_id,
522 paa.assignment_action_id,
523 pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
524 ppa.legislative_parameters)
525 from pay_payroll_actions ppa,
526 pay_assignment_actions paa,
527 per_assignments_f paf
528 where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
529 and paa.assignment_id = paf.assignment_id
530 /* and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
531 ppa.legislative_parameters) =
532 nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
533 ppa.legislative_parameters))*/
534 and paa.action_status = 'C'
535 and ppa.payroll_action_id = paa.payroll_action_id
536 and ppa.effective_date = cp_effective_date
537 and ppa.business_group_id = cp_business_group_id
538 and ppa.report_type IN ('CAEOY_RL1_AMEND_PP','RL1_XML_MAG','RL1_AMEND_MAG')
539 and exists (select 1
540 from per_assignments_f paf
541 where paf.assignment_id = paa.assignment_id
542 and paf.effective_start_date <= cp_effective_date
543 and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
544 AND not exists
545 ( SELECT pail.locked_action_id
546 FROM pay_action_interlocks pail,
547 pay_payroll_actions pact,
548 pay_assignment_actions passt
549 WHERE pact.report_type = 'RL1_AMEND_MAG'
550 AND pact.payroll_action_id = passt.payroll_action_id
551 AND passt.assignment_action_id = pail.locking_action_id
552 AND passt.serial_number=to_char(cp_person_id)
553 AND (pail.locked_action_id = paa.assignment_action_id
554 OR paa.assignment_action_id < passt.assignment_action_id))
555 group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,
556 pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
557 ppa.legislative_parameters) --Bug 9133270
558 order by paa.assignment_action_id desc;
559 -- added again Order by clause for bug 10648144
560
561 CURSOR c_get_prov_amend_flag(cp_asg_act_id number
562 ,cp_uid_rl1amend_flag number) IS
563 select fai2.value,faic.context
564 from ff_archive_items fai2,
565 ff_archive_item_contexts faic,
566 ff_contexts fc
567 where fai2.context1 = cp_asg_act_id
568 AND fai2.user_entity_id = cp_uid_rl1amend_flag
569 AND fai2.archive_item_id = faic.archive_item_id
570 AND faic.context = 'QC'
571 AND faic.context_id = fc.context_id
572 AND fc.context_name = 'JURISDICTION_CODE';
573
574 CURSOR get_warning_dtls_for_ee(cp_person_id in number) is
575 select substr(full_name,1,48), employee_number
576 from per_people_f
577 where person_id = cp_person_id
578 order by effective_end_date desc;
579
580 CURSOR c_get_ue_id(cp_user_name varchar2) IS
581 select user_entity_id
582 from ff_database_items
583 where user_name = cp_user_name;
584
585 CURSOR get_yepp_pact_id(cp_bg_id number,
586 cp_pre number,
587 cp_year date) IS
588 select payroll_action_id
589 from pay_payroll_actions
590 where business_group_id = cp_bg_id
591 and report_type = 'RL1'
592 and report_qualifier = 'CAEOYRL1'
593 and action_type = 'X'
594 and action_status = 'C'
595 and effective_date = cp_year
596 and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
597 legislative_parameters)
598 = to_char(cp_pre);
599
600 CURSOR c_paa_update_check (cp_locking_asg_act_id number) IS
601 select assignment_action_id from
602 pay_assignment_actions where
603 assignment_action_id = cp_locking_asg_act_id;
604
605 --local variables
606
607 l_year_start DATE;
608 l_year_end DATE;
609 l_effective_end_date DATE;
610 l_report_type VARCHAR2(30);
611 l_business_group_id NUMBER;
612 l_person_id NUMBER;
613 l_assignment_id NUMBER;
614 l_assignment_action_id NUMBER;
615 l_value NUMBER;
616 l_tax_unit_id NUMBER;
617 lockingactid NUMBER;
618 l_org_id NUMBER;
619
620 /* Added by ssmukher for Bug 3353115 */
621 l_prev_payact NUMBER;
622 l_payroll_act NUMBER;
623 l_quebec_val VARCHAR2(20);
624 l_quebec_no VARCHAR2(20);
625 l_quebec_name VARCHAR2(240);
626 l_return NUMBER;
627
628 ln_primary_assignment_id NUMBER := 0;
629 lv_report_type pay_payroll_actions.report_type%TYPE ;
630 ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
631 ln_pre_id_null number;
632 ln_iteration number := 0;
633 lv_flag_count number := 0;
634 lv_employee_number per_people_f.employee_number%type;
635 lv_message varchar2(100):= null;
636 lv_full_name per_people_f.full_name%type;
637 lv_record_name varchar2(100);
638 ln_rl1_amend_flag_ue_id number;
639 lv_prov_of_emp varchar2(10);
640 lv_prov_amend_flag varchar2(5);
641 ln_serial_number pay_assignment_actions.serial_number%TYPE;
642 l_paa_update_check pay_assignment_actions.assignment_action_id%TYPE;
643
644 BEGIN
645
646 -- hr_utility.trace_on('Y','RL1MAG');
647
648 l_prev_payact := -1;
649 hr_utility.set_location( 'pay_ca_rl1_amend_mag.create_assignement_act',10);
650
651 get_report_parameters(
652 p_pactid,
653 l_year_start,
654 l_year_end,
655 l_report_type,
656 l_business_group_id
657 );
658
659 validate_transmitter_info(p_pactid,
660 l_business_group_id,
661 l_year_end);
662
663 hr_utility.set_location( 'pay_ca_rl1_amend_mag.create_assignement_act',20);
664
665 hr_utility.trace('l_business_group_id ='|| l_business_group_id);
666 hr_utility.trace('l_year_end ='|| l_year_end);
667 --IF l_report_type = 'PYRL1MAG' THEN
668
669 open c_get_ue_id('CAEOY_RL1_AMENDMENT_FLAG');
670 fetch c_get_ue_id into ln_rl1_amend_flag_ue_id;
671 close c_get_ue_id;
672
673 OPEN c_all_asg;
674 LOOP
675 FETCH c_all_asg INTO l_person_id,
676 l_assignment_id,
677 l_tax_unit_id,
678 l_effective_end_date,
679 l_assignment_action_id,
680 l_payroll_act;
681
682
683 hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 30);
684
685 EXIT WHEN c_all_asg%NOTFOUND;
686
687 /* Added by ssmukher for validating the
688 Quebec Identification Number */
689
690 if l_prev_payact <> l_payroll_act and l_report_type = 'CAEOY_RL1_AMEND_PP' then
691 hr_utility.trace('The payroll action id '||l_payroll_act);
692
693 l_prev_payact := l_payroll_act;
694 l_quebec_val := get_arch_val(l_payroll_act,'CAEOY_RL1_QUEBEC_BN');
695 l_quebec_name := get_arch_val(l_payroll_act,'CAEOY_RL1_EMPLOYER_NAME');
696
697 hr_utility.trace('The Quebec Number is '||l_quebec_val);
698
699 l_quebec_no := substr(l_quebec_val ,1,10);
700
701 hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
702 hr_utility.trace('l_quebec_name ='|| l_quebec_name);
703 l_return := pay_ca_rl1_mag.validate_quebec_number(l_quebec_val,l_quebec_name);
704
705 end if ;
706
707 --Create the assignment action for the record
708
709 hr_utility.trace('Assignment Fetched - ');
710 hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
711 hr_utility.trace('Person Id : '|| to_char(l_person_id));
712 hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
713 hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
714 hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 40);
715
716 /* Create an assignment action for this person */
717
718
719 select pay_assignment_actions_s.nextval
720 into lockingactid
721 from dual;
722 hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 50);
723 hr_utility.trace('New RL1 Amend Action = ' ||
724 to_char(lockingactid));
725
726 open get_latest_rl1_amend_dtls(l_person_id,
727 --l_tax_unit_id,
728 l_year_end,
729 l_business_group_id);
730
731 loop
732
733 fetch get_latest_rl1_amend_dtls into lv_report_type
734 ,ln_primary_assignment_id
735 ,ln_asg_act_to_lock
736 ,ln_pre_id_null;
737
738 if get_latest_rl1_amend_dtls%notfound then
739
740 if ln_iteration = 0 then
741
742 open get_warning_dtls_for_ee(l_person_id);
743 fetch get_warning_dtls_for_ee into lv_full_name
744 ,lv_employee_number;
745 close get_warning_dtls_for_ee;
746
747 hr_utility.trace('get_latest_rl1_amend_dtls not found');
748 hr_utility.trace('p_person_id :'||to_char(l_person_id));
749
750
751 lv_record_name := 'RL1 Amendment Magnetic Media';
752
753 lv_message := 'Latest amendment details not available for this employee';
754
755 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','A');
756 pay_core_utils.push_token('record_name',lv_record_name);
757 pay_core_utils.push_token('name_or_number',lv_full_name);
758 pay_core_utils.push_token('description',lv_message);
759
760 end if;
761 exit;
762
763 end if;
764
765 ln_iteration := ln_iteration + 1;
766
767 if get_latest_rl1_amend_dtls%found then
768
769 hr_utility.trace('get_latest_rl1_amend_dtls found ');
770 hr_utility.trace('Report Type: '||lv_report_type);
771
772 if lv_report_type='CAEOY_RL1_AMEND_PP' then
773 begin
774
775 open c_get_prov_amend_flag(ln_asg_act_to_lock,
776 ln_rl1_amend_flag_ue_id);
777
778 loop -- check amend flag for each province
779
780
781 lv_prov_amend_flag := 'N';
782 fetch c_get_prov_amend_flag into lv_prov_amend_flag,
783 lv_prov_of_emp;
784 hr_utility.trace('lv_prov_amend_flag : '||lv_prov_amend_flag);
785 hr_utility.trace('lv_prov_of_emp : '||lv_prov_of_emp);
786 exit when c_get_prov_amend_flag%NOTFOUND;
787
788 if c_get_prov_amend_flag%FOUND then
789 --To make sure only the latest amendment details are printed
790 if lv_prov_amend_flag = 'Y' AND lv_flag_count = 0 then
791
792 /* Insert into pay_assignment_actions. */
793 hr_nonrun_asact.insact(lockingactid
794 ,ln_primary_assignment_id
795 ,p_pactid
796 ,p_chunk
797 ,l_tax_unit_id);
798
799 /***********************************************************
800 ** Update the serial number column with Province_code QC,
801 ** Archiver assignment_action and Archiver Payroll_action_id
802 ** so that we need not refer back in the reports.
803 ***********************************************************/
804
805 update pay_assignment_actions aa
806 set aa.serial_number = to_char(l_person_id)
807 where aa.assignment_action_id = lockingactid;
808
809 --Added to lock the Amend Archiver
810 hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 60);
811 hr_nonrun_asact.insint(lockingactid
812 ,ln_asg_act_to_lock);
813 hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 70);
814 hr_utility.trace('Locking Action'||lockingactid);
815 hr_utility.trace('Locked Action = ' || to_char(ln_asg_act_to_lock));
816 hr_utility.trace('lv_flag_count = ' || to_char(lv_flag_count));
817 lv_flag_count := lv_flag_count + 1;
818 end if; -- lv_prov_amend_flag = 'Y'
819
820 end if; -- c_get_prov_amend_flag%FOUND
821
822
823 end loop; -- end of check amend flag for each province
824 close c_get_prov_amend_flag;
825
826 end;
827 end if; --lv_report_type
828
829 if lv_report_type in ('RL1_XML_MAG','RL1_AMEND_MAG') then --To lock previous Mag Reports
830
831 open c_paa_update_check(lockingactid);
832 fetch c_paa_update_check into l_paa_update_check;
833 hr_utility.trace('l_update_check : '||l_paa_update_check);
834 if c_paa_update_check%FOUND then
835
836 /***********************************************************
837 ** Update the serial number column with Province_code QC,
838 ** Archiver assignment_action and Archiver Payroll_action_id
839 ** so that we need not refer back in the reports.
840 ***********************************************************/
841
842 update pay_assignment_actions aa
843 set aa.serial_number = to_char(l_person_id)
844 where aa.assignment_action_id = lockingactid;
845
846 --Added to lock previous mag reports
847 hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 60);
848 hr_nonrun_asact.insint(lockingactid
849 ,ln_asg_act_to_lock);
850 hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 70);
851 hr_utility.trace('Locking Action'||lockingactid);
852 hr_utility.trace('Locked Action = ' || to_char(ln_asg_act_to_lock));
853
854 end if;--c_paa_update_check%FOUND
855 close c_paa_update_check;
856
857 end if; ---END lv_report_type
858
859 end if; /* get_latest_rl1_amend_dtls found*/
860
861 end loop; /* get_latest_rl1_amend_dtls loop */
862 close get_latest_rl1_amend_dtls;
863 lv_flag_count := 0; -- Flag reset to 0 for the new employee
864 END LOOP;
865 CLOSE c_all_asg;
866
867 -- END IF;
868
869 END create_assignment_act;
870
871 FUNCTION get_arch_val(p_context_id IN NUMBER,
872 p_user_name IN VARCHAR2)
873 RETURN varchar2 IS
874
875 cursor cur_archive (b_context_id NUMBER, b_user_name VARCHAR2) is
876 select fai.value
877 from ff_archive_items fai,
878 ff_database_items fdi
879 where fai.user_entity_id = fdi.user_entity_id
880 and fai.context1 = b_context_id
881 and fdi.user_name = b_user_name;
882
883 l_return VARCHAR2(240);
884
885 BEGIN
886 open cur_archive(p_context_id,p_user_name);
887 fetch cur_archive into l_return;
888 close cur_archive;
889
890 return (l_return);
891 END;
892
893 PROCEDURE xml_transmitter_record IS
894 BEGIN
895
896 DECLARE
897
898 l_final_xml CLOB;
899 l_final_xml_string VARCHAR2(32000);
900 l_is_temp_final_xml VARCHAR2(2);
901
902 TYPE transmitter_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
903
904 tab_transmitter transmitter_info;
905
906 lAnnee NUMBER;
907 lTypeEnvoi NUMBER;
908 lProvenance NUMBER;
909 lNo NUMBER;
910 lType NUMBER;
911 lNom1 NUMBER;
912 lNom2 NUMBER;
913 lLigne1 NUMBER;
914 lLigne2 NUMBER;
915 lVille NUMBER;
916 lProvince NUMBER;
917 lCodePostal NUMBER;
918 lNom NUMBER;
919 lIndRegional NUMBER;
920 lTel NUMBER;
921 lPosteTel NUMBER;
922 lLangue NUMBER;
923 lANom NUMBER;
924 lAIndRegional NUMBER;
925 lATel NUMBER;
926 lAPosteTel NUMBER;
927 lALangue NUMBER;
928 lNoConcepteur NUMBER;
929
930 EOL VARCHAR2(5);
931 l_address_line hr_locations.address_line_1%TYPE;
932 l_contact VARCHAR2(60);
933 l_transmitter_name VARCHAR2(100);
934 l_context1 ff_archive_items.context1%TYPE;
935 l_taxation_year VARCHAR2(4);
936 l_return VARCHAR2(60);
937 l_certification_no VARCHAR2(30);
938 l_VersionSchema VARCHAR2(20);
939
940 BEGIN
941
942 hr_utility.trace('XML Transmitter');
943
944 SELECT
945 fnd_global.local_chr(13) || fnd_global.local_chr(10)
946 INTO EOL
947 FROM dual;
948
949 lAnnee := 1;
950 lTypeEnvoi := 2;
951 lProvenance := 3;
952 lNo := 4;
953 lType := 5;
954 lNom1 := 6;
955 lNom2 := 7;
956 lLigne1 := 8;
957 lLigne2 := 9;
958 lVille := 10;
959 lProvince := 11;
960 lCodePostal := 12;
961 lNom := 13;
962 lIndRegional := 14;
963 lTel := 15;
964 lPosteTel := 16;
965 lLangue := 17;
966 lANom := 18;
967 lAIndRegional := 19;
968 lATel := 20;
969 lAPosteTel := 21;
970 lALangue := 22;
971 lNoConcepteur := 23;
972
973 l_taxation_year
974 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
975 l_context1
976 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
977
978 hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
979 hr_utility.trace('XML Transmitter: l_context1 = ' || to_char(l_context1));
980
981
982 -- Annee
983 tab_transmitter(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' ||EOL;
984 hr_utility.trace('tab_transmitter(lAnnee) = ' || tab_transmitter(lAnnee));
985
986 -- TypeEnvoi
987 tab_transmitter(lTypeEnvoi) := '<TypeEnvoi>' ||
988 pay_ca_archive_utils.get_archive_value(l_context1,
989 'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE') || '</TypeEnvoi>' || EOL;--Bug 9154497
990 hr_utility.trace('tab_transmitter(lTypeEnvoi) = ' ||
991 tab_transmitter(lTypeEnvoi));
992
993 tab_transmitter(lProvenance) := '<Provenance>' ||
994 pay_ca_archive_utils.get_archive_value(l_context1,
995 'CAEOY_RL1_SOURCE_OF_SLIPS') || '</Provenance>' || EOL;
996
997 hr_utility.trace('tab_transmitter(lProvenance) = ' || tab_transmitter(lProvenance));
998
999 tab_transmitter(lNo) := '<No>' ||
1000 pay_ca_archive_utils.get_archive_value(l_context1,
1001 'CAEOY_RL1_TRANSMITTER_NUMBER') || '</No>' || EOL;
1002
1003 hr_utility.trace('tab_transmitter(lNo) = ' || tab_transmitter(lNo));
1004
1005 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1006 'CAEOY_RL1_TRANSMITTER_TYPE');
1007 IF l_return IS NOT NULL THEN
1008 tab_transmitter(lType) := '<Type>' || l_return || '</Type>' || EOL;
1009 ELSE
1010 tab_transmitter(lType) := NULL;
1011 END IF;
1012
1013 hr_utility.trace('tab_transmitter(lType) = ' || tab_transmitter(lType));
1014
1015 l_transmitter_name := pay_ca_archive_utils.get_archive_value(l_context1,
1016 'CAEOY_RL1_TRANSMITTER_NAME');
1017
1018 tab_transmitter(lNom1) := '<Nom1>' ||
1019 pay_ca_rl1_mag.convert_special_char(substr(l_transmitter_name,1,30)) || '</Nom1>' || EOL;
1020
1021 hr_utility.trace('tab_transmitter(lNom1) = ' || tab_transmitter(lNom1));
1022
1023 l_return := substr(l_transmitter_name,31,30);
1024 IF l_return IS NOT NULL THEN
1025 tab_transmitter(lNom2) := '<Nom2>' || pay_ca_rl1_mag.convert_special_char(l_return) || '</Nom2>' || EOL;
1026 ELSE
1027 tab_transmitter(lNom2) := NULL;
1028 END IF;
1029
1030 hr_utility.trace('tab_transmitter(lNom2) = ' || tab_transmitter(lNom2));
1031
1032 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1033 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
1034
1035 tab_transmitter(lLigne1) := '<Ligne1>' ||
1036 pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,30)) || '</Ligne1>' || EOL;
1037
1038 hr_utility.trace('tab_transmitter(lLigne1) = ' || tab_transmitter(lLigne1));
1039
1040 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1041 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE2');
1042
1043 IF l_address_line IS NOT NULL THEN
1044 tab_transmitter(lLigne2) := '<Ligne2>' ||
1045 pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,30)) || '</Ligne2>' || EOL;
1046 ELSE
1047 tab_transmitter(lLigne2) := NULL;
1048 END IF;
1049
1050 hr_utility.trace('tab_transmitter(lLigne2) = ' || tab_transmitter(lLigne2));
1051
1052 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1053 'CAEOY_RL1_TRANSMITTER_CITY');
1054
1055 IF l_address_line IS NOT NULL THEN
1056 tab_transmitter(lVille) := '<Ville>' ||
1057 pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,30)) || '</Ville>' || EOL;
1058 ELSE
1059 tab_transmitter(lVille) := NULL;
1060 END IF;
1061
1062 hr_utility.trace('tab_transmitter(lVille) = ' || tab_transmitter(lVille));
1063
1064 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1065 'CAEOY_RL1_TRANSMITTER_PROVINCE');
1066
1067 tab_transmitter(lProvince) := '<Province>' ||
1068 SUBSTR(hr_general.decode_lookup(
1069 'CA_PROVINCE',l_address_line),1,20) || '</Province>' || EOL;
1070
1071 hr_utility.trace('tab_transmitter(lProvince) = ' || tab_transmitter(lProvince));
1072
1073 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1074 'CAEOY_RL1_TRANSMITTER_POSTAL_CODE');
1075
1076 tab_transmitter(lCodePostal) := '<CodePostal>' ||
1077 substr(replace(l_address_line,' '),1,6) || '</CodePostal>' || EOL;
1078
1079 hr_utility.trace('tab_transmitter(lCodePostal) = ' || tab_transmitter(lCodePostal));
1080
1081 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1082 'CAEOY_RL1_TECHNICAL_CONTACT_NAME');
1083
1084 IF l_contact IS NOT NULL THEN
1085 tab_transmitter(lNom) := '<Nom>' ||
1086 substr(l_contact,1,30) || '</Nom>' || EOL;
1087 ELSE
1088 tab_transmitter(lNom) := NULL;
1089 END IF;
1090
1091 hr_utility.trace('tab_transmitter(lNom) = ' || tab_transmitter(lNom));
1092
1093 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1094 'CAEOY_RL1_TECHNICAL_CONTACT_AREA_CODE');
1095
1096 IF l_contact IS NOT NULL THEN
1097 tab_transmitter(lIndRegional) := '<IndRegional>' ||
1098 l_contact || '</IndRegional>' || EOL;
1099 ELSE
1100 tab_transmitter(lIndRegional) := NULL;
1101 END IF;
1102
1103 hr_utility.trace('tab_transmitter(lIndRegional) = ' || tab_transmitter(lIndRegional));
1104
1105 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1106 'CAEOY_RL1_TECHNICAL_CONTACT_PHONE');
1107 IF l_contact IS NOT NULL THEN
1108 l_contact := substr(l_contact,1,3) || '-' || substr(l_contact,4,4);
1109 tab_transmitter(lTel) := '<Tel>' || l_contact || '</Tel>' || EOL;
1110 ELSE
1111 tab_transmitter(lTel) := NULL;
1112 END IF;
1113 hr_utility.trace('tab_transmitter(lTel) = ' || tab_transmitter(lTel));
1114
1115 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1116 'CAEOY_RL1_TECHNICAL_CONTACT_EXTENSION');
1117
1118 IF l_contact IS NOT NULL THEN
1119 tab_transmitter(lPosteTel) := '<PosteTel>' || l_contact ||
1120 '</PosteTel>' || EOL;
1121 ELSE
1122 tab_transmitter(lPosteTel) := NULL;
1123 END IF;
1124 hr_utility.trace('tab_transmitter(lPosteTel) = ' ||
1125 tab_transmitter(lPosteTel));
1126
1127 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1128 'CAEOY_RL1_TECHNICAL_CONTACT_LANGUAGE');
1129
1130 IF l_contact IS NOT NULL THEN
1131 tab_transmitter(lLangue) := '<Langue>' ||l_contact || '</Langue>' || EOL;
1132 ELSE
1133 tab_transmitter(lLangue) := NULL;
1134 END IF;
1135
1136 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1137 'CAEOY_RL1_ACCOUNTING_CONTACT_NAME');
1138
1139 IF l_contact IS NOT NULL THEN
1140 tab_transmitter(lANom) := '<Nom>' ||
1141 pay_ca_rl1_mag.convert_special_char(substr(l_contact,1,30)) || '</Nom>' || EOL;
1142 ELSE
1143 tab_transmitter(lANom) := NULL;
1144 END IF;
1145 hr_utility.trace('tab_transmitter(lANom) = ' || tab_transmitter(lANom));
1146
1147 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1148 'CAEOY_RL1_ACCOUNTING_CONTACT_AREA_CODE');
1149
1150 IF l_contact IS NOT NULL THEN
1151 tab_transmitter(lAIndRegional) := '<IndRegional>' || l_contact ||
1152 '</IndRegional>' || EOL;
1153 ELSE
1154 tab_transmitter(lAIndRegional) := NULL;
1155 END IF;
1156 hr_utility.trace('tab_transmitter(lAIndRegional) = ' || tab_transmitter(lAIndRegional));
1157
1158 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1159 'CAEOY_RL1_ACCOUNTING_CONTACT_PHONE');
1160
1161 IF l_contact IS NOT NULL THEN
1162 l_contact := substr(l_contact,1,3) || '-' || substr(l_contact,4,4);
1163 tab_transmitter(lATel) := '<Tel>' || l_contact || '</Tel>' || EOL;
1164 ELSE
1165 tab_transmitter(lATel) := NULL;
1166 END IF;
1167 hr_utility.trace('tab_transmitter(lATel) = ' || tab_transmitter(lATel));
1168
1169 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1170 'CAEOY_RL1_ACCOUNTING_CONTACT_EXTENSION');
1171
1172 IF l_contact IS NOT NULL THEN
1173 tab_transmitter(lAPosteTel) := '<PosteTel>' || l_contact ||
1174 '</PosteTel>' || EOL;
1175 ELSE
1176 tab_transmitter(lAPosteTel) := NULL;
1177 END IF;
1178 hr_utility.trace('tab_transmitter(lAPosteTel) = ' ||
1179 tab_transmitter(lAPosteTel));
1180
1181 l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1182 'CAEOY_RL1_ACCOUNTING_CONTACT_LANGUAGE');
1183 IF l_contact IS NOT NULL THEN
1184 tab_transmitter(lALangue) := '<Langue>' || l_contact ||
1185 '</Langue>' || EOL;
1186 ELSE
1187 tab_transmitter(lALangue) := NULL;
1188 END IF;
1189
1190 hr_utility.trace('tab_transmitter(lALangue) = ' || tab_transmitter(lALangue));
1191
1192 -- For bug 6738509
1193 if(l_taxation_year = '2006') then
1194 l_certification_no := 'RQ-06-01-098';
1195 elsif(l_taxation_year = '2007') then
1196 l_certification_no := 'RQ-07-01-146';
1197 elsif(l_taxation_year = '2008') then
1198 l_certification_no := 'RQ-08-01-114';
1199 elsif(l_taxation_year = '2009') then
1200 l_certification_no := 'RQ-09-01-047'; -- Bug 9206928
1201 elsif(l_taxation_year = '2010') then
1202 l_certification_no := 'RQ-10-01-058'; -- Bug 10364963
1203 elsif(l_taxation_year = '2011') then
1204 l_certification_no := 'RQ-11-01-083'; -- Bug 13486728
1205 elsif(l_taxation_year = '2012') then
1206 l_certification_no := 'RQ-12-01-111'; -- Bug 15996999
1207 else
1208 -- l_certification_no := 'RQ-09-99-999'; -- Bug 9037784
1209 /* Modified for bug 10134138
1210 For sending testing file format for certification no will be
1211 RQ-nn-99-999 nn stands for last two digit of taxation year.
1212 */
1213 l_certification_no := 'RQ-'||substr(trim(l_taxation_year),3,2)||'-99-999';
1214 end if;
1215 -- End bug 6738509
1216
1217 tab_transmitter(lNoConcepteur) :=
1218 '<NoCertification>'|| pay_ca_rl1_mag.convert_special_char(l_certification_no)
1219 ||'</NoCertification>'|| EOL;
1220
1221 -- Bug 7602718
1222 if(l_taxation_year = '2006') then
1223 l_VersionSchema := '2006.1.2';
1224 elsif(l_taxation_year = '2007') then
1225 l_VersionSchema := '2007.1.1';
1226 else
1227 l_VersionSchema := trim(l_taxation_year)||'.1';
1228 end if;
1229 -- End
1230
1231 l_final_xml_string :=
1232 '<Transmission VersionSchema="'||l_VersionSchema||'" ' ||
1233 'pxmlns="http://www.mrq.gouv.qc.ca/T5">' || EOL ||
1234 '<P>' || EOL ||
1235 tab_transmitter(lAnnee) ||
1236 tab_transmitter(lTypeEnvoi) ||
1237 tab_transmitter(lProvenance) || '<Preparateur>' || EOL ||
1238 tab_transmitter(lNo) ||
1239 tab_transmitter(lType) ||
1240 tab_transmitter(lNom1) ||
1241 tab_transmitter(lNom2) || '<Adresse>' || EOL ||
1242 tab_transmitter(lLigne1) ||
1243 tab_transmitter(lLigne2) ||
1244 tab_transmitter(lVille) ||
1245 tab_transmitter(lProvince) ||
1246 tab_transmitter(lCodePostal) || '</Adresse>' || EOL ||
1247 '</Preparateur>' || EOL || '<Informatique>' || EOL ||
1248 tab_transmitter(lNom) ||
1249 tab_transmitter(lIndRegional) ||
1250 tab_transmitter(lTel) ||
1251 tab_transmitter(lPosteTel) ||
1252 tab_transmitter(lLangue) || '</Informatique>' || EOL ||
1253 '<Comptabilite>' || EOL ||
1254 tab_transmitter(lANom) ||
1255 tab_transmitter(lAIndRegional) ||
1256 tab_transmitter(lATel) ||
1257 tab_transmitter(lAPosteTel) ||
1258 tab_transmitter(lALangue) || '</Comptabilite>' || EOL ||
1259 tab_transmitter(lNoConcepteur) ||
1260 '</P>' || EOL;
1261
1262 hr_utility.trace('xml_transmitter l_final_xml_string = ' ||
1263 l_final_xml_string);
1264 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1265
1266 END;
1267 END xml_transmitter_record;
1268 PROCEDURE end_of_file is
1269 BEGIN
1270
1271 DECLARE
1272
1273 l_final_xml CLOB;
1274 l_final_xml_string VARCHAR2(32000);
1275 l_is_temp_final_xml VARCHAR2(2);
1276
1277 BEGIN
1278
1279 l_final_xml_string := '</Transmission>';
1280
1281 hr_utility.trace('end_of_file l_final_xml_string = '
1282 || l_final_xml_string );
1283 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1284
1285 END;
1286
1287 END;
1288
1289 PROCEDURE xml_employee_record IS
1290 BEGIN
1291
1292 DECLARE
1293 /****************************************************/
1294 l_payroll_actid NUMBER;
1295 l_rep_type VARCHAR2(30);
1296 l_business_group_id NUMBER;
1297 l_year_start DATE;
1298 l_year_end DATE;
1299 l_legislative_param pay_payroll_actions.legislative_parameters%type;
1300 l_arch_asg_actid NUMBER;
1301 l_arch_pay_actid NUMBER;
1302 -- l_asg_id NUMBER;
1303 l_emplyr_final1 VARCHAR2(5000);
1304 l_emplyr_final2 VARCHAR2(5000);
1305 l_emplyr_final3 VARCHAR2(5000);
1306 --l_boxo_amount_cnt NUMBER :=0;
1307 --l_xml_boxo_amount VARCHAR2(200);
1308 --l_flag_seeattch BOOLEAN;
1309 -- l_footnote VARCHAR2(500);
1310 l_footnote_boxo1 VARCHAR2(1000);
1311 l_footnote_boxo2 VARCHAR2(1000);
1312 l_footnote_boxo3 VARCHAR2(1000);
1313 l_person_id1 NUMBER;
1314 l_session_date DATE;
1315 lForm_number NUMBER;
1316 l_neg_bal_exists BOOlEAN := FALSE;
1317 /*Bug 14701466 sbachu*/
1318 lv_rl1_slip_no varchar2(240);
1319 lv_org_rl1_slip_no varchar2(240);
1320 lv_ident_addr_string VARCHAR2(32000);
1321 lv_montants_string VARCHAR2(32000);
1322 TYPE FURTHER_CODES IS VARRAY(80) OF VARCHAR2(10);
1323 TYPE FURTHER_AMOUNTS IS VARRAY(80) OF VARCHAR2(50);
1324 l_further_code FURTHER_CODES:=FURTHER_CODES('','','','','','','','','','','','','',
1325 '','','','','','','','','','','','','',
1326 '','','','','','','','','','','','','',
1327 '','','','','','','','','','','','',
1328 '','','','','','','','','','','','',
1329 '','','','','','','','','','','','',
1330 '','','','','');
1331 l_further_amount FURTHER_AMOUNTS:=FURTHER_AMOUNTS('','','','','','','','','','','','','',
1332 '','','','','','','','','','','','','',
1333 '','','','','','','','','','','','','',
1334 '','','','','','','','','','','','',
1335 '','','','','','','','','','','','',
1336 '','','','','','','','','','','','',
1337 '','','','','');
1338 l_code varchar2(10);
1339 l_amount varchar2(50);
1340 l_i number;
1341 l_j number;
1342 l_k number;
1343 l_m number;
1344 l_further_info_string VARCHAR2(2000);
1345 ln_no_fi_per_slip number;
1346
1347 /* added these CURSORs for bug 13360884, to get further information*/
1348 cursor cur_boxo(p_arch_assact_id in pay_assignment_actions.assignment_action_id%TYPE) is
1349 SELECT 'RZ-'||substr(fdi.user_name,23,2) code,to_number(fai.value) value
1350 FROM ff_database_items fdi,
1351 ff_archive_items fai
1352 WHERE fai.user_entity_id = fdi.user_entity_id
1353 AND fai.context1 = p_arch_assact_id
1354 AND fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT____PER_JD_YTD'
1355 ORDER BY substr(fdi.user_name,5,4);
1356
1357 cursor cur_further_info(p_arch_assact_id in pay_assignment_actions.assignment_action_id%TYPE) is
1358 select substr(ltrim(rtrim(code)),1,60) code, value value
1359 from (
1360 select decode(fdi.user_name,'CAEOY_RL1_FURTHER_INFO_AMOUNT_201_AMT_PER_JD_YTD','201',
1361 'CAEOY_RL1_FURTHER_INFO_AMOUNT_211_AMT_PER_JD_YTD','211',
1362 'CAEOY_RL1_FURTHER_INFO_AMOUNT_235_AMT_PER_JD_YTD','235',
1363 substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),1,1)||'-'||
1364 substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),2)) code,
1365 to_number(fai.value) value
1366 from ff_database_items fdi,
1367 ff_archive_items fai
1368 where fai.user_entity_id=fdi.user_entity_id
1369 and fai.context1= p_arch_assact_id
1370 and fdi.user_name LIKE 'CAEOY_RL1_FURTHER_INFO_AMOUNT_%_AMT_PER_JD_YTD'
1371 union
1372 select 'G-1' code,
1373 to_number(pai.action_information5) value
1374 from pay_action_information pai
1375 where pai.action_context_id = p_arch_assact_id
1376 and pai.action_information4 = 'G-1'
1377 ) order by code;
1378
1379 /* ended these CURSORs for bug 13360884, to get further information*/
1380
1381 CURSOR c_get_payroll_asg_actid(p_assg_actid NUMBER) IS
1382 SELECT
1383 to_number(substr(paa.serial_number,3,14)) asgactid , --archiver assignment action id
1384 to_number(substr(paa.serial_number,17,14)) payactid, --archiver payroll action id
1385 paa.assignment_id asgid
1386 FROM
1387 pay_assignment_actions paa
1388 WHERE paa.assignment_action_id = p_assg_actid;
1389
1390 cursor c_province( p_arch_asact_id number ) is
1391 select fai.value
1392 from ff_archive_items fai,
1393 ff_database_items fdi
1394 where fai.user_entity_id = fdi.user_entity_id
1395 and fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT'
1396 and fai.context1 =p_arch_asact_id;
1397
1398 l_page_break VARCHAR2(50);
1399 l_final_xml_string1 VARCHAR2(32000);
1400 l_final_xml_string2 VARCHAR2(32000);
1401 l_final_xml_string3 VARCHAR2(32000);
1402 k NUMBER;
1403 addr pay_ca_rl1_reg.primaryaddress;
1404 /********************** ************************/
1405 l_final_xml CLOB;
1406 l_final_xml_string VARCHAR2(32000);
1407 l_is_temp_final_xml VARCHAR2(2);
1408
1409 CURSOR cur_parameters(p_mag_asg_action_id NUMBER) IS
1410 SELECT
1411 pai.locked_action_id, -- Archiver asg_action_id
1412 paa.assignment_id,
1413 pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id),
1414 -- date_earned
1415 fai.value -- Jurisdiction
1416 FROM
1417 ff_archive_items fai,
1418 ff_database_items fdi,
1419 per_all_people_f ppf,
1420 per_all_assignments_f paf,
1421 pay_action_interlocks pai,
1422 pay_assignment_actions paa,
1423 pay_payroll_actions ppa,
1424 pay_assignment_actions paa_arch
1425 WHERE
1426 paa.assignment_action_id = p_mag_asg_action_id AND
1427 ppa.payroll_action_id = paa.payroll_action_id AND
1428 pai.locking_action_id = paa.assignment_action_id AND
1429 paf.assignment_id = paa.assignment_id AND
1430 ppf.person_id = paf.person_id AND
1431 pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
1432 between
1433 paf.effective_start_date and paf.effective_end_date AND
1434 pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
1435 between
1436 ppf.effective_start_date and ppf.effective_end_date AND
1437 fai.context1 = pai.locked_action_id AND
1438 fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT' AND
1439 fai.user_entity_id = fdi.user_entity_id AND
1440 paa_arch.assignment_action_id = fai.context1 AND
1441 --paa_arch.payroll_action_id =
1442 -- to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')) AND
1443 paa_arch.assignment_action_id = pai.locked_action_id
1444 ORDER BY
1445 ppf.last_name,ppf.first_name,ppf.middle_names;
1446
1447 CURSOR cur_ppa (p_payroll_action_id IN NUMBER) IS
1448 SELECT ppa.legislative_parameters
1449 FROM pay_payroll_actions ppa
1450 WHERE ppa.payroll_action_id = p_payroll_action_id;
1451
1452 CURSOR c_original_slipno (p_person IN NUMBER,p_tax_year IN VARCHAR2,p_trans_id IN NUMBER) IS
1453 SELECT pei_information7
1454 FROM per_people_extra_info pei
1455 WHERE pei_information6=to_char(p_trans_id)
1456 AND substr(pei_information5,1,4)=p_tax_year
1457 AND to_number(pei.person_id) = p_person
1458 AND pei.information_type = 'PAY_CA_RL1_FORM_NO'
1459 ORDER BY pei_information7; /*Bug 13564765,15972601*/
1460
1461 l_original_slpno VARCHAR2(150);
1462 l_mag_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
1463 l_arch_action_id pay_assignment_actions.assignment_action_id%TYPE;
1464 l_asg_id per_assignments_f.assignment_id%TYPE;
1465 l_date_earned DATE;
1466 l_province VARCHAR2(30);
1467 l_O_AutreRevenu VARCHAR2(1000);
1468
1469 TYPE employee_info IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
1470
1471 tab_employee employee_info;
1472 tab_xml_employee employee_info; --
1473
1474 lAnnee NUMBER;
1475 lNoReleve NUMBER;
1476 lNAS NUMBER;
1477 --SIN1 NUMBER;
1478 --SIN2 NUMBER;
1479 --SIN3 NUMBER;
1480 lNo NUMBER;
1481 lNomFamille NUMBER;
1482 lPrenom NUMBER;
1483 lInitiale NUMBER;
1484 lLigne1 NUMBER;
1485 lLigne2 NUMBER;
1486 lVille NUMBER;
1487 lProvince NUMBER;
1488 lCodePostal NUMBER;
1489 lA_RevenuEmploi NUMBER;
1490 lB_CotisationRRQ NUMBER;
1491 lC_CotisationAssEmploi NUMBER;
1492 lD_CotisationRPA NUMBER;
1493 lE_ImpotQue NUMBER;
1494 lF_CotisationSyndicale NUMBER;
1495 lG_SalaireAdmisRRQ NUMBER;
1496 lV_NourritureLogement NUMBER;
1497 lW_Vehicule NUMBER;
1498 lJ_RegimeAssMaladie NUMBER;
1499 lK_Voyage NUMBER;
1500 lL_AutreAvantage NUMBER;
1501 lM_Commission NUMBER;
1502 lN_DonBienfaisance NUMBER;
1503 lO_AutreRevenu NUMBER;
1504 l_SourceCase NUMBER;
1505 lP_RegimeAssInterEntr NUMBER;
1506 lQ_SalaireDiffere NUMBER;
1507 lR_RevenuIndien NUMBER;
1508 lS_PourboireRecu NUMBER;
1509 lT_PourboireAttribue NUMBER;
1510 lU_RetraiteProgressive NUMBER;
1511 l_ContisationRPC NUMBER;
1512 lH_CotisationRQAP NUMBER;
1513 lI_SalaireAdmisRQAP NUMBER;
1514 /* BUG 13567075 sbachu*/
1515 l_FraisGarde NUMBER;
1516 l_DeductionForce NUMBER;
1517
1518 l_person_id per_people_f.person_id%TYPE;
1519 l_address_line1 per_addresses.address_line1%TYPE;
1520 l_address_line2 per_addresses.address_line2%TYPE;
1521 l_address_line3 per_addresses.address_line3%TYPE;
1522 l_combined_addr VARCHAR2(500);
1523 l_city per_addresses.town_or_city%TYPE;
1524 l_postal_code per_addresses.postal_code%TYPE;
1525 l_country VARCHAR2(60);
1526 l_emp_province per_addresses.region_1%TYPE;
1527 EOL VARCHAR2(5);
1528 l_taxation_year VARCHAR2(5);
1529 l_name VARCHAR2(200);
1530 l_box VARCHAR2(20);
1531 l_per_id varchar2(50);
1532 l_boxA VARCHAR2(20);
1533 l_boxB VARCHAR2(20);
1534 l_boxU VARCHAR2(20);
1535 l_boxQ VARCHAR2(20);
1536 l_return VARCHAR2(30);
1537 l_status VARCHAR2(10);
1538 l_addr_begin_tag VARCHAR2(10);
1539 l_addr_end_tag VARCHAR2(10);
1540 l_formatted_box VARCHAR2(20);
1541 l_formatted_code VARCHAR2(20);
1542 l_boxO VARCHAR2(20);
1543 l_other_details VARCHAR2(32000);
1544 l_authorization_code VARCHAR2(100);
1545 l_authorization_header VARCHAR2(100);
1546 l_year VARCHAR2(5);
1547 l_transmitter_org_id NUMBER;
1548 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
1549 lBoxA_01 NUMBER;
1550 lBoxA_02 NUMBER;
1551 lBoxA_15 NUMBER;
1552 lBoxA_16 NUMBER;
1553 lBoxA_17 NUMBER;
1554 lBoxA_18 NUMBER;
1555 lBoxA_19 NUMBER;
1556 lBoxA_25 NUMBER;
1557 lBoxA_26 NUMBER;
1558 lBoxA_27 NUMBER;
1559 lBoxD_07 NUMBER;
1560 lBoxD_08 NUMBER;
1561 lBoxD_20 NUMBER;
1562 lBoxK_11 NUMBER;
1563 lBoxO_RA NUMBER;
1564 lBoxO_RB NUMBER;
1565 lBoxO_RC NUMBER;
1566 lBoxO_RD NUMBER;
1567 lBoxO_RE NUMBER;
1568 lBoxO_RF NUMBER;
1569 lBoxO_RG NUMBER;
1570 lBoxO_RH NUMBER;
1571 lBoxO_RI NUMBER;
1572 lBoxO_RJ NUMBER;
1573 lBoxO_RK NUMBER;
1574 lBoxO_RL NUMBER;
1575 lBoxO_RL22 NUMBER;
1576 lBoxO_RL28 NUMBER;
1577 lBoxO_RM NUMBER;
1578 lBoxO_RN NUMBER;
1579 lBoxO_RO NUMBER;
1580 lBoxO_RP NUMBER;
1581 lBoxO_RQ NUMBER;
1582 lBoxO_RR NUMBER;
1583 lBoxO_RS NUMBER;
1584 lBoxO_RT NUMBER;
1585 lBoxO_RU NUMBER;
1586 lBoxO_RV NUMBER;
1587 lBoxO_RW NUMBER;
1588 lBoxO_RX NUMBER;
1589 --Added for bug 9178892
1590 lBoxO_CA NUMBER;
1591 lBoxO_CB NUMBER;
1592 lBoxO_CC NUMBER;
1593 --
1594 lBoxQ_24 NUMBER;
1595 lBoxR_14 NUMBER;
1596 lErrorDetails NUMBER;
1597 lBoxA_29 NUMBER;
1598 lBoxA_30 NUMBER;
1599 lBoxO_RN_31 NUMBER;
1600 l_origi_slipno NUMBER;
1601
1602 CURSOR cur_get_meaning(p_lookup_code VARCHAR2) IS
1603 SELECT
1604 meaning
1605 FROM
1606 hr_lookups
1607 WHERE
1608 lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
1609 lookup_code = p_lookup_code;
1610
1611 /* Cursor for fetching authorisation code */
1612 CURSOR c_get_auth_code(p_reporting_year varchar2) IS
1613 SELECT meaning
1614 FROM hr_lookups
1615 WHERE trim(lookup_code) = p_reporting_year
1616 AND lookup_type = 'PAY_CA_RL1_PDF_AUTH'
1617 AND enabled_flag='Y';
1618
1619 l_meaning hr_lookups.meaning%TYPE;
1620 l_msg_code VARCHAR2(30);
1621 l_all_box_0 BOOLEAN;
1622 l_format_mask VARCHAR2(30);
1623 l_sequence_number NUMBER(9);
1624 l_sequence_number1 NUMBER(9);
1625
1626 /* Moved this function to package pay_ca_eoy_rl1_archive
1627
1628 FUNCTION getnext_seq_num (p_curr_seq IN NUMBER)
1629 RETURN NUMBER IS
1630 l_seq_number number;
1631 l_check_number number;
1632 BEGIN
1633
1634 l_check_number := mod(p_curr_seq,7);
1635 hr_utility.trace('l_check_number ='|| l_check_number);
1636 l_seq_number := (p_curr_seq * 10) + l_check_number;
1637 hr_utility.trace('l_seq_number ='|| l_seq_number);
1638 return l_seq_number;
1639 END; */
1640
1641 BEGIN
1642 -- hr_utility.trace_on(null,'PDF');
1643 hr_utility.trace('Inside the Employee record proc');
1644 /*******************************************************************************/
1645 --l_rep_type:=pay_magtape_generic.get_parameter_value('REPORT_TYPE'); --
1646 l_payroll_actid
1647 := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1648 hr_utility.trace('l_payroll_actid='||l_payroll_actid);
1649 SELECT ppa.report_type
1650 INTO l_rep_type
1651 from pay_payroll_actions ppa
1652 where payroll_action_id=l_payroll_actid;
1653 hr_utility.trace('report_type='||l_rep_type);
1654 /******************************* ************************************/
1655
1656
1657 hr_utility.trace('XML Employee');
1658 l_status := 'Success';
1659 l_all_box_0 := TRUE;
1660 l_format_mask := '99999999999999990.99';
1661
1662 SELECT
1663 fnd_global.local_chr(13) || fnd_global.local_chr(10)
1664 INTO EOL
1665 FROM dual;
1666
1667 lAnnee := 1;
1668 lNoReleve := 2;
1669 lNAS := 3;
1670 lNo := 4;
1671 lNomFamille := 5;
1672 lPrenom := 6;
1673 lInitiale := 7;
1674 lLigne1 := 8;
1675 lLigne2 := 9;
1676 lVille := 10;
1677 lProvince := 11;
1678 lCodePostal := 12;
1679 lA_RevenuEmploi := 13;
1680 lB_CotisationRRQ := 14;
1681 lC_CotisationAssEmploi := 15;
1682 lD_CotisationRPA := 16;
1683 lE_ImpotQue := 17;
1684 lF_CotisationSyndicale := 18;
1685 lG_SalaireAdmisRRQ := 19;
1686 lV_NourritureLogement := 20;
1687 lW_Vehicule := 21;
1688 lJ_RegimeAssMaladie := 22;
1689 lK_Voyage := 23;
1690 lL_AutreAvantage := 24;
1691 lM_Commission := 25;
1692 lN_DonBienfaisance := 26;
1693 lO_AutreRevenu := 27;
1694 l_SourceCase := 28;
1695 lP_RegimeAssInterEntr := 29;
1696 lQ_SalaireDiffere := 30;
1697 lR_RevenuIndien := 31;
1698 lS_PourboireRecu := 32;
1699 lT_PourboireAttribue := 33;
1700 lU_RetraiteProgressive := 34;
1701 l_ContisationRPC := 35;
1702 lH_CotisationRQAP:=92;
1703 lI_SalaireAdmisRQAP := 93;
1704
1705 lBoxA_01 := 36;
1706 lBoxA_02 := 37;
1707 lBoxA_15 := 38;
1708 lBoxA_16 := 39;
1709 lBoxA_17 := 40;
1710 lBoxA_18 := 41;
1711 lBoxA_19 := 42;
1712 lBoxA_25 := 43;
1713 lBoxA_26 := 44;
1714 lBoxA_27 := 45;
1715 lBoxD_07 := 46;
1716 lBoxD_08 := 47;
1717 lBoxD_20 := 48;
1718
1719 lBoxO_RA := 49;
1720 lBoxO_RB := 50;
1721 lBoxO_RC := 51;
1722 lBoxO_RD := 52;
1723 lBoxO_RE := 53;
1724 lBoxO_RF := 54;
1725 lBoxO_RG := 55;
1726 lBoxO_RH := 56;
1727 lBoxO_RI := 57;
1728 lBoxO_RJ := 58;
1729 lBoxO_RK := 59;
1730 lBoxO_RL := 60;
1731 lBoxO_RL22 := 61;
1732 lBoxO_RL28 := 62;
1733 lBoxO_RM := 63;
1734 lBoxO_RN := 64;
1735 lBoxO_RO := 65;
1736 lBoxO_RP := 66;
1737 lBoxO_RQ := 67;
1738 lBoxO_RR := 68;
1739 lBoxO_RS := 69;
1740 lBoxO_RT := 70;
1741 lBoxO_RU := 80;
1742 lBoxO_RV := 81;
1743 lBoxO_RW := 82;
1744 lBoxO_RX := 83;
1745 lBoxQ_24 := 84;
1746 lBoxR_14 := 85;
1747 lBoxK_11 := 86;
1748 lErrorDetails := 87;
1749 lBoxA_29 := 88;
1750 lBoxA_30 := 89;
1751 lBoxO_RN_31 := 90;
1752 lForm_number :=91;
1753 l_origi_slipno :=94; --
1754 --SIN1 :=93;
1755 --SIN2 :=94;
1756 --SIN3 :=95;
1757
1758 --Added for bug 9178892
1759 lBoxO_CA := 95;
1760 lBoxO_CB := 96;
1761 lBoxO_CC := 97;
1762 --
1763 --Added for bug 13567075 /*sbachu*/
1764 l_FraisGarde := 98;
1765 l_DeductionForce := 99;
1766
1767 l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value
1768 ('TRANSFER_ACT_ID'));
1769
1770 hr_utility.trace('XML Employee: l_mag_asg_action_id = '
1771 || to_char(l_mag_asg_action_id));
1772
1773 /**********************************************************************/
1774
1775 OPEN cur_parameters(l_mag_asg_action_id);
1776 FETCH cur_parameters
1777 INTO
1778 l_arch_action_id,
1779 l_asg_id,
1780 l_date_earned,
1781 l_province;
1782 -- CLOSE cur_parameters;
1783 if cur_parameters%found then
1784 /**************************************** ******************/
1785
1786 hr_utility.trace('XML Employee: l_arch_action_id = '
1787 || to_char(l_arch_action_id));
1788 hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1789 hr_utility.trace('XML Employee: l_date_earned = '
1790 || to_char(l_date_earned));
1791 hr_utility.trace('XML Employee: l_province = ' || l_province);
1792
1793 l_taxation_year
1794 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
1795
1796 l_authorization_header := 'No d''autorisation :';
1797
1798 l_year := pay_ca_archive_utils.get_archive_value(l_arch_pay_actid, 'CAEOY_TAXATION_YEAR');
1799
1800 /*if( l_year ='2006' ) then
1801 l_authorization_code := 'FS-06-01-103';
1802 elsif (l_year ='2007' ) then
1803 l_authorization_code := 'FS-07-01-107'; --Bug 6747916
1804 elsif (l_year ='2008' ) then
1805 l_authorization_code := 'FS-08-01-020'; --Bug 7503515
1806 end if; */
1807
1808 open c_get_auth_code(l_year);
1809 fetch c_get_auth_code into l_authorization_code;
1810 close c_get_auth_code;
1811
1812 --Annee
1813 tab_employee(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' || EOL;
1814 hr_utility.trace('tab_employee(lAnnee) = ' || tab_employee(lAnnee));
1815 --NoReleve
1816 /*Bug 13564765 here*/
1817 lv_rl1_slip_no := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1818 'CAEOY_RL1_SLIP_NUMBER');
1819 l_return := pay_ca_rl1_mag.get_slip_seq_no('|',lv_rl1_slip_no,1);
1820
1821 IF l_return IS NULL THEN
1822 l_status := 'Failed';
1823 tab_employee(lNoReleve) := NULL;
1824 tab_xml_employee(lNoReleve) := NULL; --
1825 ELSE
1826 tab_employee(lNoReleve) := '<NoReleve>' || l_return ||
1827 '</NoReleve>' || EOL;
1828 tab_xml_employee(lNoReleve) := l_return; --
1829 END IF;
1830 hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve));
1831 hr_utility.trace('tab_xml_employee(lNoReleve) = ' || tab_xml_employee(lNoReleve)); --
1832
1833 -- NAS
1834 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1835 'CAEOY_EMPLOYEE_SIN');
1836
1837 IF l_return IS NOT NULL THEN
1838 tab_employee(lNAS) := '<NAS>' || l_return || '</NAS>' || EOL;
1839 tab_xml_employee(lNAS) := l_return; --
1840
1841 ELSE
1842 l_Status := 'Failed';
1843 l_msg_code := 'SIN';
1844 tab_employee(lNAS) := NULL;
1845 tab_xml_employee(lNAS) := NULL; --
1846 END IF;
1847 hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
1848 hr_utility.trace('tab_xml_employee(lNAS) = ' || tab_xml_employee(lNAS));
1849
1850 -- No
1851 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1852 'CAEOY_EMPLOYEE_NUMBER');
1853 IF l_return IS NOT NULL THEN
1854 tab_employee(lNo) := '<No>' || pay_ca_rl1_mag.convert_special_char(l_return) || '</No>' || EOL;
1855 tab_xml_employee(lNo) := pay_ca_rl1_mag.convert_special_char(l_return); --
1856 ELSE
1857 tab_employee(lNo) := NULL;
1858 tab_xml_employee(lNo) := NULL; --
1859 END IF;
1860 hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
1861 hr_utility.trace('tab_xml_employee(lNo) = ' || tab_xml_employee(lNo)); --
1862
1863 -- NomFamille
1864 l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1865 'CAEOY_EMPLOYEE_LAST_NAME');
1866 tab_employee(lNomFamille) := '<NomFamille>' ||
1867 pay_ca_rl1_mag.convert_special_char(substr(l_name,1,30)) || '</NomFamille>' || EOL;
1868 tab_xml_employee(lNomFamille) := pay_ca_rl1_mag.convert_special_char(substr(l_name,1,20)); --
1869 hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
1870 hr_utility.trace('tab_xml_employee(lNomFamille) = ' || tab_xml_employee(lNomFamille)); --
1871
1872 -- Prenom
1873 l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1874 'CAEOY_EMPLOYEE_FIRST_NAME');
1875 IF l_name is NOT NULL THEN
1876 tab_employee(lPrenom) := '<Prenom>' || pay_ca_rl1_mag.convert_special_char(substr(l_name,1,30))
1877 || '</Prenom>' || EOL;
1878 tab_xml_employee(lPrenom) := pay_ca_rl1_mag.convert_special_char(substr(l_name,1,20)) ; --
1879
1880 ELSE
1881 l_status := 'Failed';
1882 l_msg_code := 'MISSING_EMP_FIRST_NAME';
1883 tab_employee(lPrenom) := NULL;
1884 tab_xml_employee(lPrenom) := NULL ; --
1885 END IF;
1886 hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
1887 hr_utility.trace('tab_xml_employee(lPrenom) = ' || tab_xml_employee(lPrenom)); --
1888
1889 -- Initiale
1890 l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1891 'CAEOY_EMPLOYEE_INITIAL');
1892 IF l_name is NOT NULL THEN
1893 tab_employee(lInitiale) := '<Initiale>' || substr(l_name,1,1)
1894 || '</Initiale>' || EOL;
1895 tab_xml_employee(lInitiale) := substr(l_name,1,1); --
1896
1897 ELSE
1898 tab_employee(lInitiale) := NULL;
1899 tab_xml_employee(lInitiale) := NULL; --
1900 END IF;
1901 hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
1902 hr_utility.trace('tab_xml_employee(lInitiale) = ' || tab_xml_employee(lInitiale)); --
1903
1904 l_person_id := to_number(pay_ca_archive_utils.get_archive_value(
1905 l_arch_action_id,
1906 'CAEOY_PERSON_ID'));
1907 /***************************************************************/
1908
1909 l_return := pay_ca_emp_address_dtls.get_emp_address(
1910 l_person_id,
1911 l_address_line1,
1912 l_address_line2,
1913 l_address_line3,
1914 l_city,
1915 l_postal_code,
1916 l_country,
1917 l_emp_province
1918 );
1919 -- If Address line 1 is NULL or ' ' then the employee is missing
1920 -- address information - as line 1 is mandatory in the Address form.
1921 -- Need to check data by SS transaction /API.
1922
1923 hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1924 hr_utility.trace('l_address_line1 = ' || l_address_line1);
1925 hr_utility.trace('l_address_line2 = ' || l_address_line2);
1926 hr_utility.trace('l_postal_code = ' || l_postal_code);
1927
1928 -- Address Line 1
1929 IF l_address_line1 IS NULL OR
1930 l_address_line1 = ' ' THEN
1931
1932 l_status := 'Failed';
1933 l_msg_code := 'MISSING_EMP_ADDRESS';
1934
1935 l_addr_begin_tag := NULL;
1936 tab_employee(lLigne1) := NULL;
1937 tab_employee(lLigne2) := NULL;
1938 tab_employee(lVille) := NULL;
1939 tab_employee(lProvince) := NULL;
1940 tab_employee(lCodePostal) := NULL;
1941 tab_employee(lCodePostal) := NULL;
1942 l_addr_end_tag := NULL;
1943
1944 ELSE
1945
1946 l_addr_begin_tag := '<Adresse>';
1947
1948 tab_employee(lLigne1) := '<Ligne1>' ||
1949 pay_ca_rl1_mag.convert_special_char(substr(l_address_line1,1,30)) || '</Ligne1>' || EOL;
1950 hr_utility.trace('tab_employee(lLigne1) = ' || tab_employee(lLigne1));
1951
1952 -- Address Line 2
1953 IF ((l_address_line2 IS NULL OR
1954 l_address_line2 <> ' ') OR
1955 (l_address_line3 IS NULL OR
1956 l_address_line3 <> ' ')) THEN
1957 l_combined_addr := rtrim(ltrim(l_address_line2)) || rtrim(ltrim(l_address_line3));
1958 tab_employee(lLigne2) := '<Ligne2>' ||
1959 pay_ca_rl1_mag.convert_special_char(substr(l_combined_addr,1,30)) || '</Ligne2>' || EOL;
1960 ELSE
1961 tab_employee(lLigne2) := NULL;
1962 END IF;
1963 hr_utility.trace('tab_employee(lLigne2) = ' || tab_employee(lLigne2));
1964
1965 -- Ville (City)
1966 IF l_city IS NULL OR
1967 l_city <> ' ' THEN
1968 tab_employee(lVille) := '<Ville>' ||
1969 substr(l_city,1,30) || '</Ville>' || EOL;
1970 ELSE
1971 tab_employee(lVille) := NULL;
1972 END IF;
1973 hr_utility.trace('tab_employee(lVille) = ' || tab_employee(lVille));
1974
1975 -- Province
1976 IF l_emp_province IS NULL OR
1977 l_emp_province <> ' ' THEN
1978 IF l_country = 'CA' THEN
1979 tab_employee(lProvince) := '<Province>' ||
1980 SUBSTR(hr_general.decode_lookup(
1981 'CA_PROVINCE',l_emp_province),1,20) || '</Province>' || EOL;
1982 ELSIF l_country = 'US' THEN
1983 tab_employee(lProvince) := '<Province>' || l_emp_province || '</Province>' || EOL;
1984 ELSE
1985 tab_employee(lProvince) := '<Province>' || l_country || '</Province>' || EOL;
1986 END IF;
1987 ELSE
1988 tab_employee(lProvince) := NULL;
1989 END IF;
1990 hr_utility.trace('tab_employee(lProvince) = ' || tab_employee(lProvince));
1991
1992 -- Postal Code
1993 IF l_postal_code IS NULL OR
1994 l_postal_code <> ' ' THEN
1995 tab_employee(lCodePostal) := '<CodePostal>' ||
1996 substr(replace(l_postal_code,' '),1,6) || '</CodePostal>' || EOL;
1997 ELSE
1998 tab_employee(lCodePostal) := NULL;
1999 END IF;
2000 hr_utility.trace('tab_employee(lCodePostal) = ' || tab_employee(lCodePostal));
2001 l_addr_end_tag := '</Adresse>';
2002
2003 END IF;
2004
2005
2006 -- Summ (Box A)
2007
2008 l_box := pay_ca_archive_utils.get_archive_value(
2009 l_arch_action_id,
2010 l_province,
2011 'JURISDICTION_CODE',
2012 'CAEOY_GROSS_EARNINGS_PER_JD_YTD');
2013
2014 IF TO_NUMBER(l_box) > 9999999.99 THEN
2015 l_status := 'Failed';
2016 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2017 END IF;
2018
2019 IF l_box IS NOT NULL AND
2020 to_number(l_box) <> 0 THEN
2021
2022 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2023 INTO l_formatted_box
2024 FROM dual;
2025 tab_employee(lA_RevenuEmploi) := '<A_RevenuEmploi>' || l_formatted_box ||
2026 '</A_RevenuEmploi>' || EOL;
2027 tab_xml_employee(lA_RevenuEmploi) := l_formatted_box; --
2028 l_all_box_0 := FALSE;
2029 l_BoxA := l_formatted_box;
2030 ELSE
2031 tab_employee(lA_RevenuEmploi) := NULL;
2032 tab_xml_employee(lA_RevenuEmploi) := NULL; --
2033 END IF;
2034 hr_utility.trace('tab_employee(lA_RevenuEmploi) = ' || tab_employee(lA_RevenuEmploi));
2035 hr_utility.trace('tab_xml_employee(lA_RevenuEmploi) = ' || tab_xml_employee(lA_RevenuEmploi));
2036
2037 -- Summ (Box B)
2038 l_box := pay_ca_archive_utils.get_archive_value(
2039 l_arch_action_id,
2040 l_province,
2041 'JURISDICTION_CODE',
2042 'CAEOY_QPP_EE_WITHHELD_PER_JD_YTD');
2043
2044 IF TO_NUMBER(l_box) > 9999999.99 THEN
2045 l_status := 'Failed';
2046 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2047 END IF;
2048
2049 IF l_box IS NOT NULL AND
2050 to_number(l_box) <> 0 THEN
2051
2052 l_BoxB := l_box;
2053
2054 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2055 INTO l_formatted_box
2056 FROM dual;
2057
2058 tab_employee(lB_CotisationRRQ) := '<B_CotisationRRQ>' || l_formatted_box
2059 || '</B_CotisationRRQ>' || EOL;
2060 tab_xml_employee(lB_CotisationRRQ) := l_formatted_box; --
2061 l_all_box_0 := FALSE;
2062 ELSE
2063
2064 tab_employee(lB_CotisationRRQ) := NULL;
2065 tab_xml_employee(lB_CotisationRRQ) := NULL; --
2066
2067 END IF;
2068 hr_utility.trace('tab_employee(lB_CotisationRRQ) = ' ||
2069 tab_employee(lB_CotisationRRQ));
2070 hr_utility.trace('tab_xml_employee(lB_CotisationRRQ) = ' ||
2071 tab_xml_employee(lB_CotisationRRQ)); --
2072
2073 -- Summ (Box C)
2074 l_box := pay_ca_archive_utils.get_archive_value(
2075 l_arch_action_id,
2076 l_province,
2077 'JURISDICTION_CODE',
2078 'CAEOY_EI_EE_WITHHELD_PER_JD_YTD');
2079
2080 IF TO_NUMBER(l_box) > 9999999.99 THEN
2081 l_status := 'Failed';
2082 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2083 END IF;
2084
2085 IF l_box IS NOT NULL AND
2086 to_number(l_box) <> 0 THEN
2087
2088 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2089 INTO l_formatted_box
2090 FROM dual;
2091
2092 tab_employee(lC_CotisationAssEmploi) := '<C_CotisationAssEmploi>' ||
2093 l_formatted_box || '</C_CotisationAssEmploi>' || EOL;
2094 tab_xml_employee(lC_CotisationAssEmploi) := l_formatted_box; --
2095 l_all_box_0 := FALSE;
2096 ELSE
2097 tab_employee(lC_CotisationAssEmploi) := NULL;
2098 tab_xml_employee(lC_CotisationAssEmploi) := NULL; --
2099 END IF;
2100
2101 hr_utility.trace('tab_employee(lC_CotisationAssEmploi) = ' ||
2102 tab_employee(lC_CotisationAssEmploi));
2103
2104 hr_utility.trace('tab_xml_employee(lC_CotisationAssEmploi) = ' ||
2105 tab_xml_employee(lC_CotisationAssEmploi)); --
2106 -- Summ (Box D)
2107 l_box := pay_ca_archive_utils.get_archive_value(
2108 l_arch_action_id,
2109 l_province,
2110 'JURISDICTION_CODE',
2111 'CAEOY_RL1_BOXD_PER_JD_YTD');
2112
2113 IF TO_NUMBER(l_box) > 9999999.99 THEN
2114 l_status := 'Failed';
2115 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2116 END IF;
2117
2118 IF l_box IS NOT NULL AND
2119 to_number(l_box) <> 0 THEN
2120
2121 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2122 INTO l_formatted_box
2123 FROM dual;
2124
2125 tab_employee(lD_CotisationRPA) := '<D_CotisationRPA>' ||
2126 l_formatted_box || '</D_CotisationRPA>' || EOL;
2127 tab_xml_employee(lD_CotisationRPA) := l_formatted_box; --
2128 l_all_box_0 := FALSE;
2129 ELSE
2130 tab_employee(lD_CotisationRPA) := NULL;
2131 tab_xml_employee(lD_CotisationRPA) := NULL; --
2132 END IF;
2133 hr_utility.trace('tab_employee(lD_CotisationRPA) = ' ||
2134 tab_employee(lD_CotisationRPA));
2135 hr_utility.trace('tab_xml_employee(lD_CotisationRPA) = ' ||
2136 tab_xml_employee(lD_CotisationRPA));
2137
2138
2139 -- (Box E)
2140
2141 l_box := pay_ca_archive_utils.get_archive_value(
2142 l_arch_action_id,
2143 l_province,
2144 'JURISDICTION_CODE',
2145 'CAEOY_PROV_WITHHELD_PER_JD_YTD');
2146
2147 IF TO_NUMBER(l_box) > 9999999.99 THEN
2148 l_status := 'Failed';
2149 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2150 END IF;
2151
2152 IF l_box IS NOT NULL AND
2153 to_number(l_box) <> 0 THEN
2154
2155 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2156 INTO l_formatted_box
2157 FROM dual;
2158
2159 tab_employee(lE_ImpotQue) := '<E_ImpotQue>' ||
2160 l_formatted_box || '</E_ImpotQue>' || EOL;
2161 tab_xml_employee(lE_ImpotQue) := l_formatted_box ; --
2162 l_all_box_0 := FALSE;
2163 ELSE
2164 tab_employee(lE_ImpotQue) := NULL ;
2165 tab_xml_employee(lE_ImpotQue) := NULL ; --
2166 END IF;
2167
2168 hr_utility.trace('tab_employee(lE_ImpotQue) = ' ||
2169 tab_employee(lE_ImpotQue));
2170 hr_utility.trace('tab_xml_employee(lE_ImpotQue) = ' ||
2171 tab_xml_employee(lE_ImpotQue)); --
2172
2173 -- (Box F)
2174 l_box := pay_ca_archive_utils.get_archive_value(
2175 l_arch_action_id,
2176 l_province,
2177 'JURISDICTION_CODE',
2178 'CAEOY_RL1_BOXF_PER_JD_YTD');
2179
2180 IF TO_NUMBER(l_box) > 9999999.99 THEN
2181 l_status := 'Failed';
2182 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2183 END IF;
2184
2185 IF l_box IS NOT NULL AND
2186 to_number(l_box) <> 0 THEN
2187
2188 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2189 INTO l_formatted_box
2190 FROM dual;
2191
2192 tab_employee(lF_CotisationSyndicale) := '<F_CotisationSyndicale>' ||
2193 l_formatted_box || '</F_CotisationSyndicale>' || EOL;
2194 tab_xml_employee(lF_CotisationSyndicale) := l_formatted_box; --
2195 l_all_box_0 := FALSE;
2196 ELSE
2197 tab_employee(lF_CotisationSyndicale) := NULL;
2198 tab_xml_employee(lF_CotisationSyndicale) := NULL; --
2199 END IF;
2200 hr_utility.trace('tab_employee(lF_CotisationSyndicale) = ' ||
2201 tab_employee(lF_CotisationSyndicale));
2202
2203 hr_utility.trace('tab_xml_employee(lF_CotisationSyndicale) = ' ||
2204 tab_xml_employee(lF_CotisationSyndicale));
2205 -- (Box Q)
2206 l_box := pay_ca_archive_utils.get_archive_value(
2207 l_arch_action_id,
2208 l_province,
2209 'JURISDICTION_CODE',
2210 'CAEOY_RL1_BOXQ_PER_JD_YTD');
2211
2212 IF TO_NUMBER(l_box) > 9999999.99 THEN
2213 l_status := 'Failed';
2214 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2215 END IF;
2216
2217 IF l_box IS NOT NULL AND
2218 to_number(l_box) <> 0 THEN
2219
2220 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2221 INTO l_formatted_box
2222 FROM dual;
2223
2224 tab_employee(lQ_SalaireDiffere) := '<Q_SalaireDiffere>' ||
2225 l_formatted_box || '</Q_SalaireDiffere>' || EOL;
2226 tab_xml_employee(lQ_SalaireDiffere) := l_formatted_box; --
2227 l_all_box_0 := FALSE;
2228 l_BoxQ := l_formatted_box;
2229 ELSE
2230 tab_employee(lQ_SalaireDiffere) := NULL;
2231 tab_xml_employee(lQ_SalaireDiffere) := NULL; --
2232 END IF;
2233 hr_utility.trace('tab_employee(lQ_SalaireDiffere) = ' ||
2234 tab_employee(lQ_SalaireDiffere));
2235
2236 hr_utility.trace('tab_xml_employee(lQ_SalaireDiffere) = ' ||
2237 tab_xml_employee(lQ_SalaireDiffere));
2238 -- (Box U)
2239 l_box := pay_ca_archive_utils.get_archive_value(
2240 l_arch_action_id,
2241 l_province,
2242 'JURISDICTION_CODE',
2243 'CAEOY_RL1_BOXU_PER_JD_YTD');
2244
2245 IF TO_NUMBER(l_box) > 9999999.99 THEN
2246
2247 l_status := 'Failed';
2248 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2249
2250 END IF;
2251
2252 IF l_box IS NOT NULL AND
2253 to_number(l_box) <> 0 THEN
2254
2255 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2256 INTO l_formatted_box
2257 FROM dual;
2258
2259 tab_employee(lU_RetraiteProgressive) := '<U_RetraiteProgressive>' ||
2260 l_formatted_box || '</U_RetraiteProgressive>' || EOL;
2261 tab_xml_employee(lU_RetraiteProgressive) := l_formatted_box; --
2262 l_all_box_0 := FALSE;
2263 l_BoxU := l_formatted_box;
2264 ELSE
2265 tab_employee(lU_RetraiteProgressive) := NULL;
2266 tab_xml_employee(lU_RetraiteProgressive) := NULL; --
2267 END IF;
2268 hr_utility.trace('tab_employee(lU_RetraiteProgressive) = ' ||
2269 tab_employee(lU_RetraiteProgressive));
2270
2271 hr_utility.trace('tab_xml_employee(lU_RetraiteProgressive) = ' ||
2272 tab_xml_employee(lU_RetraiteProgressive));
2273 -- (Box G)
2274 l_box := pay_ca_archive_utils.get_archive_value(
2275 l_arch_action_id,
2276 l_province,
2277 'JURISDICTION_CODE',
2278 --commented for bug 13360872
2279 --'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD');
2280 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD');
2281
2282 hr_utility.trace('l_box = ' || l_box);
2283 hr_utility.trace('l_boxA = ' || l_BoxA);
2284 hr_utility.trace('l_boxQ = ' || l_BoxQ);
2285 hr_utility.trace('l_boxU = ' || l_boxU);
2286
2287
2288 IF l_box IS NOT NULL THEN
2289
2290 IF TO_NUMBER(l_box) > 9999999.99 THEN
2291
2292 l_status := 'Failed';
2293 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2294
2295 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2296 INTO l_formatted_box
2297 FROM dual;
2298
2299 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
2300 l_formatted_box || '</G_SalaireAdmisRRQ>' || EOL;
2301 --tab_xml_employee(lG_SalaireAdmisRRQ) := l_formatted_box; --
2302
2303 ELSIF to_number(l_box) = 0 THEN
2304
2305 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
2306 '0.00</G_SalaireAdmisRRQ>' || EOL;
2307 --tab_xml_employee(lG_SalaireAdmisRRQ) := '0.00'; --
2308
2309 ELSIF to_number(l_box) <> (NVL(to_number(l_BoxA),0) +
2310 NVL(to_number(l_BoxQ),0) +
2311 NVL(to_number(l_BoxU),0)) THEN
2312
2313 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2314 INTO l_formatted_box
2315 FROM dual;
2316
2317 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
2318 l_formatted_box || '</G_SalaireAdmisRRQ>' || EOL;
2319
2320 --tab_xml_employee(lG_SalaireAdmisRRQ) := l_formatted_box; --
2321 l_all_box_0 := FALSE;
2322
2323 ELSIF to_number(l_box) = (NVL(to_number(l_BoxA),0) +
2324 NVL(to_number(l_BoxQ),0) +
2325 NVL(to_number(l_BoxU),0)) THEN
2326
2327 --commented for bug 10283923, in this case also Box G value should display
2328 --tab_employee(lG_SalaireAdmisRRQ) := NULL;
2329 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2330 INTO l_formatted_box
2331 FROM dual;
2332
2333 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
2334 l_formatted_box || '</G_SalaireAdmisRRQ>' || EOL;
2335
2336
2337 END IF;
2338
2339 ELSE
2340 --commented for bug 10283923
2341 --tab_employee(lG_SalaireAdmisRRQ) := NULL;
2342 tab_employee(lG_SalaireAdmisRRQ) := '<G_SalaireAdmisRRQ>' ||
2343 '0.00</G_SalaireAdmisRRQ>' || EOL;
2344 END IF;
2345
2346 tab_xml_employee(lG_SalaireAdmisRRQ) := NULL;
2347
2348
2349 hr_utility.trace('tab_xml_employee(lG_SalaireAdmisRRQ) = ' ||
2350 tab_xml_employee(lG_SalaireAdmisRRQ));
2351
2352
2353 -- (Box V)
2354 l_box := pay_ca_archive_utils.get_archive_value(
2355 l_arch_action_id,
2356 l_province,
2357 'JURISDICTION_CODE',
2358 'CAEOY_RL1_BOXV_PER_JD_YTD');
2359
2360 IF TO_NUMBER(l_box) > 9999999.99 THEN
2361 l_status := 'Failed';
2362 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2363 END IF;
2364
2365 IF l_box IS NOT NULL AND
2366 to_number(l_box) <> 0 THEN
2367
2368 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2369 INTO l_formatted_box
2370 FROM dual;
2371
2372 tab_employee(lV_NourritureLogement) := '<V_NourritureLogement>' ||
2373 l_formatted_box || '</V_NourritureLogement>' || EOL;
2374 tab_xml_employee(lV_NourritureLogement) := l_formatted_box; --
2375 l_all_box_0 := FALSE;
2376 ELSE
2377 tab_employee(lV_NourritureLogement) := NULL;
2378 tab_xml_employee(lV_NourritureLogement) := NULL; --
2379 END IF;
2380
2381 hr_utility.trace('tab_employee(lV_NourritureLogement) = ' ||
2382 tab_employee(lV_NourritureLogement));
2383
2384 hr_utility.trace('tab_xml_employee(lV_NourritureLogement) = ' ||
2385 tab_xml_employee(lV_NourritureLogement));
2386 -- (Box W)
2387 l_box := pay_ca_archive_utils.get_archive_value(
2388 l_arch_action_id,
2389 l_province,
2390 'JURISDICTION_CODE',
2391 'CAEOY_RL1_BOXW_PER_JD_YTD');
2392
2393 IF TO_NUMBER(l_box) > 9999999.99 THEN
2394 l_status := 'Failed';
2395 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2396 END IF;
2397
2398 IF l_box IS NOT NULL AND
2399 to_number(l_box) <> 0 THEN
2400
2401 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2402 INTO l_formatted_box
2403 FROM dual;
2404
2405 tab_employee(lW_Vehicule) := '<W_Vehicule>' ||
2406 l_formatted_box || '</W_Vehicule>' || EOL;
2407 tab_xml_employee(lW_Vehicule) := l_formatted_box; --
2408 l_all_box_0 := FALSE;
2409 ELSE
2410 tab_employee(lW_Vehicule) := NULL;
2411 tab_xml_employee(lW_Vehicule) := NULL; --
2412 END IF;
2413 hr_utility.trace('tab_employee(lW_Vehicule) = ' ||
2414 tab_employee(lW_Vehicule));
2415
2416 hr_utility.trace('tab_xml_employee(lW_Vehicule) = ' ||
2417 tab_xml_employee(lW_Vehicule));
2418
2419 --(BOX H)
2420 l_box := pay_ca_archive_utils.get_archive_value(
2421 l_arch_action_id,
2422 l_province,
2423 'JURISDICTION_CODE',
2424 'CAEOY_PPIP_EE_WITHHELD_PER_JD_YTD');
2425 IF TO_NUMBER(l_box) > 9999999.99 THEN
2426 l_status := 'Failed';
2427 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2428 END IF;
2429
2430 IF l_box IS NOT NULL AND
2431 to_number(l_box) <> 0 THEN
2432
2433 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2434 INTO l_formatted_box
2435 FROM dual;
2436
2437 tab_employee(lH_CotisationRQAP) := '<H_CotisationRQAP>' ||
2438 l_formatted_box || '</H_CotisationRQAP>' || EOL;
2439 tab_xml_employee(lH_CotisationRQAP) := l_formatted_box; --
2440 l_all_box_0 := FALSE;
2441 ELSE
2442 tab_employee(lH_CotisationRQAP) := NULL;
2443 tab_xml_employee(lH_CotisationRQAP) := NULL; --
2444 END IF;
2445 hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
2446 tab_employee(lH_CotisationRQAP));
2447
2448 hr_utility.trace('tab_xml_employee(lH_CotisationRQAP) = ' ||
2449 tab_xml_employee(lH_CotisationRQAP));
2450
2451 --(BOX I)
2452 l_box := pay_ca_archive_utils.get_archive_value(
2453 l_arch_action_id,
2454 l_province,
2455 'JURISDICTION_CODE',
2456 -- commented for bug 6623199.
2457 -- 'CAEOY_PPIP_REDUCED_SUBJECT_PER_JD_YTD');
2458 'CAEOY_PPIP_EE_TAXABLE_PER_JD_YTD');
2459 IF TO_NUMBER(l_box) > 9999999.99 THEN
2460 l_status := 'Failed';
2461 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2462 END IF;
2463
2464 IF l_box IS NOT NULL AND
2465 to_number(l_box) <> 0 THEN
2466
2467 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2468 INTO l_formatted_box
2469 FROM dual;
2470
2471 tab_employee(lI_SalaireAdmisRQAP) := '<I_SalaireAdmisRQAP>' ||
2472 l_formatted_box || '</I_SalaireAdmisRQAP>' || EOL;
2473 tab_xml_employee(lI_SalaireAdmisRQAP) := l_formatted_box; --
2474 l_all_box_0 := FALSE;
2475 ELSE
2476 tab_employee(lI_SalaireAdmisRQAP) := '<I_SalaireAdmisRQAP>' ||
2477 '0.00' || '</I_SalaireAdmisRQAP>' || EOL;
2478 tab_xml_employee(lI_SalaireAdmisRQAP) := '0.00';--
2479 END IF;
2480 hr_utility.trace('tab_employee(lI_SalaireAdmisRQAP) = ' ||
2481 tab_employee(lI_SalaireAdmisRQAP));
2482
2483 hr_utility.trace('tab_xml_employee(lI_SalaireAdmisRQAP) = ' ||
2484 tab_xml_employee(lI_SalaireAdmisRQAP));
2485
2486 -- (Box J)
2487 l_box := pay_ca_archive_utils.get_archive_value(
2488 l_arch_action_id,
2489 l_province,
2490 'JURISDICTION_CODE',
2491 'CAEOY_RL1_BOXJ_PER_JD_YTD');
2492
2493 IF TO_NUMBER(l_box) > 9999999.99 THEN
2494 l_status := 'Failed';
2495 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2496 END IF;
2497
2498 IF l_box IS NOT NULL AND
2499 to_number(l_box) <> 0 THEN
2500
2501 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2502 INTO l_formatted_box
2503 FROM dual;
2504
2505 tab_employee(lJ_RegimeAssMaladie) := '<J_RegimeAssMaladie>' ||
2506 l_formatted_box || '</J_RegimeAssMaladie>' || EOL;
2507 tab_xml_employee(lJ_RegimeAssMaladie) := l_formatted_box; --
2508 l_all_box_0 := FALSE;
2509 ELSE
2510 tab_employee(lJ_RegimeAssMaladie) := NULL;
2511 tab_xml_employee(lJ_RegimeAssMaladie) := NULL; --
2512 END IF;
2513 hr_utility.trace('tab_employee(lJ_RegimeAssMaladie) = ' ||
2514 tab_employee(lJ_RegimeAssMaladie));
2515
2516 hr_utility.trace('tab_xml_employee(lJ_RegimeAssMaladie) = ' ||
2517 tab_xml_employee(lJ_RegimeAssMaladie));
2518
2519 -- (Box K)
2520 l_box := pay_ca_archive_utils.get_archive_value(
2521 l_arch_action_id,
2522 l_province,
2523 'JURISDICTION_CODE',
2524 'CAEOY_RL1_BOXK_PER_JD_YTD');
2525
2526 IF TO_NUMBER(l_box) > 9999999.99 THEN
2527 l_status := 'Failed';
2528 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2529 END IF;
2530
2531 IF l_box IS NOT NULL AND
2532 to_number(l_box) <> 0 THEN
2533
2534 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2535 INTO l_formatted_box
2536 FROM dual;
2537
2538 tab_employee(lK_Voyage) := '<K_Voyage>' ||
2539 l_formatted_box || '</K_Voyage>' || EOL;
2540 tab_xml_employee(lK_Voyage) := l_formatted_box; --
2541 l_all_box_0 := FALSE;
2542 ELSE
2543 tab_employee(lK_Voyage) := NULL;
2544 tab_xml_employee(lK_Voyage) := NULL; --
2545 END IF;
2546 hr_utility.trace('tab_employee(lK_Voyage) = ' ||
2547 tab_employee(lK_Voyage));
2548
2549 hr_utility.trace('tab_xml_employee(lK_Voyage) = ' ||
2550 tab_xml_employee(lK_Voyage));
2551 -- (Box L)
2552 l_box := pay_ca_archive_utils.get_archive_value(
2553 l_arch_action_id,
2554 l_province,
2555 'JURISDICTION_CODE',
2556 'CAEOY_RL1_BOXL_PER_JD_YTD');
2557
2558 IF TO_NUMBER(l_box) > 9999999.99 THEN
2559 l_status := 'Failed';
2560 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2561 END IF;
2562
2563 IF l_box IS NOT NULL AND
2564 to_number(l_box) <> 0 THEN
2565
2566 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2567 INTO l_formatted_box
2568 FROM dual;
2569
2570 tab_employee(lL_AutreAvantage) := '<L_AutreAvantage>' ||
2571 l_formatted_box || '</L_AutreAvantage>' || EOL;
2572 tab_xml_employee(lL_AutreAvantage) := l_formatted_box; --
2573 l_all_box_0 := FALSE;
2574 ELSE
2575 tab_employee(lL_AutreAvantage) := NULL;
2576 tab_xml_employee(lL_AutreAvantage) := NULL; --
2577 END IF;
2578 hr_utility.trace('tab_employee(lL_AutreAvantage) = ' ||
2579 tab_employee(lL_AutreAvantage));
2580
2581 hr_utility.trace('tab_xml_employee(lL_AutreAvantage) = ' ||
2582 tab_xml_employee(lL_AutreAvantage));
2583 -- (Box M)
2584 l_box := pay_ca_archive_utils.get_archive_value(
2585 l_arch_action_id,
2586 l_province,
2587 'JURISDICTION_CODE',
2588 'CAEOY_RL1_BOXM_PER_JD_YTD');
2589
2590 IF TO_NUMBER(l_box) > 9999999.99 THEN
2591 l_status := 'Failed';
2592 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2593 END IF;
2594
2595 IF l_box IS NOT NULL AND
2596 to_number(l_box) <> 0 THEN
2597
2598 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2599 INTO l_formatted_box
2600 FROM dual;
2601
2602 tab_employee(lM_Commission) := '<M_Commission>' ||
2603 l_formatted_box || '</M_Commission>' || EOL;
2604 tab_xml_employee(lM_Commission) := l_formatted_box; --
2605 l_all_box_0 := FALSE;
2606 ELSE
2607 tab_employee(lM_Commission) := NULL;
2608 tab_xml_employee(lM_Commission) := NULL; --
2609 END IF;
2610 hr_utility.trace('tab_employee(lM_Commission) = ' ||
2611 tab_employee(lM_Commission));
2612
2613 hr_utility.trace('tab_xml_employee(lM_Commission) = ' ||
2614 tab_xml_employee(lM_Commission));
2615 -- (Box N)
2616 l_box := pay_ca_archive_utils.get_archive_value(
2617 l_arch_action_id,
2618 l_province,
2619 'JURISDICTION_CODE',
2620 'CAEOY_RL1_BOXN_PER_JD_YTD');
2621
2622 IF TO_NUMBER(l_box) > 9999999.99 THEN
2623 l_status := 'Failed';
2624 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2625 END IF;
2626
2627 IF l_box IS NOT NULL AND
2628 to_number(l_box) <> 0 THEN
2629
2630 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2631 INTO l_formatted_box
2632 FROM dual;
2633
2634 tab_employee(lN_DonBienfaisance) := '<N_DonBienfaisance>' ||
2635 l_formatted_box || '</N_DonBienfaisance>' || EOL;
2636 tab_xml_employee(lN_DonBienfaisance) := l_formatted_box; --
2637 l_all_box_0 := FALSE;
2638 ELSE
2639 tab_employee(lN_DonBienfaisance) := NULL;
2640 tab_xml_employee(lN_DonBienfaisance) := NULL; --
2641 END IF;
2642
2643 hr_utility.trace('tab_employee(lN_DonBienfaisance) = ' ||
2644 tab_employee(lN_DonBienfaisance));
2645
2646 hr_utility.trace('tab_xml_employee(lN_DonBienfaisance) = ' ||
2647 tab_xml_employee(lN_DonBienfaisance));
2648 -- Summ (Box O)
2649 l_box := pay_ca_archive_utils.get_archive_value(
2650 l_arch_action_id,
2651 l_province,
2652 'JURISDICTION_CODE',
2653 'CAEOY_RL1_BOXO_PER_JD_YTD');
2654
2655 IF TO_NUMBER(l_box) > 9999999.99 THEN
2656 l_status := 'Failed';
2657 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2658 END IF;
2659
2660 IF l_box IS NOT NULL AND
2661 to_number(l_box) <> 0 THEN
2662
2663 l_boxO := l_box;
2664 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2665 INTO l_formatted_box
2666 FROM dual;
2667
2668 tab_employee(lO_AutreRevenu) := '<MontantCaseO>' ||
2669 l_formatted_box || '</MontantCaseO>' || EOL;
2670 tab_xml_employee(lO_AutreRevenu) := l_formatted_box; --
2671 l_all_box_0 := FALSE;
2672 ELSE
2673 tab_employee(lO_AutreRevenu) := NULL;
2674 tab_xml_employee(lO_AutreRevenu) := NULL; --
2675 END IF;
2676 hr_utility.trace('tab_employee(lO_AutreRevenu) = ' ||
2677 tab_employee(lO_AutreRevenu));
2678
2679 hr_utility.trace('tab_xml_employee(lO_AutreRevenu) = ' ||
2680 tab_xml_employee(lO_AutreRevenu));
2681
2682 -- SourceCasem
2683
2684 IF to_number(l_boxO) <> 0 THEN
2685 l_box := pay_ca_archive_utils.get_archive_value(
2686 l_arch_action_id,
2687 l_province,
2688 'JURISDICTION_CODE',
2689 'CAEOY_RL1_BOXO_CODE_PER_JD_YTD');
2690 tab_employee(l_SourceCase) := '<SourceCaseO>' ||
2691 l_box || '</SourceCaseO>' || EOL;
2692 tab_xml_employee(l_SourceCase) := l_box; --
2693 ELSE
2694 tab_employee(l_SourceCase) := NULL;
2695 tab_xml_employee(l_SourceCase) := NULL; --
2696 END IF;
2697 hr_utility.trace('tab_employee(l_SourceCase) = ' ||
2698 tab_employee(l_SourceCase));
2699
2700 hr_utility.trace('tab_xml_employee(l_SourceCase) = ' ||
2701 tab_xml_employee(l_SourceCase));
2702 -- (Box P)
2703 if tab_employee(lO_AutreRevenu) is not null
2704 and tab_employee(l_SourceCase)is not null then
2705 l_O_AutreRevenu := '<O_AutreRevenu>'||EOL
2706 ||tab_employee(lO_AutreRevenu)||EOL
2707 ||tab_employee(l_SourceCase)||EOL
2708 ||'</O_AutreRevenu>'||EOL;
2709 else
2710 l_O_AutreRevenu := null;
2711 end if;
2712 l_box := pay_ca_archive_utils.get_archive_value(
2713 l_arch_action_id,
2714 l_province,
2715 'JURISDICTION_CODE',
2716 'CAEOY_RL1_BOXP_PER_JD_YTD');
2717
2718 IF TO_NUMBER(l_box) > 9999999.99 THEN
2719 l_status := 'Failed';
2720 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2721 END IF;
2722
2723 IF l_box IS NOT NULL AND
2724 to_number(l_box) <> 0 THEN
2725
2726 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2727 INTO l_formatted_box
2728 FROM dual;
2729
2730 tab_employee(lP_RegimeAssInterEntr) := '<P_RegimeAssInterEntr>' ||
2731 l_formatted_box || '</P_RegimeAssInterEntr>' || EOL;
2732 tab_xml_employee(lP_RegimeAssInterEntr) := l_formatted_box; --
2733 l_all_box_0 := FALSE;
2734 ELSE
2735 tab_employee(lP_RegimeAssInterEntr) := NULL;
2736 tab_xml_employee(lP_RegimeAssInterEntr) := NULL; --
2737 END IF;
2738 hr_utility.trace('tab_employee(lP_RegimeAssInterEntr) = ' ||
2739 tab_employee(lP_RegimeAssInterEntr));
2740
2741 hr_utility.trace('tab_xml_employee(lP_RegimeAssInterEntr) = ' ||
2742 tab_xml_employee(lP_RegimeAssInterEntr));
2743 -- (Box R)
2744 l_box := pay_ca_archive_utils.get_archive_value(
2745 l_arch_action_id,
2746 l_province,
2747 'JURISDICTION_CODE',
2748 'CAEOY_RL1_BOXR_PER_JD_YTD');
2749
2750 IF TO_NUMBER(l_box) > 9999999.99 THEN
2751 l_status := 'Failed';
2752 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2753 END IF;
2754
2755 IF l_box IS NOT NULL AND
2756 to_number(l_box) <> 0 THEN
2757
2758 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2759 INTO l_formatted_box
2760 FROM dual;
2761
2762 tab_employee(lR_RevenuIndien) := '<R_RevenuIndien>' ||
2763 l_formatted_box || '</R_RevenuIndien>' || EOL;
2764 tab_xml_employee(lR_RevenuIndien) := l_formatted_box; --
2765 l_all_box_0 := FALSE;
2766 ELSE
2767 tab_employee(lR_RevenuIndien) := NULL;
2768 tab_xml_employee(lR_RevenuIndien) := NULL; --
2769 END IF;
2770 hr_utility.trace('tab_employee(lR_RevenuIndien) = ' ||
2771 tab_employee(lR_RevenuIndien));
2772
2773 hr_utility.trace('tab_xml_employee(lR_RevenuIndien) = ' ||
2774 tab_xml_employee(lR_RevenuIndien));
2775 -- (Box S)
2776 l_box := pay_ca_archive_utils.get_archive_value(
2777 l_arch_action_id,
2778 l_province,
2779 'JURISDICTION_CODE',
2780 'CAEOY_RL1_BOXS_PER_JD_YTD');
2781
2782 IF TO_NUMBER(l_box) > 9999999.99 THEN
2783 l_status := 'Failed';
2784 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2785 END IF;
2786
2787 IF l_box IS NOT NULL AND
2788 to_number(l_box) <> 0 THEN
2789
2790 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2791 INTO l_formatted_box
2792 FROM dual;
2793
2794 tab_employee(lS_PourboireRecu) := '<S_PourboireRecu>' ||
2795 l_formatted_box || '</S_PourboireRecu>' || EOL;
2796 tab_xml_employee(lS_PourboireRecu) := l_formatted_box; --
2797 l_all_box_0 := FALSE;
2798 ELSE
2799 tab_employee(lS_PourboireRecu) := NULL;
2800 tab_xml_employee(lS_PourboireRecu) := NULL; --
2801 END IF;
2802 hr_utility.trace('tab_employee(lS_PourboireRecu) = ' ||
2803 tab_employee(lS_PourboireRecu));
2804
2805 hr_utility.trace('tab_xml_employee(lS_PourboireRecu) = ' ||
2806 tab_xml_employee(lS_PourboireRecu));
2807 -- (Box T)
2808 l_box := pay_ca_archive_utils.get_archive_value(
2809 l_arch_action_id,
2810 l_province,
2811 'JURISDICTION_CODE',
2812 'CAEOY_RL1_BOXT_PER_JD_YTD');
2813
2814 IF TO_NUMBER(l_box) > 9999999.99 THEN
2815 l_status := 'Failed';
2816 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2817 END IF;
2818
2819 IF l_box IS NOT NULL AND
2820 to_number(l_box) <> 0 THEN
2821
2822 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2823 INTO l_formatted_box
2824 FROM dual;
2825
2826 tab_employee(lT_PourboireAttribue) := '<T_PourboireAttribue>' ||
2827 l_formatted_box || '</T_PourboireAttribue>' || EOL;
2828 tab_xml_employee(lT_PourboireAttribue) := l_formatted_box; --
2829 l_all_box_0 := FALSE;
2830 ELSE
2831 tab_employee(lT_PourboireAttribue) := NULL;
2832 tab_xml_employee(lT_PourboireAttribue) := NULL; --
2833 END IF;
2834 hr_utility.trace('tab_employee(lT_PourboireAttribue) = ' ||
2835 tab_employee(lT_PourboireAttribue));
2836 hr_utility.trace('tab_xml_employee(lT_PourboireAttribue) = ' ||
2837 tab_xml_employee(lT_PourboireAttribue));
2838 -- (Box ContisationRPC)
2839 l_box := pay_ca_archive_utils.get_archive_value(
2840 l_arch_action_id,
2841 --l_province,
2842 --'JURISDICTION_CODE',
2843 'CAEOY_CPP_EE_WITHHELD_PER_YTD');
2844
2845 IF TO_NUMBER(l_box) > 9999999.99 THEN
2846 l_status := 'Failed';
2847 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2848 END IF;
2849
2850 IF l_box IS NOT NULL AND
2851 to_number(l_box) <> 0
2852 and l_taxation_year = '2011' THEN /*Bug 14701466 here*/
2853
2854 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2855 INTO l_formatted_box
2856 FROM dual;
2857
2858 tab_employee(l_ContisationRPC) := '<CotisationRPC>' ||
2859 l_formatted_box || '</CotisationRPC>' || EOL;
2860 l_all_box_0 := FALSE;
2861 ELSE
2862 tab_employee(l_ContisationRPC) := NULL;
2863 END IF;
2864 hr_utility.trace('tab_employee(lR_ContisationRPC) = ' ||
2865 tab_employee(l_ContisationRPC));
2866
2867 /* Changes for bug 13567075 starts here -- sbachu*/
2868
2869 l_box := pay_ca_archive_utils.get_archive_value(
2870 l_arch_action_id,
2871 l_province,
2872 'JURISDICTION_CODE',
2873 'CAEOY_RL1_FURTHER_INFO_AMOUNT_201_AMT_PER_JD_YTD');
2874
2875 IF TO_NUMBER(l_box) > 9999999.99 THEN
2876 l_status := 'Failed';
2877 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2878 END IF;
2879
2880 IF l_box IS NOT NULL AND
2881 to_number(l_box) <> 0
2882 and l_taxation_year = '2011' THEN /*Bug 14701466 here*/
2883
2884 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2885 INTO l_formatted_box
2886 FROM dual;
2887
2888 tab_employee(l_FraisGarde) := '<FraisGarde>' ||
2889 l_formatted_box || '</FraisGarde>' || EOL;
2890 l_all_box_0 := FALSE;
2891 ELSE
2892 tab_employee(l_FraisGarde) := NULL;
2893 END IF;
2894 hr_utility.trace('tab_employee(l_FraisGarde) = ' ||
2895 tab_employee(l_FraisGarde));
2896
2897 l_box := fnd_number.number_to_canonical(fnd_number.canonical_to_number(pay_ca_archive_utils.get_archive_value(
2898 l_arch_action_id,
2899 l_province,
2900 'JURISDICTION_CODE',
2901 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A7_AMT_PER_JD_YTD')) +
2902 fnd_number.canonical_to_number(pay_ca_archive_utils.get_archive_value(
2903 l_arch_action_id,
2904 l_province,
2905 'JURISDICTION_CODE',
2906 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A8_AMT_PER_JD_YTD')));
2907
2908 IF TO_NUMBER(l_box) > 9999999.99 THEN
2909 l_status := 'Failed';
2910 l_msg_code := 'AMT_GREATER_THAN_RANGE';
2911 END IF;
2912
2913 IF l_box IS NOT NULL AND
2914 to_number(l_box) <> 0
2915 and l_taxation_year = '2011' THEN /*Bug 14701466 here*/
2916
2917 SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
2918 INTO l_formatted_box
2919 FROM dual;
2920
2921 tab_employee(l_DeductionForce) := '<DeductionForce>' ||
2922 l_formatted_box || '</DeductionForce>' || EOL;
2923 l_all_box_0 := FALSE;
2924 ELSE
2925 tab_employee(l_DeductionForce) := NULL;
2926 END IF;
2927 hr_utility.trace('tab_employee(l_DeductionForce) = ' ||
2928 tab_employee(l_DeductionForce));
2929
2930 /*Changes for bug 13567075 ends here --sbachu*/
2931
2932 OPEN cur_ppa(l_payroll_actid);
2933 FETCH cur_ppa
2934 INTO l_legislative_parameters;
2935 CLOSE cur_ppa;
2936
2937 l_transmitter_org_id := pay_ca_rl1_mag.get_parameter('TRANSMITTER_PRE',
2938 l_legislative_parameters);
2939 -- Original Slip Number
2940 /*Bug 13564765 starts here*/
2941 lv_org_rl1_slip_no := NULL;
2942 l_k := 0;
2943 OPEN c_original_slipno(l_person_id,l_taxation_year,l_transmitter_org_id);
2944 loop
2945 FETCH c_original_slipno INTO l_original_slpno;
2946 exit when c_original_slipno%NOTFOUND;
2947 if l_k = 0 then
2948 lv_org_rl1_slip_no := l_original_slpno;
2949 l_k := 1;
2950 else
2951 lv_org_rl1_slip_no := lv_org_rl1_slip_no || '|'||l_original_slpno;
2952 end if;
2953 END LOOP;
2954 CLOSE c_original_slipno;
2955
2956 if lv_org_rl1_slip_no is NULL THEN
2957 tab_employee(l_origi_slipno) := NULL;
2958 l_status := 'Failed';
2959 l_msg_code := 'MISSING_SLIP_NO';
2960 ELSE
2961 l_original_slpno := pay_ca_rl1_mag.get_slip_seq_no('|',lv_org_rl1_slip_no,1);
2962 tab_employee(l_origi_slipno) := '<NoReleveDerniereTrans>' ||l_original_slpno|| '</NoReleveDerniereTrans>'||EOL;
2963 END IF;
2964 /*Bug 13564765 ends here*/
2965 -- Negative Balance Exists
2966 hr_utility.trace('finding if neg bal exists');
2967 l_box := pay_ca_archive_utils.get_archive_value(
2968 l_arch_action_id,
2969 l_province,
2970 'JURISDICTION_CODE',
2971 'CAEOY_RL1_NEGATIVE_BALANCE_EXISTS');
2972
2973 IF l_box = 'Y' THEN
2974 l_status := 'Failed';
2975 l_msg_code := 'NEG';
2976 l_neg_bal_exists := TRUE;
2977 hr_utility.trace('neg bal exists');
2978 END IF;
2979
2980 IF to_number(l_taxation_year) < 2011 AND l_all_box_0 THEN /*Bug 13633422*/
2981 l_status := 'Failed';
2982 l_msg_code := 'ALL_BOXES_ZERO';
2983 END IF;
2984 /*Bug 14701466 starts here*/
2985 store_further_information (l_arch_action_id,l_asg_id);
2986
2987 for l_j in 1..g_further_info_list.count
2988 loop
2989 get_further_information (l_j,
2990 l_code,
2991 l_amount);
2992 l_further_code(l_j):=l_code;
2993 l_further_amount(l_j):=l_amount;
2994 end loop;
2995
2996 IF l_status = 'Failed' THEN
2997 l_other_details := NULL;
2998 for l_i in 1..g_further_info_list.count
2999 loop
3000 if l_further_amount(l_i) is not null and
3001 l_further_amount(l_i) <> '0' and
3002 l_further_code(l_i) <> 'B-1' then
3003 SELECT ltrim(rtrim(to_char(to_number(l_further_amount(l_i)),l_format_mask)))
3004 INTO l_formatted_box
3005 FROM dual;
3006 select decode(l_further_code(l_i),'201','F_1','211','F_2','235','F_3',replace(l_further_code(l_i),'-','_'))
3007 into l_formatted_code
3008 from dual;
3009 l_other_details:=l_other_details||'<'||l_formatted_code||'>'
3010 ||l_formatted_box||'</'||l_formatted_code||'>'||EOL;
3011 end if;
3012 end loop;
3013 /*Bug 14701466 ends here*/
3014 OPEN cur_get_meaning(l_msg_code);
3015 FETCH cur_get_meaning
3016 INTO l_meaning;
3017 CLOSE cur_get_meaning;
3018 hr_utility.trace('l_meaning ='|| l_meaning);
3019 tab_employee(lErrorDetails) := '<ErrorDetails>' ||
3020 l_meaning || '</ErrorDetails>' || EOL;
3021 l_other_details := l_other_details||tab_employee(lErrorDetails);
3022 END IF;
3023 hr_utility.trace('l_other_details ='||l_other_details);
3024 hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3025 tab_employee(lH_CotisationRQAP));
3026 hr_utility.trace('l_rep_type ='|| l_rep_type);
3027
3028 hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3029 tab_employee(lH_CotisationRQAP));
3030 /*bug 14701466 starts here sbachu*/
3031 lv_ident_addr_string := '<Identification>'|| EOL
3032 || '<Employe>' || EOL ||
3033 tab_employee(lNAS) ||
3034 tab_employee(lNo) ||
3035 tab_employee(lNomFamille) ||
3036 tab_employee(lPrenom) ||
3037 tab_employee(lInitiale) || '</Employe>' || EOL
3038 ||'</Identification>' || EOL
3039 || l_addr_begin_tag || EOL ||
3040 tab_employee(lLigne1) ||
3041 tab_employee(lLigne2) ||
3042 tab_employee(lVille) ||
3043 tab_employee(lProvince) ||
3044 tab_employee(lCodePostal) ||
3045 l_addr_end_tag || EOL ;
3046 lv_montants_string := '<Montants>' || EOL ||
3047 tab_employee(lA_RevenuEmploi) ||
3048 tab_employee(lB_CotisationRRQ) ||
3049 tab_employee(lC_CotisationAssEmploi) ||
3050 tab_employee(lD_CotisationRPA) ||
3051 tab_employee(lE_ImpotQue) ||
3052 tab_employee(lF_CotisationSyndicale) ||
3053 tab_employee(lG_SalaireAdmisRRQ) ||
3054 tab_employee(lH_CotisationRQAP)||
3055 tab_employee(lI_SalaireAdmisRQAP)||
3056 tab_employee(lJ_RegimeAssMaladie) ||
3057 tab_employee(lK_Voyage) ||
3058 tab_employee(lL_AutreAvantage) ||
3059 tab_employee(lM_Commission) ||
3060 tab_employee(lN_DonBienfaisance) ||
3061 l_O_AutreRevenu||
3062 tab_employee(lP_RegimeAssInterEntr) ||
3063 tab_employee(lQ_SalaireDiffere) ||
3064 tab_employee(lR_RevenuIndien) ||
3065 tab_employee(lS_PourboireRecu) ||
3066 tab_employee(lT_PourboireAttribue) ||
3067 tab_employee(lU_RetraiteProgressive) ||
3068 tab_employee(lV_NourritureLogement) ||
3069 tab_employee(lW_Vehicule) ||
3070 tab_employee(l_ContisationRPC) ||
3071 tab_employee(l_FraisGarde) ||
3072 tab_employee(l_DeductionForce) ||
3073 l_other_details ||
3074 '</Montants>' || EOL ;
3075 IF l_status = 'Failed' THEN
3076 l_final_xml_string := '<' || l_status || '>' || EOL ||
3077 '<A>' || EOL ||
3078 tab_employee(lAnnee) ||
3079 '<NoReleve>' || lv_rl1_slip_no || '</NoReleve>' || EOL
3080 || lv_ident_addr_string || lv_montants_string ||
3081 '<NoReleveDerniereTrans>' ||lv_org_rl1_slip_no|| '</NoReleveDerniereTrans>'||EOL|| '</A>' || EOL ||
3082 '</' || l_status || '>' ;
3083 ELSE
3084 l_k := 0;
3085 l_m := 0;
3086 l_further_info_string := '';
3087 l_final_xml_string := '';
3088 ln_no_fi_per_slip := 4;
3089
3090 for l_i in 1..g_further_info_list.count
3091 loop
3092 if l_further_amount(l_i) is not null
3093 and l_further_amount(l_i) <> '0'
3094 and (NVL(tab_xml_employee(l_SourceCase),'RZ') = 'RZ' or l_further_code(l_i) not like 'RZ%') then
3095 l_k := l_k + 1;
3096 SELECT ltrim(rtrim(to_char(to_number(l_further_amount(l_i)),l_format_mask)))
3097 INTO l_formatted_box
3098 FROM dual;
3099 l_further_info_string:=l_further_info_string||'<CaseRensCompl>'||EOL
3100 ||'<CodeRensCompl>'||l_further_code(l_i)||'</CodeRensCompl>'||EOL
3101 ||'<DonneeRensCompl>'||l_formatted_box||'</DonneeRensCompl>'||EOL
3102 ||'</CaseRensCompl>';
3103
3104 if (mod(l_k,ln_no_fi_per_slip) = 0) then
3105
3106 if l_m = 0 then
3107 l_final_xml_string := '<' || l_status || '>' || EOL ||
3108 '<A>' || EOL ||
3109 tab_employee(lAnnee) ||
3110 '<NoReleve>' || pay_ca_rl1_mag.get_slip_seq_no('|',lv_rl1_slip_no,1) || '</NoReleve>' || EOL
3111 || lv_ident_addr_string || lv_montants_string || l_further_info_string ||
3112 '<NoReleveDerniereTrans>' ||pay_ca_rl1_mag.get_slip_seq_no('|',lv_org_rl1_slip_no,1)|| '</NoReleveDerniereTrans>'||EOL|| '</A>' || EOL ||
3113 '</' || l_status || '>' ;
3114
3115 else
3116 l_final_xml_string := l_final_xml_string || EOL ||
3117 '<' || l_status || '>' || EOL ||
3118 '<A>' || EOL ||
3119 tab_employee(lAnnee) ||
3120 '<NoReleve>' || pay_ca_rl1_mag.get_slip_seq_no('|',lv_rl1_slip_no,trunc(l_k/ln_no_fi_per_slip)) || '</NoReleve>' || EOL
3121 || lv_ident_addr_string ||
3122 '<Montants>' || EOL ||
3123 '</Montants>' || EOL ||
3124 l_further_info_string ||
3125 '<NoReleveDerniereTrans>' ||pay_ca_rl1_mag.get_slip_seq_no('|',lv_org_rl1_slip_no,trunc(l_k/ln_no_fi_per_slip))|| '</NoReleveDerniereTrans>'||EOL|| '</A>' || EOL ||
3126 '</' || l_status || '>' ;
3127 end if;
3128 l_m := 1;
3129 l_further_info_string :='';
3130 end if;
3131 end if;
3132 end loop;
3133
3134 if (mod(l_k,ln_no_fi_per_slip) <> 0) or l_k = 0 then
3135 if l_m = 0 then
3136 l_final_xml_string := '<' || l_status || '>' || EOL ||
3137 '<A>' || EOL ||
3138 tab_employee(lAnnee) ||
3139 '<NoReleve>' || pay_ca_rl1_mag.get_slip_seq_no('|',lv_rl1_slip_no,1) || '</NoReleve>' || EOL
3140 || lv_ident_addr_string || lv_montants_string || l_further_info_string ||
3141 '<NoReleveDerniereTrans>' ||pay_ca_rl1_mag.get_slip_seq_no('|',lv_org_rl1_slip_no,1)|| '</NoReleveDerniereTrans>'||EOL|| '</A>' || EOL ||
3142 '</' || l_status || '>' ;
3143
3144 else
3145 l_final_xml_string := l_final_xml_string || EOL ||
3146 '<' || l_status || '>' || EOL ||
3147 '<A>' || EOL ||
3148 tab_employee(lAnnee) ||
3149 '<NoReleve>' || pay_ca_rl1_mag.get_slip_seq_no('|',lv_rl1_slip_no,trunc(l_k/ln_no_fi_per_slip)+1) || '</NoReleve>' || EOL
3150 || lv_ident_addr_string ||
3151 '<Montants>' || EOL ||
3152 '</Montants>' || EOL ||
3153 l_further_info_string ||
3154 '<NoReleveDerniereTrans>' ||pay_ca_rl1_mag.get_slip_seq_no('|',lv_org_rl1_slip_no,trunc(l_k/ln_no_fi_per_slip)+1)|| '</NoReleveDerniereTrans>'||EOL|| '</A>' || EOL ||
3155 '</' || l_status || '>' ;
3156
3157 end if;
3158 end if;
3159 END IF; /* if l_status = 'Failed' */
3160 /*bug 14701466 ends here sbachu*/
3161 hr_utility.trace('rl1_xml_employee: l_final_xml_string = ' || l_final_xml_string);
3162 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3163 end if; --if cur_parameters%found
3164 CLOSE cur_parameters;
3165 hr_utility.trace('end of xml_employee_record');
3166
3167 END;
3168 END xml_employee_record;
3169
3170 PROCEDURE xml_employer_start IS
3171 BEGIN
3172
3173 DECLARE
3174
3175 l_final_xml CLOB;
3176 l_final_xml_string VARCHAR2(32000);
3177 l_is_temp_final_xml VARCHAR2(2);
3178
3179 BEGIN
3180
3181 l_final_xml_string := '<Groupe01>';
3182 hr_utility.trace('Inside the Employer Start record proc');
3183 hr_utility.trace('rl1_xml_employee_start: l_final_xml_string = ' || l_final_xml_string);
3184 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3185
3186
3187 END;
3188 END xml_employer_start;
3189
3190 PROCEDURE xml_employer_record IS
3191 BEGIN
3192 DECLARE
3193
3194 l_final_xml CLOB;
3195 l_final_xml_string VARCHAR2(32000);
3196 l_is_temp_final_xml VARCHAR2(2);
3197
3198 TYPE employer_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
3199
3200 tab_employer employer_info;
3201
3202 lAnnee NUMBER;
3203 lNbReleves NUMBER;
3204 lNoId NUMBER;
3205 lTypeDossier NUMBER;
3206 lNoDossier NUMBER;
3207 lNEQ NUMBER;
3208 lNom1 NUMBER;
3209 lNom2 NUMBER;
3210 lLigne1 NUMBER;
3211 lLigne2 NUMBER;
3212 lVille NUMBER;
3213 lProvince NUMBER;
3214 lCodePostal NUMBER;
3215
3216 l_taxation_year varchar2(4);
3217 l_context1 ff_archive_items.context1%TYPE;
3218 EOL varchar2(5);
3219 l_employer_name varchar2(100);
3220 l_quebec_bn varchar2(20);
3221 l_address_line hr_locations.address_line_1%TYPE;
3222 l_address_begin_tag varchar2(10);
3223 l_address_end_tag varchar2(10);
3224
3225
3226 BEGIN
3227
3228 hr_utility.trace('XML Employer');
3229 hr_utility.trace('Inside the Employer Main Record proc');
3230 SELECT
3231 fnd_global.local_chr(13) || fnd_global.local_chr(10)
3232 INTO EOL
3233 FROM dual;
3234
3235 lAnnee := 1;
3236 lNbReleves := 2;
3237 lNoId := 3;
3238 lTypeDossier := 4;
3239 lNoDossier := 5;
3240 lNEQ := 6;
3241 lNom1 := 7;
3242 lNom2 := 8;
3243 lLigne1 := 9;
3244 lLigne2 := 10;
3245 lVille := 11;
3246 lProvince := 12;
3247 lCodePostal := 13;
3248
3249 l_taxation_year
3250 := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
3251 l_context1 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
3252
3253 hr_utility.trace ('l_cvontext1 ='||l_context1);
3254
3255 tab_employer(lAnnee) := '<Annee>' || l_taxation_year || '</Annee>' || EOL;
3256 tab_employer(lNbReleves) := '<NbReleves>' || 'Running Total' || '</NbReleves>' || EOL;
3257
3258 l_quebec_bn := pay_ca_archive_utils.get_archive_value
3259 (l_context1,'CAEOY_RL1_QUEBEC_BN');
3260
3261 tab_employer(lNoId) := '<NoId>' || substr(l_quebec_bn,1,10) ||
3262 '</NoId>' || EOL;
3263 tab_employer(lTypeDossier) := '<TypeDossier>' || 'RS' ||
3264 '</TypeDossier>' || EOL;
3265 tab_employer(lNoDossier) := '<NoDossier>' || substr(l_quebec_bn,13,4) ||
3266 '</NoDossier>' || EOL;
3267 tab_employer(lNEQ) := '<NEQ>' || substr(l_quebec_bn,1,10) ||
3268 '</NEQ>' || EOL;
3269 l_employer_name := pay_ca_archive_utils.get_archive_value(l_context1,
3270 'CAEOY_RL1_EMPLOYER_NAME');
3271
3272 tab_employer(lNom1) := '<Nom1>' ||
3273 pay_ca_rl1_mag.convert_special_char(substr(l_employer_name,1,30)) || '</Nom1>' || EOL;
3274 hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
3275 hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3276 hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3277 hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3278 hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3279 hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3280 hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3281
3282 IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
3283 tab_employer(lNom2) := '<Nom2>' ||
3284 pay_ca_rl1_mag.convert_special_char(substr(l_employer_name,31,30)) || '</Nom2>' || EOL;
3285 ELSE
3286 tab_employer(lNom2) := NULL;
3287 END IF;
3288 hr_utility.trace('tab_employer(lNom2) = ' || tab_employer(lNom2));
3289
3290 -- Address Line 1
3291
3292 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3293 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1');
3294
3295 IF l_address_line IS NULL THEN
3296
3297 l_address_begin_tag := '';
3298 tab_employer(lLigne1) := NULL;
3299 tab_employer(lLigne2) := NULL;
3300 tab_employer(lVille) := NULL;
3301 tab_employer(lProvince) := NULL;
3302 tab_employer(lCodePostal) := NULL;
3303 l_address_end_tag := '';
3304
3305 ELSE
3306
3307 l_address_begin_tag := '<Adresse>';
3308
3309 tab_employer(lLigne1) := '<Ligne1>' ||
3310 pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,30)) || '</Ligne1>' || EOL;
3311 hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
3312
3313
3314 -- Address Line 2
3315
3316 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3317 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2');
3318
3319 IF l_address_line IS NOT NULL THEN
3320 tab_employer(lLigne2) := '<Ligne2>' ||
3321 pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,30)) || '</Ligne2>' || EOL;
3322 ELSE
3323 tab_employer(lLigne2) := NULL;
3324 END IF;
3325 hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
3326
3327 -- Ville (City)
3328
3329 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3330 'CAEOY_RL1_EMPLOYER_CITY');
3331 IF l_address_line IS NOT NULL THEN
3332 tab_employer(lVille) := '<Ville>' ||
3333 pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,30)) || '</Ville>' || EOL;
3334 ELSE
3335 tab_employer(lVille) := NULL;
3336 END IF;
3337 hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
3338
3339 -- Province
3340
3341 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3342 'CAEOY_RL1_EMPLOYER_PROVINCE');
3343
3344 IF l_address_line IS NOT NULL THEN
3345 tab_employer(lProvince) := '<Province>' ||
3346 SUBSTR(hr_general.decode_lookup( 'CA_PROVINCE',
3347 l_address_line),1,20) || '</Province>' || EOL;
3348 ELSE
3349 tab_employer(lProvince) := NULL;
3350 END IF;
3351 hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
3352
3353 -- Postal Code
3354
3355 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3356 'CAEOY_RL1_EMPLOYER_POSTAL_CODE');
3357
3358 IF l_address_line IS NOT NULL THEN
3359 tab_employer(lCodePostal) := '<CodePostal>' ||
3360 substr(replace(l_address_line,' '),1,6) || '</CodePostal>' || EOL;
3361 ELSE
3362 tab_employer(lCodePostal) := NULL;
3363 END IF;
3364 hr_utility.trace('tab_employer(lCodePostal) = ' || tab_employer(lCodePostal));
3365
3366 l_address_end_tag := '</Adresse>';
3367
3368 END IF;
3369
3370 l_final_xml_string := '<T>' || EOL ||
3371 tab_employer(lAnnee) ||
3372 tab_employer(lNbReleves) || '<Employeur>' || EOL ||
3373 tab_employer(lNoId) ||
3374 tab_employer(lTypeDossier) ||
3375 tab_employer(lNoDossier) ||
3376 tab_employer(lNEQ) ||
3377 tab_employer(lNom1) ||
3378 tab_employer(lNom2) || l_address_begin_tag || EOL ||
3379 tab_employer(lLigne1) ||
3380 tab_employer(lLigne2) ||
3381 tab_employer(lVille) ||
3382 tab_employer(lProvince) ||
3383 tab_employer(lCodePostal) ||
3384 l_address_end_tag || EOL || '</Employeur>' || EOL ||
3385 '</T>' || EOL ||
3386 '</Groupe01>' || EOL;
3387
3388 hr_utility.trace('rl1_xml_employer_start: l_final_xml_string = ' || l_final_xml_string);
3389 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3390 END;
3391 END xml_employer_record;
3392
3393
3394 /**********************************************************************************************************/
3395 PROCEDURE xml_report_end IS
3396 BEGIN
3397
3398 DECLARE
3399 l_final_xml_string VARCHAR2(32000);
3400
3401 BEGIN
3402 hr_utility.trace('report ends here..closing RL1PAPER tag');
3403 l_final_xml_string := '</RL1PAPER>';
3404 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3405
3406 END;
3407 END xml_report_end;
3408
3409 PROCEDURE xml_rl1_report_start IS
3410 BEGIN
3411
3412 DECLARE
3413 l_final_xml_string VARCHAR2(32000);
3414
3415 BEGIN
3416 --hr_utility.trace_on(null,'SATI');
3417 hr_utility.trace('inside xml_rl1_report_start');
3418 l_final_xml_string := '<RL1PAPER>';
3419 pay_core_files.write_to_magtape_lob(l_final_xml_string);
3420
3421 END;
3422 END xml_rl1_report_start;
3423
3424 PROCEDURE xml_footnote_boxo(p_arch_assact_id IN NUMBER
3425 ,p_assgn_id IN NUMBER
3426 ,p_footnote_boxo1 OUT NOCOPY VARCHAR2
3427 ,p_footnote_boxo2 OUT NOCOPY VARCHAR2
3428 ,p_footnote_boxo3 OUT NOCOPY VARCHAR2
3429 ) is
3430
3431 l_person_lang VARCHAR2(10);
3432 l_cpp_withheld NUMBER;
3433 l_footnote1 VARCHAR2(200);
3434 l_footnote2 VARCHAR2(200);
3435 l_footnote3 VARCHAR2(200);
3436 l_count NUMBER;
3437 l_count_boxo NUMBER;
3438 l_boxo1 VARCHAR(400);
3439 l_boxo2 VARCHAR(400);
3440 l_boxo3 VARCHAR(400);
3441 l_footnote_value VARCHAR(30);
3442
3443 type t_footnote_record is record ( code varchar2(100)
3444 ,value VARCHAR2(30));
3445 type t_footnote_table is table of t_footnote_record index by BINARY_INTEGER;
3446
3447 t_footnote t_footnote_table;
3448 type t_boxo_record is record ( code varchar2(100)
3449 ,value NUMBER);
3450 type t_boxo_table is table of t_boxo_record index by BINARY_INTEGER;
3451 t_boxo t_boxo_table;
3452 i NUMBER :=0;
3453 EOL varchar2(5);
3454
3455 cursor c_get_language(p_assgn_id number) is
3456 select decode(correspondence_language,NULL,'US',correspondence_language)
3457 from per_all_people_f
3458 where person_id = to_number(pay_ca_archive_utils.get_archive_value(
3459 p_arch_assact_id,
3460 'CAEOY_PERSON_ID'));
3461 cursor cur_boxo is
3462 SELECT 'BOXO-'||substr(fdi.user_name,23,2) DB_Name,to_number(fai.value) value
3463 FROM ff_database_items fdi,
3464 ff_archive_items fai
3465 WHERE fai.user_entity_id = fdi.user_entity_id
3466 AND fai.context1 = p_arch_assact_id
3467 AND fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT_R__PER_JD_YTD'
3468 and fai.value <> '0'
3469 ORDER BY substr(fdi.user_name,5,4);
3470
3471 cursor cur_ftnt(p_cpp_withheld NUMBER
3472 ,p_person_lang VARCHAR2
3473 ,p_arch_assactid NUMBER)is
3474 select substr(ltrim(rtrim(code)),1,60) code,to_char(value,'999,999.99') value
3475 from (
3476
3477 select pay_ca_rl1_reg.get_label(lookup_type,lookup_code,p_person_lang) code, p_cpp_withheld value
3478 from hr_lookups
3479 where lookup_type = 'PAY_CA_RL1_FOOTNOTES'
3480 and lookup_code = 'CPP'
3481 and p_cpp_withheld <> 0
3482 union
3483 select SUBSTR(fdi.user_name,11,4)||', '||pay_ca_rl1_reg.get_label(hl.lookup_type,hl.lookup_code,p_person_lang) code,
3484 to_number(fai.value) value
3485 from HR_LOOKUPS HL,
3486 ff_database_items fdi,
3487 ff_archive_items fai
3488 where fai.user_entity_id=fdi.user_entity_id
3489 and fai.context1= p_arch_assactid
3490 and fdi.user_name like 'CAEOY_RL1_BOX%_AMT_PER_JD_YTD'
3491 and fai.value <> '0'
3492 and hl.lookup_type = 'PAY_CA_RL1_FOOTNOTES'
3493 AND HL.LOOKUP_CODE = SUBSTR(replace(FDI.USER_NAME,'_AMT_PER_JD_YTD'),-2)
3494 union all
3495 select pay_ca_rl1_reg.get_label(hl.lookup_type,hl.lookup_code,p_person_lang) code,
3496 to_number(pai.action_information5) value
3497 from pay_action_information pai
3498 , hr_lookups hl
3499 where pai.action_context_id = p_arch_assactid
3500 and hl.lookup_type = 'PAY_CA_RL1_NONBOX_FOOTNOTES'
3501 and hl.lookup_code = pai.action_information4
3502 );
3503
3504 l_see_attached Varchar2(100);
3505
3506 begin
3507 l_footnote1 :=NULL;
3508 l_footnote2 :=NULL;
3509 l_footnote3 :=NULL;
3510 l_boxo1 :=NULL;
3511 l_boxo2 :=NULL;
3512 l_boxo3 :=NULL;
3513
3514 l_see_attached := hr_general.decode_lookup('PAY_CA_LABELS'
3515 ,'SEE_ATTACHED');
3516 hr_utility.trace('l_see_attached = '||l_see_attached);
3517 SELECT
3518 fnd_global.local_chr(13) || fnd_global.local_chr(10)
3519 INTO EOL
3520 from dual;
3521 l_cpp_withheld := pay_ca_archive_utils.get_archive_value(p_arch_assact_id
3522 ,'CAEOY_CPP_EE_WITHHELD_PER_YTD');
3523 open c_get_language(p_assgn_id);
3524 fetch c_get_language into l_person_lang;
3525 close c_get_language;
3526 hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
3527 hr_utility.trace(' l_person_lang ='|| l_person_lang);
3528 hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
3529 for rec in cur_ftnt(l_cpp_withheld,l_person_lang,p_arch_assact_id)
3530 loop
3531 i:=i+1;
3532 t_footnote(i) := rec;
3533 end loop;
3534 i:=0;
3535 for rec in cur_boxo
3536 loop
3537 i:=i+1;
3538 t_boxo(i) :=rec;
3539 end loop;
3540 l_count_boxo:=t_boxo.count;
3541 l_count := t_footnote.count;
3542 hr_utility.trace(' l_count_boxo ='|| l_count_boxo);
3543 hr_utility.trace(' l_count ='|| l_count);
3544 if l_count > 1 then --more than 1 footnote
3545 p_footnote_boxo1 :='<Seeattached1>'|| l_see_attached ||'</Seeattached1>';
3546 p_footnote_boxo2 :='<Seeattached2>'|| l_see_attached ||'</Seeattached2>';
3547 p_footnote_boxo3 :='<Seeattached3>'|| l_see_attached ||'</Seeattached3>';
3548 elsif l_count=1 and l_count_boxo > 1 then --accomodate 1 box o
3549 p_footnote_boxo1 :='<Seeattached1>'|| l_see_attached ||'</Seeattached1>';
3550 p_footnote_boxo2 :='<Seeattached2>'|| l_see_attached ||'</Seeattached2>';
3551 p_footnote_boxo3 :='<Seeattached3>'|| l_see_attached ||'</Seeattached3>';
3552 elsif l_count=1 and l_count_boxo<=1 then
3553 if(t_footnote(1).code = 'Volunteer-Allow not incl in A and L:$1,000') then --Bug 6748011
3554 l_footnote_value := null;
3555 else
3556 l_footnote_value := t_footnote(1).value;
3557 end if;
3558 l_footnote1 :='<Footnote_Code1>'||t_footnote(1).code||'</Footnote_Code1>'||EOL
3559 ||'<Footnote_value1>'|| l_footnote_value || '</Footnote_value1>'||EOL;
3560 l_footnote2 :='<Footnote_Code2>'||t_footnote(1).code||'</Footnote_Code2>'||EOL
3561 ||'<Footnote_value2>'|| l_footnote_value || '</Footnote_value2>'||EOL;
3562 l_footnote3 :='<Footnote_Code3>'||t_footnote(1).code||'</Footnote_Code3>'||EOL
3563 ||'<Footnote_value3>'|| l_footnote_value || '</Footnote_value3>'||EOL;
3564 hr_utility.trace(' l_footnote1 ='|| l_footnote1);
3565 hr_utility.trace(' l_footnote2 ='|| l_footnote2);
3566 hr_utility.trace(' l_footnote3 ='|| l_footnote3);
3567 if (l_count_boxo=1) then
3568 l_boxo1 :='<Boxo_code_31>'||t_boxo(1).code||'</Boxo_code_31>'||EOL
3569 ||'<Boxo_value_31>'||t_boxo(1).value||'</Boxo_value_31>'||EOL;
3570 l_boxo2 :='<Boxo_code_32>'||t_boxo(1).code||'</Boxo_code_32>'||EOL
3571 ||'<Boxo_value_32>'||t_boxo(1).value||'</Boxo_value_32>'||EOL;
3572 l_boxo3 :='<Boxo_code_33>'||t_boxo(1).code||'</Boxo_code_33>'||EOL
3573 ||'<Boxo_value_33>'||t_boxo(1).value||'</Boxo_value_33>'||EOL;
3574
3575 end if;
3576
3577 p_footnote_boxo1 :=l_footnote1||l_boxo1;
3578 p_footnote_boxo2 :=l_footnote2||l_boxo2;
3579 p_footnote_boxo3 :=l_footnote3||l_boxo3;
3580 elsif l_count = 0 and l_count_boxo < 4 then
3581 hr_utility.trace('boxo count less than 4');
3582 for k in 1.. l_count_boxo
3583 loop
3584 l_boxo1 :=l_boxo1||'<Boxo_code_'||k||'1>'||t_boxo(k).code||'</Boxo_code_'||k||'1>'||EOL
3585 ||'<Boxo_value_'||k||'1>'||t_boxo(k).value||'</Boxo_value_'||k||'1>'||EOL;
3586 l_boxo2 :=l_boxo2||'<Boxo_code_'||k||'2>'||t_boxo(k).code||'</Boxo_code_'||k||'2>'||EOL
3587 ||'<Boxo_value_'||k||'2>'||t_boxo(k).value||'</Boxo_value_'||k||'2>'||EOL;
3588 l_boxo3 :=l_boxo3||'<Boxo_code_'||k||'3>'||t_boxo(k).code||'</Boxo_code_'||k||'3>'||EOL
3589 ||'<Boxo_value_'||k||'3>'||t_boxo(k).value||'</Boxo_value_'||k||'3>'||EOL;
3590
3591 end loop;
3592 hr_utility.trace(' l_boxo1 ='|| l_boxo1);
3593 hr_utility.trace(' l_boxo2 ='|| l_boxo2);
3594 hr_utility.trace(' l_boxo3 ='|| l_boxo3);
3595
3596 p_footnote_boxo1 :=l_boxo1;
3597 p_footnote_boxo2 :=l_boxo2;
3598 p_footnote_boxo3 :=l_boxo3;
3599
3600 elsif l_count_boxo > 3 then
3601 p_footnote_boxo1 :='<Seeattached1>'|| l_see_attached ||'</Seeattached1>';
3602 p_footnote_boxo2 :='<Seeattached2>'|| l_see_attached ||'</Seeattached2>';
3603 p_footnote_boxo3 :='<Seeattached3>'|| l_see_attached ||'</Seeattached3>';
3604 end if;
3605
3606 hr_utility.trace(' p_footnote_boxo1 ='|| p_footnote_boxo1);
3607 hr_utility.trace(' p_footnote_boxo2 ='|| p_footnote_boxo2);
3608 hr_utility.trace(' p_footnote_boxo3 ='|| p_footnote_boxo3);
3609
3610 end xml_footnote_boxo;
3611
3612 PROCEDURE RL1XML_emplyer_data(p_assact_id IN NUMBER
3613 ,p_emplyr_final1 OUT NOCOPY VARCHAR2
3614 ,p_emplyr_final2 OUT NOCOPY VARCHAR2
3615 ,p_emplyr_final3 OUT NOCOPY VARCHAR2
3616 ) is
3617
3618 CURSOR c_get_arch_pay_actid IS
3619 SELECT to_number(substr(paa.serial_number,17,14)) payactid --archiver payroll action id
3620 FROM pay_assignment_actions paa
3621 WHERE paa.assignment_action_id = p_assact_id;
3622 l_final_xml_string VARCHAR2(32000);
3623 l_index NUMBER;
3624 l_address_line hr_locations.address_line_1%TYPE;
3625 TYPE employer_info IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
3626
3627 tab_employer employer_info;
3628 lNoId NUMBER;
3629 lNom1 NUMBER;
3630 lLigne1 NUMBER;
3631 lLigne2 NUMBER;
3632 lVille NUMBER;
3633 lProvince NUMBER;
3634 lCodePostal NUMBER;
3635 lLigne3 NUMBER;
3636 lCountry NUMBER;
3637 l_context1 ff_archive_items.context1%TYPE;
3638 EOL varchar2(5);
3639 l_employer_name varchar2(100);
3640 l_quebec_bn varchar2(20);
3641
3642 BEGIN
3643
3644 hr_utility.trace('XML Employer');
3645
3646 SELECT
3647 fnd_global.local_chr(13) || fnd_global.local_chr(10)
3648 INTO EOL
3649 FROM dual;
3650
3651 lNoId := 1;
3652 lNom1 := 2;
3653 lLigne1 := 3;
3654 lLigne2 := 4;
3655 lVille := 5;
3656 lProvince := 6;
3657 lCodePostal := 7;
3658 lLigne3 := 8;
3659 lCountry := 9;
3660
3661 open c_get_arch_pay_actid;
3662 fetch c_get_arch_pay_actid
3663 into l_context1;
3664 close c_get_arch_pay_actid;
3665 hr_utility.trace ('l_cvontext1 ='||l_context1);
3666
3667 l_quebec_bn := pay_ca_archive_utils.get_archive_value
3668 (l_context1,'CAEOY_RL1_QUEBEC_BN');
3669 tab_employer(lNoId) := substr(l_quebec_bn,1,10); -- employer id
3670
3671 l_employer_name := pay_ca_archive_utils.get_archive_value(l_context1,
3672 'CAEOY_RL1_EMPLOYER_NAME');
3673
3674 tab_employer(lNom1) := pay_ca_rl1_mag.convert_special_char(l_employer_name);
3675 hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3676 hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3677
3678 -- Address Line 1
3679 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3680 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1');
3681 tab_employer(lLigne1) := pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,40));
3682 hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
3683
3684 -- Address Line 2
3685 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3686 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2');
3687 tab_employer(lLigne2) := pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,40));
3688 hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
3689
3690 -- Address Line 3
3691 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3692 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE3');
3693 tab_employer(lLigne3) := pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,40));
3694 hr_utility.trace('tab_employer(lLigne3) = ' || tab_employer(lLigne3));
3695
3696 if(tab_employer(lLigne2) is not null or tab_employer(lLigne3) is not null) then
3697 tab_employer(lLigne2) := tab_employer(lLigne2) ||' '|| tab_employer(lLigne3) ; /*******/
3698 end if;
3699
3700 -- Ville (City)
3701 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3702 'CAEOY_RL1_EMPLOYER_CITY');
3703 tab_employer(lVille) := pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,30));
3704 hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
3705
3706 -- Province
3707
3708 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3709 'CAEOY_RL1_EMPLOYER_PROVINCE');
3710
3711 tab_employer(lProvince) :=l_address_line;
3712 hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
3713
3714 --Country
3715 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3716 'CAEOY_RL1_EMPLOYER_COUNTRY');
3717 tab_employer(lCountry) :=l_address_line;
3718 hr_utility.trace('tab_employer(lCountry) = ' || tab_employer(lCountry));
3719
3720 -- Postal Code
3721 l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3722 'CAEOY_RL1_EMPLOYER_POSTAL_CODE');
3723 tab_employer(lCodePostal) :=substr(replace(l_address_line,' '),1,6);
3724
3725 --final city
3726 tab_employer(lVille):=tab_employer(lVille)||' '||
3727 tab_employer(lProvince)||' '||
3728 tab_employer(lCountry) ||' '||
3729 substr(tab_employer(lCodePostal),1,3)||' '||
3730 substr(tab_employer(lCodePostal),4,3);
3731
3732 hr_utility.trace('tab_employer(lCodePostal) = ' || tab_employer(lCodePostal));
3733 for l_index in 1..3 loop
3734 l_final_xml_string := '<Employer_name'||l_index||'>' ||substr(tab_employer(lNom1),1,40) || '</Employer_name'||l_index||'>' || EOL;
3735
3736 /*********************************************/
3737 if tab_employer(lLigne1) is not null
3738 or tab_employer(lLigne2) is not null
3739 or tab_employer(lVille) is not null then
3740
3741 l_final_xml_string := l_final_xml_string||'<emplr_Ligne1'||l_index||'>' ;
3742
3743
3744 if tab_employer(lLigne1) is not null then
3745 l_final_xml_string := l_final_xml_string||substr(tab_employer(lLigne1),1,40)||EOL;
3746 end if;
3747 if tab_employer(lLigne2) is not null then
3748 l_final_xml_string := l_final_xml_string||substr(tab_employer(lLigne2),1,40)||EOL;
3749 end if;
3750 if tab_employer(lVille) is not null then
3751 l_final_xml_string := l_final_xml_string||substr(tab_employer(lVille),1,40)||EOL;
3752 end if;
3753
3754 l_final_xml_string := l_final_xml_string|| '</emplr_Ligne1'||l_index||'>' || EOL;
3755 end if;
3756 /********************************************/
3757
3758 /*****************************************************
3759 if tab_employer(lLigne1) is not null then
3760 l_final_xml_string := l_final_xml_string
3761 ||'<emplr_Ligne1'||l_index||'>' ||substr(tab_employer(lLigne1),1,40) || '</emplr_Ligne1'||l_index||'>' || EOL;
3762 end if;
3763 if tab_employer(lLigne2) is not null then
3764 l_final_xml_string := l_final_xml_string
3765 ||'<emplr_Ligne2'||l_index||'>' ||substr(tab_employer(lLigne2),1,40) || '</emplr_Ligne2'||l_index||'>' || EOL;
3766 end if;
3767 if tab_employer(lVille) is not null then
3768 l_final_xml_string := l_final_xml_string
3769 ||'<emplr_Ville'||l_index||'>' ||substr(tab_employer(lVille),1,40) || '</emplr_Ville'||l_index||'>' || EOL;
3770 end if;
3771 ******************************************************/
3772
3773
3774 hr_utility.trace('rl1_xml_employer_start: l_final_xml_string = ' || l_final_xml_string);
3775 if l_index=1 then
3776 p_emplyr_final1 := l_final_xml_string;
3777 end if;
3778 if l_index=2 then
3779 p_emplyr_final2 := l_final_xml_string;
3780 end if;
3781 if l_index=3 then
3782 p_emplyr_final3 := l_final_xml_string;
3783 end if;
3784 end loop;
3785
3786 END RL1XML_emplyer_data;
3787
3788 PROCEDURE archive_ca_deinit (p_pactid IN NUMBER) IS
3789
3790 CURSOR c_get_report_type ( p_pactid number) IS
3791 SELECT report_type
3792 FROM pay_payroll_actions
3793 WHERE payroll_action_id = p_pactid;
3794
3795 l_report_type pay_payroll_actions.report_type%type;
3796
3797 BEGIN
3798
3799 open c_get_report_type(p_pactid);
3800 fetch c_get_report_type
3801 into l_report_type;
3802 close c_get_report_type;
3803
3804
3805
3806 END archive_ca_deinit;
3807 /******************************************** *************************************************************/
3808
3809 END PAY_CA_RL1_AMEND_MAG;