APPS.IBE_SEARCH_SETUP_PVT SQL Statements

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

Line: 10

 |             Body file for the iStore Search Insert Concurrent Program     |
 |             Modification is not recommended.                              |
 |                                                                           |
 |   Description                                                             |
 |                                                                           |
 |                                                                           |
 |   SYTONG - bug fix 2550147 -- change the query to check if index exists   |
 |   SYTONG - bug fix 2926852 -- use bulk fetch and insert                   |
 |   abhandar - bug fix 3168087 -catalog search performance                  |
 |   madesai - bug fix 3871664 - GSCC warning fix - remove apps reference    |
 |   madesai - bug fix 4585787 - Remove KOREAN_LEXER for 10Gr12              |
 |   madesai - bug fix 4674288 -explicitly remove korean lobs lexer          |
 |   mgiridha - bug 6924793    - changes to FP 11510 bug 6777665             |
 |   amahsehw - bug 12980419 - UNABLE TO SEARCH PRODUCT USING PRODUCT NUMBER |
 |   ytian    - bug12980419/13252575 -- Changed  Fuzzy score to 1            |
 |   scnagara - bug13935780  -- Removed CTXSYS schema reference              |
 |___________________________________________________________________________|*/

G_PKG_NAME CONSTANT VARCHAR2(30) := 'ibe_search_setup_pvt';

Line: 159

        cursor old_multi_lexer is select pre_name from ctx_preferences
                            where  pre_name = 'IBE_GLOBAL_LEXER';

Line: 162

        cursor old_sub_lexers is select pre_name from ctx_preferences
                           where  pre_name like 'IBE_LOBS__LEXER';

Line: 167

			  select ORACLE_USERNAME
			  from   fnd_oracle_userid
			  where  ORACLE_ID = l_oracle_id;

Line: 190

select application_short_name
into   l_application_short_name
from   fnd_application
where  application_id = 671 ;

Line: 236

 select count(*)
 into l_index_exists
 from user_indexes
 where index_name = 'IBE_CT_IMEDIA_SEARCH_IM';

Line: 265

select application_short_name
into   l_application_short_name
from   fnd_application
where  application_id = 671 ;

Line: 330

    INSERT INTO IBE_CT_IMEDIA_SEARCH(
     IBE_CT_IMEDIA_SEARCH_ID,OBJECT_VERSION_NUMBER,
     CREATED_BY,CREATION_DATE,
     LAST_UPDATED_BY,LAST_UPDATE_DATE,
     LAST_UPDATE_LOGIN,CATEGORY_ID,
     ORGANIZATION_ID,CATEGORY_SET_ID,
     INVENTORY_ITEM_ID,LANGUAGE,
     DESCRIPTION,LONG_DESCRIPTION,
     INDEXED_SEARCH,WEB_STATUS,
     SECURITY_GROUP_ID)
   (SELECT
    ibe_ct_imedia_search_s1.nextval,
    1,
    FND_GLOBAL.user_id,
    SYSDATE,
    FND_GLOBAL.user_id,
    SYSDATE,
    FND_GLOBAL.conc_login_id,
    c.CATEGORY_ID,
    b.ORGANIZATION_ID,
    c.CATEGORY_SET_ID,--bug 6924793
    b.INVENTORY_ITEM_ID,
    b.LANGUAGE,
    b.DESCRIPTION,
    b.LONG_DESCRIPTION,
    ibe_search_setup_pvt.WriteToLob(b.DESCRIPTION,b.LONG_DESCRIPTION,a.concatenated_segments),
    a.web_status,
    null
   FROM mtl_system_items_b_kfv a ,mtl_system_items_tl b , mtl_item_categories c
   WHERE b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID and
         b.organization_id  = c.organization_id and
         c.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and
         c.organization_id  = a.organization_id and
         exists (select 1
                 from oe_system_parameters_all osp
                 where osp.master_organization_id = b.organization_id));

Line: 371

	INSERT INTO IBE_CT_IMEDIA_SEARCH(
     IBE_CT_IMEDIA_SEARCH_ID,OBJECT_VERSION_NUMBER,
     CREATED_BY,CREATION_DATE,
     LAST_UPDATED_BY,LAST_UPDATE_DATE,
     LAST_UPDATE_LOGIN,CATEGORY_ID,
     ORGANIZATION_ID,CATEGORY_SET_ID,
     INVENTORY_ITEM_ID,LANGUAGE,
     DESCRIPTION,LONG_DESCRIPTION,
     INDEXED_SEARCH,WEB_STATUS,
     SECURITY_GROUP_ID)
     (SELECT ibe_ct_imedia_search_s1.nextval,
       1,
       FND_GLOBAL.user_id,
       SYSDATE,
       FND_GLOBAL.user_id,
       SYSDATE,
       FND_GLOBAL.conc_login_id,
       c.CATEGORY_ID,
       b.ORGANIZATION_ID,
       c.CATEGORY_SET_ID,
       b.INVENTORY_ITEM_ID,
       b.LANGUAGE,
       b.DESCRIPTION,
       b.LONG_DESCRIPTION,
       ibe_search_setup_pvt.WriteToLob(b.DESCRIPTION,b.LONG_DESCRIPTION,a.concatenated_segments),
	   a.web_status,
       null
       FROM  mtl_system_items_b_kfv a,mtl_system_items_tl b,mtl_item_categories c
       WHERE b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID and b.organization_id   = c.organization_id
         and c.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and c.organization_id   = a.organization_id
         and exists ( select 1
                   from oe_system_parameters_all osp
                where osp.master_organization_id = b.organization_id)
         and a.web_status='PUBLISHED');

Line: 410

	INSERT INTO IBE_CT_IMEDIA_SEARCH(
     IBE_CT_IMEDIA_SEARCH_ID,OBJECT_VERSION_NUMBER,
     CREATED_BY,CREATION_DATE,
     LAST_UPDATED_BY,LAST_UPDATE_DATE,
     LAST_UPDATE_LOGIN,CATEGORY_ID,
     ORGANIZATION_ID,CATEGORY_SET_ID,
     INVENTORY_ITEM_ID,LANGUAGE,
     DESCRIPTION,LONG_DESCRIPTION,
     INDEXED_SEARCH,WEB_STATUS,
     SECURITY_GROUP_ID)
     (SELECT  ibe_ct_imedia_search_s1.nextval,
       1,
       FND_GLOBAL.user_id,
       SYSDATE,
       FND_GLOBAL.user_id,
       SYSDATE,
       FND_GLOBAL.conc_login_id,
       c.CATEGORY_ID,
       b.ORGANIZATION_ID,
       c.CATEGORY_SET_ID,
       b.INVENTORY_ITEM_ID,
       b.LANGUAGE,
       b.DESCRIPTION,
       b.LONG_DESCRIPTION,
       ibe_search_setup_pvt.WriteToLob(b.DESCRIPTION,b.LONG_DESCRIPTION,a.concatenated_segments),
       a.web_status,
       null
       FROM  mtl_system_items_b_kfv a ,mtl_system_items_tl b , mtl_item_categories c
       WHERE b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID and b.organization_id   = c.organization_id
         and c.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and c.organization_id   = a.organization_id
         and exists ( select 1
                   from oe_system_parameters_all osp
                where osp.master_organization_id = b.organization_id)
         and a.web_status in ('PUBLISHED','UNPUBLISHED'));

Line: 449

	INSERT INTO IBE_CT_IMEDIA_SEARCH(
     IBE_CT_IMEDIA_SEARCH_ID,OBJECT_VERSION_NUMBER,
     CREATED_BY,CREATION_DATE,
     LAST_UPDATED_BY,LAST_UPDATE_DATE,
     LAST_UPDATE_LOGIN,CATEGORY_ID,
     ORGANIZATION_ID,CATEGORY_SET_ID,
     INVENTORY_ITEM_ID,LANGUAGE,
     DESCRIPTION,LONG_DESCRIPTION,
     INDEXED_SEARCH,WEB_STATUS,
     SECURITY_GROUP_ID)
     (SELECT  ibe_ct_imedia_search_s1.nextval,
       1,
       FND_GLOBAL.user_id,
       SYSDATE,
       FND_GLOBAL.user_id,
       SYSDATE,
       FND_GLOBAL.conc_login_id,
       c.CATEGORY_ID,
       b.ORGANIZATION_ID,
       c.CATEGORY_SET_ID,
       b.INVENTORY_ITEM_ID,
       b.LANGUAGE,
       b.DESCRIPTION,
       b.LONG_DESCRIPTION,
       ibe_search_setup_pvt.WriteToLob(b.DESCRIPTION,b.LONG_DESCRIPTION,a.concatenated_segments),
       a.web_status,
	   null
     FROM mtl_system_items_b_kfv a ,mtl_system_items_tl b , mtl_item_categories c
     WHERE b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID and b.organization_id   = c.organization_id
       and c.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and c.organization_id   = a.organization_id
       and exists (select 1
                   from oe_system_parameters_all osp
                   where osp.master_organization_id = b.organization_id)
       and c.category_set_id = l_search_category_set);

Line: 488

	INSERT INTO IBE_CT_IMEDIA_SEARCH(
     IBE_CT_IMEDIA_SEARCH_ID,OBJECT_VERSION_NUMBER,
     CREATED_BY,CREATION_DATE,
     LAST_UPDATED_BY,LAST_UPDATE_DATE,
     LAST_UPDATE_LOGIN,CATEGORY_ID,
     ORGANIZATION_ID,CATEGORY_SET_ID,
     INVENTORY_ITEM_ID,LANGUAGE,
     DESCRIPTION,LONG_DESCRIPTION,
     INDEXED_SEARCH,WEB_STATUS,
     SECURITY_GROUP_ID)
   (SELECT  ibe_ct_imedia_search_s1.nextval,
      1,
      FND_GLOBAL.user_id,
      SYSDATE,
      FND_GLOBAL.user_id,
      SYSDATE,
      FND_GLOBAL.conc_login_id,
      c.CATEGORY_ID,
      b.ORGANIZATION_ID,
      c.CATEGORY_SET_ID,
      b.INVENTORY_ITEM_ID,
      b.LANGUAGE,
      b.DESCRIPTION,
      b.LONG_DESCRIPTION,
      ibe_search_setup_pvt.WriteToLob(b.DESCRIPTION,b.LONG_DESCRIPTION,a.concatenated_segments),
      a.web_status,
	  null
     FROM  mtl_system_items_b_kfv a ,mtl_system_items_tl b , mtl_item_categories c
     WHERE b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID and b.organization_id   = c.organization_id
       and c.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and c.organization_id   = a.organization_id
       and exists (select 1
                   from oe_system_parameters_all osp
                   where osp.master_organization_id = b.organization_id)
       and c.category_set_id = l_search_category_set
       and a.web_status = 'PUBLISHED');

Line: 528

	INSERT INTO IBE_CT_IMEDIA_SEARCH(
     IBE_CT_IMEDIA_SEARCH_ID,OBJECT_VERSION_NUMBER,
     CREATED_BY,CREATION_DATE,
     LAST_UPDATED_BY,LAST_UPDATE_DATE,
     LAST_UPDATE_LOGIN,CATEGORY_ID,
     ORGANIZATION_ID,CATEGORY_SET_ID,
     INVENTORY_ITEM_ID,LANGUAGE,
     DESCRIPTION,LONG_DESCRIPTION,
     INDEXED_SEARCH,WEB_STATUS,
     SECURITY_GROUP_ID)
   (SELECT  ibe_ct_imedia_search_s1.nextval,
     1,
     FND_GLOBAL.user_id,
     SYSDATE,
     FND_GLOBAL.user_id,
     SYSDATE,
     FND_GLOBAL.conc_login_id,
     c.CATEGORY_ID,
     b.ORGANIZATION_ID,
     c.CATEGORY_SET_ID,
     b.INVENTORY_ITEM_ID,
     b.LANGUAGE,
     b.DESCRIPTION,
     b.LONG_DESCRIPTION,
     ibe_search_setup_pvt.WriteToLob(b.DESCRIPTION,b.LONG_DESCRIPTION,a.concatenated_segments),
     a.web_status,
     null
    FROM mtl_system_items_b_kfv a ,mtl_system_items_tl b , mtl_item_categories c
    WHERE b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID and b.organization_id   = c.organization_id
      and c.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and c.organization_id   = a.organization_id
      and exists (select 1
                  from oe_system_parameters_all osp
                  where osp.master_organization_id = b.organization_id)
      and c.category_set_id = l_search_category_set
      and a.web_status IN ('PUBLISHED', 'UNPUBLISHED'));

Line: 630

     select count(*) into l_fuzzy_count
     from CTX_PREFERENCES
     where
      pre_name = 'IBE_STEM_FUZZY_PREF' ;