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