The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
' SET (system_action, rt_item_id) = (SELECT DISTINCT decode(it2.action, ' ||
'''SYNC'', decode(it2.row_type, ''PRICE'', ''ADD'', ' ||
'decode(item.rt_item_id, NULL, ''ADD'', decode(tl.rt_item_id, ' ||
'NULL, ''TRANSLATE'', ''UPDATE''))), it2.action), item.rt_item_id ' ||
'FROM icx_por_items_tl tl, icx_por_items item, ' || p_table_name ||
' it2, icx_por_oracle_item_subtable orc ' ||
'WHERE it1.rowid = it2.rowid ' ||
'AND it2.supplier_name = item.a1 (+) ' ||
'AND it2.supplier_part_num = item.a3 (+) ' ||
'AND item.rt_item_id = orc.rt_item_id (+) ' ||
'AND (orc.orc_operating_unit_id = :oper_unit_id OR orc.orc_operating_unit_id is null) '||
'AND item.rt_item_id = tl.rt_item_id (+) ' ||
'AND tl.language (+) = ''' || p_language || ''') ' ||
'WHERE line_number between :startrow AND :endrow';
l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
' SET (system_action, rt_item_id) = (SELECT decode(it2.action, ' ||
'''SYNC'', decode(it2.row_type, ''PRICE'', ''ADD'', ' ||
'decode(item.rt_item_id, NULL, ''ADD'', decode(tl.rt_item_id, ' ||
'NULL, ''TRANSLATE'', ''UPDATE''))), it2.action), item.rt_item_id ' ||
'FROM icx_por_items_tl tl, icx_por_items item, ' || p_table_name ||
' it2 WHERE it1.rowid = it2.rowid AND it2.supplier_name = item.a1 (+) ' ||
'AND it2.supplier_part_num = item.a3 (+) ' ||
'AND item.rt_item_id = tl.rt_item_id (+) ' ||
'AND tl.language (+) = ''' || p_language || ''') ' ||
'WHERE line_number between :startrow AND :endrow';
l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
'SET (buyer_id, error_message) = (SELECT decode(it2.buyer_name, null, -2, ''All-Buyers'', -2, buy.organization_id), it1.error_message || ' ||
'decode(it2.system_action, ''ADD'', decode(it2.supplier_part_num, null, ' ||
'''.SUPPLIER_PART_NUM:ICX_POR_SUPPLIER_PART_REQD'', null), ' ||
'''DELETE'', decode(it2.supplier_part_num, null, ' ||
'''.SUPPLIER_PART_NUM:ICX_POR_SUPPLIER_PART_REQD'', null), null) || ' ||
'decode(it2.system_action, ''DELETE'', decode(it2.rt_item_id, null, ' ||
'''.SUPPLIER_PART_NUM:ICX_POR_PRC_INVALID_SUP_PART'', null), null) || ' ||
-- Bug 1344934: Loading item and buyer-specific price in a file will
-- reject buyer-specific price
-- Will check item of Price line during moving data process
-- 'decode(it2.row_type, ''PRICE'', decode(it2.rt_item_id, null, ' ||
-- '''.SUPPLIER_PART_NUM:ICX_POR_PRC_INVALID_SUP_PART'', null), null) || ' ||
'decode(it2.system_action, ''TRANSLATE'', decode(it2.description, null, ' ||
'''.DESCRIPTION:ICX_POR_INVALID_DESCRIPTION'', null), null) || ' ||
'decode(it2.system_action, ''ADD'', decode(it2.row_type, ''ITEM_PRICE'', ' ||
'decode(it2.description, null, ' ||
'''.DESCRIPTION:ICX_POR_INVALID_DESCRIPTION'', null), null),null) || ' ||
'decode(it2.system_action, ''ADD'', decode(it2.row_type, ''ITEM'', ' ||
'decode(it2.price_string, null, ' ||
'''.PRICE:ICX_POR_PRICE_REQD'', null), null), null) || ' ||
'decode(it2.system_action, ''ADD'', decode(it2.row_type, ''ITEM'', ' ||
'decode(it2.uom_code, null, ' ||
'''.PRICE:ICX_POR_UOM_REQD'', null), null), null) || ' ||
'decode(it2.row_type, ''PRICE'', decode(it2.buyer_name, null, ' ||
'''.BUYER:ICX_POR_BUYER_REQD'', null), null) || ' ||
'decode(it2.row_type, ''PRICE'', decode(it2.pricelist_name, null, ' ||
'''.PRICELIST:ICX_POR_CAT_PRICE_LIST_NAME_M'', null), null) || ' ||
'decode(it2.row_type, ''PRICE'', decode(it2.currency_code, null, ' ||
'''.CURRENCY:ICX_POR_CURRENCY_REQD'', null), null) || ' ||
'decode(it2.row_type, ''PRICE'', decode(it2.price_string, null, ' ||
'''.PRICE:ICX_POR_PRICE_REQD'', null), null) || ' ||
'decode(it2.row_type, ''PRICE'', decode(it2.uom_code, null, ' ||
'''.UOM:ICX_POR_UOM_REQD'', null), null) || ' ||
-- BUG#2228935 Check Price and UOM reqd only if system_action is ADD
'decode(it2.system_action, ''ADD'', ' ||
'decode(it2.row_type, ''ITEM_PRICE'', decode(it2.price_string, null, ' ||
'''.PRICE:ICX_POR_PRICE_REQD'', null), null), null) || ' ||
-- BUG#2228935 Check Price and UOM reqd only if system_action is ADD
'decode(it2.system_action, ''ADD'', ' ||
'decode(it2.row_type, ''ITEM_PRICE'', decode(it2.uom_code, null, ' ||
'''.UOM:ICX_POR_UOM_REQD'', null), null), null) || ' ||
'decode(it2.buyer_name, null, null, :all_buyer_list_name' ||
', null, decode(buy.name, null, ' ||
'''.BUYER:ICX_POR_INVALID_BUYER'')) ' ||
' FROM hr_all_organization_units buy, ' || p_table_name || ' it2 WHERE it1.rowid = it2.rowid ' ||
' AND it2.buyer_name = buy.name (+) ' ||
' AND :bus_group_id = buy.business_group_id (+)) ' ||
'WHERE line_number between :startrow AND :endrow';
l_sql_string := 'SELECT vendor_id ' ||
'FROM po_vendors supp, ' || p_table_name || ' it2 ' ||
'WHERE supp.vendor_name = it2.supplier_name ';
SELECT header_id, name, currency_code
INTO l_list_price_id, l_list_price_name, l_list_price_currency
FROM icx_por_price_lists
WHERE supplier_id = l_supplier_id AND Buyer_id = l_organization_id;
l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
'SET (pricelist_id, supplier_id, error_message) = ' ||
'(SELECT DISTINCT pl.header_id, ' ||
'supp.vendor_id, it1.error_message || '||
'decode(it2.supplier_name, null, null, decode(supp.vendor_name, null, ' ||
'''.SNAME:ICX_POR_INVALID_SUPPLIER'', null)) || ' ||
'decode(greatest( nvl(supp.start_date_active,sysdate-1), sysdate), sysdate, null, ' ||
'''.SNAME:ICX_POR_INACTIVE_SUPPLIER'') || ' ||
'decode(greatest( nvl(supp.end_date_active ,sysdate+1), sysdate), sysdate, '||
'''.SNAME:ICX_POR_INACTIVE_SUPPLIER'', null) || ' ||
'decode(it2.item_type, null, null, decode(lkp.lookup_code, null, ' ||
'''.ITEM_TYPE:ICX_POR_INVALID_ITEM_TYPE'', null)) || ' ||
'decode(it2.currency_code, null, null, decode(cur.currency_code, null, ' ||
'''.CURRENCY:ICX_POR_INVALID_CURRENCY'', null)) ' ||
' FROM po_vendors supp, icx_por_price_lists pl, ' ||
' fnd_lookup_values lkp, fnd_currencies cur, '||
p_table_name ||' it2 ' ||
' WHERE it1.rowid = it2.rowid ' ||
' AND upper(it2.currency_code) = cur.currency_code (+) ' ||
' AND it2.item_type = lkp.lookup_code (+) ' ||
' AND lkp.lookup_type (+) = ''ICX_CATALOG_ITEM_TYPE'' ' ||
' AND lkp.language (+) = :language' ||
' AND it2.buyer_id = pl.buyer_id (+) ' ||
--Bug#1581013: Currency validation
' AND it2.currency_code = pl.currency_code (+) ' ||
' AND pl.supplier_id (+) = :supplier_id ' ||
' AND supp.vendor_name (+) = it2.supplier_name ' ||
') WHERE line_number BETWEEN :startrow AND :endrow ';
SELECT nvl(multi_org_flag, 'N') INTO l_chk_multi_org
FROM fnd_product_groups ;
l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
'SET error_message = error_message ||( ' ||
'SELECT DISTINCT error_message || ' ||
'decode(site.purchasing_site_flag, ' ||'''N''' || ', ' ||
'''.SITE:ICX_POR_INVALID_SUPP_SITE_2'', null ) || ' ||
'decode(greatest(nvl(site.inactive_date ,sysdate+1), sysdate), sysdate, ' ||
'''.SITE:ICX_POR_INACTIVE_SUPP_SITE'', null) || ' ||
-- Bug#2054819
-- Bug 2182815 fixed by sosingha
-- Bug 2107543 fixed by sosingha
'decode(it2.supplier_site, null, null, decode(it2.buyer_id,'
|| '''-2'', ''.SITE:ICX_POR_OU_REQD'', '
-- Bug 2325999
|| 'decode(it2.row_type, ''PRICE'', '
|| ' decode(it2.rt_item_id, null, ''.SITE:ICX_POR_PRC_INVALID_SUP_PART'', '
|| ' decode(site.vendor_site_code,null, ''.SITE:ICX_POR_INVALID_SUPP_SITE'', null)), '
|| 'decode(site.vendor_site_code, null, '
||'''.SITE:ICX_POR_INVALID_SUPP_SITE'', null)))) '||
'FROM ' || p_table_name || ' it2 , po_vendor_sites_all site '||
'WHERE it1.rowid = it2.rowid ' ||
' AND it2.buyer_id IS NOT NULL ' ||
-- Bug#1975528: Vendor ID constraint added.
' AND site.vendor_id (+) = it2.supplier_id ' ||
' AND site.vendor_site_code (+) = UPPER(it2.supplier_site) ' ||
' AND site.org_id (+) = it2.buyer_id ' ||
') WHERE line_number BETWEEN :startrow AND :endrow ';
l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
'SET error_message = error_message ||( ' ||
'SELECT DISTINCT error_message || ' ||
'decode(site.purchasing_site_flag, ' ||'''N''' || ', ' ||
'''.SITE:ICX_POR_INVALID_SUPP_SITE_2'', null ) || ' ||
'decode(greatest(nvl(site.inactive_date ,sysdate+1), sysdate), sysdate, ' ||
'''.SITE:ICX_POR_INACTIVE_SUPP_SITE'', null) || ' ||
'decode(it2.supplier_site, null, null, ' ||
'decode(it2.row_type, ''PRICE'', ' ||
'decode(it2.rt_item_id, null, ''.SITE:ICX_POR_PRC_INVALID_SUP_PART'', ' ||
'decode(site.vendor_site_code, null, ''.SITE:ICX_POR_INVALID_SUPP_SITE'', ' ||
'null)), decode(site.vendor_site_code, null, ' ||
'''.SITE:ICX_POR_INVALID_SUPP_SITE'', null))) ' ||
'FROM ' || p_table_name || ' it2 , po_vendor_sites_all site '||
'WHERE it1.rowid = it2.rowid ' ||
' AND site.vendor_id (+) = it2.supplier_id ' ||
' AND site.vendor_site_code (+) = UPPER(it2.supplier_site) ' ||
') WHERE line_number BETWEEN :startrow AND :endrow ';
l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
'SET old_category_id = (SELECT cai.rt_category_id ' ||
'FROM icx_por_category_items cai, icx_por_categories_tl ca '||
'WHERE cai.rt_item_id = it1.rt_item_id '||
' AND ca.rt_category_id = cai.rt_category_id '||
' AND ca.type = 2 '||
' AND ca.language = ''' || p_language || ''') ' ||
'WHERE it1.system_action = ''UPDATE'' AND it1.rt_item_id IS NOT NULL ' ||
'AND line_number BETWEEN :startrow AND :endrow ';
l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
'SET it1.error_message = it1.error_message || ' ||
'decode(it1.error_message, it1.error_message, ' ||
'decode(it1.row_type, ''PRICE'', ''.PRICELIST:ICX_POR_DUP_PRICE_LIST1'', '||
'''ITEM_PRICE'', ''.SUPPLIER_PART_NUM:ICX_POR_DUP_SUPPLIER_PART'', ' ||
'''.SUPPLIER_PART_NUM:ICX_POR_DUP_SUPPLIER_PART'')) ' ||
'WHERE NOT it1.ROWID = ( ' ||
'SELECT MAX(it2.ROWID) ' ||
'FROM ' || p_table_name || ' it2 ' ||
'WHERE it2.supplier_part_num = it1.supplier_part_num ' ||
'AND it2.supplier_id = it1.supplier_id ' ||
'AND NVL(it2.buyer_id,-2) = NVL(it1.buyer_id,-2) ' ||
-- Bug#2352152 : Constraint for currency check
'AND NVL(it2.currency_code,''USD'') = NVL(it1.currency_code,''USD'') ' ||
'AND it2.row_type = it1.row_type )';
l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
'SET it1.row_type = ''PRICE'', it1.system_action = ''ADD'' '||
'WHERE NOT it1.ROWID = ( ' ||
'SELECT MIN(it2.ROWID) ' ||
'FROM ' || p_table_name || ' it2 ' ||
'WHERE it2.error_message IS NULL ' ||
'AND it2.supplier_part_num = it1.supplier_part_num ' ||
'AND it2.supplier_id = it1.supplier_id ' ||
'AND it2.row_type = it1.row_type ) ' ||
'AND it1.error_message IS NULL ';
l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
--Bug#1505751
'SET priceline_rowid = (SELECT DISTINCT pl.rowid FROM icx_por_price_list_lines pl ' ||
'WHERE it1.pricelist_id = pl.header_id ' ||
'AND it1.rt_item_id = pl.item_id ' ||
'AND pl.buyer_approval_status = ''APPROVED'' ' ||
'AND ((it1.row_type = ''ITEM_PRICE'' ' ||
'AND it1.system_action IN (''UPDATE'', ''TRANSLATE'')) ' ||
'OR (it1.row_type = ''PRICE'' ' ||
'AND it1.system_action IN (''ADD'',''DELETE'')))) ' ||
'WHERE line_number between :startrow AND :endrow';
'SELECT count(*) - count(distinct supplier_name || supplier_part_num) ' ||
'FROM ' || p_table_name;
'SELECT buy.organization_id ' ||
'FROM hr_all_organization_units buy, icx_por_uploader_subtable ipus, ' || p_table_name || ' it ' ||
'WHERE buy.business_group_id = :bus_group_id ' ||
'AND buy.name = ipus.operating_unit ' ||
'AND ipus.job_number = it.job_number ' ||
'AND ROWNUM < 2';
** Procedure to insert the SQL string and Bind variables into
** FND_LOG_MESSAGES table using the AOL API.
*/
PROCEDURE insert_fnd_log_messages(p_debug_bind_variables VARCHAR2,
p_debug_sql_string VARCHAR2) is
l_size NUMBER := 2000;
/*Insert the Debug SQL string */
WHILE l_start < l_sql_string_length LOOP
l_start := l_start + l_size;
/*Insert the Debug Bind Variable string */
WHILE l_start < l_bind_string_length LOOP
l_start := l_start + l_size;
END insert_fnd_log_messages;