[Home] [Help]
PACKAGE BODY: APPS.PO_GML_CONV_MIG
Source
1 PACKAGE BODY PO_GML_CONV_MIG AS
2 /* $Header: POXMGGMB.pls 120.0 2005/06/08 13:20:28 pbamb noship $ */
3 /*===========================================================================
4 -- PROCEDURE:
5 -- PO_MIG_GML_DATA
6 --
7 -- DESCRIPTION:
8 -- This PL/SQL procedure is used to all the PO entities for Inv Convergence
9 -- project. Main Procedure that calls the other 4 procedures.
10 --
11 -- PARAMETERS:
12 -- None
13 --
14 -- SYNOPSIS:
15 -- po_mig_gml_data;
16 --
17 -- HISTORY
18 -- P. Bamb 10-May-2005 Created
19 --=========================================================================== */
20 Procedure po_mig_gml_data IS
21
22 BEGIN
23
24 -- Call proc to update the po_line_locations for quantity shipped.
25 Update_po_shipment;
26
27 END po_mig_gml_data;
28
29 /*===========================================================================
30 -- PROCEDURE:
31 -- update_po_shipment
32 --
33 -- DESCRIPTION:
34 -- This PL/SQL procedure is used to Update secondary_quantity_shipped
35 -- and secondary_quantity_shipped in PO_LINE_LOCATIONS_ALL by converting
36 -- respective transaction quantities.
37 -- PARAMETERS:
38 -- None
39 --
40 -- SYNOPSIS:
41 -- update_po_shipment;
42 --
43 -- HISTORY
44 -- P. Bamb 10-May-2005 Created
45 --=========================================================================== */
46 PROCEDURE update_po_shipment IS
47
48 CURSOR CR_SHIPMENTS IS
49 Select pll.secondary_quantity_shipped,
50 pll.unit_meas_lookup_code,
51 pl.item_id,
52 pll.secondary_unit_of_measure,
53 pll.po_header_id,
54 pll.po_line_id,
55 pll.line_location_id,
56 pll.quantity_shipped,
57 pll.quantity_cancelled
58 from po_line_locations_all pll,
59 po_lines_all pl,
60 mtl_parameters mp
61 where pll.secondary_unit_of_measure is not null
62 AND pll.po_header_id = pll.po_header_id
63 AND pll.po_line_id = pl.po_line_id
64 AND pll.ship_to_organization_id = mp.organization_id
65 AND pll.shipment_type in ('STANDARD', 'PLANNED', 'BLANKET')
66 AND (
67 (nvl(pll.secondary_quantity_shipped,0) = 0 and nvl(pll.quantity_shipped,0) <> 0)
68 OR
69 (nvl(pll.secondary_quantity_cancelled,0) = 0 and nvl(pll.quantity_cancelled,0) <> 0)
70 )
71 AND mp.process_enabled_flag = 'Y'
72 FOR UPDATE OF secondary_quantity_shipped;
73
74 l_secondary_quantity_shipped NUMBER;
75 l_secondary_quantity_cancelled NUMBER;
76
77 po_shipment_data_err EXCEPTION;
78
79 cr_rec cr_shipments%ROWTYPE;
80
81 BEGIN
82
83 FOR cr_rec in cr_shipments LOOP
84 BEGIN
85
86 l_secondary_quantity_shipped := NULL;
87 l_secondary_quantity_cancelled := NULL;
88
89 IF cr_rec.quantity_shipped IS NOT NULL and cr_rec.quantity_shipped <> 0 THEN
90
91 l_secondary_quantity_shipped := INV_CONVERT.inv_um_convert(
92 item_id => cr_rec.item_id,
93 precision => 6,
94 from_quantity => cr_rec.quantity_shipped,
95 from_unit => NULL,
96 to_unit => NULL,
97 from_name => cr_rec.unit_meas_lookup_code ,
98 to_name => cr_rec.secondary_unit_of_measure );
99
100 IF l_secondary_quantity_shipped <=0 THEN
101 raise po_shipment_data_err;
102 End If;
103 END IF;
104
105 IF cr_rec.quantity_cancelled IS NOT NULL and cr_rec.quantity_cancelled <> 0 THEN
106
107 l_secondary_quantity_cancelled := INV_CONVERT.inv_um_convert(
108 item_id => cr_rec.item_id,
109 precision => 6,
110 from_quantity => cr_rec.quantity_cancelled,
111 from_unit => NULL,
112 to_unit => NULL,
113 from_name => cr_rec.unit_meas_lookup_code ,
114 to_name => cr_rec.secondary_unit_of_measure );
115
116 IF l_secondary_quantity_cancelled <=0 THEN
117 raise po_shipment_data_err;
118 End If;
119 END IF;
120
121 UPDATE po_line_locations_all
122 SET secondary_quantity_shipped = nvl(l_secondary_quantity_shipped,secondary_quantity_shipped),
123 secondary_quantity_cancelled = nvl(l_secondary_quantity_cancelled,secondary_quantity_cancelled)
124 WHERE CURRENT OF cr_shipments;
125
126 EXCEPTION
127 WHEN PO_SHIPMENT_DATA_ERR THEN
128 insert into gml_po_mig_errors
129 (migration_type,po_header_id,po_line_id,line_location_id,
130 transaction_id, shipment_header_id,shipment_line_id,
131 column_name,table_name,error_message,
132 creation_date,last_update_date)
133 values ('CONVERGENCE',cr_rec.po_header_id,cr_rec.po_line_id,cr_rec.line_location_id,
134 NULL, NULL, NULL,
135 'SECONDARY_QUANTITY_SHIPPED','PO_LINE_LOCATIONS_ALL',
136 'ERROR DERIVING SECONDARY QUANTITY SHIPPED FROM QUANTITY SHIPPED',sysdate,sysdate);
137 END;
138 END LOOP;
139 Commit;
140 END update_po_shipment;
141
142 /*===========================================================================
143 -- PROCEDURE:
144 -- migrate_hazard_classes
145 --
146 -- DESCRIPTION:
147 -- This PL/SQL procedure is used to migrate hazard classes from the Regulatory property
148 -- values table to the po_hazard_classes tables.
149 --
150 -- PARAMETERS:
151 -- p_migration_run_id - Migration run id to be used for writing to the message log
152 -- p_commit - Indicates if commit should be issued after logical unit is migrated
153 -- x_failure_count - Returns the number of failures that occurred during migration
154 --
155 -- SYNOPSIS:
156 -- migrate_hazard_classes(
157 -- p_migration_run_id => migration_id,
158 -- p_commit => 'Y',
159 -- x_failure_count => failure_count );
160 --
161 -- HISTORY
162 -- M. Grosser 10-May-2005 Created
163 --=========================================================================== */
164 PROCEDURE migrate_hazard_classes
165 (
166 p_migration_run_id IN NUMBER,
167 p_commit IN VARCHAR2,
168 x_failure_count OUT NOCOPY NUMBER
169 ) IS
170
171 /* ------------- LOCAL VARIABLES ------------------- */
172 l_temp NUMBER;
173 l_rowid VARCHAR2(2000);
174 l_seq NUMBER;
175 l_mig_status NUMBER;
176 l_migration_count NUMBER:=0;
177 l_exists_count NUMBER:=0;
178 l_hazard_description VARCHAR2(240);
179
180 /* ------------------ CURSORS ---------------------- */
181 /* Cursor used retrieve the hazard classification codes */
182 CURSOR c_get_hazard_classes IS
183 SELECT *
184 FROM gr_property_values_tl
185 WHERE language = userenv('LANG') and
186 property_id = 'UNCLSS';
187 l_hazard_class_rec c_get_hazard_classes%ROWTYPE;
188
189 /* Cursor used to check if the hazard class is already in the table */
190 CURSOR c_check_existence (v_hazard_class VARCHAR2) IS
191 SELECT 1
192 FROM sys.dual
193 WHERE EXISTS (SELECT 1
194 FROM po_hazard_classes_tl
195 WHERE hazard_class = v_hazard_class);
196
197 /* Cursor used to retrieve the next sequence number */
198 CURSOR c_get_seq IS
199 SELECT PO_HAZARD_CLASSES_S.nextval
200 FROM sys.dual;
201
202 /* Cursor used to retrieve translated descriptions for installed languages */
203 CURSOR c_get_translated (v_hazard_class VARCHAR2) IS
204 SELECT *
205 FROM gr_property_values_tl
206 WHERE language in (SELECT language_code
207 FROM fnd_languages
208 WHERE language_code <> userenv('LANG')
209 AND installed_flag in ('I','B'))
210 AND value = v_hazard_class;
211 l_translated_rec c_get_translated%ROWTYPE;
212
213 BEGIN
214
215 x_failure_count := 0;
216
217 GMA_COMMON_LOGGING.gma_migration_central_log (
218 p_run_id => P_migration_run_id,
219 p_log_level => FND_LOG.LEVEL_PROCEDURE,
220 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
221 p_table_name => 'PO_HAZARD_CLASSES',
222 p_context => 'HAZARD_CLASSES',
223 p_param1 => NULL,
224 p_param2 => NULL,
225 p_param3 => NULL,
226 p_param4 => NULL,
227 p_param5 => NULL,
228 p_db_error => NULL,
229 p_app_short_name => 'GMA');
230
231
232 /* Select hazard classes that have not yet been migrated */
233 OPEN c_get_hazard_classes;
234 FETCH c_get_hazard_classes into l_hazard_class_rec;
235
236 /* While there are results that have not been migrated */
237 WHILE c_get_hazard_classes%FOUND LOOP
238
239 BEGIN
240
241 SAVEPOINT Hazard_Class;
242
243 /* Check to see if the hazard class already exists in the table */
244 OPEN c_check_existence(l_hazard_class_rec.value);
245 FETCH c_check_existence into l_temp;
246
247 IF c_check_existence%NOTFOUND THEN
248
249 l_mig_status := 1;
250
251 /* Retrieve next sequence value */
252 OPEN c_get_seq;
253 FETCH c_get_seq INTO l_seq;
254 CLOSE c_get_seq;
255
256 PO_HAZARD_CLASSES_PKG.insert_row (
257 X_ROWID => l_rowid,
258 X_HAZARD_CLASS_ID => l_seq,
259 X_HAZARD_CLASS => l_hazard_class_rec.value,
260 X_DESCRIPTION => l_hazard_class_rec.meaning,
261 X_INACTIVE_DATE => NULL,
262 X_CREATION_DATE => l_hazard_class_rec.CREATION_DATE,
263 X_CREATED_BY => l_hazard_class_rec.CREATED_BY,
264 X_LAST_UPDATE_DATE => l_hazard_class_rec.LAST_UPDATE_DATE,
265 X_LAST_UPDATED_BY => l_hazard_class_rec.LAST_UPDATED_BY,
266 X_LAST_UPDATE_LOGIN => l_hazard_class_rec.LAST_UPDATE_LOGIN,
267 X_ATTRIBUTE_CATEGORY => NULL,
268 X_ATTRIBUTE1 => NULL,
269 X_ATTRIBUTE2 => NULL,
270 X_ATTRIBUTE3 => NULL,
271 X_ATTRIBUTE4 => NULL,
272 X_ATTRIBUTE5 => NULL,
273 X_ATTRIBUTE6 => NULL,
274 X_ATTRIBUTE7 => NULL,
275 X_ATTRIBUTE8 => NULL,
276 X_ATTRIBUTE9 => NULL,
277 X_ATTRIBUTE10 => NULL,
278 X_ATTRIBUTE11 => NULL,
279 X_ATTRIBUTE12 => NULL,
280 X_ATTRIBUTE13 => NULL,
281 X_ATTRIBUTE14 => NULL,
282 X_ATTRIBUTE15 => NULL,
283 X_REQUEST_ID => NULL );
287 FETCH c_get_translated INTO l_translated_rec;
284
285
286 OPEN c_get_translated(l_hazard_class_rec.value);
288
289 WHILE c_get_translated%FOUND LOOP
290
291 /* Update the descriptions with the values from Regulatory */
292 UPDATE po_hazard_classes_tl
293 SET description = l_translated_rec.meaning,
294 source_lang = l_translated_rec.source_lang,
295 creation_date = l_translated_rec.creation_date,
296 created_by = l_translated_rec.created_by,
297 last_update_date = l_translated_rec.last_update_date,
298 last_updated_by = l_translated_rec.last_updated_by,
299 last_update_login = l_translated_rec.last_update_login
300 WHERE language = l_translated_rec.language
301 AND hazard_class_id = l_seq;
302
303 FETCH c_get_translated INTO l_translated_rec;
304 END LOOP;
305 CLOSE c_get_translated;
306
307 ELSE
308 l_mig_status := 0;
309
310 GMA_COMMON_LOGGING.gma_migration_central_log (
311 p_run_id => P_migration_run_id,
312 p_log_level => FND_LOG.LEVEL_STATEMENT,
313 p_message_token => 'GR_HAZARD_CLASS_EXISTS',
314 p_table_name => 'PO_HAZARD_CLASSES',
315 p_context => 'HAZARD_CLASSES',
316 p_param1 => l_hazard_class_rec.value,
317 p_param2 => NULL,
318 p_param3 => NULL,
319 p_param4 => NULL,
320 p_param5 => NULL,
321 p_db_error => NULL,
322 p_app_short_name => 'GR');
323
324 END IF;
325
326 CLOSE c_check_existence;
327
328 /* Issue commit if required */
329 IF p_commit = 'Y' THEN
330 COMMIT;
331 END IF;
332
333 /* Increment appropriate counter */
334 IF l_mig_status = 1 THEN
335 l_migration_count := l_migration_count + 1;
336 ELSE
337 l_exists_count := l_exists_count + 1;
338 END IF;
339
340
341 EXCEPTION
342 WHEN OTHERS THEN
343 x_failure_count := x_failure_count + 1;
344
345 GMA_COMMON_LOGGING.gma_migration_central_log (
346 p_run_id => P_migration_run_id,
347 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
348 p_message_token => 'GMA_MIGRATION_DB_ERROR',
349 p_table_name => 'PO_HAZARD_CLASSES',
350 p_context => 'HAZARD_CLASSES',
351 p_param1 => NULL,
352 p_param2 => NULL,
353 p_param3 => NULL,
354 p_param4 => NULL,
355 p_param5 => NULL,
356 p_db_error => SQLERRM,
357 p_app_short_name => 'GMA');
358
359 ROLLBACK to SAVEPOINT Hazard_Class;
360
361 END;
362
363 FETCH c_get_hazard_classes into l_hazard_class_rec;
364
365 END LOOP; /* Number or records selected */
366
367 CLOSE c_get_hazard_classes;
368
369
370 GMA_COMMON_LOGGING.gma_migration_central_log (
371 p_run_id => P_migration_run_id,
372 p_log_level => FND_LOG.LEVEL_PROCEDURE,
373 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
374 p_table_name => 'PO_HAZARD_CLASSES',
375 p_context => 'HAZARD_CLASSES',
376 p_param1 => l_migration_count,
377 p_param2 => x_failure_count,
378 p_param3 => NULL,
379 p_param4 => NULL,
380 p_param5 => NULL,
381 p_db_error => NULL,
382 p_app_short_name => 'GMA');
383
384
385 EXCEPTION
386 WHEN OTHERS THEN
387 x_failure_count := x_failure_count + 1;
388
389 GMA_COMMON_LOGGING.gma_migration_central_log (
390 p_run_id => P_migration_run_id,
391 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
392 p_message_token => 'GMA_MIGRATION_DB_ERROR',
393 p_table_name => 'PO_HAZARD_CLASSES',
394 p_context => 'HAZARD_CLASSES',
395 p_param1 => NULL,
396 p_param2 => NULL,
397 p_param3 => NULL,
398 p_param4 => NULL,
399 p_param5 => NULL,
400 p_db_error => SQLERRM,
401 p_app_short_name => 'GMA');
402
403 GMA_COMMON_LOGGING.gma_migration_central_log (
404 p_run_id => P_migration_run_id,
405 p_log_level => FND_LOG.LEVEL_PROCEDURE,
406 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
407 p_table_name => 'PO_HAZARD_CLASSES',
408 p_context => 'HAZARD_CLASSES',
409 p_param1 => x_failure_count,
410 p_param2 => NULL,
411 p_param3 => NULL,
412 p_param4 => NULL,
413 p_param5 => NULL,
414 p_db_error => NULL,
415 p_app_short_name => 'GMA');
416
417 ROLLBACK to SAVEPOINT Hazard_Class;
418
419 END migrate_hazard_classes;
420
421
422 /*===========================================================================
423 -- PROCEDURE:
424 -- migrate_un_numbers
425 --
426 -- DESCRIPTION:
430 -- PARAMETERS:
427 -- This PL/SQL procedure is used to migrate UN Number values from the Regulatory item
428 -- properties table to the po_hazard_classes tables.
429 --
431 -- p_migration_run_id - Migration run id to be used for writing to the message log
432 -- p_commit - Indicates if commit should be issued after logical unit is migrated
433 -- x_failure_count - Returns the number of failures that occurred during migration
434 --
435 -- SYNOPSIS:
436 -- migrate_un_numbers(
437 -- p_migration_run_id => migration_id,
438 -- p_commit => 'Y',
439 -- x_failure_count => failure_count );
440 --
441 -- HISTORY
442 -- M. Grosser 10-May-2005 Created
443 --=========================================================================== */
444 PROCEDURE migrate_un_numbers
445 (
446 p_migration_run_id IN NUMBER,
447 p_commit IN VARCHAR2,
448 x_failure_count OUT NOCOPY NUMBER
449 ) IS
450
451 /* ------------- LOCAL VARIABLES ------------------- */
452 l_temp NUMBER;
453 l_rowid VARCHAR2(2000);
454 l_seq NUMBER;
455 l_mig_status NUMBER;
456 l_migration_count NUMBER:=0;
457 l_exists_count NUMBER:=0;
458 l_hazard_class_id NUMBER;
459 l_return_status VARCHAR2(2);
460 l_msg_data VARCHAR2(2000);
461 l_un_number NUMBER;
462
463 /* ------------------ CURSORS ---------------------- */
464 /* Cursor used retrieve the un number values */
465 CURSOR c_get_un_numbers IS
466 SELECT DISTINCT(number_value)
467 FROM gr_item_properties
468 WHERE migration_ind IS NULL and
469 property_id = 'UNNUMB' and
470 label_code = '14001';
471
472 CURSOR c_get_un_number_details IS
473 SELECT *
474 FROM gr_item_properties
475 WHERE migration_ind IS NULL and
476 number_value = l_un_number and
477 property_id = 'UNNUMB' and
478 label_code = '14001'
479 ORDER BY creation_date;
480 l_un_number_rec c_get_un_number_details%ROWTYPE;
481
482 /* Cursor used to check if the un number is already in the table */
483 CURSOR c_check_existence (v_un_number VARCHAR2) IS
484 SELECT 1
485 FROM sys.dual
486 WHERE EXISTS (SELECT 1
487 FROM po_un_numbers_tl
488 WHERE un_number = 'UN'||v_un_number);
489
490 /* Cursor used to retrieve the next sequence number */
491 CURSOR c_get_seq IS
492 SELECT PO_UN_NUMBERS_S.nextval
493 FROM sys.dual;
494
495 BEGIN
496
497 x_failure_count := 0;
498
499 GMA_COMMON_LOGGING.gma_migration_central_log (
500 p_run_id => P_migration_run_id,
501 p_log_level => FND_LOG.LEVEL_PROCEDURE,
502 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
503 p_table_name => 'PO_UN_NUMBERS',
504 p_context => 'UN_NUMBERS',
505 p_param1 => NULL,
506 p_param2 => NULL,
507 p_param3 => NULL,
508 p_param4 => NULL,
509 p_param5 => NULL,
510 p_db_error => NULL,
511 p_app_short_name => 'GMA');
512
513
514 /* Select un numbers that have not yet been migrated */
515 OPEN c_get_un_numbers;
516 FETCH c_get_un_numbers into l_un_number;
517
518 /* While there are un numbers that have not been migrated */
519 WHILE c_get_un_numbers%FOUND LOOP
520
521 BEGIN
522
523 SAVEPOINT UN_Number;
524
525 /* Check to see if the un number already exists in the table */
526 IF c_check_existence%ISOPEN THEN
527 CLOSE c_check_existence;
528 END IF;
529
530 OPEN c_check_existence(TO_CHAR(l_un_number));
531 FETCH c_check_existence into l_temp;
532
533 IF c_check_existence%NOTFOUND THEN
534
535 l_mig_status := 1;
536
537 /* Retrieve next sequence value */
538 OPEN c_get_seq;
539 FETCH c_get_seq INTO l_seq;
540 CLOSE c_get_seq;
541
542 /* Retrieve un number details */
543 OPEN c_get_un_number_details;
544 FETCH c_get_un_number_details INTO l_un_number_rec;
545 CLOSE c_get_un_number_details;
546
547 l_hazard_class_id := NULL;
548
549 l_hazard_class_id := GR_MIGRATE_TO_12.get_hazard_class_id(
550 p_item_code => l_un_number_rec.item_code,
551 x_return_status => l_return_status,
552 x_msg_data => l_msg_data );
553
554
555 PO_UN_NUMBERS_PKG.insert_row (
556 X_ROWID => l_rowid,
557 X_UN_NUMBER_ID => l_seq,
558 X_UN_NUMBER => 'UN'||TO_CHAR(l_un_number_rec.number_value),
559 X_DESCRIPTION => 'UN'||TO_CHAR(l_un_number_rec.number_value),
560 X_HAZARD_CLASS_ID => l_hazard_class_id,
561 X_INACTIVE_DATE => NULL,
562 X_CREATION_DATE => l_un_number_rec.CREATION_DATE,
563 X_CREATED_BY => l_un_number_rec.CREATED_BY,
564 X_LAST_UPDATE_DATE => l_un_number_rec.LAST_UPDATE_DATE,
565 X_LAST_UPDATED_BY => l_un_number_rec.LAST_UPDATED_BY,
566 X_LAST_UPDATE_LOGIN => l_un_number_rec.LAST_UPDATE_LOGIN,
567 X_ATTRIBUTE_CATEGORY => NULL,
568 X_ATTRIBUTE1 => NULL,
569 X_ATTRIBUTE2 => NULL,
570 X_ATTRIBUTE3 => NULL,
571 X_ATTRIBUTE4 => NULL,
572 X_ATTRIBUTE5 => NULL,
573 X_ATTRIBUTE6 => NULL,
574 X_ATTRIBUTE7 => NULL,
575 X_ATTRIBUTE8 => NULL,
576 X_ATTRIBUTE9 => NULL,
577 X_ATTRIBUTE10 => NULL,
578 X_ATTRIBUTE11 => NULL,
579 X_ATTRIBUTE12 => NULL,
580 X_ATTRIBUTE13 => NULL,
581 X_ATTRIBUTE14 => NULL,
582 X_ATTRIBUTE15 => NULL,
583 X_REQUEST_ID => NULL );
584
585 ELSE
586 l_mig_status := 0;
587
588 GMA_COMMON_LOGGING.gma_migration_central_log (
589 p_run_id => P_migration_run_id,
590 p_log_level => FND_LOG.LEVEL_STATEMENT,
591 p_message_token => 'GR_UN_NUMBER_EXISTS',
592 p_table_name => 'PO_UN_NUMBERS',
593 p_context => 'UN_NUMBERS',
594 p_param1 => TO_CHAR(l_un_number_rec.number_value),
595 p_param2 => NULL,
596 p_param3 => NULL,
597 p_param4 => NULL,
598 p_param5 => NULL,
599 p_db_error => NULL,
600 p_app_short_name => 'GR');
601
602 END IF;
603
604 CLOSE c_check_existence;
605
606 /* Set record status to migrated */
607 UPDATE gr_item_properties
608 SET migration_ind = l_mig_status
609 WHERE number_value = l_un_number_rec.number_value and
610 property_id = 'UNNUMB' and
611 label_code = '14001';
612
613 /* Issue commit if required */
614 IF p_commit = 'Y' THEN
615 COMMIT;
616 END IF;
617
618 /* Increment appropriate counter */
619 IF l_mig_status = 1 THEN
620 l_migration_count := l_migration_count + 1;
621 ELSE
622 l_exists_count := l_exists_count + 1;
623 END IF;
624
625
626 EXCEPTION
627 WHEN OTHERS THEN
628 x_failure_count := x_failure_count + 1;
629
630 ROLLBACK to SAVEPOINT UN_Number;
631
632 GMA_COMMON_LOGGING.gma_migration_central_log (
633 p_run_id => P_migration_run_id,
634 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
635 p_message_token => 'GMA_MIGRATION_DB_ERROR',
636 p_table_name => 'PO_UN_NUMBERS',
637 p_context => 'UN_NUMBERS',
638 p_param1 => NULL,
639 p_param2 => NULL,
640 p_param3 => NULL,
641 p_param4 => NULL,
642 p_param5 => NULL,
643 p_db_error => SQLERRM,
644 p_app_short_name => 'GMA');
645
646 END;
647
648 FETCH c_get_un_numbers into l_un_number;
649
650 END LOOP; /* Number or records selected */
651
652 CLOSE c_get_un_numbers;
653
654
655 GMA_COMMON_LOGGING.gma_migration_central_log (
656 p_run_id => P_migration_run_id,
657 p_log_level => FND_LOG.LEVEL_PROCEDURE,
658 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
659 p_table_name => 'PO_UN_NUMBERS',
660 p_context => 'UN_NUMBERS',
661 p_param1 => l_migration_count,
662 p_param2 => x_failure_count,
663 p_param3 => NULL,
664 p_param4 => NULL,
665 p_param5 => NULL,
666 p_db_error => NULL,
667 p_app_short_name => 'GMA');
668
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 x_failure_count := x_failure_count + 1;
673
674 GMA_COMMON_LOGGING.gma_migration_central_log (
675 p_run_id => P_migration_run_id,
676 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
677 p_message_token => 'GMA_MIGRATION_DB_ERROR',
678 p_table_name => 'PO_UN_NUMBERS',
679 p_context => 'UN_NUMBERS',
680 p_param1 => NULL,
681 p_param2 => NULL,
682 p_param3 => NULL,
683 p_param4 => NULL,
684 p_param5 => NULL,
685 p_db_error => SQLERRM,
686 p_app_short_name => 'GMA');
687
688 GMA_COMMON_LOGGING.gma_migration_central_log (
689 p_run_id => P_migration_run_id,
690 p_log_level => FND_LOG.LEVEL_PROCEDURE,
691 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
692 p_table_name => 'PO_UN_NUMBERS',
693 p_context => 'UN_NUMBERS',
694 p_param1 => x_failure_count,
695 p_param2 => NULL,
696 p_param3 => NULL,
697 p_param4 => NULL,
698 p_param5 => NULL,
699 p_db_error => NULL,
700 p_app_short_name => 'GMA');
701
702 END migrate_un_numbers;
703
704 END PO_GML_CONV_MIG;
705