APPS.AMS_MTL_CATG_LOADER_PVT SQL Statements

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

Line: 36

 cursor c1 is select category_id, structure_id
               from mtl_categories_vl
     where  nvl(DISABLE_DATE,sysdate)  >= sysdate ;

Line: 43

/*****cursor get_lang is select language_code
                     from fnd_languages
                    where installed_flag in ('I','B');

Line: 49

cursor get_cat_b is select count(*) from  AMS_MTL_CATEGORIES_DENORM_B;

Line: 51

cursor get_cat_tl is select count(*) from  AMS_MTL_CATEGORIES_DENORM_TL;

Line: 68

    delete from AMS_MTL_CATEGORIES_DENORM_B;

Line: 78

    delete from AMS_MTL_CATEGORIES_DENORM_TL;

Line: 97

                        INSERT INTO  AMS_MTL_CATEGORIES_DENORM_B (
                          CATEGORY_ID      ,
                          STRUCTURE_ID   ,
                          LAST_UPDATE_DATE  ,
                          LAST_UPDATED_BY  ,
                          CREATION_DATE  ,
                          CREATED_BY    ,
                          LAST_UPDATE_LOGIN ,
                          concatenated_ids   )
                          VALUES (
                          c1_rec.category_id,
                           c1_rec.structure_id,
                           sysdate,
                           FND_GLOBAL.user_id,
                           sysdate,
                           FND_GLOBAL.user_id,
                           FND_GLOBAL.conc_login_id,
                          FND_FLEX_KEYVAL.concatenated_values
                          );

Line: 117

                        insert into  AMS_MTL_CATEGORIES_DENORM_TL (
                          CATEGORY_ID      ,
                          LANGUAGE   ,
                          SOURCE_LANG     ,
                          LAST_UPDATE_DATE ,
                          LAST_UPDATED_BY   ,
                          CREATION_DATE      ,
                          CREATED_BY         ,
                          LAST_UPDATE_LOGIN  ,
                          concatenated_description )
    SELECT
                          c1_rec.category_id,
                          l.language_code,
                          USERENV('LANG'),
                          sysdate,
                          FND_GLOBAL.user_id,
                          sysdate,
                          FND_GLOBAL.user_id,
                          FND_GLOBAL.conc_login_id,
                          FND_FLEX_KEYVAL.concatenated_descriptions
                          FROM     fnd_languages l
      WHERE  l.installed_flag IN ('I', 'B')
      AND NOT EXISTS (SELECT   NULL
                      FROM     AMS_MTL_CATEGORIES_DENORM_TL t
                      WHERE  t.category_id = c1_rec.category_id
                      AND t.language = l.language_code);

Line: 171

  delete from AMS_MTL_CATEGORIES_DENORM_TL T
  where not exists
    (select NULL
    from AMS_MTL_CATEGORIES_DENORM_B B
    where B.CATEGORY_ID = T.CATEGORY_ID
    );

Line: 178

  update AMS_MTL_CATEGORIES_DENORM_TL T set (
      CATEGORY_ID
    ) = (select
      B.CATEGORY_ID
    from AMS_MTL_CATEGORIES_DENORM_TL B
    where B.CATEGORY_ID = T.CATEGORY_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.CATEGORY_ID,
      T.LANGUAGE
  ) in (select
      SUBT.CATEGORY_ID,
      SUBT.LANGUAGE
    from AMS_MTL_CATEGORIES_DENORM_TL SUBB, AMS_MTL_CATEGORIES_DENORM_TL SUBT
    where SUBB.CATEGORY_ID = SUBT.CATEGORY_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.CATEGORY_ID <> SUBT.CATEGORY_ID
  ));

Line: 198

     INSERT INTO   AMS_MTL_CATEGORIES_DENORM_TL (
          CATEGORY_ID      ,
          LANGUAGE   ,
          SOURCE_LANG     ,
          LAST_UPDATE_DATE ,
          LAST_UPDATED_BY   ,
          CREATION_DATE      ,
          CREATED_BY         ,
          LAST_UPDATE_LOGIN  ,
          concatenated_description )
   SELECT
            B.CATEGORY_ID,
           l.language_code,
           B.SOURCE_LANG,
            B.LAST_UPDATE_DATE,
           B.LAST_UPDATED_BY,
           B.CREATION_DATE,
           B.CREATED_BY,
           B.LAST_UPDATE_LOGIN,
           B.concatenated_description
  from AMS_MTL_CATEGORIES_DENORM_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from AMS_MTL_CATEGORIES_DENORM_TL T
    where T.CATEGORY_ID = B.CATEGORY_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 236

   SELECT interest_type_id,Competitor_product_id
   ,COMPETITOR_PRODUCT_NAME
   FROM ams_competitor_products_vl
   WHERE interest_type_id is not null;

Line: 247

   SELECT PRODUCT_CATEGORY_ID
   ,PRODUCT_CAT_SET_ID
   FROM as_interest_types_b
   where interest_type_id = l_interest_type_id;

Line: 256

   SELECT interest_type
   FROM as_interest_types_tl
   where interest_type_id = l_interest_type_id
   and language = userenv('LANG');

Line: 278

         UPDATE ams_competitor_products_b
         SET category_id = l_cat_rec.product_category_id
         ,category_set_id = l_cat_rec.product_cat_set_id
         WHERE competitor_product_id = l_competitor_prod_id;

Line: 323

   SELECT activity_product_id,category_set_id
   ,category_id, act_product_used_by_id, arc_act_product_used_by
   FROM ams_act_products act
   WHERE level_type_code = 'FAMILY'
   and category_id is not null
   and category_set_id is not null
   and category_set_id not in (select distinct category_set_id
          from ENI_PROD_DEN_HRCHY_PARENTS_V);

Line: 341

   SELECT dt.target_catg_id cat_id, hd.target_catg_set_id cat_set_id
   FROM ego_catg_map_hdrs_b hd
   ,ego_catg_map_dtls dt
   WHERE hd.source_catg_set_id = l_category_set_id
   AND dt.source_catg_id = l_category_id
   AND hd.catg_map_id = dt.catg_map_id;

Line: 352

   SELECT description
   from mtl_categories_vl
   where category_id = cat_id ;

Line: 375

         UPDATE ams_act_products
         SET category_id = l_cat_rec.cat_id
         ,category_set_id = l_cat_rec.cat_set_id
         WHERE activity_product_id = l_activity_product_id;

Line: 401

     UPDATE AMS_ACT_PRODUCTS A
     SET (A.CATEGORY_ID, A.CATEGORY_SET_ID) =
                         (SELECT B.CATEGORY_ID, B.CATEGORY_SET_ID
                            FROM MTL_ITEM_CATEGORIES B,
                                 MTL_DEFAULT_CATEGORY_SETS D
                           WHERE B.INVENTORY_ITEM_ID = A.INVENTORY_ITEM_ID
                             AND B.ORGANIZATION_ID = A.ORGANIZATION_ID
                             AND D.CATEGORY_SET_ID = B.CATEGORY_SET_ID
                             AND D.FUNCTIONAL_AREA_ID = 11)
     WHERE A.CATEGORY_ID IS NULL
     AND A.LEVEL_TYPE_CODE = 'PRODUCT'
     AND ARC_ACT_PRODUCT_USED_BY IN ('CAMP','CSCH');