eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.EGO_ITEM_BULKLOAD_PKG dependencies on MTL_SYSTEM_ITEMS_INTERFACE

Line 216: -- 1. While inserting in MTL_SYSTEM_ITEMS_INTERFACE. --

212:
213: -----------------------------------------------------------------------
214: -- Using following Columns in EGO_BULKLOAD_INTF as buffer columns to --
215: -- store item attributes information, to be retrieved later : --
216: -- 1. While inserting in MTL_SYSTEM_ITEMS_INTERFACE. --
217: -- 2. While querying the errors page. --
218: -----------------------------------------------------------------------
219: -- used for all value set conversions as the user enters the display
220: -- value which will be converted into internal value.

Line 250: -- in MTL_SYSTEM_ITEMS_INTERFACE table

246: l_trade_item_descriptor_col VARCHAR2(50) := NULL; --R12C Pack Changes
247:
248: -----------------------------------------
249: -- R12 - GTIN needs to be populated
250: -- in MTL_SYSTEM_ITEMS_INTERFACE table
251: -----------------------------------------
252: G_GTIN_NUM_ATTR_CODE VARCHAR2(50) := 'GTIN_NUM';
253: G_GTIN_DESC_ATTR_CODE VARCHAR2(50) := 'GTIN_DESC';
254: G_GTIN_NUM_DB_COL VARCHAR2(50) := 'GLOBAL_TRADE_ITEM_NUMBER';

Line 508: FROM SYS.ALL_TAB_COLUMNS WHERE TABLE_NAME = 'MTL_SYSTEM_ITEMS_INTERFACE'

504: -- fix for 12.2 OLP compatible
505:
506: SELECT COLUMN_NAME
507: BULK COLLECT INTO l_column_name
508: FROM SYS.ALL_TAB_COLUMNS WHERE TABLE_NAME = 'MTL_SYSTEM_ITEMS_INTERFACE'
509: AND COLUMN_NAME NOT IN ('SET_PROCESS_ID',
510: 'TRANSACTION_ID',
511: 'REQUEST_ID',
512: 'PROGRAM_APPLICATION_ID',

Line 538: where syn.synonym_name = 'MTL_SYSTEM_ITEMS_INTERFACE'

534: -- bug#15835530 fix for 12.2 OLP compatible
535: SELECT col.column_name
536: BULK COLLECT INTO l_column_name
537: from user_synonyms syn, dba_tab_columns col
538: where syn.synonym_name = 'MTL_SYSTEM_ITEMS_INTERFACE'
539: and col.owner = syn.table_owner
540: and col.table_name = syn.table_name
541: AND COLUMN_NAME NOT IN ('SET_PROCESS_ID',
542: 'TRANSACTION_ID',

Line 575: l_dyn_sql := l_dyn_sql || ' DELETE MTL_SYSTEM_ITEMS_INTERFACE MSII ' ;

571: l_column_list := SUBSTR(l_column_list,1,length(l_column_list)-4);
572: END IF;
573:
574: l_dyn_sql := '';
575: l_dyn_sql := l_dyn_sql || ' DELETE MTL_SYSTEM_ITEMS_INTERFACE MSII ' ;
576: l_dyn_sql := l_dyn_sql || ' WHERE ';
577: l_dyn_sql := l_dyn_sql || ' ( ';
578: l_dyn_sql := l_dyn_sql || ' (ITEM_NUMBER IS NOT NULL AND ORGANIZATION_CODE IS NOT NULL ) ';
579: l_dyn_sql := l_dyn_sql || ' OR ';

Line 605: l_dyn_sql := l_dyn_sql || ' FROM MTL_SYSTEM_ITEMS_INTERFACE MSI ';

601: l_dyn_sql := l_dyn_sql || ' AND PROCESS_FLAG = :PROCESS_STATUS_1 '; --Bug 3763665
602: l_dyn_sql := l_dyn_sql || ' AND ( ';
603: l_dyn_sql := l_dyn_sql || ' EXISTS ( '; -- there exists a row where item is being Created or updated in the same request
604: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
605: l_dyn_sql := l_dyn_sql || ' FROM MTL_SYSTEM_ITEMS_INTERFACE MSI ';
606: l_dyn_sql := l_dyn_sql || ' WHERE MSI.DESCRIPTION IS NOT NULL ';
607: l_dyn_sql := l_dyn_sql || ' AND ((MSI.ITEM_NUMBER IS NULL AND MSII.ITEM_NUMBER IS NULL) OR (MSI.ITEM_NUMBER = MSII.ITEM_NUMBER))';
608: l_dyn_sql := l_dyn_sql || ' AND SET_PROCESS_ID = :SET_PROCESS_ID_2 ';
609: l_dyn_sql := l_dyn_sql || ' AND PROCESS_FLAG = :PROCESS_STATUS_2 ';

Line 780: || ' FROM MTL_SYSTEM_ITEMS_INTERFACE '

776: BEGIN
777:
778: l_item_num_sql :=
779: ' SELECT SEGMENT1 , TRANSACTION_ID '
780: || ' FROM MTL_SYSTEM_ITEMS_INTERFACE '
781: || ' WHERE REQUEST_ID = '||REQUEST_ID
782: || ' AND PROCESS_FLAG = '||G_INTF_STATUS_SUCCESS
783: || ' AND TRANSACTION_TYPE = '''||G_CREATE||'''';
784:

Line 818: , p_table_name => 'MTL_SYSTEM_ITEMS_INTERFACE'

814: , p_message_text => NULL
815: , p_token_tbl => l_token_tbl
816: , p_message_type => 'E'
817: , p_row_identifier => l_transaction_id_table(i)
818: , p_table_name => 'MTL_SYSTEM_ITEMS_INTERFACE'
819: , p_entity_id => NULL
820: , p_entity_index => NULL
821: , p_entity_code => G_ERROR_ENTITY_CODE
822: );

Line 1165: FROM mtl_system_items_interface

1161: -- To check if the given Set Process ID already exists in MSII.
1162: --------------------------------------------------------------------------
1163: CURSOR c_msii_set_id_exists(c_set_process_id IN NUMBER) IS
1164: SELECT 'x'
1165: FROM mtl_system_items_interface
1166: WHERE set_process_id = c_set_process_id;
1167:
1168:
1169: ---------------------------------------------------------------------

Line 1257: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL

1253: --------------------------------------------------------------------
1254: UPDATE ego_bulkload_intf
1255: --The Transaction ID sequence that is used in INVPOPIF package to
1256: --auto-populate Transaction ID in MSII.
1257: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
1258: WHERE resultfmt_usage_id = p_resultfmt_usage_id;
1259:
1260: Write_Debug('Retrieving the Display and INTF cols');
1261: i := 0;

Line 1377: -- R12 - to update GTIN and GTIN description in MTL_SYSTEM_ITEMS_INTERFACE table

1373: -- R12C Pack Hierarchy Changes for Trade Item Descriptor --
1374: l_trade_item_descriptor_col := l_intf_col_name;
1375: END IF;
1376:
1377: -- R12 - to update GTIN and GTIN description in MTL_SYSTEM_ITEMS_INTERFACE table
1378: IF (l_prod_col_name = G_GTIN_NUM_ATTR_CODE) THEN
1379: l_prod_col_name_tbl(i) := G_GTIN_NUM_DB_COL;
1380: ELSIF (l_prod_col_name = G_GTIN_DESC_ATTR_CODE) THEN
1381: l_prod_col_name_tbl(i) := G_GTIN_DESC_DB_COL;

Line 1831: l_dyn_sql := 'INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE MSII';

1827:
1828: -----------------------------------------------------------------
1829: -- Insert rows from EBI into MSII
1830: -----------------------------------------------------------------
1831: l_dyn_sql := 'INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE MSII';
1832: l_dyn_sql := l_dyn_sql || ' ( ';
1833: l_dyn_sql := l_dyn_sql || ' SET_PROCESS_ID , ';
1834: l_dyn_sql := l_dyn_sql || ' TRANSACTION_ID , ';
1835: l_dyn_sql := l_dyn_sql || ' REQUEST_ID , ';

Line 1912: -- l_dyn_sql := l_dyn_sql || 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

1908: --(Catalog Name validation will be done by IOI)
1909: ---------------------------------------------------------------------
1910: -- IF (l_prod_col_name_tbl(i) = G_ITEM_CATALOG_GROUP) THEN
1911: -- l_dyn_sql := '';
1912: -- l_dyn_sql := l_dyn_sql || 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
1913: -- l_dyn_sql := l_dyn_sql || ' SET (MSII.ITEM_CATALOG_GROUP_ID, MSII.PROCESS_FLAG)=';
1914: -- l_dyn_sql := l_dyn_sql || '( ';
1915: -- l_dyn_sql := l_dyn_sql || ' SELECT MICG.ITEM_CATALOG_GROUP_ID ';
1916: -- l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(MICG.ITEM_CATALOG_GROUP_ID, -1), -1,'||G_ITEM_CATALOG_NAME_ERR_STS||', ' || G_PROCESS_STATUS || ' ) ';

Line 1953: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

1949:
1950: IF (l_prod_col_name_tbl(i) = G_PRIMARY_UOM) THEN
1951:
1952: -- populating MSII with PRIMARY_UOM_CODE if exists
1953: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
1954: l_dyn_sql := l_dyn_sql || ' SET (MSII.PRIMARY_UOM_CODE ';
1955: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
1956: l_dyn_sql := l_dyn_sql || '( ';
1957: l_dyn_sql := l_dyn_sql || ' SELECT MUOM.UOM_CODE ';

Line 1982: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

1978: Write_Debug(l_dyn_sql);
1979: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
1980:
1981: -- populating MSII with PRIMARY_UOM_CODE if special char for null out exists
1982: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
1983: l_dyn_sql := l_dyn_sql || ' SET MSII.PRIMARY_UOM_CODE = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
1984: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
1985: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
1986: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2025: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2021: -- by doing Value-to-ID Conversion.
2022: ----------------------------------------------------------------------------------
2023: ELSIF (l_prod_col_name_tbl(i) = G_USER_ITEM_TYPE) THEN
2024:
2025: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2026: l_dyn_sql := l_dyn_sql || ' SET (MSII.ITEM_TYPE ';
2027: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2028: l_dyn_sql := l_dyn_sql || '( ';
2029: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

Line 2054: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2050:
2051: Write_Debug(l_dyn_sql);
2052: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2053:
2054: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2055: l_dyn_sql := l_dyn_sql || ' SET MSII.ITEM_TYPE = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2056: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2057: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2058: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2077: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2073: -- by doing Value-to-ID Conversion.
2074: ----------------------------------------------------------------------------------
2075: ELSIF (l_prod_col_name_tbl(i) = G_BOM_ITEM_TYPE) THEN
2076:
2077: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2078: l_dyn_sql := l_dyn_sql || ' SET (MSII.BOM_ITEM_TYPE ';
2079: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2080: l_dyn_sql := l_dyn_sql || '( ';
2081: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

Line 2106: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2102:
2103: Write_Debug(l_dyn_sql);
2104: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2105:
2106: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2107: l_dyn_sql := l_dyn_sql || ' SET MSII.BOM_ITEM_TYPE = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2108: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2109: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2110: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2124: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2120: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id;
2121: Write_Debug('MSII: Updated the BOM Item Types.');
2122:
2123: ELSIF (l_prod_col_name_tbl(i) = G_TRADE_ITEM_DESCRIPTOR) THEN
2124: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2125: l_dyn_sql := l_dyn_sql || ' SET (MSII.TRADE_ITEM_DESCRIPTOR ';
2126: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2127: l_dyn_sql := l_dyn_sql || '( ';
2128: l_dyn_sql := l_dyn_sql || ' SELECT IT.INTERNAL_NAME ';

Line 2152: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2148:
2149: Write_Debug(l_dyn_sql);
2150: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2151:
2152: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2153: l_dyn_sql := l_dyn_sql || ' SET MSII.TRADE_ITEM_DESCRIPTOR = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2154: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2155: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2156: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2175: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2171: -- by doing Value-to-ID Conversion.
2172: ----------------------------------------------------------------------------------
2173: ELSIF (l_prod_col_name_tbl(i) = G_ENG_ITEM_FLAG) THEN
2174:
2175: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2176: l_dyn_sql := l_dyn_sql || ' SET (MSII.ENG_ITEM_FLAG ';
2177: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2178: l_dyn_sql := l_dyn_sql || '( ';
2179: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

Line 2204: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2200:
2201: Write_Debug(l_dyn_sql);
2202: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2203:
2204: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2205: l_dyn_sql := l_dyn_sql || ' SET MSII.ENG_ITEM_FLAG = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2206: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2207: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2208: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2229: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2225: -- by doing Value-to-ID Conversion.
2226: ----------------------------------------------------------------------------------
2227: ELSIF (l_prod_col_name_tbl(i) = G_CONVERSIONS) THEN
2228:
2229: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2230: l_dyn_sql := l_dyn_sql || ' SET (MSII.ALLOWED_UNITS_LOOKUP_CODE ';
2231: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2232: l_dyn_sql := l_dyn_sql || '( ';
2233: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

Line 2258: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2254:
2255: Write_Debug(l_dyn_sql);
2256: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2257:
2258: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2259: l_dyn_sql := l_dyn_sql || ' SET MSII.ALLOWED_UNITS_LOOKUP_CODE = :NULL_NUM ';
2260: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2261: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2262: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2281: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2277: -- by doing Value-to-ID Conversion.
2278: ----------------------------------------------------------------------------------
2279: ELSIF (l_prod_col_name_tbl(i) = G_SECONDARY_DEF_IND) THEN
2280:
2281: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2282: l_dyn_sql := l_dyn_sql || ' SET (MSII.SECONDARY_DEFAULT_IND ';
2283: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2284: l_dyn_sql := l_dyn_sql || '( ';
2285: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

Line 2310: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2306:
2307: Write_Debug(l_dyn_sql);
2308: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2309:
2310: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2311: l_dyn_sql := l_dyn_sql || ' SET MSII.SECONDARY_DEFAULT_IND = :NULL_NUM';
2312: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2313: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2314: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2333: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2329: -- by doing Value-to-ID Conversion.
2330: ----------------------------------------------------------------------------------
2331: ELSIF (l_prod_col_name_tbl(i) = G_ONT_PRICING_QTY_SRC) THEN
2332:
2333: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2334: l_dyn_sql := l_dyn_sql || ' SET (MSII.ONT_PRICING_QTY_SOURCE ';
2335: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2336: l_dyn_sql := l_dyn_sql || '( ';
2337: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

Line 2362: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2358:
2359: Write_Debug(l_dyn_sql);
2360: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2361:
2362: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2363: l_dyn_sql := l_dyn_sql || ' SET MSII.ONT_PRICING_QTY_SOURCE = :NULL_NUM';
2364: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2365: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2366: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2385: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2381: -- by doing Value-to-ID Conversion.
2382: ----------------------------------------------------------------------------------
2383: ELSIF (l_prod_col_name_tbl(i) = G_SECONDARY_UOM_CODE) THEN
2384:
2385: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2386: l_dyn_sql := l_dyn_sql || ' SET (MSII.SECONDARY_UOM_CODE ';
2387: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2388: l_dyn_sql := l_dyn_sql || '( ';
2389: l_dyn_sql := l_dyn_sql || ' SELECT MUOM.UOM_CODE ';

Line 2413: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2409:
2410: Write_Debug(l_dyn_sql);
2411: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2412:
2413: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2414: l_dyn_sql := l_dyn_sql || ' SET MSII.SECONDARY_UOM_CODE = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2415: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2416: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2417: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2436: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2432: -- by doing Value-to-ID Conversion.
2433: ----------------------------------------------------------------------------------
2434: ELSIF (l_prod_col_name_tbl(i) = G_TRACKING_QTY_IND) THEN
2435:
2436: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2437: l_dyn_sql := l_dyn_sql || ' SET (MSII.TRACKING_QUANTITY_IND ';
2438: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2439: l_dyn_sql := l_dyn_sql || '( ';
2440: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

Line 2465: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2461:
2462: Write_Debug(l_dyn_sql);
2463: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2464:
2465: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2466: l_dyn_sql := l_dyn_sql || ' SET MSII.TRACKING_QUANTITY_IND = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2467: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2468: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2469: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2489: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2485: -- by doing Value-to-ID Conversion. Rathna
2486: ----------------------------------------------------------------------------------
2487: ELSIF (l_prod_col_name_tbl(i) = G_INVENTORY_ITEM_STATUS) THEN
2488:
2489: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2490: l_dyn_sql := l_dyn_sql || ' SET (MSII.INVENTORY_ITEM_STATUS_CODE ';
2491: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2492: l_dyn_sql := l_dyn_sql || '( ';
2493: l_dyn_sql := l_dyn_sql || ' SELECT IT.INVENTORY_ITEM_STATUS_CODE ';

Line 2516: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2512:
2513: Write_Debug(l_dyn_sql);
2514: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2515:
2516: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2517: l_dyn_sql := l_dyn_sql || ' SET MSII.TRACKING_QUANTITY_IND = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2518: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2519: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2520: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2549: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2545: l_col_name := l_prod_col_name_tbl(i);
2546: END IF;
2547: Write_Debug('Updating MSII: l_col_name = ' || l_col_name);
2548: --Bug Fix 4713312:END
2549: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2550: l_dyn_sql := l_dyn_sql || ' SET MSII.'||l_col_name||' = ';
2551: l_dyn_sql := l_dyn_sql || '( ';
2552: -- MLS Status
2553: -- IF (l_prod_col_name_tbl(i) = G_INV_STATUS_CODE_NAME) THEN

Line 2634: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2630: IF (l_prod_col_name_tbl(i) = G_DESCRIPTION) THEN
2631: --DPHILIP: we need to check what is the impact of this check
2632: Write_Debug('DPHILIP :updating description setting the error status to: ' || G_DESCRIPTION_ERR_STS);
2633:
2634: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2635: l_dyn_sql := l_dyn_sql || ' SET MSII.PROCESS_FLAG = '||G_DESCRIPTION_ERR_STS||' ';
2636: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2637: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2638: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2676: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2672: ----------------------------------------------------------------------------------
2673: -- First Transfer the Lifecycle information from EBI to MSII
2674: -- by doing Value-to-ID Conversion.
2675: ----------------------------------------------------------------------------------
2676: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2677: l_dyn_sql := l_dyn_sql || ' SET (MSII.LIFECYCLE_ID ';
2678: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2679: l_dyn_sql := l_dyn_sql || '( ';
2680: l_dyn_sql := l_dyn_sql || ' SELECT LC.PROJ_ELEMENT_ID ';

Line 2703: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2699:
2700: Write_Debug(l_dyn_sql);
2701: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2702:
2703: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2704: l_dyn_sql := l_dyn_sql || ' SET MSII.LIFECYCLE_ID = :NULL_NUM';
2705: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2706: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2707: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2729: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2725: ----------------------------------------------------------------------------------
2726: -- Next Transfer the Lifecycle Phase information from EBI to MSII
2727: -- by doing Value-to-ID Conversion, and by joining Lifecycle ID information.
2728: ----------------------------------------------------------------------------------
2729: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2730: l_dyn_sql := l_dyn_sql || ' SET (MSII.CURRENT_PHASE_ID ';
2731: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2732: l_dyn_sql := l_dyn_sql || '( ';
2733: l_dyn_sql := l_dyn_sql || ' SELECT LCP.PROJ_ELEMENT_ID ';

Line 2757: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

2753:
2754: Write_Debug(l_dyn_sql);
2755: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2756:
2757: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2758: l_dyn_sql := l_dyn_sql || ' SET MSII.CURRENT_PHASE_ID = :NULL_NUM';
2759: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2760: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2761: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2780: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII

2776: END IF; --IF ( l_prod_col_name_tbl.count > 0) THEN
2777: --
2778: -- convert all date fields values from Excel Null to INTF Null
2779: --
2780: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
2781: SET start_date_active = DECODE(start_date_active,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,start_date_active),
2782: end_date_active = DECODE(end_date_active,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,end_date_active),
2783: engineering_date = DECODE(engineering_date,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,engineering_date)
2784: WHERE MSII.SET_PROCESS_ID = l_msii_set_process_id

Line 2962: l_dyn_sql := l_dyn_sql || ', ''MTL_SYSTEM_ITEMS_INTERFACE'' ';

2958: l_dyn_sql := l_dyn_sql || ', '||G_USER_ID ;
2959: l_dyn_sql := l_dyn_sql || ', SYSDATE ';
2960: l_dyn_sql := l_dyn_sql || ', '||G_USER_ID;
2961: l_dyn_sql := l_dyn_sql || ', '||G_LOGIN_ID;
2962: l_dyn_sql := l_dyn_sql || ', ''MTL_SYSTEM_ITEMS_INTERFACE'' ';
2963: l_dyn_sql := l_dyn_sql || ', DECODE(MSII.PROCESS_FLAG, ';
2964: l_dyn_sql := l_dyn_sql || G_ITEM_CATALOG_NAME_ERR_STS||', ''EGO_ITEMCATALOG_INVALID'', ';
2965: l_dyn_sql := l_dyn_sql || G_PRIMARY_UOM_ERR_STS||', ''EGO_PRIMARYUOM_INVALID'', ';
2966: l_dyn_sql := l_dyn_sql || G_LIFECYCLE_ERR_STS||', ''EGO_LIFECYCLE_INVALID'', ';

Line 3014: l_dyn_sql := l_dyn_sql || 'FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, EGO_BULKLOAD_INTF EBI ';

3010: l_dyn_sql := l_dyn_sql || ' ) ';
3011: l_dyn_sql := l_dyn_sql || ', MSII.TRANSACTION_ID ';
3012: l_dyn_sql := l_dyn_sql || ', NULL ';
3013: l_dyn_sql := l_dyn_sql || ', NULL ';
3014: l_dyn_sql := l_dyn_sql || 'FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, EGO_BULKLOAD_INTF EBI ';
3015: l_dyn_sql := l_dyn_sql || 'WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID ';
3016: l_dyn_sql := l_dyn_sql || ' AND MSII.SET_PROCESS_ID = '||p_set_process_id;
3017: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG IN ';
3018: l_dyn_sql := l_dyn_sql || ' ( ';

Line 3058: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII

3054: --Now that the error messages are inserted, update MSII lines to
3055: --Process status ERROR.
3056: -----------------------------------------------------------------------
3057:
3058: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
3059: SET MSII.PROCESS_FLAG = G_INTF_STATUS_ERROR
3060: WHERE MSII.SET_PROCESS_ID = p_set_process_id
3061: AND MSII.PROCESS_FLAG IN
3062: (

Line 3087: -- DELETE MTL_SYSTEM_ITEMS_INTERFACE MSII

3083: );
3084:
3085: Write_Debug('MSII: Updated all the line statuses to Error for Pre-processing validation errors');
3086:
3087: -- DELETE MTL_SYSTEM_ITEMS_INTERFACE MSII
3088: -- WHERE
3089: -- (
3090: -- (
3091: -- ITEM_NUMBER IS NOT NULL AND

Line 3144: -- FROM MTL_SYSTEM_ITEMS_INTERFACE MSI

3140: ------------------------------------------------------------------------------------------------
3141: -- AND PROCESS_FLAG = G_PROCESS_STATUS --Bug 3763665
3142: -- AND (EXISTS( -- there exists a row where item is being Created or updated in the same request
3143: -- SELECT 'X'
3144: -- FROM MTL_SYSTEM_ITEMS_INTERFACE MSI
3145: -- WHERE MSI.DESCRIPTION IS NOT NULL
3146: -- AND NVL(MSI.ITEM_NUMBER,FND_API.G_MISS_CHAR) = NVL(MSII.ITEM_NUMBER,FND_API.G_MISS_CHAR)
3147: -- AND SET_PROCESS_ID = p_set_process_id
3148: -- AND PROCESS_FLAG = G_PROCESS_STATUS

Line 3169: -- UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII

3165: -- -- So, all new Items (if ENG_ITEM_FLAG is unspecified) are created as
3166: -- -- Engineering Items.
3167: -- -----------------------------------------------------------------------
3168: --
3169: -- UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
3170: -- SET MSII.ENG_ITEM_FLAG = 'Y'
3171: -- WHERE MSII.SET_PROCESS_ID = p_set_process_id
3172: -- AND MSII.INVENTORY_ITEM_ID IS NULL
3173: -- AND MSII.TRANSACTION_TYPE IN ('SYNC', 'CREATE')

Line 3277: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

3273: UPDATE EGO_BULKLOAD_INTF EBI
3274: SET EBI.PROCESS_STATUS =
3275: (
3276: SELECT MSII.PROCESS_FLAG
3277: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
3278: WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
3279: )
3280: WHERE EXISTS
3281: (

Line 3283: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

3279: )
3280: WHERE EXISTS
3281: (
3282: SELECT 'X'
3283: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
3284: WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
3285: )
3286: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
3287:

Line 3302: -- FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

3298: -- SET EBI.PROCESS_STATUS = G_INTF_STATUS_ERROR
3299: -- WHERE EXISTS
3300: -- (
3301: -- SELECT 'X'
3302: -- FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
3303: -- WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
3304: -- AND MSII.PROCESS_FLAG = G_INTF_STATUS_SUCCESS
3305: -- AND MSII.INVENTORY_ITEM_ID IS NULL
3306: -- )

Line 3330: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

3326: UPDATE EGO_BULKLOAD_INTF EBI
3327: SET EBI.INSTANCE_PK1_VALUE =
3328: (
3329: SELECT MSII.INVENTORY_ITEM_ID
3330: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
3331: WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
3332: )
3333: WHERE EXISTS
3334: (

Line 3336: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

3332: )
3333: WHERE EXISTS
3334: (
3335: SELECT 'X'
3336: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
3337: WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
3338: )
3339: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
3340: AND EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS;

Line 3429: FROM mtl_system_items_interface

3425: -- To check if the given Set Process ID already exists in MSII.
3426: --------------------------------------------------------------------------
3427: CURSOR c_msii_set_id_exists(c_set_process_id IN NUMBER) IS
3428: SELECT 'x'
3429: FROM mtl_system_items_interface
3430: WHERE set_process_id = c_set_process_id;
3431:
3432: ---------------------------------------------------------------------
3433: -- Type Declarations

Line 3489: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL

3485: -- this was happening because the error was logged with a previous transaction id and
3486: -- here a new transaction id was updated to Bulkload interface table.
3487: IF p_data_level <> G_ITEM_DATA_LEVEL THEN
3488: UPDATE ego_bulkload_intf
3489: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
3490: WHERE resultfmt_usage_id = p_resultfmt_usage_id;
3491: END IF;
3492:
3493: Write_Debug('Retrieving the Display and INTF cols');

Line 4325: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

4321: UPDATE EGO_BULKLOAD_INTF EBI
4322: SET EBI.INSTANCE_PK1_VALUE =
4323: (
4324: SELECT MSII.INVENTORY_ITEM_ID
4325: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
4326: WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
4327: )
4328: WHERE EXISTS
4329: (

Line 4331: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

4327: )
4328: WHERE EXISTS
4329: (
4330: SELECT 'X'
4331: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
4332: WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
4333: )
4334: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
4335: AND EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS;

Line 5627: l_dyn_sql_update_msii := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE SET ';

5623: -- Update MSII with the internal values. --
5624: -- Use dynamic sql with bind variable to share cursor for all items. --
5625: -- Construct the sql text once and bind variables for each item. --
5626: --------------------------------------------------------------------------------
5627: l_dyn_sql_update_msii := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE SET ';
5628: l_cursor_update_msii := DBMS_SQL.OPEN_CURSOR;
5629:
5630: -- construct dynamic sql
5631: FOR i in 1..l_msii_col_table.COUNT LOOP

Line 5858: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE ';

5854: -- --
5855: -- By this way we can keep the error code flow the same as before. --
5856: --------------------------------------------------------------------------------
5857:
5858: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE ';
5859: l_dyn_sql := l_dyn_sql || ' SET ';
5860:
5861: FOR i in 1..l_msii_col_table.COUNT LOOP
5862: -- flash errors where int val is not correct

Line 5959: l_dyn_sql := l_dyn_sql || ', ''MTL_SYSTEM_ITEMS_INTERFACE'' ';

5955: l_dyn_sql := l_dyn_sql || ', '||G_USER_ID ;
5956: l_dyn_sql := l_dyn_sql || ', SYSDATE ';
5957: l_dyn_sql := l_dyn_sql || ', '||G_USER_ID;
5958: l_dyn_sql := l_dyn_sql || ', '||G_LOGIN_ID;
5959: l_dyn_sql := l_dyn_sql || ', ''MTL_SYSTEM_ITEMS_INTERFACE'' ';
5960: l_dyn_sql := l_dyn_sql || ', DECODE(MSII.PROCESS_FLAG, ';
5961: l_dyn_sql := l_dyn_sql || G_VS_INVALID_ERR_STS||', ''EGO_IPI_INVALID_VALUE''';
5962: l_dyn_sql := l_dyn_sql || ' ) ';
5963: l_dyn_sql := l_dyn_sql || ', NULL ';

Line 5974: l_dyn_sql := l_dyn_sql || 'FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, EGO_BULKLOAD_INTF EBI ';

5970: l_dyn_sql := l_dyn_sql || ' ) ';
5971: l_dyn_sql := l_dyn_sql || ', MSII.TRANSACTION_ID ';
5972: l_dyn_sql := l_dyn_sql || ', NULL ';
5973: l_dyn_sql := l_dyn_sql || ', NULL ';
5974: l_dyn_sql := l_dyn_sql || 'FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, EGO_BULKLOAD_INTF EBI ';
5975: l_dyn_sql := l_dyn_sql || 'WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID ';
5976: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG IN ';
5977: l_dyn_sql := l_dyn_sql || ' ( ';
5978: l_dyn_sql := l_dyn_sql || G_VS_INVALID_ERR_STS; --take care of invalid value set

Line 5987: UPDATE MTL_SYSTEM_ITEMS_INTERFACE

5983: Write_Debug(l_dyn_sql);
5984: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id;
5985:
5986: Write_Debug(' All Value Set value errors populated.');
5987: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
5988: SET PROCESS_FLAG = G_INTF_STATUS_ERROR
5989: WHERE PROCESS_FLAG IN
5990: (
5991: G_VS_INVALID_ERR_STS

Line 7173: FROM MTL_SYSTEM_ITEMS_INTERFACE

7169:
7170: --Removing Item Num NULL clause - R12C
7171: SELECT DISTINCT item_catalog_group_id
7172: BULK COLLECT INTO l_cc_id_table
7173: FROM MTL_SYSTEM_ITEMS_INTERFACE
7174: WHERE set_process_id = p_set_id
7175: AND (organization_id = p_org_id OR p_all_org = 1)
7176: AND process_flag = p_rec_status
7177: AND transaction_type = 'CREATE';

Line 7194: FROM MTL_SYSTEM_ITEMS_INTERFACE

7190: source_system_reference
7191: BULK COLLECT INTO l_org_id_table, l_old_item_num_table,
7192: l_trans_id_table, l_ss_id_table,
7193: l_ss_ref_table
7194: FROM MTL_SYSTEM_ITEMS_INTERFACE
7195: WHERE set_process_id = p_set_id
7196: AND (organization_id = p_org_id OR p_all_org = 1)
7197: AND item_catalog_group_id = l_cc_id_table(cc_row_index)
7198: AND process_flag = p_rec_status

Line 7212: UPDATE mtl_system_items_interface

7208: );
7209:
7210: -- for item interface table
7211: FORALL item_num_row_index IN 1..l_item_rows_processed
7212: UPDATE mtl_system_items_interface
7213: SET item_number = l_item_num_table(item_num_row_index),
7214: SEGMENT1 = NULL,
7215: SEGMENT2 = NULL,
7216: SEGMENT3 = NULL,

Line 7668: l_dyn_sql := l_dyn_sql || 'MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL , ';

7664: END IF;
7665: l_dyn_sql := l_dyn_sql || 'EBI.'||l_org_code_col ||' , ';
7666: l_dyn_sql := l_dyn_sql || '''SYNC'' , ';
7667: l_dyn_sql := l_dyn_sql || G_PROCESS_STATUS||' , ';
7668: l_dyn_sql := l_dyn_sql || 'MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL , ';
7669: l_dyn_sql := l_dyn_sql || 'EBI.'||'CREATED_BY , ';
7670: l_dyn_sql := l_dyn_sql || 'EBI.'||'CREATION_DATE , ';
7671: l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATED_BY , ';
7672: l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATE_DATE , ';

Line 7763: l_dyn_sql := l_dyn_sql || 'MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL , ';

7759: l_dyn_sql := l_dyn_sql || ' SOURCE_SYSTEM_REFERENCE , ';
7760: l_dyn_sql := l_dyn_sql || ' ASSOCIATION_ID ';
7761: l_dyn_sql := l_dyn_sql || ' ) ';
7762: l_dyn_sql := l_dyn_sql || 'SELECT ';
7763: l_dyn_sql := l_dyn_sql || 'MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL , ';
7764: l_dyn_sql := l_dyn_sql || ' INTERSECTIONS.* ';
7765: l_dyn_sql := l_dyn_sql || ' FROM ( ';
7766:
7767: l_dyn_sql := l_dyn_sql || 'SELECT ';

Line 7802: --l_dyn_sql := l_dyn_sql || 'MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL, ';

7798: l_dyn_sql := l_dyn_sql || 'EBI.'||l_supplier_site_name_col ||' , ';
7799: l_dyn_sql := l_dyn_sql || 'EBI.'||l_org_code_col ||' , ';
7800: l_dyn_sql := l_dyn_sql || '''SYNC'' , ';
7801: l_dyn_sql := l_dyn_sql || G_PROCESS_STATUS||' , ';
7802: --l_dyn_sql := l_dyn_sql || 'MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL, ';
7803: l_dyn_sql := l_dyn_sql || 'EBI.'||'CREATED_BY , ';
7804: l_dyn_sql := l_dyn_sql || 'EBI.'||'CREATION_DATE , ';
7805: l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATED_BY , ';
7806: l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATE_DATE , ';

Line 7864: --l_dyn_sql := l_dyn_sql || 'MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL, ';

7860:
7861: l_dyn_sql := l_dyn_sql || 'EBI.'||l_org_code_col ||' , ';
7862: l_dyn_sql := l_dyn_sql || '''SYNC'' , ';
7863: l_dyn_sql := l_dyn_sql || G_PROCESS_STATUS||' , ';
7864: --l_dyn_sql := l_dyn_sql || 'MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL, ';
7865: l_dyn_sql := l_dyn_sql || 'EBI.'||'CREATED_BY , ';
7866: l_dyn_sql := l_dyn_sql || 'EBI.'||'CREATION_DATE , ';
7867: l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATED_BY , ';
7868: l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATE_DATE , ';