APPS.AS_CATALOG_MIGRATION SQL Statements

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

Line: 134

        select B.interest_type_id, TL.interest_type, TL.description, B.Expected_Purchase_Flag,B.enabled_flag
        from as_interest_types_b B, as_interest_types_tl TL
        where B.interest_type_id = TL.interest_type_id
        and TL.language = userenv('LANG');

Line: 141

        select B.interest_code_id, TL.code, TL.description,B.enabled_flag
        from as_interest_codes_b B, as_interest_codes_tl TL
        where B.interest_code_id = TL.interest_code_id
        and TL.language = userenv('LANG')
        and B.interest_type_id = c_interest_type_id
        and B.parent_interest_code_id is null;

Line: 149

        select B.interest_code_id, TL.code, TL.description,B.enabled_flag
        from as_interest_codes_b B, as_interest_codes_tl TL
        where B.interest_code_id = TL.interest_code_id
        and TL.language = userenv('LANG')
        and B.parent_interest_code_id = c_interest_code_id;

Line: 183

        select C.structure_id,C.control_level,C.mult_item_cat_assign_flag
        into l_structure_id, l_control_level, l_mult_item_cat_assign_flag
        from MTL_CATEGORY_SETS C where C.category_set_id = l_category_set_id;

Line: 200

            select FIFS.ID_FLEX_NUM into l_old_structure_id
            from FND_ID_FLEX_STRUCTURES FIFS
            where FIFS.ID_FLEX_CODE = 'MCAT' AND FIFS.APPLICATION_ID = 401  AND FIFS.ID_FLEX_STRUCTURE_CODE = 'SALES_CATEGORIES';

Line: 215

        SELECT ATTR_GROUP_ID INTO l_attr_group_id FROM EGO_FND_DSC_FLX_CTX_EXT WHERE APPLICATION_ID = 431 AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_PRODUCT_CATEGORY_SET' AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'SalesAndMarketing';

Line: 264

        Update AS_INTEREST_TYPES_B set product_category_id = l_int_type_cat_id, product_cat_set_id = l_category_set_id where interest_type_id = scr.interest_type_id;

Line: 302

             Update AS_INTEREST_CODES_B set product_category_id = l_pri_int_code_cat_id, product_cat_set_id = l_category_set_id where interest_code_id = scr2.interest_code_id;

Line: 340

                 Update AS_INTEREST_CODES_B set product_category_id = l_sec_int_code_cat_id, product_cat_set_id = l_category_set_id where interest_code_id = scr3.interest_code_id;

Line: 532

        select category_set_id, hierarchy_enabled
        from MTL_CATEGORY_SETS
        where category_set_name = G_SME_CATEGORY_SET_NAME;

Line: 537

        select 1
        from MTL_CATEGORY_SETS S, MTL_DEFAULT_CATEGORY_SETS D
        where S.category_set_id = D.category_set_id
        and D.functional_area_id = G_FUNCTIONAL_AREA
        and D.category_set_id = c_category_set_id;

Line: 590

    select C.structure_id, C.control_level into l_structure_id, l_control_level
    from MTL_DEFAULT_CATEGORY_SETS D, MTL_CATEGORY_SETS C
    where D.functional_area_id = G_FUNCTIONAL_AREA and D.category_set_id = C.category_set_id;

Line: 594

    select MTL_CATEGORY_SETS_S.NEXTVAL into l_next_val from dual;

Line: 596

    MTL_CATEGORY_SETS_PKG.INSERT_ROW (
      X_ROWID => l_row_id,
      X_CATEGORY_SET_ID         => l_next_val,
      X_CATEGORY_SET_NAME       => G_SME_CATEGORY_SET_NAME,
      X_DESCRIPTION             => G_SME_CATEGORY_SET_NAME,
      X_STRUCTURE_ID            => l_structure_id,
      X_VALIDATE_FLAG           => 'Y',
      X_MULT_ITEM_CAT_ASSIGN_FLAG   => 'N',
      X_CONTROL_LEVEL_UPDT_FLAG     => 'N',
      X_MULT_ITEM_CAT_UPDT_FLAG     => 'N',
      X_VALIDATE_FLAG_UPDT_FLAG     => 'N',
      X_HIERARCHY_ENABLED           => 'Y',
      X_CONTROL_LEVEL               => l_control_level,
      X_DEFAULT_CATEGORY_ID         => null,
      X_LAST_UPDATE_DATE            => SYSDATE,
      X_LAST_UPDATED_BY             => 0,
      X_CREATION_DATE               => SYSDATE,
      X_CREATED_BY                  => 0,
      X_LAST_UPDATE_LOGIN           => 0 );

Line: 638

  SELECT count(*) into l_num_cat_set_grants
  FROM fnd_grants fg, fnd_objects fo
  WHERE fg.object_id = fo.object_id
  and fo.obj_name = l_category_set_object
  and fg.instance_pk1_value=p_category_set_id;

Line: 695

    Update MTL_DEFAULT_CATEGORY_SETS
    set category_set_id = p_category_set_id
    where functional_area_id = G_FUNCTIONAL_AREA;

Line: 715

    Update Mtl_Category_Sets
    set hierarchy_enabled = 'Y'
    where category_set_id = p_category_set_id;

Line: 722

It creates/updates records in following MTL tables:
a) MTL_CATEGORIES_B
b) MTL_CATEGORIES_TL
c) MTL_CATEGORY_SET_VALID_CATS
d) MTL_ITEM_CATEGORIES
*/
PROCEDURE Process_Categories(p_int_typ_cod_id       IN NUMBER,
                             p_structure_id         IN NUMBER,
                             p_old_structure_id     IN NUMBER,
                             p_category_set_id      IN NUMBER,
                             p_control_level        IN NUMBER,
                             p_mult_item_cat_assign_flag IN VARCHAR2,
                             p_parent_category_id   IN NUMBER,
                             p_category_name        IN VARCHAR2,
                             p_description          IN VARCHAR2,
                             p_interest_level       IN NUMBER,
                             p_expected_purchase    IN VARCHAR2,
                             p_level0_enabled_flag  IN VARCHAR2,
                             p_level1_enabled_flag  IN VARCHAR2,
                             p_level2_enabled_flag  IN VARCHAR2,
                             p_attr_group_id        IN NUMBER,
                             p_name_count_tab       IN OUT NOCOPY Name_Count_Tab,
                             x_return_status        OUT NOCOPY VARCHAR2,
                             x_msg_count            OUT NOCOPY NUMBER,
                             x_msg_data             OUT NOCOPY VARCHAR2,
                             x_category_id          OUT NOCOPY NUMBER,
                             x_warning_flag         OUT NOCOPY VARCHAR2) IS

    CURSOR C_Get_Items(c_structure_id Number, c_type_code_id Number, c_interest_level Number) IS
        select MIC.INVENTORY_ITEM_ID,
               MIC.ORGANIZATION_ID
          from
              (      SELECT CATEGORY_ID
                       FROM MTL_CATEGORIES_B MC
                      WHERE MC.STRUCTURE_ID = c_structure_id
                        and DECODE(c_interest_level,0,MC.SEGMENT1,1,MC.SEGMENT2,2,MC.SEGMENT3,NULL) = c_type_code_id
                        and DECODE(c_interest_level,0,MC.SEGMENT2,1,MC.SEGMENT3,NULL) IS NULL
                        and DECODE(c_interest_level,0,MC.SEGMENT3,NULL) IS NULL
              ) MC1,
                       MTL_ITEM_CATEGORIES MIC
        where  MIC.CATEGORY_ID = MC1.CATEGORY_ID;

Line: 765

        select 1
        from MTL_CATEGORY_SET_VALID_CATS
        where
            category_id = c_category_id
            and category_set_id = c_category_set_id;

Line: 772

        select 1
          from EGO_PRODUCT_CAT_SET_EXT
         where category_set_id = c_category_set_id
           and category_id     = c_category_id;

Line: 930

             INSERT INTO EGO_PRODUCT_CAT_SET_EXT ( EXTENSION_ID, CATEGORY_SET_ID, CATEGORY_ID, ATTR_GROUP_ID,
             CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
             INCLUDE_IN_FORECAST, EXPECTED_PURCHASE, EXCLUDE_USER_VIEW )
             VALUES (EGO_EXTFWK_S.NEXTVAL, p_category_set_id, l_out_category_id, p_attr_group_id, -1,  sysdate, -1,  sysdate, -1, 'Y', p_expected_purchase, l_exclude_user_view);

Line: 935

         UPDATE EGO_PRODUCT_CAT_SET_EXT
            SET EXPECTED_PURCHASE = p_expected_purchase,
                EXCLUDE_USER_VIEW = l_exclude_user_view
          WHERE CATEGORY_SET_ID = p_category_set_id
                AND CATEGORY_ID     = l_out_category_id;

Line: 1041

                         INSERT INTO EGO_PRODUCT_CAT_SET_EXT ( EXTENSION_ID, CATEGORY_SET_ID, CATEGORY_ID, ATTR_GROUP_ID,
                         CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
                         INCLUDE_IN_FORECAST, EXPECTED_PURCHASE, EXCLUDE_USER_VIEW )
                         VALUES ( EGO_EXTFWK_S.NEXTVAL, p_category_set_id, l_out_legacy_category_id, p_attr_group_id, -1,  sysdate, -1,  sysdate, -1, 'Y', p_expected_purchase, l_exclude_user_view);

Line: 1046

                         UPDATE EGO_PRODUCT_CAT_SET_EXT
                            SET EXPECTED_PURCHASE = p_expected_purchase,
                                EXCLUDE_USER_VIEW = l_exclude_user_view
                         WHERE CATEGORY_SET_ID = p_category_set_id
                            AND CATEGORY_ID     = l_out_legacy_category_id;

Line: 1124

        select 1
        from mtl_parameters
        where organization_id = master_organization_id
        and organization_id = c_organization_id;

Line: 1135

          SELECT  COUNT( category_id ), COUNT( DECODE(category_id, p_category_id,1, NULL) )
          FROM  mtl_item_categories
          WHERE
                  inventory_item_id = p_inventory_item_id
             AND  organization_id   = p_organization_id
             AND  category_set_id = p_category_set_id;

Line: 1146

          SELECT 'x' --2879647
          FROM  mtl_system_items_b
          WHERE  inventory_item_id = p_inventory_item_id
            AND  organization_id   = p_organization_id;

Line: 1153

          SELECT  'x'
          FROM  mtl_categories_b
          WHERE  category_id = p_category_id
            AND NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE;

Line: 1165

    Update MTL_ITEM_CATEGORIES
    Set category_id = p_category_id
    where category_set_id = p_category_set_id
    and organization_id = p_organization_id
    and inventory_item_id = p_inventory_item_id;

Line: 1238

                DELETE FROM mtl_item_categories
                WHERE organization_id   = p_organization_id
                AND inventory_item_id = p_inventory_item_id
                AND category_set_id = p_category_set_id;

Line: 1293

            FND_MSG_PUB.Delete_Msg(k);

Line: 1311

    select category_id
    from MTL_CATEGORIES_B
    where structure_id = c_structure_id
          and segment1 = c_category_name
          and segment2 is null
          and segment3 is null
          and segment4 is null
          and segment5 is null
          and segment6 is null
          and segment7 is null
          and segment8 is null
          and segment9 is null
          and segment10 is null
          and segment11 is null
          and segment12 is null
          and segment13 is null
          and segment14 is null
          and segment15 is null
          and segment16 is null
          and segment17 is null
          and segment18 is null
          and segment19 is null
          and segment20 is null;

Line: 1354

        select B.category_id, B.segment1 category_name from mtl_category_set_valid_cats V, mtl_categories_b B
        where V.category_set_id=c_category_set_id
        and V.parent_category_id=c_category_id
        and V.category_id = B.category_id;

Line: 1366

        update mtl_item_categories
        set category_id=p_category_id
        where category_id=scr.category_id
        and category_set_id=p_category_set_id;

Line: 1370

        delete from ego_product_cat_set_ext
        where category_id=scr.category_id and category_set_id=p_category_set_id;

Line: 1372

        delete from mtl_category_set_valid_cats
        where category_id=scr.category_id and category_set_id=p_category_set_id;

Line: 1374

        delete from mtl_categories_b where category_id=scr.category_id;

Line: 1375

        delete from mtl_categories_tl where category_id=scr.category_id;

Line: 1382

delete from mtl_categories_tl where category_id in
(select category_id from mtl_categories_b where structure_id in
(select structure_id from mtl_category_sets where category_set_name='SME Product Catalog'));

Line: 1385

delete from mtl_categories_b where structure_id in
(select structure_id from mtl_category_sets where category_set_name='SME Product Catalog');

Line: 1387

delete from mtl_category_set_valid_cats where category_set_id in
(select category_set_id from mtl_category_sets where category_set_name='SME Product Catalog');

Line: 1389

delete from mtl_item_categories where category_set_id in
(select category_set_id from mtl_category_sets where category_set_name='SME Product Catalog');

Line: 1391

delete from EGO_PRODUCT_CAT_SET_EXT where category_set_id in
(select category_set_id from mtl_category_sets where category_set_name='SME Product Catalog');