eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IBE_SEARCH_PVT SQL Statements

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

Line: 14


|This is primarily used for the triggers of mtl tables to update the
|IBE_CT_IMEDIA_SEARCH table .
|
|MTL_ITEM_CATEGORIES
|1) insert ,
|2) delete
|not sure if direct updates take place here
|
|MTL_SYSTEM_ITEMS_B
|dont care about its update as we are not using any of its columns right now
|Although we could just write the inset update delete on its tl table we
|still decided to write the delete on it :-)
|
|
|
| HISTORY
|   12-15-99  Savio T    Created.
|   10-16-03 abhandar  modified bug fix 3168087:catalog search performance
|   04-16-08 mgiridha  bug 6924793 ITEMS NOT SEARCHABLE BY PART NUMBER AFTER REPUBLISHING ON ISTORE.
+=======================================================================*/

-----------------------------------------------
--PROCEDURE CALLED ON INSERT IN ITEM CATEGORIES
--HERE YOU NEED TO INSERT A NEW ROW INTO IMEDIA_SEARCH TABLE
--added on 04/18 code to join on mtl_system_items_b
--table to get web status flag over
-----------------------------------------------
procedure Item_Category_Inserted(
new_category_id       number,
new_category_set_id   number,
new_inventory_item_id number,
new_organization_id   number)
is

l_insert_flag boolean:=false;

Line: 75


      l_insert_flag := TRUE;

Line: 77


       l_insert_flag := FALSE;

Line: 80


  l_insert_flag := TRUE;

Line: 84


IF (l_insert_flag) THEN
   --dbms_output.put_line('l_insert_flag=true');

Line: 93


     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, INVENTORY_ITEM_ID
        , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
        , CATEGORY_SET_ID, WEB_STATUS)
        SELECT
           ibe_ct_imedia_search_s1.nextval,
           0,       FND_GLOBAL.user_id,
           SYSDATE,       FND_GLOBAL.user_id,
           SYSDATE,       FND_GLOBAL.conc_login_id,
           new_category_id ,       new_organization_id ,
           new_inventory_item_id,       b.LANGUAGE,
           b.DESCRIPTION,       b.LONG_DESCRIPTION,
           ibe_search_setup_pvt.WriteToLob(b.description , b.long_description ,c.concatenated_segments),
           new_category_set_id   ,       a.web_status
        from  mtl_system_items_b a ,mtl_system_items_tl b ,mtl_system_items_kfv c
        where   b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
        and   b.organization_id   = a.organization_id
        and   b.INVENTORY_ITEM_ID = new_inventory_item_id
        and   b.ORGANIZATION_ID   = new_organization_id
        and   a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
        and   a.ORGANIZATION_ID   = c.organization_id
        and   a.web_status = 'PUBLISHED';

Line: 121


        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, INVENTORY_ITEM_ID
        , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
        , CATEGORY_SET_ID, WEB_STATUS)
        SELECT
            ibe_ct_imedia_search_s1.nextval,
            0,       FND_GLOBAL.user_id,
            SYSDATE,       FND_GLOBAL.user_id,
            SYSDATE,       FND_GLOBAL.conc_login_id,
            new_category_id ,       new_organization_id ,
            new_inventory_item_id,       b.LANGUAGE,
            b.DESCRIPTION,       b.LONG_DESCRIPTION,
            ibe_search_setup_pvt.WriteToLob(b.description, b.long_description ,c.concatenated_segments),
            new_category_set_id ,  a.web_status
            from  mtl_system_items_b a ,mtl_system_items_tl b ,mtl_system_items_kfv c
            where   b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
            and   b.organization_id   = a.organization_id
            and   b.INVENTORY_ITEM_ID = new_inventory_item_id
            and   b.ORGANIZATION_ID   = new_organization_id
            and   a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
            and   a.ORGANIZATION_ID   = c.organization_id
            and   a.web_status IN ('PUBLISHED', 'UNPUBLISHED');

Line: 147


         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, INVENTORY_ITEM_ID
        , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
        , CATEGORY_SET_ID, WEB_STATUS)
        SELECT
           ibe_ct_imedia_search_s1.nextval,
           0,       FND_GLOBAL.user_id,
           SYSDATE,       FND_GLOBAL.user_id,
           SYSDATE,       FND_GLOBAL.conc_login_id,
           new_category_id ,       new_organization_id ,
           new_inventory_item_id,       b.LANGUAGE,
           b.DESCRIPTION,       b.LONG_DESCRIPTION,
           ibe_search_setup_pvt.WriteToLob(b.description , b.long_description ,c.concatenated_segments),
           new_category_set_id   ,       a.web_status
        from  mtl_system_items_b a ,mtl_system_items_tl b ,mtl_system_items_kfv c
        where   b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
        and   b.organization_id   = a.organization_id
        and   b.INVENTORY_ITEM_ID = new_inventory_item_id
        and   b.ORGANIZATION_ID   = new_organization_id
        and   a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
        and   a.ORGANIZATION_ID   = c.organization_id;

Line: 174


END Item_Category_Inserted;

Line: 184


procedure Item_Category_Deleted(
old_category_id number,
old_category_set_id number,
old_inventory_item_id number,
old_organization_id number)
is
begin

delete from  ibe_ct_imedia_search c
where  c.CATEGORY_ID       = old_category_id
and    c.CATEGORY_SET_ID   = old_category_set_id
and    c.INVENTORY_ITEM_ID = old_inventory_item_id
and    c.ORGANIZATION_ID   = old_organization_id  ;

Line: 199


end Item_Category_Deleted;

Line: 209


procedure Item_Category_Updated(
old_category_id       number,new_category_id       number,
old_category_set_id   number,new_category_set_id   number,
old_inventory_item_id number,new_inventory_item_id number,
old_organization_id   number,new_organization_id   number)
is

l_search_category_set VARCHAR2(30);

Line: 223


select 1  from mtl_system_items_b item
where item.inventory_item_id =c_inventory_item_id
and item.organization_id=c_organization_id
and item.web_status='PUBLISHED' and
exists (select 1 from ibe_dsp_section_items sec_item
where sec_item.inventory_item_id=item.inventory_item_id
and organization_id=item.organization_id);

Line: 246


    update ibe_ct_imedia_search c
    set CATEGORY_ID        = new_category_id,
       CATEGORY_SET_ID     = new_category_set_id,
       LAST_UPDATE_DATE    = sysdate,
       INVENTORY_ITEM_ID   = new_inventory_item_id,
       ORGANIZATION_ID     = new_organization_id
    where  c.CATEGORY_ID       = old_category_id
    AND    c.CATEGORY_SET_ID   = old_category_set_id
    AND    c.INVENTORY_ITEM_ID = old_inventory_item_id
    AND    c.ORGANIZATION_ID   = old_organization_id;

Line: 264


        update ibe_ct_imedia_search c
        set CATEGORY_ID         = new_category_id,
            CATEGORY_SET_ID     = new_category_set_id,
            LAST_UPDATE_DATE    = sysdate,
            INVENTORY_ITEM_ID   = new_inventory_item_id,
            ORGANIZATION_ID     = new_organization_id
        where c.CATEGORY_ID    = old_category_id
        AND c.CATEGORY_SET_ID   = old_category_set_id
        AND c.INVENTORY_ITEM_ID = old_inventory_item_id
        AND c.ORGANIZATION_ID   = old_organization_id;

Line: 291


                    update ibe_ct_imedia_search c
                    set CATEGORY_ID      = new_category_id,
                    CATEGORY_SET_ID     = new_category_set_id,
                    LAST_UPDATE_DATE    = sysdate,
                    INVENTORY_ITEM_ID   = new_inventory_item_id,
                    ORGANIZATION_ID     = new_organization_id
                    where c.CATEGORY_ID   = old_category_id
                    AND c.CATEGORY_SET_ID  = old_category_set_id
                    AND c.INVENTORY_ITEM_ID= old_inventory_item_id
                    AND c.ORGANIZATION_ID  = old_organization_id;

Line: 305


                    DELETE ibe_ct_imedia_search where
                    CATEGORY_ID    = old_category_id
                    AND CATEGORY_SET_ID   = old_category_set_id
                    AND INVENTORY_ITEM_ID = old_inventory_item_id
                    AND ORGANIZATION_ID   = old_organization_id;

Line: 313


              DELETE ibe_ct_imedia_search where
              CATEGORY_ID    = old_category_id
              AND CATEGORY_SET_ID   = old_category_set_id
              AND INVENTORY_ITEM_ID = old_inventory_item_id
              AND ORGANIZATION_ID   = old_organization_id;

Line: 324


          select web_status into l_web_status  from mtl_system_items_b
          where inventory_item_id=new_inventory_item_id and organization_id= new_organization_id;

Line: 337


               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, INVENTORY_ITEM_ID
                , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
                , CATEGORY_SET_ID, WEB_STATUS)
              SELECT
                ibe_ct_imedia_search_s1.nextval,
                0,  FND_GLOBAL.user_id,
                SYSDATE, FND_GLOBAL.user_id,
                SYSDATE, FND_GLOBAL.conc_login_id,
                new_category_id , new_organization_id ,
                new_inventory_item_id,  b.LANGUAGE,
                b.DESCRIPTION, b.LONG_DESCRIPTION,
                ibe_search_setup_pvt.WriteToLob(b.description , b.long_description ,c.concatenated_segments),
                new_category_set_id , a.web_status
             from  mtl_system_items_b a ,mtl_system_items_tl b ,mtl_system_items_kfv c
             where b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
             and   b.organization_id   = a.organization_id
             and   b.INVENTORY_ITEM_ID = new_inventory_item_id
             and   b.ORGANIZATION_ID   = new_organization_id
             and   a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
             and   a.ORGANIZATION_ID   = c.organization_id;

Line: 366


END Item_Category_Updated;

Line: 375


procedure Item_Deleted(
old_inventory_item_id number,
old_organization_id number)
is
begin

delete from  ibe_ct_imedia_search c
where        c.INVENTORY_ITEM_ID = old_inventory_item_id
and          c.ORGANIZATION_ID   = old_organization_id ;

Line: 385


end Item_Deleted ;

Line: 406


      select 1 into l_item_exists_count from mtl_item_categories
      where inventory_item_id = p_inventory_item_id and
      organization_id= p_organization_id and category_set_id=l_search_category_set
      and rownum=1;

Line: 411


     select 1 into l_item_exists_count  from mtl_item_categories
     where inventory_item_id = p_inventory_item_id and
     organization_id= p_organization_id and rownum=1;

Line: 426


procedure Item_Updated(
old_inventory_item_id number,
old_organization_id   number,
old_web_status        varchar2,
new_web_status        varchar2)
is

l_search_web_status VARCHAR2(30);

Line: 449


    update ibe_ct_imedia_search
    set     web_status  = new_web_status
    where   inventory_item_id   = old_inventory_item_id
    and     organization_id     = old_organization_id;

Line: 474


            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, INVENTORY_ITEM_ID
            , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
            , CATEGORY_SET_ID, WEB_STATUS)
            SELECT
             ibe_ct_imedia_search_s1.nextval,
             0,       FND_GLOBAL.user_id,
             SYSDATE,       FND_GLOBAL.user_id,
             SYSDATE,       FND_GLOBAL.conc_login_id,
             d.category_id  ,       old_organization_id ,
             old_inventory_item_id,       b.LANGUAGE,
             b.DESCRIPTION,       b.LONG_DESCRIPTION,
             ibe_search_setup_pvt.WriteToLob(b.description , b.long_description ,a.concatenated_segments),
             d.category_set_id ,  new_web_status
             from  mtl_system_items_tl b ,mtl_item_categories d, mtl_system_items_b_kfv a
             where b.INVENTORY_ITEM_ID = old_inventory_item_id
	     and   b.ORGANIZATION_ID   = old_organization_id
	     and   b.INVENTORY_ITEM_ID = d.INVENTORY_ITEM_ID
	     and   b.organization_id   = d.organization_id
	     and   d.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
	     and   d.organization_id   = a.organization_id;

Line: 504


        update ibe_ct_imedia_search
        set     web_status  = new_web_status
        where   inventory_item_id   = old_inventory_item_id
        AND     organization_id     = old_organization_id;

Line: 530


            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, INVENTORY_ITEM_ID
            , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
            , CATEGORY_SET_ID, WEB_STATUS)
            SELECT
             ibe_ct_imedia_search_s1.nextval,
             0,       FND_GLOBAL.user_id,
             SYSDATE,       FND_GLOBAL.user_id,
             SYSDATE,       FND_GLOBAL.conc_login_id,
             d.category_id  ,       old_organization_id ,
             old_inventory_item_id,       b.LANGUAGE,
             b.DESCRIPTION,       b.LONG_DESCRIPTION,
             ibe_search_setup_pvt.WriteToLob(b.description , b.long_description ,a.concatenated_segments),
             d.category_set_id ,  new_web_status
             from  mtl_system_items_tl b ,mtl_item_categories d, mtl_system_items_b_kfv a
             where b.INVENTORY_ITEM_ID = old_inventory_item_id
	     and   b.ORGANIZATION_ID   = old_organization_id
	     and   b.INVENTORY_ITEM_ID = d.INVENTORY_ITEM_ID
	     and   b.organization_id   = d.organization_id
	     and   d.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
	     and   d.organization_id   = a.organization_id;

Line: 558


      Delete from ibe_ct_imedia_search
      where INVENTORY_ITEM_ID = old_inventory_item_id
      and   ORGANIZATION_ID   = old_organization_id;

Line: 565


end Item_Updated;

Line: 570


procedure ItemTL_Deleted(
old_inventory_item_id number,
old_organization_id   number,
old_language          varchar2)
is
begin

delete from  ibe_ct_imedia_search c
where        c.INVENTORY_ITEM_ID = old_inventory_item_id
and          c.LANGUAGE          = old_language
and          c.ORGANIZATION_ID   = old_organization_id ;

Line: 583


end ItemTL_Deleted;

Line: 593


procedure ItemTL_Updated(
old_inventory_item_id number,
old_organization_id number,
old_language varchar2,
new_language varchar2,
new_description varchar2,
new_long_description varchar2
)
is
begin

 update IBE_CT_IMEDIA_SEARCH g
 set
	g.language                = new_language ,
	g.LAST_UPDATE_DATE        = sysdate ,
        g.DESCRIPTION             = new_description ,
        g.LONG_DESCRIPTION        = new_long_description ,
        g.INDEXED_SEARCH        =  (select ibe_search_setup_pvt.WriteToLob(new_description , new_long_description,a.concatenated_segments)
                                    from mtl_system_items_kfv a
                                    where a.inventory_item_id = old_inventory_item_id
                                    and   a.organization_id   = old_organization_id
                                   )
   where g.INVENTORY_ITEM_ID    = old_inventory_item_id
   AND  g.organization_id       = old_organization_id
   AND  g.language              = old_language ;

Line: 619


end ItemTL_Updated;

Line: 628


procedure ItemTL_Inserted(
new_inventory_item_id number,
new_organization_id number,
new_language varchar2,
new_description varchar2,
new_long_description varchar2
)
is

 l_search_category_set varchar2(30);

Line: 640


 l_insert_flag boolean :=false;

Line: 645


select 1  from mtl_system_items_b item
where item.inventory_item_id =c_inventory_item_id
and item.organization_id=c_organization_id
and item.web_status='PUBLISHED' and
exists (select 1 from ibe_dsp_section_items sec_item
where sec_item.inventory_item_id=item.inventory_item_id
and organization_id=item.organization_id);

Line: 660


l_insert_flag := FALSE;

Line: 677


        l_insert_flag := TRUE;

Line: 685


                     l_insert_flag:=TRUE;

Line: 687


                    l_insert_flag:=FALSE;

Line: 691


             l_insert_flag:= FALSE;

Line: 697


   l_insert_flag := TRUE;

Line: 700


IF (l_insert_flag) THEN

   ----dbms_output.put_line('l_insert_flag=true');

Line: 710


        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, INVENTORY_ITEM_ID
        , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
        , CATEGORY_SET_ID, WEB_STATUS)
        SELECT
        ibe_ct_imedia_search_s1.nextval,
        0,       FND_GLOBAL.user_id,
        SYSDATE,       FND_GLOBAL.user_id,
        SYSDATE,       FND_GLOBAL.conc_login_id,
        c.category_id ,       new_organization_id ,
        new_inventory_item_id,       new_LANGUAGE,
        new_DESCRIPTION,       new_LONG_DESCRIPTION,
        ibe_search_setup_pvt.WriteToLob(new_description , new_long_description ,a.concatenated_segments),
        c.category_set_id   ,       b.web_status
        from  mtl_system_items_kfv a, mtl_system_items_b b, mtl_item_categories c
        where   b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
        and   b.organization_id   = a.organization_id
        and   b.INVENTORY_ITEM_ID = new_inventory_item_id
        and   b.ORGANIZATION_ID   = new_organization_id
        and   a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
        and   a.ORGANIZATION_ID   = c.organization_id
        and   b.web_status = 'PUBLISHED';

Line: 737


        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, INVENTORY_ITEM_ID
        , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
        , CATEGORY_SET_ID, WEB_STATUS)
        SELECT
        ibe_ct_imedia_search_s1.nextval,
        0,       FND_GLOBAL.user_id,
        SYSDATE,       FND_GLOBAL.user_id,
        SYSDATE,       FND_GLOBAL.conc_login_id,
        c.category_id ,       new_organization_id ,
        new_inventory_item_id,       new_LANGUAGE,
        new_DESCRIPTION,       new_LONG_DESCRIPTION,
        ibe_search_setup_pvt.WriteToLob(new_description , new_long_description ,a.concatenated_segments),
        c.category_set_id   ,       b.web_status
        from  mtl_system_items_kfv a, mtl_system_items_b b, mtl_item_categories c
        where   b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
        and   b.organization_id   = a.organization_id
        and   b.INVENTORY_ITEM_ID = new_inventory_item_id
        and   b.ORGANIZATION_ID   = new_organization_id
        and   a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
        and   a.ORGANIZATION_ID   = c.organization_id
        and   b.web_status IN ('PUBLISHED', 'UNPUBLISHED');

Line: 763


        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, INVENTORY_ITEM_ID
        , LANGUAGE, DESCRIPTION, LONG_DESCRIPTION, INDEXED_SEARCH
        , CATEGORY_SET_ID, WEB_STATUS)
        SELECT
        ibe_ct_imedia_search_s1.nextval,
        0,       FND_GLOBAL.user_id,
        SYSDATE,       FND_GLOBAL.user_id,
        SYSDATE,       FND_GLOBAL.conc_login_id,
        c.category_id ,       new_organization_id ,
        new_inventory_item_id,       new_LANGUAGE,
        new_DESCRIPTION,       new_LONG_DESCRIPTION,
        ibe_search_setup_pvt.WriteToLob(new_description , new_long_description ,a.concatenated_segments),
        c.category_set_id   ,       b.web_status
        from  mtl_system_items_kfv a, mtl_system_items_b b, mtl_item_categories c
        where  b.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID
        and   b.organization_id   = a.organization_id
        and   b.INVENTORY_ITEM_ID = new_inventory_item_id
        and   b.ORGANIZATION_ID   = new_organization_id
        and   a.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
        and   a.ORGANIZATION_ID   = c.organization_id;

Line: 789


end ItemTL_Inserted;