[Home] [Help]
PACKAGE BODY: APPS.GMA_MIGRATION_UTILS
Source
1 PACKAGE BODY gma_migration_utils AS
2 /* $Header: GMAUMIGB.pls 120.6.12000000.2 2007/02/26 17:39:32 acataldo ship $ */
3
4
5
6 /*====================================================================
7 -- FUNCTION:
8 -- GET_REASON_ID
9 --
10 -- DESCRIPTION:
11 -- This PL/SQL procedure returns the reason_id for a given reason_code
12 --
13 -- PARAMETERS:
14 -- p_reason_code IN Parameter for the reason_code
15 -- --
16 -- SYNOPSIS:
17 --
18 -- X_reason_id := GET_REASON_ID ( p_reason_code => l_reason_code);
19 --
20 -- HISTORY
21 -- 5/23/2005 - NC
22 --====================================================================*/
23 FUNCTION get_reason_id (p_reason_code IN VARCHAR2) RETURN NUMBER IS
24 CURSOR reason_id_cur(p_reason_code IN VARCHAR2) IS
25 SELECT reason_id
26 FROM sy_reas_cds_b
27 WHERE reason_code = p_reason_code;
28 X_reason_Id NUMBER(15);
29 BEGIN
30 OPEN reason_id_cur(p_reason_code);
31 FETCH reason_id_cur INTO x_reason_id;
32 CLOSE reason_id_cur;
33 RETURN (X_reason_id);
34 END get_reason_id;
35 /*====================================================================
36 -- FUNCTION:
37 -- get_organization_id
38 --
39 -- DESCRIPTION:
40 -- This PL/SQL procedure is used to fetch the Organization id to
41 -- a specific orgn code.
42 --
43 --
44 -- PARAMETERS:
45 -- P_orgn_code - Organization code will be used to fetch the orgn id
46 --
47 -- SYNOPSIS:
48 -- fetch_organization(p_orgn_code IN VARCHARE) RETURN NUMBER;
49 --
50 -- HISTORY
51 --====================================================================*/
52 FUNCTION get_organization_id (P_orgn_code IN VARCHAR2) RETURN NUMBER IS
53 CURSOR Cur_get_orgn_id IS
54 SELECT organization_id
55 FROM sy_orgn_mst
56 WHERE orgn_code = P_orgn_code;
57 X_organization_id NUMBER;
58 BEGIN
59 OPEN Cur_get_orgn_id;
60 FETCH Cur_get_orgn_id INTO X_organization_id;
61 CLOSE Cur_get_orgn_id;
62 RETURN(X_organization_id);
63 END get_organization_id;
64 /*====================================================================
65 -- FUNCTION:
66 -- get_uom_code
67 --
68 -- DESCRIPTION:
69 -- This PL/SQL procedure is used to fetch the 3 character uom code to
70 -- OPM's 4 character UOM code.
71 --
72 --
73 -- PARAMETERS:
74 -- P_um_code - Organization code will be used to fetch the orgn id
75 --
76 -- SYNOPSIS:
77 -- fetch_organization(p_orgn_code IN VARCHARE) RETURN NUMBER;
78 --
79 -- HISTORY
80 --====================================================================*/
81 FUNCTION get_uom_code (P_um_code IN VARCHAR2) RETURN VARCHAR2 IS
82 CURSOR Cur_get_uom_code IS
83 SELECT uom_code
84 FROM sy_uoms_mst
85 WHERE um_code = P_um_code;
86 X_uom_code VARCHAR2(3);
87 BEGIN
88 OPEN Cur_get_uom_code;
89 FETCH Cur_get_uom_code INTO X_uom_code;
90 CLOSE Cur_get_uom_code;
91 RETURN(X_uom_code);
92 END get_uom_code;
93 /*====================================================================
94 -- PROCEDURE:
95 -- GMA_EDIT_TEXT_MIGRATION
96 --
97 -- DESCRIPTION:
98 -- This PL/SQL procedure is used to migration OPM Reason Codes
99 --
100 -- PARAMETERS:
101 -- p_migration_run_id This is used for message logging.
102 -- p_commit Commit flag.
103 -- x_failure_count count of the failed lines.An out parameter.
104 --
105 -- SYNOPSIS:
106 --
107 -- GMA_EDIT_TEXT_MIGRATION ( p_migration_run_id IN NUMBER
108 -- , p_commit IN VARCHAR2
109 -- , x_failure_count OUT NUMBER)
110 --
111 -- HISTORY
112 -- 5/23/2005 - nchekuri
113 -- 2/26/2007 - acataldo - Bug 5736539 - setup text table correctly
114 --====================================================================*/
115 PROCEDURE GMA_EDIT_TEXT_MIGRATION ( err_buf OUT NOCOPY varchar2
116 , ret_code OUT NOCOPY number
117 , migration_var IN varchar2
118 ) IS
119 -- Variables defenition
120 l_failure_count NUMBER := 0;
121 l_success_count NUMBER := 0;
122 l_table_name VARCHAR2(30) DEFAULT NULL;
123 l_opm_table_name VARCHAR2(30) DEFAULT NULL;
124 p_error varchar2(3000);
125 p_warning varchar2(3000);
126 -- Variables used for the AD Event Registry
127 l_event_owner varchar2(100);
128 l_event_name varchar2(100);
129 l_module_name varchar2(100);
130 p_commit varchar2(10) := FND_API.G_TRUE;
131 -- Variables to take care of the FND Logging
132 l_dummy_cnt BINARY_INTEGER;
133 i BINARY_INTEGER;
134 l_count BINARY_INTEGER;
135 l_msg_data varchar2(1000);
136 mig_entity varchar2(20);
137
138
139 /* We'll define one cursor for each entity for which Edit Text needs to be migrated. */
140
141 --
142 --- As this prgram need to be run as a concurrent program
143 --- Removing all the GMA Logging
144 --
145
146
147 /* ic_item_mst */
148 --
149 --- Cursor for the item text_code migration
150 --
151
152
153 CURSOR item_mst_cur IS
154 SELECT ic.text_code,
155 ic.item_no,
156 msi.inventory_item_id,
157 msi.organization_id,
158 msi.segment1
159 FROM mtl_system_items msi,
160 mtl_parameters p,
161 ic_item_mst ic
162 WHERE msi.segment1 = ic.item_no
163 AND msi.organization_id = p.master_organization_id
164 AND p.organization_id = p.master_organization_id
165 AND ic.text_code IS NOT NULL;
166 --
167 --- Cursor for the lot text_code migraiton
168 --
169 CURSOR lot_mst_cur IS
170 SELECT p.lot_number,
171 itm.INVENTORY_ITEM_ID,
172 p.organization_id,
173 ic.text_code
174 FROM ic_item_mst_b_mig itm,
175 mtl_lot_numbers msi,
176 IC_LOTS_MST_MIG p,
177 IC_LOTS_MST ic
178 WHERE p.lot_number = msi.lot_number
179 AND p.item_id = itm.item_id
180 AND p.lot_id = ic.lot_id
181 AND p.organization_id = itm.organization_id
182 AND ic.text_code IS NOT NULL;
183 --
184 --- Cursor for the grades
185 --
186 CURSOR grd_mst_cur IS
187 SELECT mgb.grade_code,
188 opm.text_code
189 FROM MTL_GRADES_B MGB,
190 GMD_GRADES_B opm
191 WHERE opm.qc_grade = mgb.grade_code
192 AND opm.text_code IS NOT NULL;
193
194 --
195 --- Cursor for the action codes
196 --
197 CURSOR act_mst_cur IS
198 SELECT mac.action_code,
199 opm.text_code
200 FROM MTL_ACTIONS_B MAC,
201 GMD_ACTIONS_B opm
202 WHERE opm.action_code = mac.action_code
203 AND opm.text_code IS NOT NULL;
204
205 --
206 --- Cursor for the Rason Codes migration
207 --
208
209 CURSOR reas_mst_cur IS
210 SELECT src.text_code,
211 src.reason_id,
212 src.reason_code
213 FROM SY_REAS_CDS_B src
214 WHERE src.text_code IS NOT NULL
215 and src.reason_id <> 0;
216 --
217 --- Cursor for the Organization Codes migration
218 --
219 CURSOR org_mst_cur IS
220 SELECT som.text_code,
221 som.organization_id,
222 som.orgn_code
223 FROM SY_ORGN_MST som
224 WHERE som.text_code IS NOT NULL
225 and som.migrate_as_ind=3;
226
227
228 BEGIN
229
230 --
231 --- Initialize the FND logging
232 --
233 FND_MSG_PUB.initialize;
234
235 --
236 --- Begin by logging a message that reason_code migration has started */
237 --
238
239 /* Put the FND logging here for the start of the migration*/
240
241 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_MIG_STARTED');
242 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',migration_var,FALSE);
243 FND_MSG_PUB.ADD;
244 --
245 --- Prepare the case statement
246 --
247
248 IF migration_var = 'ITEM' or migration_var='ALL' THEN
249
250 l_event_owner := 'GMA';
251 l_event_name := 'GMA_ITEM';
252 l_module_name := 'GMA_ITEM';
253 l_failure_count := 0;
254 mig_entity:='ITEM';
255 IF AD_EVENT_REGISTRY_PKG.Is_Event_Done (p_Owner=>l_event_owner,
256 p_Event_Name=>l_event_name) = FALSE THEN
257
258 /* Edit Text Migration for Item Master */
259 l_table_name := 'MTL_SYSTEM_ITEMS';
260 l_opm_table_name := 'IC_ITEM_MST';
261
262 FOR l_item_mst_rec IN item_mst_cur LOOP
263 BEGIN
264 /* Call the Attachment_main procedure */
265
266 GMA_EDITEXT_ATTACH_MIG.Attachment_Main (
267 p_text_table_tl => 'ic_text_tbl_tl', /* OPMs Text Table */
268 p_text_code => l_item_mst_rec.text_code, /* text code to be migrated */
269 p_sy_para_cds_table_name => l_opm_table_name, /* Table name in OPM */
270 p_attach_form_short_name => 'INVIDITM', /* Form name in Apps */
271 p_attach_table_name => l_table_name, /* Table name in Apps */
272 p_attach_pk1_value => l_item_mst_rec.organization_id,
273 p_attach_pk2_value => l_item_mst_rec.inventory_item_id,
274 p_attach_pk3_value => NULL,
275 p_attach_pk4_value => NULL,
276 p_attach_pk5_value => NULL);
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 /* Failure count goes up by 1 */
281 l_failure_count := l_failure_count+1;
282 -- Update the CCM logs
283
284 FND_MESSAGE.SET_NAME('GMA', 'GMA_TEXT_CODE_FAILURE');
285 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
286 FND_MSG_PUB.ADD;
287
288 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
289 NULL;
290 END IF;
291 END;
292 END LOOP; /* For item_mst_rec */
293
294 if l_failure_count = 0 then
295 AD_EVENT_REGISTRY_PKG.Set_Event_As_Done (
296 p_Owner=>l_event_owner,
297 p_Event_Name=>l_event_name,
298 p_module_Name=>l_module_name);
299
300 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_MIG_COMPLETED');
301 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
302 FND_MSG_PUB.ADD;
303
304 else
305 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_ERROR');
306 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
307 FND_MESSAGE.SET_TOKEN('ERROR_COUNT',l_failure_count,FALSE);
308 FND_MSG_PUB.ADD;
309
310 end if;
311
312 ELSE
313
314 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_ALREADY_MIGRATED');
315 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
316 FND_MSG_PUB.ADD;
317
318 END IF;
319
320 END IF;
321
322 --
323 --- For the Lot Text Code migraiton
324 --
325
326 IF migration_var = 'LOT' or migration_var='ALL' then
327
328 l_event_owner := 'GMA';
329 l_event_name := 'GMA_LOT_TXT_CDE_MIG';
330 l_module_name := 'GMA_LOT_TEXT_CODE_MIGRATION';
331 l_failure_count := 0;
332 mig_entity:='LOT';
333 IF (AD_EVENT_REGISTRY_PKG.Is_Event_Done (
334 p_Owner=>l_event_owner,
335 p_Event_Name=>l_event_name) = FALSE) THEN
336
337 /* Edit Text Migration for Lot Master */
338 l_table_name := 'MTL_LOT_NUMBERS';
339 l_opm_table_name := 'IC_LOTS_MST';
340 FOR l_lot_mst_rec IN lot_mst_cur LOOP
341 BEGIN
342 /* Call the Attachment_main procedure */
343 GMA_EDITEXT_ATTACH_MIG.Attachment_Main (
344 p_text_table_tl => 'ic_text_tbl_tl', /* OPMs Text Table */
345 p_text_code => l_lot_mst_rec.text_code, /* text code to be migrated */
346 p_sy_para_cds_table_name => l_opm_table_name, /* Table name in OPM */
347 p_attach_form_short_name => 'INVIDILT', /* Form name in Apps */
348 p_attach_table_name => l_table_name, /* Table name in Apps */
349 p_attach_pk1_value => l_lot_mst_rec.organization_id,
350 p_attach_pk2_value => l_lot_mst_rec.inventory_item_id,
351 p_attach_pk3_value => l_lot_mst_rec.lot_number,
352 p_attach_pk4_value => NULL,
353 p_attach_pk5_value => NULL
354 );
355 EXCEPTION
356 WHEN OTHERS THEN
357 /* Failure count goes up by 1 */
358 l_failure_count := l_failure_count+1;
359 -- Update the CCM Logs
360 FND_MESSAGE.SET_NAME('GMA', 'GMA_TEXT_CODE_FAILURE');
361 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
362 FND_MSG_PUB.ADD;
363
364 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
365 NULL;
366 END IF;
367 END;
368 END LOOP; /* For lot_mst_rec */
369 if l_failure_count = 0 then
370 AD_EVENT_REGISTRY_PKG.Set_Event_As_Done (
371 p_Owner=>l_event_owner,
372 p_Event_Name=>l_event_name,
373 p_module_Name=>l_module_name);
374
375 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_MIG_COMPLETED');
376 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
377 FND_MSG_PUB.ADD;
378
379 else
380 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_ERROR');
381 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
382 FND_MESSAGE.SET_TOKEN('ERROR_COUNT',l_failure_count,FALSE);
383 FND_MSG_PUB.ADD;
384
385 end if;
386 ELSE
387 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_ALREADY_MIGRATED');
388 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
389 FND_MSG_PUB.ADD;
390 END IF;
391
392 END IF;
393 --
394 --- For the GRADES Text Code migraiton
395 --
396
397 IF migration_var = 'GRADES' or migration_var='ALL' then
398
399 l_event_owner := 'GMA';
400 l_event_name := 'GMA_GRD_TXT_CODE_MIG';
401 l_module_name := 'GMA_GRADES_TEXT_CODE_MIGRATION';
402 mig_entity:='GRADES';
403 l_failure_count:=0;
404
405 IF (AD_EVENT_REGISTRY_PKG.Is_Event_Done (
406 p_Owner=>l_event_owner,
407 p_Event_Name=>l_event_name) = FALSE) THEN
408 /* Edit Text Migration for Grades */
409 l_table_name := 'MTL_GRADES_B';
410 l_opm_table_name := 'GMD_GRADES';
411 FOR l_grd_mst_rec IN grd_mst_cur LOOP
412 BEGIN
413 /* Call the Attachment_main procedure */
414 GMA_EDITEXT_ATTACH_MIG.Attachment_Main (
415 p_text_table_tl => 'qc_text_tbl_tl', /* OPMs Text Table */
416 p_text_code => l_grd_mst_rec.text_code, /* text code to be migrated */
417 p_sy_para_cds_table_name => l_opm_table_name, /* Table name in OPM */
418 p_attach_form_short_name => 'INVGRADE', /* Form name in Apps */
419 p_attach_table_name => l_table_name, /* Table name in Apps */
420 p_attach_pk1_value => l_grd_mst_rec.grade_code,
421 p_attach_pk2_value => NULL,
422 p_attach_pk3_value => NULL,
423 p_attach_pk4_value => NULL,
424 p_attach_pk5_value => NULL
425 );
426 EXCEPTION
427 WHEN OTHERS THEN
428 /* Failure count goes up by 1 */
429 l_failure_count := l_failure_count+1;
430 -- Update the CCM logs
431 FND_MESSAGE.SET_NAME('GMA', 'GMA_TEXT_CODE_FAILURE');
432 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
433 FND_MSG_PUB.ADD;
434
435 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
436 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
437 NULL;
438 END IF;
439 END;
440 END LOOP; /* For lot_mst_rec */
441 if l_failure_count = 0 then
442 AD_EVENT_REGISTRY_PKG.Set_Event_As_Done (
443 p_Owner=>l_event_owner,
444 p_Event_Name=>l_event_name,
445 p_module_Name=>l_module_name);
446
447 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_MIG_COMPLETED');
448 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
449 FND_MSG_PUB.ADD;
450
451 else
452 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_ERROR');
453 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
454 FND_MESSAGE.SET_TOKEN('ERROR_COUNT',l_failure_count,FALSE);
455 FND_MSG_PUB.ADD;
456
457 end if;
458 ELSE
459 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_ALREADY_MIGRATED');
460 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
461 FND_MSG_PUB.ADD;
462
463 END IF;
464 END IF;
465
466 --
467 --- For the ACTION codes Text Code migraiton
468 --
469
470 IF migration_var = 'ACTION' or migration_var='ALL' then
471
472 l_event_owner := 'GMA';
473 l_event_name := 'GMA_ACT_TXT_CODE_MIG';
474 l_module_name := 'GMA_ACTION_TEXT_CODE_MIGRATION';
475 mig_entity:='ACTION';
476 l_failure_count:=0;
477
478 IF (AD_EVENT_REGISTRY_PKG.Is_Event_Done (
479 p_Owner=>l_event_owner,
480 p_Event_Name=>l_event_name) = FALSE) THEN
481 /* Edit Text Migration for Grades */
482 l_table_name := 'MTL_ACTIONS_B';
483 l_opm_table_name := 'QC_ACTN_MST';
484 FOR l_act_mst_rec IN act_mst_cur LOOP
485 BEGIN
486 /* Call the Attachment_main procedure */
487 GMA_EDITEXT_ATTACH_MIG.Attachment_Main (
488 p_text_table_tl => 'qc_text_tbl_tl', /* OPMs Text Table */
489 p_text_code => l_act_mst_rec.text_code, /* text code to be migrated */
490 p_sy_para_cds_table_name => l_opm_table_name, /* Table name in OPM */
491 p_attach_form_short_name => 'INVACODE', /* Form name in Apps */
492 p_attach_table_name => l_table_name, /* Table name in Apps */
493 p_attach_pk1_value => l_act_mst_rec.action_code,
494 p_attach_pk2_value => NULL,
495 p_attach_pk3_value => NULL,
496 p_attach_pk4_value => NULL,
497 p_attach_pk5_value => NULL
498 );
499 EXCEPTION
500 WHEN OTHERS THEN
501 /* Failure count goes up by 1 */
502 l_failure_count := l_failure_count+1;
503 -- Update the CCM logs
504 FND_MESSAGE.SET_NAME('GMA', 'GMA_TEXT_CODE_FAILURE');
505 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
506 FND_MSG_PUB.ADD;
507
508 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
509 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
510 NULL;
511 END IF;
512 END;
513 END LOOP; /* For act_mst_rec */
514 if l_failure_count = 0 then
515 AD_EVENT_REGISTRY_PKG.Set_Event_As_Done (
516 p_Owner=>l_event_owner,
517 p_Event_Name=>l_event_name,
518 p_module_Name=>l_module_name);
519
520 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_MIG_COMPLETED');
521 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
522 FND_MSG_PUB.ADD;
523
524 else
525 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_ERROR');
526 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
527 FND_MESSAGE.SET_TOKEN('ERROR_COUNT',l_failure_count,FALSE);
528 FND_MSG_PUB.ADD;
529
530 end if;
531 ELSE
532 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_ALREADY_MIGRATED');
533 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
534 FND_MSG_PUB.ADD;
535
536 END IF;
537 END IF;
538
539 --
540 --- REASONS text code migration
541 --
542 IF migration_var = 'REASON CODE' or migration_var='ALL' then
543
544 l_event_owner := 'GMA';
545 l_event_name := 'GMA_REAS_TXT_CDE_MIG';
546 l_module_name := 'GMA_REAS_TXT_CDE_MIG';
547 mig_entity:='REASON CODES';
548 l_failure_count:=0;
549
550 IF (AD_EVENT_REGISTRY_PKG.Is_Event_Done (
551 p_Owner=>l_event_owner,
552 p_Event_Name=>l_event_name) = FALSE) THEN
553 /* Edit Text Migration for Grades */
554 l_table_name := 'MTL_TRANSACTION_REASONS';
555 l_opm_table_name := 'SY_REAS_CDS';
556 FOR l_reas_mst_rec IN reas_mst_cur LOOP
557 BEGIN
558 /* Call the Attachment_main procedure */
559 /* Bug 5736539 setup text table to the correct sy text table */
560 GMA_EDITEXT_ATTACH_MIG.Attachment_Main (
561 p_text_table_tl => 'sy_text_tbl_tl', /* OPMs Text Table */
562 p_text_code => l_reas_mst_rec.text_code, /* text code to be migrated */
563 p_sy_para_cds_table_name => l_opm_table_name, /* Table name in OPM */
564 p_attach_form_short_name => 'INVTDTRS', /* Form name in Apps */
565 p_attach_table_name => l_table_name, /* Table name in Apps */
566 p_attach_pk1_value => l_reas_mst_rec.REASON_ID,
567 p_attach_pk2_value => NULL,
568 p_attach_pk3_value => NULL,
569 p_attach_pk4_value => NULL,
570 p_attach_pk5_value => NULL
571 );
572 EXCEPTION
573 WHEN OTHERS THEN
574 /* Failure count goes up by 1 */
575 l_failure_count := l_failure_count+1;
576 -- Update the CCM logs
577
578 FND_MESSAGE.SET_NAME('GMA', 'GMA_TEXT_CODE_FAILURE');
579 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
580 FND_MSG_PUB.ADD;
581
582
583 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
584 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
585 NULL;
586 END IF;
587 END;
588 END LOOP; /* For lot_mst_rec */
589 if l_failure_count = 0 then
590 AD_EVENT_REGISTRY_PKG.Set_Event_As_Done (
591 p_Owner=>l_event_owner,
592 p_Event_Name=>l_event_name,
593 p_module_Name=>l_module_name);
594
595 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_MIG_COMPLETED');
596 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
597 FND_MSG_PUB.ADD;
598
599 else
600 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_ERROR');
601 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
602 FND_MESSAGE.SET_TOKEN('ERROR_COUNT',l_failure_count,FALSE);
603 FND_MSG_PUB.ADD;
604
605 end if;
606
607 ELSE
608 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_ALREADY_MIGRATED');
609 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
610 FND_MSG_PUB.ADD;
611
612 END IF;
613 END IF;
614 --
615 --- Organization Text Code migration
616 --
617
618 IF migration_var = 'ORGANIZATION' or migration_var='ALL' then
619
620 l_event_owner := 'GMA';
621 l_event_name := 'GMA_ORG_TXT_CDE_MIG';
622 l_module_name := 'GMA_ORG_TXT_CDE_MIG';
623 mig_entity:='ORGANIZATION';
624 l_failure_count:=0;
625
626 IF (AD_EVENT_REGISTRY_PKG.Is_Event_Done (
627 p_Owner=>l_event_owner,
628 p_Event_Name=>l_event_name) = FALSE) THEN
629 /* Edit Text Migration for Grades */
630 l_table_name := 'MTL_PARAMETERS';
631 l_opm_table_name := 'SY_ORGN_MST';
632 FOR l_org_mst_rec IN org_mst_cur LOOP
633 BEGIN
634 /* Call the Attachment_main procedure */
635 /* Bug 5736539 setup text table to the correct sy text table */
636 GMA_EDITEXT_ATTACH_MIG.Attachment_Main (
637 p_text_table_tl => 'sy_text_tbl_tl', /* OPMs Text Table */
638 p_text_code => l_org_mst_rec.text_code, /* text code to be migrated */
639 p_sy_para_cds_table_name => l_opm_table_name, /* Table name in OPM */
640 p_attach_form_short_name => 'PERWSDOR', /* Form name in Apps */
641 p_attach_table_name => l_table_name, /* Table name in Apps */
642 p_attach_pk1_value => l_org_mst_rec.ORGANIZATION_ID,
643 p_attach_pk2_value => NULL,
644 p_attach_pk3_value => NULL,
645 p_attach_pk4_value => NULL,
646 p_attach_pk5_value => NULL
647 );
648 EXCEPTION
649 WHEN OTHERS THEN
650 /* Failure count goes up by 1 */
651 l_failure_count := l_failure_count+1;
652 -- Update the CCM logs
653
654 FND_MESSAGE.SET_NAME('GMA', 'GMA_TEXT_CODE_FAILURE');
655 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
656 FND_MSG_PUB.ADD;
657
658
659 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
660 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
661 NULL;
662 END IF;
663 END;
664 END LOOP; /* For lot_mst_rec */
665 if l_failure_count = 0 then
666 AD_EVENT_REGISTRY_PKG.Set_Event_As_Done (
667 p_Owner=>l_event_owner,
668 p_Event_Name=>l_event_name,
669 p_module_Name=>l_module_name);
670
671 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_MIG_COMPLETED');
672 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
673 FND_MSG_PUB.ADD;
674
675 else
676 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_ERROR');
677 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
678 FND_MESSAGE.SET_TOKEN('ERROR_COUNT',l_failure_count,FALSE);
679 FND_MSG_PUB.ADD;
680
681 end if;
682
683 ELSE
684 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_ALREADY_MIGRATED');
685 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',mig_entity,FALSE);
686 FND_MSG_PUB.ADD;
687
688 END IF;
689 END IF;
690 --
691 --- If commit flag is set do the commit
692 --
693 IF p_commit = FND_API.G_TRUE THEN
694 COMMIT;
695 END IF;
696
697 --
698 --- Update for the Concurrent logs for the completion of update
699 --
700 FND_MESSAGE.SET_NAME('GMA','GMA_TEXT_CODE_MIG_FINISHED');
701 FND_MSG_PUB.ADD;
702
703 --
704 --- Log the message to the FND log
705 --
706
707 l_count := FND_MSG_PUB.Count_Msg;
708 FOR i IN 1..l_count LOOP
709 FND_MSG_PUB.Get(
710 p_msg_index => i,
711 p_data => l_msg_data,
712 p_encoded => FND_API.G_FALSE,
713 p_msg_index_out => l_dummy_cnt);
714
715 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
716 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
717 END LOOP;
718
719 --
720 --- When exception then
721 --
722
723 EXCEPTION
724 WHEN OTHERS THEN
725 FND_MESSAGE.SET_NAME('GMA', 'GMA_TEXT_CODE_FAILURE');
726 FND_MESSAGE.SET_TOKEN('MIGRATION_ENTITY',migration_var,FALSE);
727 FND_MSG_PUB.ADD;
728
729
730
731 END GMA_EDIT_TEXT_MIGRATION;
732 END gma_migration_utils;