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.ICX_POR_ITEM_UPLOAD_VALIDATE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 48


    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';

Line: 64


    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';

Line: 110


  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';

Line: 184


  l_sql_string := 'SELECT vendor_id '  ||
                       'FROM po_vendors supp, ' || p_table_name || ' it2 ' ||
                       'WHERE supp.vendor_name = it2.supplier_name ';

Line: 217


    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;

Line: 248


  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 ';

Line: 310


  SELECT nvl(multi_org_flag, 'N') INTO l_chk_multi_org
  FROM fnd_product_groups ;

Line: 317


    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 ';

Line: 346


    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 ';

Line: 387


  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 ';

Line: 431


  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 )';

Line: 466


  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 ';

Line: 506


  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';

Line: 581


  'SELECT count(*) - count(distinct supplier_name || supplier_part_num) ' ||
  'FROM ' || p_table_name;

Line: 609


  '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';

Line: 672


 ** 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;

Line: 687


  /*Insert the Debug SQL string */
   WHILE l_start < l_sql_string_length LOOP
      l_start := l_start + l_size;

Line: 697


   /*Insert the Debug Bind Variable string */
   WHILE l_start < l_bind_string_length LOOP
      l_start := l_start + l_size;

Line: 706


END insert_fnd_log_messages;