1 PACKAGE BODY per_us_eth_orig_mig_pkg AS
2 /* $Header: peusethmig.pkb 120.5 2011/10/21 06:10:15 ybudamal noship $ */
3 /*****************************************************************************
4 ******************************************************************
5 * *
6 * Copyright (C) 1996 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : per_us_eth_orig_mig_pkg
21
22 Description : This package is called by a concurrent program.
23 In this package we upgrade all old ethnic origin
24 data to new EIT.
25
26 Change List
27 -----------
28 Date Name Ver Bug No Description
29 ----------- -------- ------- --------- -------------------------------
30 31-Mar-2011 nkjaladi 115.0 11736960 Created.
31 05-Apr-2011 nkjaladi 115.1 11736960 Corrected GSCC errors and added
32 comments
33 29-Jul-2011 nkjaladi 115.2 12800711 Increased the size of variables
34 08-Aug-2011 nkjaladi 115.3 12839027 Created new procedure
35 irc_upgrade_ethnic_origin
36 14-Oct-2011 ybudamal 115.4 13031928 Increased the size of variables
37 20-Oct-2011 ybudamal 115.5 13106407 Altered declaration of variable
38 l_temp_utl
39 *****************************************************************************/
40
41 /************************************************************
42 ** Local Package Variables
43 ************************************************************/
44 type varchar2_tab is table of varchar2(100) index by binary_integer;
45 type number_tab is table of number index by binary_integer;
46 gv_name_not_gen varchar2_tab;
47 gv_package_name varchar2(50) := 'per_us_eth_orig_mig_pkg.';
48 gv_location number;
49 gv_emp_count number :=0;
50
51 /*****************************************************************************
52 Name : upgrade_ethnic_origin
53
54 Description : This procedure is called from the Concurrent Request. Based on
55 person_id passed in as a parameter, we will select the person
56 and migrate the person ethnic origin data to new EIT.
57 *****************************************************************************/
58 PROCEDURE upgrade_ethnic_origin (p_person_id in number)
59 IS
60
61 /* Cursor to get the person details */
62
63 CURSOR csr_get_person_details(p_person_id NUMBER) IS
64 SELECT paf.person_id
65 ,paf.per_information1 ethnic_origin
66 ,pei.pei_information5 addl_ethnic_origin
67 ,paf.employee_number
68 ,paf.business_group_id
69 ,paf.last_name
70 ,paf.first_name
71 FROM per_all_people_f paf
72 ,per_people_extra_info pei
73 WHERE paf.person_id = pei.person_id(+)
74 AND pei.information_type(+) = 'PER_US_ADDL_ETHNIC_CAT'
75 AND trim(paf.per_information1) IS NOT NULL
76 AND paf.person_id = p_person_id
77 AND paf.effective_start_Date = (SELECT MAX(paf2.effective_start_date)
78 FROM per_all_people_f paf2
79 WHERE paf2.person_id = paf.person_id
80 AND trim(paf.per_information1) IS NOT NULL)
81 ORDER BY paf.business_group_id,paf.person_id;
82
83 /* cursor to check if the data already exists in the EIT*/
84 CURSOR c_eit_exists(p_person_id NUMBER) IS
85 SELECT 'Y'
86 FROM per_people_extra_info pei
87 WHERE pei.person_id = p_person_id
88 AND pei.information_type = 'US_ETHNIC_ORIGIN';
89
90 l_object_version_number NUMBER;
91 l_person_extra_info_id NUMBER;
92 l_hispanic VARCHAR2(1);
93 l_ai_an VARCHAR2(1);
94 l_asian VARCHAR2(1);
95 l_black VARCHAR2(1);
96 l_nhop VARCHAR2(1);
97 l_white VARCHAR2(1);
98 l_tmr VARCHAR2(1);
99 lv_dir VARCHAR2(1000);--#12800711 increased to 1000
100 lv_filename VARCHAR2(1000);--#12800711 increased to 1000
101 lv_file UTL_FILE.FILE_TYPE;
102 lv_line VARCHAR2(4000); --#12800711 increased to 4000
103 lv_warning VARCHAR2(1000); --#12800711 increased to 1000
104 l_eit_exists_flag VARCHAR2(2);
105 l_temp_utl v$parameter.value%type; -- #12800711 increased to 1000
106 -- #13031928 increased to 4000
107 -- #13106407 altered to v$parameter.value%type
108 l_payroll_action_id NUMBER;
109 l_request_id NUMBER;
110 lv_procedure_name VARCHAR2(50);
111
112 BEGIN
113 lv_procedure_name := 'upgrade_ethnic_origin';
114
115 hr_utility.set_location('Entering ' || gv_package_name ||lv_procedure_name,5);
116 /* CSV file generated would be created in the first directory mentioned in
117 v$parameter*/
118 gv_emp_count := gv_emp_count +1;
119 SELECT TRANSLATE(LTRIM(value),',',' ') -- #13031928 Added substr
120 -- #13106407 Removed substr
121 INTO l_temp_utl
122 FROM v$parameter
123 WHERE name = 'utl_file_dir';
124
125 hr_utility.set_location(gv_package_name ||lv_procedure_name,10);
126
127 IF (INSTR(l_temp_utl,' ') > 0 AND l_temp_utl IS NOT NULL) THEN
128 SELECT SUBSTRB(l_temp_utl, 1, INSTR(l_temp_utl,' ') - 1)
129 INTO lv_dir
130 FROM DUAL;
131 ELSIF (l_temp_utl IS NOT NULL) THEN
132 lv_dir := l_temp_utl;
133 END IF;
134
135 /* Derive the Request id so that this can be concatenated to CSV file name
136 so that everytime a unique file name is generated*/
137 BEGIN
138 SELECT max(payroll_action_id)
139 INTO l_payroll_action_id
140 FROM pay_temp_object_actions
141 WHERE object_id = p_person_id
142 AND object_type = 'PER';
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145 NULL;
146 WHEN OTHERS THEN
147 NULL;
148 END;
149 hr_utility.set_location(gv_package_name ||lv_procedure_name,15);
150
151 BEGIN
152 SELECT request_id
153 INTO l_request_id
154 FROM pay_payroll_actions
155 WHERE payroll_action_id = l_payroll_action_id;
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 NULL;
159 WHEN OTHERS THEN
160 NULL;
161 END;
162 hr_utility.set_location(gv_package_name ||lv_procedure_name,20);
163
164 /* CSV file name create is concatenation of static text ethnic_org_mig_ and
165 request id/payroll action id*/
166 lv_filename := 'ethnic_origin_mig_'||nvl(l_request_id,l_payroll_action_id)||'.csv';
167 lv_file := UTL_FILE.FOPEN(lv_dir, lv_filename, 'a');
168
169 hr_utility.set_location(gv_package_name ||lv_procedure_name,25);
170
171 /* print the header in log file for only first record*/
172 IF gv_emp_count = 1 then
173 lv_line := 'Business Group,Employee Number,Employee Last Name,Employee First Name,Ethnic Origin, Additional Ethnic Origin,Migrated,Error/Warning,Message';
174 UTL_FILE.PUT_LINE(lv_file,lv_line);
175 END IF;
176
177 hr_utility.set_location(gv_package_name ||lv_procedure_name,30);
178
179 FOR lc_person_details IN csr_get_person_details(p_person_id)
180 LOOP
181 hr_utility.set_location(gv_package_name ||lv_procedure_name,35);
182 l_hispanic := 'N';
183 l_ai_an := 'N';
184 l_asian := 'N';
185 l_black := 'N';
186 l_nhop := 'N';
187 l_white := 'N';
188 l_tmr := 'N'; -- Two or More Races flag
189 lv_warning := NULL;
190 l_eit_exists_flag := 'N';
191
192 /* check if the data at the person level EIT "US_ETHNIC_ORIGIN" is
193 already migrated or created manaully. If EIT data exists then we
194 don't need to migrate the data. Report the employee as error in CSV.*/
195
196 OPEN c_eit_exists(lc_person_details.person_id);
197 FETCH c_eit_exists INTO l_eit_exists_flag;
198 CLOSE c_eit_exists;
199
200 hr_utility.set_location(gv_package_name ||lv_procedure_name,40);
201
202 IF l_eit_exists_flag = 'N' THEN
203 hr_utility.set_location(gv_package_name ||lv_procedure_name,45);
204 IF lc_person_details.ethnic_origin = '1' THEN
205 l_white := 'Y';
206 END IF;
207
208 IF lc_person_details.ethnic_origin = '2' THEN
209 l_black := 'Y';
210 END IF;
211
212 IF lc_person_details.ethnic_origin = '3' THEN
213 l_hispanic := 'Y';
214 END IF;
215
216 IF lc_person_details.ethnic_origin = '4' THEN
217 l_asian := 'Y';
218 END IF;
219
220 IF lc_person_details.ethnic_origin = '5' THEN
221 l_nhop := 'Y';
222 END IF;
223
224 IF lc_person_details.ethnic_origin = '6' THEN
225 l_ai_an := 'Y';
226 END IF;
227
228 IF lc_person_details.ethnic_origin = '13' THEN
229 hr_utility.set_location(gv_package_name ||lv_procedure_name,50);
230 l_tmr := 'Y';
231 IF lc_person_details.addl_ethnic_origin = '1' THEN
232 l_white := 'Y';
233 END IF;
234 IF lc_person_details.addl_ethnic_origin = '2' THEN
235 l_black := 'Y';
236 END IF;
237 IF lc_person_details.addl_ethnic_origin = '3' THEN
238 l_hispanic := 'Y';
239 END IF;
240 IF lc_person_details.addl_ethnic_origin = '4' THEN
241 l_asian := 'Y';
242 END IF;
243 IF lc_person_details.addl_ethnic_origin = '5' THEN
244 l_nhop := 'Y';
245 END IF;
246 IF lc_person_details.addl_ethnic_origin = '6' THEN
247 l_ai_an := 'Y';
248 END IF;
249 IF lc_person_details.addl_ethnic_origin IS NULL THEN
250 hr_utility.set_location(gv_package_name ||lv_procedure_name,55);
251 lv_warning := 'Ethnic Origin is Two Or More Race but Additional Ethnic Origin is Null';
252 END IF;
253 END IF;
254 hr_utility.set_location(gv_package_name ||lv_procedure_name,60);
255
256 lv_line := hr_general.decode_organization(lc_person_details.business_group_id)||','||lc_person_details.employee_number;
257 lv_line := lv_line||','||lc_person_details.last_name||','||lc_person_details.first_name||',';
258 lv_line := lv_line||hr_general.decode_lookup('US_ETHNIC_GROUP',lc_person_details.ethnic_origin);
259
260 hr_utility.set_location(gv_package_name ||lv_procedure_name,65);
261 IF lc_person_details.ethnic_origin = '13' THEN
262 hr_utility.set_location(gv_package_name ||lv_procedure_name,70);
263 lv_line := lv_line||','||hr_general.decode_lookup('US_ETHNIC_GROUP',lc_person_details.addl_ethnic_origin)||',';
264 ELSE
265 hr_utility.set_location(gv_package_name ||lv_procedure_name,75);
266 lv_line := lv_line||','||',';
267 END IF;
268 l_object_version_number := NULL;
269 l_person_extra_info_id := NULL;
270
271 hr_utility.set_location(gv_package_name ||lv_procedure_name,80);
272 BEGIN
273 hr_person_extra_info_api.create_person_extra_info
274 (p_person_id => lc_person_details.person_id,
275 p_information_type => 'US_ETHNIC_ORIGIN',
276 p_pei_information_category => 'US_ETHNIC_ORIGIN',
277 p_pei_information1 => l_hispanic,
278 p_pei_information2 => l_ai_an,
279 p_pei_information3 => l_asian,
280 p_pei_information4 => l_black,
281 p_pei_information5 => l_nhop,
282 p_pei_information6 => l_white,
283 p_pei_information7 => l_tmr,
284 p_object_version_number => l_object_version_number,
285 p_validate => false,
286 p_person_extra_info_id => l_person_extra_info_id);
287 hr_utility.set_location(gv_package_name ||lv_procedure_name,85);
288 lv_line := lv_line||'Yes,';
289 IF lv_warning IS NULL THEN
290 hr_utility.set_location(gv_package_name ||lv_procedure_name,90);
291 lv_line := lv_line||',';
292 ELSE
293 hr_utility.set_location(gv_package_name ||lv_procedure_name,95);
294 lv_line := lv_line||'Warning,'||lv_warning;
295 END IF;
296 EXCEPTION
297 WHEN OTHERS THEN
298 hr_utility.set_location(gv_package_name ||lv_procedure_name,100);
299 lv_line := lv_line||',No,Error,'||SQLCODE||'-'||SQLERRM;
300 UTL_FILE.PUT_LINE(lv_file,lv_line);
301 END;
302 ELSE
303 hr_utility.set_location(gv_package_name ||lv_procedure_name,105);
304 lv_line := hr_general.decode_organization(lc_person_details.business_group_id)||','||lc_person_details.employee_number||',';
305 lv_line := lv_line||lc_person_details.last_name||','||lc_person_details.first_name||',';
306 lv_line := lv_line||hr_general.decode_lookup('US_ETHNIC_GROUP',lc_person_details.ethnic_origin);
307 lv_line := lv_line||',,No,Error,Already Data exists in the US Ethnic Origin EIT';
308 END IF;
309 UTL_FILE.PUT_LINE(lv_file,lv_line);
310 hr_utility.set_location(gv_package_name ||lv_procedure_name,110);
311 END LOOP;
312
313 UTL_FILE.FCLOSE(lv_file);
314
315 hr_utility.set_location('Leaving ' ||gv_package_name ||lv_procedure_name,115);
316 return;
317
318 EXCEPTION
319
320 WHEN OTHERS THEN
321 hr_utility.set_location('Leaving ' ||gv_package_name ||lv_procedure_name,120);
322 hr_utility.trace('error:'||SQLCODE||'-'||SQLERRM);
323 hr_utility.raise_error;
324 END upgrade_ethnic_origin;
325
326 /*****************************************************************************
327 Name : qual_pers_upg
328
329 Description : This is the qualifying procedure which determines whether
330 the person passed in as a parameter needs to be migrated.
331 The conditions that are checked here are
332 1. Should have ethnic origin information at person level.
333 2. Shouldn't have data for new EIT.
334 *****************************************************************************/
335 PROCEDURE qual_pers_upg(p_object_id VARCHAR2,
336 p_qualified OUT NOCOPY VARCHAR2)
337 IS
338
339 CURSOR c_get_ethnic_origin(p_object_id NUMBER) IS
340 SELECT paf.per_information1
341 FROM per_all_people_f paf
342 WHERE paf.person_id = p_object_id
343 AND paf.per_information1 IS NOT NULL;
344
345 l_date_of_mig DATE;
346 l_upg_flag VARCHAR2(10);
347 l_qualifier VARCHAR2(10);
351
348 lv_procedure_name VARCHAR2(50);
349
350 BEGIN
352 lv_procedure_name := 'qual_pers_upg';
353
354 hr_utility.set_location('Entering ' || gv_package_name ||lv_procedure_name,5);
355
356
357 OPEN c_get_ethnic_origin(p_object_id);
358 FETCH c_get_ethnic_origin INTO l_upg_flag;
359 IF c_get_ethnic_origin%NOTFOUND then
360 l_qualifier := 'N';
361 ELSE
362 l_qualifier := 'Y';
363 END IF;
364 CLOSE c_get_ethnic_origin;
365
366 p_qualified := l_qualifier;
367
368 IF l_qualifier = 'Y' THEN
369 hr_utility.trace('Person_id ' || p_object_id || ' qualified');
370 hr_utility.set_location('Entering ' || gv_package_name ||lv_procedure_name,10);
371 ELSE
372 hr_utility.trace('Person_id ' || p_object_id || ' not qualified');
373 hr_utility.set_location('Entering ' || gv_package_name ||lv_procedure_name,15);
374 END IF;
375
376 hr_utility.set_location('Leaving ' || gv_package_name ||lv_procedure_name,20);
377
378 END qual_pers_upg;
379
380 PROCEDURE irc_upgrade_ethnic_origin (p_person_id IN NUMBER,
381 p_eit_override_flag IN VARCHAR2)
382 IS
383
384 /* Cursor to get the person details */
385
386 CURSOR csr_get_person_details(p_person_id NUMBER) IS
387 SELECT paf.person_id
388 ,paf.per_information1 ethnic_origin
389 ,pei.pei_information5 addl_ethnic_origin
390 ,paf.employee_number
391 ,paf.business_group_id
392 ,paf.last_name
393 ,paf.first_name
394 FROM per_all_people_f paf
395 ,per_people_extra_info pei
396 WHERE paf.person_id = pei.person_id(+)
397 AND pei.information_type(+) = 'PER_US_ADDL_ETHNIC_CAT'
398 AND trim(paf.per_information1) IS NOT NULL
399 AND paf.person_id = p_person_id
400 AND paf.effective_start_Date = (SELECT MAX(paf2.effective_start_date)
401 FROM per_all_people_f paf2
402 WHERE paf2.person_id = paf.person_id
403 AND trim(paf.per_information1) IS NOT NULL);
404
405 /* cursor to check if the data already exists in the EIT*/
406 CURSOR c_del_extra_info(p_person_id NUMBER) IS
407 SELECT pei.person_extra_info_id, pei.object_version_number
408 FROM per_people_extra_info pei
409 WHERE pei.person_id = p_person_id
410 AND pei.information_type = 'US_ETHNIC_ORIGIN';
411
412 l_object_version_number NUMBER;
413 l_person_extra_info_id NUMBER;
414 l_hispanic VARCHAR2(1);
415 l_ai_an VARCHAR2(1);
416 l_asian VARCHAR2(1);
417 l_black VARCHAR2(1);
418 l_nhop VARCHAR2(1);
419 l_white VARCHAR2(1);
420 l_tmr VARCHAR2(1);
421 l_del_pei_id NUMBER :=0;
422 l_pei_ovn NUMBER;
423 l_payroll_action_id NUMBER;
424 l_request_id NUMBER;
425 lv_procedure_name VARCHAR2(50);
426 l_eit_exists_flag VARCHAR2(2);
427
428 BEGIN
429 lv_procedure_name := 'irc_upgrade_ethnic_origin';
430
431 hr_utility.set_location('Entering ' || gv_package_name ||lv_procedure_name,5);
432
433 FOR lc_person_details IN csr_get_person_details(p_person_id)
434 LOOP
435 hr_utility.set_location(gv_package_name ||lv_procedure_name,10);
436 l_hispanic := 'N';
437 l_ai_an := 'N';
438 l_asian := 'N';
439 l_black := 'N';
440 l_nhop := 'N';
441 l_white := 'N';
442 l_tmr := 'N'; -- Two or More Races flag
443 l_eit_exists_flag := 'N';
444
445 /* check if the data at the person level EIT "US_ETHNIC_ORIGIN" is
446 already migrated or created manaully. If EIT data exists and
447 parameter p_eit_override_flag is 'Y' then we delete the existing
448 EIT data.*/
449 hr_utility.set_location(gv_package_name ||lv_procedure_name,15);
450 OPEN c_del_extra_info(lc_person_details.person_id);
451 FETCH c_del_extra_info INTO l_del_pei_id,l_pei_ovn;
452 IF c_del_extra_info%NOTFOUND THEN
453 hr_utility.set_location(gv_package_name ||lv_procedure_name,20);
454 l_del_pei_id := 0;
455 l_eit_exists_flag := 'N';
456 ELSE
457 hr_utility.set_location(gv_package_name ||lv_procedure_name,25);
458 l_eit_exists_flag := 'Y';
459 END IF;
460 CLOSE c_del_extra_info;
461
462 hr_utility.set_location(gv_package_name ||lv_procedure_name,30);
463
464 IF ((l_eit_exists_flag = 'Y') AND (upper(substr(p_eit_override_flag,1,1)) = 'Y')) THEN
465 hr_utility.set_location(gv_package_name ||lv_procedure_name,35);
466 BEGIN
467 hr_person_extra_info_api.delete_person_extra_info
468 (p_validate => FALSE
469 ,p_person_extra_info_id => l_del_pei_id
470 ,p_object_version_number => l_pei_ovn
471 );
472 l_eit_exists_flag := 'N';
473 EXCEPTION
474 WHEN OTHERS THEN
475 l_eit_exists_flag := 'Y';
476 hr_utility.set_location('Delete Exception ' ||gv_package_name ||lv_procedure_name,40);
480 END IF;
477 hr_utility.trace('error:'||SQLCODE||'-'||SQLERRM);
478 hr_utility.raise_error;
479 END;
481
482 hr_utility.set_location(gv_package_name ||lv_procedure_name,45);
483
484 /* Create the new EIT data.*/
485 IF l_eit_exists_flag = 'N' THEN
486 hr_utility.set_location(gv_package_name ||lv_procedure_name,50);
487 IF lc_person_details.ethnic_origin = '1' THEN
488 l_white := 'Y';
489 END IF;
490
491 IF lc_person_details.ethnic_origin = '2' THEN
492 l_black := 'Y';
493 END IF;
494
495 IF lc_person_details.ethnic_origin = '3' THEN
496 l_hispanic := 'Y';
497 END IF;
498
499 IF lc_person_details.ethnic_origin = '4' THEN
500 l_asian := 'Y';
501 END IF;
502
503 IF lc_person_details.ethnic_origin = '5' THEN
504 l_nhop := 'Y';
505 END IF;
506
507 IF lc_person_details.ethnic_origin = '6' THEN
508 l_ai_an := 'Y';
509 END IF;
510
511 IF lc_person_details.ethnic_origin = '13' THEN
512 hr_utility.set_location(gv_package_name ||lv_procedure_name,55);
513 l_tmr := 'Y';
514 IF lc_person_details.addl_ethnic_origin = '1' THEN
515 l_white := 'Y';
516 END IF;
517 IF lc_person_details.addl_ethnic_origin = '2' THEN
518 l_black := 'Y';
519 END IF;
520 IF lc_person_details.addl_ethnic_origin = '3' THEN
521 l_hispanic := 'Y';
522 END IF;
523 IF lc_person_details.addl_ethnic_origin = '4' THEN
524 l_asian := 'Y';
525 END IF;
526 IF lc_person_details.addl_ethnic_origin = '5' THEN
527 l_nhop := 'Y';
528 END IF;
529 IF lc_person_details.addl_ethnic_origin = '6' THEN
530 l_ai_an := 'Y';
531 END IF;
532 END IF;
533 hr_utility.set_location(gv_package_name ||lv_procedure_name,60);
534
535 l_object_version_number := NULL;
536 l_person_extra_info_id := NULL;
537
538 hr_utility.set_location(gv_package_name ||lv_procedure_name,65);
539 BEGIN
540 hr_person_extra_info_api.create_person_extra_info
541 (p_person_id => lc_person_details.person_id,
542 p_information_type => 'US_ETHNIC_ORIGIN',
543 p_pei_information_category => 'US_ETHNIC_ORIGIN',
544 p_pei_information1 => l_hispanic,
545 p_pei_information2 => l_ai_an,
546 p_pei_information3 => l_asian,
547 p_pei_information4 => l_black,
548 p_pei_information5 => l_nhop,
549 p_pei_information6 => l_white,
550 p_pei_information7 => l_tmr,
551 p_object_version_number => l_object_version_number,
552 p_validate => false,
553 p_person_extra_info_id => l_person_extra_info_id);
554 hr_utility.set_location(gv_package_name ||lv_procedure_name,70);
555 EXCEPTION
556 WHEN OTHERS THEN
557 hr_utility.set_location(gv_package_name ||lv_procedure_name,75);
558 hr_utility.trace('error in creation:'||SQLCODE||'-'||SQLERRM);
559 hr_utility.raise_error;
560 END;
561 END IF;
562 END LOOP;
563
564 hr_utility.set_location('Leaving ' ||gv_package_name ||lv_procedure_name,80);
565 return;
566
567 EXCEPTION
568 WHEN OTHERS THEN
569 hr_utility.set_location('Leaving ' ||gv_package_name ||lv_procedure_name,85);
570 hr_utility.trace('error:'||SQLCODE||'-'||SQLERRM);
571 hr_utility.raise_error;
572 END irc_upgrade_ethnic_origin;
573
574 END per_us_eth_orig_mig_pkg;