[Home] [Help]
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 |
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
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:
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:
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);
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,
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
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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;
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
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;
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,
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;
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;
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,
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;
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,
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:
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,
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,
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,
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,
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,
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,
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,
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'
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:
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 (
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,
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,