[Home] [Help]
PACKAGE BODY: APPS.GR_MIGRATE_TO_12
Source
1 PACKAGE BODY GR_MIGRATE_TO_12 AS
2 /* $Header: GRMIG12B.pls 120.10.12020000.2 2013/03/25 07:21:17 maychen ship $ */
3
4 /*===========================================================================
5 -- FUNCTION:
6 -- get_inventory_item_id
7 --
8 -- DESCRIPTION:
9 -- This PL/SQL procedure is used to retrieve an inventory_item_id after the item
10 -- has been migrated to the mtl_system_items_b table.
11 --
12 -- PARAMETERS:
13 -- p_organization_id - Organization id to use to retrieve the value
14 -- p_item_code - Item_code to use to retrieve the value
15 -- x_return_status - Returns the status of the function (success, failure, etc.)
16 -- x_msg_data - Returns message data if an error occurred
17 --
18 -- RETURNS:
19 -- inventory_item_id - ID of item in mtl_system_items_b table
20 --
21 -- SYNOPSIS:
22 -- l_item_id := get_inventory_item_id(
23 -- p_organization_id => l_org_id,
24 -- p_item_code => l_item_code,
25 -- x_return_status => l_return_status,
26 -- x_msg_data => l_msg_data );
27 --
28 -- HISTORY
29 --=========================================================================== */
30 FUNCTION get_inventory_item_id
31 (
32 p_organization_id IN NUMBER,
33 p_item_code IN VARCHAR2,
34 x_return_status OUT NOCOPY VARCHAR2,
35 x_msg_data OUT NOCOPY VARCHAR2
36 )
37 RETURN NUMBER IS
38
39 /* ------------- LOCAL VARIABLES ------------------- */
40 l_inventory_item_id NUMBER;
41
42 /* ------------------ CURSORS ---------------------- */
43 /* Cursor used retrieve the inventory_item_ id */
44 CURSOR c_get_inventory_item_id IS
45 SELECT inventory_item_id
46 FROM gr_item_general_mig
47 WHERE item_code = p_item_code
48 AND organization_id = p_organization_id;
49
50 /* ----------------- EXCEPTIONS -------------------- */
51 INVALID_ORG_ITEM EXCEPTION;
52
53 BEGIN
54
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56
57 /* Validate the inventory item id */
58 OPEN c_get_inventory_item_id;
59 FETCH c_get_inventory_item_id INTO l_inventory_item_id;
60
61 /* If inventory item not found */
62 IF c_get_inventory_item_id %NOTFOUND THEN
63 CLOSE c_get_inventory_item_id;
64 RAISE INVALID_ORG_ITEM;
65 END IF;
66
67 CLOSE c_get_inventory_item_id;
68
69 RETURN l_inventory_item_id;
70
71 EXCEPTION
72
73 WHEN INVALID_ORG_ITEM THEN
74 x_msg_data := 'INVALID_ORG_ITEM';
75 x_return_status := FND_API.G_RET_STS_ERROR;
76 RETURN NULL;
77
78 WHEN OTHERS THEN
79 x_msg_data := SQLERRM;
80 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
81 RETURN NULL;
82
83 END get_inventory_item_id;
84
85
86
87
88
89 /*===========================================================================
90 -- FUNCTION:
91 -- get_hazard_class_id
92 --
93 -- DESCRIPTION:
94 -- This PL/SQL procedure is used to retrieve a hazard class id for a given item
95 -- after the hazard class has been migrated to the po_hazard_classes table.
96 --
97 -- RETURNS:
98 -- hazard_class_id - ID of hazard class in po_hazard_classes table
99 --
100 -- PARAMETERS:
101 -- p_item_code - Item_code to retrieve value for
102 -- x_return_status - Returns the status of the function (success, failure, etc.)
103 -- x_msg_data - Returns message data if an error occurred
104 --
105 -- SYNOPSIS:
106 -- l_haz_class_id := get_hazard_class_id(
107 -- p_item_code => l_item_code,
108 -- x_return_status => l_return_status,
109 -- x_msg_data => l_msg_data );
110 --
111 -- HISTORY
112 --=========================================================================== */
113 FUNCTION get_hazard_class_id
114 (
115 p_item_code IN VARCHAR2,
116 x_return_status OUT NOCOPY VARCHAR2,
117 x_msg_data OUT NOCOPY VARCHAR2
118 )
119 RETURN NUMBER IS
120
121 /* ------------- LOCAL VARIABLES ------------------- */
122 l_hazard_class VARCHAR2(25);
123 l_hazard_class_id NUMBER;
124
125 /* ------------------ CURSORS ---------------------- */
126 /* Cursor used retrieve the hazard class */
127 CURSOR c_get_hazard_class IS
128 SELECT alpha_value
129 FROM gr_item_properties
130 WHERE label_code = '14002'
131 AND property_id = 'UNCLSS'
132 AND item_code = p_item_code;
133
134 /* Cursor used retrieve the hazard class_ id */
135 CURSOR c_get_hazard_class_id IS
136 SELECT hazard_class_id
137 FROM po_hazard_classes
138 WHERE hazard_class = l_hazard_class;
139
140 /* ----------------- EXCEPTIONS -------------------- */
141 INVALID_HAZARD_CLASS EXCEPTION;
142
143 BEGIN
144
145 x_return_status := FND_API.G_RET_STS_SUCCESS;
146
147 /* Retrieve the hazard class */
148 OPEN c_get_hazard_class;
149 FETCH c_get_hazard_class INTO l_hazard_class;
150
151 /* If hazard class not found */
152 IF c_get_hazard_class %NOTFOUND THEN
153 CLOSE c_get_hazard_class;
154 RETURN NULL;
155 END IF;
156 CLOSE c_get_hazard_class;
157
158 /* Retrieve the hazard class id */
159 OPEN c_get_hazard_class_id;
160 FETCH c_get_hazard_class_id INTO l_hazard_class_id;
161
162 /* If hazard class not found */
163 IF c_get_hazard_class_id %NOTFOUND THEN
164 CLOSE c_get_hazard_class_id;
165 RAISE INVALID_HAZARD_CLASS;
166 END IF;
167
168 CLOSE c_get_hazard_class_id;
169 RETURN l_hazard_class_id;
170
171 EXCEPTION
172 WHEN INVALID_HAZARD_CLASS THEN
173 x_msg_data := 'INVALID_HAZARD_CLASS';
174 x_return_status := FND_API.G_RET_STS_ERROR;
175 RETURN NULL;
176
177 WHEN OTHERS THEN
178 x_msg_data := SQLERRM;
179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
180 RETURN NULL;
181
182 END get_hazard_class_id;
183
184
185
186 /*===========================================================================
187 -- FUNCTION:
188 -- get_un_number_id
189 --
190 -- DESCRIPTION:
191 -- This PL/SQL procedure is used to retrieve a un number id for a given item
192 -- after the un number has been migrated to the po_un_numbers table.
193 --
194 -- PARAMETERS:
195 -- p_item_code - Item_code to retrieve value for
196 -- x_return_status - Returns the status of the function (success, failure, etc.)
197 -- x_msg_data - Returns message data if an error occurred
198 --
199 -- RETURNS:
200 -- un_number_id - ID of un_number in po_un_numbers_table
201 --
202 -- SYNOPSIS:
203 -- l_un_number_id := get_un_number_id(
204 -- p_item_code => l_item_code,
205 -- x_return_status => l_return_status,
206 -- x_msg_data => l_msg_data );
207 --
208 -- HISTORY
209 --=========================================================================== */
210 FUNCTION get_un_number_id
211 (
212 p_item_code IN VARCHAR2,
213 x_return_status OUT NOCOPY VARCHAR2,
214 x_msg_data OUT NOCOPY VARCHAR2
215 )
216 RETURN NUMBER IS
217
218 /* ------------- LOCAL VARIABLES ------------------- */
219 l_un_number VARCHAR2(240);
220 l_un_number_id NUMBER;
221
222 /* ------------------ CURSORS ---------------------- */
223 /* Cursor used retrieve the un_number */
224 CURSOR c_get_un_number IS
225 SELECT 'UN'||TO_CHAR(number_value)
226 FROM gr_item_properties
227 WHERE label_code = '14001'
228 AND property_id = 'UNNUMB'
229 AND item_code = p_item_code;
230
231 /* Cursor used retrieve the un_number_ id */
232 CURSOR c_get_un_number_id IS
233 SELECT un_number_id
234 FROM po_un_numbers
235 WHERE un_number = l_un_number;
236
237 /* ----------------- EXCEPTIONS -------------------- */
238 INVALID_UN_NUMBER EXCEPTION;
239
240
241 BEGIN
242
243 x_return_status := FND_API.G_RET_STS_SUCCESS;
244
245 /* Retrieve the un number */
246 OPEN c_get_un_number;
247 FETCH c_get_un_number INTO l_un_number;
248
249 /* If un number not found */
250 IF c_get_un_number %NOTFOUND THEN
251 CLOSE c_get_un_number;
252 RETURN NULL;
253 END IF;
254 CLOSE c_get_un_number;
255
256 /* Validate the un number */
257 OPEN c_get_un_number_id;
258 FETCH c_get_un_number_id INTO l_un_number_id;
259
260 /* If un number id not found */
261 IF c_get_un_number_id %NOTFOUND THEN
262 CLOSE c_get_un_number_id;
263 RAISE INVALID_UN_NUMBER;
264 END IF;
265
266 CLOSE c_get_un_number_id;
267 RETURN l_un_number_id;
268
269 EXCEPTION
270
271 WHEN INVALID_UN_NUMBER THEN
272 x_msg_data := 'INVALID_UN_NUMBER';
273 x_return_status := FND_API.G_RET_STS_ERROR;
274 RETURN NULL;
275
276 WHEN OTHERS THEN
277 x_msg_data := SQLERRM;
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279 RETURN NULL;
280
281 END get_un_number_id;
282
283
284
285 /*===========================================================================
286 -- PROCEDURE:
287 -- create_item_mig_records
288 --
289 -- DESCRIPTION:
290 -- This PL/SQL procedure is used to insert records into the gr_item_general_mig table.
291 -- This table will be used to drive the migration of Regulatory items to the specified
292 -- organizations.
293 --
294 -- PARAMETERS:
295 -- p_migration_run_id - Migration run id to be used for writing to the message log
296 -- p_commit - Indicates if commit should be issued after logical unit is migrated
297 -- x_failure_count - Returns the number of failures that occurred during migration
298 --
299 -- SYNOPSIS:
300 -- create_item_mig_records(
301 -- p_migration_run_id => migration_id,
302 -- p_commit => 'Y',
303 -- x_failure_count => failure_count );
304 --
305 -- HISTORY
306 -- M. Grosser 08-Dec-2005: Modified code to put out warning if Regulatory data
307 -- exists and no orgs are designated as Regulatory orgs
308 --=========================================================================== */
309 PROCEDURE create_item_mig_records
310 (
311 p_migration_run_id IN NUMBER,
312 p_commit IN VARCHAR2,
313 x_failure_count OUT NOCOPY NUMBER
314 ) IS
315
316 /* ------------- LOCAL VARIABLES ------------------- */
317 l_temp NUMBER;
318 l_organization_id NUMBER;
319 l_seq NUMBER;
320 l_mig_status NUMBER;
321 l_migration_count NUMBER:=0;
322 l_recs_inserted NUMBER:=0;
323 l_reg_orgs_found NUMBER:=0;
324
325 /* ------------------ CURSORS ---------------------- */
326 /* Cursor used retrieve the master organizations that will track Regulatory data */
327 CURSOR c_get_master_orgs IS
328 SELECT organization_id
329 FROM sy_orgn_mst_b
330 WHERE master_organization_id IS NULL and
331 regulatory_org_ind = 'Y'and
332 organization_id is not null; --in case the org is not migrated then this indicates that
333
334 /* Cursor used retrieve the child organizations that will track Regulatory data */
335 CURSOR c_get_child_orgs IS
336 SELECT organization_id
337 FROM sy_orgn_mst_b
338 WHERE master_organization_id IS NOT NULL and
339 regulatory_org_ind = 'Y' and
340 organization_id is not null;
341
342 /* Cursor used check if there is Regulatory data if no orgs are set as Regualtory orgs */
343 CURSOR c_check_reg_data IS
344 SELECT 1
345 FROM gr_item_general;
346
347 /* ----------------- EXCEPTIONS -------------------- */
348 NO_REG_ORG EXCEPTION;
349
350 BEGIN
351 x_failure_count := 0;
352
353 GMA_COMMON_LOGGING.gma_migration_central_log (
354 p_run_id => P_migration_run_id,
355 p_log_level => FND_LOG.LEVEL_PROCEDURE,
356 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
357 p_table_name => 'GR_ITEM_GENERAL_MIG',
358 p_context => 'GR_ITEM_MIGRATION_TABLE',
359 p_param1 => NULL,
360 p_param2 => NULL,
361 p_param3 => NULL,
362 p_param4 => NULL,
363 p_param5 => NULL,
364 p_db_error => NULL,
365 p_app_short_name => 'GMA');
366
367 /* Select master orgs that have not yet been migrated - master orgs get migrated first*/
368 OPEN c_get_master_orgs;
369 FETCH c_get_master_orgs into l_organization_id;
370
371 /* While there are results that have not been migrated */
372 WHILE c_get_master_orgs%FOUND LOOP
373
374 l_reg_orgs_found := 1;
375
376 INSERT INTO gr_item_general_mig
377 (
378 item_code,
379 organization_id,
380 inventory_item_id,
381 migration_ind
382 )
383 SELECT
384 a.item_code,
385 l_organization_id,
386 NULL,
387 NULL
388 FROM gr_item_general a
389 WHERE NOT EXISTS (SELECT 1 from gr_item_general_mig b
390 WHERE b.item_code = a.item_code and
391 b.organization_id = l_organization_id);
392
393 /* Issue commit if required */
394 IF p_commit = FND_API.G_TRUE THEN
395 COMMIT;
396 END IF;
397
398 l_migration_count:= l_migration_count + l_recs_inserted;
399
400 FETCH c_get_master_orgs into l_organization_id;
401
402 END LOOP;
403 CLOSE c_get_master_orgs;
404
405 /* Select master orgs that have not yet been migrated - master orgs get migrated first*/
406 OPEN c_get_child_orgs;
407 FETCH c_get_child_orgs into l_organization_id;
408
409 /* While there are results that have not been migrated */
410 WHILE c_get_child_orgs%FOUND LOOP
411
412 l_reg_orgs_found := 1;
413
414 INSERT INTO gr_item_general_mig
415 (
416 item_code,
417 organization_id,
418 inventory_item_id,
419 migration_ind
420 )
421 SELECT
422 a.item_code,
423 l_organization_id,
424 NULL,
425 NULL
426 FROM gr_item_general a
427 WHERE NOT EXISTS (SELECT 1 from gr_item_general_mig b
428 WHERE b.item_code = a.item_code and
429 b.organization_id = l_organization_id);
430
431 /* Issue commit if required */
432 IF p_commit = FND_API.G_TRUE THEN
433 COMMIT;
434 END IF;
435
436 FETCH c_get_child_orgs into l_organization_id;
437
438 END LOOP;
439 CLOSE c_get_child_orgs;
440
441 /* If no organizations are designated as Regulatory orgs, raise an error if there is Regulatory data */
442 IF l_reg_orgs_found = 0 THEN
443 OPEN c_check_reg_data;
444 FETCH c_check_reg_data into l_temp;
445 IF c_check_reg_data%NOTFOUND THEN
446 RAISE NO_REG_ORG;
447 END IF;
448 END IF;
449
450 GMA_COMMON_LOGGING.gma_migration_central_log (
451 p_run_id => P_migration_run_id,
452 p_log_level => FND_LOG.LEVEL_PROCEDURE,
453 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
454 p_table_name => 'GR_ITEM_GENERAL_MIG',
455 p_context => 'GR_ITEM_MIGRATION_TABLE',
456 p_param1 => l_migration_count,
457 p_param2 => x_failure_count,
458 p_param3 => NULL,
459 p_param4 => NULL,
460 p_param5 => NULL,
461 p_db_error => NULL,
462 p_app_short_name => 'GMA');
463
464
465 EXCEPTION
466
467 WHEN NO_REG_ORG THEN
468
469 GMA_COMMON_LOGGING.gma_migration_central_log (
470 p_run_id => P_migration_run_id,
471 p_log_level => FND_LOG.LEVEL_ERROR,
472 p_message_token => 'GR_NO_REG_ORG',
473 p_table_name => 'GR_ITEM_GENERAL_MIG',
474 p_context => 'GR_ITEM_MIGRATION_TABLE',
475 p_app_short_name => 'GMA');
476
477 WHEN OTHERS THEN
478
479 x_failure_count := x_failure_count + 1;
480
481 GMA_COMMON_LOGGING.gma_migration_central_log (
482 p_run_id => P_migration_run_id,
483 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
484 p_message_token => 'GMA_MIGRATION_DB_ERROR',
485 p_table_name => 'GR_ITEM_GENERAL_MIG',
486 p_context => 'GR_ITEM_MIGRATION_TABLE',
487 p_param1 => NULL,
488 p_param2 => NULL,
489 p_param3 => NULL,
490 p_param4 => NULL,
491 p_param5 => NULL,
492 p_db_error => SQLERRM,
493 p_app_short_name => 'GMA');
494
495 GMA_COMMON_LOGGING.gma_migration_central_log (
496 p_run_id => P_migration_run_id,
497 p_log_level => FND_LOG.LEVEL_PROCEDURE,
498 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
499 p_table_name => 'GR_ITEM_GENERAL_MIG',
500 p_context => 'GR_ITEM_MIGRATION_TABLE',
501 p_param1 => x_failure_count,
502 p_param2 => NULL,
503 p_param3 => NULL,
504 p_param4 => NULL,
505 p_param5 => NULL,
506 p_db_error => NULL,
507 p_app_short_name => 'GMA');
508
509 END create_item_mig_records;
510
511
512
513 /*===========================================================================
514 -- PROCEDURE:
515 -- migrate_regulatory_items
516 --
517 -- DESCRIPTION:
518 -- This PL/SQL procedure is used to migrate Regulatory Items to the
519 -- mtl_system_items tables, migrate properties to organization_specific
520 -- tables, migrate attachments.
521 --
522 -- PARAMETERS:
523 -- p_migration_run_id - Migration run id to be used for writing to the message log
524 -- p_commit - Indicates if commit should be issued after logical unit is migrated
525 -- x_failure_count - Returns the number of failures that occurred during migration
526 --
527 -- SYNOPSIS:
528 -- migrate_regulatory_items(
529 -- p_migration_run_id => migration_id,
530 -- p_commit => 'Y',
531 -- x_failure_count => failure_count );
532 --
533 -- HISTORY
534 -- M. Grosser 17-May-2005 Created
535 -- P Lowe 14-MAY-2012 Bug 14067151 - 11I TO R12 MIGRATION SCRIPTS,
536 -- OPM REGULATORY DATA, ERROR DUE TO MISSING CAS NUMB
537 -- relax mandatory primary_cas_number requirement
538 --=========================================================================== */
539 PROCEDURE migrate_regulatory_items
540 (
541 p_migration_run_id IN NUMBER,
542 p_commit IN VARCHAR2,
543 x_failure_count OUT NOCOPY NUMBER
544 ) IS
545
546 /* ------------- LOCAL VARIABLES ------------------- */
547 l_temp NUMBER;
548 l_rowid VARCHAR2(2000);
549 l_inv_category_id NUMBER;
550 l_reg_category_id NUMBER;
551 l_inventory_item_id NUMBER;
552 l_un_number_id NUMBER;
553 l_hazard_class_id NUMBER;
554 l_mig_status NUMBER;
555 l_migration_count NUMBER := 0;
556 l_exists_count NUMBER := 0;
557 l_return_status VARCHAR2(2);
558 l_msg_data VARCHAR2(2000);
559 l_hazard_description VARCHAR2(240);
560 l_failure_count NUMBER := 0;
561 l_doc_category_id NUMBER;
562 l_attached_doc_id NUMBER;
563 l_media_id NUMBER;
564 l_related_item_id NUMBER;
565
566 /* ------------------ CURSORS ---------------------- */
567 /* Cursor used to retrieve record from migration table */
568 CURSOR c_get_mig_rec IS
569 SELECT item_code, organization_id
570 FROM gr_item_general_mig
571 WHERE migration_ind is NULL;
572 l_mig_rec c_get_mig_rec%ROWTYPE;
573
574 /* Cursor used to retrieve document category ids */
575 CURSOR c_get_category_id(v_category_name VARCHAR2) IS
576 SELECT category_id
577 FROM fnd_document_categories
578 WHERE name = v_category_name;
579
580 /* Cursor used to check if item for organization is already in the table */
581 CURSOR c_check_exists IS
582 SELECT inventory_item_id
583 FROM mtl_system_items_b
584 WHERE segment1 = l_mig_rec.item_code and
585 organization_id = l_mig_rec.organization_id;
586
587 /* Cursor used to retrieve regulatory item record */
588 CURSOR c_get_reg_item IS
589 SELECT *
590 FROM gr_item_general
591 WHERE item_code = l_mig_rec.item_code;
592 l_reg_item_rec c_get_reg_item%ROWTYPE;
593
594 /* Cursor used to retrieve the Regulatory item description*/
595 CURSOR c_get_translated IS
596 SELECT *
597 FROM gr_multilingual_name_tl
598 WHERE language in (SELECT language_code
599 FROM fnd_languages
600 WHERE language_code <> userenv('LANG')
601 AND installed_flag in ('I','B')) and
602 label_code = '11007' and
603 item_code = l_mig_rec.item_code;
604 l_translated_rec c_get_translated%ROWTYPE;
605
606 /* Cursor used to retrieve related inventory items */
607 CURSOR c_get_related IS
608 SELECT *
609 FROM gr_generic_items_b
610 WHERE item_code = l_mig_rec.item_code;
611 l_related_rec c_get_related%ROWTYPE;
612
613 /* Cursor used to retrieve OPM item id */
614 CURSOR c_get_opm_item_id IS
615 SELECT item_id
616 FROM ic_item_mst_b
617 WHERE item_no = l_related_rec.item_no;
618 l_opm_item_id NUMBER := NULL;
619
620 /* Cursor used to retrieve document attached to Regulatory item */
621 CURSOR c_get_attachments IS
622 SELECT *
623 FROM fnd_attached_documents
624 WHERE entity_name = 'GR_ITEM_GENERAL' and
625 pk1_value = l_mig_rec.item_code;
626 l_attachment_rec c_get_attachments%ROWTYPE;
627
628
629 /* ----------------- EXCEPTIONS -------------------- */
630 INVALID_REG_ITEM EXCEPTION;
631 ITEM_CREATE_ERROR EXCEPTION;
632 NO_CAS_NUMBER EXCEPTION;
633 PROC_CALL_ERROR EXCEPTION;
634
635 BEGIN
636
637 x_failure_count := 0;
638
639 GMA_COMMON_LOGGING.gma_migration_central_log (
640 p_run_id => P_migration_run_id,
641 p_log_level => FND_LOG.LEVEL_PROCEDURE,
642 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
643 p_table_name => 'GR_ITEM_GENERAL',
644 p_context => 'REGULATORY_ITEMS',
645 p_param1 => NULL,
646 p_param2 => NULL,
647 p_param3 => NULL,
648 p_param4 => NULL,
649 p_param5 => NULL,
650 p_db_error => NULL,
651 p_app_short_name => 'GMA');
652
653
654 /* Select item/organization combinations that have not yet been migrated */
655 OPEN c_get_mig_rec;
656 FETCH c_get_mig_rec into l_mig_rec;
657
658 IF c_get_mig_rec%NOTFOUND THEN
659
660 GMA_COMMON_LOGGING.gma_migration_central_log (
661 p_run_id => P_migration_run_id,
662 p_log_level => FND_LOG.LEVEL_PROCEDURE,
663 p_message_token => 'GR_NO_REGITEMS_TO_MIG',
664 p_table_name => 'GR_ITEM_GENERAL',
665 p_context => 'REGULATORY_ITEMS',
666 p_param1 => NULL,
667 p_param2 => NULL,
668 p_param3 => NULL,
669 p_param4 => NULL,
670 p_param5 => NULL,
671 p_db_error => NULL,
672 p_app_short_name => 'GR');
673
674 ELSE
675
676 /* Retrieve category_id for document category MSDS_INV_ITEM */
677 OPEN c_get_category_id('MSDS_INV_ITEM');
678 FETCH c_get_category_id INTO l_inv_category_id;
679 CLOSE c_get_category_id;
680
681 /* Retrieve category_id for document category MSDS_REG_ITEM */
682 OPEN c_get_category_id('MSDS_REG_ITEM');
683 FETCH c_get_category_id INTO l_reg_category_id;
684 CLOSE c_get_category_id;
685
686 /* While there are results that have not been migrated */
687 WHILE c_get_mig_rec%FOUND LOOP
688
689 BEGIN
690
691 SAVEPOINT Org_Item;
692
693 /* Retrieve regulatory item info */
694 --Bug# 5293938 - close cursor if open
695 IF c_get_reg_item%ISOPEN THEN
696 CLOSE c_get_reg_item;
697 END IF;
698 OPEN c_get_reg_item;
699 FETCH c_get_reg_item INTO l_reg_item_rec;
700
701 IF c_get_reg_item%NOTFOUND THEN
702 CLOSE c_get_reg_item;
703 RAISE INVALID_REG_ITEM;
704 END IF;
705
706 CLOSE c_get_reg_item;
707
708 /* Make sure that there is a CAS number -- relax this for bug 14067151 and log warning instead */
709 IF l_reg_item_rec.primary_cas_number is NULL THEN
710 -- start 14067151
711 -- RAISE NO_CAS_NUMBER;
712 -- log warning instead -- 14067151
713 -- x_failure_count := x_failure_count + 1; -- 14067151
714
715 GMA_COMMON_LOGGING.gma_migration_central_log (
716 p_run_id => P_migration_run_id,
717 p_log_level => FND_LOG.LEVEL_PROCEDURE,
718 p_message_token => 'GR_NO_CAS_NUMBER',
719 p_table_name => 'GR_ITEM_GENERAL',
720 p_context => 'Migrate_regulatory_items',
721 p_param1 => l_reg_item_rec.item_code,
722 p_param2 => l_reg_item_rec.primary_cas_number,
723 p_param3 => NULL,
724 p_param4 => NULL,
725 p_param5 => NULL,
726 p_db_error => NULL,
727 p_app_short_name => 'GMA');
728 -- end 14067151
729
730 END IF; -- IF l_reg_item_rec.primary_cas_number is NULL THEN
731
732 --Bug# 5293938 - close cursor if open
733 IF c_check_exists%ISOPEN THEN
734 CLOSE c_check_exists;
735 END IF;
736
737 OPEN c_check_exists;
738 FETCH c_check_exists INTO l_inventory_item_id;
739
740 IF c_check_exists%NOTFOUND THEN
741
742 INV_OPM_ITEM_MIGRATION.get_ODM_regulatory_item
743 ( p_migration_run_id => p_migration_run_id,
744 p_item_code => l_mig_rec.item_code,
745 p_organization_id => l_mig_rec.organization_id,
746 p_mode => NULL,
747 p_commit => 'T',
748 x_inventory_item_id => l_inventory_item_id,
749 x_failure_count => l_failure_count);
750
751 IF l_failure_count > 0 THEN
752 x_failure_count := x_failure_count + l_failure_count;
753 RAISE ITEM_CREATE_ERROR;
754 END IF;
755
756 ELSE
757
758 GMA_COMMON_LOGGING.gma_migration_central_log (
759 p_run_id => P_migration_run_id,
760 p_log_level => FND_LOG.LEVEL_PROCEDURE,
761 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
762 p_table_name => 'GR_ITEM_GENERAL',
763 p_context => 'REGULATORY_ITEMS',
764 p_param1 => NULL,
765 p_param2 => NULL,
766 p_param3 => NULL,
767 p_param4 => NULL,
768 p_param5 => NULL,
769 p_db_error => NULL,
770 p_app_short_name => 'GMA');
771
772 END IF; -- If Item already exists
773 CLOSE c_check_exists;
774
775 /* Retrieve items UN Number */
776 l_un_number_id := get_un_number_id
777 (
778 p_item_code => l_mig_rec.item_code,
779 x_return_status => l_return_status,
780 x_msg_data => l_msg_data
781 );
782
783 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
784 RAISE PROC_CALL_ERROR;
785 END IF;
786
787 /* Retrieve items UN Class */
788 l_hazard_class_id := get_hazard_class_id
789 (
790 p_item_code => l_mig_rec.item_code,
791 x_return_status => l_return_status,
792 x_msg_data => l_msg_data
793 );
794
795 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
796 RAISE PROC_CALL_ERROR;
797 END IF;
798
799 UPDATE mtl_system_items_b
800 SET hazardous_material_flag = 'Y',
801 cas_number = l_reg_item_rec.primary_cas_number,
802 hazard_class_id = l_hazard_class_id,
803 un_number_id = l_un_number_id
804 WHERE organization_id = l_mig_rec.organization_id and
805 inventory_item_id = l_inventory_item_id;
806
807
808 INSERT INTO gr_item_explosion_properties
809 (
810 organization_id,
811 inventory_item_id,
812 actual_hazard,
813 ingredient_flag,
814 explode_ingredient_flag,
815 created_by,
816 creation_date,
817 last_updated_by,
818 last_update_date,
819 last_update_login
820 )
821 VALUES
822 (
823 l_mig_rec.organization_id,
824 l_inventory_item_id,
825 l_reg_item_rec.ACTUAL_HAZARD,
826 l_reg_item_rec.INGREDIENT_FLAG,
827 l_reg_item_rec.EXPLODE_INGREDIENT_FLAG,
828 l_reg_item_rec.CREATED_BY,
829 l_reg_item_rec.CREATION_DATE,
830 l_reg_item_rec.LAST_UPDATED_BY,
831 l_reg_item_rec.LAST_UPDATE_DATE,
832 l_reg_item_rec.LAST_UPDATE_LOGIN
833 );
834
835 OPEN c_get_translated;
836 FETCH c_get_translated INTO l_translated_rec;
837
838 WHILE c_get_translated%FOUND LOOP
839
840 /* Update the descriptions with the values from Regulatory */
841 UPDATE mtl_system_items_tl
842 SET description = l_translated_rec.name_description,
843 source_lang = l_translated_rec.source_lang,
844 creation_date = l_translated_rec.creation_date,
845 created_by = l_translated_rec.created_by,
846 last_update_date = l_translated_rec.last_update_date,
847 last_updated_by = l_translated_rec.last_updated_by,
848 last_update_login = l_translated_rec.last_update_login
849 WHERE language = l_translated_rec.language and
850 organization_id = l_mig_rec.organization_id and
851 inventory_item_id = l_inventory_item_id;
852
853 FETCH c_get_translated INTO l_translated_rec;
854
855 END LOOP; -- tranlated descriptions
856 CLOSE c_get_translated;
857
858
859 /* Copy all of the properties to the org/item combination */
860 INSERT INTO gr_inv_item_properties
861 (
862 organization_id,
863 inventory_item_id,
864 sequence_number,
865 property_id,
866 label_code,
867 number_value,
868 alpha_value,
869 date_value,
870 created_by,
871 creation_date,
872 last_updated_by,
873 last_update_date,
874 last_update_login
875 )
876 SELECT
877 l_mig_rec.organization_id,
878 l_inventory_item_id,
879 sequence_number,
880 property_id,
881 label_code,
882 number_value,
883 alpha_value,
884 date_value,
885 created_by,
886 creation_date,
887 last_updated_by,
888 last_update_date,
889 last_update_login
890 FROM gr_item_properties
891 WHERE label_code <> '14001' and
892 label_code <> '14002' and
893 item_code = l_reg_item_rec.item_code;
894
895
896 --Bug# 5293938 Close cursor if open
897 IF c_get_attachments%ISOPEN THEN
898 CLOSE c_get_attachments;
899 END IF;
900 OPEN c_get_attachments;
901 FETCH c_get_attachments INTO l_attachment_rec;
902
903 WHILE c_get_attachments%FOUND LOOP
904
905 /* Retrieve category_id for document category */
906 OPEN c_get_category_id(l_attachment_rec.attribute_category);
907 FETCH c_get_category_id INTO l_doc_category_id;
908 CLOSE c_get_category_id;
909
910 IF l_doc_category_id = l_reg_category_id THEN
911
912 UPDATE fnd_documents
913 SET category_id = l_inv_category_id
914 WHERE document_id = l_attachment_rec.document_id;
915
916 UPDATE fnd_documents_tl
917 SET doc_attribute_category = 'MSDS_INV_ITEM'
918 WHERE document_id = l_attachment_rec.document_id;
919
920 END IF;
921
922 --Bug# 5293938 Attached Document Id has to be populated from the sequence.
923 select fnd_attached_documents_s.nextval
924 into l_attached_doc_id
925 from sys.dual;
926
927 FND_ATTACHED_DOCUMENTS_PKG.Insert_Row(
928 X_Rowid => l_rowid,
929 X_attached_document_id => l_attached_doc_id,
930 X_document_id => l_attachment_rec.document_id,
931 X_creation_date => l_attachment_rec.creation_date,
932 X_created_by => l_attachment_rec.created_by,
933 X_last_update_date => l_attachment_rec.last_update_date,
934 X_last_updated_by => l_attachment_rec.last_updated_by,
935 X_last_update_login => l_attachment_rec.last_update_login,
936 X_seq_num => l_attachment_rec.seq_num,
937 X_entity_name => 'MTL_SYSTEM_ITEMS',
938 X_column1 => NULL,
939 X_pk1_value => l_mig_rec.organization_id,
940 X_pk2_value => l_inventory_item_id,
941 X_pk3_value => NULL,
942 X_pk4_value => NULL,
943 X_pk5_value => NULL,
944 X_automatically_added_flag => l_attachment_rec.automatically_added_flag,
945 X_request_id => l_attachment_rec.request_id,
946 X_datatype_id => NULL,
947 X_category_id => l_attachment_rec.category_id,
948 X_security_type => NULL,
949 X_security_id => NULL,
950 X_publish_flag => NULL,
951 X_storage_type => NULL,
952 X_usage_type => NULL,
953 X_language => NULL,
954 X_description => NULL,
955 X_file_name => NULL,
956 X_media_id => l_media_id,
957 X_attribute_category => l_attachment_rec.attribute_category,
958 X_attribute1 => l_attachment_rec.attribute1,
959 X_attribute2 => l_attachment_rec.attribute2,
960 X_attribute3 => l_attachment_rec.attribute3,
961 X_attribute4 => l_attachment_rec.attribute4,
962 X_attribute5 => l_attachment_rec.attribute5,
963 X_attribute6 => l_attachment_rec.attribute6,
964 X_attribute7 => l_attachment_rec.attribute7,
965 X_attribute8 => l_attachment_rec.attribute8,
966 X_attribute9 => l_attachment_rec.attribute9,
967 X_attribute10 => l_attachment_rec.attribute10,
968 X_attribute11 => l_attachment_rec.attribute11,
969 X_attribute12 => l_attachment_rec.attribute12,
970 X_attribute13 => l_attachment_rec.attribute13,
971 X_attribute14 => l_attachment_rec.attribute14,
972 X_attribute15 => l_attachment_rec.attribute15,
973 X_create_doc => 'N');
974
975 FETCH c_get_attachments INTO l_attachment_rec;
976
977 END LOOP; /* Item attachments */
978 CLOSE c_get_attachments;
979
980 --Bug# 5293938 Close cursor if open
981 IF c_get_related%ISOPEN THEN
982 CLOSE c_get_related;
983 END IF;
984 OPEN c_get_related;
985 FETCH c_get_related INTO l_related_rec;
986
987 WHILE c_get_related%FOUND LOOP
988 --Bug# 5293938 get opm item id of related item
989 OPEN c_get_opm_item_id;
990 FETCH c_get_opm_item_id into l_opm_item_id;
991 IF c_get_opm_item_id%NOTFOUND THEN
992 l_msg_data := 'Related item '||l_related_rec.item_no||' not found in ic_item_mst';
993 CLOSE c_get_opm_item_id;
994 RAISE PROC_CALL_ERROR;
995 END IF;
996 CLOSE c_get_opm_item_id;
997
998 INV_OPM_ITEM_MIGRATION.get_ODM_item
999 ( p_migration_run_id => p_migration_run_id,
1000 p_item_id => l_opm_item_id, --Bug# 5293938
1001 p_organization_id => l_mig_rec.organization_id,
1002 p_mode => NULL,
1003 p_commit => 'T',
1004 x_inventory_item_id => l_related_item_id,
1005 x_failure_count => l_failure_count);
1006
1007 MTL_RELATED_ITEMS_PKG.Insert_Row (
1008 X_Rowid => l_rowid,
1009 X_Inventory_Item_Id => l_inventory_item_id,
1010 X_Organization_Id => l_mig_rec.organization_id,
1011 X_Related_Item_Id => l_related_item_id,
1012 X_Relationship_Type_Id => 19,
1013 X_Reciprocal_Flag => 'N',
1014 X_Planning_Enabled_Flag => 'N',
1015 X_Start_Date => l_related_rec.creation_date,
1016 X_End_Date => NULL,
1017 X_Attr_Context => NULL,
1018 X_Attr_Char1 => NULL,
1019 X_Attr_Char2 => NULL,
1020 X_Attr_Char3 => NULL,
1021 X_Attr_Char4 => NULL,
1022 X_Attr_Char5 => NULL,
1023 X_Attr_Char6 => NULL,
1024 X_Attr_Char7 => NULL,
1025 X_Attr_Char8 => NULL,
1026 X_Attr_Char9 => NULL,
1027 X_Attr_Char10 => NULL,
1028 X_Attr_Num1 => NULL,
1029 X_Attr_Num2 => NULL,
1030 X_Attr_Num3 => NULL,
1031 X_Attr_Num4 => NULL,
1032 X_Attr_Num5 => NULL,
1033 X_Attr_Num6 => NULL,
1034 X_Attr_Num7 => NULL,
1035 X_Attr_Num8 => NULL,
1036 X_Attr_Num9 => NULL,
1037 X_Attr_Num10 => NULL,
1038 X_Attr_Date1 => NULL,
1039 X_Attr_Date2 => NULL,
1040 X_Attr_Date3 => NULL,
1041 X_Attr_Date4 => NULL,
1042 X_Attr_Date5 => NULL,
1043 X_Attr_Date6 => NULL,
1044 X_Attr_Date7 => NULL,
1045 X_Attr_Date8 => NULL,
1046 X_Attr_Date9 => NULL,
1047 X_Attr_Date10 => NULL,
1048 X_Last_Update_Date => l_related_rec.last_update_date,
1049 X_Last_Updated_By => l_related_rec.last_updated_by,
1050 X_Creation_Date => l_related_rec.creation_date,
1051 X_Created_By => l_related_rec.created_by,
1052 X_Last_Update_Login => l_related_rec.last_update_login,
1053 X_Object_Version_Number => NULL
1054 );
1055
1056 FETCH c_get_related INTO l_related_rec;
1057 END LOOP; -- Related Items
1058 CLOSE c_get_related;
1059
1060 UPDATE gr_item_general_mig
1061 SET migration_ind = 1,
1062 inventory_item_id = l_inventory_item_id
1063 WHERE item_code = l_mig_rec.item_code and
1064 organization_id = l_mig_rec. organization_id;
1065
1066 /* Issue commit if required */
1067 IF p_commit = FND_API.G_TRUE THEN
1068 COMMIT;
1069 END IF;
1070
1071 /* Increment appropriate counter */
1072 IF l_mig_status = 1 THEN
1073 l_migration_count := l_migration_count + 1;
1074 ELSE
1075 l_exists_count := l_exists_count + 1;
1076 END IF;
1077
1078 EXCEPTION
1079 WHEN INVALID_REG_ITEM THEN
1080 x_failure_count := x_failure_count + 1;
1081 GMA_COMMON_LOGGING.gma_migration_central_log (
1082 p_run_id => P_migration_run_id,
1083 p_log_level => FND_LOG.LEVEL_EXCEPTION,
1084 p_message_token => 'GR_INVALID_REG_ITEM',
1085 p_table_name => 'GR_ITEM_GENERAL',
1086 p_context => 'REGULATORY_ITEMS',
1087 p_param1 => l_mig_rec.item_code,
1088 p_param2 => NULL,
1089 p_param3 => NULL,
1090 p_param4 => NULL,
1091 p_param5 => NULL,
1092 p_db_error => SQLERRM,
1093 p_app_short_name => 'GR');
1094
1095 ROLLBACK to SAVEPOINT Org_Item;
1096 --Bug# 5293938 Add this exception handler
1097 WHEN NO_CAS_NUMBER THEN
1098 x_failure_count := x_failure_count + 1;
1099 GMA_COMMON_LOGGING.gma_migration_central_log (
1100 p_run_id => P_migration_run_id,
1101 p_log_level => FND_LOG.LEVEL_EXCEPTION,
1102 p_message_token => 'GR_NO_CAS_NUMBER',
1103 p_table_name => 'GR_ITEM_GENERAL',
1104 p_context => 'REGULATORY_ITEMS',
1105 p_param1 => l_mig_rec.item_code,
1106 p_param2 => NULL,
1107 p_param3 => NULL,
1108 p_param4 => NULL,
1109 p_param5 => NULL,
1110 p_db_error => SQLERRM,
1111 p_app_short_name => 'GR');
1112
1113 ROLLBACK to SAVEPOINT Org_Item;
1114
1115
1116 WHEN ITEM_CREATE_ERROR THEN
1117 x_failure_count := x_failure_count + 1;
1118 GMA_COMMON_LOGGING.gma_migration_central_log (
1119 p_run_id => P_migration_run_id,
1120 p_log_level => FND_LOG.LEVEL_EXCEPTION,
1121 p_message_token => 'GR_INV_ITEM_ERROR',
1122 p_table_name => 'GR_ITEM_GENERAL',
1123 p_context => 'REGULATORY_ITEMS',
1124 p_param1 => l_mig_rec.item_code,
1125 p_param2 => NULL,
1126 p_param3 => NULL,
1127 p_param4 => NULL,
1128 p_param5 => NULL,
1129 p_db_error => SQLERRM,
1130 p_app_short_name => 'GR');
1131
1132 ROLLBACK to SAVEPOINT Org_Item;
1133
1134 WHEN PROC_CALL_ERROR THEN
1135 x_failure_count := x_failure_count + 1;
1136 GMA_COMMON_LOGGING.gma_migration_central_log (
1137 p_run_id => P_migration_run_id,
1138 p_log_level => FND_LOG.LEVEL_EXCEPTION,
1139 p_message_token => l_msg_data,
1140 p_table_name => 'GR_ITEM_GENERAL',
1141 p_context => 'REGULATORY_ITEMS',
1142 p_param1 => NULL,
1143 p_param2 => NULL,
1144 p_param3 => NULL,
1145 p_param4 => NULL,
1146 p_param5 => NULL,
1147 p_db_error => SQLERRM,
1148 p_app_short_name => 'GMA');
1149
1150 ROLLBACK to SAVEPOINT Org_Item;
1151
1152 WHEN OTHERS THEN
1153 x_failure_count := x_failure_count + 1;
1154 GMA_COMMON_LOGGING.gma_migration_central_log (
1155 p_run_id => P_migration_run_id,
1156 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1157 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1158 p_table_name => 'GR_ITEM_GENERAL',
1159 p_context => 'REGULATORY_ITEMS',
1160 p_param1 => NULL,
1161 p_param2 => NULL,
1162 p_param3 => NULL,
1163 p_param4 => NULL,
1164 p_param5 => NULL,
1165 p_db_error => SQLERRM,
1166 p_app_short_name => 'GMA');
1167
1168 ROLLBACK to SAVEPOINT Org_Item;
1169
1170 END; -- Subprogram
1171
1172 FETCH c_get_mig_rec into l_mig_rec;
1173
1174 END LOOP; -- Records in migration table
1175 CLOSE c_get_mig_rec;
1176
1177 END IF; -- Unmigrated records found
1178
1179 GMA_COMMON_LOGGING.gma_migration_central_log (
1180 p_run_id => P_migration_run_id,
1181 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1182 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
1183 p_table_name => 'GR_ITEM_GENERAL',
1184 p_context => 'REGULATORY_ITEMS',
1185 p_param1 => l_migration_count,
1186 p_param2 => x_failure_count,
1187 p_param3 => NULL,
1188 p_param4 => NULL,
1189 p_param5 => NULL,
1190 p_db_error => NULL,
1191 p_app_short_name => 'GMA');
1192
1193 EXCEPTION
1194 WHEN OTHERS THEN
1195 x_failure_count := x_failure_count + 1;
1196 GMA_COMMON_LOGGING.gma_migration_central_log (
1197 p_run_id => P_migration_run_id,
1198 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1199 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1200 p_table_name => 'GR_ITEM_GENERAL',
1201 p_context => 'REGULATORY_ITEMS',
1202 p_param1 => NULL,
1203 p_param2 => NULL,
1204 p_param3 => NULL,
1205 p_param4 => NULL,
1206 p_param5 => NULL,
1207 p_db_error => SQLERRM,
1208 p_app_short_name => 'GMA');
1209
1210 GMA_COMMON_LOGGING.gma_migration_central_log (
1211 p_run_id => P_migration_run_id,
1212 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1213 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
1214 p_table_name => 'GR_ITEM_GENERAL',
1215 p_context => 'REGULATORY_ITEMS',
1216 p_param1 => x_failure_count,
1217 p_param2 => NULL,
1218 p_param3 => NULL,
1219 p_param4 => NULL,
1220 p_param5 => NULL,
1221 p_db_error => NULL,
1222 p_app_short_name => 'GMA');
1223
1224 END migrate_regulatory_items;
1225
1226
1227 /*===========================================================================
1228 -- PROCEDURE:
1229 -- migrate_standalone_formulas
1230 --
1231 -- DESCRIPTION:
1232 -- This PL/SQL procedure is used to migrate standalone Regulatory formulas
1233 -- to the formula, recipe and validity rules table.
1234 --
1235 -- PARAMETERS:
1236 -- p_migration_run_id - Migration run id to be used for writing to the message log
1237 -- p_commit - Indicates if commit should be issued after logical unit is migrated
1238 -- x_failure_count - Returns the number of failures that occurred during migration
1239 --
1240 -- SYNOPSIS:
1241 -- migrate_standalone_formulas(
1242 -- p_migration_run_id => migration_id,
1243 -- p_commit => 'Y',
1244 -- x_failure_count => failure_count );
1245 --
1246 -- HISTORY
1247 -- M. Grosser 17-May-2005 Created
1248 -- RLNAGARA 22-Apr-2008 Modified the call to GME_RECIPES_MLS for Fixed Process Loss ME
1249 --=========================================================================== */
1250 PROCEDURE migrate_standalone_formulas
1251 (
1252 p_migration_run_id IN NUMBER,
1253 p_commit IN VARCHAR2,
1254 x_failure_count OUT NOCOPY NUMBER
1255 ) IS
1256
1257 /* ------------- LOCAL VARIABLES ------------------- */
1258 l_temp NUMBER;
1259 l_rowid VARCHAR2(2000);
1260 l_migration_count NUMBER := 0;
1261 l_failure_count NUMBER := 0;
1262 l_exists_count NUMBER := 0;
1263 l_item_code VARCHAR2(32);
1264 l_uom_type sy_uoms_typ.um_type%TYPE;
1265 l_uom VARCHAR2(4);
1266 l_owner_org sy_orgn_mst.orgn_code%TYPE;
1267 l_owner_org_id sy_orgn_mst.organization_id%TYPE;
1268 l_inventory_item_id NUMBER;
1269 l_text VARCHAR2(80);
1270 l_line_no NUMBER;
1271 l_formula_id NUMBER;
1272 l_formulaline_id NUMBER;
1273 l_recipe_id NUMBER;
1274 l_validity_rule_id NUMBER;
1275 l_prod_primary_uom VARCHAR2(4);
1276 l_ing_primary_uom VARCHAR2(4);
1277 l_formula_vers NUMBER;
1278 l_recipe_vers NUMBER;
1279 l_prod_item_id NUMBER;
1280 l_ing_item_id NUMBER;
1281 l_mig_status NUMBER;
1282 l_return_status VARCHAR2(2);
1283 l_msg_data VARCHAR2(2000);
1284 l_recipe_type NUMBER;
1285 l_inv_qty NUMBER;
1286
1287 /* ------------------ CURSORS ---------------------- */
1288 /* Cursor used to retrieve items with formula type of standalone that have formulas saved */
1289 CURSOR c_get_items IS
1290 SELECT a.item_code
1291 FROM gr_item_general a
1292 WHERE EXISTS (SELECT 1
1293 FROM gr_item_concentrations b
1294 WHERE b.item_code = a.item_code) and
1295 a.formula_source_indicator = 'S';
1296
1297 /* Cursor used to retrieve profile value at site level */
1298 CURSOR c_get_profile_value(v_profile_name VARCHAR2) IS
1299 SELECT profile_option_value
1300 FROM fnd_profile_options a, fnd_profile_option_values b
1301 WHERE b.level_id = 10001 and
1302 a.profile_option_id = b.profile_option_id and
1303 a.profile_option_name = v_profile_name;
1304
1305 /* Cursor used to retrieve the std uom for FM_YIELD_TYPE class */
1306 CURSOR c_get_uom (v_um_type VARCHAR2) IS
1307 SELECT std_um
1308 FROM sy_uoms_typ
1309 WHERE um_type = v_um_type;
1310
1311 /* Cursor used to retrieve concentration records for item */
1312 CURSOR c_get_item_concentrations IS
1313 SELECT *
1314 FROM gr_item_concentrations
1315 WHERE migration_ind is NULL and
1316 item_code = l_item_code;
1317 l_conc_rec c_get_item_concentrations%ROWTYPE;
1318
1319 /* Cursor used to retrieve next formula version */
1320 CURSOR c_get_formula_vers IS
1321 SELECT MAX(formula_vers) + 1
1322 FROM fm_form_mst_b
1323 WHERE formula_no = l_item_code;
1324
1325 /* Cursor used to retrieve next recipe version */
1326 CURSOR c_get_recipe_vers IS
1327 SELECT MAX(recipe_version) + 1
1328 FROM gmd_recipes_b
1329 WHERE recipe_no = l_item_code;
1330
1331 /* Cursor used to retrieve organization_id */
1332 CURSOR c_get_organization_id (v_org_code VARCHAR2) IS
1333 SELECT organization_id
1334 FROM sy_orgn_mst
1335 WHERE orgn_code = v_org_code;
1336
1337 /* Cursor used to retrieve items primary_uom */
1338 CURSOR c_get_primary_uom (v_organization_id NUMBER, v_inventory_item_id NUMBER) IS
1339 SELECT primary_uom_code
1340 FROM mtl_system_items_b
1341 WHERE organization_id = v_organization_id and
1342 inventory_item_id = v_inventory_item_id;
1343
1344 /* Cursor used to retrieve next formula_id value */
1345 CURSOR c_get_formula_id IS
1346 SELECT gem5_formula_id_s.NEXTVAL
1347 FROM SYS.DUAL;
1348
1349 /* Cursor used to retrieve next formulaline_id value */
1350 CURSOR c_get_formulaline_id IS
1351 SELECT gem5_formulaline_id_s.NEXTVAL
1352 FROM SYS.DUAL;
1353
1354 /* Cursor used to retrieve next recipe_id value */
1355 CURSOR c_get_recipe_id IS
1356 SELECT gmd_recipe_id_s.NEXTVAL
1357 FROM SYS.DUAL;
1358
1359 /* Cursor used to retrieve next recipe_vr_id value */
1360 CURSOR c_get_recipe_vr_id IS
1361 SELECT gmd_recipe_validity_id_s.NEXTVAL
1362 FROM SYS.DUAL;
1363
1364 /* ------------------- EXCEPTIONS -------------------- */
1365 PROC_CALL_ERROR EXCEPTION;
1366 ORGN_NOT_MIGRATED EXCEPTION;
1367 NO_UOM_CONVERSION EXCEPTION;
1368
1369 BEGIN
1370
1371 x_failure_count := 0;
1372
1373 GMA_COMMON_LOGGING.gma_migration_central_log (
1374 p_run_id => P_migration_run_id,
1375 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1376 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
1377 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1378 p_context => 'STANDALONE_FORMULAS',
1379 p_param1 => NULL,
1380 p_param2 => NULL,
1381 p_param3 => NULL,
1382 p_param4 => NULL,
1383 p_param5 => NULL,
1384 p_db_error => NULL,
1385 p_app_short_name => 'GMA');
1386
1387 /* Retrieve default Regulatory org to use as owner org for formulas */
1388 OPEN c_get_profile_value('GR_ORGN_DEFAULT');
1389 FETCH c_get_profile_value INTO l_owner_org;
1390 CLOSE c_get_profile_value;
1391
1392 /* Retrieve organization_id to use as owner org for formulas */
1393 OPEN c_get_organization_id(l_owner_org);
1394 FETCH c_get_organization_id INTO l_owner_org_id;
1395 CLOSE c_get_organization_id;
1396
1397 IF (l_owner_org_id IS NULL) THEN
1398 RAISE ORGN_NOT_MIGRATED;
1399 END IF;
1400
1401
1402 /* Retrieve yield type to use to retrieve default uom */
1403 OPEN c_get_profile_value('FM_YIELD_TYPE');
1404 FETCH c_get_profile_value INTO l_uom_type;
1405 CLOSE c_get_profile_value;
1406
1407 /* Retrieve default uom */
1408 OPEN c_get_uom(l_uom_type);
1409 FETCH c_get_uom INTO l_uom;
1410 CLOSE c_get_uom;
1411
1412 /* Retrieve description text for formula and recipe */
1413 FND_MESSAGE.SET_NAME('GR','GR_DESC_TEXT');
1414 l_text := FND_MESSAGE.GET;
1415
1416 /* Select items that have a formula source of Standalone */
1417 OPEN c_get_items;
1418 FETCH c_get_items into l_item_code;
1419
1420 /* While items are found */
1421 WHILE c_get_items%FOUND LOOP
1422 BEGIN
1423 SAVEPOINT Standalone_Formula;
1424
1425 /* Select items that have a formula source of Standalone */
1426 --Bug# 5293938 Since its in a loop close it before reopening it.
1427 IF c_get_item_concentrations%ISOPEN THEN
1428 CLOSE c_get_item_concentrations;
1429 END IF;
1430 OPEN c_get_item_concentrations;
1431 FETCH c_get_item_concentrations into l_conc_rec;
1432
1433 IF c_get_item_concentrations%FOUND THEN
1434
1435 l_prod_item_id := get_inventory_item_id
1436 (
1437 p_organization_id => l_owner_org_id,
1438 p_item_code => l_item_code,
1439 x_return_status => l_return_status,
1440 x_msg_data => l_msg_data
1441 );
1442
1443 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1444 RAISE PROC_CALL_ERROR;
1445 END IF;
1446
1447 /* Retrieve product's primary uom */
1448 OPEN c_get_primary_uom(l_owner_org_id, l_prod_item_id);
1449 FETCH c_get_primary_uom INTO l_prod_primary_uom;
1450 CLOSE c_get_primary_uom;
1451
1452 /* If the item's primary uom is not equal to the formula uom */
1453 IF l_prod_primary_uom <> l_uom THEN
1454 l_inv_qty := INV_CONVERT.inv_um_convert
1455 (
1456 item_id => l_prod_item_id,
1457 precision => 5,
1458 from_quantity => 100,
1459 from_unit => l_prod_primary_uom,
1460 to_unit => l_uom,
1461 from_name => NULL,
1462 to_name => NULL
1463 );
1464
1465 IF l_inv_qty = -99999 THEN
1466 RAISE NO_UOM_CONVERSION;
1467 END IF;
1468 ELSE
1469 l_inv_qty := 100;
1470 END IF; -- Item's primary uom <> formula uom
1471
1472 /* Retrieve formula version */
1473 OPEN c_get_formula_vers;
1474 FETCH c_get_formula_vers INTO l_formula_vers;
1475
1476 --Bug# 5293938 added is null condition since group functions do not raise notfound
1477 IF c_get_formula_vers%NOTFOUND OR l_formula_vers IS NULL THEN
1478 l_formula_vers := 1;
1479 END IF;
1480 CLOSE c_get_formula_vers;
1481
1482 /* Retrieve recipe version */
1483 OPEN c_get_recipe_vers;
1484 FETCH c_get_recipe_vers INTO l_recipe_vers;
1485 --Bug# 5293938 added is null condition since group functions do not raise notfound
1486 IF c_get_recipe_vers%NOTFOUND OR l_recipe_vers IS NULL THEN
1487 l_recipe_vers := 1;
1488 END IF;
1489 CLOSE c_get_recipe_vers;
1490
1491 /* Retrieve formula id */
1492 OPEN c_get_formula_id;
1493 FETCH c_get_formula_id INTO l_formula_id;
1494 CLOSE c_get_formula_id;
1495
1496 /* Create formula header record and translated records */
1497 FM_FORM_MST_MLS.INSERT_ROW(
1498 X_ROWID => l_rowid,
1499 X_FORMULA_ID => l_formula_id,
1500 X_MASTER_FORMULA_ID => NULL,
1501 X_OWNER_ORGANIZATION_ID => l_owner_org_id,
1502 X_TOTAL_INPUT_QTY => 100,
1503 X_TOTAL_OUTPUT_QTY => 100,
1504 X_YIELD_UOM => l_uom,
1505 X_FORMULA_STATUS => '700',
1506 X_OWNER_ID => l_conc_rec.last_updated_by,
1507 X_PROJECT_ID => NULL,
1508 X_TEXT_CODE => NULL,
1509 X_DELETE_MARK => 0,
1510 X_FORMULA_NO => l_item_code,
1511 X_FORMULA_VERS => l_formula_vers,
1512 X_FORMULA_TYPE => 0,
1513 X_IN_USE => NULL,
1514 X_INACTIVE_IND => 0,
1515 X_SCALE_TYPE => 0,
1516 X_FORMULA_CLASS => NULL,
1517 X_FMCONTROL_CLASS => NULL,
1518 X_ATTRIBUTE_CATEGORY => NULL,
1519 X_ATTRIBUTE1 => NULL,
1520 X_ATTRIBUTE2 => NULL,
1521 X_ATTRIBUTE3 => NULL,
1522 X_ATTRIBUTE4 => NULL,
1523 X_ATTRIBUTE5 => NULL,
1524 X_ATTRIBUTE6 => NULL,
1525 X_ATTRIBUTE7 => NULL,
1526 X_ATTRIBUTE8 => NULL,
1527 X_ATTRIBUTE9 => NULL,
1528 X_ATTRIBUTE10 => NULL,
1529 X_ATTRIBUTE11 => NULL,
1530 X_ATTRIBUTE12 => NULL,
1531 X_ATTRIBUTE13 => NULL,
1532 X_ATTRIBUTE14 => NULL,
1533 X_ATTRIBUTE15 => NULL,
1534 X_ATTRIBUTE16 => NULL,
1535 X_ATTRIBUTE17 => NULL,
1536 X_ATTRIBUTE18 => NULL,
1537 X_ATTRIBUTE19 => NULL,
1538 X_ATTRIBUTE20 => NULL,
1539 X_ATTRIBUTE21 => NULL,
1540 X_ATTRIBUTE22 => NULL,
1541 X_ATTRIBUTE23 => NULL,
1542 X_ATTRIBUTE24 => NULL,
1543 X_ATTRIBUTE25 => NULL,
1544 X_ATTRIBUTE26 => NULL,
1545 X_ATTRIBUTE27 => NULL,
1546 X_ATTRIBUTE28 => NULL,
1547 X_ATTRIBUTE29 => NULL,
1548 X_ATTRIBUTE30 => NULL,
1549 X_FORMULA_DESC1 => l_text || ' ' || l_item_code,
1550 X_FORMULA_DESC2 => NULL,
1551 X_CREATION_DATE => l_conc_rec.creation_date,
1552 X_CREATED_BY => l_conc_rec.created_by,
1553 X_LAST_UPDATE_DATE => l_conc_rec.last_update_date,
1554 X_LAST_UPDATED_BY => l_conc_rec.last_updated_by,
1555 X_LAST_UPDATE_LOGIN => l_conc_rec.last_update_login);
1556
1557 IF l_rowid IS NULL THEN
1558 RAISE PROC_CALL_ERROR;
1559 END IF;
1560
1561 /* Retrieve formula line id */
1562 OPEN c_get_formulaline_id;
1563 FETCH c_get_formulaline_id INTO l_formulaline_id;
1564 CLOSE c_get_formulaline_id;
1565
1566 /* Create formula detail line for product */
1567 INSERT INTO fm_matl_dtl
1568 (
1569 FORMULALINE_ID,
1570 FORMULA_ID,
1571 LINE_TYPE,
1572 LINE_NO,
1573 INVENTORY_ITEM_ID,
1574 ORGANIZATION_ID,
1575 QTY,
1576 DETAIL_UOM,
1577 RELEASE_TYPE,
1578 SCRAP_FACTOR,
1579 SCALE_TYPE,
1580 PHANTOM_TYPE,
1581 REWORK_TYPE,
1582 CREATED_BY,
1583 CREATION_DATE,
1584 LAST_UPDATE_DATE,
1585 LAST_UPDATED_BY,
1586 LAST_UPDATE_LOGIN,
1587 CONTRIBUTE_STEP_QTY_IND,
1588 CONTRIBUTE_YIELD_IND
1589 )
1590 VALUES
1591 (
1592 l_formulaline_id,
1593 l_formula_id,
1594 1,
1595 1,
1596 l_prod_item_id,
1597 l_owner_org_id,
1598 100,
1599 l_uom,
1600 0,
1601 0,
1602 1,
1603 0,
1604 0,
1605 l_conc_rec.CREATED_BY,
1606 l_conc_rec.CREATION_DATE,
1607 l_conc_rec.LAST_UPDATE_DATE,
1608 l_conc_rec.LAST_UPDATED_BY,
1609 l_conc_rec.LAST_UPDATE_LOGIN,
1610 'Y',
1611 'Y'
1612 );
1613
1614 l_line_no := 0;
1615
1616 WHILE c_get_item_concentrations%FOUND LOOP
1617
1618 l_line_no := l_line_no +1;
1619
1620 l_ing_item_id := get_inventory_item_id
1621 (
1622 p_organization_id => l_owner_org_id,
1623 p_item_code => l_conc_rec.ingredient_item_code,
1624 x_return_status => l_return_status,
1625 x_msg_data => l_msg_data
1626 );
1627
1628 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1629 RAISE PROC_CALL_ERROR;
1630 END IF;
1631
1632 /* Retrieve ingredient's primary uom */
1633 OPEN c_get_primary_uom(l_owner_org_id, l_ing_item_id);
1634 FETCH c_get_primary_uom INTO l_ing_primary_uom;
1635 CLOSE c_get_primary_uom;
1636
1637 /* If the item's primary uom is not equal to the formula uom */
1638 IF l_prod_primary_uom <> l_uom THEN
1639 l_temp := INV_CONVERT.inv_um_convert
1640 (
1641 item_id => l_ing_item_id,
1642 precision => 5,
1643 from_quantity => 1,
1644 from_unit => l_ing_primary_uom,
1645 to_unit => l_uom,
1646 from_name => NULL,
1647 to_name => NULL
1648 );
1649
1650 IF l_temp = -99999 THEN
1651 RAISE NO_UOM_CONVERSION;
1652 END IF;
1653
1654 END IF; -- Item's primary uom <> formula uom
1655
1656 /* Retrieve formula line id */
1657 OPEN c_get_formulaline_id;
1658 FETCH c_get_formulaline_id INTO l_formulaline_id;
1659 CLOSE c_get_formulaline_id;
1660
1661 INSERT INTO fm_matl_dtl
1662 (
1663 FORMULALINE_ID,
1664 FORMULA_ID,
1665 LINE_TYPE,
1666 LINE_NO,
1667 INVENTORY_ITEM_ID,
1668 ORGANIZATION_ID,
1669 QTY,
1670 DETAIL_UOM,
1671 RELEASE_TYPE,
1672 SCRAP_FACTOR,
1673 SCALE_TYPE,
1674 PHANTOM_TYPE,
1675 REWORK_TYPE,
1676 CREATED_BY,
1677 CREATION_DATE,
1678 LAST_UPDATE_DATE,
1679 LAST_UPDATED_BY,
1680 LAST_UPDATE_LOGIN,
1681 CONTRIBUTE_STEP_QTY_IND,
1682 CONTRIBUTE_YIELD_IND
1683 )
1684 VALUES
1685 (
1686 l_formulaline_id,
1687 l_formula_id,
1688 -1,
1689 l_line_no,
1690 l_ing_item_id,
1691 l_owner_org_id,
1692 l_conc_rec.concentration_percentage,
1693 l_uom,
1694 0,
1695 0,
1696 1,
1697 0,
1698 0,
1699 l_conc_rec.CREATED_BY,
1700 l_conc_rec.CREATION_DATE,
1701 l_conc_rec.LAST_UPDATE_DATE,
1702 l_conc_rec.LAST_UPDATED_BY,
1703 l_conc_rec.LAST_UPDATE_LOGIN,
1704 'Y',
1705 'Y'
1706 );
1707
1708 FETCH c_get_item_concentrations into l_conc_rec;
1709
1710 END LOOP; -- Item concentration recs
1711 CLOSE c_get_item_concentrations;
1712
1713 /* Retrieve recipe id */
1714 OPEN c_get_recipe_id;
1715 FETCH c_get_recipe_id INTO l_recipe_id;
1716 CLOSE c_get_recipe_id;
1717
1718 /* Create the recipe */
1719 GMD_RECIPES_MLS.INSERT_ROW(
1720 X_ROWID => l_rowid,
1721 X_RECIPE_ID => l_recipe_id,
1722 X_OWNER_ID => l_conc_rec.last_updated_by,
1723 X_OWNER_LAB_TYPE => NULL,
1724 X_DELETE_MARK => 0,
1725 X_TEXT_CODE => NULL,
1726 X_RECIPE_NO => l_item_code,
1727 X_RECIPE_VERSION => l_recipe_vers,
1728 X_OWNER_ORGANIZATION_ID => l_owner_org_id,
1729 X_CREATION_ORGANIZATION_ID => l_owner_org_id,
1730 X_FORMULA_ID => l_formula_id,
1731 X_ROUTING_ID => NULL,
1732 X_PROJECT_ID => NULL,
1733 X_RECIPE_STATUS => '700',
1734 X_RECIPE_TYPE => 1,
1735 X_ENHANCED_PI_IND => NULL,
1736 X_CALCULATE_STEP_QUANTITY => 0,
1737 X_PLANNED_PROCESS_LOSS => NULL,
1738 X_CONTIGUOUS_IND => NULL,
1739 X_RECIPE_DESCRIPTION => l_text || ' ' || l_item_code,
1740 X_ATTRIBUTE_CATEGORY => NULL,
1741 X_ATTRIBUTE1 => NULL,
1742 X_ATTRIBUTE2 => NULL,
1743 X_ATTRIBUTE3 => NULL,
1744 X_ATTRIBUTE4 => NULL,
1745 X_ATTRIBUTE5 => NULL,
1746 X_ATTRIBUTE6 => NULL,
1747 X_ATTRIBUTE7 => NULL,
1748 X_ATTRIBUTE8 => NULL,
1749 X_ATTRIBUTE9 => NULL,
1750 X_ATTRIBUTE10 => NULL,
1751 X_ATTRIBUTE11 => NULL,
1752 X_ATTRIBUTE12 => NULL,
1753 X_ATTRIBUTE13 => NULL,
1754 X_ATTRIBUTE14 => NULL,
1755 X_ATTRIBUTE15 => NULL,
1756 X_ATTRIBUTE16 => NULL,
1757 X_ATTRIBUTE17 => NULL,
1758 X_ATTRIBUTE18 => NULL,
1759 X_ATTRIBUTE19 => NULL,
1760 X_ATTRIBUTE20 => NULL,
1761 X_ATTRIBUTE21 => NULL,
1762 X_ATTRIBUTE22 => NULL,
1763 X_ATTRIBUTE23 => NULL,
1764 X_ATTRIBUTE24 => NULL,
1765 X_ATTRIBUTE25 => NULL,
1766 X_ATTRIBUTE26 => NULL,
1767 X_ATTRIBUTE27 => NULL,
1768 X_ATTRIBUTE28 => NULL,
1769 X_ATTRIBUTE29 => NULL,
1770 X_ATTRIBUTE30 => NULL,
1771 X_CREATION_DATE => l_conc_rec.creation_date,
1772 X_CREATED_BY => l_conc_rec.created_by,
1773 X_LAST_UPDATE_DATE => l_conc_rec.last_update_date,
1774 X_LAST_UPDATED_BY => l_conc_rec.last_updated_by,
1775 X_LAST_UPDATE_LOGIN => l_conc_rec.last_update_login,
1776 X_FIXED_PROCESS_LOSS => NULL, /* RLNAGARA 6811759*/
1777 X_FIXED_PROCESS_LOSS_UOM => NULL
1778 );
1779
1780 IF l_rowid IS NULL THEN
1781 RAISE PROC_CALL_ERROR;
1782 END IF;
1783
1784 /* Retrieve recipe validity rule id */
1785 OPEN c_get_recipe_vr_id;
1786 FETCH c_get_recipe_vr_id INTO l_validity_rule_id;
1787 CLOSE c_get_recipe_vr_id;
1788
1789 /* Create validity rule for new recipe */
1790 INSERT INTO gmd_recipe_validity_rules
1791 (
1792 RECIPE_VALIDITY_RULE_ID,
1793 RECIPE_ID,
1794 ORGN_CODE,
1795 RECIPE_USE,
1796 PREFERENCE,
1797 START_DATE,
1798 END_DATE,
1799 MIN_QTY,
1800 MAX_QTY,
1801 STD_QTY,
1802 DETAIL_UOM,
1803 INV_MIN_QTY,
1804 INV_MAX_QTY,
1805 DELETE_MARK,
1806 CREATED_BY,
1807 CREATION_DATE,
1808 LAST_UPDATE_DATE,
1809 LAST_UPDATED_BY,
1810 LAST_UPDATE_LOGIN,
1811 VALIDITY_RULE_STATUS,
1812 LAB_TYPE,
1813 ORGANIZATION_ID,
1814 INVENTORY_ITEM_ID
1815 )
1816 VALUES
1817 (
1818 l_validity_rule_id,
1819 l_recipe_id,
1820 NULL,
1821 3,
1822 1,
1823 l_conc_rec.creation_date,
1824 NULL,
1825 100,
1826 100,
1827 100,
1828 l_uom,
1829 l_inv_qty,
1830 l_inv_qty,
1831 0,
1832 l_conc_rec.CREATED_BY,
1833 l_conc_rec.CREATION_DATE,
1834 l_conc_rec.LAST_UPDATE_DATE,
1835 l_conc_rec.LAST_UPDATED_BY,
1836 l_conc_rec.LAST_UPDATE_LOGIN,
1837 700,
1838 NULL,
1839 l_owner_org_id,
1840 l_prod_item_id
1841 );
1842
1843
1844 UPDATE gr_item_concentrations
1845 SET migration_ind = 1
1846 WHERE item_code = l_item_code;
1847
1848 /* Issue commit if required */
1849 IF p_commit = FND_API.G_TRUE THEN
1850 COMMIT;
1851 END IF;
1852
1853 END IF; -- If concentration record found
1854
1855 EXCEPTION
1856 WHEN PROC_CALL_ERROR THEN
1857 x_failure_count := x_failure_count + 1;
1858
1859 GMA_COMMON_LOGGING.gma_migration_central_log (
1860 p_run_id => P_migration_run_id,
1861 p_log_level => FND_LOG.LEVEL_EXCEPTION,
1862 p_message_token => l_msg_data,
1863 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1864 p_context => 'STANDALONE_FORMULAS',
1865 p_param1 => NULL,
1866 p_param2 => NULL,
1867 p_param3 => NULL,
1868 p_param4 => NULL,
1869 p_param5 => NULL,
1870 p_db_error => SQLERRM,
1871 p_app_short_name => 'GMA');
1872
1873 ROLLBACK to SAVEPOINT Standalone_Formula;
1874
1875 WHEN NO_UOM_CONVERSION THEN
1876 x_failure_count := x_failure_count + 1;
1877
1878 GMA_COMMON_LOGGING.gma_migration_central_log (
1879 p_run_id => P_migration_run_id,
1880 p_log_level => FND_LOG.LEVEL_EXCEPTION,
1881 p_message_token => l_msg_data,
1882 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1883 p_context => 'STANDALONE_FORMULAS',
1884 p_param1 => NULL,
1885 p_param2 => NULL,
1886 p_param3 => NULL,
1887 p_param4 => NULL,
1888 p_param5 => NULL,
1889 p_db_error => SQLERRM,
1890 p_app_short_name => 'GMA');
1891
1892 WHEN OTHERS THEN
1893 x_failure_count := x_failure_count + 1;
1894
1895 GMA_COMMON_LOGGING.gma_migration_central_log (
1896 p_run_id => P_migration_run_id,
1897 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1898 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1899 p_table_name => 'PO_HAZARD_CLASSES',
1900 p_context => 'HAZARD_CLASSES',
1901 p_param1 => NULL,
1902 p_param2 => NULL,
1903 p_param3 => NULL,
1904 p_param4 => NULL,
1905 p_param5 => NULL,
1906 p_db_error => SQLERRM,
1907 p_app_short_name => 'GMA');
1908
1909 ROLLBACK to SAVEPOINT Standalone_Formula;
1910 END; -- Subprogram
1911
1912 FETCH c_get_items into l_item_code;
1913
1914 END LOOP; -- Items with standalone formula source
1915 CLOSE c_get_items;
1916
1917 GMA_COMMON_LOGGING.gma_migration_central_log (
1918 p_run_id => P_migration_run_id,
1919 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1920 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
1921 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1922 p_context => 'STANDALONE_FORMULAS',
1923 p_param1 => l_migration_count,
1924 p_param2 => x_failure_count,
1925 p_param3 => NULL,
1926 p_param4 => NULL,
1927 p_param5 => NULL,
1928 p_db_error => NULL,
1929 p_app_short_name => 'GMA');
1930
1931
1932 EXCEPTION
1933 WHEN ORGN_NOT_MIGRATED THEN
1934 x_failure_count := x_failure_count + l_failure_count;
1935 GMA_COMMON_LOGGING.gma_migration_central_log (
1936 p_run_id => P_migration_run_id,
1937 p_log_level => FND_LOG.LEVEL_ERROR,
1938 p_message_token => 'GMA_ORG_NOT_MIGRATED',
1939 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1940 p_context => 'STANDALONE_FORMULAS',
1941 p_token1 => 'ORGANIZATION',
1942 p_param1 => l_owner_org,
1943 p_app_short_name => 'GR');
1944
1945 WHEN OTHERS THEN
1946 x_failure_count := x_failure_count + 1;
1947
1948 GMA_COMMON_LOGGING.gma_migration_central_log (
1949 p_run_id => P_migration_run_id,
1950 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1951 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1952 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1953 p_context => 'STANDALONE_FORMULAS',
1954 p_param1 => NULL,
1955 p_param2 => NULL,
1956 p_param3 => NULL,
1957 p_param4 => NULL,
1958 p_param5 => NULL,
1959 p_db_error => SQLERRM,
1960 p_app_short_name => 'GMA');
1961
1962 GMA_COMMON_LOGGING.gma_migration_central_log (
1963 p_run_id => P_migration_run_id,
1964 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1965 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
1966 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1967 p_context => 'STANDALONE_FORMULAS',
1968 p_param1 => x_failure_count,
1969 p_param2 => NULL,
1970 p_param3 => NULL,
1971 p_param4 => NULL,
1972 p_param5 => NULL,
1973 p_db_error => NULL,
1974 p_app_short_name => 'GMA');
1975
1976 END migrate_standalone_formulas;
1977
1978
1979
1980 /*===========================================================================
1981 -- PROCEDURE:
1982 -- update_dispatch_history
1983 --
1984 -- DESCRIPTION:
1985 -- This PL/SQL procedure is used to update the organization_id and
1986 -- inventory_item_id columns in the gr_dispatch_history table if the
1987 -- values are NULL.
1988 --
1989 -- PARAMETERS:
1990 -- p_migration_run_id - Migration run id to be used for writing to the message log
1991 -- p_commit - Indicates if commit should be issued after logical unit is migrated
1992 -- x_failure_count - Returns the number of failures that occurred during migration
1993 --
1994 -- SYNOPSIS:
1995 -- update_dispatch_history(
1996 -- p_migration_run_id => migration_id,
1997 -- p_commit => 'Y',
1998 -- x_failure_count => failure_count );
1999 --
2000 -- HISTORY
2001 -- M. Grosser 17-May-2005 Created
2002 --=========================================================================== */
2003 PROCEDURE update_dispatch_history
2004 (
2005 p_migration_run_id IN NUMBER,
2006 p_commit IN VARCHAR2,
2007 x_failure_count OUT NOCOPY NUMBER
2008 ) IS
2009 /* ------------- LOCAL VARIABLES ------------------- */
2010 l_seq NUMBER;
2011 l_mig_status NUMBER;
2012 l_migration_count NUMBER := 0;
2013 l_default_org VARCHAR2(4);
2014 l_default_org_id NUMBER;
2015 l_doc_org VARCHAR2(4);
2016 l_org_id NUMBER;
2017 l_return_status VARCHAR2(2);
2018 l_msg_data VARCHAR2(2000);
2019 l_inv_item_id NUMBER;
2020
2021 /* ------------------ CURSORS ---------------------- */
2022 /* Cursor used retrieve the default organization code */
2023 CURSOR c_get_default_org IS
2024 SELECT profile_option_value
2025 FROM fnd_profile_options a, fnd_profile_option_values b
2026 WHERE b.level_id = 10001 and
2027 a.profile_option_id = b.profile_option_id and
2028 a.profile_option_name = 'GR_ORGN_DEFAULT';
2029
2030 /* Cursor used retrieve organization id */
2031 CURSOR c_get_org_id (v_orgn_code VARCHAR2) IS
2032 SELECT organization_id
2033 FROM sy_orgn_mst_b
2034 WHERE orgn_code = v_orgn_code;
2035
2036 /* Cursor used retrieve the records that don't have an organization id */
2037 CURSOR c_get_disp_rec IS
2038 SELECT dispatch_history_id, item, document_id
2039 FROM gr_dispatch_history
2040 WHERE organization_id is NULL;
2041 l_dispatch_rec c_get_disp_rec%ROWTYPE;
2042
2043 /* Cursor used retrieve the organization_code from the document */
2044 CURSOR c_get_doc_org IS
2045 SELECT doc_attribute5
2046 FROM fnd_documents_tl
2047 WHERE language = userenv('LANG') and
2048 document_id = l_dispatch_rec.document_id;
2049
2050 /* ----------------- EXCEPTIONS -------------------- */
2051 INVALID_ORG_ITEM EXCEPTION;
2052
2053 BEGIN
2054
2055 x_failure_count := 0;
2056
2057 GMA_COMMON_LOGGING.gma_migration_central_log (
2058 p_run_id => P_migration_run_id,
2059 p_log_level => FND_LOG.LEVEL_PROCEDURE,
2060 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
2061 p_table_name => 'GR_DISPATCH_HISTORY',
2062 p_context => 'UPDATE_DISPATCH_HISTORY',
2063 p_param1 => NULL,
2064 p_param2 => NULL,
2065 p_param3 => NULL,
2066 p_param4 => NULL,
2067 p_param5 => NULL,
2068 p_db_error => NULL,
2069 p_app_short_name => 'GMA');
2070
2071 /* Retrieve default org */
2072 OPEN c_get_default_org;
2073 FETCH c_get_default_org into l_default_org;
2074 CLOSE c_get_default_org;
2075
2076 /* Retrieve organization id for default org */
2077 OPEN c_get_org_id(l_default_org);
2078 FETCH c_get_org_id into l_default_org_id;
2079 CLOSE c_get_org_id;
2080
2081 /* Retrieve organization id for default org */
2082 OPEN c_get_disp_rec;
2083 FETCH c_get_disp_rec into l_dispatch_rec;
2084
2085 WHILE c_get_disp_rec%FOUND LOOP
2086
2087 BEGIN
2088
2089 /* Retrieve organization id for default org */
2090 OPEN c_get_doc_org;
2091 FETCH c_get_doc_org into l_doc_org;
2092 CLOSE c_get_doc_org;
2093
2094 /* Retrieve organization id for default org */
2095 OPEN c_get_org_id(l_doc_org);
2096 FETCH c_get_org_id into l_org_id;
2097
2098 IF c_get_org_id%NOTFOUND THEN
2099 l_org_id := l_default_org_id;
2100 END IF;
2101
2102 CLOSE c_get_org_id;
2103
2104 l_inv_item_id := get_inventory_item_id
2105 (
2106 p_organization_id => l_org_id,
2107 p_item_code => l_dispatch_rec.item,
2108 x_return_status => l_return_status,
2109 x_msg_data => l_msg_data
2110 );
2111
2112 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2113 RAISE INVALID_ORG_ITEM;
2114 END IF;
2115
2116 UPDATE gr_dispatch_history
2117 SET organization_id = l_org_id,
2118 inventory_item_id = l_inv_item_id
2119 WHERE dispatch_history_id = l_dispatch_rec.dispatch_history_id;
2120
2121 /* Issue commit if required */
2122 IF p_commit = FND_API.G_TRUE THEN
2123 COMMIT;
2124 END IF;
2125
2126 l_migration_count := l_migration_count + 1;
2127
2128 EXCEPTION
2129
2130 WHEN INVALID_ORG_ITEM THEN
2131 x_failure_count := x_failure_count + 1;
2132
2133 GMA_COMMON_LOGGING.gma_migration_central_log (
2134 p_run_id => P_migration_run_id,
2135 p_log_level => FND_LOG.LEVEL_EXCEPTION,
2136 p_message_token => 'GR_INVALID_ORG_ITEM',
2137 p_table_name => 'GR_DISPATCH_HISTORY',
2138 p_context => 'UPDATE_DISPATCH_HISTORY',
2139 p_param1 => l_org_id,
2140 p_param2 => l_dispatch_rec.item,
2141 p_param3 => NULL,
2142 p_param4 => NULL,
2143 p_param5 => NULL,
2144 p_db_error => SQLERRM,
2145 p_app_short_name => 'GR');
2146
2147 WHEN OTHERS THEN
2148 x_failure_count := x_failure_count + 1;
2149
2150 GMA_COMMON_LOGGING.gma_migration_central_log (
2151 p_run_id => P_migration_run_id,
2152 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
2153 p_message_token => 'GMA_MIGRATION_DB_ERROR',
2154 p_table_name => 'GR_DISPATCH_HISTORY',
2155 p_context => 'UPDATE_DISPATCH_HISTORY',
2156 p_param1 => NULL,
2157 p_param2 => NULL,
2158 p_param3 => NULL,
2159 p_param4 => NULL,
2160 p_param5 => NULL,
2161 p_db_error => SQLERRM,
2162 p_app_short_name => 'GMA');
2163
2164 END;
2165
2166 FETCH c_get_disp_rec into l_dispatch_rec;
2167
2168 END LOOP;
2169
2170 CLOSE c_get_disp_rec;
2171
2172 GMA_COMMON_LOGGING.gma_migration_central_log (
2173 p_run_id => P_migration_run_id,
2174 p_log_level => FND_LOG.LEVEL_PROCEDURE,
2175 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
2176 p_table_name => 'GR_DISPATCH_HISTORY',
2177 p_context => 'UPDATE_DISPATCH_HISTORY',
2178 p_param1 => l_migration_count,
2179 p_param2 => x_failure_count,
2180 p_param3 => NULL,
2181 p_param4 => NULL,
2182 p_param5 => NULL,
2183 p_db_error => NULL,
2184 p_app_short_name => 'GMA');
2185
2186 EXCEPTION
2187 WHEN OTHERS THEN
2188 x_failure_count := x_failure_count + 1;
2189
2190 GMA_COMMON_LOGGING.gma_migration_central_log (
2191 p_run_id => P_migration_run_id,
2192 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
2193 p_message_token => 'GMA_MIGRATION_DB_ERROR',
2194 p_table_name => 'GR_DISPATCH_HISTORY',
2195 p_context => 'UPDATE_DISPATCH_HISTORY',
2196 p_param1 => NULL,
2197 p_param2 => NULL,
2198 p_param3 => NULL,
2199 p_param4 => NULL,
2200 p_param5 => NULL,
2201 p_db_error => SQLERRM,
2202 p_app_short_name => 'GMA');
2203
2204 GMA_COMMON_LOGGING.gma_migration_central_log (
2205 p_run_id => P_migration_run_id,
2206 p_log_level => FND_LOG.LEVEL_PROCEDURE,
2207 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
2208 p_table_name => 'GR_DISPATCH_HISTORY',
2209 p_context => 'UPDATE_DISPATCH_HISTORY',
2210 p_param1 => x_failure_count,
2211 p_param2 => NULL,
2212 p_param3 => NULL,
2213 p_param4 => NULL,
2214 p_param5 => NULL,
2215 p_db_error => NULL,
2216 p_app_short_name => 'GMA');
2217
2218 END update_dispatch_history;
2219
2220
2221
2222 END GR_MIGRATE_TO_12;