[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fvt.application_table_name INTO l_tab_name
FROM fnd_flex_validation_tables fvt,
fnd_flex_value_sets fvs
WHERE fvs.flex_value_set_id = X_vs_id
AND fvs.validation_type = 'F'
AND fvt.flex_value_set_id = fvs.flex_value_set_id;
select flex_value_set_name into l_vs_name
from fnd_flex_value_sets
where flex_value_set_id = p_vs_id;
select flex_value into l_flex_value
from fnd_flex_values
where flex_value_id = p_flex_value_id;
SELECT MASTER_VALUE_SET_ID, DBI_HIER_TOP_NODE, DBI_HIER_TOP_NODE_ID,
DBI_ENABLED_FLAG
INTO G_MASTER_VALUE_SET_ID, G_TOP_NODE_VALUE, G_TOP_NODE_ID,
G_DBI_ENABLED_FLAG
FROM FII_FINANCIAL_DIMENSIONS
WHERE DIMENSION_SHORT_NAME = g_dimension_name;
select distinct child_flex_value_set_id
from FII_DIM_NORM_HIERARCHY
where parent_flex_value_set_id = G_MASTER_VALUE_SET_ID
and parent_flex_value_set_id <> child_flex_value_set_id
and child_flex_value_set_id IN
(select map.flex_value_set_id1
from fii_dim_mapping_rules map,
fii_slg_assignments sts,
fii_source_ledger_groups slg
where map.dimension_short_name = g_dimension_name
and map.chart_of_accounts_id = sts.chart_of_accounts_id
and sts.source_ledger_group_id = slg.source_ledger_group_id
and slg.usage_code = 'DBI');
Insert into FII_DIM_NORM_HIER_GT (
PARENT_FLEX_VALUE_SET_ID,
PARENT_FLEX_VALUE,
RANGE_ATTRIBUTE,
CHILD_FLEX_VALUE_SET_ID,
CHILD_FLEX_VALUE_LOW,
CHILD_FLEX_VALUE_HIGH)
Select
PARENT_FLEX_VALUE_SET_ID,
PARENT_FLEX_VALUE,
RANGE_ATTRIBUTE,
CHILD_FLEX_VALUE_SET_ID,
CHILD_FLEX_VALUE_LOW,
CHILD_FLEX_VALUE_HIGH
From FII_DIM_NORM_HIERARCHY
Where PARENT_FLEX_VALUE_SET_ID = G_MASTER_VALUE_SET_ID
And PARENT_FLEX_VALUE_SET_ID <> CHILD_FLEX_VALUE_SET_ID
And CHILD_FLEX_VALUE_SET_ID IN
(select map.flex_value_set_id1
from fii_dim_mapping_rules map,
fii_slg_assignments sts,
fii_source_ledger_groups slg
where map.dimension_short_name = g_dimension_name -- Bug 4152798. Removed literal
and map.chart_of_accounts_id = sts.chart_of_accounts_id
and sts.source_ledger_group_id = slg.source_ledger_group_id
and slg.usage_code = 'DBI');
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_DIM_NORM_HIER_GT');
g_phase := 'insert records for the master value set';
Insert into FII_DIM_NORM_HIER_GT (
PARENT_FLEX_VALUE_SET_ID,
PARENT_FLEX_VALUE,
RANGE_ATTRIBUTE,
CHILD_FLEX_VALUE_SET_ID,
CHILD_FLEX_VALUE_LOW,
CHILD_FLEX_VALUE_HIGH)
Select
FLEX_VALUE_SET_ID,
PARENT_FLEX_VALUE,
RANGE_ATTRIBUTE,
FLEX_VALUE_SET_ID,
CHILD_FLEX_VALUE_LOW,
CHILD_FLEX_VALUE_HIGH
From FND_FLEX_VALUE_NORM_HIERARCHY
Where flex_value_set_id = G_MASTER_VALUE_SET_ID;
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_DIM_NORM_HIER_GT');
g_phase := 'Insert records for all local (child) value sets';
g_phase := 'Insert records for local value set ' || l_vset_id;
Insert into FII_DIM_NORM_HIER_GT (
PARENT_FLEX_VALUE_SET_ID,
PARENT_FLEX_VALUE,
RANGE_ATTRIBUTE,
CHILD_FLEX_VALUE_SET_ID,
CHILD_FLEX_VALUE_LOW,
CHILD_FLEX_VALUE_HIGH)
Select
FLEX_VALUE_SET_ID,
PARENT_FLEX_VALUE,
RANGE_ATTRIBUTE,
FLEX_VALUE_SET_ID,
CHILD_FLEX_VALUE_LOW,
CHILD_FLEX_VALUE_HIGH
From FND_FLEX_VALUE_NORM_HIERARCHY
Where flex_value_set_id = l_vset_id;
SELECT count(parent_fin_cat_id) parents,
child_fin_cat_id flex_value_id
FROM FII_FIN_ITEM_HIER_GT
WHERE next_level_fin_cat_id = child_fin_cat_id
AND parent_level = next_level - 1
GROUP BY child_fin_cat_id
HAVING count(parent_fin_cat_id) > 1;
SELECT parent_fin_cat_id,
parent_flex_value_set_id,
child_fin_cat_id,
child_flex_value_set_id
FROM FII_FIN_ITEM_HIER_GT
WHERE child_fin_cat_id = p_child_value_id
AND next_level_fin_cat_id = child_fin_cat_id
AND parent_level = next_level - 1;
PROCEDURE INSERT_IMM_CHILD_NODES
(p_vset_id NUMBER, p_root_node VARCHAR2) IS
--Per suggestion from performance team, add a hint to the select
--(it uses a new index FII_DIM_NORM_HIER_GT_N1)
CURSOR direct_children_csr (p_parent_vs_id NUMBER, p_parent_node VARCHAR2) IS
SELECT /*+ leading(ffvnh) index(ffvnh) */
ffv.flex_value_id, ffv.flex_value, ffv.flex_value_set_id, attribute_sort_order sort_order
FROM FII_DIM_NORM_HIER_GT ffvnh,
fnd_flex_values ffv
WHERE ffvnh.child_flex_value_set_id = ffv.flex_value_set_id
AND (ffv.flex_value BETWEEN ffvnh.child_flex_value_low
AND ffvnh.child_flex_value_high)
AND ((ffvnh.range_attribute = 'P' and ffv.summary_flag = 'Y') OR
(ffvnh.range_attribute = 'C' and ffv.summary_flag = 'N'))
AND ffvnh.parent_flex_value = p_parent_node
AND ffvnh.parent_flex_value_set_id = p_parent_vs_id;
select flex_value_id, attribute_sort_order into l_flex_value_id, l_sort_order
from fnd_flex_values
where flex_value_set_id = p_vset_id
and flex_value = p_root_node;
/* Inserting parent in a gt table: FII_DIM_HIER_HELP_GT */
g_index := g_index + 1;
insert into FII_DIM_HIER_HELP_GT
( IDX,
FLEX_VALUE_ID,
FLEX_VALUE_SET_ID,
NEXT_LEVEL_FLEX_VALUE_ID, SORT_ORDER)
values
( g_index,
l_flex_value_id,
l_flex_value_set_id,
l_flex_value_id, l_sort_order);
update FII_DIM_HIER_HELP_GT
set NEXT_LEVEL_FLEX_VALUE_ID= l_flex_value_id,
SORT_ORDER= l_sort_order
where IDX = g_index - 1;
/* Inserting record with all parents */
INSERT INTO fii_fin_item_hier_gt (
parent_level,
parent_fin_cat_id,
child_fin_cat_id,
next_level,
child_level,
next_level_is_leaf,
is_leaf_flag,
parent_flex_value_Set_id,
child_flex_value_set_id,
next_level_fin_cat_id,
next_level_fin_cat_sort_order)
SELECT pp.idx,
pp.flex_value_id,
direct_children_rec.flex_value_id,
pp.idx + 1,
g_index + 1,
'N',
'N',
pp.flex_value_set_id,
direct_children_rec.flex_value_set_id,
decode(pp.idx, g_index,
direct_children_rec.flex_value_id,
pp.next_level_flex_value_id),
decode(pp.idx, g_index,
direct_children_rec.sort_order,
pp.sort_order)
FROM FII_DIM_HIER_HELP_GT pp;
INSERT_IMM_CHILD_NODES (direct_children_rec.flex_value_set_id,
direct_children_rec.flex_value);
delete from FII_DIM_HIER_HELP_GT where idx = g_index;
FII_UTIL.WRITE_LOG ('Insert Immediate child: No Data Found');
'FII_DIMENSION_MAINTAIN_PKG.Fin_Insert_Imm_Child_Nodes');
(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.INSERT_IMM_CHILD_NODES');
END INSERT_IMM_CHILD_NODES;
SELECT flex_value_id INTO p_parent_id
FROM FND_FLEX_VALUES
WHERE flex_value_set_id = p_vset_id
AND flex_value = p_root_node;
INSERT_IMM_CHILD_NODES (p_vset_id, p_root_node);
g_phase := 'insert all self nodes';
insert into fii_fin_item_hier_gt (
parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
child_level,
child_fin_cat_id,
child_flex_value_set_id,
parent_flex_value_set_id,
next_level_is_leaf,
is_leaf_flag)
select
child_level,
child_fin_cat_id,
child_level,
child_fin_cat_id,
child_level,
child_fin_cat_id,
child_flex_value_set_id,
child_flex_value_set_id,
'N',
'N'
from (select distinct child_fin_cat_id,child_level,child_flex_value_set_id from fii_fin_item_hier_gt);
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_fin_item_hier_gt');
g_phase := 'Insert self node for the top node';
INSERT INTO fii_fin_item_hier_gt
(parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
child_level,
child_fin_cat_id,
child_flex_value_set_id,
parent_flex_value_set_id,
next_level_is_leaf,
is_leaf_flag)
VALUES
(1,
p_parent_id,
1,
p_parent_id,
1,
p_parent_id,
p_vset_id,
FINDIM_parent_vset_id,
'N',
'N');
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_fin_item_hier_gt');
PROCEDURE Update_GT IS
Begin
IF (FIIDIM_Debug) THEN
FII_MESSAGE.Func_Ent(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
'Update_GT');
g_phase := 'Update next_level_is_leaf, is_leaf_flag';
Update fii_fin_item_hier_gt tab1
Set next_level_is_leaf = 'Y'
Where tab1.next_level_fin_cat_id = tab1.child_fin_cat_id
and tab1.next_level_fin_cat_id IN (
select /*+ ordered */ tab3.next_level_fin_cat_id
from fii_fin_item_hier_gt tab3,
fii_fin_item_hier_gt tab2
where tab2.parent_fin_cat_id = tab3.parent_fin_cat_id
and tab3.parent_fin_cat_id = tab3.child_fin_cat_id
group by tab3.next_level_fin_cat_id
having count(*) = 1);
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_fin_item_hier_gt');
g_phase := 'Update is_leaf_flag';
Update fii_fin_item_hier_gt
Set is_leaf_flag = 'Y'
Where parent_fin_cat_id = next_level_fin_cat_id
and next_level_fin_cat_id = child_fin_cat_id
and next_level_is_leaf = 'Y';
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_fin_item_hier_gt');
'Update_GT');
FII_UTIL.Write_Log ('Update_GT -> phase: '|| g_phase);
FII_UTIL.Write_Log ('Update_GT: '|| substr(sqlerrm,1,180));
'Update_GT');
END Update_GT;
CURSOR pre_dep_cur IS SELECT * FROM
( -- normalized parent-child relationship (one-level)
select parent_fin_cat_id pid
, child_fin_cat_id cid
, child_level clv
, child_flex_value_set_id cvs
, is_leaf_flag clf
from fii_fin_item_hier_gt
where parent_level + 1 = child_level
--and child_flex_value_set_id = G_MASTER_VALUE_SET_ID
union all
select null, G_TOP_NODE_ID, 1, G_MASTER_VALUE_SET_ID, 'N'
from dual
)
START WITH pid is NULL
CONNECT BY pid = PRIOR cid
ORDER siblings BY cid;
-- We want to update the newly introduced level columns for BI - 2006
----------------------------------------------------------------------
FOR pre_dep_rec IN pre_dep_cur LOOP
-- put (pop/push) the new child value on the stack
c_top := pre_dep_rec.clv;
update fii_fin_item_hier_gt
set LEVEL2_fin_cat_ID = r_stack( least( p_top + 1, c_top) ).cid
, LEVEL3_fin_cat_ID = r_stack( least( p_top + 2, c_top ) ).cid
, LEVEL4_fin_cat_ID = r_stack( least( p_top + 3, c_top ) ).cid
, LEVEL5_fin_cat_ID = r_stack( least( p_top + 4, c_top ) ).cid
where parent_fin_cat_id = r_stack( p_top ).cid
and child_fin_cat_id = r_stack( c_top ).cid;
PROCEDURE Delete_LVS_Records IS
Begin
IF (FIIDIM_Debug) THEN
FII_MESSAGE.Func_Ent(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.'||
'Delete_LVS_Records');
g_phase := 'Delete FII_FIN_ITEM_HIER_GT ';
Delete from FII_FIN_ITEM_HIER_GT
Where child_flex_value_set_id <> G_MASTER_VALUE_SET_ID;
FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows in fii_fin_item_hier_gt');
g_phase := 'Update FII_FIN_ITEM_HIER_GT for pruned hierarchy table';
Update_Gt;
'Delete_LVS_Records');
FII_UTIL.Write_Log ('Delete_LVS_Records -> phase: '|| g_phase);
FII_UTIL.Write_Log ('Delete_LVS_Records: '|| substr(sqlerrm,1,180));
'Delete_LVS_Records');
END Delete_LVS_Records;
INSERT INTO FII_FIN_CAT_MAPPINGS_GT
(parent_fin_cat_id,
child_fin_cat_id)
SELECT fh.parent_fin_cat_id,
fh.child_fin_cat_id
FROM FII_FULL_FIN_ITEM_HIERS fh
WHERE fh.parent_fin_cat_id IN
(SELECT ph.parent_fin_cat_id
FROM FII_FIN_ITEM_HIERARCHIES ph
WHERE ph.is_leaf_flag = 'Y');
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_MAPPINGS_GT');
g_phase := 'insert self-mapping records for all nodes in pruned hierarchy';
INSERT INTO FII_FIN_CAT_MAPPINGS_GT
(parent_fin_cat_id,
child_fin_cat_id)
SELECT parent_fin_cat_id,
child_fin_cat_id
FROM FII_FIN_ITEM_HIERARCHIES
WHERE child_flex_value_set_id = G_MASTER_VALUE_SET_ID
AND parent_fin_cat_id = child_fin_cat_id
AND is_leaf_flag = 'N';
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_MAPPINGS_GT');
select FIN_CATEGORY_ID
from FII_FC_TYPE_ASSGNS_GT
where FIN_CAT_TYPE_CODE = p_cat_type;
select FIN_CATEGORY_ID, FIN_CAT_TYPE_CODE
from FII_FC_TYPE_ASSGNS_GT
where FIN_CATEGORY_ID = p_rev_cat_id
and FIN_CAT_TYPE_CODE in ('OE', 'TE', 'PE', 'CGS');
select FIN_CATEGORY_ID, FIN_CAT_TYPE_CODE
from FII_FC_TYPE_ASSGNS_GT
where FIN_CATEGORY_ID = p_rev_cat_id
and FIN_CAT_TYPE_CODE <> p_cat_type;
g_phase := 'insert records into the denorm TMP table';
Insert into FII_FC_TYPE_ASSGNS_GT
(fin_cat_type_code,
fin_category_id,
top_node_flag)
select distinct
fcn.fin_cat_type_code,
hier.child_fin_cat_id,
'N'
from FII_FC_TYPE_NORM_ASSIGN fcn,
FII_FULL_FIN_ITEM_HIERS hier
where fcn.fin_category_id = hier.parent_fin_cat_id;
FII_UTIL.Write_Log('Inserted ' || l_sql_rowcount || ' rows into FII_FC_TYPE_ASSGNS_GT');
g_phase := 'Insert a new internal type (EXP)';
Insert into FII_FC_TYPE_ASSGNS_GT
(fin_cat_type_code,
fin_category_id,
top_node_flag)
select distinct
'EXP',
fct.fin_category_id,
'N'
from FII_FC_TYPE_ASSGNS_GT fct
where fct.fin_cat_type_code IN ('OE', 'TE', 'PE', 'CGS');
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FC_TYPE_ASSGNS_GT');
g_phase := 'update the column TOP_NODE_FLAG';
UPDATE FII_FC_TYPE_ASSGNS_GT tab1
SET tab1.TOP_NODE_FLAG = 'Y'
WHERE (tab1.fin_cat_type_code,tab1.fin_category_id) IN
(select /*+ ordered parallel(hier) */
tab3.fin_cat_type_code,tab3.fin_category_id
from FII_FC_TYPE_ASSGNS_GT tab3,
FII_FULL_FIN_ITEM_HIERS hier,
FII_FC_TYPE_ASSGNS_GT tab2
where tab2.fin_cat_type_code = tab3.fin_cat_type_code
and hier.child_fin_cat_id = tab3.fin_category_id
and hier.parent_fin_cat_id = tab2.fin_category_id
group by tab3.fin_cat_type_code,
tab3.fin_category_id
having count(*) = 1);
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_FC_TYPE_ASSGNS_GT');
g_phase := 'INSERT INTO FII_FIN_CAT_TYPE_ASSGNS';
INSERT /*+ APPEND */ INTO FII_FIN_CAT_TYPE_ASSGNS
(fin_cat_type_code,
fin_category_id,
top_node_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT fin_cat_type_code,
fin_category_id,
top_node_flag,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FC_TYPE_ASSGNS_GT;
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_TYPE_ASSGNS');
ELSE --incremental update
g_phase := 'DELETE FROM FII_FIN_CAT_TYPE_ASSGNS';
DELETE FROM FII_FIN_CAT_TYPE_ASSGNS
WHERE (fin_cat_type_code, fin_category_id, top_node_flag) IN
(SELECT fin_cat_type_code, fin_category_id, top_node_flag
FROM FII_FIN_CAT_TYPE_ASSGNS
MINUS
SELECT fin_cat_type_code, fin_category_id, top_node_flag
FROM FII_FC_TYPE_ASSGNS_GT);
FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FIN_CAT_TYPE_ASSGNS');
g_phase := 'Insert into FII_FIN_CAT_TYPE_ASSGNS';
Insert into FII_FIN_CAT_TYPE_ASSGNS(
fin_cat_type_code,
fin_category_id,
top_node_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
(SELECT
fin_cat_type_code,
fin_category_id,
top_node_flag,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FC_TYPE_ASSGNS_GT
MINUS
SELECT
fin_cat_type_code,
fin_category_id,
top_node_flag,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FIN_CAT_TYPE_ASSGNS);
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_TYPE_ASSGNS');
INSERT /*+ APPEND */ INTO FII_FULL_FIN_ITEM_HIERS (
parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT
parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FIN_ITEM_HIER_GT;
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FULL_FIN_ITEM_HIERS');
g_phase := 'Delete LVS records from FII_FIN_ITEM_HIER_GT for pruned hierarchy table for Expense Analysis';
Delete_LVS_Records;
INSERT /*+ APPEND */ INTO FII_FIN_ITEM_LEAF_HIERS (
parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf_flag,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
next_level_fin_cat_sort_order,
aggregate_next_level_flag,
LEVEL2_fin_cat_ID,
LEVEL3_fin_cat_ID,
LEVEL4_fin_cat_ID,
LEVEL5_fin_cat_ID,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
is_to_be_rolled_up_flag)
SELECT
parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
next_level_fin_cat_sort_order,
'N',
LEVEL2_fin_cat_ID,
LEVEL3_fin_cat_ID,
LEVEL4_fin_cat_ID,
LEVEL5_fin_cat_ID,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID,
'N'
FROM FII_FIN_ITEM_HIER_GT;
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
INSERT /*+ APPEND */ INTO FII_FIN_ITEM_HIERARCHIES (
parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT
parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FIN_ITEM_HIER_GT;
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_HIERARCHIES');
INSERT /*+ APPEND */ INTO FII_FIN_CAT_LEAF_MAPS (
parent_fin_cat_id,
child_fin_cat_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT
parent_fin_cat_id,
child_fin_cat_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FIN_CAT_MAPPINGS_GT;
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_LEAF_MAPS');
INSERT /*+ APPEND */ INTO FII_FIN_CAT_MAPPINGS (
parent_fin_cat_id,
child_fin_cat_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT
parent_fin_cat_id,
child_fin_cat_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FIN_CAT_MAPPINGS_GT;
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_MAPPINGS');
g_phase := 'Update is_to_be_rolled_up_flag flag';
UPDATE FII_FIN_ITEM_LEAF_HIERS
SET is_to_be_rolled_up_flag = 'Y'
WHERE next_level_fin_cat_id in ( SELECT fin_category_id
FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' and
fin_cat_type_code in ('R','EXP')
)
OR parent_fin_cat_id in ( SELECT fin_category_id
FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' and
fin_cat_type_code in ('R','EXP')
);
g_phase := 'Update top_node_fin_cat_type flag for OE';
UPDATE fii_fin_item_leaf_hiers
SET top_node_fin_cat_type = 'OE'
WHERE next_level_fin_cat_id in (SELECT fin_category_id FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' AND fin_cat_type_code = 'OE')
AND next_level_fin_cat_id <> parent_fin_cat_id;
g_phase := 'Update top_node_fin_cat_type flag for CGS';
UPDATE fii_fin_item_leaf_hiers
SET top_node_fin_cat_type = 'CGS'
where next_level_fin_cat_id in (SELECT fin_category_id FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' AND fin_cat_type_code = 'CGS')
and next_level_fin_cat_id <> parent_fin_cat_id;
g_phase := 'Update top_node_fin_cat_type flag for other category types';
UPDATE fii_fin_item_leaf_hiers fin
SET top_node_fin_cat_type = (SELECT fin_cat_type_code FROM fii_fin_cat_type_assgns
WHERE fin_category_id = fin.next_level_fin_cat_id and fin_cat_type_code in ( 'R','TE'))
WHERE (fin.next_level_fin_cat_id in (SELECT fin_category_id FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' AND fin_cat_type_code in ( 'R','TE'))
AND fin.next_level_fin_cat_id not in (SELECT fin_category_id FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' AND fin_cat_type_code in ('OE', 'CGS')))
AND next_level_fin_cat_id <> parent_fin_cat_id;
PROCEDURE Incre_Update (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2) IS
ret_val BOOLEAN := FALSE;
INSERT /*+ APPEND */ INTO FII_FULL_FIN_ITEM_HIERS (
parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT
parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FIN_ITEM_HIER_GT;
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FULL_FIN_ITEM_HIERS');
g_phase := 'Delete LVS records from FII_FIN_ITEM_HIER_GT for pruned hierarchy table for Expense Analysis';
Delete_LVS_Records;
g_phase := 'DELETE FROM FII_FIN_ITEM_LEAF_HIERS';
DELETE FROM FII_FIN_ITEM_LEAF_HIERS
WHERE
(parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
next_level_is_leaf_flag, is_leaf_flag, child_level, child_fin_cat_id,
parent_flex_value_set_id, child_flex_value_set_id,
NVL(next_level_fin_cat_sort_order, -92883),
LEVEL2_fin_cat_ID,
LEVEL3_fin_cat_ID,
LEVEL4_fin_cat_ID,
LEVEL5_fin_cat_ID) IN
(SELECT parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
next_level_is_leaf_flag, is_leaf_flag, child_level, child_fin_cat_id,
parent_flex_value_set_id, child_flex_value_set_id,
NVL(next_level_fin_cat_sort_order, -92883),
LEVEL2_fin_cat_ID,
LEVEL3_fin_cat_ID,
LEVEL4_fin_cat_ID,
LEVEL5_fin_cat_ID
FROM FII_FIN_ITEM_LEAF_HIERS
MINUS
SELECT parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
next_level_is_leaf, is_leaf_flag, child_level, child_fin_cat_id,
parent_flex_value_set_id, child_flex_value_set_id,
NVL(next_level_fin_cat_sort_order, -92883),
LEVEL2_fin_cat_ID,
LEVEL3_fin_cat_ID,
LEVEL4_fin_cat_ID,
LEVEL5_fin_cat_ID
FROM FII_FIN_ITEM_HIER_GT);
FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FIN_ITEM_LEAF_HIERS');
g_phase := 'Insert into FII_FIN_ITEM_LEAF_HIERS';
Insert into FII_FIN_ITEM_LEAF_HIERS (
parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf_flag,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
next_level_fin_cat_sort_order,
aggregate_next_level_flag,
LEVEL2_fin_cat_ID,
LEVEL3_fin_cat_ID,
LEVEL4_fin_cat_ID,
LEVEL5_fin_cat_ID,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
is_to_be_rolled_up_flag)
(SELECT parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
next_level_fin_cat_sort_order,
'N',
LEVEL2_fin_cat_ID,
LEVEL3_fin_cat_ID,
LEVEL4_fin_cat_ID,
LEVEL5_fin_cat_ID,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID,
'N'
FROM FII_FIN_ITEM_HIER_GT
MINUS
SELECT parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf_flag,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
next_level_fin_cat_sort_order,
'N',
LEVEL2_fin_cat_ID,
LEVEL3_fin_cat_ID,
LEVEL4_fin_cat_ID,
LEVEL5_fin_cat_ID,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID,
'N'
FROM FII_FIN_ITEM_LEAF_HIERS);
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
g_phase := 'DELETE FROM FII_FIN_ITEM_HIERARCHIES';
DELETE FROM FII_FIN_ITEM_HIERARCHIES
WHERE
(parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
next_level_is_leaf, is_leaf_flag, child_level, child_fin_cat_id,
parent_flex_value_set_id, child_flex_value_set_id) IN
(SELECT parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
next_level_is_leaf, is_leaf_flag, child_level, child_fin_cat_id,
parent_flex_value_set_id, child_flex_value_set_id
FROM FII_FIN_ITEM_HIERARCHIES
MINUS
SELECT parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
next_level_is_leaf, is_leaf_flag, child_level, child_fin_cat_id,
parent_flex_value_set_id, child_flex_value_set_id
FROM FII_FIN_ITEM_HIER_GT);
FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FIN_ITEM_HIERARCHIES');
g_phase := 'Insert into FII_FIN_ITEM_HIERARCHIES';
Insert into FII_FIN_ITEM_HIERARCHIES (
parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
(SELECT parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FIN_ITEM_HIER_GT
MINUS
SELECT parent_level,
parent_fin_cat_id,
next_level,
next_level_fin_cat_id,
next_level_is_leaf,
is_leaf_flag,
child_level,
child_fin_cat_id,
parent_flex_value_set_id,
child_flex_value_set_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FIN_ITEM_HIERARCHIES);
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_HIERARCHIES');
-- We have added an update statement on FII_FIN_ITEM_LEAF_HIERS. Hence, moved gathering statistics
-- for FII_FIN_ITEM_LEAF_HIERS table at the end of procedure.
--Call FND_STATS to collect statistics after re-populating the tables.
--Will seed this in RSG
-- FND_STATS.gather_table_stats
-- (ownname => g_schema_name,
-- tabname => 'FII_FIN_ITEM_HIERARCHIES');
g_phase := 'DELETE FROM FII_FIN_CAT_LEAF_MAPS';
DELETE FROM FII_FIN_CAT_LEAF_MAPS
WHERE
(parent_fin_cat_id, child_fin_cat_id) IN
(SELECT parent_fin_cat_id, child_fin_cat_id
FROM FII_FIN_CAT_LEAF_MAPS
MINUS
SELECT parent_fin_cat_id, child_fin_cat_id
FROM FII_FIN_CAT_MAPPINGS_GT);
FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FIN_CAT_LEAF_MAPS');
g_phase := 'Insert into FII_FIN_CAT_LEAF_MAPS';
Insert into FII_FIN_CAT_LEAF_MAPS (
parent_fin_cat_id,
child_fin_cat_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
(SELECT parent_fin_cat_id,
child_fin_cat_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FIN_CAT_MAPPINGS_GT
MINUS
SELECT parent_fin_cat_id,
child_fin_cat_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FIN_CAT_LEAF_MAPS);
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_LEAF_MAPS');
g_phase := 'DELETE FROM FII_FIN_CAT_MAPPINGS';
DELETE FROM FII_FIN_CAT_MAPPINGS
WHERE
(parent_fin_cat_id, child_fin_cat_id) IN
(SELECT parent_fin_cat_id, child_fin_cat_id
FROM FII_FIN_CAT_MAPPINGS
MINUS
SELECT parent_fin_cat_id, child_fin_cat_id
FROM FII_FIN_CAT_MAPPINGS_GT);
FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FIN_CAT_MAPPINGS');
g_phase := 'Insert into FII_FIN_CAT_MAPPINGS';
Insert into FII_FIN_CAT_MAPPINGS (
parent_fin_cat_id,
child_fin_cat_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
(SELECT parent_fin_cat_id,
child_fin_cat_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FIN_CAT_MAPPINGS_GT
MINUS
SELECT parent_fin_cat_id,
child_fin_cat_id,
SYSDATE,
FII_USER_ID,
SYSDATE,
FII_USER_ID,
FII_LOGIN_ID
FROM FII_FIN_CAT_MAPPINGS);
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_MAPPINGS');
g_phase := 'Update is_to_be_rolled_up_flag flag ';
UPDATE FII_FIN_ITEM_LEAF_HIERS
SET is_to_be_rolled_up_flag = 'Y'
WHERE (next_level_fin_cat_id in ( SELECT fin_category_id
FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' and
fin_cat_type_code in ('R','EXP')
)
OR parent_fin_cat_id in ( SELECT fin_category_id
FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' and
fin_cat_type_code in ('R','EXP')
))
AND is_to_be_rolled_up_flag <> 'Y' ;
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
g_phase := 'Update top_node_fin_cat_type flag for OE';
UPDATE fii_fin_item_leaf_hiers
SET top_node_fin_cat_type = 'OE'
WHERE next_level_fin_cat_id IN (SELECT fin_category_id FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' AND fin_cat_type_code = 'OE')
AND next_level_fin_cat_id <> parent_fin_cat_id
AND (top_node_fin_cat_type <> 'OE' OR top_node_fin_cat_type is null);
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
g_phase := 'Update top_node_fin_cat_type flag for CGS';
UPDATE fii_fin_item_leaf_hiers
SET top_node_fin_cat_type = 'CGS'
where next_level_fin_cat_id IN (SELECT fin_category_id FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' AND fin_cat_type_code = 'CGS')
AND next_level_fin_cat_id <> parent_fin_cat_id
AND (top_node_fin_cat_type <> 'CGS' OR top_node_fin_cat_type is null);
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
g_phase := 'Update top_node_fin_cat_type flag for other category types';
UPDATE fii_fin_item_leaf_hiers fin
SET top_node_fin_cat_type = (SELECT fin_cat_type_code FROM fii_fin_cat_type_assgns
WHERE fin_category_id = fin.next_level_fin_cat_id and fin_cat_type_code in ( 'R','TE'))
WHERE (fin.next_level_fin_cat_id in (SELECT fin_category_id FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' AND fin_cat_type_code in ( 'R','TE'))
AND fin.next_level_fin_cat_id not in (SELECT fin_category_id FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' AND fin_cat_type_code in ('OE', 'CGS')))
AND next_level_fin_cat_id <> parent_fin_cat_id
AND (top_node_fin_cat_type <> (SELECT fin_cat_type_code FROM fii_fin_cat_type_assgns
WHERE fin_category_id = fin.next_level_fin_cat_id and fin_cat_type_code in ( 'R','TE'))
OR top_node_fin_cat_type is null);
-- This update statement is added for the nodes for which there is no category assigned now, but they had one before
-- This is a valid case
UPDATE fii_fin_item_leaf_hiers fin
set top_node_fin_cat_type = NULL
where next_level_fin_cat_id in (SELECT next_level_fin_cat_id from fii_fin_item_leaf_hiers
WHERE top_node_fin_cat_type is not null
MINUS
SELECT fin_category_id FROM fii_fin_cat_type_assgns
WHERE top_node_flag = 'Y' AND fin_cat_type_code in ( 'R','TE', 'OE', 'CGS')
)
AND next_level_fin_cat_id <> parent_fin_cat_id;
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
FII_UTIL.Write_Log('FII_FIN_CAT_MAINTAIN_PKG.Incre_Update'||
'User defined error');
FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Incre_Update');
FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
FII_UTIL.Write_Log('FII_FIN_CAT_MAINTAIN_PKG.Incre_Update: '||
'Diamond Shape Detected');
FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Incre_Update');
FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
FII_UTIL.Write_Log('FII_FIN_CAT_MAINTAIN_PKG.Incre_Update: '||
'Invalid FC Type Assignment Detected');
FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Incre_Update');
FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
FII_MESSAGE.Func_Fail(func_name => 'FII_FIN_CAT_MAINTAIN_PKG.Incre_Update');
FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
'Other error in FII_FIN_CAT_MAINTAIN_PKG.Incre_Update: ' || substr(sqlerrm,1,180));
FII_MESSAGE.Func_Fail(func_name => 'II_FIN_CAT_MAINTAIN_PKG.Incre_Update');
END Incre_Update;