[Home] [Help]
PACKAGE BODY: APPS.INV_GMI_MIGRATION
Source
1 PACKAGE BODY INV_GMI_Migration AS
2 /* $Header: INVGMIMB.pls 120.17 2007/12/17 09:47:28 rlnagara ship $
3 +==========================================================================+
4 | Copyright (c) 1998 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +==========================================================================+
8 | FILE NAME |
9 | INVGMIMB.pls |
10 | |
11 | TYPE |
12 | |
13 | PACKAGE NAME |
14 | INVGMIMB |
15 | |
16 | DESCRIPTION |
17 | This package contains the procedure used for inventory migration for |
18 | OPM convergence project. These procedure are meant for migration only.|
19 | |
20 | Contents |
21 | migrate_inventory_types |
22 | migrate_item_categories |
23 | migrate_default_category_sets |
24 | migrate_lot_status |
25 | migrate_actions |
26 | migrate_opm_grades |
27 | migrate_odm_grades |
28 | migrate_lot_conversions |
29 | migrate_inventory_balances |
30 | |
31 | HISTORY |
32 | Created - Jatinder Gogna - 3/22/05 |
33 | |
34 | Jatinder - 12/1/06 - initialize the locator_id - 5692408 |
35 | |
36 | Jatinder Gogna - 12/1/06 - use the following trans type for the |
37 | shipping_ind - 5692788. |
38 | Following is based upon input for Pete, Roberta |
39 | and Discrete team. |
40 | -- Sales Order Pick |
41 | -- Move Order Transfer |
42 | |
43 | Jatinder - 11/6/06 - Bug 5692929. As per GME team, only WIP Issue |
44 | should be Disallowed. |
45 | Jatinder - 12/18/06- Bug 5722698. Added NVL to the |
46 | UPDATE_BATCH_INDICATOR column. |
47 | |
48 +==========================================================================+
49 */
50
51 G_DEFAULT_LOCT VARCHAR2(50);
52 G_msg_item_id NUMBER;
53 G_msg_ditem_id NUMBER;
54 G_msg_lot_id NUMBER;
55 G_msg_organization_id NUMBER;
56 G_msg_item_no VARCHAR2(100);
57 G_msg_ditem_no VARCHAR2(100);
58 G_msg_lot_no VARCHAR2(100);
59 G_msg_organization_code VARCHAR2(100);
60
61 /*====================================================================
62 -- PROCEDURE:
63 -- item
64 --
65 -- DESCRIPTION:
66 -- This PL/SQL procedure is used to item_no
67 --
68 --
69 -- PARAMETERS:
70 --
71 -- SYNOPSIS:
72 -- get_mig_status;
73 --
74 -- HISTORY
75 --====================================================================*/
76 FUNCTION item
77 ( p_item_id IN NUMBER)
78 RETURN VARCHAR2 IS
79 PRAGMA AUTONOMOUS_TRANSACTION;
80 l_return_val VARCHAR2(200);
81 BEGIN
82 IF G_msg_item_id = p_item_id THEN
83 RETURN G_msg_item_no;
84 END IF;
85
86 BEGIN
87 SELECT item_no ||'('||to_char(p_item_id)||')'
88 INTO l_return_val
89 FROM ic_item_mst_b
90 WHERE item_id = p_item_id;
91 EXCEPTION
92 WHEN NO_DATA_FOUND THEN
93 G_msg_item_no := '('||to_char(p_item_id)||')';
94 END;
95
96 G_msg_item_id := p_item_id;
97 G_msg_item_no := substr(l_return_val,1,100);
98
99 RETURN G_msg_item_no;
100 END;
101
102 /*====================================================================
103 -- PROCEDURE:
104 -- ditem
105 --
106 -- DESCRIPTION:
107 -- This PL/SQL procedure is used to item_no
108 --
109 --
110 -- PARAMETERS:
111 --
112 -- SYNOPSIS:
113 -- get_mig_status;
114 --
115 -- HISTORY
116 --====================================================================*/
117 FUNCTION ditem
118 ( p_organization_id IN NUMBER,
119 p_ditem_id IN NUMBER)
120 RETURN VARCHAR2 IS
121 PRAGMA AUTONOMOUS_TRANSACTION;
122 l_return_val VARCHAR2(200);
123 BEGIN
124 IF G_msg_ditem_id = p_ditem_id THEN
125 RETURN G_msg_ditem_no;
126 END IF;
127
128 BEGIN
129 SELECT segment1 ||'('||to_char(p_ditem_id)||')'
130 INTO l_return_val
131 FROM mtl_system_items_b
132 WHERE organization_id = p_organization_id AND
133 inventory_item_id = p_ditem_id;
134 EXCEPTION
135 WHEN NO_DATA_FOUND THEN
136 G_msg_ditem_no := '('||to_char(p_ditem_id)||')';
137 END;
138
139 G_msg_ditem_id := p_ditem_id;
140 G_msg_ditem_no := substr(l_return_val,1,100);
141
142 RETURN G_msg_ditem_no;
143 END;
144
145 /*====================================================================
146 -- PROCEDURE:
147 -- lot
148 --
149 -- DESCRIPTION:
150 -- This PL/SQL procedure is used to get lot_no
151 --
152 --
153 -- PARAMETERS:
154 --
155 -- SYNOPSIS:
156 -- get_mig_status;
157 --
158 -- HISTORY
159 --====================================================================*/
160 FUNCTION lot
161 ( p_lot_id IN NUMBER)
162 RETURN VARCHAR2 IS
163 PRAGMA AUTONOMOUS_TRANSACTION;
164 l_return_val VARCHAR2(200);
165 BEGIN
166 IF G_msg_lot_id = p_lot_id THEN
167 RETURN G_msg_lot_no;
168 END IF;
169
170 BEGIN
171 SELECT lot_no ||decode(sublot_no, NULL,NULL,', '||sublot_no)
172 ||'('||to_char(p_lot_id)||')'
173 INTO l_return_val
174 FROM ic_lots_mst
175 WHERE lot_id = p_lot_id AND
176 rownum = 1;
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN
179 G_msg_lot_no := '('||to_char(p_lot_id)||')';
180 END;
181
182 G_msg_lot_id := p_lot_id;
183 G_msg_lot_no := substr(l_return_val,1,100);
184
185 RETURN G_msg_lot_no;
186 END;
187
188 /*====================================================================
189 -- PROCEDURE:
190 -- org
191 --
192 -- DESCRIPTION:
193 -- This PL/SQL procedure is used to get org code
194 --
195 --
196 -- PARAMETERS:
197 --
198 -- SYNOPSIS:
199 -- get_mig_status;
200 --
201 -- HISTORY
202 --====================================================================*/
203 FUNCTION org
204 ( p_organization_id IN NUMBER)
205 RETURN VARCHAR2 IS
206 PRAGMA AUTONOMOUS_TRANSACTION;
207 l_return_val VARCHAR2(200);
208 BEGIN
209 IF G_msg_organization_id = p_organization_id THEN
210 RETURN G_msg_organization_code;
211 END IF;
212
213 BEGIN
214 SELECT organization_code ||'('||to_char(p_organization_id)||')'
215 INTO l_return_val
216 FROM mtl_parameters
217 WHERE organization_id = p_organization_id;
218 EXCEPTION
219 WHEN NO_DATA_FOUND THEN
220 G_msg_organization_code := '('||to_char(p_organization_id)||')';
221 END;
222
223 G_msg_organization_id := p_organization_id;
224 G_msg_organization_code := substr(l_return_val,1,100);
225
226 RETURN G_msg_organization_code;
227 END;
228
229 /*====================================================================
230 -- PROCEDURE:
231 -- migrate_inventory_types
232 --
233 -- DESCRIPTION:
234 -- This PL/SQL procedure is used to migrate the OPM inventory types
235 -- to Oracle Inventory for the convergence project.
236 --
237 -- PARAMETERS:
238 -- p_migration_run_id - id to use to right to migration log
239 -- p_commit - flag to indicate if commit shouldbe performed.
240 -- x_failure_count - Number of exceptions occurred.
241 --
242 -- SYNOPSIS:
243 -- migrate_inventory_types (p_migartion_id => l_migration_id,
244 -- p_commit => l_commit ,
245 -- x_exception_count => l_exception_count );
246 --
247 -- HISTORY
248 -- Jatinder Gogna - Created - 03/25/05
249 --====================================================================*/
250
251 PROCEDURE migrate_inventory_types
252 ( p_migration_run_id IN NUMBER
253 , p_commit IN VARCHAR2
254 , x_failure_count OUT NOCOPY NUMBER) IS
255
256 CURSOR c_ic_invn_typ IS
257 SELECT *
258 FROM ic_invn_typ
259 WHERE migrated_ind is NULL;
260
261 l_status_id NUMBER;
262 l_count PLS_INTEGER;
263 l_enabled_flag VARCHAR2(1);
264 l_is_allowed PLS_INTEGER;
265 l_migrate_count PLS_INTEGER;
266
267 l_rowid ROWID;
268
269 BEGIN
270 x_failure_count := 0;
271 l_migrate_count := 0;
272 -- dbms_output.put_line ('Started INVENTORY TYPES migration');
273 GMA_COMMON_LOGGING.gma_migration_central_log (
274 p_run_id => p_migration_run_id,
275 p_log_level => FND_LOG.LEVEL_PROCEDURE,
276 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
277 p_table_name => 'IC_INVN_TYP',
278 p_context => 'INVENTORY TYPES',
279 p_param1 => NULL,
280 p_param2 => NULL,
281 p_param3 => NULL,
282 p_param4 => NULL,
283 p_param5 => NULL,
284 p_db_error => NULL,
285 p_app_short_name => 'GMA');
286
287 FOR l_inv_type IN c_ic_invn_typ LOOP
288 BEGIN
289 -- Check if the inventory type already exists in the discrete
290 SELECT count(*)
291 INTO l_count
292 FROM fnd_lookup_values
293 WHERE
294 lookup_type = 'ITEM_TYPE' and
295 lookup_code = l_inv_type.inv_type and
296 view_application_id = 3 and
297 ROWNUM = 1;
298
299 IF (l_count > 0) THEN
300 -- No migration needed, skip it.
301 -- dbms_output.put_line ('Inventory type '||l_inv_type.inv_type||' already exist in discrete.');
302 GMA_COMMON_LOGGING.gma_migration_central_log (
303 p_run_id => p_migration_run_id,
304 p_log_level => FND_LOG.LEVEL_PROCEDURE,
305 p_message_token => 'GMI_MIG_TYPE_EXISTS',
306 p_table_name => 'IC_INVN_TYP',
307 p_context => 'INVENTORY TYPES',
308 p_param1 => l_inv_type.inv_type,
309 p_param2 => NULL,
310 p_param3 => NULL,
311 p_param4 => NULL,
312 p_param5 => NULL,
313 p_db_error => NULL,
314 p_app_short_name => 'GMI');
315 raise FND_API.G_EXC_ERROR;
316 END IF;
317
318
319 l_enabled_flag := 'Y';
320 IF (l_inv_type.delete_mark = 1) THEN
321 l_enabled_flag := 'N';
322 END IF;
323 FND_LOOKUP_VALUES_PKG.INSERT_ROW (
324 X_ROWID=> l_rowid,
325 X_LOOKUP_TYPE=> 'ITEM_TYPE',
326 X_SECURITY_GROUP_ID=> 0,
327 X_VIEW_APPLICATION_ID=> 3,
328 X_LOOKUP_CODE=> l_inv_type.inv_type,
329 X_TAG=> NULL,
330 X_ENABLED_FLAG=> l_enabled_flag,
331 X_START_DATE_ACTIVE=> NULL,
332 X_END_DATE_ACTIVE=> NULL,
333 X_TERRITORY_CODE=> NULL,
334 X_ATTRIBUTE_CATEGORY=> NULL,
335 X_ATTRIBUTE1 => l_inv_type.attribute1,
336 X_ATTRIBUTE2 => l_inv_type.attribute2,
337 X_ATTRIBUTE3 => l_inv_type.attribute3,
338 X_ATTRIBUTE4 => l_inv_type.attribute4,
339 X_ATTRIBUTE5 => l_inv_type.attribute5,
340 X_ATTRIBUTE6 => l_inv_type.attribute6,
341 X_ATTRIBUTE7 => l_inv_type.attribute7,
342 X_ATTRIBUTE8 => l_inv_type.attribute8,
343 X_ATTRIBUTE9 => l_inv_type.attribute9,
344 X_ATTRIBUTE10 => l_inv_type.attribute10,
345 X_ATTRIBUTE11 => l_inv_type.attribute11,
346 X_ATTRIBUTE12 => l_inv_type.attribute12,
347 X_ATTRIBUTE13 => l_inv_type.attribute13,
348 X_ATTRIBUTE14 => l_inv_type.attribute14,
349 X_ATTRIBUTE15 => l_inv_type.attribute15,
350 X_MEANING=> l_inv_type.inv_type,
351 X_DESCRIPTION=> l_inv_type.inv_type_desc,
352 X_CREATION_DATE=> l_inv_type.creation_date,
353 X_CREATED_BY=> l_inv_type.created_by,
354 X_LAST_UPDATE_DATE=> l_inv_type.last_update_date,
355 X_LAST_UPDATED_BY=> l_inv_type.last_updated_by,
356 X_LAST_UPDATE_LOGIN=> NULL
357 );
358
359 UPDATE ic_invn_typ
360 SET
361 migrated_ind = 1
362 WHERE
363 inv_type = l_inv_type.inv_type;
364
365 IF (p_commit <> FND_API.G_FALSE) THEN
366 COMMIT;
367 END IF;
368 l_migrate_count := l_migrate_count + 1;
369 EXCEPTION
370 WHEN FND_API.G_EXC_ERROR THEN
371 NULL; -- Move to the next record.
372 END;
373 END LOOP;
374
375 -- dbms_output.put_line ('Completed INVENTORY TYPES migration. Migrated = '||to_char(l_migrate_count)||', Failed = '||to_char(x_failure_count));
376 GMA_COMMON_LOGGING.gma_migration_central_log (
377 p_run_id => p_migration_run_id,
378 p_log_level => FND_LOG.LEVEL_PROCEDURE,
379 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
380 p_table_name => 'IC_INVN_TYP',
381 p_context => 'INVENTORY TYPES',
382 p_param1 => l_migrate_count,
383 p_param2 => x_failure_count,
384 p_param3 => NULL,
385 p_param4 => NULL,
386 p_param5 => NULL,
387 p_db_error => NULL,
388 p_app_short_name => 'GMA');
389 EXCEPTION
390 WHEN OTHERS THEN
391 x_failure_count := x_failure_count + 1;
392 -- dbms_output.put_line (substr(SQLERRM,1,255));
393 GMA_COMMON_LOGGING.gma_migration_central_log (
394 p_run_id => p_migration_run_id,
395 p_log_level => FND_LOG.LEVEL_ERROR,
396 p_message_token => 'GMA_MIGRATION_DB_ERROR',
397 p_table_name => 'IC_INVN_TYP',
398 p_context => 'INVENTORY TYPES',
399 p_param1 => NULL,
400 p_param2 => NULL,
401 p_param3 => NULL,
402 p_param4 => NULL,
403 p_param5 => NULL,
404 p_db_error => SQLERRM,
405 p_app_short_name => 'GMA');
406
407 END;
408
409 /*====================================================================
410 -- PROCEDURE:
411 -- migrate_default_category_sets
412 --
413 -- DESCRIPTION:
414 -- This PL/SQL procedure is used to migrate the OPM default category_sets
415 -- to Oracle Inventory for the convergence project.
416 --
417 -- PARAMETERS:
418 -- P_migration_run_id - id to use to right to migration log
419 -- p_commit - flag to indicate if commit should be performed.
420 -- x_failure_count - Number of exceptions occurred.
421 --
422 -- SYNOPSIS:
423 -- migrate_category_sets(p_migartion_id => l_migration_id,
424 -- p_commit => l_commit ,
425 -- x_exception_count => l_exception_count );
426 --
427 -- HISTORY
428 -- Jatinder Gogna - Created - 03/25/05
429 --====================================================================*/
430
431 PROCEDURE migrate_default_category_sets
432 ( p_migration_run_id IN NUMBER
433 , p_commit IN VARCHAR2
434 , x_failure_count OUT NOCOPY NUMBER) IS
435
436 e_already_exists EXCEPTION;
437 l_migrate_count PLS_INTEGER;
438 l_functional_area_id NUMBER;
439 l_category_set_id NUMBER;
440
441 CURSOR c_opm_category_sets IS
442 SELECT * FROM gmi_category_sets
443 WHERE migrated_ind is NULL AND
444 OPM_CLASS in ('ALLOC_CLASS','SEQ_CLASS','SUB_STANDARD_CLASS',
445 'TECH_CLASS','GL_CLASS','COST_CLASS','GL_BUSINESS_CLASS',
446 'GL_PRODUCT_LINE');
447
448 BEGIN
449 x_failure_count := 0;
450 l_migrate_count := 0;
451 -- dbms_output.put_line ('Started CATEGORY SETS migration');
452 GMA_COMMON_LOGGING.gma_migration_central_log (
453 p_run_id => p_migration_run_id,
454 p_log_level => FND_LOG.LEVEL_PROCEDURE,
455 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
456 p_table_name => 'GMI_CATEGORY_SETS',
457 p_context => 'CATEGORY SETS',
458 p_param1 => NULL,
459 p_param2 => NULL,
460 p_param3 => NULL,
461 p_param4 => NULL,
462 p_param5 => NULL,
463 p_db_error => NULL,
464 p_app_short_name => 'GMA');
465 FOR c in c_opm_category_sets LOOP
466 BEGIN
467 SELECT functional_area_id, category_set_id
468 INTO l_functional_area_id, l_category_set_id
469 FROM mtl_default_category_sets s,
470 mfg_lookups l
471 WHERE
472 l.lookup_type = 'MTL_FUNCTIONAL_AREAS' and
473 l.meaning = 'Process '|| decode (c.user_opm_class, 'General Ledger Class',
474 'GL Class', 'GL Product Line', 'Product Line',
475 c.user_opm_class) AND
476 l.lookup_code = s.functional_area_id;
477
478 IF (l_category_set_id <> -1) THEN
479 -- dbms_output.put_line ('Default category set already assigned for '||c.user_opm_class);
480 GMA_COMMON_LOGGING.gma_migration_central_log (
481 p_run_id => p_migration_run_id,
482 p_log_level => FND_LOG.LEVEL_PROCEDURE,
483 p_message_token => 'GMI_MIG_CAT_SET_ASSIGNED',
484 p_table_name => 'GMI_CATEGORY_SETS',
485 p_context => 'CATEGORY SETS',
486 p_param1 => c.user_opm_class,
487 p_param2 => NULL,
488 p_param3 => NULL,
489 p_param4 => NULL,
490 p_param5 => NULL,
491 p_db_error => NULL,
492 p_app_short_name => 'GMI');
493 raise e_already_exists;
494 END IF;
495
496 -- Update discrete functional area with OPM category set id for convergence
497 -- functional areas
498 UPDATE mtl_default_category_sets
499 SET category_set_id = NVL(c.category_set_id, -1)
500 WHERE
501 functional_area_id = l_functional_area_id and
502 category_set_id = -1;
503
504 UPDATE gmi_category_sets
505 SET migrated_ind = 1
506 WHERE opm_class = c.opm_class;
507
508 IF (p_commit <> FND_API.G_FALSE) THEN
509 COMMIT;
510 END IF;
511 l_migrate_count := l_migrate_count + 1;
512 EXCEPTION
513 WHEN e_already_exists THEN
514 x_failure_count := x_failure_count + 1;
515 WHEN NO_DATA_FOUND THEN
516 -- dbms_output.put_line ('Functional area does not exist in discrete : '||c.user_opm_class);
517 GMA_COMMON_LOGGING.gma_migration_central_log (
518 p_run_id => p_migration_run_id,
519 p_log_level => FND_LOG.LEVEL_PROCEDURE,
520 p_message_token => 'GMI_MIG_NO_FUNC_AREA',
521 p_table_name => 'GMI_CATEGORY_SETS',
522 p_context => 'CATEGORY SETS',
523 p_param1 => c.user_opm_class,
524 p_param2 => NULL,
525 p_param3 => NULL,
526 p_param4 => NULL,
527 p_param5 => NULL,
528 p_db_error => NULL,
529 p_app_short_name => 'GMI');
530 END;
531 END LOOP;
532
533 -- dbms_output.put_line ('Completed CATEGORY SETS migration. Migrated = '||to_char(l_migrate_count)||', Failed = '||to_char(x_failure_count));
534 GMA_COMMON_LOGGING.gma_migration_central_log (
535 p_run_id => p_migration_run_id,
536 p_log_level => FND_LOG.LEVEL_PROCEDURE,
537 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
538 p_table_name => 'GMI_CATEGORY_SETS',
539 p_context => 'CATEGORY SETS',
540 p_param1 => NULL,
541 p_param2 => NULL,
542 p_param3 => NULL,
543 p_param4 => NULL,
544 p_param5 => NULL,
545 p_db_error => NULL,
546 p_app_short_name => 'GMA');
547 EXCEPTION
548 WHEN OTHERS THEN
549 x_failure_count := x_failure_count + 1;
550 -- dbms_output.put_line (substr(SQLERRM,1,255));
551 GMA_COMMON_LOGGING.gma_migration_central_log (
552 p_run_id => p_migration_run_id,
553 p_log_level => FND_LOG.LEVEL_ERROR,
554 p_message_token => 'GMA_MIGRATION_DB_ERROR',
555 p_table_name => 'GMI_CATEGORY_SETS',
556 p_context => 'CATEGORY SETS',
557 p_param1 => NULL,
558 p_param2 => NULL,
559 p_param3 => NULL,
560 p_param4 => NULL,
561 p_param5 => NULL,
562 p_db_error => SQLERRM,
563 p_app_short_name => 'GMA');
564 END;
565
566 /*====================================================================
567 -- PROCEDURE:
568 -- migrate_item_categories
569 --
570 -- DESCRIPTION:
571 -- This PL/SQL procedure is used to migrate the OPM Item Categories
572 -- to Oracle Inventory for the convergence project.
573 --
574 -- PARAMETERS:
575 -- p_migration_run_id - id to use to right to migration log
576 -- p_commit - flag to indicate if commit shouldbe performed.
577 -- x_failure_count - Number of exceptions occurred.
578 --
579 -- SYNOPSIS:
580 -- migrate_item_categories (p_migartion_id => l_migration_id,
581 -- p_commit => l_commit ,
582 -- x_exception_count => l_exception_count );
583 --
584 -- HISTORY
585 -- Jatinder Gogna - Created - 03/25/05
586 --====================================================================*/
587
588 PROCEDURE migrate_item_categories
589 ( p_migration_run_id IN NUMBER
590 , p_commit IN VARCHAR2
591 , p_start_rowid IN ROWID
592 , p_end_rowid IN ROWID
593 , x_failure_count OUT NOCOPY NUMBER) IS
594
595 CURSOR c_item_cat_error IS
596 SELECT DISTINCT
597 i.inventory_item_id,
598 i.organization_id,
599 g.category_set_id
600 FROM gmi_item_categories g,
601 ic_item_mst_b_mig i,
602 mtl_item_categories m
603 WHERE g.rowid BETWEEN p_start_rowid AND p_end_rowid AND
604 i.migrated_ind is not NULL and
605 i.category_migrated_ind is NULL and
606 i.item_id = g.item_id and
607 m.organization_id = i.organization_id and
608 m.inventory_item_id = i.inventory_item_id and
609 m.category_set_id = g.category_set_id and
610 m.category_id <> g.category_id;
611
612 l_migrate_count PLS_INTEGER;
613
614 BEGIN
615 x_failure_count := 0;
616 l_migrate_count := 0;
617 -- dbms_output.put_line ('Started ITEM CATEGORIES migration');
618 GMA_COMMON_LOGGING.gma_migration_central_log (
619 p_run_id => p_migration_run_id,
620 p_log_level => FND_LOG.LEVEL_PROCEDURE,
621 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
622 p_table_name => 'GMI_ITEM_CATEGORIES',
623 p_context => 'ITEM CATEGORIES',
624 p_param1 => NULL,
625 p_param2 => NULL,
626 p_param3 => NULL,
627 p_param4 => NULL,
628 p_param5 => NULL,
629 p_db_error => NULL,
630 p_app_short_name => 'GMA');
631
632 -- insert new record in discrete
633 insert into mtl_item_categories(
634 INVENTORY_ITEM_ID,
635 ORGANIZATION_ID,
636 CATEGORY_SET_ID,
637 CATEGORY_ID,
638 LAST_UPDATE_DATE,
639 LAST_UPDATED_BY,
640 CREATION_DATE,
641 CREATED_BY,
642 LAST_UPDATE_LOGIN,
643 REQUEST_ID,
644 PROGRAM_APPLICATION_ID,
645 PROGRAM_ID,
646 PROGRAM_UPDATE_DATE,
647 WH_UPDATE_DATE)
648 SELECT
649 i.inventory_item_id,
650 i.organization_id,
651 g.category_set_id,
652 g.category_id,
653 g.creation_date,
654 g.created_by,
655 g.last_update_date,
656 g.last_updated_by,
657 NULL,
658 NULL,
659 NULL,
660 NULL,
661 NULL,
662 NULL
663 FROM gmi_item_categories g,
664 ic_item_mst_b_mig i
665 WHERE g.rowid BETWEEN p_start_rowid AND p_end_rowid AND
666 i.migrated_ind is not NULL and
667 i.category_migrated_ind is NULL and
668 i.item_id = g.item_id and
669 NOT EXISTS(
670 SELECT 1
671 FROM mtl_item_categories
672 WHERE
673 organization_id = i.organization_id AND
674 inventory_item_id = i.inventory_item_id AND
675 category_set_id = g.category_set_id);
676
677 l_migrate_count := SQL%ROWCOUNT;
678
679 /* Select rows with error */
680 FOR r in c_item_cat_error LOOP
681 -- Log warning message
682 -- dbms_output.put_line ('A different category already assigned in discrete. Org id, Item, category set id' || to_char(r.organization_id)||', '||to_char(v_inventory_item_id)||', '||to_char(r.category_set_id));
683 GMA_COMMON_LOGGING.gma_migration_central_log (
684 p_run_id => p_migration_run_id,
685 p_log_level => FND_LOG.LEVEL_PROCEDURE,
686 p_message_token => 'GMI_MIG_ITEM_CAT_EXISTS',
687 p_table_name => 'GMI_ITEM_CATEGORIES',
688 p_context => 'ITEM CATEGORIES',
689 p_param1 => INV_GMI_Migration.org(r.organization_id),
690 p_param2 => INV_GMI_Migration.ditem(r.organization_id, r.inventory_item_id),
691 p_param3 => to_char(r.category_set_id),
692 p_param4 => NULL,
693 p_param5 => NULL,
694 p_db_error => NULL,
695 p_app_short_name => 'GMI');
696 x_failure_count := x_failure_count + 1;
697 END LOOP;
698
699
700 /* Update the rows as migrated */
701 UPDATE ic_item_mst_b_mig
702 SET category_migrated_ind = 1
703 WHERE
704 (organization_id, inventory_item_id) IN (
705 SELECT organization_id, inventory_item_id
706 FROM gmi_item_categories
707 WHERE
708 rowid BETWEEN p_start_rowid AND p_end_rowid);
709
710 -- dbms_output.put_line ('Completed ITEM CATEGORIES migration. Migrated = '||to_char(l_migrate_count)||', Failed = '||to_char(x_failure_count));
711 GMA_COMMON_LOGGING.gma_migration_central_log (
712 p_run_id => p_migration_run_id,
713 p_log_level => FND_LOG.LEVEL_PROCEDURE,
714 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
715 p_table_name => 'GMI_ITEM_CATEGORIES',
716 p_context => 'ITEM CATEGORIES',
717 p_param1 => l_migrate_count,
718 p_param2 => x_failure_count,
719 p_param3 => NULL,
720 p_param4 => NULL,
721 p_param5 => NULL,
722 p_db_error => NULL,
723 p_app_short_name => 'GMA');
724 EXCEPTION
725 WHEN OTHERS THEN
726 x_failure_count := x_failure_count + 1;
727 -- dbms_output.put_line (substr(SQLERRM,1,255));
728 GMA_COMMON_LOGGING.gma_migration_central_log (
729 p_run_id => p_migration_run_id,
730 p_log_level => FND_LOG.LEVEL_ERROR,
731 p_message_token => 'GMA_MIGRATION_DB_ERROR',
732 p_table_name => 'GMI_ITEM_CATEGORIES',
733 p_context => 'ITEM CATEGORIES',
734 p_param1 => NULL,
735 p_param2 => NULL,
736 p_param3 => NULL,
737 p_param4 => NULL,
738 p_param5 => NULL,
739 p_db_error => SQLERRM,
740 p_app_short_name => 'GMA');
741
742 END;
743
744 /*====================================================================
745 -- PROCEDURE:
746 -- migrate_lot_status
747 --
748 -- DESCRIPTION:
749 -- This PL/SQL procedure is used to migrate the OPM Lot status
750 -- to Oracle Inventory for the convergence project.
751 --
752 -- PARAMETERS:
753 -- P_migration_run_id - id to use to right to migration log
754 -- p_commit - flag to indicate if commit shouldbe performed.
755 -- x_failure_count - Number of exceptions occurred.
756 --
757 -- SYNOPSIS:
758 -- migrate_lot_status(p_migartion_id => l_migration_id,
759 -- p_commit => l_commit ,
760 -- x_exception_count => l_exception_count );
761 --
762 -- HISTORY
763 -- Jatinder Gogna - Created - 03/25/05
764 -- RLNAGARA Bug 6607319 Added Status control flag while inserting.
765 --====================================================================*/
766
767 PROCEDURE migrate_lot_status
768 ( p_migration_run_id IN NUMBER
769 , p_commit IN VARCHAR2
770 , x_failure_count OUT NOCOPY NUMBER) IS
771
772 l_status_id NUMBER;
773 l_count PLS_INTEGER;
774 l_availability_type PLS_INTEGER;
775 l_is_allowed PLS_INTEGER;
776 l_migrate_count PLS_INTEGER;
777
778 l_rowid ROWID;
779
780 CURSOR c_ic_lots_sts IS
781 SELECT *
782 FROM ic_lots_sts
783 WHERE status_id is NULL ;
784
785 CURSOR c_trans_type IS
786 SELECT transaction_type_id, transaction_type_name, transaction_source_type_id
787 FROM mtl_transaction_types
788 WHERE
789 status_control_flag = 1 and
790 disable_date is NULL ;
791
792 BEGIN
793 x_failure_count := 0;
794 l_migrate_count := 0;
795 -- dbms_output.put_line ('Started LOT STATUS migration');
796 GMA_COMMON_LOGGING.gma_migration_central_log (
797 p_run_id => p_migration_run_id,
798 p_log_level => FND_LOG.LEVEL_PROCEDURE,
799 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
800 p_table_name => 'IC_LOTS_STS',
801 p_context => 'LOT STATUS',
802 p_param1 => NULL,
803 p_param2 => NULL,
804 p_param3 => NULL,
805 p_param4 => NULL,
806 p_param5 => NULL,
807 p_db_error => NULL,
808 p_app_short_name => 'GMA');
809 FOR l_lot_status IN c_ic_lots_sts LOOP
810 BEGIN
811 -- Check if the material status already exists in the discrete
812 SELECT count(*)
813 INTO l_count
814 FROM mtl_material_statuses_tl
815 WHERE
816 status_code = l_lot_status.lot_status and
817 rownum = 1;
818
819 IF (l_count > 0) THEN
820 -- No migration needed, skip it.
821 -- dbms_output.put_line ('Lot status already exists: '||l_lot_status.lot_status);
822 GMA_COMMON_LOGGING.gma_migration_central_log (
823 p_run_id => p_migration_run_id,
824 p_log_level => FND_LOG.LEVEL_ERROR,
825 p_message_token => 'GMI_MIG_STATUS_EXISTS',
826 p_table_name => 'IC_LOTS_STS',
827 p_context => 'LOT STATUS',
828 p_param1 => l_lot_status.lot_status,
829 p_param2 => NULL,
830 p_param3 => NULL,
831 p_param4 => NULL,
832 p_param5 => NULL,
833 p_db_error => NULL,
834 p_app_short_name => 'GMI');
835 raise FND_API.G_EXC_ERROR;
836 END IF;
837
838 SELECT mtl_material_status_s.NEXTVAL
839 INTO l_status_id
840 FROM DUAL;
841
842 l_availability_type := 2;
843 IF (l_lot_status.rejected_ind = 0) THEN
844 IF (l_lot_status.nettable_ind = 1) THEN
845 l_availability_type := 1;
846 END IF;
847 END IF;
848 MTL_MATERIAL_STATUSES_PKG.INSERT_ROW (
849 X_ROWID => l_rowid,
850 X_STATUS_ID => l_status_id,
851 X_ATTRIBUTE1 => NULL,
852 X_ATTRIBUTE2 => NULL,
853 X_ATTRIBUTE3 => NULL,
854 X_ATTRIBUTE4 => NULL,
855 X_ATTRIBUTE5 => NULL,
856 X_ATTRIBUTE6 => NULL,
857 X_ATTRIBUTE7 => NULL,
858 X_ATTRIBUTE8 => NULL,
859 X_ATTRIBUTE9 => NULL,
860 X_ATTRIBUTE10 => NULL,
861 X_ATTRIBUTE11 => NULL,
862 X_ATTRIBUTE12 => NULL,
863 X_ATTRIBUTE13 => NULL,
864 X_ATTRIBUTE14 => NULL,
865 X_ATTRIBUTE15 => NULL,
866 X_LOCATOR_CONTROL => 2,
867 X_LOT_CONTROL => 2, --RLNAGARA Material Status Migration ME - when onhand status is enabled - lot status is disabled.
868 X_SERIAL_CONTROL => 2,
869 X_ZONE_CONTROL => 2,
870 X_ONHAND_CONTROL => 1, --RLNAGARA Material Status Migration - all the statuses are default onhand controlled.
871 X_REQUEST_ID => NULL,
872 X_ATTRIBUTE_CATEGORY => NULL,
873 X_ENABLED_FLAG => l_lot_status.delete_mark+1,
874 X_STATUS_CODE => l_lot_status.lot_status,
875 X_DESCRIPTION => l_lot_status.status_desc,
876 X_CREATION_DATE => l_lot_status.creation_date,
877 X_CREATED_BY => l_lot_status.created_by,
878 X_LAST_UPDATE_DATE => l_lot_status.last_update_date,
879 X_LAST_UPDATED_BY => l_lot_status.last_updated_by,
880 X_LAST_UPDATE_LOGIN => NULL,
881 X_LPN_CONTROL => 2,
882 X_INVENTORY_ATP_CODE => l_lot_status.rejected_ind+1,
883 X_RESERVABLE_TYPE => l_lot_status.rejected_ind+1,
884 X_AVAILABILITY_TYPE => l_availability_type
885 );
886
887 FOR tt IN c_trans_type LOOP
888 l_is_allowed := 1;
889 IF (l_lot_status.order_proc_ind = 0 or l_lot_status.rejected_ind = 1) THEN
890 -- Sales Order Pick
891 -- Internal Order Pick
892 IF (tt.transaction_type_id = 52 or
893 tt.transaction_type_id = 53) THEN
894 l_is_allowed := 2;
895 END IF;
896 END IF;
897 IF (l_lot_status.prod_ind = 0 or l_lot_status.rejected_ind = 1) THEN
898 -- WIP Issue
899 -- WIP Completion Return
900 -- WIP Assy Completion
901 -- WIP Component Return
902 -- WIP By-product Completion
903 -- WIP By-product Return
904 -- Jatinder - 11/6/06 - Bug 5692929. As per GME team, only WIP Issue should be
905 -- Disallowed.
906 IF (tt.transaction_type_id = 35) THEN
907 l_is_allowed := 2;
908 END IF;
909 END IF;
910 IF (l_lot_status.shipping_ind = 0 or l_lot_status.rejected_ind = 1) THEN
911 -- Sales order issue
912 -- Internal order issue
913 -- Int Order Intr Ship
914 -- Jatinder Gogna - 12/1/06 - use the following trans type for the shipping_ind - 5692788
915 -- Following is based upon input for Pete, Roberta and Discrete team.
916 -- Sales Order Pick
917 -- Move Order Transfer
918 IF (tt.transaction_type_id = 52 or
919 tt.transaction_type_id = 64) THEN
920 l_is_allowed := 2;
921 END IF;
922 END IF;
923
924 INSERT INTO MTL_STATUS_TRANSACTION_CONTROL (
925 status_id,
926 transaction_type_id,
927 is_allowed,
928 creation_date,
929 created_by,
930 last_updated_by,
931 last_update_date
932 ) VALUES (
933 l_status_id,
934 tt.transaction_type_id,
935 l_is_allowed,
936 l_lot_status.creation_date,
937 l_lot_status.created_by,
938 l_lot_status.last_updated_by,
939 l_lot_status.last_update_date);
940
941 END LOOP;
942 UPDATE ic_lots_sts
943 SET
944 status_id = l_status_id,
945 migrated_ind = 1
946 WHERE
947 lot_status = l_lot_status.lot_status;
948
949 IF (p_commit <> FND_API.G_FALSE) THEN
950 COMMIT;
951 END IF;
952 l_migrate_count := l_migrate_count + 1;
953 EXCEPTION
954 WHEN FND_API.G_EXC_ERROR THEN
955 NULL; -- Move to the next record.
956 END;
957 END LOOP;
958
959 -- dbms_output.put_line ('Completed LOT STATUS migration. Migrated = '||to_char(l_migrate_count)||', Failed = '||to_char(x_failure_count));
960 GMA_COMMON_LOGGING.gma_migration_central_log (
961 p_run_id => p_migration_run_id,
962 p_log_level => FND_LOG.LEVEL_PROCEDURE,
963 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
964 p_table_name => 'IC_LOTS_STS',
965 p_context => 'LOT STATUS',
966 p_param1 => l_migrate_count,
967 p_param2 => x_failure_count,
968 p_param3 => NULL,
969 p_param4 => NULL,
970 p_param5 => NULL,
971 p_db_error => NULL,
972 p_app_short_name => 'GMA');
973
974 EXCEPTION
975 WHEN OTHERS THEN
976 x_failure_count := x_failure_count + 1;
977 -- dbms_output.put_line (substr(SQLERRM,1,255));
978 GMA_COMMON_LOGGING.gma_migration_central_log (
979 p_run_id => p_migration_run_id,
980 p_log_level => FND_LOG.LEVEL_ERROR,
981 p_message_token => 'GMA_MIGRATION_DB_ERROR',
982 p_table_name => 'IC_LOTS_STS',
983 p_context => 'LOT STATUS',
984 p_param1 => NULL,
985 p_param2 => NULL,
986 p_param3 => NULL,
987 p_param4 => NULL,
988 p_param5 => NULL,
989 p_db_error => SQLERRM,
990 p_app_short_name => 'GMA');
991 END;
992
993 /*====================================================================
994 -- PROCEDURE:
995 -- migrate_actions
996 --
997 -- DESCRIPTION:
998 -- This PL/SQL procedure is used to migrate the OPM actions
999 -- to Oracle Inventory for the convergence project.
1000 --
1001 -- PARAMETERS:
1002 -- P_migration_run_id - id to use to right to migration log
1003 -- p_commit - flag to indicate if commit shouldbe performed.
1004 -- x_failure_count - Number of exceptions occurred.
1005 --
1006 -- SYNOPSIS:
1007 -- migrate_actions(p_migartion_id => l_migration_id,
1008 -- p_commit => l_commit ,
1009 -- x_exception_count => l_exception_count );
1010 --
1011 -- HISTORY
1012 -- Jatinder Gogna - Created - 03/25/05
1013 --====================================================================*/
1014
1015 PROCEDURE migrate_actions
1016 ( p_migration_run_id IN NUMBER
1017 , p_commit IN VARCHAR2
1018 , x_failure_count OUT NOCOPY NUMBER) IS
1019
1020 l_rowid ROWID;
1021 l_disable_flag VARCHAR2(1);
1022 e_already_exists EXCEPTION;
1023 l_count PLS_INTEGER;
1024 l_migrate_count PLS_INTEGER;
1025
1026 CURSOR c_opm_actions IS
1027 SELECT * FROM gmd_actions_b
1028 WHERE migrated_ind is NULL;
1029
1030 CURSOR c_opm_actions_tl (p_action_code VARCHAR2) IS
1031 SELECT * FROM gmd_actions_tl
1032 WHERE action_code = p_action_code;
1033 BEGIN
1034 x_failure_count := 0;
1035 l_migrate_count := 0;
1036 -- dbms_output.put_line ('Started ACTIONS migration');
1037 GMA_COMMON_LOGGING.gma_migration_central_log (
1038 p_run_id => p_migration_run_id,
1039 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1040 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
1041 p_table_name => 'GMD_ACTIONS',
1042 p_context => 'ACTIONS',
1043 p_param1 => NULL,
1044 p_param2 => NULL,
1045 p_param3 => NULL,
1046 p_param4 => NULL,
1047 p_param5 => NULL,
1048 p_db_error => NULL,
1049 p_app_short_name => 'GMA');
1050 FOR g in c_opm_actions LOOP
1051 BEGIN
1052
1053 SELECT count(*)
1054 INTO l_count
1055 FROM mtl_actions_b
1056 WHERE action_code = g.action_code;
1057
1058 IF (l_count > 0) THEN
1059 -- dbms_output.put_line ('Action already exists: '||g.action_code);
1060 GMA_COMMON_LOGGING.gma_migration_central_log (
1061 p_run_id => p_migration_run_id,
1062 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1063 p_message_token => 'GMI_MIG_ACTION_EXISTS',
1064 p_table_name => 'GMD_ACTIONS',
1065 p_context => 'ACTIONS',
1066 p_param1 => g.action_code,
1067 p_param2 => NULL,
1068 p_param3 => NULL,
1069 p_param4 => NULL,
1070 p_param5 => NULL,
1071 p_db_error => NULL,
1072 p_app_short_name => 'GMI');
1073 raise e_already_exists;
1074 END IF;
1075
1076 l_disable_flag := 'N';
1077 IF (g.delete_mark = 1) THEN
1078 l_disable_flag := 'Y';
1079 END IF;
1080 MTL_ACTIONS_PVT.INSERT_ROW (
1081 X_ROWID => l_rowid,
1082 X_ACTION_CODE => g.action_code,
1083 X_DESCRIPTION => ' ',
1084 X_DISABLE_FLAG => l_disable_flag,
1085 X_ATTRIBUTE1 => g.ATTRIBUTE1,
1086 X_ATTRIBUTE2 => g.ATTRIBUTE2,
1087 X_ATTRIBUTE3 => g.ATTRIBUTE3,
1088 X_ATTRIBUTE4 => g.ATTRIBUTE4,
1089 X_ATTRIBUTE5 => g.ATTRIBUTE5,
1090 X_ATTRIBUTE6 => g.ATTRIBUTE6,
1091 X_ATTRIBUTE7 => g.ATTRIBUTE7,
1092 X_ATTRIBUTE8 => g.ATTRIBUTE8,
1093 X_ATTRIBUTE9 => g.ATTRIBUTE9,
1094 X_ATTRIBUTE10 => g.ATTRIBUTE10,
1095 X_ATTRIBUTE11 => g.ATTRIBUTE11,
1096 X_ATTRIBUTE12 => g.ATTRIBUTE12,
1097 X_ATTRIBUTE13 => g.ATTRIBUTE13,
1098 X_ATTRIBUTE14 => g.ATTRIBUTE14,
1099 X_ATTRIBUTE15 => g.ATTRIBUTE15,
1100 X_ATTRIBUTE16 => g.ATTRIBUTE16,
1101 X_ATTRIBUTE17 => g.ATTRIBUTE17,
1102 X_ATTRIBUTE18 => g.ATTRIBUTE18,
1103 X_ATTRIBUTE19 => g.ATTRIBUTE19,
1104 X_ATTRIBUTE20 => g.ATTRIBUTE20,
1105 X_ATTRIBUTE21 => g.ATTRIBUTE21,
1106 X_ATTRIBUTE22 => g.ATTRIBUTE22,
1107 X_ATTRIBUTE23 => g.ATTRIBUTE23,
1108 X_ATTRIBUTE24 => g.ATTRIBUTE24,
1109 X_ATTRIBUTE25 => g.ATTRIBUTE25,
1110 X_ATTRIBUTE26 => g.ATTRIBUTE26,
1111 X_ATTRIBUTE27 => g.ATTRIBUTE27,
1112 X_ATTRIBUTE28 => g.ATTRIBUTE28,
1113 X_ATTRIBUTE29 => g.ATTRIBUTE29,
1114 X_ATTRIBUTE30 => g.ATTRIBUTE30,
1115 X_ATTRIBUTE_CATEGORY => g.ATTRIBUTE_CATEGORY,
1116 X_CREATION_DATE => g.CREATION_DATE,
1117 X_CREATED_BY => g.CREATED_BY,
1118 X_LAST_UPDATE_DATE => g.LAST_UPDATE_DATE,
1119 X_LAST_UPDATED_BY => g.LAST_UPDATED_BY,
1120 X_LAST_UPDATE_LOGIN => g.LAST_UPDATE_LOGIN);
1121
1122 FOR gt in c_opm_actions_tl (g.action_code) LOOP
1123 UPDATE mtl_actions_TL
1124 SET
1125 DESCRIPTION = gt.action_desc,
1126 SOURCE_LANG = gt.source_lang
1127 WHERE
1128 action_code = gt.action_code AND
1129 language = gt.language;
1130 END LOOP;
1131
1132 UPDATE gmd_actions_b
1133 SET migrated_ind = 1
1134 WHERE action_code = g.action_code;
1135
1136 IF (p_commit <> FND_API.G_FALSE) THEN
1137 COMMIT;
1138 END IF;
1139 l_migrate_count := l_migrate_count + 1;
1140 EXCEPTION
1141 WHEN e_already_exists THEN
1142 UPDATE gmd_actions_b
1143 SET migrated_ind = 1
1144 WHERE action_code = g.action_code;
1145 END;
1146 END LOOP;
1147
1148 -- dbms_output.put_line ('Completed ACTIONS migration. Migrated = '||to_char(l_migrate_count)||', Failed = '||to_char(x_failure_count));
1149 GMA_COMMON_LOGGING.gma_migration_central_log (
1150 p_run_id => p_migration_run_id,
1151 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1152 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
1153 p_table_name => 'GMD_ACTIONS',
1154 p_context => 'ACTIONS',
1155 p_param1 => NULL,
1156 p_param2 => NULL,
1157 p_param3 => NULL,
1158 p_param4 => NULL,
1159 p_param5 => NULL,
1160 p_db_error => NULL,
1161 p_app_short_name => 'GMA');
1162 EXCEPTION
1163 WHEN OTHERS THEN
1164 x_failure_count := x_failure_count + 1;
1165 -- dbms_output.put_line (substr(SQLERRM,1,255));
1166 GMA_COMMON_LOGGING.gma_migration_central_log (
1167 p_run_id => p_migration_run_id,
1168 p_log_level => FND_LOG.LEVEL_ERROR,
1169 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1170 p_table_name => 'GMD_ACTIONS',
1171 p_context => 'ACTIONS',
1172 p_param1 => NULL,
1173 p_param2 => NULL,
1174 p_param3 => NULL,
1175 p_param4 => NULL,
1176 p_param5 => NULL,
1177 p_db_error => SQLERRM,
1178 p_app_short_name => 'GMA');
1179 END;
1180
1181 /*====================================================================
1182 -- PROCEDURE:
1183 -- migrate_opm_grades
1184 --
1185 -- DESCRIPTION:
1186 -- This PL/SQL procedure is used to migrate the OPM grades
1187 -- to Oracle Inventory for the convergence project.
1188 --
1189 -- PARAMETERS:
1190 -- P_migration_run_id - id to use to right to migration log
1191 -- p_commit - flag to indicate if commit shouldbe performed.
1192 -- x_failure_count - Number of exceptions occurred.
1193 --
1194 -- SYNOPSIS:
1195 -- migrate_opm_grades(p_migartion_id => l_migration_id,
1196 -- p_commit => l_commit ,
1197 -- x_exception_count => l_exception_count );
1198 --
1199 -- HISTORY
1200 -- Jatinder Gogna - Created - 03/25/05
1201 --====================================================================*/
1202
1203 PROCEDURE migrate_opm_grades
1204 ( p_migration_run_id IN NUMBER
1205 , p_commit IN VARCHAR2
1206 , x_failure_count OUT NOCOPY NUMBER) IS
1207
1208 l_rowid ROWID;
1209 l_disable_flag VARCHAR2(1);
1210 e_already_exists EXCEPTION;
1211 l_count PLS_INTEGER;
1212 l_migrate_count PLS_INTEGER;
1213
1214 CURSOR c_opm_grades IS
1215 SELECT * FROM gmd_grades_b
1216 WHERE migrated_ind is NULL;
1217
1218 CURSOR c_opm_grades_tl (p_qc_grade VARCHAR2) IS
1219 SELECT * FROM gmd_grades_tl
1220 WHERE QC_GRADE = p_qc_grade;
1221 BEGIN
1222 x_failure_count := 0;
1223 l_migrate_count := 0;
1224 -- dbms_output.put_line ('Started GRADES migration');
1225 GMA_COMMON_LOGGING.gma_migration_central_log (
1226 p_run_id => p_migration_run_id,
1227 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1228 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
1229 p_table_name => 'GMD_GRADES',
1230 p_context => 'GRADES',
1231 p_param1 => NULL,
1232 p_param2 => NULL,
1233 p_param3 => NULL,
1234 p_param4 => NULL,
1235 p_param5 => NULL,
1236 p_db_error => NULL,
1237 p_app_short_name => 'GMA');
1238 FOR g in c_opm_grades LOOP
1239 BEGIN
1240
1241 SELECT count(*)
1242 INTO l_count
1243 FROM mtl_grades_b
1244 WHERE grade_code = g.qc_grade;
1245
1246 IF (l_count > 0) THEN
1247 -- dbms_output.put_line ('Grade already exists: '||g.qc_grade);
1248 GMA_COMMON_LOGGING.gma_migration_central_log (
1249 p_run_id => p_migration_run_id,
1250 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1251 p_message_token => 'GMI_MIG_GRADE_EXISTS',
1252 p_table_name => 'GMD_GRADES',
1253 p_context => 'GRADES',
1254 p_param1 => g.qc_grade,
1255 p_param2 => NULL,
1256 p_param3 => NULL,
1257 p_param4 => NULL,
1258 p_param5 => NULL,
1259 p_db_error => NULL,
1260 p_app_short_name => 'GMI');
1261 raise e_already_exists;
1262 END IF;
1263
1264 l_disable_flag := 'N';
1265 IF (g.delete_mark = 1) THEN
1266 l_disable_flag := 'Y';
1267 END IF;
1268 MTL_GRADES_PVT.INSERT_ROW (
1269 X_ROWID => l_rowid,
1270 X_GRADE_CODE => g.qc_grade,
1271 X_DESCRIPTION => nvl(g.qc_grade_desc, g.qc_grade),
1272 X_DISABLE_FLAG => l_disable_flag,
1273 X_ATTRIBUTE1 => g.ATTRIBUTE1,
1274 X_ATTRIBUTE2 => g.ATTRIBUTE2,
1275 X_ATTRIBUTE3 => g.ATTRIBUTE3,
1276 X_ATTRIBUTE4 => g.ATTRIBUTE4,
1277 X_ATTRIBUTE5 => g.ATTRIBUTE5,
1278 X_ATTRIBUTE6 => g.ATTRIBUTE6,
1279 X_ATTRIBUTE7 => g.ATTRIBUTE7,
1280 X_ATTRIBUTE8 => g.ATTRIBUTE8,
1281 X_ATTRIBUTE9 => g.ATTRIBUTE9,
1282 X_ATTRIBUTE10 => g.ATTRIBUTE10,
1283 X_ATTRIBUTE11 => g.ATTRIBUTE11,
1284 X_ATTRIBUTE12 => g.ATTRIBUTE12,
1285 X_ATTRIBUTE13 => g.ATTRIBUTE13,
1286 X_ATTRIBUTE14 => g.ATTRIBUTE14,
1287 X_ATTRIBUTE15 => g.ATTRIBUTE15,
1288 X_ATTRIBUTE16 => g.ATTRIBUTE16,
1289 X_ATTRIBUTE17 => g.ATTRIBUTE17,
1290 X_ATTRIBUTE18 => g.ATTRIBUTE18,
1291 X_ATTRIBUTE19 => g.ATTRIBUTE19,
1292 X_ATTRIBUTE20 => g.ATTRIBUTE20,
1293 X_ATTRIBUTE21 => g.ATTRIBUTE21,
1294 X_ATTRIBUTE22 => g.ATTRIBUTE22,
1295 X_ATTRIBUTE23 => g.ATTRIBUTE23,
1296 X_ATTRIBUTE24 => g.ATTRIBUTE24,
1297 X_ATTRIBUTE25 => g.ATTRIBUTE25,
1298 X_ATTRIBUTE26 => g.ATTRIBUTE26,
1299 X_ATTRIBUTE27 => g.ATTRIBUTE27,
1300 X_ATTRIBUTE28 => g.ATTRIBUTE28,
1301 X_ATTRIBUTE29 => g.ATTRIBUTE29,
1302 X_ATTRIBUTE30 => g.ATTRIBUTE30,
1303 X_ATTRIBUTE_CATEGORY => g.ATTRIBUTE_CATEGORY,
1304 X_CREATION_DATE => g.CREATION_DATE,
1305 X_CREATED_BY => g.CREATED_BY,
1306 X_LAST_UPDATE_DATE => g.LAST_UPDATE_DATE,
1307 X_LAST_UPDATED_BY => g.LAST_UPDATED_BY,
1308 X_LAST_UPDATE_LOGIN => g.LAST_UPDATE_LOGIN);
1309
1310 FOR gt in c_opm_grades_tl (g.qc_grade) LOOP
1311 UPDATE MTL_GRADES_TL
1312 SET
1313 DESCRIPTION = gt.qc_grade_desc,
1314 SOURCE_LANG = gt.source_lang
1315 WHERE
1316 grade_code = gt.qc_grade AND
1317 language = gt.language;
1318 END LOOP;
1319
1320 UPDATE gmd_grades_b
1321 SET migrated_ind = 1
1322 WHERE qc_grade = g.qc_grade;
1323
1324 IF (p_commit <> FND_API.G_FALSE) THEN
1325 COMMIT;
1326 END IF;
1327 l_migrate_count := l_migrate_count + 1;
1328 EXCEPTION
1329 WHEN e_already_exists THEN
1330 UPDATE gmd_grades_b
1331 SET migrated_ind = 1
1332 WHERE qc_grade = g.qc_grade;
1333 END;
1334 END LOOP;
1335
1336 -- dbms_output.put_line ('Completed GRADES migration. Migrated = '||to_char(l_migrate_count)||', Failed = '||to_char(x_failure_count));
1337 GMA_COMMON_LOGGING.gma_migration_central_log (
1338 p_run_id => p_migration_run_id,
1339 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1340 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
1341 p_table_name => 'GMD_GRADES',
1342 p_context => 'GRADES',
1343 p_param1 => l_migrate_count,
1344 p_param2 => x_failure_count,
1345 p_param3 => NULL,
1346 p_param4 => NULL,
1347 p_param5 => NULL,
1348 p_db_error => NULL,
1349 p_app_short_name => 'GMA');
1350 EXCEPTION
1351 WHEN OTHERS THEN
1352 x_failure_count := x_failure_count + 1;
1353 -- dbms_output.put_line (substr(SQLERRM,1,255));
1354 GMA_COMMON_LOGGING.gma_migration_central_log (
1355 p_run_id => p_migration_run_id,
1356 p_log_level => FND_LOG.LEVEL_ERROR,
1357 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1358 p_table_name => 'GMD_GRADES',
1359 p_context => 'GRADES',
1360 p_param1 => NULL,
1361 p_param2 => NULL,
1362 p_param3 => NULL,
1363 p_param4 => NULL,
1364 p_param5 => NULL,
1365 p_db_error => SQLERRM,
1366 p_app_short_name => 'GMA');
1367 END;
1368
1369 /*====================================================================
1370 -- PROCEDURE:
1371 -- migrate_odm_grades
1372 --
1373 -- DESCRIPTION:
1374 -- This PL/SQL procedure is used to migrate the Discrete grades
1375 -- to Oracle Inventory for the convergence project.
1376 --
1377 -- PARAMETERS:
1378 -- P_migration_run_id - id to use to right to migration log
1379 -- p_commit - flag to indicate if commit shouldbe performed.
1380 -- x_failure_count - Number of exceptions occurred.
1381 --
1382 -- SYNOPSIS:
1383 -- migrate_odm_grades(p_migartion_id => l_migration_id,
1384 -- p_commit => l_commit ,
1385 -- x_exception_count => l_exception_count );
1386 --
1387 -- HISTORY
1388 -- Jatinder Gogna - Created - 03/25/05
1389 --====================================================================*/
1390
1391 PROCEDURE migrate_odm_grades
1392 ( p_migration_run_id IN NUMBER
1393 , p_commit IN VARCHAR2
1394 , x_failure_count OUT NOCOPY NUMBER) IS
1395
1396 l_rowid ROWID;
1397 l_disable_flag VARCHAR2(1);
1398 e_already_exists EXCEPTION;
1399 l_count PLS_INTEGER;
1400 l_migrate_count PLS_INTEGER;
1401
1402 CURSOR c_lot_grades IS
1403 SELECT distinct grade_code FROM mtl_lot_numbers
1404 WHERE grade_code is not NULL;
1405
1406 BEGIN
1407 x_failure_count := 0;
1408 l_migrate_count := 0;
1409 -- dbms_output.put_line ('Started GRADES migration');
1410 GMA_COMMON_LOGGING.gma_migration_central_log (
1411 p_run_id => p_migration_run_id,
1412 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1413 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
1414 p_table_name => 'MTL_LOT_NUMBERS',
1415 p_context => 'GRADES',
1416 p_param1 => NULL,
1417 p_param2 => NULL,
1418 p_param3 => NULL,
1419 p_param4 => NULL,
1420 p_param5 => NULL,
1421 p_db_error => NULL,
1422 p_app_short_name => 'GMA');
1423 FOR g in c_lot_grades LOOP
1424 BEGIN
1425
1426 SELECT count(*)
1427 INTO l_count
1428 FROM mtl_grades_b
1429 WHERE grade_code = g.grade_code;
1430
1431 IF (l_count > 0) THEN
1432 raise e_already_exists;
1433 END IF;
1434
1435 MTL_GRADES_PVT.INSERT_ROW (
1436 X_ROWID => l_rowid,
1437 X_GRADE_CODE => g.grade_code,
1438 X_DESCRIPTION => g.grade_code,
1439 X_DISABLE_FLAG => 'N',
1440 X_ATTRIBUTE1 => NULL,
1441 X_ATTRIBUTE2 => NULL,
1442 X_ATTRIBUTE3 => NULL,
1443 X_ATTRIBUTE4 => NULL,
1444 X_ATTRIBUTE5 => NULL,
1445 X_ATTRIBUTE6 => NULL,
1446 X_ATTRIBUTE7 => NULL,
1447 X_ATTRIBUTE8 => NULL,
1448 X_ATTRIBUTE9 => NULL,
1449 X_ATTRIBUTE10 => NULL,
1450 X_ATTRIBUTE11 => NULL,
1451 X_ATTRIBUTE12 => NULL,
1452 X_ATTRIBUTE13 => NULL,
1453 X_ATTRIBUTE14 => NULL,
1454 X_ATTRIBUTE15 => NULL,
1455 X_ATTRIBUTE16 => NULL,
1456 X_ATTRIBUTE17 => NULL,
1457 X_ATTRIBUTE18 => NULL,
1458 X_ATTRIBUTE19 => NULL,
1459 X_ATTRIBUTE20 => NULL,
1460 X_ATTRIBUTE21 => NULL,
1461 X_ATTRIBUTE22 => NULL,
1462 X_ATTRIBUTE23 => NULL,
1463 X_ATTRIBUTE24 => NULL,
1464 X_ATTRIBUTE25 => NULL,
1465 X_ATTRIBUTE26 => NULL,
1466 X_ATTRIBUTE27 => NULL,
1467 X_ATTRIBUTE28 => NULL,
1468 X_ATTRIBUTE29 => NULL,
1469 X_ATTRIBUTE30 => NULL,
1470 X_ATTRIBUTE_CATEGORY => NULL,
1471 X_CREATION_DATE => sysdate,
1472 X_CREATED_BY => 0,
1473 X_LAST_UPDATE_DATE => sysdate,
1474 X_LAST_UPDATED_BY => 0,
1475 X_LAST_UPDATE_LOGIN => NULL);
1476
1477
1478 IF (p_commit <> FND_API.G_FALSE) THEN
1479 COMMIT;
1480 END IF;
1481 l_migrate_count := l_migrate_count + 1;
1482 EXCEPTION
1483 WHEN e_already_exists THEN
1484 NULL;
1485 END;
1486 END LOOP;
1487
1488 -- dbms_output.put_line ('Completed GRADES migration. Migrated = '||to_char(l_migrate_count)||', Failed = '||to_char(x_failure_count));
1489 GMA_COMMON_LOGGING.gma_migration_central_log (
1490 p_run_id => p_migration_run_id,
1491 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1492 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
1493 p_table_name => 'MTL_LOT_NUMBERS',
1494 p_context => 'GRADES',
1495 p_param1 => NULL,
1496 p_param2 => NULL,
1497 p_param3 => NULL,
1498 p_param4 => NULL,
1499 p_param5 => NULL,
1500 p_db_error => NULL,
1501 p_app_short_name => 'GMA');
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 x_failure_count := x_failure_count + 1;
1505 -- dbms_output.put_line (substr(SQLERRM,1,255));
1506 GMA_COMMON_LOGGING.gma_migration_central_log (
1507 p_run_id => p_migration_run_id,
1508 p_log_level => FND_LOG.LEVEL_ERROR,
1509 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1510 p_table_name => 'MTL_LOT_NUMBERS',
1511 p_context => 'GRADES',
1512 p_param1 => NULL,
1513 p_param2 => NULL,
1514 p_param3 => NULL,
1515 p_param4 => NULL,
1516 p_param5 => NULL,
1517 p_db_error => SQLERRM,
1518 p_app_short_name => 'GMA');
1519 END;
1520
1521 /*====================================================================
1522 -- PROCEDURE:
1523 -- migrate_lot_conversions
1524 --
1525 -- DESCRIPTION:
1526 -- This PL/SQL procedure is used to migrate the Lot conversions
1527 -- to Oracle Inventory for the convergence project.
1528 --
1529 -- PARAMETERS:
1530 -- P_migration_run_id - id to use to right to migration log
1531 -- p_commit - flag to indicate if commit shouldbe performed.
1532 -- x_failure_count - Number of exceptions occurred.
1533 --
1534 -- SYNOPSIS:
1535 -- migrate_lot_conversions(p_migartion_id => l_migration_id,
1536 -- p_commit => l_commit ,
1537 -- x_exception_count => l_exception_count );
1538 --
1539 -- HISTORY
1540 -- Jatinder Gogna - Created - 03/25/05
1541 --====================================================================*/
1542
1543 PROCEDURE migrate_lot_conversions
1544 ( p_migration_run_id IN NUMBER
1545 , p_commit IN VARCHAR2
1546 , p_start_rowid IN ROWID
1547 , p_end_rowid IN ROWID
1548 , x_failure_count OUT NOCOPY NUMBER) IS
1549
1550 l_rowid ROWID;
1551 l_disable_flag VARCHAR2(1);
1552 e_already_exists EXCEPTION;
1553 l_count PLS_INTEGER;
1554 l_inventory_item_id NUMBER;
1555 l_failure_count PLS_INTEGER;
1556 l_migrate_count PLS_INTEGER;
1557 l_from_unit_of_measure VARCHAR2(25);
1558 l_from_uom_code VARCHAR2(3);
1559 l_from_uom_class VARCHAR2(10);
1560 l_to_unit_of_measure VARCHAR2(25);
1561 l_to_uom_code VARCHAR2(3);
1562 l_field_name VARCHAR(50);
1563 l_field_value VARCHAR(50);
1564 l_conversion_id NUMBER;
1565 l_conv_audit_id NUMBER;
1566 l_conv_audit_detail_id NUMBER;
1567 l_reason_id NUMBER;
1568 l_organization_id NUMBER;
1569 l_locator_id NUMBER;
1570 l_subinventory_ind_flag VARCHAR2(1);
1571 l_migrated_ind PLS_INTEGER;
1572
1573 CURSOR c_ic_item_cnv IS
1574 SELECT m.parent_lot_number, m.lot_number, m.organization_id, c.*
1575 FROM ic_item_cnv c, ic_lots_mst_mig m
1576 WHERE
1577 c.rowid BETWEEN p_start_rowid AND p_end_rowid and
1578 c.item_id = m.item_id and
1579 c.lot_id = m.lot_id and
1580 nvl(m.migrated_ind,0) = 1 and
1581 m.conv_migrated_ind is NULL;
1582
1583
1584 CURSOR c_gmi_item_conv_audit
1585 (pconversion_id NUMBER) IS
1586 SELECT *
1587 FROM gmi_item_conv_audit
1588 WHERE conversion_id = pconversion_id;
1589
1590 CURSOR c_gmi_item_conv_audit_details
1591 (pconv_audit_id NUMBER) IS
1592 SELECT *
1593 FROM gmi_item_conv_audit_details
1594 WHERE conv_audit_id = pconv_audit_id;
1595 BEGIN
1596 x_failure_count := 0;
1597 l_migrate_count := 0;
1598 -- dbms_output.put_line ('Started LOT CONVERSION migration');
1599 GMA_COMMON_LOGGING.gma_migration_central_log (
1600 p_run_id => p_migration_run_id,
1601 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1602 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
1603 p_table_name => 'IC_LOTS_CNV',
1604 p_context => 'LOT CONVERSION',
1605 p_param1 => NULL,
1606 p_param2 => NULL,
1607 p_param3 => NULL,
1608 p_param4 => NULL,
1609 p_param5 => NULL,
1610 p_db_error => NULL,
1611 p_app_short_name => 'GMA');
1612
1613 FOR c in c_ic_item_cnv LOOP
1614 BEGIN
1615 -- Get the discrete item id
1616 INV_OPM_Item_Migration.get_ODM_item (
1617 p_migration_run_id => p_migration_run_id,
1618 p_item_id => c.item_id,
1619 p_organization_id => c.organization_id,
1620 p_mode => NULL,
1621 p_commit => 'Y',
1622 x_inventory_item_id => l_inventory_item_id,
1623 x_failure_count => l_failure_count);
1624 IF (l_failure_count > 0) THEN
1625 -- Log Error
1626 -- dbms_output.put_line ('Failed to get discrete item. Item id :'||to_char(c.item_id));
1627 GMA_COMMON_LOGGING.gma_migration_central_log (
1628 p_run_id => p_migration_run_id,
1629 p_log_level => FND_LOG.LEVEL_ERROR,
1630 p_message_token => 'GMI_MIG_ITEM_MIG_FAILED',
1631 p_table_name => 'IC_ITEM_CNV',
1632 p_context => 'LOT CONVERSION',
1633 p_param1 => INV_GMI_Migration.org(c.organization_id),
1634 p_param2 => INV_GMI_Migration.item(c.item_id),
1635 p_param3 => NULL,
1636 p_param4 => NULL,
1637 p_param5 => NULL,
1638 p_db_error => NULL,
1639 p_app_short_name => 'GMI');
1640 raise FND_API.G_EXC_ERROR;
1641 END IF;
1642
1643 l_field_name := 'From UOM data';
1644 l_field_value := 'Organization = '||to_char(c.organization_id)||', Item Id = '||to_char(l_inventory_item_id);
1645 SELECT bu.unit_of_measure,
1646 bu.uom_code,
1647 bu.uom_class
1648 INTO l_from_unit_of_measure,
1649 l_from_uom_code,
1650 l_from_uom_class
1651 FROM mtl_system_items_b i,
1652 mtl_units_of_measure iu,
1653 mtl_units_of_measure bu
1654 WHERE
1655 i.organization_id = c.organization_id
1656 AND i.inventory_item_id = l_inventory_item_id
1657 AND i.primary_uom_code = iu.uom_Code
1658 AND iu.uom_class = bu.uom_class
1659 AND bu.base_uom_flag = 'Y';
1660
1661 l_field_name := 'To UOM data';
1662 l_field_value := 'UM Type = '||c.um_type;
1663 SELECT unit_of_measure, uom_code
1664 INTO l_to_unit_of_measure, l_to_uom_code
1665 FROM mtl_units_of_measure
1666 WHERE
1667 uom_class = c.um_type AND
1668 base_uom_flag = 'Y';
1669
1670 -- Check if the conversion already exists
1671 SELECT count(*)
1672 INTO l_count
1673 FROM mtl_lot_uom_class_conversions
1674 WHERE
1675 organization_id = c.organization_id AND
1676 inventory_item_id = l_inventory_item_id AND
1677 lot_number = c.lot_number AND
1678 from_uom_class = l_from_uom_class AND
1679 to_uom_class = c.um_type;
1680
1681 IF (l_count > 0) THEN
1682 -- No migration needed, skip it.
1683 -- dbms_output.put_line ('Lot conversion already exist. org_id, inventory_item_id, lot num, from class, to class'||to_char(c.organization_id)||', '||to_char(l_inventory_item_id)||', '||c.lot_number||', '||l_from_uom_class||', '||c.um_type);
1684 GMA_COMMON_LOGGING.gma_migration_central_log (
1685 p_run_id => p_migration_run_id,
1686 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1687 p_message_token => 'GMI_MIG_LOT_CONV_EXISTS',
1688 p_table_name => 'IC_ITEM_CNV',
1689 p_context => 'LOT CONVERSIONS',
1690 p_param1 => INV_GMI_Migration.org(c.organization_id),
1691 p_param2 => INV_GMI_Migration.ditem(c.organization_id, l_inventory_item_id),
1692 p_param3 => c.lot_number,
1693 p_param4 => l_from_uom_class,
1694 p_param5 => c.um_type,
1695 p_db_error => NULL,
1696 p_app_short_name => 'GMI');
1697 raise FND_API.G_EXC_ERROR;
1698 END IF;
1699
1700 SELECT MTL_CONVERSION_ID_S.NEXTVAL INTO l_conversion_id FROM DUAL;
1701
1702 INSERT INTO mtl_lot_uom_class_conversions(
1703 conversion_id,
1704 lot_number,
1705 organization_id,
1706 inventory_item_id,
1707 from_unit_of_measure,
1708 from_uom_code,
1709 from_uom_class,
1710 to_unit_of_measure,
1711 to_uom_code,
1712 to_uom_class,
1713 conversion_rate,
1714 disable_date,
1715 event_spec_disp_id,
1716 created_by,
1717 creation_date,
1718 last_updated_by,
1719 last_update_date,
1720 last_update_login)
1721 VALUES(
1722 l_conversion_id,
1723 c.lot_number,
1724 c.organization_id,
1725 l_inventory_item_id,
1726 l_from_unit_of_measure,
1727 l_from_uom_code,
1728 l_from_uom_class,
1729 l_to_unit_of_measure,
1730 l_to_uom_code,
1731 c.um_type,
1732 c.type_factor,
1733 DECODE (c.delete_mark, 1, c.last_update_date, NULL),
1734 c.event_spec_disp_id,
1735 c.created_by,
1736 c.creation_date,
1737 c.last_updated_by,
1738 c.last_update_date,
1739 c.last_update_login
1740 );
1741
1742 FOR cuadit in c_gmi_item_conv_audit(c.conversion_id) LOOP
1743
1744 SELECT MTL_CONV_AUDIT_ID_S.NEXTVAL
1745 INTO l_conv_audit_id FROM DUAL;
1746
1747 l_field_name := 'Reason Id';
1748 l_field_value := 'Reason Code = ' || cuadit.reason_code;
1749
1750 IF cuadit.reason_code IS NOT NULL THEN
1751 SELECT reason_id INTO l_reason_id
1752 FROM sy_reas_cds_b
1753 WHERE
1754 reason_code = cuadit.reason_code;
1755 END IF;
1756
1757 -- Jatinder - 12/18/06- Bug 5722698. Added NVL to the
1758 -- UPDATE_BATCH_INDICATOR column.
1759 INSERT INTO mtl_lot_conv_audit(
1760 conv_audit_id,
1761 conversion_id,
1762 conversion_date,
1763 update_type_indicator,
1764 batch_id,
1765 reason_id,
1766 old_conversion_rate,
1767 new_conversion_rate,
1768 event_spec_disp_id,
1769 created_by,
1770 creation_date,
1771 last_updated_by,
1772 last_update_date
1773 )VALUES(
1774 l_conv_audit_id,
1775 l_conversion_id,
1776 cuadit.conversion_date,
1777 NVL(cuadit.update_batch_indicator, 0),
1778 cuadit.batch_id,
1779 l_reason_id,
1780 cuadit.old_type_factor,
1781 cuadit.new_type_factor,
1782 cuadit.event_spec_disp_id,
1783 cuadit.created_by,
1784 cuadit.creation_date,
1785 cuadit.last_updated_by,
1786 cuadit.last_update_date);
1787
1788 FOR adetail in c_gmi_item_conv_audit_details (cuadit.conv_audit_id) LOOP
1789
1790 -- Get the organization / subinventory for the warehouse
1791 l_field_name := 'Organization/ Subinventory';
1792 l_field_value := 'Warehouse = '||adetail.whse_code;
1793 SELECT organization_id, subinventory_ind_flag, migrated_ind
1794 INTO l_organization_id, l_subinventory_ind_flag, l_migrated_ind
1795 FROM ic_whse_mst
1796 WHERE
1797 whse_code = adetail.whse_code;
1798 IF (nvl(l_migrated_ind,0) <> 1) THEN
1799 -- dbms_output.put_line ('Warehouse not mmigrated: '||adetail.whse_code);
1800 GMA_COMMON_LOGGING.gma_migration_central_log (
1801 p_run_id => p_migration_run_id,
1802 p_log_level => FND_LOG.LEVEL_ERROR,
1803 p_message_token => 'GMI_MIG_WHSE_NOT_MIGRATED',
1804 p_table_name => 'IC_ITEM_CNV',
1805 p_context => 'LOT CONVERSION',
1806 p_param1 => adetail.whse_code,
1807 p_param2 => NULL,
1808 p_param3 => NULL,
1809 p_param4 => NULL,
1810 p_param5 => NULL,
1811 p_db_error => NULL,
1812 p_app_short_name => 'GMI');
1813 RAISE FND_API.G_EXC_ERROR; -- Skip this conversion
1814 END IF;
1815
1816 -- Get the Locator id
1817 l_field_name := 'Loctor Id';
1818 l_field_value := 'Warehouse/ Location =' ||adetail.whse_code
1819 ||'/ '||adetail.location;
1820 -- Jatinder - 12/1/06 - initialize the locator_id - 5692408
1821 l_locator_id := NULL;
1822 IF (G_DEFAULT_LOCT is NULL) THEN
1823 SELECT fnd_profile.value ('IC$DEFAULT_LOCT')
1824 INTO G_DEFAULT_LOCT
1825 FROM dual;
1826 END IF;
1827 IF (adetail.location <> G_DEFAULT_LOCT) THEN
1828 BEGIN
1829 SELECT locator_id INTO l_locator_id
1830 FROM ic_loct_mst
1831 WHERE
1832 whse_code = adetail.whse_code AND
1833 location = adetail.location;
1834
1835 IF (l_locator_id is NULL) THEN
1836 -- dbms_output.put_line ('Location not migrated for whse, loc :'||adetail.whse_code||', '||adetail.location);
1837 GMA_COMMON_LOGGING.gma_migration_central_log (
1838 p_run_id => p_migration_run_id,
1839 p_log_level => FND_LOG.LEVEL_ERROR,
1840 p_message_token => 'GMI_MIG_LOC_NOT_MIGRATED',
1841 p_table_name => 'IC_ITEM_CNV',
1842 p_context => 'LOT CONVERSION',
1843 p_param1 => adetail.whse_code,
1844 p_param2 => adetail.location,
1845 p_param3 => NULL,
1846 p_param4 => NULL,
1847 p_param5 => NULL,
1848 p_db_error => NULL,
1849 p_app_short_name => 'GMI');
1850 RAISE FND_API.G_EXC_ERROR; -- Skip this conversion
1851 END IF;
1852 EXCEPTION
1853 WHEN NO_DATA_FOUND THEN
1854 -- Create locator in discrete ( dynamic locator)
1855 l_failure_count := 0;
1856 inv_migrate_process_org.create_location(
1857 p_migration_run_id => p_migration_run_id,
1858 p_organization_id => l_organization_id,
1859 p_subinventory_code => adetail.whse_code,
1860 p_location => adetail.location,
1861 p_loct_desc => adetail.location,
1862 p_start_date_active => adetail.creation_date,
1863 p_commit => FND_API.G_TRUE,
1864 x_location_id => l_locator_id,
1865 x_failure_count => l_failure_count,
1866 p_segment2 => NULL,
1867 p_segment3 => NULL,
1868 p_segment4 => NULL,
1869 p_segment5 => NULL,
1870 p_segment6 => NULL,
1871 p_segment7 => NULL,
1872 p_segment8 => NULL,
1873 p_segment9 => NULL,
1874 p_segment10 => NULL,
1875 p_segment11 => NULL,
1876 p_segment12 => NULL,
1877 p_segment13 => NULL,
1878 p_segment14 => NULL,
1879 p_segment15 => NULL,
1880 p_segment16 => NULL,
1881 p_segment17 => NULL,
1882 p_segment18 => NULL,
1883 p_segment19 => NULL,
1884 p_segment20 => NULL);
1885
1886 IF (l_failure_count > 0) THEN
1887 -- Log error
1888 -- dbms_output.put_line ( 'Unable to create the locator for dynamic OPM location :' || adetail.whse_code ||', '||adetail.location );
1889 GMA_COMMON_LOGGING.gma_migration_central_log (
1890 p_run_id => p_migration_run_id,
1891 p_log_level => FND_LOG.LEVEL_ERROR,
1892 p_message_token => 'GMI_LOC_CREATION_FAILED',
1893 p_table_name => 'IC_LOCT_INV',
1894 p_context => 'INVENTORY BALANCE',
1895 p_param1 => adetail.whse_code,
1896 p_param2 => adetail.location,
1897 p_param3 => NULL,
1898 p_param4 => NULL,
1899 p_param5 => NULL,
1900 p_db_error => NULL,
1901 p_app_short_name => 'GMI');
1902 raise FND_API.G_EXC_ERROR;
1903 END IF;
1904 END;
1905 END IF;
1906
1907 SELECT MTL_CONV_AUDIT_DETAIL_ID_S.NEXTVAL
1908 INTO l_conv_audit_detail_id FROM DUAL;
1909 INSERT INTO mtl_lot_conv_audit_details(
1910 conv_audit_detail_id,
1911 conv_audit_id,
1912 revision,
1913 organization_id,
1914 subinventory_code,
1915 lpn_id,
1916 locator_id,
1917 old_primary_qty,
1918 old_secondary_qty,
1919 new_primary_qty,
1920 new_secondary_qty,
1921 transaction_primary_qty,
1922 transaction_secondary_qty,
1923 transaction_update_flag,
1924 created_by,
1925 creation_date,
1926 last_updated_by,
1927 last_update_date
1928 )VALUES(
1929 l_conv_audit_detail_id,
1930 l_conv_audit_id,
1931 NULL,
1932 l_organization_id,
1933 adetail.whse_code,
1934 NULL,
1935 l_locator_id,
1936 adetail.old_onhand_qty,
1937 adetail.old_onhand_qty2,
1938 adetail.new_onhand_qty,
1939 adetail.new_onhand_qty2,
1940 adetail.trans_qty,
1941 adetail.trans_qty2,
1942 adetail.trans_update_flag,
1943 adetail.created_by,
1944 adetail.creation_date,
1945 adetail.last_updated_by,
1946 adetail.last_update_date
1947 );
1948 END LOOP;
1949 END LOOP;
1950 UPDATE ic_lots_mst_mig
1951 SET
1952 conv_migrated_ind = 1,
1953 last_update_date = sysdate,
1954 last_updated_by = 0
1955 WHERE
1956 item_id = c.item_id AND
1957 organization_id = c.organization_id AND
1958 lot_number = c.lot_number;
1959
1960 IF (p_commit <> FND_API.G_FALSE) THEN
1961 COMMIT;
1962 END IF;
1963 l_migrate_count := l_migrate_count + 1;
1964 EXCEPTION
1965 WHEN NO_DATA_FOUND THEN
1966 -- dbms_output.put_line ('Cannot find '||l_field_name||' for '||l_field_value);
1967 ROLLBACK;
1968 GMA_COMMON_LOGGING.gma_migration_central_log (
1969 p_run_id => p_migration_run_id,
1970 p_log_level => FND_LOG.LEVEL_ERROR,
1971 p_message_token => 'GMI_MIG_NO_DATA_FOR_FIELD',
1972 p_table_name => 'IC_ITEM_CNV',
1973 p_context => 'LOT CONVERSION',
1974 p_param1 => l_field_name,
1975 p_param2 => l_field_value,
1976 p_param3 => NULL,
1977 p_param4 => NULL,
1978 p_param5 => NULL,
1979 p_db_error => NULL,
1980 p_app_short_name => 'GMI');
1981 x_failure_count := x_failure_count + 1;
1982 WHEN FND_API.G_EXC_ERROR THEN
1983 ROLLBACK;
1984 x_failure_count := x_failure_count + 1;
1985 NULL; -- Skip to the next row.
1986 END;
1987 END LOOP;
1988
1989 -- dbms_output.put_line ('Completed LOT CONVERSION migration. Migrated = '||to_char(l_migrate_count)||', Failed = '||to_char(x_failure_count));
1990 GMA_COMMON_LOGGING.gma_migration_central_log (
1991 p_run_id => p_migration_run_id,
1992 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1993 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
1994 p_table_name => 'IC_ITEM_CNV',
1995 p_context => 'LOT CONVERSION',
1996 p_param1 => l_migrate_count,
1997 p_param2 => x_failure_count,
1998 p_param3 => NULL,
1999 p_param4 => NULL,
2000 p_param5 => NULL,
2001 p_db_error => NULL,
2002 p_app_short_name => 'GMA');
2003 EXCEPTION
2004 WHEN OTHERS THEN
2005 ROLLBACK;
2006 x_failure_count := x_failure_count + 1;
2007 -- dbms_output.put_line (substr(SQLERRM,1,255));
2008 GMA_COMMON_LOGGING.gma_migration_central_log (
2009 p_run_id => p_migration_run_id,
2010 p_log_level => FND_LOG.LEVEL_ERROR,
2011 p_message_token => 'GMA_MIGRATION_DB_ERROR',
2012 p_table_name => 'IC_ITEM_CNV',
2013 p_context => 'LOT CONVERSION',
2014 p_param1 => NULL,
2015 p_param2 => NULL,
2016 p_param3 => NULL,
2017 p_param4 => NULL,
2018 p_param5 => NULL,
2019 p_db_error => SQLERRM,
2020 p_app_short_name => 'GMA');
2021 END;
2022
2023 /*====================================================================
2024 -- PROCEDURE:
2025 -- init_doc_seq
2026 --
2027 -- DESCRIPTION:
2028 -- This PL/SQL procedure will insert doc sequence record, if needed
2029 -- The procedure is autonomous, becuase the transfer API has autonomous
2030 -- routine to get the doc_no
2031 --
2032 -- PARAMETERS:
2033 -- p_orgn_code - organization of the doc sequence.
2034 --
2035 -- SYNOPSIS:
2036 -- init_doc_seq (p_orgn_code => l_orgn_code);
2037 --
2038 -- HISTORY
2039 -- Jatinder Gogna - Created - 03/25/05
2040 --====================================================================*/
2041
2042 PROCEDURE init_doc_seq ( p_orgn_code IN VARCHAR2) IS
2043 PRAGMA AUTONOMOUS_TRANSACTION;
2044
2045 l_count PLS_INTEGER;
2046 l_rowid ROWID;
2047 BEGIN
2048 INSERT INTO sy_docs_seq(
2049 doc_type,
2050 orgn_code,
2051 assignment_type,
2052 last_assigned,
2053 format_size,
2054 pad_char,
2055 delete_mark,
2056 creation_date,
2057 created_by,
2058 last_update_date,
2059 last_updated_by,
2060 trans_cnt)
2061 SELECT
2062 'DXFR',
2063 p_orgn_code,
2064 2,
2065 0,
2066 6,
2067 0,
2068 0,
2069 sysdate,
2070 0,
2071 sysdate,
2072 0,
2073 0
2074 FROM dual
2075 WHERE
2076 NOT EXISTS (
2077 SELECT 1
2078 FROM sy_docs_seq
2079 WHERE
2080 doc_type = 'DXFR' AND
2081 orgn_code = p_orgn_code);
2082
2083
2084 SELECT count(*)
2085 INTO l_count
2086 FROM sy_reas_cds
2087 WHERE
2088 reason_code = 'CNVM';
2089
2090 IF l_count = 0 THEN
2091 sy_reas_cds_pkg.insert_row (
2092 x_rowid => l_rowid,
2093 x_reason_code => 'CNVM',
2094 x_reason_desc2 => NULL,
2095 x_reason_type => 0,
2096 x_flow_type => 0,
2097 x_auth_string => NULL,
2098 x_delete_mark => 0,
2099 x_text_code => NULL,
2100 x_trans_cnt => 0,
2101 x_reason_desc1 => 'OPM Convergence Migration',
2102 x_creation_date => sysdate,
2103 x_created_by => 0,
2104 x_last_update_date => sysdate,
2105 x_last_updated_by => 0,
2106 x_last_update_login => NULL);
2107
2108 UPDATE sy_reas_cds_b
2109 SET reason_id = -99
2110 WHERE reason_code = 'CNVM';
2111 END IF;
2112 COMMIT;
2113 END;
2114 /*====================================================================
2115 -- PROCEDURE:
2116 -- migrate_inventory_balances
2117 --
2118 -- DESCRIPTION:
2119 -- This PL/SQL procedure is used to migrate the Inventory Balances
2120 -- to Oracle Inventory for the convergence project.
2121 --
2122 -- PARAMETERS:
2123 -- P_migration_run_id - id to use to right to migration log
2124 -- p_commit - flag to indicate if commit shouldbe performed.
2125 -- x_failure_count - Number of exceptions occurred.
2126 --
2127 -- SYNOPSIS:
2128 -- migrate_inventory_balances(p_migartion_id => l_migration_id,
2129 -- p_commit => l_commit ,
2130 -- x_exception_count => l_exception_count );
2131 --
2132 -- HISTORY
2133 -- Jatinder Gogna - Created - 03/25/05
2134 --====================================================================*/
2135
2136 PROCEDURE migrate_inventory_balances
2137 ( p_migration_run_id IN NUMBER
2138 , p_commit IN VARCHAR2
2139 , x_failure_count OUT NOCOPY NUMBER) IS
2140
2141 l_prev_whse_code VARCHAR2(4);
2142 l_prev_organization_id NUMBER;
2143 l_skip_to_next_whse PLS_INTEGER;
2144 l_migrate_count PLS_INTEGER;
2145 l_orgn_code VARCHAR2(4);
2146 l_co_code VARCHAR2(4);
2147 l_subinventory_ind_flag VARCHAR2(1);
2148 l_whse_loct_ctl PLS_INTEGER;
2149 l_item_loct_ctl PLS_INTEGER;
2150 l_lot_ctl PLS_INTEGER;
2151 l_noninv_ind PLS_INTEGER;
2152 l_item_um VARCHAR2(4);
2153 l_item_no VARCHAR2(32);
2154 l_organization_id NUMBER;
2155 l_inventory_item_id NUMBER;
2156 l_subinventory_code VARCHAR2(10);
2157 l_locator_id NUMBER;
2158 l_migrated_ind PLS_INTEGER;
2159 l_last_updated_by NUMBER;
2160 l_field_name VARCHAR(50);
2161 l_field_value VARCHAR(50);
2162 l_period_id INTEGER;
2163 l_open_past_period BOOLEAN;
2164 l_period_set_name VARCHAR2(15);
2165 l_accounted_period_type VARCHAR2(15);
2166 l_period_name VARCHAR2(15);
2167 l_period_year NUMBER;
2168 l_period_number NUMBER;
2169 l_period_end_date DATE;
2170 l_prior_period_open BOOLEAN;
2171 l_new_acct_period_id NUMBER;
2172 l_last_scheduled_close_date DATE;
2173 l_duplicate_open_period BOOLEAN;
2174 l_commit_complete BOOLEAN;
2175 l_recs_displayed NUMBER;
2176 l_failure_count NUMBER;
2177 l_return_status VARCHAR2(1);
2178 l_msg_count NUMBER;
2179 l_msg_data VARCHAR2(1000);
2180 l_hdr_rec gmivdx.hdr_type;
2181 l_line_rec_tbl gmivdx.line_type_tbl;
2182 l_lot_rec_tbl gmivdx.lot_type_tbl;
2183 o_hdr_row gmi_discrete_transfers%ROWTYPE;
2184 o_line_row_tbl gmivdx.line_row_tbl;
2185 o_lot_row_tbl gmivdx.lot_row_tbl;
2186 l_transaction_set_id NUMBER;
2187 l_lot_number VARCHAR2(80);
2188 l_parent_lot_number VARCHAR2(80);
2189 l_count PLS_INTEGER;
2190
2191
2192 e_skip_whse EXCEPTION;
2193
2194 CURSOR c_ic_loct_inv IS
2195 SELECT rowid, l.*
2196 FROM ic_loct_inv l
2197 WHERE migrated_ind is NULL AND
2198 ROUND(loct_onhand, 5) <> 0
2199 ORDER by whse_code;
2200
2201 -- Get the subinventory for the warehouse
2202 CURSOR c_subinventory (p_whse_code VARCHAR2) IS
2203 SELECT subinventory_code, count(*)
2204 FROM ic_loct_mst o, mtl_item_locations d
2205 WHERE o.locator_id = d.inventory_location_id AND
2206 o.whse_code = p_whse_code
2207 GROUP BY whse_code, subinventory_code
2208 ORDER by 2 desc;
2209
2210 -- Get the current period info
2211 CURSOR c_future_periods IS
2212 SELECT period_name, period_year,
2213 period_number , end_date
2214 INTO l_period_name, l_period_year,
2215 l_period_number, l_period_end_date
2216 FROM org_acct_periods_v
2217 WHERE rec_type = 'GL_PERIOD' AND
2218 period_set_name = l_period_set_name AND
2219 accounted_period_type = l_accounted_period_type AND
2220 end_date > l_last_scheduled_close_date AND
2221 start_date < sysdate;
2222 BEGIN
2223 x_failure_count:= 0;
2224 l_migrate_count := 0;
2225 l_skip_to_next_whse := 0;
2226
2227 -- dbms_output.put_line ('Started INVENTORY BALANCE migration');
2228 GMA_COMMON_LOGGING.gma_migration_central_log (
2229 p_run_id => p_migration_run_id,
2230 p_log_level => FND_LOG.LEVEL_ERROR,
2231 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
2232 p_table_name => 'IC_LOCT_INV',
2233 p_context => 'INVENTORY BALANCE',
2234 p_param1 => NULL,
2235 p_param2 => NULL,
2236 p_param3 => NULL,
2237 p_param4 => NULL,
2238 p_param5 => NULL,
2239 p_db_error => NULL,
2240 p_app_short_name => 'GMA');
2241
2242 FOR bal in c_ic_loct_inv LOOP
2243 BEGIN
2244 -- SAVEPOINT bal_migration_start;
2245
2246 IF (bal.whse_code = l_prev_whse_code and l_skip_to_next_whse = 1) THEN
2247 RAISE e_skip_whse;
2248 END IF;
2249
2250 IF (bal.whse_code <> l_prev_whse_code or l_prev_whse_code IS NULL) THEN
2251 -- New warehouse, reset some of the values
2252 l_skip_to_next_whse := 0;
2253 l_prev_whse_code := bal.whse_code;
2254
2255 -- Check if warehouse has been migrated
2256 SELECT orgn_code, subinventory_ind_flag, loct_ctl,
2257 organization_id, migrated_ind, last_updated_by
2258 INTO l_orgn_code, l_subinventory_ind_flag, l_whse_loct_ctl,
2259 l_organization_id, l_migrated_ind, l_last_updated_by
2260 FROM ic_whse_mst
2261 WHERE
2262 whse_code = bal.whse_code;
2263
2264 IF (l_migrated_ind is NULL or l_organization_id is NULL) THEN
2265 -- dbms_output.put_line ('Warehouse not migrated :'||bal.whse_code);
2266 GMA_COMMON_LOGGING.gma_migration_central_log (
2267 p_run_id => p_migration_run_id,
2268 p_log_level => FND_LOG.LEVEL_ERROR,
2269 p_message_token => 'GMI_MIG_WHSE_NOT_MIGRATED',
2270 p_table_name => 'IC_LOCT_INV',
2271 p_context => 'INVENTORY BALANCE',
2272 p_param1 => bal.whse_code,
2273 p_param2 => NULL,
2274 p_param3 => NULL,
2275 p_param4 => NULL,
2276 p_param5 => NULL,
2277 p_db_error => NULL,
2278 p_app_short_name => 'GMI');
2279 RAISE e_skip_whse; -- skip all rows for the warehouse
2280 END IF;
2281
2282 -- Check in the inventory period is open in discrete
2283 invttmtx.tdatechk (l_organization_id, trunc(sysdate), l_period_id,
2284 l_open_past_period);
2285 invttmtx.G_ORG_ID := NULL;
2286 IF (l_period_id = -1) THEN
2287 -- dbms_output.put_line ('Error retrieving open period for org :' ||to_char(l_organization_id));
2288 GMA_COMMON_LOGGING.gma_migration_central_log (
2289 p_run_id => p_migration_run_id,
2290 p_log_level => FND_LOG.LEVEL_ERROR,
2291 p_message_token => 'INV_RETRIEVE_PERIOD',
2292 p_table_name => 'IC_LOCT_INV',
2293 p_context => 'INVENTORY BALANCE',
2294 p_param1 => NULL,
2295 p_param2 => NULL,
2296 p_param3 => NULL,
2297 p_param4 => NULL,
2298 p_param5 => NULL,
2299 p_db_error => NULL,
2300 p_app_short_name => 'INV');
2301 RAISE e_skip_whse; -- skip all rows for the warehouse
2302 ELSIF (l_period_id = 0) THEN
2303 -- dbms_output.put_line ('No open period for org :' ||to_char(l_organization_id));
2304 GMA_COMMON_LOGGING.gma_migration_central_log (
2305 p_run_id => p_migration_run_id,
2306 p_log_level => FND_LOG.LEVEL_ERROR,
2307 p_message_token => 'GMI_MIG_NO_ODM_OPEN_PERIOD',
2308 p_table_name => 'IC_LOCT_INV',
2309 p_context => 'INVENTORY BALANCE',
2310 p_param1 => INV_GMI_Migration.org(l_organization_id),
2311 p_param2 => NULL,
2312 p_param3 => NULL,
2313 p_param4 => NULL,
2314 p_param5 => NULL,
2315 p_db_error => NULL,
2316 p_app_short_name => 'GMI');
2317
2318 -- Try to open the period
2319 l_field_name := 'Organization Definition';
2320 l_field_value := INV_GMI_Migration.org(l_organization_id);
2321 l_skip_to_next_whse := 1;
2322 SELECT b.period_set_name, b.accounted_period_type
2323 INTO l_period_set_name, l_accounted_period_type
2324 FROM org_organization_definitions a,
2325 gl_sets_of_books b
2326 WHERE a.organization_id = l_organization_id
2327 AND a.set_of_books_id = b.set_of_books_id;
2328 l_skip_to_next_whse := 0;
2329
2330 SELECT NVL(MAX(schedule_close_date), sysdate)
2331 INTO l_last_scheduled_close_date
2332 FROM org_acct_periods
2333 WHERE organization_id = l_organization_id;
2334
2335 -- Open all future periods till the sysdate
2336 FOR fp IN c_future_periods LOOP
2337 -- Open the period
2338 -- dbms_output.put_line ('Opening period year/num/name' || fp.period_year ||'/'||fp.period_number||'/'||fp.period_name);
2339 GMA_COMMON_LOGGING.gma_migration_central_log (
2340 p_run_id => p_migration_run_id,
2341 p_log_level => FND_LOG.LEVEL_ERROR,
2342 p_message_token => 'GMI_MIG_OPENING_ODM_PERIOD',
2343 p_table_name => 'IC_LOCT_INV',
2344 p_context => 'INVENTORY BALANCE',
2345 p_param1 => INV_GMI_Migration.org(l_organization_id),
2346 p_param2 => fp.period_year,
2347 p_param3 => fp.period_number,
2348 p_param4 => fp.period_name,
2349 p_param5 => NULL,
2350 p_db_error => NULL,
2351 p_app_short_name => 'GMI');
2352
2353 CST_AccountingPeriod_PUB.Open_Period(
2354 p_api_version => 1.0,
2355 p_org_id => l_organization_id,
2356 p_user_id => l_last_updated_by,
2357 p_login_id => NULL,
2358 p_acct_period_type => l_accounted_period_type,
2359 p_org_period_set_name => l_period_set_name,
2360 p_open_period_name => fp.period_name,
2361 p_open_period_year => fp.period_year,
2362 p_open_period_num => fp.period_number,
2363 x_last_scheduled_close_date => l_last_scheduled_close_date,
2364 p_period_end_date => fp.end_date,
2365 x_prior_period_open => l_prior_period_open,
2366 x_new_acct_period_id => l_new_acct_period_id,
2367 x_duplicate_open_period => l_duplicate_open_period,
2368 x_commit_complete => l_commit_complete,
2369 x_return_status => l_return_status );
2370
2371 IF (NOT l_prior_period_open) THEN
2372 -- dbms_output.put_line ('Prior period is not open');
2373 GMA_COMMON_LOGGING.gma_migration_central_log (
2374 p_run_id => p_migration_run_id,
2375 p_log_level => FND_LOG.LEVEL_ERROR,
2376 p_message_token => 'INV_PREV_PD_NOT_OPEN_10G',
2377 p_table_name => 'IC_LOCT_INV',
2378 p_context => 'INVENTORY BALANCE',
2379 p_param1 => NULL,
2380 p_param2 => NULL,
2381 p_param3 => NULL,
2382 p_param4 => NULL,
2383 p_param5 => NULL,
2384 p_db_error => NULL,
2385 p_app_short_name => 'INV');
2386 RAISE e_skip_whse; -- skip all rows for the warehouse
2387 ELSIF (l_new_acct_period_id = 0) THEN
2388 -- dbms_output.put_line ('Cannot get next period');
2389 GMA_COMMON_LOGGING.gma_migration_central_log (
2390 p_run_id => p_migration_run_id,
2391 p_log_level => FND_LOG.LEVEL_ERROR,
2392 p_message_token => 'INV_CANNOT_GET_NEXT_PERIOD',
2393 p_table_name => 'IC_LOCT_INV',
2394 p_context => 'INVENTORY BALANCE',
2395 p_param1 => NULL,
2396 p_param2 => NULL,
2397 p_param3 => NULL,
2398 p_param4 => NULL,
2399 p_param5 => NULL,
2400 p_db_error => NULL,
2401 p_app_short_name => 'INV');
2402 RAISE e_skip_whse; -- skip all rows for the warehouse
2403 ELSIF (l_duplicate_open_period) THEN
2404 -- dbms_output.put_line ('Period opened by another user');
2405 GMA_COMMON_LOGGING.gma_migration_central_log (
2406 p_run_id => p_migration_run_id,
2407 p_log_level => FND_LOG.LEVEL_ERROR,
2408 p_message_token => 'INV_DUPLICATE_OPEN_PERIOD',
2409 p_table_name => 'IC_LOCT_INV',
2410 p_context => 'INVENTORY BALANCE',
2411 p_param1 => NULL,
2412 p_param2 => NULL,
2413 p_param3 => NULL,
2414 p_param4 => NULL,
2415 p_param5 => NULL,
2416 p_db_error => NULL,
2417 p_app_short_name => 'INV');
2418 RAISE e_skip_whse; -- skip all rows for the warehouse
2419 ELSIF (NOT l_commit_complete) THEN
2420 -- dbms_output.put_line ('No Change made for period opening');
2421 GMA_COMMON_LOGGING.gma_migration_central_log (
2422 p_run_id => p_migration_run_id,
2423 p_log_level => FND_LOG.LEVEL_ERROR,
2424 p_message_token => 'INV_NO_CHANGES',
2425 p_table_name => 'IC_LOCT_INV',
2426 p_context => 'INVENTORY BALANCE',
2427 p_param1 => NULL,
2428 p_param2 => NULL,
2429 p_param3 => NULL,
2430 p_param4 => NULL,
2431 p_param5 => NULL,
2432 p_db_error => NULL,
2433 p_app_short_name => 'INV');
2434 RAISE e_skip_whse; -- skip all rows for the warehouse
2435 END IF;
2436 END LOOP; -- Opening ODM periods
2437 END IF; -- ODM period open check / period creation
2438
2439 -- Setup document sequencing for warehouse organization, if it is not there.
2440 init_doc_seq (l_orgn_code);
2441 END IF; -- First warehouse row
2442
2443 -- Start the balance migration
2444 -- Prepare the data for the migration
2445 -- Get the discrete item id
2446 fnd_msg_pub.initialize;
2447 INV_OPM_Item_Migration.get_ODM_item (
2448 p_migration_run_id => p_migration_run_id,
2449 p_item_id => bal.item_id,
2450 p_organization_id => l_organization_id,
2451 p_mode => '',
2452 p_commit => FND_API.G_TRUE,
2453 x_inventory_item_id => l_inventory_item_id,
2454 x_failure_count => l_failure_count);
2455 IF (l_failure_count > 0) THEN
2456 -- Log Error
2457 -- dbms_output.put_line ('Failed to migrate item,org = '||to_char(bal.item_id)||', '||to_char(l_organization_id));
2458 GMA_COMMON_LOGGING.gma_migration_central_log (
2459 p_run_id => p_migration_run_id,
2460 p_log_level => FND_LOG.LEVEL_ERROR,
2461 p_message_token => 'GMI_MIG_ITEM_MIG_FAILED',
2462 p_table_name => 'IC_LOCT_INV',
2463 p_context => 'INVENTORY BALANCE',
2464 p_param1 => INV_GMI_Migration.org(l_organization_id),
2465 p_param2 => INV_GMI_Migration.item(bal.item_id),
2466 p_param3 => NULL,
2467 p_param4 => NULL,
2468 p_param5 => NULL,
2469 p_db_error => NULL,
2470 p_app_short_name => 'GMI');
2471 raise FND_API.G_EXC_ERROR;
2472 END IF;
2473
2474 l_field_name := 'OPM Item attributes';
2475 l_field_value := 'OPM item id = '||to_char(bal.item_id);
2476
2477 SELECT loct_ctl, item_um, lot_ctl, noninv_ind, item_no
2478 INTO l_item_loct_ctl, l_item_um, l_lot_ctl, l_noninv_ind, l_item_no
2479 FROM ic_item_mst_b
2480 WHERE
2481 item_id = bal.item_id;
2482
2483 IF (l_noninv_ind = 1) THEN
2484 -- Log Error
2485 -- dbms_output.put_line ('Cannot migrate balances for non-inventory item '|| l_item_no);
2486 GMA_COMMON_LOGGING.gma_migration_central_log (
2487 p_run_id => p_migration_run_id,
2488 p_log_level => FND_LOG.LEVEL_ERROR,
2489 p_message_token => 'GMI_MIG_NONINV_ITEM',
2490 p_table_name => 'IC_LOCT_INV',
2491 p_context => 'INVENTORY BALANCE',
2492 p_param1 => l_item_no,
2493 p_param3 => NULL,
2494 p_param4 => NULL,
2495 p_param5 => NULL,
2496 p_db_error => NULL,
2497 p_app_short_name => 'GMI');
2498 raise FND_API.G_EXC_ERROR;
2499 END IF;
2500
2501 -- Get the discrete lot number
2502 l_lot_number := NULL;
2503 l_parent_lot_number := NULL;
2504
2505 IF (bal.lot_id > 0) THEN -- Lot controlled item
2506 fnd_msg_pub.initialize;
2507 INV_OPM_Lot_Migration.get_ODM_lot (
2508 p_migration_run_id => p_migration_run_id,
2509 p_item_id => bal.item_id,
2510 p_lot_id => bal.lot_id,
2511 p_whse_code => bal.whse_code,
2512 p_orgn_code => NULL,
2513 p_location => bal.location,
2514 p_commit => FND_API.G_TRUE,
2515 x_lot_number => l_lot_number,
2516 x_parent_lot_number => l_parent_lot_number,
2517 x_failure_count => l_failure_count);
2518
2519 IF (l_failure_count > 0) THEN
2520 -- Log Error
2521 -- dbms_output.put_line ('Failed to migrate lot = '||to_char(bal.lot_id)||', '||bal.whse_code||', '||bal.location);
2522 GMA_COMMON_LOGGING.gma_migration_central_log (
2523 p_run_id => p_migration_run_id,
2524 p_log_level => FND_LOG.LEVEL_ERROR,
2525 p_message_token => 'GMI_MIG_LOT_MIG_FAILED',
2526 p_table_name => 'IC_LOCT_INV',
2527 p_context => 'INVENTORY BALANCE',
2528 p_param1 => INV_GMI_Migration.item(bal.item_id),
2529 p_param2 => INV_GMI_Migration.lot(bal.lot_id),
2530 p_param3 => bal.whse_code,
2531 p_param4 => bal.location,
2532 p_param5 => NULL,
2533 p_db_error => NULL,
2534 p_app_short_name => 'GMI');
2535 raise FND_API.G_EXC_ERROR;
2536 END IF;
2537 END IF;
2538
2539 -- Get the loctor id
2540 l_field_name := 'Default Location Profile';
2541 l_field_value := NULL;
2542 l_locator_id := NULL;
2543 l_subinventory_code := NULL;
2544 IF (G_DEFAULT_LOCT is NULL) THEN
2545 SELECT fnd_profile.value ('IC$DEFAULT_LOCT')
2546 INTO G_DEFAULT_LOCT
2547 FROM dual;
2548 END IF;
2549 IF ( l_whse_loct_ctl <> 0 and l_item_loct_ctl <> 0 and
2550 bal.location <> G_DEFAULT_LOCT) THEN
2551 BEGIN
2552 SELECT ol.locator_id, dl.subinventory_code
2553 INTO l_locator_id, l_subinventory_code
2554 FROM ic_loct_mst ol, mtl_item_locations dl
2555 WHERE
2556 ol.whse_code = bal.whse_code AND
2557 ol.location = bal.location AND
2558 ol.locator_id = dl.inventory_location_id (+);
2559
2560 IF (l_locator_id is NULL) THEN
2561 -- Log error
2562 -- dbms_output.put_line ( 'Warehouse location not migrated :' || bal.whse_code ||', '||bal.location );
2563 GMA_COMMON_LOGGING.gma_migration_central_log (
2564 p_run_id => p_migration_run_id,
2565 p_log_level => FND_LOG.LEVEL_ERROR,
2566 p_message_token => 'GMI_MIG_LOC_NOT_MIGRATED',
2567 p_table_name => 'IC_LOCT_INV',
2568 p_context => 'INVENTORY BALANCE',
2569 p_param1 => bal.whse_code,
2570 p_param2 => bal.location,
2571 p_param3 => NULL,
2572 p_param4 => NULL,
2573 p_param5 => NULL,
2574 p_db_error => NULL,
2575 p_app_short_name => 'GMI');
2576 raise FND_API.G_EXC_ERROR;
2577 END IF;
2578
2579 EXCEPTION
2580 WHEN NO_DATA_FOUND THEN
2581 -- Create locator in discrete ( dynamic locator)
2582 l_failure_count := 0;
2583 -- Get the subinventory for the warehouse
2584 OPEN c_subinventory (bal.whse_code);
2585 FETCH c_subinventory INTO l_subinventory_code, l_count;
2586 CLOSE c_subinventory;
2587
2588 IF (l_subinventory_code is NULL) THEN
2589 l_subinventory_code := bal.whse_code;
2590 END IF;
2591
2592 inv_migrate_process_org.create_location(
2593 p_migration_run_id => p_migration_run_id,
2594 p_organization_id => l_organization_id,
2595 p_subinventory_code => l_subinventory_code,
2596 p_location => bal.location,
2597 p_loct_desc => bal.location,
2598 p_start_date_active => bal.creation_date,
2599 p_commit => FND_API.G_TRUE,
2600 x_location_id => l_locator_id,
2601 x_failure_count => l_failure_count,
2602 p_segment2 => NULL,
2603 p_segment3 => NULL,
2604 p_segment4 => NULL,
2605 p_segment5 => NULL,
2606 p_segment6 => NULL,
2607 p_segment7 => NULL,
2608 p_segment8 => NULL,
2609 p_segment9 => NULL,
2610 p_segment10 => NULL,
2611 p_segment11 => NULL,
2612 p_segment12 => NULL,
2613 p_segment13 => NULL,
2614 p_segment14 => NULL,
2615 p_segment15 => NULL,
2616 p_segment16 => NULL,
2617 p_segment17 => NULL,
2618 p_segment18 => NULL,
2619 p_segment19 => NULL,
2620 p_segment20 => NULL);
2621 -- Log error
2622 IF (l_failure_count > 0) THEN
2623 -- dbms_output.put_line ( 'Warehouse location not migrated :' || bal.whse_code ||', '||bal.location );
2624 GMA_COMMON_LOGGING.gma_migration_central_log (
2625 p_run_id => p_migration_run_id,
2626 p_log_level => FND_LOG.LEVEL_ERROR,
2627 p_message_token => 'GMI_LOC_CREATION_FAILED',
2628 p_table_name => 'IC_LOCT_INV',
2629 p_context => 'INVENTORY BALANCE',
2630 p_param1 => bal.whse_code,
2631 p_param2 => bal.location,
2632 p_param3 => NULL,
2633 p_param4 => NULL,
2634 p_param5 => NULL,
2635 p_db_error => NULL,
2636 p_app_short_name => 'GMI');
2637 raise FND_API.G_EXC_ERROR;
2638 END IF;
2639 END;
2640 END IF;
2641
2642 -- Get the subinventory for non-location controlled warehouse. For
2643 -- Location controlled whse, it determined as part of locator.
2644 IF (l_subinventory_code is NULL) THEN
2645 l_subinventory_code := bal.whse_code;
2646 END IF;
2647
2648 -- Header record
2649 l_hdr_rec.orgn_code := l_orgn_code;
2650 IF (bal.loct_onhand < 0) THEN
2651 l_hdr_rec.transfer_type := 1; -- Discrete to process
2652 ELSE
2653 l_hdr_rec.transfer_type := 0; -- Process to Discrete
2654 END IF;
2655 l_hdr_rec.trans_date := sysdate;
2656 l_hdr_rec.comments := 'OPM Inventory Convergence migration';
2657
2658 -- Line record
2659 l_line_rec_tbl(1).line_no := 1;
2660 l_line_rec_tbl(1).opm_item_id := bal.item_id;
2661 l_line_rec_tbl(1).opm_whse_code := bal.whse_code;
2662 l_line_rec_tbl(1).opm_location := bal.location;
2663 l_line_rec_tbl(1).opm_lot_id := 0;
2664 IF (bal.lot_id > 0) THEN
2665 l_line_rec_tbl(1).opm_lot_id := NULL;
2666 END IF;
2667 l_line_rec_tbl(1).odm_inv_organization_id := l_organization_id;
2668 l_line_rec_tbl(1).odm_item_id := l_inventory_item_id;
2669 l_line_rec_tbl(1).odm_subinventory := l_subinventory_code;
2670 l_line_rec_tbl(1).opm_reason_code := 'CNVM';
2671 l_line_rec_tbl(1).odm_locator_id := l_locator_id;
2672 l_line_rec_tbl(1).odm_lot_number := NULL;
2673 IF (bal.loct_onhand < 0) THEN
2674 l_line_rec_tbl(1).quantity := -bal.loct_onhand;
2675 l_line_rec_tbl(1).quantity2 := -bal.loct_onhand2;
2676 ELSE
2677 l_line_rec_tbl(1).quantity := bal.loct_onhand;
2678 l_line_rec_tbl(1).quantity2 := bal.loct_onhand2;
2679 END IF;
2680 l_line_rec_tbl(1).quantity_um := l_item_um;
2681
2682
2683 -- Lot record ( for lot controlled items)
2684 IF (bal.lot_id > 0) THEN
2685 l_lot_rec_tbl(1).line_no := 1;
2686 l_lot_rec_tbl(1).opm_lot_id := bal.lot_id;
2687 l_lot_rec_tbl(1).odm_lot_number := l_lot_number;
2688 IF (bal.loct_onhand < 0) THEN
2689 l_lot_rec_tbl(1).quantity := -bal.loct_onhand;
2690 l_lot_rec_tbl(1).quantity2 := -bal.loct_onhand2;
2691 ELSE
2692 l_lot_rec_tbl(1).quantity := bal.loct_onhand;
2693 l_lot_rec_tbl(1).quantity2 := bal.loct_onhand2;
2694 END IF;
2695 ELSE
2696 l_lot_rec_tbl(1).line_no := NULL;
2697 l_lot_rec_tbl(1).opm_lot_id := NULL;
2698 l_lot_rec_tbl(1).odm_lot_number := NULL;
2699 l_lot_rec_tbl(1).quantity := NULL;
2700 l_lot_rec_tbl(1).quantity2 := NULL;
2701 END IF;
2702
2703
2704 -- dbms_output.put_line ('Disc. lot = '||l_lot_rec_tbl(1).odm_lot_number);
2705 -- Transfer the balance
2706 fnd_msg_pub.initialize;
2707 GMIVDX.Create_transfer_pvt (
2708 p_api_version => 1.0,
2709 x_return_status => l_return_status,
2710 x_msg_count => l_msg_count,
2711 x_msg_data => l_msg_data,
2712 p_hdr_rec => l_hdr_rec,
2713 p_line_rec_tbl => l_line_rec_tbl,
2714 p_lot_rec_tbl => l_lot_rec_tbl,
2715 x_hdr_row => o_hdr_row,
2716 x_line_row_tbl => o_line_row_tbl,
2717 x_lot_row_tbl => o_lot_row_tbl,
2718 x_transaction_set_id => l_transaction_set_id);
2719
2720 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2721 -- dbms_output.put_line ('Balance migration failed for item, whse, lot, location'||l_item_no||', '||bal.whse_code||', '||l_lot_number||', '||bal.location);
2722 GMA_COMMON_LOGGING.gma_migration_central_log (
2723 p_run_id => p_migration_run_id,
2724 p_log_level => FND_LOG.LEVEL_ERROR,
2725 p_message_token => 'GMI_MIG_BALANCE_MIG_FAILED',
2726 p_table_name => 'IC_LOCT_INV',
2727 p_context => 'INVENTORY BALANCE',
2728 p_param1 => INV_GMI_Migration.item(bal.item_id),
2729 p_param2 => bal.whse_code,
2730 p_param3 => INV_GMI_Migration.lot(bal.lot_id),
2731 p_param4 => bal.location,
2732 p_param5 => NULL,
2733 p_db_error => NULL,
2734 p_app_short_name => 'GMI');
2735 FND_MSG_PUB.Count_AND_GET (p_count => l_msg_count, p_data => l_msg_data);
2736 FOR i in 1..l_msg_count LOOP
2737 -- dbms_output.put_line (substr(fnd_msg_pub.get_detail(i, NULL),1,255));
2738 GMA_COMMON_LOGGING.gma_migration_central_log (
2739 p_run_id => p_migration_run_id,
2740 p_log_level => FND_LOG.LEVEL_ERROR,
2741 p_message_token => 'GMI_UNEXPECTED_ERROR',
2742 p_table_name => 'IC_LOCT_INV',
2743 p_context => 'INVENTORY BALANCE',
2744 p_token1 => 'ERROR',
2745 p_param1 => fnd_msg_pub.get_detail(i, NULL),
2746 p_param2 => NULL,
2747 p_param3 => NULL,
2748 p_param4 => NULL,
2749 p_param5 => NULL,
2750 p_db_error => NULL,
2751 p_app_short_name => 'GMI');
2752 END LOOP;
2753 RAISE FND_API.G_EXC_ERROR;
2754 END IF;
2755
2756 -- Update the discrete transactions as costed
2757 UPDATE mtl_material_transactions
2758 SET
2759 costed_flag = NULL,
2760 opm_costed_flag = NULL
2761 WHERE
2762 transaction_set_id = l_transaction_set_id;
2763
2764 -- Update mtl transaction id back in OPM table
2765 UPDATE ic_loct_inv
2766 SET migrated_ind = 1,
2767 material_transaction_id = l_transaction_set_id
2768 WHERE
2769 rowid = bal.rowid;
2770
2771 l_migrate_count := l_migrate_count + 1;
2772 IF (p_commit <> FND_API.G_FALSE) THEN
2773 COMMIT;
2774 END IF;
2775 EXCEPTION
2776 WHEN NO_DATA_FOUND THEN
2777 -- dbms_output.put_line ('Cannot find '||l_field_name||' for '||l_field_value);
2778 GMA_COMMON_LOGGING.gma_migration_central_log (
2779 p_run_id => p_migration_run_id,
2780 p_log_level => FND_LOG.LEVEL_ERROR,
2781 p_message_token => 'GMI_MIG_NO_DATA_FOR_FIELD',
2782 p_table_name => 'IC_LOCT_INV',
2783 p_context => 'INVENTORY BALANCE',
2784 p_param1 => l_field_name,
2785 p_param2 => l_field_value,
2786 p_param3 => NULL,
2787 p_param4 => NULL,
2788 p_param5 => NULL,
2789 p_db_error => NULL,
2790 p_app_short_name => 'GMI');
2791 x_failure_count := x_failure_count + 1;
2792 -- ROLLBACK TO bal_migration_start;
2793 ROLLBACK ;
2794 WHEN e_skip_whse THEN
2795 x_failure_count := x_failure_count + 1;
2796 IF (l_skip_to_next_whse = 0) THEN
2797 l_skip_to_next_whse := 1;
2798 END IF;
2799 -- ROLLBACK TO bal_migration_start;
2800 ROLLBACK ;
2801 WHEN FND_API.G_EXC_ERROR THEN
2802 x_failure_count := x_failure_count + 1;
2803 -- ROLLBACK TO bal_migration_start;
2804 ROLLBACK ;
2805 END;
2806 END LOOP;
2807 -- dbms_output.put_line ('Completed INVENTORY BALANCE migration. Migrated = '||to_char(l_migrate_count)||', Failed = '||to_char(x_failure_count));
2808 -- dbms_output.put_line ('Migrated ' || to_char(l_migrate_count) ||' rows');
2809 GMA_COMMON_LOGGING.gma_migration_central_log (
2810 p_run_id => p_migration_run_id,
2811 p_log_level => FND_LOG.LEVEL_ERROR,
2812 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
2813 p_table_name => 'IC_LOCT_INV',
2814 p_context => 'INVENTORY BALANCE',
2815 p_param1 => l_migrate_count,
2816 p_param2 => x_failure_count,
2817 p_param3 => NULL,
2818 p_param4 => NULL,
2819 p_param5 => NULL,
2820 p_db_error => NULL,
2821 p_app_short_name => 'GMA');
2822 EXCEPTION
2823 WHEN OTHERS THEN
2824 x_failure_count := x_failure_count + 1;
2825 -- dbms_output.put_line (substr(SQLERRM,1,255));
2826 GMA_COMMON_LOGGING.gma_migration_central_log (
2827 p_run_id => p_migration_run_id,
2828 p_log_level => FND_LOG.LEVEL_ERROR,
2829 p_message_token => 'GMA_MIGRATION_DB_ERROR',
2830 p_table_name => 'IC_LOCT_INV',
2831 p_context => 'INVENTORY BALANCE',
2832 p_param1 => NULL,
2833 p_param2 => NULL,
2834 p_param3 => NULL,
2835 p_param4 => NULL,
2836 p_param5 => NULL,
2837 p_db_error => SQLERRM,
2838 p_app_short_name => 'GMA');
2839 ROLLBACK ;
2840 END;
2841
2842 END INV_GMI_Migration;