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 503: FROM SYS.ALL_TAB_COLUMNS WHERE TABLE_NAME = 'MTL_SYSTEM_ITEMS_INTERFACE'

499: -----------------------------------------------------------------------
500:
501: SELECT COLUMN_NAME
502: BULK COLLECT INTO l_column_name
503: FROM SYS.ALL_TAB_COLUMNS WHERE TABLE_NAME = 'MTL_SYSTEM_ITEMS_INTERFACE'
504: AND COLUMN_NAME NOT IN ('SET_PROCESS_ID',
505: 'TRANSACTION_ID',
506: 'REQUEST_ID',
507: 'PROGRAM_APPLICATION_ID',

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

534: l_column_list := SUBSTR(l_column_list,1,length(l_column_list)-4);
535: END IF;
536:
537: l_dyn_sql := '';
538: l_dyn_sql := l_dyn_sql || ' DELETE MTL_SYSTEM_ITEMS_INTERFACE MSII ' ;
539: l_dyn_sql := l_dyn_sql || ' WHERE ';
540: l_dyn_sql := l_dyn_sql || ' ( ';
541: l_dyn_sql := l_dyn_sql || ' (ITEM_NUMBER IS NOT NULL AND ORGANIZATION_CODE IS NOT NULL ) ';
542: l_dyn_sql := l_dyn_sql || ' OR ';

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

564: l_dyn_sql := l_dyn_sql || ' AND PROCESS_FLAG = :PROCESS_STATUS_1 '; --Bug 3763665
565: l_dyn_sql := l_dyn_sql || ' AND ( ';
566: l_dyn_sql := l_dyn_sql || ' EXISTS ( '; -- there exists a row where item is being Created or updated in the same request
567: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
568: l_dyn_sql := l_dyn_sql || ' FROM MTL_SYSTEM_ITEMS_INTERFACE MSI ';
569: l_dyn_sql := l_dyn_sql || ' WHERE MSI.DESCRIPTION IS NOT NULL ';
570: 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))';
571: l_dyn_sql := l_dyn_sql || ' AND SET_PROCESS_ID = :SET_PROCESS_ID_2 ';
572: l_dyn_sql := l_dyn_sql || ' AND PROCESS_FLAG = :PROCESS_STATUS_2 ';

Line 743: || ' FROM MTL_SYSTEM_ITEMS_INTERFACE '

739: BEGIN
740:
741: l_item_num_sql :=
742: ' SELECT SEGMENT1 , TRANSACTION_ID '
743: || ' FROM MTL_SYSTEM_ITEMS_INTERFACE '
744: || ' WHERE REQUEST_ID = '||REQUEST_ID
745: || ' AND PROCESS_FLAG = '||G_INTF_STATUS_SUCCESS
746: || ' AND TRANSACTION_TYPE = '''||G_CREATE||'''';
747:

Line 781: , p_table_name => 'MTL_SYSTEM_ITEMS_INTERFACE'

777: , p_message_text => NULL
778: , p_token_tbl => l_token_tbl
779: , p_message_type => 'E'
780: , p_row_identifier => l_transaction_id_table(i)
781: , p_table_name => 'MTL_SYSTEM_ITEMS_INTERFACE'
782: , p_entity_id => NULL
783: , p_entity_index => NULL
784: , p_entity_code => G_ERROR_ENTITY_CODE
785: );

Line 1127: FROM mtl_system_items_interface

1123: -- To check if the given Set Process ID already exists in MSII.
1124: --------------------------------------------------------------------------
1125: CURSOR c_msii_set_id_exists(c_set_process_id IN NUMBER) IS
1126: SELECT 'x'
1127: FROM mtl_system_items_interface
1128: WHERE set_process_id = c_set_process_id;
1129:
1130:
1131: ---------------------------------------------------------------------

Line 1215: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL

1211: --------------------------------------------------------------------
1212: UPDATE ego_bulkload_intf
1213: --The Transaction ID sequence that is used in INVPOPIF package to
1214: --auto-populate Transaction ID in MSII.
1215: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
1216: WHERE resultfmt_usage_id = p_resultfmt_usage_id;
1217:
1218: Write_Debug('Retrieving the Display and INTF cols');
1219: i := 0;

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

1331: -- R12C Pack Hierarchy Changes for Trade Item Descriptor --
1332: l_trade_item_descriptor_col := l_intf_col_name;
1333: END IF;
1334:
1335: -- R12 - to update GTIN and GTIN description in MTL_SYSTEM_ITEMS_INTERFACE table
1336: IF (l_prod_col_name = G_GTIN_NUM_ATTR_CODE) THEN
1337: l_prod_col_name_tbl(i) := G_GTIN_NUM_DB_COL;
1338: ELSIF (l_prod_col_name = G_GTIN_DESC_ATTR_CODE) THEN
1339: l_prod_col_name_tbl(i) := G_GTIN_DESC_DB_COL;

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

1762:
1763: -----------------------------------------------------------------
1764: -- Insert rows from EBI into MSII
1765: -----------------------------------------------------------------
1766: l_dyn_sql := 'INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE MSII';
1767: l_dyn_sql := l_dyn_sql || ' ( ';
1768: l_dyn_sql := l_dyn_sql || ' SET_PROCESS_ID , ';
1769: l_dyn_sql := l_dyn_sql || ' TRANSACTION_ID , ';
1770: l_dyn_sql := l_dyn_sql || ' REQUEST_ID , ';

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

1843: --(Catalog Name validation will be done by IOI)
1844: ---------------------------------------------------------------------
1845: -- IF (l_prod_col_name_tbl(i) = G_ITEM_CATALOG_GROUP) THEN
1846: -- l_dyn_sql := '';
1847: -- l_dyn_sql := l_dyn_sql || 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
1848: -- l_dyn_sql := l_dyn_sql || ' SET (MSII.ITEM_CATALOG_GROUP_ID, MSII.PROCESS_FLAG)=';
1849: -- l_dyn_sql := l_dyn_sql || '( ';
1850: -- l_dyn_sql := l_dyn_sql || ' SELECT MICG.ITEM_CATALOG_GROUP_ID ';
1851: -- 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 1888: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

1884:
1885: IF (l_prod_col_name_tbl(i) = G_PRIMARY_UOM) THEN
1886:
1887: -- populating MSII with PRIMARY_UOM_CODE if exists
1888: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
1889: l_dyn_sql := l_dyn_sql || ' SET (MSII.PRIMARY_UOM_CODE ';
1890: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
1891: l_dyn_sql := l_dyn_sql || '( ';
1892: l_dyn_sql := l_dyn_sql || ' SELECT MUOM.UOM_CODE ';

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

1913: Write_Debug(l_dyn_sql);
1914: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
1915:
1916: -- populating MSII with PRIMARY_UOM_CODE if special char for null out exists
1917: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
1918: l_dyn_sql := l_dyn_sql || ' SET MSII.PRIMARY_UOM_CODE = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
1919: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
1920: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
1921: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

1956: -- by doing Value-to-ID Conversion.
1957: ----------------------------------------------------------------------------------
1958: ELSIF (l_prod_col_name_tbl(i) = G_USER_ITEM_TYPE) THEN
1959:
1960: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
1961: l_dyn_sql := l_dyn_sql || ' SET (MSII.ITEM_TYPE ';
1962: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
1963: l_dyn_sql := l_dyn_sql || '( ';
1964: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

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

1985:
1986: Write_Debug(l_dyn_sql);
1987: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
1988:
1989: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
1990: l_dyn_sql := l_dyn_sql || ' SET MSII.ITEM_TYPE = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
1991: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
1992: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
1993: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

2008: -- by doing Value-to-ID Conversion.
2009: ----------------------------------------------------------------------------------
2010: ELSIF (l_prod_col_name_tbl(i) = G_BOM_ITEM_TYPE) THEN
2011:
2012: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2013: l_dyn_sql := l_dyn_sql || ' SET (MSII.BOM_ITEM_TYPE ';
2014: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2015: l_dyn_sql := l_dyn_sql || '( ';
2016: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

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

2037:
2038: Write_Debug(l_dyn_sql);
2039: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2040:
2041: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2042: l_dyn_sql := l_dyn_sql || ' SET MSII.BOM_ITEM_TYPE = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2043: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2044: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2045: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

2055: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id;
2056: Write_Debug('MSII: Updated the BOM Item Types.');
2057:
2058: ELSIF (l_prod_col_name_tbl(i) = G_TRADE_ITEM_DESCRIPTOR) THEN
2059: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2060: l_dyn_sql := l_dyn_sql || ' SET (MSII.TRADE_ITEM_DESCRIPTOR ';
2061: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2062: l_dyn_sql := l_dyn_sql || '( ';
2063: l_dyn_sql := l_dyn_sql || ' SELECT IT.INTERNAL_NAME ';

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

2083:
2084: Write_Debug(l_dyn_sql);
2085: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2086:
2087: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2088: l_dyn_sql := l_dyn_sql || ' SET MSII.TRADE_ITEM_DESCRIPTOR = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2089: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2090: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2091: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

2106: -- by doing Value-to-ID Conversion.
2107: ----------------------------------------------------------------------------------
2108: ELSIF (l_prod_col_name_tbl(i) = G_ENG_ITEM_FLAG) THEN
2109:
2110: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2111: l_dyn_sql := l_dyn_sql || ' SET (MSII.ENG_ITEM_FLAG ';
2112: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2113: l_dyn_sql := l_dyn_sql || '( ';
2114: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

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

2135:
2136: Write_Debug(l_dyn_sql);
2137: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2138:
2139: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2140: l_dyn_sql := l_dyn_sql || ' SET MSII.ENG_ITEM_FLAG = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2141: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2142: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2143: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

2160: -- by doing Value-to-ID Conversion.
2161: ----------------------------------------------------------------------------------
2162: ELSIF (l_prod_col_name_tbl(i) = G_CONVERSIONS) THEN
2163:
2164: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2165: l_dyn_sql := l_dyn_sql || ' SET (MSII.ALLOWED_UNITS_LOOKUP_CODE ';
2166: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2167: l_dyn_sql := l_dyn_sql || '( ';
2168: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

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

2189:
2190: Write_Debug(l_dyn_sql);
2191: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2192:
2193: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2194: l_dyn_sql := l_dyn_sql || ' SET MSII.ALLOWED_UNITS_LOOKUP_CODE = :NULL_NUM ';
2195: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2196: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2197: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

2212: -- by doing Value-to-ID Conversion.
2213: ----------------------------------------------------------------------------------
2214: ELSIF (l_prod_col_name_tbl(i) = G_SECONDARY_DEF_IND) THEN
2215:
2216: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2217: l_dyn_sql := l_dyn_sql || ' SET (MSII.SECONDARY_DEFAULT_IND ';
2218: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2219: l_dyn_sql := l_dyn_sql || '( ';
2220: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

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

2241:
2242: Write_Debug(l_dyn_sql);
2243: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2244:
2245: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2246: l_dyn_sql := l_dyn_sql || ' SET MSII.SECONDARY_DEFAULT_IND = :NULL_NUM';
2247: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2248: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2249: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

2264: -- by doing Value-to-ID Conversion.
2265: ----------------------------------------------------------------------------------
2266: ELSIF (l_prod_col_name_tbl(i) = G_ONT_PRICING_QTY_SRC) THEN
2267:
2268: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2269: l_dyn_sql := l_dyn_sql || ' SET (MSII.ONT_PRICING_QTY_SOURCE ';
2270: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2271: l_dyn_sql := l_dyn_sql || '( ';
2272: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

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

2293:
2294: Write_Debug(l_dyn_sql);
2295: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2296:
2297: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2298: l_dyn_sql := l_dyn_sql || ' SET MSII.ONT_PRICING_QTY_SOURCE = :NULL_NUM';
2299: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2300: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2301: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

2316: -- by doing Value-to-ID Conversion.
2317: ----------------------------------------------------------------------------------
2318: ELSIF (l_prod_col_name_tbl(i) = G_SECONDARY_UOM_CODE) THEN
2319:
2320: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2321: l_dyn_sql := l_dyn_sql || ' SET (MSII.SECONDARY_UOM_CODE ';
2322: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2323: l_dyn_sql := l_dyn_sql || '( ';
2324: l_dyn_sql := l_dyn_sql || ' SELECT MUOM.UOM_CODE ';

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

2344:
2345: Write_Debug(l_dyn_sql);
2346: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2347:
2348: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2349: l_dyn_sql := l_dyn_sql || ' SET MSII.SECONDARY_UOM_CODE = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2350: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2351: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2352: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

2367: -- by doing Value-to-ID Conversion.
2368: ----------------------------------------------------------------------------------
2369: ELSIF (l_prod_col_name_tbl(i) = G_TRACKING_QTY_IND) THEN
2370:
2371: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2372: l_dyn_sql := l_dyn_sql || ' SET (MSII.TRACKING_QUANTITY_IND ';
2373: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2374: l_dyn_sql := l_dyn_sql || '( ';
2375: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';

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

2396:
2397: Write_Debug(l_dyn_sql);
2398: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2399:
2400: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2401: l_dyn_sql := l_dyn_sql || ' SET MSII.TRACKING_QUANTITY_IND = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2402: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2403: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2404: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

2420: -- by doing Value-to-ID Conversion. Rathna
2421: ----------------------------------------------------------------------------------
2422: ELSIF (l_prod_col_name_tbl(i) = G_INVENTORY_ITEM_STATUS) THEN
2423:
2424: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2425: l_dyn_sql := l_dyn_sql || ' SET (MSII.INVENTORY_ITEM_STATUS_CODE ';
2426: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2427: l_dyn_sql := l_dyn_sql || '( ';
2428: l_dyn_sql := l_dyn_sql || ' SELECT IT.INVENTORY_ITEM_STATUS_CODE ';

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

2447:
2448: Write_Debug(l_dyn_sql);
2449: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2450:
2451: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2452: l_dyn_sql := l_dyn_sql || ' SET MSII.TRACKING_QUANTITY_IND = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2453: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2454: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2455: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

2477: l_col_name := l_prod_col_name_tbl(i);
2478: END IF;
2479: Write_Debug('Updating MSII: l_col_name = ' || l_col_name);
2480: --Bug Fix 4713312:END
2481: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2482: l_dyn_sql := l_dyn_sql || ' SET MSII.'||l_col_name||' = ';
2483: l_dyn_sql := l_dyn_sql || '( ';
2484: -- MLS Status
2485: -- IF (l_prod_col_name_tbl(i) = G_INV_STATUS_CODE_NAME) THEN

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

2545: IF (l_prod_col_name_tbl(i) = G_DESCRIPTION) THEN
2546: --DPHILIP: we need to check what is the impact of this check
2547: Write_Debug('DPHILIP :updating description setting the error status to: ' || G_DESCRIPTION_ERR_STS);
2548:
2549: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2550: l_dyn_sql := l_dyn_sql || ' SET MSII.PROCESS_FLAG = '||G_DESCRIPTION_ERR_STS||' ';
2551: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2552: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2553: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

2587: ----------------------------------------------------------------------------------
2588: -- First Transfer the Lifecycle information from EBI to MSII
2589: -- by doing Value-to-ID Conversion.
2590: ----------------------------------------------------------------------------------
2591: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2592: l_dyn_sql := l_dyn_sql || ' SET (MSII.LIFECYCLE_ID ';
2593: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2594: l_dyn_sql := l_dyn_sql || '( ';
2595: l_dyn_sql := l_dyn_sql || ' SELECT LC.PROJ_ELEMENT_ID ';

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

2614:
2615: Write_Debug(l_dyn_sql);
2616: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2617:
2618: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2619: l_dyn_sql := l_dyn_sql || ' SET MSII.LIFECYCLE_ID = :NULL_NUM';
2620: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2621: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2622: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

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

2640: ----------------------------------------------------------------------------------
2641: -- Next Transfer the Lifecycle Phase information from EBI to MSII
2642: -- by doing Value-to-ID Conversion, and by joining Lifecycle ID information.
2643: ----------------------------------------------------------------------------------
2644: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2645: l_dyn_sql := l_dyn_sql || ' SET (MSII.CURRENT_PHASE_ID ';
2646: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2647: l_dyn_sql := l_dyn_sql || '( ';
2648: l_dyn_sql := l_dyn_sql || ' SELECT LCP.PROJ_ELEMENT_ID ';

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

2668:
2669: Write_Debug(l_dyn_sql);
2670: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, p_resultfmt_usage_id;
2671:
2672: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';
2673: l_dyn_sql := l_dyn_sql || ' SET MSII.CURRENT_PHASE_ID = :NULL_NUM';
2674: l_dyn_sql := l_dyn_sql || ' WHERE MSII.SET_PROCESS_ID = ' || l_msii_set_process_id;
2675: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2676: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';

Line 2695: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII

2691: END IF; --IF ( l_prod_col_name_tbl.count > 0) THEN
2692: --
2693: -- convert all date fields values from Excel Null to INTF Null
2694: --
2695: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
2696: SET start_date_active = DECODE(start_date_active,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,start_date_active),
2697: end_date_active = DECODE(end_date_active,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,end_date_active),
2698: engineering_date = DECODE(engineering_date,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,engineering_date)
2699: WHERE MSII.SET_PROCESS_ID = l_msii_set_process_id

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

2873: l_dyn_sql := l_dyn_sql || ', '||G_USER_ID ;
2874: l_dyn_sql := l_dyn_sql || ', SYSDATE ';
2875: l_dyn_sql := l_dyn_sql || ', '||G_USER_ID;
2876: l_dyn_sql := l_dyn_sql || ', '||G_LOGIN_ID;
2877: l_dyn_sql := l_dyn_sql || ', ''MTL_SYSTEM_ITEMS_INTERFACE'' ';
2878: l_dyn_sql := l_dyn_sql || ', DECODE(MSII.PROCESS_FLAG, ';
2879: l_dyn_sql := l_dyn_sql || G_ITEM_CATALOG_NAME_ERR_STS||', ''EGO_ITEMCATALOG_INVALID'', ';
2880: l_dyn_sql := l_dyn_sql || G_PRIMARY_UOM_ERR_STS||', ''EGO_PRIMARYUOM_INVALID'', ';
2881: l_dyn_sql := l_dyn_sql || G_LIFECYCLE_ERR_STS||', ''EGO_LIFECYCLE_INVALID'', ';

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

2925: l_dyn_sql := l_dyn_sql || ' ) ';
2926: l_dyn_sql := l_dyn_sql || ', MSII.TRANSACTION_ID ';
2927: l_dyn_sql := l_dyn_sql || ', NULL ';
2928: l_dyn_sql := l_dyn_sql || ', NULL ';
2929: l_dyn_sql := l_dyn_sql || 'FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, EGO_BULKLOAD_INTF EBI ';
2930: l_dyn_sql := l_dyn_sql || 'WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID ';
2931: l_dyn_sql := l_dyn_sql || ' AND MSII.SET_PROCESS_ID = '||p_set_process_id;
2932: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG IN ';
2933: l_dyn_sql := l_dyn_sql || ' ( ';

Line 2973: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII

2969: --Now that the error messages are inserted, update MSII lines to
2970: --Process status ERROR.
2971: -----------------------------------------------------------------------
2972:
2973: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
2974: SET MSII.PROCESS_FLAG = G_INTF_STATUS_ERROR
2975: WHERE MSII.SET_PROCESS_ID = p_set_process_id
2976: AND MSII.PROCESS_FLAG IN
2977: (

Line 3002: -- DELETE MTL_SYSTEM_ITEMS_INTERFACE MSII

2998: );
2999:
3000: Write_Debug('MSII: Updated all the line statuses to Error for Pre-processing validation errors');
3001:
3002: -- DELETE MTL_SYSTEM_ITEMS_INTERFACE MSII
3003: -- WHERE
3004: -- (
3005: -- (
3006: -- ITEM_NUMBER IS NOT NULL AND

Line 3059: -- FROM MTL_SYSTEM_ITEMS_INTERFACE MSI

3055: ------------------------------------------------------------------------------------------------
3056: -- AND PROCESS_FLAG = G_PROCESS_STATUS --Bug 3763665
3057: -- AND (EXISTS( -- there exists a row where item is being Created or updated in the same request
3058: -- SELECT 'X'
3059: -- FROM MTL_SYSTEM_ITEMS_INTERFACE MSI
3060: -- WHERE MSI.DESCRIPTION IS NOT NULL
3061: -- AND NVL(MSI.ITEM_NUMBER,FND_API.G_MISS_CHAR) = NVL(MSII.ITEM_NUMBER,FND_API.G_MISS_CHAR)
3062: -- AND SET_PROCESS_ID = p_set_process_id
3063: -- AND PROCESS_FLAG = G_PROCESS_STATUS

Line 3084: -- UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII

3080: -- -- So, all new Items (if ENG_ITEM_FLAG is unspecified) are created as
3081: -- -- Engineering Items.
3082: -- -----------------------------------------------------------------------
3083: --
3084: -- UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
3085: -- SET MSII.ENG_ITEM_FLAG = 'Y'
3086: -- WHERE MSII.SET_PROCESS_ID = p_set_process_id
3087: -- AND MSII.INVENTORY_ITEM_ID IS NULL
3088: -- AND MSII.TRANSACTION_TYPE IN ('SYNC', 'CREATE')

Line 3192: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

3188: UPDATE EGO_BULKLOAD_INTF EBI
3189: SET EBI.PROCESS_STATUS =
3190: (
3191: SELECT MSII.PROCESS_FLAG
3192: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
3193: WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
3194: )
3195: WHERE EXISTS
3196: (

Line 3198: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

3194: )
3195: WHERE EXISTS
3196: (
3197: SELECT 'X'
3198: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
3199: WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
3200: )
3201: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
3202:

Line 3217: -- FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

3213: -- SET EBI.PROCESS_STATUS = G_INTF_STATUS_ERROR
3214: -- WHERE EXISTS
3215: -- (
3216: -- SELECT 'X'
3217: -- FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
3218: -- WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
3219: -- AND MSII.PROCESS_FLAG = G_INTF_STATUS_SUCCESS
3220: -- AND MSII.INVENTORY_ITEM_ID IS NULL
3221: -- )

Line 3245: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

3241: UPDATE EGO_BULKLOAD_INTF EBI
3242: SET EBI.INSTANCE_PK1_VALUE =
3243: (
3244: SELECT MSII.INVENTORY_ITEM_ID
3245: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
3246: WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
3247: )
3248: WHERE EXISTS
3249: (

Line 3251: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

3247: )
3248: WHERE EXISTS
3249: (
3250: SELECT 'X'
3251: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
3252: WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
3253: )
3254: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
3255: AND EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS;

Line 3344: FROM mtl_system_items_interface

3340: -- To check if the given Set Process ID already exists in MSII.
3341: --------------------------------------------------------------------------
3342: CURSOR c_msii_set_id_exists(c_set_process_id IN NUMBER) IS
3343: SELECT 'x'
3344: FROM mtl_system_items_interface
3345: WHERE set_process_id = c_set_process_id;
3346:
3347: ---------------------------------------------------------------------
3348: -- Type Declarations

Line 3404: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL

3400: -- this was happening because the error was logged with a previous transaction id and
3401: -- here a new transaction id was updated to Bulkload interface table.
3402: IF p_data_level <> G_ITEM_DATA_LEVEL THEN
3403: UPDATE ego_bulkload_intf
3404: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
3405: WHERE resultfmt_usage_id = p_resultfmt_usage_id;
3406: END IF;
3407:
3408: Write_Debug('Retrieving the Display and INTF cols');

Line 4240: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

4236: UPDATE EGO_BULKLOAD_INTF EBI
4237: SET EBI.INSTANCE_PK1_VALUE =
4238: (
4239: SELECT MSII.INVENTORY_ITEM_ID
4240: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
4241: WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
4242: )
4243: WHERE EXISTS
4244: (

Line 4246: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

4242: )
4243: WHERE EXISTS
4244: (
4245: SELECT 'X'
4246: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
4247: WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
4248: )
4249: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
4250: AND EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS;

Line 5719: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE ';

5715: -- Update MSII with the internal values derived above for the row. --
5716: -- Update that particular row using Transaction ID. --
5717: --------------------------------------------------------------------------------
5718:
5719: l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE ';
5720: l_dyn_sql := l_dyn_sql || ' SET ';
5721:
5722: FOR i in 1..l_msii_col_table.COUNT LOOP
5723: -- flash errors where int val is not correct

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

5802: l_dyn_sql := l_dyn_sql || ', '||G_USER_ID ;
5803: l_dyn_sql := l_dyn_sql || ', SYSDATE ';
5804: l_dyn_sql := l_dyn_sql || ', '||G_USER_ID;
5805: l_dyn_sql := l_dyn_sql || ', '||G_LOGIN_ID;
5806: l_dyn_sql := l_dyn_sql || ', ''MTL_SYSTEM_ITEMS_INTERFACE'' ';
5807: l_dyn_sql := l_dyn_sql || ', DECODE(MSII.PROCESS_FLAG, ';
5808: l_dyn_sql := l_dyn_sql || G_VS_INVALID_ERR_STS||', ''EGO_IPI_INVALID_VALUE''';
5809: l_dyn_sql := l_dyn_sql || ' ) ';
5810: l_dyn_sql := l_dyn_sql || ', NULL ';

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

5817: l_dyn_sql := l_dyn_sql || ' ) ';
5818: l_dyn_sql := l_dyn_sql || ', MSII.TRANSACTION_ID ';
5819: l_dyn_sql := l_dyn_sql || ', NULL ';
5820: l_dyn_sql := l_dyn_sql || ', NULL ';
5821: l_dyn_sql := l_dyn_sql || 'FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, EGO_BULKLOAD_INTF EBI ';
5822: l_dyn_sql := l_dyn_sql || 'WHERE MSII.TRANSACTION_ID = EBI.TRANSACTION_ID ';
5823: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG IN ';
5824: l_dyn_sql := l_dyn_sql || ' ( ';
5825: l_dyn_sql := l_dyn_sql || G_VS_INVALID_ERR_STS; --take care of invalid value set

Line 5834: UPDATE MTL_SYSTEM_ITEMS_INTERFACE

5830: Write_Debug(l_dyn_sql);
5831: EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id;
5832:
5833: Write_Debug(' All Value Set value errors populated.');
5834: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
5835: SET PROCESS_FLAG = G_INTF_STATUS_ERROR
5836: WHERE PROCESS_FLAG IN
5837: (
5838: G_VS_INVALID_ERR_STS

Line 7020: FROM MTL_SYSTEM_ITEMS_INTERFACE

7016:
7017: --Removing Item Num NULL clause - R12C
7018: SELECT DISTINCT item_catalog_group_id
7019: BULK COLLECT INTO l_cc_id_table
7020: FROM MTL_SYSTEM_ITEMS_INTERFACE
7021: WHERE set_process_id = p_set_id
7022: AND (organization_id = p_org_id OR p_all_org = 1)
7023: AND process_flag = p_rec_status
7024: AND transaction_type = 'CREATE';

Line 7041: FROM MTL_SYSTEM_ITEMS_INTERFACE

7037: source_system_reference
7038: BULK COLLECT INTO l_org_id_table, l_old_item_num_table,
7039: l_trans_id_table, l_ss_id_table,
7040: l_ss_ref_table
7041: FROM MTL_SYSTEM_ITEMS_INTERFACE
7042: WHERE set_process_id = p_set_id
7043: AND (organization_id = p_org_id OR p_all_org = 1)
7044: AND item_catalog_group_id = l_cc_id_table(cc_row_index)
7045: AND process_flag = p_rec_status

Line 7059: UPDATE mtl_system_items_interface

7055: );
7056:
7057: -- for item interface table
7058: FORALL item_num_row_index IN 1..l_item_rows_processed
7059: UPDATE mtl_system_items_interface
7060: SET item_number = l_item_num_table(item_num_row_index),
7061: SEGMENT1 = NULL,
7062: SEGMENT2 = NULL,
7063: SEGMENT3 = NULL,

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

7511: END IF;
7512: l_dyn_sql := l_dyn_sql || 'EBI.'||l_org_code_col ||' , ';
7513: l_dyn_sql := l_dyn_sql || '''SYNC'' , ';
7514: l_dyn_sql := l_dyn_sql || G_PROCESS_STATUS||' , ';
7515: l_dyn_sql := l_dyn_sql || 'MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL , ';
7516: l_dyn_sql := l_dyn_sql || 'EBI.'||'CREATED_BY , ';
7517: l_dyn_sql := l_dyn_sql || 'EBI.'||'CREATION_DATE , ';
7518: l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATED_BY , ';
7519: l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATE_DATE , ';

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

7606: l_dyn_sql := l_dyn_sql || ' SOURCE_SYSTEM_REFERENCE , ';
7607: l_dyn_sql := l_dyn_sql || ' ASSOCIATION_ID ';
7608: l_dyn_sql := l_dyn_sql || ' ) ';
7609: l_dyn_sql := l_dyn_sql || 'SELECT ';
7610: l_dyn_sql := l_dyn_sql || 'MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL , ';
7611: l_dyn_sql := l_dyn_sql || ' INTERSECTIONS.* ';
7612: l_dyn_sql := l_dyn_sql || ' FROM ( ';
7613:
7614: l_dyn_sql := l_dyn_sql || 'SELECT ';

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

7645: l_dyn_sql := l_dyn_sql || 'EBI.'||l_supplier_site_name_col ||' , ';
7646: l_dyn_sql := l_dyn_sql || 'EBI.'||l_org_code_col ||' , ';
7647: l_dyn_sql := l_dyn_sql || '''SYNC'' , ';
7648: l_dyn_sql := l_dyn_sql || G_PROCESS_STATUS||' , ';
7649: --l_dyn_sql := l_dyn_sql || 'MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL, ';
7650: l_dyn_sql := l_dyn_sql || 'EBI.'||'CREATED_BY , ';
7651: l_dyn_sql := l_dyn_sql || 'EBI.'||'CREATION_DATE , ';
7652: l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATED_BY , ';
7653: l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATE_DATE , ';

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

7707:
7708: l_dyn_sql := l_dyn_sql || 'EBI.'||l_org_code_col ||' , ';
7709: l_dyn_sql := l_dyn_sql || '''SYNC'' , ';
7710: l_dyn_sql := l_dyn_sql || G_PROCESS_STATUS||' , ';
7711: --l_dyn_sql := l_dyn_sql || 'MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL, ';
7712: l_dyn_sql := l_dyn_sql || 'EBI.'||'CREATED_BY , ';
7713: l_dyn_sql := l_dyn_sql || 'EBI.'||'CREATION_DATE , ';
7714: l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATED_BY , ';
7715: l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATE_DATE , ';