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