1 package body IBE_SearchUpdate_PVT as
2 /* $Header: IBEVCSUB.pls 120.7 2005/10/12 14:43:41 madesai ship $ */
3
4 --+=======================================================================
5 -- | Copyright (c) 1999 Oracle Corporation, Redwood Shores, CA, USA
6 -- | All rights reserved.
7 --+=======================================================================
8 -- | FILENAME
9 -- | ibevcsub.sql
10 -- |
11 -- | DESCRIPTION
12 -- | update script for updating search clob (ibe_ct_imedia_search
13 -- | table when the mtl_items table is updated
14 -- |
15 -- |
16 -- | HISTORY
17 -- | 12-15-99 Savio T Created.
18 -- | 12/11/02 sytong bug fix 2550153, remove extra table in from clause
19 -- | 10/16/03 abhandar bug fix 3168087 :catalog search performance improvement
20 -- | 04/08/05 rgupta Deprecated procedures InsertClob, UpdateClob, and TokenizeString
21 -- | 06/11/05 svijaykr Using partition table instead of materialized view
22 -- +=======================================================================
23 G_FETCH_LIMIT CONSTANT NUMBER := 1000;
24
25
26 -------
27 -- (code for PROCEDURE InsertClob removed on 04/08/2005 by rgupta)
28 -- This procedure is no longer used and has been removed as a result of SQL REP
29 -- issues.
30 --
31
32
33 -------
34 -- (code for PROCEDURE UpdateClob removed on 04/08/2005 by rgupta)
35 -- This procedure is no longer used and has been removed as a result of SQL REP
36 -- issues.
37 --
38
39
40 -------
41 -- (code for PROCEDURE TokenizeString removed on 04/08/2005 by rgupta)
42 -- This procedure is no longer used and has been removed as a result of SQL REP
43 -- issues.
44 --
45
46
47 --=======================================================================
48 -- script used to populate IBE_SECTION_SEARCH table
49 -- with data required for section search
50 --=======================================================================
51 procedure loadMsitesSectionItemsTable(
52 errbuf OUT NOCOPY VARCHAR2,
53 retcode OUT NOCOPY NUMBER
54 )
55 is
56
57 l_application_short_name varchar2(300) ;
58 l_status_AppInfo varchar2(300) ;
59 l_industry_AppInfo varchar2(300) ;
60 l_oracle_schema_AppInfo varchar2(300) ;
61 x_msite_ids msite_id_tbl_type;
62 l_index BINARY_INTEGER;
63 l_master_msite_id number;
64 l_root_section_id number;
65 l_sequence number;
66
67
68 TYPE t_id IS TABLE OF ibe_ct_imedia_search.IBE_CT_IMEDIA_SEARCH_ID%TYPE
69 INDEX BY BINARY_INTEGER;
70 TYPE t_version_number IS TABLE OF
71 ibe_ct_imedia_search.OBJECT_VERSION_NUMBER%TYPE INDEX BY BINARY_INTEGER;
72 TYPE t_created_by IS TABLE OF ibe_ct_imedia_search.CREATED_BY%TYPE
73 INDEX BY BINARY_INTEGER;
74 TYPE t_creation_date IS TABLE OF ibe_ct_imedia_search.CREATION_DATE%TYPE
75 INDEX BY BINARY_INTEGER;
76 TYPE t_last_updated_by IS TABLE OF ibe_ct_imedia_search.LAST_UPDATED_BY%TYPE
77 INDEX BY BINARY_INTEGER;
78 TYPE t_last_updated_date IS TABLE OF
79 ibe_ct_imedia_search.LAST_UPDATE_DATE%TYPE INDEX BY BINARY_INTEGER;
80 TYPE t_last_update_login IS TABLE OF
81 ibe_ct_imedia_search.LAST_UPDATE_LOGIN%TYPE INDEX BY BINARY_INTEGER;
82 TYPE t_category_id IS TABLE OF ibe_ct_imedia_search.CATEGORY_ID%TYPE
83 INDEX BY BINARY_INTEGER;
84 TYPE t_organization_id IS TABLE OF ibe_ct_imedia_search.ORGANIZATION_ID%TYPE
85 INDEX BY BINARY_INTEGER;
86 TYPE t_inventory_item_id IS TABLE OF
87 ibe_ct_imedia_search.INVENTORY_ITEM_ID%TYPE INDEX BY BINARY_INTEGER;
88 TYPE t_language IS TABLE OF ibe_ct_imedia_search.LANGUAGE%TYPE
89 INDEX BY BINARY_INTEGER;
90 TYPE t_description IS TABLE OF ibe_ct_imedia_search.DESCRIPTION%TYPE
91 INDEX BY BINARY_INTEGER;
92 TYPE t_long_description IS TABLE OF
93 ibe_ct_imedia_search.LONG_DESCRIPTION%TYPE INDEX BY BINARY_INTEGER;
94 TYPE t_concatenated_segments IS TABLE OF
95 mtl_system_items_b_kfv.concatenated_segments%TYPE INDEX BY BINARY_INTEGER;
96 TYPE t_category_set_id IS TABLE OF ibe_ct_imedia_search.CATEGORY_SET_ID%TYPE
97 INDEX BY BINARY_INTEGER;
98 TYPE t_web_status IS TABLE OF ibe_ct_imedia_search.WEB_STATUS%TYPE
99 INDEX BY BINARY_INTEGER;
100 TYPE t_section_id IS TABLE of ibe_dsp_sections_b.section_id%TYPE
101 INDEX BY BINARY_INTEGER;
102
103
104 l_use_category_search VARCHAR(50);
105 l_search_category_set VARCHAR2(50);
106 l_tmp NUMBER;
107 l_index_exists NUMBER := 0 ;
108 l_id_tbl t_id;
109 l_version_number_tbl t_version_number;
110 l_created_by_tbl t_created_by;
111 l_creation_date_tbl t_creation_date;
112 l_last_updated_by_tbl t_last_updated_by;
113 l_last_updated_date_tbl t_last_updated_date;
114 l_last_update_login_tbl t_last_update_login;
115 l_category_id_tbl t_category_id;
116 l_organization_id_tbl t_organization_id;
117 l_inventory_item_id_tbl t_inventory_item_id;
118 l_language_tbl t_language;
119 l_description_tbl t_description;
120 l_long_description_tbl t_long_description;
121 l_concatenated_segments_tbl t_concatenated_segments;
122 l_category_set_id_tbl t_category_set_id;
123 l_web_status_tbl t_web_status;
124 l_toplevel_section_tbl t_section_id;
125 --anita end ---
126
127 CURSOR compCur IS
128 select MSITE_ID,MSITE_ROOT_SECTION_ID
129 from ibe_msites_b
130 where msite_id <> 1 and site_type = 'I';
131 -- where END_DATE_ACTIVE > sysdate ;
132
133 -- sytong, bug fix 2550153, remove extra table ibe_msites_b in from clause
134 cursor allTopLevelSections(l_minisite_id in number,
135 l_msite_root_section_id in number) IS
136 SELECT distinct s.section_id
137 FROM IBE_DSP_MSITE_SCT_SECTS mss, IBE_DSP_SECTIONS_B s
138 WHERE mss.parent_section_id = l_msite_root_section_id
139 AND mss.mini_site_id = l_minisite_id
140 AND s.section_id = mss.child_section_id
141 AND s.section_type_code = 'N'
142 -- Fix bug
143 AND s.status_code = 'PUBLISHED'
144 AND NVL(mss.start_date_active, SYSDATE) <= SYSDATE
145 AND NVL(mss.end_date_active, SYSDATE) >= SYSDATE
146 AND NVL(s.start_date_active, SYSDATE) <= SYSDATE
147 AND NVL(s.end_date_active, SYSDATE) >= SYSDATE
148 ORDER BY s.section_id;
149
150 -- cursor for catalog exclusions
151 CURSOR c2(l_c_master_msite_id IN NUMBER,
152 l_c_msite_id IN NUMBER, l_c_root_section_id IN NUMBER) IS
153 select /*+ first_rows */ inventory_item_id
154 from (
155 select section_item_id, idsi.inventory_item_id
156 from ibe_dsp_section_items idsi
157 where section_id IN
158 (
159 select child_section_id
160 from ibe_dsp_msite_sct_sects s1
161 where mini_site_id = l_c_master_msite_id
162 and NOT EXISTS
163 (
164 select child_section_id
165 from ibe_dsp_msite_sct_sects s2
166 where mini_site_id = l_c_msite_id
167 and s2.child_section_id=s1.child_section_id
168 )
169 CONNECT BY PRIOR child_section_id = parent_section_id
170 and PRIOR mini_site_id = l_c_master_msite_id
171 and mini_site_id = l_c_master_msite_id
172 START WITH child_section_id = l_c_root_section_id
173 and mini_site_id = l_c_master_msite_id
174 )
175 and NOT EXISTS
176 (
177 select inventory_item_id
178 from ibe_dsp_section_items i1, ibe_dsp_msite_sct_items i2
179
180 where i1.section_item_id = i2.section_item_id
181 and i2.mini_site_id = l_c_msite_id
182 and i1.inventory_item_id = idsi.inventory_item_id
183 )
184 union
185 select /*ordered use_nl(s3,i2) */ section_item_id, i2.inventory_item_id
186 from ibe_dsp_msite_sct_sects s3, ibe_dsp_section_items i2
187 where i2.section_id = s3.child_section_id
188 and s3.mini_site_id = l_c_msite_id
189 and NOT EXISTS
190 (
191 select null
192 from ibe_dsp_msite_sct_items i3
193 where mini_site_id = l_c_msite_id
194 and i3.section_item_id = i2.section_item_id
195 )
196 );
197
198
199 CURSOR c3 IS
200 SELECT msite_id, msite_root_section_id
201 FROM ibe_msites_b
202 WHERE sysdate BETWEEN start_date_active AND NVL(end_date_active, sysdate)
203 AND master_msite_flag = 'N' AND site_type = 'I';
204
205 --added new cursor :ab-------
206 BEGIN
207 -- Solve the GSCC warning:
208 -- Do not use default values in PL/SQL variable declaration or initialization
209 l_use_category_search :='';
210 l_search_category_set :='PUBLISHED';
211
212 select application_short_name
213 into l_application_short_name
214 from fnd_application
215 where application_id = 671 ;
216
217 if (fnd_installation.get_app_info(l_application_short_name,
218 l_status_AppInfo ,
219 l_industry_AppInfo ,
220 l_oracle_schema_AppInfo )) then
221 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_oracle_schema_AppInfo || '.'
222 || 'IBE_SECTION_SEARCH_PART drop storage';
223 else
224 -- This happens only when application schema checking call
225 -- is wrong. It usually should not happen.
226 execute immediate 'truncate table IBE_SECTION_SEARCH_PART';
227 end if ;
228
229 for v_CompoundData in compCur loop --{
230 open allTopLevelSections(v_CompoundData.MSITE_ID,v_CompoundData.MSITE_ROOT_SECTION_ID);
231 fetch allTopLevelSections bulk collect into l_toplevel_section_tbl;
232 close allTopLevelSections;
233 FND_FILE.PUT_LINE(FND_FILE.LOG,' Inserting into IBE_SECTION_SEARCH_PART' );
234
235 forall l_count2 in 1..l_toplevel_section_tbl.count
236 -- removed on 08/28/01 the minus for parent sections , this is because
237 -- anyway the parent sections cannot have items , look at bug 1917056
238 -- also changed the 2nd minus to juts remove unpublished sections
239 -- compared to before where it was removing unpublished sections
240 -- for each given minisite by joining minisite_section table
241 insert into IBE_SECTION_SEARCH_PART
242 (inventory_item_id
243 , organization_id
244 , section_id
245 , minisite_id
246 , OBJECT_VERSION_NUMBER
247 , CREATED_BY
248 , CREATION_DATE
249 , LAST_UPDATED_BY
250 , LAST_UPDATE_DATE
251 , LAST_UPDATE_LOGIN)
252 select
253 distinct f.inventory_item_id,f.organization_id ,l_toplevel_section_tbl(l_count2),
254 --sectionIdData.section_id,
255 --distinct g.inventory_item_id,g.organization_id ,sectionIdData.section_id,
256 v_CompoundData.MSITE_ID,
257 1,
258 FND_GLOBAL.user_id,
259 SYSDATE,
260 FND_GLOBAL.user_id,
261 SYSDATE,
262 FND_GLOBAL.conc_login_id
263 from ibe_dsp_section_items f ,
264 ibe_dsp_sections_b s
265 where (f.end_date_active > sysdate or f.end_date_active is null)
266 and f.start_date_active < sysdate
267 and f.section_id = s.section_id
268 and s.status_code = 'PUBLISHED' and f.section_id in
269 ( SELECT mss.child_section_id
270 FROM IBE_DSP_MSITE_SCT_SECTS mss
271 START WITH mss.child_section_id = l_toplevel_section_tbl(l_count2) --sectionIdData.section_id
272 and mss.mini_site_id = v_CompoundData.MSITE_ID
273 CONNECT BY PRIOR mss.child_section_id = mss.parent_section_id
274 AND mss.mini_site_id = v_CompoundData.MSITE_ID
275 );
276 /*
277 select distinct g.inventory_item_id,g.organization_id ,sectionIdData.section_id,
278 v_CompoundData.MSITE_ID,
279 1,
280 FND_GLOBAL.user_id,
281 SYSDATE,
282 FND_GLOBAL.user_id,
283 SYSDATE,
284 FND_GLOBAL.conc_login_id
285 from ibe_dsp_section_items f ,
286 mtl_system_items_b g,
287 ibe_dsp_sections_b s
288 where f.inventory_item_id = g.inventory_item_id
289 and (f.end_date_active > sysdate or f.end_date_active is null)
290 and f.start_date_active < sysdate
291 and f.section_id = s.section_id
292 and s.status_code = 'PUBLISHED'
293 and f.section_id in
294 ( SELECT mss.child_section_id
295 FROM IBE_DSP_MSITE_SCT_SECTS mss
296 START WITH mss.child_section_id = sectionIdData.section_id
297 and mss.mini_site_id = v_CompoundData.MSITE_ID
298 CONNECT BY PRIOR mss.child_section_id = mss.parent_section_id
299 AND mss.mini_site_id =v_CompoundData.MSITE_ID
300 );
301 end loop;
302 */
303 end loop; --}
304
305 /* Now after loading the data we will remove the catalog
306 exclusions for item to minisite from the table
307 */
308 select msite_id into l_master_msite_id
309 from ibe_msites_b
310 where UPPER(master_msite_flag) = 'Y' and site_type = 'I';
311
312 l_index := 1;
313 FOR r3 IN c3 LOOP
314 x_msite_ids(l_index).msite_id := r3.msite_id;
315 x_msite_ids(l_index).msite_root_section_id := r3.msite_root_section_id;
316 l_index := l_index + 1;
317 END LOOP;
318
319 -- For items
320 l_index := 1;
321 FOR i IN 1..x_msite_ids.COUNT LOOP
322 FOR r2 IN c2(l_master_msite_id, x_msite_ids(i).msite_id,
323 x_msite_ids(i).msite_root_section_id) LOOP
324 delete from ibe_section_search_part
325 where inventory_item_id = r2.inventory_item_id
326 and minisite_id = x_msite_ids(i).msite_id;
327 END LOOP; -- end loop r2
328 END LOOP; -- end loop i
329 FND_FILE.PUT_LINE(FND_FILE.LOG,' Calling Alter table exchange partition' );
330
331 if (fnd_installation.get_app_info(l_application_short_name,
332 l_status_AppInfo ,
333 l_industry_AppInfo ,
334 l_oracle_schema_AppInfo )) then
335 EXECUTE IMMEDIATE 'ALTER TABLE ' || l_oracle_schema_AppInfo || '.'
336 || 'IBE_SECTION_SEARCH_PART exchange partition PART1 with table '
337 || l_oracle_schema_AppInfo || '.' || 'IBE_SECTION_SEARCH'
341 -- is wrong. It usually should not happen.
338 ||' including indexes without validation';
339 else
340 -- This happens only when application schema checking call
342
343 execute immediate
344 'alter table IBE_SECTION_SEARCH_PART exchange partition PART1 with table IBE_SECTION_SEARCH ' ||
345 'including indexes without validation' ;
346 end if;
347
348
349 /* Section for getting oracle schema name for ibe , since we cannot hard code schema
350 name , so strategy is to get application short name pass it to fnd api . note it
351 can throw exception if more than one schema is found , but this cannot happen for
352 iStore since it is a new product in 11.5
353 */
354 -- Remove the following select statement as the l_application_short_name
355 -- has been set at the beginning of the procedure.
356 -- select application_short_name
357 -- into l_application_short_name
358 -- from fnd_application
359 -- where application_id = 671 ;
360 --
361 -- refresh materialized view in apps schema
362 /*
363 execute immediate
364 'begin DBMS_MVIEW.REFRESH(:1,''A'','''',TRUE, FALSE, 0,0,0, TRUE); end; '
365 using 'ibe_sct_search_mv' ;
366 */
367 -- removing for now as it is very expenisve and time consuming to do
368 -- and unnecessary if you just add a new section etc
369 --ctx_ddl.sync_index('IBE_CT_IMEDIA_SEARCH_IM');
370
371 -- ========= ab start =============================================
372
373 --Original Section Refresh logic in the program, including populating ibe_section_search table and refresh materialized view ibe_sct_search_mv;
374 --Get category search profile setting IBE_USE_CATEGORY_SEARCH;
375 l_use_category_search := FND_PROFILE.VALUE_specific('IBE_USE_CATEGORY_SEARCH',671,0,671);
376
377 If l_use_category_search='N' Then
378 l_tmp:=0;
379 ----dbms_output.put_line('category search is N');
380
381 --Get search category set profile setting IBE_SEARCH_CATEGORY_SET;
382 l_search_category_set := FND_PROFILE.VALUE_specific('IBE_SEARCH_CATEGORY_SET',671,0,671);
383 ----dbms_output.put_line('search category set value='||l_search_category_set);
384 If (l_search_category_set is not null) Then
385 INSERT INTO ibe_ct_imedia_search
386 (IBE_CT_IMEDIA_SEARCH_ID,
387 INVENTORY_ITEM_ID,
388 OBJECT_VERSION_NUMBER,
389 CREATED_BY,
390 CREATION_DATE,
391 LAST_UPDATED_BY,
392 LAST_UPDATE_DATE,
393 LAST_UPDATE_LOGIN,
394 CATEGORY_ID,
395 ORGANIZATION_ID,
396 LANGUAGE,
397 DESCRIPTION,
398 LONG_DESCRIPTION,
399 CATEGORY_SET_ID,
400 WEB_STATUS,
401 INDEXED_SEARCH)
402 select ibe_ct_imedia_search_s1.nextval,
403 t.INVENTORY_ITEM_ID,t.OBJECT_VERSION_NUMBER,t.CREATED_BY,t.CREATION_DATE,t.LAST_UPDATED_BY,
404 t.LAST_UPDATE_DATE,t.LAST_UPDATE_LOGIN,t.CATEGORY_ID,t.ORGANIZATION_ID,t.LANGUAGE,
405 t.DESCRIPTION,t.LONG_DESCRIPTION,t.CATEGORY_SET_ID,t.WEB_STATUS,
406 ibe_search_setup_pvt.WriteToLob(t.DESCRIPTION, t.LONG_DESCRIPTION, t.concatenated_segments)
407 from
408 (select distinct b.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,1 OBJECT_VERSION_NUMBER,
409 FND_GLOBAL.user_id CREATED_BY,SYSDATE CREATION_DATE,
410 FND_GLOBAL.user_id LAST_UPDATED_BY, SYSDATE LAST_UPDATE_DATE,
411 FND_GLOBAL.conc_login_id LAST_UPDATE_LOGIN, c.CATEGORY_ID,
412 b.ORGANIZATION_ID,b.LANGUAGE,b.DESCRIPTION, b.LONG_DESCRIPTION,
413 a.concatenated_segments,c.CATEGORY_SET_ID,a.WEB_STATUS
414 from mtl_system_items_b_kfv a, mtl_system_items_tl b, mtl_item_categories c, ibe_dsp_section_items d
415 where d.inventory_item_id = a.inventory_item_id
416 and d.organization_id = a.organization_id
417 and a.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID
418 and a.organization_id = b.organization_id
419 and a.web_status = 'PUBLISHED'
420 and a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
421 and a.organization_id = c.organization_id
422 and not exists ( select r.INVENTORY_ITEM_ID ,r.ORGANIZATION_ID
423 from IBE_CT_IMEDIA_SEARCH r
424 where r.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID
425 and r.ORGANIZATION_ID = b.ORGANIZATION_ID)
426 )t ;
427
428 commit;
429
430 FND_FILE.PUT_LINE(FND_FILE.LOG,' the loop index after insert is = ' || l_tmp );
431 ----dbms_output.put_line(' the loop index after insert is = ' || l_tmp );
432 ----dbms_output.put_line('sequence value='||l_sequence);
433 l_sequence:=l_sequence+1;
434 l_tmp:=l_tmp+1;
435
436
437 --Check IBE_CT_IMEDIA_SEARCH_IM Index;
438 select count(*)
439 into l_index_exists
440 from user_indexes
441 where index_name = 'IBE_CT_IMEDIA_SEARCH_IM';
442
443 if(l_index_exists > 0 ) then
444 ----dbms_output.put_line('Before synchronizing the index');
445 --Synchronize the IBE_CT_IMEDIA_SEARCH_IM index by calling inter media synchronize procedure;
446 FND_FILE.PUT_LINE(FND_FILE.LOG,' Sync the intermedia index' );
447
448 ctx_ddl.sync_index('IBE_CT_IMEDIA_SEARCH_IM');
449
450 End if;
451 End if;
452 End if;
453
454 --==========end ab =======================================
455 FND_FILE.PUT_LINE(FND_FILE.LOG,'Procedure completed sucessfully');
456 end loadMsitesSectionItemsTable;
457
458
459 end IBE_SearchUpdate_PVT ;
[Home] [Help]