[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
CASE
WHEN D.APPLICATION_COLUMN_NAME1 =
A.APPLICATION_COLUMN_NAME THEN 1
WHEN D.APPLICATION_COLUMN_NAME2 =
A.APPLICATION_COLUMN_NAME THEN 2
WHEN D.APPLICATION_COLUMN_NAME3 =
A.APPLICATION_COLUMN_NAME THEN 3
WHEN D.APPLICATION_COLUMN_NAME4 =
A.APPLICATION_COLUMN_NAME THEN 4
WHEN D.APPLICATION_COLUMN_NAME5 =
A.APPLICATION_COLUMN_NAME THEN 5
ELSE NULL
END SEGMENT_NUMBER
FROM
FEM_INTG_DIM_RULES R,
FEM_OBJECT_DEFINITION_B OD,
FEM_INTG_DIM_RULE_DEFS D,
FND_SEGMENT_ATTRIBUTE_VALUES A
WHERE
R.CHART_OF_ACCOUNTS_ID = pv_coa_id AND
R.DIMENSION_ID = c_dimension_id AND
OD.OBJECT_ID = R.DIM_RULE_OBJ_ID AND
D.DIM_RULE_OBJ_DEF_ID = OD.OBJECT_DEFINITION_ID AND
A.APPLICATION_ID = 101 AND
A.ID_FLEX_CODE = 'GL#' AND
A.ID_FLEX_NUM = pv_coa_id AND
A.SEGMENT_ATTRIBUTE_TYPE = c_qualifier AND
A.ATTRIBUTE_VALUE = 'Y';
SELECT R.DIMENSION_ID
,DECODE(R.DIMENSION_ID,
0, 'INTERCOMPANY',
B.DIMENSION_VARCHAR_LABEL)
,R.CHART_OF_ACCOUNTS_ID
,F.ID_FLEX_STRUCTURE_CODE
INTO pv_dim_id
,pv_dim_varchar_label
,pv_coa_id
,pv_coa_name
FROM FEM_INTG_DIM_RULES R
,FND_ID_FLEX_STRUCTURES F
,FEM_DIMENSIONS_B B
WHERE R.DIM_RULE_OBJ_ID = pv_dim_rule_obj_id
AND F.APPLICATION_ID = 101
AND F.ID_FLEX_CODE = 'GL#'
AND F.ID_FLEX_NUM = R.CHART_OF_ACCOUNTS_ID
AND DECODE(R.DIMENSION_ID,
0, B.DIMENSION_VARCHAR_LABEL,
B.DIMENSION_ID) =
DECODE(R.DIMENSION_ID,
0, 'COMPANY_COST_CENTER_ORG',
R.DIMENSION_ID);
SELECT SEGMENT_COUNT
, DIM_MAPPING_OPTION_CODE
, DEFAULT_MEMBER_ID
, DEFAULT_MEMBER_VALUE_SET_ID
, NVL(MAX_CCID_PROCESSED,-1)
, NVL(MAX_FLEX_VALUE_ID_PROCESSED,-1)
, DECODE(DIM_MAPPING_OPTION_CODE, 'SINGLEVAL'
,DEFAULT_MEMBER_VALUE_SET_ID,
NVL(FEM_VALUE_SET_ID,-1))
, APPLICATION_COLUMN_NAME1
, NVL(APPLICATION_COLUMN_NAME2,'-99')
, NVL(APPLICATION_COLUMN_NAME3,'-99')
, NVL(APPLICATION_COLUMN_NAME4,'-99')
, NVL(APPLICATION_COLUMN_NAME5,'-99')
INTO pv_segment_count
, pv_dim_mapping_option_code
, pv_default_member_id
, pv_default_member_vs_id
, pv_max_ccid_processed
, pv_max_flex_value_id_processed
, pv_fem_vs_id
, pv_mapped_segs(1).application_column_name
, pv_mapped_segs(2).application_column_name
, pv_mapped_segs(3).application_column_name
, pv_mapped_segs(4).application_column_name
, pv_mapped_segs(5).application_column_name
FROM FEM_INTG_DIM_RULE_DEFS
WHERE DIM_RULE_OBJ_DEF_ID = pv_dim_rule_obj_def_id;
SELECT NVL(S.FLEX_VALUE_SET_ID,-99),
DECODE(V.VALIDATION_TYPE, 'F', 'Y', 'N'),
T.APPLICATION_TABLE_NAME,
T.ID_COLUMN_NAME,
T.VALUE_COLUMN_NAME,
T.COMPILED_ATTRIBUTE_COLUMN_NAME,
T.MEANING_COLUMN_NAME,
T.ADDITIONAL_WHERE_CLAUSE,
DECODE(V.VALIDATION_TYPE,'D','Y','N'),
DECODE(V.VALIDATION_TYPE,'D',V.PARENT_FLEX_VALUE_SET_ID,NULL)
INTO pv_mapped_segs(i).vs_id,
pv_mapped_segs(i).table_validated_flag,
pv_mapped_segs(i).table_name,
pv_mapped_segs(i).id_col_name,
pv_mapped_segs(i).val_col_name,
pv_mapped_segs(i).compiled_attr_col_name,
pv_mapped_segs(i).meaning_col_name,
pv_mapped_segs(i).where_clause,
pv_mapped_segs(i).dependent_value_set_flag,
pv_mapped_segs(i).dependent_vs_id
FROM FND_ID_FLEX_SEGMENTS S,
FND_FLEX_VALUE_SETS V,
FND_FLEX_VALIDATION_TABLES T
WHERE S.APPLICATION_ID = 101
AND S.ID_FLEX_CODE = 'GL#'
AND S.ID_FLEX_NUM = pv_coa_id
AND S.APPLICATION_COLUMN_NAME =
pv_mapped_segs(i).application_column_name
AND V.FLEX_VALUE_SET_ID = NVL(S.FLEX_VALUE_SET_ID, -99)
AND T.FLEX_VALUE_SET_ID (+) = V.FLEX_VALUE_SET_ID;
SELECT DIMENSION_ID
INTO pv_cctr_org_dim_id
FROM FEM_DIMENSIONS_B
WHERE DIMENSION_VARCHAR_LABEL = 'COMPANY_COST_CENTER_ORG';
SELECT DIMENSION_ID
INTO pv_fin_element_dim_id
FROM FEM_DIMENSIONS_B
WHERE DIMENSION_VARCHAR_LABEL = 'FINANCIAL_ELEMENT';
SELECT value_set_id
INTO pv_fin_element_vs_id
FROM FEM_FIN_ELEMS_B
WHERE ROWNUM = 1;
SELECT a.attribute_id
,v.version_id
INTO pv_ext_acct_type_attr_id
,pv_ext_acct_attr_version_id
FROM fem_dim_attributes_b a,
fem_dim_attr_versions_b v
WHERE a.dimension_id = 2
AND a.attribute_varchar_label='EXTENDED_ACCOUNT_TYPE'
AND v.attribute_id = a.attribute_id
AND v.default_version_flag = 'Y';
SELECT SOURCE_SYSTEM_CODE
INTO pv_source_system_code_id
FROM FEM_SOURCE_SYSTEMS_B
WHERE SOURCE_SYSTEM_DISPLAY_CODE = 'OGL';
SELECT DIMENSION_ID
INTO pv_com_dim_id
FROM FEM_DIMENSIONS_B
WHERE DIMENSION_VARCHAR_LABEL = 'COMPANY';
SELECT DIMENSION_ID
INTO pv_cc_dim_id
FROM FEM_DIMENSIONS_B
WHERE DIMENSION_VARCHAR_LABEL = 'COST_CENTER';
SELECT MEMBER_B_TABLE_NAME
,MEMBER_TL_TABLE_NAME
,MEMBER_COL
,MEMBER_NAME_COL
,MEMBER_VL_OBJECT_NAME
,MEMBER_DESCRIPTION_COL
,MEMBER_DISPLAY_CODE_COL
,ATTRIBUTE_TABLE_NAME
INTO pv_member_b_table_name
,pv_member_tl_table_name
,pv_member_col
,pv_member_name_col
,pv_member_vl_object_name
,pv_member_desc_col
,pv_member_display_code_col
,pv_attr_table_name
FROM FEM_XDIM_DIMENSIONS
WHERE DIMENSION_ID = pv_dim_id;
SELECT MEMBER_B_TABLE_NAME,
MEMBER_TL_TABLE_NAME,
MEMBER_VL_OBJECT_NAME,
MEMBER_COL,
MEMBER_DISPLAY_CODE_COL,
MEMBER_NAME_COL,
ATTRIBUTE_TABLE_NAME
INTO pv_member_b_table_name,
pv_member_tl_table_name,
pv_member_vl_object_name,
pv_cctr_org_member_col,
pv_member_display_code_col,
pv_member_name_col,
pv_attr_table_name
FROM FEM_XDIM_DIMENSIONS
WHERE DIMENSION_ID = pv_cctr_org_dim_id;
v_map_records_inserted_count NUMBER;
pv_progress := 'Before select of object id and folder id';
SELECT O.OBJECT_ID, O.FOLDER_ID
INTO pv_dim_rule_obj_id, pv_folder_id
FROM FEM_OBJECT_DEFINITION_B B,
FEM_OBJECT_CATALOG_B O
WHERE B.OBJECT_DEFINITION_ID = pv_dim_rule_obj_def_id
AND O.OBJECT_ID = B.OBJECT_ID
AND O.OBJECT_TYPE_CODE = 'OGL_INTG_DIM_RULE';
pv_progress := 'Before select of GVSC ID';
SELECT COA.GLOBAL_VS_COMBO_ID
INTO pv_gvsc_id
FROM FEM_OBJECT_DEFINITION_B DEF,
FEM_INTG_COA_GVSC_MAP COA
WHERE DEF.OBJECT_DEFINITION_ID = p_dim_rule_obj_def_id
AND COA.CHART_OF_ACCOUNTS_ID = pv_coa_id;
pv_progress := 'select fem_vs_id from AOL valuse set map';
SELECT fem_value_set_id
INTO pv_fem_vs_id
FROM FEM_INTG_AOL_VALSET_MAP
WHERE DIMENSION_ID = pv_dim_id
AND NVL(SEGMENT1_VALUE_SET_ID,-99) = pv_mapped_segs(1).vs_id
AND NVL(SEGMENT2_VALUE_SET_ID,-99) = pv_mapped_segs(2).vs_id
AND NVL(SEGMENT3_VALUE_SET_ID,-99) = pv_mapped_segs(3).vs_id
AND NVL(SEGMENT4_VALUE_SET_ID,-99) = pv_mapped_segs(4).vs_id
AND NVL(SEGMENT5_VALUE_SET_ID,-99) = pv_mapped_segs(5).vs_id;
SELECT FEM_VALUE_SETS_B_S.nextval
INTO pv_fem_vs_id
FROM DUAL;
pv_progress := 'insert fem vs into aol mapping table';
INSERT INTO FEM_INTG_AOL_VALSET_MAP(
FEM_VALUE_SET_ID,
DIMENSION_ID,
SEGMENT1_VALUE_SET_ID,
SEGMENT2_VALUE_SET_ID,
SEGMENT3_VALUE_SET_ID,
SEGMENT4_VALUE_SET_ID,
SEGMENT5_VALUE_SET_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
pv_fem_vs_id,
pv_dim_id,
pv_mapped_segs(1).vs_id,
pv_mapped_segs(2).vs_id,
pv_mapped_segs(3).vs_id,
pv_mapped_segs(4).vs_id,
pv_mapped_segs(5).vs_id,
SYSDATE,
pv_user_id,
SYSDATE,
pv_user_id,
pv_login_id
);
,p_msg_text => 'Following error during selecting fem VSID
from FEM_INTG_AOL_VALSET_MAP: '||sqlerrm);
UPDATE fem_intg_dim_rule_defs def
SET default_member_value_set_id = pv_fem_vs_id,
default_member_id =
(SELECT org.company_cost_center_org_id
FROM fem_cctr_orgs_b org
WHERE org.cctr_org_display_code = 'Default'
AND org.value_set_id = pv_fem_vs_id)
WHERE def.dim_rule_obj_def_id =
(SELECT odb.object_definition_id
FROM fem_object_definition_b odb,
fem_intg_dim_rules rule
WHERE odb.object_id = rule.dim_rule_obj_id
AND rule.chart_of_accounts_id = pv_coa_id
AND rule.dimension_id = 0)
AND def.dim_mapping_option_code = 'SINGLEVAL';
SELECT ruledef.DIM_MAPPING_OPTION_CODE
, ruledef.SEGMENT_COUNT
INTO v_cctr_map_option_code
, v_cctr_seg_count
from fem_intg_dim_rules rules
, fem_intg_dim_rule_defs ruledef
, fem_object_definition_b objdef
where rules.chart_of_accounts_id = pv_coa_id
and rules.dim_rule_obj_id = objdef.object_id
and objdef.object_definition_id = ruledef.dim_rule_obj_def_id
and rules.dimension_id = pv_cctr_org_dim_id;
pv_progress := 'select fem_valueset_id for intercompany based on CCTR org dimension';
SELECT value_set_id
INTO pv_fem_vs_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_id = pv_gvsc_id
AND dimension_id = pv_cctr_org_dim_id;
SELECT value_set_id
INTO pv_com_vs_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_id = pv_gvsc_id
AND dimension_id = pv_com_dim_id;
SELECT value_set_id
INTO pv_cc_vs_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_id = pv_gvsc_id
AND dimension_id = pv_cc_dim_id;
pv_progress := 'Update FEM_GLOBAL_VS_COMBO_DEFS for intercompany dimension';
UPDATE FEM_GLOBAL_VS_COMBO_DEFS
SET VALUE_SET_ID = pv_fem_vs_id
,LAST_UPDATED_BY = pv_user_id
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATE_LOGIN = pv_login_id
WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
AND DIMENSION_ID = pv_dim_id;
SELECT VALUE_SET_ID
INTO v_global_defs_vs_id
FROM FEM_GLOBAL_VS_COMBO_DEFS
WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
AND DIMENSION_ID = pv_dim_id;
pv_progress := 'Update FEM_GLOBAL_VS_COMBO_DEFS';
UPDATE FEM_GLOBAL_VS_COMBO_DEFS
SET VALUE_SET_ID = pv_fem_vs_id
,LAST_UPDATED_BY = pv_user_id
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATE_LOGIN = pv_login_id
WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
AND DIMENSION_ID = pv_dim_id;
UPDATE FEM_GLOBAL_VS_COMBO_DEFS
SET VALUE_SET_ID = pv_fin_element_vs_id
,LAST_UPDATED_BY = pv_user_id
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATE_LOGIN = pv_login_id
WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
AND DIMENSION_ID = pv_fin_element_dim_id;
SELECT value_set_id
INTO pv_com_vs_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_id = pv_gvsc_id
AND dimension_id = pv_com_dim_id;
SELECT value_set_id
INTO pv_cc_vs_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_id = pv_gvsc_id
AND dimension_id = pv_cc_dim_id;
pv_progress := 'select fem_vs_id from AOL valuse set map for com vsid';
SELECT fem_value_set_id
INTO pv_com_vs_id
FROM FEM_INTG_AOL_VALSET_MAP
WHERE DIMENSION_ID = pv_com_dim_id
AND NVL(SEGMENT1_VALUE_SET_ID,-99) = pv_mapped_segs(1).vs_id
AND NVL(SEGMENT2_VALUE_SET_ID,-99) = -99
AND NVL(SEGMENT3_VALUE_SET_ID,-99) = -99
AND NVL(SEGMENT4_VALUE_SET_ID,-99) = -99
AND NVL(SEGMENT5_VALUE_SET_ID,-99) = -99;
pv_progress := 'unexpected error whiel selecting pv_com_vs_id from FEM_INTG_AOL_VALSET_MAP: ' ||sqlerrm;
pv_progress := 'insert newly created company vset into aol mapping table';
SELECT FEM_VALUE_SETS_B_S.nextval
INTO pv_com_vs_id
FROM DUAL;
INSERT INTO FEM_INTG_AOL_VALSET_MAP
(
FEM_VALUE_SET_ID,
DIMENSION_ID,
SEGMENT1_VALUE_SET_ID,
SEGMENT2_VALUE_SET_ID,
SEGMENT3_VALUE_SET_ID,
SEGMENT4_VALUE_SET_ID,
SEGMENT5_VALUE_SET_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
pv_com_vs_id,
pv_com_dim_id,
pv_mapped_segs(1).vs_id,
-99,
-99,
-99,
-99,
SYSDATE,
pv_user_id,
SYSDATE,
pv_user_id,
pv_login_id
);
pv_progress := 'Update FEM_GLOBAL_VS_COMBO_DEFS for company dim';
UPDATE FEM_GLOBAL_VS_COMBO_DEFS
SET VALUE_SET_ID = pv_com_vs_id
,LAST_UPDATED_BY = pv_user_id
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATE_LOGIN = pv_login_id
WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
AND DIMENSION_ID = pv_com_dim_id;
pv_progress := 'select fem_vs_id from AOL valuse set map for cc vsid';
SELECT fem_value_set_id
INTO pv_cc_vs_id
FROM FEM_INTG_AOL_VALSET_MAP
WHERE DIMENSION_ID = pv_cc_dim_id
AND NVL(SEGMENT1_VALUE_SET_ID,-99) = v_temp_vs_id
AND NVL(SEGMENT2_VALUE_SET_ID,-99) = -99
AND NVL(SEGMENT3_VALUE_SET_ID,-99) = -99
AND NVL(SEGMENT4_VALUE_SET_ID,-99) = -99
AND NVL(SEGMENT5_VALUE_SET_ID,-99) = -99;
pv_progress := 'unexpected error while selecting pv_cc_vs_id
from FEM_INTG_AOL_VALSET_MAP: ' ||sqlerrm;
SELECT FEM_VALUE_SETS_B_S.nextval
INTO pv_cc_vs_id
FROM DUAL;
pv_progress := 'insert newly created cc vset into aol mapping table';
INSERT INTO FEM_INTG_AOL_VALSET_MAP
(
FEM_VALUE_SET_ID,
DIMENSION_ID,
SEGMENT1_VALUE_SET_ID,
SEGMENT2_VALUE_SET_ID,
SEGMENT3_VALUE_SET_ID,
SEGMENT4_VALUE_SET_ID,
SEGMENT5_VALUE_SET_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
pv_cc_vs_id,
pv_cc_dim_id,
v_temp_vs_id,
-99,
-99,
-99,
-99,
SYSDATE,
pv_user_id,
SYSDATE,
pv_user_id,
pv_login_id
);
pv_progress := 'Insert into FEM_GLOBAL_VS_COMBO_DEFS for cc dim';
UPDATE FEM_GLOBAL_VS_COMBO_DEFS
SET VALUE_SET_ID = pv_cc_vs_id
,LAST_UPDATED_BY = pv_user_id
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATE_LOGIN = pv_login_id
WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
AND DIMENSION_ID = pv_cc_dim_id;
SELECT fem_value_set_id
INTO pv_com_vs_id
FROM FEM_INTG_AOL_VALSET_MAP
WHERE DIMENSION_ID = pv_com_dim_id
AND NVL(SEGMENT1_VALUE_SET_ID,-99) = v_com_fnd_vs_id
AND NVL(SEGMENT2_VALUE_SET_ID,-99) = -99
AND NVL(SEGMENT3_VALUE_SET_ID,-99) = -99
AND NVL(SEGMENT4_VALUE_SET_ID,-99) = -99
AND NVL(SEGMENT5_VALUE_SET_ID,-99) = -99;
SELECT fem_value_set_id
INTO pv_cc_vs_id
FROM FEM_INTG_AOL_VALSET_MAP
WHERE DIMENSION_ID = pv_cc_dim_id
AND NVL(SEGMENT1_VALUE_SET_ID,-99) = v_cc_fnd_vs_id
AND NVL(SEGMENT2_VALUE_SET_ID,-99) = -99
AND NVL(SEGMENT3_VALUE_SET_ID,-99) = -99
AND NVL(SEGMENT4_VALUE_SET_ID,-99) = -99
AND NVL(SEGMENT5_VALUE_SET_ID,-99) = -99;
pv_progress := 'insert newly created company vset into aol mapping table';
SELECT default_value_set_id
INTO pv_com_vs_id
FROM fem_xdim_dimensions
WHERE dimension_id = pv_com_dim_id;
SELECT FEM_VALUE_SETS_B_S.nextval
INTO pv_com_vs_id
FROM DUAL;
INSERT INTO FEM_INTG_AOL_VALSET_MAP
(
FEM_VALUE_SET_ID,
DIMENSION_ID,
SEGMENT1_VALUE_SET_ID,
SEGMENT2_VALUE_SET_ID,
SEGMENT3_VALUE_SET_ID,
SEGMENT4_VALUE_SET_ID,
SEGMENT5_VALUE_SET_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
pv_com_vs_id,
pv_com_dim_id,
v_com_fnd_vs_id,
-99,
-99,
-99,
-99,
SYSDATE,
pv_user_id,
SYSDATE,
pv_user_id,
pv_login_id
);
pv_progress := 'Update FEM_GLOBAL_VS_COMBO_DEFS for company dim';
UPDATE FEM_GLOBAL_VS_COMBO_DEFS
SET VALUE_SET_ID = pv_com_vs_id
,LAST_UPDATED_BY = pv_user_id
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATE_LOGIN = pv_login_id
WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
AND DIMENSION_ID = pv_com_dim_id;
pv_progress := 'insert newly created cost center vset into aol mapping table';
SELECT default_value_set_id
INTO pv_cc_vs_id
FROM fem_xdim_dimensions
WHERE dimension_id = pv_cc_dim_id;
SELECT FEM_VALUE_SETS_B_S.nextval
INTO pv_cc_vs_id
FROM DUAL;
INSERT INTO FEM_INTG_AOL_VALSET_MAP
(
FEM_VALUE_SET_ID,
DIMENSION_ID,
SEGMENT1_VALUE_SET_ID,
SEGMENT2_VALUE_SET_ID,
SEGMENT3_VALUE_SET_ID,
SEGMENT4_VALUE_SET_ID,
SEGMENT5_VALUE_SET_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
pv_cc_vs_id,
pv_cc_dim_id,
v_cc_fnd_vs_id,
-99,
-99,
-99,
-99,
SYSDATE,
pv_user_id,
SYSDATE,
pv_user_id,
pv_login_id
);
pv_progress := 'Insert into FEM_GLOBAL_VS_COMBO_DEFS for cc dim';
UPDATE FEM_GLOBAL_VS_COMBO_DEFS
SET VALUE_SET_ID = pv_cc_vs_id
,LAST_UPDATED_BY = pv_user_id
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATE_LOGIN = pv_login_id
WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id
AND DIMENSION_ID = pv_cc_dim_id;
pv_progress := 'Before update fem_intg_dim_rule_defs';
UPDATE fem_intg_dim_rule_defs
SET fem_value_set_id = pv_fem_vs_id
WHERE dim_rule_obj_def_id = p_dim_rule_obj_def_id;
SELECT NVL(MAX(CODE_COMBINATION_ID),-1)
INTO v_max_ccid_in_map_table
FROM FEM_INTG_OGL_CCID_MAP
WHERE GLOBAL_VS_COMBO_ID = pv_gvsc_id;
SELECT NVL(MAX(CODE_COMBINATION_ID),-1)
INTO v_max_ccid_in_glccid_table
FROM GL_CODE_COMBINATIONS
WHERE CHART_OF_ACCOUNTS_ID = pv_coa_id;
v_map_records_inserted_count
);
SELECT odb.object_definition_id
INTO v_interco_rule_def_id
FROM fem_object_definition_b odb,
fem_intg_dim_rules rule
WHERE odb.object_id = rule.dim_rule_obj_id
AND rule.chart_of_accounts_id = pv_coa_id
AND rule.dimension_id = 0;
select INDEX_NAME BULK COLLECT
INTO pv_index_name
from all_indexes where table_name in('FEM_INTG_AOL_VALSET_MAP',
'FEM_INTG_OGL_CCID_MAP',
'FEM_COMPANIES_B',
'FEM_COMPANIES_TL',
'FEM_COST_CENTERS_B',
'FEM_COST_CENTERS_TL',
pv_member_b_table_name,
pv_member_tl_table_name,
pv_attr_table_name)
AND table_owner = 'FEM' and owner = 'FEM';
INSERT INTO FEM_INTG_OGL_CCID_MAP(
CODE_COMBINATION_ID,
GLOBAL_VS_COMBO_ID,
COMPANY_COST_CENTER_ORG_ID,
NATURAL_ACCOUNT_ID,
LINE_ITEM_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PRODUCT_ID,
CHANNEL_ID,
PROJECT_ID,
CUSTOMER_ID,
ENTITY_ID,
INTERCOMPANY_ID,
USER_DIM1_ID,
USER_DIM2_ID,
USER_DIM3_ID,
USER_DIM4_ID,
USER_DIM5_ID,
USER_DIM6_ID,
USER_DIM7_ID,
USER_DIM8_ID,
USER_DIM9_ID,
USER_DIM10_ID,
TASK_ID,
EXTENDED_ACCOUNT_TYPE)
SELECT
GLCC.CODE_COMBINATION_ID,
p_gvsc_id,
-1,
-1,
-1,
SYSDATE,
pv_user_id,
SYSDATE,
pv_user_id,
pv_login_id,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1
FROM GL_CODE_COMBINATIONS GLCC
WHERE GLCC.CODE_COMBINATION_ID BETWEEN
p_max_ccid_in_map_table+1 AND p_max_ccid_in_glccid_table
AND CHART_OF_ACCOUNTS_ID = p_coa_id
AND GLCC.SUMMARY_FLAG = 'N';
SELECT flex_value_set_name
,description
INTO v_vs_name
,v_vs_desc
FROM FND_FLEX_VALUE_SETS
WHERE flex_value_set_id = pv_mapped_segs(1).vs_id;
SELECT flex_value_set_name
,description
INTO v_seg_name
,v_seg_desc
FROM FND_FLEX_VALUE_SETS
WHERE flex_value_set_id = pv_mapped_segs(i).vs_id;
SELECT flex_value_set_name
,description
INTO v_vs_name
,v_vs_desc
FROM FND_FLEX_VALUE_SETS
WHERE flex_value_set_id = p_fnd_vs_id;
SELECT count(*)
INTO v_vs_count
FROM FEM_VALUE_SETS_B
WHERE value_set_id = p_fem_value_set_id;
,p_msg_text => 'Calling FEM_VALUE_SETS_PKG.insert_row with' ||pv_crlf||
'X_VALUE_SET_ID => '||p_fem_value_set_id||pv_crlf||
'X_DEFAULT_LOAD_MEMBER_ID => NULL'||pv_crlf||
'X_DEFAULT_MEMBER_ID => NULL'||pv_crlf||
'X_OBJECT_VERSION_NUMBER => 1'||pv_crlf||
'X_DEFAULT_HIERARCHY_OBJ_ID => NULL'||pv_crlf||
'X_READ_ONLY_FLAG => N'||pv_crlf||
'X_VALUE_SET_DISPLAY_CODE => '||v_vs_name||pv_crlf||
'X_DIMENSION_ID => '||p_dim_id||pv_crlf||
'X_VALUE_SET_NAME => '||v_vs_name||pv_crlf||
'X_DESCRIPTION => '||v_vs_desc);
FEM_VALUE_SETS_PKG.insert_row
( X_ROWID => v_rowid
, X_VALUE_SET_ID => p_fem_value_set_id
, X_DEFAULT_LOAD_MEMBER_ID => NULL
, X_DEFAULT_MEMBER_ID => NULL
, X_OBJECT_VERSION_NUMBER => 1
, X_DEFAULT_HIERARCHY_OBJ_ID => NULL
, X_READ_ONLY_FLAG => 'N'
, X_VALUE_SET_DISPLAY_CODE => v_vs_name
, X_DIMENSION_ID => p_dim_id
, X_VALUE_SET_NAME => v_vs_name
, X_DESCRIPTION => v_vs_desc
, X_CREATION_DATE => SYSDATE
, X_CREATED_BY => pv_user_id
, X_LAST_UPDATE_DATE => SYSDATE
, X_LAST_UPDATED_BY => pv_user_id
, X_LAST_UPDATE_LOGIN => pv_login_id);
UPDATE fem_cctr_orgs_b
SET read_only_flag = 'Y'
WHERE cctr_org_display_code = 'Default'
AND value_set_id = p_fem_value_set_id;
SELECT 1 FROM fem_pl_object_executions
WHERE object_id
IN (SELECT BAL_RULE_OBJ_ID
FROM fem_intg_bal_rules
WHERE chart_of_accounts_id =c_charts_of_account_id );
SELECT 1
FROM fem_data_locations fdl,
fem_ledgers_attr fla,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb
WHERE fdab.attribute_varchar_label = 'GLOBAL_VS_COMBO'
AND fdavb.attribute_id = fdab.attribute_id
AND fdavb.default_version_flag = 'Y'
AND fla.attribute_id = fdab.attribute_id
AND fla.version_id = fdavb.version_id
AND fla.dim_attribute_numeric_member = c_gvsc_id
AND fdl.ledger_id = fla.ledger_id
AND fdl.table_name <> 'FEM_BALANCES';
select 1 from fem_global_vs_combo_defs
where global_vs_combo_id <> c_gvsc_id
and dimension_id = c_dim_id
and value_set_id = c_val_set_id ;
SELECT chart_of_accounts_id,
dimension_id
INTO l_chart_of_accounts_id,
l_dim_id
FROM fem_intg_dim_rules
WHERE dim_rule_obj_id = p_dim_rule_obj_id;
SELECT GLOBAL_VS_COMBO_ID
INTO l_global_vs_combo_id
FROM fem_intg_coa_gvsc_map
WHERE chart_of_accounts_id = l_chart_of_accounts_id;
SELECT object_definition_id
INTO l_obj_def_id
FROM fem_object_definition_b
WHERE object_id = p_dim_rule_obj_id ;
SELECT dim_mapping_Option_code
INTO l_dim_mapping_Option_code
FROM fem_intg_dim_rule_defs
WHERE dim_rule_obj_def_id = l_obj_def_id;
SELECT MEMBER_B_TABLE_NAME,
MEMBER_TL_TABLE_NAME,
HIERARCHY_TABLE_NAME,
ATTRIBUTE_TABLE_NAME,
MEMBER_COL
INTO l_memb_b_tab_name,
l_memb_tl_tab_name,
l_hier_tab_name,
l_attr_tab_name,
l_memb_col_name
FROM fem_xdim_dimensions
WHERE dimension_id = l_dim_id;
SELECT value_set_id
INTO l_value_set_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_Id = l_global_vs_combo_id
and dimension_id = l_dim_id;
SELECT value_set_id
INTO comp_val_set_id
FROM fem_global_vs_combo_defs
WHERE dimension_id =112
AND global_vs_combo_Id = l_global_vs_combo_id;
SELECT value_set_id
INTO cctr_val_set_id
FROM fem_global_vs_combo_defs
WHERE dimension_id =113
AND global_vs_combo_Id = l_global_vs_combo_id;
SELECT odb.object_id,odb.object_definition_id
INTO l_intercomp_obj_id,l_intercomp_obj_def_id
FROM fem_object_definition_b odb,
fem_intg_dim_rules idr
WHERE odb.object_id = idr.dim_rule_obj_id
And idr.chart_of_accounts_id = l_chart_of_accounts_id
AND idr.dimension_id = 0;
SELECT max(o.request_id)
INTO l_request_id
FROM fem_pl_object_executions o
WHERE o.display_flag = 'Y'
AND o.object_id = p_dim_rule_obj_id;
temp_stmt:= 'UPDATE fem_intg_ogl_ccid_map
SET ' || l_memb_col_name ||' =-1 ' ;
UPDATE fem_global_vs_combo_defs
SET value_set_id = -1
WHERE global_vs_combo_id = l_global_vs_combo_id
AND dimension_id in (8,112,113);
UPDATE fem_global_vs_combo_defs
SET value_set_id = -1
WHERE global_vs_combo_id = l_global_vs_combo_id
AND dimension_id = l_dim_id;
UPDATE fem_intg_dim_rule_defs
SET MAX_CCID_PROCESSED = null,
MAX_FLEX_VALUE_ID_PROCESSED = null,
FEM_VALUE_SET_ID = null
WHERE DIM_RULE_OBJ_DEF_ID = l_obj_def_id;
UPDATE fem_intg_dim_rule_defs
SET MAX_CCID_PROCESSED = null,
MAX_FLEX_VALUE_ID_PROCESSED = null,
FEM_VALUE_SET_ID = null,
DEFAULT_MEMBER_ID = null,
DEFAULT_MEMBER_VALUE_SET_ID = null
WHERE DIM_RULE_OBJ_DEF_ID = l_intercomp_obj_def_id;
temp_query := 'SELECT 1 from ' || l_hier_tab_name ||
' WHERE parent_value_set_id =:1 OR child_value_set_id =:2 ';
temp_query := 'Delete from ' || l_memb_b_tab_name ||
' where value_set_id =:1 ';
temp_query := 'Delete from ' || l_memb_tl_tab_name ||
' where value_set_id =:1 ';
temp_query := 'Delete from ' || l_attr_tab_name ||
' where value_set_id =:1 ';
delete from fem_value_sets_b where value_set_id = l_value_set_id;
delete from fem_value_sets_tl where value_set_id = l_value_set_id;
DELETE FROM FEM_COMPANIES_B WHERE value_set_id = comp_val_set_id;
DELETE FROM FEM_COMPANIES_TL WHERE value_set_id = comp_val_set_id;
DELETE FROM FEM_COMPANIES_ATTR WHERE value_set_id = comp_val_set_id;
delete from fem_value_sets_b where value_set_id = comp_val_set_id;
DELETE FROM FEM_COST_CENTERS_B WHERE value_set_id = cctr_val_set_id;
DELETE FROM FEM_COST_CENTERS_TL WHERE value_set_id = cctr_val_set_id;
DELETE FROM FEM_COST_CENTERS_ATTR WHERE value_set_id = cctr_val_set_id;
delete from fem_value_sets_b where value_set_id = cctr_val_set_id;
delete from fem_intg_aol_valset_map where fem_value_set_id = l_value_set_id;