[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select category_set_id into l_vbh_catset_id
from mtl_default_category_sets
where functional_area_id = 11;
select id_flex_num into l_struct_code
from fnd_id_flex_structures a, mtl_category_sets_b b
where a.id_flex_num=b.structure_id
and b.category_set_id = l_vbh_catset_id
and id_flex_structure_code='PRODUCT_CATEGORIES';
select application_column_name, enabled_flag, to_number(flex_value_set_id)
into l_application_column, l_enabled_flag, l_flex_value_set_id
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MCAT'
and enabled_flag = 'Y'
and id_flex_num = l_struct_code;
select structure_id into l_struct_id
from mtl_category_sets_b
where category_set_id = p_vbh_catset_id;
select count(flex_value_set_id)
into l_count_segments
from fnd_id_flex_segments
where application_id = p_appl_id
and id_flex_code = p_id_flex_code
and enabled_flag = 'Y'
and id_flex_num = l_struct_id;
select flex_value_set_id into l_flex_value_set_id
from fnd_id_flex_segments
where application_id = p_appl_id
and id_flex_code = p_id_flex_code
and enabled_flag = 'Y'
and id_flex_num = l_struct_id;
select /*+ first_rows */ val.FLEX_VALUE_MEANING l_segment1,
val.DESCRIPTION l_description,
val.SUMMARY_FLAG l_summary_flag,
val.ENABLED_FLAG l_enabled_flag,
val.START_DATE_ACTIVE l_start_date_active,
val.END_DATE_ACTIVE l_end_date_active,
mtb.category_id category_id,
cat.STRUCTURE_ID l_structure_id,
1 update_flag -- Update Flag to indicate updateable records
from FND_FLEX_VALUES_VL val, --FND_ID_FLEX_SEGMENTS seg,
MTL_CATEGORY_SETS cat, MTL_CATEGORIES_B mtb, MTL_CATEGORIES_TL mtl
where val.FLEX_VALUE_SET_ID = l_value_set_id -- seg.FLEX_VALUE_SET_ID
-- and seg.ID_FLEX_CODE='MCAT'
-- and seg.APPLICATION_ID = '401'
-- and seg.APPLICATION_COLUMN_NAME ='SEGMENT1'
-- and seg.ID_FLEX_NUM=cat.STRUCTURE_ID
and cat.CATEGORY_SET_ID = l_vbh_catset_id
and cat.structure_id = mtb.structure_id
and mtb.segment1 = val.flex_value_meaning
and mtl.category_id = mtb.category_id
and (to_date(to_char(val.last_update_date,'DD/MM/YYYY HH:MI'),'DD/MM/YYYY HH:MI')> to_date(to_char(mtb.last_update_date,'DD/MM/YYYY HH:MI'),'DD/MM/YYYY HH:MI')
or mtl.description <> val.description
or mtb.end_date_active <> val.end_date_active)
union all
select val.FLEX_VALUE_MEANING l_segment1,
val.DESCRIPTION l_description,
val.SUMMARY_FLAG l_summary_flag,
val.ENABLED_FLAG l_enabled_flag,
val.START_DATE_ACTIVE l_start_date_active,
val.END_DATE_ACTIVE l_end_date_active,
to_number(null),
cat.STRUCTURE_ID l_structure_id,
0 -- Insert Flag to indicate a new node
from FND_FLEX_VALUES_VL val,-- FND_ID_FLEX_SEGMENTS seg,
MTL_CATEGORY_SETS cat
where val.FLEX_VALUE_SET_ID = l_value_set_id -- seg.FLEX_VALUE_SET_ID
-- and seg.ID_FLEX_CODE='MCAT'
-- and seg.APPLICATION_ID = '401'
-- and seg.APPLICATION_COLUMN_NAME ='SEGMENT1'
-- and seg.ID_FLEX_NUM=cat.STRUCTURE_ID
and cat.CATEGORY_SET_ID = l_vbh_catset_id
and not exists(select category_id from mtl_categories_b
where structure_id = cat.structure_id
and segment1 = val.flex_value_meaning);
select FLEX_VALUE_SET_ID
into l_value_set_id
from FND_ID_FLEX_SEGMENTS
where APPLICATION_ID = '401'
and ID_FLEX_CODE = 'MCAT'
and APPLICATION_COLUMN_NAME = 'SEGMENT1'
and ID_FLEX_NUM = (select STRUCTURE_ID
from MTL_CATEGORY_SETS_B
where CATEGORY_SET_ID = l_vbh_catset_id)
and ENABLED_FLAG = 'Y';
if c1_rec.update_flag = 1 then
l_category_rec.category_id := c1_rec.category_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Selected node for update: '||c1_rec.l_segment1);
INV_ITEM_CATEGORY_PUB.Update_Category(
p_api_version=>1,
x_return_status =>l_return_status,
x_errorcode=> l_errorcode ,
x_msg_count=> l_msg_count,
x_msg_data=> l_msg_data,
p_category_rec =>l_category_rec
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Selected node for insert: '|| c1_rec.l_segment1);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in insert/update of node' );
FND_FILE.PUT_LINE(FND_FILE.LOG, 'No nodes were updated or inserted. This could be because there were no changes in the valueset since the last time it was updated or there are no values defined in the hierarchy');