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