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