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