[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_LE_MIGRATE
Source
1 PACKAGE BODY pay_ie_le_migrate AS
2 /* $Header: pyiemigr.pkb 120.8 2012/01/19 09:28:00 rpahune ship $ */
3 TYPE balance_rec IS RECORD (
4 old_defined_balance_id NUMBER,
5 new_defined_balance_id NUMBER,
6 balance_name VARCHAR2(150)
7 );
8
9 TYPE balance_table IS TABLE OF balance_rec INDEX BY BINARY_INTEGER;
10 g_statutory_balance_table balance_table;
11 g_max_balance_index NUMBER := 8;
12 l_asg_found varchar2(1) := 'N';
13 l_stage varchar2(300);
14
15 PROCEDURE revert_migration(p_bg_id IN NUMBER) IS
16 CURSOR c_get_org_details(l_bg_id NUMBER) IS
17 SELECT hou.name,
18 hou.date_from,
19 hou.date_to,
20 hou.internal_external_flag,
21 hoi.*,
22 decode(hoi.org_information11,'YY','YY'
23 ,'Y','YN',
24 'YYY','YYY'
25 ,'NN') migrated_flag
26 FROM hr_organization_units hou,
27 hr_organization_information hoi
28 WHERE hou.organization_id = l_bg_id
29 AND hoi.organization_id = hou.organization_id
30 AND hoi.org_information_context = 'IE_ORG_INFORMATION'
31 AND NVL(hoi.org_information11,'NN') <> 'NN';
32
33 CURSOR c_get_er_details(l_bg_id NUMBER,l_name VARCHAR2) IS
34 SELECT hou.organization_id,hou.name
35 FROM hr_organization_units hou
36 ,hr_organization_information hoi1
37 ,hr_organization_information hoi2
38 WHERE hou.organization_id = hoi1.organization_id
39 AND hou.organization_id = hoi2.organization_id
40 AND hou.business_group_id = l_bg_id
41 AND hoi1.org_information_context = 'CLASS'
42 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
43 AND hoi1.org_information2 = 'Y'
44 AND hoi2.org_information_context = 'IE_EMPLOYER_INFO'
45 AND hou.name = l_name;
46
47
48 -- Cursor to fetch all the assignment actions in 2005
49 CURSOR c_get_asg_action(p_bg_id NUMBER,p_tax_unit_id NUMBER) IS
50 SELECT paa.*,ppa.action_type action_type,ppa.effective_date effective_date,
51 ppa.report_type,ppa.report_qualifier
52 FROM pay_assignment_actions paa,
53 pay_payroll_actions ppa
54 WHERE paa.payroll_action_id = ppa.payroll_action_id
55 AND paa.action_status IN ('C','S') --10225372
56 AND ppa.business_group_id = p_bg_id
57 AND ppa.action_type in ('R','Q','P','U','I','B','V','X')
58 AND (ppa.action_type <> 'X' OR ppa.report_type <> 'P45')
59 AND to_char(ppa.effective_date,'YYYY') = 2005
60 AND paa.tax_unit_id = p_tax_unit_id;
61
62 -- Procedure for Migrating Assignment action info to be striped by Tax unit ID and Update
66 CURSOR c_get_def_bal(p_balance_name VARCHAR2) IS
63 -- Action Information PER_YTD with _PER_PAYE_REF_YTD due to bug 4655083
64 -- Cursor to fetch assignment actions for a payroll
65
67 SELECT def_old.defined_balance_id old_id
68 ,def_new.defined_balance_id new_id
69 FROM pay_balance_types pbt
70 ,pay_balance_dimensions dim_old
71 ,pay_balance_dimensions dim_new
72 ,pay_defined_balances def_old
73 ,pay_defined_balances def_new
74 WHERE pbt.balance_name = p_balance_name
75 AND dim_old.database_item_suffix = '_PER_PAYE_REF_YTD'
76 AND dim_old.legislation_code = 'IE'
77 AND dim_new.legislation_code = 'IE'
78 AND dim_new.database_item_suffix = '_PER_YTD'
79 AND pbt.legislation_code = 'IE'
80 AND def_old.balance_type_id = pbt.balance_type_id
81 AND def_old.balance_dimension_id = dim_old.balance_dimension_id
82 AND def_new.balance_type_id = pbt.balance_type_id
83 AND def_new.balance_dimension_id = dim_new.balance_dimension_id;
84
85 -- Cursor to fetch the Action Information Details
86 CURSOR c_get_act_info(p_context_id NUMBER,p_defbal_id NUMBER) IS
87 SELECT pai.action_information_id,pai.object_version_number,pai.source_id,pai.action_information4
88 FROM pay_action_information pai
89 WHERE pai.action_context_id = p_context_id
90 AND pai.action_information1 = p_defbal_id
91 AND pai.action_context_type = 'AAP'
92 AND pai.action_information_category = 'EMEA BALANCES';
93 l_old_id NUMBER;
94 l_new_id NUMBER;
95 l_act_info_id NUMBER;
96 l_ovn NUMBER;
97 l_source_id NUMBER;
98 l_value NUMBER;
99 l_old_value VARCHAR2(50);
100 l_object_version_number NUMBER;
101
102 begin
103
104 -- Setup the balance Table
105 g_statutory_balance_table(1).balance_name := 'IE Taxable Pay';
106 g_statutory_balance_table(2).balance_name := 'IE Net Tax';
107 g_statutory_balance_table(3).balance_name := 'IE PRSI Employee';
108 g_statutory_balance_table(4).balance_name := 'IE PRSI K Employee Lump Sum';
109 g_statutory_balance_table(5).balance_name := 'IE PRSI M Employee Lump Sum';
110 g_statutory_balance_table(6).balance_name := 'IE PRSI Employer';
111 g_statutory_balance_table(7).balance_name := 'IE PRSI K Employer Lump Sum';
112 g_statutory_balance_table(8).balance_name := 'IE PRSI M Employer Lump Sum';
113
114 FOR l_index in 1 .. g_max_balance_index LOOP
115 l_old_id := NULL;
116 l_new_id := NULL;
117
118 OPEN c_get_def_bal(g_statutory_balance_table(l_index).balance_name);
119 FETCH c_get_def_bal INTO l_old_id,l_new_id;
120 g_statutory_balance_table(l_index).old_defined_balance_id := l_old_id;
121 g_statutory_balance_table(l_index).new_defined_balance_id := l_new_id;
122 CLOSE c_get_def_bal;
123
124 END LOOP;
125 -- Balance setup ends here
126
127
128 FOR v_er_bg in c_get_org_details(p_bg_id) LOOP
129 FOR v_er_le in c_get_er_details(p_bg_id,v_er_bg.org_information8) LOOP
130
131 FOR v_assact IN c_get_asg_action(p_bg_id,v_er_le.organization_id) LOOP
132
133 UPDATE pay_assignment_actions paa
134 SET tax_unit_id = NULL
135 WHERE paa.assignment_action_id = v_assact.assignment_action_id;
136
137
138 IF (v_assact.report_type = 'IEPS' AND v_assact.report_qualifier = 'IE'
139 AND v_assact.source_action_id IS NULL) THEN
140
141
142
143 FOR l_index in 1 .. g_max_balance_index LOOP
144 l_act_info_id := NULL;
145 l_ovn := NULL;
146 l_source_id := NULL;
147 l_value := 0;
148 OPEN c_get_act_info(v_assact.assignment_action_id,g_statutory_balance_table(l_index).old_defined_balance_id);
149 FETCH c_get_act_info INTO l_act_info_id,l_ovn,l_source_id,l_old_value;
150 CLOSE c_get_act_info;
151 IF l_act_info_id IS NOT NULL THEN
152 l_value := pay_balance_pkg.get_value(g_statutory_balance_table(l_index).new_defined_balance_id,
153 l_source_id,v_er_le.organization_id,NULL,NULL,NULL,NULL,NULL);
154 IF l_value <> 0 THEN
155 pay_action_information_api.update_action_information
156 (p_action_information_id => l_act_info_id
157 ,p_object_version_number => l_ovn
158 ,p_action_information1 => g_statutory_balance_table(l_index).new_defined_balance_id
159 ,p_action_information4 => fnd_number.number_to_canonical(l_value)
160 );
161
162 END IF;
163
164 END IF;
165 END LOOP;
166 END IF;
167
168 END LOOP;
169
170 BEGIN
171 l_object_version_number := v_er_bg.object_version_number;
172 HR_ORGanization_api.update_org_information
173 (
174 p_validate => FALSE,
175 p_effective_date => sysdate,
176 p_org_information_id => v_er_bg.org_information_id,
177 p_org_info_type_code => 'IE_ORG_INFORMATION',
178 p_org_information2 => v_er_bg.org_information2,
179 p_org_information11 => 'Y',
180 p_object_version_number => l_object_version_number
181 );
182 EXCEPTION
183 WHEN OTHERS
184 THEN
185 ROLLBACK;
186 raise_application_error(-20001, sqlerrm);
187 END;
188
189 COMMIT;
190 END LOOP;
191 END LOOP;
192 END revert_migration;
193
194 PROCEDURE migrate_asg_act_actinfo_tu(p_bg_id NUMBER,p_org_id NUMBER,p_payroll_id NUMBER,p_start_date DATE,p_end_date DATE,p_migrate_archive_data varchar2,p_p45_migrated NUMBER) IS
195
199 ppa.report_type,ppa.report_qualifier,asg.person_id person_id
196 -- Cursor to fetch all the assignment actions in 2005
197 CURSOR c_get_asg_action(p_bg_id NUMBER,p_payroll_id NUMBER,p_start_date DATE,p_end_date DATE) IS
198 SELECT distinct paa.*,ppa.action_type action_type,ppa.effective_date effective_date,
200 FROM pay_assignment_actions paa,
201 pay_payroll_actions ppa,
202 per_all_assignments_f asg
203 WHERE paa.payroll_action_id = ppa.payroll_action_id
204 AND paa.action_status IN ('C','S') --10225372
205 AND ppa.effective_date between p_start_date and p_end_date
206 AND paa.assignment_id = asg.assignment_id
207 --AND ppa.effective_date between asg.effective_start_date and asg.effective_end_date
208 AND asg.payroll_id = p_payroll_id
209 AND ppa.business_group_id = p_bg_id
210 AND (
211 (p_migrate_archive_data = 'N' and ppa.action_type in ('R','Q','P','U','I','B','V')) or
212 (p_migrate_archive_data = 'Y' and ppa.action_type in ('R','Q','P','U','I','B','V','X'))
213 )
214 -- AND ppa.action_type in ('R','Q','P','U','I','B','V','X')
215 AND (ppa.action_type <> 'X' OR ppa.report_type <> 'P45' OR p_p45_migrated = 1)
216 AND to_char(ppa.effective_date,'YYYY') = 2005
217 AND paa.tax_unit_id IS NULL
218 ORDER BY ppa.action_type ASC,
219 ppa.report_qualifier,
220 ppa.report_type,
221 paa.assignment_id,
222 paa.assignment_action_id DESC;
223
224 -- Cursor to fetch the Action Information Details
225 CURSOR c_get_act_info(p_context_id NUMBER,p_defbal_id NUMBER) IS
226 SELECT pai.action_information_id,pai.object_version_number,pai.source_id,pai.action_information4
227 FROM pay_action_information pai
228 WHERE pai.action_context_id = p_context_id
229 AND pai.action_information1 = p_defbal_id
230 AND pai.action_context_type = 'AAP'
231 AND pai.action_information_category = 'EMEA BALANCES';
232
233 -- Cursor to fetch the action information details
234 CURSOR c_get_action_info(p_asg_action_id NUMBER,p_act_info_cat VARCHAR2) IS
235 SELECT pai.action_information_id,pai.object_version_number
236 FROM pay_action_information pai
237 WHERE pai.action_context_id = p_asg_action_id
238 AND pai.action_information_category = p_act_info_cat;
239
240 -- Cursor to fetch the Commencement Date of the Employee
241 CURSOR c_get_comm_date(p_asg_action_id NUMBER) IS
242 SELECT act_inf.action_information11
243 FROM pay_action_information act_inf
244 WHERE act_inf.action_context_id = p_asg_action_id
245 AND act_inf.action_information_category = 'EMPLOYEE DETAILS'
246 AND act_inf.action_context_type = 'AAP';
247
248
249 l_asg_id NUMBER := -1;
250 l_act_info_id NUMBER;
251 l_ovn NUMBER;
252 l_source_id NUMBER;
253 l_value NUMBER;
254 l_old_value VARCHAR2(50);
255 l_object_version_number NUMBER;
256 l_comm_date VARCHAR2(30);
257
258 BEGIN
259 for v_assact IN c_get_asg_action(p_bg_id,
260 p_payroll_id,
261 p_start_date,
262 p_end_date
263 ) LOOP
264
265 IF l_asg_found = 'N' THEN
266 fnd_file.put_line(FND_FILE.LOG,' ');
267 fnd_file.put_line(FND_FILE.LOG,' ');
268 fnd_file.put_line(FND_FILE.LOG,'Assignment Action Migartion Details');
269 fnd_file.put_line(FND_FILE.LOG,' ');
270 fnd_file.put_line(FND_FILE.LOG,rpad(lpad('Assignment ID',14),16)||' '||'Action Type'||' '||'Effective Date'||' '||'Report Type');
271 l_asg_found := 'Y';
272 END IF;
273
274 l_stage := 'Update Asg Action' || v_assact.assignment_action_id;
275 fnd_file.put_line(FND_FILE.LOG,rpad(lpad(v_assact.assignment_id,11),16)||' '||rpad(lpad(v_assact.action_type,6),11)||' '||rpad(lpad(v_assact.effective_date,11),14)||' '||rpad(lpad(v_assact.report_type,8),11));
276 -- Update the assignment actions
277 UPDATE pay_assignment_actions paa
278 SET tax_unit_id = p_org_id
279 WHERE paa.assignment_action_id = v_assact.assignment_action_id;
280
281 IF (v_assact.report_type = 'P45' AND v_assact.report_qualifier = 'IE' AND v_assact.source_action_id IS NULL) THEN
282 FOR v_get_act_info IN c_get_action_info(v_assact.assignment_action_id,'IE EMPLOYEE DETAILS') LOOP
283 l_object_version_number := v_get_act_info.object_version_number;
284 l_act_info_id := v_get_act_info.action_information_id;
285 l_comm_date := NULL;
286
287 OPEN c_get_comm_date(v_assact.assignment_action_id);
288 FETCH c_get_comm_date INTO l_comm_date;
289 CLOSE c_get_comm_date;
290
291 IF l_comm_date IS NOT NULL THEN
292
293 l_stage := 'Update Employee Details' || v_assact.assignment_action_id || ' date '|| l_comm_date;
294 fnd_file.put_line(FND_FILE.LOG,'Updating Commencement Date to '||l_comm_date);
295 pay_action_information_api.update_action_information
296 (p_action_information_id => l_act_info_id
297 ,p_object_version_number => l_object_version_number
298 ,p_action_information30 => l_comm_date
299 );
300 END IF;
301 END LOOP;
302
303 FOR v_get_act_info IN c_get_action_info(v_assact.assignment_action_id,'IE P45 INFORMATION') LOOP
304 l_object_version_number := v_get_act_info.object_version_number;
308 fnd_file.put_line(FND_FILE.LOG,'Updating Person ID to '||v_assact.person_id);
305 l_act_info_id := v_get_act_info.action_information_id;
306
307 l_stage := 'Update P45 Information' || v_assact.assignment_action_id || ' person '|| v_assact.person_id;
309 pay_action_information_api.update_action_information
310 (p_action_information_id => l_act_info_id
311 ,p_object_version_number => l_object_version_number
312 ,p_action_information8 => v_assact.person_id
313 );
314
315 END LOOP;
316
317
318 END IF;
319 IF (v_assact.report_type = 'IEPS' AND v_assact.report_qualifier = 'IE'
320 AND v_assact.source_action_id IS NULL) THEN
321 IF v_assact.assignment_id <> l_asg_id THEN
322 l_stage := 'Update Action Information' || v_assact.assignment_action_id;
323
324 FOR l_index in 1 .. g_max_balance_index LOOP
325 l_act_info_id := NULL;
326 l_ovn := NULL;
327 l_source_id := NULL;
328 l_value := 0;
329
330 OPEN c_get_act_info(v_assact.assignment_action_id,g_statutory_balance_table(l_index).old_defined_balance_id);
331 FETCH c_get_act_info INTO l_act_info_id,l_ovn,l_source_id,l_old_value;
332 CLOSE c_get_act_info;
333
334 IF l_act_info_id IS NOT NULL THEN
335 l_value := pay_balance_pkg.get_value(g_statutory_balance_table(l_index).new_defined_balance_id,
336 l_source_id,p_org_id,NULL,NULL,NULL,NULL,NULL);
337 IF l_value <> 0 THEN
338 pay_action_information_api.update_action_information
339 (p_action_information_id => l_act_info_id
340 ,p_object_version_number => l_ovn
341 ,p_action_information1 => g_statutory_balance_table(l_index).new_defined_balance_id
342 ,p_action_information4 => fnd_number.number_to_canonical(l_value)
343 );
344 fnd_file.put_line(FND_FILE.LOG,'Updating Balance '|| g_statutory_balance_table(l_index).balance_name || ' with value ='|| fnd_number.number_to_canonical(l_value) || ' from Old Value '|| l_old_value ||' Asg action = '|| v_assact.assignment_action_id);
345
346 END IF;
347
348 END IF;
349
350 END LOOP;
351
352 END IF;
353
354
355 l_asg_id := v_assact.assignment_id;
356
357 END IF;
358 END LOOP;
359
360 END migrate_asg_act_actinfo_tu;
361
362 PROCEDURE migrate_data(errbuf OUT NOCOPY VARCHAR2,
363 retcode OUT NOCOPY VARCHAR2,
364 p_bg_id IN NUMBER) IS
365
366 /* Cursor Fetch Non-Migrated Employer Details defined at BG Level */
367 CURSOR c_get_org_details(l_bg_id NUMBER,l_p45_migrated NUMBER) IS
368 SELECT hou.name,
369 hou.date_from,
370 hou.date_to,
371 hou.internal_external_flag,
372 hoi.*,
373 decode(hoi.org_information11,'YY','YY'
374 ,'Y','YN',
375 'YYY','YYY'
376 ,'NN') migrated_flag
377 FROM hr_organization_units hou,
378 hr_organization_information hoi
379 WHERE hou.organization_id = l_bg_id
380 AND hoi.organization_id = hou.organization_id
381 AND hoi.org_information_context = 'IE_ORG_INFORMATION'
382 AND (
383 ( nvl(hoi.org_information11,'NN') <> 'YY' AND l_p45_migrated = 0) OR
384 ( nvl(hoi.org_information11,'NN') <> 'YYY' AND l_p45_migrated = 1)
385 );
386
387 -- Cursor to fetch date-tracked payroll records for an Employer
388 CURSOR c_get_payroll_details(p_bg_id NUMBER,p_tax_ref VARCHAR2,p_paye_ref VARCHAR2) IS
389 SELECT scl.id_flex_num id_flex_num,scl.segment2 segment2,
390 scl.segment1 segment1,scl.segment3 segment3,
391 pap.*
392 FROM pay_all_payrolls_f pap,
393 hr_soft_coding_keyflex scl
394 WHERE pap.business_group_id = p_bg_id
395 AND pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
396 AND scl.segment1 = p_tax_ref
397 AND scl.segment3 = p_paye_ref
398 AND scl.segment4 IS NULL;
399
400 /* Cursor to fetch the migrated employers in the bg */
401 CURSOR c_get_er_details(l_bg_id number,l_name varchar2) IS
402 SELECT hou.organization_id,hou.name
403 FROM hr_organization_units hou
404 ,hr_organization_information hoi1
405 ,hr_organization_information hoi2
406 WHERE hou.organization_id = hoi1.organization_id
407 AND hou.organization_id = hoi2.organization_id
408 AND hou.business_group_id = l_bg_id
409 AND hoi1.org_information_context = 'CLASS'
410 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
411 AND hoi1.org_information2 = 'Y'
412 AND hoi2.org_information_context = 'IE_EMPLOYER_INFO'
413 AND hou.name = l_name;
414
415 -- Cursor to fetch non-migrated P45 actions
416 CURSOR c_get_prl_p35_details(p_bg_id number,p_tax_unit_id number) IS
417 SELECT papf.payroll_id,papf.payroll_name,papf.effective_start_date,papf.effective_end_date
418 FROM pay_all_payrolls_f papf
419 ,hr_soft_coding_keyflex hsck
420 WHERE papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
421 AND papf.business_group_id = p_bg_id
422 AND hsck.segment4 = to_char(p_tax_unit_id);
423
424 -- Procedure for Migrating Assignment action info to be striped by Tax unit ID and Update
425 -- Action Information PER_YTD with _PER_PAYE_REF_YTD due to bug 4655083
426 -- Cursor to fetch assignment actions for a payroll
427
431 FROM pay_balance_types pbt
428 CURSOR c_get_def_bal(p_balance_name VARCHAR2) IS
429 SELECT def_old.defined_balance_id old_id
430 ,def_new.defined_balance_id new_id
432 ,pay_balance_dimensions dim_old
433 ,pay_balance_dimensions dim_new
434 ,pay_defined_balances def_old
435 ,pay_defined_balances def_new
436 WHERE pbt.balance_name = p_balance_name
437 AND dim_old.database_item_suffix = '_PER_YTD'
438 AND dim_old.legislation_code = 'IE'
439 AND dim_new.legislation_code = 'IE'
440 AND dim_new.database_item_suffix = '_PER_PAYE_REF_YTD'
441 AND pbt.legislation_code = 'IE'
442 AND def_old.balance_type_id = pbt.balance_type_id
443 AND def_old.balance_dimension_id = dim_old.balance_dimension_id
444 AND def_new.balance_type_id = pbt.balance_type_id
445 AND def_new.balance_dimension_id = dim_new.balance_dimension_id;
446
447 CURSOR c_p45_applied IS
448 SELECT 1
449 FROM FND_DESCR_FLEX_COLUMN_USAGES
450 WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'Action Information DF'
451 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'IE P45 INFORMATION'
452 AND APPLICATION_COLUMN_NAME = 'ACTION_INFORMATION8'
453 AND END_USER_COLUMN_NAME = 'Person ID';
454
455 p_org_id NUMBER;
456 l_object_version_number NUMBER;
457 l_duplicate_org_warning BOOLEAN;
458 l_scl_keyflex_id NUMBER;
459 l_concat_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
460 l_payroll_id NUMBER;
461 l_start_date date;
462 l_end_date date;
463 l_comment_id NUMBER;
464 l_org_information_id NUMBER;
465 l_status BOOLEAN;
466 l_no_data_found varchar2(1);
467 l_pay_found varchar2(1);
468 l_old_id NUMBER;
469 l_new_id NUMBER;
470 l_p45_migrated number;
471
472 l_migrate_archive_data varchar2(1);
473
474 BEGIN
475
476 retcode := 0;
477 l_p45_migrated := 0;
478
479 fnd_file.put_line(FND_FILE.OUTPUT,'----------------------------------------------------------------------------------------------------');
480 fnd_file.put_line(FND_FILE.OUTPUT,' Migration Details ');
481 fnd_file.put_line(FND_FILE.OUTPUT,'----------------------------------------------------------------------------------------------------');
482 fnd_file.put_line(FND_FILE.OUTPUT,' ');
483 fnd_file.put_line(FND_FILE.OUTPUT,' ');
484
485 fnd_file.put_line(FND_FILE.LOG,'----------------------------------------------------------------------------------------------------');
486 fnd_file.put_line(FND_FILE.LOG,' Migration Details ');
487 fnd_file.put_line(FND_FILE.LOG,'----------------------------------------------------------------------------------------------------');
488 fnd_file.put_line(FND_FILE.LOG,' ');
489 fnd_file.put_line(FND_FILE.LOG,' ');
490
491 l_no_data_found := 'Y';
492
493 -- Setup the balance Table
494 g_statutory_balance_table(1).balance_name := 'IE Taxable Pay';
495 g_statutory_balance_table(2).balance_name := 'IE Net Tax';
496 g_statutory_balance_table(3).balance_name := 'IE PRSI Employee';
497 g_statutory_balance_table(4).balance_name := 'IE PRSI K Employee Lump Sum';
498 g_statutory_balance_table(5).balance_name := 'IE PRSI M Employee Lump Sum';
499 g_statutory_balance_table(6).balance_name := 'IE PRSI Employer';
500 g_statutory_balance_table(7).balance_name := 'IE PRSI K Employer Lump Sum';
501 g_statutory_balance_table(8).balance_name := 'IE PRSI M Employer Lump Sum';
502
503 FOR l_index in 1 .. g_max_balance_index LOOP
504 l_old_id := NULL;
505 l_new_id := NULL;
506
507 OPEN c_get_def_bal(g_statutory_balance_table(l_index).balance_name);
508 FETCH c_get_def_bal INTO l_old_id,l_new_id;
509 g_statutory_balance_table(l_index).old_defined_balance_id := l_old_id;
510 g_statutory_balance_table(l_index).new_defined_balance_id := l_new_id;
511 CLOSE c_get_def_bal;
512
513 END LOOP;
514 -- Balance setup ends here
515
516 OPEN c_p45_applied;
517 FETCH c_p45_applied INTO l_p45_migrated;
518 CLOSE c_p45_applied;
519
520 /* Organization Migration Start here */
521 FOR v_org IN c_get_org_details(p_bg_id,l_p45_migrated) LOOP
522 BEGIN
523
524
525 fnd_file.put_line(FND_FILE.LOG,' ');
526 fnd_file.put_line(FND_FILE.LOG,' ');
527 fnd_file.put_line(FND_FILE.LOG,' Migration Details for Org '||v_org.org_information8);
528 fnd_file.put_line(FND_FILE.LOG,'----------------------------------------------------------------------------------------------------');
529
530 fnd_file.put_line(FND_FILE.LOG,' ');
531 fnd_file.put_line(FND_FILE.LOG,'Organization Level Migartion Details');
532 fnd_file.put_line(FND_FILE.LOG,' ');
533
534 IF l_no_data_found = 'Y' THEN
535 l_no_data_found := 'N';
536 fnd_file.put_line(FND_FILE.OUTPUT,rpad(lpad('Org Id',7),20)||' '||rpad('Name',40)||' '||rpad('Paye Ref Number',15)||' '||rpad('Migrated',10));
537 fnd_file.put_line(FND_FILE.OUTPUT,' ');
538 END IF;
539
540 -- If Employer is not migrated Call this 'NN'
541
542 IF v_org.migrated_flag = 'NN' THEN
543
544 l_stage := 'Create Organization ' ||v_org.org_information8 ;
545 fnd_file.put_line(FND_FILE.LOG,'Organization Name => '|| v_org.org_information8);
546 fnd_file.put_line(FND_FILE.LOG,'Date From => '|| v_org.date_from);
547 fnd_file.put_line(FND_FILE.LOG,'Date To => '|| v_org.date_to);
551 HR_ORGANIZATION_API.create_organization(
548 fnd_file.put_line(FND_FILE.LOG,'Int Or Ext Flag => '|| v_org.internal_external_flag);
549
550 -- Create new Organization
552 p_validate => FALSE
553 ,p_effective_date => sysdate
554 ,p_business_group_id => p_bg_id
555 ,p_name => v_org.org_information8
556 ,p_date_from => v_org.date_from
557 ,p_date_to => v_org.date_to
558 ,p_internal_external_flag => v_org.internal_external_flag
559 ,p_organization_id => p_org_id
560 ,p_object_version_number => l_object_version_number
561 ,p_duplicate_org_warning => l_duplicate_org_warning
562 );
563
564 fnd_file.put_line(FND_FILE.LOG,'Organization Id => '|| p_org_id);
565
566 -- Attach classification Legal Employer
567 l_stage := 'Create Classification ' || v_org.org_information8;
568 HR_ORGANIZATION_API.create_org_class_internal(
569 p_validate => FALSE
570 ,p_effective_date => sysdate
571 ,p_organization_id => p_org_id
572 ,p_org_classif_code => 'HR_LEGAL_EMPLOYER'
573 ,p_classification_enabled => 'Y'
574 ,p_org_information_id => l_org_information_id
575 ,p_object_version_number => l_object_version_number
576 );
577 fnd_file.put_line(FND_FILE.LOG,'Classification => Legal Employer');
578
579 l_stage := 'Create Org Information ' || v_org.org_information8;
580 fnd_file.put_line(FND_FILE.LOG,'Information Type => IE_EMPLOYER_INFO');
581 fnd_file.put_line(FND_FILE.LOG,'Tax District Number => '|| v_org.Org_Information1);
582 fnd_file.put_line(FND_FILE.LOG,'PAYE Ref Number => '|| v_org.Org_Information2);
583 fnd_file.put_line(FND_FILE.LOG,'Employer Trade Name => '|| v_org.Org_Information9);
584 fnd_file.put_line(FND_FILE.LOG,'ER Tax Ref Contact => '|| v_org.Org_Information6);
585 fnd_file.put_line(FND_FILE.LOG,' ');
586 fnd_file.put_line(FND_FILE.LOG,' ');
587
588 -- Create new extra Information for the Legal Employer
589 HR_ORGanization_api.create_org_information(
590 p_validate => FALSE,
591 p_effective_date => sysdate,
592 p_Org_Information_Id => l_org_information_id,
593 p_Org_Info_type_code => 'IE_EMPLOYER_INFO',
594 p_Organization_Id => p_org_id,
595 p_Org_Information1 => v_org.Org_Information1,
596 p_Org_Information2 => v_org.Org_Information2,
597 p_Org_Information3 => v_org.Org_Information9,
598 p_Org_Information4 => v_org.Org_Information6,
599 p_object_version_number => l_object_version_number
600 );
601
602 l_pay_found := 'N';
603 l_migrate_archive_data := 'N';
604
605 /* Payroll Migration Start here */
606 for v_payroll in c_get_payroll_details(p_bg_id,v_org.Org_Information1,v_org.Org_Information2) LOOP
607
608 IF l_pay_found = 'N' THEN
609 l_pay_found := 'Y';
610 fnd_file.put_line(FND_FILE.LOG,'Payroll Level Migartion Details');
611 fnd_file.put_line(FND_FILE.LOG,' ');
612 END IF;
613 l_stage := 'Create Soft Coding Keyflex' || v_payroll.payroll_name;
614
615 fnd_file.put_line(FND_FILE.LOG,'Payroll Name => '|| v_payroll.payroll_name);
616 fnd_file.put_line(FND_FILE.LOG,'Start Date => '|| v_payroll.effective_start_date);
617 fnd_file.put_line(FND_FILE.LOG,'End Date => '|| v_payroll.effective_end_date);
618
619 hr_kflex_utility.ins_or_sel_keyflex_comb
620 (p_appl_short_name => 'PER'
621 ,p_flex_code => 'SCL'
622 ,p_flex_num => v_payroll.id_flex_num
623 -- ,p_segment1 => v_payroll.segment1
624 ,p_segment2 => v_payroll.segment2
625 -- ,p_segment3 => v_payroll.segment3
626 ,p_segment4 => p_org_id
627 ,p_ccid => l_scl_keyflex_id
628 ,p_concat_segments_out => l_concat_segments);
629 l_payroll_id := v_payroll.payroll_id;
630 l_object_version_number := v_payroll.object_version_number;
631
632 l_stage := 'Update Payroll '||v_payroll.payroll_name;
633 -- Update the payroll
634 pay_payroll_api.update_payroll
635 (
636 p_validate => FALSE,
637 p_effective_date => v_payroll.effective_start_date,
638 p_datetrack_mode => 'CORRECTION',
639 p_payroll_id => l_payroll_id,
640 p_object_version_number => l_object_version_number,
641 p_soft_coding_keyflex_id => l_scl_keyflex_id,
642 p_prl_effective_start_date => l_start_date,
643 p_prl_effective_end_date => l_end_date,
644 p_comment_id => l_comment_id
645 );
646
647 fnd_file.put_line(FND_FILE.LOG,'New Employer => '|| v_org.org_information8);
648
649 l_asg_found := 'N';
650
651 /* Assignment migration starts here */
652 migrate_asg_act_actinfo_tu(p_bg_id,p_org_id,l_payroll_id,v_payroll.effective_start_date,v_payroll.effective_end_date,l_migrate_archive_data,l_p45_migrated);
653 /* Assignment Migration Ends here */
654
655 END LOOP;
656
657 l_migrate_archive_data := 'Y';
658
659 for v_payroll in c_get_prl_p35_details(p_bg_id,p_org_id) LOOP
660
661 /* Assignment migration starts here */
662 migrate_asg_act_actinfo_tu(p_bg_id,p_org_id,l_payroll_id,v_payroll.effective_start_date,v_payroll.effective_end_date,l_migrate_archive_data,l_p45_migrated);
663 /* Assignment Migration Ends here */
664
665 END LOOP;
666 /* Payroll Migration Ends here */
667 ELSE
668
669 FOR v_p35_org IN c_get_er_details(p_bg_id,v_org.org_information8) LOOP
670 p_org_id := v_p35_org.organization_id;
671 l_migrate_archive_data := 'N';
672
673 FOR v_p35_prl IN c_get_prl_p35_details(p_bg_id,v_p35_org.organization_id) LOOP
674 migrate_asg_act_actinfo_tu(p_bg_id,v_p35_org.organization_id,v_p35_prl.payroll_id,v_p35_prl.effective_start_date,v_p35_prl.effective_end_date,l_migrate_archive_data,l_p45_migrated);
675 END LOOP;
676
677 l_migrate_archive_data := 'Y';
678 FOR v_p35_prl IN c_get_prl_p35_details(p_bg_id,v_p35_org.organization_id) LOOP
679 migrate_asg_act_actinfo_tu(p_bg_id,v_p35_org.organization_id,v_p35_prl.payroll_id,v_p35_prl.effective_start_date,v_p35_prl.effective_end_date,l_migrate_archive_data,l_p45_migrated);
680 END LOOP;
681 END LOOP;
682
683 END IF;
684
685 l_stage := 'Set Migration Status '||v_org.org_information8;
686 l_object_version_number := v_org.object_version_number;
687 -- Set the Employer Information as migrated
688 HR_ORGanization_api.update_org_information
689 (
690 p_validate => FALSE,
691 p_effective_date => sysdate,
692 p_org_information_id => v_org.org_information_id,
693 p_org_info_type_code => 'IE_ORG_INFORMATION',
694 p_org_information2 => v_org.org_information2,
695 p_org_information11 => 'YYY',
696 p_object_version_number => l_object_version_number
697 );
698
699 -- If Employer is not migrated Ends here
700
701 -- Migrate P35 for this Employer
702
703 fnd_file.put_line(FND_FILE.OUTPUT,rpad(lpad(p_org_id,7),20)||' '||rpad(v_org.org_information8,40)||' '|| rpad(v_org.Org_Information2,15)||' '||rpad('Yes',10));
704
705 COMMIT;
706 -- ROLLBACK;
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 fnd_file.put_line(FND_FILE.LOG,' ');
711 fnd_file.put_line(FND_FILE.LOG,'Error in '||l_stage);
712 fnd_file.put_line(FND_FILE.LOG,sqlerrm);
713 ROLLBACK;
714
715
716 fnd_file.put_line(FND_FILE.OUTPUT,rpad(lpad('*****',7),20)||' '||rpad(v_org.org_information8,40)||' '|| rpad(v_org.Org_Information2,15)||' '||rpad('No',10));
717
718 /* Set the program completion status to Warning */
719 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS
720 (
721 status => 'WARNING',
722 message => 'Please Check the Log File for more details'
723 );
724
725
726 END;
727
728 END LOOP;
729 /* Organization Migration Ends here */
730
731 if l_no_data_found = 'Y' THEN
732 fnd_file.put_line(FND_FILE.OUTPUT,'The data has been successfully migrated. There is no data to migrate.');
733 end if;
734
735
736 END migrate_data;
737
738 END pay_ie_le_migrate;