APPS.ICX_POR_SCHEMA_UPGRADE SQL Statements

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

Line: 20

  insert into icx_cat_categories_tl
  (
     RT_CATEGORY_ID,
     LANGUAGE,
     SOURCE_LANG,
     CATEGORY_NAME,
     DESCRIPTION,
     TYPE,
     KEY,
     TITLE,
     ITEM_COUNT,
     CREATED_BY,
     CREATION_DATE,
     LAST_UPDATED_BY,
     LAST_UPDATE_DATE,
     LAST_UPDATE_LOGIN,
     REQUEST_ID,
     PROGRAM_APPLICATION_ID,
     PROGRAM_ID,
     PROGRAM_UPDATE_DATE,
     UPPER_CATEGORY_NAME,
     REBUILD_FLAG,
     section_map,
     UPPER_KEY
  )
  (
   select
     ct1.RT_CATEGORY_ID,
     ct1.LANGUAGE,
     ct1.SOURCE_LANG,
     ct1.CATEGORY_NAME,
     ct1.DESCRIPTION,
     ct1.TYPE,
     ct1.KEY,
     ct1.TITLE,
     ct1.ITEM_COUNT,
     ct1.CREATED_BY,
     ct1.CREATION_DATE,
     ct1.LAST_UPDATED_BY,
     ct1.LAST_UPDATE_DATE,
     ct1.LAST_UPDATE_LOGIN,
     ct1.BATCH_JOB_NUM, -- batch_job_num is request id
     ct1.PROGRAM_APPLICATION_ID,
     ct1.PROGRAM_ID,
     ct1.PROGRAM_UPDATE_DATE,
     ct1.UPPER_CATEGORY_NAME,
     REBUILD_FLAG,
     rpad('0', 300, '0'),
     UPPER_KEY
   from
     icx_por_categories_tl ct1
   where ct1.rt_category_id > 0
   and not exists (select null from icx_cat_categories_tl ct2
                   where ct1.key = ct2.key
                   and ct1.type = ct2.type
                   and   ct1.language = ct2.language)
  );

Line: 82

  insert into icx_cat_descriptors_tl
  (
      RT_DESCRIPTOR_ID,
      LANGUAGE,
      SOURCE_LANG,
      TITLE,
      DESCRIPTOR_NAME,
      DESCRIPTION,
      RT_CATEGORY_ID,
      TYPE,
      -- Bug 3092172 fixed by sosingha
      -- ignore the hidden column as upgrade will fail for pre FPE customers due to non availability of this column. We wont honour this column and hence no need to copy the value for this.
      -- HIDDEN,
      SEQUENCE,
      KEY,
      DEFAULTVALUE,
      MULTI_VALUE_TYPE,
      MULTI_VALUE_KEY,
      REQUIRED,
      REFINABLE,
      SEARCHABLE,
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_ID,
      PROGRAM_UPDATE_DATE,
      SEARCH_RESULTS_VISIBLE,
      ITEM_DETAIL_VISIBLE,
      REBUILD_FLAG,
      CLASS
  )
  (
    select
      des1.RT_DESCRIPTOR_ID,
      des1.LANGUAGE,
      des1.SOURCE_LANG,
      des1.TITLE,
      des1.DESCRIPTOR_NAME,
      des1.DESCRIPTION,
      des1.RT_CATEGORY_ID,
      des1.TYPE,
      -- Bug 3092172 fixed by sosingha
      -- des1.HIDDEN,
      des1.SEQUENCE,
      des1.KEY,
      des1.DEFAULTVALUE,
      des1.MULTI_VALUE_TYPE,
      des1.MULTI_VALUE_KEY,
      des1.REQUIRED,
      des1.REFINABLE,
      des1.SEARCHABLE,
      des1.CREATED_BY,
      des1.CREATION_DATE,
      des1.LAST_UPDATED_BY,
      des1.LAST_UPDATE_DATE,
      des1.LAST_UPDATE_LOGIN,
      des1.BATCH_JOB_NUM,
      des1.PROGRAM_APPLICATION_ID,
      des1.PROGRAM_ID,
      des1.PROGRAM_UPDATE_DATE,
      des1.SEARCH_RESULTS_VISIBLE,
      des1.ITEM_DETAIL_VISIBLE,
      des1.REBUILD_FLAG,
      decode(des1.rt_category_id, 0 , 'ICX_BASE_ATTR', 'ICX_CAT_ATTR')
    from
      icx_por_descriptors_tl des1
    where des1.rt_descriptor_id > 100
    and not exists (select null from icx_cat_descriptors_tl des2
                   where des1.rt_descriptor_id = des2.rt_descriptor_id
                   and   des1.language = des2.language)
    );

Line: 161

  insert into icx_cat_browse_trees (PARENT_CATEGORY_ID,
  CHILD_CATEGORY_ID, LAST_UPDATE_LOGIN ,
  LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE)
  select  toc1.RT_CATEGORY_ID, toc1.CHILD, 1, toc1.LAST_UPDATED_BY,
  toc1.LAST_UPDATE_DATE, toc1.CREATED_BY, toc1.CREATION_DATE
  from icx_por_table_of_contents_tl toc1
  where not exists (select null from icx_cat_browse_trees toc2
                    where toc2.PARENT_CATEGORY_ID = toc1.RT_CATEGORY_ID
                    and   toc2.CHILD_CATEGORY_ID = toc1.CHILD);

Line: 199

    select  distinct RT_CATEGORY_ID
    from   icx_cat_descriptors_tl;

Line: 209

    l_category_ids.DELETE;