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.

[Home] [Help]

APPS.EGO_ITEM_BULKLOAD_PKG dependencies on EGO_BULKLOAD_INTF

Line 99: G_ERROR_TABLE_NAME VARCHAR2(99) := 'EGO_BULKLOAD_INTF';

95:
96: ---------------------------------------------------------------
97: -- Used for Error Reporting. --
98: ---------------------------------------------------------------
99: G_ERROR_TABLE_NAME VARCHAR2(99) := 'EGO_BULKLOAD_INTF';
100: G_ERROR_ENTITY_CODE VARCHAR2(99) := 'EGO_ITEM';
101: G_ERROR_FILE_NAME VARCHAR2(99);
102: G_BO_IDENTIFIER VARCHAR2(99) := 'EGO_ITEM';
103: G_INV_STATUS_CODE_NAME VARCHAR2(99) := 'INVENTORY_ITEM_STATUS_CODE';

Line 133: -- Generic Bulkload interface table: EGO_BULKLOAD_INTF segment number range --

129: G_SYNC CONSTANT VARCHAR2(10) := 'SYNC';
130:
131:
132: ------------------------------------------------------------------------------
133: -- Generic Bulkload interface table: EGO_BULKLOAD_INTF segment number range --
134: -- as per data type. --
135: ------------------------------------------------------------------------------
136: G_VARCHAR_SEQ_MIN NUMBER := 1;
137: G_VARCHAR_SEQ_MAX NUMBER := 200;

Line 214: -- Using following Columns in EGO_BULKLOAD_INTF as buffer columns to --

210: G_REV_IMPL_DATE_DB_COL VARCHAR2(50) := 'IMPLEMENTATION_DATE';
211: G_REV_EFF_DATE_DB_COL VARCHAR2(50) := 'EFFECTIVITY_DATE';
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: -----------------------------------------------------------------------

Line 663: || ' FROM EGO_BULKLOAD_INTF '

659: p_item_num_colname IN VARCHAR2) IS
660:
661: l_item_num_sql VARCHAR2(10000) :=
662: ' SELECT '|| p_item_num_colname
663: || ' FROM EGO_BULKLOAD_INTF '
664: || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
665: --|| ' AND PROCESS_STATUS = 1';
666:
667: -----------------------------------------------------------------------

Line 814: || ' FROM EGO_BULKLOAD_INTF '

810: --------------------------------------------------------------------
811: l_org_id_sql VARCHAR2(10000) :=
812: ' SELECT '
813: || ' INSTANCE_PK2_VALUE '
814: || ' FROM EGO_BULKLOAD_INTF '
815: || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID'
816: || ' AND '|| p_item_num_colname ||' IS NULL '
817: || ' AND PROCESS_STATUS = 1';
818:

Line 951: DELETE EGO_BULKLOAD_INTF

947:
948: BEGIN
949:
950: --Delete all the earlier loads from the same spreadsheet.
951: DELETE EGO_BULKLOAD_INTF
952: WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
953: AND PROCESS_STATUS <> 1;
954:
955:

Line 961: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF EBI';

957: ---------------------------------------------------------------
958: -- Introduced for 11.5.10, so that Java Conc Program can --
959: -- continue writing to the same Error Log File. --
960: ---------------------------------------------------------------
961: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF EBI';
962: l_dyn_sql := l_dyn_sql || ' SET '||G_ERR_LOGFILE_COL ||' = ''' || G_ERRFILE_PATH_AND_NAME ||'''';
963: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
964: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
965: --------------------------------------------------------------

Line 1212: UPDATE ego_bulkload_intf

1208: Write_Debug('About to populate the EBI with Trans IDs');
1209: --------------------------------------------------------------------
1210: --Populate the Transaction IDs for current Result fmt usage ID
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;

Line 1359: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

1355:
1356: -----------------------------------------------------------------------
1357: -- Save all Value-to-ID conversion columns in designated places in EBI.
1358: -----------------------------------------------------------------------
1359: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
1360: l_dyn_sql := l_dyn_sql || ' SET ';
1361: IF l_item_catalog_name_col IS NOT NULL THEN
1362: l_dyn_sql := l_dyn_sql || G_ITEM_CATALOG_NAME_EBI_COL||' = '||l_item_catalog_name_col|| ' ,';
1363: l_value_to_id_col_exists := TRUE;

Line 1428: l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';

1424: Write_Debug('Updating EBI with Org IDs');
1425: -----------------------------------------------------
1426: -- Update Instance PK2 Value with ORG ID.
1427: -----------------------------------------------------
1428: l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';
1429: l_dyn_sql := l_dyn_sql || ' SET INSTANCE_PK2_VALUE = ';
1430: l_dyn_sql := l_dyn_sql || ' ( ';
1431: l_dyn_sql := l_dyn_sql || ' SELECT ORGANIZATION_ID ';
1432: l_dyn_sql := l_dyn_sql || ' FROM MTL_PARAMETERS ';

Line 1479: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF ';

1475: -- Upcasing the Transaction Type, for fixing Trans Type related bugs
1476: ----------------------------------------------------------------------
1477: l_dyn_sql := l_dyn_sql || ' UPPER(TRANSACTION_TYPE) , ';
1478: l_dyn_sql := l_dyn_sql || ' TRANSACTION_ID ';
1479: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF ';
1480: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1481: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1';
1482: -- l_dyn_sql := l_dyn_sql || ' AND '|| l_item_number_col ||' IS NOT NULL';
1483:

Line 1541: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

1537: l_inventory_item_id := FND_FLEX_KEYVAL.combination_id;
1538:
1539: Write_Debug('Inv Item ID : '||To_char(l_inventory_item_id));
1540:
1541: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
1542: l_dyn_sql := l_dyn_sql || ' SET INSTANCE_PK1_VALUE = '||l_inventory_item_id;
1543: l_dyn_sql := l_dyn_sql || ' WHERE INSTANCE_PK2_VALUE = '||l_org_id_table(i);
1544: l_dyn_sql := l_dyn_sql || ' AND '|| l_item_number_col|| ' = :ITEM_NUMBER';
1545: l_dyn_sql := l_dyn_sql || ' AND RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

Line 1571: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF EBI';

1567: -- Bug #4652582(RSOUNDAR)
1568: -----------------------------------------------------------------
1569: IF G_CATALOG_GROUP_ID IS NULL THEN
1570: IF l_item_catalog_name_col IS NOT NULL THEN
1571: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF EBI';
1572: l_dyn_sql := l_dyn_sql || ' SET '||G_ITEM_CATALOG_EBI_COL||' = ';
1573: l_dyn_sql := l_dyn_sql || '( ';
1574: l_dyn_sql := l_dyn_sql || ' SELECT TO_CHAR(MICG.ITEM_CATALOG_GROUP_ID) ';
1575: l_dyn_sql := l_dyn_sql || ' FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG ';

Line 1591: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

1587: ELSE -- G_CATALOG_GROUP_ID IS NOT NULL
1588: /* Since Item Number can be NULL, removing its reference from the WHERE Clause.
1589:
1590: l_dyn_sql := '';
1591: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
1592: -------------------------------------------------------------
1593: --Storing Catalog Group ID and other imp data in buffer cols
1594: -------------------------------------------------------------
1595: l_dyn_sql := l_dyn_sql || ' SET '||G_ITEM_CATALOG_EBI_COL||' = '||G_CATALOG_GROUP_ID;

Line 1603: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

1599: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
1600:
1601: EXECUTE IMMEDIATE l_dyn_sql USING l_item_number_table(i), p_resultfmt_usage_id;
1602: */
1603: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
1604: -------------------------------------------------------------
1605: --Storing Catalog Group ID and other imp data in buffer cols
1606: -------------------------------------------------------------
1607: l_dyn_sql := l_dyn_sql || ' SET '||G_ITEM_CATALOG_EBI_COL||' = '||G_CATALOG_GROUP_ID;

Line 1664: Write_Debug('Populating item number into ego_bulkload_intf for function generated items');

1660: DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
1661:
1662: Write_Debug('Done with Item IDs population.');
1663:
1664: Write_Debug('Populating item number into ego_bulkload_intf for function generated items');
1665: IF G_PDH_BATCH THEN
1666: /***
1667: l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||
1668: ' SET '||l_item_number_col||' = ''$$FG-''||TO_CHAR(transaction_id)'||

Line 1667: l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||

1663:
1664: Write_Debug('Populating item number into ego_bulkload_intf for function generated items');
1665: IF G_PDH_BATCH THEN
1666: /***
1667: l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||
1668: ' SET '||l_item_number_col||' = ''$$FG-''||TO_CHAR(transaction_id)'||
1669: ' ,c_fix_column12 = TO_CHAR(transaction_id)'||
1670: ' WHERE resultfmt_usage_id = :RESULTFMT_USAGE_ID'||
1671: ' AND '||G_ITEM_CATALOG_EBI_COL||' IS NOT NULL'||

Line 1690: l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||

1686: --
1687: -- R12C changes Tag_Function_Gen_Item_Nums with item_num as $$FG-transaction_id
1688: -- populate item number col if null but always set the source system reference
1689: --
1690: l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||
1691: ' SET '||l_item_number_col||' = NVL( '||l_item_number_col||', ''$$FG-''||TO_CHAR(transaction_id) )'||
1692: ' ,c_fix_column12 = TO_CHAR(transaction_id)'||
1693: ' WHERE resultfmt_usage_id = :RESULTFMT_USAGE_ID'||
1694: ' AND '||G_ITEM_CATALOG_EBI_COL||' IS NOT NULL'||

Line 1721: l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||

1717: --
1718: -- R12C changes Tag_Sequence_Gen_Item_Nums with item_num as $$SG-transaction_id
1719: -- populate item number col if null but always set the source system reference
1720: --
1721: l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||
1722: ' SET '||l_item_number_col||' = NVL( '||l_item_number_col||', ''$$SG-''||TO_CHAR(transaction_id) )'||
1723: ' ,c_fix_column12 = TO_CHAR(transaction_id)'||
1724: ' WHERE resultfmt_usage_id = :RESULTFMT_USAGE_ID'||
1725: ' AND '||G_ITEM_CATALOG_EBI_COL||' IS NOT NULL'||

Line 1807: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

1803: l_dyn_sql := l_dyn_sql || G_PROCESS_STATUS||' , ';
1804: l_dyn_sql := l_dyn_sql || ' TO_NUMBER(EBI.C_FIX_COLUMN11) , ';
1805: l_dyn_sql := l_dyn_sql || ' EBI.C_FIX_COLUMN12 , ';
1806: l_dyn_sql := l_dyn_sql || ' EBI.C_FIX_COLUMN13 ';
1807: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
1808: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1809: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1';
1810:
1811: Write_Debug(l_dyn_sql);

Line 1818: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

1814:
1815: ----------------------------------------------------------------------------------
1816: --Save Item Num, Org Code in designated columns in EBI for Error Reporting later
1817: ----------------------------------------------------------------------------------
1818: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
1819: l_dyn_sql := l_dyn_sql || ' SET '||G_ITEM_NUMBER_EBI_COL||' = '||l_item_number_col||' , ';
1820: l_dyn_sql := l_dyn_sql || G_ORG_CODE_EBI_COL||' = '||l_org_code_col ;
1821: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1822: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';

Line 1852: -- l_dyn_sql := l_dyn_sql || ' FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG, EGO_BULKLOAD_INTF EBI ';

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 || ' ) ';
1852: -- l_dyn_sql := l_dyn_sql || ' FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG, EGO_BULKLOAD_INTF EBI ';
1853: -- l_dyn_sql := l_dyn_sql || ' WHERE MICG.CONCATENATED_SEGMENTS(+) = EBI.'||l_intf_col_name_tbl(i);
1854: -- l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1855: -- l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
1856: -- ----------------------------------------------------------------------------------

Line 1864: -- l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

1860: -- l_dyn_sql := l_dyn_sql || ') ';
1861: -- l_dyn_sql := l_dyn_sql || ' WHERE EXISTS ';
1862: -- l_dyn_sql := l_dyn_sql || '( ';
1863: -- l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
1864: -- l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
1865: -- l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
1866: -- l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
1867: -- ----------------------------------------------------------------------------------
1868: -- -- Validate only for the NOT NULL values, and update MSII.

Line 1894: l_dyn_sql := l_dyn_sql || ' FROM MTL_UNITS_OF_MEASURE_TL MUOM, EGO_BULKLOAD_INTF EBI ';

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 ';
1893: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(MUOM.UOM_CODE, ''NULL''),''NULL'','||G_PRIMARY_UOM_ERR_STS||', ' || G_PROCESS_STATUS || ' ) ';
1894: l_dyn_sql := l_dyn_sql || ' FROM MTL_UNITS_OF_MEASURE_TL MUOM, EGO_BULKLOAD_INTF EBI ';
1895: l_dyn_sql := l_dyn_sql || ' WHERE MUOM.UNIT_OF_MEASURE_TL (+) = EBI.'||l_intf_col_name_tbl(i);
1896: l_dyn_sql := l_dyn_sql || ' AND MUOM.LANGUAGE (+) = Userenv(''LANG'') ';
1897: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1898: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';

Line 1905: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

1901: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
1902: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
1903: l_dyn_sql := l_dyn_sql || '( ';
1904: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
1905: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
1906: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
1907: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
1908: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
1909: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 1924: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

1920: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
1921: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
1922: l_dyn_sql := l_dyn_sql || '( ';
1923: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
1924: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
1925: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
1926: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
1927: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
1928: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 1966: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';

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 ';
1965: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_USER_ITEM_TYPE_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
1966: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
1967: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''ITEM_TYPE'' ';
1968: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
1969: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
1970: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

Line 1978: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

1974: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
1975: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
1976: l_dyn_sql := l_dyn_sql || '( ';
1977: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
1978: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
1979: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
1980: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
1981: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
1982: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 1996: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

1992: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
1993: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
1994: l_dyn_sql := l_dyn_sql || '( ';
1995: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
1996: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
1997: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
1998: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
1999: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2000: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2018: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';

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 ';
2017: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_BOM_ITEM_TYPE_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2018: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2019: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''BOM_ITEM_TYPE'' ';
2020: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2021: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2022: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

Line 2030: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2026: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2027: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2028: l_dyn_sql := l_dyn_sql || '( ';
2029: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2030: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2031: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2032: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2033: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2034: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 2048: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2044: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2045: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2046: l_dyn_sql := l_dyn_sql || '( ';
2047: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2048: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2049: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2050: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2051: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2052: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2065: l_dyn_sql := l_dyn_sql || ' FROM EGO_VALUE_SET_VALUES_V IT, EGO_BULKLOAD_INTF EBI ';

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 ';
2064: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.INTERNAL_NAME, ''NULL''),''NULL'','||G_TRADE_ITEM_DESC_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2065: l_dyn_sql := l_dyn_sql || ' FROM EGO_VALUE_SET_VALUES_V IT, EGO_BULKLOAD_INTF EBI ';
2066: l_dyn_sql := l_dyn_sql || ' WHERE IT.VALUE_SET_NAME = ''TradeItemDescVS'' ';
2067: l_dyn_sql := l_dyn_sql || ' AND IT.DISPLAY_NAME = EBI.'||l_intf_col_name_tbl(i);
2068: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
2069: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';

Line 2076: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2072: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2073: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2074: l_dyn_sql := l_dyn_sql || '( ';
2075: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2076: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2077: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2078: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2079: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2080: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 2094: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2090: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2091: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2092: l_dyn_sql := l_dyn_sql || '( ';
2093: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2094: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2095: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2096: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2097: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2098: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2116: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';

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 ';
2115: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_ENG_ITEM_FLAG_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2116: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2117: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''EGO_YES_NO'' ';
2118: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2119: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2120: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

Line 2128: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2124: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2125: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2126: l_dyn_sql := l_dyn_sql || '( ';
2127: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2128: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2129: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2130: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2131: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2132: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 2146: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2142: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2143: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2144: l_dyn_sql := l_dyn_sql || '( ';
2145: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2146: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2147: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2148: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2149: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2150: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2170: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';

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 ';
2169: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_CONVERSIONS_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2170: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2171: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''MTL_CONVERSION_TYPE'' ';
2172: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2173: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2174: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

Line 2182: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2178: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2179: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2180: l_dyn_sql := l_dyn_sql || '( ';
2181: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2182: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2183: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2184: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2185: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2186: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 2200: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2196: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2197: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2198: l_dyn_sql := l_dyn_sql || '( ';
2199: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2200: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2201: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2202: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2203: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2204: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2222: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';

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 ';
2221: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_SECONDARY_DEF_IND_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2222: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2223: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''INV_DEFAULTING_UOM_TYPE'' ';
2224: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2225: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2226: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

Line 2234: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2230: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2231: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2232: l_dyn_sql := l_dyn_sql || '( ';
2233: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2234: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2235: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2236: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2237: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2238: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 2252: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2248: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2249: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2250: l_dyn_sql := l_dyn_sql || '( ';
2251: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2252: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2253: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2254: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2255: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2256: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2274: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';

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 ';
2273: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_ONT_PRICING_QTY_SRC_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2274: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2275: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''INV_PRICING_UOM_TYPE'' ';
2276: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2277: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2278: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

Line 2286: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2282: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2283: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2284: l_dyn_sql := l_dyn_sql || '( ';
2285: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2286: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2287: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2288: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2289: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2290: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 2304: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2300: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2301: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2302: l_dyn_sql := l_dyn_sql || '( ';
2303: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2304: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2305: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2306: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2307: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2308: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2326: l_dyn_sql := l_dyn_sql || ' FROM MTL_UNITS_OF_MEASURE_TL MUOM, EGO_BULKLOAD_INTF EBI ';

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 ';
2325: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(MUOM.UOM_CODE, ''NULL''),''NULL'','||G_SECONDARY_UOM_CODE_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2326: l_dyn_sql := l_dyn_sql || ' FROM MTL_UNITS_OF_MEASURE_TL MUOM, EGO_BULKLOAD_INTF EBI ';
2327: l_dyn_sql := l_dyn_sql || ' WHERE MUOM.UNIT_OF_MEASURE_TL (+) = EBI.'||l_intf_col_name_tbl(i);
2328: l_dyn_sql := l_dyn_sql || ' AND MUOM.LANGUAGE (+) = Userenv(''LANG'') ';
2329: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
2330: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';

Line 2337: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2333: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2334: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2335: l_dyn_sql := l_dyn_sql || '( ';
2336: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2337: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2338: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2339: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2340: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2341: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 2355: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2351: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2352: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2353: l_dyn_sql := l_dyn_sql || '( ';
2354: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2355: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2356: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2357: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2358: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2359: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2377: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';

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 ';
2376: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_TRACKING_QTY_IND_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2377: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2378: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''INV_TRACKING_UOM_TYPE'' ';
2379: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2380: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2381: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

Line 2389: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2385: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2386: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2387: l_dyn_sql := l_dyn_sql || '( ';
2388: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2389: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2390: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2391: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2392: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2393: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 2407: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2403: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2404: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2405: l_dyn_sql := l_dyn_sql || '( ';
2406: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2407: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2408: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2409: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2410: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2411: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2430: l_dyn_sql := l_dyn_sql || ' FROM MTL_ITEM_STATUS IT, EGO_BULKLOAD_INTF EBI ';

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 ';
2429: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.INVENTORY_ITEM_STATUS_CODE, ''NULL''),''NULL'','||G_INV_ITEM_STATUS_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2430: l_dyn_sql := l_dyn_sql || ' FROM MTL_ITEM_STATUS IT, EGO_BULKLOAD_INTF EBI ';
2431: l_dyn_sql := l_dyn_sql || ' WHERE IT.INVENTORY_ITEM_STATUS_CODE_TL (+) = EBI.'||l_intf_col_name_tbl(i);
2432: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
2433: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2434: l_dyn_sql := l_dyn_sql || ') ';

Line 2440: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2436: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2437: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2438: l_dyn_sql := l_dyn_sql || '( ';
2439: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2440: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2441: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2442: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2443: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2444: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 2458: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2454: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2455: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2456: l_dyn_sql := l_dyn_sql || '( ';
2457: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2458: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2459: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2460: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2461: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2462: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2507: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2503: ----------------------------------------------------------------------------------
2504: l_dyn_sql := l_dyn_sql || ' SELECT EBI.'||l_intf_col_name_tbl(i);
2505: END IF; --end: IF (l_prod_col_name_tbl(i) = G_ITEM_TEMPLATE_NAME)
2506:
2507: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2508:
2509: IF (l_prod_col_name_tbl(i) IN ('STYLE_ITEM_FLAG', 'GDSN_OUTBOUND_ENABLED_FLAG' )) THEN
2510: l_dyn_sql := l_dyn_sql || ' , FND_LOOKUP_VALUES_VL LKUP ';
2511: END IF;

Line 2527: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2523: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2524: l_dyn_sql := l_dyn_sql || ' AND MSII.TRANSACTION_ID IN ';
2525: l_dyn_sql := l_dyn_sql || ' ( ';
2526: l_dyn_sql := l_dyn_sql || ' SELECT EBI.TRANSACTION_ID ';
2527: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2528: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2529: -- l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2530: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
2531: l_dyn_sql := l_dyn_sql || ' ) ';

Line 2556: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI, MTL_SYSTEM_ITEMS_VL MSIV ';

2552: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2553: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2554: l_dyn_sql := l_dyn_sql || '( ';
2555: l_dyn_sql := l_dyn_sql || ' SELECT NULL ';
2556: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI, MTL_SYSTEM_ITEMS_VL MSIV ';
2557: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2558: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2559: l_dyn_sql := l_dyn_sql || ' AND EBI.INSTANCE_PK1_VALUE = MSIV.INVENTORY_ITEM_ID ';
2560: l_dyn_sql := l_dyn_sql || ' AND EBI.INSTANCE_PK2_VALUE = MSIV.ORGANIZATION_ID ';

Line 2597: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_LIFECYCLES_V LC, EGO_BULKLOAD_INTF EBI ';

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 ';
2596: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(LC.PROJ_ELEMENT_ID, -1), -1,'||G_LIFECYCLE_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2597: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_LIFECYCLES_V LC, EGO_BULKLOAD_INTF EBI ';
2598: l_dyn_sql := l_dyn_sql || ' WHERE LC.NAME (+) = EBI.'||l_lifecycle_col_val;
2599: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
2600: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2601: l_dyn_sql := l_dyn_sql || ') ';

Line 2607: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2603: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2604: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2605: l_dyn_sql := l_dyn_sql || '( ';
2606: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2607: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2608: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2609: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2610: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_lifecycle_col_val||' IS NOT NULL';
2611: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_lifecycle_col_val||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 2625: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2621: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2622: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2623: l_dyn_sql := l_dyn_sql || '( ';
2624: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2625: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2626: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2627: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2628: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_lifecycle_col_val||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2629: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2650: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_PHASES_V LCP, EGO_BULKLOAD_INTF EBI ';

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 ';
2649: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(LCP.PROJ_ELEMENT_ID, -1), -1,'||G_LIFECYCLE_PHASE_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2650: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_PHASES_V LCP, EGO_BULKLOAD_INTF EBI ';
2651: l_dyn_sql := l_dyn_sql || ' WHERE LCP.PARENT_STRUCTURE_ID (+) = MSII.LIFECYCLE_ID ';
2652: l_dyn_sql := l_dyn_sql || ' AND LCP.NAME (+) = EBI.'||l_lifecycle_phase_col_val;
2653: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
2654: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';

Line 2661: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2657: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2658: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2659: l_dyn_sql := l_dyn_sql || '( ';
2660: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2661: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2662: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2663: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2664: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_lifecycle_phase_col_val||' IS NOT NULL';
2665: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_lifecycle_phase_col_val||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

Line 2679: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

2675: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2676: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2677: l_dyn_sql := l_dyn_sql || '( ';
2678: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2679: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2680: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2681: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2682: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_lifecycle_phase_col_val||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2683: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2709: FROM EGO_BULKLOAD_INTF EBI

2705: MSII.engineering_date IS NOT NULL
2706: )
2707: AND EXISTS
2708: ( SELECT 'X'
2709: FROM EGO_BULKLOAD_INTF EBI
2710: WHERE EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
2711: AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID
2712: AND EBI.PROCESS_STATUS = 1
2713: );

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 2996: FROM EGO_BULKLOAD_INTF

2992: )
2993: AND MSII.TRANSACTION_ID IN
2994: (
2995: SELECT TRANSACTION_ID
2996: FROM EGO_BULKLOAD_INTF
2997: WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
2998: );
2999:
3000: Write_Debug('MSII: Updated all the line statuses to Error for Pre-processing validation errors');

Line 3188: UPDATE EGO_BULKLOAD_INTF EBI

3184: -----------------------------------------------------------------
3185: -- Update EBI, with the process status of rows in MSII after
3186: -- the completion of IOI processing.
3187: -----------------------------------------------------------------
3188: UPDATE EGO_BULKLOAD_INTF EBI
3189: SET EBI.PROCESS_STATUS =
3190: (
3191: SELECT MSII.PROCESS_FLAG
3192: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

Line 3204: --Update all the lines in EGO_BULKLOAD_INTF as failure, for which

3200: )
3201: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
3202:
3203: ----------------------------------------------------------------------------
3204: --Update all the lines in EGO_BULKLOAD_INTF as failure, for which
3205: --The Inventory Item IDs were not available.
3206: --1. For Transaction Type : CREATE, Item ID should be populated
3207: -- at the end of Processing
3208: --2. For Transaction Type : SYNC / UPDATE, Item ID should be

Line 3212: -- UPDATE EGO_BULKLOAD_INTF EBI

3208: --2. For Transaction Type : SYNC / UPDATE, Item ID should be
3209: -- retrieved during processing.
3210: ----------------------------------------------------------------------------
3211: -- R12: this is not required
3212: -- UPDATE EGO_BULKLOAD_INTF EBI
3213: -- SET EBI.PROCESS_STATUS = G_INTF_STATUS_ERROR
3214: -- WHERE EXISTS
3215: -- (
3216: -- SELECT 'X'

Line 3225: -- Update all the lines in EGO_BULKLOAD_INTF as SUCCESS

3221: -- )
3222: -- AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
3223:
3224: ----------------------------------------------------------------------------
3225: -- Update all the lines in EGO_BULKLOAD_INTF as SUCCESS
3226: -- for these rows were used to populate Multi-Row
3227: -- Appropriate errors will be displayed by the User-Defined Attrs Import
3228: -- processing later.
3229: ----------------------------------------------------------------------------

Line 3230: UPDATE EGO_BULKLOAD_INTF EBI

3226: -- for these rows were used to populate Multi-Row
3227: -- Appropriate errors will be displayed by the User-Defined Attrs Import
3228: -- processing later.
3229: ----------------------------------------------------------------------------
3230: UPDATE EGO_BULKLOAD_INTF EBI
3231: SET EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS
3232: WHERE EBI.PROCESS_STATUS = G_INTF_STATUS_TOBE_PROCESS
3233: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
3234:

Line 3238: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)

3234:
3235: ----------------------------------------------------------------------------
3236: -- Update the Item IDs (Doesnt matter Create or Update, because
3237: -- Item IDs will be populated either way in MSII) in
3238: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)
3239: -- These Item IDs are required for Item User-Defined Attrs bulkload
3240: ----------------------------------------------------------------------------
3241: UPDATE EGO_BULKLOAD_INTF EBI
3242: SET EBI.INSTANCE_PK1_VALUE =

Line 3241: UPDATE EGO_BULKLOAD_INTF EBI

3237: -- Item IDs will be populated either way in MSII) in
3238: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)
3239: -- These Item IDs are required for Item User-Defined Attrs bulkload
3240: ----------------------------------------------------------------------------
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

Line 3403: UPDATE ego_bulkload_intf

3399: -- Bug: 3804572 - Error messages are not shown, if import format contains revision attrs.
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:

Line 3507: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF EBI ';

3503:
3504: ---------------------------------------------
3505: -- Update Instance PK2 Value with ORG ID. --
3506: ---------------------------------------------
3507: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF EBI ';
3508: l_dyn_sql := l_dyn_sql || ' SET INSTANCE_PK2_VALUE = ';
3509: l_dyn_sql := l_dyn_sql || ' ( ';
3510: l_dyn_sql := l_dyn_sql || ' SELECT ORGANIZATION_ID ';
3511: l_dyn_sql := l_dyn_sql || ' FROM MTL_PARAMETERS ';

Line 3525: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

3521: ---------------------------------------------------------------------
3522: -- Update EBI with Catalog Group Name if present in the Result Format
3523: ---------------------------------------------------------------------
3524: IF l_item_catalog_name_col IS NOT NULL THEN
3525: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
3526: l_dyn_sql := l_dyn_sql || ' SET ';
3527: l_dyn_sql := l_dyn_sql || G_ITEM_CATALOG_NAME_EBI_COL||' = '||l_item_catalog_name_col ;
3528: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3529: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';

Line 3541: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF ';

3537: -- Fetch Organization ID, Item Number in Temp PLSQL tables.
3538: --------------------------------------------------------------
3539: l_dyn_sql := ' SELECT INSTANCE_PK2_VALUE, '||l_item_number_col || ', ';
3540: l_dyn_sql := l_dyn_sql || ' TRANSACTION_ID ';
3541: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF ';
3542: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3543: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
3544: Write_Debug(l_dyn_sql);
3545:

Line 3588: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

3584: l_inventory_item_id := FND_FLEX_KEYVAL.combination_id;
3585:
3586: Write_Debug('Inv Item ID : '||To_char(l_inventory_item_id));
3587:
3588: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
3589: l_dyn_sql := l_dyn_sql || ' SET INSTANCE_PK1_VALUE = '||l_inventory_item_id;
3590: l_dyn_sql := l_dyn_sql || ' WHERE INSTANCE_PK2_VALUE = '||l_org_id_table(i);
3591: l_dyn_sql := l_dyn_sql || ' AND '|| l_item_number_col|| ' = :ITEM_NUMBER';
3592: l_dyn_sql := l_dyn_sql || ' AND RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

Line 3607: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF EBI';

3603: -- Bug #5179741(RSOUNDAR)
3604: -----------------------------------------------------------------
3605: IF G_CATALOG_GROUP_ID IS NULL THEN
3606: IF l_item_catalog_name_col IS NOT NULL THEN
3607: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF EBI';
3608: l_dyn_sql := l_dyn_sql || ' SET '||G_ITEM_CATALOG_EBI_COL||' = ';
3609: l_dyn_sql := l_dyn_sql || '( ';
3610: l_dyn_sql := l_dyn_sql || ' SELECT TO_CHAR(MICG.ITEM_CATALOG_GROUP_ID) ';
3611: l_dyn_sql := l_dyn_sql || ' FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG ';

Line 3625: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

3621: NULL;
3622: END IF; -- l_item_catalog_name_col IS NOT NULL
3623: ELSE -- G_CATALOG_GROUP_ID IS NOT NULL
3624:
3625: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
3626: -------------------------------------------------------------
3627: --Storing Catalog Group ID and other imp data in buffer cols
3628: -------------------------------------------------------------
3629: l_dyn_sql := l_dyn_sql || ' SET '||G_ITEM_CATALOG_EBI_COL||' = '||G_CATALOG_GROUP_ID;

Line 3726: l_dyn_sql := l_dyn_sql || 'FROM EGO_BULKLOAD_INTF EBI ';

3722: l_dyn_sql := l_dyn_sql || ' EBI.'||l_org_code_col ||' , ';
3723: l_dyn_sql := l_dyn_sql || G_PROCESS_STATUS ||' , ';
3724: l_dyn_sql := l_dyn_sql || ' TO_NUMBER(EBI.C_FIX_COLUMN11) , ';
3725: l_dyn_sql := l_dyn_sql || ' EBI.C_FIX_COLUMN12 ';
3726: l_dyn_sql := l_dyn_sql || 'FROM EGO_BULKLOAD_INTF EBI ';
3727: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3728: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
3729:
3730: Write_Debug(l_dyn_sql);

Line 3737: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

3733:
3734: ----------------------------------------------------------------------------------
3735: --Save Item Num, Org Code in designated columns in EBI for Error Reporting later
3736: ----------------------------------------------------------------------------------
3737: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
3738: l_dyn_sql := l_dyn_sql || ' SET ';
3739: -----------------------------------------------------------------------------------------
3740: -- In case of ITEM_LEVEL the following is already populated in load_item_interface_lines
3741: -- procedure.

Line 3776: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';

3772: l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';
3773: l_dyn_sql := l_dyn_sql || ' SET MIRI.'||G_REV_REASON_DB_COL||' = ';
3774: l_dyn_sql := l_dyn_sql || '( ';
3775: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';
3776: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
3777: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE = ''EGO_ITEM_REVISION_REASON'' ';
3778: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE = Userenv(''LANG'') ';
3779: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING = EBI.'||l_intf_col_name_tbl(i);
3780: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

Line 3790: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';

3786: l_dyn_sql := l_dyn_sql || ') ';
3787: l_dyn_sql := l_dyn_sql || ' WHERE EXISTS ';
3788: l_dyn_sql := l_dyn_sql || '( ';
3789: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
3790: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
3791: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE = ''EGO_ITEM_REVISION_REASON'' ';
3792: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE = Userenv(''LANG'') ';
3793: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING = EBI.'||l_intf_col_name_tbl(i);
3794: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';

Line 3834: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

3830: l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';
3831: l_dyn_sql := l_dyn_sql || ' SET MIRI.'||l_prod_col_name_tbl(i) ||' = ';
3832: l_dyn_sql := l_dyn_sql || '( ';
3833: l_dyn_sql := l_dyn_sql || ' SELECT EBI.'||l_intf_col_name_tbl(i);
3834: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
3835: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3836: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';
3837: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
3838: l_dyn_sql := l_dyn_sql || ') ';

Line 3842: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';

3838: l_dyn_sql := l_dyn_sql || ') ';
3839: l_dyn_sql := l_dyn_sql || ' WHERE EXISTS ';
3840: l_dyn_sql := l_dyn_sql || '( ';
3841: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
3842: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
3843: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
3844: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';
3845: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
3846: l_dyn_sql := l_dyn_sql || ') ';

Line 3885: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_LIFECYCLES_PHASES_V LC, EGO_BULKLOAD_INTF EBI ';

3881: l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';
3882: l_dyn_sql := l_dyn_sql || ' SET MIRI.LIFECYCLE_ID = ';
3883: l_dyn_sql := l_dyn_sql || '( ';
3884: l_dyn_sql := l_dyn_sql || ' SELECT LC.PROJ_ELEMENT_ID ';
3885: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_LIFECYCLES_PHASES_V LC, EGO_BULKLOAD_INTF EBI ';
3886: l_dyn_sql := l_dyn_sql || ' WHERE LC.OBJECT_TYPE = ''PA_STRUCTURES'' ';
3887: l_dyn_sql := l_dyn_sql || ' AND LC.NAME = EBI.'||l_lifecycle_col_val;
3888: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3889: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';

Line 3895: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_LIFECYCLES_PHASES_V LC, EGO_BULKLOAD_INTF EBI ';

3891: l_dyn_sql := l_dyn_sql || ') ';
3892: l_dyn_sql := l_dyn_sql || ' WHERE EXISTS ';
3893: l_dyn_sql := l_dyn_sql || '( ';
3894: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
3895: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_LIFECYCLES_PHASES_V LC, EGO_BULKLOAD_INTF EBI ';
3896: l_dyn_sql := l_dyn_sql || ' WHERE LC.OBJECT_TYPE = ''PA_STRUCTURES'' ';
3897: l_dyn_sql := l_dyn_sql || ' AND LC.NAME = EBI.'||l_lifecycle_col_val;
3898: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
3899: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';

Line 3925: l_dyn_sql := l_dyn_sql || ' FROM MTL_SYSTEM_ITEMS_B MSI, EGO_BULKLOAD_INTF EBI ';

3921: l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';
3922: l_dyn_sql := l_dyn_sql || ' SET MIRI.LIFECYCLE_ID = ';
3923: l_dyn_sql := l_dyn_sql || '( ';
3924: l_dyn_sql := l_dyn_sql || ' SELECT MSI.LIFECYCLE_ID ';
3925: l_dyn_sql := l_dyn_sql || ' FROM MTL_SYSTEM_ITEMS_B MSI, EGO_BULKLOAD_INTF EBI ';
3926: l_dyn_sql := l_dyn_sql || ' WHERE MSI.INVENTORY_ITEM_ID = EBI.INSTANCE_PK1_VALUE ';
3927: l_dyn_sql := l_dyn_sql || ' AND MSI.ORGANIZATION_ID = EBI.INSTANCE_PK2_VALUE ';
3928: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3929: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';

Line 3935: l_dyn_sql := l_dyn_sql || ' FROM MTL_SYSTEM_ITEMS_B MSI, EGO_BULKLOAD_INTF EBI ';

3931: l_dyn_sql := l_dyn_sql || ') ';
3932: l_dyn_sql := l_dyn_sql || ' WHERE EXISTS ';
3933: l_dyn_sql := l_dyn_sql || '( ';
3934: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
3935: l_dyn_sql := l_dyn_sql || ' FROM MTL_SYSTEM_ITEMS_B MSI, EGO_BULKLOAD_INTF EBI ';
3936: l_dyn_sql := l_dyn_sql || ' WHERE MSI.INVENTORY_ITEM_ID = EBI.INSTANCE_PK1_VALUE ';
3937: l_dyn_sql := l_dyn_sql || ' AND MSI.ORGANIZATION_ID = EBI.INSTANCE_PK2_VALUE ';
3938: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
3939: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';

Line 3959: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_PHASES_V LCP, EGO_BULKLOAD_INTF EBI ';

3955: l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';
3956: l_dyn_sql := l_dyn_sql || ' SET MIRI.CURRENT_PHASE_ID = ';
3957: l_dyn_sql := l_dyn_sql || '( ';
3958: l_dyn_sql := l_dyn_sql || ' SELECT LCP.PROJ_ELEMENT_ID ';
3959: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_PHASES_V LCP, EGO_BULKLOAD_INTF EBI ';
3960: l_dyn_sql := l_dyn_sql || ' WHERE LCP.PARENT_STRUCTURE_ID = MIRI.LIFECYCLE_ID ';
3961: l_dyn_sql := l_dyn_sql || ' AND LCP.NAME = EBI.'||l_lifecycle_phase_col_val;
3962: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3963: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';

Line 3969: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_PHASES_V LCP, EGO_BULKLOAD_INTF EBI ';

3965: l_dyn_sql := l_dyn_sql || ') ';
3966: l_dyn_sql := l_dyn_sql || ' WHERE EXISTS ';
3967: l_dyn_sql := l_dyn_sql || '( ';
3968: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
3969: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_PHASES_V LCP, EGO_BULKLOAD_INTF EBI ';
3970: l_dyn_sql := l_dyn_sql || ' WHERE LCP.PARENT_STRUCTURE_ID = MIRI.LIFECYCLE_ID ';
3971: l_dyn_sql := l_dyn_sql || ' AND LCP.NAME = EBI.'||l_lifecycle_phase_col_val;
3972: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
3973: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';

Line 4004: FROM EGO_BULKLOAD_INTF EBI

4000: AND revision_label IS NULL
4001: AND revision_reason IS NULL
4002: AND current_phase_id IS NULL
4003: AND EXISTS (SELECT 'X'
4004: FROM EGO_BULKLOAD_INTF EBI
4005: WHERE EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
4006: AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID
4007: AND EBI.PROCESS_STATUS = 1
4008: ); */

Line 4019: FROM EGO_BULKLOAD_INTF EBI

4015: implementation_date = DECODE(implementation_date,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,implementation_date),
4016: effectivity_date = DECODE(effectivity_date,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,effectivity_date)
4017: WHERE MIRI.TRANSACTION_ID IN
4018: ( SELECT EBI.TRANSACTION_ID
4019: FROM EGO_BULKLOAD_INTF EBI
4020: WHERE EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
4021: AND EBI.PROCESS_STATUS = 1
4022: )
4023: AND (MIRI.ecn_initiation_date IS NOT NULL

Line 4185: UPDATE EGO_BULKLOAD_INTF EBI

4181: -----------------------------------------------------------------
4182: -- Update EBI, with the process status of rows in MIRI after
4183: -- the completion of IOI Revision processing.
4184: -----------------------------------------------------------------
4185: UPDATE EGO_BULKLOAD_INTF EBI
4186: SET EBI.PROCESS_STATUS =
4187: (
4188: SELECT MIRI.PROCESS_FLAG
4189: FROM MTL_ITEM_REVISIONS_INTERFACE MIRI

Line 4201: -- Update all the lines in EGO_BULKLOAD_INTF as failure, for which

4197: )
4198: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
4199:
4200: ----------------------------------------------------------------------------
4201: -- Update all the lines in EGO_BULKLOAD_INTF as failure, for which
4202: -- The Revision IDS were not available.
4203: -- 1. For Transaction Type : CREATE, Revision ID should be populated
4204: -- at the end of Processing
4205: -- 2. For Transaction Type : SYNC / UPDATE, Revision ID should be

Line 4208: UPDATE EGO_BULKLOAD_INTF EBI

4204: -- at the end of Processing
4205: -- 2. For Transaction Type : SYNC / UPDATE, Revision ID should be
4206: -- retrieved during processing.
4207: ----------------------------------------------------------------------------
4208: UPDATE EGO_BULKLOAD_INTF EBI
4209: SET EBI.PROCESS_STATUS = G_INTF_STATUS_ERROR
4210: WHERE EXISTS
4211: (
4212: SELECT 'X'

Line 4221: -- Update all the lines in EGO_BULKLOAD_INTF as SUCCESS

4217: )
4218: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
4219:
4220: ----------------------------------------------------------------------------
4221: -- Update all the lines in EGO_BULKLOAD_INTF as SUCCESS
4222: -- for these rows were used to populate Multi-Row
4223: -- Appropriate errors will be displayed by the User-Defined Attrs Bulkldr.
4224: ----------------------------------------------------------------------------
4225: UPDATE EGO_BULKLOAD_INTF EBI

Line 4225: UPDATE EGO_BULKLOAD_INTF EBI

4221: -- Update all the lines in EGO_BULKLOAD_INTF as SUCCESS
4222: -- for these rows were used to populate Multi-Row
4223: -- Appropriate errors will be displayed by the User-Defined Attrs Bulkldr.
4224: ----------------------------------------------------------------------------
4225: UPDATE EGO_BULKLOAD_INTF EBI
4226: SET EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS
4227: WHERE EBI.PROCESS_STATUS = G_INTF_STATUS_TOBE_PROCESS
4228: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
4229:

Line 4233: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)

4229:
4230: ----------------------------------------------------------------------------
4231: -- Update the Item IDs (Doesnt matter Create or Update, because
4232: -- Item IDs will be populated either way in MSII) in
4233: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)
4234: -- These Item IDs are required for Item User-Defined Attrs bulkload
4235: ----------------------------------------------------------------------------
4236: UPDATE EGO_BULKLOAD_INTF EBI
4237: SET EBI.INSTANCE_PK1_VALUE =

Line 4236: UPDATE EGO_BULKLOAD_INTF EBI

4232: -- Item IDs will be populated either way in MSII) in
4233: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)
4234: -- These Item IDs are required for Item User-Defined Attrs bulkload
4235: ----------------------------------------------------------------------------
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

Line 4255: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)

4251:
4252: ----------------------------------------------------------------------------
4253: -- Update the Item Revision IDs (Doesnt matter Create or Update, because
4254: -- Item Revision IDs will be populated either way in MIRI) in
4255: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)
4256: -- These Revision IDs are required for Revision User-Defined Attrs bulkload
4257: ----------------------------------------------------------------------------
4258: UPDATE EGO_BULKLOAD_INTF EBI
4259: SET EBI.INSTANCE_PK3_VALUE =

Line 4258: UPDATE EGO_BULKLOAD_INTF EBI

4254: -- Item Revision IDs will be populated either way in MIRI) in
4255: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)
4256: -- These Revision IDs are required for Revision User-Defined Attrs bulkload
4257: ----------------------------------------------------------------------------
4258: UPDATE EGO_BULKLOAD_INTF EBI
4259: SET EBI.INSTANCE_PK3_VALUE =
4260: (
4261: SELECT MIRI.REVISION_ID
4262: FROM MTL_ITEM_REVISIONS_INTERFACE MIRI

Line 4667: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' FROM EGO_BULKLOAD_INTF ' ;

4663: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || l_intf_col_name_table(i) ;
4664: END IF;
4665: END LOOP LIORUI_update_item_ids_loop; --end: FOR i IN 1..l_attr_id_table.COUNT LOOP
4666:
4667: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' FROM EGO_BULKLOAD_INTF ' ;
4668: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
4669: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' AND PROCESS_STATUS = :PROCESS_STATUS ';
4670: Write_Debug(l_api_name || l_dyn_attr_id_val_sql);
4671:

Line 5488: -- Fetch the Oper Attrs values from EGO_BULKLOAD_INTF. --

5484: Write_Debug('load_item_oper_attr_values: Retrieved rows => '||To_char(l_count));
5485: DBMS_SQL.CLOSE_CURSOR(l_cursor_oper_attr);
5486:
5487: ----------------------------------------------------------------------------
5488: -- Fetch the Oper Attrs values from EGO_BULKLOAD_INTF. --
5489: ----------------------------------------------------------------------------
5490:
5491: l_cursor_oper_attr := DBMS_SQL.OPEN_CURSOR;
5492: l_dyn_sql := ' SELECT ';

Line 5504: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF ';

5500: ELSE
5501: l_dyn_sql := l_dyn_sql || l_intf_col_table(i) || ' ';
5502: END IF;
5503: END LOOP; --FOR (i in 1..l_attr_id_table.COUNT) LOOP
5504: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF ';
5505: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID ';
5506: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
5507:
5508: Write_Debug(l_dyn_sql);

Line 5731: l_dyn_sql_ebi := ' UPDATE ego_bulkload_intf ' ||

5727: FND_MESSAGE.set_name('EGO', 'EGO_IPI_INVALID_VALUE');
5728: FND_MESSAGE.set_token('NAME', l_attr_disp_name_table(i));
5729: FND_MESSAGE.set_token('VALUE',NVL(l_attr_disp_val_table(i),l_attr_int_val_table(i)));
5730: l_ebi_err_msg := fnd_message.get();
5731: l_dyn_sql_ebi := ' UPDATE ego_bulkload_intf ' ||
5732: ' SET '|| G_VAL_SET_CONV_ERR_COL ||' = '''||l_ebi_err_msg||''''||
5733: ' WHERE TRANSACTION_ID = ' || l_transaction_id;
5734: EXECUTE IMMEDIATE l_dyn_sql_ebi;
5735: EXIT;

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 5843: FROM EGO_BULKLOAD_INTF

5839: )
5840: AND TRANSACTION_ID IN
5841: (
5842: SELECT TRANSACTION_ID
5843: FROM EGO_BULKLOAD_INTF
5844: WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
5845: );
5846:
5847: EXCEPTION

Line 5892: FROM EGO_BULKLOAD_INTF

5888: ------------------------------------------------------
5889:
5890: CURSOR c_err_logfile (c_resultfmt_usage_id IN NUMBER) IS
5891: SELECT C_INTF_ATTR239
5892: FROM EGO_BULKLOAD_INTF
5893: WHERE RESULTFMT_USAGE_ID = c_resultfmt_usage_id
5894: AND C_INTF_ATTR239 IS NOT NULL;
5895:
5896: -- Long Dynamic SQL String

Line 5923: l_log_dir := SUBSTR(l_err_logfile_fullpath, 0, INSTR(l_err_logfile_fullpath, 'EGO_BULKLOAD_INTF') - 1);

5919: -- Debug file, that has been opened before.
5920: ------------------------------------------------------------
5921: IF (l_is_debug) THEN
5922:
5923: l_log_dir := SUBSTR(l_err_logfile_fullpath, 0, INSTR(l_err_logfile_fullpath, 'EGO_BULKLOAD_INTF') - 1);
5924: l_log_file := SUBSTR(l_err_logfile_fullpath, INSTR(l_err_logfile_fullpath, 'EGO_BULKLOAD_INTF'));
5925:
5926: -----------------------------------------------------------------------
5927: -- To open the Debug Session to write the Debug Log. --

Line 5924: l_log_file := SUBSTR(l_err_logfile_fullpath, INSTR(l_err_logfile_fullpath, 'EGO_BULKLOAD_INTF'));

5920: ------------------------------------------------------------
5921: IF (l_is_debug) THEN
5922:
5923: l_log_dir := SUBSTR(l_err_logfile_fullpath, 0, INSTR(l_err_logfile_fullpath, 'EGO_BULKLOAD_INTF') - 1);
5924: l_log_file := SUBSTR(l_err_logfile_fullpath, INSTR(l_err_logfile_fullpath, 'EGO_BULKLOAD_INTF'));
5925:
5926: -----------------------------------------------------------------------
5927: -- To open the Debug Session to write the Debug Log. --
5928: -- This sets Debug value so that Error_Handler.Get_Debug returns 'Y' --

Line 5953: ' FROM EGO_BULKLOAD_INTF EBI ' ||

5949: ' ) ' ||
5950: ' WHERE EXISTS ' ||
5951: ' ( ' ||
5952: ' SELECT ''X'' ' ||
5953: ' FROM EGO_BULKLOAD_INTF EBI ' ||
5954: ' WHERE FND_NUMBER.CANONICAL_TO_NUMBER(EBI.INSTANCE_PK1_VALUE) = MSIB.INVENTORY_ITEM_ID ' ||
5955: ' AND FND_NUMBER.CANONICAL_TO_NUMBER(EBI.INSTANCE_PK2_VALUE) = MSIB.ORGANIZATION_ID ' ||
5956: ' AND EBI.PROCESS_STATUS = 7 ' || -- Successful Rows Only
5957: ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_2 ' ||

Line 6383: FROM EGO_BULKLOAD_INTF EBI

6379: AND revision_label IS NULL
6380: AND revision_reason IS NULL
6381: AND current_phase_id IS NULL
6382: AND EXISTS (SELECT 'X'
6383: FROM EGO_BULKLOAD_INTF EBI
6384: WHERE EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
6385: AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID
6386: AND EBI.PROCESS_STATUS = 1
6387: )

Line 6413: l_dyn_sql := l_dyn_sql || 'EGO_BULKLOAD_INTF EBI '||G_NEWLINE;

6409: l_dyn_sql := l_dyn_sql || 'EBI.C_INTF_ATTR241 ORGANIZATION_CODE, '||G_NEWLINE;
6410: l_dyn_sql := l_dyn_sql || 'EBI.C_INTF_ATTR242 REVISION_CODE, '||G_NEWLINE;
6411: l_dyn_sql := l_dyn_sql || 'MIERR.ERROR_MESSAGE '||G_NEWLINE;
6412: l_dyn_sql := l_dyn_sql || 'FROM MTL_INTERFACE_ERRORS MIERR, '||G_NEWLINE;
6413: l_dyn_sql := l_dyn_sql || 'EGO_BULKLOAD_INTF EBI '||G_NEWLINE;
6414: l_dyn_sql := l_dyn_sql || 'WHERE MIERR.TRANSACTION_ID = EBI.TRANSACTION_ID '||G_NEWLINE;
6415: l_dyn_sql := l_dyn_sql || 'AND MIERR.request_id = '||G_REQUEST_ID||G_NEWLINE;
6416: Developer_Debug('In Case Error Reporting page has problems, Execute the following SQL to fetch the Concurrent Program CUMULATIVE Errors: ');
6417: Developer_Debug(l_dyn_sql);

Line 6476: Developer_Debug('Setting all the Unprocessed rows (PROCESS_STATUS = 1) in EGO_BULKLOAD_INTF to PROCESS_STATUS = 3.');

6472: Developer_Debug('Returning x_retcode : '|| x_retcode);
6473: l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', --Status
6474: to_char(SQLCODE) --Message
6475: );
6476: Developer_Debug('Setting all the *Unprocessed* rows (PROCESS_STATUS = 1) in EGO_BULKLOAD_INTF to PROCESS_STATUS = 3.');
6477:
6478: -----------------------------------------------------------------------------------------------
6479: -- Update all the lines in EGO_BULKLOAD_INTF as ERROR.
6480: -- This will ensure that the next submission of the Same excel doesnt throw

Line 6479: -- Update all the lines in EGO_BULKLOAD_INTF as ERROR.

6475: );
6476: Developer_Debug('Setting all the *Unprocessed* rows (PROCESS_STATUS = 1) in EGO_BULKLOAD_INTF to PROCESS_STATUS = 3.');
6477:
6478: -----------------------------------------------------------------------------------------------
6479: -- Update all the lines in EGO_BULKLOAD_INTF as ERROR.
6480: -- This will ensure that the next submission of the Same excel doesnt throw
6481: -- IOI error:
6482: -- Item already exists in the organization V1. Please use a different item name/number.
6483: -----------------------------------------------------------------------------------------------

Line 6484: UPDATE EGO_BULKLOAD_INTF EBI

6480: -- This will ensure that the next submission of the Same excel doesnt throw
6481: -- IOI error:
6482: -- Item already exists in the organization V1. Please use a different item name/number.
6483: -----------------------------------------------------------------------------------------------
6484: UPDATE EGO_BULKLOAD_INTF EBI
6485: SET EBI.PROCESS_STATUS = G_INTF_STATUS_ERROR
6486: WHERE EBI.PROCESS_STATUS = G_INTF_STATUS_TOBE_PROCESS
6487: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
6488:

Line 6547: FROM EGO_BULKLOAD_INTF

6543: )
6544: IS
6545: CURSOR c_errLogFile ( c_resultfmt_usage_id IN NUMBER) IS
6546: SELECT C_INTF_ATTR239
6547: FROM EGO_BULKLOAD_INTF
6548: WHERE C_INTF_ATTR239 IS NOT NULL
6549: AND RESULTFMT_USAGE_ID = c_resultfmt_usage_id
6550: AND ROWNUM < 2;
6551:

Line 6554: FROM EGO_BULKLOAD_INTF

6550: AND ROWNUM < 2;
6551:
6552: CURSOR c_itemId_OrgId (c_resultfmt_usage_id IN NUMBER) IS
6553: SELECT INSTANCE_PK1_VALUE , INSTANCE_PK2_VALUE
6554: FROM EGO_BULKLOAD_INTF
6555: WHERE RESULTFMT_USAGE_ID = c_resultfmt_usage_id
6556: AND PROCESS_STATUS = 7;
6557:
6558: CURSOR c_gtid (c_inventory_itemId IN NUMBER , c_organization_Id IN NUMBER) IS

Line 6676: l_err_log_file_dir := SUBSTR (l_err_log_file , 0 , INSTR (l_err_log_file , 'EGO_BULKLOAD_INTF' ) - 1 );

6672: Write_Debug('Clear_Gtin_Attrs : ');
6673: Write_Debug('-----------------------------------------------------------------');
6674:
6675: IF (l_is_Debug ) THEN
6676: l_err_log_file_dir := SUBSTR (l_err_log_file , 0 , INSTR (l_err_log_file , 'EGO_BULKLOAD_INTF' ) - 1 );
6677: l_err_log_file_name := SUBSTR (l_err_log_file , INSTR ( l_err_log_file , 'EGO_BULKLOAD_INTF' )-1 );
6678: -----------------------------------------------------------------------
6679: -- To open the Debug Session to write the Debug Log. --
6680: -- This sets Debug value so that Error_Handler.Get_Debug returns 'Y' --

Line 6677: l_err_log_file_name := SUBSTR (l_err_log_file , INSTR ( l_err_log_file , 'EGO_BULKLOAD_INTF' )-1 );

6673: Write_Debug('-----------------------------------------------------------------');
6674:
6675: IF (l_is_Debug ) THEN
6676: l_err_log_file_dir := SUBSTR (l_err_log_file , 0 , INSTR (l_err_log_file , 'EGO_BULKLOAD_INTF' ) - 1 );
6677: l_err_log_file_name := SUBSTR (l_err_log_file , INSTR ( l_err_log_file , 'EGO_BULKLOAD_INTF' )-1 );
6678: -----------------------------------------------------------------------
6679: -- To open the Debug Session to write the Debug Log. --
6680: -- This sets Debug value so that Error_Handler.Get_Debug returns 'Y' --
6681: -----------------------------------------------------------------------

Line 7529: l_dyn_sql := l_dyn_sql || 'FROM EGO_BULKLOAD_INTF EBI ';

7525: l_dyn_sql := l_dyn_sql || ' NULL , ';
7526: l_dyn_sql := l_dyn_sql || ' TO_NUMBER(EBI.C_FIX_COLUMN11) , ';
7527: l_dyn_sql := l_dyn_sql || ' EBI.C_FIX_COLUMN12 , ';
7528: l_dyn_sql := l_dyn_sql || ' NULL ';
7529: l_dyn_sql := l_dyn_sql || 'FROM EGO_BULKLOAD_INTF EBI ';
7530: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
7531: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
7532: IF(l_supplier_name_col IS NOT NULL AND l_supplier_number_col IS NOT NULL)THEN
7533: l_dyn_sql := l_dyn_sql || ' AND ( EBI.'||l_supplier_name_col||' IS NOT NULL ';

Line 7663: l_dyn_sql := l_dyn_sql || 'FROM EGO_BULKLOAD_INTF EBI ';

7659: l_dyn_sql := l_dyn_sql || ' NULL BUNDLE_ID , ';
7660: l_dyn_sql := l_dyn_sql || ' TO_NUMBER(EBI.C_FIX_COLUMN11) , ';
7661: l_dyn_sql := l_dyn_sql || ' EBI.C_FIX_COLUMN12 , ';
7662: l_dyn_sql := l_dyn_sql || ' NULL ASSOCIATION_ID ';
7663: l_dyn_sql := l_dyn_sql || 'FROM EGO_BULKLOAD_INTF EBI ';
7664: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
7665: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
7666: IF(l_supplier_name_col IS NOT NULL AND l_supplier_number_col IS NOT NULL)THEN
7667: l_dyn_sql := l_dyn_sql || ' AND ( EBI.'||l_supplier_name_col||' IS NOT NULL ';

Line 7725: l_dyn_sql := l_dyn_sql || 'FROM EGO_BULKLOAD_INTF EBI ';

7721: l_dyn_sql := l_dyn_sql || ' NULL BUNDLE_ID , ';
7722: l_dyn_sql := l_dyn_sql || ' TO_NUMBER(EBI.C_FIX_COLUMN11) , ';
7723: l_dyn_sql := l_dyn_sql || ' EBI.C_FIX_COLUMN12 , ';
7724: l_dyn_sql := l_dyn_sql || ' NULL ASSOCIATION_ID ';
7725: l_dyn_sql := l_dyn_sql || 'FROM EGO_BULKLOAD_INTF EBI ';
7726: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
7727: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
7728: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_supplier_name_col||' IS NOT NULL ';
7729: l_dyn_sql := l_dyn_sql || ' AND ''Y'' = '''||l_has_sup_sit_org_col||''' ';