The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 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 |
|___________________________________________________________________________|*/
G_PKG_NAME CONSTANT VARCHAR2(30) := 'ibe_search_setup_pvt';
cursor old_multi_lexer is select pre_name from ctxsys.ctx_preferences
where pre_name = 'IBE_GLOBAL_LEXER';
cursor old_sub_lexers is select pre_name from ctxsys.ctx_preferences
where pre_name like 'IBE_LOBS__LEXER';
select count(*)
into l_index_exists
from user_indexes
where index_name = 'IBE_CT_IMEDIA_SEARCH_IM';
select application_short_name
into l_application_short_name
from fnd_application
where application_id = 671 ;
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));
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');
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'));
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);
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');
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'));
select count(*) into l_fuzzy_count
from CTX_PREFERENCES
where
pre_name = 'IBE_STEM_FUZZY_PREF' ;