[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4_CANCEL_MAG
Source
1 PACKAGE BODY pay_ca_t4_cancel_mag AS
2 /* $Header: pycat4cmag.pkb 120.6.12020000.4 2013/01/08 14:46:33 sgotlasw ship $ */
3
4 PROCEDURE range_cursor (
5 p_pactid IN NUMBER,
6 p_sqlstr OUT NOCOPY VARCHAR2)
7 IS
8
9 BEGIN
10
11 hr_utility.trace('Inside Range Cursor');
12
13 p_sqlstr := 'select distinct paaf.person_id
14 from per_all_assignments_f paaf,
15 pay_payroll_actions ppa,
16 pay_payroll_actions ppa1,
17 pay_assignment_actions paa
18 where ppa.payroll_action_id = paa.payroll_action_id
19 and paa.assignment_id = paaf.assignment_id
20 and ppa1.payroll_action_id = :payroll_action_id
21 and ppa.payroll_action_id =
22 to_number(pay_ca_t4_cancel_mag.get_parameter(''PAY_ACT'', ppa1.legislative_parameters))
23 and paaf.person_id =
24 nvl(pay_ca_t4_cancel_mag.get_parameter(''PER_ID'',ppa1.legislative_parameters),paaf.person_id)
25 order by paaf.person_id';
26
27 hr_utility.trace('Leaving Range Cursor');
28
29 END range_cursor;
30 /**************************************************************************/
31 PROCEDURE action_creation(
32 p_pactid IN NUMBER,
33 p_stperson IN NUMBER,
34 p_endperson IN NUMBER,
35 p_chunk IN NUMBER )
36 IS
37
38 l_trans_gre VARCHAR2(10);
39 l_validate_gre VARCHAR2(10);
40 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
41 l_asg_set_id NUMBER := NULL;
42
43 CURSOR c_all_asg IS
44 select paaf.person_id,
45 paaf.assignment_id,
46 paa1.tax_unit_id,
47 paaf.effective_end_date,
48 paa.assignment_action_id,
49 ppa2.payroll_action_id
50 from per_all_assignments_f paaf,
51 pay_payroll_actions ppa,
52 pay_payroll_actions ppa1,
53 pay_payroll_actions ppa2,
54 pay_assignment_actions paa,
55 pay_assignment_actions paa1,
56 pay_action_interlocks int
57 where ppa.payroll_action_id = paa.payroll_action_id
58 and paa.assignment_id = paaf.assignment_id
59 and ppa1.payroll_action_id = p_pactid
60 and paaf.person_id between p_stperson and p_endperson
61 and ppa.payroll_action_id =
62 to_number(pay_ca_t4_cancel_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
63 and ppa2.report_type in ('T4','CAEOY_T4_AMEND_PP')
64 and int.locking_action_id = paa.assignment_action_id
65 and paa1.assignment_action_id = int.locked_action_id
66 and ppa2.payroll_action_id = paa1.payroll_action_id
67 and ppa2.action_status = 'C'
68 and paa.action_status = 'C'
69 and paa1.action_status = 'C'
70 and paa.assignment_action_id
71 not in (select paa2.assignment_action_id
72 from pay_action_interlocks pai, pay_assignment_actions paa2
73 where paa2.assignment_action_id = pai.locked_action_id
74 and paa2.payroll_action_id =
75 to_number(pay_ca_t4_cancel_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
76 )
77 and paaf.effective_start_date = (select max(paaf2.effective_start_date)
78 from per_all_assignments_f paaf2
79 where paaf2.assignment_id = paa.assignment_id
80 and paaf2.effective_start_date <= ppa.effective_date)
81 order by paaf.person_id;
82 -----
83 CURSOR c_all_asg_in_asgset IS
84 select paaf.person_id,
85 paaf.assignment_id,
86 paa1.tax_unit_id,
87 paaf.effective_end_date,
88 paa.assignment_action_id,
89 ppa2.payroll_action_id
90 from
91 per_all_assignments_f paaf,
92 pay_payroll_actions ppa,
93 pay_payroll_actions ppa1,
94 pay_payroll_actions ppa2,
95 pay_assignment_actions paa,
96 pay_assignment_actions paa1,
97 pay_action_interlocks int
98 where
99 ppa.payroll_action_id = paa.payroll_action_id
100 and paa.assignment_id = paaf.assignment_id
101 and ppa1.payroll_action_id = p_pactid
102 and paaf.person_id between p_stperson and p_endperson
103 and ppa.payroll_action_id =
104 to_number(pay_ca_t4_cancel_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
105 and ppa2.report_type in ('T4','CAEOY_T4_AMEND_PP')
106 and int.locking_action_id = paa.assignment_action_id
107 and paa1.assignment_action_id = int.locked_action_id
108 and ppa2.payroll_action_id = paa1.payroll_action_id
109 and ppa2.action_status = 'C'
110 and paa.action_status = 'C'
111 and paa1.action_status = 'C'
112 and exists (select 1
113 from hr_assignment_set_amendments hasa,
114 per_assignments_f paf
115 where hasa.assignment_set_id = l_asg_set_id
116 and upper(hasa.include_or_exclude) = 'I'
117 and hasa.assignment_id = paf.assignment_id
118 and paf.person_id = paaf.person_id)
119 and paa.assignment_action_id
120 not in (select paa2.assignment_action_id
121 from pay_action_interlocks pai,
122 pay_assignment_actions paa2
123 where paa2.assignment_action_id = pai.locked_action_id
124 and paa2.payroll_action_id =
125 to_number(pay_ca_t4_cancel_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
126 )
127 and paaf.effective_start_date = (select max(paaf2.effective_start_date)
128 from per_all_assignments_f paaf2
129 where paaf2.assignment_id = paa.assignment_id
130 and paaf2.effective_start_date <= ppa.effective_date)
131 order by paaf.person_id;
132 -----
133 l_year_end DATE;
134 l_effective_end_date DATE;
135 l_report_type pay_payroll_actions.report_type%TYPE;
136 l_business_group_id pay_payroll_actions.business_group_id%TYPE;
137 l_person_id per_all_people_f.person_id%TYPE;
138 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
139 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
140 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
141 lockingactid pay_assignment_actions.assignment_action_id%TYPE;
142 lv_report_type pay_payroll_actions.report_type%TYPE ;
143 ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
144 lv_fed_amend_flag ff_archive_items.value%TYPE;
145 ln_t4_amend_flag_ue_id ff_database_items.user_entity_id%TYPE;
146 lv_flag_count NUMBER := 0;
147 l_lockingactid_check pay_assignment_actions.assignment_action_id%TYPE;
148 l_payroll_act NUMBER;
149
150 BEGIN
151 hr_utility.trace('+=========================================================+');
152 hr_utility.set_location( 'pay_ca_t4_cancel_mag.action_creation',10);
153 get_report_parameters(
154 p_pactid,
155 l_legislative_parameters,
156 l_year_end,
157 l_report_type,
158 l_business_group_id);
159
160 l_trans_gre := pay_ca_t4_cancel_mag.get_parameter('TRANSMITTER_GRE',
161 l_legislative_parameters);
162 hr_utility.trace('l_trans_gre ='||l_trans_gre);
163 l_validate_gre := validate_gre_data(l_trans_gre, to_char(l_year_end,'YYYY'));
164 IF l_validate_gre = '1' THEN
165 hr_utility.raise_error;
166 END IF;
167 hr_utility.set_location( 'pay_ca_t4_cancel_mag.action_creation',15);
168 l_asg_set_id := to_number(pay_ca_t4_cancel_mag.get_parameter('ASG_SET_ID',
169 l_legislative_parameters));
170 hr_utility.trace('l_assignment_set_id ='||l_asg_set_id);
171 --
172 IF l_asg_set_id IS NOT NULL THEN
173 OPEN c_all_asg_in_asgset;
174 ELSE
175 OPEN c_all_asg;
176 END IF;
177
178 LOOP
179 IF l_asg_set_id IS NOT NULL THEN
180 FETCH c_all_asg_in_asgset INTO l_person_id,
181 l_assignment_id,
182 l_tax_unit_id,
183 l_effective_end_date,
184 l_assignment_action_id,
185 l_payroll_act;
186 hr_utility.set_location('pay_ca_t4_cancel_mag.action_creation', 20);
187 EXIT WHEN c_all_asg_in_asgset%NOTFOUND;
188 ELSE
189 FETCH c_all_asg INTO l_person_id,
190 l_assignment_id,
191 l_tax_unit_id,
192 l_effective_end_date,
193 l_assignment_action_id,
194 l_payroll_act;
195 hr_utility.set_location('pay_ca_t4_cancel_mag.action_creation', 25);
196 EXIT WHEN c_all_asg%NOTFOUND;
197 END IF;
198
199 hr_utility.trace('+------------------------------------------------+');
200 hr_utility.trace('Fetched Assignment ID : '|| to_char(l_assignment_id));
201 hr_utility.trace('Person ID : '|| to_char(l_person_id));
202 hr_utility.trace('Tax Unit ID : '|| to_char(l_tax_unit_id));
203 hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date,'DD-Mon-YYYY'));
204 hr_utility.trace('x------------------------------------------------x');
205 SELECT pay_assignment_actions_s.nextval
206 INTO lockingactid
207 FROM dual;
208 hr_utility.trace('New T4 Cancel Action = ' ||to_char(lockingactid));
209 hr_utility.set_location('pay_ca_t4_cancel_mag.action_creation', 30);
210 hr_nonrun_asact.insact(lockingactid
211 ,l_assignment_id
212 ,p_pactid
213 ,p_chunk
214 ,l_tax_unit_id);
215 hr_utility.set_location('pay_ca_t4_cancel_mag.action_creation', 40);
216
217 UPDATE pay_assignment_actions aa
218 SET aa.serial_number = to_char(l_person_id)
219 WHERE aa.assignment_action_id = lockingactid;
220
221 hr_utility.set_location('pay_ca_t4_cancel_mag.action_creation', 50);
222 hr_nonrun_asact.insint(lockingactid ,l_assignment_action_id);
223 hr_utility.trace(to_char(lockingactid)||' locked Cancel Archiver ID '||to_char(l_assignment_action_id));
224 hr_utility.trace('x------------------------------------------------x');
225 END LOOP; --c_all_asg loop
226 IF l_asg_set_id IS NOT NULL THEN
227 CLOSE c_all_asg_in_asgset;
228 ELSE
229 CLOSE c_all_asg;
230 END IF;
231 hr_utility.trace('X=========================================================X');
232 END action_creation;
233 /**************************************************************************/
234 FUNCTION validate_gre_data ( p_trans IN VARCHAR2,
235 p_year IN VARCHAR2)
236 RETURN VARCHAR2 IS
237 ----
238 CURSOR c_trans_payid (c_trans_id IN VARCHAR2,
239 c_year IN VARCHAR2) IS
240 SELECT ppa.payroll_action_id,ppa.business_group_id
241 FROM hr_organization_information hoi,
242 pay_payroll_actions ppa
243 WHERE hoi.organization_id = to_number(c_trans_id)
244 AND hoi.org_information_context='Fed Magnetic Reporting'
245 AND ppa.report_type in ('T4','CAEOY_T4_AMEND_PP') -- T4 Archiver Report Type
246 AND hoi.organization_id = pay_ca_t4_cancel_mag.get_parameter('TRANSFER_GRE',
247 ppa.legislative_parameters)
248 AND ppa.effective_date = to_date('31-12-'||c_year,'DD-MM-YYYY');
249 ----
250
251 CURSOR c_all_gres(p_trans IN VARCHAR2,
252 p_year IN VARCHAR2,
253 p_bg_id IN NUMBER) IS
254 SELECT DISTINCT ppa.payroll_action_id, hoi.organization_id, hou.name
255 FROM pay_payroll_actions ppa,
256 hr_organization_information hoi,
257 hr_all_organization_units hou
258 WHERE hoi.org_information_context = 'Canada Employer Identification'
259 AND hoi.org_information11 = p_trans
260 AND hou.business_group_id = p_bg_id
261 AND hou.organization_id = hoi.organization_id
262 AND ppa.report_type in ('T4','CAEOY_T4_AMEND_PP')
263 AND ppa.effective_date = to_date('31-12-'||p_year,'DD-MM-YYYY')
264 AND ppa.business_group_id = p_bg_id
265 AND hoi.organization_id = pay_ca_t4_cancel_mag.get_parameter('TRANSFER_GRE',
266 ppa.legislative_parameters);
267 ----
268 CURSOR c_gre_name (b_org_id IN VARCHAR2) IS
269 SELECT hou.name
270 FROM hr_all_organization_units hou
271 WHERE hou.organization_id = to_number(b_org_id);
272 ----
273 /* Local variables */
274 l_trans_gre hr_all_organization_units.organization_id%TYPE;
275 l_year VARCHAR2(10);
276 l_gre hr_all_organization_units.organization_id%TYPE;
277 l_bus_grp hr_all_organization_units.business_group_id%TYPE;
278 l_trans_no ff_archive_items.value%TYPE;
279 l_tech_name ff_archive_items.value%TYPE;
280 l_tech_area ff_archive_items.value%TYPE;
281 l_tech_phno ff_archive_items.value%TYPE;
282 l_tech_email ff_archive_items.value%TYPE;
283 l_lang ff_archive_items.value%TYPE;
284 l_acc_name ff_archive_items.value%TYPE;
285 l_acc_area ff_archive_items.value%TYPE;
286 l_acc_phno ff_archive_items.value%TYPE;
287 l_trans_bus_no ff_archive_items.value%TYPE;
288 l_bus_no ff_archive_items.value%TYPE;
289 l_trans_payid pay_payroll_actions.payroll_action_id%TYPE;
290 l_gre_payid pay_payroll_actions.payroll_action_id%TYPE;
291 l_gre_actid pay_assignment_actions.assignment_action_id%TYPE;
292 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
293 l_acc_info_flag CHAR(1);
294 l_trans_name ff_archive_items.value%TYPE;
295 l_gre_name ff_archive_items.value%TYPE;
296 l_bg_id pay_payroll_actions.business_group_id%TYPE;
297
298 BEGIN
299 /* Fetching the Payroll Action Id for Trasnmitter GRE */
300
301 hr_utility.trace('Inside the Validation Code');
302 hr_utility.trace('The Transmitter GRE id passed is '||p_trans);
303 OPEN c_trans_payid(p_trans,p_year);
304 FETCH c_trans_payid INTO l_trans_payid,l_bg_id;
305 IF c_trans_payid%NOTFOUND THEN
306 CLOSE c_trans_payid;
307 hr_utility.trace('The Transmitter GRE ID not found :'||p_trans);
308 hr_utility.raise_error;
309 RETURN '1';
310 ELSE
311 CLOSE c_trans_payid;
312 END IF;
313
314 hr_utility.trace('Fetched the Payroll Id for transmitter GRE :'|| l_trans_payid);
315 hr_utility.trace('The Reporting Year is '||p_year);
316 /*Fetching the Trasnmitter Level Data */
317
318 l_trans_no := get_arch_val(l_trans_payid, 'CAEOY_TRANSMITTER_NUMBER');
319 l_tech_name := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_NAME');
320 l_tech_area := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_AREA_CODE');
321 l_tech_email := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_EMAIL');
322 l_tech_phno := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_PHONE');
323 l_lang := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_LANGUAGE');
324 l_acc_name := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_NAME');
325 l_acc_area := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
326 l_acc_phno := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_PHONE');
327 l_trans_bus_no := get_arch_val(l_trans_payid, 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
328
329 OPEN c_gre_name(to_number(p_trans));
330 FETCH c_gre_name INTO l_trans_name;
331 CLOSE c_gre_name;
332
333 hr_utility.trace('Transmitter Number :'||l_trans_no);
334 hr_utility.trace('Tech Name : '||l_tech_name);
335 hr_utility.trace('Tech Area : '||l_tech_area);
336 hr_utility.trace('Tech Phone : '||l_tech_phno);
337 hr_utility.trace('Tech Email : '||l_tech_email);
338 hr_utility.trace('Tech Language : '||l_lang);
339
340 /* Checking for the validity of the above values fetched */
341 IF l_trans_no IS NULL
342 OR TRANSLATE(l_trans_no,'M0123456789','M9999999999') <> 'MM999999' THEN
343 hr_utility.trace('Incorrect Transmitter No format');
344 hr_utility.set_message(801,'PAY_74155_INCORRECT_TRANSMT_NO');
345 hr_utility.set_message_token('GRE_NAME',l_trans_name);
346 pay_core_utils.push_message(801,'PAY_74155_INCORRECT_TRANSMT_NO','P');
347 pay_core_utils.push_token('GRE_NAME',l_trans_name);
348 hr_utility.raise_error;
349 RETURN '1';
350 END IF;
351
352 IF l_tech_name IS NULL OR
353 l_tech_area IS NULL OR
354 l_tech_phno IS NULL OR
355 l_tech_email IS NULL OR
356 l_lang IS NULL THEN
357 hr_utility.trace('Technical contact details missing');
358 hr_utility.set_message(801,'PAY_74158_INCORRECT_TCHN_INFO');
359 hr_utility.set_message_token('GRE_NAME',l_trans_name);
360 pay_core_utils.push_message(801,'PAY_74158_INCORRECT_TCHN_INFO','P');
361 pay_core_utils.push_token('GRE_NAME',l_trans_name);
362 hr_utility.raise_error;
363 RETURN '1';
364 END IF;
365
366 IF l_acc_name IS NULL OR
367 l_acc_phno IS NULL OR
368 l_acc_area IS NULL THEN
369 l_acc_info_flag := 'N';
370 ELSE
371 l_acc_info_flag := 'Y';
372 END IF;
373 hr_utility.trace('The value of the Flag is '||l_acc_info_flag);
374
375 /* If Transmitter Level Accounting Information is Missing checking for the GRE level information */
376
377 OPEN c_all_gres(p_trans,p_year,l_bg_id);
378 LOOP
379 FETCH c_all_gres into l_gre_payid, l_gre, l_gre_name;
380 hr_utility.trace('The Gre id fetched is '||l_gre);
381 IF c_all_gres%NOTFOUND THEN
382 CLOSE c_all_gres;
383 EXIT;
384 END IF;
385
386 hr_utility.trace('Before fetching the GREs for this Transmitter '||l_gre||'-'||p_year);
387
388
389 IF l_gre <> to_number(p_trans) THEN
390 hr_utility.trace('Inside the loop'||l_gre_payid);
391
392 hr_utility.trace('Checking GRE level data');
393 hr_utility.trace('The Payroll Action Id for Gre is '|| l_gre_payid);
394 l_bus_no := get_arch_val(l_gre_payid,'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
395 l_acc_name := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_NAME');
396 l_acc_area := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
397 l_acc_phno := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_PHONE');
398
399 hr_utility.trace('Tax unit ID :'||l_tax_unit_id);
400 hr_utility.trace('Acc Name :'||l_acc_name);
401 hr_utility.trace('Acc Area :'||l_acc_area);
402 hr_utility.trace('Acc Phone : '||l_acc_phno);
403 hr_utility.trace('GRE Name :'||l_gre_name);
404
405 IF l_bus_no IS NULL
406 OR TRANSLATE(l_bus_no,'0123456789RP','9999999999RP') <> '999999999RP9999' THEN
407 hr_utility.trace('No Business Number Entereed ');
408 hr_utility.set_message(801,'PAY_74154_INCORRECT_BN');
409 hr_utility.set_message_token('GRE_NAME',l_gre_name);
410 pay_core_utils.push_message(801,'PAY_74154_INCORRECT_BN','P');
411 pay_core_utils.push_token('GRE_NAME',l_gre_name);
412 hr_utility.raise_error;
413 RETURN '1';
414 END IF;
415
416 IF (l_acc_name IS NULL OR
417 l_acc_area IS NULL OR
418 l_acc_phno IS NULL ) AND
419 l_acc_info_flag = 'N' THEN
420 hr_utility.trace('No Accounting Contact info present');
421 hr_utility.set_message(801,'PAY_74157_INCORRECT_ACNT_INFO');
422 hr_utility.set_message_token('GRE_NAME',l_gre_name);
423 pay_core_utils.push_message(801,'PAY_74157_INCORRECT_ACNT_INFO','P');
424 pay_core_utils.push_token('GRE_NAME',l_gre_name);
425 hr_utility.raise_error;
426 RETURN '1';
427 END IF;
428
429 ELSIF l_gre = to_number(p_trans) THEN
430
431 IF l_trans_bus_no IS NULL
432 OR TRANSLATE(l_trans_bus_no,'0123456789RP','9999999999RP') <> '999999999RP9999' THEN
433 hr_utility.trace('No Business Number Entereed ');
434 hr_utility.set_message(801,'PAY_74154_INCORRECT_BN');
435 hr_utility.set_message_token('GRE_NAME',l_trans_name);
436 pay_core_utils.push_message(801,'PAY_74154_INCORRECT_BN','P');
437 pay_core_utils.push_token('GRE_NAME',l_trans_name);
438 hr_utility.raise_error;
439 RETURN '1';
440 END IF;
441
442 IF l_acc_info_flag = 'N' THEN
443 hr_utility.trace('No Accounting Contact info present');
444 hr_utility.set_message(801,'PAY_74157_INCORRECT_ACNT_INFO');
445 hr_utility.set_message_token('GRE_NAME',l_trans_name);
446 pay_core_utils.push_message(801,'PAY_74157_INCORRECT_ACNT_INFO','P');
447 pay_core_utils.push_token('GRE_NAME',l_trans_name);
448 hr_utility.raise_error;
449 RETURN '1';
450 END IF;
451 END IF;
452 END LOOP;
453 RETURN '0';
454 END validate_gre_data;
455 /**************************************************************************/
456 FUNCTION get_arch_val( p_context_id IN NUMBER,
457 p_user_name IN VARCHAR2)
458 RETURN VARCHAR2 IS
459
460 CURSOR cur_archive (b_context_id IN NUMBER,
461 b_user_name VARCHAR2) IS
462 SELECT fai.value
463 FROM ff_archive_items fai,
464 ff_database_items fdi
465 WHERE fai.user_entity_id = fdi.user_entity_id
466 AND fai.context1 = b_context_id
467 AND fdi.user_name = b_user_name;
468
469 l_return ff_archive_items.value%TYPE;
470
471 BEGIN
472 OPEN cur_archive(p_context_id,p_user_name);
473 FETCH cur_archive INTO l_return;
474 CLOSE cur_archive;
475 RETURN (l_return);
476 END get_arch_val;
477 /**************************************************************************/
478 -- Name : get_report_parameters
479
480 -----------------------------------------------------------------------------
481 --
482 -- Purpose
483 -- The procedure gets the 'parameter' for which the report is being
484 -- run i.e., the period, state and business organization.
485 --
486 -- Arguments
487 -- p_pactid Payroll_action_id passed from pyugen process
488 -- p_legislative_parameters Legislative parameters of the report
489 -- p_year_end End date of the period
490 -- p_report_type Type of report being run T4_CANCEL_MAG
491 -- p_business_group_id Business group for which the report is being run
492
493 ----------------------------------------------------------------------------
494 PROCEDURE get_report_parameters(
495 p_pactid IN NUMBER,
496 p_legislative_parameters IN OUT NOCOPY VARCHAR2,
497 p_year_end IN OUT NOCOPY DATE,
498 p_report_type IN OUT NOCOPY VARCHAR2,
499 p_business_group_id IN OUT NOCOPY NUMBER) IS
500 BEGIN
501 SELECT ppa.legislative_parameters,
502 ppa.effective_date,
503 ppa.business_group_id,
504 ppa.report_type
505 INTO p_legislative_parameters,
506 p_year_end,
507 p_business_group_id,
508 p_report_type
509 FROM pay_payroll_actions ppa
510 WHERE payroll_action_id = p_pactid;
511 END get_report_parameters;
512 /**************************************************************************/
513 FUNCTION get_parameter(name IN VARCHAR2,
514 parameter_list IN VARCHAR2)
515 RETURN VARCHAR2 IS
516 start_ptr NUMBER;
517 end_ptr NUMBER;
518 token_val pay_payroll_actions.legislative_parameters%TYPE;
519 par_value pay_payroll_actions.legislative_parameters%TYPE;
520 BEGIN
521 --
522 token_val := name||'=';
523 --
524 start_ptr := instr(parameter_list, token_val) + length(token_val);
525 end_ptr := instr(parameter_list, ' ',start_ptr);
526 --
527 /* if there is no spaces use then length of the string */
528 IF end_ptr = 0 THEN
529 end_ptr := length(parameter_list)+1;
530 END IF;
531 --
532 /* Did we find the token */
533 IF instr(parameter_list, token_val) = 0 THEN
534 par_value := NULL;
535 ELSE
536 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
537 END IF;
538 --
539 RETURN par_value;
540 --
541 END get_parameter;
542 /**************************************************************************/
543 FUNCTION convert_2_xml(p_data IN VARCHAR2,
544 p_tag IN VARCHAR2,
545 p_datatype IN CHAR DEFAULT 'T',
546 p_format IN VARCHAR2 DEFAULT NULL,
547 p_null_allowed IN VARCHAR2 DEFAULT 'N' )
548 RETURN VARCHAR2 IS
549
550 l_data VARCHAR2(4000);
551 l_output VARCHAR2(4000);
552 EOL VARCHAR2(5);
553 BEGIN
554 /* if p_data is null then
555 return null;
556 end if; */
557 SELECT
558 fnd_global.local_chr(13) || fnd_global.local_chr(10)
559 INTO EOL
560 FROM dual;
561 IF p_null_allowed = 'N'
562 AND (TRIM(p_data) IS NULL
563 OR (p_datatype IN ('N','C')
564 AND to_number(p_data) = 0)) THEN
565 RETURN ' ';
566 END IF;
567 l_data := trim(p_data);
568 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
569 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
570 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
571 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
572 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
573 --------------------------------------------------------
574 --- P_Datatype: T = Text, N = Number, C=Currency, D=Date
575 --------------------------------------------------------
576 --hr_utility.trace('l_data='||l_data);
577 IF p_datatype = 'T' OR p_datatype = 'D' THEN
578 l_output := '<' || trim(p_tag) || '>' || trim(l_data)
579 || '</' || trim(p_tag) || '>'||EOL;
580 -- hr_utility.trace('l_output='||l_output);
581 ELSIF p_datatype = 'N' OR p_datatype = 'C' THEN
582 IF TRIM(p_format) IS NOT NULL THEN
583 SELECT to_char(to_number(p_data), p_format)
584 INTO l_data FROM dual;
585 ELSIF p_datatype = 'C' THEN -- Currency should be two decimal places
586 SELECT to_char(to_number(p_data), '99999999999999999999999999999999999990.99')
587 INTO l_data FROM dual;
588 END IF;
589 l_output := '<' || trim(p_tag) || '>' || trim(l_data)
590 || '</' || trim(p_tag) || '>'||EOL;
591 ELSIF p_datatype = 'Z' then
592 if l_data like '%._' then
593 l_data := l_data||'0';
594 elsif l_data like '%.__' then
595 l_data := l_data;
596 else l_data := l_data||'.00';
597 end if;
598 l_output := '<' || trim(p_tag) || '>' || trim(l_data) || '</' || trim(p_tag) || '>';
599 END IF;
600 --hr_utility.trace('l_output='||l_output);
601 RETURN l_output;
602 END;
603 /**************************************************************************/
604 FUNCTION get_t4_pp_regno(p_pactid IN NUMBER,
605 p_tax_unit_id IN NUMBER,
606 p_pp_regno1 OUT NOCOPY VARCHAR2,
607 p_pp_regno2 OUT NOCOPY VARCHAR2,
608 p_pp_regno3 OUT NOCOPY VARCHAR2)
609 RETURN VARCHAR2 IS
610
611 CURSOR c_get_reg_no(cp_pact_id IN NUMBER,
612 cp_tax_unit_id IN NUMBER) IS
613 SELECT pai.action_information4 ppreg_no
614 FROM pay_action_information pai,pay_payroll_actions ppa
615 WHERE pai.action_context_id = cp_pact_id
616 AND pai.tax_unit_id = cp_tax_unit_id
617 AND ppa.payroll_action_id = pai.action_context_id
618 AND pai.effective_date = ppa.effective_date
619 AND pai.action_information_category = 'CAEOY PENSION PLAN INFO'
620 ORDER BY 1 DESC;
621
622 lv_pp_regno1 pay_action_information.action_information4%TYPE := ' ';
623 lv_pp_regno2 pay_action_information.action_information4%TYPE := ' ';
624 lv_pp_regno3 pay_action_information.action_information4%TYPE := ' ';
625 lv_pp_regno pay_action_information.action_information4%TYPE;
626
627 BEGIN
628 OPEN c_get_reg_no(p_pactid,p_tax_unit_id);
629 LOOP
630 FETCH c_get_reg_no INTO lv_pp_regno;
631 EXIT WHEN c_get_reg_no%NOTFOUND;
632
633 IF c_get_reg_no%ROWCOUNT = 1 THEN
634 lv_pp_regno1 := lv_pp_regno;
635 ELSIF c_get_reg_no%ROWCOUNT = 2 THEN
636 lv_pp_regno2 := lv_pp_regno;
637 ELSIF c_get_reg_no%ROWCOUNT = 3 THEN
638 lv_pp_regno3 := lv_pp_regno;
639 END IF;
640
641 IF c_get_reg_no%ROWCOUNT > 3 THEN
642 EXIT;
643 END IF;
644
645 END LOOP;
646 CLOSE c_get_reg_no;
647
648 p_pp_regno1 := lv_pp_regno1;
649 p_pp_regno2 := lv_pp_regno2;
650 p_pp_regno3 := lv_pp_regno3;
651 RETURN '1';
652 END get_t4_pp_regno;
653 /*****************************************************************************/
654
655 PROCEDURE t4_cancel_mag_transmitter IS
656 BEGIN
657 DECLARE
658
659 l_final_xml VARCHAR2(32000);
660 l_context1 ff_archive_items.context1%TYPE;
661 EOL VARCHAR2(5) := fnd_global.local_chr(13)
662 || fnd_global.local_chr(10);
663 l_return ff_archive_items.value%TYPE;
664 l_status BOOLEAN := TRUE;
665 l_field_name VARCHAR2(250);
666
667 TYPE transmitter_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
668 tab_transmitter transmitter_info;
669
670 T619_trnmtr_nbr NUMBER :=1;
671 T619_sbmt_ref_id NUMBER :=2;
672 T619_trnmtr_tcd NUMBER :=3;
673 T619_summ_cnt NUMBER :=4;
674 T619_l1_nm NUMBER :=5;
675 T619_l2_nm NUMBER :=6;
676 T619_addr_l1_txt NUMBER :=7;
677 T619_addr_l2_txt NUMBER :=8;
678 T619_cty_nm NUMBER :=9;
679 T619_prov_cd NUMBER :=10;
680 T619_cntry_cd NUMBER :=11;
681 T619_pstl_cd NUMBER :=12;
682 T619_cntc_nm NUMBER :=13;
683 T619_cntc_area_cd NUMBER :=14;
684 T619_cntc_phn_nbr NUMBER :=15;
685 T619_cntc_extn_nbr NUMBER :=16;
686 T619_cntc_email_area NUMBER :=17;
687 T619_lang_cd NUMBER :=18;
688 T619_rpt_tcd NUMBER :=19;
689
690 BEGIN
691 hr_utility.trace('+==========================================================+');
692 hr_utility.trace('XML Transmitter');
693
694 begin
695 select max(paa1.payroll_action_id)
696 into l_context1
697 from pay_assignment_actions paa,
698 pay_assignment_actions paa1,
699 pay_action_interlocks int,
700 pay_payroll_actions ppa
701 where int.locking_action_id = paa.assignment_action_id
702 and paa1.assignment_action_id = int.locked_action_id
703 and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
704 and ppa.payroll_action_id = paa1.payroll_action_id
705 and ppa.report_type in ('T4','CAEOY_T4_AMEND_PP');
706 exception
707 when no_data_found then
708 raise;
709 end;
710
711 hr_utility.trace('PAYROLL_ACTION_ID l_context1 ='||l_context1);
712
713 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
714 'CAEOY_TRANSMITTER_NUMBER');
715 IF l_return IS NULL THEN
716 l_field_name := l_field_name || 'CAEOY_TRANSMITTER_NUMBER,';
717 l_status := FALSE;
718 END IF;
719
720 tab_transmitter(T619_trnmtr_nbr)
721 := convert_2_xml(nvl(l_return,' '),'trnmtr_nbr');
722 ----
723 /* # 11074583
724 l_return := pay_magtape_generic.get_parameter_value('SBMT_REF_ID');*/
725 l_return := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
726 tab_transmitter(T619_sbmt_ref_id)
727 := convert_2_xml(nvl(l_return,' '),'sbmt_ref_id');
728 ----
729 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
730 'CAEOY_TRANSMITTER_TYPE_INDICATOR');
731 IF l_return IS NULL THEN
732 l_field_name := l_field_name || 'CAEOY_TRANSMITTER_TYPE_INDICATOR,';
733 l_status := FALSE;
734 END IF;
735 tab_transmitter(T619_trnmtr_tcd)
736 := convert_2_xml(l_return,'trnmtr_tcd');
737 ----
738 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
739 'CAEOY_T4_TOTAL_NO_SUMMARY_COUNT');
740 tab_transmitter(T619_summ_cnt)
741 := convert_2_xml(l_return,'summ_cnt');
742 ----
743 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
744 'CAEOY_TRANSMITTER_NAME');
745 tab_transmitter(T619_l1_nm)
746 := convert_2_xml
747 (upper(rpad(substr(l_return,1,30),30)) ,'l1_nm');
748 tab_transmitter(T619_l2_nm)
749 := convert_2_xml
750 (upper(rpad(substr(l_return,31,30),30)) ,'l2_nm');
751 ----
752 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
753 'CAEOY_TRANSMITTER_ADDRESS_LINE1');
754 tab_transmitter(T619_addr_l1_txt)
755 := convert_2_xml(substr(l_return,1,30),'addr_l1_txt');
756 ----
757 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
758 'CAEOY_TRANSMITTER_ADDRESS_LINE2');
759 tab_transmitter(T619_addr_l2_txt)
760 := convert_2_xml(substr(l_return,1,30),'addr_l2_txt');
761 ----
762 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
763 'CAEOY_TRANSMITTER_CITY');
764 tab_transmitter(T619_cty_nm)
765 := convert_2_xml(substr(l_return,1,28),'cty_nm');
766 ----
767 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
768 'CAEOY_TRANSMITTER_PROVINCE');
769 tab_transmitter(T619_prov_cd)
770 := convert_2_xml(l_return,'prov_cd');
771 ----
772 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
773 'CAEOY_TRANSMITTER_COUNTRY');
774 IF l_return IS NULL THEN
775 tab_transmitter(T619_cntry_cd) := NULL;
776 ELSIF l_return='CA' THEN
777 tab_transmitter(T619_cntry_cd) := convert_2_xml('CAN', 'cntry_cd');
778 ELSIF l_return='US' THEN
779 tab_transmitter(T619_cntry_cd) := convert_2_xml('USA', 'cntry_cd');
780 END IF;
781 ----
782 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
783 'CAEOY_TRANSMITTER_POSTAL_CODE');
784 tab_transmitter(T619_pstl_cd) := convert_2_xml(substr(REPLACE(l_return,' ',''),1,10), 'pstl_cd');
785 ----
786 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
787 'CAEOY_TECHNICAL_CONTACT_NAME');
788 IF l_return IS NULL THEN
789 l_field_name := l_field_name || 'CAEOY_TECHNICAL_CONTACT_NAME,';
790 l_status := FALSE;
791 END IF;
792 tab_transmitter(T619_cntc_nm)
793 := convert_2_xml(substr(l_return,1,22),'cntc_nm');
794
795 ----
796 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
797 'CAEOY_TECHNICAL_CONTACT_AREA_CODE');
798 tab_transmitter(T619_cntc_area_cd)
799 := convert_2_xml(substr(l_return,1,3),'cntc_area_cd');
800 ----
801 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
802 'CAEOY_TECHNICAL_CONTACT_PHONE');
803 IF l_return IS NULL THEN
804 tab_transmitter(T619_cntc_phn_nbr) := NULL;
805 ELSE
806 l_return := REPLACE(REPLACE(l_return, ' '), '-');
807 l_return := upper(lpad(substr(l_return, 1, 7), 7,'0'));
808 tab_transmitter(T619_cntc_phn_nbr)
809 := convert_2_xml(substr(l_return,1,3)||'-'||
810 substr(l_return,4,4),
811 'cntc_phn_nbr');
812 END IF;
813 ----
814 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
815 'CAEOY_TECHNICAL_CONTACT_EXTN');
816 IF l_return IS NULL THEN
817 tab_transmitter(T619_cntc_extn_nbr) := NULL;
818 ELSE
819 tab_transmitter(T619_cntc_extn_nbr)
820 := convert_2_xml(substr(l_return,1,5),
821 'cntc_extn_nbr');
822 END IF;
823 ----
824 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
825 'CAEOY_TECHNICAL_CONTACT_EMAIL');
826 IF l_return IS NULL THEN
827 tab_transmitter(T619_cntc_email_area) := NULL;
828 l_field_name := l_field_name || 'CAEOY_TECHNICAL_CONTACT_EMAIL,';
829 l_status := FALSE;
830 ELSE
831 tab_transmitter(T619_cntc_email_area)
832 := convert_2_xml(substr(l_return,1,60),
833 'cntc_email_area');
834 END IF;
835 ----
836 tab_transmitter(T619_rpt_tcd)
837 := convert_2_xml('A','rpt_tcd'); -- A -> Cancel
838 ----
839 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
840 'CAEOY_TECHNICAL_CONTACT_LANGUAGE');
841 tab_transmitter(T619_lang_cd)
842 := convert_2_xml(l_return,'lang_cd');
843 ----
844 IF l_status = FALSE THEN
845 hr_utility.set_location('Error in T4 Transmitter record',10);
846 hr_utility.set_location('Error in the following fields: ' || l_field_name,10);
847 hr_utility.raise_error;
848 END IF;
849 ----
850 l_final_xml := '<Submission>' || EOL
851 || '<T619>'|| EOL||
852 tab_transmitter(T619_sbmt_ref_id)||
853 tab_transmitter(T619_rpt_tcd)||
854 tab_transmitter(T619_trnmtr_nbr)||
855 tab_transmitter(T619_trnmtr_tcd)||
856 tab_transmitter(T619_summ_cnt)||
857 tab_transmitter(T619_lang_cd)||
858 '<TRNMTR_NM>'|| EOL||
859 tab_transmitter(T619_l1_nm)||
860 tab_transmitter(T619_l2_nm)||
861 '</TRNMTR_NM>' || EOL || '<TRNMTR_ADDR>' ||
862 tab_transmitter(T619_addr_l1_txt)||
863 tab_transmitter(T619_addr_l2_txt)||
864 tab_transmitter(T619_cty_nm)||
865 tab_transmitter(T619_prov_cd)||
866 tab_transmitter(T619_cntry_cd)||
867 tab_transmitter(T619_pstl_cd)||
868 '</TRNMTR_ADDR>' || EOL||
869 '<CNTC>' || EOL||
870 tab_transmitter(T619_cntc_nm)||
871 tab_transmitter(T619_cntc_area_cd)||
872 tab_transmitter(T619_cntc_phn_nbr)||
873 tab_transmitter(T619_cntc_extn_nbr)||
874 tab_transmitter(T619_cntc_email_area)||
875 '</CNTC>' || EOL||
876 '</T619>' || EOL;
877 hr_utility.trace(l_final_xml);
878 pay_core_files.write_to_magtape_lob(l_final_xml);
879 hr_utility.trace('X==========================================================X');
880 END;
881 END t4_cancel_mag_transmitter;
882 /*****************************************************************************/
883
884 PROCEDURE end_of_file is
885 BEGIN
886 DECLARE
887 l_final_xml_string VARCHAR2(32000);
888 BEGIN
889 hr_utility.trace('+==========================================================+');
890 hr_utility.trace('Entering end_of_file');
891 l_final_xml_string := '</Submission>';
892
893 hr_utility.trace(l_final_xml_string );
894 pay_core_files.write_to_magtape_lob(l_final_xml_string);
895 hr_utility.trace('Exiting end_of_file');
896 hr_utility.trace('X==========================================================X');
897 END;
898 END end_of_file;
899 /*****************************************************************************/
900
901 PROCEDURE t4_cancel_employer_start IS
902 BEGIN
903 DECLARE
904 l_final_xml_string VARCHAR2(32000);
905 EOL VARCHAR2(5) := fnd_global.local_chr(13)
906 || fnd_global.local_chr(10);
907
908 BEGIN
909 hr_utility.trace('+==========================================================+');
910 hr_utility.trace('Entering t4_cancel_employer_start');
911 l_final_xml_string := '<Return>'||EOL||'<T4>'||EOL;
912 hr_utility.trace(l_final_xml_string);
913 pay_core_files.write_to_magtape_lob(l_final_xml_string);
914 hr_utility.trace('Exiting t4_cancel_employer_start');
915 hr_utility.trace('X==========================================================X');
916 END;
917 END t4_cancel_employer_start;
918
919 /*****************************************************************************/
920 PROCEDURE t4_cancel_employer_record is
921 BEGIN
922 DECLARE
923 l_final_xml_string VARCHAR2(32000);
924 l_context1 ff_archive_items.context1%TYPE;
925 l_return ff_archive_items.value%TYPE;
926 EOL VARCHAR2(5) := fnd_global.local_chr(13) || fnd_global.local_chr(10);
927 l_transfer_pact_id VARCHAR2(15);
928 l_transfer_tax_unit_id VARCHAR2(15);
929 l_transfer_ei_er_rate VARCHAR2(15);
930 lv_ppreg_no1 pay_action_information.action_information4%TYPE;
931 lv_ppreg_no2 pay_action_information.action_information4%TYPE;
932 lv_ppreg_no3 pay_action_information.action_information4%TYPE;
933
934 TYPE employer_info IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
935 tab_employer employer_info;
936 EIRate NUMBER :=100;
937 T4Summary_bn NUMBER :=1;
938 T4Summary_l1_nm NUMBER :=2;
939 T4Summary_l2_nm NUMBER :=3;
940 T4Summary_l3_nm NUMBER :=4;
941 T4Summary_addr_l1_txt NUMBER :=5;
942 T4Summary_addr_l2_txt NUMBER :=6;
943 T4Summary_cty_nm NUMBER :=7;
944 T4Summary_prov_cd NUMBER :=8;
945 T4Summary_cntry_cd NUMBER :=9;
946 T4Summary_pstl_cd NUMBER :=10;
947 T4Summary_cntc_nm NUMBER :=11;
948 T4Summary_cntc_area_cd NUMBER :=12;
949 T4Summary_cntc_phn_nbr NUMBER :=13;
950 T4Summary_cntc_extn_nbr NUMBER :=14;
951 T4Summary_tx_yr NUMBER :=15;
952 -- T4Summary_slp_cnt Computed in XSL template
953 T4Summary_rpp_rgst_1_nbr NUMBER :=16;
954 T4Summary_rpp_rgst_2_nbr NUMBER :=17;
955 T4Summary_rpp_rgst_3_nbr NUMBER :=18;
956 T4Summary_pprtr_1_sin NUMBER :=19;
957 T4Summary_pprtr_2_sin NUMBER :=20;
958 T4Summary_rpt_tcd NUMBER :=21;
959
960 BEGIN
961 hr_utility.trace('+==========================================================+');
962 hr_utility.trace('Entering t4_cancel_employer_record');
963 l_context1 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
964 l_transfer_pact_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
965 l_transfer_tax_unit_id := pay_magtape_generic.get_parameter_value('TRANSMITTER_GRE');
966 l_transfer_ei_er_rate := pay_magtape_generic.get_parameter_value('TRANSFER_EI_ER_RATE');
967
968 hr_utility.trace('PAYROLL_ACTION_ID l_context1 = ' ||l_context1);
969 hr_utility.trace('TRANSFER_PAYROLL_ACTION_ID l_transfer_pact_id = ' ||l_transfer_pact_id);
970 hr_utility.trace('TRANSMITTER_GRE l_transfer_tax_unit_id = ' ||l_transfer_tax_unit_id);
971 hr_utility.trace('TRANSFER_EI_ER_RATE l_transfer_ei_er_rate = ' ||l_transfer_ei_er_rate);
972 ----
973 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
974 'CAEOY_GRE_EI_RATE');
975 hr_utility.trace('CAEOY_GRE_EI_RATE = ' ||l_return);
976 IF (l_return IS NULL) OR (to_number(l_return) = 0) THEN
977 tab_employer(EIRate):= convert_2_xml(l_transfer_ei_er_rate,'EIRate');
978 ELSE
979 tab_employer(EIRate):= convert_2_xml(l_return,'EIRate');
980 END IF;
981 -- EIRate is an internal tag used for muliplying in XSL for last T4Summary tag tot_empr_eip_amt
982 ----
983 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
984 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
985 tab_employer(T4Summary_bn):= convert_2_xml(l_return,'bn');
986
987 ----
988 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
989 'CAEOY_EMPLOYER_NAME');
990 tab_employer(T4Summary_l1_nm):= convert_2_xml(substr(l_return,1,30),'l1_nm');
991 tab_employer(T4Summary_l2_nm):= convert_2_xml(substr(l_return,31,30),'l2_nm');
992 tab_employer(T4Summary_l3_nm):= convert_2_xml(substr(l_return,61,30),'l3_nm');
993 ----
994 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
995 'CAEOY_EMPLOYER_ADDRESS_LINE1');
996 tab_employer(T4Summary_addr_l1_txt):= convert_2_xml(substr(l_return,1,30),'addr_l1_txt');
997 ----
998 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
999 'CAEOY_EMPLOYER_ADDRESS_LINE2');
1000 tab_employer(T4Summary_addr_l2_txt):= convert_2_xml(substr(l_return,1,30),'addr_l2_txt');
1001 ----
1002 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1003 'CAEOY_EMPLOYER_CITY');
1004 tab_employer(T4Summary_cty_nm):= convert_2_xml(substr(l_return,1,28),'cty_nm');
1005 ----
1006 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1007 'CAEOY_EMPLOYER_PROVINCE');
1008 tab_employer(T4Summary_prov_cd):= convert_2_xml(substr(l_return,1,2),'prov_cd');
1009 ----
1010 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1011 'CAEOY_EMPLOYER_COUNTRY');
1012 IF l_return IS NULL THEN
1013 tab_employer(T4Summary_cntry_cd) := NULL;
1014 ELSIF l_return ='CA' THEN
1015 tab_employer(T4Summary_cntry_cd):= convert_2_xml('CAN','cntry_cd');
1016 ELSIF l_return ='US' THEN
1017 tab_employer(T4Summary_cntry_cd):= convert_2_xml('USA','cntry_cd');
1018 END IF;
1019 ----
1020 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1021 'CAEOY_EMPLOYER_POSTAL_CODE');
1022 tab_employer(T4Summary_pstl_cd) := convert_2_xml(substr(REPLACE(l_return,' ',''),1,10), 'pstl_cd');
1023 ----
1024 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1025 'CAEOY_ACCOUNTING_CONTACT_NAME');
1026 IF l_return IS NULL THEN
1027 tab_employer(T4Summary_cntc_nm):= convert_2_xml(' ', 'cntc_nm');
1028 ELSE
1029 tab_employer(T4Summary_cntc_nm):= convert_2_xml(substr(l_return,1,22)
1030 , 'cntc_nm');
1031 END IF;
1032 ----
1033 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1034 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
1035 IF l_return IS NULL THEN
1036 tab_employer(T4Summary_cntc_area_cd):= convert_2_xml(' ', 'cntc_area_cd');
1037 ELSE
1038 tab_employer(T4Summary_cntc_area_cd):= convert_2_xml(substr(l_return,1,3)
1039 , 'cntc_area_cd');
1040 END IF;
1041 ----
1042 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1043 'CAEOY_ACCOUNTING_CONTACT_PHONE');
1044 IF l_return IS NULL THEN
1045 tab_employer(T4Summary_cntc_phn_nbr):= convert_2_xml('000-0000 ', 'cntc_phn_nbr');
1046 ELSE
1047 l_return := REPLACE(REPLACE(l_return, ' '), '-');
1048 l_return := upper(lpad(substr(l_return, 1, 7), 7,'0'));
1049 tab_employer(T4Summary_cntc_phn_nbr):= convert_2_xml(substr(l_return,1,3)||'-'||substr(l_return,4,4)
1050 , 'cntc_phn_nbr');
1051 END IF;
1052 ----
1053 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1054 'CAEOY_ACCOUNTING_CONTACT_EXTENSION');
1055 IF l_return IS NULL THEN
1056 tab_employer(T4Summary_cntc_extn_nbr):= convert_2_xml(' ', 'cntc_extn_nbr');
1057 ELSE
1058 tab_employer(T4Summary_cntc_extn_nbr):= convert_2_xml(substr(l_return,1,5)
1059 , 'cntc_extn_nbr');
1060 END IF;
1061 ----
1062 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1063 'CAEOY_TAXATION_YEAR');
1064 tab_employer(T4Summary_tx_yr):= convert_2_xml(l_return, 'tx_yr');
1065 ----
1066
1067 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1068 'CAEOY_PROPRIETOR_SIN1');
1069 -- 11074583 start
1070 if trim(l_return) IS NULL then
1071 l_return := lpad('0',9,'0');
1072 end if;
1073 -- 11074583 end
1074 tab_employer(T4Summary_pprtr_1_sin):= convert_2_xml(substr(REPLACE(l_return,' ',''),1,9), 'pprtr_1_sin');
1075 ----
1076 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1077 'CAEOY_PROPRIETOR_SIN2');
1078 -- 11074583 start
1079 if trim(l_return) IS NULL then
1080 l_return := lpad('0',9,'0');
1081 end if;
1082 -- 11074583 end
1083 tab_employer(T4Summary_pprtr_2_sin):= convert_2_xml(substr(REPLACE(l_return,' ',''),1,9), 'pprtr_2_sin');
1084 ----
1085 tab_employer(T4Summary_rpt_tcd) := convert_2_xml('A','rpt_tcd'); --Cancel
1086 ----
1087 l_final_xml_string := '<T4Summary>'||EOL||
1088 tab_employer(EIRate)||
1089 tab_employer(T4Summary_bn)||
1090 '<EMPR_NM>'||EOL||
1091 tab_employer(T4Summary_l1_nm)||
1092 tab_employer(T4Summary_l2_nm)||
1093 tab_employer(T4Summary_l3_nm)||
1094 '</EMPR_NM>' || EOL||
1095 '<EMPR_ADDR>'||EOL||
1096 tab_employer(T4Summary_addr_l1_txt)||
1097 tab_employer(T4Summary_addr_l2_txt)||
1098 tab_employer(T4Summary_cty_nm)||
1099 tab_employer(T4Summary_prov_cd)||
1100 tab_employer(T4Summary_cntry_cd)||
1101 tab_employer(T4Summary_pstl_cd)||
1102 '</EMPR_ADDR>' || EOL||
1103 '<CNTC>'||EOL||
1104 tab_employer(T4Summary_cntc_nm)||
1105 tab_employer(T4Summary_cntc_area_cd)||
1106 tab_employer(T4Summary_cntc_phn_nbr)||
1107 tab_employer(T4Summary_cntc_extn_nbr)||
1108 '</CNTC>' || EOL||
1109 tab_employer(T4Summary_tx_yr)||
1110 '<PPRTR_SIN>'||EOL||
1111 tab_employer(T4Summary_pprtr_1_sin)||
1112 tab_employer(T4Summary_pprtr_2_sin)||
1113 '</PPRTR_SIN>'||EOL||
1114 tab_employer(T4Summary_rpt_tcd)||
1115 '</T4Summary>' || EOL||
1116 '</T4>'||EOL||'</Return>'||EOL;
1117 hr_utility.trace(l_final_xml_string);
1118 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1119 hr_utility.trace('X==========================================================X');
1120 END;
1121 END t4_cancel_employer_record;
1122 /*****************************************************************************/
1123
1124 PROCEDURE t4_cancel_employee_record IS
1125
1126 /*
1127 This procedure has the following sections:
1128 1. Generate T4Slip Tags
1129 This set of tags repeat for eacch T4Slip record.
1130 2. Generate T4_AMT Tags
1131 This set of tags appear only on first T4Slip record.
1132 3. Generate OTH_INFO Tags
1133 Based on the number of Other Information available, It is split into multiples
1134 of 6 and different sets of tags are generated.
1135 4. Generate the Final XML
1136 If there was an error, a single record with T4Slip,T4_AMT and
1137 all OTH_INFO is generated within <Fail> tag along with Error message.
1138 Else Multiple records with T4Slip,T4_AMT(only in first) and sets of
1139 OTH_INFO are generated within <Success> tag.
1140 */
1141 BEGIN
1142 DECLARE
1143 transfer_cpp_max NUMBER :=0;
1144 transfer_ei_max NUMBER :=0;
1145 transfer_ppip_max NUMBER :=0;
1146 lv_ei_display_flag NUMBER :=1;
1147 l_payroll_actid NUMBER;
1148 l_mag_asg_action_id NUMBER;
1149 l_jurisdiction_code ff_archive_items.value%TYPE;
1150 EOL VARCHAR2(5) := fnd_global.local_chr(13)
1151 || fnd_global.local_chr(10);
1152 l_arch_action_id pay_assignment_actions.assignment_action_id%TYPE;
1153 l_arch_pay_actid pay_assignment_actions.payroll_action_id%TYPE;
1154 l_arch_report_type pay_payroll_actions.report_type%TYPE;
1155 -- l_asg_id NUMBER;
1156 l_neg_bal_exists BOOLEAN := FALSE;
1157 l_person_id per_people_f.person_id%TYPE;
1158 l_address_line1 per_addresses.address_line1%TYPE;
1159 l_address_line2 per_addresses.address_line2%TYPE;
1160 l_address_line3 per_addresses.address_line3%TYPE;
1161 l_city per_addresses.town_or_city%TYPE;
1162 l_postal_code per_addresses.postal_code%TYPE;
1163 l_country VARCHAR2(60);
1164 l_emp_province per_addresses.region_1%TYPE;
1165
1166 l_return ff_archive_items.value%TYPE;
1167 l_return1 ff_archive_items.value%TYPE;
1168 l_return2 ff_archive_items.value%TYPE;
1169 l_return_xml VARCHAR2(500);
1170 l_status BOOLEAN := TRUE;
1171 l_name_status BOOLEAN := TRUE;
1172 l_error_message VARCHAR2(500);
1173 l_name_error_message VARCHAR2(500);
1174
1175 l_other_info_count NUMBER := 0;
1176 l_other_info_sets NUMBER := 1;
1177
1178 l_final_xml_string VARCHAR2(32000);
1179 l_T4Slip_xml_string VARCHAR2(32000);
1180 l_T4_AMT_xml_string VARCHAR2(32000);
1181 l_all_final_xml_string VARCHAR2(32000);
1182 -- l_all_oth_info_xml_string VARCHAR2(32000);
1183 lv_year ff_database_items.user_entity_id%TYPE := '0'; -- bug 13087483
1184
1185 TYPE other_info_xml_string IS TABLE OF VARCHAR2(32000) INDEX BY BINARY_INTEGER;
1186 l_other_info_xml_string other_info_xml_string;
1187
1188 TYPE string_table IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
1189 tab_employee_common string_table;
1190 tab_employee string_table;
1191 tab_other_info_dbi string_table;
1192 tab_other_info_tag string_table;
1193
1194 T4Slip_snm NUMBER :=1;
1195 T4Slip_gvn_nm NUMBER :=2;
1196 T4Slip_init NUMBER :=3;
1197 T4Slip_addr_l1_txt NUMBER :=4;
1198 T4Slip_addr_l2_txt NUMBER :=5;
1199 T4Slip_cty_nm NUMBER :=6;
1200 T4Slip_prov_cd NUMBER :=7;
1201 T4Slip_cntry_cd NUMBER :=8;
1202 T4Slip_pstl_cd NUMBER :=9;
1203 T4Slip_sin NUMBER :=10;
1204 T4Slip_empe_nbr NUMBER :=11;
1205 T4Slip_bn NUMBER :=12;
1206 T4Slip_rpp_dpsp_rgst_nbr NUMBER :=13;
1207 T4Slip_cpp_qpp_xmpt_cd NUMBER :=14;
1208 T4Slip_ei_xmpt_cd NUMBER :=15;
1209 T4Slip_prov_pip_xmpt_cd NUMBER :=16;
1210 T4Slip_empt_cd NUMBER :=17;
1211 T4Slip_rpt_tcd NUMBER :=18;
1212 T4Slip_empt_prov_cd NUMBER :=19;
1213
1214 T4_AMT_empt_incamt NUMBER :=101;
1215 T4_AMT_cpp_cntrb_amt NUMBER :=102;
1216 T4_AMT_qpp_cntrb_amt NUMBER :=103;
1217 T4_AMT_empe_eip_amt NUMBER :=104;
1218 T4_AMT_rpp_cntrb_amt NUMBER :=105;
1219 T4_AMT_itx_ddct_amt NUMBER :=106;
1220 T4_AMT_ei_insu_ern_amt NUMBER :=107;
1221 T4_AMT_cpp_qpp_ern_amt NUMBER :=108;
1222 T4_AMT_unn_dues_amt NUMBER :=109;
1223 T4_AMT_chrty_dons_amt NUMBER :=110;
1224 T4_AMT_padj_amt NUMBER :=111;
1225 T4_AMT_prov_pip_amt NUMBER :=112;
1226 T4_AMT_prov_insu_ern_amt NUMBER :=113;
1227 T4_AMT_empr_eip_amt NUMBER :=114; -- Bug 15953807
1228
1229 ----
1230 CURSOR c_get_payroll_asg_actid(p_assg_actid NUMBER) IS
1231 SELECT
1232 paa.assignment_action_id,
1233 paa.payroll_action_id
1234 FROM pay_assignment_actions paa,
1235 pay_payroll_actions ppa,
1236 pay_action_interlocks pai,
1237 pay_action_interlocks pai2
1238 WHERE pai.locking_action_id = p_assg_actid
1239 AND pai.locked_action_id = pai2.locking_action_id
1240 AND pai2.locked_action_id = paa.assignment_action_id
1241 AND ppa.payroll_action_id = paa.payroll_action_id
1242 AND ppa.report_type in ('CAEOY_T4_AMEND_PP','T4')
1243 ORDER BY
1244 paa.assignment_action_id DESC;
1245 ----
1246 CURSOR c_get_person_id(p_mag_asg_action_id NUMBER) IS
1247 SELECT
1248 serial_number
1249 FROM
1250 pay_assignment_actions
1251 WHERE assignment_action_id = p_mag_asg_action_id;
1252 ----
1253 CURSOR c_get_t4_ei_earn_display(p_aa_id IN NUMBER) IS
1254 SELECT
1255 count(1)
1256 FROM
1257 ff_archive_items fai,
1258 ff_database_items fdi
1259 WHERE fai.context1 = p_aa_id
1260 AND fai.user_entity_id = fdi.user_entity_id
1261 AND fdi.user_name='CAEOY_PROVINCE_OF_EMPLOYMENT';
1262 ----
1263 -- modified to get max amount for PPIP EARNINGS also
1264 CURSOR c_get_max_values(p_payroll_actid NUMBER) IS
1265 SELECT
1266 pcli.information_value CPP_MAX,
1267 pcli1.information_value EI_MAX,
1268 pcli2.information_value PPIP_MAX
1269 FROM
1270 pay_payroll_actions ppa,
1271 pay_ca_legislation_info pcli,
1272 pay_ca_legislation_info pcli1,
1273 pay_ca_legislation_info pcli2
1274 WHERE ppa.payroll_action_id= p_payroll_actid
1275 AND pcli.information_type = 'MAX_CPP_EARNINGS'
1276 AND ppa.effective_date BETWEEN pcli.start_date AND pcli.end_date
1277 AND pcli.jurisdiction_code IS NULL
1278 AND pcli1.information_type = 'MAX_EI_EARNINGS'
1279 AND pcli1.jurisdiction_code IS NULL
1280 AND ppa.effective_date BETWEEN pcli1.start_date AND pcli1.end_date
1281 AND pcli2.information_type = 'MAX_PPIP_EARNINGS'
1282 AND pcli2.jurisdiction_code IS NULL
1283 AND ppa.effective_date BETWEEN pcli2.start_date AND pcli2.end_date;
1284 ----
1285 CURSOR c_get_report_type(p_arch_action_id IN NUMBER) IS
1286 SELECT
1287 ppa.report_type
1288 FROM
1289 pay_assignment_actions paa,
1290 pay_payroll_actions ppa
1291 WHERE paa.assignment_action_id = p_arch_action_id
1292 AND paa.payroll_action_id = ppa.payroll_action_id;
1293 ----
1294 CURSOR c_cancel_mag_jurisdictions(p_arch_action_id IN NUMBER) IS
1295 SELECT
1296 fai.value
1297 FROM
1298 ff_archive_items fai,
1299 ff_database_items fdi
1300 WHERE fai.context1 = p_arch_action_id
1301 AND fai.user_entity_id = fdi.user_entity_id
1302 AND fdi.user_name = 'CAEOY_PROVINCE_OF_EMPLOYMENT';
1303 ----
1304 CURSOR c_cancel_amend_jurisdictions(p_arch_action_id IN NUMBER) IS
1305 SELECT
1306 fac.context
1307 FROM
1308 ff_archive_items fai,
1309 ff_database_items fdi,
1310 ff_archive_item_contexts fac,
1311 ff_contexts ffc
1312 WHERE fai.context1 = p_arch_action_id
1313 AND fai.archive_item_id = fac.archive_item_id
1314 AND fai.user_entity_id = fdi.user_entity_id
1315 AND fdi.user_name = 'CAEOY_T4_AMENDMENT_FLAG'
1316 AND fac.context_id = ffc.context_id
1317 AND ffc.context_name = 'JURISDICTION_CODE'
1318 AND fai.value='Y';
1319
1320 BEGIN
1321 hr_utility.trace('+==========================================================+');
1322 hr_utility.trace('Employee Record');
1323 /* DBIs of other info amounts */
1324 tab_other_info_dbi(1) := 'CAEOY_T4_OTHER_INFO_AMOUNT30_PER_JD_GRE_YTD';
1325 tab_other_info_dbi(2) := 'CAEOY_T4_OTHER_INFO_AMOUNT31_PER_JD_GRE_YTD';
1326 tab_other_info_dbi(3) := 'CAEOY_T4_OTHER_INFO_AMOUNT32_PER_JD_GRE_YTD';
1327 tab_other_info_dbi(4) := 'CAEOY_T4_OTHER_INFO_AMOUNT33_PER_JD_GRE_YTD';
1328 tab_other_info_dbi(5) := 'CAEOY_T4_OTHER_INFO_AMOUNT34_PER_JD_GRE_YTD';
1329 tab_other_info_dbi(6) := 'CAEOY_T4_OTHER_INFO_AMOUNT35_PER_JD_GRE_YTD';
1330 tab_other_info_dbi(7) := 'CAEOY_T4_OTHER_INFO_AMOUNT36_PER_JD_GRE_YTD';
1331 tab_other_info_dbi(8) := 'CAEOY_T4_OTHER_INFO_AMOUNT37_PER_JD_GRE_YTD';
1332 tab_other_info_dbi(9) := 'CAEOY_T4_OTHER_INFO_AMOUNT38_PER_JD_GRE_YTD';
1333 tab_other_info_dbi(10) := 'CAEOY_T4_OTHER_INFO_AMOUNT39_PER_JD_GRE_YTD';
1334 tab_other_info_dbi(11) := 'CAEOY_T4_OTHER_INFO_AMOUNT40_PER_JD_GRE_YTD';
1335 tab_other_info_dbi(12) := 'CAEOY_T4_OTHER_INFO_AMOUNT41_PER_JD_GRE_YTD';
1336 tab_other_info_dbi(13) := 'CAEOY_T4_OTHER_INFO_AMOUNT42_PER_JD_GRE_YTD';
1337 tab_other_info_dbi(14) := 'CAEOY_T4_OTHER_INFO_AMOUNT43_PER_JD_GRE_YTD';
1338 /* commented for bug 13087483*/
1339 -- tab_other_info_dbi(15) := 'CAEOY_T4_OTHER_INFO_AMOUNT53_PER_JD_GRE_YTD';
1340 tab_other_info_dbi(15) := 'CAEOY_T4_OTHER_INFO_AMOUNT66_PER_JD_GRE_YTD';
1341 tab_other_info_dbi(16) := 'CAEOY_T4_OTHER_INFO_AMOUNT67_PER_JD_GRE_YTD';
1342 tab_other_info_dbi(17) := 'CAEOY_T4_OTHER_INFO_AMOUNT68_PER_JD_GRE_YTD';
1343 tab_other_info_dbi(18) := 'CAEOY_T4_OTHER_INFO_AMOUNT69_PER_JD_GRE_YTD';
1344 tab_other_info_dbi(19) := 'CAEOY_T4_OTHER_INFO_AMOUNT70_PER_JD_GRE_YTD';
1345 tab_other_info_dbi(20) := 'CAEOY_T4_OTHER_INFO_AMOUNT71_PER_JD_GRE_YTD';
1346 tab_other_info_dbi(21) := 'CAEOY_T4_OTHER_INFO_AMOUNT72_PER_JD_GRE_YTD';
1347 tab_other_info_dbi(22) := 'CAEOY_T4_OTHER_INFO_AMOUNT73_PER_JD_GRE_YTD';
1348 tab_other_info_dbi(23) := 'CAEOY_T4_OTHER_INFO_AMOUNT74_PER_JD_GRE_YTD';
1349 tab_other_info_dbi(24) := 'CAEOY_T4_OTHER_INFO_AMOUNT75_PER_JD_GRE_YTD';
1350 tab_other_info_dbi(25) := 'CAEOY_T4_OTHER_INFO_AMOUNT77_PER_JD_GRE_YTD';
1351 tab_other_info_dbi(26) := 'CAEOY_T4_OTHER_INFO_AMOUNT78_PER_JD_GRE_YTD';
1352 tab_other_info_dbi(27) := 'CAEOY_T4_OTHER_INFO_AMOUNT79_PER_JD_GRE_YTD';
1353 tab_other_info_dbi(28) := 'CAEOY_T4_OTHER_INFO_AMOUNT80_PER_JD_GRE_YTD';
1354 tab_other_info_dbi(29) := 'CAEOY_T4_OTHER_INFO_AMOUNT81_PER_JD_GRE_YTD';
1355 tab_other_info_dbi(30) := 'CAEOY_T4_OTHER_INFO_AMOUNT82_PER_JD_GRE_YTD';
1356 tab_other_info_dbi(31) := 'CAEOY_T4_OTHER_INFO_AMOUNT83_PER_JD_GRE_YTD';
1357 tab_other_info_dbi(32) := 'CAEOY_T4_OTHER_INFO_AMOUNT84_PER_JD_GRE_YTD';
1358 tab_other_info_dbi(33) := 'CAEOY_T4_OTHER_INFO_AMOUNT85_PER_JD_GRE_YTD';
1359 tab_other_info_dbi(34) := 'CAEOY_T4_OTHER_INFO_AMOUNT86_PER_JD_GRE_YTD';
1360 -- Modification for bug 13087483 starts here.
1361 tab_other_info_dbi(35) := 'CAEOY_T4_OTHER_INFO_AMOUNT87_PER_JD_GRE_YTD';
1362 -- Modification for bug 13087483 ends here.
1363
1364 /* XML Tags for corresponding other info amounts*/
1365 tab_other_info_tag(1) := 'hm_brd_lodg_amt';
1366 tab_other_info_tag(2) := 'spcl_wrk_site_amt';
1367 tab_other_info_tag(3) := 'prscb_zn_trvl_amt';
1368 tab_other_info_tag(4) := 'med_trvl_amt';
1369 tab_other_info_tag(5) := 'prsnl_vhcl_amt';
1370 tab_other_info_tag(6) := 'rsn_per_km_amt';
1371 tab_other_info_tag(7) := 'low_int_loan_amt';
1372 tab_other_info_tag(8) := 'empe_hm_loan_amt';
1373 tab_other_info_tag(9) := 'sob_a00_feb_amt';
1374 tab_other_info_tag(10) := 'sod_d_a00_feb_amt';
1375 tab_other_info_tag(11) := 'oth_tx_ben_amt';
1376 tab_other_info_tag(12) := 'sod_d1_a00_feb_amt';
1377 tab_other_info_tag(13) := 'empt_cmsn_amt';
1378 tab_other_info_tag(14) := 'cfppa_amt';
1379 /* commented for bug 13087483*/
1380 -- tab_other_info_tag(15) := 'dfr_sob_amt';
1381 tab_other_info_tag(15) := 'elg_rtir_amt';
1382 tab_other_info_tag(16) := 'nelg_rtir_amt';
1383 tab_other_info_tag(17) := 'indn_elg_rtir_amt';
1384 tab_other_info_tag(18) := 'indn_nelg_rtir_amt';
1385 tab_other_info_tag(19) := 'mun_ofcr_examt';
1386 tab_other_info_tag(20) := 'indn_empe_amt';
1387 tab_other_info_tag(21) := 'oc_incamt';
1388 tab_other_info_tag(22) := 'oc_dy_cnt';
1389 tab_other_info_tag(23) := 'pr_90_cntrbr_amt';
1390 tab_other_info_tag(24) := 'pr_90_ncntrbr_amt';
1391 tab_other_info_tag(25) := 'cmpn_rpay_empr_amt';
1392 tab_other_info_tag(26) := 'fish_gro_ern_amt';
1393 tab_other_info_tag(27) := 'fish_net_ptnr_amt';
1394 tab_other_info_tag(28) := 'fish_shr_prsn_amt';
1395 tab_other_info_tag(29) := 'plcmt_emp_agcy_amt';
1396 tab_other_info_tag(30) := 'drvr_taxis_oth_amt';
1397 tab_other_info_tag(31) := 'brbr_hrdrssr_amt';
1398 tab_other_info_tag(32) := 'pub_trnst_pass_amt';
1399 tab_other_info_tag(33) := 'epaid_hlth_pln_amt';
1400 tab_other_info_tag(34) := 'stok_opt_csh_out_eamt';
1401 tab_other_info_tag(35) := 'vlntr_firefighter_xmpt_amt'; -- for bug 13087483
1402 --
1403 l_payroll_actid := to_number(pay_magtape_generic.get_parameter_value
1404 ('TRANSFER_PAYROLL_ACTION_ID'));
1405 hr_utility.trace('T4_CANCEL_MAG Payroll action ='||l_payroll_actid);
1406 --
1407 l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value
1408 ('TRANSFER_ACT_ID'));
1409 hr_utility.trace('T4_CANCEL_MAG Assignment action ='|| to_char(l_mag_asg_action_id));
1410
1411 --
1412 OPEN c_get_payroll_asg_actid(l_mag_asg_action_id);
1413 FETCH c_get_payroll_asg_actid INTO l_arch_action_id, l_arch_pay_actid;
1414 CLOSE c_get_payroll_asg_actid;
1415 hr_utility.trace('Archiver Assignment action='|| l_arch_action_id);
1416 hr_utility.trace('Archiver Payroll action='|| l_arch_pay_actid);
1417 --
1418 OPEN c_get_max_values(l_payroll_actid);
1419 FETCH c_get_max_values INTO transfer_cpp_max, transfer_ei_max, transfer_ppip_max;
1420 CLOSE c_get_max_values;
1421 hr_utility.trace('TRANSFER_CPP_MAX = '||transfer_cpp_max);
1422 hr_utility.trace('TRANSFER_EI_MAX = '||transfer_ei_max);
1423 hr_utility.trace('TRANSFER_PPIP_MAX = '||transfer_ppip_max);
1424 --
1425 lv_ei_display_flag := 1;
1426 OPEN c_get_t4_ei_earn_display(l_arch_action_id);
1427 FETCH c_get_t4_ei_earn_display INTO lv_ei_display_flag;
1428 CLOSE c_get_t4_ei_earn_display;
1429 hr_utility.trace('lv_ei_display_flag ='||lv_ei_display_flag);
1430 /*Removed this as this is not required.
1431 l_arch_pay_actid
1432 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
1433 */
1434 ----
1435 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1436 ,'CAEOY_T4_NEGATIVE_BALANCE_EXISTS');
1437
1438 IF(l_return='Y') THEN
1439 l_status := FALSE;
1440 l_error_message := hr_general.decode_lookup('PAY_CA_MAG_EXCEPTIONS','NEG');
1441 END IF;
1442 ----
1443 /*****************************************************************************/
1444 /* T4Slip Tags begin*/
1445 OPEN c_get_person_id(l_mag_asg_action_id);
1446 FETCH c_get_person_id INTO l_person_id;
1447 CLOSE c_get_person_id;
1448 hr_utility.trace('Person ID ='||l_person_id);
1449 ----1
1450 l_return := pay_ca_emp_address_dtls.get_emp_address(l_person_id,
1451 l_address_line1,
1452 l_address_line2,
1453 l_address_line3,
1454 l_city,
1455 l_postal_code,
1456 l_country,
1457 l_emp_province);
1458 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1459 ,'CAEOY_EMPLOYEE_LAST_NAME');
1460 IF l_return IS NULL THEN
1461 l_name_status := FALSE;
1462 l_name_error_message := l_name_error_message||'Blank Last Name ';
1463 END IF;
1464 tab_employee_common(T4Slip_snm) :=convert_2_xml(substr(l_return,1,20),'snm');
1465
1466 ----2
1467 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1468 ,'CAEOY_EMPLOYEE_FIRST_NAME');
1469 -- bug 13105140
1470 /* IF l_return IS NULL THEN
1471 l_name_status := FALSE;
1472 l_name_error_message := l_name_error_message||'Blank First Name ';
1473 END IF; */
1474
1475 tab_employee_common(T4Slip_gvn_nm) :=convert_2_xml(substr(l_return,1,12),'gvn_nm');
1476
1477 ----3
1478 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1479 ,'CAEOY_EMPLOYEE_INITIAL');
1480 tab_employee_common(T4Slip_init) :=convert_2_xml(substr(l_return,1,1),'init');
1481
1482 ----4,5
1483 tab_employee_common(T4Slip_addr_l1_txt) := convert_2_xml(SUBSTR(ltrim(rtrim(l_address_line1)),1,30), 'addr_l1_txt');
1484 tab_employee_common(T4Slip_addr_l2_txt) := convert_2_xml(SUBSTR(ltrim(rtrim(l_address_line2||' '||l_address_line3)),1,30),
1485 'addr_l2_txt');
1486
1487 ----6
1488 tab_employee_common(T4Slip_cty_nm) := convert_2_xml(substr(l_city,1,28), 'cty_nm');
1489
1490 ----7
1491 tab_employee_common(T4Slip_prov_cd) := convert_2_xml(l_emp_province, 'prov_cd');
1492 ----8
1493 IF (upper(l_country) = 'CA') THEN
1494 tab_employee_common(T4Slip_cntry_cd) := convert_2_xml('CAN', 'cntry_cd');
1495 ELSIF (upper(l_country) = 'US') THEN
1496 tab_employee_common(T4Slip_cntry_cd) := convert_2_xml('USA', 'cntry_cd');
1497 ELSE
1498 tab_employee_common(T4Slip_cntry_cd) := ' ';
1499 END IF;
1500
1501 ----9
1502 tab_employee_common(T4Slip_pstl_cd) := convert_2_xml(substr(REPLACE(l_postal_code,' ',''),1,10), 'pstl_cd');
1503
1504 ----10
1505 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1506 ,'CAEOY_EMPLOYEE_SIN');
1507 IF l_return IS NULL THEN
1508 tab_employee_common(T4Slip_sin) := convert_2_xml('000000000', 'sin');
1509 ELSE
1510 tab_employee_common(T4Slip_sin) := convert_2_xml(substr(l_return,1,9),'sin');
1511 END IF;
1512
1513 ----11
1514
1515 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1516 ,'CAEOY_EMPLOYEE_NUMBER');
1517 tab_employee_common(T4Slip_empe_nbr) :=convert_2_xml(substr(l_return,1,20),'empe_nbr');
1518
1519 ----12
1520 l_return := pay_ca_archive_utils.get_archive_value(l_arch_pay_actid
1521 ,'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
1522 tab_employee_common(T4Slip_bn) :=convert_2_xml(substr(nvl(l_return,' '),1,15),'bn');
1523
1524 ----13
1525 /* l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1526 ,'CAEOY_T4_EMPLOYEE_REGISTRATION_NO');
1527 tab_employee_common(T4Slip_rpp_dpsp_rgst_nbr) :=convert_2_xml(substr(l_return,1,7),'rpp_dpsp_rgst_nbr');
1528 */
1529
1530 ---- Tags above are common for all Jurisdictions and below are Jurisdiction Specific.
1531 OPEN c_get_report_type(l_arch_action_id);
1532 FETCH c_get_report_type INTO l_arch_report_type;
1533 CLOSE c_get_report_type;
1534
1535 IF l_arch_report_type = 'T4' THEN
1536 OPEN c_cancel_mag_jurisdictions(l_arch_action_id);
1537 ELSE
1538 OPEN c_cancel_amend_jurisdictions(l_arch_action_id);
1539 END IF;
1540
1541 LOOP -- Loop through each cancelled jurisdiction of the employee
1542 IF l_arch_report_type = 'T4' THEN
1543 FETCH c_cancel_mag_jurisdictions INTO l_jurisdiction_code;
1544 EXIT WHEN c_cancel_mag_jurisdictions%NOTFOUND;
1545 ELSE
1546 FETCH c_cancel_amend_jurisdictions INTO l_jurisdiction_code;
1547 EXIT WHEN c_cancel_amend_jurisdictions%NOTFOUND;
1548 END IF;
1549 hr_utility.trace('Jurisdiction Code ='|| to_char(l_jurisdiction_code));
1550 l_status := l_name_status;
1551 l_error_message := l_name_error_message;
1552 l_other_info_count := 0;
1553 l_other_info_sets := 1;
1554 l_T4Slip_xml_string := ' ';
1555 l_T4_AMT_xml_string := ' ';
1556 l_other_info_xml_string(1) := ' ';
1557 l_other_info_xml_string(2) := ' ';
1558 l_other_info_xml_string(3) := ' ';
1559 l_other_info_xml_string(4) := ' ';
1560 l_other_info_xml_string(5) := ' ';
1561 l_other_info_xml_string(6) := ' ';
1562 l_final_xml_string:=' ';
1563 tab_employee.DELETE;
1564
1565 -- JURISDICTION_CODE context added for bug 13427161
1566 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id, l_jurisdiction_code,
1567 'JURISDICTION_CODE',
1568 'CAEOY_T4_EMPLOYEE_REGISTRATION_NO');
1569 tab_employee_common(T4Slip_rpp_dpsp_rgst_nbr) :=convert_2_xml(substr(l_return,1,7),'rpp_dpsp_rgst_nbr');
1570
1571 ----
1572 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1573 ,l_jurisdiction_code,'JURISDICTION_CODE'
1574 ,'CAEOY_T4_NEGATIVE_BALANCE_EXISTS');
1575 IF(l_return='Y') THEN
1576 l_status := FALSE;
1577 l_error_message := l_error_message||hr_general.decode_lookup('PAY_CA_MAG_EXCEPTIONS','NEG');
1578 END IF;
1579
1580 ---14
1581 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1582 ,l_jurisdiction_code,'JURISDICTION_CODE'
1583 ,'CAEOY_CPP_QPP_EXEMPT');
1584
1585 IF l_return IS NULL THEN
1586 tab_employee(T4Slip_cpp_qpp_xmpt_cd) := convert_2_xml('0','cpp_qpp_xmpt_cd');
1587 ELSE
1588 tab_employee(T4Slip_cpp_qpp_xmpt_cd) := convert_2_xml('1','cpp_qpp_xmpt_cd');
1589 END IF;
1590
1591 ---15
1592 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1593 ,l_jurisdiction_code,'JURISDICTION_CODE'
1594 ,'CAEOY_EI_EXEMPT');
1595
1596 IF l_return IS NULL THEN
1597 tab_employee(T4Slip_ei_xmpt_cd) := convert_2_xml('0','ei_xmpt_cd');
1598 ELSE
1599 tab_employee(T4Slip_ei_xmpt_cd) := convert_2_xml('1','ei_xmpt_cd');
1600 END IF;
1601
1602 ---16
1603 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1604 ,l_jurisdiction_code,'JURISDICTION_CODE'
1605 ,'CAEOY_PPIP_EXEMPT');
1606
1607 IF l_return IS NULL THEN
1608 tab_employee(T4Slip_prov_pip_xmpt_cd) := convert_2_xml('0','prov_pip_xmpt_cd');
1609 ELSE
1610 tab_employee(T4Slip_prov_pip_xmpt_cd) := convert_2_xml('1','prov_pip_xmpt_cd');
1611 END IF;
1612
1613 ----17
1614 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1615 ,l_jurisdiction_code,'JURISDICTION_CODE'
1616 ,'CAEOY_EMPLOYMENT_CODE');
1617
1618 tab_employee(T4Slip_empt_cd) := convert_2_xml(substr(nvl(l_return,' '),1,2),'empt_cd');
1619
1620 ----18
1621 tab_employee(T4Slip_rpt_tcd) := convert_2_xml('C', 'rpt_tcd'); --Cancellation
1622
1623 ----19
1624 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1625 ,l_jurisdiction_code,'JURISDICTION_CODE'
1626 ,'CAEOY_PROVINCE_OF_EMPLOYMENT');
1627
1628 IF l_return = 'NF' THEN
1629 tab_employee(T4Slip_empt_prov_cd) := convert_2_xml('NL','empt_prov_cd');
1630 ELSE
1631 tab_employee(T4Slip_empt_prov_cd) := convert_2_xml(substr(l_return,1, 2),'empt_prov_cd');
1632 END IF;
1633
1634 ----
1635
1636 l_T4Slip_xml_string := '<EMPE_NM>'||EOL||
1637 tab_employee_common(T4Slip_snm)||
1638 tab_employee_common(T4Slip_gvn_nm)||
1639 tab_employee_common(T4Slip_init)||
1640 '</EMPE_NM>' || EOL||
1641 '<EMPE_ADDR>'||EOL||
1642 tab_employee_common(T4Slip_addr_l1_txt)||
1643 tab_employee_common(T4Slip_addr_l2_txt)||
1644 tab_employee_common(T4Slip_cty_nm)||
1645 tab_employee_common(T4Slip_prov_cd)||
1646 tab_employee_common(T4Slip_cntry_cd)||
1647 tab_employee_common(T4Slip_pstl_cd)||
1648 '</EMPE_ADDR>'||EOL||
1649 tab_employee_common(T4Slip_sin)||
1650 tab_employee_common(T4Slip_empe_nbr)||
1651 tab_employee_common(T4Slip_bn)||
1652 tab_employee_common(T4Slip_rpp_dpsp_rgst_nbr)||
1653 tab_employee(T4Slip_cpp_qpp_xmpt_cd)||
1654 tab_employee(T4Slip_ei_xmpt_cd)||
1655 tab_employee(T4Slip_prov_pip_xmpt_cd)||
1656 tab_employee(T4Slip_empt_cd)||
1657 tab_employee(T4Slip_rpt_tcd)||
1658 tab_employee(T4Slip_empt_prov_cd);
1659
1660 /* T4Slip Tags end */
1661 /*****************************************************************************/
1662 /* T4_AMT Tags begin */
1663
1664 ----1
1665 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1666 ,l_jurisdiction_code,'JURISDICTION_CODE'
1667 ,'CAEOY_GROSS_EARNINGS_PER_JD_GRE_YTD');
1668 l_return2 := l_return; --Used in 7 and 8
1669 tab_employee(T4_AMT_empt_incamt) :=convert_2_xml(nvl(l_return,'0'),'empt_incamt','C');
1670
1671 ----2
1672 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1673 ,l_jurisdiction_code,'JURISDICTION_CODE'
1674 ,'CAEOY_CPP_EE_WITHHELD_PER_JD_GRE_YTD');
1675 tab_employee(T4_AMT_cpp_cntrb_amt) :=convert_2_xml(nvl(l_return,'0'),'cpp_cntrb_amt','C');
1676
1677 ----3
1678 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1679 ,l_jurisdiction_code,'JURISDICTION_CODE'
1680 ,'CAEOY_QPP_EE_WITHHELD_PER_JD_GRE_YTD');
1681 tab_employee(T4_AMT_qpp_cntrb_amt) :=convert_2_xml(nvl(l_return,'0'),'qpp_cntrb_amt','C');
1682
1683 ----4
1684 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1685 ,l_jurisdiction_code,'JURISDICTION_CODE'
1686 ,'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD');
1687 tab_employee(T4_AMT_empe_eip_amt) :=convert_2_xml(nvl(l_return,'0'),'empe_eip_amt','C');
1688
1689 /* Bug 15953807 */
1690 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1691 ,l_jurisdiction_code,'JURISDICTION_CODE'
1692 ,'CAEOY_EI_ER_LIABILITY_PER_JD_GRE_YTD');
1693 tab_employee(T4_AMT_empr_eip_amt) :=convert_2_xml(nvl(l_return,'0'),'empr_eip_amt','C');
1694 /* Bug 15953807 */
1695
1696 ----5
1697 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1698 ,l_jurisdiction_code,'JURISDICTION_CODE'
1699 ,'CAEOY_T4_BOX20_PER_JD_GRE_YTD');
1700 tab_employee(T4_AMT_rpp_cntrb_amt) :=convert_2_xml(nvl(l_return,'0'),'rpp_cntrb_amt','C');
1701
1702 ----6
1703 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1704 ,l_jurisdiction_code,'JURISDICTION_CODE'
1705 ,'CAEOY_FED_WITHHELD_PER_JD_GRE_YTD');
1706 tab_employee(T4_AMT_itx_ddct_amt) :=convert_2_xml(nvl(l_return,'0'),'itx_ddct_amt','C');
1707
1708 ----7
1709 lv_year := pay_ca_archive_utils.get_archive_value(
1710 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),
1711 'CAEOY_TAXATION_YEAR'); -- bug 13087483
1712 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1713 ,l_jurisdiction_code,'JURISDICTION_CODE'
1714 ,'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD');
1715
1716 -- if condition added for bug 13087483
1717 IF to_number(ltrim(rtrim(lv_year))) < 2011 then
1718 IF (to_number(l_return) = to_number(l_return2) OR
1719 to_number(l_return) = to_number(transfer_ei_max) OR
1720 l_return IS NULL) AND lv_ei_display_flag = 1 THEN
1721 tab_employee(T4_AMT_ei_insu_ern_amt) :=convert_2_xml('0','ei_insu_ern_amt','C');
1722 ELSE
1723 tab_employee(T4_AMT_ei_insu_ern_amt) :=convert_2_xml(nvl(l_return,'0'),'ei_insu_ern_amt','C');
1724 END IF;
1725 ELSE
1726 tab_employee(T4_AMT_ei_insu_ern_amt) :=convert_2_xml(nvl(l_return,'0'),'ei_insu_ern_amt','Z');
1727 END IF;
1728
1729 ----8
1730
1731 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1732 ,l_jurisdiction_code,'JURISDICTION_CODE'
1733 ,'CAEOY_CPP_EE_TAXABLE_PER_JD_GRE_YTD');
1734 IF to_number(l_return) = 0 THEN
1735 /* 'CAEOY_QPP_EE_RSUBJECT_PER_JD_GRE_YTD' contains exact value of QPP amount
1736 'CAEOY_QPP_EE_TAXABLE_PER_JD_GRE_YTD' contains exact value of QPP if
1737 it is not more than max limit other wise it contains max limit
1738 */
1739 /*l_return1 := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1740 ,l_jurisdiction_code,'JURISDICTION_CODE'
1741 ,'CAEOY_QPP_EE_RSUBJECT_PER_JD_GRE_YTD');*/
1742
1743 l_return1 := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1744 ,l_jurisdiction_code,'JURISDICTION_CODE'
1745 ,'CAEOY_QPP_EE_TAXABLE_PER_JD_GRE_YTD');
1746 -- if condition added for bug 13087483
1747 IF to_number(ltrim(rtrim(lv_year))) < 2011 then
1748 IF to_number(l_return1) = to_number(l_return2)
1749 OR to_number(l_return1) >= to_number(transfer_cpp_max)
1750 OR l_return1 IS NULL THEN
1751 tab_employee(T4_AMT_cpp_qpp_ern_amt) :=convert_2_xml('0','cpp_qpp_ern_amt','C');
1752 ELSE
1753 tab_employee(T4_AMT_cpp_qpp_ern_amt) :=convert_2_xml(nvl(l_return1,'0'),'cpp_qpp_ern_amt','C');
1754 END IF;
1755 ELSE
1756 tab_employee(T4_AMT_cpp_qpp_ern_amt) :=convert_2_xml(nvl(l_return1,'0'),'cpp_qpp_ern_amt','Z');
1757 END IF;
1758 ELSE
1759 -- if condition added for bug 13087483
1760 IF to_number(ltrim(rtrim(lv_year))) < 2011 then
1761 IF to_number(l_return) = to_number(l_return2)
1762 OR to_number(l_return) >= to_number(transfer_cpp_max)
1763 OR l_return IS NULL THEN
1764 tab_employee(T4_AMT_cpp_qpp_ern_amt) :=convert_2_xml('0','cpp_qpp_ern_amt','C');
1765 ELSE
1766 tab_employee(T4_AMT_cpp_qpp_ern_amt) :=convert_2_xml(nvl(l_return,'0'),'cpp_qpp_ern_amt','C');
1767 END IF;
1768 ELSE
1769 tab_employee(T4_AMT_cpp_qpp_ern_amt) :=convert_2_xml(nvl(l_return,'0'),'cpp_qpp_ern_amt','Z');
1770 END IF;
1771 END IF;
1772
1773 ----9
1774 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1775 ,l_jurisdiction_code,'JURISDICTION_CODE'
1776 ,'CAEOY_T4_BOX44_PER_JD_GRE_YTD');
1777 tab_employee(T4_AMT_unn_dues_amt) :=convert_2_xml(nvl(l_return,'0'),'unn_dues_amt','C');
1778
1779 ----10
1780 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1781 ,l_jurisdiction_code,'JURISDICTION_CODE'
1782 ,'CAEOY_T4_BOX46_PER_JD_GRE_YTD');
1783
1784 tab_employee(T4_AMT_chrty_dons_amt) :=convert_2_xml(nvl(l_return,'0'),'chrty_dons_amt','C');
1785
1786 ----11
1787 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1788 ,l_jurisdiction_code,'JURISDICTION_CODE'
1789 ,'CAEOY_T4_BOX52_PER_JD_GRE_YTD');
1790 tab_employee(T4_AMT_padj_amt) :=convert_2_xml(nvl(l_return,'0'),'padj_amt','C');
1791
1792 ----12
1793 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1794 ,l_jurisdiction_code,'JURISDICTION_CODE'
1795 ,'CAEOY_PPIP_EE_WITHHELD_PER_JD_GRE_YTD');
1796 tab_employee(T4_AMT_prov_pip_amt) :=convert_2_xml(nvl(l_return,'0'),'prov_pip_amt','C');
1797
1798
1799 ----13
1800 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1801 ,l_jurisdiction_code,'JURISDICTION_CODE'
1802 ,'CAEOY_PPIP_EE_TAXABLE_PER_JD_GRE_YTD');
1803 IF to_number(l_return) = to_number(l_return2)
1804 OR to_number(l_return) >= to_number(transfer_ppip_max)
1805 OR l_return IS NULL THEN
1806 tab_employee(T4_AMT_prov_insu_ern_amt) :=convert_2_xml(' ','prov_insu_ern_amt','C');
1807 ELSE
1808 tab_employee(T4_AMT_prov_insu_ern_amt) :=convert_2_xml(nvl(l_return,'0'),'prov_insu_ern_amt','C');
1809 END IF;
1810
1811 ----
1812 l_T4_AMT_xml_string := '<T4_AMT>'||
1813 tab_employee(T4_AMT_empt_incamt)||
1814 tab_employee(T4_AMT_cpp_cntrb_amt)||
1815 tab_employee(T4_AMT_qpp_cntrb_amt)||
1816 tab_employee(T4_AMT_empe_eip_amt)||
1817 tab_employee(T4_AMT_rpp_cntrb_amt)||
1818 tab_employee(T4_AMT_itx_ddct_amt)||
1819 tab_employee(T4_AMT_ei_insu_ern_amt)||
1820 tab_employee(T4_AMT_cpp_qpp_ern_amt)||
1821 tab_employee(T4_AMT_unn_dues_amt)||
1822 tab_employee(T4_AMT_chrty_dons_amt)||
1823 tab_employee(T4_AMT_padj_amt)||
1824 tab_employee(T4_AMT_prov_pip_amt)||
1825 tab_employee(T4_AMT_prov_insu_ern_amt)||
1826 '</T4_AMT>';
1827 /* T4_AMT Tags end */
1828 /*****************************************************************************/
1829 /* OTH_INFO Tags begin */
1830 IF l_status THEN
1831 hr_utility.trace('Status = Success');
1832 ELSE
1833 hr_utility.trace('Status = Fail');
1834 END IF;
1835
1836
1837 FOR i IN 1..tab_other_info_dbi.COUNT LOOP
1838
1839 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1840 ,l_jurisdiction_code,'JURISDICTION_CODE'
1841 ,tab_other_info_dbi(i));
1842
1843 IF nvl(l_return,0)<>0 THEN
1844 -- commented for bug 3891966
1845 -- l_return_xml := convert_2_xml(l_return ,tab_other_info_tag(i),'C');
1846
1847 if(i=22) then
1848 --added lpad and ceil functions for dispalying code 73 in three digit format
1849 -- bug 3891966
1850 l_return_xml := convert_2_xml(lpad(ceil(fnd_number.canonical_to_number(l_return)),3,0),tab_other_info_tag(i),'N');
1851 else
1852 l_return_xml := convert_2_xml(l_return ,tab_other_info_tag(i),'C');
1853 end if;
1854
1855 hr_utility.trace(tab_other_info_dbi(i)||' => '||l_return_xml);
1856 l_other_info_count := l_other_info_count+1;
1857 IF l_status = FALSE THEN --Error report has single record A02
1858 l_other_info_xml_string(1) := l_other_info_xml_string(1)||l_return_xml;
1859 ELSIF l_status = TRUE THEN --Success report has multiple records
1860 IF l_other_info_count<=6 THEN
1861 l_other_info_xml_string(1) := l_other_info_xml_string(1)||l_return_xml;
1862 ELSIF l_other_info_count<=12 THEN
1863 l_other_info_sets:=2;
1864 l_other_info_xml_string(2) := l_other_info_xml_string(2)||l_return_xml;
1865 ELSIF l_other_info_count<=18 THEN
1866 l_other_info_sets:=3;
1867 l_other_info_xml_string(3) := l_other_info_xml_string(3)||l_return_xml;
1868 ELSIF l_other_info_count<=24 THEN
1869 l_other_info_sets:=4;
1870 l_other_info_xml_string(4) := l_other_info_xml_string(4)||l_return_xml;
1871 ELSIF l_other_info_count<=30 THEN
1872 l_other_info_sets:=5;
1873 l_other_info_xml_string(5) := l_other_info_xml_string(5)||l_return_xml;
1874 ELSIF l_other_info_count<=36 THEN
1875 l_other_info_sets:=6;
1876 l_other_info_xml_string(6) := l_other_info_xml_string(6)||l_return_xml;
1877 END IF;
1878 END IF;
1879 END IF;
1880 END LOOP;
1881
1882 hr_utility.trace('Other_info_count = '||l_other_info_count ||', Other_info_sets = '||l_other_info_sets);
1883
1884 /* OTH_INFO Tags end */
1885 /*****************************************************************************/
1886 /* Final XML Generation begin */
1887
1888 IF l_status = TRUE THEN --Multiple Records
1889
1890 FOR i IN 1..l_other_info_sets LOOP
1891 l_final_xml_string:=l_final_xml_string||'<Success>' || EOL;
1892 l_final_xml_string := l_final_xml_string||
1893 '<T4Slip>'||EOL||l_T4Slip_xml_string||EOL;
1894 IF i = 1 THEN
1895 l_final_xml_string := l_final_xml_string||l_T4_AMT_xml_string||EOL;
1896 END IF;
1897 IF l_other_info_xml_string(i)<>' ' THEN
1898 l_final_xml_string := l_final_xml_string||'<OTH_INFO>'|| EOL||
1899 l_other_info_xml_string(i)||
1900 '</OTH_INFO>'|| EOL;
1901 END IF;
1902 l_final_xml_string := l_final_xml_string||'</T4Slip>' || EOL;
1903 l_final_xml_string:=l_final_xml_string||'</Success>' || EOL;
1904 IF i = 1 THEN
1905 l_final_xml_string:=l_final_xml_string||tab_employee(T4_AMT_empr_eip_amt) || EOL; --Bug 15953807
1906 END IF;
1907 END LOOP;
1908
1909 ELSIF l_status = FALSE THEN --Single Record
1910 l_final_xml_string:=l_final_xml_string||'<Fail>' || EOL;
1911 l_final_xml_string:=l_final_xml_string||'<T4Slip>'||EOL||l_T4Slip_xml_string||EOL;
1912 l_final_xml_string:=l_final_xml_string||l_T4_AMT_xml_string||EOL;
1913 l_final_xml_string:=l_final_xml_string||'<OTH_INFO>'|| EOL||
1914 l_other_info_xml_string(1)||
1915 '</OTH_INFO>'|| EOL||'</T4Slip>' || EOL;
1916 l_final_xml_string:=l_final_xml_string||'<Error_msg>'||l_error_message||'</Error_msg>'
1917 ||EOL||'</Fail>'||EOL;
1918 END IF;
1919 l_all_final_xml_string := l_all_final_xml_string||l_final_xml_string;
1920 END LOOP;-- End of Loop for cancelled Jurisdiction.
1921 IF l_arch_report_type = 'T4' THEN
1922 CLOSE c_cancel_mag_jurisdictions;
1923 ELSE
1924 CLOSE c_cancel_amend_jurisdictions;
1925 END IF;
1926
1927 hr_utility.trace(l_all_final_xml_string);
1928 pay_core_files.write_to_magtape_lob(l_all_final_xml_string);
1929 hr_utility.trace('End t4_cancel_employee_record ');
1930 hr_utility.trace('X==========================================================X');
1931
1932 /* Final XML Generation end */
1933 /*****************************************************************************/
1934 END;
1935
1936
1937 END t4_cancel_employee_record;
1938
1939 END pay_ca_t4_cancel_mag;