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 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 700: || ' FROM EGO_BULKLOAD_INTF '

696: p_item_num_colname IN VARCHAR2) IS
697:
698: l_item_num_sql VARCHAR2(10000) :=
699: ' SELECT '|| p_item_num_colname
700: || ' FROM EGO_BULKLOAD_INTF '
701: || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
702: --|| ' AND PROCESS_STATUS = 1';
703:
704: -----------------------------------------------------------------------

Line 851: || ' FROM EGO_BULKLOAD_INTF '

847: --------------------------------------------------------------------
848: l_org_id_sql VARCHAR2(10000) :=
849: ' SELECT '
850: || ' INSTANCE_PK2_VALUE '
851: || ' FROM EGO_BULKLOAD_INTF '
852: || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID'
853: || ' AND '|| p_item_num_colname ||' IS NULL '
854: || ' AND PROCESS_STATUS = 1';
855:

Line 988: DELETE EGO_BULKLOAD_INTF

984:
985: BEGIN
986:
987: --Delete all the earlier loads from the same spreadsheet.
988: DELETE EGO_BULKLOAD_INTF
989: WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
990: AND PROCESS_STATUS <> 1;
991:
992:

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

994: ---------------------------------------------------------------
995: -- Introduced for 11.5.10, so that Java Conc Program can --
996: -- continue writing to the same Error Log File. --
997: ---------------------------------------------------------------
998: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF EBI';
999: l_dyn_sql := l_dyn_sql || ' SET '||G_ERR_LOGFILE_COL ||' = ''' || G_ERRFILE_PATH_AND_NAME ||'''';
1000: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1001: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
1002: --------------------------------------------------------------

Line 1254: UPDATE ego_bulkload_intf

1250: Write_Debug('About to populate the EBI with Trans IDs');
1251: --------------------------------------------------------------------
1252: --Populate the Transaction IDs for current Result fmt usage ID
1253: --------------------------------------------------------------------
1254: UPDATE ego_bulkload_intf
1255: --The Transaction ID sequence that is used in INVPOPIF package to
1256: --auto-populate Transaction ID in MSII.
1257: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
1258: WHERE resultfmt_usage_id = p_resultfmt_usage_id;

Line 1401: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

1397:
1398: -----------------------------------------------------------------------
1399: -- Save all Value-to-ID conversion columns in designated places in EBI.
1400: -----------------------------------------------------------------------
1401: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
1402: l_dyn_sql := l_dyn_sql || ' SET ';
1403: IF l_item_catalog_name_col IS NOT NULL THEN
1404: l_dyn_sql := l_dyn_sql || G_ITEM_CATALOG_NAME_EBI_COL||' = '||l_item_catalog_name_col|| ' ,';
1405: l_value_to_id_col_exists := TRUE;

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

1466: Write_Debug('Updating EBI with Org IDs');
1467: -----------------------------------------------------
1468: -- Update Instance PK2 Value with ORG ID.
1469: -----------------------------------------------------
1470: l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';
1471: l_dyn_sql := l_dyn_sql || ' SET INSTANCE_PK2_VALUE = ';
1472: l_dyn_sql := l_dyn_sql || ' ( ';
1473: l_dyn_sql := l_dyn_sql || ' SELECT ORGANIZATION_ID ';
1474: l_dyn_sql := l_dyn_sql || ' FROM MTL_PARAMETERS ';

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

1517: -- Upcasing the Transaction Type, for fixing Trans Type related bugs
1518: ----------------------------------------------------------------------
1519: l_dyn_sql := l_dyn_sql || ' UPPER(TRANSACTION_TYPE) , ';
1520: l_dyn_sql := l_dyn_sql || ' TRANSACTION_ID ';
1521: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF ';
1522: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1523: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1';
1524: -- l_dyn_sql := l_dyn_sql || ' AND '|| l_item_number_col ||' IS NOT NULL';
1525:

Line 1599: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

1595: l_executed_item(l_executed_item.count+1) := l_inventory_item_id||'|'||l_org_id_table(i)||'|'||l_item_number_table(i)||'|' ||l_trans_type_table(i);
1596:
1597: Write_Debug('Inv Item ID : '||To_char(l_inventory_item_id));
1598:
1599: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
1600: l_dyn_sql := l_dyn_sql || ' SET INSTANCE_PK1_VALUE = '||l_inventory_item_id;
1601: l_dyn_sql := l_dyn_sql || ' WHERE INSTANCE_PK2_VALUE = '||l_org_id_table(i);
1602: l_dyn_sql := l_dyn_sql || ' AND '|| l_item_number_col|| ' = :ITEM_NUMBER';
1603: l_dyn_sql := l_dyn_sql || ' AND RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

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

1631: END IF;
1632:
1633: IF l_catalog_group_id IS NULL THEN
1634: IF l_item_catalog_name_col IS NOT NULL THEN
1635: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF EBI';
1636: l_dyn_sql := l_dyn_sql || ' SET '||G_ITEM_CATALOG_EBI_COL||' = ';
1637: l_dyn_sql := l_dyn_sql || '( ';
1638: l_dyn_sql := l_dyn_sql || ' SELECT TO_CHAR(MICG.ITEM_CATALOG_GROUP_ID) ';
1639: l_dyn_sql := l_dyn_sql || ' FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG ';

Line 1655: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

1651: ELSE -- G_CATALOG_GROUP_ID IS NOT NULL
1652: /* Since Item Number can be NULL, removing its reference from the WHERE Clause.
1653:
1654: l_dyn_sql := '';
1655: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
1656: -------------------------------------------------------------
1657: --Storing Catalog Group ID and other imp data in buffer cols
1658: -------------------------------------------------------------
1659: l_dyn_sql := l_dyn_sql || ' SET '||G_ITEM_CATALOG_EBI_COL||' = '||G_CATALOG_GROUP_ID;

Line 1667: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

1663: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
1664:
1665: EXECUTE IMMEDIATE l_dyn_sql USING l_item_number_table(i), p_resultfmt_usage_id;
1666: */
1667: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
1668: -------------------------------------------------------------
1669: --Storing Catalog Group ID and other imp data in buffer cols
1670: -------------------------------------------------------------
1671: l_dyn_sql := l_dyn_sql || ' SET '||G_ITEM_CATALOG_EBI_COL||' = '||l_catalog_group_id;

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

1725: DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
1726:
1727: Write_Debug('Done with Item IDs population.');
1728:
1729: Write_Debug('Populating item number into ego_bulkload_intf for function generated items');
1730: IF G_PDH_BATCH THEN
1731: /***
1732: l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||
1733: ' SET '||l_item_number_col||' = ''$$FG-''||TO_CHAR(transaction_id)'||

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

1728:
1729: Write_Debug('Populating item number into ego_bulkload_intf for function generated items');
1730: IF G_PDH_BATCH THEN
1731: /***
1732: l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||
1733: ' SET '||l_item_number_col||' = ''$$FG-''||TO_CHAR(transaction_id)'||
1734: ' ,c_fix_column12 = TO_CHAR(transaction_id)'||
1735: ' WHERE resultfmt_usage_id = :RESULTFMT_USAGE_ID'||
1736: ' AND '||G_ITEM_CATALOG_EBI_COL||' IS NOT NULL'||

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

1751: --
1752: -- R12C changes Tag_Function_Gen_Item_Nums with item_num as $$FG-transaction_id
1753: -- populate item number col if null but always set the source system reference
1754: --
1755: l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||
1756: ' SET '||l_item_number_col||' = NVL( '||l_item_number_col||', ''$$FG-''||TO_CHAR(transaction_id) )'||
1757: ' ,c_fix_column12 = TO_CHAR(transaction_id)'||
1758: ' WHERE resultfmt_usage_id = :RESULTFMT_USAGE_ID'||
1759: ' AND '||G_ITEM_CATALOG_EBI_COL||' IS NOT NULL'||

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

1782: --
1783: -- R12C changes Tag_Sequence_Gen_Item_Nums with item_num as $$SG-transaction_id
1784: -- populate item number col if null but always set the source system reference
1785: --
1786: l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||
1787: ' SET '||l_item_number_col||' = NVL( '||l_item_number_col||', ''$$SG-''||TO_CHAR(transaction_id) )'||
1788: ' ,c_fix_column12 = TO_CHAR(transaction_id)'||
1789: ' WHERE resultfmt_usage_id = :RESULTFMT_USAGE_ID'||
1790: ' AND '||G_ITEM_CATALOG_EBI_COL||' IS NOT NULL'||

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

1868: l_dyn_sql := l_dyn_sql || G_PROCESS_STATUS||' , ';
1869: l_dyn_sql := l_dyn_sql || ' TO_NUMBER(EBI.C_FIX_COLUMN11) , ';
1870: l_dyn_sql := l_dyn_sql || ' EBI.C_FIX_COLUMN12 , ';
1871: l_dyn_sql := l_dyn_sql || ' EBI.C_FIX_COLUMN13 ';
1872: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
1873: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1874: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1';
1875:
1876: Write_Debug(l_dyn_sql);

Line 1883: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

1879:
1880: ----------------------------------------------------------------------------------
1881: --Save Item Num, Org Code in designated columns in EBI for Error Reporting later
1882: ----------------------------------------------------------------------------------
1883: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
1884: l_dyn_sql := l_dyn_sql || ' SET '||G_ITEM_NUMBER_EBI_COL||' = '||l_item_number_col||' , ';
1885: l_dyn_sql := l_dyn_sql || G_ORG_CODE_EBI_COL||' = '||l_org_code_col ;
1886: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1887: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';

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

1913: -- l_dyn_sql := l_dyn_sql || ' SET (MSII.ITEM_CATALOG_GROUP_ID, MSII.PROCESS_FLAG)=';
1914: -- l_dyn_sql := l_dyn_sql || '( ';
1915: -- l_dyn_sql := l_dyn_sql || ' SELECT MICG.ITEM_CATALOG_GROUP_ID ';
1916: -- l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(MICG.ITEM_CATALOG_GROUP_ID, -1), -1,'||G_ITEM_CATALOG_NAME_ERR_STS||', ' || G_PROCESS_STATUS || ' ) ';
1917: -- l_dyn_sql := l_dyn_sql || ' FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG, EGO_BULKLOAD_INTF EBI ';
1918: -- l_dyn_sql := l_dyn_sql || ' WHERE MICG.CONCATENATED_SEGMENTS(+) = EBI.'||l_intf_col_name_tbl(i);
1919: -- l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1920: -- l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
1921: -- ----------------------------------------------------------------------------------

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

1925: -- l_dyn_sql := l_dyn_sql || ') ';
1926: -- l_dyn_sql := l_dyn_sql || ' WHERE EXISTS ';
1927: -- l_dyn_sql := l_dyn_sql || '( ';
1928: -- l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
1929: -- l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
1930: -- l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
1931: -- l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
1932: -- ----------------------------------------------------------------------------------
1933: -- -- Validate only for the NOT NULL values, and update MSII.

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

1955: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
1956: l_dyn_sql := l_dyn_sql || '( ';
1957: l_dyn_sql := l_dyn_sql || ' SELECT MUOM.UOM_CODE ';
1958: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(MUOM.UOM_CODE, ''NULL''),''NULL'','||G_PRIMARY_UOM_ERR_STS||', ' || G_PROCESS_STATUS || ' ) ';
1959: l_dyn_sql := l_dyn_sql || ' FROM MTL_UNITS_OF_MEASURE_TL MUOM, EGO_BULKLOAD_INTF EBI ';
1960: l_dyn_sql := l_dyn_sql || ' WHERE MUOM.UNIT_OF_MEASURE_TL (+) = EBI.'||l_intf_col_name_tbl(i);
1961: l_dyn_sql := l_dyn_sql || ' AND MUOM.LANGUAGE (+) = Userenv(''LANG'') ';
1962: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1963: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';

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

1966: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
1967: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
1968: l_dyn_sql := l_dyn_sql || '( ';
1969: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
1970: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
1971: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
1972: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
1973: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
1974: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

1985: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
1986: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
1987: l_dyn_sql := l_dyn_sql || '( ';
1988: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
1989: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
1990: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
1991: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
1992: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
1993: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

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

2027: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2028: l_dyn_sql := l_dyn_sql || '( ';
2029: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';
2030: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_USER_ITEM_TYPE_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2031: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2032: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''ITEM_TYPE'' ';
2033: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2034: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2035: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

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

2039: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2040: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2041: l_dyn_sql := l_dyn_sql || '( ';
2042: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2043: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2044: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2045: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2046: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2047: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

2057: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2058: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2059: l_dyn_sql := l_dyn_sql || '( ';
2060: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2061: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2062: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2063: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2064: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2065: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

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

2079: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2080: l_dyn_sql := l_dyn_sql || '( ';
2081: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';
2082: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_BOM_ITEM_TYPE_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2083: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2084: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''BOM_ITEM_TYPE'' ';
2085: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2086: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2087: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

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

2091: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2092: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2093: l_dyn_sql := l_dyn_sql || '( ';
2094: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2095: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2096: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2097: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2098: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2099: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

2109: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2110: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2111: l_dyn_sql := l_dyn_sql || '( ';
2112: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2113: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2114: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2115: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2116: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2117: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

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

2126: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2127: l_dyn_sql := l_dyn_sql || '( ';
2128: l_dyn_sql := l_dyn_sql || ' SELECT IT.INTERNAL_NAME ';
2129: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.INTERNAL_NAME, ''NULL''),''NULL'','||G_TRADE_ITEM_DESC_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2130: l_dyn_sql := l_dyn_sql || ' FROM EGO_VALUE_SET_VALUES_V IT, EGO_BULKLOAD_INTF EBI ';
2131: l_dyn_sql := l_dyn_sql || ' WHERE IT.VALUE_SET_NAME = ''TradeItemDescVS'' ';
2132: l_dyn_sql := l_dyn_sql || ' AND IT.DISPLAY_NAME = EBI.'||l_intf_col_name_tbl(i);
2133: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
2134: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';

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

2137: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2138: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2139: l_dyn_sql := l_dyn_sql || '( ';
2140: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2141: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2142: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2143: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2144: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2145: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

2155: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2156: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2157: l_dyn_sql := l_dyn_sql || '( ';
2158: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2159: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2160: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2161: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2162: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2163: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

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

2177: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2178: l_dyn_sql := l_dyn_sql || '( ';
2179: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';
2180: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_ENG_ITEM_FLAG_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2181: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2182: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''EGO_YES_NO'' ';
2183: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2184: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2185: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

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

2189: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2190: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2191: l_dyn_sql := l_dyn_sql || '( ';
2192: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2193: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2194: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2195: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2196: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2197: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

2207: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2208: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2209: l_dyn_sql := l_dyn_sql || '( ';
2210: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2211: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2212: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2213: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2214: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2215: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

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

2231: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2232: l_dyn_sql := l_dyn_sql || '( ';
2233: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';
2234: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_CONVERSIONS_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2235: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2236: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''MTL_CONVERSION_TYPE'' ';
2237: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2238: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2239: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

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

2243: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2244: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2245: l_dyn_sql := l_dyn_sql || '( ';
2246: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2247: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2248: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2249: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2250: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2251: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

2261: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2262: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2263: l_dyn_sql := l_dyn_sql || '( ';
2264: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2265: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2266: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2267: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2268: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2269: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

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

2283: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2284: l_dyn_sql := l_dyn_sql || '( ';
2285: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';
2286: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_SECONDARY_DEF_IND_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2287: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2288: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''INV_DEFAULTING_UOM_TYPE'' ';
2289: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2290: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2291: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

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

2295: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2296: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2297: l_dyn_sql := l_dyn_sql || '( ';
2298: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2299: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2300: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2301: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2302: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2303: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

2313: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2314: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2315: l_dyn_sql := l_dyn_sql || '( ';
2316: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2317: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2318: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2319: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2320: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2321: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

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

2335: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2336: l_dyn_sql := l_dyn_sql || '( ';
2337: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';
2338: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_ONT_PRICING_QTY_SRC_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2339: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2340: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''INV_PRICING_UOM_TYPE'' ';
2341: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2342: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2343: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

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

2347: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2348: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2349: l_dyn_sql := l_dyn_sql || '( ';
2350: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2351: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2352: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2353: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2354: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2355: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

2365: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2366: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2367: l_dyn_sql := l_dyn_sql || '( ';
2368: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2369: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2370: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2371: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2372: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2373: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

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

2387: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2388: l_dyn_sql := l_dyn_sql || '( ';
2389: l_dyn_sql := l_dyn_sql || ' SELECT MUOM.UOM_CODE ';
2390: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(MUOM.UOM_CODE, ''NULL''),''NULL'','||G_SECONDARY_UOM_CODE_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2391: l_dyn_sql := l_dyn_sql || ' FROM MTL_UNITS_OF_MEASURE_TL MUOM, EGO_BULKLOAD_INTF EBI ';
2392: l_dyn_sql := l_dyn_sql || ' WHERE MUOM.UNIT_OF_MEASURE_TL (+) = EBI.'||l_intf_col_name_tbl(i);
2393: l_dyn_sql := l_dyn_sql || ' AND MUOM.LANGUAGE (+) = Userenv(''LANG'') ';
2394: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
2395: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';

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

2398: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2399: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2400: l_dyn_sql := l_dyn_sql || '( ';
2401: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2402: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2403: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2404: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2405: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2406: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

2416: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2417: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2418: l_dyn_sql := l_dyn_sql || '( ';
2419: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2420: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2421: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2422: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2423: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2424: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

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

2438: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2439: l_dyn_sql := l_dyn_sql || '( ';
2440: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';
2441: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.LOOKUP_CODE, ''NULL''),''NULL'','||G_TRACKING_QTY_IND_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2442: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
2443: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE (+) = ''INV_TRACKING_UOM_TYPE'' ';
2444: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE (+) = Userenv(''LANG'') ';
2445: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING (+) = EBI.'||l_intf_col_name_tbl(i);
2446: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

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

2450: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2451: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2452: l_dyn_sql := l_dyn_sql || '( ';
2453: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2454: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2455: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2456: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2457: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2458: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

2468: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2469: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2470: l_dyn_sql := l_dyn_sql || '( ';
2471: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2472: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2473: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2474: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2475: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2476: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

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

2491: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2492: l_dyn_sql := l_dyn_sql || '( ';
2493: l_dyn_sql := l_dyn_sql || ' SELECT IT.INVENTORY_ITEM_STATUS_CODE ';
2494: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(IT.INVENTORY_ITEM_STATUS_CODE, ''NULL''),''NULL'','||G_INV_ITEM_STATUS_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2495: l_dyn_sql := l_dyn_sql || ' FROM MTL_ITEM_STATUS IT, EGO_BULKLOAD_INTF EBI ';
2496: l_dyn_sql := l_dyn_sql || ' WHERE IT.INVENTORY_ITEM_STATUS_CODE_TL (+) = EBI.'||l_intf_col_name_tbl(i);
2497: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
2498: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2499: l_dyn_sql := l_dyn_sql || ') ';

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

2501: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2502: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2503: l_dyn_sql := l_dyn_sql || '( ';
2504: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2505: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2506: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2507: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2508: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' IS NOT NULL';
2509: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

2519: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2520: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2521: l_dyn_sql := l_dyn_sql || '( ';
2522: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2523: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2524: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2525: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2526: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_intf_col_name_tbl(i)||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2527: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

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

2571: ----------------------------------------------------------------------------------
2572: l_dyn_sql := l_dyn_sql || ' SELECT EBI.'||l_intf_col_name_tbl(i);
2573: END IF; --end: IF (l_prod_col_name_tbl(i) = G_ITEM_TEMPLATE_NAME)
2574:
2575: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2576:
2577: IF (l_prod_col_name_tbl(i) IN ('STYLE_ITEM_FLAG', 'GDSN_OUTBOUND_ENABLED_FLAG' )) THEN
2578: l_dyn_sql := l_dyn_sql || ' , FND_LOOKUP_VALUES_VL LKUP ';
2579: END IF;

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

2597: /* option 2 (commented for now) : use if option 1 does not work. fetch the icc_id and set it
2598: l_dyn_sql := l_dyn_sql || ' , MSII.ITEM_CATALOG_GROUP_ID = ';
2599: l_dyn_sql := l_dyn_sql || ' ( ';
2600: l_dyn_sql := l_dyn_sql || ' SELECT MICG.ITEM_CATALOG_GROUP_ID ';
2601: l_dyn_sql := l_dyn_sql || ' FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG, EGO_BULKLOAD_INTF EBI ';
2602: l_dyn_sql := l_dyn_sql || ' WHERE MICG.CONCATENATED_SEGMENTS(+) = EBI.'||l_intf_col_name_tbl(i);
2603: l_dyn_sql := l_dyn_sql || ' ) ';
2604: */
2605: END IF;

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

2608: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2609: l_dyn_sql := l_dyn_sql || ' AND MSII.TRANSACTION_ID IN ';
2610: l_dyn_sql := l_dyn_sql || ' ( ';
2611: l_dyn_sql := l_dyn_sql || ' SELECT EBI.TRANSACTION_ID ';
2612: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2613: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2614: -- l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2615: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
2616: l_dyn_sql := l_dyn_sql || ' ) ';

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

2637: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2638: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2639: l_dyn_sql := l_dyn_sql || '( ';
2640: l_dyn_sql := l_dyn_sql || ' SELECT NULL ';
2641: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI, MTL_SYSTEM_ITEMS_VL MSIV ';
2642: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2643: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2644: l_dyn_sql := l_dyn_sql || ' AND EBI.INSTANCE_PK1_VALUE = MSIV.INVENTORY_ITEM_ID ';
2645: l_dyn_sql := l_dyn_sql || ' AND EBI.INSTANCE_PK2_VALUE = MSIV.ORGANIZATION_ID ';

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

2678: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2679: l_dyn_sql := l_dyn_sql || '( ';
2680: l_dyn_sql := l_dyn_sql || ' SELECT LC.PROJ_ELEMENT_ID ';
2681: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(LC.PROJ_ELEMENT_ID, -1), -1,'||G_LIFECYCLE_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2682: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_LIFECYCLES_V LC, EGO_BULKLOAD_INTF EBI ';
2683: l_dyn_sql := l_dyn_sql || ' WHERE LC.NAME (+) = EBI.'||l_lifecycle_col_val;
2684: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
2685: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2686: l_dyn_sql := l_dyn_sql || ') ';

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

2688: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2689: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2690: l_dyn_sql := l_dyn_sql || '( ';
2691: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2692: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2693: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2694: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2695: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_lifecycle_col_val||' IS NOT NULL';
2696: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_lifecycle_col_val||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

2706: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2707: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2708: l_dyn_sql := l_dyn_sql || '( ';
2709: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2710: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2711: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2712: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2713: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_lifecycle_col_val||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2714: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

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

2731: l_dyn_sql := l_dyn_sql || ' , MSII.PROCESS_FLAG )= ';
2732: l_dyn_sql := l_dyn_sql || '( ';
2733: l_dyn_sql := l_dyn_sql || ' SELECT LCP.PROJ_ELEMENT_ID ';
2734: l_dyn_sql := l_dyn_sql || ' , DECODE(NVL(LCP.PROJ_ELEMENT_ID, -1), -1,'||G_LIFECYCLE_PHASE_ERR_STS||', ' || G_PROCESS_STATUS || ') ';
2735: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_PHASES_V LCP, EGO_BULKLOAD_INTF EBI ';
2736: l_dyn_sql := l_dyn_sql || ' WHERE LCP.PARENT_STRUCTURE_ID (+) = MSII.LIFECYCLE_ID ';
2737: l_dyn_sql := l_dyn_sql || ' AND LCP.NAME (+) = EBI.'||l_lifecycle_phase_col_val;
2738: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
2739: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';

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

2742: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2743: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2744: l_dyn_sql := l_dyn_sql || '( ';
2745: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2746: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2747: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2748: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2749: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_lifecycle_phase_col_val||' IS NOT NULL';
2750: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_lifecycle_phase_col_val||' <> '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';

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

2760: l_dyn_sql := l_dyn_sql || ' AND MSII.PROCESS_FLAG = ' || G_PROCESS_STATUS;
2761: l_dyn_sql := l_dyn_sql || ' AND EXISTS ';
2762: l_dyn_sql := l_dyn_sql || '( ';
2763: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
2764: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
2765: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
2766: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID ';
2767: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_lifecycle_phase_col_val||' = '''||EGO_ITEM_PUB.G_INTF_NULL_CHAR||'''';
2768: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';

Line 2794: FROM EGO_BULKLOAD_INTF EBI

2790: MSII.engineering_date IS NOT NULL
2791: )
2792: AND EXISTS
2793: ( SELECT 'X'
2794: FROM EGO_BULKLOAD_INTF EBI
2795: WHERE EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
2796: AND EBI.TRANSACTION_ID = MSII.TRANSACTION_ID
2797: AND EBI.PROCESS_STATUS = 1
2798: );

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

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

Line 3081: FROM EGO_BULKLOAD_INTF

3077: )
3078: AND MSII.TRANSACTION_ID IN
3079: (
3080: SELECT TRANSACTION_ID
3081: FROM EGO_BULKLOAD_INTF
3082: WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
3083: );
3084:
3085: Write_Debug('MSII: Updated all the line statuses to Error for Pre-processing validation errors');

Line 3273: UPDATE EGO_BULKLOAD_INTF EBI

3269: -----------------------------------------------------------------
3270: -- Update EBI, with the process status of rows in MSII after
3271: -- the completion of IOI processing.
3272: -----------------------------------------------------------------
3273: UPDATE EGO_BULKLOAD_INTF EBI
3274: SET EBI.PROCESS_STATUS =
3275: (
3276: SELECT MSII.PROCESS_FLAG
3277: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

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

3285: )
3286: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
3287:
3288: ----------------------------------------------------------------------------
3289: --Update all the lines in EGO_BULKLOAD_INTF as failure, for which
3290: --The Inventory Item IDs were not available.
3291: --1. For Transaction Type : CREATE, Item ID should be populated
3292: -- at the end of Processing
3293: --2. For Transaction Type : SYNC / UPDATE, Item ID should be

Line 3297: -- UPDATE EGO_BULKLOAD_INTF EBI

3293: --2. For Transaction Type : SYNC / UPDATE, Item ID should be
3294: -- retrieved during processing.
3295: ----------------------------------------------------------------------------
3296: -- R12: this is not required
3297: -- UPDATE EGO_BULKLOAD_INTF EBI
3298: -- SET EBI.PROCESS_STATUS = G_INTF_STATUS_ERROR
3299: -- WHERE EXISTS
3300: -- (
3301: -- SELECT 'X'

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

3306: -- )
3307: -- AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
3308:
3309: ----------------------------------------------------------------------------
3310: -- Update all the lines in EGO_BULKLOAD_INTF as SUCCESS
3311: -- for these rows were used to populate Multi-Row
3312: -- Appropriate errors will be displayed by the User-Defined Attrs Import
3313: -- processing later.
3314: ----------------------------------------------------------------------------

Line 3315: UPDATE EGO_BULKLOAD_INTF EBI

3311: -- for these rows were used to populate Multi-Row
3312: -- Appropriate errors will be displayed by the User-Defined Attrs Import
3313: -- processing later.
3314: ----------------------------------------------------------------------------
3315: UPDATE EGO_BULKLOAD_INTF EBI
3316: SET EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS
3317: WHERE EBI.PROCESS_STATUS = G_INTF_STATUS_TOBE_PROCESS
3318: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
3319:

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

3319:
3320: ----------------------------------------------------------------------------
3321: -- Update the Item IDs (Doesnt matter Create or Update, because
3322: -- Item IDs will be populated either way in MSII) in
3323: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)
3324: -- These Item IDs are required for Item User-Defined Attrs bulkload
3325: ----------------------------------------------------------------------------
3326: UPDATE EGO_BULKLOAD_INTF EBI
3327: SET EBI.INSTANCE_PK1_VALUE =

Line 3326: UPDATE EGO_BULKLOAD_INTF EBI

3322: -- Item IDs will be populated either way in MSII) in
3323: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)
3324: -- These Item IDs are required for Item User-Defined Attrs bulkload
3325: ----------------------------------------------------------------------------
3326: UPDATE EGO_BULKLOAD_INTF EBI
3327: SET EBI.INSTANCE_PK1_VALUE =
3328: (
3329: SELECT MSII.INVENTORY_ITEM_ID
3330: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

Line 3488: UPDATE ego_bulkload_intf

3484: -- Bug: 3804572 - Error messages are not shown, if import format contains revision attrs.
3485: -- this was happening because the error was logged with a previous transaction id and
3486: -- here a new transaction id was updated to Bulkload interface table.
3487: IF p_data_level <> G_ITEM_DATA_LEVEL THEN
3488: UPDATE ego_bulkload_intf
3489: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
3490: WHERE resultfmt_usage_id = p_resultfmt_usage_id;
3491: END IF;
3492:

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

3588:
3589: ---------------------------------------------
3590: -- Update Instance PK2 Value with ORG ID. --
3591: ---------------------------------------------
3592: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF EBI ';
3593: l_dyn_sql := l_dyn_sql || ' SET INSTANCE_PK2_VALUE = ';
3594: l_dyn_sql := l_dyn_sql || ' ( ';
3595: l_dyn_sql := l_dyn_sql || ' SELECT ORGANIZATION_ID ';
3596: l_dyn_sql := l_dyn_sql || ' FROM MTL_PARAMETERS ';

Line 3610: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

3606: ---------------------------------------------------------------------
3607: -- Update EBI with Catalog Group Name if present in the Result Format
3608: ---------------------------------------------------------------------
3609: IF l_item_catalog_name_col IS NOT NULL THEN
3610: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
3611: l_dyn_sql := l_dyn_sql || ' SET ';
3612: l_dyn_sql := l_dyn_sql || G_ITEM_CATALOG_NAME_EBI_COL||' = '||l_item_catalog_name_col ;
3613: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3614: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';

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

3622: -- Fetch Organization ID, Item Number in Temp PLSQL tables.
3623: --------------------------------------------------------------
3624: l_dyn_sql := ' SELECT INSTANCE_PK2_VALUE, '||l_item_number_col || ', ';
3625: l_dyn_sql := l_dyn_sql || ' TRANSACTION_ID ';
3626: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF ';
3627: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3628: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
3629: Write_Debug(l_dyn_sql);
3630:

Line 3673: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

3669: l_inventory_item_id := FND_FLEX_KEYVAL.combination_id;
3670:
3671: Write_Debug('Inv Item ID : '||To_char(l_inventory_item_id));
3672:
3673: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
3674: l_dyn_sql := l_dyn_sql || ' SET INSTANCE_PK1_VALUE = '||l_inventory_item_id;
3675: l_dyn_sql := l_dyn_sql || ' WHERE INSTANCE_PK2_VALUE = '||l_org_id_table(i);
3676: l_dyn_sql := l_dyn_sql || ' AND '|| l_item_number_col|| ' = :ITEM_NUMBER';
3677: l_dyn_sql := l_dyn_sql || ' AND RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

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

3688: -- Bug #5179741(RSOUNDAR)
3689: -----------------------------------------------------------------
3690: IF G_CATALOG_GROUP_ID IS NULL THEN
3691: IF l_item_catalog_name_col IS NOT NULL THEN
3692: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF EBI';
3693: l_dyn_sql := l_dyn_sql || ' SET '||G_ITEM_CATALOG_EBI_COL||' = ';
3694: l_dyn_sql := l_dyn_sql || '( ';
3695: l_dyn_sql := l_dyn_sql || ' SELECT TO_CHAR(MICG.ITEM_CATALOG_GROUP_ID) ';
3696: l_dyn_sql := l_dyn_sql || ' FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG ';

Line 3710: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

3706: NULL;
3707: END IF; -- l_item_catalog_name_col IS NOT NULL
3708: ELSE -- G_CATALOG_GROUP_ID IS NOT NULL
3709:
3710: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
3711: -------------------------------------------------------------
3712: --Storing Catalog Group ID and other imp data in buffer cols
3713: -------------------------------------------------------------
3714: l_dyn_sql := l_dyn_sql || ' SET '||G_ITEM_CATALOG_EBI_COL||' = '||G_CATALOG_GROUP_ID;

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

3807: l_dyn_sql := l_dyn_sql || ' EBI.'||l_org_code_col ||' , ';
3808: l_dyn_sql := l_dyn_sql || G_PROCESS_STATUS ||' , ';
3809: l_dyn_sql := l_dyn_sql || ' TO_NUMBER(EBI.C_FIX_COLUMN11) , ';
3810: l_dyn_sql := l_dyn_sql || ' EBI.C_FIX_COLUMN12 ';
3811: l_dyn_sql := l_dyn_sql || 'FROM EGO_BULKLOAD_INTF EBI ';
3812: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3813: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
3814:
3815: Write_Debug(l_dyn_sql);

Line 3822: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

3818:
3819: ----------------------------------------------------------------------------------
3820: --Save Item Num, Org Code in designated columns in EBI for Error Reporting later
3821: ----------------------------------------------------------------------------------
3822: l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';
3823: l_dyn_sql := l_dyn_sql || ' SET ';
3824: -----------------------------------------------------------------------------------------
3825: -- In case of ITEM_LEVEL the following is already populated in load_item_interface_lines
3826: -- procedure.

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

3857: l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';
3858: l_dyn_sql := l_dyn_sql || ' SET MIRI.'||G_REV_REASON_DB_COL||' = ';
3859: l_dyn_sql := l_dyn_sql || '( ';
3860: l_dyn_sql := l_dyn_sql || ' SELECT IT.LOOKUP_CODE ';
3861: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
3862: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE = ''EGO_ITEM_REVISION_REASON'' ';
3863: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE = Userenv(''LANG'') ';
3864: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING = EBI.'||l_intf_col_name_tbl(i);
3865: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

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

3871: l_dyn_sql := l_dyn_sql || ') ';
3872: l_dyn_sql := l_dyn_sql || ' WHERE EXISTS ';
3873: l_dyn_sql := l_dyn_sql || '( ';
3874: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
3875: l_dyn_sql := l_dyn_sql || ' FROM FND_LOOKUP_VALUES IT, EGO_BULKLOAD_INTF EBI ';
3876: l_dyn_sql := l_dyn_sql || ' WHERE IT.LOOKUP_TYPE = ''EGO_ITEM_REVISION_REASON'' ';
3877: l_dyn_sql := l_dyn_sql || ' AND IT.LANGUAGE = Userenv(''LANG'') ';
3878: l_dyn_sql := l_dyn_sql || ' AND IT.MEANING = EBI.'||l_intf_col_name_tbl(i);
3879: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';

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

3915: l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';
3916: l_dyn_sql := l_dyn_sql || ' SET MIRI.'||l_prod_col_name_tbl(i) ||' = ';
3917: l_dyn_sql := l_dyn_sql || '( ';
3918: l_dyn_sql := l_dyn_sql || ' SELECT EBI.'||l_intf_col_name_tbl(i);
3919: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
3920: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3921: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';
3922: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
3923: l_dyn_sql := l_dyn_sql || ') ';

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

3923: l_dyn_sql := l_dyn_sql || ') ';
3924: l_dyn_sql := l_dyn_sql || ' WHERE EXISTS ';
3925: l_dyn_sql := l_dyn_sql || '( ';
3926: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
3927: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF EBI ';
3928: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
3929: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';
3930: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
3931: l_dyn_sql := l_dyn_sql || ') ';

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

3966: l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';
3967: l_dyn_sql := l_dyn_sql || ' SET MIRI.LIFECYCLE_ID = ';
3968: l_dyn_sql := l_dyn_sql || '( ';
3969: l_dyn_sql := l_dyn_sql || ' SELECT LC.PROJ_ELEMENT_ID ';
3970: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_LIFECYCLES_PHASES_V LC, EGO_BULKLOAD_INTF EBI ';
3971: l_dyn_sql := l_dyn_sql || ' WHERE LC.OBJECT_TYPE = ''PA_STRUCTURES'' ';
3972: l_dyn_sql := l_dyn_sql || ' AND LC.NAME = EBI.'||l_lifecycle_col_val;
3973: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3974: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';

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

3976: l_dyn_sql := l_dyn_sql || ') ';
3977: l_dyn_sql := l_dyn_sql || ' WHERE EXISTS ';
3978: l_dyn_sql := l_dyn_sql || '( ';
3979: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
3980: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_LIFECYCLES_PHASES_V LC, EGO_BULKLOAD_INTF EBI ';
3981: l_dyn_sql := l_dyn_sql || ' WHERE LC.OBJECT_TYPE = ''PA_STRUCTURES'' ';
3982: l_dyn_sql := l_dyn_sql || ' AND LC.NAME = EBI.'||l_lifecycle_col_val;
3983: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
3984: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';

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

4006: l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';
4007: l_dyn_sql := l_dyn_sql || ' SET MIRI.LIFECYCLE_ID = ';
4008: l_dyn_sql := l_dyn_sql || '( ';
4009: l_dyn_sql := l_dyn_sql || ' SELECT MSI.LIFECYCLE_ID ';
4010: l_dyn_sql := l_dyn_sql || ' FROM MTL_SYSTEM_ITEMS_B MSI, EGO_BULKLOAD_INTF EBI ';
4011: l_dyn_sql := l_dyn_sql || ' WHERE MSI.INVENTORY_ITEM_ID = EBI.INSTANCE_PK1_VALUE ';
4012: l_dyn_sql := l_dyn_sql || ' AND MSI.ORGANIZATION_ID = EBI.INSTANCE_PK2_VALUE ';
4013: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
4014: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';

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

4016: l_dyn_sql := l_dyn_sql || ') ';
4017: l_dyn_sql := l_dyn_sql || ' WHERE EXISTS ';
4018: l_dyn_sql := l_dyn_sql || '( ';
4019: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
4020: l_dyn_sql := l_dyn_sql || ' FROM MTL_SYSTEM_ITEMS_B MSI, EGO_BULKLOAD_INTF EBI ';
4021: l_dyn_sql := l_dyn_sql || ' WHERE MSI.INVENTORY_ITEM_ID = EBI.INSTANCE_PK1_VALUE ';
4022: l_dyn_sql := l_dyn_sql || ' AND MSI.ORGANIZATION_ID = EBI.INSTANCE_PK2_VALUE ';
4023: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
4024: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';

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

4040: l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';
4041: l_dyn_sql := l_dyn_sql || ' SET MIRI.CURRENT_PHASE_ID = ';
4042: l_dyn_sql := l_dyn_sql || '( ';
4043: l_dyn_sql := l_dyn_sql || ' SELECT LCP.PROJ_ELEMENT_ID ';
4044: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_PHASES_V LCP, EGO_BULKLOAD_INTF EBI ';
4045: l_dyn_sql := l_dyn_sql || ' WHERE LCP.PARENT_STRUCTURE_ID = MIRI.LIFECYCLE_ID ';
4046: l_dyn_sql := l_dyn_sql || ' AND LCP.NAME = EBI.'||l_lifecycle_phase_col_val;
4047: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
4048: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';

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

4050: l_dyn_sql := l_dyn_sql || ') ';
4051: l_dyn_sql := l_dyn_sql || ' WHERE EXISTS ';
4052: l_dyn_sql := l_dyn_sql || '( ';
4053: l_dyn_sql := l_dyn_sql || ' SELECT ''X'' ';
4054: l_dyn_sql := l_dyn_sql || ' FROM PA_EGO_PHASES_V LCP, EGO_BULKLOAD_INTF EBI ';
4055: l_dyn_sql := l_dyn_sql || ' WHERE LCP.PARENT_STRUCTURE_ID = MIRI.LIFECYCLE_ID ';
4056: l_dyn_sql := l_dyn_sql || ' AND LCP.NAME = EBI.'||l_lifecycle_phase_col_val;
4057: l_dyn_sql := l_dyn_sql || ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_EXISTS';
4058: l_dyn_sql := l_dyn_sql || ' AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID ';

Line 4089: FROM EGO_BULKLOAD_INTF EBI

4085: AND revision_label IS NULL
4086: AND revision_reason IS NULL
4087: AND current_phase_id IS NULL
4088: AND EXISTS (SELECT 'X'
4089: FROM EGO_BULKLOAD_INTF EBI
4090: WHERE EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
4091: AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID
4092: AND EBI.PROCESS_STATUS = 1
4093: ); */

Line 4104: FROM EGO_BULKLOAD_INTF EBI

4100: implementation_date = DECODE(implementation_date,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,implementation_date),
4101: effectivity_date = DECODE(effectivity_date,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,effectivity_date)
4102: WHERE MIRI.TRANSACTION_ID IN
4103: ( SELECT EBI.TRANSACTION_ID
4104: FROM EGO_BULKLOAD_INTF EBI
4105: WHERE EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
4106: AND EBI.PROCESS_STATUS = 1
4107: )
4108: AND (MIRI.ecn_initiation_date IS NOT NULL

Line 4270: UPDATE EGO_BULKLOAD_INTF EBI

4266: -----------------------------------------------------------------
4267: -- Update EBI, with the process status of rows in MIRI after
4268: -- the completion of IOI Revision processing.
4269: -----------------------------------------------------------------
4270: UPDATE EGO_BULKLOAD_INTF EBI
4271: SET EBI.PROCESS_STATUS =
4272: (
4273: SELECT MIRI.PROCESS_FLAG
4274: FROM MTL_ITEM_REVISIONS_INTERFACE MIRI

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

4282: )
4283: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
4284:
4285: ----------------------------------------------------------------------------
4286: -- Update all the lines in EGO_BULKLOAD_INTF as failure, for which
4287: -- The Revision IDS were not available.
4288: -- 1. For Transaction Type : CREATE, Revision ID should be populated
4289: -- at the end of Processing
4290: -- 2. For Transaction Type : SYNC / UPDATE, Revision ID should be

Line 4293: UPDATE EGO_BULKLOAD_INTF EBI

4289: -- at the end of Processing
4290: -- 2. For Transaction Type : SYNC / UPDATE, Revision ID should be
4291: -- retrieved during processing.
4292: ----------------------------------------------------------------------------
4293: UPDATE EGO_BULKLOAD_INTF EBI
4294: SET EBI.PROCESS_STATUS = G_INTF_STATUS_ERROR
4295: WHERE EXISTS
4296: (
4297: SELECT 'X'

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

4302: )
4303: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
4304:
4305: ----------------------------------------------------------------------------
4306: -- Update all the lines in EGO_BULKLOAD_INTF as SUCCESS
4307: -- for these rows were used to populate Multi-Row
4308: -- Appropriate errors will be displayed by the User-Defined Attrs Bulkldr.
4309: ----------------------------------------------------------------------------
4310: UPDATE EGO_BULKLOAD_INTF EBI

Line 4310: UPDATE EGO_BULKLOAD_INTF EBI

4306: -- Update all the lines in EGO_BULKLOAD_INTF as SUCCESS
4307: -- for these rows were used to populate Multi-Row
4308: -- Appropriate errors will be displayed by the User-Defined Attrs Bulkldr.
4309: ----------------------------------------------------------------------------
4310: UPDATE EGO_BULKLOAD_INTF EBI
4311: SET EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS
4312: WHERE EBI.PROCESS_STATUS = G_INTF_STATUS_TOBE_PROCESS
4313: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
4314:

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

4314:
4315: ----------------------------------------------------------------------------
4316: -- Update the Item IDs (Doesnt matter Create or Update, because
4317: -- Item IDs will be populated either way in MSII) in
4318: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)
4319: -- These Item IDs are required for Item User-Defined Attrs bulkload
4320: ----------------------------------------------------------------------------
4321: UPDATE EGO_BULKLOAD_INTF EBI
4322: SET EBI.INSTANCE_PK1_VALUE =

Line 4321: UPDATE EGO_BULKLOAD_INTF EBI

4317: -- Item IDs will be populated either way in MSII) in
4318: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)
4319: -- These Item IDs are required for Item User-Defined Attrs bulkload
4320: ----------------------------------------------------------------------------
4321: UPDATE EGO_BULKLOAD_INTF EBI
4322: SET EBI.INSTANCE_PK1_VALUE =
4323: (
4324: SELECT MSII.INVENTORY_ITEM_ID
4325: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII

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

4336:
4337: ----------------------------------------------------------------------------
4338: -- Update the Item Revision IDs (Doesnt matter Create or Update, because
4339: -- Item Revision IDs will be populated either way in MIRI) in
4340: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)
4341: -- These Revision IDs are required for Revision User-Defined Attrs bulkload
4342: ----------------------------------------------------------------------------
4343: UPDATE EGO_BULKLOAD_INTF EBI
4344: SET EBI.INSTANCE_PK3_VALUE =

Line 4343: UPDATE EGO_BULKLOAD_INTF EBI

4339: -- Item Revision IDs will be populated either way in MIRI) in
4340: -- Generic Bulkload Intf table (EGO_BULKLOAD_INTF)
4341: -- These Revision IDs are required for Revision User-Defined Attrs bulkload
4342: ----------------------------------------------------------------------------
4343: UPDATE EGO_BULKLOAD_INTF EBI
4344: SET EBI.INSTANCE_PK3_VALUE =
4345: (
4346: SELECT MIRI.REVISION_ID
4347: FROM MTL_ITEM_REVISIONS_INTERFACE MIRI

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

4748: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || l_intf_col_name_table(i) ;
4749: END IF;
4750: END LOOP LIORUI_update_item_ids_loop; --end: FOR i IN 1..l_attr_id_table.COUNT LOOP
4751:
4752: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' FROM EGO_BULKLOAD_INTF ' ;
4753: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
4754: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' AND PROCESS_STATUS = :PROCESS_STATUS ';
4755: Write_Debug(l_api_name || l_dyn_attr_id_val_sql);
4756:

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

5643: ------------------------------------------ Bug#13816809: (HIGH SQL VERSION COUNT PROBLEM) --------------------------------------------
5644:
5645:
5646: ----------------------------------------------------------------------------
5647: -- Fetch the Oper Attrs values from EGO_BULKLOAD_INTF. --
5648: ----------------------------------------------------------------------------
5649:
5650: l_cursor_oper_attr := DBMS_SQL.OPEN_CURSOR;
5651: l_dyn_sql := ' SELECT ';

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

5659: ELSE
5660: l_dyn_sql := l_dyn_sql || l_intf_col_table(i) || ' ';
5661: END IF;
5662: END LOOP; --FOR (i in 1..l_attr_id_table.COUNT) LOOP
5663: l_dyn_sql := l_dyn_sql || ' FROM EGO_BULKLOAD_INTF ';
5664: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID ';
5665: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
5666:
5667: Write_Debug(l_dyn_sql);

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

5867: FND_MESSAGE.set_name('EGO', 'EGO_IPI_INVALID_VALUE');
5868: FND_MESSAGE.set_token('NAME', l_attr_disp_name_table(i));
5869: FND_MESSAGE.set_token('VALUE',NVL(l_attr_disp_val_table(i),l_attr_int_val_table(i)));
5870: l_ebi_err_msg := fnd_message.get();
5871: l_dyn_sql_ebi := ' UPDATE ego_bulkload_intf ' ||
5872: ' SET '|| G_VAL_SET_CONV_ERR_COL ||' = '''||l_ebi_err_msg||''''||
5873: ' WHERE TRANSACTION_ID = ' || l_transaction_id;
5874: EXECUTE IMMEDIATE l_dyn_sql_ebi;
5875: EXIT;

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

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

Line 5996: FROM EGO_BULKLOAD_INTF

5992: )
5993: AND TRANSACTION_ID IN
5994: (
5995: SELECT TRANSACTION_ID
5996: FROM EGO_BULKLOAD_INTF
5997: WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
5998: );
5999:
6000: EXCEPTION

Line 6045: FROM EGO_BULKLOAD_INTF

6041: ------------------------------------------------------
6042:
6043: CURSOR c_err_logfile (c_resultfmt_usage_id IN NUMBER) IS
6044: SELECT C_INTF_ATTR239
6045: FROM EGO_BULKLOAD_INTF
6046: WHERE RESULTFMT_USAGE_ID = c_resultfmt_usage_id
6047: AND C_INTF_ATTR239 IS NOT NULL;
6048:
6049: -- Long Dynamic SQL String

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

6072: -- Debug file, that has been opened before.
6073: ------------------------------------------------------------
6074: IF (l_is_debug) THEN
6075:
6076: l_log_dir := SUBSTR(l_err_logfile_fullpath, 0, INSTR(l_err_logfile_fullpath, 'EGO_BULKLOAD_INTF') - 1);
6077: l_log_file := SUBSTR(l_err_logfile_fullpath, INSTR(l_err_logfile_fullpath, 'EGO_BULKLOAD_INTF'));
6078:
6079: -----------------------------------------------------------------------
6080: -- To open the Debug Session to write the Debug Log. --

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

6073: ------------------------------------------------------------
6074: IF (l_is_debug) THEN
6075:
6076: l_log_dir := SUBSTR(l_err_logfile_fullpath, 0, INSTR(l_err_logfile_fullpath, 'EGO_BULKLOAD_INTF') - 1);
6077: l_log_file := SUBSTR(l_err_logfile_fullpath, INSTR(l_err_logfile_fullpath, 'EGO_BULKLOAD_INTF'));
6078:
6079: -----------------------------------------------------------------------
6080: -- To open the Debug Session to write the Debug Log. --
6081: -- This sets Debug value so that Error_Handler.Get_Debug returns 'Y' --

Line 6106: ' FROM EGO_BULKLOAD_INTF EBI ' ||

6102: ' ) ' ||
6103: ' WHERE EXISTS ' ||
6104: ' ( ' ||
6105: ' SELECT ''X'' ' ||
6106: ' FROM EGO_BULKLOAD_INTF EBI ' ||
6107: ' WHERE FND_NUMBER.CANONICAL_TO_NUMBER(EBI.INSTANCE_PK1_VALUE) = MSIB.INVENTORY_ITEM_ID ' ||
6108: ' AND FND_NUMBER.CANONICAL_TO_NUMBER(EBI.INSTANCE_PK2_VALUE) = MSIB.ORGANIZATION_ID ' ||
6109: ' AND EBI.PROCESS_STATUS = 7 ' || -- Successful Rows Only
6110: ' AND EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_2 ' ||

Line 6536: FROM EGO_BULKLOAD_INTF EBI

6532: AND revision_label IS NULL
6533: AND revision_reason IS NULL
6534: AND current_phase_id IS NULL
6535: AND EXISTS (SELECT 'X'
6536: FROM EGO_BULKLOAD_INTF EBI
6537: WHERE EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
6538: AND EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID
6539: AND EBI.PROCESS_STATUS = 1
6540: )

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

6562: l_dyn_sql := l_dyn_sql || 'EBI.C_INTF_ATTR241 ORGANIZATION_CODE, '||G_NEWLINE;
6563: l_dyn_sql := l_dyn_sql || 'EBI.C_INTF_ATTR242 REVISION_CODE, '||G_NEWLINE;
6564: l_dyn_sql := l_dyn_sql || 'MIERR.ERROR_MESSAGE '||G_NEWLINE;
6565: l_dyn_sql := l_dyn_sql || 'FROM MTL_INTERFACE_ERRORS MIERR, '||G_NEWLINE;
6566: l_dyn_sql := l_dyn_sql || 'EGO_BULKLOAD_INTF EBI '||G_NEWLINE;
6567: l_dyn_sql := l_dyn_sql || 'WHERE MIERR.TRANSACTION_ID = EBI.TRANSACTION_ID '||G_NEWLINE;
6568: l_dyn_sql := l_dyn_sql || 'AND MIERR.request_id = '||G_REQUEST_ID||G_NEWLINE;
6569: Developer_Debug('In Case Error Reporting page has problems, Execute the following SQL to fetch the Concurrent Program CUMULATIVE Errors: ');
6570: Developer_Debug(l_dyn_sql);

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

6625: Developer_Debug('Returning x_retcode : '|| x_retcode);
6626: l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', --Status
6627: to_char(SQLCODE) --Message
6628: );
6629: Developer_Debug('Setting all the *Unprocessed* rows (PROCESS_STATUS = 1) in EGO_BULKLOAD_INTF to PROCESS_STATUS = 3.');
6630:
6631: -----------------------------------------------------------------------------------------------
6632: -- Update all the lines in EGO_BULKLOAD_INTF as ERROR.
6633: -- This will ensure that the next submission of the Same excel doesnt throw

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

6628: );
6629: Developer_Debug('Setting all the *Unprocessed* rows (PROCESS_STATUS = 1) in EGO_BULKLOAD_INTF to PROCESS_STATUS = 3.');
6630:
6631: -----------------------------------------------------------------------------------------------
6632: -- Update all the lines in EGO_BULKLOAD_INTF as ERROR.
6633: -- This will ensure that the next submission of the Same excel doesnt throw
6634: -- IOI error:
6635: -- Item already exists in the organization V1. Please use a different item name/number.
6636: -----------------------------------------------------------------------------------------------

Line 6637: UPDATE EGO_BULKLOAD_INTF EBI

6633: -- This will ensure that the next submission of the Same excel doesnt throw
6634: -- IOI error:
6635: -- Item already exists in the organization V1. Please use a different item name/number.
6636: -----------------------------------------------------------------------------------------------
6637: UPDATE EGO_BULKLOAD_INTF EBI
6638: SET EBI.PROCESS_STATUS = G_INTF_STATUS_ERROR
6639: WHERE EBI.PROCESS_STATUS = G_INTF_STATUS_TOBE_PROCESS
6640: AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
6641:

Line 6700: FROM EGO_BULKLOAD_INTF

6696: )
6697: IS
6698: CURSOR c_errLogFile ( c_resultfmt_usage_id IN NUMBER) IS
6699: SELECT C_INTF_ATTR239
6700: FROM EGO_BULKLOAD_INTF
6701: WHERE C_INTF_ATTR239 IS NOT NULL
6702: AND RESULTFMT_USAGE_ID = c_resultfmt_usage_id
6703: AND ROWNUM < 2;
6704:

Line 6707: FROM EGO_BULKLOAD_INTF

6703: AND ROWNUM < 2;
6704:
6705: CURSOR c_itemId_OrgId (c_resultfmt_usage_id IN NUMBER) IS
6706: SELECT INSTANCE_PK1_VALUE , INSTANCE_PK2_VALUE
6707: FROM EGO_BULKLOAD_INTF
6708: WHERE RESULTFMT_USAGE_ID = c_resultfmt_usage_id
6709: AND PROCESS_STATUS = 7;
6710:
6711: CURSOR c_gtid (c_inventory_itemId IN NUMBER , c_organization_Id IN NUMBER) IS

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

6825: Write_Debug('Clear_Gtin_Attrs : ');
6826: Write_Debug('-----------------------------------------------------------------');
6827:
6828: IF (l_is_Debug ) THEN
6829: l_err_log_file_dir := SUBSTR (l_err_log_file , 0 , INSTR (l_err_log_file , 'EGO_BULKLOAD_INTF' ) - 1 );
6830: l_err_log_file_name := SUBSTR (l_err_log_file , INSTR ( l_err_log_file , 'EGO_BULKLOAD_INTF' )-1 );
6831: -----------------------------------------------------------------------
6832: -- To open the Debug Session to write the Debug Log. --
6833: -- This sets Debug value so that Error_Handler.Get_Debug returns 'Y' --

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

6826: Write_Debug('-----------------------------------------------------------------');
6827:
6828: IF (l_is_Debug ) THEN
6829: l_err_log_file_dir := SUBSTR (l_err_log_file , 0 , INSTR (l_err_log_file , 'EGO_BULKLOAD_INTF' ) - 1 );
6830: l_err_log_file_name := SUBSTR (l_err_log_file , INSTR ( l_err_log_file , 'EGO_BULKLOAD_INTF' )-1 );
6831: -----------------------------------------------------------------------
6832: -- To open the Debug Session to write the Debug Log. --
6833: -- This sets Debug value so that Error_Handler.Get_Debug returns 'Y' --
6834: -----------------------------------------------------------------------

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

7678: l_dyn_sql := l_dyn_sql || ' NULL , ';
7679: l_dyn_sql := l_dyn_sql || ' TO_NUMBER(EBI.C_FIX_COLUMN11) , ';
7680: l_dyn_sql := l_dyn_sql || ' EBI.C_FIX_COLUMN12 , ';
7681: l_dyn_sql := l_dyn_sql || ' NULL ';
7682: l_dyn_sql := l_dyn_sql || 'FROM EGO_BULKLOAD_INTF EBI ';
7683: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
7684: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
7685: IF(l_supplier_name_col IS NOT NULL AND l_supplier_number_col IS NOT NULL)THEN
7686: l_dyn_sql := l_dyn_sql || ' AND ( EBI.'||l_supplier_name_col||' IS NOT NULL ';

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

7812: l_dyn_sql := l_dyn_sql || ' NULL BUNDLE_ID , ';
7813: l_dyn_sql := l_dyn_sql || ' TO_NUMBER(EBI.C_FIX_COLUMN11) , ';
7814: l_dyn_sql := l_dyn_sql || ' EBI.C_FIX_COLUMN12 , ';
7815: l_dyn_sql := l_dyn_sql || ' NULL ASSOCIATION_ID ';
7816: l_dyn_sql := l_dyn_sql || 'FROM EGO_BULKLOAD_INTF EBI ';
7817: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
7818: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
7819: IF(l_supplier_name_col IS NOT NULL AND l_supplier_number_col IS NOT NULL)THEN
7820: l_dyn_sql := l_dyn_sql || ' AND ( EBI.'||l_supplier_name_col||' IS NOT NULL ';

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

7874: l_dyn_sql := l_dyn_sql || ' NULL BUNDLE_ID , ';
7875: l_dyn_sql := l_dyn_sql || ' TO_NUMBER(EBI.C_FIX_COLUMN11) , ';
7876: l_dyn_sql := l_dyn_sql || ' EBI.C_FIX_COLUMN12 , ';
7877: l_dyn_sql := l_dyn_sql || ' NULL ASSOCIATION_ID ';
7878: l_dyn_sql := l_dyn_sql || 'FROM EGO_BULKLOAD_INTF EBI ';
7879: l_dyn_sql := l_dyn_sql || ' WHERE EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
7880: l_dyn_sql := l_dyn_sql || ' AND EBI.PROCESS_STATUS = 1 ';
7881: l_dyn_sql := l_dyn_sql || ' AND EBI.'||l_supplier_name_col||' IS NOT NULL ';
7882: l_dyn_sql := l_dyn_sql || ' AND ''Y'' = '''||l_has_sup_sit_org_col||''' ';