The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor c1 is select category_id, structure_id
from mtl_categories_vl
where nvl(DISABLE_DATE,sysdate) >= sysdate ;
/*****cursor get_lang is select language_code
from fnd_languages
where installed_flag in ('I','B');
cursor get_cat_b is select count(*) from AMS_MTL_CATEGORIES_DENORM_B;
cursor get_cat_tl is select count(*) from AMS_MTL_CATEGORIES_DENORM_TL;
delete from AMS_MTL_CATEGORIES_DENORM_B;
delete from AMS_MTL_CATEGORIES_DENORM_TL;
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
);
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);
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
);
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
));
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);
SELECT interest_type_id,Competitor_product_id
,COMPETITOR_PRODUCT_NAME
FROM ams_competitor_products_vl
WHERE interest_type_id is not null;
SELECT PRODUCT_CATEGORY_ID
,PRODUCT_CAT_SET_ID
FROM as_interest_types_b
where interest_type_id = l_interest_type_id;
SELECT interest_type
FROM as_interest_types_tl
where interest_type_id = l_interest_type_id
and language = userenv('LANG');
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;
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);
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;
SELECT description
from mtl_categories_vl
where category_id = cat_id ;
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;
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');