[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 29-MAR-07 RFlippo added insert into fem_alloc_br_objects when create
| new default rule
| 12-APR-07 RFlippo fixed issues with Table data components in
| generate_condition_summary
| 26-APR-07 RFlippo added generate_fctr_summary API for creating the
| Factor table summary data for the Factor table Details
| pluggable region
| 10-MAY-07 RFlippo added commit for when p_commit is TRUE on all procedures
| except for gen_cond_summary and gen_fctr_summary;
| it does not try to insert into fem_alloc_br_objects
| when a default definition already exists
| 29-JUN-2007 asadadek bug#6158146. Call API delete_map_rule_content to handle
| deletion of map rule contents minus the helper records
| instead of DeleteObjectDefinition.
| 2-JUL-07 RFlippo Bug#6146396 Set any dimension cols in the Mapping Output list
| = "SAME_AS_SOURCE" if they don't have a default
| assigned in fem_alloc_br_dimensions for the
| default rule
| 7-JUL-07 RFlippo Bug#6179151 Modify so that for Adj rules, the default
| is VALUE
| 6-JUL-07 RFlippo Need to get a unique object name for the snapshot objects.
| To do this, will concatenate the sysdate, include MI:SS
| so that we can have multiple snapshots (Preview, Edit, etc)
| for a given mapping rule. Can even support multiple snapshots
| of the same object type (i.e, Preview) without any conflict;
We always insert a set of rows for the target table
However - if post_to_ledger_flg = 'Y' on fem_alloc_br_formula,
then we insert another set of records for FEM_BALANCES
7/5/2007 Rflippo Bug#6179151 Modify so that for Adj rules, the default
is VALUE
7/10/2007 Rflippo bug#6196776 Only do defaults for enabled_flg='Y'
*************************************************************************/
PROCEDURE set_dim_usage_dflt (p_object_definition_id IN NUMBER) IS
cursor c_tgttab (p_obj_def_id IN VARCHAR2) is
SELECT function_seq, table_name, post_to_ledger_flg, function_cd
FROM fem_alloc_br_formula
WHERE object_definition_id = p_obj_def_id
AND function_cd IN ('CREDIT','DEBIT')
AND enable_flg <> 'N';
SELECT column_name
FROM fem_tab_column_prop
WHERE table_name = p_tgt_table
AND column_property_code = 'MAPPING_UI_OUTPUT'
AND column_name NOT IN (
SELECT alloc_dim_col_name
FROM fem_alloc_br_dimensions
WHERE object_definition_id = p_obj_def_id
AND function_seq = p_func_seq);
SELECT column_name
FROM fem_tab_column_prop
WHERE table_name = 'FEM_BALANCES'
AND column_property_code = 'MAPPING_UI_OUTPUT'
AND column_name NOT IN (
SELECT alloc_dim_col_name
FROM fem_alloc_br_dimensions
WHERE object_definition_id = p_obj_def_id
AND function_seq = p_func_seq);
SELECT object_id
INTO v_object_id
FROM fem_objdef_helper_rules
WHERE helper_obj_def_id = p_object_definition_id
AND helper_object_type_code = 'MAPPING_EDIT_SNAPSHOT';
SELECT map_rule_type_code
INTO v_rule_type_code
FROM fem_alloc_br_objects
WHERE map_rule_object_id = v_object_id;
insert into fem_alloc_br_dimensions (
OBJECT_DEFINITION_ID
,FUNCTION_SEQ
,ALLOC_DIM_COL_NAME
,POST_TO_BALANCES_FLAG
,FUNCTION_CD
,ALLOC_DIM_USAGE_CODE
,DIMENSION_VALUE
,DIMENSION_VALUE_CHAR
,PERCENT_DISTRIBUTION_CODE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER )
values (p_object_definition_id, func_seq.function_seq, dimcol.column_name,
'N', func_seq.function_cd,
v_dflt_usage,null,null,null,c_user_id,sysdate,
c_user_id,sysdate,null,1);
insert into fem_alloc_br_dimensions (
OBJECT_DEFINITION_ID
,FUNCTION_SEQ
,ALLOC_DIM_COL_NAME
,POST_TO_BALANCES_FLAG
,FUNCTION_CD
,ALLOC_DIM_USAGE_CODE
,DIMENSION_VALUE
,DIMENSION_VALUE_CHAR
,PERCENT_DISTRIBUTION_CODE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER )
values (p_object_definition_id, func_seq.function_seq, baldimcol.column_name,
'Y', func_seq.function_cd,
v_dflt_usage,null,null,null,c_user_id,sysdate,
c_user_id,sysdate,null,1);
SELECT to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
INTO v_sysdate
FROM dual;
SELECT C.folder_id, C.local_vs_combo_id, C.object_access_code,
C.object_origin_code, C.object_id, C.description, D.display_name,
D.description
INTO v_folder_id, v_local_vs_combo_id, v_object_access_code,
v_object_origin_code, v_object_name, v_description, v_display_name,
v_objdef_desc
FROM fem_object_catalog_vl C, fem_object_definition_vl D
WHERE D.object_id = C.object_id
AND D.object_definition_id = p_map_rule_obj_def_id
AND C.object_type_code IN (C_RULE_OBJTYPE, C_DFLT_OBJTYPE);
SELECT object_type_code
INTO v_object_type_code
FROM fem_object_types_vl
WHERE object_type_code IN ('MAPPING_EDIT_SNAPSHOT','MAPPING_PREVIEW')
AND object_type_code = p_snapshot_obj_type_code;
/* Need to update the description of the snapshot
to be the same as the source default rule (since
the create_object api doesn't allow us to specify
description */
update fem_object_definition_tl
set description = v_objdef_desc
where object_definition_id = x_snapshot_objdef_id;
PURPOSE: updates the true mapping rule definition with content from the
Edit snapshot
*************************************************************************/
PROCEDURE refresh_maprule_from_snapshot (
p_map_rule_obj_def_id IN NUMBER,
p_api_version IN NUMBER DEFAULT c_api_version,
p_init_msg_list IN VARCHAR2 DEFAULT c_false,
p_commit IN VARCHAR2 DEFAULT c_false,
p_encoded IN VARCHAR2 DEFAULT c_true,
x_snapshot_objdef_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
'fem.plsql.fem_alloc_br_util_pkg.refresh_maprule_from_snapshot';
SELECT distinct sub_object_id, creation_date, last_update_date
FROM fem_alloc_br_formula
WHERE object_definition_id = p_object_definition_id
AND sub_object_id IS NOT NULL;
SELECT 1
INTO v_count
FROM fem_object_catalog_vl C, fem_object_definition_vl D
WHERE D.object_id = C.object_id
AND D.object_definition_id = p_map_rule_obj_def_id
AND C.object_type_code IN (C_RULE_OBJTYPE, C_DFLT_OBJTYPE);
/* delete the content for the true rule */
FEM_BR_MAPPING_RULE_PVT.delete_map_rule_content(p_map_rule_obj_def_id);
/* update the objdef name/description */
SELECT display_name, description, effective_start_date, effective_end_date
INTO v_snap_objdef_name, v_snap_objdef_desc, v_snap_start_date, v_snap_end_date
FROM fem_object_definition_vl
WHERE object_definition_id = v_helper_obj_def_id;
UPDATE fem_object_definition_vl
SET display_name = v_snap_objdef_name, description = v_snap_objdef_desc,
effective_start_date = v_snap_start_date, effective_end_date = v_snap_end_date
WHERE object_definition_id = p_map_rule_obj_def_id;
DELETE FROM fem_object_dependencies
WHERE object_definition_id = p_map_rule_obj_def_id;
INSERT INTO fem_object_dependencies
(OBJECT_DEFINITION_ID
,REQUIRED_OBJECT_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER )
VALUES (p_map_rule_obj_def_id
,subobj.sub_object_id
,c_user_id
,subobj.creation_date
,c_user_id
,subobj.last_update_date
,c_login_id
,c_object_version);
PURPOSE: updates the snapshot definition with content from the
true mapping rule
*************************************************************************/
PROCEDURE refresh_snapshot_from_maprule (
p_map_rule_obj_def_id IN NUMBER,
p_api_version IN NUMBER DEFAULT c_api_version,
p_init_msg_list IN VARCHAR2 DEFAULT c_false,
p_commit IN VARCHAR2 DEFAULT c_false,
p_encoded IN VARCHAR2 DEFAULT c_true,
x_snapshot_objdef_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
'fem.plsql.fem_alloc_br_util_pkg.refresh_snapshot_from_maprule';
SELECT 1
INTO v_count
FROM fem_object_catalog_vl C, fem_object_definition_vl D
WHERE D.object_id = C.object_id
AND D.object_definition_id = p_map_rule_obj_def_id
AND C.object_type_code IN (C_RULE_OBJTYPE, C_DFLT_OBJTYPE);
/* delete the content for the snapshot */
FEM_BR_MAPPING_RULE_PVT.delete_map_rule_content(v_helper_obj_def_id);
/* update the objdef name/description */
SELECT display_name, description, effective_start_date, effective_end_date
INTO v_maprule_objdef_name, v_maprule_objdef_desc,v_maprule_start_date, v_maprule_end_date
FROM fem_object_definition_vl
WHERE object_definition_id = p_map_rule_obj_def_id;
UPDATE fem_object_definition_vl
SET display_name = v_maprule_objdef_name, description = v_maprule_objdef_desc,
effective_start_date = v_maprule_start_date, effective_end_date = v_maprule_end_date
WHERE object_definition_id = v_helper_obj_def_id;
PURPOSE: updates the snapshot definition with content from the
seeded default
HISTORY:
6/26/2007 Rflippo Bug#6146396 Set any dimension cols in the Mapping Output list
= "SAME_AS_SOURCE" if they don't have a default
assigned in fem_alloc_br_dimensions for the
default rule
*************************************************************************/
PROCEDURE refresh_snapshot_from_defaults (
p_map_rule_obj_def_id IN NUMBER,
p_api_version IN NUMBER DEFAULT c_api_version,
p_init_msg_list IN VARCHAR2 DEFAULT c_false,
p_commit IN VARCHAR2 DEFAULT c_false,
p_encoded IN VARCHAR2 DEFAULT c_true,
x_snapshot_objdef_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
'fem.plsql.fem_alloc_br_util_pkg.refresh_snapshot_from_defaults';
SELECT O.map_rule_type_code
INTO v_map_rule_type_code
FROM fem_object_catalog_vl C, fem_object_definition_vl D,
fem_alloc_br_objects O
WHERE D.object_id = C.object_id
AND D.object_definition_id = p_map_rule_obj_def_id
AND C.object_type_code = 'MAPPING_RULE'
AND C.object_id = O.map_rule_object_id;
SELECT min (D.object_definition_id)
INTO v_dflt_obj_def_id
FROM fem_object_definition_b D, fem_alloc_br_objects O,
fem_object_catalog_b C
WHERE C.object_id = D.object_id
AND C.object_type_code = C_DFLT_OBJTYPE
AND C.object_id = O.map_rule_object_id
AND O.map_rule_type_code = v_map_rule_type_code
AND C.local_vs_combo_id = v_global_vs_combo_id;
/* delete the content for the snapshot */
FEM_BR_MAPPING_RULE_PVT.delete_map_rule_content(v_helper_obj_def_id);
SELECT count(*)
INTO v_count
FROM fem_map_rule_types_b
WHERE map_rule_type_code = p_map_rule_type_code;
SELECT min (D.object_definition_id)
INTO x_dflt_objdef_id
FROM fem_object_definition_b D, fem_alloc_br_objects O,
fem_object_catalog_b C
WHERE C.object_id = D.object_id
AND C.object_type_code = C_DFLT_OBJTYPE
AND C.object_id = O.map_rule_object_id
AND O.map_rule_type_code = p_map_rule_type_code
AND C.local_vs_combo_id = v_global_vs_combo_id;
SELECT D1.objdef, C1.object_id
INTO v_seeded_dflt_obj_def_id,v_seeded_dflt_object_id
FROM
(SELECT min (D.object_definition_id) objdef
FROM fem_object_definition_b D, fem_alloc_br_objects O,
fem_object_catalog_b C
WHERE C.object_id = D.object_id
AND C.object_type_code = C_DFLT_OBJTYPE
AND C.object_id = O.map_rule_object_id
AND O.map_rule_type_code = p_map_rule_type_code
AND C.local_vs_combo_id IS NULL) D1,
fem_object_definition_b C1
WHERE D1.objdef = C1.object_definition_id;
SELECT object_name, description, folder_id
INTO v_object_name, v_description, v_dflt_folder_id
FROM fem_object_catalog_vl C
WHERE object_id = v_seeded_dflt_object_id;
SELECT display_name, description
INTO v_objdef_name, v_objdef_desc
FROM fem_object_definition_vl
WHERE object_definition_id = v_seeded_dflt_obj_def_id;
/* Update the default rule description (for all languages),
since we couldn't provide it when we created the default rule object */
UPDATE fem_object_definition_tl
SET description = v_objdef_desc
WHERE object_definition_id = x_dflt_objdef_id;
/* Insert the rule type information into fem_alloc_br_objects */
INSERT INTO fem_alloc_br_objects (
MAP_RULE_OBJECT_ID
,MAP_RULE_TYPE_CODE
,OBJECT_VERSION_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
VALUES (v_new_dflt_object_id
, p_map_rule_type_code
,1
,sysdate
,C_USER_ID
,C_USER_ID
,sysdate
,C_LOGIN_ID);
SELECT cost_contribution_flag, accumulate_flag
INTO v_br_cost_flag,v_br_acc_flag
FROM fem_alloc_business_rule
WHERE object_definition_id = p_map_rule_obj_def_id;
SELECT cost_contribution_flag, accumulate_flag
INTO v_sbr_cost_flag,v_sbr_acc_flag
FROM fem_alloc_business_rule
WHERE object_definition_id = v_helper_obj_def_id;
v_sql := 'SELECT function_seq, function_cd, sub_object_id, value, table_name, '||
'column_name, math_operator_cd,formula_macro_cd,force_to_100_flg,'||
'enable_flg, post_to_ledger_flg, open_paren, close_paren,'||
'apply_to_debit_code'||
' FROM fem_alloc_br_formula'||
' WHERE object_definition_id = :1'||
' ORDER BY function_seq';
t_br_function_seq.DELETE;
t_br_function_cd.DELETE;
t_sbr_function_seq.DELETE;
t_sbr_function_cd.DELETE;
v_sql := 'SELECT function_seq, alloc_dim_col_name, post_to_balances_flag, '||
'function_cd, alloc_dim_usage_code, dimension_value, dimension_value_char,'||
'percent_distribution_code'||
' FROM fem_alloc_br_dimensions'||
' WHERE object_definition_id = :1'||
' ORDER BY function_seq, alloc_dim_col_name, post_to_balances_flag';
SELECT object_definition_id
FROM fem_object_definition_b
WHERE object_id = p_cond_object_id
ORDER BY effective_end_date;
SELECT C.cond_component_obj_id, O.object_definition_id cond_comp_obj_def_id,
C.data_dim_flag,
OB.local_vs_combo_id global_vs_combo_id
FROM fem_cond_components C,
fem_object_definition_b O,
fem_object_catalog_b OB
WHERE C.cond_component_obj_id = O.object_id
AND C.condition_obj_def_id = p_object_definition_id
AND O.object_id = OB.object_id;
SELECT D.dim_comp_type, D.dim_id, D.dim_column, D.value,
T.dimension_name
FROM fem_cond_dim_components D,
fem_dimensions_vl T
WHERE D.cond_dim_cmp_obj_def_id = p_cond_comp_obj_def_id
AND D.dim_id = T.dimension_id;
SELECT D.hierarchy_obj_id, O.object_name
FROM (SELECT DISTINCT hierarchy_obj_id
FROM fem_cond_dim_cmp_dtl D
WHERE cond_dim_cmp_obj_def_id = p_cond_obj_def_id) D,
fem_object_catalog_vl O
WHERE D.hierarchy_obj_id = O.object_id;
SELECT A.attribute_id, A.attribute_name, A.attribute_dimension_id,
A.attribute_data_type_code, D.dim_attr_value
FROM fem_dim_attributes_vl A, fem_cond_dim_cmp_dtl D, fem_cond_dim_components C
WHERE A.dimension_id = p_dimension_id
AND A.dimension_id = C.dim_id
AND D.cond_dim_cmp_obj_def_id = p_cond_obj_def_id
AND D.cond_dim_cmp_obj_def_id = C.cond_dim_cmp_obj_def_id
AND A.attribute_varchar_label = D.dim_attr_varchar_label;
SELECT S.step_sequence, S.table_name, S.step_type, S.column_name, S.operator,
D.value, D.max_range_value,
T.fem_data_type_code, T.dimension_id, T.display_name col_display_name, TA.display_name table_display_name
FROM fem_cond_data_cmp_steps S, fem_cond_data_cmp_st_dtl D,
fem_tab_columns_vl T, fem_tables_vl TA
WHERE S.step_sequence = D.step_sequence
AND S.cond_data_cmp_obj_def_id = D.cond_data_cmp_obj_def_id
AND S.table_name= D.table_name
AND S.cond_data_cmp_obj_def_id = p_cond_comp_obj_def_id
AND S.table_name = T.table_name
AND S.column_name = T.column_name
AND T.table_name = TA.table_name;
DELETE FROM fem_alloc_br_cond_sum_gt
WHERE condition_obj_def_id IN (SELECT object_definition_id
FROM fem_object_definition_b
WHERE object_id = p_condition_object_id);
SELECT meaning
INTO v_any_table
FROM fem_lookups
WHERE lookup_type = 'FEM_CONDITION_TABLE_MACROS'
AND lookup_code = 'ANY';
SELECT meaning
INTO v_cond_type
FROM fem_lookups
WHERE lookup_type = 'FEM_CONDITION_DIM_COMP_TYPES'
AND lookup_code = 'HIERARCHY';
INSERT INTO FEM_ALLOC_BR_COND_SUM_GT (
CONDITION_OBJ_DEF_ID,
TABLE_DISPLAY_NAME,
COLUMN_DISPLAY_NAME,
CONDITION_TYPE,
HIER_ATTR_DISPLAY_NAME,
CONDITION_VALUE )
VALUES (objdef.object_definition_id,
v_table_name,
v_cond_column,
v_cond_type,
v_hier_attr,
v_cond_value);
SELECT meaning
INTO v_cond_type
FROM fem_lookups
WHERE lookup_type = 'FEM_CONDITION_DIM_COMP_TYPES'
AND lookup_code = 'ATTRIBUTE';
SELECT value_set_required_flag
INTO v_vsr_flag
FROM fem_xdim_dimensions
WHERE dimension_id = attr.attribute_dimension_id;
INSERT INTO FEM_ALLOC_BR_COND_SUM_GT (
CONDITION_OBJ_DEF_ID,
TABLE_DISPLAY_NAME,
COLUMN_DISPLAY_NAME,
CONDITION_TYPE,
HIER_ATTR_DISPLAY_NAME,
CONDITION_VALUE,
COND_VALUE_ATTR_DATA_TYPE )
VALUES (objdef.object_definition_id,
v_table_name,
v_cond_column,
v_cond_type,
v_hier_attr,
v_cond_value,
attr.attribute_data_type_code);
SELECT meaning
INTO v_cond_type
FROM fem_lookups
WHERE lookup_type = 'FEM_CONDITION_DIM_COMP_TYPES'
AND lookup_code = 'VALUE';
SELECT value_set_required_flag
INTO v_vsr_flag
FROM fem_xdim_dimensions
WHERE dimension_id = dimcomp.dim_id;
INSERT INTO FEM_ALLOC_BR_COND_SUM_GT (
CONDITION_OBJ_DEF_ID,
TABLE_DISPLAY_NAME,
COLUMN_DISPLAY_NAME,
CONDITION_TYPE,
HIER_ATTR_DISPLAY_NAME,
CONDITION_VALUE )
VALUES (objdef.object_definition_id,
v_table_name,
v_cond_column,
v_cond_type,
v_hier_attr,
v_cond_value);
SELECT meaning
INTO v_operator_name
FROM fem_lookups
WHERE lookup_type = 'FEM_CONDITION_OPERATOR'
AND lookup_code = step.operator;
SELECT meaning
INTO v_cond_type
FROM fem_lookups
WHERE lookup_type = 'FEM_CONDITION_DATA_STEP_TYPE'
AND lookup_code = 'DATA_SPECIFIC';
SELECT value_set_required_flag
INTO v_vsr_flag
FROM fem_xdim_dimensions
WHERE dimension_id = step.dimension_id;
SELECT meaning
INTO v_cond_type
FROM fem_lookups
WHERE lookup_type = 'FEM_CONDITION_DATA_STEP_TYPE'
AND lookup_code = 'DATA_RANGE';
SELECT meaning
INTO v_and_name
FROM fem_lookups
WHERE lookup_type = 'FEM_CONDITION_OPERATOR_AND'
AND lookup_code = 'AND';
SELECT value_set_required_flag
INTO v_vsr_flag
FROM fem_xdim_dimensions
WHERE dimension_id = step.dimension_id;
SELECT meaning
INTO v_cond_type
FROM fem_lookups
WHERE lookup_type = 'FEM_CONDITION_DATA_STEP_TYPE'
AND lookup_code = 'ANOTHER_COL';
SELECT display_name
INTO v_table_name
FROM fem_tables_vl
WHERE table_name = step.table_name;
SELECT display_name
INTO v_cond_column
FROM fem_tab_columns_vl
WHERE table_name = step.table_name
AND column_name = step.column_name;
INSERT INTO FEM_ALLOC_BR_COND_SUM_GT (
CONDITION_OBJ_DEF_ID,
TABLE_DISPLAY_NAME,
COLUMN_DISPLAY_NAME,
CONDITION_TYPE,
HIER_ATTR_DISPLAY_NAME,
CONDITION_VALUE )
VALUES (objdef.object_definition_id,
v_table_name,
v_cond_column,
v_cond_type,
v_hier_attr,
v_cond_value);
SELECT O.map_rule_type_code, C.folder_id
INTO v_map_rule_type_code, v_folder_id
FROM fem_object_catalog_b C, fem_alloc_br_objects O
WHERE C.object_id = p_map_rule_obj_id
AND C.object_type_code = 'MAPPING_RULE'
AND C.object_id = O.map_rule_object_id;
SELECT *
INTO v_fem_obj_def_row
FROM fem_object_definition_vl
WHERE object_definition_id = v_dflt_obj_def_id;
UPDATE fem_object_definition_vl
SET description = v_fem_obj_def_row.description
WHERE object_definition_id = x_map_rule_objdef_id;
SELECT min (D.object_definition_id)
INTO v_dflt_objdef_id
FROM fem_object_definition_b D, fem_alloc_br_objects O,
fem_object_catalog_b C
WHERE C.object_id = D.object_id
AND C.object_type_code = C_DFLT_OBJTYPE
AND C.object_id = O.map_rule_object_id
AND O.map_rule_type_code = p_map_rule_type_code
AND C.local_vs_combo_id = v_global_vs_combo_id;
SELECT object_definition_id
FROM fem_object_definition_b
WHERE object_id = p_fctr_object_id
ORDER BY effective_end_date;
SELECT D.dimension_name, F.hier_object_id, F.hier_group_id, F.hier_relation_code
FROM fem_dimensions_vl D, fem_factor_table_dims F
WHERE F.dimension_id = D.dimension_id
AND F.object_definition_id = p_object_definition_id
AND F.dim_usage_code = 'MATCH';
DELETE FROM fem_alloc_br_fctr_sum_gt
WHERE factor_obj_def_id IN (SELECT object_definition_id
FROM fem_object_definition_b
WHERE object_id = p_fctr_object_id);
SELECT object_name
INTO v_hier_name
FROM fem_object_catalog_vl
WHERE object_id = dim.hier_object_id;
SELECT dimension_group_name
INTO v_group_name
FROM fem_dimension_grps_vl
WHERE dimension_group_id = dim.hier_group_id;
SELECT meaning
INTO v_hier_relation_desc
FROM fem_lookups
WHERE lookup_type = 'FEM_COND_HIER_RELATIONS'
AND lookup_code = dim.hier_relation_code;
INSERT INTO FEM_ALLOC_BR_FCTR_SUM_GT (
FACTOR_OBJ_DEF_ID,
MATCHING_DIMENSION_NAME,
HIERARCHY_NAME,
DIMENSION_GROUP_NAME,
HIER_RELATION_DESC )
VALUES (objdef.object_definition_id,
dim.dimension_name,
v_hier_name,
v_group_name,
v_hier_relation_desc );
select map_rule_type_code into v_map_rule_type_code
from fem_alloc_br_objects abo,fem_object_definition_b defs
where defs.object_definition_id = p_object_definition_id
and abo.map_rule_object_id = defs.object_id ;
select count(*) into v_stat_count
from fem_alloc_br_formula
where object_definition_id = p_object_definition_id
and function_cd = 'TABLE_ACCESS';
select decode(NVL(cost_contribution_flag,'N'),'Y',1,0)
into v_trace_contrib_flag
from fem_alloc_business_rule
where object_definition_id = p_object_definition_id;
SELECT count(*) INTO v_row_count
FROM fem_vt_obj_def_attribs
WHERE object_definition_id = p_object_definition_id;
INSERT INTO fem_vt_obj_def_attribs(object_definition_id,source_enabled_flg,
driver_enabled_flg,trace_contribution_enabled_flg,
created_by,creation_date,last_updated_by,
last_update_date,last_update_login,object_version_number)
VALUES (p_object_definition_id,v_src_flag,
v_drv_flag,v_trace_contrib_flag,
c_user_id,sysdate,c_user_id,sysdate,c_login_id,0);
UPDATE fem_vt_obj_def_attribs
SET source_enabled_flg = v_src_flag,driver_enabled_flg = v_drv_flag,
trace_contribution_enabled_flg = v_trace_contrib_flag ,
Last_update_date = sysdate,last_update_login =c_login_id,
last_updated_by=c_user_id,object_version_number = (object_version_number+1)
WHERE object_definition_id = p_object_definition_id;