[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select fh.flattened_rows_flag,
fod.display_name
from fem_hierarchies fh, fem_object_definition_vl fod
where fod.object_definition_id = p_hier_obj_defn_id
and fod.object_id = fh.hierarchy_obj_id;
l_focus_node_stmt := 'Delete '||p_hier_table_name||
' where hierarchy_obj_def_id = :1 '||
' and parent_id = :2 '||
' and parent_value_set_id = :3 '||
' and child_id = :4 '||
' and child_value_set_id = :5 '||
' and single_depth_flag = ''N''';
l_fl_child_stmt := 'Select child_id,'||
'child_depth_num, child_value_set_id,'||
'single_depth_flag,display_order_num, '||
'weighting_pct from '||p_hier_table_name||
' where hierarchy_obj_def_id = '||p_hier_obj_defn_id||
' and parent_id = '||p_focus_node||
' and parent_value_set_id = '||p_focus_value_set_id||
' and single_depth_flag = ''Y''';
' using (Select parent_id,parent_depth_num ,'||
' parent_value_set_id , hierarchy_obj_def_id '||
' From '||p_hier_table_name||
' where hierarchy_obj_def_id = :1 '||
' and child_id = :2 '||
' and child_value_set_id = :3 '||
' and not (parent_id = child_id and '||
' parent_value_set_id = child_value_set_id) '||
' union '||
' Select child_id parent_id, '||
' child_depth_num parent_depth_num , '||
' child_value_set_id parent_value_set_id, '||
' hierarchy_obj_def_id '||
' from '||p_hier_table_name||
' where hierarchy_obj_def_id = :A '||
' and child_id = :B '||
' and child_value_set_id = :C '||
' and parent_id = :D '||
' and parent_value_set_id = :E '||
' and not (parent_id = child_id and '||
' parent_value_set_id = child_value_set_id) ' || --) hierB'||
-- Start Bug#4022561
/* Do not insert leaf node flattened entries
* for nodes that have children
*/
' and not exists ( select 1 from ' || p_hier_table_name || ' Z ' ||
' where z.hierarchy_obj_def_id = ' || p_hier_obj_defn_id ||
' and z.parent_id = ' || l_child_id ||
' and z.parent_value_set_id = ' || l_child_valueset_id ||
' and z.single_depth_flag = ''Y'' )) hierB '||
-- End Bug#4022561
' on (hierA.parent_id = hierB.parent_id and '||
' hierA.parent_value_set_id = hierB.parent_value_set_id '||
' and hierA.child_id = :4 and '||
' hierA.child_value_set_id = :5 and '||
' hierA.hierarchy_obj_def_id = '||
' hierB.hierarchy_obj_def_id )'||
' when matched then update set parent_depth_num = '||
' hierB.parent_depth_num '||
' when not matched then '||
' Insert ' || --Bug#4240532, Provide column list for insert stmt
' ( '||
'HIERARCHY_OBJ_DEF_ID, '||
'PARENT_DEPTH_NUM, '||
'PARENT_ID, '||
'PARENT_VALUE_SET_ID, '||
'CHILD_DEPTH_NUM, '||
'CHILD_ID, '||
'CHILD_VALUE_SET_ID, '||
'SINGLE_DEPTH_FLAG,'||
'DISPLAY_ORDER_NUM,'||
'WEIGHTING_PCT, ' ||
'CREATION_DATE,'||
'CREATED_BY, '||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_DATE,'||
'LAST_UPDATE_LOGIN, '||
'OBJECT_VERSION_NUMBER) ' ||
' values '||
' ( :6, hierB.parent_depth_num, hierB.parent_id, '||
' hierB.parent_value_set_id, :7, :8, :9, :10,'||
' :11, :12, :13, :14, :15, :16, :17,:18) ';
l_focus_node_stmt := 'Delete '||p_hier_table_name||
' where hierarchy_obj_def_id = :1 '||
' and parent_id = :2 '||
' and child_id = :4 '||
' and single_depth_flag = ''N''';
l_fl_child_stmt := 'Select child_id,'||
'child_depth_num, '||
'single_depth_flag,display_order_num, '||
'weighting_pct from '||p_hier_table_name||
' where hierarchy_obj_def_id = '||p_hier_obj_defn_id||
' and parent_id = '||p_focus_node||
' and single_depth_flag = ''Y''';
' using (Select parent_id,parent_depth_num ,'||
' hierarchy_obj_def_id '||
' From '||p_hier_table_name||
' where hierarchy_obj_def_id = :1 '||
' and child_id = :2 '||
' and parent_id <> child_id '||
' union '||
' Select child_id parent_id, '||
' child_depth_num parent_depth_num , '||
' hierarchy_obj_def_id '||
' from '||p_hier_table_name||
' where hierarchy_obj_def_id = :A '||
' and child_id = :B '||
' and parent_id <> child_id '||
' and parent_id = : C ' || -- hierB '||
-- Start Bug#4022561
/* Do not insert leaf node flattened entries
* for nodes that have children
*/
' and not exists ( select 1 from ' || p_hier_table_name || ' Z ' ||
' where z.hierarchy_obj_def_id = ' || p_hier_obj_defn_id ||
' and z.parent_id = ' || l_child_id ||
' and z.single_depth_flag = ''Y'' )) hierB '||
-- End Bug#4022561
' on (hierA.parent_id = hierB.parent_id and '||
' hierA.child_id = :3 and '||
' hierA.hierarchy_obj_def_id = '||
' hierB.hierarchy_obj_def_id )'||
' when matched then update set parent_depth_num = '||
' hierB.parent_depth_num '||
' when not matched then '||
' Insert '|| --Bug#4240532, Provide column list for insert stmt
' ( '||
'HIERARCHY_OBJ_DEF_ID, '||
'PARENT_DEPTH_NUM, '||
'PARENT_ID, '||
'CHILD_DEPTH_NUM, '||
'CHILD_ID, '||
'SINGLE_DEPTH_FLAG,'||
'DISPLAY_ORDER_NUM,'||
'WEIGHTING_PCT, ' ||
'CREATION_DATE,'||
'CREATED_BY, '||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_DATE,'||
'LAST_UPDATE_LOGIN, '||
'OBJECT_VERSION_NUMBER) '||
' values( :4, hierB.parent_depth_num, hierB.parent_id, '||
' :5, :6, :7, :8, :9, :10,'||
' :11, :12, :13, :14, :15) ';
( Select fh.flattened_rows_flag,
fh.dimension_id,
fod.display_name
from fem_hierarchies fh, fem_object_definition_vl fod
where fod.object_definition_id = p_hier_obj_defn_id
and fod.object_id = fh.hierarchy_obj_id)
Loop
l_flattened_rows_flag := l_hier_csr_rec.flattened_rows_flag;
Select hierarchy_table_name,
value_set_required_flag
from fem_xdim_dimensions
where dimension_id = l_dimension_id
)
Loop
l_hier_table_name := l_dim_csr_rec.hierarchy_table_name;
l_hier_flat_stmt := 'Select 1 '||
' from '||l_hier_table_name||
' where hierarchy_obj_def_id = :1 ' ||
' and parent_id <> child_id '||
' and single_depth_flag = ''N''';
l_hier_top_stmt := 'Select parent_id , parent_value_set_id '||
' from '||l_hier_table_name||
' where hierarchy_obj_def_id = :1 ' ||
' and parent_id = child_id '||
' and parent_value_set_id = child_value_set_id '||
' and single_depth_flag = ''Y''';
l_hier_top_stmt := 'Select parent_id '||
' from '||l_hier_table_name||
' where hierarchy_obj_def_id = :1 ' ||
' and parent_id = child_id '||
' and single_depth_flag = ''Y''';
Update fem_hier_definitions
set flattened_rows_completion_code = 'COMPLETED',
last_updated_by = g_current_user_id,
last_update_date = g_current_date,
last_update_login = g_current_login_id,
object_version_number = object_version_number + 1
where hierarchy_obj_def_id = p_hier_obj_defn_id;
execute immediate 'DELETE FROM ' || p_hier_table_name ||
' WHERE hierarchy_obj_def_id = ' || p_hier_obj_defn_id ||
' AND parent_id = ' || p_focus_node ||
' AND parent_id = child_id ' ||
' AND parent_value_set_id = ' || p_focus_value_set_id ||
' AND child_value_set_id = parent_value_set_id ' ||
' AND single_depth_flag = ''N'' ';
l_all_child_stmt := 'SELECT h.parent_id, h.parent_value_set_id, '||
' h.child_id , h.child_depth_num, '||
' h.display_order_num ,h.child_value_set_id,' ||
' h.weighting_pct '||
'FROM ' || p_hier_table_name || ' h ' ||
'WHERE h.hierarchy_obj_def_id = :1 AND ' ||
' NOT ( h.parent_id = h.child_id AND '||
' h.parent_value_set_id = h.child_value_set_id) '||
'START WITH h.parent_id = :2 AND ' ||
' h.parent_value_set_id = :3 AND ' ||
'h.hierarchy_obj_def_id = :4 AND ' ||
'h.single_depth_flag = ''Y'' AND ' ||
' NOT ( h.parent_id = h.child_id AND '||
' h.parent_value_set_id = h.child_value_set_id) '||
'CONNECT BY PRIOR h.child_id = h.parent_id AND ' ||
' PRIOR h.child_value_set_id = h.parent_value_set_id '||
' AND h.hierarchy_obj_def_id = :5 AND ' ||
'h.single_depth_flag = ''Y'' AND ' ||
' NOT ( h.parent_id = h.child_id AND '||
' h.parent_value_set_id = h.child_value_set_id) '||
'ORDER BY level ';
l_leaf_child_stmt := 'Select child_id '||
' from '||p_hier_table_name||
' where hierarchy_obj_def_id = :1'||
' and parent_id = :2 '||
' and parent_value_set_id = :3 ';
' using (Select parent_id,parent_depth_num ,'||
' parent_value_set_id , hierarchy_obj_def_id '||
' From '||p_hier_table_name||
' where hierarchy_obj_def_id = :1 '||
' and child_id = :2 '||
' and child_value_set_id = :3 '||
' and not ( parent_id = child_id and '||
' parent_value_set_id = child_value_set_id)) hierB '||
' on (hierA.parent_id = hierB.parent_id and '||
' hierA.parent_value_set_id = hierB.parent_value_set_id '||
' and hierA.child_id = :4 and '||
' hierA.child_value_set_id = :5 and '||
' hierA.hierarchy_obj_def_id = '||
' hierB.hierarchy_obj_def_id )'||
' when matched then update set parent_depth_num = '||
' hierB.parent_depth_num '||
' when not matched then '||
' Insert ' || --Bug#4240532, Provide column list for insert stmt
' ( '||
'HIERARCHY_OBJ_DEF_ID, '||
'PARENT_DEPTH_NUM, '||
'PARENT_ID, '||
'PARENT_VALUE_SET_ID, '||
'CHILD_DEPTH_NUM, '||
'CHILD_ID, '||
'CHILD_VALUE_SET_ID, '||
'SINGLE_DEPTH_FLAG,'||
'DISPLAY_ORDER_NUM,'||
'WEIGHTING_PCT, ' ||
'CREATION_DATE,'||
'CREATED_BY, '||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_DATE,'||
'LAST_UPDATE_LOGIN, '||
'OBJECT_VERSION_NUMBER) '||
' values '||
' ( :6, hierB.parent_depth_num, hierB.parent_id, '||
' hierB.parent_value_set_id, :7, :8, :9, :10,'||
' :11, :12, :13, :14, :15, :16, :17 ,:18) ';
' using (Select parent_id , parent_depth_num , '||
' parent_value_set_id , child_id, hierarchy_obj_def_id '||
' From '|| p_hier_table_name||
' where hierarchy_obj_def_id = :1 '||
' and single_depth_flag = ''Y'''||
' and parent_id <> child_id '||
' and level <> 1 '||
' start with child_id = :2 '||
' and hierarchy_obj_def_id = :3 '||
' and single_depth_flag = ''Y'' '||
' and parent_id <> child_id '||
' connect by prior parent_id = child_id '||
' and hierarchy_obj_def_id = :4 '||
' and single_depth_flag = ''Y'''||
' and parent_id <> child_id ) hierB'||
' on (hierA.parent_id = hierB.parent_id and '||
' hierA.child_id = :x and '||
' hierA.hierarchy_obj_def_id = '||
' hierB.hierarchy_obj_def_id )'||
' when matched then update set parent_depth_num = '||
' hierB.parent_depth_num '||
' when not matched then '||
' Insert values '||
' ( :5,hierB.parent_depth_num,hierB.parent_id,'||
' hierB.parent_value_set_id,:6,:7,:8,:9,:10,'||
' :11,:12,:13,:14,:15, :16,:17 ) '; */
l_leaf_flat_stmt := 'Insert into '||p_hier_table_name||
' ( '||
'HIERARCHY_OBJ_DEF_ID, '||
'PARENT_DEPTH_NUM, '||
'PARENT_ID, '||
'PARENT_VALUE_SET_ID, '||
'CHILD_DEPTH_NUM, '||
'CHILD_ID, '||
'CHILD_VALUE_SET_ID, '||
'SINGLE_DEPTH_FLAG,'||
'DISPLAY_ORDER_NUM,'||
'WEIGHTING_PCT, ' ||
'CREATION_DATE,'||
'CREATED_BY, '||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_DATE,'||
'LAST_UPDATE_LOGIN, '||
'OBJECT_VERSION_NUMBER) '||
' values '||
'( :1, '||
' :lparentdepthnum,'||
' :lparentid,'||
' :lparentvaluesetid,'||
' :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)' ;
execute immediate 'DELETE FROM ' || p_hier_table_name ||
' WHERE hierarchy_obj_def_id = ' || p_hier_obj_defn_id ||
' AND parent_id = ' || p_focus_node ||
' AND parent_id = child_id ' ||
' AND single_depth_flag = ''N'' ';
l_all_child_stmt := 'SELECT h.parent_id, '||
' h.child_id , h.child_depth_num, '||
' h.display_order_num ,'||
' h.weighting_pct '||
'FROM ' || p_hier_table_name || ' h ' ||
'WHERE h.hierarchy_obj_def_id = :1 AND ' ||
' h.parent_id <> h.child_id '||
'START WITH h.parent_id = :2 AND ' ||
'h.hierarchy_obj_def_id = :4 AND ' ||
'h.single_depth_flag = ''Y'' AND ' ||
' h.parent_id <> h.child_id '||
'CONNECT BY PRIOR h.child_id = h.parent_id AND ' ||
'h.hierarchy_obj_def_id = :5 AND ' ||
'h.single_depth_flag = ''Y'' AND ' ||
' h.parent_id <> h.child_id '||
'ORDER BY level ';
l_leaf_child_stmt := 'Select child_id '||
' from '||p_hier_table_name||
' where hierarchy_obj_def_id = :1'||
' and parent_id = :2 ';
' using (Select parent_id,parent_depth_num ,'||
' hierarchy_obj_def_id '||
' From '||p_hier_table_name||
' where hierarchy_obj_def_id = :1 '||
' and child_id = :2 '||
' and parent_id <> child_id ) hierB '||
' on (hierA.parent_id = hierB.parent_id '||
' and hierA.child_id = :3 and '||
' hierA.hierarchy_obj_def_id = '||
' hierB.hierarchy_obj_def_id )'||
' when matched then update set parent_depth_num = '||
' hierB.parent_depth_num '||
' when not matched then '||
' Insert ' || --Bug#4240532, Provide column list for insert stmt
' ( '||
'HIERARCHY_OBJ_DEF_ID, '||
'PARENT_DEPTH_NUM, '||
'PARENT_ID, '||
'CHILD_DEPTH_NUM, '||
'CHILD_ID, '||
'SINGLE_DEPTH_FLAG,'||
'DISPLAY_ORDER_NUM,'||
'WEIGHTING_PCT, ' ||
'CREATION_DATE,'||
'CREATED_BY, '||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_DATE,'||
'LAST_UPDATE_LOGIN, '||
'OBJECT_VERSION_NUMBER) '||
' values '||
' ( :6, hierB.parent_depth_num, hierB.parent_id, '||
' :7, :8, :9, :10,'||
' :11, :12, :13, :14, :15, :16, :17 ) ';
l_leaf_flat_stmt := 'Insert into '||p_hier_table_name||
' ( '||
'HIERARCHY_OBJ_DEF_ID, '||
'PARENT_DEPTH_NUM, '||
'PARENT_ID, '||
'CHILD_DEPTH_NUM, '||
'CHILD_ID, '||
'SINGLE_DEPTH_FLAG,'||
'DISPLAY_ORDER_NUM,'||
'WEIGHTING_PCT, ' ||
'CREATION_DATE,'||
'CREATED_BY, '||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_DATE,'||
'LAST_UPDATE_LOGIN, '||
'OBJECT_VERSION_NUMBER) '||
' values '||
'( :1, '||
' :lparentdepthnum,'||
' :lparentid,'||
' :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12)' ;
l_select_stmt VARCHAR2(1000);
l_imm_delete_stmt VARCHAR2(1000);
* children of the deleted member
*/
IF(p_operation = 'RemoveImmChildren') THEN
l_imm_delete_stmt := 'DELETE FROM ' || p_hier_table_name ||
' WHERE hierarchy_obj_def_id = :1 ' ||
' AND child_id = :2 ' ||
' AND child_value_set_id = :3 ' ||
' AND single_depth_flag = ''N''';
EXECUTE IMMEDIATE l_imm_delete_stmt
using p_hier_obj_defn_id , p_imm_child_id,
p_imm_child_value_set_id;
l_chi_stmt := 'Select child_id, child_value_set_id '||
' from '||p_hier_table_name||
' where hierarchy_obj_def_id = '||p_hier_obj_defn_id||
' and parent_id = :1 '||
' and parent_value_set_id = :2 '||
' and single_depth_flag = ''N''';
l_del_stmt := 'Delete '||p_hier_table_name||
' where hierarchy_obj_def_id = '||p_hier_obj_defn_id||
' and child_id = :1 '||
' and child_value_set_id = :2 '||
' and single_depth_flag = ''N''';
/* Check if the immediate parent of the deleted
* member has any other children. If not,insert a leaf
* entry for the parent node only if it is not a root.
*/
l_select_stmt := ' SELECT h.child_depth_num ' ||
' FROM ' || p_hier_table_name || ' h ' ||
' WHERE h.hierarchy_obj_def_id = :1 AND ' ||
' h.child_id = :2 AND h.child_value_set_id = :3 AND ' ||
' h.single_depth_flag = ''Y'' ';
OPEN l_root_csr for l_select_stmt
USING p_hier_obj_defn_id,p_parent_id, p_parent_value_set_id;
l_select_stmt := ' SELECT ''Y'' FROM DUAL WHERE EXISTS ( ' ||
' SELECT 1 FROM ' || p_hier_table_name ||
' WHERE hierarchy_obj_def_id = :1 ' ||
' AND parent_id = :2 ' ||
' AND child_id <> :3 ' ||
' AND parent_value_set_id = :4 ' ||
' AND single_depth_flag = ''Y'' )';
OPEN l_imm_parent_child_csr for l_select_stmt
USING p_hier_obj_defn_id, p_parent_id,
p_focus_node,p_parent_value_set_id;
IF(l_imm_child_flag <> 'Y') -- No children.Insert leaf row.
THEN
/* Fetch parent's parent data.
* The display_order_number, weighting_pct
* fetched here will be used while
* inserting leaf row.*/
l_select_stmt := ' SELECT parent_id, parent_depth_num, ' ||
' child_id, child_depth_num, ' ||
' parent_value_set_id, child_value_set_id, ' ||
' display_order_num,weighting_pct ' ||
' FROM ' || p_hier_table_name || ' h ' ||
' WHERE h.hierarchy_obj_def_id = :1 ' ||
' AND h.child_id = :2 ' ||
' AND h.child_value_set_id = :3 ' ||
' AND h.single_depth_flag = ''Y'' ';
OPEN l_imm_parent_csr for l_select_stmt
USING p_hier_obj_defn_id, p_parent_id,p_parent_value_set_id;
l_leaf_flat_stmt := ' INSERT INTO '|| p_hier_table_name ||
' ( '||
'HIERARCHY_OBJ_DEF_ID, '||
'PARENT_DEPTH_NUM, '||
'PARENT_ID, '||
'PARENT_VALUE_SET_ID, '||
'CHILD_DEPTH_NUM, '||
'CHILD_ID, '||
'CHILD_VALUE_SET_ID, '||
'SINGLE_DEPTH_FLAG,'||
'DISPLAY_ORDER_NUM,'||
'WEIGHTING_PCT, ' ||
'CREATION_DATE,'||
'CREATED_BY, '||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_DATE,'||
'LAST_UPDATE_LOGIN, '||
'OBJECT_VERSION_NUMBER) '||
' values '||
'( :1, '||
' :lparentdepthnum,'||
' :lparentid,'||
' :lparentvaluesetid,'||
' :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)' ;
execute immediate 'Delete '||p_hier_table_name||
' where hierarchy_obj_def_id = '||p_hier_obj_defn_id||
' and child_id = '||p_focus_node ||
' and child_value_set_id = '||p_focus_value_set_id||
' and single_depth_flag = ''N''';
* children of the deleted member
*/
IF(p_operation = 'RemoveImmChildren') THEN
l_imm_delete_stmt := 'DELETE FROM ' || p_hier_table_name ||
' WHERE hierarchy_obj_def_id = :1 ' ||
' AND child_id = :2 ' ||
' AND single_depth_flag = ''N''';
EXECUTE IMMEDIATE l_imm_delete_stmt
using p_hier_obj_defn_id , p_imm_child_id;
l_chi_stmt := 'Select child_id '||
' from '||p_hier_table_name||
' where hierarchy_obj_def_id = '||p_hier_obj_defn_id||
' and parent_id = :1 '||
' and single_depth_flag = ''N''';
l_del_stmt := 'Delete '||p_hier_table_name||
' where hierarchy_obj_def_id = '||p_hier_obj_defn_id||
' and child_id = :1 '||
' and single_depth_flag = ''N''';
/* Check if the immediate parent of the deleted
* member has any other children. If not,insert a leaf
* entry for the parent node only if it is not a root.
*/
l_select_stmt := ' SELECT h.child_depth_num ' ||
' FROM ' || p_hier_table_name || ' h ' ||
' WHERE h.hierarchy_obj_def_id = :1 AND ' ||
' h.child_id = :2 AND ' ||
' h.single_depth_flag = ''Y'' ';
OPEN l_root_csr for l_select_stmt
USING p_hier_obj_defn_id,p_parent_id;
l_select_stmt := ' SELECT ''Y'' FROM DUAL WHERE EXISTS ( ' ||
' SELECT 1 FROM ' || p_hier_table_name ||
' WHERE hierarchy_obj_def_id = :1 ' ||
' AND parent_id = :2 ' ||
' AND child_id <> :3 ' ||
' AND single_depth_flag = ''Y'' )';
OPEN l_imm_parent_child_csr for l_select_stmt
USING p_hier_obj_defn_id, p_parent_id,
p_focus_node;
IF(l_imm_child_flag <> 'Y') -- No children.Insert leaf row.
THEN
/* Fetch parent's parent data.
* The display_order_number, weighting_pct
* fetched here will be used while
* inserting leaf row.*/
l_select_stmt := ' SELECT parent_id, parent_depth_num, ' ||
' child_id, child_depth_num, ' ||
' display_order_num,weighting_pct ' ||
' FROM ' || p_hier_table_name || ' h ' ||
' WHERE h.hierarchy_obj_def_id = :1 ' ||
' AND h.child_id = :2 ' ||
' AND h.single_depth_flag = ''Y'' ';
OPEN l_imm_parent_csr for l_select_stmt
USING p_hier_obj_defn_id, p_parent_id;
l_leaf_flat_stmt := 'INSERT INTO '||p_hier_table_name||
' ( '||
'HIERARCHY_OBJ_DEF_ID, '||
'PARENT_DEPTH_NUM, '||
'PARENT_ID, '||
'CHILD_DEPTH_NUM, '||
'CHILD_ID, '||
'SINGLE_DEPTH_FLAG,'||
'DISPLAY_ORDER_NUM,'||
'WEIGHTING_PCT, ' ||
'CREATION_DATE,'||
'CREATED_BY, '||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_DATE,'||
'LAST_UPDATE_LOGIN, '||
'OBJECT_VERSION_NUMBER) '||
' values '||
'( :1, '||
' :lparentdepthnum,'||
' :lparentid,'||
' :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12)' ;
execute immediate 'Delete '||p_hier_table_name||
' where hierarchy_obj_def_id = '||p_hier_obj_defn_id||
' and child_id = '||p_focus_node ||
' and single_depth_flag = ''N''';
| PROCEDURE Insert_Root_Node |
+===========================================================================*/
--
-- The API to insert Root Nodes.
--
PROCEDURE Insert_Root_Node (
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
p_return_status OUT NOCOPY VARCHAR2 ,
p_msg_count OUT NOCOPY NUMBER ,
p_msg_data OUT NOCOPY VARCHAR2 ,
p_rowid IN OUT NOCOPY VARCHAR2,
p_vs_required_flag IN VARCHAR2,
p_hier_table_name IN VARCHAR2,
p_hier_obj_def_id IN NUMBER,
p_parent_depth_num IN NUMBER,
p_parent_id IN NUMBER,
p_parent_value_set_id IN NUMBER,
p_child_depth_num IN NUMBER,
p_child_id IN NUMBER,
p_child_value_set_id IN NUMBER,
p_single_depth_flag IN VARCHAR2,
p_display_order_num IN NUMBER,
p_weighting_pct IN NUMBER ) IS
l_sql_stmt VARCHAR2(2000);
l_last_update_date DATE ;
l_last_Updated_by NUMBER ;
l_last_update_login NUMBER ;
SAVEPOINT Insert_Root_Node_Pvt ;
l_last_update_date := SYSDATE;
l_last_Updated_by := FND_GLOBAL.User_Id;
l_last_update_login := FND_GLOBAL.Login_Id ;
l_sql_stmt := 'INSERT INTO '||p_hier_table_name||
' ( '||
'HIERARCHY_OBJ_DEF_ID, '||
'PARENT_DEPTH_NUM, '||
'PARENT_ID, '||
'PARENT_VALUE_SET_ID, '||
'CHILD_DEPTH_NUM, '||
'CHILD_ID, '||
'CHILD_VALUE_SET_ID, '||
'SINGLE_DEPTH_FLAG,'||
'DISPLAY_ORDER_NUM,'||
'CREATION_DATE,'||
'CREATED_BY, '||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_DATE,'||
'LAST_UPDATE_LOGIN, '||
'OBJECT_VERSION_NUMBER) '||
' VALUES ('||
p_hier_obj_def_id||','||
p_parent_depth_num||','||
p_parent_id||','||
p_parent_value_set_id||','||
p_child_depth_num||','||
p_child_id||','||
p_child_value_set_id||','''||
p_single_depth_flag||''','||
p_display_order_num||','''||
sysdate||''','||
l_created_by||','||
l_last_Updated_by||','''||
sysdate||''','||
l_last_update_login ||','||1||')';
l_sql_stmt := 'INSERT INTO '||p_hier_table_name||
' ( '||
'HIERARCHY_OBJ_DEF_ID, '||
'PARENT_DEPTH_NUM, '||
'PARENT_ID, '||
'CHILD_DEPTH_NUM, '||
'CHILD_ID, '||
'SINGLE_DEPTH_FLAG,'||
'DISPLAY_ORDER_NUM,'||
'CREATION_DATE,'||
'CREATED_BY, '||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_DATE,'||
'LAST_UPDATE_LOGIN, '||
'OBJECT_VERSION_NUMBER) '||
' VALUES ('||
p_hier_obj_def_id||','||
p_parent_depth_num||','||
p_parent_id||','||
p_child_depth_num||','||
p_child_id||','''||
p_single_depth_flag||''','||
p_display_order_num||','''||
sysdate||''','||
l_created_by||','||
l_last_Updated_by||','''||
sysdate||''','||
l_last_update_login ||','||1||')';
ROLLBACK TO Insert_Root_Node_Pvt ;
ROLLBACK TO Insert_Root_Node_Pvt ;
ROLLBACK TO Insert_Root_Node_Pvt ;
END Insert_Root_Node;
l_last_update_date DATE ;
l_last_Updated_by NUMBER ;
l_last_update_login NUMBER ;
select *
from fem_hier_dimension_grps
where hierarchy_obj_id = p_hierarchy_obj_id;
select *
from fem_hier_value_sets
where hierarchy_obj_id = p_hierarchy_obj_id;
select *
from fem_hierarchies
where hierarchy_obj_id = p_hierarchy_obj_id;
select *
from fem_object_catalog_vl
where object_id = p_object_id;
l_last_update_date := SYSDATE;
l_last_Updated_by := FND_GLOBAL.User_Id;
l_last_update_login := FND_GLOBAL.Login_Id ;
select fem_object_id_seq.nextval
into l_new_object_id
from dual;
FEM_OBJECT_CATALOG_PKG.insert_row(
X_ROWID => l_row_id,
X_OBJECT_ID => l_new_object_id,
X_OBJECT_TYPE_CODE => l_object_catalog_details_rec.object_type_code,
X_FOLDER_ID => p_dest_hier_folder_id,
X_LOCAL_VS_COMBO_ID => l_object_catalog_details_rec.local_vs_combo_id,
X_OBJECT_ACCESS_CODE => l_object_catalog_details_rec.object_access_code,
X_OBJECT_ORIGIN_CODE => l_object_catalog_details_rec.object_origin_code,
X_OBJECT_VERSION_NUMBER => 1,
X_OBJECT_NAME => p_dest_hier_name,
X_DESCRIPTION => p_dest_hier_desc,
X_CREATION_DATE => l_creation_date,
X_CREATED_BY => l_created_by,
X_LAST_UPDATE_DATE => l_last_update_date,
X_LAST_UPDATED_BY => l_last_updated_by,
X_LAST_UPDATE_LOGIN => l_last_update_login);
INSERT INTO fem_hierarchies
(hierarchy_obj_id,
dimension_id,
hierarchy_type_code,
group_sequence_enforced_code,
multi_top_flag,
financial_category_flag,
value_set_id, -- ??? may go away
calendar_id,
period_type,
personal_flag,
flattened_rows_flag,
hierarchy_usage_code,
multi_value_set_flag,
object_version_number,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(l_new_object_id,
l_hier_details_rec.dimension_id,
l_hier_details_rec.hierarchy_type_code,
l_hier_details_rec.group_sequence_enforced_code,
l_hier_details_rec.multi_top_flag,
l_hier_details_rec.financial_category_flag,
l_hier_details_rec.value_set_id, -- ??? may go away
l_hier_details_rec.calendar_id,
l_hier_details_rec.period_type,
l_hier_details_rec.personal_flag,
l_hier_details_rec.flattened_rows_flag,
l_hier_details_rec.hierarchy_usage_code,
l_hier_details_rec.multi_value_set_flag,
1,
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login);
INSERT INTO fem_hier_dimension_grps
(dimension_group_id,
hierarchy_obj_id,
relative_dimension_group_seq,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(l_hier_dim_groups_rec.dimension_group_id,
l_new_object_id,
l_hier_dim_groups_rec.relative_dimension_group_seq,
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
INSERT INTO fem_hier_value_sets
(hierarchy_obj_id,
value_set_id,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(l_new_object_id,
l_hier_value_sets_rec.value_set_id,
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
SELECT parent_id,
child_id,
child_sequence_num
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = c_hierarchy_id
AND c_dest_version_start_date
BETWEEN effective_start_date AND effective_end_date
MINUS
SELECT parent_id,
child_id,
child_sequence_num
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = c_hierarchy_id
AND c_src_version_start_date
BETWEEN effective_start_date AND effective_end_date;
SELECT parent_id,
child_id,
display_order_num,
bom_reference,
parent_qty,
child_qty,
yield_percentage
FROM fem_cost_objects_hier fcoh,
fem_cost_obj_hier_qty fcohq
WHERE hierarchy_obj_id = c_hierarchy_id
AND fcoh.relationship_id = fcohq.relationship_id
AND c_src_version_start_date
BETWEEN effective_start_date AND effective_end_date
MINUS
SELECT parent_id,
child_id,
display_order_num,
bom_reference,
parent_qty,
child_qty,
yield_percentage
FROM fem_cost_objects_hier fcoh,
fem_cost_obj_hier_qty fcohq
WHERE hierarchy_obj_id = c_hierarchy_id
AND fcoh.relationship_id = fcohq.relationship_id
AND c_dest_version_start_date
BETWEEN effective_start_date AND effective_end_date;
SELECT effective_start_date
FROM fem_object_definition_b
WHERE object_definition_id = c_version_id;
l_last_update_date DATE := sysdate;
l_last_Updated_by NUMBER := fnd_global.user_id;
l_last_update_login NUMBER := fnd_global.login_id;
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_products_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_products_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_channels_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_channels_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_cctr_orgs_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_cctr_orgs_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_customers_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_customers_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_entities_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_entities_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_geography_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_geography_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_ln_items_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_ln_items_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_nat_accts_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_nat_accts_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_projects_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_projects_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_tasks_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_tasks_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_projects_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_projects_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_user_dim1_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_user_dim1_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_user_dim2_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_user_dim2_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_user_dim3_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_user_dim3_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_user_dim4_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_user_dim4_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_user_dim5_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_user_dim5_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_user_dim6_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_user_dim6_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_user_dim7_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_user_dim7_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_user_dim8_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_user_dim8_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_user_dim9_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_user_dim9_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_prnt_value_set_id,
t_chld_depth_num,
t_chld_id,
t_chld_value_set_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_user_dim10_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_user_dim10_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
parent_value_set_id,
child_depth_num,
child_id,
child_value_set_id ,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_prnt_value_set_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_chld_value_set_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
select rowid,
parent_depth_num,
parent_id,
child_depth_num,
child_id,
single_depth_flag,
display_order_num,
weighting_pct
bulk collect into
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_chld_depth_num,
t_chld_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
from fem_cal_periods_hier
where hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_cal_periods_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
child_depth_num,
child_id,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
SELECT effective_start_date
INTO l_version_start_date
FROM fem_object_definition_b
WHERE object_definition_id = p_src_hier_version_id ;
SELECT fcoh.rowid,
fem_cost_objects_hier_s.NEXTVAL,
effective_start_date,
parent_id,
child_id,
child_sequence_num,
display_order_num,
effective_end_date,
bom_reference,
dataset_code,
cal_period_id,
parent_qty,
child_qty,
yield_percentage
BULK COLLECT INTO
t_co_row_id,
t_co_rel_id,
t_co_eff_start_date,
t_co_prnt_id,
t_co_chld_id,
t_co_chld_sequence_num,
t_co_disp_order_num,
t_co_eff_end_date,
t_co_bom_ref,
t_co_ds_code,
t_co_cal_per_id,
t_co_prnt_qty,
t_co_chld_qty,
t_co_yld_percentage
FROM fem_cost_objects_hier fcoh,
fem_cost_obj_hier_qty fcohq
WHERE fcoh.relationship_id = fcohq.relationship_id
AND hierarchy_obj_id = p_src_hier_obj_id
AND l_version_start_date BETWEEN effective_start_date
AND effective_end_date;
INSERT INTO fem_cost_objects_hier
(relationship_id,
effective_start_date,
hierarchy_obj_id,
parent_id,
child_id,
child_sequence_num,
display_order_num,
effective_end_date,
bom_reference,
creation_date,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES
(t_co_rel_id(rec),
t_co_eff_start_date(rec),
p_dest_hier_obj_id,
t_co_prnt_id(rec),
t_co_chld_id(rec),
t_co_chld_sequence_num(rec),
t_co_disp_order_num(rec),
t_co_eff_end_date(rec),
t_co_bom_ref(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
INSERT INTO fem_cost_obj_hier_qty
(relationship_id,
dataset_code,
cal_period_id,
child_qty,
parent_qty,
yield_percentage,
creation_date,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES
(t_co_rel_id(rec),
t_co_ds_code(rec),
t_co_cal_per_id(rec),
t_co_chld_qty(rec),
t_co_prnt_qty(rec),
t_co_yld_percentage(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
SELECT rowid,
parent_depth_num,
parent_id,
child_depth_num,
child_id,
single_depth_flag,
display_order_num,
weighting_pct
BULK COLLECT INTO
t_row_id,
t_prnt_depth_num,
t_prnt_id,
t_chld_depth_num,
t_chld_id,
t_sngle_depth_flag,
t_dspy_order_num,
t_wt_pct
FROM fem_activities_hier
WHERE hierarchy_obj_def_id = p_src_hier_version_id;
INSERT INTO fem_activities_hier
( hierarchy_obj_def_id,
parent_depth_num,
parent_id ,
child_depth_num,
child_id,
single_depth_flag,
display_order_num ,
weighting_pct,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
VALUES (p_dest_hier_version_id,
t_prnt_depth_num(rec),
t_prnt_id(rec),
t_chld_depth_num(rec),
t_chld_id(rec),
t_sngle_depth_flag(rec),
t_dspy_order_num(rec),
t_wt_pct(rec),
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
l_last_update_date DATE := sysdate;
l_last_Updated_by NUMBER := fnd_global.user_id;
l_last_update_login NUMBER := fnd_global.login_id;
SELECT *
FROM fem_object_definition_vl
WHERE object_definition_id = p_version_id;
SELECT hier_versioning_type_code
FROM fem_xdim_dimensions
WHERE dimension_id = (SELECT dimension_id
FROM fem_hierarchies
WHERE hierarchy_obj_id = c_hierarchy_id);
select fem_object_definition_id_seq.nextval
into l_new_version_id
from dual;
FEM_OBJECT_DEFINITION_PKG.INSERT_ROW (X_ROWID => l_row_id,
X_OBJECT_DEFINITION_ID => l_new_version_id,
X_OBJECT_VERSION_NUMBER => 1,
X_OBJECT_ID => p_dest_hier_obj_id,
X_EFFECTIVE_START_DATE => p_dest_start_date,
X_EFFECTIVE_END_DATE => p_dest_end_date,
X_OBJECT_ORIGIN_CODE => l_object_definition_rec.object_origin_code,
X_APPROVAL_STATUS_CODE => l_object_definition_rec.approval_status_code,
X_OLD_APPROVED_COPY_FLAG => l_object_definition_rec.old_approved_copy_flag,
X_OLD_APPROVED_COPY_OBJ_DEF_ID => l_object_definition_rec.old_approved_copy_obj_def_id,
X_APPROVED_BY => l_object_definition_rec.approved_by,
X_APPROVAL_DATE => l_object_definition_rec.approval_date,
X_DISPLAY_NAME => p_dest_version_name,
X_DESCRIPTION => p_dest_version_desc,
X_CREATION_DATE => l_creation_date,
X_CREATED_BY => l_created_by,
X_LAST_UPDATE_DATE => l_last_update_date,
X_LAST_UPDATED_BY => l_last_updated_by,
X_LAST_UPDATE_LOGIN => l_last_update_login);
INSERT INTO fem_hier_definitions
(hierarchy_obj_def_id,
flattened_rows_completion_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(l_new_version_id,
'COMPLETED',
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
| FUNCTION Can_Delete_Hier_Version |
+===========================================================================*/
--
-- This function returns 'N' if the concurrent request is submitted for deleting
-- a Hierarchy Version.
--
FUNCTION can_delete_hier_version (p_hier_version_id IN NUMBER, p_folder_id IN NUMBER)
return VARCHAR2 AS
l_delete_status VARCHAR2(1) := 'Y';
SELECT flattened_rows_completion_code
INTO l_hier_version_status
FROM fem_hier_definitions
WHERE hierarchy_obj_def_id = p_hier_version_id;
SELECT WRITE_FLAG
INTO l_write_status
FROM FEM_USER_FOLDERS
WHERE FOLDER_ID = p_folder_id
AND USER_ID = FND_GLOBAL.USER_ID ;
l_delete_status := 'N';
l_delete_status := 'N';
return l_delete_status;
return l_delete_status;
end can_delete_hier_version;
SELECT flattened_rows_completion_code
INTO l_hier_version_status
FROM fem_hier_definitions
WHERE hierarchy_obj_def_id = p_hier_version_id;
SELECT flattened_rows_completion_code
INTO l_hier_version_status
FROM fem_hier_definitions
WHERE hierarchy_obj_def_id = p_hier_version_id;
| FUNCTION is_hier_ver_deleted |
+===========================================================================*/
--
-- This function returns 'N' if the concurrent request is submitted for deleting
-- a Hierarchy version.
--
FUNCTION is_hier_ver_deleted (p_hier_ver_id IN NUMBER)
return VARCHAR2 AS
l_delete_status VARCHAR2(1) := 'Y';
SELECT flattened_rows_completion_code
INTO l_hier_version_status
FROM fem_hier_definitions
WHERE hierarchy_obj_def_id = p_hier_ver_id;
l_delete_status := 'N';
return l_delete_status ;
return l_delete_status ;
return l_delete_status;
end is_hier_ver_deleted;
| FUNCTION is_hier_deleted |
+===========================================================================*/
--
-- This function returns 'N' if the concurrent request is submitted for deleting
-- a Hierarchy.
--
FUNCTION is_hier_deleted (p_hierarchy_id IN NUMBER)
return VARCHAR2 AS
l_delete_status VARCHAR2(1) := 'Y';
SELECT distinct flattened_rows_completion_code
INTO l_hier_status
FROM fem_hier_definitions hierDef,
fem_object_definition_b objDef
WHERE hierDef.hierarchy_obj_def_id = objDef.object_definition_id
AND objDef.object_id = p_hierarchy_id;
l_delete_status := 'N';
return l_delete_status;
return l_delete_status;
end is_hier_deleted;
| FUNCTION Can_Delete_Hierarchy |
+===========================================================================*/
--
-- This function returns 'N' if the concurrent request is submitted for deleting
-- a Hierarchy.
--
FUNCTION can_delete_hierarchy (p_hierarchy_id IN NUMBER, p_folder_id IN NUMBER)
return VARCHAR2 AS
l_delete_status VARCHAR2(1) := 'Y';
SELECT distinct flattened_rows_completion_code
INTO l_hier_status
FROM fem_hier_definitions hierDef,
fem_object_definition_b objDef
WHERE hierDef.hierarchy_obj_def_id = objDef.object_definition_id
AND objDef.object_id = p_hierarchy_id;
SELECT WRITE_FLAG
INTO l_write_status
FROM FEM_USER_FOLDERS
WHERE FOLDER_ID = p_folder_id
AND USER_ID = FND_GLOBAL.USER_ID ;
l_delete_status := 'N';
l_delete_status := 'N';
return l_delete_status;
return l_delete_status;
end can_delete_hierarchy;
SAVEPOINT Launch_Delete_Hier;
UPDATE fem_hier_definitions
SET flattened_rows_completion_code = 'PENDING_DELETION'
WHERE hierarchy_obj_def_id in (select object_definition_id
from fem_object_definition_vl
where object_id = p_hier_obj_id);
delete_hierarchy (p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_hier_table_name => p_hier_table_name,
p_hier_obj_id => p_hier_obj_id);
ROLLBACK TO Launch_Delete_Hier;
UPDATE fem_hier_definitions
SET flattened_rows_completion_code = 'COMPLETE'
WHERE hierarchy_obj_def_id in (select object_definition_id
from fem_object_definition_vl
where object_id = p_hier_obj_id);
| PROCEDURE Delete_Hierarchy |
+===========================================================================*/
--
-- The API to delete a Hierarchy.
--
PROCEDURE Delete_Hierarchy(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
p_return_status OUT NOCOPY VARCHAR2 ,
p_msg_count OUT NOCOPY NUMBER ,
p_msg_data OUT NOCOPY VARCHAR2 ,
p_hier_table_name IN VARCHAR2,
p_hier_obj_id IN NUMBER )
is
cursor obj_def_csr (p_object_id IN NUMBER)
is
select object_definition_id
from fem_object_definition_vl
where object_id = p_object_id;
l_delete_allowed BOOLEAN;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Hierarchy' ;
SAVEPOINT Delete_Hierarachy_Pvt;
delete_hier_version( p_api_version => p_api_version,
p_return_status => p_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_hier_table_name => p_hier_table_name,
p_hier_obj_id => p_hier_obj_id,
p_hier_version_id => NULL);
DELETE FROM fem_hier_dimension_grps
WHERE hierarchy_obj_id = p_hier_obj_id;
DELETE FROM fem_hier_value_sets
WHERE hierarchy_obj_id = p_hier_obj_id;
DELETE FROM fem_hierarchies
WHERE hierarchy_obj_id = p_hier_obj_id;
ROLLBACK TO Delete_Hierarachy_Pvt ;
ROLLBACK TO Delete_Hierarachy_Pvt ;
ROLLBACK TO Delete_Hierarachy_Pvt ;
end Delete_Hierarchy;
SELECT flattened_rows_completion_code
INTO l_current_hier_status
FROM fem_hier_definitions
WHERE hierarchy_obj_def_id = p_hier_version_id;
UPDATE fem_hier_definitions
SET flattened_rows_completion_code = 'PENDING_DELETION'
WHERE hierarchy_obj_def_id = p_hier_version_id;
delete_hier_version (p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_hier_table_name => p_hier_table_name,
p_hier_obj_id => p_hier_obj_id,
p_hier_version_id => p_hier_version_id);
UPDATE fem_hier_definitions
SET flattened_rows_completion_code = l_current_hier_status
WHERE hierarchy_obj_def_id = p_hier_version_id;
UPDATE fem_hier_definitions
SET flattened_rows_completion_code = 'COMPLETE'
WHERE hierarchy_obj_def_id = p_hier_version_id;
| PROCEDURE Delete_Hier_Version |
+===========================================================================*/
--
-- The API to Delete Hierarchy Version.
--
PROCEDURE Delete_Hier_Version(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
p_return_status OUT NOCOPY VARCHAR2 ,
p_msg_count OUT NOCOPY NUMBER ,
p_msg_data OUT NOCOPY VARCHAR2 ,
p_hier_table_name IN VARCHAR2,
p_hier_obj_id IN NUMBER,
p_hier_version_id IN NUMBER)
IS
CURSOR obj_def_csr (p_object_id IN NUMBER)
IS
SELECT object_definition_id
FROM fem_object_definition_vl
WHERE object_id = p_object_id;
SELECT 'Y' FROM dual
WHERE EXISTs
(SELECT 1
FROM fem_object_catalog_vl obj,
fem_object_definition_vl ver
WHERE obj.object_id = ver.object_id
AND ver.object_definition_id <> p_version_id
AND ver.object_id = p_object_id);
SELECT parent_id,
child_id,
child_sequence_num
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = c_hierarchy_id
AND (SELECT effective_start_date
FROM fem_object_definition_b
WHERE object_definition_id = c_version_id) BETWEEN
effective_start_date AND effective_end_date;
l_delete_allowed BOOLEAN;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Hier_Version' ;
SAVEPOINT Delete_Hier_Version_Pvt;
DELETE FROM fem_hier_definitions
WHERE hierarchy_obj_def_id = obj_def_rec.object_definition_id;
DELETE FROM fem_cost_obj_hier_qty
WHERE relationship_id IN (SELECT relationship_id
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = p_hier_obj_id);
DELETE FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = p_hier_obj_id;
l_sql_stmt := 'DELETE FROM '||p_hier_table_name||
' WHERE hierarchy_obj_def_id = '||obj_def_rec.object_definition_id;
FEM_BUSINESS_RULE_PVT.DeleteObjectDefinition
(p_object_type_code => 'HIERARCHY',
p_obj_def_id => obj_def_rec.object_definition_id,
x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data);
l_sql_stmt := 'DELETE FROM '||p_hier_table_name||
' WHERE hierarchy_obj_def_id = '||p_hier_version_id;
DELETE FROM fem_hier_definitions
WHERE hierarchy_obj_def_id = p_hier_version_id;
FEM_BUSINESS_RULE_PVT.DeleteObjectDefinition
(p_object_type_code => 'HIERARCHY',
p_obj_def_id => p_hier_version_id,
x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data);
ROLLBACK TO Delete_Hier_Version_Pvt ;
ROLLBACK TO Delete_Hier_Version_Pvt ;
ROLLBACK TO Delete_Hier_Version_Pvt ;
end Delete_Hier_Version;
l_select_stmt VARCHAR2(1000);
l_delete_stmt VARCHAR2(1000);
l_update_stmt VARCHAR2(1000);
l_select_stmt := 'SELECT h.child_id, h.child_depth_num, ' ||
'h.display_order_num ' ||
'FROM ' || p_hier_table_name || ' h ' ||
'WHERE h.hierarchy_obj_def_id = :1 AND ' ||
'h.child_id = :2 AND h.single_depth_flag = ''Y'' ' ||
'UNION ' ||
'SELECT h.child_id, h.child_depth_num, ' ||
'h.display_order_num ' ||
'FROM ' || p_hier_table_name || ' h ' ||
'WHERE h.hierarchy_obj_def_id = :3 AND ' ||
'h.parent_id <> h.child_id ' ||
'START WITH h.parent_id = :4 AND ' ||
'h.hierarchy_obj_def_id = :5 AND ' ||
'h.single_depth_flag = ''Y'' AND ' ||
'h.parent_depth_num <> h.child_depth_num ' ||
'CONNECT BY PRIOR h.child_id = h.parent_id AND ' ||
'h.hierarchy_obj_def_id = :6 AND ' ||
'h.single_depth_flag = ''Y'' AND ' ||
'h.parent_depth_num <> h.child_depth_num ' ||
'ORDER BY child_depth_num DESC';
OPEN l_hier_csr for l_select_stmt
USING p_version_id, p_member_id, p_version_id, p_member_id,
p_version_id, p_version_id;
l_select_stmt := 'SELECT h.parent_id, h.parent_depth_num, ' ||
'h.child_depth_num, h.display_order_num ' ||
'FROM ' || p_hier_table_name || ' h ' ||
'WHERE h.hierarchy_obj_def_id = :1 AND ' ||
'h.child_id = :2 AND h.single_depth_flag = ''Y'' ';
OPEN l_root_csr for l_select_stmt
USING p_version_id, p_member_id;
l_delete_stmt := 'DELETE FROM ' || p_hier_table_name ||
' WHERE hierarchy_obj_def_id = :1' ||
' AND child_id = :2' ||
' AND child_depth_num = :3' ||
' AND single_depth_flag = ''Y''';
l_update_stmt := 'Update ' || p_hier_table_name || ' ' ||
'SET display_order_num = display_order_num - 1, ' ||
'object_version_number = object_version_number + 1, ' ||
'last_updated_by = :1, ' ||
'last_update_date = :2' ||
'WHERE hierarchy_obj_def_id = :3 ' ||
'AND child_id = :4 ' ||
'AND child_depth_num = :5 ' ||
'AND single_depth_flag = ''Y''';
l_select_stmt := 'SELECT child_id ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND parent_id = child_id ' ||
'AND single_depth_flag = ''Y'' ' ||
'AND display_order_num > :2 ';
OPEN l_dspl_csr for l_select_stmt
USING p_version_id, l_display_order_num;
execute immediate l_update_stmt
using p_user_id, sysdate, p_version_id, l_child_id, 1;
l_select_stmt := 'SELECT child_id, child_depth_num ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND parent_id = :2 ' ||
'AND single_depth_flag = ''Y'' ' ||
'AND NOT (parent_id = child_id ' ||
'AND parent_depth_num = child_depth_num) ' ||
'AND display_order_num > :3 ';
OPEN l_dspl_csr for l_select_stmt
USING p_version_id, l_parent_id, l_display_order_num;
execute immediate l_update_stmt
using p_user_id, sysdate, p_version_id, l_child_id,
l_child_depth_num;
* of the 'to be deleted' member
*/
l_select_stmt := ' SELECT child_id ' ||
' FROM ' || p_hier_table_name ||
' WHERE hierarchy_obj_def_id = :1 ' ||
' AND parent_id = :2 ' ||
' AND single_depth_flag = ''Y''';
OPEN l_imm_child_csr for l_select_stmt
USING p_version_id, p_member_id;
* 'to be deleted' member. This will be passed
* to unflatten_focus_node_tree() where leaf node
* entry for parent node will be inserted depending
* upon whether the parent has any other children and
* it is a root node or not.
*/
l_select_stmt := ' SELECT parent_id ' ||
' FROM ' || p_hier_table_name || ' h ' ||
' WHERE h.hierarchy_obj_def_id = :1 ' ||
' AND h.child_id = :2 ' ||
' AND h.single_depth_flag = ''Y'' ';
OPEN l_imm_parent_csr FOR l_select_stmt
USING p_version_id, p_member_id;
execute immediate l_delete_stmt
using p_version_id, l_child_id_tbl(i), l_child_depth_num_tbl(i);
l_select_stmt := 'SELECT h.child_id, h.child_value_set_id, ' ||
'h.child_depth_num, h.display_order_num ' ||
'FROM ' || p_hier_table_name || ' h ' ||
'WHERE h.hierarchy_obj_def_id = :1 AND ' ||
'h.child_id = :2 AND h.child_value_set_id = :3 AND ' ||
'h.single_depth_flag = ''Y'' ' ||
'UNION ' ||
'SELECT h.child_id, h.child_value_set_id, ' ||
'h.child_depth_num, h.display_order_num ' ||
'FROM ' || p_hier_table_name || ' h ' ||
'WHERE h.hierarchy_obj_def_id = :4 AND ' ||
'NOT (h.parent_id = h.child_id AND ' ||
'h.parent_value_set_id = h.child_value_set_id) ' ||
'START WITH h.parent_id = :5 AND ' ||
'h.parent_value_set_id = :6 AND ' ||
'h.hierarchy_obj_def_id = :7 AND ' ||
'h.single_depth_flag = ''Y'' AND ' ||
'h.parent_depth_num <> h.child_depth_num ' ||
'CONNECT BY PRIOR h.child_id = h.parent_id AND ' ||
'PRIOR h.child_value_set_id = h.parent_value_set_id ' ||
'AND ' ||
'h.hierarchy_obj_def_id = :8 AND ' ||
'h.single_depth_flag = ''Y'' AND ' ||
'h.parent_depth_num <> h.child_depth_num ' ||
'ORDER BY child_depth_num DESC';
OPEN l_hier_csr for l_select_stmt
USING p_version_id, p_member_id, p_value_set_id,
p_version_id, p_member_id, p_value_set_id,
p_version_id, p_version_id;
l_select_stmt := 'SELECT h.parent_id, h.parent_value_set_id, ' ||
'h.parent_depth_num, h.child_depth_num, ' ||
'h.display_order_num ' ||
'FROM ' || p_hier_table_name || ' h ' ||
'WHERE h.hierarchy_obj_def_id = :1 AND ' ||
'h.child_id = :2 AND h.child_value_set_id = :3 AND ' ||
'h.single_depth_flag = ''Y'' ';
OPEN l_root_csr for l_select_stmt
USING p_version_id, p_member_id, p_value_set_id;
l_delete_stmt := 'DELETE FROM ' || p_hier_table_name ||
' WHERE hierarchy_obj_def_id = :1' ||
' AND child_id = :2' ||
' AND child_value_set_id = :3 ' ||
' AND child_depth_num = :4' ||
' AND single_depth_flag = ''Y''';
l_update_stmt := 'Update ' || p_hier_table_name || ' ' ||
'SET display_order_num = display_order_num - 1, ' ||
'object_version_number = object_version_number + 1, ' ||
'last_updated_by = :1, ' ||
'last_update_date = :2' ||
'WHERE hierarchy_obj_def_id = :3 ' ||
'AND child_id = :4 ' ||
'AND child_value_set_id = :5 ' ||
'AND child_depth_num = :6 ' ||
'AND single_depth_flag = ''Y''';
l_select_stmt := 'SELECT child_id, child_value_set_id ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND parent_id = child_id ' ||
'AND parent_value_set_id = child_value_set_id ' ||
'AND single_depth_flag = ''Y'' ' ||
'AND display_order_num > :2 ';
OPEN l_dspl_csr for l_select_stmt
USING p_version_id, l_display_order_num;
execute immediate l_update_stmt
using p_user_id, sysdate, p_version_id, l_child_id,
l_child_value_set_id, 1;
l_select_stmt := 'SELECT child_id, child_value_set_id, ' ||
'child_depth_num ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND parent_id = :2 ' ||
'AND parent_value_set_id = :3 ' ||
'AND single_depth_flag = ''Y'' ' ||
'AND NOT(parent_id = child_id ' ||
'AND parent_value_set_id = child_value_set_id ' ||
'AND parent_depth_num = child_depth_num) ' ||
'AND display_order_num > :4 ';
OPEN l_dspl_csr for l_select_stmt
USING p_version_id, l_parent_id,
l_parent_value_set_id, l_display_order_num;
execute immediate l_update_stmt
using p_user_id, sysdate, p_version_id, l_child_id,
l_child_value_set_id, l_child_depth_num;
* of the 'to be deleted' member
*/
l_select_stmt := ' SELECT child_id, child_value_set_id ' ||
' FROM ' || p_hier_table_name ||
' WHERE hierarchy_obj_def_id = :1 ' ||
' AND parent_id = :2 ' ||
' AND parent_value_set_id = :3 ' ||
' AND single_depth_flag = ''Y''';
OPEN l_imm_child_csr for l_select_stmt
USING p_version_id, p_member_id,p_value_set_id;
* 'to be deleted' member. This will be passed
* to unflatten_focus_node_tree() where leaf node
* entry for parent node will be inserted depending
* upon whether the parent has any other children and
* it is a root node or not.
*/
l_select_stmt := ' SELECT parent_id, parent_value_set_id ' ||
' FROM ' || p_hier_table_name || ' h ' ||
' WHERE h.hierarchy_obj_def_id = :1 ' ||
' AND h.child_id = :2 ' ||
' AND h.child_value_set_id = :3 ' ||
' AND h.single_depth_flag = ''Y'' ';
OPEN l_imm_parent_csr FOR l_select_stmt
USING p_version_id, p_member_id, p_value_set_id;
execute immediate l_delete_stmt
using p_version_id, l_child_id_tbl(i),
l_child_value_set_id_tbl(i), l_child_depth_num_tbl(i);
* 'to be deleted member'.
*/
IF(l_imm_child_id_tbl.COUNT > 0 ) THEN
FOR i in l_imm_child_id_tbl.FIRST .. l_imm_child_id_tbl.LAST
LOOP
l_req_id := FND_REQUEST.SUBMIT_REQUEST
(application => 'FEM',
program => 'DHMHVMFL',
description => NULL,
start_time => NULL,
sub_request => FALSE,
argument1 => p_version_id,
argument2 => p_hier_table_name,
argument3 => p_member_id,
argument4 => p_value_set_id,
argument5 => NULL,
argument6 => NULL,
argument7 => l_imm_child_id_tbl(i),
argument8 => l_imm_child_value_set_id_tbl(i),
argument9 => 'RemoveImmChildren');
FND_MESSAGE.SET_NAME('FEM', 'FEM_DHM_MEM_DELETED_ERR');
l_last_update_login NUMBER:= FND_GLOBAL.Login_Id ;
l_select_stmt VARCHAR2(1000);
l_insert_stmt VARCHAR2(1000);
l_select_stmt := 'SELECT child_depth_num, weighting_pct ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 AND child_id = :2 ' ||
'AND single_depth_flag = ''Y''';
OPEN l_src_node_csr for l_select_stmt
USING p_version_id, p_source_member_id;
l_select_stmt := 'SELECT parent_id, parent_depth_num, ' ||
'child_id, child_depth_num, ' ||
'display_order_num, weighting_pct ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 AND ' ||
'parent_id <> child_id ' ||
'START WITH parent_id = :2 AND ' ||
'hierarchy_obj_def_id = :3 AND ' ||
'single_depth_flag = ''Y'' AND ' ||
'parent_depth_num <> child_depth_num ' ||
'CONNECT BY PRIOR child_id = parent_id AND ' ||
'hierarchy_obj_def_id = :4 AND ' ||
'single_depth_flag = ''Y'' AND ' ||
'parent_depth_num <> child_depth_num ' ||
'ORDER BY child_depth_num DESC';
OPEN l_whl_hier_csr for l_select_stmt
USING p_version_id, p_source_member_id, p_version_id, p_version_id;
l_select_stmt := 'SELECT parent_depth_num, child_depth_num, ' ||
'max(display_order_num) ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND parent_id = :2 ' ||
'AND single_depth_flag = ''Y'' ' ||
'GROUP BY child_depth_num, parent_depth_num ' ||
'ORDER BY child_depth_num DESC, parent_depth_num DESC';
OPEN l_dpth_csr for l_select_stmt
USING p_version_id, p_dest_member_id;
l_select_stmt := 'SELECT child_depth_num ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND child_id = :2 ' ||
'AND NOT(parent_id = child_id ' ||
'AND parent_depth_num = child_depth_num) ' ||
'AND single_depth_flag = ''Y''';
OPEN l_leaf_csr for l_select_stmt
USING p_version_id, p_dest_member_id;
*the below insert stmt is executed, we get a unique key violation.
*/
execute immediate 'DELETE FROM ' || p_hier_table_name ||
' WHERE hierarchy_obj_def_id = ' || p_version_id ||
' AND parent_id = ' || p_dest_member_id ||
' AND child_id = ' || p_source_member_id ||
' AND single_depth_flag = ''N''';
l_insert_stmt := 'INSERT INTO '||p_hier_table_name||
' ( '||
'HIERARCHY_OBJ_DEF_ID, '||
'PARENT_DEPTH_NUM, '||
'PARENT_ID, '||
'CHILD_DEPTH_NUM, '||
'CHILD_ID, '||
'SINGLE_DEPTH_FLAG,'||
'DISPLAY_ORDER_NUM,'||
'WEIGHTING_PCT, ' ||
'CREATION_DATE,'||
'CREATED_BY, '||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_DATE,'||
'LAST_UPDATE_LOGIN, '||
'OBJECT_VERSION_NUMBER) '||
' VALUES ('||
':1,'||
':2,'||
':3,'||
':4,'||
':5,'||
':6,'||
':7,'||
':8,'||
':9,'||
p_user_id||','||
p_user_id||','||
':10,'||
l_last_update_login ||','||1||')';
execute immediate l_insert_stmt
using p_version_id,
l_parent_depth_num, p_dest_member_id,
l_child_depth_num, p_source_member_id,
'Y', l_display_order_num, l_weighting_pct,
sysdate, sysdate;
execute immediate l_insert_stmt
using p_version_id,
l_parent_depth_num_tbl(i), l_parent_id_tbl(i),
l_child_depth_num_tbl(i), l_child_id_tbl(i),
'Y', l_display_order_num_tbl(i), l_weighting_pct_tbl(i),
sysdate, sysdate;
l_select_stmt := 'SELECT child_depth_num, weighting_pct ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 AND ' ||
'child_id = :2 AND child_value_set_id = :3 AND ' ||
'single_depth_flag = ''Y''';
OPEN l_src_node_csr for l_select_stmt
USING p_version_id, p_source_member_id, p_source_value_set_id;
l_select_stmt := 'SELECT parent_id, parent_value_set_id, ' ||
'parent_depth_num, ' ||
'child_id, child_value_set_id, child_depth_num, ' ||
'display_order_num, weighting_pct ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 AND ' ||
'NOT (parent_id = child_id AND ' ||
'parent_value_set_id = child_value_set_id) ' ||
'START WITH parent_id = :2 AND ' ||
'parent_value_set_id = :3 AND ' ||
'hierarchy_obj_def_id = :4 AND ' ||
'single_depth_flag = ''Y'' AND ' ||
'parent_depth_num <> child_depth_num ' ||
'CONNECT BY PRIOR child_id = parent_id AND ' ||
'PRIOR child_value_set_id = parent_value_set_id AND ' ||
'hierarchy_obj_def_id = :5 AND ' ||
'single_depth_flag = ''Y'' AND ' ||
'parent_depth_num <> child_depth_num ' ||
'ORDER BY child_depth_num DESC';
OPEN l_whl_hier_csr for l_select_stmt
USING p_version_id, p_source_member_id, p_source_value_set_id,
p_version_id, p_version_id;
l_select_stmt := 'SELECT parent_depth_num, child_depth_num, ' ||
'max(display_order_num) ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND parent_id = :2 ' ||
'AND parent_value_set_id = :3 ' ||
'AND single_depth_flag = ''Y'' ' ||
'GROUP BY child_depth_num, parent_depth_num ' ||
'ORDER BY child_depth_num DESC, parent_depth_num DESC';
OPEN l_dpth_csr for l_select_stmt
USING p_version_id, p_dest_member_id, p_dest_value_set_id;
l_select_stmt := 'SELECT child_depth_num ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND child_id = :2 ' ||
'AND child_value_set_id = :3 ' ||
'AND NOT(parent_id = child_id ' ||
'AND parent_value_set_id = child_value_set_id ' ||
'AND parent_depth_num = child_depth_num) ' ||
'AND single_depth_flag = ''Y''';
OPEN l_leaf_csr for l_select_stmt
USING p_version_id, p_dest_member_id, p_dest_value_set_id;
*the below insert stmt is executed, we get a unique key violation.
*/
execute immediate 'DELETE FROM ' || p_hier_table_name ||
' WHERE hierarchy_obj_def_id = ' || p_version_id ||
' AND parent_id = ' || p_dest_member_id ||
' AND child_id = ' || p_source_member_id ||
' AND parent_value_set_id = ' || p_dest_value_set_id ||
' AND child_value_set_id = ' || p_source_value_set_id ||
' AND single_depth_flag = ''N''';
l_insert_stmt := 'INSERT INTO '||p_hier_table_name||
' ( '||
'HIERARCHY_OBJ_DEF_ID, '||
'PARENT_DEPTH_NUM, '||
'PARENT_ID, '||
'PARENT_VALUE_SET_ID, '||
'CHILD_DEPTH_NUM, '||
'CHILD_ID, '||
'CHILD_VALUE_SET_ID, '||
'SINGLE_DEPTH_FLAG,'||
'DISPLAY_ORDER_NUM,'||
'WEIGHTING_PCT, ' ||
'CREATION_DATE,'||
'CREATED_BY, '||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_DATE,'||
'LAST_UPDATE_LOGIN, '||
'OBJECT_VERSION_NUMBER) '||
' VALUES ('||
':1,'||
':2,'||
':3,'||
':4,'||
':5,'||
':6,'||
':7,'||
':8,'||
':9,'||
':10,'||
':11,'||
p_user_id||','||
p_user_id||','||
':12,'||
l_last_update_login ||','||1||')';
execute immediate l_insert_stmt
using p_version_id,
l_parent_depth_num, p_dest_member_id, p_dest_value_set_id,
l_child_depth_num, p_source_member_id, p_source_value_set_id,
'Y', l_display_order_num, l_weighting_pct,
sysdate, sysdate;
execute immediate l_insert_stmt
using p_version_id,
l_parent_depth_num_tbl(i),
l_parent_id_tbl(i),
l_parent_value_set_id_tbl(i),
l_child_depth_num_tbl(i),
l_child_id_tbl(i),
l_child_value_set_id_tbl(i),
'Y',
l_display_order_num_tbl(i),
l_weighting_pct_tbl(i),
sysdate,
sysdate;
l_select_depth_stmt VARCHAR2(1000);
l_select_depth_stmt := 'SELECT parent_depth_num, child_depth_num, ' ||
'max(display_order_num) ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND parent_id = :2 ' ||
'AND single_depth_flag = ''Y'' ' ||
'GROUP BY child_depth_num, parent_depth_num ' ||
'ORDER BY child_depth_num DESC, ' ||
'parent_depth_num DESC';
OPEN l_dpth_csr for l_select_depth_stmt
USING p_version_id, p_parent_member_id;
l_select_depth_stmt := 'SELECT child_depth_num ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND child_id = :2 ' ||
'AND NOT (parent_id = child_id ' ||
'AND parent_depth_num = child_depth_num) ' ||
'AND single_depth_flag = ''Y''';
OPEN l_leaf_csr for l_select_depth_stmt
USING p_version_id, p_parent_member_id;
l_select_depth_stmt := 'SELECT parent_depth_num, child_depth_num, ' ||
'max(display_order_num) ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND parent_id = :2 ' ||
'AND parent_value_set_id = :3 ' ||
'AND single_depth_flag = ''Y'' ' ||
'GROUP BY child_depth_num, parent_depth_num ' ||
'ORDER BY child_depth_num DESC, ' ||
'parent_depth_num DESC';
OPEN l_dpth_csr for l_select_depth_stmt
USING p_version_id, p_parent_member_id, p_parent_value_set_id;
l_select_depth_stmt := 'SELECT child_depth_num ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND child_id = :2 ' ||
'AND child_value_set_id = :3 ' ||
'AND NOT(parent_id = child_id ' ||
'AND parent_value_set_id = child_value_set_id ' ||
'AND parent_depth_num = child_depth_num) ' ||
'AND single_depth_flag = ''Y''';
OPEN l_leaf_csr for l_select_depth_stmt
USING p_version_id, p_parent_member_id, p_parent_value_set_id;
insert_root_node(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_vs_required_flag => p_value_set_required_flag,
p_hier_table_name => p_hier_table_name,
p_hier_obj_def_id => p_version_id,
p_parent_depth_num => l_parent_depth_num,
p_parent_id => p_parent_member_id,
p_parent_value_set_id => p_parent_value_set_id,
p_child_depth_num => l_child_depth_num,
p_child_id => p_child_members(i).member_id,
p_child_value_set_id => p_child_members(i).value_set_id,
p_single_depth_flag => 'Y',
p_display_order_num => l_display_order_num,
p_weighting_pct => NULL);
l_select_stmt VARCHAR2(1000);
l_select_stmt := 'SELECT max(display_order_num) ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND parent_id = child_id ' ||
'AND parent_depth_num = child_depth_num ' ||
'AND child_depth_num = 1 ' ||
'AND single_depth_flag = ''Y''';
OPEN l_dhm_csr for l_select_stmt
USING p_version_id;
l_select_stmt := 'SELECT max(display_order_num) ' ||
'FROM ' || p_hier_table_name || ' ' ||
'WHERE hierarchy_obj_def_id = :1 ' ||
'AND parent_id = child_id ' ||
'AND parent_value_set_id = child_value_set_id ' ||
'AND parent_depth_num = child_depth_num ' ||
'AND child_depth_num = 1 ' ||
'AND single_depth_flag = ''Y''';
OPEN l_dhm_csr for l_select_stmt
USING p_version_id;
insert_root_node(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_vs_required_flag => p_value_set_required_flag,
p_hier_table_name => p_hier_table_name,
p_hier_obj_def_id => p_version_id,
p_parent_depth_num => 1,
p_parent_id => p_child_members(i).member_id,
p_parent_value_set_id => p_child_members(i).value_set_id,
p_child_depth_num => 1,
p_child_id => p_child_members(i).member_id,
p_child_value_set_id => p_child_members(i).value_set_id,
p_single_depth_flag => 'Y',
p_display_order_num => l_display_order_num,
p_weighting_pct => NULL);
procedure Hier_Member_Sequence_Update (
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
p_return_status OUT NOCOPY VARCHAR2 ,
p_msg_count OUT NOCOPY NUMBER ,
p_msg_data OUT NOCOPY VARCHAR2 ,
--
p_new_display_order_num IN NUMBER ,
p_hierarchy_table_name IN VARCHAR2 ,
p_hierarchy_obj_def_id IN NUMBER ,
p_parent_id IN NUMBER ,
p_parent_value_set_id IN NUMBER ,
p_child_id IN NUMBER ,
p_child_value_set_id IN NUMBER
) IS
begin
--
SAVEPOINT Update_Row_Pvt ;
execute immediate 'update ' || p_hierarchy_table_name ||
' SET DISPLAY_ORDER_NUM = ' || p_new_display_order_num ||
' where hierarchy_obj_def_id = ' || p_hierarchy_obj_def_id || ' AND ' ||
' parent_id = ' || p_parent_id || ' AND ' ||
' child_id = ' || p_child_id;
execute immediate 'update ' || p_hierarchy_table_name ||
' SET DISPLAY_ORDER_NUM = ' || p_new_display_order_num ||
' where hierarchy_obj_def_id = ' || p_hierarchy_obj_def_id || ' AND ' ||
' parent_id = ' || p_parent_id || ' AND ' ||
' parent_value_set_id = ' || p_parent_value_set_id || ' AND ' ||
' child_value_set_id = ' || p_child_value_set_id || ' AND ' ||
' child_id = ' || p_child_id;
ROLLBACK TO Update_Row_Pvt ;
ROLLBACK TO Update_Row_Pvt ;
ROLLBACK TO Update_Row_Pvt ;
END Hier_Member_Sequence_Update;
SELECT a.relative_dimension_group_seq
FROM fem_hier_dimension_grps a
WHERE a.hierarchy_obj_id = p_hierarchy_obj_id AND
a.dimension_group_id = p_dimension_group_id AND
NOT EXISTS
(
SELECT b.dimension_group_id,
b.hierarchy_obj_id,
b.relative_dimension_group_seq
FROM fem_hier_dimension_grps b
WHERE b.hierarchy_obj_id = a.hierarchy_obj_id AND
b.relative_dimension_group_seq >
a.relative_dimension_group_seq
)
)
LOOP
l_is_lowest_group := 'Y';
SELECT a.relative_dimension_group_seq
FROM fem_hier_dimension_grps a
WHERE a.hierarchy_obj_id = p_hierarchy_obj_id AND
a.dimension_group_id = p_dimension_group_id AND
NOT EXISTS
(
SELECT b.dimension_group_id,
b.hierarchy_obj_id,
b.relative_dimension_group_seq
FROM fem_hier_dimension_grps b
WHERE b.hierarchy_obj_id = a.hierarchy_obj_id AND
b.relative_dimension_group_seq <
a.relative_dimension_group_seq
)
)
LOOP
l_is_highest_group := 'Y';
SELECT MEMBER_COL,
MEMBER_VL_OBJECT_NAME,
HIERARCHY_TABLE_NAME,
PERSONAL_HIERARCHY_TABLE_NAME,
VALUE_SET_REQUIRED_FLAG
INTO l_member_col,
l_member_vl_object_name,
l_hierarchy_table_name,
l_per_hierarchy_table_name,
l_value_set_required_flag
FROM FEM_XDIM_DIMENSIONS_VL
WHERE DIMENSION_ID = p_dimension_id;
l_stmt := 'SELECT ''Y'' FROM DUAL WHERE EXISTS ( ' ||
'SELECT H.CHILD_ID ';
' SELECT HVS.VALUE_SET_ID ' ||
' FROM FEM_HIER_VALUE_SETS HVS ' ||
' WHERE PARENT_VALUE_SET_ID = HVS.VALUE_SET_ID AND ' ||
' HVS.HIERARCHY_OBJ_ID = :2 ' ||
' ) AND ' ||
' H.CHILD_ID = DIM.' || l_member_col || ' AND ' ||
' H.CHILD_VALUE_SET_ID = DIM.VALUE_SET_ID ';
l_stmt := 'SELECT ''Y'' FROM DUAL WHERE EXISTS ( ' ||
'SELECT H.CHILD_ID ';
l_stmt := 'SELECT ''Y'' FROM DUAL WHERE EXISTS ( ' ||
'SELECT H.CHILD_ID ';
l_stmt := 'SELECT ''Y'' FROM DUAL WHERE EXISTS ( ' ||
'SELECT H.CHILD_ID ';
l_stmt := 'SELECT ''Y'' FROM DUAL WHERE EXISTS ( ' ||
'SELECT CO.COST_OBJECT_ID ';
' EXISTS (SELECT 1 ' ||
' FROM '||l_hierarchy_table_name||' H2, '||
' FEM_OBJECT_DEFINITION_VL OD, '||
' FEM_HIER_VALUE_SETS VS '||
' WHERE (H2.PARENT_ID = CO.COST_OBJECT_ID) AND '||
' H2.HIERARCHY_OBJ_ID = OD.OBJECT_ID AND '||
' OD.EFFECTIVE_START_DATE BETWEEN H2.EFFECTIVE_START_DATE AND H2.EFFECTIVE_END_DATE '||
' AND OD.OBJECT_DEFINITION_ID = :2 AND '||
' H2.HIERARCHY_OBJ_ID = :3 AND '||
' CO.LOCAL_VS_COMBO_ID = VS.VALUE_SET_ID AND '||
' H2.HIERARCHY_OBJ_ID = VS.HIERARCHY_OBJ_ID))';
SELECT parent_id
FROM (SELECT parent_id,
child_id
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = c_hierarchy_id)
START WITH child_id = c_child_id
CONNECT BY PRIOR parent_id = child_id;
SELECT parent_id
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = c_hierarchy_id
START WITH child_id = c_child_id
CONNECT BY PRIOR parent_id = child_id ;
SELECT cost_object_display_code
FROM fem_cost_objects
WHERE cost_object_id = c_cost_object_id;
SELECT effective_start_date,
effective_end_date
FROM fem_object_definition_b
WHERE object_definition_id = c_version_id;
SELECT COUNT(*)
FROM fem_object_definition_b
WHERE object_id = c_hierarchy_id;
SELECT 'Y' FROM dual
WHERE EXISTS( SELECT 1
FROM fem_object_definition_b a,
fem_object_definition_b b
WHERE a.object_id = b.object_id
AND a.object_id = c_hierarchy_id
AND b.object_definition_id = c_version_id
AND a.effective_start_date > b.effective_end_date);
( SELECT NVL(MAX(child_sequence_num),0) child_sequence_num
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = p_parent_id
AND child_id = p_child_id)
LOOP
l_child_sequence_num := l_max_child_seq_num_rec.child_sequence_num + 1;
SELECT fem_cost_objects_hier_s.NEXTVAL
INTO l_new_relationship_id
FROM dual;
INSERT INTO fem_cost_objects_hier
(relationship_id,
effective_start_date,
hierarchy_obj_id,
parent_id,
child_id,
child_sequence_num,
display_order_num,
effective_end_date,
bom_reference,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(l_new_relationship_id,
l_effective_start_date,
p_hierarchy_id,
p_parent_id,
p_child_id,
l_child_sequence_num,
p_display_order_num,
l_effective_end_date,
p_bom_reference,
g_current_date,
g_current_user_id,
g_current_user_id,
g_current_date,
g_current_login_id,
1);
INSERT INTO fem_cost_obj_hier_qty
(relationship_id,
dataset_code,
cal_period_id,
child_qty,
parent_qty,
yield_percentage,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(l_new_relationship_id,
l_dataset_code,
l_cal_period_id,
p_child_qty,
p_parent_qty,
p_yield_pct,
g_current_date,
g_current_user_id,
g_current_user_id,
g_current_date,
g_current_login_id,
1);
FOR x_rec IN (SELECT child_sequence_num, parent_id, child_id
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = p_parent_id
AND child_id = p_child_id
AND p_curr_ver_start_date
BETWEEN effective_start_date AND effective_end_date
ORDER BY child_sequence_num)
LOOP
UPDATE fem_cost_objects_hier
SET child_sequence_num = l_child_sequence_num
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = x_rec.parent_id
AND child_id = x_rec.child_id
AND child_sequence_num = x_rec.child_sequence_num
AND p_curr_ver_start_date
BETWEEN effective_start_date AND effective_end_date;
SELECT 'Y' FROM dual
WHERE EXISTS( SELECT 1
FROM fem_object_definition_b
WHERE object_id = c_hierarchy_id
AND effective_start_date < c_eff_start_date);
SELECT 'Y' FROM dual
WHERE EXISTS (SELECT 1
FROM fem_object_definition_vl a
WHERE object_id = c_hierarchy_id
AND effective_end_date > c_eff_end_date);
SELECT effective_start_date, effective_end_date
FROM fem_object_definition_b
WHERE object_definition_id = c_version_id;
SELECT child_id, bom_reference, child_sequence_num, display_order_num
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = c_hierarchy_id
AND parent_id = c_parent_id;
else
end if
else
if remove_all_children = Y then
else
end if
end if
else
if remove_all_children = Y then
else
end if
if later_version_exists = Y then
if remove_all_children = Y then
else
end if
end if
end if
*******************************************************************/
OPEN l_version_details_csr(c_version_id => p_version_id);
UPDATE fem_cost_objects_hier
SET effective_start_date = l_curr_version_end_date + 1,
last_update_date = g_current_date,
last_updated_by = g_current_user_id,
last_update_login = g_current_login_id,
object_version_number = object_version_number + 1
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = l_parent_id
AND l_curr_version_start_date
BETWEEN effective_start_date AND effective_end_date;
UPDATE fem_cost_objects_hier
SET effective_start_date = l_curr_version_end_date + 1,
last_update_date = g_current_date,
last_updated_by = g_current_user_id,
last_update_login = g_current_login_id,
object_version_number = object_version_number + 1
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = l_parent_id
AND child_id = l_child_id
AND child_sequence_num = l_child_sequence_num
AND l_curr_version_start_date
BETWEEN effective_start_date AND effective_end_date;
DELETE FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = l_parent_id;
DELETE FROM fem_cost_obj_hier_qty
WHERE relationship_id = (SELECT relationship_id
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = l_parent_id);
DELETE FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = l_parent_id
AND child_id = l_child_id
AND child_sequence_num = l_child_sequence_num;
DELETE FROM fem_cost_obj_hier_qty
WHERE relationship_id = (SELECT relationship_id
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = l_parent_id
AND child_id = l_child_id
AND child_sequence_num=l_child_sequence_num);
UPDATE fem_cost_objects_hier
SET effective_end_date = (l_curr_version_start_date - 1),
last_update_date = g_current_date,
last_updated_by = g_current_user_id,
last_update_login = g_current_login_id,
object_version_number = object_version_number + 1
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = l_parent_id;
DELETE FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = l_parent_id
AND child_sequence_num = l_child_sequence_num;
UPDATE fem_cost_objects_hier
SET effective_end_date = (l_curr_version_start_date - 1),
last_update_date = g_current_date,
last_updated_by = g_current_user_id,
last_update_login = g_current_login_id,
object_version_number = object_version_number + 1
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = l_parent_id
AND child_id = l_child_id
AND child_sequence_num = l_child_sequence_num;
DELETE FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = p_hierarchy_id
AND parent_id = l_parent_id
AND child_id = l_child_id
AND child_sequence_num = l_child_sequence_num;
SELECT fem_cost_objects_hier_s.NEXTVAL
INTO l_new_relationship_id
FROM dual;
INSERT INTO fem_cost_objects_hier
(relationship_id,
effective_start_date,
hierarchy_obj_id,
parent_id,
child_id,
child_sequence_num,
display_order_num,
effective_end_date,
bom_reference,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(l_new_relationship_id,
l_curr_version_end_date+1,
p_hierarchy_id,
l_parent_id,
l_all_children_rec.child_id,
l_all_children_rec.child_sequence_num,
l_all_children_rec.display_order_num,
TO_DATE('01/01/2500','mm/dd/yyyy'),
l_all_children_rec.bom_reference,
g_current_date,
g_current_user_id,
g_current_user_id,
g_current_date,
g_current_login_id,
1);
SELECT fem_cost_objects_hier_s.NEXTVAL
INTO l_new_relationship_id
FROM dual;
INSERT INTO fem_cost_objects_hier
(relationship_id,
effective_start_date,
hierarchy_obj_id,
parent_id,
child_id,
child_sequence_num,
display_order_num,
effective_end_date,
bom_reference,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(l_new_relationship_id,
l_curr_version_end_date+1,
p_hierarchy_id,
l_parent_id,
l_child_id,
l_child_sequence_num,
l_display_order_num,
TO_DATE('01/01/2500','mm/dd/yyyy'),
l_bom_reference,
g_current_date,
g_current_user_id,
g_current_user_id,
g_current_date,
g_current_login_id,
1);
| PROCEDURE Update_Relation |
+===========================================================================*/
--
-- The API to update a Relation in a Hierarchy.
-- NOTE: This is currently used for Cost Object Hierarchies.
--
PROCEDURE Update_Relation(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
--
p_hierarchy_id IN NUMBER,
p_version_id IN NUMBER,
p_parent_id IN NUMBER,
p_parent_qty IN NUMBER,
p_child_id IN NUMBER,
p_child_qty IN NUMBER,
p_child_sequence_num IN NUMBER,
p_yield_pct IN NUMBER,
p_bom_reference IN VARCHAR2,
p_display_order_num IN NUMBER,
p_hier_table_name IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Relation';
l_insert_flag VARCHAR2(1) := 'Y';
l_last_update_date DATE := SYSDATE;
l_last_Updated_by NUMBER := FND_GLOBAL.User_Id;
l_last_update_login NUMBER := FND_GLOBAL.Login_Id;
SELECT effective_start_date
FROM fem_object_definition_b
WHERE object_definition_id = c_version_id;
SELECT relationship_id
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = c_hierarchy_id
AND parent_id = c_parent_id
AND child_id = c_child_id
AND child_sequence_num = c_child_sequence_num
AND c_version_start_date BETWEEN effective_start_date
AND effective_end_date;
SAVEPOINT Update_Relation_Pvt;
SELECT 'N' into l_insert_flag from fem_cost_obj_hier_qty
WHERE relationship_id = l_relationship_id and
dataset_code = l_dataset_code and
cal_period_id = l_cal_period_id;
UPDATE fem_cost_objects_hier
SET bom_reference = p_bom_reference
WHERE relationship_id = l_relationship_id;
IF l_insert_flag <> 'N' then
INSERT into fem_cost_obj_hier_qty
(relationship_id,
dataset_code,
cal_period_id,
child_qty,
parent_qty,
yield_percentage,
creation_date,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number)
values(
l_relationship_id,
l_dataset_code,
l_cal_period_id,
p_child_qty,
p_parent_qty,
p_yield_pct,
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
1);
UPDATE fem_cost_obj_hier_qty
SET parent_qty = p_parent_qty,
child_qty = p_child_qty,
yield_percentage = p_yield_pct
WHERE relationship_id = l_relationship_id
-- Bug#5723800 : Begin
AND dataset_code = l_dataset_code
AND cal_period_id = l_cal_period_id;
ROLLBACK TO Update_Relation_Pvt ;
ROLLBACK TO Update_Relation_Pvt ;
ROLLBACK TO Update_Relation_Pvt ;
END Update_Relation;
| PROCEDURE Update_Relations |
+===========================================================================*/
--
-- The API to update multiple Relations in a Hierarchy.
-- NOTE: This is currently used for Cost Object Hierarchies.
--
PROCEDURE Update_Relations(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
--
p_hierarchy_id IN NUMBER,
p_version_id IN NUMBER,
p_hier_table_name IN VARCHAR2,
p_relation_details_tbl IN FEM_DHM_HIER_NODE_TAB_TYP
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Relations';
SAVEPOINT Update_Relations_Pvt;
Update_Relation(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_hierarchy_id => p_hierarchy_id,
p_version_id => p_version_id,
p_child_sequence_num
=> l_relations_tab_type(i).child_sequence_num,
p_parent_id => l_relations_tab_type(i).parent_id,
p_parent_qty => l_relations_tab_type(i).parent_qty,
p_child_id => l_relations_tab_type(i).child_id,
p_child_qty => l_relations_tab_type(i).child_qty,
p_yield_pct => l_relations_tab_type(i).yield_pct,
p_display_order_num
=> l_relations_tab_type(i).display_order_num,
p_bom_reference => l_relations_tab_type(i).bom_reference,
p_hier_table_name => p_hier_table_name);
ROLLBACK TO Update_Relations_Pvt ;
ROLLBACK TO Update_Relations_Pvt ;
ROLLBACK TO Update_Relations_Pvt ;
END Update_Relations;
SELECT relationship_id,
display_order_num,
bom_reference
FROM fem_cost_objects_hier
WHERE hierarchy_obj_id = c_hierarchy_id
AND parent_id = c_parent_id
AND child_id = c_child_id
AND child_sequence_num = c_child_sequence_num;
SELECT dataset_code,
cal_period_id,
child_qty,
parent_qty,
yield_percentage
FROM fem_cost_obj_hier_qty
WHERE relationship_id = c_relationship_id;
SELECT object_definition_id
FROM fem_object_definition_b
WHERE object_id = c_hierarchy_id;
UPDATE fem_hier_definitions
SET flattened_rows_completion_code = 'COMPLETED'
WHERE hierarchy_obj_def_id IN
(SELECT object_definition_id
FROM fem_object_definition_b
WHERE object_id = p_hierarchy_id);
| FUNCTION Can_Update_Hierarchy |
+===========================================================================*/
--
-- The function checks if the hierarchy can be updated by the
-- current user or not. Returns 'E' if it can be updated, 'D' otherwise.
--
FUNCTION Can_Update_Hierarchy
(
p_object_access_code IN VARCHAR2,
p_hier_created_by IN VARCHAR2
)
RETURN VARCHAR2 AS
begin
if p_hier_created_by=g_current_user_id then
return 'E';
end Can_Update_Hierarchy;
FUNCTION is_hier_vs_deleteable
(
p_hierarchy_id IN NUMBER,
p_value_set_id IN NUMBER,
p_hier_table IN VARCHAR2
)
RETURN VARCHAR2 AS
l_access_sql VARCHAR2(300);
l_access_sql := ' SELECT COUNT(*) FROM DUAL WHERE EXISTS ' ||
' (SELECT 1 FROM ' || p_hier_table || ' WHERE ' ||
' HIERARCHY_OBJ_DEF_ID IN (SELECT OBJECT_DEFINITION_ID ' ||
' FROM FEM_OBJECT_DEFINITION_B WHERE OBJECT_ID = :1) ' ||
' AND CHILD_VALUE_SET_ID = :2) ';
end is_hier_vs_deleteable;