[Home] [Help]
PACKAGE BODY: APPS.GR_MIGRATE_TO_12
Source
1 PACKAGE BODY GR_MIGRATE_TO_12 AS
2 /* $Header: GRMIG12B.pls 120.7.12010000.3 2008/11/21 15:23:53 plowe 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 --=========================================================================== */
536 PROCEDURE migrate_regulatory_items
537 (
538 p_migration_run_id IN NUMBER,
539 p_commit IN VARCHAR2,
540 x_failure_count OUT NOCOPY NUMBER
541 ) IS
542
543 /* ------------- LOCAL VARIABLES ------------------- */
544 l_temp NUMBER;
545 l_rowid VARCHAR2(2000);
546 l_inv_category_id NUMBER;
547 l_reg_category_id NUMBER;
548 l_inventory_item_id NUMBER;
549 l_un_number_id NUMBER;
550 l_hazard_class_id NUMBER;
551 l_mig_status NUMBER;
552 l_migration_count NUMBER := 0;
553 l_exists_count NUMBER := 0;
554 l_return_status VARCHAR2(2);
555 l_msg_data VARCHAR2(2000);
556 l_hazard_description VARCHAR2(240);
557 l_failure_count NUMBER := 0;
558 l_doc_category_id NUMBER;
559 l_attached_doc_id NUMBER;
560 l_media_id NUMBER;
561 l_related_item_id NUMBER;
562
563 /* ------------------ CURSORS ---------------------- */
564 /* Cursor used to retrieve record from migration table */
565 CURSOR c_get_mig_rec IS
566 SELECT item_code, organization_id
567 FROM gr_item_general_mig
568 WHERE migration_ind is NULL;
569 l_mig_rec c_get_mig_rec%ROWTYPE;
570
571 /* Cursor used to retrieve document category ids */
572 CURSOR c_get_category_id(v_category_name VARCHAR2) IS
573 SELECT category_id
574 FROM fnd_document_categories
575 WHERE name = v_category_name;
576
577 /* Cursor used to check if item for organization is already in the table */
578 CURSOR c_check_exists IS
579 SELECT inventory_item_id
580 FROM mtl_system_items_b
581 WHERE segment1 = l_mig_rec.item_code and
582 organization_id = l_mig_rec.organization_id;
583
584 /* Cursor used to retrieve regulatory item record */
585 CURSOR c_get_reg_item IS
586 SELECT *
587 FROM gr_item_general
588 WHERE item_code = l_mig_rec.item_code;
589 l_reg_item_rec c_get_reg_item%ROWTYPE;
590
591 /* Cursor used to retrieve the Regulatory item description*/
592 CURSOR c_get_translated IS
593 SELECT *
594 FROM gr_multilingual_name_tl
595 WHERE language in (SELECT language_code
596 FROM fnd_languages
597 WHERE language_code <> userenv('LANG')
598 AND installed_flag in ('I','B')) and
599 label_code = '11007' and
600 item_code = l_mig_rec.item_code;
601 l_translated_rec c_get_translated%ROWTYPE;
602
603 /* Cursor used to retrieve related inventory items */
604 CURSOR c_get_related IS
605 SELECT *
606 FROM gr_generic_items_b
607 WHERE item_code = l_mig_rec.item_code;
608 l_related_rec c_get_related%ROWTYPE;
609
610 /* Cursor used to retrieve OPM item id */
611 CURSOR c_get_opm_item_id IS
612 SELECT item_id
613 FROM ic_item_mst_b
614 WHERE item_no = l_related_rec.item_no;
615 l_opm_item_id NUMBER := NULL;
616
617 /* Cursor used to retrieve document attached to Regulatory item */
618 CURSOR c_get_attachments IS
619 SELECT *
620 FROM fnd_attached_documents
621 WHERE entity_name = 'GR_ITEM_GENERAL' and
622 pk1_value = l_mig_rec.item_code;
623 l_attachment_rec c_get_attachments%ROWTYPE;
624
625
626 /* ----------------- EXCEPTIONS -------------------- */
627 INVALID_REG_ITEM EXCEPTION;
628 ITEM_CREATE_ERROR EXCEPTION;
629 NO_CAS_NUMBER EXCEPTION;
630 PROC_CALL_ERROR EXCEPTION;
631
632 BEGIN
633
634 x_failure_count := 0;
635
636 GMA_COMMON_LOGGING.gma_migration_central_log (
637 p_run_id => P_migration_run_id,
638 p_log_level => FND_LOG.LEVEL_PROCEDURE,
639 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
640 p_table_name => 'GR_ITEM_GENERAL',
641 p_context => 'REGULATORY_ITEMS',
642 p_param1 => NULL,
643 p_param2 => NULL,
644 p_param3 => NULL,
645 p_param4 => NULL,
646 p_param5 => NULL,
647 p_db_error => NULL,
648 p_app_short_name => 'GMA');
649
650
651 /* Select item/organization combinations that have not yet been migrated */
652 OPEN c_get_mig_rec;
653 FETCH c_get_mig_rec into l_mig_rec;
654
655 IF c_get_mig_rec%NOTFOUND THEN
656
657 GMA_COMMON_LOGGING.gma_migration_central_log (
658 p_run_id => P_migration_run_id,
659 p_log_level => FND_LOG.LEVEL_PROCEDURE,
660 p_message_token => 'GR_NO_REGITEMS_TO_MIG',
661 p_table_name => 'GR_ITEM_GENERAL',
662 p_context => 'REGULATORY_ITEMS',
663 p_param1 => NULL,
664 p_param2 => NULL,
665 p_param3 => NULL,
666 p_param4 => NULL,
667 p_param5 => NULL,
668 p_db_error => NULL,
669 p_app_short_name => 'GR');
670
671 ELSE
672
673 /* Retrieve category_id for document category MSDS_INV_ITEM */
674 OPEN c_get_category_id('MSDS_INV_ITEM');
675 FETCH c_get_category_id INTO l_inv_category_id;
676 CLOSE c_get_category_id;
677
678 /* Retrieve category_id for document category MSDS_REG_ITEM */
679 OPEN c_get_category_id('MSDS_REG_ITEM');
680 FETCH c_get_category_id INTO l_reg_category_id;
681 CLOSE c_get_category_id;
682
683 /* While there are results that have not been migrated */
684 WHILE c_get_mig_rec%FOUND LOOP
685
686 BEGIN
687
688 SAVEPOINT Org_Item;
689
690 /* Retrieve regulatory item info */
691 --Bug# 5293938 - close cursor if open
692 IF c_get_reg_item%ISOPEN THEN
693 CLOSE c_get_reg_item;
694 END IF;
695 OPEN c_get_reg_item;
696 FETCH c_get_reg_item INTO l_reg_item_rec;
697
698 IF c_get_reg_item%NOTFOUND THEN
699 CLOSE c_get_reg_item;
700 RAISE INVALID_REG_ITEM;
701 END IF;
702
703 CLOSE c_get_reg_item;
704
705 /* Make sure that there is a CAS number */
706 IF l_reg_item_rec.primary_cas_number is NULL THEN
707 RAISE NO_CAS_NUMBER;
708 END IF;
709 --Bug# 5293938 - close cursor if open
710 IF c_check_exists%ISOPEN THEN
711 CLOSE c_check_exists;
712 END IF;
713
714 OPEN c_check_exists;
715 FETCH c_check_exists INTO l_inventory_item_id;
716
717 IF c_check_exists%NOTFOUND THEN
718
719 INV_OPM_ITEM_MIGRATION.get_ODM_regulatory_item
720 ( p_migration_run_id => p_migration_run_id,
721 p_item_code => l_mig_rec.item_code,
722 p_organization_id => l_mig_rec.organization_id,
723 p_mode => NULL,
724 p_commit => 'T',
725 x_inventory_item_id => l_inventory_item_id,
726 x_failure_count => l_failure_count);
727
728 IF l_failure_count > 0 THEN
729 x_failure_count := x_failure_count + l_failure_count;
730 RAISE ITEM_CREATE_ERROR;
731 END IF;
732
733 ELSE
734
735 GMA_COMMON_LOGGING.gma_migration_central_log (
736 p_run_id => P_migration_run_id,
737 p_log_level => FND_LOG.LEVEL_PROCEDURE,
738 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
739 p_table_name => 'GR_ITEM_GENERAL',
740 p_context => 'REGULATORY_ITEMS',
741 p_param1 => NULL,
742 p_param2 => NULL,
743 p_param3 => NULL,
744 p_param4 => NULL,
745 p_param5 => NULL,
746 p_db_error => NULL,
747 p_app_short_name => 'GMA');
748
749 END IF; -- If Item already exists
750 CLOSE c_check_exists;
751
752 /* Retrieve items UN Number */
753 l_un_number_id := get_un_number_id
754 (
755 p_item_code => l_mig_rec.item_code,
756 x_return_status => l_return_status,
757 x_msg_data => l_msg_data
758 );
759
760 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
761 RAISE PROC_CALL_ERROR;
762 END IF;
763
764 /* Retrieve items UN Class */
765 l_hazard_class_id := get_hazard_class_id
766 (
767 p_item_code => l_mig_rec.item_code,
768 x_return_status => l_return_status,
769 x_msg_data => l_msg_data
770 );
771
772 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
773 RAISE PROC_CALL_ERROR;
774 END IF;
775
776 UPDATE mtl_system_items_b
777 SET hazardous_material_flag = 'Y',
778 cas_number = l_reg_item_rec.primary_cas_number,
779 hazard_class_id = l_hazard_class_id,
780 un_number_id = l_un_number_id
781 WHERE organization_id = l_mig_rec.organization_id and
782 inventory_item_id = l_inventory_item_id;
783
784
785 INSERT INTO gr_item_explosion_properties
786 (
787 organization_id,
788 inventory_item_id,
789 actual_hazard,
790 ingredient_flag,
791 explode_ingredient_flag,
792 created_by,
793 creation_date,
794 last_updated_by,
795 last_update_date,
796 last_update_login
797 )
798 VALUES
799 (
800 l_mig_rec.organization_id,
801 l_inventory_item_id,
802 l_reg_item_rec.ACTUAL_HAZARD,
803 l_reg_item_rec.INGREDIENT_FLAG,
804 l_reg_item_rec.EXPLODE_INGREDIENT_FLAG,
805 l_reg_item_rec.CREATED_BY,
806 l_reg_item_rec.CREATION_DATE,
807 l_reg_item_rec.LAST_UPDATED_BY,
808 l_reg_item_rec.LAST_UPDATE_DATE,
809 l_reg_item_rec.LAST_UPDATE_LOGIN
810 );
811
812 OPEN c_get_translated;
813 FETCH c_get_translated INTO l_translated_rec;
814
815 WHILE c_get_translated%FOUND LOOP
816
817 /* Update the descriptions with the values from Regulatory */
818 UPDATE mtl_system_items_tl
819 SET description = l_translated_rec.name_description,
820 source_lang = l_translated_rec.source_lang,
821 creation_date = l_translated_rec.creation_date,
822 created_by = l_translated_rec.created_by,
823 last_update_date = l_translated_rec.last_update_date,
824 last_updated_by = l_translated_rec.last_updated_by,
825 last_update_login = l_translated_rec.last_update_login
826 WHERE language = l_translated_rec.language and
827 organization_id = l_mig_rec.organization_id and
828 inventory_item_id = l_inventory_item_id;
829
830 FETCH c_get_translated INTO l_translated_rec;
831
832 END LOOP; -- tranlated descriptions
833 CLOSE c_get_translated;
834
835
836 /* Copy all of the properties to the org/item combination */
837 INSERT INTO gr_inv_item_properties
838 (
839 organization_id,
840 inventory_item_id,
841 sequence_number,
842 property_id,
843 label_code,
844 number_value,
845 alpha_value,
846 date_value,
847 created_by,
848 creation_date,
849 last_updated_by,
850 last_update_date,
851 last_update_login
852 )
853 SELECT
854 l_mig_rec.organization_id,
855 l_inventory_item_id,
856 sequence_number,
857 property_id,
858 label_code,
859 number_value,
860 alpha_value,
861 date_value,
862 created_by,
863 creation_date,
864 last_updated_by,
865 last_update_date,
866 last_update_login
867 FROM gr_item_properties
868 WHERE label_code <> '14001' and
869 label_code <> '14002' and
870 item_code = l_reg_item_rec.item_code;
871
872
873 --Bug# 5293938 Close cursor if open
874 IF c_get_attachments%ISOPEN THEN
875 CLOSE c_get_attachments;
876 END IF;
877 OPEN c_get_attachments;
878 FETCH c_get_attachments INTO l_attachment_rec;
879
880 WHILE c_get_attachments%FOUND LOOP
881
882 /* Retrieve category_id for document category */
883 OPEN c_get_category_id(l_attachment_rec.attribute_category);
884 FETCH c_get_category_id INTO l_doc_category_id;
885 CLOSE c_get_category_id;
886
887 IF l_doc_category_id = l_reg_category_id THEN
888
889 UPDATE fnd_documents
890 SET category_id = l_inv_category_id
891 WHERE document_id = l_attachment_rec.document_id;
892
893 UPDATE fnd_documents_tl
894 SET doc_attribute_category = 'MSDS_INV_ITEM'
895 WHERE document_id = l_attachment_rec.document_id;
896
897 END IF;
898
899 --Bug# 5293938 Attached Document Id has to be populated from the sequence.
900 select fnd_attached_documents_s.nextval
901 into l_attached_doc_id
902 from sys.dual;
903
904 FND_ATTACHED_DOCUMENTS_PKG.Insert_Row(
905 X_Rowid => l_rowid,
906 X_attached_document_id => l_attached_doc_id,
907 X_document_id => l_attachment_rec.document_id,
908 X_creation_date => l_attachment_rec.creation_date,
909 X_created_by => l_attachment_rec.created_by,
910 X_last_update_date => l_attachment_rec.last_update_date,
911 X_last_updated_by => l_attachment_rec.last_updated_by,
912 X_last_update_login => l_attachment_rec.last_update_login,
913 X_seq_num => l_attachment_rec.seq_num,
914 X_entity_name => 'MTL_SYSTEM_ITEMS',
915 X_column1 => NULL,
916 X_pk1_value => l_mig_rec.organization_id,
917 X_pk2_value => l_inventory_item_id,
918 X_pk3_value => NULL,
919 X_pk4_value => NULL,
920 X_pk5_value => NULL,
921 X_automatically_added_flag => l_attachment_rec.automatically_added_flag,
922 X_request_id => l_attachment_rec.request_id,
923 X_datatype_id => NULL,
924 X_category_id => l_attachment_rec.category_id,
925 X_security_type => NULL,
926 X_security_id => NULL,
927 X_publish_flag => NULL,
928 X_storage_type => NULL,
929 X_usage_type => NULL,
930 X_language => NULL,
931 X_description => NULL,
932 X_file_name => NULL,
933 X_media_id => l_media_id,
934 X_attribute_category => l_attachment_rec.attribute_category,
935 X_attribute1 => l_attachment_rec.attribute1,
936 X_attribute2 => l_attachment_rec.attribute2,
937 X_attribute3 => l_attachment_rec.attribute3,
938 X_attribute4 => l_attachment_rec.attribute4,
939 X_attribute5 => l_attachment_rec.attribute5,
940 X_attribute6 => l_attachment_rec.attribute6,
941 X_attribute7 => l_attachment_rec.attribute7,
942 X_attribute8 => l_attachment_rec.attribute8,
943 X_attribute9 => l_attachment_rec.attribute9,
944 X_attribute10 => l_attachment_rec.attribute10,
945 X_attribute11 => l_attachment_rec.attribute11,
946 X_attribute12 => l_attachment_rec.attribute12,
947 X_attribute13 => l_attachment_rec.attribute13,
948 X_attribute14 => l_attachment_rec.attribute14,
949 X_attribute15 => l_attachment_rec.attribute15,
950 X_create_doc => 'N');
951
952 FETCH c_get_attachments INTO l_attachment_rec;
953
954 END LOOP; /* Item attachments */
955 CLOSE c_get_attachments;
956
957 --Bug# 5293938 Close cursor if open
958 IF c_get_related%ISOPEN THEN
959 CLOSE c_get_related;
960 END IF;
961 OPEN c_get_related;
962 FETCH c_get_related INTO l_related_rec;
963
964 WHILE c_get_related%FOUND LOOP
965 --Bug# 5293938 get opm item id of related item
966 OPEN c_get_opm_item_id;
967 FETCH c_get_opm_item_id into l_opm_item_id;
968 IF c_get_opm_item_id%NOTFOUND THEN
969 l_msg_data := 'Related item '||l_related_rec.item_no||' not found in ic_item_mst';
970 CLOSE c_get_opm_item_id;
971 RAISE PROC_CALL_ERROR;
972 END IF;
973 CLOSE c_get_opm_item_id;
974
975 INV_OPM_ITEM_MIGRATION.get_ODM_item
976 ( p_migration_run_id => p_migration_run_id,
977 p_item_id => l_opm_item_id, --Bug# 5293938
978 p_organization_id => l_mig_rec.organization_id,
979 p_mode => NULL,
980 p_commit => 'T',
981 x_inventory_item_id => l_related_item_id,
982 x_failure_count => l_failure_count);
983
984 MTL_RELATED_ITEMS_PKG.Insert_Row (
985 X_Rowid => l_rowid,
986 X_Inventory_Item_Id => l_inventory_item_id,
987 X_Organization_Id => l_mig_rec.organization_id,
988 X_Related_Item_Id => l_related_item_id,
989 X_Relationship_Type_Id => 19,
990 X_Reciprocal_Flag => 'N',
991 X_Planning_Enabled_Flag => 'N',
992 X_Start_Date => l_related_rec.creation_date,
993 X_End_Date => NULL,
994 X_Attr_Context => NULL,
995 X_Attr_Char1 => NULL,
996 X_Attr_Char2 => NULL,
997 X_Attr_Char3 => NULL,
998 X_Attr_Char4 => NULL,
999 X_Attr_Char5 => NULL,
1000 X_Attr_Char6 => NULL,
1001 X_Attr_Char7 => NULL,
1002 X_Attr_Char8 => NULL,
1003 X_Attr_Char9 => NULL,
1004 X_Attr_Char10 => NULL,
1005 X_Attr_Num1 => NULL,
1006 X_Attr_Num2 => NULL,
1007 X_Attr_Num3 => NULL,
1008 X_Attr_Num4 => NULL,
1009 X_Attr_Num5 => NULL,
1010 X_Attr_Num6 => NULL,
1011 X_Attr_Num7 => NULL,
1012 X_Attr_Num8 => NULL,
1013 X_Attr_Num9 => NULL,
1014 X_Attr_Num10 => NULL,
1015 X_Attr_Date1 => NULL,
1016 X_Attr_Date2 => NULL,
1017 X_Attr_Date3 => NULL,
1018 X_Attr_Date4 => NULL,
1019 X_Attr_Date5 => NULL,
1020 X_Attr_Date6 => NULL,
1021 X_Attr_Date7 => NULL,
1022 X_Attr_Date8 => NULL,
1023 X_Attr_Date9 => NULL,
1024 X_Attr_Date10 => NULL,
1025 X_Last_Update_Date => l_related_rec.last_update_date,
1026 X_Last_Updated_By => l_related_rec.last_updated_by,
1027 X_Creation_Date => l_related_rec.creation_date,
1028 X_Created_By => l_related_rec.created_by,
1029 X_Last_Update_Login => l_related_rec.last_update_login,
1030 X_Object_Version_Number => NULL
1031 );
1032
1033 FETCH c_get_related INTO l_related_rec;
1034 END LOOP; -- Related Items
1035 CLOSE c_get_related;
1036
1037 UPDATE gr_item_general_mig
1038 SET migration_ind = 1,
1039 inventory_item_id = l_inventory_item_id
1040 WHERE item_code = l_mig_rec.item_code and
1041 organization_id = l_mig_rec. organization_id;
1042
1043 /* Issue commit if required */
1044 IF p_commit = FND_API.G_TRUE THEN
1045 COMMIT;
1046 END IF;
1047
1048 /* Increment appropriate counter */
1049 IF l_mig_status = 1 THEN
1050 l_migration_count := l_migration_count + 1;
1051 ELSE
1052 l_exists_count := l_exists_count + 1;
1053 END IF;
1054
1055 EXCEPTION
1056 WHEN INVALID_REG_ITEM THEN
1057 x_failure_count := x_failure_count + 1;
1058 GMA_COMMON_LOGGING.gma_migration_central_log (
1059 p_run_id => P_migration_run_id,
1060 p_log_level => FND_LOG.LEVEL_EXCEPTION,
1061 p_message_token => 'GR_INVALID_REG_ITEM',
1062 p_table_name => 'GR_ITEM_GENERAL',
1063 p_context => 'REGULATORY_ITEMS',
1064 p_param1 => l_mig_rec.item_code,
1065 p_param2 => NULL,
1066 p_param3 => NULL,
1067 p_param4 => NULL,
1068 p_param5 => NULL,
1069 p_db_error => SQLERRM,
1070 p_app_short_name => 'GR');
1071
1072 ROLLBACK to SAVEPOINT Org_Item;
1073 --Bug# 5293938 Add this exception handler
1074 WHEN NO_CAS_NUMBER THEN
1075 x_failure_count := x_failure_count + 1;
1076 GMA_COMMON_LOGGING.gma_migration_central_log (
1077 p_run_id => P_migration_run_id,
1078 p_log_level => FND_LOG.LEVEL_EXCEPTION,
1079 p_message_token => 'GR_NO_CAS_NUMBER',
1080 p_table_name => 'GR_ITEM_GENERAL',
1081 p_context => 'REGULATORY_ITEMS',
1082 p_param1 => l_mig_rec.item_code,
1083 p_param2 => NULL,
1084 p_param3 => NULL,
1085 p_param4 => NULL,
1086 p_param5 => NULL,
1087 p_db_error => SQLERRM,
1088 p_app_short_name => 'GR');
1089
1090 ROLLBACK to SAVEPOINT Org_Item;
1091
1092
1093 WHEN ITEM_CREATE_ERROR THEN
1094 x_failure_count := x_failure_count + 1;
1095 GMA_COMMON_LOGGING.gma_migration_central_log (
1096 p_run_id => P_migration_run_id,
1097 p_log_level => FND_LOG.LEVEL_EXCEPTION,
1098 p_message_token => 'GR_INV_ITEM_ERROR',
1099 p_table_name => 'GR_ITEM_GENERAL',
1100 p_context => 'REGULATORY_ITEMS',
1101 p_param1 => l_mig_rec.item_code,
1102 p_param2 => NULL,
1103 p_param3 => NULL,
1104 p_param4 => NULL,
1105 p_param5 => NULL,
1106 p_db_error => SQLERRM,
1107 p_app_short_name => 'GR');
1108
1109 ROLLBACK to SAVEPOINT Org_Item;
1110
1111 WHEN PROC_CALL_ERROR THEN
1112 x_failure_count := x_failure_count + 1;
1113 GMA_COMMON_LOGGING.gma_migration_central_log (
1114 p_run_id => P_migration_run_id,
1115 p_log_level => FND_LOG.LEVEL_EXCEPTION,
1116 p_message_token => l_msg_data,
1117 p_table_name => 'GR_ITEM_GENERAL',
1118 p_context => 'REGULATORY_ITEMS',
1119 p_param1 => NULL,
1120 p_param2 => NULL,
1121 p_param3 => NULL,
1122 p_param4 => NULL,
1123 p_param5 => NULL,
1124 p_db_error => SQLERRM,
1125 p_app_short_name => 'GMA');
1126
1127 ROLLBACK to SAVEPOINT Org_Item;
1128
1129 WHEN OTHERS THEN
1130 x_failure_count := x_failure_count + 1;
1131 GMA_COMMON_LOGGING.gma_migration_central_log (
1132 p_run_id => P_migration_run_id,
1133 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1134 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1135 p_table_name => 'GR_ITEM_GENERAL',
1136 p_context => 'REGULATORY_ITEMS',
1137 p_param1 => NULL,
1138 p_param2 => NULL,
1139 p_param3 => NULL,
1140 p_param4 => NULL,
1141 p_param5 => NULL,
1142 p_db_error => SQLERRM,
1143 p_app_short_name => 'GMA');
1144
1145 ROLLBACK to SAVEPOINT Org_Item;
1146
1147 END; -- Subprogram
1148
1149 FETCH c_get_mig_rec into l_mig_rec;
1150
1151 END LOOP; -- Records in migration table
1152 CLOSE c_get_mig_rec;
1153
1154 END IF; -- Unmigrated records found
1155
1156 GMA_COMMON_LOGGING.gma_migration_central_log (
1157 p_run_id => P_migration_run_id,
1158 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1159 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
1160 p_table_name => 'GR_ITEM_GENERAL',
1161 p_context => 'REGULATORY_ITEMS',
1162 p_param1 => l_migration_count,
1163 p_param2 => x_failure_count,
1164 p_param3 => NULL,
1165 p_param4 => NULL,
1166 p_param5 => NULL,
1167 p_db_error => NULL,
1168 p_app_short_name => 'GMA');
1169
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 x_failure_count := x_failure_count + 1;
1173 GMA_COMMON_LOGGING.gma_migration_central_log (
1174 p_run_id => P_migration_run_id,
1175 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1176 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1177 p_table_name => 'GR_ITEM_GENERAL',
1178 p_context => 'REGULATORY_ITEMS',
1179 p_param1 => NULL,
1180 p_param2 => NULL,
1181 p_param3 => NULL,
1182 p_param4 => NULL,
1183 p_param5 => NULL,
1184 p_db_error => SQLERRM,
1185 p_app_short_name => 'GMA');
1186
1187 GMA_COMMON_LOGGING.gma_migration_central_log (
1188 p_run_id => P_migration_run_id,
1189 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1190 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
1191 p_table_name => 'GR_ITEM_GENERAL',
1192 p_context => 'REGULATORY_ITEMS',
1193 p_param1 => x_failure_count,
1194 p_param2 => NULL,
1195 p_param3 => NULL,
1196 p_param4 => NULL,
1197 p_param5 => NULL,
1198 p_db_error => NULL,
1199 p_app_short_name => 'GMA');
1200
1201 END migrate_regulatory_items;
1202
1203
1204 /*===========================================================================
1205 -- PROCEDURE:
1206 -- migrate_standalone_formulas
1207 --
1208 -- DESCRIPTION:
1209 -- This PL/SQL procedure is used to migrate standalone Regulatory formulas
1210 -- to the formula, recipe and validity rules table.
1211 --
1212 -- PARAMETERS:
1213 -- p_migration_run_id - Migration run id to be used for writing to the message log
1214 -- p_commit - Indicates if commit should be issued after logical unit is migrated
1215 -- x_failure_count - Returns the number of failures that occurred during migration
1216 --
1217 -- SYNOPSIS:
1218 -- migrate_standalone_formulas(
1219 -- p_migration_run_id => migration_id,
1220 -- p_commit => 'Y',
1221 -- x_failure_count => failure_count );
1222 --
1223 -- HISTORY
1224 -- M. Grosser 17-May-2005 Created
1225 --=========================================================================== */
1226 PROCEDURE migrate_standalone_formulas
1227 (
1228 p_migration_run_id IN NUMBER,
1229 p_commit IN VARCHAR2,
1230 x_failure_count OUT NOCOPY NUMBER
1231 ) IS
1232
1233 /* ------------- LOCAL VARIABLES ------------------- */
1234 l_temp NUMBER;
1235 l_rowid VARCHAR2(2000);
1236 l_migration_count NUMBER := 0;
1237 l_failure_count NUMBER := 0;
1238 l_exists_count NUMBER := 0;
1239 l_item_code VARCHAR2(32);
1240 l_uom_type sy_uoms_typ.um_type%TYPE;
1241 l_uom VARCHAR2(4);
1242 l_owner_org sy_orgn_mst.orgn_code%TYPE;
1243 l_owner_org_id sy_orgn_mst.organization_id%TYPE;
1244 l_inventory_item_id NUMBER;
1245 l_text VARCHAR2(80);
1246 l_line_no NUMBER;
1247 l_formula_id NUMBER;
1248 l_formulaline_id NUMBER;
1249 l_recipe_id NUMBER;
1250 l_validity_rule_id NUMBER;
1251 l_prod_primary_uom VARCHAR2(4);
1252 l_ing_primary_uom VARCHAR2(4);
1253 l_formula_vers NUMBER;
1254 l_recipe_vers NUMBER;
1255 l_prod_item_id NUMBER;
1256 l_ing_item_id NUMBER;
1257 l_mig_status NUMBER;
1258 l_return_status VARCHAR2(2);
1259 l_msg_data VARCHAR2(2000);
1260 l_recipe_type NUMBER;
1261 l_inv_qty NUMBER;
1262
1263 /* ------------------ CURSORS ---------------------- */
1264 /* Cursor used to retrieve items with formula type of standalone that have formulas saved */
1265 CURSOR c_get_items IS
1266 SELECT a.item_code
1267 FROM gr_item_general a
1268 WHERE EXISTS (SELECT 1
1269 FROM gr_item_concentrations b
1270 WHERE b.item_code = a.item_code) and
1271 a.formula_source_indicator = 'S';
1272
1273 /* Cursor used to retrieve profile value at site level */
1274 CURSOR c_get_profile_value(v_profile_name VARCHAR2) IS
1275 SELECT profile_option_value
1276 FROM fnd_profile_options a, fnd_profile_option_values b
1277 WHERE b.level_id = 10001 and
1278 a.profile_option_id = b.profile_option_id and
1279 a.profile_option_name = v_profile_name;
1280
1281 /* Cursor used to retrieve the std uom for FM_YIELD_TYPE class */
1282 CURSOR c_get_uom (v_um_type VARCHAR2) IS
1283 SELECT std_um
1284 FROM sy_uoms_typ
1285 WHERE um_type = v_um_type;
1286
1287 /* Cursor used to retrieve concentration records for item */
1288 CURSOR c_get_item_concentrations IS
1289 SELECT *
1290 FROM gr_item_concentrations
1291 WHERE migration_ind is NULL and
1292 item_code = l_item_code;
1293 l_conc_rec c_get_item_concentrations%ROWTYPE;
1294
1295 /* Cursor used to retrieve next formula version */
1296 CURSOR c_get_formula_vers IS
1297 SELECT MAX(formula_vers) + 1
1298 FROM fm_form_mst_b
1299 WHERE formula_no = l_item_code;
1300
1301 /* Cursor used to retrieve next recipe version */
1302 CURSOR c_get_recipe_vers IS
1303 SELECT MAX(recipe_version) + 1
1304 FROM gmd_recipes_b
1305 WHERE recipe_no = l_item_code;
1306
1307 /* Cursor used to retrieve organization_id */
1308 CURSOR c_get_organization_id (v_org_code VARCHAR2) IS
1309 SELECT organization_id
1310 FROM sy_orgn_mst
1311 WHERE orgn_code = v_org_code;
1312
1313 /* Cursor used to retrieve items primary_uom */
1314 CURSOR c_get_primary_uom (v_organization_id NUMBER, v_inventory_item_id NUMBER) IS
1315 SELECT primary_uom_code
1316 FROM mtl_system_items_b
1317 WHERE organization_id = v_organization_id and
1318 inventory_item_id = v_inventory_item_id;
1319
1320 /* Cursor used to retrieve next formula_id value */
1321 CURSOR c_get_formula_id IS
1322 SELECT gem5_formula_id_s.NEXTVAL
1323 FROM SYS.DUAL;
1324
1325 /* Cursor used to retrieve next formulaline_id value */
1326 CURSOR c_get_formulaline_id IS
1327 SELECT gem5_formulaline_id_s.NEXTVAL
1328 FROM SYS.DUAL;
1329
1330 /* Cursor used to retrieve next recipe_id value */
1331 CURSOR c_get_recipe_id IS
1332 SELECT gmd_recipe_id_s.NEXTVAL
1333 FROM SYS.DUAL;
1334
1335 /* Cursor used to retrieve next recipe_vr_id value */
1336 CURSOR c_get_recipe_vr_id IS
1337 SELECT gmd_recipe_validity_id_s.NEXTVAL
1338 FROM SYS.DUAL;
1339
1340 /* ------------------- EXCEPTIONS -------------------- */
1341 PROC_CALL_ERROR EXCEPTION;
1342 ORGN_NOT_MIGRATED EXCEPTION;
1343 NO_UOM_CONVERSION EXCEPTION;
1344
1345 BEGIN
1346
1347 x_failure_count := 0;
1348
1349 GMA_COMMON_LOGGING.gma_migration_central_log (
1350 p_run_id => P_migration_run_id,
1351 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1352 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
1353 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1354 p_context => 'STANDALONE_FORMULAS',
1355 p_param1 => NULL,
1356 p_param2 => NULL,
1357 p_param3 => NULL,
1358 p_param4 => NULL,
1359 p_param5 => NULL,
1360 p_db_error => NULL,
1361 p_app_short_name => 'GMA');
1362
1363 /* Retrieve default Regulatory org to use as owner org for formulas */
1364 OPEN c_get_profile_value('GR_ORGN_DEFAULT');
1365 FETCH c_get_profile_value INTO l_owner_org;
1366 CLOSE c_get_profile_value;
1367
1368 /* Retrieve organization_id to use as owner org for formulas */
1369 OPEN c_get_organization_id(l_owner_org);
1370 FETCH c_get_organization_id INTO l_owner_org_id;
1371 CLOSE c_get_organization_id;
1372
1373 IF (l_owner_org_id IS NULL) THEN
1374 RAISE ORGN_NOT_MIGRATED;
1375 END IF;
1376
1377
1378 /* Retrieve yield type to use to retrieve default uom */
1379 OPEN c_get_profile_value('FM_YIELD_TYPE');
1380 FETCH c_get_profile_value INTO l_uom_type;
1381 CLOSE c_get_profile_value;
1382
1383 /* Retrieve default uom */
1384 OPEN c_get_uom(l_uom_type);
1385 FETCH c_get_uom INTO l_uom;
1386 CLOSE c_get_uom;
1387
1388 /* Retrieve description text for formula and recipe */
1389 FND_MESSAGE.SET_NAME('GR','GR_DESC_TEXT');
1390 l_text := FND_MESSAGE.GET;
1391
1392 /* Select items that have a formula source of Standalone */
1393 OPEN c_get_items;
1394 FETCH c_get_items into l_item_code;
1395
1396 /* While items are found */
1397 WHILE c_get_items%FOUND LOOP
1398 BEGIN
1399 SAVEPOINT Standalone_Formula;
1400
1401 /* Select items that have a formula source of Standalone */
1402 --Bug# 5293938 Since its in a loop close it before reopening it.
1403 IF c_get_item_concentrations%ISOPEN THEN
1404 CLOSE c_get_item_concentrations;
1405 END IF;
1406 OPEN c_get_item_concentrations;
1407 FETCH c_get_item_concentrations into l_conc_rec;
1408
1409 IF c_get_item_concentrations%FOUND THEN
1410
1411 l_prod_item_id := get_inventory_item_id
1412 (
1413 p_organization_id => l_owner_org_id,
1414 p_item_code => l_item_code,
1415 x_return_status => l_return_status,
1416 x_msg_data => l_msg_data
1417 );
1418
1419 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1420 RAISE PROC_CALL_ERROR;
1421 END IF;
1422
1423 /* Retrieve product's primary uom */
1424 OPEN c_get_primary_uom(l_owner_org_id, l_prod_item_id);
1425 FETCH c_get_primary_uom INTO l_prod_primary_uom;
1426 CLOSE c_get_primary_uom;
1427
1428 /* If the item's primary uom is not equal to the formula uom */
1429 IF l_prod_primary_uom <> l_uom THEN
1430 l_inv_qty := INV_CONVERT.inv_um_convert
1431 (
1432 item_id => l_prod_item_id,
1433 precision => 5,
1434 from_quantity => 100,
1435 from_unit => l_prod_primary_uom,
1436 to_unit => l_uom,
1437 from_name => NULL,
1438 to_name => NULL
1439 );
1440
1441 IF l_inv_qty = -99999 THEN
1442 RAISE NO_UOM_CONVERSION;
1443 END IF;
1444 ELSE
1445 l_inv_qty := 100;
1446 END IF; -- Item's primary uom <> formula uom
1447
1448 /* Retrieve formula version */
1449 OPEN c_get_formula_vers;
1450 FETCH c_get_formula_vers INTO l_formula_vers;
1451
1452 --Bug# 5293938 added is null condition since group functions do not raise notfound
1453 IF c_get_formula_vers%NOTFOUND OR l_formula_vers IS NULL THEN
1454 l_formula_vers := 1;
1455 END IF;
1456 CLOSE c_get_formula_vers;
1457
1458 /* Retrieve recipe version */
1459 OPEN c_get_recipe_vers;
1460 FETCH c_get_recipe_vers INTO l_recipe_vers;
1461 --Bug# 5293938 added is null condition since group functions do not raise notfound
1462 IF c_get_recipe_vers%NOTFOUND OR l_recipe_vers IS NULL THEN
1463 l_recipe_vers := 1;
1464 END IF;
1465 CLOSE c_get_recipe_vers;
1466
1467 /* Retrieve formula id */
1468 OPEN c_get_formula_id;
1469 FETCH c_get_formula_id INTO l_formula_id;
1470 CLOSE c_get_formula_id;
1471
1472 /* Create formula header record and translated records */
1473 FM_FORM_MST_MLS.INSERT_ROW(
1474 X_ROWID => l_rowid,
1475 X_FORMULA_ID => l_formula_id,
1476 X_MASTER_FORMULA_ID => NULL,
1477 X_OWNER_ORGANIZATION_ID => l_owner_org_id,
1478 X_TOTAL_INPUT_QTY => 100,
1479 X_TOTAL_OUTPUT_QTY => 100,
1480 X_YIELD_UOM => l_uom,
1481 X_FORMULA_STATUS => '700',
1482 X_OWNER_ID => l_conc_rec.last_updated_by,
1483 X_PROJECT_ID => NULL,
1484 X_TEXT_CODE => NULL,
1485 X_DELETE_MARK => 0,
1486 X_FORMULA_NO => l_item_code,
1487 X_FORMULA_VERS => l_formula_vers,
1488 X_FORMULA_TYPE => 0,
1489 X_IN_USE => NULL,
1490 X_INACTIVE_IND => 0,
1491 X_SCALE_TYPE => 0,
1492 X_FORMULA_CLASS => NULL,
1493 X_FMCONTROL_CLASS => NULL,
1494 X_ATTRIBUTE_CATEGORY => NULL,
1495 X_ATTRIBUTE1 => NULL,
1496 X_ATTRIBUTE2 => NULL,
1497 X_ATTRIBUTE3 => NULL,
1498 X_ATTRIBUTE4 => NULL,
1499 X_ATTRIBUTE5 => NULL,
1500 X_ATTRIBUTE6 => NULL,
1501 X_ATTRIBUTE7 => NULL,
1502 X_ATTRIBUTE8 => NULL,
1503 X_ATTRIBUTE9 => NULL,
1504 X_ATTRIBUTE10 => NULL,
1505 X_ATTRIBUTE11 => NULL,
1506 X_ATTRIBUTE12 => NULL,
1507 X_ATTRIBUTE13 => NULL,
1508 X_ATTRIBUTE14 => NULL,
1509 X_ATTRIBUTE15 => NULL,
1510 X_ATTRIBUTE16 => NULL,
1511 X_ATTRIBUTE17 => NULL,
1512 X_ATTRIBUTE18 => NULL,
1513 X_ATTRIBUTE19 => NULL,
1514 X_ATTRIBUTE20 => NULL,
1515 X_ATTRIBUTE21 => NULL,
1516 X_ATTRIBUTE22 => NULL,
1517 X_ATTRIBUTE23 => NULL,
1518 X_ATTRIBUTE24 => NULL,
1519 X_ATTRIBUTE25 => NULL,
1520 X_ATTRIBUTE26 => NULL,
1521 X_ATTRIBUTE27 => NULL,
1522 X_ATTRIBUTE28 => NULL,
1523 X_ATTRIBUTE29 => NULL,
1524 X_ATTRIBUTE30 => NULL,
1525 X_FORMULA_DESC1 => l_text || ' ' || l_item_code,
1526 X_FORMULA_DESC2 => NULL,
1527 X_CREATION_DATE => l_conc_rec.creation_date,
1528 X_CREATED_BY => l_conc_rec.created_by,
1529 X_LAST_UPDATE_DATE => l_conc_rec.last_update_date,
1530 X_LAST_UPDATED_BY => l_conc_rec.last_updated_by,
1531 X_LAST_UPDATE_LOGIN => l_conc_rec.last_update_login);
1532
1533 IF l_rowid IS NULL THEN
1534 RAISE PROC_CALL_ERROR;
1535 END IF;
1536
1537 /* Retrieve formula line id */
1538 OPEN c_get_formulaline_id;
1539 FETCH c_get_formulaline_id INTO l_formulaline_id;
1540 CLOSE c_get_formulaline_id;
1541
1542 /* Create formula detail line for product */
1543 INSERT INTO fm_matl_dtl
1544 (
1545 FORMULALINE_ID,
1546 FORMULA_ID,
1547 LINE_TYPE,
1548 LINE_NO,
1549 INVENTORY_ITEM_ID,
1550 ORGANIZATION_ID,
1551 QTY,
1552 DETAIL_UOM,
1553 RELEASE_TYPE,
1554 SCRAP_FACTOR,
1555 SCALE_TYPE,
1556 PHANTOM_TYPE,
1557 REWORK_TYPE,
1558 CREATED_BY,
1559 CREATION_DATE,
1560 LAST_UPDATE_DATE,
1561 LAST_UPDATED_BY,
1562 LAST_UPDATE_LOGIN,
1563 CONTRIBUTE_STEP_QTY_IND,
1564 CONTRIBUTE_YIELD_IND
1565 )
1566 VALUES
1567 (
1568 l_formulaline_id,
1569 l_formula_id,
1570 1,
1571 1,
1572 l_prod_item_id,
1573 l_owner_org_id,
1574 100,
1575 l_uom,
1576 0,
1577 0,
1578 1,
1579 0,
1580 0,
1581 l_conc_rec.CREATED_BY,
1582 l_conc_rec.CREATION_DATE,
1583 l_conc_rec.LAST_UPDATE_DATE,
1584 l_conc_rec.LAST_UPDATED_BY,
1585 l_conc_rec.LAST_UPDATE_LOGIN,
1586 'Y',
1587 'Y'
1588 );
1589
1590 l_line_no := 0;
1591
1592 WHILE c_get_item_concentrations%FOUND LOOP
1593
1594 l_line_no := l_line_no +1;
1595
1596 l_ing_item_id := get_inventory_item_id
1597 (
1598 p_organization_id => l_owner_org_id,
1599 p_item_code => l_conc_rec.ingredient_item_code,
1600 x_return_status => l_return_status,
1601 x_msg_data => l_msg_data
1602 );
1603
1604 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1605 RAISE PROC_CALL_ERROR;
1606 END IF;
1607
1608 /* Retrieve ingredient's primary uom */
1609 OPEN c_get_primary_uom(l_owner_org_id, l_ing_item_id);
1610 FETCH c_get_primary_uom INTO l_ing_primary_uom;
1611 CLOSE c_get_primary_uom;
1612
1613 /* If the item's primary uom is not equal to the formula uom */
1614 IF l_prod_primary_uom <> l_uom THEN
1615 l_temp := INV_CONVERT.inv_um_convert
1616 (
1617 item_id => l_ing_item_id,
1618 precision => 5,
1619 from_quantity => 1,
1620 from_unit => l_ing_primary_uom,
1621 to_unit => l_uom,
1622 from_name => NULL,
1623 to_name => NULL
1624 );
1625
1626 IF l_temp = -99999 THEN
1627 RAISE NO_UOM_CONVERSION;
1628 END IF;
1629
1630 END IF; -- Item's primary uom <> formula uom
1631
1632 /* Retrieve formula line id */
1633 OPEN c_get_formulaline_id;
1634 FETCH c_get_formulaline_id INTO l_formulaline_id;
1635 CLOSE c_get_formulaline_id;
1636
1637 INSERT INTO fm_matl_dtl
1638 (
1639 FORMULALINE_ID,
1640 FORMULA_ID,
1641 LINE_TYPE,
1642 LINE_NO,
1643 INVENTORY_ITEM_ID,
1644 ORGANIZATION_ID,
1645 QTY,
1646 DETAIL_UOM,
1647 RELEASE_TYPE,
1648 SCRAP_FACTOR,
1649 SCALE_TYPE,
1650 PHANTOM_TYPE,
1651 REWORK_TYPE,
1652 CREATED_BY,
1653 CREATION_DATE,
1654 LAST_UPDATE_DATE,
1655 LAST_UPDATED_BY,
1656 LAST_UPDATE_LOGIN,
1657 CONTRIBUTE_STEP_QTY_IND,
1658 CONTRIBUTE_YIELD_IND
1659 )
1660 VALUES
1661 (
1662 l_formulaline_id,
1663 l_formula_id,
1664 -1,
1665 l_line_no,
1666 l_ing_item_id,
1667 l_owner_org_id,
1668 l_conc_rec.concentration_percentage,
1669 l_uom,
1670 0,
1671 0,
1672 1,
1673 0,
1674 0,
1675 l_conc_rec.CREATED_BY,
1676 l_conc_rec.CREATION_DATE,
1677 l_conc_rec.LAST_UPDATE_DATE,
1678 l_conc_rec.LAST_UPDATED_BY,
1679 l_conc_rec.LAST_UPDATE_LOGIN,
1680 'Y',
1681 'Y'
1682 );
1683
1684 FETCH c_get_item_concentrations into l_conc_rec;
1685
1686 END LOOP; -- Item concentration recs
1687 CLOSE c_get_item_concentrations;
1688
1689 /* Retrieve recipe id */
1690 OPEN c_get_recipe_id;
1691 FETCH c_get_recipe_id INTO l_recipe_id;
1692 CLOSE c_get_recipe_id;
1693
1694 /* Create the recipe */
1695 GMD_RECIPES_MLS.INSERT_ROW(
1696 X_ROWID => l_rowid,
1697 X_RECIPE_ID => l_recipe_id,
1698 X_OWNER_ID => l_conc_rec.last_updated_by,
1699 X_OWNER_LAB_TYPE => NULL,
1700 X_DELETE_MARK => 0,
1701 X_TEXT_CODE => NULL,
1702 X_RECIPE_NO => l_item_code,
1703 X_RECIPE_VERSION => l_recipe_vers,
1704 X_OWNER_ORGANIZATION_ID => l_owner_org_id,
1705 X_CREATION_ORGANIZATION_ID => l_owner_org_id,
1706 X_FORMULA_ID => l_formula_id,
1707 X_ROUTING_ID => NULL,
1708 X_PROJECT_ID => NULL,
1709 X_RECIPE_STATUS => '700',
1710 X_RECIPE_TYPE => 1,
1711 X_ENHANCED_PI_IND => NULL,
1712 X_CALCULATE_STEP_QUANTITY => 0,
1713 X_PLANNED_PROCESS_LOSS => NULL,
1714 X_CONTIGUOUS_IND => NULL,
1715 X_RECIPE_DESCRIPTION => l_text || ' ' || l_item_code,
1716 X_ATTRIBUTE_CATEGORY => NULL,
1717 X_ATTRIBUTE1 => NULL,
1718 X_ATTRIBUTE2 => NULL,
1719 X_ATTRIBUTE3 => NULL,
1720 X_ATTRIBUTE4 => NULL,
1721 X_ATTRIBUTE5 => NULL,
1722 X_ATTRIBUTE6 => NULL,
1723 X_ATTRIBUTE7 => NULL,
1724 X_ATTRIBUTE8 => NULL,
1725 X_ATTRIBUTE9 => NULL,
1726 X_ATTRIBUTE10 => NULL,
1727 X_ATTRIBUTE11 => NULL,
1728 X_ATTRIBUTE12 => NULL,
1729 X_ATTRIBUTE13 => NULL,
1730 X_ATTRIBUTE14 => NULL,
1731 X_ATTRIBUTE15 => NULL,
1732 X_ATTRIBUTE16 => NULL,
1733 X_ATTRIBUTE17 => NULL,
1734 X_ATTRIBUTE18 => NULL,
1735 X_ATTRIBUTE19 => NULL,
1736 X_ATTRIBUTE20 => NULL,
1737 X_ATTRIBUTE21 => NULL,
1738 X_ATTRIBUTE22 => NULL,
1739 X_ATTRIBUTE23 => NULL,
1740 X_ATTRIBUTE24 => NULL,
1741 X_ATTRIBUTE25 => NULL,
1742 X_ATTRIBUTE26 => NULL,
1743 X_ATTRIBUTE27 => NULL,
1744 X_ATTRIBUTE28 => NULL,
1745 X_ATTRIBUTE29 => NULL,
1746 X_ATTRIBUTE30 => NULL,
1747 X_CREATION_DATE => l_conc_rec.creation_date,
1748 X_CREATED_BY => l_conc_rec.created_by,
1749 X_LAST_UPDATE_DATE => l_conc_rec.last_update_date,
1750 X_LAST_UPDATED_BY => l_conc_rec.last_updated_by,
1751 X_LAST_UPDATE_LOGIN => l_conc_rec.last_update_login,
1752 X_FIXED_PROCESS_LOSS => 0 , /* 7582454*/
1753 X_FIXED_PROCESS_LOSS_UOM => NULL /* 7582454*/
1754 );
1755
1756 IF l_rowid IS NULL THEN
1757 RAISE PROC_CALL_ERROR;
1758 END IF;
1759
1760 /* Retrieve recipe validity rule id */
1761 OPEN c_get_recipe_vr_id;
1762 FETCH c_get_recipe_vr_id INTO l_validity_rule_id;
1763 CLOSE c_get_recipe_vr_id;
1764
1765 /* Create validity rule for new recipe */
1766 INSERT INTO gmd_recipe_validity_rules
1767 (
1768 RECIPE_VALIDITY_RULE_ID,
1769 RECIPE_ID,
1770 ORGN_CODE,
1771 RECIPE_USE,
1772 PREFERENCE,
1773 START_DATE,
1774 END_DATE,
1775 MIN_QTY,
1776 MAX_QTY,
1777 STD_QTY,
1778 DETAIL_UOM,
1779 INV_MIN_QTY,
1780 INV_MAX_QTY,
1781 DELETE_MARK,
1782 CREATED_BY,
1783 CREATION_DATE,
1784 LAST_UPDATE_DATE,
1785 LAST_UPDATED_BY,
1786 LAST_UPDATE_LOGIN,
1787 VALIDITY_RULE_STATUS,
1788 LAB_TYPE,
1789 ORGANIZATION_ID,
1790 INVENTORY_ITEM_ID
1791 )
1792 VALUES
1793 (
1794 l_validity_rule_id,
1795 l_recipe_id,
1796 NULL,
1797 3,
1798 1,
1799 l_conc_rec.creation_date,
1800 NULL,
1801 100,
1802 100,
1803 100,
1804 l_uom,
1805 l_inv_qty,
1806 l_inv_qty,
1807 0,
1808 l_conc_rec.CREATED_BY,
1809 l_conc_rec.CREATION_DATE,
1810 l_conc_rec.LAST_UPDATE_DATE,
1811 l_conc_rec.LAST_UPDATED_BY,
1812 l_conc_rec.LAST_UPDATE_LOGIN,
1813 700,
1814 NULL,
1815 l_owner_org_id,
1816 l_prod_item_id
1817 );
1818
1819
1820 UPDATE gr_item_concentrations
1821 SET migration_ind = 1
1822 WHERE item_code = l_item_code;
1823
1824 /* Issue commit if required */
1825 IF p_commit = FND_API.G_TRUE THEN
1826 COMMIT;
1827 END IF;
1828
1829 END IF; -- If concentration record found
1830
1831 EXCEPTION
1832 WHEN PROC_CALL_ERROR THEN
1833 x_failure_count := x_failure_count + 1;
1834
1835 GMA_COMMON_LOGGING.gma_migration_central_log (
1836 p_run_id => P_migration_run_id,
1837 p_log_level => FND_LOG.LEVEL_EXCEPTION,
1838 p_message_token => l_msg_data,
1839 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1840 p_context => 'STANDALONE_FORMULAS',
1841 p_param1 => NULL,
1842 p_param2 => NULL,
1843 p_param3 => NULL,
1844 p_param4 => NULL,
1845 p_param5 => NULL,
1846 p_db_error => SQLERRM,
1847 p_app_short_name => 'GMA');
1848
1849 ROLLBACK to SAVEPOINT Standalone_Formula;
1850
1851 WHEN NO_UOM_CONVERSION THEN
1852 x_failure_count := x_failure_count + 1;
1853
1854 GMA_COMMON_LOGGING.gma_migration_central_log (
1855 p_run_id => P_migration_run_id,
1856 p_log_level => FND_LOG.LEVEL_EXCEPTION,
1857 p_message_token => l_msg_data,
1858 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1859 p_context => 'STANDALONE_FORMULAS',
1860 p_param1 => NULL,
1861 p_param2 => NULL,
1862 p_param3 => NULL,
1863 p_param4 => NULL,
1864 p_param5 => NULL,
1865 p_db_error => SQLERRM,
1866 p_app_short_name => 'GMA');
1867
1868 WHEN OTHERS THEN
1869 x_failure_count := x_failure_count + 1;
1870
1871 GMA_COMMON_LOGGING.gma_migration_central_log (
1872 p_run_id => P_migration_run_id,
1873 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1874 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1875 p_table_name => 'PO_HAZARD_CLASSES',
1876 p_context => 'HAZARD_CLASSES',
1877 p_param1 => NULL,
1878 p_param2 => NULL,
1879 p_param3 => NULL,
1880 p_param4 => NULL,
1881 p_param5 => NULL,
1882 p_db_error => SQLERRM,
1883 p_app_short_name => 'GMA');
1884
1885 ROLLBACK to SAVEPOINT Standalone_Formula;
1886 END; -- Subprogram
1887
1888 FETCH c_get_items into l_item_code;
1889
1890 END LOOP; -- Items with standalone formula source
1891 CLOSE c_get_items;
1892
1893 GMA_COMMON_LOGGING.gma_migration_central_log (
1894 p_run_id => P_migration_run_id,
1895 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1896 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
1897 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1898 p_context => 'STANDALONE_FORMULAS',
1899 p_param1 => l_migration_count,
1900 p_param2 => x_failure_count,
1901 p_param3 => NULL,
1902 p_param4 => NULL,
1903 p_param5 => NULL,
1904 p_db_error => NULL,
1905 p_app_short_name => 'GMA');
1906
1907
1908 EXCEPTION
1909 WHEN ORGN_NOT_MIGRATED THEN
1910 x_failure_count := x_failure_count + l_failure_count;
1911 GMA_COMMON_LOGGING.gma_migration_central_log (
1912 p_run_id => P_migration_run_id,
1913 p_log_level => FND_LOG.LEVEL_ERROR,
1914 p_message_token => 'GMA_ORG_NOT_MIGRATED',
1915 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1916 p_context => 'STANDALONE_FORMULAS',
1917 p_token1 => 'ORGANIZATION',
1918 p_param1 => l_owner_org,
1919 p_app_short_name => 'GR');
1920
1921 WHEN OTHERS THEN
1922 x_failure_count := x_failure_count + 1;
1923
1924 GMA_COMMON_LOGGING.gma_migration_central_log (
1925 p_run_id => P_migration_run_id,
1926 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1927 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1928 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1929 p_context => 'STANDALONE_FORMULAS',
1930 p_param1 => NULL,
1931 p_param2 => NULL,
1932 p_param3 => NULL,
1933 p_param4 => NULL,
1934 p_param5 => NULL,
1935 p_db_error => SQLERRM,
1936 p_app_short_name => 'GMA');
1937
1938 GMA_COMMON_LOGGING.gma_migration_central_log (
1939 p_run_id => P_migration_run_id,
1940 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1941 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
1942 p_table_name => 'GR_ITEM_CONCENTRATIONS',
1943 p_context => 'STANDALONE_FORMULAS',
1944 p_param1 => x_failure_count,
1945 p_param2 => NULL,
1946 p_param3 => NULL,
1947 p_param4 => NULL,
1948 p_param5 => NULL,
1949 p_db_error => NULL,
1950 p_app_short_name => 'GMA');
1951
1952 END migrate_standalone_formulas;
1953
1954
1955
1956 /*===========================================================================
1957 -- PROCEDURE:
1958 -- update_dispatch_history
1959 --
1960 -- DESCRIPTION:
1961 -- This PL/SQL procedure is used to update the organization_id and
1962 -- inventory_item_id columns in the gr_dispatch_history table if the
1963 -- values are NULL.
1964 --
1965 -- PARAMETERS:
1966 -- p_migration_run_id - Migration run id to be used for writing to the message log
1967 -- p_commit - Indicates if commit should be issued after logical unit is migrated
1968 -- x_failure_count - Returns the number of failures that occurred during migration
1969 --
1970 -- SYNOPSIS:
1971 -- update_dispatch_history(
1972 -- p_migration_run_id => migration_id,
1973 -- p_commit => 'Y',
1974 -- x_failure_count => failure_count );
1975 --
1976 -- HISTORY
1977 -- M. Grosser 17-May-2005 Created
1978 --=========================================================================== */
1979 PROCEDURE update_dispatch_history
1980 (
1981 p_migration_run_id IN NUMBER,
1982 p_commit IN VARCHAR2,
1983 x_failure_count OUT NOCOPY NUMBER
1984 ) IS
1985 /* ------------- LOCAL VARIABLES ------------------- */
1986 l_seq NUMBER;
1987 l_mig_status NUMBER;
1988 l_migration_count NUMBER := 0;
1989 l_default_org VARCHAR2(4);
1990 l_default_org_id NUMBER;
1991 l_doc_org VARCHAR2(4);
1992 l_org_id NUMBER;
1993 l_return_status VARCHAR2(2);
1994 l_msg_data VARCHAR2(2000);
1995 l_inv_item_id NUMBER;
1996
1997 /* ------------------ CURSORS ---------------------- */
1998 /* Cursor used retrieve the default organization code */
1999 CURSOR c_get_default_org IS
2000 SELECT profile_option_value
2001 FROM fnd_profile_options a, fnd_profile_option_values b
2002 WHERE b.level_id = 10001 and
2003 a.profile_option_id = b.profile_option_id and
2004 a.profile_option_name = 'GR_ORGN_DEFAULT';
2005
2006 /* Cursor used retrieve organization id */
2007 CURSOR c_get_org_id (v_orgn_code VARCHAR2) IS
2008 SELECT organization_id
2009 FROM sy_orgn_mst_b
2010 WHERE orgn_code = v_orgn_code;
2011
2012 /* Cursor used retrieve the records that don't have an organization id */
2013 CURSOR c_get_disp_rec IS
2014 SELECT dispatch_history_id, item, document_id
2015 FROM gr_dispatch_history
2016 WHERE organization_id is NULL;
2017 l_dispatch_rec c_get_disp_rec%ROWTYPE;
2018
2019 /* Cursor used retrieve the organization_code from the document */
2020 CURSOR c_get_doc_org IS
2021 SELECT doc_attribute5
2022 FROM fnd_documents_tl
2023 WHERE language = userenv('LANG') and
2024 document_id = l_dispatch_rec.document_id;
2025
2026 /* ----------------- EXCEPTIONS -------------------- */
2027 INVALID_ORG_ITEM EXCEPTION;
2028
2029 BEGIN
2030
2031 x_failure_count := 0;
2032
2033 GMA_COMMON_LOGGING.gma_migration_central_log (
2034 p_run_id => P_migration_run_id,
2035 p_log_level => FND_LOG.LEVEL_PROCEDURE,
2036 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
2037 p_table_name => 'GR_DISPATCH_HISTORY',
2038 p_context => 'UPDATE_DISPATCH_HISTORY',
2039 p_param1 => NULL,
2040 p_param2 => NULL,
2041 p_param3 => NULL,
2042 p_param4 => NULL,
2043 p_param5 => NULL,
2044 p_db_error => NULL,
2045 p_app_short_name => 'GMA');
2046
2047 /* Retrieve default org */
2048 OPEN c_get_default_org;
2049 FETCH c_get_default_org into l_default_org;
2050 CLOSE c_get_default_org;
2051
2052 /* Retrieve organization id for default org */
2053 OPEN c_get_org_id(l_default_org);
2054 FETCH c_get_org_id into l_default_org_id;
2055 CLOSE c_get_org_id;
2056
2057 /* Retrieve organization id for default org */
2058 OPEN c_get_disp_rec;
2059 FETCH c_get_disp_rec into l_dispatch_rec;
2060
2061 WHILE c_get_disp_rec%FOUND LOOP
2062
2063 BEGIN
2064
2065 /* Retrieve organization id for default org */
2066 OPEN c_get_doc_org;
2067 FETCH c_get_doc_org into l_doc_org;
2068 CLOSE c_get_doc_org;
2069
2070 /* Retrieve organization id for default org */
2071 OPEN c_get_org_id(l_doc_org);
2072 FETCH c_get_org_id into l_org_id;
2073
2074 IF c_get_org_id%NOTFOUND THEN
2075 l_org_id := l_default_org_id;
2076 END IF;
2077
2078 CLOSE c_get_org_id;
2079
2080 l_inv_item_id := get_inventory_item_id
2081 (
2082 p_organization_id => l_org_id,
2083 p_item_code => l_dispatch_rec.item,
2084 x_return_status => l_return_status,
2085 x_msg_data => l_msg_data
2086 );
2087
2088 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2089 RAISE INVALID_ORG_ITEM;
2090 END IF;
2091
2092 UPDATE gr_dispatch_history
2093 SET organization_id = l_org_id,
2094 inventory_item_id = l_inv_item_id
2095 WHERE dispatch_history_id = l_dispatch_rec.dispatch_history_id;
2096
2097 /* Issue commit if required */
2098 IF p_commit = FND_API.G_TRUE THEN
2099 COMMIT;
2100 END IF;
2101
2102 l_migration_count := l_migration_count + 1;
2103
2104 EXCEPTION
2105
2106 WHEN INVALID_ORG_ITEM THEN
2107 x_failure_count := x_failure_count + 1;
2108
2109 GMA_COMMON_LOGGING.gma_migration_central_log (
2110 p_run_id => P_migration_run_id,
2111 p_log_level => FND_LOG.LEVEL_EXCEPTION,
2112 p_message_token => 'GR_INVALID_ORG_ITEM',
2113 p_table_name => 'GR_DISPATCH_HISTORY',
2114 p_context => 'UPDATE_DISPATCH_HISTORY',
2115 p_param1 => l_org_id,
2116 p_param2 => l_dispatch_rec.item,
2117 p_param3 => NULL,
2118 p_param4 => NULL,
2119 p_param5 => NULL,
2120 p_db_error => SQLERRM,
2121 p_app_short_name => 'GR');
2122
2123 WHEN OTHERS THEN
2124 x_failure_count := x_failure_count + 1;
2125
2126 GMA_COMMON_LOGGING.gma_migration_central_log (
2127 p_run_id => P_migration_run_id,
2128 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
2129 p_message_token => 'GMA_MIGRATION_DB_ERROR',
2130 p_table_name => 'GR_DISPATCH_HISTORY',
2131 p_context => 'UPDATE_DISPATCH_HISTORY',
2132 p_param1 => NULL,
2133 p_param2 => NULL,
2134 p_param3 => NULL,
2135 p_param4 => NULL,
2136 p_param5 => NULL,
2137 p_db_error => SQLERRM,
2138 p_app_short_name => 'GMA');
2139
2140 END;
2141
2142 FETCH c_get_disp_rec into l_dispatch_rec;
2143
2144 END LOOP;
2145
2146 CLOSE c_get_disp_rec;
2147
2148 GMA_COMMON_LOGGING.gma_migration_central_log (
2149 p_run_id => P_migration_run_id,
2150 p_log_level => FND_LOG.LEVEL_PROCEDURE,
2151 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
2152 p_table_name => 'GR_DISPATCH_HISTORY',
2153 p_context => 'UPDATE_DISPATCH_HISTORY',
2154 p_param1 => l_migration_count,
2155 p_param2 => x_failure_count,
2156 p_param3 => NULL,
2157 p_param4 => NULL,
2158 p_param5 => NULL,
2159 p_db_error => NULL,
2160 p_app_short_name => 'GMA');
2161
2162 EXCEPTION
2163 WHEN OTHERS THEN
2164 x_failure_count := x_failure_count + 1;
2165
2166 GMA_COMMON_LOGGING.gma_migration_central_log (
2167 p_run_id => P_migration_run_id,
2168 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
2169 p_message_token => 'GMA_MIGRATION_DB_ERROR',
2170 p_table_name => 'GR_DISPATCH_HISTORY',
2171 p_context => 'UPDATE_DISPATCH_HISTORY',
2172 p_param1 => NULL,
2173 p_param2 => NULL,
2174 p_param3 => NULL,
2175 p_param4 => NULL,
2176 p_param5 => NULL,
2177 p_db_error => SQLERRM,
2178 p_app_short_name => 'GMA');
2179
2180 GMA_COMMON_LOGGING.gma_migration_central_log (
2181 p_run_id => P_migration_run_id,
2182 p_log_level => FND_LOG.LEVEL_PROCEDURE,
2183 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
2184 p_table_name => 'GR_DISPATCH_HISTORY',
2185 p_context => 'UPDATE_DISPATCH_HISTORY',
2186 p_param1 => x_failure_count,
2187 p_param2 => NULL,
2188 p_param3 => NULL,
2189 p_param4 => NULL,
2190 p_param5 => NULL,
2191 p_db_error => NULL,
2192 p_app_short_name => 'GMA');
2193
2194 END update_dispatch_history;
2195
2196
2197
2198 END GR_MIGRATE_TO_12;