[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_CA_T4A_CANCEL_MAG
Source
1 PACKAGE BODY pay_ca_t4a_cancel_mag AS
2 /* $Header: pycat4acmag.pkb 120.4.12020000.3 2012/10/01 07:49:45 rgottipa ship $ */
3
4 PROCEDURE range_cursor (
5 p_pactid IN NUMBER,
6 p_sqlstr OUT NOCOPY VARCHAR2)
7 IS
8 BEGIN
9
10 p_sqlstr := 'select distinct paaf.person_id
11 from per_all_assignments_f paaf,
12 pay_payroll_actions ppa,
13 pay_payroll_actions ppa1,
14 pay_assignment_actions paa
15 where ppa.payroll_action_id = paa.payroll_action_id
16 and paa.assignment_id = paaf.assignment_id
17 and ppa1.payroll_action_id = :payroll_action_id
18 and ppa.payroll_action_id =
19 to_number(pay_ca_t4a_cancel_mag.get_parameter(''PAY_ACT'', ppa1.legislative_parameters))
20 and paaf.person_id =
21 nvl(pay_ca_t4a_cancel_mag.get_parameter(''PER_ID'',ppa1.legislative_parameters),paaf.person_id)
22 order by paaf.person_id';
23
24 END range_cursor;
25 /**************************************************************************/
26 PROCEDURE action_creation(
27 p_pactid IN NUMBER,
28 p_stperson IN NUMBER,
29 p_endperson IN NUMBER,
30 p_chunk IN NUMBER )
31 IS
32
33 l_trans_gre VARCHAR2(10);
34 l_validate_gre VARCHAR2(10);
35 l_legislative_parameters VARCHAR2(200);
36 l_asg_set_id NUMBER := NULL;
37
38 CURSOR c_all_asg IS
39 select paaf.person_id,
40 paaf.assignment_id,
41 paa1.tax_unit_id,
42 paaf.effective_end_date,
43 paa.assignment_action_id,
44 ppa2.payroll_action_id
45 from
46 per_all_assignments_f paaf,
47 pay_payroll_actions ppa,
48 pay_payroll_actions ppa1,
49 pay_payroll_actions ppa2,
50 pay_assignment_actions paa,
51 pay_assignment_actions paa1,
52 pay_action_interlocks int
53 where
54 ppa.payroll_action_id = paa.payroll_action_id
55 and paa.assignment_id = paaf.assignment_id
56 and ppa1.payroll_action_id = p_pactid
57 and paaf.person_id between p_stperson and p_endperson
58 and ppa.payroll_action_id =
59 to_number(pay_ca_t4a_cancel_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
60 and ppa2.report_type in ('T4A','CAEOY_T4A_AMEND_PP')
61 and int.locking_action_id = paa.assignment_action_id
62 and paa1.assignment_action_id = int.locked_action_id
63 and ppa2.payroll_action_id = paa1.payroll_action_id
64 and ppa2.action_status = 'C'
65 and paa.action_status = 'C'
66 and paa1.action_status = 'C'
67 and paa.assignment_action_id
68 not in (select paa2.assignment_action_id
69 from pay_action_interlocks pai, pay_assignment_actions paa2
70 where paa2.assignment_action_id = pai.locked_action_id
71 and paa2.payroll_action_id =
72 to_number(pay_ca_t4a_cancel_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
73 )
74 and paaf.effective_start_date = (select max(paaf2.effective_start_date)
75 from per_all_assignments_f paaf2
76 where paaf2.assignment_id = paa.assignment_id
77 and paaf2.effective_start_date <= ppa.effective_date)
78 order by paaf.person_id;
79 -----
80 CURSOR c_all_asg_in_asgset IS
81 select paaf.person_id,
82 paaf.assignment_id,
83 paa1.tax_unit_id,
84 paaf.effective_end_date,
85 paa.assignment_action_id,
86 ppa2.payroll_action_id
87 from
88 per_all_assignments_f paaf,
89 pay_payroll_actions ppa,
90 pay_payroll_actions ppa1,
91 pay_payroll_actions ppa2,
92 pay_assignment_actions paa,
93 pay_assignment_actions paa1,
94 pay_action_interlocks int
95 where
96 ppa.payroll_action_id = paa.payroll_action_id
97 and paa.assignment_id = paaf.assignment_id
98 and ppa1.payroll_action_id = p_pactid
99 and paaf.person_id between p_stperson and p_endperson
100 and ppa.payroll_action_id =
101 to_number(pay_ca_t4a_cancel_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
102 and ppa2.report_type in ('T4A','CAEOY_T4A_AMEND_PP')
103 and int.locking_action_id = paa.assignment_action_id
104 and paa1.assignment_action_id = int.locked_action_id
105 and ppa2.payroll_action_id = paa1.payroll_action_id
106 and ppa2.action_status = 'C'
107 and paa.action_status = 'C'
108 and paa1.action_status = 'C'
109 and exists (select 1
110 from hr_assignment_set_amendments hasa,
111 per_assignments_f paf
112 where hasa.assignment_set_id = l_asg_set_id
113 and upper(hasa.include_or_exclude) = 'I'
114 and hasa.assignment_id = paf.assignment_id
115 and paf.person_id = paaf.person_id)
116 and paa.assignment_action_id
117 not in (select paa2.assignment_action_id
118 from pay_action_interlocks pai,
119 pay_assignment_actions paa2
120 where paa2.assignment_action_id = pai.locked_action_id
121 and paa2.payroll_action_id =
122 to_number(pay_ca_t4a_cancel_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
123 )
124 and paaf.effective_start_date = (select max(paaf2.effective_start_date)
125 from per_all_assignments_f paaf2
126 where paaf2.assignment_id = paa.assignment_id
127 and paaf2.effective_start_date <= ppa.effective_date)
128 order by paaf.person_id;
129 -----
130 l_year_end DATE;
131 l_effective_end_date DATE;
132 l_report_type VARCHAR2(30);
133 l_business_group_id NUMBER;
134 l_person_id NUMBER;
135 l_assignment_id NUMBER;
136 l_assignment_action_id NUMBER;
137 l_tax_unit_id NUMBER;
138 lockingactid NUMBER;
139 lv_report_type pay_payroll_actions.report_type%TYPE ;
140 ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;--
141 lv_fed_amend_flag VARCHAR2(5);
142 ln_t4a_amend_flag_ue_id NUMBER;
143 lv_flag_count NUMBER := 0;
144 l_lockingactid_check pay_assignment_actions.assignment_action_id%TYPE;
145 l_payroll_act NUMBER;
146
147 BEGIN
148 hr_utility.trace('+=========================================================+');
149 hr_utility.set_location( 'pay_ca_t4a_cancel_mag.action_creation',10);
150 get_report_parameters(
151 p_pactid,
152 l_legislative_parameters,
153 l_year_end,
154 l_report_type,
155 l_business_group_id);
156
157 l_trans_gre := pay_ca_t4a_cancel_mag.get_parameter('TRANSMITTER_GRE',
158 l_legislative_parameters);
159 hr_utility.trace('l_trans_gre ='||l_trans_gre);
160 l_validate_gre := validate_gre_data(l_trans_gre, to_char(l_year_end,'YYYY'));
161 IF l_validate_gre = '1' THEN
162 hr_utility.raise_error;
163 END IF;
164 hr_utility.set_location( 'pay_ca_t4a_cancel_mag.action_creation',15);
165 l_asg_set_id := to_number(pay_ca_t4a_cancel_mag.get_parameter('ASG_SET_ID',
166 l_legislative_parameters));
167 hr_utility.trace('l_assignment_set_id ='||l_asg_set_id);
168 --
169 IF l_asg_set_id IS NOT NULL THEN
170 OPEN c_all_asg_in_asgset;
171 ELSE
172 OPEN c_all_asg;
173 END IF;
174
175 LOOP
176 IF l_asg_set_id IS NOT NULL THEN
177 FETCH c_all_asg_in_asgset INTO l_person_id,
178 l_assignment_id,
179 l_tax_unit_id,
180 l_effective_end_date,
181 l_assignment_action_id,
182 l_payroll_act;
183 hr_utility.set_location('pay_ca_t4a_cancel_mag.action_creation', 20);
184 EXIT WHEN c_all_asg_in_asgset%NOTFOUND;
185 ELSE
186 FETCH c_all_asg INTO l_person_id,
187 l_assignment_id,
188 l_tax_unit_id,
189 l_effective_end_date,
190 l_assignment_action_id,
191 l_payroll_act;
192 hr_utility.set_location('pay_ca_t4a_cancel_mag.action_creation', 25);
193 EXIT WHEN c_all_asg%NOTFOUND;
194 END IF;
195
196 hr_utility.trace('+------------------------------------------------+');
197 hr_utility.trace('Fetched Assignment ID : '|| to_char(l_assignment_id));
198 hr_utility.trace('Person ID : '|| to_char(l_person_id));
199 hr_utility.trace('Tax Unit ID : '|| to_char(l_tax_unit_id));
200 hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date,'DD-Mon-YYYY'));
201 hr_utility.trace('x------------------------------------------------x');
202 SELECT pay_assignment_actions_s.nextval
203 INTO lockingactid
204 FROM dual;
205 hr_utility.trace('New T4A Cancel Action = ' ||to_char(lockingactid));
206 hr_utility.set_location('pay_ca_t4a_cancel_mag.action_creation', 30);
207 hr_nonrun_asact.insact(lockingactid
208 ,l_assignment_id
209 ,p_pactid
210 ,p_chunk
211 ,l_tax_unit_id);
212 hr_utility.set_location('pay_ca_t4a_cancel_mag.action_creation', 40);
213
214 UPDATE pay_assignment_actions aa
215 SET aa.serial_number = to_char(l_person_id)
216 WHERE aa.assignment_action_id = lockingactid;
217
218 hr_utility.set_location('pay_ca_t4a_cancel_mag.action_creation', 50);
219 hr_nonrun_asact.insint(lockingactid ,l_assignment_action_id);
220 hr_utility.trace(to_char(lockingactid)||' locked Cancel Archiver ID '||to_char(l_assignment_action_id));
221 hr_utility.trace('x------------------------------------------------x');
222 END LOOP; --c_all_asg loop
223 IF l_asg_set_id IS NOT NULL THEN
224 CLOSE c_all_asg_in_asgset;
225 ELSE
226 CLOSE c_all_asg;
227 END IF;
228 hr_utility.trace('X=========================================================X');
229 END action_creation;
230 /**************************************************************************/
231 FUNCTION validate_gre_data ( p_trans IN VARCHAR2,
232 p_year IN VARCHAR2)
233 RETURN VARCHAR2 IS
234 ----
235 CURSOR c_trans_payid (c_trans_id IN VARCHAR2,
236 c_year IN VARCHAR2) IS
237 SELECT ppa.payroll_action_id,ppa.business_group_id
238 FROM hr_organization_information hoi,
239 pay_payroll_actions ppa
240 WHERE hoi.organization_id = to_number(c_trans_id)
241 AND hoi.org_information_context='Fed Magnetic Reporting'
242 AND ppa.report_type in ('T4A','CAEOY_T4A_AMEND_PP') -- T4A Archiver Report Type
243 AND hoi.organization_id = pay_ca_t4a_cancel_mag.get_parameter('TRANSFER_GRE',
244 ppa.legislative_parameters)
245 AND ppa.effective_date = to_date('31-12-'||c_year,'DD-MM-YYYY');
246 ----
247
248 CURSOR c_all_gres(p_trans IN VARCHAR2,
249 p_year IN VARCHAR2,
250 p_bg_id IN NUMBER) IS
251 SELECT DISTINCT ppa.payroll_action_id, hoi.organization_id, hou.name
252 FROM pay_payroll_actions ppa,
253 hr_organization_information hoi,
254 hr_all_organization_units hou
255 WHERE hoi.org_information_context = 'Canada Employer Identification'
256 AND hoi.org_information11 = p_trans
257 AND hou.business_group_id = p_bg_id
258 AND hou.organization_id = hoi.organization_id
259 AND ppa.report_type in ('T4A','CAEOY_T4A_AMEND_PP')
260 AND ppa.effective_date = to_date('31-12-'||p_year,'DD-MM-YYYY')
261 AND ppa.business_group_id = p_bg_id
262 AND hoi.organization_id = pay_ca_t4a_cancel_mag.get_parameter('TRANSFER_GRE',
263 ppa.legislative_parameters);
264 ----
265 CURSOR c_gre_name (b_org_id IN VARCHAR2) IS
266 SELECT hou.name
267 FROM hr_all_organization_units hou
268 WHERE hou.organization_id = to_number(b_org_id);
269 ----
270 /* Local variables */
271 l_trans_gre hr_all_organization_units.organization_id%TYPE;
272 l_year VARCHAR2(10);
273 l_gre hr_all_organization_units.organization_id%TYPE;
274 l_bus_grp hr_all_organization_units.business_group_id%TYPE;
275 l_trans_no VARCHAR2(240);
276 l_tech_name VARCHAR2(240) ;
277 l_tech_area VARCHAR2(240) ;
278 l_tech_phno VARCHAR2(240) ;
279 l_tech_email VARCHAR2(240) ;
280 l_lang VARCHAR2(240) ;
281 l_acc_name VARCHAR2(240) ;
282 l_acc_area VARCHAR2(240) ;
283 l_acc_phno VARCHAR2(240) ;
284 l_trans_bus_no VARCHAR2(240) ;
285 l_bus_no VARCHAR2(240) ;
286 l_trans_payid pay_payroll_actions.payroll_action_id%TYPE;
287 l_gre_payid pay_payroll_actions.payroll_action_id%TYPE;
288 l_gre_actid pay_assignment_actions.assignment_action_id%TYPE;
289 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
290 l_acc_info_flag CHAR(1);
291 l_trans_name VARCHAR2(240);
292 l_gre_name VARCHAR2(240);
293 l_bg_id NUMBER;
294
295 BEGIN
296 /* Fetching the Payroll Action Id for Trasnmitter GRE */
297
298 hr_utility.trace('Inside the Validation Code');
299 hr_utility.trace('The Transmitter GRE id passed is '||p_trans);
300 OPEN c_trans_payid(p_trans,p_year);
301 FETCH c_trans_payid INTO l_trans_payid,l_bg_id;
302 IF c_trans_payid%NOTFOUND THEN
303 CLOSE c_trans_payid;
304 hr_utility.trace('The Transmitter GRE ID not found :'||p_trans);
305 hr_utility.raise_error;
306 RETURN '1';
307 ELSE
308 CLOSE c_trans_payid;
309 END IF;
310
311 hr_utility.trace('Fetched the Payroll Id for transmitter GRE :'|| l_trans_payid);
312 hr_utility.trace('The Reporting Year is '||p_year);
313 /*Fetching the Trasnmitter Level Data */
314
315 l_trans_no := get_arch_val(l_trans_payid, 'CAEOY_TRANSMITTER_NUMBER');
316 l_tech_name := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_NAME');
317 l_tech_area := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_AREA_CODE');
318 l_tech_phno := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_PHONE');
319 l_tech_email := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_EMAIL');
320 l_lang := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_LANGUAGE');
321 l_acc_name := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_NAME');
322 l_acc_area := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
323 l_acc_phno := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_PHONE');
324 l_trans_bus_no := get_arch_val(l_trans_payid, 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
325
326 OPEN c_gre_name(to_number(p_trans));
327 FETCH c_gre_name INTO l_trans_name;
328 CLOSE c_gre_name;
329
330 hr_utility.trace('Transmitter Number :'||l_trans_no);
331 hr_utility.trace('Tech Name : '||l_tech_name);
332 hr_utility.trace('Tech Area : '||l_tech_area);
333 hr_utility.trace('Tech Phone : '||l_tech_phno);
334 hr_utility.trace('Tech Email : '||l_tech_email);
335 hr_utility.trace('Tech Language : '||l_lang);
336
337 /* Checking for the validity of the above values fetched */
338 IF l_trans_no IS NULL
339 OR TRANSLATE(l_trans_no,'M0123456789','M9999999999') <> 'MM999999' THEN
340 hr_utility.trace('Incorrect Transmitter No format');
341 hr_utility.set_message(801,'PAY_74155_INCORRECT_TRANSMT_NO');
342 hr_utility.set_message_token('GRE_NAME',l_trans_name);
343 pay_core_utils.push_message(801,'PAY_74155_INCORRECT_TRANSMT_NO','P');
344 pay_core_utils.push_token('GRE_NAME',l_trans_name);
345 hr_utility.raise_error;
346 RETURN '1';
347 END IF;
348
349 IF l_tech_name IS NULL OR
350 l_tech_area IS NULL OR
351 l_tech_phno IS NULL OR
352 l_tech_email IS NULL OR
353 l_lang IS NULL THEN
354 hr_utility.trace('Technical contact details missing');
355 hr_utility.set_message(801,'PAY_74158_INCORRECT_TCHN_INFO');
356 hr_utility.set_message_token('GRE_NAME',l_trans_name);
357 pay_core_utils.push_message(801,'PAY_74158_INCORRECT_TCHN_INFO','P');
358 pay_core_utils.push_token('GRE_NAME',l_trans_name);
359 hr_utility.raise_error;
360 RETURN '1';
361 END IF;
362
363 IF l_acc_name IS NULL OR
364 l_acc_phno IS NULL OR
365 l_acc_area IS NULL THEN
366 l_acc_info_flag := 'N';
367 ELSE
368 l_acc_info_flag := 'Y';
369 END IF;
370 hr_utility.trace('The value of the Flag is '||l_acc_info_flag);
371
372 /* If Transmitter Level Accounting Information is Missing checking for the GRE level information */
373
374 OPEN c_all_gres(p_trans,p_year,l_bg_id);
375 LOOP
376 FETCH c_all_gres into l_gre_payid, l_gre, l_gre_name;
377 hr_utility.trace('The Gre id fetched is '||l_gre);
378 IF c_all_gres%NOTFOUND THEN
379 CLOSE c_all_gres;
380 EXIT;
381 END IF;
382
383 hr_utility.trace('Before fetching the GREs for this Transmitter '||l_gre||'-'||p_year);
384
385
386 IF l_gre <> to_number(p_trans) THEN
387 hr_utility.trace('Inside the loop'||l_gre_payid);
388
389 hr_utility.trace('Checking GRE level data');
390 hr_utility.trace('The Payroll Action Id for Gre is '|| l_gre_payid);
391 l_bus_no := get_arch_val(l_gre_payid,'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
392 l_acc_name := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_NAME');
393 l_acc_area := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
394 l_acc_phno := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_PHONE');
395
396 hr_utility.trace('Tax unit ID :'||l_tax_unit_id);
397 hr_utility.trace('Acc Name :'||l_acc_name);
398 hr_utility.trace('Acc Area :'||l_acc_area);
399 hr_utility.trace('Acc Phone : '||l_acc_phno);
400 hr_utility.trace('GRE Name :'||l_gre_name);
401
402 IF l_bus_no IS NULL
403 OR TRANSLATE(l_bus_no,'0123456789RP','9999999999RP') <> '999999999RP9999' THEN
404 hr_utility.trace('No Business Number Entereed ');
405 hr_utility.set_message(801,'PAY_74154_INCORRECT_BN');
406 hr_utility.set_message_token('GRE_NAME',l_gre_name);
407 pay_core_utils.push_message(801,'PAY_74154_INCORRECT_BN','P');
408 pay_core_utils.push_token('GRE_NAME',l_gre_name);
409 hr_utility.raise_error;
410 RETURN '1';
411 END IF;
412
413 IF (l_acc_name IS NULL OR
414 l_acc_area IS NULL OR
415 l_acc_phno IS NULL ) AND
416 l_acc_info_flag = 'N' THEN
417 hr_utility.trace('No Accounting Contact info present');
418 hr_utility.set_message(801,'PAY_74157_INCORRECT_ACNT_INFO');
419 hr_utility.set_message_token('GRE_NAME',l_gre_name);
420 pay_core_utils.push_message(801,'PAY_74157_INCORRECT_ACNT_INFO','P');
421 pay_core_utils.push_token('GRE_NAME',l_gre_name);
422 hr_utility.raise_error;
423 RETURN '1';
424 END IF;
425
426 ELSIF l_gre = to_number(p_trans) THEN
427
428 IF l_trans_bus_no IS NULL
429 OR TRANSLATE(l_trans_bus_no,'0123456789RP','9999999999RP') <> '999999999RP9999' THEN
430 hr_utility.trace('No Business Number Entereed ');
431 hr_utility.set_message(801,'PAY_74154_INCORRECT_BN');
432 hr_utility.set_message_token('GRE_NAME',l_trans_name);
433 pay_core_utils.push_message(801,'PAY_74154_INCORRECT_BN','P');
434 pay_core_utils.push_token('GRE_NAME',l_trans_name);
435 hr_utility.raise_error;
436 RETURN '1';
437 END IF;
438
439 IF l_acc_info_flag = 'N' THEN
440 hr_utility.trace('No Accounting Contact info present');
441 hr_utility.set_message(801,'PAY_74157_INCORRECT_ACNT_INFO');
442 hr_utility.set_message_token('GRE_NAME',l_trans_name);
443 pay_core_utils.push_message(801,'PAY_74157_INCORRECT_ACNT_INFO','P');
444 pay_core_utils.push_token('GRE_NAME',l_trans_name);
445 hr_utility.raise_error;
446 RETURN '1';
447 END IF;
448 END IF;
449 END LOOP;
450 RETURN '0';
451 END validate_gre_data;
452 /**************************************************************************/
453 FUNCTION get_arch_val( p_context_id IN NUMBER,
454 p_user_name IN VARCHAR2)
455 RETURN VARCHAR2 IS
456
457 CURSOR cur_archive (b_context_id IN NUMBER,
458 b_user_name VARCHAR2) IS
459 SELECT fai.value
460 FROM ff_archive_items fai,
461 ff_database_items fdi
462 WHERE fai.user_entity_id = fdi.user_entity_id
463 AND fai.context1 = b_context_id
464 AND fdi.user_name = b_user_name;
465
466 l_return VARCHAR2(240);
467
468 BEGIN
469 OPEN cur_archive(p_context_id,p_user_name);
470 FETCH cur_archive INTO l_return;
471 CLOSE cur_archive;
472 RETURN (l_return);
473 END get_arch_val;
474 /**************************************************************************/
475 -- Name : get_report_parameters
476
477 -----------------------------------------------------------------------------
478 --
479 -- Purpose
480 -- The procedure gets the 'parameter' for which the report is being
481 -- run i.e., the period, state and business organization.
482 --
483 -- Arguments
484 -- p_pactid Payroll_action_id passed from pyugen process
485 -- p_legislative_parameters Legislative parameters of the report
486 -- p_year_end End date of the period
487 -- p_report_type Type of report being run T4A_AMEND_MAG
488 -- p_business_group_id Business group for which the report is being run
489
490 ----------------------------------------------------------------------------
491 PROCEDURE get_report_parameters(
492 p_pactid IN NUMBER,
493 p_legislative_parameters IN OUT NOCOPY VARCHAR2,
494 p_year_end IN OUT NOCOPY DATE,
495 p_report_type IN OUT NOCOPY VARCHAR2,
496 p_business_group_id IN OUT NOCOPY NUMBER) IS
497 BEGIN
498 SELECT ppa.legislative_parameters,
499 ppa.effective_date,
500 ppa.business_group_id,
501 ppa.report_type
502 INTO p_legislative_parameters,
503 p_year_end,
504 p_business_group_id,
505 p_report_type
506 FROM pay_payroll_actions ppa
507 WHERE payroll_action_id = p_pactid;
508 END get_report_parameters;
509 /**************************************************************************/
510 FUNCTION get_parameter(name IN VARCHAR2,
511 parameter_list IN VARCHAR2)
512 RETURN VARCHAR2 IS
513 start_ptr NUMBER;
514 end_ptr NUMBER;
515 token_val pay_payroll_actions.legislative_parameters%TYPE;
516 par_value pay_payroll_actions.legislative_parameters%TYPE;
517 BEGIN
518 --
519 token_val := name||'=';
520 --
521 start_ptr := instr(parameter_list, token_val) + length(token_val);
522 end_ptr := instr(parameter_list, ' ',start_ptr);
523 --
524 /* if there is no spaces use then length of the string */
525 IF end_ptr = 0 THEN
526 end_ptr := length(parameter_list)+1;
527 END IF;
528 --
529 /* Did we find the token */
530 IF instr(parameter_list, token_val) = 0 THEN
531 par_value := NULL;
532 ELSE
533 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
534 END IF;
535 --
536 RETURN par_value;
537 --
538 END get_parameter;
539 /**************************************************************************/
540 FUNCTION convert_2_xml(p_data IN VARCHAR2,
541 p_tag IN VARCHAR2,
542 p_datatype IN CHAR DEFAULT 'T',
543 p_format IN VARCHAR2 DEFAULT NULL,
544 p_null_allowed IN VARCHAR2 DEFAULT 'N' )
545 RETURN VARCHAR2 IS
546
547 l_data VARCHAR2(4000);
548 l_output VARCHAR2(4000);
549 EOL VARCHAR2(5);
550 BEGIN
551 /* if p_data is null then
552 return null;
553 end if; */
554 SELECT
555 fnd_global.local_chr(13) || fnd_global.local_chr(10)
556 INTO EOL
557 FROM dual;
558 IF p_null_allowed = 'N'
559 AND (TRIM(p_data) IS NULL
560 OR (p_datatype IN ('N','C')
561 AND to_number(p_data) = 0)) THEN
562 RETURN ' ';
563 END IF;
564 l_data := trim(p_data);
565 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
566 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
567 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
568 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
569 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
570 --------------------------------------------------------
571 --- P_Datatype: T = Text, N = Number, C=Currency, D=Date
572 --------------------------------------------------------
573 --hr_utility.trace('l_data='||l_data);
574 IF p_datatype = 'T' OR p_datatype = 'D' THEN
575 l_output := '<' || trim(p_tag) || '>' || trim(l_data)
576 || '</' || trim(p_tag) || '>'||EOL;
577 -- hr_utility.trace('l_output='||l_output);
578 ELSIF p_datatype = 'N' OR p_datatype = 'C' THEN
579 IF TRIM(p_format) IS NOT NULL THEN
580 SELECT to_char(to_number(p_data), p_format)
581 INTO l_data FROM dual;
582 ELSIF p_datatype = 'C' THEN -- Currency should be two decimal places
583 SELECT to_char(to_number(p_data), '99999999999999999999999999999999999990.99')
584 INTO l_data FROM dual;
585 END IF;
586 l_output := '<' || trim(p_tag) || '>' || trim(l_data)
587 || '</' || trim(p_tag) || '>'||EOL;
588 END IF;
589 --hr_utility.trace('l_output='||l_output);
590 RETURN l_output;
591 END;
592 /**************************************************************************/
593 FUNCTION get_t4a_pp_regno(p_pactid IN NUMBER,
594 p_tax_unit_id IN NUMBER,
595 p_pp_regno1 OUT NOCOPY VARCHAR2,
596 p_pp_regno2 OUT NOCOPY VARCHAR2,
597 p_pp_regno3 OUT NOCOPY VARCHAR2)
598 RETURN VARCHAR2 IS
599
600 CURSOR c_get_reg_no(cp_pact_id IN NUMBER,
601 cp_tax_unit_id IN NUMBER) IS
602 SELECT to_number(pai.action_information5) ppreg_amt,
603 pai.action_information4 ppreg_no
604 FROM pay_action_information pai,pay_payroll_actions ppa
605 WHERE pai.action_context_id = cp_pact_id
606 AND pai.tax_unit_id = cp_tax_unit_id
607 AND ppa.payroll_action_id = pai.action_context_id
608 AND pai.effective_date = ppa.effective_date
609 AND pai.action_information_category = 'CAEOY PENSION PLAN INFO'
610 ORDER BY 1 DESC;
611
612 lv_pp_regno1 varchar2(30) := ' ';
613 ln_pp_regamt1 number(30);
614 lv_pp_regno2 varchar2(30) := ' ';
615 ln_pp_regamt2 number(30);
616 lv_pp_regno3 varchar2(30) := ' ';
617 ln_pp_regamt3 number(30);
618 lv_pp_regno varchar2(30);
619 ln_pp_regamt number(30);
620
621 BEGIN
622 OPEN c_get_reg_no(p_pactid,p_tax_unit_id);
623 LOOP
624 FETCH c_get_reg_no INTO ln_pp_regamt, lv_pp_regno;
625 EXIT WHEN c_get_reg_no%NOTFOUND;
626
627 IF c_get_reg_no%ROWCOUNT = 1 THEN
628 lv_pp_regno1 := lv_pp_regno;
629 ln_pp_regamt1 := ln_pp_regamt;
630 ELSIF c_get_reg_no%ROWCOUNT = 2 THEN
631 lv_pp_regno2 := lv_pp_regno;
632 ln_pp_regamt2 := ln_pp_regamt;
633 ELSIF c_get_reg_no%ROWCOUNT = 3 THEN
634 lv_pp_regno3 := lv_pp_regno;
635 ln_pp_regamt3 := ln_pp_regamt;
636 END IF;
637
638 IF c_get_reg_no%ROWCOUNT > 3 THEN
639 EXIT;
640 END IF;
641
642 END LOOP;
643 CLOSE c_get_reg_no;
644
645 p_pp_regno1 := lv_pp_regno1;
646 p_pp_regno2 := lv_pp_regno2;
647 p_pp_regno3 := lv_pp_regno3;
648 RETURN '1';
649 END get_t4a_pp_regno;
650 /*****************************************************************************/
651 PROCEDURE t4a_cancel_mag_transmitter IS
652 BEGIN
653 DECLARE
654
655 l_final_xml VARCHAR2(32000);
656 l_context1 ff_archive_items.context1%TYPE;
657 EOL VARCHAR2(5) := fnd_global.local_chr(13)
658 || fnd_global.local_chr(10);
659 l_return VARCHAR2(240);
660 l_status BOOLEAN := TRUE;
661 l_field_name VARCHAR2(250);
662
663 TYPE transmitter_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
664 tab_transmitter transmitter_info;
665
666 T619_trnmtr_nbr NUMBER :=1;
667 T619_sbmt_ref_id NUMBER :=2;
668 T619_trnmtr_tcd NUMBER :=3;
669 T619_summ_cnt NUMBER :=4;
670 T619_l1_nm NUMBER :=5;
671 T619_l2_nm NUMBER :=6;
672 T619_addr_l1_txt NUMBER :=7;
673 T619_addr_l2_txt NUMBER :=8;
674 T619_cty_nm NUMBER :=9;
675 T619_prov_cd NUMBER :=10;
676 T619_cntry_cd NUMBER :=11;
677 T619_pstl_cd NUMBER :=12;
678 T619_cntc_nm NUMBER :=13;
679 T619_cntc_area_cd NUMBER :=14;
680 T619_cntc_phn_nbr NUMBER :=15;
681 T619_cntc_extn_nbr NUMBER :=16;
682 T619_cntc_email_area NUMBER :=17;
683 T619_lang_cd NUMBER :=18;
684 T619_rpt_tcd NUMBER :=19;
685
686 BEGIN
687 hr_utility.trace('+==========================================================+');
688 hr_utility.trace('XML Transmitter');
689
690 begin
691 select max(paa1.payroll_action_id)
692 into l_context1
693 from pay_assignment_actions paa,
694 pay_assignment_actions paa1,
695 pay_action_interlocks int
696 where int.locking_action_id = paa.assignment_action_id
697 and paa1.assignment_action_id = int.locked_action_id
698 and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
699 exception
700 when no_data_found then
701 raise;
702 end;
703
704 -- l_context1 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
705 hr_utility.trace('PAYROLL_ACTION_ID l_context1 ='||l_context1);
706
707 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
708 'CAEOY_TRANSMITTER_NUMBER');
709 IF l_return IS NULL THEN
710 l_field_name := l_field_name || 'CAEOY_TRANSMITTER_NUMBER,';
711 l_status := FALSE;
712 END IF;
713
714 tab_transmitter(T619_trnmtr_nbr)
715 := convert_2_xml(nvl(l_return,' '),'trnmtr_nbr');
716 ----
717 /* # 11074583
718 l_return := pay_magtape_generic.get_parameter_value('SBMT_REF_ID');*/
719 l_return := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
720 tab_transmitter(T619_sbmt_ref_id)
721 := convert_2_xml(nvl(l_return,' '),'sbmt_ref_id');
722 ----
723 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
724 'CAEOY_TRANSMITTER_TYPE_INDICATOR');
725 IF l_return IS NULL THEN
726 l_field_name := l_field_name || 'CAEOY_TRANSMITTER_TYPE_INDICATOR,';
727 l_status := FALSE;
728 END IF;
729 tab_transmitter(T619_trnmtr_tcd)
730 := convert_2_xml(l_return,'trnmtr_tcd');
731 ----
732 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
733 'CAEOY_T4A_TOTAL_NO_SUMMARY_COUNT');
734 tab_transmitter(T619_summ_cnt)
735 := convert_2_xml(l_return,'summ_cnt');
736 ----
737 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
738 'CAEOY_TRANSMITTER_NAME');
739 tab_transmitter(T619_l1_nm)
740 := convert_2_xml
741 (upper(rpad(substr(l_return,1,30),30)) ,'l1_nm');
742 tab_transmitter(T619_l2_nm)
743 := convert_2_xml
744 (upper(rpad(substr(l_return,31,30),30)) ,'l2_nm');
745 ----
746 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
747 'CAEOY_TRANSMITTER_ADDRESS_LINE1');
748 tab_transmitter(T619_addr_l1_txt)
749 := convert_2_xml(substr(l_return,1,30),'addr_l1_txt');
750 ----
751 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
752 'CAEOY_TRANSMITTER_ADDRESS_LINE2');
753 tab_transmitter(T619_addr_l2_txt)
754 := convert_2_xml(substr(l_return,1,30),'addr_l2_txt');
755 ----
756 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
757 'CAEOY_TRANSMITTER_CITY');
758 tab_transmitter(T619_cty_nm)
759 := convert_2_xml(substr(l_return,1,28),'cty_nm');
760 ----
761 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
762 'CAEOY_TRANSMITTER_PROVINCE');
763 tab_transmitter(T619_prov_cd)
764 := convert_2_xml(l_return,'prov_cd');
765 ----
766 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
767 'CAEOY_TRANSMITTER_COUNTRY');
768 IF l_return IS NULL THEN
769 tab_transmitter(T619_cntry_cd) := NULL;
770 ELSIF l_return='CA' THEN
771 tab_transmitter(T619_cntry_cd) := convert_2_xml('CAN', 'cntry_cd');
772 ELSIF l_return='US' THEN
773 tab_transmitter(T619_cntry_cd) := convert_2_xml('USA', 'cntry_cd');
774 END IF;
775 ----
776 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
777 'CAEOY_TRANSMITTER_POSTAL_CODE');
778 tab_transmitter(T619_pstl_cd) := convert_2_xml(substr(REPLACE(l_return,' ',''),1,10), 'pstl_cd');
779 ----
780 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
781 'CAEOY_TECHNICAL_CONTACT_NAME');
782 IF l_return IS NULL THEN
783 l_field_name := l_field_name || 'CAEOY_TECHNICAL_CONTACT_NAME,';
784 l_status := FALSE;
785 END IF;
786 tab_transmitter(T619_cntc_nm)
787 := convert_2_xml(substr(l_return,1,22),'cntc_nm');
788
789 ----
790 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
791 'CAEOY_TECHNICAL_CONTACT_AREA_CODE');
792 tab_transmitter(T619_cntc_area_cd)
793 := convert_2_xml(substr(l_return,1,3),'cntc_area_cd');
794 ----
795 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
796 'CAEOY_TECHNICAL_CONTACT_PHONE');
797 IF l_return IS NULL THEN
798 tab_transmitter(T619_cntc_phn_nbr) := NULL;
799 ELSE
800 l_return := REPLACE(REPLACE(l_return, ' '), '-');
801 l_return := upper(lpad(substr(l_return, 1, 7), 7,'0'));
802 tab_transmitter(T619_cntc_phn_nbr)
803 := convert_2_xml(substr(l_return,1,3)||'-'||
804 substr(l_return,4,4),
805 'cntc_phn_nbr');
806 END IF;
807 ----
808 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
809 'CAEOY_TECHNICAL_CONTACT_EXTN');
810 IF l_return IS NULL THEN
811 tab_transmitter(T619_cntc_extn_nbr) := NULL;
812 ELSE
813 tab_transmitter(T619_cntc_extn_nbr)
814 := convert_2_xml(substr(l_return,1,5),
815 'cntc_extn_nbr');
816 END IF;
817 ----
818 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
819 'CAEOY_TECHNICAL_CONTACT_EMAIL');
820 IF l_return IS NULL THEN
821 tab_transmitter(T619_cntc_email_area) := NULL;
822 l_field_name := l_field_name || 'CAEOY_TECHNICAL_CONTACT_EMAIL,';
823 l_status := FALSE;
824 ELSE
825 tab_transmitter(T619_cntc_email_area)
826 := convert_2_xml(substr(l_return,1,60),
827 'cntc_email_area');
828 END IF;
829 ----
830 tab_transmitter(T619_rpt_tcd)
831 := convert_2_xml('A','rpt_tcd');
832 ----
833 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
834 'CAEOY_TECHNICAL_CONTACT_LANGUAGE');
835 tab_transmitter(T619_lang_cd)
836 := convert_2_xml(l_return,'lang_cd');
837 ----
838 IF l_status = FALSE THEN
839 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
840 'CAEOY_TRANSMITTER_NAME');
841 hr_utility.set_location('Error in T4A Transmitter record',10);
842 hr_utility.set_location('Error in the following fields: ' || l_field_name,20);
843 hr_utility.trace('Technical contact details missing');
844 hr_utility.set_message(801,'PAY_74158_INCORRECT_TCHN_INFO');
845 hr_utility.set_message_token('GRE_NAME',l_return);
846 pay_core_utils.push_message(801,'PAY_74158_INCORRECT_TCHN_INFO','P');
847 pay_core_utils.push_token('GRE_NAME',l_return);
848 hr_utility.raise_error;
849 END IF;
850 ----
851 l_final_xml := '<Submission>' || EOL
852 || '<T619>'|| EOL||
853 tab_transmitter(T619_sbmt_ref_id)||
854 tab_transmitter(T619_rpt_tcd)||
855 tab_transmitter(T619_trnmtr_nbr)||
856 tab_transmitter(T619_trnmtr_tcd)||
857 tab_transmitter(T619_summ_cnt)||
858 tab_transmitter(T619_lang_cd)||
859 '<TRNMTR_NM>'|| EOL||
860 tab_transmitter(T619_l1_nm)||
861 tab_transmitter(T619_l2_nm)||
862 '</TRNMTR_NM>' || EOL || '<TRNMTR_ADDR>' ||
863 tab_transmitter(T619_addr_l1_txt)||
864 tab_transmitter(T619_addr_l2_txt)||
865 tab_transmitter(T619_cty_nm)||
866 tab_transmitter(T619_prov_cd)||
867 tab_transmitter(T619_cntry_cd)||
868 tab_transmitter(T619_pstl_cd)||
869 '</TRNMTR_ADDR>' || EOL||
870 '<CNTC>' || EOL||
871 tab_transmitter(T619_cntc_nm)||
872 tab_transmitter(T619_cntc_area_cd)||
873 tab_transmitter(T619_cntc_phn_nbr)||
874 tab_transmitter(T619_cntc_extn_nbr)||
875 tab_transmitter(T619_cntc_email_area)||
876 '</CNTC>' || EOL||
877 '</T619>' || EOL;
878 hr_utility.trace(l_final_xml);
879 pay_core_files.write_to_magtape_lob(l_final_xml);
880 hr_utility.trace('X==========================================================X');
881 END;
882 END t4a_cancel_mag_transmitter;
883 /*****************************************************************************/
884
885 PROCEDURE end_of_file is
886 BEGIN
887 DECLARE
888 l_final_xml CLOB;
889 l_final_xml_string VARCHAR2(32000);
890 l_is_temp_final_xml VARCHAR2(2);
891 BEGIN
892 hr_utility.trace('+==========================================================+');
893 hr_utility.trace('Entering end_of_file');
894 l_final_xml_string := '</Submission>';
895
896 hr_utility.trace(l_final_xml_string );
897 pay_core_files.write_to_magtape_lob(l_final_xml_string);
898 hr_utility.trace('Exiting end_of_file');
899 hr_utility.trace('X==========================================================X');
900 END;
901 END end_of_file;
902 /*****************************************************************************/
903
904 PROCEDURE t4a_cancel_employer_start IS
905 BEGIN
906 DECLARE
907 l_final_xml_string VARCHAR2(32000);
908 EOL VARCHAR2(5) := fnd_global.local_chr(13)
909 || fnd_global.local_chr(10);
910
911 BEGIN
912 hr_utility.trace('+==========================================================+');
913 hr_utility.trace('Entering t4a_amend_employer_start');
914 l_final_xml_string := '<Return>'||EOL||'<T4A>'||EOL;
915 hr_utility.trace(l_final_xml_string);
916 pay_core_files.write_to_magtape_lob(l_final_xml_string);
917 hr_utility.trace('Exiting t4a_amend_employer_start');
918 hr_utility.trace('X==========================================================X');
919 END;
920 END t4a_cancel_employer_start;
921
922 /*****************************************************************************/
923 PROCEDURE t4a_cancel_employer_record is
924 BEGIN
925 DECLARE
926 l_final_xml_string VARCHAR2(32000);
927 l_context1 ff_archive_items.context1%TYPE;
928 l_return VARCHAR2(240);
929 EOL varchar2(5) := fnd_global.local_chr(13) || fnd_global.local_chr(10);
930 l_transfer_pact_id VARCHAR2(15);
931 l_transfer_tax_unit_id VARCHAR2(10);
932 lv_ppreg_no1 VARCHAR2(20);
933 lv_ppreg_no2 VARCHAR2(20);
934 lv_ppreg_no3 VARCHAR2(20);
935
936 TYPE employer_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
937 tab_employer employer_info;
938
939 T4ASummary_bn NUMBER :=1;
940 T4ASummary_l1_nm NUMBER :=2;
941 T4ASummary_l2_nm NUMBER :=3;
942 T4ASummary_l3_nm NUMBER :=4;
943 T4ASummary_addr_l1_txt NUMBER :=5;
944 T4ASummary_addr_l2_txt NUMBER :=6;
945 T4ASummary_cty_nm NUMBER :=7;
946 T4ASummary_prov_cd NUMBER :=8;
947 T4ASummary_cntry_cd NUMBER :=9;
948 T4ASummary_pstl_cd NUMBER :=10;
949 T4ASummary_cntc_nm NUMBER :=11;
950 T4ASummary_cntc_area_cd NUMBER :=12;
951 T4ASummary_cntc_phn_nbr NUMBER :=13;
952 T4ASummary_cntc_extn_nbr NUMBER :=14;
953 T4ASummary_tx_yr NUMBER :=15;
954 -- T4ASummary_slp_cnt Computed in XSL template
955 T4ASummary_rpp_rgst_1_nbr NUMBER :=16;
956 T4ASummary_rpp_rgst_2_nbr NUMBER :=17;
957 T4ASummary_rpp_rgst_3_nbr NUMBER :=18;
958 T4ASummary_pprtr_1_sin NUMBER :=19;
959 T4ASummary_pprtr_2_sin NUMBER :=20;
960 T4ASummary_rpt_tcd NUMBER :=21;
961
962 BEGIN
963 hr_utility.trace('+==========================================================+');
964 hr_utility.trace('Entering t4a_amend_employer_record');
965 l_context1 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
966 l_transfer_pact_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
967 l_transfer_tax_unit_id := pay_magtape_generic.get_parameter_value('TRANSMITTER_GRE');
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 ----
972 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
973 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
974 tab_employer(T4ASummary_bn):= convert_2_xml(l_return,'bn');
975
976 ----
977 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
978 'CAEOY_EMPLOYER_NAME');
979 tab_employer(T4ASummary_l1_nm):= convert_2_xml(substr(l_return,1,30),'l1_nm');
980 tab_employer(T4ASummary_l2_nm):= convert_2_xml(substr(l_return,31,30),'l2_nm');
981 tab_employer(T4ASummary_l3_nm):= convert_2_xml(substr(l_return,61,30),'l3_nm');
982 ----
983 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
984 'CAEOY_EMPLOYER_ADDRESS_LINE1');
985 tab_employer(T4ASummary_addr_l1_txt):= convert_2_xml(substr(l_return,1,30),'addr_l1_txt');
986 ----
987 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
988 'CAEOY_EMPLOYER_ADDRESS_LINE2');
989 tab_employer(T4ASummary_addr_l2_txt):= convert_2_xml(substr(l_return,1,30),'addr_l2_txt');
990 ----
991 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
992 'CAEOY_EMPLOYER_CITY');
993 tab_employer(T4ASummary_cty_nm):= convert_2_xml(substr(l_return,1,28),'cty_nm');
994 ----
995 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
996 'CAEOY_EMPLOYER_PROVINCE');
997 tab_employer(T4ASummary_prov_cd):= convert_2_xml(substr(l_return,1,2),'prov_cd');
998 ----
999 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1000 'CAEOY_EMPLOYER_COUNTRY');
1001 IF l_return IS NULL THEN
1002 tab_employer(T4ASummary_cntry_cd) := NULL;
1003 ELSIF l_return ='CA' THEN
1004 tab_employer(T4ASummary_cntry_cd):= convert_2_xml('CAN','cntry_cd');
1005 ELSIF l_return ='US' THEN
1006 tab_employer(T4ASummary_cntry_cd):= convert_2_xml('USA','cntry_cd');
1007 END IF;
1008 ----
1009 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1010 'CAEOY_EMPLOYER_POSTAL_CODE');
1011 tab_employer(T4ASummary_pstl_cd) := convert_2_xml(substr(REPLACE(l_return,' ',''),1,10), 'pstl_cd');
1012 ----
1013 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1014 'CAEOY_ACCOUNTING_CONTACT_NAME');
1015 IF l_return IS NULL THEN
1016 tab_employer(T4ASummary_cntc_nm):= convert_2_xml(' ', 'cntc_nm');
1017 ELSE
1018 tab_employer(T4ASummary_cntc_nm):= convert_2_xml(substr(l_return,1,22)
1019 , 'cntc_nm');
1020 END IF;
1021 ----
1022 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1023 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
1024 IF l_return IS NULL THEN
1025 tab_employer(T4ASummary_cntc_area_cd):= convert_2_xml(' ', 'cntc_area_cd');
1026 ELSE
1027 tab_employer(T4ASummary_cntc_area_cd):= convert_2_xml(substr(l_return,1,22)
1028 , 'cntc_area_cd');
1029 END IF;
1030 ----
1031 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1032 'CAEOY_ACCOUNTING_CONTACT_PHONE');
1033 IF l_return IS NULL THEN
1034 tab_employer(T4ASummary_cntc_phn_nbr):= convert_2_xml('000-0000 ', 'cntc_phn_nbr');
1035 ELSE
1036 l_return := REPLACE(REPLACE(l_return, ' '), '-');
1037 l_return := upper(lpad(substr(l_return, 1, 7), 7,'0'));
1038 tab_employer(T4ASummary_cntc_phn_nbr):= convert_2_xml(substr(l_return,1,3)||'-'||substr(l_return,4,4)
1039 , 'cntc_phn_nbr');
1040 END IF;
1041 ----
1042 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1043 'CAEOY_ACCOUNTING_CONTACT_EXTENSION');
1044 IF l_return IS NULL THEN
1045 tab_employer(T4ASummary_cntc_extn_nbr):= convert_2_xml(' ', 'cntc_extn_nbr');
1046 ELSE
1047 tab_employer(T4ASummary_cntc_extn_nbr):= convert_2_xml(substr(l_return,1,5)
1048 , 'cntc_extn_nbr');
1049 END IF;
1050 ----
1051 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1052 'CAEOY_TAXATION_YEAR');
1053 tab_employer(T4ASummary_tx_yr):= convert_2_xml(l_return, 'tx_yr');
1054 ----
1055
1056 l_return:= get_t4a_pp_regno(to_number(l_context1),
1057 to_number(l_transfer_tax_unit_id ),
1058 lv_ppreg_no1,
1059 lv_ppreg_no2,
1060 lv_ppreg_no3);
1061
1062 tab_employer(T4ASummary_rpp_rgst_1_nbr) := convert_2_xml(substr(lv_ppreg_no1,1,7)
1063 ,'rpp_rgst_1_nbr');
1064 tab_employer(T4ASummary_rpp_rgst_2_nbr) := convert_2_xml(substr(lv_ppreg_no2,1,7)
1065 ,'rpp_rgst_2_nbr');
1066 tab_employer(T4ASummary_rpp_rgst_3_nbr) := convert_2_xml(substr(lv_ppreg_no3,1,7)
1067 ,'rpp_rgst_3_nbr');
1068 ----
1069 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1070 'CAEOY_PROPRIETOR_SIN1');
1071 -- 11074583 start
1072 if trim(l_return) IS NULL then
1073 l_return := lpad('0',9,'0');
1074 end if;
1075 -- 11074583 end
1076 tab_employer(T4ASummary_pprtr_1_sin):= convert_2_xml(substr(REPLACE(l_return,' ',''),1,9), 'pprtr_1_sin');
1077 ----
1078 l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1079 'CAEOY_PROPRIETOR_SIN2');
1080 -- 11074583 start
1081 if trim(l_return) IS NULL then
1082 l_return := lpad('0',9,'0');
1083 end if;
1084 -- 11074583 end
1085 tab_employer(T4ASummary_pprtr_2_sin):= convert_2_xml(substr(REPLACE(l_return,' ',''),1,9), 'pprtr_2_sin');
1086 ----
1087 tab_employer(T4ASummary_rpt_tcd) := convert_2_xml('A','rpt_tcd');
1088 ----
1089 l_final_xml_string := '<T4ASummary>'||EOL||
1090 tab_employer(T4ASummary_bn)||
1091 '<PAYR_NM>'||EOL||
1092 tab_employer(T4ASummary_l1_nm)||
1093 tab_employer(T4ASummary_l2_nm)||
1094 tab_employer(T4ASummary_l3_nm)||
1095 '</PAYR_NM>' || EOL||
1096 '<PAYR_ADDR>'||EOL||
1097 tab_employer(T4ASummary_addr_l1_txt)||
1098 tab_employer(T4ASummary_addr_l2_txt)||
1099 tab_employer(T4ASummary_cty_nm)||
1100 tab_employer(T4ASummary_prov_cd)||
1101 tab_employer(T4ASummary_cntry_cd)||
1102 tab_employer(T4ASummary_pstl_cd)||
1103 '</PAYR_ADDR>' || EOL||
1104 '<CNTC>'||EOL||
1105 tab_employer(T4ASummary_cntc_nm)||
1106 tab_employer(T4ASummary_cntc_area_cd)||
1107 tab_employer(T4ASummary_cntc_phn_nbr)||
1108 tab_employer(T4ASummary_cntc_extn_nbr)||
1109 '</CNTC>' || EOL||
1110 tab_employer(T4ASummary_tx_yr);
1111 -- added for bug 13976778
1112 if length(trim(lv_ppreg_no1)||trim(lv_ppreg_no2)||trim(lv_ppreg_no3)) > 0 then
1113 l_final_xml_string := l_final_xml_string||
1114 '<RPP_NBR>' || EOL||
1115 tab_employer(T4ASummary_rpp_rgst_1_nbr)||
1116 tab_employer(T4ASummary_rpp_rgst_2_nbr)||
1117 tab_employer(T4ASummary_rpp_rgst_3_nbr)||
1118 '</RPP_NBR>' || EOL;
1119 end if;
1120
1121 l_final_xml_string := l_final_xml_string||'<PPRTR_SIN>'||EOL||
1122 tab_employer(T4ASummary_pprtr_1_sin)||
1123 tab_employer(T4ASummary_pprtr_2_sin)||
1124 '</PPRTR_SIN>'||EOL||
1125 tab_employer(T4ASummary_rpt_tcd)||
1126 '</T4ASummary>' || EOL||
1127 '</T4A>'||EOL||'</Return>'||EOL;
1128 hr_utility.trace(l_final_xml_string);
1129 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1130 hr_utility.trace('X==========================================================X');
1131 END;
1132 END t4a_cancel_employer_record;
1133 /*****************************************************************************/
1134
1135 PROCEDURE t4a_cancel_employee_record IS
1136
1137 /*
1138 This procedure has the following sections:
1139 1. Generate T4ASlip Tags
1140 This set of tags repeat for eacch T4ASlip record.
1141 2. Generate T4A_AMT Tags
1142 This set of tags appear only on first T4ASlip record.
1143 3. Generate OTH_INFO Tags
1144 Based on the number of Other Information available, It is split into multiples
1145 of 12 and different sets of tags are generated.
1146 4. Generate the Final XML
1147 If there was an error, a single record with T4ASlip,T4A_AMT and
1148 all OTH_INFO is generated within <Fail> tag along with Error message.
1149 Else Multiple records with T4ASlip,T4A_AMT(only in first) and sets of
1150 OTH_INFO are generated within <Success> tag.
1151 */
1152 BEGIN
1153 DECLARE
1154 l_payroll_actid NUMBER;
1155 l_mag_asg_action_id NUMBER;
1156 EOL VARCHAR2(5) := fnd_global.local_chr(13)
1157 || fnd_global.local_chr(10);
1158 l_arch_action_id NUMBER;
1159 l_arch_pay_actid NUMBER;
1160 l_asg_id NUMBER;
1161 l_neg_bal_exists BOOLEAN := FALSE;
1162
1163 l_person_id per_people_f.person_id%TYPE;
1164 l_address_line1 per_addresses.address_line1%TYPE;
1165 l_address_line2 per_addresses.address_line2%TYPE;
1166 l_address_line3 per_addresses.address_line3%TYPE;
1167 l_city per_addresses.town_or_city%TYPE;
1168 l_postal_code per_addresses.postal_code%TYPE;
1169 l_country VARCHAR2(60);
1170 l_emp_province per_addresses.region_1%TYPE;
1171
1172 l_return VARCHAR2(240);
1173 l_return_xml VARCHAR2(200);
1174 l_status BOOLEAN := TRUE;
1175 l_error_message VARCHAR2(500);
1176
1177 l_other_info_count NUMBER := 0;
1178 l_other_info_sets NUMBER := 1;
1179
1180 l_final_xml_string VARCHAR2(32000);
1181 l_T4ASlip_xml_string VARCHAR2(32000);
1182 l_T4A_AMT_xml_string VARCHAR2(32000);
1183 l_all_oth_info_xml_string VARCHAR2(32000);
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(200) INDEX BY BINARY_INTEGER;
1189 tab_employee string_table;
1190 tab_other_info_dbi string_table;
1191 tab_other_info_tag string_table;
1192
1193 T4ASlip_snm NUMBER :=1;
1194 T4ASlip_gvn_nm NUMBER :=2;
1195 T4ASlip_init NUMBER :=3;
1196 T4ASlip_sin NUMBER :=4;
1197 T4ASlip_rcpnt_bn NUMBER :=5;
1198 T4ASlip_addr_l1_txt NUMBER :=6;
1199 T4ASlip_addr_l2_txt NUMBER :=7;
1200 T4ASlip_cty_nm NUMBER :=8;
1201 T4ASlip_prov_cd NUMBER :=9;
1202 T4ASlip_cntry_cd NUMBER :=10;
1203 T4ASlip_pstl_cd NUMBER :=11;
1204 T4ASlip_rcpnt_nbr NUMBER :=12;
1205 T4ASlip_bn NUMBER :=13;
1206 T4ASlip_ppln_dpsp_rgst_nbr NUMBER :=14;
1207 T4ASlip_rpt_tcd NUMBER :=15;
1208
1209 T4A_AMT_pens_spran_amt NUMBER :=101;
1210 T4A_AMT_lsp_amt NUMBER :=102;
1211 T4A_AMT_self_empl_cmsn_amt NUMBER :=103;
1212 T4A_AMT_itx_ddct_amt NUMBER :=104;
1213 T4A_AMT_annty_amt NUMBER :=105;
1214 T4A_AMT_fee_or_oth_srvc_amt NUMBER :=106;
1215
1216 CURSOR c_get_payroll_asg_actid(p_assg_actid NUMBER) IS
1217 SELECT
1218 paa.assignment_action_id,
1219 paa.payroll_action_id
1220 FROM pay_assignment_actions paa,
1221 pay_payroll_actions ppa,
1222 pay_action_interlocks pai,
1223 pay_action_interlocks pai2
1224 WHERE pai.locking_action_id = p_assg_actid
1225 AND pai.locked_action_id = pai2.locking_action_id
1226 AND pai2.locked_action_id = paa.assignment_action_id
1227 AND ppa.payroll_action_id = paa.payroll_action_id
1228 AND ppa.report_type in ('CAEOY_T4A_AMEND_PP','T4A')
1229 ORDER BY
1230 paa.assignment_action_id DESC;
1231
1232 CURSOR c_get_person_id(p_mag_asg_action_id NUMBER) IS
1233 SELECT
1234 serial_number
1235 FROM
1236 pay_assignment_actions
1237 WHERE assignment_action_id = p_mag_asg_action_id;
1238
1239 BEGIN
1240 hr_utility.trace('+==========================================================+');
1241 hr_utility.trace('Employee Record');
1242
1243 l_payroll_actid := to_number(pay_magtape_generic.get_parameter_value
1244 ('TRANSFER_PAYROLL_ACTION_ID'));
1245 hr_utility.trace('T4A_CANCEL_MAG Payroll action ='||l_payroll_actid);
1246
1247 l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value
1248 ('TRANSFER_ACT_ID'));
1249 hr_utility.trace('T4A_CANCEL_MAG Assignment action ='|| to_char(l_mag_asg_action_id));
1250
1251 OPEN c_get_payroll_asg_actid(l_mag_asg_action_id);
1252 FETCH c_get_payroll_asg_actid INTO l_arch_action_id, l_arch_pay_actid;
1253 CLOSE c_get_payroll_asg_actid;
1254 hr_utility.trace('Archiver Assignment action='|| l_arch_action_id);
1255 hr_utility.trace('Archiver Payroll action='|| l_arch_pay_actid);
1256 ----
1257 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1258 ,'CAEOY_T4A_NEGATIVE_BALANCE_EXISTS');
1259
1260 IF(l_return='Y') THEN
1261 l_status := FALSE;
1262 l_error_message := hr_general.decode_lookup('PAY_CA_MAG_EXCEPTIONS','NEG');
1263 END IF;
1264 ----
1265 /*****************************************************************************/
1266 /* T4ASlip Tags begin*/
1267 OPEN c_get_person_id(l_mag_asg_action_id);
1268 FETCH c_get_person_id INTO l_person_id;
1269 CLOSE c_get_person_id;
1270 hr_utility.trace('Person ID ='||l_person_id);
1271 ----1
1272 l_return := pay_ca_emp_address_dtls.get_emp_address(l_person_id,
1273 l_address_line1,
1274 l_address_line2,
1275 l_address_line3,
1276 l_city,
1277 l_postal_code,
1278 l_country,
1279 l_emp_province);
1280
1281 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1282 ,'CAEOY_EMPLOYEE_LAST_NAME');
1283 IF l_return IS NULL THEN
1284 l_status := FALSE;
1285 l_error_message := l_error_message||'Blank Last Name ';
1286 END IF;
1287 tab_employee(T4ASlip_snm) :=convert_2_xml(substr(l_return,1,20),'snm');
1288
1289 ----2
1290 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1291 ,'CAEOY_EMPLOYEE_FIRST_NAME');
1292 --bug 13105140
1293 /* IF l_return IS NULL THEN
1294 l_status := FALSE;
1295 l_error_message := l_error_message||'Blank First Name ';
1296 END IF; */
1297
1298 tab_employee(T4ASlip_gvn_nm) :=convert_2_xml(substr(l_return,1,12),'gvn_nm');
1299
1300 ----3
1301 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1302 ,'CAEOY_EMPLOYEE_INITIAL');
1303 tab_employee(T4ASlip_init) :=convert_2_xml(substr(l_return,1,1),'init');
1304
1305 ----4
1306 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1307 ,'CAEOY_EMPLOYEE_SIN');
1308 IF l_return IS NULL THEN
1309 tab_employee(T4ASlip_sin) := convert_2_xml('000000000', 'sin');
1310 ELSE
1311 tab_employee(T4ASlip_sin) := convert_2_xml(substr(l_return,1,9),'sin');
1312 END IF;
1313
1314 ----5
1315 tab_employee(T4ASlip_rcpnt_bn) := convert_2_xml('000000000RP0000', 'rcpnt_bn');
1316
1317 ----6,7
1318 tab_employee(T4ASlip_addr_l1_txt) := convert_2_xml(SUBSTR(ltrim(rtrim(l_address_line1)),1,30), 'addr_l1_txt');
1319 tab_employee(T4ASlip_addr_l2_txt) := convert_2_xml(SUBSTR(ltrim(rtrim(l_address_line2||' '||l_address_line3)),1,30),
1320 'addr_l2_txt');
1321
1322 ----8
1323 tab_employee(T4ASlip_cty_nm) := convert_2_xml(substr(l_city,1,28), 'cty_nm');
1324
1325 ----9
1326 tab_employee(T4ASlip_prov_cd) := convert_2_xml(l_emp_province, 'prov_cd');
1327
1328 ----10
1329 IF (upper(l_country) = 'CA') THEN
1330 tab_employee(T4ASlip_cntry_cd) := convert_2_xml('CAN', 'cntry_cd');
1331 ELSIF (upper(l_country) = 'US') THEN
1332 tab_employee(T4ASlip_cntry_cd) := convert_2_xml('USA', 'cntry_cd');
1333 ELSE
1334 tab_employee(T4ASlip_cntry_cd) := ' ';
1335 END IF;
1336
1337 ----11
1338 tab_employee(T4ASlip_pstl_cd) := convert_2_xml(substr(REPLACE(l_postal_code,' ',''),1,10), 'pstl_cd');
1339
1340 ----12
1341
1342 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1343 ,'CAEOY_EMPLOYEE_NUMBER');
1344 tab_employee(T4ASlip_rcpnt_nbr) :=convert_2_xml(substr(l_return,1,20),'rcpnt_nbr');
1345
1346 ----13
1347 l_return := pay_ca_archive_utils.get_archive_value(l_arch_pay_actid
1348 ,'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
1349 tab_employee(T4ASlip_bn) :=convert_2_xml(nvl(l_return,' '),'bn');
1350
1351 ----14
1352 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1353 ,'CAEOY_T4A_EMPLOYEE_REGISTRATION_NO');
1354 tab_employee(T4ASlip_ppln_dpsp_rgst_nbr) :=convert_2_xml(substr(l_return,1,7),'ppln_dpsp_rgst_nbr');
1355
1356 ----15
1357 tab_employee(T4ASlip_rpt_tcd) := convert_2_xml('C', 'rpt_tcd'); --Cancel
1358 ----
1359
1360 l_T4ASlip_xml_string := '<RCPNT_NM>'||EOL||
1361 tab_employee(T4ASlip_snm)||
1362 tab_employee(T4ASlip_gvn_nm)||
1363 tab_employee(T4ASlip_init)||
1364 '</RCPNT_NM>' || EOL||
1365 tab_employee(T4ASlip_sin)||
1366 tab_employee(T4ASlip_rcpnt_bn)||
1367 '<RCPNT_ADDR>'||EOL||
1368 tab_employee(T4ASlip_addr_l1_txt)||
1369 tab_employee(T4ASlip_addr_l2_txt)||
1370 tab_employee(T4ASlip_cty_nm)||
1371 tab_employee(T4ASlip_prov_cd)||
1372 tab_employee(T4ASlip_cntry_cd)||
1373 tab_employee(T4ASlip_pstl_cd)||
1374 '</RCPNT_ADDR>'||EOL||
1375 tab_employee(T4ASlip_rcpnt_nbr)||
1376 tab_employee(T4ASlip_bn)||
1377 tab_employee(T4ASlip_ppln_dpsp_rgst_nbr)||
1378 tab_employee(T4ASlip_rpt_tcd);
1379
1380
1381 /* T4ASlip Tags end */
1382 /*****************************************************************************/
1383 /* T4A_AMT Tags begin */
1384
1385 ----1
1386 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1387 ,'CAEOY_T4A_BOX16_PER_GRE_YTD');
1388 tab_employee(T4A_AMT_pens_spran_amt) :=convert_2_xml(nvl(l_return,'0'),'pens_spran_amt','C');
1389
1390 ----2
1391 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1392 ,'CAEOY_T4A_BOX18_PER_GRE_YTD');
1393 tab_employee(T4A_AMT_lsp_amt) :=convert_2_xml(nvl(l_return,'0'),'lsp_amt','C');
1394
1395 ----3
1396 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1397 ,'CAEOY_T4A_BOX20_PER_GRE_YTD');
1398 tab_employee(T4A_AMT_self_empl_cmsn_amt) :=convert_2_xml(nvl(l_return,'0'),'self_empl_cmsn_amt','C');
1399
1400 ----4
1401 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1402 ,'CAEOY_FED_WITHHELD_PER_GRE_YTD');
1403
1404 tab_employee(T4A_AMT_itx_ddct_amt) :=convert_2_xml(nvl(l_return,'0'),'itx_ddct_amt','C');
1405
1406 ----5
1407 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1408 ,'CAEOY_T4A_BOX24_PER_GRE_YTD');
1409 tab_employee(T4A_AMT_annty_amt) :=convert_2_xml(nvl(l_return,'0'),'annty_amt','C');
1410
1411 ----6
1412 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1413 ,'CAEOY_T4A_BOX48_PER_GRE_YTD');
1414 tab_employee(T4A_AMT_fee_or_oth_srvc_amt) :=convert_2_xml(nvl(l_return,'0'),'fee_or_oth_srvc_amt','C');
1415
1416 l_T4A_AMT_xml_string := '<T4A_AMT>'||
1417 tab_employee(T4A_AMT_pens_spran_amt)||
1418 tab_employee(T4A_AMT_lsp_amt)||
1419 tab_employee(T4A_AMT_self_empl_cmsn_amt)||
1420 tab_employee(T4A_AMT_itx_ddct_amt)||
1421 tab_employee(T4A_AMT_annty_amt)||
1422 tab_employee(T4A_AMT_fee_or_oth_srvc_amt)||
1423 '</T4A_AMT>';
1424
1425 /* T4A_AMT Tags end */
1426 /*****************************************************************************/
1427 /* OTH_INFO Tags begin */
1428
1429 tab_other_info_dbi(1) := 'CAEOY_T4A_OTHER_INFO_AMOUNT026_PER_GRE_YTD';
1430 tab_other_info_dbi(2) := 'CAEOY_T4A_OTHER_INFO_AMOUNT027_PER_GRE_YTD';
1431 tab_other_info_dbi(3) := 'CAEOY_T4A_OTHER_INFO_AMOUNT028_PER_GRE_YTD';
1432 tab_other_info_dbi(4) := 'CAEOY_T4A_OTHER_INFO_AMOUNT030_PER_GRE_YTD';
1433 tab_other_info_dbi(5) := 'CAEOY_T4A_OTHER_INFO_AMOUNT032_PER_GRE_YTD';
1434 tab_other_info_dbi(6) := 'CAEOY_T4A_OTHER_INFO_AMOUNT034_PER_GRE_YTD';
1435 tab_other_info_dbi(7) := 'CAEOY_T4A_OTHER_INFO_AMOUNT040_PER_GRE_YTD';
1436 tab_other_info_dbi(8) := 'CAEOY_T4A_OTHER_INFO_AMOUNT042_PER_GRE_YTD';
1437 tab_other_info_dbi(9) := 'CAEOY_T4A_OTHER_INFO_AMOUNT046_PER_GRE_YTD';
1438 tab_other_info_dbi(10) := 'CAEOY_T4A_OTHER_INFO_AMOUNT102_PER_GRE_YTD';
1439 tab_other_info_dbi(11) := 'CAEOY_T4A_OTHER_INFO_AMOUNT104_PER_GRE_YTD';
1440 tab_other_info_dbi(12) := 'CAEOY_T4A_OTHER_INFO_AMOUNT105_PER_GRE_YTD';
1441 tab_other_info_dbi(13) := 'CAEOY_T4A_OTHER_INFO_AMOUNT106_PER_GRE_YTD';
1442 tab_other_info_dbi(14) := 'CAEOY_T4A_OTHER_INFO_AMOUNT107_PER_GRE_YTD';
1443 tab_other_info_dbi(15) := 'CAEOY_T4A_OTHER_INFO_AMOUNT108_PER_GRE_YTD';
1444 tab_other_info_dbi(16) := 'CAEOY_T4A_OTHER_INFO_AMOUNT109_PER_GRE_YTD';
1445 tab_other_info_dbi(17) := 'CAEOY_T4A_OTHER_INFO_AMOUNT110_PER_GRE_YTD';
1446 tab_other_info_dbi(18) := 'CAEOY_T4A_OTHER_INFO_AMOUNT111_PER_GRE_YTD';
1447 tab_other_info_dbi(19) := 'CAEOY_T4A_OTHER_INFO_AMOUNT115_PER_GRE_YTD';
1448 tab_other_info_dbi(20) := 'CAEOY_T4A_OTHER_INFO_AMOUNT116_PER_GRE_YTD';
1449 tab_other_info_dbi(21) := 'CAEOY_T4A_OTHER_INFO_AMOUNT117_PER_GRE_YTD';
1450 tab_other_info_dbi(22) := 'CAEOY_T4A_OTHER_INFO_AMOUNT118_PER_GRE_YTD';
1451 tab_other_info_dbi(23) := 'CAEOY_T4A_OTHER_INFO_AMOUNT119_PER_GRE_YTD';
1452 tab_other_info_dbi(24) := 'CAEOY_T4A_OTHER_INFO_AMOUNT122_PER_GRE_YTD';
1453 tab_other_info_dbi(25) := 'CAEOY_T4A_OTHER_INFO_AMOUNT123_PER_GRE_YTD';
1454 tab_other_info_dbi(26) := 'CAEOY_T4A_OTHER_INFO_AMOUNT124_PER_GRE_YTD';
1455 tab_other_info_dbi(27) := 'CAEOY_T4A_OTHER_INFO_AMOUNT125_PER_GRE_YTD';
1456 tab_other_info_dbi(28) := 'CAEOY_T4A_OTHER_INFO_AMOUNT126_PER_GRE_YTD';
1457 tab_other_info_dbi(29) := 'CAEOY_T4A_OTHER_INFO_AMOUNT127_PER_GRE_YTD';
1458 tab_other_info_dbi(30) := 'CAEOY_T4A_OTHER_INFO_AMOUNT129_PER_GRE_YTD';
1459 tab_other_info_dbi(31) := 'CAEOY_T4A_OTHER_INFO_AMOUNT130_PER_GRE_YTD';
1460 tab_other_info_dbi(32) := 'CAEOY_T4A_OTHER_INFO_AMOUNT131_PER_GRE_YTD';
1461 tab_other_info_dbi(33) := 'CAEOY_T4A_OTHER_INFO_AMOUNT132_PER_GRE_YTD';
1462 tab_other_info_dbi(34) := 'CAEOY_T4A_OTHER_INFO_AMOUNT133_PER_GRE_YTD';
1463 tab_other_info_dbi(35) := 'CAEOY_T4A_OTHER_INFO_AMOUNT134_PER_GRE_YTD';
1464 tab_other_info_dbi(36) := 'CAEOY_T4A_OTHER_INFO_AMOUNT135_PER_GRE_YTD';
1465 tab_other_info_dbi(37) := 'CAEOY_T4A_OTHER_INFO_AMOUNT142_PER_GRE_YTD';
1466 tab_other_info_dbi(38) := 'CAEOY_T4A_OTHER_INFO_AMOUNT143_PER_GRE_YTD';
1467 tab_other_info_dbi(39) := 'CAEOY_T4A_OTHER_INFO_AMOUNT144_PER_GRE_YTD';
1468 tab_other_info_dbi(40) := 'CAEOY_T4A_OTHER_INFO_AMOUNT146_PER_GRE_YTD';
1469 tab_other_info_dbi(41) := 'CAEOY_T4A_OTHER_INFO_AMOUNT148_PER_GRE_YTD';
1470 tab_other_info_dbi(42) := 'CAEOY_T4A_OTHER_INFO_AMOUNT150_PER_GRE_YTD';
1471 tab_other_info_dbi(43) := 'CAEOY_T4A_OTHER_INFO_AMOUNT152_PER_GRE_YTD';
1472 tab_other_info_dbi(44) := 'CAEOY_T4A_OTHER_INFO_AMOUNT154_PER_GRE_YTD';
1473 tab_other_info_dbi(45) := 'CAEOY_T4A_OTHER_INFO_AMOUNT156_PER_GRE_YTD';
1474 tab_other_info_dbi(46) := 'CAEOY_T4A_OTHER_INFO_AMOUNT158_PER_GRE_YTD';
1475 tab_other_info_dbi(47) := 'CAEOY_T4A_OTHER_INFO_AMOUNT180_PER_GRE_YTD';
1476 tab_other_info_dbi(48) := 'CAEOY_T4A_OTHER_INFO_AMOUNT190_PER_GRE_YTD';
1477
1478 /* XML Tags for corresponding other info amounts*/
1479 tab_other_info_tag(1) := 'elg_rtir_amt';
1480 tab_other_info_tag(2) := 'nelg_rtir_amt';
1481 tab_other_info_tag(3) := 'oth_incamt';
1482 tab_other_info_tag(4) := 'ptrng_aloc_amt';
1483 tab_other_info_tag(5) := 'rpp_past_srvc_amt';
1484 tab_other_info_tag(6) := 'padj_amt';
1485 tab_other_info_tag(7) := 'resp_aip_amt';
1486 tab_other_info_tag(8) := 'resp_educt_ast_amt';
1487 tab_other_info_tag(9) := 'chrty_dons_amt';
1488 tab_other_info_tag(10) := 'nr_lsp_trnsf_amt';
1489 tab_other_info_tag(11) := 'rsch_grnt_amt';
1490 tab_other_info_tag(12) := 'brsy_amt';
1491 tab_other_info_tag(13) := 'dth_ben_amt';
1492 tab_other_info_tag(14) := 'wag_ls_incamt';
1493 tab_other_info_tag(15) := 'lsp_rpp_nelg_amt';
1494 tab_other_info_tag(16) := 'nrgst_ppln_amt';
1495 tab_other_info_tag(17) := 'pr_71_acr_lsp_amt';
1496 tab_other_info_tag(18) := 'inc_avg_annty_amt';
1497 tab_other_info_tag(19) := 'dpsp_ins_pay_amt';
1498 tab_other_info_tag(20) := 'med_trvl_amt';
1499 tab_other_info_tag(21) := 'loan_ben_amt';
1500 tab_other_info_tag(22) := 'med_prem_ben_amt';
1501 tab_other_info_tag(23) := 'grp_trm_life_amt';
1502 tab_other_info_tag(24) := 'resp_aip_oth_amt';
1503 tab_other_info_tag(25) := 'ins_rvk_dpsp_amt';
1504 tab_other_info_tag(26) := 'brd_wrk_site_amt';
1505 tab_other_info_tag(27) := 'dsblt_ben_amt';
1506 tab_other_info_tag(28) := 'pr_90_rpp_amt';
1507 tab_other_info_tag(29) := 'vtrn_ben_amt';
1508 tab_other_info_tag(30) := 'tx_dfr_ptrng_dvamt';
1509 tab_other_info_tag(31) := 'atp_inctv_grnt_amt';
1510 tab_other_info_tag(32) := 'rdsp_amt';
1511 tab_other_info_tag(33) := 'wag_ptct_pgm_amt';
1512 tab_other_info_tag(34) := 'var_pens_ben_amt';
1513 tab_other_info_tag(35) := 'tfsa_tax_amt';
1514 tab_other_info_tag(36) := 'rcpnt_pay_prem_phsp_amt';
1515 tab_other_info_tag(37) := 'indn_elg_rtir_amt';
1516 tab_other_info_tag(38) := 'indn_nelg_rtir_amt';
1517 tab_other_info_tag(39) := 'indn_oth_incamt';
1518 tab_other_info_tag(40) := 'indn_xmpt_pens_amt';
1519 tab_other_info_tag(41) := 'indn_xmpt_lsp_amt';
1520 tab_other_info_tag(42) := 'lbr_adj_ben_aprpt_act_amt';
1521 tab_other_info_tag(43) := 'subp_qlf_amt';
1522 tab_other_info_tag(44) := 'csh_awrd_pze_payr_amt';
1523 tab_other_info_tag(45) := 'bkcy_sttl_amt';
1524 tab_other_info_tag(46) := 'lsp_nelg_trnsf_amt';
1525 tab_other_info_tag(47) := 'lsp_dpsp_nelg_amt';
1526 tab_other_info_tag(48) := 'lsp_nrgst_pens_amt';
1527
1528 IF l_status THEN
1529 hr_utility.trace('Status = Success');
1530 ELSE
1531 hr_utility.trace('Status = Fail');
1532 END IF;
1533
1534
1535 l_other_info_xml_string(1) := ' ';
1536 l_other_info_xml_string(2) := ' ';
1537 l_other_info_xml_string(3) := ' ';
1538 l_other_info_xml_string(4) := ' ';
1539
1540 FOR i IN 1..tab_other_info_dbi.COUNT LOOP
1541
1542 l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id
1543 ,tab_other_info_dbi(i));
1544
1545 IF nvl(l_return,0)<>0 THEN
1546 l_return_xml := convert_2_xml(l_return ,tab_other_info_tag(i),'C');
1547 hr_utility.trace(tab_other_info_dbi(i)||' => '||l_return_xml);
1548 l_other_info_count := l_other_info_count+1;
1549 IF l_status = FALSE THEN --Error report has single record A02
1550 l_other_info_xml_string(1) := l_other_info_xml_string(1)||l_return_xml;
1551 ELSIF l_status = TRUE THEN --Success report has multiple records
1552 IF l_other_info_count<=12 THEN
1553 l_other_info_xml_string(1) := l_other_info_xml_string(1)||l_return_xml;
1554 ELSIF l_other_info_count<=24 THEN
1555 l_other_info_sets:=2;
1556 l_other_info_xml_string(2) := l_other_info_xml_string(2)||l_return_xml;
1557 ELSIF l_other_info_count<=36 THEN
1558 l_other_info_sets:=3;
1559 l_other_info_xml_string(3) := l_other_info_xml_string(3)||l_return_xml;
1560 ELSIF l_other_info_count<=48 THEN
1561 l_other_info_sets:=4;
1562 l_other_info_xml_string(4) := l_other_info_xml_string(4)||l_return_xml;
1563 END IF;
1564 END IF;
1565 END IF;
1566 END LOOP;
1567
1568 hr_utility.trace('Other_info_count = '||l_other_info_count ||', Other_info_sets = '||l_other_info_sets);
1569
1570 /* OTH_INFO Tags end */
1571 /*****************************************************************************/
1572 /* Final XML Generation begin */
1573
1574 l_final_xml_string:=' ';
1575
1576 IF l_status = TRUE THEN --Multiple Records
1577
1578 FOR i IN 1..l_other_info_sets LOOP
1579 l_final_xml_string:=l_final_xml_string||'<Success>' || EOL;
1580 l_final_xml_string := l_final_xml_string||
1581 '<T4ASlip>'||EOL||l_T4ASlip_xml_string||EOL;
1582 IF i = 1 THEN
1583 l_final_xml_string := l_final_xml_string||l_T4A_AMT_xml_string||EOL;
1584 END IF;
1585 IF l_other_info_xml_string(i)<>' ' THEN
1586 l_final_xml_string := l_final_xml_string||'<OTH_INFO>'|| EOL||
1587 l_other_info_xml_string(i)||
1588 '</OTH_INFO>'|| EOL;
1589 END IF;
1590 l_final_xml_string := l_final_xml_string||'</T4ASlip>' || EOL;
1591 l_final_xml_string:=l_final_xml_string||'</Success>' || EOL;
1592 END LOOP;
1593
1594 ELSIF l_status = FALSE THEN --Single Record
1595 l_final_xml_string:=l_final_xml_string||'<Fail>' || EOL;
1596 l_final_xml_string:=l_final_xml_string||'<T4ASlip>'||EOL||l_T4ASlip_xml_string||EOL;
1597 l_final_xml_string:=l_final_xml_string||l_T4A_AMT_xml_string||EOL;
1598 l_final_xml_string:=l_final_xml_string||'<OTH_INFO>'|| EOL||
1599 l_other_info_xml_string(1)||
1600 '</OTH_INFO>'|| EOL||'</T4ASlip>' || EOL;
1601 l_final_xml_string:=l_final_xml_string||'<Error_msg>'||l_error_message||'</Error_msg>'
1602 ||EOL||'</Fail>'||EOL;
1603 END IF;
1604
1605 hr_utility.trace(l_final_xml_string);
1606 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1607 hr_utility.trace('X==========================================================X');
1608
1609 /* Final XML Generation end */
1610 /*****************************************************************************/
1611 END;
1612
1613
1614 END t4a_cancel_employee_record;
1615
1616 END pay_ca_t4a_cancel_mag;