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_CTX_SQL_PKG SQL Statements

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

Line: 12


 - pInsertSQL - Table containing SQL for processing new items for the loader
 - pUpdateSQL - Table containing SQL for processing existing items
   (Existing means it exists in icx_por_items_tl)
*/
PROCEDURE build_ctx_sql(pCategoryId IN NUMBER,
  pDescriptors IN DescriptorInfoTab, pWhereClause IN VARCHAR2,
  pLanguage IN VARCHAR2, pMaxLength IN NUMBER,
  pInsertSQL OUT NOCOPY SQLTab, pUpdateSQL OUT NOCOPY SQLTab) IS
  i PLS_INTEGER;

Line: 21


  v_insert_sql VARCHAR2(4000) := null;

Line: 22


  v_update_sql VARCHAR2(4000) := null;

Line: 24


  v_insert_suffix VARCHAR2(4000) := null;

Line: 25


  v_update_suffix VARCHAR2(4000) := null;

Line: 28


  v_insert_prefix VARCHAR2(4000) := null;

Line: 29


  v_update_prefix VARCHAR2(4000) := null;

Line: 30


  v_extra_update_suffix VARCHAR2(4000) := null;

Line: 36


  pInsertSQL.DELETE;

Line: 37


  pUpdateSQL.DELETE;

Line: 39


  v_insert_prefix := 'INSERT INTO icx_cat_items_ctx_tlp (rt_item_id, language, sequence, ctx_desc) SELECT :p_item_id, :p_language, :p_sequence, null';

Line: 42


  v_insert_suffix := ' FROM icx_cat_items_gt WHERE rowid = :p_rowid' ||
    ' AND :action_name = :p_system_action ';

Line: 48


    v_insert_suffix := v_insert_suffix || 'AND :p_language = :language_array';

Line: 51


  v_insert_sql := v_insert_prefix;

Line: 53


  v_update_prefix := 'INSERT INTO icx_cat_items_ctx_tlp (rt_item_id, language, sequence, ctx_desc) SELECT tl.rt_item_id, tl.language, :p_sequence, null';

Line: 56


    v_insert_suffix := v_insert_suffix ||
      ' AND :current_category_id = :p_category_id';

Line: 58


    v_update_suffix := ' FROM icx_cat_ext_items_tlp tl WHERE ' ||
      -- The following two are for use during bulk loading
      -- For regular use just bind in the same values for current_category_id
      -- and p_category_id, as well as action_name and p_system_action
      ' :current_category_id = :p_category_id ' ||
      'AND :action_name = :p_system_action ';

Line: 68


      v_update_suffix := v_update_suffix ||
        ' AND tl.language = :p_language AND :p_language = :language_array';

Line: 72


    v_extra_update_suffix := v_update_suffix;

Line: 74


    v_update_suffix := ' FROM icx_cat_items_tlp tl ' ||
      -- The following is for use during bulk loading
      -- For regular use just bind in the same values for action_name
      -- and p_system_action
      'WHERE :action_name = :p_system_action ';

Line: 83


      v_update_suffix := v_update_suffix ||
      ' AND tl.language = :p_language AND :p_language = :language_array';

Line: 88


    v_extra_update_suffix := ' FROM icx_cat_items_tlp tl ' ||
      -- The following is for use during bulk loading
      -- For regular use just bind in the same values for action_name
      -- and p_system_action
      'WHERE :action_name = :p_system_action ';

Line: 97


      v_extra_update_suffix := v_extra_update_suffix ||
      ' AND tl.language = :p_language AND :p_language = :language_array';

Line: 104


    v_update_suffix := v_update_suffix || ' ' || pWhereClause;

Line: 105


    v_extra_update_suffix := v_extra_update_suffix || ' ' || pWhereClause;

Line: 108


  v_update_sql := v_update_prefix;

Line: 112


  v_insert_sql := v_insert_sql || ' || '''' || language || ''''';

Line: 113


  v_update_sql := v_update_sql || ' || '''' || tl.language || ''''';

Line: 134


        v_insert_sql := v_insert_sql || ' || '''' || to_char(:p_supplier_id) || ''SUPPLIER INTERNALleaf'' || :p_category_id || '''' || :p_category_name || ''''';

Line: 135


        v_update_sql := v_update_sql || ' || '''' || to_char(tl.supplier_id) || '''' || ';

Line: 136


        v_update_sql := v_update_sql || ' decode(tl.item_source_type,''BOTH'', ''SUPPLIER INTERNAL'', tl.item_source_type)||''leaf'' || tl.primary_category_id || '''' || tl.primary_category_name || ''''';

Line: 147


        pInsertSQL(v_sql_count) := v_insert_sql || v_insert_suffix;

Line: 148


        v_insert_sql := v_insert_prefix;

Line: 151


        pUpdateSQL(v_sql_count) := v_update_sql || v_extra_update_suffix;

Line: 152


        v_update_sql := v_update_prefix;

Line: 155


        pInsertSQL(v_sql_count) := v_insert_sql || v_insert_suffix;

Line: 156


        v_insert_sql := v_insert_prefix;

Line: 157


        pUpdateSQL(v_sql_count) := v_update_sql || v_update_suffix;

Line: 158


        v_update_sql := v_update_prefix;

Line: 167


    v_insert_sql := v_insert_sql || ' || ''<' ||
    -- to_char(v_desc.section_tag) || '>'' || ' || v_stored_in_column ||
       to_char(v_desc.section_tag) || '>'' || ' || 'replace(replace('|| v_stored_in_column || ',' || '''<''' ||  ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
       ' || ''''';

Line: 172


    v_update_sql := v_update_sql || ' || ''<' ||
       -- to_char(v_desc.section_tag) || '>'' || ' || v_upd_stored_in_column ||
       to_char(v_desc.section_tag) || '>'' || ' || 'replace(replace('|| v_upd_stored_in_column || ',' || '''<''' ||  ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
       ' || ''''';

Line: 182


      pInsertSQL(v_sql_count) := v_insert_sql || v_insert_suffix;

Line: 186


        pUpdateSQL(v_sql_count) := v_update_sql || v_extra_update_suffix;

Line: 188


        pUpdateSQL(v_sql_count) := v_update_sql || v_update_suffix;

Line: 199


        v_insert_sql := v_insert_sql || ' || '''' || to_char(:p_supplier_id) || ''SUPPLIER INTERNALleaf'' || :p_category_id || '''' || :p_category_name || ''''';

Line: 200


        v_update_sql := v_update_sql || ' || '''' || to_char(tl.supplier_id) || '''' || ';

Line: 201


        v_update_sql := v_update_sql ||' decode(tl.item_source_type,''BOTH'', ''SUPPLIER INTERNAL'', tl.item_source_type)||''leaf'' || tl.primary_category_id || '''' || tl.primary_category_name || ''''';

Line: 205


      pInsertSQL(v_sql_count) := v_insert_sql || v_insert_suffix;

Line: 206


      pUpdateSQL(v_sql_count) := v_update_sql || v_update_suffix;

Line: 217


    pInsertSQL(v_sql_count) := v_insert_prefix || ' || '''' ' ||
      v_insert_suffix;

Line: 219


    pUpdateSQL(v_sql_count) := v_update_prefix || ' || '''' ' ||
      v_update_suffix;

Line: 222


    pInsertSQL(v_sql_count) := v_insert_prefix || ' || '''' ' ||
      v_insert_suffix;

Line: 224


    pUpdateSQL(v_sql_count) := v_update_prefix || ' || '''' ' ||
      v_update_suffix;

Line: 243


 - pInsertSQL - Table containing SQL for processing new items for the loader
 - pUpdateSQL - Table containing SQL for processing existing items
   (Existing means it exists in icx_por_items_tl)
*/
PROCEDURE build_ctx_sql(pCategoryId IN NUMBER, pWhereClause IN VARCHAR2,
  pLanguage IN VARCHAR2, pMaxLength IN NUMBER,
  pInsertSQL OUT NOCOPY SQLTab, pUpdateSQL OUT NOCOPY SQLTab) IS
  vDescInfoTab DescriptorInfoTab;

Line: 256


    SELECT rt_descriptor_id, key, type, section_tag, searchable,
           stored_in_table, stored_in_column
    FROM icx_cat_descriptors_tl
    WHERE rt_category_id = p_category_id
    AND language = p_language
    AND stored_in_table <> 'ICX_CAT_ITEM_PRICES'
    order by rt_descriptor_id;

Line: 267


    SELECT language_code INTO vLanguage
    FROM fnd_languages WHERE installed_flag = 'B';

Line: 306


      pMaxLength, pInsertSQL, pUpdateSQL);

Line: 320


 - pInsertSQL - Table containing SQL for processing new items for the loader
 - pUpdateSQL - Table containing SQL for processing existing items
   (Existing means it exists in icx_por_items_tl)
*/
PROCEDURE build_ctx_sql(pCategoryId IN NUMBER, pWhereClause IN VARCHAR2,
  pLanguage IN VARCHAR2, pInsertSQL OUT NOCOPY SQLTab, pUpdateSQL OUT NOCOPY SQLTab) IS
BEGIN
  build_ctx_sql(pCategoryId, pWhereClause, pLanguage, DEFAULT_MAX_LENGTH,
    pInsertSQL, pUpdateSQL);

Line: 336


 - pInsertSQL - Table containing SQL for processing new items for the loader
 - pUpdateSQL - Table containing SQL for processing existing items
   (Existing means it exists in icx_por_items_tl)
*/
PROCEDURE build_ctx_sql(pCategoryId IN NUMBER, pWhereClause IN VARCHAR2,
  pInsertSQL OUT NOCOPY SQLTab, pUpdateSQL OUT NOCOPY SQLTab) IS
BEGIN
  build_ctx_sql(pCategoryId, pWhereClause, null, DEFAULT_MAX_LENGTH,
    pInsertSQL, pUpdateSQL);