[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_VALUESET_FROM_CATEGORY
(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
l_struct_id NUMBER; -- structure id of default category set associated with Product reporting functional area
SELECT
V.CONCATENATED_SEGMENTS PARENT_CODE,
V1.CONCATENATED_SEGMENTS CHILD_CODE,
DECODE(F.SUMMARY_FLAG, 'Y', 'P', 'C') RANGE_ATTRIBUTE
FROM MTL_CATEGORY_SET_VALID_CATS T, MTL_CATEGORIES_KFV V, MTL_CATEGORIES_KFV V1, FND_FLEX_VALUES F
WHERE T.CATEGORY_SET_ID = g_catset_id
AND T.CATEGORY_ID = V1.CATEGORY_ID
AND T.PARENT_CATEGORY_ID = V.CATEGORY_ID
AND V1.CONCATENATED_SEGMENTS = F.FLEX_VALUE
AND F.FLEX_VALUE_SET_ID = l_flex_val_set_id
AND NOT EXISTS (SELECT NULL FROM FND_FLEX_VALUE_NORM_HIERARCHY H
WHERE FLEX_VALUE_SET_ID = F.FLEX_VALUE_SET_ID
AND PARENT_FLEX_VALUE = V.CONCATENATED_SEGMENTS
AND RANGE_ATTRIBUTE = DECODE(F.SUMMARY_FLAG, 'Y', 'P', 'C')
AND CHILD_FLEX_VALUE_LOW = V1.CONCATENATED_SEGMENTS
AND CHILD_FLEX_VALUE_HIGH = V1.CONCATENATED_SEGMENTS);
select l_top_node parent_code,
concatenated_segments child_code,
decode(f.summary_flag,'Y','P','C') range_attribute
from mtl_category_set_valid_cats a,
mtl_categories_kfv b, fnd_flex_values f
where parent_category_id is null
and category_set_id = g_catset_id
and a.category_id = b.category_id
and b.structure_id = l_struct_id
and b.concatenated_segments = f.flex_value
and f.flex_value_set_id = l_flex_val_set_id
and l_top_node is not null
and not exists( select 'X' from fnd_flex_value_norm_hierarchy h
where flex_value_set_id = f.flex_value_set_id
and parent_flex_value = l_top_node
and range_attribute= decode(f.summary_flag,'Y','P','C')
and child_flex_value_low = b.concatenated_segments
and child_flex_value_high = b.concatenated_segments);
SELECT b.concatenated_segments nodes
FROM mtl_category_set_valid_cats a, mtl_categories_kfv b
WHERE a.category_set_id = g_catset_id
AND a.category_id = b.category_id
AND b.structure_id = l_struct_id
INTERSECT
SELECT child_code nodes
FROM eni_vset_hrchy_temp
WHERE hrchy_flag = 'P'
-- AND parent_code is not null
START with child_code = l_top_node
CONNECT BY child_code = prior parent_code;
SELECT STRUCTURE_ID INTO l_struct_id
FROM MTL_CATEGORY_SETS_B
WHERE CATEGORY_SET_ID = g_catset_id;
Select segment_num into l_count
from fnd_id_flex_segments
where id_flex_num = l_struct_id
and enabled_flag = 'Y';
select a.flex_value_set_id, a.top_node, b.flex_value_set_name
INTO l_flex_val_set_id, l_top_node, l_value_set_name
FROM ego_financial_reporting_agv a, fnd_flex_value_sets b
WHERE a.category_set_id = g_catset_id
AND a.flex_value_set_id = b.flex_value_set_id
AND rownum = 1;
SELECT flex_value INTO l_top_node
FROM fnd_flex_values
WHERE flex_value_set_id = l_flex_val_set_id
AND flex_value_id = l_top_node;
INSERT INTO ENI_VSET_HRCHY_TEMP(
CHILD_CODE,
PARENT_CODE,
SUMMARY_FLAG,
child_value_id,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
HRCHY_FLAG)
SELECT
a.FLEX_VALUE CHILD_CODE,
PARENT_FLEX_VALUE PARENT_CODE,
a.SUMMARY_FLAG,
b.flex_value_id,
b.ENABLED_FLAG,
b.START_DATE_ACTIVE,
b.END_DATE_ACTIVE,
'P'
FROM FND_FLEX_VALUE_CHILDREN_V a, fnd_flex_values b
WHERE a.FLEX_VALUE_SET_ID = l_flex_val_set_id
and a.flex_value_set_id = b.flex_value_set_id
and a.flex_value = b.flex_value
UNION
SELECT FLEX_VALUE,
null,
SUMMARY_FLAG,
flex_value_id,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
'P'
FROM FND_FLEX_VALUES A
WHERE flex_value_set_id = l_flex_val_set_id
AND not exists (Select flex_value
from fnd_flex_value_children_v
where flex_value_set_id = a.flex_value_set_id
and flex_value = a.flex_value);
INSERT INTO ENI_VSET_HRCHY_TEMP (
CHILD_CODE,
PARENT_CODE,
SUMMARY_FLAG,
CHILD_VALUE_ID,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
HRCHY_FLAG)
SELECT CHILD_CODE,
PARENT_CODE,
summary_flag,
child_value_id,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
'Y'
FROM ENI_VSET_HRCHY_TEMP H
CONNECT BY PRIOR CHILD_CODE = PARENT_CODE
START WITH CHILD_CODE = l_top_node;
UPDATE eni_vset_hrchy_temp
SET hrchy_flag = 'Y'
WHERE hrchy_flag = 'P';
FND_FILE.PUT_LINE(FND_FILE.LOG, 'NEW NODES: New values that will be inserted into the value set');
SELECT
V.CONCATENATED_SEGMENTS ,
V.ENABLED_FLAG,
T.DESCRIPTION,
V.START_DATE_ACTIVE,
V.DISABLE_DATE,
NVL((SELECT ''Y'' FROM MTL_CATEGORY_SET_VALID_CATS C
WHERE C.CATEGORY_SET_ID = H.CATEGORY_SET_ID
AND C.PARENT_CATEGORY_ID = V.CATEGORY_ID
AND ROWNUM = 1), ''N'') SUMMARY_FLAG
FROM MTL_CATEGORIES_KFV V,
MTL_CATEGORIES_TL T,
MTL_CATEGORY_SET_VALID_CATS H
WHERE V.STRUCTURE_ID = :l_struct_id
AND V.CATEGORY_ID = T.CATEGORY_ID
AND T.LANGUAGE = USERENV(''LANG'')
AND V.CATEGORY_ID = H.CATEGORY_ID
AND H.CATEGORY_SET_ID = :g_catset_id
AND NOT EXISTS
(SELECT NULL FROM FND_FLEX_VALUES F
WHERE F.FLEX_VALUE = V.CONCATENATED_SEGMENTS
AND F.FLEX_VALUE_SET_ID = :l_flex_val_set_id)';
FND_FILE.PUT_LINE(FND_FILE.LOG, ' Opening Cursor to Insert new values');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error while Inserting '||l_flex_value||', '||l_msg);
errbuf := 'Error while Inserting '||l_flex_value||', '||l_msg;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'UPDATED NODES: Updating Existing Values');
SELECT
X.CHILD_VALUE_ID,
X.CHILD_CODE,
X.NEW_ENABLED_FLAG,
X.NEW_DESCRIPTION,
X.NEW_START_DATE,
X.NEW_END_DATE,
X.NEW_SUMMARY_FLAG
FROM
(
SELECT
F.FLEX_VALUE_ID CHILD_VALUE_ID,
F.FLEX_VALUE CHILD_CODE,
T.DESCRIPTION NEW_DESCRIPTION,
NVL((SELECT V.ENABLED_FLAG FROM MTL_CATEGORY_SET_VALID_CATS C
WHERE C.CATEGORY_SET_ID = H.CATEGORY_SET_ID
AND C.CATEGORY_ID = V.CATEGORY_ID
AND ROWNUM = 1),''N'') NEW_ENABLED_FLAG,
V.START_DATE_ACTIVE NEW_START_DATE,
V.DISABLE_DATE NEW_END_DATE,
F.ENABLED_FLAG OLD_ENABLED_FLAG,
F.START_DATE_ACTIVE OLD_START_DATE,
F.END_DATE_ACTIVE OLD_END_DATE,
F.SUMMARY_FLAG OLD_SUMMARY_FLAG,
FT.DESCRIPTION OLD_DESCRIPTION,
NVL((SELECT ''Y'' FROM MTL_CATEGORY_SET_VALID_CATS C
WHERE C.CATEGORY_SET_ID = H.CATEGORY_SET_ID
AND C.PARENT_CATEGORY_ID = V.CATEGORY_ID
AND ROWNUM = 1), ''N'') NEW_SUMMARY_FLAG
FROM MTL_CATEGORIES_KFV V, MTL_CATEGORIES_TL T,
FND_FLEX_VALUES F,
FND_FLEX_VALUES_TL FT,
MTL_CATEGORY_SET_VALID_CATS H
WHERE V.STRUCTURE_ID = :l_struct_id
AND V.CATEGORY_ID = T.CATEGORY_ID
AND T.LANGUAGE = USERENV(''LANG'')
AND F.flex_value= V.CONCATENATED_SEGMENTS
AND F.flex_value_set_id = :l_flex_val_set_id
AND F.flex_VALUE_ID = FT.FLEX_VALUE_ID
AND FT.LANGUAGE = USERENV(''LANG'')
AND V.CATEGORY_ID = H.CATEGORY_ID(+)
AND H.CATEGORY_SET_ID(+) = :g_catset_id) X
WHERE X.NEW_ENABLED_FLAG <> X.OLD_ENABLED_FLAG
OR X.NEW_SUMMARY_FLAG <> X.OLD_SUMMARY_FLAG
OR NVL(X.NEW_DESCRIPTION, ''XX'') <> NVL(X.OLD_DESCRIPTION, ''XX'')
OR NVL(X.NEW_START_DATE, SYSDATE) <> NVL(X.OLD_START_DATE, SYSDATE)
OR NVL(X.NEW_END_DATE, SYSDATE) <> NVL(X.OLD_END_DATE, SYSDATE)';
FND_FLEX_VAL_API.UPDATE_INDEPENDENT_VSET_VALUE
(p_flex_value_set_name => l_value_set_name,
p_flex_value => l_flex_value,
p_description => l_new_description,
p_enabled_flag => l_new_enabled_flag,
p_start_date_active => l_new_start_date,
p_end_date_active => l_new_end_date,
p_summary_flag => l_new_summary_flag,
x_storage_value => l_msg);
Select summary_flag, enabled_flag
into l_summary_flag, l_enabled_flag
from fnd_flex_values
where flex_value = l_top_node
and flex_value_set_id = l_flex_val_set_id; --Bug 5087675
FND_FLEX_VAL_API.UPDATE_INDEPENDENT_VSET_VALUE
(p_flex_value_set_name => l_value_set_name,
p_flex_value => l_top_node,
p_summary_flag => 'Y',
p_enabled_flag => 'Y',
x_storage_value => l_msg);
errbuf := 'Error while Inserting '||l_flex_value||', '||l_msg;
delete from fnd_flex_value_norm_hierarchy hrchy
where flex_value_set_id = l_flex_val_set_id
and exists (
select null
from fnd_flex_values b
where hrchy.flex_value_set_id = b.flex_value_set_id
and hrchy.parent_flex_value = b.flex_value
and b.enabled_flag = 'Y')
and (parent_flex_value,
child_flex_value_low,
child_flex_value_high,
range_attribute)
not in (
select nvl(a.concatenated_segments,l_top_node),
b.concatenated_segments,
b.concatenated_segments,
NVL((select 'P' from mtl_category_set_valid_cats v
where v.category_set_id = c.category_set_id
and v.parent_category_id = b.category_id
and rownum = 1), 'C')
-- decode(b.summary_flag,'Y','P','C')
from mtl_categories_kfv a,
mtl_categories_kfv b,
mtl_category_set_valid_cats c
where a.structure_id(+) = l_struct_id
and b.structure_id = l_struct_id
and c.category_set_id = g_catset_id
and c.parent_category_id = a.category_id(+)
and c.category_id = b.category_id
);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number of records deleted: '||l_count);
END UPDATE_VALUESET_FROM_CATEGORY;