1 package body IBE_Search_PVT as
2 /* $Header: IBEVCSKB.pls 120.1 2008/04/16 08:08:36 mgiridha ship $ */
3
4 /* +=======================================================================
5 | Copyright (c) 1999 Oracle Corporation, Redwood Shores, CA, USA
6 | All rights reserved.
7 +=======================================================================
8 | FILENAME
9 | ibevcskb.sql
10 |
11 | DESCRIPTION
12 |
13 | NOTES
14 |This is primarily used for the triggers of mtl tables to update the
15 |IBE_CT_IMEDIA_SEARCH table .
16 |
17 |MTL_ITEM_CATEGORIES
18 |1) insert ,
19 |2) delete
20 |not sure if direct updates take place here
21 |
22 |MTL_SYSTEM_ITEMS_B
23 |dont care about its update as we are not using any of its columns right now
24 |Although we could just write the inset update delete on its tl table we
25 |still decided to write the delete on it :-)
26 |
27 |
28 |
29 | HISTORY
30 | 12-15-99 Savio T Created.
31 | 10-16-03 abhandar modified bug fix 3168087:catalog search performance
32 | 04-16-08 mgiridha bug 6924793 ITEMS NOT SEARCHABLE BY PART NUMBER AFTER REPUBLISHING ON ISTORE.
33 +=======================================================================*/
34
35 -----------------------------------------------
36 --PROCEDURE CALLED ON INSERT IN ITEM CATEGORIES
37 --HERE YOU NEED TO INSERT A NEW ROW INTO IMEDIA_SEARCH TABLE
38 --added on 04/18 code to join on mtl_system_items_b
39 --table to get web status flag over
40 -----------------------------------------------
41 procedure Item_Category_Inserted(
42 new_category_id number,
43 new_category_set_id number,
44 new_inventory_item_id number,
45 new_organization_id number)
46 is
47
48 l_insert_flag boolean:=false;
49 l_search_category_set varchar2(30):='';
50 l_search_web_status VARCHAR2(30):= 'PUBLISHED';
51 l_use_category_search VARCHAR2(30) := 'N ';
52
53
54 begin
55
56 --dbms_output.put_line('Start item category inserted');
57
58 --dbms_output.put_line('inputs are:new_category_id='||new_category_id
59 --||':new_category_set_id='||new_category_set_id
60 --||':new_inventory_item_id='||new_inventory_item_id
61 --||':new_organization_id ='||new_organization_id);
62
63 --Get IBE_SEARCH_CATEGORY_SET Profile value
64 l_use_category_search := FND_PROFILE.VALUE_specific('IBE_USE_CATEGORY_SEARCH',671,0,671);
65 --Get Search Category Set profile IBE_SEARCH_CATEGORY_SET;
66 l_search_category_set := FND_PROFILE.VALUE_specific('IBE_SEARCH_CATEGORY_SET',671,0,671);
67 --Get product status profile IBE_SEARCH_WEB_STATUS;
68 l_search_web_status := FND_PROFILE.VALUE_specific('IBE_SEARCH_WEB_STATUS',671,0,671);
69
70 --dbms_output.put_line('use_category_search='||l_use_category_search
71 --||'search_category_set='||l_search_category_set||':search_web_status='||l_search_web_status);
72
73 IF l_search_category_set is not null THEN
74 IF new_category_set_id = l_search_category_set THEN
75 l_insert_flag := TRUE;
76 ELSE
77 l_insert_flag := FALSE;
78 END IF;
79 ELSE
80 l_insert_flag := TRUE;
81 END IF;
82
83
84 IF (l_insert_flag) THEN
85 --dbms_output.put_line('l_insert_flag=true');
86
87 --Get IBE_SEARCH_WEB_STATUS profile value
88 l_search_web_status := FND_PROFILE.VALUE_specific('IBE_SEARCH_WEB_STATUS',671,0,671);
89
90 IF l_search_web_status = 'PUBLISHED' or l_search_web_status is NULL THEN
91
92 --dbms_output.put_line('inserting into ibe_ct_imedia_search table for web status PUBLISHED/NULL');
93 insert into ibe_ct_imedia_search (IBE_CT_IMEDIA_SEARCH_ID
94 , OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE
95 , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
96 , CATEGORY_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID
97 , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
98 , CATEGORY_SET_ID, WEB_STATUS)
99 SELECT
100 ibe_ct_imedia_search_s1.nextval,
101 0, FND_GLOBAL.user_id,
102 SYSDATE, FND_GLOBAL.user_id,
103 SYSDATE, FND_GLOBAL.conc_login_id,
104 new_category_id , new_organization_id ,
105 new_inventory_item_id, b.LANGUAGE,
106 b.DESCRIPTION, b.LONG_DESCRIPTION,
107 ibe_search_setup_pvt.WriteToLob(b.description , b.long_description ,c.concatenated_segments),
108 new_category_set_id , a.web_status
109 from mtl_system_items_b a ,mtl_system_items_tl b ,mtl_system_items_kfv c
110 where b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
111 and b.organization_id = a.organization_id
112 and b.INVENTORY_ITEM_ID = new_inventory_item_id
113 and b.ORGANIZATION_ID = new_organization_id
114 and a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
115 and a.ORGANIZATION_ID = c.organization_id
116 and a.web_status = 'PUBLISHED';
117
118 ELSIF l_search_web_status ='PUBLISHED_UNPUBLISHED' THEN
119
120 --dbms_output.put_line('inserting into ibe_ct_imedia_search table for web status PUBLISHED_UNPUBLISHED');
121 insert into ibe_ct_imedia_search (IBE_CT_IMEDIA_SEARCH_ID
122 , OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE
123 , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
124 , CATEGORY_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID
125 , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
126 , CATEGORY_SET_ID, WEB_STATUS)
127 SELECT
128 ibe_ct_imedia_search_s1.nextval,
129 0, FND_GLOBAL.user_id,
130 SYSDATE, FND_GLOBAL.user_id,
131 SYSDATE, FND_GLOBAL.conc_login_id,
132 new_category_id , new_organization_id ,
133 new_inventory_item_id, b.LANGUAGE,
134 b.DESCRIPTION, b.LONG_DESCRIPTION,
135 ibe_search_setup_pvt.WriteToLob(b.description, b.long_description ,c.concatenated_segments),
136 new_category_set_id , a.web_status
137 from mtl_system_items_b a ,mtl_system_items_tl b ,mtl_system_items_kfv c
138 where b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
139 and b.organization_id = a.organization_id
140 and b.INVENTORY_ITEM_ID = new_inventory_item_id
141 and b.ORGANIZATION_ID = new_organization_id
142 and a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
143 and a.ORGANIZATION_ID = c.organization_id
144 and a.web_status IN ('PUBLISHED', 'UNPUBLISHED');
145 ELSE
146 --dbms_output.put_line('inserting into ibe_ct_imedia_search table for web status ALL');
147 insert into ibe_ct_imedia_search (IBE_CT_IMEDIA_SEARCH_ID
148 , OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE
149 , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
150 , CATEGORY_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID
151 , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
152 , CATEGORY_SET_ID, WEB_STATUS)
153 SELECT
154 ibe_ct_imedia_search_s1.nextval,
155 0, FND_GLOBAL.user_id,
156 SYSDATE, FND_GLOBAL.user_id,
157 SYSDATE, FND_GLOBAL.conc_login_id,
158 new_category_id , new_organization_id ,
159 new_inventory_item_id, b.LANGUAGE,
160 b.DESCRIPTION, b.LONG_DESCRIPTION,
161 ibe_search_setup_pvt.WriteToLob(b.description , b.long_description ,c.concatenated_segments),
162 new_category_set_id , a.web_status
163 from mtl_system_items_b a ,mtl_system_items_tl b ,mtl_system_items_kfv c
164 where b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
165 and b.organization_id = a.organization_id
166 and b.INVENTORY_ITEM_ID = new_inventory_item_id
167 and b.ORGANIZATION_ID = new_organization_id
168 and a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
169 and a.ORGANIZATION_ID = c.organization_id;
170 END IF;
171 END IF;
172
173 --dbms_output.put_line(' End Item Category Inserted');
174 END Item_Category_Inserted;
175
176
177 -----------------------------------------------
178 --PROCEDURE CALLED ON DELETE IN ITEM CATEGORIES
179 -- 04/17 UPDATED THIS FUNCTION CALL TO TAKE OLD
180 -- AND NEW ORG ID SO THAT WE CAN NOW DELETE A CATEGORY
181 -- FOR A PRODUCT BELONGING TO A SPECIFIC ORG ID
182 -----------------------------------------------
183
184 procedure Item_Category_Deleted(
185 old_category_id number,
186 old_category_set_id number,
187 old_inventory_item_id number,
188 old_organization_id number)
189 is
190 begin
191
192 delete from ibe_ct_imedia_search c
193 where c.CATEGORY_ID = old_category_id
194 and c.CATEGORY_SET_ID = old_category_set_id
195 and c.INVENTORY_ITEM_ID = old_inventory_item_id
196 and c.ORGANIZATION_ID = old_organization_id ;
197
198
199 end Item_Category_Deleted;
200
201
202 -----------------------------------------------
203 --PROCEDURE CALLED ON UPDATE IN ITEM CATEGORIES
204 -- 04/17 UPDATED THIS FUNCTION CALL TO TAKE OLD
205 -- AND NEW ORG ID SO THAT WE CAN NOW UPDATE A CATEGORY
206 -- FOR A PRODUCT BELONGING TO A SPECIFIC ORG ID
207 -----------------------------------------------
208
209 procedure Item_Category_Updated(
210 old_category_id number,new_category_id number,
211 old_category_set_id number,new_category_set_id number,
212 old_inventory_item_id number,new_inventory_item_id number,
213 old_organization_id number,new_organization_id number)
214 is
215
216 l_search_category_set VARCHAR2(30);
217 l_search_web_status VARCHAR2(30);
218 l_use_category_search VARCHAR(30);
219 l_web_status VARCHAR2(30);
220 l_temp number;
221
222 CURSOR c_check_section_item_csr (c_inventory_item_id number, c_organization_id number) is
223 select 1 from mtl_system_items_b item
224 where item.inventory_item_id =c_inventory_item_id
225 and item.organization_id=c_organization_id
226 and item.web_status='PUBLISHED' and
227 exists (select 1 from ibe_dsp_section_items sec_item
228 where sec_item.inventory_item_id=item.inventory_item_id
229 and organization_id=item.organization_id);
230
231
232 begin
233
234 ----dbms_output.put_line('begin item category updated');
235 ----dbms_output.put_line('inputs :oldCatId='||old_category_id
236 --||':oldCatSetId='||old_category_set_id
237 --||':old_inventory_item_id='||old_inventory_item_id
238 --||':oldOrgId='||old_organization_id
239 --||':newCatId='||new_category_id
240 --||':newCatSetId='||new_category_set_id
241 --||':newInvItemId='||new_inventory_item_id
242 --||':newOrgId='||new_organization_id);
243
244 IF old_category_set_id = new_category_set_id THEN
245 --dbms_output.put_line('Updating data :old category set id = new category set id');
246 update ibe_ct_imedia_search c
247 set CATEGORY_ID = new_category_id,
248 CATEGORY_SET_ID = new_category_set_id,
249 LAST_UPDATE_DATE = sysdate,
250 INVENTORY_ITEM_ID = new_inventory_item_id,
251 ORGANIZATION_ID = new_organization_id
252 where c.CATEGORY_ID = old_category_id
253 AND c.CATEGORY_SET_ID = old_category_set_id
254 AND c.INVENTORY_ITEM_ID = old_inventory_item_id
255 AND c.ORGANIZATION_ID = old_organization_id;
256
257 ELSE -- the old and new category set ids are different-------------
258
259 --Get IBE_SEARCH_CATEGORY_SET Profile value
260 l_search_category_set := FND_PROFILE.VALUE_specific('IBE_SEARCH_CATEGORY_SET',671,0,671);
261 ----dbms_output.put_line('Search category set='||l_search_category_set);
262 IF l_search_category_set is NULL THEN
263 ------dbms_output.put_line('Updating data :search category set is null');
264 update ibe_ct_imedia_search c
265 set CATEGORY_ID = new_category_id,
266 CATEGORY_SET_ID = new_category_set_id,
267 LAST_UPDATE_DATE = sysdate,
268 INVENTORY_ITEM_ID = new_inventory_item_id,
269 ORGANIZATION_ID = new_organization_id
270 where c.CATEGORY_ID = old_category_id
271 AND c.CATEGORY_SET_ID = old_category_set_id
272 AND c.INVENTORY_ITEM_ID = old_inventory_item_id
273 AND c.ORGANIZATION_ID = old_organization_id;
274
275 ELSE ----- search category set not null----------------------
276
277 ----dbms_output.put_line('Search category set is not null');
278 IF old_category_set_id = l_search_category_set THEN
279 ----dbms_output.put_line('Deleting row:old category set id = l_search category set');
280
281 --------------enhancement -check for section search -------------
282 l_use_category_search := FND_PROFILE.VALUE_specific('IBE_USE_CATEGORY_SEARCH',671,0,671);
283
284 IF l_use_category_search ='N' then --profile set for section search
285 -- check whether item associated to some section
286 OPEN c_check_section_item_csr(old_inventory_item_id,old_organization_id);
287 FETCH c_check_section_item_csr into l_temp;
288 IF c_check_section_item_csr%FOUND then
289 --- update the record
290 CLOSE c_check_section_item_csr;
291 update ibe_ct_imedia_search c
292 set CATEGORY_ID = new_category_id,
293 CATEGORY_SET_ID = new_category_set_id,
294 LAST_UPDATE_DATE = sysdate,
295 INVENTORY_ITEM_ID = new_inventory_item_id,
296 ORGANIZATION_ID = new_organization_id
297 where c.CATEGORY_ID = old_category_id
298 AND c.CATEGORY_SET_ID = old_category_set_id
299 AND c.INVENTORY_ITEM_ID= old_inventory_item_id
300 AND c.ORGANIZATION_ID = old_organization_id;
301
302 ELSE ------- c_check_section_item_csr NOT found-----------
303 -- delete the record -
304 CLOSE c_check_section_item_csr;
305 DELETE ibe_ct_imedia_search where
306 CATEGORY_ID = old_category_id
307 AND CATEGORY_SET_ID = old_category_set_id
308 AND INVENTORY_ITEM_ID = old_inventory_item_id
309 AND ORGANIZATION_ID = old_organization_id;
310 END IF;
311
312 ELSE ------l_use_category_search ='Y'--------
313 DELETE ibe_ct_imedia_search where
314 CATEGORY_ID = old_category_id
315 AND CATEGORY_SET_ID = old_category_set_id
316 AND INVENTORY_ITEM_ID = old_inventory_item_id
317 AND ORGANIZATION_ID = old_organization_id;
318 END IF;
319
320 ElSIF new_category_set_id = l_search_category_set THEN
321 ----dbms_output.put_line('Adding row :new category set id = l_search category set');
322 --Insert item into ibe_ct_imedia_search table based on the new value and IBE_SEARCH_WEB_STATUS profile value;
323
324 select web_status into l_web_status from mtl_system_items_b
325 where inventory_item_id=new_inventory_item_id and organization_id= new_organization_id;
326 l_search_web_status := FND_PROFILE.VALUE_specific('IBE_SEARCH_WEB_STATUS',671,0,671);
327
328 ----dbms_output.put_line('l_web_status='||l_web_status||':search web status='||l_search_web_status);
329
330 if ( l_search_web_status ='ALL'
331 OR l_search_web_status=l_web_status
332 OR (l_search_web_status='PUBLISHED_UNPUBLISHED' AND l_web_status='PUBLISHED')
333 OR (l_search_web_status='PUBLISHED_UNPUBLISHED' AND l_web_status='UNPUBLISHED')) THEN
334
335 ----dbms_output.put_line ('Inserting into ibe_ct_imedia_search table');
336
337 insert into ibe_ct_imedia_search (IBE_CT_IMEDIA_SEARCH_ID
338 , OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE
339 , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
340 , CATEGORY_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID
341 , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
342 , CATEGORY_SET_ID, WEB_STATUS)
343 SELECT
344 ibe_ct_imedia_search_s1.nextval,
345 0, FND_GLOBAL.user_id,
346 SYSDATE, FND_GLOBAL.user_id,
347 SYSDATE, FND_GLOBAL.conc_login_id,
348 new_category_id , new_organization_id ,
349 new_inventory_item_id, b.LANGUAGE,
350 b.DESCRIPTION, b.LONG_DESCRIPTION,
351 ibe_search_setup_pvt.WriteToLob(b.description , b.long_description ,c.concatenated_segments),
352 new_category_set_id , a.web_status
353 from mtl_system_items_b a ,mtl_system_items_tl b ,mtl_system_items_kfv c
354 where b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
355 and b.organization_id = a.organization_id
356 and b.INVENTORY_ITEM_ID = new_inventory_item_id
357 and b.ORGANIZATION_ID = new_organization_id
358 and a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
359 and a.ORGANIZATION_ID = c.organization_id;
360 -- and a.web_status = l_search_web_status;
361 END IF;
362 END IF;
363 END IF;
364 ----dbms_output.put_line('Done');
365 END IF;
366 END Item_Category_Updated;
367
368 -----------------------------------------------
369 --PROCEDURE CALLED ON DELETE IN ITEM
370 --SINCE WE DONT MOVE DATA FROM MTL_ITEMS BASE
371 --TABLE NOW , WE ARE NOT WRITING THE INSERT AND UPDATE
372 --PROCEDURES FOR IT
373 -----------------------------------------------
374
375 procedure Item_Deleted(
376 old_inventory_item_id number,
377 old_organization_id number)
378 is
379 begin
380
381 delete from ibe_ct_imedia_search c
382 where c.INVENTORY_ITEM_ID = old_inventory_item_id
383 and c.ORGANIZATION_ID = old_organization_id ;
384
385 end Item_Deleted ;
386
387 -----------------------------------------------
388 --Function to check whether item associated with the
389 -- profile value category set id
390 -----------------------------------------------
391
392 function Item_In_CatSetId_profile(
393 p_inventory_item_id IN number,
394 p_organization_id IN number) return NUMBER
395 is
396 l_search_category_set varchar2(30);
397 l_search_web_status VARCHAR2(30):= 'PUBLISHED';
398 l_use_category_search VARCHAR2(30) := 'N ';
399 l_item_exists_count NUMBER := 0 ;
400
401 begin
402 --Get Search Category Set profile IBE_SEARCH_CATEGORY_SET;
403 l_search_category_set := FND_PROFILE.VALUE_specific('IBE_SEARCH_CATEGORY_SET',671,0,671);
404 -- Check if item is under the category set specified by IBE_SEARCH_CATEGORY_SET
405 if l_search_category_set is not null then
406 select 1 into l_item_exists_count from mtl_item_categories
407 where inventory_item_id = p_inventory_item_id and
408 organization_id= p_organization_id and category_set_id=l_search_category_set
409 and rownum=1;
410 else ---- profile value NULL ie All Category Set Ids
411 select 1 into l_item_exists_count from mtl_item_categories
412 where inventory_item_id = p_inventory_item_id and
413 organization_id= p_organization_id and rownum=1;
414 END If;
415
416 RETURN l_item_exists_count;
417 end Item_In_CatSetId_profile;
418 -----------------------------------------------
419 --PROCEDURE CALLED ON UPDATE IN ITEM
420 --WE will not insert new row when web_status flag
421 -- is updated to "PUBLISHED" because we already
422 -- have a data inserted through the concurrent program
423 -- regardless of web_status. We just need to update
424 -- the web_status in ibe_ct_imedia_search
425 -----------------------------------------------
426 procedure Item_Updated(
427 old_inventory_item_id number,
428 old_organization_id number,
429 old_web_status varchar2,
430 new_web_status varchar2)
431 is
432
433 l_search_web_status VARCHAR2(30);
434 l_item_exists number :=0;
435 l_module varchar2(20):='ibe_search_pvt';
436 l_temp number:=0;
437
438 begin
439 ----dbms_output.put_line('inputs:oldInvItemId='||old_inventory_item_id
440 --||':oldOrgId='||old_organization_id
441 --||':oldWebStatus='||old_web_status
442 --||':newWebStatus='||new_web_status);
443
444 --Get IBE_SEARCH_WEB_STATUS Profile value
445 l_search_web_status := FND_PROFILE.VALUE_specific('IBE_SEARCH_WEB_STATUS',671,0,671);
446
447 IF l_search_web_status='ALL' THEN
448 --dbms_output.put_line('updating data;Web status =ALL');
449 update ibe_ct_imedia_search
450 set web_status = new_web_status
451 where inventory_item_id = old_inventory_item_id
452 and organization_id = old_organization_id;
453
454 ELSIF l_search_web_status='PUBLISHED_UNPUBLISHED' THEN
455 IF old_web_status NOT IN ('PUBLISHED', 'UNPUBLISHED') THEN
456 ----dbms_output.put_line('old_web_status NOT IN (PUBLISHED,UNPUBLISHED)');
457 IF new_web_status IN ('PUBLISHED', 'UNPUBLISHED') THEN
458 ----dbms_output.put_line ('new_web_status IN (PUBLISHED, UNPUBLISHED)');
459
460 -- need to check whether the item exists under the profile category_set_id value
461 l_item_exists:=Item_In_CatSetId_profile(old_inventory_item_id,old_organization_id);
462
463 ----dbms_output.put_line('l_item_exists='||l_item_exists);
464 IF (l_item_exists>0) then
465 --Insert item into ibe_ct_imedia_search;
466 --dbms_output.put_line('Adding Row ;l_item_exists >0');
467
468 -- ***inserting '' for the concatenated_segment in the clob
469 --as cannot access mtl_system_items_kfv.concatenated_segments value here.
470 --Later on ItemTL_Updated trigger will be called which will update the
471 -- CLOB value ****.
472 -- Above comment is not valid changed below query for bug 6924793 mgiridha
473
474 insert into ibe_ct_imedia_search (IBE_CT_IMEDIA_SEARCH_ID
475 , OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE
476 , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
477 , CATEGORY_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID
478 , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
479 , CATEGORY_SET_ID, WEB_STATUS)
480 SELECT
481 ibe_ct_imedia_search_s1.nextval,
482 0, FND_GLOBAL.user_id,
483 SYSDATE, FND_GLOBAL.user_id,
484 SYSDATE, FND_GLOBAL.conc_login_id,
485 d.category_id , old_organization_id ,
486 old_inventory_item_id, b.LANGUAGE,
487 b.DESCRIPTION, b.LONG_DESCRIPTION,
488 ibe_search_setup_pvt.WriteToLob(b.description , b.long_description ,a.concatenated_segments),
489 d.category_set_id , new_web_status
490 from mtl_system_items_tl b ,mtl_item_categories d, mtl_system_items_b_kfv a
491 where b.INVENTORY_ITEM_ID = old_inventory_item_id
492 and b.ORGANIZATION_ID = old_organization_id
493 and b.INVENTORY_ITEM_ID = d.INVENTORY_ITEM_ID
494 and b.organization_id = d.organization_id
495 and d.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
496 and d.organization_id = a.organization_id;
497
498 END IF;
499 END IF;
500 ELSE -- old_web_status IN ('PUBLISHED', 'UNPUBLISHED')
501 IF new_web_status IN ('PUBLISHED', 'UNPUBLISHED') THEN
502 ----dbms_output.put_line('updating... ,new_web_status IN (PUBLISHED, UNPUBLISHED');
503
504 update ibe_ct_imedia_search
505 set web_status = new_web_status
506 where inventory_item_id = old_inventory_item_id
507 AND organization_id = old_organization_id;
508 END IF;
509 END IF;
510 ELSIF (l_search_web_status='PUBLISHED' OR l_search_web_status is null) THEN
511
512 ----dbms_output.put_line('l_search_web_status=PUBLISHED OR l_search_web_status is null');
513
514 IF new_web_status ='PUBLISHED' THEN
515
516 ----dbms_output.put_line('new_web_status = PUBLISHED');
517 -- need to check whether the item exists under the profile category_set_id value
518 l_item_exists:=Item_In_CatSetId_profile(old_inventory_item_id,old_organization_id);
519
520 IF (l_item_exists>0) then
521 --Insert item into ibe_ct_imedia_search;
522 ----dbms_output.put_line('inserting row...: l_item_exists='||l_item_exists);
523
524 --**** inserting '' for the concatenated_segment in the clob
525 --as cannot access mtl_system_items_kfv.concatenated_segments value here.
526 --Later on ItemTL_Updated trigger will be called which will update the
527 -- CLOB value ****.
528 -- Above comment is not valid changed below query for bug 6924793 mgiridha
529
530 insert into ibe_ct_imedia_search (IBE_CT_IMEDIA_SEARCH_ID
531 , OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE
532 , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
533 , CATEGORY_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID
534 , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
535 , CATEGORY_SET_ID, WEB_STATUS)
536 SELECT
537 ibe_ct_imedia_search_s1.nextval,
538 0, FND_GLOBAL.user_id,
539 SYSDATE, FND_GLOBAL.user_id,
540 SYSDATE, FND_GLOBAL.conc_login_id,
541 d.category_id , old_organization_id ,
542 old_inventory_item_id, b.LANGUAGE,
543 b.DESCRIPTION, b.LONG_DESCRIPTION,
544 ibe_search_setup_pvt.WriteToLob(b.description , b.long_description ,a.concatenated_segments),
545 d.category_set_id , new_web_status
546 from mtl_system_items_tl b ,mtl_item_categories d, mtl_system_items_b_kfv a
547 where b.INVENTORY_ITEM_ID = old_inventory_item_id
548 and b.ORGANIZATION_ID = old_organization_id
549 and b.INVENTORY_ITEM_ID = d.INVENTORY_ITEM_ID
550 and b.organization_id = d.organization_id
551 and d.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
552 and d.organization_id = a.organization_id;
553
554 END IF;
555 ELSE
556 -- Delete item from ibe_ct_imedia_search;
557 ----dbms_output.put_line('deleting from ibe_ct_imedia_search');
558 Delete from ibe_ct_imedia_search
559 where INVENTORY_ITEM_ID = old_inventory_item_id
560 and ORGANIZATION_ID = old_organization_id;
561 END If;
562 END IF;
563 commit;
564 ----dbms_output.put_line('Item_Update done.');
565 end Item_Updated;
566 -----------------------------------------------
567 --PROCEDURE CALLED ON DELETE IN ITEMS_TL
568 -----------------------------------------------
569
570 procedure ItemTL_Deleted(
571 old_inventory_item_id number,
572 old_organization_id number,
573 old_language varchar2)
574 is
575 begin
576
577 delete from ibe_ct_imedia_search c
578 where c.INVENTORY_ITEM_ID = old_inventory_item_id
579 and c.LANGUAGE = old_language
580 and c.ORGANIZATION_ID = old_organization_id ;
581
582
583 end ItemTL_Deleted;
584
585 -----------------------------------------------
586 --PROCEDURE CALLED ON UPDATE IN ITEMS_TL
587 --??????????????????????????????????????????????????????????????????????????????????
588 --question on how to take care of scenario when an item is deleted from 1 org
589 --and assigned to another org is it an update operation or is it a 2 step
590 --delete/insert operation
591 -----------------------------------------------
592
593 procedure ItemTL_Updated(
594 old_inventory_item_id number,
595 old_organization_id number,
596 old_language varchar2,
597 new_language varchar2,
598 new_description varchar2,
599 new_long_description varchar2
600 )
601 is
602 begin
603
604 update IBE_CT_IMEDIA_SEARCH g
605 set
606 g.language = new_language ,
607 g.LAST_UPDATE_DATE = sysdate ,
608 g.DESCRIPTION = new_description ,
609 g.LONG_DESCRIPTION = new_long_description ,
610 g.INDEXED_SEARCH = (select ibe_search_setup_pvt.WriteToLob(new_description , new_long_description,a.concatenated_segments)
611 from mtl_system_items_kfv a
612 where a.inventory_item_id = old_inventory_item_id
613 and a.organization_id = old_organization_id
614 )
615 where g.INVENTORY_ITEM_ID = old_inventory_item_id
616 AND g.organization_id = old_organization_id
617 AND g.language = old_language ;
618
619 end ItemTL_Updated;
620
621
622
623 -----------------------------------------------
624 --PROCEDURE CALLED ON INSERT IN ITEMS_TL
625 --HERE YOU NEED TO INSERT A NEW ROW INTO IMEDIA_SEARCH TABLE
626 -----------------------------------------------
627
628 procedure ItemTL_Inserted(
629 new_inventory_item_id number,
630 new_organization_id number,
631 new_language varchar2,
632 new_description varchar2,
633 new_long_description varchar2
634 )
635 is
636
637 l_search_category_set varchar2(30);
638 l_search_web_status VARCHAR2(30):= 'PUBLISHED';
639 l_use_category_search VARCHAR2(30) := 'N ';
640 l_insert_flag boolean :=false;
641 l_item_exists Number:=0;
642 l_temp number;
643
644 CURSOR c_check_section_item_csr (c_inventory_item_id number, c_organization_id number) is
645 select 1 from mtl_system_items_b item
646 where item.inventory_item_id =c_inventory_item_id
647 and item.organization_id=c_organization_id
648 and item.web_status='PUBLISHED' and
649 exists (select 1 from ibe_dsp_section_items sec_item
650 where sec_item.inventory_item_id=item.inventory_item_id
651 and organization_id=item.organization_id);
652
653 begin
654 ----dbms_output.put_line('inputs:newInvItemId='||new_inventory_item_id
655 --||':newOrgId='||new_organization_id
656 --||':newlang='||new_language
657 --||':new_description='||new_description
658 --||':new_long_description='||new_long_description);
659
660 l_insert_flag := FALSE;
661 --Get IBE_SEARCH_CATEGORY_SET Profile value
662 l_use_category_search := FND_PROFILE.VALUE_specific('IBE_USE_CATEGORY_SEARCH',671,0,671);
663
664 ----dbms_output.put_line('l_use_category_search='||l_use_category_search);
665
666 l_search_category_set := FND_PROFILE.VALUE_specific('IBE_SEARCH_CATEGORY_SET',671,0,671);
667
668 ----dbms_output.put_line('l_search_category_set='||l_search_category_set);
669
670 IF l_search_category_set is not null then
671
672 l_item_exists:=Item_In_CatSetId_profile(new_inventory_item_id,new_organization_id);
673 --IF item is under the category_set_id specified by the profile_value THEN
674 ----dbms_output.put_line('l_item_exists='||l_item_exists);
675
676 IF l_item_exists >0 then
677 l_insert_flag := TRUE;
678
679 ELSE
680 ---------check whether item associated to some section------
681 IF l_use_category_search='N' THEN --profile set for section search
682 OPEN c_check_section_item_csr(new_inventory_item_id,new_organization_id);
683 FETCH c_check_section_item_csr into l_temp;
684 IF c_check_section_item_csr%FOUND then
685 l_insert_flag:=TRUE;
686 ELSE
687 l_insert_flag:=FALSE;
688 END IF;
689 CLOSE c_check_section_item_csr;
690 ELSE
691 l_insert_flag:= FALSE;
692 END IF;
693 -----------------------------------
694 END IF;
695
696 ELSE -- l_search_category_set is null ie ALL ----------------
697 l_insert_flag := TRUE;
698 END IF;
699
700 IF (l_insert_flag) THEN
701
702 ----dbms_output.put_line('l_insert_flag=true');
703 --Get IBE_SEARCH_WEB_STATUS profile value
704 l_search_web_status := FND_PROFILE.VALUE_specific('IBE_SEARCH_WEB_STATUS',671,0,671);
705
706 ----dbms_output.put_line('l_search_web_status='||l_search_web_status);
707
708 IF l_search_web_status = 'PUBLISHED' THEN
709 ----dbms_output.put_line('inserting row , web status PUBLISHED');
710 insert into ibe_ct_imedia_search (IBE_CT_IMEDIA_SEARCH_ID
711 , OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE
712 , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
713 , CATEGORY_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID
714 , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
715 , CATEGORY_SET_ID, WEB_STATUS)
716 SELECT
717 ibe_ct_imedia_search_s1.nextval,
718 0, FND_GLOBAL.user_id,
719 SYSDATE, FND_GLOBAL.user_id,
720 SYSDATE, FND_GLOBAL.conc_login_id,
721 c.category_id , new_organization_id ,
722 new_inventory_item_id, new_LANGUAGE,
723 new_DESCRIPTION, new_LONG_DESCRIPTION,
724 ibe_search_setup_pvt.WriteToLob(new_description , new_long_description ,a.concatenated_segments),
725 c.category_set_id , b.web_status
726 from mtl_system_items_kfv a, mtl_system_items_b b, mtl_item_categories c
727 where b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
728 and b.organization_id = a.organization_id
729 and b.INVENTORY_ITEM_ID = new_inventory_item_id
730 and b.ORGANIZATION_ID = new_organization_id
731 and a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
732 and a.ORGANIZATION_ID = c.organization_id
733 and b.web_status = 'PUBLISHED';
734
735 ELSIF l_search_web_status = 'PUBLISHED_UNPUBLISHED' THEN
736 ----dbms_output.put_line('inserting row , web status PUBLISHED UNPUBLISHED');
737 insert into ibe_ct_imedia_search (IBE_CT_IMEDIA_SEARCH_ID
738 , OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE
739 , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
740 , CATEGORY_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID
741 , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
742 , CATEGORY_SET_ID, WEB_STATUS)
743 SELECT
744 ibe_ct_imedia_search_s1.nextval,
745 0, FND_GLOBAL.user_id,
746 SYSDATE, FND_GLOBAL.user_id,
747 SYSDATE, FND_GLOBAL.conc_login_id,
748 c.category_id , new_organization_id ,
749 new_inventory_item_id, new_LANGUAGE,
750 new_DESCRIPTION, new_LONG_DESCRIPTION,
751 ibe_search_setup_pvt.WriteToLob(new_description , new_long_description ,a.concatenated_segments),
752 c.category_set_id , b.web_status
753 from mtl_system_items_kfv a, mtl_system_items_b b, mtl_item_categories c
754 where b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
755 and b.organization_id = a.organization_id
756 and b.INVENTORY_ITEM_ID = new_inventory_item_id
757 and b.ORGANIZATION_ID = new_organization_id
758 and a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
759 and a.ORGANIZATION_ID = c.organization_id
760 and b.web_status IN ('PUBLISHED', 'UNPUBLISHED');
761 ELSE
762 ----dbms_output.put_line('inserting row , web status ALL');
763 insert into ibe_ct_imedia_search (IBE_CT_IMEDIA_SEARCH_ID
764 , OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE
765 , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
766 , CATEGORY_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID
767 , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
768 , CATEGORY_SET_ID, WEB_STATUS)
769 SELECT
770 ibe_ct_imedia_search_s1.nextval,
771 0, FND_GLOBAL.user_id,
772 SYSDATE, FND_GLOBAL.user_id,
773 SYSDATE, FND_GLOBAL.conc_login_id,
774 c.category_id , new_organization_id ,
775 new_inventory_item_id, new_LANGUAGE,
776 new_DESCRIPTION, new_LONG_DESCRIPTION,
777 ibe_search_setup_pvt.WriteToLob(new_description , new_long_description ,a.concatenated_segments),
778 c.category_set_id , b.web_status
779 from mtl_system_items_kfv a, mtl_system_items_b b, mtl_item_categories c
780 where b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
781 and b.organization_id = a.organization_id
782 and b.INVENTORY_ITEM_ID = new_inventory_item_id
783 and b.ORGANIZATION_ID = new_organization_id
784 and a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
785 and a.ORGANIZATION_ID = c.organization_id;
786 END IF;
787 END IF;
788 ----dbms_output.put_line('END:ItemTL_Inserted');
789 end ItemTL_Inserted;
790
791
792 end IBE_Search_PVT;
[Home] [Help]