[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TYPE t_last_updated_by IS TABLE OF ibe_ct_imedia_search.LAST_UPDATED_BY%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_last_updated_date IS TABLE OF
ibe_ct_imedia_search.LAST_UPDATE_DATE%TYPE INDEX BY BINARY_INTEGER;
TYPE t_last_update_login IS TABLE OF
ibe_ct_imedia_search.LAST_UPDATE_LOGIN%TYPE INDEX BY BINARY_INTEGER;
l_last_updated_by_tbl t_last_updated_by;
l_last_updated_date_tbl t_last_updated_date;
l_last_update_login_tbl t_last_update_login;
select MSITE_ID,MSITE_ROOT_SECTION_ID
from ibe_msites_b
where msite_id <> 1 and site_type = 'I';
SELECT distinct s.section_id
FROM IBE_DSP_MSITE_SCT_SECTS mss, IBE_DSP_SECTIONS_B s
WHERE mss.parent_section_id = l_msite_root_section_id
AND mss.mini_site_id = l_minisite_id
AND s.section_id = mss.child_section_id
AND s.section_type_code = 'N'
-- Fix bug
AND s.status_code = 'PUBLISHED'
AND NVL(mss.start_date_active, SYSDATE) <= SYSDATE
AND NVL(mss.end_date_active, SYSDATE) >= SYSDATE
AND NVL(s.start_date_active, SYSDATE) <= SYSDATE
AND NVL(s.end_date_active, SYSDATE) >= SYSDATE
ORDER BY s.section_id;
select /*+ first_rows */ inventory_item_id
from (
select section_item_id, idsi.inventory_item_id
from ibe_dsp_section_items idsi
where section_id IN
(
select child_section_id
from ibe_dsp_msite_sct_sects s1
where mini_site_id = l_c_master_msite_id
and NOT EXISTS
(
select child_section_id
from ibe_dsp_msite_sct_sects s2
where mini_site_id = l_c_msite_id
and s2.child_section_id=s1.child_section_id
)
CONNECT BY PRIOR child_section_id = parent_section_id
and PRIOR mini_site_id = l_c_master_msite_id
and mini_site_id = l_c_master_msite_id
START WITH child_section_id = l_c_root_section_id
and mini_site_id = l_c_master_msite_id
)
and NOT EXISTS
(
select inventory_item_id
from ibe_dsp_section_items i1, ibe_dsp_msite_sct_items i2
where i1.section_item_id = i2.section_item_id
and i2.mini_site_id = l_c_msite_id
and i1.inventory_item_id = idsi.inventory_item_id
)
union
select /*ordered use_nl(s3,i2) */ section_item_id, i2.inventory_item_id
from ibe_dsp_msite_sct_sects s3, ibe_dsp_section_items i2
where i2.section_id = s3.child_section_id
and s3.mini_site_id = l_c_msite_id
and NOT EXISTS
(
select null
from ibe_dsp_msite_sct_items i3
where mini_site_id = l_c_msite_id
and i3.section_item_id = i2.section_item_id
)
);
SELECT msite_id, msite_root_section_id
FROM ibe_msites_b
WHERE sysdate BETWEEN start_date_active AND NVL(end_date_active, sysdate)
AND master_msite_flag = 'N' AND site_type = 'I';
select application_short_name
into l_application_short_name
from fnd_application
where application_id = 671 ;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Inserting into IBE_SECTION_SEARCH_PART' );
insert into IBE_SECTION_SEARCH_PART
(inventory_item_id
, organization_id
, section_id
, minisite_id
, OBJECT_VERSION_NUMBER
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN)
select
distinct f.inventory_item_id,f.organization_id ,l_toplevel_section_tbl(l_count2),
--sectionIdData.section_id,
--distinct g.inventory_item_id,g.organization_id ,sectionIdData.section_id,
v_CompoundData.MSITE_ID,
1,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.conc_login_id
from ibe_dsp_section_items f ,
ibe_dsp_sections_b s
where (f.end_date_active > sysdate or f.end_date_active is null)
and f.start_date_active < sysdate
and f.section_id = s.section_id
and s.status_code = 'PUBLISHED' and f.section_id in
( SELECT mss.child_section_id
FROM IBE_DSP_MSITE_SCT_SECTS mss
START WITH mss.child_section_id = l_toplevel_section_tbl(l_count2) --sectionIdData.section_id
and mss.mini_site_id = v_CompoundData.MSITE_ID
CONNECT BY PRIOR mss.child_section_id = mss.parent_section_id
AND mss.mini_site_id = v_CompoundData.MSITE_ID
);
select distinct g.inventory_item_id,g.organization_id ,sectionIdData.section_id,
v_CompoundData.MSITE_ID,
1,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.conc_login_id
from ibe_dsp_section_items f ,
mtl_system_items_b g,
ibe_dsp_sections_b s
where f.inventory_item_id = g.inventory_item_id
and (f.end_date_active > sysdate or f.end_date_active is null)
and f.start_date_active < sysdate
and f.section_id = s.section_id
and s.status_code = 'PUBLISHED'
and f.section_id in
( SELECT mss.child_section_id
FROM IBE_DSP_MSITE_SCT_SECTS mss
START WITH mss.child_section_id = sectionIdData.section_id
and mss.mini_site_id = v_CompoundData.MSITE_ID
CONNECT BY PRIOR mss.child_section_id = mss.parent_section_id
AND mss.mini_site_id =v_CompoundData.MSITE_ID
);
select msite_id into l_master_msite_id
from ibe_msites_b
where UPPER(master_msite_flag) = 'Y' and site_type = 'I';
delete from ibe_section_search_part
where inventory_item_id = r2.inventory_item_id
and minisite_id = x_msite_ids(i).msite_id;
INSERT INTO ibe_ct_imedia_search
(IBE_CT_IMEDIA_SEARCH_ID,
INVENTORY_ITEM_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CATEGORY_ID,
ORGANIZATION_ID,
LANGUAGE,
DESCRIPTION,
LONG_DESCRIPTION,
CATEGORY_SET_ID,
WEB_STATUS,
INDEXED_SEARCH)
select ibe_ct_imedia_search_s1.nextval,
t.INVENTORY_ITEM_ID,t.OBJECT_VERSION_NUMBER,t.CREATED_BY,t.CREATION_DATE,t.LAST_UPDATED_BY,
t.LAST_UPDATE_DATE,t.LAST_UPDATE_LOGIN,t.CATEGORY_ID,t.ORGANIZATION_ID,t.LANGUAGE,
t.DESCRIPTION,t.LONG_DESCRIPTION,t.CATEGORY_SET_ID,t.WEB_STATUS,
ibe_search_setup_pvt.WriteToLob(t.DESCRIPTION, t.LONG_DESCRIPTION, t.concatenated_segments)
from
(select distinct b.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,1 OBJECT_VERSION_NUMBER,
FND_GLOBAL.user_id CREATED_BY,SYSDATE CREATION_DATE,
FND_GLOBAL.user_id LAST_UPDATED_BY, SYSDATE LAST_UPDATE_DATE,
FND_GLOBAL.conc_login_id LAST_UPDATE_LOGIN, c.CATEGORY_ID,
b.ORGANIZATION_ID,b.LANGUAGE,b.DESCRIPTION, b.LONG_DESCRIPTION,
a.concatenated_segments,c.CATEGORY_SET_ID,a.WEB_STATUS
from mtl_system_items_b_kfv a, mtl_system_items_tl b, mtl_item_categories c, ibe_dsp_section_items d
where d.inventory_item_id = a.inventory_item_id
and d.organization_id = a.organization_id
and a.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID
and a.organization_id = b.organization_id
and a.web_status = 'PUBLISHED'
and a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
and a.organization_id = c.organization_id
and not exists ( select r.INVENTORY_ITEM_ID ,r.ORGANIZATION_ID
from IBE_CT_IMEDIA_SEARCH r
where r.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID
and r.ORGANIZATION_ID = b.ORGANIZATION_ID)
)t ;
FND_FILE.PUT_LINE(FND_FILE.LOG,' the loop index after insert is = ' || l_tmp );
select count(*)
into l_index_exists
from user_indexes
where index_name = 'IBE_CT_IMEDIA_SEARCH_IM';