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