[Home] [Help]
PACKAGE BODY: APPS.INV_OPM_ITEM_MIGRATION
Source
1 PACKAGE BODY INV_OPM_Item_Migration AS
2 /* $Header: INVGIMGB.pls 120.24.12020000.7 2013/02/08 18:39:30 sunarang ship $
3 +==========================================================================+
4 | Copyright (c) 1998 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +==========================================================================+
8 | FILE NAME |
9 | INVGIMGB.pls |
10 | |
11 | TYPE |
12 | Public |
13 | |
14 | PACKAGE NAME |
15 | INV_OPM_Item_Migration |
16 | |
17 | DESCRIPTION |
18 | This package contains the procedure used for item migration for OPM |
19 | convergence project. These procedure are meant for migration only. |
20 | |
21 | Contents |
22 | get_ODM_item |
23 | get_ODM_regulatory_item |
24 | migrate_opm_items |
25 | migrate_obsolete_columns |
26 | |
27 | HISTORY |
28 | Created - Jatinder Gogna - 3/22/05 |
29 | |
30 | Jatinder - 11/30/06 - Use correct organization to lock migration |
31 | records. 5690686. |
32 | Jatinder - 11/30/06 - Move commit to the main procedure to avoid |
33 | deadlocks. 5690686. |
34 | Jatinder - 12/15/06 - Make OPM items with NULL or 0 shelf life days |
35 | as user defined expiration control. Discrete item master |
36 | doesn't allow shlef life expiration control for 0 shelf |
37 | life days. 5730196 |
38 | Archana Mundhe - 11/14/08 - Bug 7166389 |
39 | Migrate status_ctl flag, status_id for status_ctl=2 items.|
40 | Abhay Satpute Bug 11887554 30-Mar-2011 Correctd assignment for |
41 | deviation factors |
42 | Kedar Bavadekar - 04/07/11 Bug 10405668 |
43 | Added procedure migrate_opm_items |
44 | Abhay Satpute 05/02/2011 Bug 12387835 Increase width of local vars |
45 | l_prim_unit_of_measure and l_sec_unit_of_measure |
46 | Srinivasulu Puri 03/14/2012 Modified to lock the ic_item_mst_B_mig |
47 | only when item is migrated by get_odm_item call. |
48 | Bug 13811730 |
49 | May Chen 05/16/2012 Bug 14013552, Modified to remove default logic for|
50 | BOM_ENABLED_FLAG in UPDATE mode, keep the original value |
51 | Shaliu Chen 07/27/2012 Bug 14364505, If there is long descripiton in |
52 | the Orcle Inventory,keep the existing long description |
53 | Bug 14495940 5-Sep-2011 Srinivasulu Puri, reverted chanegs done |
54 | for bug 14013552 as it is is confilicting with R12 functionality |
55 +==========================================================================+
56 */
57
58 /* Global variables */
59 g_item_id NUMBER;
60 g_organization_id NUMBER;
61 g_inventory_item_id NUMBER;
62 g_inv_item_status_code VARCHAR2(10);
63 g_auto_lot_alpha_prefix VARCHAR2(30);
64 g_start_auto_lot_number NUMBER;
65 g_child_lot_prefix VARCHAR2(30);
66 g_child_lot_starting_number NUMBER;
67
68 /*===========================================================================
69 -- PROCEDURE:
70 -- get_reg_item_info
71 --
72 -- DESCRIPTION:
73 -- This PL/SQL procedure is used to build a record to simulate data
74 -- in ic_item_mst_b table.
75 --
76 -- PARAMETERS:
77 -- p_item_code - Item_code to use to retrieve Regulatory item values
78 -- x_reg_item_rec - Record in the format of ic_item_mst_b
79 -- x_return_status - Returns the status of the function (success, failure, etc.)
80 -- x_msg_data - Returns message data if an error occurred
81 --
82 -- SYNOPSIS:
83 -- get_reg_item_info(
84 -- p_item_code => l_item_code,
85 -- x_reg_item_rec => l_opm_item,
86 -- x_return_status => l_return_status,
87 -- x_msg_data => l_msg_data );
88 --
89 -- HISTORY
90 -- Melanie Grosser - 5/11/05
91 --=========================================================================== */
92 PROCEDURE get_reg_item_info
93 (
94 p_item_code IN VARCHAR2,
95 x_reg_item_rec OUT NOCOPY IC_ITEM_MST_B%ROWTYPE,
96 x_return_status OUT NOCOPY VARCHAR2,
97 x_msg_data OUT NOCOPY VARCHAR2
98 ) IS
99
100 /* ------------- LOCAL VARIABLES ------------------- */
101 l_description VARCHAR2(240);
102 l_um_type sy_uoms_typ.um_type%TYPE;
103 l_uom sy_uoms_typ.std_um%TYPE;
104
105 /* ------------------ CURSORS ---------------------- */
106 /* Cursor used to retrieve the Regulatory item info*/
107 CURSOR c_get_reg_item IS
108 SELECT *
109 FROM gr_item_general
110 WHERE item_code = p_item_code;
111 l_reg_rec c_get_reg_item%ROWTYPE;
112
113 /* Cursor used to retrieve the Regulatory item description*/
114 CURSOR c_get_description IS
115 SELECT name_description
116 FROM gr_multilingual_name_tl
117 WHERE language = userenv('LANG') and
118 label_code = '11007' and
119 item_code = p_item_code;
120
121 CURSOR c_get_um_type IS
122 SELECT profile_option_value
123 FROM fnd_profile_options a, fnd_profile_option_values b
124 WHERE b.level_id = 10001 and
125 a.profile_option_id = b.profile_option_id and
126 a.profile_option_name = 'FM_YIELD_TYPE';
127
128
129 /* Cursor used to retrieve the std uom for FM_YIELD_TYPE class */
130 CURSOR c_get_uom (v_um_type VARCHAR2) IS
131 SELECT std_um
132 FROM sy_uoms_typ
133 WHERE um_type = v_um_type;
134
135 /* ----------------- EXCEPTIONS -------------------- */
136 INVALID_ITEM EXCEPTION;
137
138 BEGIN
139
140 x_return_status := FND_API.G_RET_STS_SUCCESS;
141
142 /* Retrieve Regulatory Item information */
143 OPEN c_get_reg_item;
144 FETCH c_get_reg_item INTO l_reg_rec;
145
146 /* If inventory item not found */
147 IF c_get_reg_item%NOTFOUND THEN
148 CLOSE c_get_reg_item;
149 RAISE INVALID_ITEM;
150 END IF;
151
152 CLOSE c_get_reg_item;
153
154 /* Retrieve Description (MSDS Name) */
155 OPEN c_get_description;
156 FETCH c_get_description INTO l_description;
157 CLOSE c_get_description;
158
159 /* Retrieve value of FM_YIELD_TYPE */
160 OPEN c_get_um_type;
161 FETCH c_get_um_type INTO l_um_type;
162 CLOSE c_get_um_type;
163
164 /* Retrieve std uom */
165 OPEN c_get_uom(l_um_type);
166 FETCH c_get_uom INTO l_uom;
167 CLOSE c_get_uom;
168
169 x_reg_item_rec.ITEM_NO := l_reg_rec.item_code;
170 x_reg_item_rec.ITEM_DESC1 := l_description;
171 x_reg_item_rec.ITEM_UM := l_uom;
172 x_reg_item_rec.DUALUM_IND := 0;
173 x_reg_item_rec.DEVIATION_LO := 0;
174 x_reg_item_rec.DEVIATION_HI := 0;
175 x_reg_item_rec.LOT_CTL := 0;
176 x_reg_item_rec.LOT_INDIVISIBLE := 0;
177 x_reg_item_rec.SUBLOT_CTL := 0;
178 x_reg_item_rec.LOCT_CTL := 0;
179 x_reg_item_rec.NONINV_IND := 1;
180 --Bug 13347968 01/17/2012 shalchen start
181 --Process Manufacturing attribute are disabled after OPM regulatory Items migration
182 --Change Initial value from 1 to 0
183 x_reg_item_rec.INACTIVE_IND := 0;
184 --Bug 13347968 01/17/2012 shalchen End
185 x_reg_item_rec.RETEST_INTERVAL := 0;
186 x_reg_item_rec.GRADE_CTL := 0;
187 x_reg_item_rec.STATUS_CTL := 0;
188 x_reg_item_rec.EXPERIMENTAL_IND := 0;
189 x_reg_item_rec.DELETE_MARK := 0;
190 x_reg_item_rec.CREATION_DATE := l_reg_rec.creation_date;
191 x_reg_item_rec.CREATED_BY := l_reg_rec.created_by;
192 x_reg_item_rec.LAST_UPDATE_DATE := l_reg_rec.last_update_date;
193 x_reg_item_rec.LAST_UPDATED_BY := l_reg_rec.last_updated_by;
194 x_reg_item_rec.LAST_UPDATE_LOGIN := l_reg_rec.last_update_login;
195
196
197 EXCEPTION
198
199 WHEN INVALID_ITEM THEN
200 FND_MESSAGE.SET_NAME('GR','GR_INVALID_ITEM');
201 x_msg_data := FND_MESSAGE.GET;
202 x_return_status := FND_API.G_RET_STS_ERROR;
203
204 WHEN OTHERS THEN
205 x_msg_data := SQLERRM;
206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
207
208 END get_reg_item_info;
209
210 /*====================================================================
211 -- PROCEDURE:
212 -- migrate_OPM_item_to_ODM
213 --
214 -- DESCRIPTION:
215 -- Internal routine to migrate OPM items to Oracle inventory.
216 -- This procedure should not be called on its own. Call the
217 -- get_ODM_item procedure instead.
218 --
219 -- PARAMETERS:
220 --
221 -- SYNOPSIS:
222 --
223 -- HISTORY
224 -- Jatinder Gogna - Created - 03/25/05
225 --====================================================================*/
226
227 PROCEDURE migrate_OPM_item_to_ODM
228 ( p_migration_run_id IN NUMBER
229 , p_item_id IN NUMBER
230 , p_item_code IN VARCHAR2
231 , p_item_source IN VARCHAR2
232 , p_organization_id IN NUMBER
233 , p_master_org_id IN NUMBER
234 , p_organization_type IN VARCHAR2
235 , p_commit IN VARCHAR2
236 , x_inventory_item_id OUT NOCOPY NUMBER
237 , x_failure_count OUT NOCOPY NUMBER
238 ) IS
239 l_item_rec INV_ITEM_API.Item_rec_type;
240 --BEGIN Bug 14364505
241 l_item_tl_rec INV_ITEM_API.Item_TL_rec_type;
242 --END Bug 14364505
243 o_item_rec INV_ITEM_API.Item_rec_type;
244 l_opm_item IC_ITEM_MST_B%ROWTYPE;
245 l_inventory_item_id NUMBER;
246 l_organization_code VARCHAR2(3);
247 l_action VARCHAR2(1);
248 l_event VARCHAR(20);
249 l_return_status VARCHAR2(10);
250 l_msg_count NUMBER;
251 l_msg_data VARCHAR2(2000);
252 l_field_name VARCHAR(50);
253 l_field_value VARCHAR(50);
254 v_rowid varchar2(1000);
255 e_error EXCEPTION;
256
257 l_enabled_flag VARCHAR2(1);
258 l_prim_uom_code VARCHAR2(3);
259 -- Bug 12387835 5/2/2011 Increase the width to 25 (from 15)
260 l_prim_unit_of_meassure VARCHAR2(25);
261 l_sec_uom_code VARCHAR2(3);
262 -- Bug 12387835 5/2/2011 Increase the width to 25 (from 15)
263 l_sec_unit_of_meassure VARCHAR2(25);
264 l_inventory_item_flag VARCHAR2(1);
265 l_inventory_asset_flag VARCHAR2(1);
266 l_costing_enabled_flag VARCHAR2(1);
267 l_stock_enabled_flag VARCHAR2(1);
268 l_build_in_wip_flag VARCHAR2(1);
269 l_mtl_xactions_enabled_flag VARCHAR2(1);
270 l_purchasing_enabled_flag VARCHAR2(1);
271 l_customer_order_enabled_flag VARCHAR2(1);
272 l_internal_order_enabled_flag VARCHAR2(1);
273 l_invoice_enabled_flag VARCHAR2(1);
274 l_recipe_enabled_flag VARCHAR2(1);
275 l_process_exec_enabled_flag VARCHAR2(1);
276 l_process_costing_enabled_flag VARCHAR2(1);
277 l_process_quality_enabled_flag VARCHAR2(1);
278 l_shelf_life_code NUMBER;
279 l_auto_lot_alpha_prefix VARCHAR2(30);
280 l_start_auto_lot_number NUMBER;
281 l_child_lot_prefix VARCHAR2(30);
282 l_child_lot_starting_number NUMBER;
283 l_cost_of_sales_account NUMBER;
284 l_sales_Account NUMBER;
285 l_expense_Account NUMBER;
286 l_encumbrance_account NUMBER;
287 l_status_id NUMBER;
288 l_maturity_days NUMBER;
289 l_hold_days NUMBER;
290 l_process_enabled_flag VARCHAR2(1);
291 l_get_item_description VARCHAR2(1);
292 l_org_id NUMBER;
293
294 CURSOR c_ic_item_mst_tl IS
295 SELECT *
296 FROM ic_item_mst_tl
297 WHERE item_id = p_item_id;
298
299 BEGIN
300 x_failure_count := 0;
301 -- Get the OPM Item Master Details
302 BEGIN
303 IF p_item_source = 'GR' THEN
304 get_reg_item_info (
305 p_item_code => p_item_code,
306 x_reg_item_rec => l_opm_item,
307 x_return_status => l_return_status,
308 x_msg_data => l_msg_data);
309 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
310 -- Log error
311 -- dbms_output.put_line ('Invalid Regulatory Item :' || p_item_code);
312 GMA_COMMON_LOGGING.gma_migration_central_log (
313 p_run_id => p_migration_run_id,
314 p_log_level => FND_LOG.LEVEL_ERROR,
315 p_message_token => 'GMI_UNEXPECTED_ERROR',
316 p_table_name => 'IC_ITEM_MST_B',
317 p_context => 'ITEMS',
318 p_token1 => 'ERROR',
319 p_param1 => l_msg_data,
320 p_param2 => NULL,
321 p_param3 => NULL,
322 p_param4 => NULL,
323 p_param5 => NULL,
324 p_db_error => NULL,
325 p_app_short_name => 'GMI');
326 x_failure_count := x_failure_count + 1;
327 RAISE e_error;
328 END IF;
329 ELSE
330 SELECT * INTO l_opm_item
331 FROM ic_item_mst_b
332 WHERE item_id = p_item_id;
333 END IF;
334 EXCEPTION
335 WHEN NO_DATA_FOUND THEN
336 -- Log error
337 -- dbms_output.put_line ('Invalid item id' || to_char(p_item_id));
338 GMA_COMMON_LOGGING.gma_migration_central_log (
339 p_run_id => p_migration_run_id,
340 p_log_level => FND_LOG.LEVEL_ERROR,
341 p_message_token => 'GMI_MIG_INVALID_ITEM_ID',
342 p_table_name => 'IC_ITEM_MST_B',
343 p_context => 'ITEMS',
344 p_param1 => INV_GMI_Migration.item(p_item_id),
345 p_param2 => NULL,
346 p_param3 => NULL,
347 p_param4 => NULL,
348 p_param5 => NULL,
349 p_db_error => NULL,
350 p_app_short_name => 'GMI');
351 x_failure_count := x_failure_count + 1;
352 RAISE e_error;
353 END;
354
355 -- Check if the item already exists in discrete
356 BEGIN
357 l_get_item_description := 'N';
358 l_action := 'I';
359
360 SELECT inventory_item_id
361 INTO l_inventory_item_id
362 FROM mtl_system_items_b
363 WHERE
364 segment1 = l_opm_item.item_no and
365 ROWNUM = 1;
366
367 INV_ITEM_PVT.Get_Org_Item(
368 p_Item_ID => l_inventory_item_id,
369 p_Org_ID => p_organization_id,
370 x_Item_rec => l_item_rec,
371 x_return_status => l_return_status,
372 x_msg_count => l_msg_count,
373 x_msg_data => l_msg_data);
374
375 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
376 l_action := 'U';
377
378 --BEGIN BUG 14364505
379 --If Inventory Item is existing in the Oracle Inventory,Get Item Long Description.
380
381 l_get_item_description := 'Y';
382 l_org_id := p_organization_id;
383
384 --END BUG 14364505
385 END IF;
386
387 -- Added for Bug 14558263.
388 -- We are trying to get the Item attributes from Master Org and we will use these Attributes to populate the Child Item
389
390 IF p_organization_type = 'C' AND l_action = 'I' THEN
391
392 INV_ITEM_PVT.Get_Org_Item(
393 p_Item_ID => l_inventory_item_id,
394 p_Org_ID => p_master_org_id,
395 x_Item_rec => l_item_rec,
396 x_return_status => l_return_status,
397 x_msg_count => l_msg_count,
398 x_msg_data => l_msg_data);
399
400 l_get_item_description := 'Y';
401 l_org_id := p_master_org_id;
402
403 END IF;
404
405 IF l_get_item_description = 'Y' then
406
407 OPEN INV_ITEM_API.Item_TL_csr
408 (
409 p_Item_ID => l_inventory_item_id
410 , p_Org_ID => l_org_id
411 , p_fetch_Master => FND_API.g_TRUE
412 , p_fetch_Orgs => FND_API.g_FALSE
413 );
414 FETCH INV_ITEM_API.Item_TL_csr INTO l_item_tl_rec;
415 CLOSE INV_ITEM_API.Item_TL_csr;
416
417
418 END IF;
419
420 -- Added for Bug 14558263.
421 EXCEPTION
422 WHEN NO_DATA_FOUND THEN
423 SELECT mtl_system_items_s.nextval
424 INTO l_inventory_item_id
425 FROM dual
426 WHERE rownum = 1;
427 END;
428
429 -- -- dbms_output.put_line ('Migrate Action = '||l_action);
430 -- Set local variables based upon OPM item master definition
431 IF (l_opm_item.delete_mark = 1) THEN
432 l_enabled_flag := 'N';
433 ELSE
434 l_enabled_flag := 'Y';
435 END IF;
436 BEGIN
437 -- Get default accounts
438 l_field_name := 'Organization Accounts';
439 l_field_value := 'Organization Id = '||to_char(p_organization_id);
440 SELECT cost_of_sales_account, sales_account,
441 expense_account, encumbrance_account,
442 process_enabled_flag, organization_code
443 INTO l_cost_of_sales_account, l_sales_account,
444 l_expense_account, l_encumbrance_account,
445 l_process_enabled_flag, l_organization_code
446 FROM mtl_parameters
447 WHERE organization_id = p_organization_id
448 AND rownum = 1;
449
450 IF ( l_process_enabled_flag <> 'Y') THEN
451 IF (p_organization_type = 'C') THEN
452 -- Log Error
453 -- dbms_output.put_line ('Cannot migrate discrete organization');
454 GMA_COMMON_LOGGING.gma_migration_central_log (
455 p_run_id => p_migration_run_id,
456 p_log_level => FND_LOG.LEVEL_ERROR,
457 p_message_token => 'GMI_MIG_DISCRETE_ORG',
458 p_table_name => 'IC_ITEM_MST_B',
459 p_context => 'ITEMS',
460 p_param1 => INV_GMI_Migration.org(p_organization_id),
461 p_param2 => INV_GMI_Migration.item(p_item_id),
462 p_param3 => NULL,
463 p_param4 => NULL,
464 p_param5 => NULL,
465 p_db_error => NULL,
466 p_app_short_name => 'GMI');
467 x_failure_count := x_failure_count + 1;
468 RAISE e_error;
469 END IF;
470 END IF;
471 l_field_name := 'UOM Code';
472 l_field_value := l_opm_item.item_um;
473 SELECT uom_code,unit_of_measure
474 INTO l_prim_uom_code, l_prim_unit_of_meassure
475 FROM sy_uoms_mst
476 WHERE um_code = l_opm_item.item_um;
477
478 IF (l_opm_item.dualum_ind > 0) THEN
479 l_field_name := 'Secondary UOM';
480 l_field_value := l_opm_item.item_um2;
481 SELECT uom_code,unit_of_measure
482 INTO l_sec_uom_code, l_sec_unit_of_meassure
483 FROM sy_uoms_mst
484 WHERE um_code = l_opm_item.item_um2;
485 END IF;
486
487 IF (g_inv_item_status_code is NULL) THEN
488 g_inv_item_status_code :=
489 fnd_profile.value ('INV_STATUS_DEFAULT');
490 END IF;
491
492 l_inventory_item_flag := 'Y';
493 l_inventory_asset_flag := 'Y';
494 l_costing_enabled_flag := 'Y';
495 l_stock_enabled_flag := 'Y';
496 l_build_in_wip_flag := 'Y';
497 l_mtl_xactions_enabled_flag := 'Y';
498 l_purchasing_enabled_flag := 'Y';
499 l_customer_order_enabled_flag := 'Y';
500 l_internal_order_enabled_flag := 'Y';
501 l_invoice_enabled_flag := 'Y';
502 l_recipe_enabled_flag := 'Y';
503 l_process_exec_enabled_flag := 'Y';
504 l_process_costing_enabled_flag := 'Y';
505 l_process_quality_enabled_flag := 'Y';
506 IF (l_opm_item.noninv_ind = 1) THEN
507 l_inventory_item_flag := 'N';
508 l_inventory_asset_flag := 'N';
509 l_costing_enabled_flag := 'N';
510 l_stock_enabled_flag := 'N';
511 l_build_in_wip_flag := 'N';
512 l_mtl_xactions_enabled_flag := 'N';
513 END IF;
514 -- inactive must come after noninv logic and can override noninv
515 -- logic
516 IF (l_opm_item.inactive_ind = 1) THEN
517 l_stock_enabled_flag := 'N';
518 l_build_in_wip_flag := 'N';
519 l_mtl_xactions_enabled_flag := 'N';
520 l_purchasing_enabled_flag := 'N';
521 l_customer_order_enabled_flag := 'N';
522 l_internal_order_enabled_flag := 'N';
523 l_invoice_enabled_flag := 'N';
524 l_recipe_enabled_flag := 'N';
525 l_process_exec_enabled_flag := 'N';
526 l_process_costing_enabled_flag := 'N';
527 l_process_quality_enabled_flag := 'N';
528
529 --Bug 15839498, diable inventory_item_flag,costing_enabled_flag, inventory_asset_flag when OPM item is inactive
530 --This is consistent to item mass update script INVG1MIM.sql
531 l_inventory_item_flag := 'N';
532 l_costing_enabled_flag := 'N';
533 l_inventory_asset_flag := 'N';
534
535 END IF;
536
537 -- Jatinder - 12/15/06 - Make OPM items with NULL or 0 shelf life days
538 -- as user defined expiration control. Discrete item master
539 -- doesn't allow shelf life expiration control for 0 shelf
540 -- life days. 5730196
541 l_shelf_life_code := 1; -- No control
542 IF (l_opm_item.lot_ctl = 1) THEN
543 BEGIN
544 IF (nvl(l_opm_item.shelf_life, 0) = 0) THEN
545 l_shelf_life_code := 4; -- User defined
546 ELSE
547 l_shelf_life_code := 2; -- Shelf Life Days
548 END IF;
549 END;
550 END IF;
551
552 l_field_name := 'GMI Migration parameters';
553 l_field_value := NULL;
554 IF (g_auto_lot_alpha_prefix is NULL) THEN
555 BEGIN
556 SELECT
557 auto_lot_alpha_prefix, start_auto_lot_number,
558 child_lot_prefix, child_lot_starting_number
559 INTO
560 g_auto_lot_alpha_prefix, g_start_auto_lot_number,
561 g_child_lot_prefix, g_child_lot_starting_number
562 FROM gmi_migration_parameters
563 WHERE rownum = 1;
564 EXCEPTION
565 WHEN NO_DATA_FOUND THEN
566 g_auto_lot_alpha_prefix := 'L';
567 g_start_auto_lot_number := 1;
568 g_child_lot_prefix := '#S';
569 g_child_lot_starting_number := 1;
570 END;
571 END IF;
572
573 IF (l_opm_item.lot_ctl = 1) THEN
574 l_auto_lot_alpha_prefix := nvl(l_opm_item.lot_prefix,
575 g_auto_lot_alpha_prefix);
576 l_start_auto_lot_number := nvl(l_opm_item.lot_suffix,
577 g_start_auto_lot_number);
578 END IF;
579 IF (l_opm_item.sublot_ctl = 1) THEN
580 l_child_lot_prefix := nvl(l_opm_item.sublot_prefix,
581 g_child_lot_prefix);
582 l_child_lot_starting_number := nvl(l_opm_item.sublot_suffix,
583 g_child_lot_starting_number);
584 END IF;
585
586 -- Get the default status id
587 l_field_name := 'Default Status Id';
588 l_field_value := l_opm_item.lot_status;
589 -- Bug 7166389
590 -- Migrate status_id for status_ctl = 2 items.
591 l_status_id := NULL;
592 IF (l_opm_item.status_ctl IN (1,2) and
593 l_opm_item.lot_status is not NULL) THEN
594 SELECT status_id
595 INTO l_status_id
596 FROM ic_lots_sts
597 WHERE
598 lot_status = l_opm_item.lot_status and
599 status_id is not NULL;
600 END IF;
601
602 -- Get CPG fields
603 l_field_name := 'Maturity and Hold Days';
604 BEGIN
605 SELECT ic_matr_days, ic_hold_days
606 INTO l_maturity_days, l_hold_days
607 FROM ic_item_cpg
608 WHERE
609 item_id = l_opm_item.item_id;
610 EXCEPTION
611 WHEN NO_DATA_FOUND THEN
612 NULL;
613 END;
614
615 EXCEPTION
616 WHEN NO_DATA_FOUND THEN
617 -- Log error for l_field_name
618 -- dbms_output.put_line ('Could not find '||l_field_name||' for '||l_field_value);
619 GMA_COMMON_LOGGING.gma_migration_central_log (
620 p_run_id => p_migration_run_id,
621 p_log_level => FND_LOG.LEVEL_ERROR,
622 p_message_token => 'GMI_MIG_NO_DATA_FOR_FIELD',
623 p_table_name => 'IC_ITEM_MST_B',
624 p_context => 'ITEMS',
625 p_param1 => l_field_name,
626 p_param2 => l_field_value,
627 p_param3 => NULL,
628 p_param4 => NULL,
629 p_param5 => NULL,
630 p_db_error => NULL,
631 p_app_short_name => 'GMI');
632 x_failure_count := x_failure_count + 1;
633 RAISE e_error;
634 END;
635
636 -- Prepare the data for the Item creation
637 l_item_rec.ORGANIZATION_ID := p_organization_id;
638 l_item_rec.INVENTORY_ITEM_ID := l_inventory_item_id;
639 l_item_rec.SEGMENT1 := l_opm_item.item_no;
640 l_item_rec.SUMMARY_FLAG := 'Y';
641 l_item_rec.ENABLED_FLAG := l_enabled_flag;
642
643 -- Fix any flags which may cause errors
644 l_item_rec.BOM_ENABLED_FLAG := 'N';
645 l_item_rec.PURCHASING_ITEM_FLAG := 'Y';
646 l_item_rec.CUSTOMER_ORDER_FLAG := 'Y';
647 l_item_rec.SHIPPABLE_ITEM_FLAG := 'Y';
648 l_item_rec.INTERNAL_ORDER_FLAG := 'Y';
649 l_item_rec.INVOICEABLE_ITEM_FLAG := 'Y';
650 l_item_rec.SO_TRANSACTIONS_FLAG := 'Y';
651 l_item_rec.TAXABLE_FLAG := 'Y';
652
653 l_item_rec.INVENTORY_ITEM_FLAG := l_inventory_item_flag;
654 l_item_rec.INVENTORY_ASSET_FLAG := l_inventory_asset_flag;
655 l_item_rec.COSTING_ENABLED_FLAG := l_costing_enabled_flag;
656 l_item_rec.STOCK_ENABLED_FLAG := l_stock_enabled_flag;
657 l_item_rec.BUILD_IN_WIP_FLAG := l_build_in_wip_flag;
658 l_item_rec.MTL_TRANSACTIONS_ENABLED_FLAG := l_mtl_xactions_enabled_flag;
659 l_item_rec.PURCHASING_ENABLED_FLAG := l_purchasing_enabled_flag;
660 l_item_rec.CUSTOMER_ORDER_ENABLED_FLAG := l_customer_order_enabled_flag;
661 l_item_rec.INTERNAL_ORDER_ENABLED_FLAG := l_internal_order_enabled_flag;
662 l_item_rec.INVOICE_ENABLED_FLAG := l_invoice_enabled_flag;
663 l_item_rec.RECIPE_ENABLED_FLAG := l_recipe_enabled_flag;
664 l_item_rec.PROCESS_QUALITY_ENABLED_FLAG := l_process_quality_enabled_flag;
665 l_item_rec.PROCESS_EXECUTION_ENABLED_FLAG := l_process_exec_enabled_flag;
666 l_item_rec.PROCESS_COSTING_ENABLED_FLAG := l_process_costing_enabled_flag;
667
668 IF (( l_process_enabled_flag = 'Y' and l_action = 'U') or l_action = 'I') THEN
669 l_item_rec.BOM_ENABLED_FLAG := 'N';
670 l_item_rec.DESCRIPTION := l_opm_item.item_desc1;
671 --BEGIN BUG 14364505
672 --If Inventory Item is existing in the Oracle Inventory and Long Description is not NULL,Keep the existing Long Description.
673 IF (l_action = 'U' AND l_item_tl_rec.long_description IS NOT NULL) THEN
674 l_item_rec.LONG_DESCRIPTION := l_item_tl_rec.long_description;
675 ELSE
676 l_item_rec.LONG_DESCRIPTION := l_opm_item.item_desc2;
677 END IF;
678 --END BUG 14364505
679 l_item_rec.PRIMARY_UOM_CODE := l_prim_uom_code;
680 l_item_rec.PRIMARY_UNIT_OF_MEASURE := l_prim_unit_of_meassure;
681 l_item_rec.ITEM_TYPE := l_opm_item.inv_type;
682 l_item_rec.SHELF_LIFE_CODE := l_shelf_life_code;
683 l_item_rec.SHELF_LIFE_DAYS := l_opm_item.shelf_life;
684 l_item_rec.LOT_CONTROL_CODE := l_opm_item.lot_ctl + 1;
685 l_item_rec.AUTO_LOT_ALPHA_PREFIX := l_auto_lot_alpha_prefix;
686 l_item_rec.START_AUTO_LOT_NUMBER := l_start_auto_lot_number;
687 l_item_rec.LOCATION_CONTROL_CODE := l_opm_item.loct_ctl + 1;
688 l_item_rec.ENG_ITEM_FLAG := 'N';
689 IF (l_opm_item.experimental_ind = 1) THEN
690 l_item_rec.ENG_ITEM_FLAG := 'Y';
691 END IF;
692 l_item_rec.LOT_STATUS_ENABLED := 'N';
693 -- Bug 7166389
694 -- Migrate status flag for status_ctl = 2 items
695 IF (l_opm_item.status_ctl IN (1,2)) THEN
696 l_item_rec.LOT_STATUS_ENABLED := 'Y';
697 END IF;
698 l_item_rec.DEFAULT_LOT_STATUS_ID := l_status_id;
699 l_item_rec.DUAL_UOM_CONTROL := l_opm_item.dualum_ind + 1;
700 l_item_rec.SECONDARY_UOM_CODE := l_sec_uom_code;
701 -- l_item_rec.DUAL_UOM_DEVIATION_HIGH := nvl(l_opm_item.deviation_lo*100,0);
702 -- l_item_rec.DUAL_UOM_DEVIATION_LOW := nvl(l_opm_item.deviation_hi*100,0);
703 -- Bug 11887554 Corrected the deviation assignment (old code above)
704 l_item_rec.DUAL_UOM_DEVIATION_HIGH := nvl(l_opm_item.deviation_hi*100,0);
705 l_item_rec.DUAL_UOM_DEVIATION_LOW := nvl(l_opm_item.deviation_lo*100,0);
706 l_item_rec.SECONDARY_DEFAULT_IND := NULL;
707 l_item_rec.TRACKING_QUANTITY_IND := 'P';
708 IF (l_opm_item.dualum_ind > 0) THEN
709 l_item_rec.TRACKING_QUANTITY_IND := 'PS';
710 IF (l_opm_item.dualum_ind = 1) THEN
711 l_item_rec.SECONDARY_DEFAULT_IND := 'F';
712 ELSIF (l_opm_item.dualum_ind = 2) THEN
713 l_item_rec.SECONDARY_DEFAULT_IND := 'D';
714 ELSE
715 l_item_rec.SECONDARY_DEFAULT_IND := 'N';
716 END IF;
717 END IF;
718 l_item_rec.ONT_PRICING_QTY_SOURCE := 'P';
719 IF (l_opm_item.ONT_PRICING_QTY_SOURCE = 1) THEN
720 l_item_rec.ONT_PRICING_QTY_SOURCE := 'S';
721 END IF;
722 l_item_rec.LOT_DIVISIBLE_FLAG := 'N';
723 IF (l_opm_item.lot_ctl = 1 and l_opm_item.lot_indivisible = 0) THEN
724 l_item_rec.LOT_DIVISIBLE_FLAG := 'Y';
725 END IF;
726 l_item_rec.GRADE_CONTROL_FLAG := 'N';
727 IF (l_opm_item.grade_ctl = 1) THEN
728 l_item_rec.GRADE_CONTROL_FLAG := 'Y';
729 END IF;
730 l_item_rec.DEFAULT_GRADE := l_opm_item.qc_grade;
731 l_item_rec.CHILD_LOT_FLAG := 'N';
732 l_item_rec.CHILD_LOT_VALIDATION_FLAG := 'N';
733 IF (l_opm_item.sublot_ctl = 1) THEN
734 l_item_rec.CHILD_LOT_FLAG := 'Y';
735 l_item_rec.PARENT_CHILD_GENERATION_FLAG := 'C';
736 -- The lot migration may not conform to strict validation
737 l_item_rec.CHILD_LOT_VALIDATION_FLAG := 'N';
738 l_item_rec.CHILD_LOT_PREFIX := l_child_lot_prefix;
739 l_item_rec.CHILD_LOT_STARTING_NUMBER := l_child_lot_starting_number;
740 END IF;
741 l_item_rec.COPY_LOT_ATTRIBUTE_FLAG := 'N';
742
743 l_item_rec.PROCESS_SUPPLY_SUBINVENTORY := NULL;
744 l_item_rec.PROCESS_SUPPLY_LOCATOR_ID := NULL;
745 l_item_rec.PROCESS_YIELD_SUBINVENTORY := NULL;
746 l_item_rec.PROCESS_YIELD_LOCATOR_ID := NULL;
747 l_item_rec.HAZARDOUS_MATERIAL_FLAG := 'N';
748 l_item_rec.CAS_NUMBER := NULL;
749 l_item_rec.RETEST_INTERVAL := l_opm_item.retest_interval;
750 l_item_rec.EXPIRATION_ACTION_INTERVAL := l_opm_item.expaction_interval;
751 IF (l_opm_item.expaction_code is not NULL) THEN
752 l_item_rec.EXPIRATION_ACTION_CODE := l_opm_item.expaction_code;
753 END IF;
754 l_item_rec.MATURITY_DAYS := l_maturity_days;
755 l_item_rec.HOLD_DAYS := l_hold_days;
756
757 END IF;
758
759
760 IF ((l_action = 'I') AND ( p_organization_type = 'M' )) THEN -- Modified for Bug 14558263
761
762 l_item_rec.INVENTORY_ITEM_STATUS_CODE := g_inv_item_status_code;
763 l_item_rec.CATALOG_STATUS_FLAG := 'N';
764 l_item_rec.ALLOWED_UNITS_LOOKUP_CODE := 3;
765 l_item_rec.CHECK_SHORTAGES_FLAG := 'N';
766 l_item_rec.REVISION_QTY_CONTROL_CODE := 1;
767 l_item_rec.RESERVABLE_TYPE := 1;
768 l_item_rec.CYCLE_COUNT_ENABLED_FLAG := 'N';
769 l_item_rec.SERIAL_NUMBER_CONTROL_CODE := 1;
770 l_item_rec.RESTRICT_SUBINVENTORIES_CODE := 2;
771 l_item_rec.RESTRICT_LOCATORS_CODE := 2;
772 l_item_rec.BOM_ITEM_TYPE := 4;
773 l_item_rec.EFFECTIVITY_CONTROL := 1;
774 l_item_rec.AUTO_CREATED_CONFIG_FLAG := 'N';
775 l_item_rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG := 'N';
776 l_item_rec.COST_OF_SALES_ACCOUNT := l_cost_of_sales_account;
777 l_item_rec.PURCHASING_ITEM_FLAG := 'Y';
778 l_item_rec.MUST_USE_APPROVED_VENDOR_FLAG := 'N';
779 l_item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG := 'Y';
780 l_item_rec.RFQ_REQUIRED_FLAG := 'N';
781 l_item_rec.OUTSIDE_OPERATION_FLAG := 'N';
782 l_item_rec.TAXABLE_FLAG := 'Y';
783 l_item_rec.RECEIPT_REQUIRED_FLAG := 'Y';
784 l_item_rec.INSPECTION_REQUIRED_FLAG := 'N';
785 l_item_rec.UNIT_OF_ISSUE := l_prim_unit_of_meassure;
786 l_item_rec.LIST_PRICE_PER_UNIT := 0;
787 l_item_rec.MARKET_PRICE := 0;
788 l_item_rec.PRICE_TOLERANCE_PERCENT := 0;
789 l_item_rec.ENCUMBRANCE_ACCOUNT := l_encumbrance_account;
790 l_item_rec.EXPENSE_ACCOUNT := l_expense_account;
791 l_item_rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG := 'N';
792 l_item_rec.ALLOW_UNORDERED_RECEIPTS_FLAG := 'N';
793 l_item_rec.ALLOW_EXPRESS_DELIVERY_FLAG := 'N';
794 l_item_rec.INVENTORY_PLANNING_CODE := 2;
795 l_item_rec.PLANNING_MAKE_BUY_CODE := 2;
796 l_item_rec.MRP_SAFETY_STOCK_CODE := 1;
797 l_item_rec.MRP_PLANNING_CODE := 7;
798 l_item_rec.ATO_FORECAST_CONTROL := 2;
799 l_item_rec.END_ASSEMBLY_PEGGING_FLAG := 'N';
800 l_item_rec.REPETITIVE_PLANNING_FLAG := 'N';
801 l_item_rec.ACCEPTABLE_RATE_INCREASE := 0;
802 l_item_rec.ACCEPTABLE_RATE_DECREASE := 0;
803 l_item_rec.PLANNING_TIME_FENCE_CODE := 4;
804 l_item_rec.PLANNING_TIME_FENCE_DAYS := 1;
805 l_item_rec.WIP_SUPPLY_TYPE := 1;
806 l_item_rec.BOM_ENABLED_FLAG := 'N';
807 l_item_rec.CUSTOMER_ORDER_FLAG := 'Y';
808 l_item_rec.SHIPPABLE_ITEM_FLAG := 'Y';
809 l_item_rec.INTERNAL_ORDER_FLAG := 'Y';
810 l_item_rec.SO_TRANSACTIONS_FLAG := 'Y';
811 l_item_rec.PICK_COMPONENTS_FLAG := 'N';
812 l_item_rec.ATP_FLAG := 'N';
813 l_item_rec.REPLENISH_TO_ORDER_FLAG := 'N';
814 l_item_rec.ATP_COMPONENTS_FLAG := 'N';
815 l_item_rec.SHIP_MODEL_COMPLETE_FLAG := 'N';
816 l_item_rec.RETURNABLE_FLAG := 'Y';
817 l_item_rec.RETURN_INSPECTION_REQUIREMENT := 2;
818 l_item_rec.INVOICEABLE_ITEM_FLAG := 'Y';
819 l_item_rec.SALES_ACCOUNT := l_sales_account;
820 l_item_rec.SERVICE_DURATION := 0;
821 l_item_rec.SERVICEABLE_PRODUCT_FLAG := 'N';
822 l_item_rec.SERVICE_STARTING_DELAY := 0;
823 l_item_rec.SERVICEABLE_COMPONENT_FLAG := 'N';
824 l_item_rec.PREVENTIVE_MAINTENANCE_FLAG := 'N';
825 l_item_rec.PRORATE_SERVICE_FLAG := 'N';
826 l_item_rec.SERIAL_STATUS_ENABLED := 'N';
827 l_item_rec.DEFAULT_SERIAL_STATUS_ID := NULL;
828 l_item_rec.LOT_SPLIT_ENABLED := 'N';
829 l_item_rec.LOT_MERGE_ENABLED := 'N';
830 l_item_rec.LOT_TRANSLATE_ENABLED := 'N';
831 l_item_rec.DEFAULT_SO_SOURCE_TYPE := 'INTERNAL';
832 l_item_rec.CREATE_SUPPLY_FLAG := 'Y';
833 l_item_rec.ASN_AUTOEXPIRE_FLAG := 2;
834 l_item_rec.BULK_PICKED_FLAG := 'N';
835 l_item_rec.CONSIGNED_FLAG := 2;
836 l_item_rec.CONTINOUS_TRANSFER := 3;
837 l_item_rec.CONVERGENCE := 3;
838 l_item_rec.CRITICAL_COMPONENT_FLAG := 2;
839 l_item_rec.DIVERGENCE := 3;
840 l_item_rec.DRP_PLANNED_FLAG := 2;
841 l_item_rec.EQUIPMENT_TYPE := 2;
842 l_item_rec.EXCLUDE_FROM_BUDGET_FLAG := 2;
843 l_item_rec.LEAD_TIME_LOT_SIZE := 1;
844 l_item_rec.POSTPROCESSING_LEAD_TIME := 0; -- ?????
845 l_item_rec.SERV_BILLING_ENABLED_FLAG := 'N';
846 l_item_rec.SO_AUTHORIZATION_FLAG := 1;
847 l_item_rec.VMI_FORECAST_TYPE := 1;
848 l_item_rec.WEB_STATUS := 'UNPUBLISHED';
849
850 END IF;
851
852
853 IF (g_attribute_context = 1 and l_item_rec.ATTRIBUTE_CATEGORY is NULL) THEN
854 l_item_rec.ATTRIBUTE_CATEGORY := l_opm_item.attribute_category;
855 END IF;
856 IF (g_attribute1 = 1 and l_item_rec.attribute1 is NULL) THEN l_item_rec.attribute1 := l_opm_item.attribute1; END IF;
857 IF (g_attribute2 = 1 and l_item_rec.attribute2 is NULL) THEN l_item_rec.attribute2 := l_opm_item.attribute2; END IF;
858 IF (g_attribute3 = 1 and l_item_rec.attribute3 is NULL) THEN l_item_rec.attribute3 := l_opm_item.attribute3; END IF;
859 IF (g_attribute4 = 1 and l_item_rec.attribute4 is NULL) THEN l_item_rec.attribute4 := l_opm_item.attribute4; END IF;
860 IF (g_attribute5 = 1 and l_item_rec.attribute5 is NULL) THEN l_item_rec.attribute5 := l_opm_item.attribute5; END IF;
861 IF (g_attribute6 = 1 and l_item_rec.attribute6 is NULL) THEN l_item_rec.attribute6 := l_opm_item.attribute6; END IF;
862 IF (g_attribute7 = 1 and l_item_rec.attribute7 is NULL) THEN l_item_rec.attribute7 := l_opm_item.attribute7; END IF;
863 IF (g_attribute8 = 1 and l_item_rec.attribute8 is NULL) THEN l_item_rec.attribute8 := l_opm_item.attribute8; END IF;
864 IF (g_attribute9 = 1 and l_item_rec.attribute9 is NULL) THEN l_item_rec.attribute9 := l_opm_item.attribute9; END IF;
865 IF (g_attribute10 = 1 and l_item_rec.attribute10 is NULL) THEN l_item_rec.attribute10 := l_opm_item.attribute10; END IF;
866 IF (g_attribute11 = 1 and l_item_rec.attribute11 is NULL) THEN l_item_rec.attribute11 := l_opm_item.attribute11; END IF;
867 IF (g_attribute12 = 1 and l_item_rec.attribute12 is NULL) THEN l_item_rec.attribute12 := l_opm_item.attribute12; END IF;
868 IF (g_attribute13 = 1 and l_item_rec.attribute13 is NULL) THEN l_item_rec.attribute13 := l_opm_item.attribute13; END IF;
869 IF (g_attribute14 = 1 and l_item_rec.attribute14 is NULL) THEN l_item_rec.attribute14 := l_opm_item.attribute14; END IF;
870 IF (g_attribute15 = 1 and l_item_rec.attribute15 is NULL) THEN l_item_rec.attribute15 := l_opm_item.attribute15; END IF;
871 IF (g_attribute16 = 1 and l_item_rec.attribute16 is NULL) THEN l_item_rec.attribute16 := l_opm_item.attribute16; END IF;
872 IF (g_attribute17 = 1 and l_item_rec.attribute17 is NULL) THEN l_item_rec.attribute17 := l_opm_item.attribute17; END IF;
873 IF (g_attribute18 = 1 and l_item_rec.attribute18 is NULL) THEN l_item_rec.attribute18 := l_opm_item.attribute18; END IF;
874 IF (g_attribute19 = 1 and l_item_rec.attribute19 is NULL) THEN l_item_rec.attribute19 := l_opm_item.attribute19; END IF;
875 IF (g_attribute20 = 1 and l_item_rec.attribute20 is NULL) THEN l_item_rec.attribute20 := l_opm_item.attribute20; END IF;
876 IF (g_attribute21 = 1 and l_item_rec.attribute21 is NULL) THEN l_item_rec.attribute21 := l_opm_item.attribute21; END IF;
877 IF (g_attribute22 = 1 and l_item_rec.attribute22 is NULL) THEN l_item_rec.attribute22 := l_opm_item.attribute22; END IF;
878 IF (g_attribute23 = 1 and l_item_rec.attribute23 is NULL) THEN l_item_rec.attribute23 := l_opm_item.attribute23; END IF;
879 IF (g_attribute24 = 1 and l_item_rec.attribute24 is NULL) THEN l_item_rec.attribute24 := l_opm_item.attribute24; END IF;
880 IF (g_attribute25 = 1 and l_item_rec.attribute25 is NULL) THEN l_item_rec.attribute25 := l_opm_item.attribute25; END IF;
881 IF (g_attribute26 = 1 and l_item_rec.attribute26 is NULL) THEN l_item_rec.attribute26 := l_opm_item.attribute26; END IF;
882 IF (g_attribute27 = 1 and l_item_rec.attribute27 is NULL) THEN l_item_rec.attribute27 := l_opm_item.attribute27; END IF;
883 IF (g_attribute28 = 1 and l_item_rec.attribute28 is NULL) THEN l_item_rec.attribute28 := l_opm_item.attribute28; END IF;
884 IF (g_attribute29 = 1 and l_item_rec.attribute29 is NULL) THEN l_item_rec.attribute29 := l_opm_item.attribute29; END IF;
885 IF (g_attribute30 = 1 and l_item_rec.attribute30 is NULL) THEN l_item_rec.attribute30 := l_opm_item.attribute30; END IF;
886
887 -- l_item_rec.GLOBAL_ATTRIBUTE_CATEGORY := NULL;
888 -- l_item_rec.GLOBAL_ATTRIBUTE1 := NULL;
889 -- l_item_rec.GLOBAL_ATTRIBUTE10 := NULL;
890 l_item_rec.CREATION_DATE := SYSDATE;
891 l_item_rec.CREATED_BY := l_opm_item.created_by;
892 l_item_rec.LAST_UPDATE_DATE := SYSDATE;
893 l_item_rec.LAST_UPDATED_BY := l_opm_item.last_updated_by;
894 l_item_rec.LAST_UPDATE_LOGIN := NULL;
895
896 IF p_item_source = 'GR' THEN
897 l_recipe_enabled_flag := 'Y';
898 END IF;
899 -- Call the API to create/ update item item
900 IF (l_action = 'I') THEN
901
902 l_event := 'ORG_ASSIGN';
903 IF (p_organization_type = 'M') THEN
904 l_event := 'INSERT';
905 END IF;
906
907 -- -- dbms_output.put_line ('Event = '||l_event);
908 fnd_msg_pub.initialize;
909 INV_ITEM_PVT.Create_Item( p_item_rec => l_item_rec
910 ,P_Item_Category_Struct_Id => NULL
911 ,P_Inv_Install => INV_Item_Util.Appl_Install().INV
912 ,P_Master_Org_Id => p_master_org_id
913 ,P_Category_Set_Id => NULL
914 ,P_Item_Category_Id => NULL
915 ,P_Event => l_event
916 ,x_row_Id => v_rowid
917 ,P_Default_Move_Order_Sub_Inv => NULL
918 ,P_Default_Receiving_Sub_Inv => NULL
919 ,P_Default_Shipping_Sub_Inv => NULL
920 );
921 ELSE
922 fnd_msg_pub.initialize;
923 INV_ITEM_PVT.Update_Item(
924 p_item_rec => l_item_rec,
925 P_Item_Category_Struct_Id => NULL,
926 P_Inv_Install => INV_Item_Util.Appl_Install().INV,
927 P_Master_Org_Id => p_master_org_id,
928 P_Category_Set_Id => NULL,
929 P_Item_Category_Id => NULL,
930 P_Mode => 'UPDATE',
931 P_Updateble_Item => NULL,
932 P_Cost_Txn => NULL,
933 P_Item_Cost_Details => NULL,
934 P_Inv_Item_status_old => l_item_rec.INVENTORY_ITEM_STATUS_CODE,
935 P_Default_Move_Order_Sub_Inv => '!',
936 P_Default_Receiving_Sub_Inv => '!',
937 P_Default_Shipping_Sub_Inv => '!');
938
939 END IF;
940
941 g_inventory_item_id := l_item_rec.INVENTORY_ITEM_ID;
942 x_inventory_item_id := l_item_rec.INVENTORY_ITEM_ID;
943
944 IF p_item_source = 'GMI' THEN
945
946 -- Update the item description in the TL tables.
947 FOR d in c_ic_item_mst_tl LOOP
948 UPDATE mtl_system_items_tl
949 SET description = d.item_desc1,
950 long_description = nvl(long_description, d.item_desc2),
951 source_lang = d.source_lang,
952 last_update_date = d.last_update_date,
953 last_updated_by = d.last_updated_by
954 WHERE
955 organization_id = p_organization_id AND
956 inventory_item_id = l_item_rec.INVENTORY_ITEM_ID AND
957 language = d.language;
958 END LOOP;
959
960 -- Bug 5489195 - Added migrated_ind in the update
961 UPDATE ic_item_mst_b_mig
962 SET
963 inventory_item_id = x_inventory_item_id,
964 migrated_ind = 1,
965 last_update_date = sysdate,
966 last_updated_by = 0
967 WHERE
968 item_id = p_item_id AND
969 organization_id = p_organization_id;
970
971 IF SQL%ROWCOUNT = 0 THEN
972 INSERT INTO ic_item_mst_b_mig(
973 item_id,
974 organization_id,
975 inventory_item_id,
976 migrated_ind,
977 creation_date,
978 created_by,
979 last_update_date,
980 last_updated_by,
981 last_update_login
982 )values(
983 p_item_id,
984 p_organization_id,
985 x_inventory_item_id,
986 1,
987 sysdate,
988 0,
989 sysdate,
990 0,
991 NULL
992 );
993 END IF;
994 END IF;
995
996 -- Jatinder - 11/30/06 - Move commit to the main procedure to avoid deadlocks. 5690686.
997 EXCEPTION
998 WHEN e_error THEN
999 ROLLBACK;
1000 WHEN FND_API.G_EXC_ERROR THEN
1001 x_failure_count := x_failure_count + 1;
1002 FND_MSG_PUB.Count_AND_GET (p_count => l_msg_count, p_data => l_msg_data);
1003 FOR i in 1..l_msg_count LOOP
1004 -- dbms_output.put_line (substr(fnd_msg_pub.get_detail(i, NULL),1,255));
1005 GMA_COMMON_LOGGING.gma_migration_central_log (
1006 p_run_id => p_migration_run_id,
1007 p_log_level => FND_LOG.LEVEL_ERROR,
1008 p_message_token => 'GMI_UNEXPECTED_ERROR',
1009 p_table_name => 'IC_ITEM_MST_B',
1010 p_context => 'ITEMS',
1011 p_token1 => 'ERROR',
1012 p_param1 => fnd_msg_pub.get_detail(i, NULL),
1013 p_param2 => NULL,
1014 p_param3 => NULL,
1015 p_param4 => NULL,
1016 p_param5 => NULL,
1017 p_db_error => NULL,
1018 p_app_short_name => 'FND');
1019 END LOOP;
1020 ROLLBACK;
1021
1022 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1023
1024 x_failure_count := x_failure_count + 1;
1025 FND_MSG_PUB.Count_AND_GET (p_count => l_msg_count, p_data => l_msg_data);
1026 FOR i in 1..l_msg_count LOOP
1027 -- dbms_output.put_line (substr(fnd_msg_pub.get_detail(i, NULL),1,255));
1028 GMA_COMMON_LOGGING.gma_migration_central_log (
1029 p_run_id => p_migration_run_id,
1030 p_log_level => FND_LOG.LEVEL_ERROR,
1031 p_message_token => 'GMI_UNEXPECTED_ERROR',
1032 p_table_name => 'IC_ITEM_MST_B',
1033 p_context => 'ITEMS',
1034 p_token1 => 'ERROR',
1035 p_param1 => fnd_msg_pub.get_detail(i, NULL),
1036 p_param2 => NULL,
1037 p_param3 => NULL,
1038 p_param4 => NULL,
1039 p_param5 => NULL,
1040 p_db_error => NULL,
1041 p_app_short_name => 'FND');
1042 END LOOP;
1043 ROLLBACK;
1044
1045 WHEN OTHERS THEN
1046 x_failure_count := x_failure_count + 1;
1047 FND_MSG_PUB.Count_AND_GET (p_count => l_msg_count, p_data => l_msg_data);
1048 FOR i in 1..l_msg_count LOOP
1049 -- dbms_output.put_line (substr(fnd_msg_pub.get_detail(i, NULL),1,255));
1050 GMA_COMMON_LOGGING.gma_migration_central_log (
1051 p_run_id => p_migration_run_id,
1052 p_log_level => FND_LOG.LEVEL_ERROR,
1053 p_message_token => 'GMI_UNEXPECTED_ERROR',
1054 p_table_name => 'IC_ITEM_MST_B',
1055 p_context => 'ITEMS',
1056 p_token1 => 'ERROR',
1057 p_param1 => fnd_msg_pub.get_detail(i, NULL),
1058 p_param2 => NULL,
1059 p_param3 => NULL,
1060 p_param4 => NULL,
1061 p_param5 => NULL,
1062 p_db_error => NULL,
1063 p_app_short_name => 'FND');
1064 END LOOP;
1065 -- dbms_output.put_line (substr(SQLERRM,1,255));
1066 GMA_COMMON_LOGGING.gma_migration_central_log (
1067 p_run_id => p_migration_run_id,
1068 p_log_level => FND_LOG.LEVEL_ERROR,
1069 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1070 p_table_name => 'IC_ITEM_MST_B',
1071 p_context => 'ITEMS',
1072 p_param1 => NULL,
1073 p_param2 => NULL,
1074 p_param3 => NULL,
1075 p_param4 => NULL,
1076 p_param5 => NULL,
1077 p_db_error => SQLERRM,
1078 p_app_short_name => 'GMA');
1079 ROLLBACK;
1080 END;
1081
1082 /*====================================================================
1083 -- PROCEDURE:
1084 -- validate_item_controls
1085 --
1086 -- DESCRIPTION:
1087 -- This PL/SQL procedure is used to validate that item attribute
1088 -- control is set to the correct level.
1089 --
1090 -- PARAMETERS:
1091 -- P_migration_run_id - id to use to write to migration log
1092 --
1093 -- SYNOPSIS:
1094 -- validate_item_controls(p_migartion_id => l_migration_id);
1095 --
1096 -- HISTORY
1097 -- Jatinder Gogna - Created - 03/25/05
1098 --====================================================================*/
1099
1100 PROCEDURE validate_item_controls
1101 ( p_migration_run_id IN NUMBER)
1102 IS
1103
1104 CURSOR c_master_attributes IS
1105 SELECT attribute_name FROM mtl_item_attributes
1106 WHERE
1107 control_level = 1 AND
1108 attribute_name IN ( 'MTL_SYSTEM_ITEMS.TRACKING_QUANTITY_IND',
1109 'MTL_SYSTEM_ITEMS.ONT_PRICING_QTY_SOURCE',
1110 'MTL_SYSTEM_ITEMS.SECONDARY_DEFAULT_IND',
1111 'MTL_SYSTEM_ITEMS.SECONDARY_UOM_CODE',
1112 'MTL_SYSTEM_ITEMS.DUAL_UOM_DEVIATION_HIGH',
1113 'MTL_SYSTEM_ITEMS.DUAL_UOM_DEVIATION_LOW',
1114 'MTL_SYSTEM_ITEMS.ITEM_TYPE',
1115 'MTL_SYSTEM_ITEMS.AUTO_LOT_ALPHA_PREFIX',
1116 'MTL_SYSTEM_ITEMS.ENG_ITEM_FLAG',
1117 'MTL_SYSTEM_ITEMS.ITEM_TYPE',
1118 'MTL_SYSTEM_ITEMS.LOCATION_CONTROL_CODE',
1119 'MTL_SYSTEM_ITEMS.LOT_CONTROL_CODE',
1120 'MTL_SYSTEM_ITEMS.LOT_STATUS_ENABLED',
1121 'MTL_SYSTEM_ITEMS.START_AUTO_LOT_NUMBER') AND
1122 EXISTS (
1123 SELECT 1
1124 FROM mtl_parameters mo, mtl_parameters co
1125 WHERE
1126 mo.organization_id = co.master_organization_id AND
1127 Decode(mo.process_orgn_code, NULL, 'N', 'Y') <> Decode(co.process_orgn_code, NULL, 'N', 'Y'));
1128 BEGIN
1129
1130 -- Check if certain item attributes are set to be controlled at master level
1131 -- Just log error. Do not stop the migration for this error.
1132 FOR ia IN c_master_attributes LOOP
1133 -- dbms_output.put_line ('Attribute '|| ia.attribute_name || ' is controlled at master organization level and may result in migration issues if the attribute values are different for process and discrete organization');
1134 GMA_COMMON_LOGGING.gma_migration_central_log (
1135 p_run_id => p_migration_run_id,
1136 p_log_level => FND_LOG.LEVEL_ERROR,
1137 p_message_token => 'GMI_MIG_ITEM_ATTRIBUTE',
1138 p_table_name => 'IC_ITEM_MST_B',
1139 p_context => 'ITEMS',
1140 p_param1 => ia.attribute_name,
1141 p_param2 => NULL,
1142 p_param3 => NULL,
1143 p_param4 => NULL,
1144 p_param5 => NULL,
1145 p_db_error => NULL,
1146 p_app_short_name => 'GMI');
1147 END LOOP;
1148 END;
1149
1150 /*====================================================================
1151 -- PROCEDURE:
1152 -- validate_desc_flex_definition
1153 --
1154 -- DESCRIPTION:
1155 -- This PL/SQL procedure is used to validate the conflict
1156 -- in desc flexfield usage for discrete and OPM Items.
1157 --
1158 -- PARAMETERS:
1159 -- P_migration_run_id - id to use to write to migration log
1160 --
1161 -- SYNOPSIS:
1162 -- validate_desc_flex_definition(p_migartion_id => l_migration_id);
1163 --
1164 -- HISTORY
1165 -- Jatinder Gogna - Created - 03/25/05
1166 --====================================================================*/
1167
1168 PROCEDURE validate_desc_flex_definition
1169 ( p_migration_run_id IN NUMBER)
1170 IS
1171
1172 CURSOR c_get_desc_flex_col_conflict IS
1173
1174 SELECT col.descriptive_flex_context_code,
1175 col.application_column_name,
1176 col.end_user_column_name
1177 FROM fnd_descr_flex_column_usages col,
1178 fnd_descr_flex_contexts cont
1179 WHERE
1180 col.application_id = 551 and
1181 col.descriptive_flexfield_name = 'ITEM_FLEX' and
1182 col.enabled_flag = 'Y' and
1183 col.application_id = cont.application_id and
1184 col.descriptive_flexfield_name = cont.descriptive_flexfield_name and
1185 col.descriptive_flex_context_code = cont.descriptive_flex_context_code and
1186 cont.enabled_flag = 'Y' and
1187 col.application_column_name in (
1188 SELECT col2.application_column_name
1189 FROM fnd_descr_flex_column_usages col2,
1190 fnd_descr_flex_contexts cont2
1191 WHERE
1192 col2.application_id = 401 and
1193 col2.descriptive_flexfield_name = 'MTL_SYSTEM_ITEMS' and
1194 col2.enabled_flag = 'Y' and
1195 col.application_id = cont2.application_id and
1196 col.descriptive_flexfield_name = cont2.descriptive_flexfield_name and
1197 col.descriptive_flex_context_code = cont2.descriptive_flex_context_code and
1198 cont2.enabled_flag = 'Y' );
1199
1200 CURSOR c_get_opm_desc_flex_cols IS
1201
1202 SELECT col.descriptive_flex_context_code,
1203 col.application_column_name,
1204 col.end_user_column_name
1205 FROM fnd_descr_flex_column_usages col,
1206 fnd_descr_flex_contexts cont
1207 WHERE
1208 col.application_id = 551 and
1209 col.descriptive_flexfield_name = 'ITEM_FLEX' and
1210 col.enabled_flag = 'Y' and
1211 col.application_id = cont.application_id and
1212 col.descriptive_flexfield_name = cont.descriptive_flexfield_name and
1213 col.descriptive_flex_context_code = cont.descriptive_flex_context_code and
1214 cont.enabled_flag = 'Y';
1215
1216 l_opm_context VARCHAR2(30);
1217 l_odm_context VARCHAR2(30);
1218 BEGIN
1219
1220 g_desc_flex_conflict := 0;
1221 BEGIN
1222 SELECT cont.descriptive_flex_context_code
1223 INTO l_opm_context
1224 FROM fnd_descr_flex_contexts cont
1225 WHERE cont.application_id = 551 and
1226 cont.descriptive_flexfield_name = 'ITEM_FLEX' and
1227 cont.enabled_flag = 'Y' and
1228 cont.global_flag = 'N' and
1229 rownum = 1;
1230 g_attribute_context := 1;
1231 EXCEPTION
1232 WHEN NO_DATA_FOUND THEN
1233 NULL;
1234 END;
1235 BEGIN
1236 SELECT cont.descriptive_flex_context_code
1237 INTO l_odm_context
1238 FROM fnd_descr_flex_contexts cont
1239 WHERE cont.application_id = 401 and
1240 cont.descriptive_flexfield_name = 'MTL_SYSTEM_ITEMS' and
1241 cont.enabled_flag = 'Y' and
1242 cont.global_flag = 'N' and
1243 rownum = 1;
1244 EXCEPTION
1245 WHEN NO_DATA_FOUND THEN
1246 NULL;
1247 END;
1248 IF (l_opm_context is not NULL and l_odm_context is not NULL) THEN
1249 g_desc_flex_conflict := 1;
1250 -- Log Error
1251 -- dbms_output.put_line ('Desc flexfield conflict. OPM context: '|| l_opm_context ||', ODM context: '|| l_odm_context);
1252 GMA_COMMON_LOGGING.gma_migration_central_log (
1253 p_run_id => p_migration_run_id,
1254 p_log_level => FND_LOG.LEVEL_ERROR,
1255 p_message_token => 'GMI_MIG_DFLEX_CONTEXT_CONFLICT',
1256 p_table_name => 'IC_ITEM_MST_B',
1257 p_context => 'ITEMS',
1258 p_param1 => l_opm_context,
1259 p_param2 => l_odm_context,
1260 p_param3 => NULL,
1261 p_param4 => NULL,
1262 p_param5 => NULL,
1263 p_db_error => NULL,
1264 p_app_short_name => 'GMI');
1265 END IF;
1266
1267 -- Check if any OPM item decsriptive flexfield column is used in
1268 -- Discrete item flexfield
1269
1270 FOR conflict_columns in c_get_desc_flex_col_conflict LOOP
1271 -- If we are here, that means we have a conflict
1272 g_desc_flex_conflict := 1;
1273
1274 -- dbms_output.put_line ('Desc flexfield column conflict.');
1275 GMA_COMMON_LOGGING.gma_migration_central_log (
1276 p_run_id => p_migration_run_id,
1277 p_log_level => FND_LOG.LEVEL_ERROR,
1278 p_message_token => 'GMI_MIG_DFLEX_COL_CONFLICT',
1279 p_table_name => 'IC_ITEM_MST_B',
1280 p_context => 'ITEMS',
1281 p_param1 => conflict_columns.descriptive_flex_context_code,
1282 p_param2 => conflict_columns.end_user_column_name,
1283 p_param3 => conflict_columns.application_column_name,
1284 p_param4 => NULL,
1285 p_param5 => NULL,
1286 p_db_error => NULL,
1287 p_app_short_name => 'GMI');
1288 END LOOP;
1289
1290 -- If no conflict is found, set the control variable for item migration
1291 IF (g_desc_flex_conflict = 1) THEN
1292 RETURN;
1293 END IF;
1294 FOR opm_desc_cols in c_get_opm_desc_flex_cols LOOP
1295
1296 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE1') THEN g_attribute1 := 1; END IF;
1297 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE2') THEN g_attribute2 := 1; END IF;
1298 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE3') THEN g_attribute3 := 1; END IF;
1299 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE4') THEN g_attribute4 := 1; END IF;
1300 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE5') THEN g_attribute5 := 1; END IF;
1301 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE6') THEN g_attribute6 := 1; END IF;
1302 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE7') THEN g_attribute7 := 1; END IF;
1303 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE8') THEN g_attribute8 := 1; END IF;
1304 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE9') THEN g_attribute9 := 1; END IF;
1305 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE10') THEN g_attribute10 := 1; END IF;
1306 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE11') THEN g_attribute11 := 1; END IF;
1307 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE12') THEN g_attribute12 := 1; END IF;
1308 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE13') THEN g_attribute13 := 1; END IF;
1309 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE14') THEN g_attribute14 := 1; END IF;
1310 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE15') THEN g_attribute15 := 1; END IF;
1311 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE16') THEN g_attribute16 := 1; END IF;
1312 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE17') THEN g_attribute17 := 1; END IF;
1313 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE18') THEN g_attribute18 := 1; END IF;
1314 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE19') THEN g_attribute19 := 1; END IF;
1315 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE20') THEN g_attribute20 := 1; END IF;
1316 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE21') THEN g_attribute21 := 1; END IF;
1317 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE22') THEN g_attribute22 := 1; END IF;
1318 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE23') THEN g_attribute23 := 1; END IF;
1319 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE24') THEN g_attribute24 := 1; END IF;
1320 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE25') THEN g_attribute25 := 1; END IF;
1321 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE26') THEN g_attribute26 := 1; END IF;
1322 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE27') THEN g_attribute27 := 1; END IF;
1323 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE28') THEN g_attribute28 := 1; END IF;
1324 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE29') THEN g_attribute29 := 1; END IF;
1325 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE30') THEN g_attribute30 := 1; END IF;
1326 END LOOP;
1327 END;
1328
1329 /*====================================================================
1330 -- PROCEDURE:
1331 -- get_ODM_item
1332 --
1333 -- DESCRIPTION:
1334 -- This PL/SQL procedure is used to get the Inventory Item Id for
1335 -- an OPM item. If the OPM item is not migrated, it will migrate the
1336 -- the item and return the discrete inventory item id.
1337 --
1338 -- PARAMETERS:
1339 -- P_migration_run_id - id to use to write to migration log
1340 -- p_item_id - OPM item id
1341 -- p_organization_id - Inventory organization of the item in Oracle
1342 -- Inventory. Item will be migrated to this organization.
1343 -- p_mode - Use the value 'FORCE' if you want to migrate an OPM item
1344 -- which has been migrated already. Leave it NULL for the normal
1345 -- use.
1346 -- p_commit - flag to indicate if commit should be performed.
1347 -- x_inventory_item_id - Discrete inventory item id.
1348 -- x_failure_count - Number of exceptions occurred.
1349 --
1350 -- SYNOPSIS:
1351 -- get_ODM_item( p_migartion_id => l_migration_id,
1352 -- p_item_id => l_item_id,
1353 -- p_organization_id => l_organization_id,
1354 -- p_mode => NULL,
1355 -- p_commit => 'Y',
1356 -- x_inventory_item_id => l_inventory_item_id,
1357 -- x_failure_count => l_failure_count);
1358 --
1359 -- HISTORY
1360 -- Jatinder Gogna - Created - 03/25/05
1361 --====================================================================*/
1362
1363 PROCEDURE get_ODM_item
1364 ( p_migration_run_id IN NUMBER
1365 , p_item_id IN NUMBER
1366 , p_organization_id IN NUMBER
1367 , p_mode IN VARCHAR2
1368 , p_commit IN VARCHAR2
1369 , x_inventory_item_id OUT NOCOPY NUMBER
1370 , x_failure_count OUT NOCOPY NUMBER
1371 , p_item_code IN VARCHAR2 DEFAULT NULL
1372 , p_item_source IN VARCHAR2 DEFAULT 'GMI'
1373 ) IS
1374 PRAGMA AUTONOMOUS_TRANSACTION;
1375 l_migrated_ind PLS_INTEGER;
1376 l_migrated_ind_m PLS_INTEGER;
1377 l_master_organization_id NUMBER;
1378 l_action VARCHAR2(1);
1379 l_msg_count NUMBER;
1380 l_msg_data VARCHAR2(2000);
1381 i PLS_INTEGER;
1382 dv PLS_INTEGER;
1383 BEGIN
1384 x_failure_count := 0;
1385 -- Validate input parameters
1386 IF ((p_item_source <> 'GMI' and p_item_source <> 'GR') or
1387 (p_item_source = 'GMI' and (p_item_id < 1 or p_item_id is NULL)) or
1388 (p_item_source = 'GR' and p_item_code is NULL) or
1389 p_organization_id < 1 or p_organization_id is NULL ) THEN
1390 -- Log validation error
1391 -- dbms_output.put_line ('Invalid parameters for item migration');
1392 GMA_COMMON_LOGGING.gma_migration_central_log (
1393 p_run_id => p_migration_run_id,
1394 p_log_level => FND_LOG.LEVEL_ERROR,
1395 p_message_token => 'GMI_MIG_INVALID_PARAMS',
1396 p_table_name => 'IC_ITEM_MST_B',
1397 p_context => 'ITEMS',
1398 p_param1 => NULL,
1399 p_param2 => NULL,
1400 p_param3 => NULL,
1401 p_param4 => NULL,
1402 p_param5 => NULL,
1403 p_db_error => NULL,
1404 p_app_short_name => 'GMI');
1405 x_failure_count := x_failure_count + 1;
1406 RETURN;
1407 END IF;
1408
1409 -- See if the value for the item is already cached
1410 IF p_item_source = 'GMI' and (nvl(p_mode, 'N') <> 'FORCE'
1411 and g_item_id = p_item_id and g_organization_id = p_organization_id
1412 and g_inventory_item_id is not NULL) THEN
1413 x_inventory_item_id := g_inventory_item_id;
1414 RETURN;
1415 END IF;
1416
1417 -- Check for flexfield conflicts
1418 IF (g_desc_flex_conflict is NULL) THEN
1419 validate_desc_flex_definition (p_migration_run_id);
1420 END IF;
1421
1422 IF (g_desc_flex_conflict = 1) THEN
1423 -- Log error
1424 -- No need to log any meesages as they were logged by the previous call
1425 x_failure_count := x_failure_count + 1;
1426 RETURN;
1427 END IF;
1428
1429 -- for GMI items check the value in ic_item_mst_b_mig table
1430 IF (p_item_source = 'GMI') THEN
1431 BEGIN
1432 g_item_id := p_item_id;
1433 g_organization_id := p_organization_id;
1434 g_inventory_item_id := NULL;
1435 l_migrated_ind := -1;
1436
1437 -- Select and lock the row to avoid errors associated with running this routine in
1438 -- parallel from routines using AD parrallel update logic.
1439 SELECT inventory_item_id, migrated_ind
1440 INTO g_inventory_item_id, l_migrated_ind
1441 FROM ic_item_mst_b_mig
1442 WHERE
1443 item_id = g_item_id AND
1444 organization_id = g_organization_id;
1445 --FOR UPDATE; 13811730
1446
1447 IF (nvl(p_mode, 'N') <> 'FORCE' and l_migrated_ind = 1) THEN
1448 x_inventory_item_id := g_inventory_item_id;
1449 COMMIT; -- Release the lock acquired above.
1450 RETURN;
1451 ELSIF ( nvl(l_migrated_ind,0) <> 1 ) THEN
1452
1453 -- 13811730 Lock this row for the parrallel run of this routine.
1454 SELECT 1
1455 INTO dv
1456 FROM ic_item_mst_b_mig
1457 WHERE
1458 item_id = g_item_id AND
1459 organization_id = g_organization_id
1460 FOR UPDATE;
1461
1462 END IF;
1463 EXCEPTION
1464 WHEN NO_DATA_FOUND THEN
1465 BEGIN
1466 INSERT INTO ic_item_mst_b_mig(
1467 item_id,
1468 organization_id,
1469 inventory_item_id,
1470 migrated_ind,
1471 creation_date,
1472 created_by,
1473 last_update_date,
1474 last_updated_by,
1475 last_update_login
1476 )values(
1477 g_item_id,
1478 g_organization_id,
1479 NULL,
1480 0,
1481 sysdate,
1482 0,
1483 sysdate,
1484 0,
1485 NULL
1486 );
1487 EXCEPTION
1488 WHEN DUP_VAL_ON_INDEX THEN -- Another parrallel run may have created this row already.
1489 NULL;
1490 END;
1491
1492 -- Lock this row for the parrallel run of this routine.
1493 SELECT 1
1494 INTO dv
1495 FROM ic_item_mst_b_mig
1496 WHERE
1497 item_id = g_item_id AND
1498 organization_id = g_organization_id
1499 FOR UPDATE;
1500 END;
1501 END IF;
1502
1503 -- This item needs migration
1504 -- Check the master organization to see if that has this item and
1505 -- has been migrated. For GR, item always need migration.
1506 BEGIN
1507 l_migrated_ind_m := -1;
1508 SELECT master_organization_id
1509 INTO l_master_organization_id
1510 FROM mtl_parameters
1511 WHERE
1512 organization_id = p_organization_id;
1513
1514 -- Select and lock the row to avoid errors associated with running this routine in
1515 -- parallel from routines using AD parrallel update logic.
1516 SELECT i.migrated_ind
1517 INTO l_migrated_ind_m
1518 FROM ic_item_mst_b_mig i
1519 WHERE
1520 i.organization_id = l_master_organization_id and
1521 i.item_id = p_item_id
1522 FOR UPDATE;
1523 EXCEPTION
1524 WHEN NO_DATA_FOUND THEN
1525 -- Jatinder - 11/30/06 - Use correct organization to lock migration records. 5690686.
1526 IF (p_item_source = 'GMI') THEN
1527 BEGIN
1528 INSERT INTO ic_item_mst_b_mig(
1529 item_id,
1530 organization_id,
1531 inventory_item_id,
1532 migrated_ind,
1533 creation_date,
1534 created_by,
1535 last_update_date,
1536 last_updated_by,
1537 last_update_login
1538 )values(
1539 p_item_id,
1540 l_master_organization_id,
1541 NULL,
1542 0,
1543 sysdate,
1544 0,
1545 sysdate,
1546 0,
1547 NULL
1548 );
1549 EXCEPTION
1550 WHEN DUP_VAL_ON_INDEX THEN -- Another parrallel run may have created this row already.
1551 NULL;
1552 END;
1553
1554 -- Lock this row for the parrallel run of this routine.
1555 SELECT 1
1556 INTO dv
1557 FROM ic_item_mst_b_mig
1558 WHERE
1559 item_id = p_item_id AND
1560 organization_id = l_master_organization_id
1561 FOR UPDATE;
1562 END IF;
1563 END;
1564
1565 -- -- dbms_output.put_line ('Master Org Id = '||to_char(l_master_organization_id)|| ', Migrated Ind = '||to_char(l_migrated_ind_m));
1566 -- If needed migrate the item in the master organization first.
1567
1568 -- Bug 5489195 - Need to handle null migrated ind value
1569 IF (p_mode = 'FORCE' or NVL(l_migrated_ind_m,-1) <> 1) THEN
1570 -- -- dbms_output.put_line ('Migrate to master org');
1571 migrate_OPM_item_to_ODM (
1572 p_migration_run_id,
1573 p_item_id,
1574 p_item_code,
1575 p_item_source,
1576 l_master_organization_id,
1577 l_master_organization_id,
1578 'M',
1579 p_commit,
1580 x_inventory_item_id,
1581 x_failure_count);
1582 IF (x_failure_count > 0) THEN
1583 ROLLBACK;
1584 RETURN;
1585 END IF;
1586 -- -- dbms_output.put_line ('Migrated succesfully to master');
1587 END IF;
1588
1589 -- Now migrate the OPM item in the chid organization.
1590 IF (p_organization_id <> l_master_organization_id ) THEN
1591 -- -- dbms_output.put_line ('Migrate to child org');
1592 migrate_OPM_item_to_ODM (
1593 p_migration_run_id,
1594 p_item_id,
1595 p_item_code,
1596 p_item_source,
1597 p_organization_id,
1598 l_master_organization_id,
1599 'C',
1600 p_commit,
1601 x_inventory_item_id,
1602 x_failure_count);
1603 IF (x_failure_count > 0) THEN
1604 ROLLBACK;
1605 RETURN;
1606 END IF;
1607 -- -- dbms_output.put_line ('Migrated succesfully to child');
1608 END IF;
1609
1610 -- Jatinder - 11/30/06 - Moved commit here to avoid deadlocks. 5690686.
1611 -- Autonomous transaction commit
1612 IF (p_commit <> FND_API.G_FALSE) THEN
1613 COMMIT;
1614 ELSE
1615 ROLLBACK; -- Since this is an autonomous transaction
1616 END IF;
1617 EXCEPTION
1618 WHEN OTHERS THEN
1619 x_failure_count := x_failure_count + 1;
1620 -- dbms_output.put_line (substr(SQLERRM,1,255));
1621 GMA_COMMON_LOGGING.gma_migration_central_log (
1622 p_run_id => p_migration_run_id,
1623 p_log_level => FND_LOG.LEVEL_ERROR,
1624 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1625 p_table_name => 'IC_ITEM_MST_B',
1626 p_context => 'ITEMS',
1627 p_param1 => NULL,
1628 p_param2 => NULL,
1629 p_param3 => NULL,
1630 p_param4 => NULL,
1631 p_param5 => NULL,
1632 p_db_error => SQLERRM,
1633 p_app_short_name => 'GMA');
1634 ROLLBACK;
1635 END;
1636
1637 /*====================================================================
1638 -- PROCEDURE:
1639 -- get_ODM_regulatory_item
1640 --
1641 -- DESCRIPTION:
1642 -- This PL/SQL procedure is used to get the Inventory Item Id for
1643 -- an OPM regulatory item.
1644 --
1645 -- PARAMETERS:
1646 -- P_migration_run_id - id to use to write to migration log
1647 -- p_item_code - OPM regulatory item code.
1648 -- p_organization_id - Inventory organization of the item in Oracle
1649 -- Inventory. Item will be migrated to this organization.
1650 -- p_mode - Use the value 'FORCE' if you want to migrate an OPM item
1651 -- which has been migrated already. Leave it NULL for the normal
1652 -- use.
1653 -- p_commit - flag to indicate if commit should be performed.
1654 -- x_inventory_item_id - Discrete inventory item id.
1655 -- x_failure_count - Number of exceptions occurred.
1656 --
1657 -- SYNOPSIS:
1658 -- get_ODM_regulatory_item( p_migartion_id => l_migration_id,
1659 -- p_item_code => l_item_code,
1660 -- p_organization_id => l_organization_id,
1661 -- p_mode => NULL,
1662 -- p_commit => 'Y',
1663 -- x_inventory_item_id => l_inventory_item_id,
1664 -- x_failure_count => l_failure_count);
1665 --
1666 -- HISTORY
1667 -- Jatinder Gogna - Created - 03/25/05
1668 --====================================================================*/
1669 PROCEDURE get_ODM_regulatory_item
1670 ( p_migration_run_id IN NUMBER
1671 , p_item_code IN VARCHAR2
1672 , p_organization_id IN NUMBER
1673 , p_mode IN VARCHAR2
1674 , p_commit IN VARCHAR2
1675 , x_inventory_item_id OUT NOCOPY NUMBER
1676 , x_failure_count OUT NOCOPY NUMBER) IS
1677
1678 BEGIN
1679 INV_OPM_Item_Migration.get_ODM_item (
1680 p_migration_run_id => p_migration_run_id,
1681 p_item_id => NULL,
1682 p_organization_id => p_organization_id,
1683 p_mode => p_mode,
1684 p_commit => p_commit,
1685 x_inventory_item_id => x_inventory_item_id,
1686 x_failure_count => x_failure_count,
1687 p_item_code => p_item_code,
1688 p_item_source => 'GR');
1689 END;
1690
1691
1692 /*====================================================================
1693 -- PROCEDURE:
1694 -- migrate_obsolete_columns
1695 --
1696 -- DESCRIPTION:
1697 -- This PL/SQL procedure is used to allow users to migrate the some
1698 -- of the obsolete columns in OPM Item master to the Discrete Item
1699 -- master flexfield. This script will not run automatically during
1700 -- the convergence migration.
1701 --
1702 -- PARAMETERS:
1703 -- P_migration_run_id - id to use to write to migration log
1704 -- p_obsolete_column_name - obsolete column name. Valid values:
1705 -- o ALT_ITEMA
1706 -- o ALT_ITEMB
1707 -- o MATCH_TYPE
1708 -- o UPC_CODE
1709 -- o QCITEM_ID
1710 -- o QCHOLD_RES_CODE
1711 -- p_flexfield_column_name - Descriptive flexfield column.
1712 -- p_commit - flag to indicate if commit shouldbe performed.
1713 -- x_failure_count - Number of exceptions occurred.
1714 --
1715 -- SYNOPSIS:
1716 -- migrate_obsolete_columns(p_migartion_id => l_migration_id,
1717 -- p_obsolete_column_name => 'UPC_CODE',
1718 -- p_flexfield_column_name => 'ATTRIBUTE15',
1719 -- p_commit => l_commit ,
1720 -- x_exception_count => l_exception_count );
1721 --
1722 -- HISTORY
1723 -- Jatinder Gogna - Created - 03/25/05
1724 --====================================================================*/
1725
1726 PROCEDURE migrate_obsolete_columns
1727 ( p_migration_run_id IN NUMBER
1728 , p_obsolete_column_name IN VARCHAR2
1729 , p_flexfield_column_name IN VARCHAR2
1730 , p_commit IN VARCHAR2
1731 , x_failure_count OUT NOCOPY NUMBER) IS
1732
1733 l_flexfield_column_name VARCHAR2(50);
1734 l_end_user_column_name VARCHAR2(50);
1735 l_migrated_ind NUMBER(5);
1736 l_count PLS_INTEGER;
1737 l_migrate_count PLS_INTEGER;
1738 l_obsolete_column_value VARCHAR2(240);
1739
1740 CURSOR c_item IS
1741 SELECT m.organization_id, m.inventory_item_id, i.alt_itema,
1742 i.alt_itemb, i.match_type, i.upc_code, i.qcitem_id,
1743 i.qchold_res_code
1744 FROM ic_item_mst_b i, ic_item_mst_b_mig m
1745 WHERE
1746 i.item_id = m.item_id AND
1747 m.migrated_ind = 1;
1748
1749 BEGIN
1750 x_failure_count := 0;
1751 l_migrate_count := 0;
1752 -- dbms_output.put_line ('Started ITEM OBSOLETE COLUMNS migration');
1753 GMA_COMMON_LOGGING.gma_migration_central_log (
1754 p_run_id => p_migration_run_id,
1755 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1756 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
1757 p_table_name => 'IC_ITEM_MST_B',
1758 p_context => 'ITEM OBSOLETE COLUMNS',
1759 p_param1 => NULL,
1760 p_param2 => NULL,
1761 p_param3 => NULL,
1762 p_param4 => NULL,
1763 p_param5 => NULL,
1764 p_db_error => NULL,
1765 p_app_short_name => 'GMA');
1766
1767 IF (p_obsolete_column_name is NULL or p_flexfield_column_name is NULL) THEN
1768 -- dbms_output.put_line ('Invalid parameters for obsolete column migration');
1769 GMA_COMMON_LOGGING.gma_migration_central_log (
1770 p_run_id => p_migration_run_id,
1771 p_log_level => FND_LOG.LEVEL_ERROR,
1772 p_message_token => 'GMI_MIG_INVALID_PARAMS',
1773 p_table_name => 'IC_ITEM_MST_B',
1774 p_context => 'ITEM OBSOLETE COLUMNS',
1775 p_param1 => NULL,
1776 p_param2 => NULL,
1777 p_param3 => NULL,
1778 p_param4 => NULL,
1779 p_param5 => NULL,
1780 p_db_error => NULL,
1781 p_app_short_name => 'GMI');
1782 x_failure_count := x_failure_count + 1;
1783 RETURN;
1784 END IF;
1785 IF ( p_obsolete_column_name <> 'ALT_ITEMA' AND
1786 p_obsolete_column_name <> 'ALT_ITEMB' AND
1787 p_obsolete_column_name <> 'MATCH_TYPE' AND
1788 p_obsolete_column_name <> 'UPC_CODE' AND
1789 p_obsolete_column_name <> 'QCITEM_ID' AND
1790 p_obsolete_column_name <> 'QCHOLD_RES_CODE') THEN
1791
1792 -- dbms_output.put_line ('Invalid value for the obsolete column :' || p_obsolete_column_name);
1793 GMA_COMMON_LOGGING.gma_migration_central_log (
1794 p_run_id => p_migration_run_id,
1795 p_log_level => FND_LOG.LEVEL_ERROR,
1796 p_message_token => 'GMI_MIG_INVALID_OBS_COL',
1797 p_table_name => 'IC_ITEM_MST_B',
1798 p_context => 'ITEM OBSOLETE COLUMNS',
1799 p_param1 => p_obsolete_column_name,
1800 p_param2 => NULL,
1801 p_param3 => NULL,
1802 p_param4 => NULL,
1803 p_param5 => NULL,
1804 p_db_error => NULL,
1805 p_app_short_name => 'GMI');
1806 x_failure_count := x_failure_count + 1;
1807 RETURN;
1808 END IF;
1809
1810 -- Check if the obsolete column is already mapped / migrated.
1811 BEGIN
1812 SELECT flexfield_column_name, migrated_ind
1813 INTO l_flexfield_column_name, l_migrated_ind
1814 FROM gmi_obsolete_item_columns
1815 WHERE
1816 obsolete_column_name = p_obsolete_column_name AND
1817 migrated_ind = 1;
1818
1819 IF ( l_flexfield_column_name <> p_flexfield_column_name ) THEN
1820 -- dbms_output.put_line ('Obsolete column already migrated to different flexfield column :' || l_flexfield_column_name);
1821 GMA_COMMON_LOGGING.gma_migration_central_log (
1822 p_run_id => p_migration_run_id,
1823 p_log_level => FND_LOG.LEVEL_ERROR,
1824 p_message_token => 'GMI_MIG_COL_ALREADY_MIGRATED',
1825 p_table_name => 'IC_ITEM_MST_B',
1826 p_context => 'ITEM OBSOLETE COLUMNS',
1827 p_param1 => p_obsolete_column_name,
1828 p_param2 => NULL,
1829 p_param3 => NULL,
1830 p_param4 => NULL,
1831 p_param5 => NULL,
1832 p_db_error => NULL,
1833 p_app_short_name => 'GMI');
1834 x_failure_count := x_failure_count + 1;
1835 RETURN;
1836 END IF;
1837 EXCEPTION
1838 WHEN NO_DATA_FOUND THEN
1839 NULL;
1840 END ;
1841
1842 -- Validate the flexfield column
1843 BEGIN
1844 SELECT 1
1845 INTO l_count
1846 FROM fnd_tables t, fnd_columns c
1847 WHERE
1848 t.application_id = 401 AND
1849 t.table_name = 'MTL_SYSTEM_ITEMS_B' AND
1850 t.application_id = c.application_id AND
1851 t.table_id = c.table_id AND
1852 c.flexfield_application_id = 401 AND
1853 c.flexfield_name = 'MTL_SYSTEM_ITEMS' AND
1854 c.flexfield_usage_code = 'D' AND
1855 c.column_name = p_flexfield_column_name;
1856 EXCEPTION
1857 WHEN NO_DATA_FOUND THEN
1858 -- dbms_output.put_line ('Invalid value for the flexfield column :' || p_flexfield_column_name);
1859 GMA_COMMON_LOGGING.gma_migration_central_log (
1860 p_run_id => p_migration_run_id,
1861 p_log_level => FND_LOG.LEVEL_ERROR,
1862 p_message_token => 'GMI_MIG_INVALID_FLEX_COL',
1863 p_table_name => 'IC_ITEM_MST_B',
1864 p_context => 'ITEM OBSOLETE COLUMNS',
1865 p_param1 => p_flexfield_column_name,
1866 p_param2 => NULL,
1867 p_param3 => NULL,
1868 p_param4 => NULL,
1869 p_param5 => NULL,
1870 p_db_error => NULL,
1871 p_app_short_name => 'GMI');
1872 x_failure_count := x_failure_count + 1;
1873 RETURN;
1874 END;
1875
1876 -- Check if the desc flexfield column is used for another column
1877 BEGIN
1878 SELECT end_user_column_name
1879 INTO l_end_user_column_name
1880 FROM fnd_descr_flex_column_usages col2,
1881 fnd_descr_flex_contexts cont2
1882 WHERE
1883 col2.application_id IN (401, 551) and
1884 col2.descriptive_flexfield_name in ('MTL_SYSTEM_ITEMS', 'ITEM_FLEX') AND
1885 col2.enabled_flag = 'Y' and
1886 col2.application_id = cont2.application_id and
1887 col2.descriptive_flexfield_name = cont2.descriptive_flexfield_name and
1888 col2.descriptive_flex_context_code = cont2.descriptive_flex_context_code and
1889 cont2.enabled_flag = 'Y' AND
1890 col2.application_column_name = p_flexfield_column_name;
1891
1892 IF (l_end_user_column_name <> p_obsolete_column_name) THEN
1893 -- dbms_output.put_line ('Flexfield column is already in use : '||p_flexfield_column_name);
1894 GMA_COMMON_LOGGING.gma_migration_central_log (
1895 p_run_id => p_migration_run_id,
1896 p_log_level => FND_LOG.LEVEL_ERROR,
1897 p_message_token => 'GMI_MIG_FLEX_COL_IN_USE',
1898 p_table_name => 'IC_ITEM_MST_B',
1899 p_context => 'ITEM OBSOLETE COLUMNS',
1900 p_param1 => p_flexfield_column_name,
1901 p_param2 => NULL,
1902 p_param3 => NULL,
1903 p_param4 => NULL,
1904 p_param5 => NULL,
1905 p_db_error => NULL,
1906 p_app_short_name => 'GMI');
1907 x_failure_count := x_failure_count + 1;
1908 RETURN;
1909 END IF;
1910 EXCEPTION
1911 WHEN NO_DATA_FOUND THEN
1912 -- Update flexfield definition
1913 fnd_flex_dsc_api.set_session_mode ('customer_data');
1914 fnd_flex_dsc_api.create_segment(
1915 appl_short_name => 'INV',
1916 flexfield_name => 'MTL_SYSTEM_ITEMS',
1917 context_name => 'Global Data Elements',
1918 name => p_obsolete_column_name,
1919 column => p_flexfield_column_name,
1920 description => p_obsolete_column_name,
1921 sequence_number => 100,
1922 enabled => 'Y',
1923 displayed => 'Y',
1924 value_set => NULL,
1925 default_type => NULL,
1926 default_value => NULL,
1927 required => 'N',
1928 security_enabled => 'N',
1929 display_size => 50,
1930 description_size => 50,
1931 concatenated_description_size => 25,
1932 list_of_values_prompt => p_obsolete_column_name,
1933 window_prompt => p_obsolete_column_name,
1934 range => NULL,
1935 srw_parameter => NULL,
1936 runtime_property_function => NULL);
1937 END;
1938
1939
1940 -- Migrate the obsolete values
1941 FOR i in c_item LOOP
1942 SELECT DECODE (p_obsolete_column_name,
1943 'ALT_ITEMA', i.ALT_ITEMA,
1944 'ALT_ITEMB', i.ALT_ITEMB,
1945 'MATCH_TYPE', i.MATCH_TYPE,
1946 'UPC_CODE', i.UPC_CODE,
1947 'QCITEM_ID', i.QCITEM_ID,
1948 'QCHOLD_RES_CODE', i.QCHOLD_RES_CODE)
1949 INTO l_obsolete_column_value
1950 FROM dual
1951 WHERE rownum = 1;
1952
1953 UPDATE mtl_system_items_b
1954 SET
1955 ATTRIBUTE1 = DECODE (p_flexfield_column_name, 'ATTRIBUTE1', l_obsolete_column_value, ATTRIBUTE1),
1956 ATTRIBUTE2 = DECODE (p_flexfield_column_name, 'ATTRIBUTE2', l_obsolete_column_value, ATTRIBUTE2),
1957 ATTRIBUTE3 = DECODE (p_flexfield_column_name, 'ATTRIBUTE3', l_obsolete_column_value, ATTRIBUTE3),
1958 ATTRIBUTE4 = DECODE (p_flexfield_column_name, 'ATTRIBUTE4', l_obsolete_column_value, ATTRIBUTE4),
1959 ATTRIBUTE5 = DECODE (p_flexfield_column_name, 'ATTRIBUTE5', l_obsolete_column_value, ATTRIBUTE5),
1960 ATTRIBUTE6 = DECODE (p_flexfield_column_name, 'ATTRIBUTE6', l_obsolete_column_value, ATTRIBUTE6),
1961 ATTRIBUTE7 = DECODE (p_flexfield_column_name, 'ATTRIBUTE7', l_obsolete_column_value, ATTRIBUTE7),
1962 ATTRIBUTE8 = DECODE (p_flexfield_column_name, 'ATTRIBUTE8', l_obsolete_column_value, ATTRIBUTE8),
1963 ATTRIBUTE9 = DECODE (p_flexfield_column_name, 'ATTRIBUTE9', l_obsolete_column_value, ATTRIBUTE9),
1964 ATTRIBUTE10 = DECODE (p_flexfield_column_name, 'ATTRIBUTE10', l_obsolete_column_value, ATTRIBUTE10),
1965 ATTRIBUTE11 = DECODE (p_flexfield_column_name, 'ATTRIBUTE11', l_obsolete_column_value, ATTRIBUTE11),
1966 ATTRIBUTE12 = DECODE (p_flexfield_column_name, 'ATTRIBUTE12', l_obsolete_column_value, ATTRIBUTE12),
1967 ATTRIBUTE13 = DECODE (p_flexfield_column_name, 'ATTRIBUTE13', l_obsolete_column_value, ATTRIBUTE13),
1968 ATTRIBUTE14 = DECODE (p_flexfield_column_name, 'ATTRIBUTE14', l_obsolete_column_value, ATTRIBUTE14),
1969 ATTRIBUTE15 = DECODE (p_flexfield_column_name, 'ATTRIBUTE15', l_obsolete_column_value, ATTRIBUTE15),
1970 ATTRIBUTE16 = DECODE (p_flexfield_column_name, 'ATTRIBUTE16', l_obsolete_column_value, ATTRIBUTE16),
1971 ATTRIBUTE17 = DECODE (p_flexfield_column_name, 'ATTRIBUTE17', l_obsolete_column_value, ATTRIBUTE17),
1972 ATTRIBUTE18 = DECODE (p_flexfield_column_name, 'ATTRIBUTE18', l_obsolete_column_value, ATTRIBUTE18),
1973 ATTRIBUTE19 = DECODE (p_flexfield_column_name, 'ATTRIBUTE19', l_obsolete_column_value, ATTRIBUTE19),
1974 ATTRIBUTE20 = DECODE (p_flexfield_column_name, 'ATTRIBUTE20', l_obsolete_column_value, ATTRIBUTE20),
1975 ATTRIBUTE21 = DECODE (p_flexfield_column_name, 'ATTRIBUTE21', l_obsolete_column_value, ATTRIBUTE21),
1976 ATTRIBUTE22 = DECODE (p_flexfield_column_name, 'ATTRIBUTE22', l_obsolete_column_value, ATTRIBUTE22),
1977 ATTRIBUTE23 = DECODE (p_flexfield_column_name, 'ATTRIBUTE23', l_obsolete_column_value, ATTRIBUTE23),
1978 ATTRIBUTE24 = DECODE (p_flexfield_column_name, 'ATTRIBUTE24', l_obsolete_column_value, ATTRIBUTE24),
1979 ATTRIBUTE25 = DECODE (p_flexfield_column_name, 'ATTRIBUTE25', l_obsolete_column_value, ATTRIBUTE25),
1980 ATTRIBUTE26 = DECODE (p_flexfield_column_name, 'ATTRIBUTE26', l_obsolete_column_value, ATTRIBUTE26),
1981 ATTRIBUTE27 = DECODE (p_flexfield_column_name, 'ATTRIBUTE27', l_obsolete_column_value, ATTRIBUTE27),
1982 ATTRIBUTE28 = DECODE (p_flexfield_column_name, 'ATTRIBUTE28', l_obsolete_column_value, ATTRIBUTE28),
1983 ATTRIBUTE29 = DECODE (p_flexfield_column_name, 'ATTRIBUTE29', l_obsolete_column_value, ATTRIBUTE29),
1984 ATTRIBUTE30 = DECODE (p_flexfield_column_name, 'ATTRIBUTE30', l_obsolete_column_value, ATTRIBUTE30)
1985 WHERE
1986 organization_id = i.organization_id AND
1987 inventory_item_id = i.inventory_item_id;
1988
1989 l_migrate_count := l_migrate_count + 1;
1990
1991 -- Update the mig table.
1992 UPDATE gmi_obsolete_item_columns
1993 SET
1994 migrated_ind = 1,
1995 last_update_date = sysdate,
1996 last_updated_by = 0
1997 WHERE
1998 obsolete_column_name = p_obsolete_column_name;
1999
2000 IF SQL%ROWCOUNT = 0 THEN
2001 INSERT INTO gmi_obsolete_item_columns(
2002 obsolete_column_name,
2003 flexfield_column_name,
2004 migrated_ind,
2005 creation_date,
2006 created_by,
2007 last_update_date,
2008 last_updated_by,
2009 last_update_login
2010 )values(
2011 p_obsolete_column_name,
2012 p_flexfield_column_name,
2013 1,
2014 sysdate,
2015 0,
2016 sysdate,
2017 0,
2018 NULL
2019 );
2020 END IF;
2021
2022 IF (p_commit <> FND_API.G_FALSE) THEN
2023 COMMIT;
2024 END IF;
2025 END LOOP;
2026
2027 -- dbms_output.put_line ('Completed ITEM OBSOLETE COLUMNS migration. Migrated = '||to_char(l_migrate_count)||', Failed = '||to_char(x_failure_count));
2028 GMA_COMMON_LOGGING.gma_migration_central_log (
2029 p_run_id => p_migration_run_id,
2030 p_log_level => FND_LOG.LEVEL_PROCEDURE,
2031 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
2032 p_table_name => 'IC_ITEM_MST_B',
2033 p_context => 'ITEM OBSOLETE COLUMNS',
2034 p_param1 => l_migrate_count,
2035 p_param2 => x_failure_count,
2036 p_param3 => NULL,
2037 p_param4 => NULL,
2038 p_param5 => NULL,
2039 p_db_error => NULL,
2040 p_app_short_name => 'GMA');
2041 END;
2042 /*====================================================================
2043 -- PROCEDURE:
2044 -- migrate_opm_items
2045 --
2046 -- DESCRIPTION:
2047 -- This PL/SQL procedure is used to migrate items
2048 -- to process enabled organizations even if it is not used in
2049 -- warehouse.
2050 --
2051 -- User should call this procedure as
2052 -- sql > execute migrate_opm_items('PR4', 'TRUE' ) ;
2053 --
2054 -- or use this procedure is custom procedure to call
2055 -- for multiple organizations as
2056 --
2057 -- begin
2058 -- INV_OPM_Item_Migration.migrate_opm_items('PR4', 'TRUE') ;
2059 -- INV_OPM_Item_Migration.migrate_opm_item ('PR5', 'TRUE') ;
2060 -- end ;
2061 --
2062 -- PARAMETERS:
2063 -- p_organization_code - Organization for item need to sync.
2064 -- p_commit - flag to indicate if commit shouldbe performed.
2065 --
2066 -- SYNOPSIS:
2067 -- migrate_opm_items (p_organization_code => l_organization_code,
2068 -- p_commit => l_commit
2069 -- );
2070 --
2071 -- HISTORY
2072 -- Bug#10405668
2073 -- Kedar Bavadekar Created 04/07/2011
2074 --====================================================================*/
2075
2076 PROCEDURE migrate_opm_items
2077 ( p_organization_code IN VARCHAR2
2078 , p_commit IN VARCHAR2) IS
2079
2080 cursor cr_missing_items_csr
2081 IS
2082
2083 select a.inventory_item_id ,
2084 a.organization_id ,
2085 a.segment1 item_no
2086 from mtl_system_items a,
2087 mtl_parameters b
2088 where a.organization_id = b.organization_id
2089 and b.process_enabled_flag = 'Y'
2090 and b.organization_code = p_organization_code
2091 and not exists (
2092 select 1
2093 from ic_item_mst_b_mig ic,
2094 mtl_parameters mp
2095 where ic.organization_id = mp.organization_id
2096 and mp.organization_code = p_organization_code
2097 and mp.process_enabled_flag = 'Y'
2098 and a.inventory_item_id = ic.inventory_item_id
2099 and a.organization_id = ic.organization_id
2100 and nvl(migrated_ind, 0) <> 1
2101 ) ;
2102
2103 l_migration_run_id NUMBER ;
2104 l_failure_count NUMBER ;
2105 l_failure_count2 NUMBER ;
2106 l_migrate_count NUMBER ;
2107 l_inventory_item_id NUMBER ;
2108 l_item_no ic_item_mst_b.item_no%TYPE ;
2109 l_item_id NUMBER ;
2110
2111 BEGIN
2112
2113 l_migration_run_id := GMA_MIGRATION.gma_migration_start ('GMI', 'Migrating Items to Organization ' || p_organization_code );
2114
2115 l_migrate_count := 0 ;
2116 l_failure_count := 0 ;
2117 l_failure_count2 := 0 ;
2118
2119 FOR cr_missing_items_rec in cr_missing_items_csr LOOP
2120
2121 BEGIN
2122
2123 SELECT item_id
2124 INTO l_item_id
2125 FROM ic_item_mst_b
2126 WHERE ITEM_NO = cr_missing_items_rec.item_no ;
2127
2128 -- Call get_odm_item
2129 INV_OPM_Item_Migration.get_ODM_item (
2130 p_migration_run_id => l_migration_run_id,
2131 p_item_id => l_item_id,
2132 p_organization_id => cr_missing_items_rec.organization_id ,
2133 p_mode => NULL,
2134 p_commit => p_commit,
2135 x_inventory_item_id => l_inventory_item_id,
2136 x_failure_count => l_failure_count
2137 ) ;
2138
2139 IF (l_failure_count > 0) THEN
2140
2141 l_failure_count2 := l_failure_count2 + 1 ;
2142
2143 -- Log Error
2144 -- dbms_output.put_line ('Item migration failed for Item id : '||to_char(p_item_id));
2145 GMA_COMMON_LOGGING.gma_migration_central_log (
2146 p_run_id => l_migration_run_id,
2147 p_log_level => FND_LOG.LEVEL_ERROR,
2148 p_message_token => 'GMI_MIG_ITEM_MIG_FAILED',
2149 p_table_name => 'IC_ITEM_MST_B',
2150 p_context => 'ITEMS',
2151 p_param1 => INV_GMI_Migration.org(cr_missing_items_rec.organization_id),
2152 p_param2 => INV_GMI_Migration.item(l_item_id),
2153 p_param3 => NULL,
2154 p_param4 => NULL,
2155 p_param5 => NULL,
2156 p_db_error => NULL,
2157 p_app_short_name => 'GMI');
2158
2159 ELSE
2160
2161 l_migrate_count := l_migrate_count + 1 ;
2162
2163 END IF ;
2164
2165 l_item_id := null ;
2166 l_item_no := null ;
2167 l_failure_count := 0 ;
2168
2169 EXCEPTION
2170 WHEN NO_DATA_FOUND THEN
2171 NULL ;
2172 END ;
2173
2174 END LOOP ;
2175
2176 GMA_COMMON_LOGGING.gma_migration_central_log (
2177 p_run_id => l_migration_run_id,
2178 p_log_level => FND_LOG.LEVEL_PROCEDURE,
2179 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
2180 p_table_name => 'IC_ITEM_MST_B',
2181 p_context => 'ITEMS',
2182 p_param1 => l_migrate_count,
2183 p_param2 => l_failure_count2,
2184 p_db_error => NULL,
2185 p_app_short_name => 'GMA');
2186
2187 END MIGRATE_OPM_ITEMS ;
2188
2189 END INV_OPM_Item_Migration;