[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
A.ATTRIBUTE_ID,
AV.VERSION_ID,
M.NATURAL_ACCOUNT_ID,
:pv_fem_vs_id VALUE_SET_ID,
NULL DIM_ATTRIBUTE_VALUE_SET_ID,
DECODE(
A.ATTRIBUTE_VARCHAR_LABEL,
'SOURCE_SYSTEM_CODE', :pv_source_system_code_id,
NULL
) DIM_ATTRIBUTE_NUMERIC_MEMBER,
DECODE(
A.ATTRIBUTE_VARCHAR_LABEL,
'EXTENDED_ACCOUNT_TYPE',
DECODE(
SUBSTR(
FND_GLOBAL.NEWLINE ||
V.COMPILED_VALUE_ATTRIBUTES ||
FND_GLOBAL.NEWLINE,
INSTR(
FND_GLOBAL.NEWLINE ||
V.COMPILED_VALUE_ATTRIBUTES ||
FND_GLOBAL.NEWLINE,
FND_GLOBAL.NEWLINE,
1, :v_account_type_pos
)+1,
1
),
'A', 'ASSET',
'E', 'EXPENSE',
'R', 'REVENUE',
'L', 'LIABILITY',
'O', 'EQUITY'
),
'BUDGET_ALLOWED_FLAG',
SUBSTR(
FND_GLOBAL.NEWLINE ||
V.COMPILED_VALUE_ATTRIBUTES ||
FND_GLOBAL.NEWLINE,
INSTR(
FND_GLOBAL.NEWLINE ||
V.COMPILED_VALUE_ATTRIBUTES ||
FND_GLOBAL.NEWLINE,
FND_GLOBAL.NEWLINE,
1, :v_budget_pos
)+1,
1
),
'NAT_ACCT_EXPENSE_TYPE_CODE', 'FIXED',
'INVENTORIABLE_FLAG', 'N',
'RECON_LEAF_NODE_FLAG', :v_leaf_flag,
NULL
) DIM_ATTRIBUTE_VARCHAR_MEMBER,
1 OBJECT_VERSION_NUMBER,
'N' AW_SNAPSHOT_FLAG,
'Y' READ_ONLY_FLAG,
:b_sysdate CREATION_DATE,
:pv_user_id CREATED_BY,
:b_sysdate LAST_UPDATE_DATE,
:pv_user_id LAST_UPDATED_BY,
:pv_login_id LAST_UPDATE_LOGIN
FROM
FEM_NAT_ACCTS_B M,
FND_FLEX_VALUES V,
FEM_DIM_ATTRIBUTES_B A,
FEM_DIM_ATTR_VERSIONS_B AV
WHERE
M.VALUE_SET_ID = :b_driving_where_vs_id ||
:b_m_vs_id || :b_gt_dim_id AND
V.FLEX_VALUE_SET_ID = :b_flex_value_where_vs_id1 AND
V.FLEX_VALUE = M.NATURAL_ACCOUNT_DISPLAY_CODE ||
:b_flex_value_where_vs_id2 AND
A.DIMENSION_ID = :b_a_dim_id AND
AV.ATTRIBUTE_ID = A.ATTRIBUTE_ID AND
AV.DEFAULT_VERSION_FLAG = 'Y' || :b_pv_gvsc_id || :b_pv_dim_id AND
A.ATTRIBUTE_VARCHAR_LABEL IN (
'SOURCE_SYSTEM_CODE',
'EXTENDED_ACCOUNT_TYPE',
'BUDGET_ALLOWED_FLAG',
'NAT_ACCT_EXPENSE_TYPE_CODE',
'INVENTORIABLE_FLAG',
'RECON_LEAF_NODE_FLAG'
)
) S
ON (
ATTR.ATTRIBUTE_ID = S.ATTRIBUTE_ID AND
ATTR.VERSION_ID = S.VERSION_ID AND
ATTR.NATURAL_ACCOUNT_ID = S.NATURAL_ACCOUNT_ID AND
ATTR.VALUE_SET_ID = S.VALUE_SET_ID
)
WHEN MATCHED THEN UPDATE
SET ATTR.LAST_UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN INSERT (
ATTR.ATTRIBUTE_ID,
ATTR.VERSION_ID,
ATTR.NATURAL_ACCOUNT_ID,
ATTR.VALUE_SET_ID,
ATTR.DIM_ATTRIBUTE_VALUE_SET_ID,
ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER,
ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER,
ATTR.OBJECT_VERSION_NUMBER,
ATTR.AW_SNAPSHOT_FLAG,
ATTR.READ_ONLY_FLAG,
ATTR.CREATION_DATE,
ATTR.CREATED_BY,
ATTR.LAST_UPDATE_DATE,
ATTR.LAST_UPDATED_BY,
ATTR.LAST_UPDATE_LOGIN
) VALUES (
S.ATTRIBUTE_ID,
S.VERSION_ID,
S.NATURAL_ACCOUNT_ID,
S.VALUE_SET_ID,
S.DIM_ATTRIBUTE_VALUE_SET_ID,
S.DIM_ATTRIBUTE_NUMERIC_MEMBER,
S.DIM_ATTRIBUTE_VARCHAR_MEMBER,
S.OBJECT_VERSION_NUMBER,
S.AW_SNAPSHOT_FLAG,
S.READ_ONLY_FLAG,
S.CREATION_DATE,
S.CREATED_BY,
S.LAST_UPDATE_DATE,
S.LAST_UPDATED_BY,
S.LAST_UPDATE_LOGIN
)
USING pv_fem_vs_id, 10, v_account_type_pos, v_budget_pos, 'Y',
SYSDATE, pv_user_id, SYSDATE, pv_user_id, pv_login_id,
pv_fem_vs_id, NULL, NULL, pv_mapped_segs(1).vs_id, NULL,
pv_dim_id, NULL, NULL
====================================================================== */
PROCEDURE Populate_Dimension_Attribute(
p_summary_flag IN VARCHAR,
x_completion_code OUT NOCOPY NUMBER,
x_row_count_tot OUT NOCOPY NUMBER
) IS
v_module_name VARCHAR2(100);
SELECT POSITION
FROM (
SELECT ROWNUM POSITION, VALUE_ATTRIBUTE_TYPE
FROM (
SELECT VALUE_ATTRIBUTE_TYPE
FROM FND_FLEX_VALIDATION_QUALIFIERS
WHERE ID_FLEX_APPLICATION_ID = 101
AND ID_FLEX_CODE = 'GL#'
AND SEGMENT_ATTRIBUTE_TYPE IN ('GL_GLOBAL', 'GL_ACCOUNT')
AND FLEX_VALUE_SET_ID =
FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(seg_num).vs_id
ORDER BY ASSIGNMENT_DATE, VALUE_ATTRIBUTE_TYPE
)
)
WHERE VALUE_ATTRIBUTE_TYPE = qualifier;
SELECT COUNT(ATTRIBUTE_ID)
INTO v_attribute_num
FROM FEM_DIM_ATTRIBUTES_B
WHERE DIMENSION_ID = FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
(SELECT' || '
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).vs_id || ' FLEX_VALUE_SET_ID,' || '
' || NVL(FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).id_col_name, 'NULL') || ' FLEX_VALUE_ID,' || '
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).val_col_name || ' FLEX_VALUE,' || '
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).compiled_attr_col_name ||
' COMPILED_VALUE_ATTRIBUTES
FROM' || '
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_name || '
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).where_clause || ') V,';
(SELECT ' || '
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).vs_id || ' FLEX_VALUE_SET_ID,' || '
' || NVL(FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).id_col_name, 'NULL') || ' FLEX_VALUE_ID,' || '
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).val_col_name || ' FLEX_VALUE,' || '
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).compiled_attr_col_name || ' COMPILED_VALUE_ATTRIBUTES
FROM ' || '
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).table_name || '
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(FEM_INTG_DIM_RULE_ENG_PKG.pv_natural_account_segment_num).where_clause || ') V,';
SELECT
A.ATTRIBUTE_ID,
AV.VERSION_ID' || ',
' || v_member_id_col_name || ',
:pv_fem_vs_id VALUE_SET_ID,
' || v_dim_attr_value_set_id || ' DIM_ATTRIBUTE_VALUE_SET_ID,';
:b_sysdate LAST_UPDATE_DATE,
:pv_user_id LAST_UPDATED_BY,
:pv_login_id LAST_UPDATE_LOGIN
FROM ' || v_from || '
WHERE ' || v_where || v_attributes_where;
WHEN MATCHED THEN UPDATE
SET ATTR.LAST_UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN INSERT (
ATTR.ATTRIBUTE_ID,
ATTR.VERSION_ID,
ATTR.' || v_member_col || ',
ATTR.VALUE_SET_ID,
ATTR.DIM_ATTRIBUTE_VALUE_SET_ID,
ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER,
ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER,
ATTR.OBJECT_VERSION_NUMBER,
ATTR.AW_SNAPSHOT_FLAG,
ATTR.READ_ONLY_FLAG,
ATTR.CREATION_DATE,
ATTR.CREATED_BY,
ATTR.LAST_UPDATE_DATE,
ATTR.LAST_UPDATED_BY,
ATTR.LAST_UPDATE_LOGIN
) VALUES (
S.ATTRIBUTE_ID,
S.VERSION_ID,
S.' || v_member_col || ',
S.VALUE_SET_ID,
S.DIM_ATTRIBUTE_VALUE_SET_ID,
S.DIM_ATTRIBUTE_NUMERIC_MEMBER,
S.DIM_ATTRIBUTE_VARCHAR_MEMBER,
S.OBJECT_VERSION_NUMBER,
S.AW_SNAPSHOT_FLAG,
S.READ_ONLY_FLAG,
S.CREATION_DATE,
S.CREATED_BY,
S.LAST_UPDATE_DATE,
S.LAST_UPDATED_BY,
S.LAST_UPDATE_LOGIN
)';
p_module => v_module_name || '.dsql_insert_merge_into_' ||
FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
p_msg_text => v_stmt1
);
p_module => v_module_name || '.dsql_insert_merge_into_' ||
FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
p_msg_text => v_stmt2
);
p_module => v_module_name || '.dsql_insert_merge_into_' ||
FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
p_msg_text => v_stmt3
);
p_module => v_module_name || '.dsql_insert_merge_into_' ||
FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
p_msg_text => v_stmt4
);
p_module => v_module_name || '.dsql_insert_merge_into_' ||
FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
p_msg_text => v_stmt5
);
p_module => v_module_name || '.dsql_insert_merge_into_' ||
FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
p_msg_text => v_stmt6
);
p_module => v_module_name || '.dsql_insert_merge_into_' ||
FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
p_msg_text => 'USING ' ||
TO_CHAR(FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id) || ', ' ||
TO_CHAR(FEM_INTG_DIM_RULE_ENG_PKG.pv_source_system_code_id) || ', ' ||
TO_CHAR(v_account_type_pos) || ', ' ||
TO_CHAR(v_budget_pos) || ', ' ||
v_leaf_flag || ', ' ||
TO_CHAR(SYSDATE, 'YYYY/MM/DD') || ', ' ||
TO_CHAR(pv_user_id) || ', ' ||
TO_CHAR(SYSDATE, 'YYYY/MM/DD') || ', ' ||
TO_CHAR(pv_user_id) || ', ' ||
TO_CHAR(pv_login_id) || ', ' ||
TO_CHAR(b_driving_where_vs_id) || ', ' ||
TO_CHAR(b_m_vs_id) || ', ' ||
TO_CHAR(b_gt_dim_id) || ', ' ||
TO_CHAR(b_flex_value_where_vs_id1) || ', ' ||
TO_CHAR(b_flex_value_where_vs_id2) || ', ' ||
TO_CHAR(b_a_dim_id) || ', ' ||
TO_CHAR(b_gv_gvsc_id) || ', ' ||
TO_CHAR(b_gv_dim_id)
);
p_module => v_module_name || '.cnt_insert_merge_into_' ||
FEM_INTG_DIM_RULE_ENG_PKG.pv_attr_table_name,
p_msg_text => x_row_count_tot
);
SELECT COLUMN_NAME
FROM FEM_TAB_COLUMNS_B
WHERE TABLE_NAME = 'FEM_BALANCES'
AND FEM_DATA_TYPE_CODE = 'DIMENSION'
AND DIMENSION_ID = FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
v_fch_vs_select_stmt VARCHAR2(1000):=
'SELECT 1
FROM fem_global_vs_combo_defs fch_vs_combo
WHERE fch_vs_combo.global_vs_combo_id = ( SELECT fch_global_vs_combo_id
FROM gcs_system_options )
AND fch_vs_combo.dimension_id = 8
AND fch_vs_combo.value_set_id = :fem_value_set_id';
'UPDATE fem_intg_ogl_ccid_map fiocm
SET '||v_column_list||' = '||v_value_list||'
WHERE fiocm.code_combination_id between
:v_low and :v_high
AND fiocm.global_vs_combo_id = :v_gvsc_id';
pv_progress := 'after executing update map';
UPDATE fem_intg_dim_rule_defs
SET max_ccid_processed = fem_intg_dim_rule_eng_pkg.pv_max_ccid_to_be_mapped
WHERE dim_rule_obj_def_id = fem_intg_dim_rule_eng_pkg.pv_dim_rule_obj_def_id;
OPEN fch_vs_cursor FOR v_fch_vs_select_stmt USING FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
program => 'FCH_UPDATE_ENTITY_ORGS',
sub_request => FALSE);
p_msg_text => 'Submitted Update Entity Organizations Request ' || v_request_id
);
SELECT
:p_vs_id VALUE_SET_ID,
FLEX_VALUE_ID MEMBER_ID,
FLEX_VALUE MEMBER_DISPLAY_CODE
FROM
FND_FLEX_VALUES
WHERE
FLEX_VALUE_SET_ID = :v_vs_id_b AND
SUMMARY_FLAG = 'N'
) S
ON (
B.VALUE_SET_ID = S.VALUE_SET_ID AND
B. = S.MEMBER_DISPLAY_CODE
)
WHEN MATCHED THEN UPDATE
SET B.LAST_UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN INSERT (
VALUE_SET_ID,
,
,
ENABLED_FLAG,
PERSONAL_FLAG,
READ_ONLY_FLAG,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) VALUES (
S.VALUE_SET_ID,
S.MEMBER_ID,
S.MEMBER_DISPLAY_CODE,
'Y',
'N',
'Y',
1,
:b_sysdate,
:pv_user_id,
:b_sysdate,
:pv_user_id,
:pv_login_id
)
USING p_vs_id, pv_mapped_segs(1).vs_id,
SYSDATE, pv_user_id, SYSDATE, pv_user_id, pv_login_id
====================================================================== */
PROCEDURE Populate_Single_Segment(
p_dim_id IN NUMBER,
p_vs_id IN NUMBER,
p_member_b_table_name IN VARCHAR2,
p_member_tl_table_name IN VARCHAR2,
p_member_col IN VARCHAR2,
p_member_display_code_col IN VARCHAR2,
p_member_name_col IN VARCHAR2,
x_row_count_tot OUT NOCOPY NUMBER
) IS
v_module_name VARCHAR2(100);
SELECT
:p_vs_id VALUE_SET_ID,
FLEX_VALUE_ID MEMBER_ID,
FLEX_VALUE MEMBER_DISPLAY_CODE
FROM
FND_FLEX_VALUES
WHERE
FLEX_VALUE_SET_ID = :v_vs_id_b AND
SUMMARY_FLAG = ''N''
) S';
SELECT
M.VALUE_SET_ID,
T.FLEX_VALUE_ID MEMBER_COL,
T.FLEX_VALUE_MEANING MEMBER_NAME,
T.DESCRIPTION MEMBER_DESC,
T.LANGUAGE LANGUAGE_CODE,
T.SOURCE_LANG
FROM
' || p_member_b_table_name || ' M,
FND_FLEX_VALUES B,
FND_FLEX_VALUES_TL T
WHERE
M.VALUE_SET_ID = :p_vs_id AND
T.FLEX_VALUE_ID = M.' || p_member_col || ' AND
B.FLEX_VALUE_ID = T.FLEX_VALUE_ID AND
B.FLEX_VALUE_SET_ID = :v_vs_id_b AND
B.SUMMARY_FLAG = ''N''
) S';
SELECT
:p_vs_id || :v_vs_id_b VALUE_SET_ID,
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).val_col_name || ' MEMBER_DISPLAY_CODE
FROM
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_name ||
v_cr || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).where_clause || '
) S';
SELECT
B.VALUE_SET_ID,
B.' || p_member_col || ' MEMBER_COL,
V.MEMBER_NAME,
V.MEMBER_DESC,
L.LANGUAGE_CODE
FROM (
SELECT
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).val_col_name || ' MEMBER_DISPLAY_CODE,
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).val_col_name || ' MEMBER_NAME,
' || NVL(FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).meaning_col_name, 'NULL') || ' MEMBER_DESC
FROM
' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_name ||
v_cr || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).where_clause || '
) V,
' || p_member_b_table_name || ' B,
FND_LANGUAGES L
WHERE
B.VALUE_SET_ID = :p_vs_id || :v_vs_id_b AND
B.' || p_member_display_code_col || ' = V.MEMBER_DISPLAY_CODE AND
L.INSTALLED_FLAG = ''B''
) S';
WHEN MATCHED THEN UPDATE
SET B.LAST_UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN INSERT (
VALUE_SET_ID,
' || p_member_col || ',
' || p_member_display_code_col || ',
ENABLED_FLAG,
PERSONAL_FLAG,
READ_ONLY_FLAG,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) VALUES (
S.VALUE_SET_ID,
' || v_member_id_val || ',
S.MEMBER_DISPLAY_CODE,
''Y'',
''N'',
''N'',
1,
:b_sysdate,
:pv_user_id,
:b_sysdate,
:pv_user_id,
:pv_login_id
)';
WHEN MATCHED THEN UPDATE
SET TL.LAST_UPDATE_DATE = SYSDATE,
TL.DESCRIPTION = S.MEMBER_DESC
WHEN NOT MATCHED THEN INSERT (
VALUE_SET_ID,
' || p_member_col || ',
' || p_member_name_col || ',
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) VALUES (
S.VALUE_SET_ID,
S.MEMBER_COL,
S.MEMBER_NAME,
S.MEMBER_DESC,
S.LANGUAGE_CODE,
' || v_source_lang || ',
:b_sysdate,
:pv_user_id,
:b_sysdate,
:pv_user_id,
:pv_login_id
)';
The routine also updates FEM_INTG_OGL_CCID_MAP table through
a dynamically constructed UPDATE statement based on dimension.
The following is a sample dynamic UPDATE statement for Company Cost
Center Organization:
UPDATE FEM_INTG_OGL_CCID_MAP M
SET (COMPANY_COST_CENTER_ORG_ID) = (
SELECT
B.COMPANY_COST_CENTER_ORG_ID
FROM
FEM_CCTR_ORGS_B B,
GL_CODE_COMBINATIONS G
WHERE
B.VALUE_SET_ID = :pv_fem_vs_id AND
B.CCTR_ORG_DISPLAY_CODE =
G. AND
G.CHART_OF_ACCOUNTS_ID = :pv_coa_id AND
G.SUMMARY_FLAG = 'N' AND
M.CODE_COMBINATION_ID = G.CODE_COMBINATION_ID
)
WHERE M.GLOBAL_VS_COMBO_ID = :pv_gvsc_id
AND M.CODE_COMBINATION_ID IN (
SELECT
M2.CODE_COMBINATION_ID
FROM
FEM_CCTR_ORGS_B B2,
FEM_INTG_OGL_CCID_MAP M2,
GL_CODE_COMBINATIONS G2
WHERE
B2.VALUE_SET_ID = :pv_fem_vs_id AND
B2.CCTR_ORG_DISPLAY_CODE =
G2. AND
G2.CHART_OF_ACCOUNTS_ID = :pv_coa_id AND
G2.SUMMARY_FLAG = 'N' AND
M2.CODE_COMBINATION_ID = G2.CODE_COMBINATION_ID AND
M2.GLOBAL_VS_COMBO_ID = :pv_gvsc_id AND
M2.CODE_COMBINATION_ID BETWEEN :pv_max_ccid_processed+1 AND
:pv_max_ccid_to_be_mapped
)
USING pv_fem_vs_id, pv_coa_id, pv_gvsc_id, pv_fem_vs_id, pv_coa_id,
pv_gvsc_id, pv_max_ccid_processed+1, pv_max_ccid_to_be_mapped
Note that there is a possible redundant where clause when updating the
FEM_INTG_OGL_CCID_MAP table. For details, see bug4350641.
====================================================================== */
PROCEDURE Detail_Single_Segment(
x_completion_code OUT NOCOPY NUMBER,
x_row_count_tot OUT NOCOPY NUMBER
) IS
v_module_name VARCHAR2(100);
SELECT COLUMN_NAME
FROM FEM_TAB_COLUMNS_B
WHERE TABLE_NAME = 'FEM_BALANCES'
AND FEM_DATA_TYPE_CODE = 'DIMENSION'
AND DIMENSION_ID = FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
it should not attempt to update FEM_INTG_OGL_CCID_MAP with its members.
This is because the GEOGRAPHY_ID column does not exist in both
FEM_BALANCES and FEM_INTG_OGL_CCID_MAP tables. For details,
see bug4093543.
*/
--Start bug fix 5560443
/*
--dedutta: removed the Geography check here
NonNullFlag := false;
SELECT 1
INTO v_dim_rule_req_count
FROM dual
WHERE EXISTS ( SELECT 1
FROM fnd_concurrent_programs fcp,
fnd_concurrent_requests fcr,
fem_intg_dim_rules idr,
fem_object_definition_b fodb
WHERE fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcp.application_id = fcr.program_application_id
AND fcp.application_id = 274
AND fcp.concurrent_program_name = 'FEM_INTG_DIM_RULE_ENGINE'
AND fcr.phase_code <> 'C'
AND idr.dim_rule_obj_id = fodb.object_id
AND idr.chart_of_accounts_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id
--Start bug fix 5560443
AND idr.dimension_id <> 0
--End bug fix 5560443
AND fcr.argument1 = fodb.object_definition_id
AND fcr.argument2 = 'MEMBER');
select nvl(value,1)*2 no_of_workers
into v_Num_Workers
from v$parameter
where name = 'cpu_count';
p_module => v_module_name || '.dsql_update_fem_intg_ogl_ccid_map',
p_msg_text => 'USING ' ||
TO_CHAR(FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id) || ', ' ||
TO_CHAR(FEM_INTG_DIM_RULE_ENG_PKG.pv_gvsc_id)
);
ad_parallel_updates_pkg.purge_processed_units
(X_owner => 'FEM',
X_table => 'FEM_INTG_OGL_CCID_MAP',
X_script => FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id);
ad_parallel_updates_pkg.delete_update_information
(X_update_type => ad_parallel_updates_pkg.ROWID_RANGE,
X_owner => 'FEM',
X_table => 'FEM_INTG_OGL_CCID_MAP',
X_script => FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id);
UPDATE FEM_INTG_DIM_RULE_DEFS
SET MAX_CCID_PROCESSED = FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
WHERE DIM_RULE_OBJ_DEF_ID IN ( SELECT defs.dim_rule_obj_def_id
FROM fem_intg_dim_rules idr,
fem_object_definition_b fodb,
fem_xdim_dimensions fxd,
fem_intg_dim_rule_defs defs,
fem_tab_columns_b ftcb
WHERE ftcb.table_name = 'FEM_BALANCES'
AND ftcb.fem_data_type_code = 'DIMENSION'
AND ftcb.dimension_id = fxd.dimension_id
AND DECODE(ftcb.column_name,'INTERCOMPANY_ID', 0, fxd.dimension_id) = idr.dimension_id
AND idr.chart_of_accounts_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id
AND idr.dim_rule_obj_id = fodb.object_id
AND defs.dim_rule_obj_def_id = fodb.object_definition_id
AND defs.dim_mapping_option_code IN ('SINGLESEG','SINGLEVAL') );
p_module => v_module_name || '.cnt_update_FEM_INTG_DIM_RULE_DEFS',
p_msg_text => v_row_count_tot
);
p_msg_text => 'end update mapping table'
);
v_main_gt_insert_stmt VARCHAR2(4000);
v_main_insert_gt_count NUMBER;
v_comp_gt_insert_stmt VARCHAR2(4000);
v_cc_gt_insert_stmt VARCHAR2(4000);
v_comp_insert_gt_count NUMBER;
v_cc_insert_gt_count NUMBER;
v_insert_member_b_stmt VARCHAR2(4000);
v_insert_member_b_count NUMBER;
v_insert_member_vl_stmt VARCHAR2(4000);
v_insert_member_vl_count NUMBER;
v_insert_cc_vl_stmt VARCHAR2(4000);
v_insert_comp_vl_stmt VARCHAR2(4000);
SELECT COLUMN_NAME
FROM FEM_TAB_COLUMNS_B
WHERE TABLE_NAME = 'FEM_BALANCES'
AND FEM_DATA_TYPE_CODE = 'DIMENSION'
AND DIMENSION_ID = FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
v_fch_vs_select_stmt VARCHAR2(1000):=
'SELECT 1
FROM fem_global_vs_combo_defs fch_vs_combo
WHERE fch_vs_combo.global_vs_combo_id = ( SELECT fch_global_vs_combo_id
FROM gcs_system_options )
AND fch_vs_combo.dimension_id = 8
AND fch_vs_combo.value_set_id = :fem_value_set_id';
pv_progress := 'Start dynamic building of GT insert';
v_main_gt_insert_stmt
:= ' INSERT INTO FEM_INTG_DIM_MEMBERS_GT
( DIMENSION_ID
, SEGMENT1_VALUE
, SEGMENT2_VALUE
, SEGMENT3_VALUE
, SEGMENT4_VALUE
, SEGMENT5_VALUE
, CONCAT_SEGMENT_VALUE)
SELECT DISTINCT
:v_dim_id, '||
FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).application_column_name||'
,'||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).application_column_name;
v_main_gt_insert_stmt := v_main_gt_insert_stmt || ','||
FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).application_column_name;
v_main_gt_insert_stmt := v_main_gt_insert_stmt || ',-1';
v_main_gt_insert_stmt := v_main_gt_insert_stmt || ','||
FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).application_column_name;
v_main_gt_insert_stmt := v_main_gt_insert_stmt || ',-1';
v_main_gt_insert_stmt := v_main_gt_insert_stmt || ','||
FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).application_column_name;
v_main_gt_insert_stmt := v_main_gt_insert_stmt || ',-1';
v_main_gt_insert_stmt := v_main_gt_insert_stmt ||','
||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).application_column_name
|| '||''-''||'
||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).application_column_name;
v_main_gt_insert_stmt := v_main_gt_insert_stmt || '||''-''||'||
FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).application_column_name;
v_main_gt_insert_stmt := v_main_gt_insert_stmt || '||''-''||'||
FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).application_column_name;
v_main_gt_insert_stmt := v_main_gt_insert_stmt ||'||''-''||'||
FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).application_column_name;
v_main_gt_insert_stmt := v_main_gt_insert_stmt ||
' FROM GL_CODE_COMBINATIONS GCC
WHERE code_combination_id <= :v_high
AND summary_flag = ''N''
AND chart_of_accounts_id = :v_coa_id';
,p_value2 => v_main_gt_insert_stmt);
EXECUTE IMMEDIATE v_main_gt_insert_stmt
USING
FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id
-- ,FEM_INTG_DIM_RULE_ENG_PKG.pv_max_flex_value_id_processed + 1
,FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
,FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id;
v_main_gt_insert_stmt
:= ' INSERT INTO FEM_INTG_DIM_MEMBERS_GT
( DIMENSION_ID
, SEGMENT1_VALUE
, SEGMENT2_VALUE
, SEGMENT3_VALUE
, SEGMENT4_VALUE
, SEGMENT5_VALUE
, CONCAT_SEGMENT_VALUE)
SELECT DISTINCT :v_dim_id
, substr(hgt.child_display_code, 1
, decode(instr(hgt.child_display_code, ''-'', 1, 1), 0
, length(hgt.child_display_code)
, instr(hgt.child_display_code, ''-'', 1, 1)-1))
, decode(instr(hgt.child_display_code, ''-'', 1, 1), 0, ''-1''
, substr(hgt.child_display_code, instr(hgt.child_display_code, ''-'', 1, 1)+1
, decode(instr(hgt.child_display_code, ''-'', 1, 2), 0
, length(hgt.child_display_code) - instr(hgt.child_display_code, ''-'', 1, 1)
, instr(hgt.child_display_code, ''-'', 1, 2)-instr(hgt.child_display_code, ''-'', 1, 1)-1)))
, decode(instr(hgt.child_display_code, ''-'', 1, 2), 0, ''-1''
, substr(hgt.child_display_code, instr(hgt.child_display_code, ''-'', 1, 2)+1
, decode(instr(hgt.child_display_code, ''-'', 1, 3), 0
, length(hgt.child_display_code) - instr(hgt.child_display_code, ''-'', 1, 2)
, instr(hgt.child_display_code, ''-'', 1, 3)-instr(hgt.child_display_code, ''-'', 1, 2)-1)))
, decode(instr(hgt.child_display_code, ''-'', 1, 3), 0, ''-1''
, substr(hgt.child_display_code, instr(hgt.child_display_code, ''-'', 1, 3)+1
, decode(instr(hgt.child_display_code, ''-'', 1, 4), 0
, length(hgt.child_display_code) - instr(hgt.child_display_code, ''-'', 1, 3)
, instr(hgt.child_display_code, ''-'', 1, 4)-instr(hgt.child_display_code, ''-'', 1, 3)-1)))
, decode(instr(hgt.child_display_code, ''-'', 1, 4), 0, ''-1''
, substr(hgt.child_display_code, instr(hgt.child_display_code, ''-'', 1, 4)+1, length(hgt.child_display_code)-instr(hgt.child_display_code, ''-'', 1, 3)))
, hgt.child_display_code
from FEM_INTG_DIM_HIER_GT hgt
where hgt.HIERARCHY_OBJ_DEF_ID = :v_hier_obj_def_id';
,p_value2 => v_main_gt_insert_stmt);
EXECUTE IMMEDIATE v_main_gt_insert_stmt
USING FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id
, FEM_INTG_HIER_RULE_ENG_PKG.pv_hier_obj_def_id;
v_main_insert_gt_count := SQL%ROWCOUNT;
,p_value2 => v_main_insert_gt_count);
v_insert_member_b_stmt :=
'INSERT INTO '||
FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name||' ( '||
pv_local_member_col||'
, value_set_id
, dimension_group_id
, '||FEM_INTG_DIM_RULE_ENG_PKG.pv_member_display_code_col||'
, enabled_flag
, personal_flag
, creation_date
, created_by
, last_updated_by
, last_update_date
, last_update_login
, object_version_number
, read_only_flag)
SELECT
fnd_flex_values_s.nextval
, :v_fem_vs_id
, null
, concat_segment_value
, ''Y''
, ''N''
, sysdate
, :v_userid
, :v_userid
, sysdate
, :v_login_id
, 1
, ''N''
FROM fem_intg_dim_members_gt tab1
WHERE NOT EXISTS (SELECT ''x''
FROM ' || -- Bug 4393061 - changed read_only_flag to 'N'
FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name ||' tab2
WHERE tab2.value_set_id = :v_fem_vs_id
AND tab1.concat_segment_value
= tab2.'||
FEM_INTG_DIM_RULE_ENG_PKG.pv_member_display_code_col
||')';
,p_value2 => v_insert_member_b_stmt);
EXECUTE IMMEDIATE v_insert_member_b_stmt
USING
FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id
, pv_user_id
, pv_user_id
, pv_login_id
,FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
v_insert_member_b_count := SQL%ROWCOUNT;
,p_value2 => v_insert_member_b_count);
v_merge_stmt := v_merge_stmt || 'SELECT tab1.'||pv_local_member_col||' MEM_COL
, tab1.value_set_id VAL_SET_ID
, fil.language_code MEM_LANG
, fil_source.language_code LANG_CODE
, '||fem_intg_dim_rule_eng_pkg.pv_member_display_code_col || ' DISP_CODE_COL';
,:v_userid UPDATED_BY
,sysdate UPDATED_DATE
,:v_login_id UPDATE_LOGIN
FROM '||fem_intg_dim_rule_eng_pkg.pv_member_b_table_name ||' tab1,
fem_intg_dim_members_gt GT
,fnd_languages fil
,fnd_languages fil_source';
v_merge_stmt := v_merge_stmt|| ',( SELECT '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).meaning_col_name
||' DESCR ,'
||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).val_col_name
|| ' flex_value FROM '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_name || ' '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).where_clause || ') TL1';
v_merge_stmt := v_merge_stmt|| ',( SELECT '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).meaning_col_name
||' DESCR ,'
||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).val_col_name
|| ' flex_value FROM '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).table_name || ' '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).where_clause || ') TL2';
v_merge_stmt := v_merge_stmt|| ',( SELECT '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).meaning_col_name
||' DESCR ,'
||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).val_col_name
|| ' flex_value FROM '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).table_name || ' '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(3).where_clause || ') TL3';
v_merge_stmt := v_merge_stmt|| ',( SELECT '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).meaning_col_name
||' DESCR ,'
||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).val_col_name
|| ' flex_value FROM '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).table_name || ' '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(4).where_clause || ') TL4';
v_merge_stmt := v_merge_stmt|| ',( SELECT '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).meaning_col_name
||' DESCR ,'
||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).val_col_name
|| ' flex_value FROM '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).table_name || ' '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(5).where_clause || ') TL5';
WHEN MATCHED THEN UPDATE
SET TL.DESCRIPTION = D.MEMB_DESC
WHEN NOT MATCHED THEN Insert ('||
pv_local_member_col||',
VALUE_SET_ID
, LANGUAGE
, SOURCE_LANG
, ' ||fem_intg_dim_rule_eng_pkg.pv_member_name_col||'
, DESCRIPTION
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN )
VALUES(
D.MEM_COL,
D.VAL_SET_ID,
D.MEM_LANG,
D.LANG_CODE,
D.DISP_CODE_COL,
D.MEMB_DESC,
D.CREATED_DATE,
D.CREATED_BY,
D.UPDATED_BY,
D.UPDATED_DATE,
D.UPDATE_LOGIN)';
* Build dyanmic SQL to insert new members into FEM mebers table
* Only new members will be inserted into the table
*/
IF upper( FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name) = 'FEM_CCTR_ORGS_B'
THEN
FEM_ENGINES_PKG.Tech_Message
(
p_severity => pc_log_level_event
,p_module => pc_module_name||c_func_name
,p_msg_text => 'Processing dimension is of type CCTR-ORG');
pv_progress := 'Before creating dynamic GT insert for Company';
v_comp_gt_insert_stmt :=
'INSERT INTO FEM_INTG_DIM_MEMBERS_GT GT
( DIMENSION_ID
, SEGMENT1_VALUE
, SEGMENT2_VALUE
, SEGMENT3_VALUE
, SEGMENT4_VALUE
, SEGMENT5_VALUE
, CONCAT_SEGMENT_VALUE)
SELECT DISTINCT
:v_dest_dim_id
, -1
, -1
, -1
, -1
, -1
, segment1_value
FROM FEM_INTG_DIM_MEMBERS_GT GT2
WHERE GT2.dimension_id = :v_dim_id';
,p_value2 => v_comp_gt_insert_stmt);
EXECUTE IMMEDIATE v_comp_gt_insert_stmt
USING FEM_INTG_DIM_RULE_ENG_PKG.pv_com_dim_id
,FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
v_comp_insert_gt_count := SQL%ROWCOUNT;
,p_value2 => v_comp_insert_gt_count);
pv_progress := 'Before insert into fem_companies_b';
INSERT INTO fem_companies_b
(
company_id,
value_set_id,
company_display_code,
enabled_flag,
personal_flag,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
read_only_flag,
object_version_number
)
SELECT flex.FLEX_VALUE_ID
,fem_intg_dim_rule_eng_pkg.pv_com_vs_id
,tab1.concat_segment_value
,'Y'
,'N'
,SYSDATE
,pv_user_id
,pv_user_id
,SYSDATE
,pv_login_id
,'N' -- Bug 4393061 - changed read_only_flag to 'N'
,1
FROM fem_intg_dim_members_gt tab1
,fnd_flex_values flex
WHERE dimension_id = fem_intg_dim_rule_eng_pkg.pv_com_dim_id
AND flex.FLEX_VALUE_SET_ID = fem_intg_dim_rule_eng_pkg.pv_mapped_segs(1).vs_id
AND flex.flex_value = tab1.concat_segment_value
AND not exists ( SELECT 'x'
FROM fem_companies_b tab2
WHERE tab2.value_set_id = fem_intg_dim_rule_eng_pkg.pv_com_vs_id
AND tab1.concat_segment_value = tab2.company_display_code);
pv_progress := 'Before insert into fem_companies_tl';
INSERT INTO fem_companies_tl
(
company_id,
value_set_id,
language,
source_lang,
company_name,
description,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login
)
SELECT TL.FLEX_VALUE_ID
,tab1.value_set_id
,TL.language
,TL.source_lang
,tab1.company_display_code
,TL.description
,SYSDATE
,pv_user_id
,pv_user_id
,SYSDATE
,pv_login_id
FROM fem_companies_b tab1
,fnd_flex_values_tl TL
WHERE tab1.value_set_id = fem_intg_dim_rule_eng_pkg.pv_com_vs_id
AND tab1.company_id = TL.flex_value_id
AND not exists ( SELECT 'x'
FROM fem_companies_tl tab2
WHERE tab1.value_set_id = tab2.value_set_id
AND tab1.company_id = tab2.company_id
AND TL.language = tab2.language );
pv_progress := 'Before insert into fem_companies_vl';
v_insert_comp_vl_stmt := 'INSERT INTO fem_companies_vl
(
company_id,
value_set_id,
company_display_code,
enabled_flag,
personal_flag,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
read_only_flag,
object_version_number,
company_name,
description
)
SELECT FND_FLEX_VALUES_S.nextval
,:v_seg1_vs_id
,concat_segment_value
,''Y''
,''N''
,SYSDATE
,:v_user_id
,:v_user_id
,SYSDATE
,:v_login_id
,''N''
,1
,concat_segment_value
,flex.descr
FROM fem_intg_dim_members_gt tab1';
v_insert_comp_vl_stmt := v_insert_comp_vl_stmt|| ',( SELECT '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).meaning_col_name
||' DESCR ,'
||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).val_col_name
|| ' flex_value FROM '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).table_name || ' '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).where_clause || ') FLEX';
v_insert_comp_vl_stmt := v_insert_comp_vl_stmt|| '
WHERE dimension_id = :v_com_dim_id
AND flex.flex_value = tab1.concat_segment_value
AND not exists ( SELECT ''x''
FROM fem_companies_vl tab2
WHERE :v_seg1_vs_id = tab2.value_set_id
AND tab1.concat_segment_value = tab2.company_display_code)';
,p_msg_text => 'Executing SQL Statement: '||v_insert_comp_vl_stmt||
'Using: '||fem_intg_dim_rule_eng_pkg.pv_mapped_segs(1).vs_id
||','||pv_user_id
||','||pv_user_id
||','||pv_login_id
||','||fem_intg_dim_rule_eng_pkg.pv_com_dim_id
||','||fem_intg_dim_rule_eng_pkg.pv_mapped_segs(1).vs_id);
EXECUTE IMMEDIATE v_insert_comp_vl_stmt
USING fem_intg_dim_rule_eng_pkg.pv_com_vs_id,
pv_user_id,
pv_user_id,
pv_login_id,
fem_intg_dim_rule_eng_pkg.pv_com_dim_id,
fem_intg_dim_rule_eng_pkg.pv_com_vs_id;
pv_progress := 'Before building dynamic stmt for CostCenter GT INSERT';
v_cc_gt_insert_stmt :=
'INSERT INTO FEM_INTG_DIM_MEMBERS_GT GT
( DIMENSION_ID
, SEGMENT1_VALUE
, SEGMENT2_VALUE
, SEGMENT3_VALUE
, SEGMENT4_VALUE
, SEGMENT5_VALUE
, CONCAT_SEGMENT_VALUE)
SELECT DISTINCT
:v_dest_dim_id
, -1
, -1
, -1
, -1
, -1
, segment2_value
FROM FEM_INTG_DIM_MEMBERS_GT GT2
WHERE GT2.dimension_id = :v_dim_id';
pv_progress := 'Before EXECUTION of CostCenter GT INSERT';
EXECUTE IMMEDIATE v_cc_gt_insert_stmt
USING FEM_INTG_DIM_RULE_ENG_PKG.pv_cc_dim_id
,FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id;
v_cc_insert_gt_count := SQL%ROWCOUNT;
,p_value2 => v_cc_insert_gt_count);
pv_progress := 'Before insert into fem_cost_centers_b';
INSERT INTO fem_cost_centers_b
(
cost_center_id,
value_set_id,
cost_center_display_code,
enabled_flag,
personal_flag,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
read_only_flag,
object_version_number
)
SELECT flex.FLEX_VALUE_ID
,fem_intg_dim_rule_eng_pkg.pv_cc_vs_id
,segment2_value
,'Y'
,'N'
,SYSDATE
,pv_user_id
,pv_user_id
,SYSDATE
,pv_login_id
,'N'
,1
FROM fem_intg_dim_members_gt tab1
,fnd_flex_values flex
WHERE dimension_id = fem_intg_dim_rule_eng_pkg.pv_dim_id /* Because dependent VS*/
AND flex.FLEX_VALUE_SET_ID = fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).vs_id
AND flex.parent_flex_value_low = tab1.segment1_value
AND flex.flex_value = tab1.segment2_value
AND not exists ( SELECT 'x'
FROM fem_cost_centers_b tab2
WHERE tab2.value_set_id = fem_intg_dim_rule_eng_pkg.pv_cc_vs_id
AND tab1.segment2_value = tab2.cost_center_display_code); v_cc_member_b_count := SQL%ROWCOUNT;
INSERT INTO fem_cost_centers_b
(
cost_center_id,
value_set_id,
cost_center_display_code,
enabled_flag,
personal_flag,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
read_only_flag,
object_version_number
)
SELECT flex.FLEX_VALUE_ID
,fem_intg_dim_rule_eng_pkg.pv_cc_vs_id
,concat_segment_value
,'Y'
,'N'
,SYSDATE
,pv_user_id
,pv_user_id
,SYSDATE
,pv_login_id
,'N' -- Bug 4393061 - changed read_only_flag to 'N'
,1
FROM fem_intg_dim_members_gt tab1
,fnd_flex_values flex
WHERE dimension_id = fem_intg_dim_rule_eng_pkg.pv_cc_dim_id
AND flex.FLEX_VALUE_SET_ID = fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).vs_id
AND flex.flex_value = tab1.concat_segment_value
AND not exists ( SELECT 'x'
FROM fem_cost_centers_b tab2
WHERE tab2.value_set_id = fem_intg_dim_rule_eng_pkg.pv_cc_vs_id
AND tab1.concat_segment_value = tab2.cost_center_display_code);
pv_progress := 'Before insert into fem_cost_centers_tl';
INSERT INTO fem_cost_centers_tl
(
cost_center_id,
value_set_id,
language,
source_lang,
cost_center_name,
description,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login
)
SELECT TL.FLEX_VALUE_ID
,tab1.value_set_id
,TL.language
,TL.source_lang
,tab1.cost_center_display_code
,TL.description
,SYSDATE
,pv_user_id
,pv_user_id
,SYSDATE
,pv_login_id
FROM fem_cost_centers_b tab1
,fnd_flex_values_tl TL
WHERE tab1.value_set_id = fem_intg_dim_rule_eng_pkg.pv_cc_vs_id
AND tab1.cost_center_id = TL.flex_value_id
AND not exists ( SELECT 'x'
FROM fem_cost_centers_tl tab2
WHERE tab1.value_set_id = tab2.value_set_id
AND tab1.cost_center_id = tab2.cost_center_id
AND TL.language = tab2.language );
pv_progress := 'Before insert into fem_cost_centers_vl';
v_insert_cc_vl_stmt := 'INSERT INTO fem_cost_centers_vl
(
cost_center_id,
value_set_id,
cost_center_display_code,
enabled_flag,
personal_flag,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
read_only_flag,
object_version_number,
cost_center_name,
description
)
SELECT FND_FLEX_VALUES_S.nextval
,:v_seg2_vs_id
,concat_segment_value
,''Y''
,''N''
,SYSDATE
,:v_user_id
,:v_user_id
,SYSDATE
,:v_login_id
,''N''
,1
,concat_segment_value
,flex.descr
FROM fem_intg_dim_members_gt tab1';
v_insert_cc_vl_stmt := v_insert_cc_vl_stmt|| ',( SELECT '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).meaning_col_name
||' DESCR ,'
||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).val_col_name
|| ' flex_value FROM '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).table_name || ' '
|| FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).where_clause || ') FLEX';
v_insert_cc_vl_stmt := v_insert_cc_vl_stmt|| '
WHERE dimension_id = :v_cc_dim_id
AND flex.flex_value = tab1.concat_segment_value
AND not exists ( SELECT ''x''
FROM fem_cost_centers_vl tab2
WHERE :v_seg2_vs_id = tab2.value_set_id
AND tab1.concat_segment_value = tab2.cost_center_display_code)';
,p_msg_text => 'Executing SQL Statement: '||v_insert_cc_vl_stmt||
'Using: '||fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).vs_id
||','||pv_user_id
||','||pv_user_id
||','||pv_login_id
||','||fem_intg_dim_rule_eng_pkg.pv_cc_dim_id
||','||fem_intg_dim_rule_eng_pkg.pv_mapped_segs(2).vs_id);
EXECUTE IMMEDIATE v_insert_cc_vl_stmt
USING fem_intg_dim_rule_eng_pkg.pv_cc_vs_id,
pv_user_id,
pv_user_id,
pv_login_id,
fem_intg_dim_rule_eng_pkg.pv_cc_dim_id,
fem_intg_dim_rule_eng_pkg.pv_cc_vs_id;
pv_progress := 'Before update of fem_intg_dim_rule_defs.max_flex_value_id_processed';
UPDATE fem_intg_dim_rule_defs
SET max_flex_value_id_processed
= FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
WHERE dim_rule_obj_def_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_rule_obj_def_id;
,p_msg_text => 'Update fem_intg_dim_rule_defs.max_flex_value_id_processed'||
'with '||FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped);
NVL(v_main_insert_gt_count,0)
+ NVL(v_comp_insert_gt_count,0)
+ NVL(v_cc_insert_gt_count,0)
+ NVL(v_comp_member_b_count,0)
+ NVL(v_comp_member_vl_count,0)
+ NVL(v_cc_member_b_count,0)
+ NVL(v_comp_member_tl_count,0)
+ NVL(v_cc_member_tl_count,0)
+ NVL(v_cc_member_vl_count,0)
+ NVL(v_insert_member_b_count,0)
+ NVL(v_merge_count,0)
+ NVL(v_insert_member_vl_count,0)
+ NVL(v_comp_member_vl_count,0)
+ NVL(v_attr_row_count, 0);
* Get the columns to be updated
*/
pv_progress := 'Before building map table dynamic update stmt';
/* UPDATE MAPPING TABLE
* =======================
*
* Build dyanmic SQL to insert new members into FEM mebers table
* Only new members will be inserted into the table
*/
IF FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label = 'INTERCOMPANY'
THEN
v_column_list := FEM_INTG_DIM_RULE_ENG_PKG.pv_member_col;
,p_msg_text => 'Columns '||v_column_list||' will be updated in mapping table');
v_upd_map_table_stmt := 'UPDATE fem_intg_ogl_ccid_map fiocm
SET ' || v_column_list || ' = (
SELECT ' || v_value_list || '
FROM '|| FEM_INTG_DIM_RULE_ENG_PKG.pv_member_vl_object_name||' member_table
, gl_code_combinations GCC
WHERE GCC.code_combination_id = fiocm.code_combination_id
AND member_table.value_set_id = :v_fem_vs_id
AND member_table.'||FEM_INTG_DIM_RULE_ENG_PKG.pv_member_display_code_col
||' = ';
(select attr.dim_attribute_varchar_member
from fem_nat_accts_attr attr
,fem_nat_accts_b b
,gl_code_combinations g
where attr.value_set_id = b.value_set_id
and attr.natural_account_id = b.natural_account_id
and attr.value_set_id = :v_fem_vs_id
and g.chart_of_accounts_id = :pv_coa_id
and attr.attribute_id = :v_ext_acct_type_attr_id
and attr.version_id = :v_ext_acct_type_ver_id
and b.natural_account_display_code = g.'
||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(1).application_column_name ||'||''-''
||g.'||FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(2).application_column_name;
pv_progress := 'Before executing update map';
pv_progress := 'after executing update map';
SELECT 1
INTO v_dim_rule_req_count
FROM dual
WHERE EXISTS ( SELECT 1
FROM fnd_concurrent_programs fcp,
fnd_concurrent_requests fcr,
fem_intg_dim_rules idr,
fem_object_definition_b fodb
WHERE fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcp.application_id = fcr.program_application_id
AND fcp.application_id = 274
AND fcp.concurrent_program_name = 'FEM_INTG_DIM_RULE_ENGINE'
AND fcr.phase_code <> 'C'
AND idr.dim_rule_obj_id = fodb.object_id
AND idr.chart_of_accounts_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id
--Start bug fix 5560443
AND idr.dimension_id <> 0
--End bug fix 5560443
AND fcr.argument1 = fodb.object_definition_id
AND fcr.argument2 = 'MEMBER');
select nvl(value,1)*2 no_of_workers
into v_Num_Workers
from v$parameter
where name = 'cpu_count';
ad_parallel_updates_pkg.purge_processed_units
(X_owner => 'FEM',
X_table => 'FEM_INTG_OGL_CCID_MAP',
X_script => FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id);
ad_parallel_updates_pkg.delete_update_information
(X_update_type => ad_parallel_updates_pkg.ROWID_RANGE,
X_owner => 'FEM',
X_table => 'FEM_INTG_OGL_CCID_MAP',
X_script => FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id);
UPDATE FEM_INTG_DIM_RULE_DEFS
SET MAX_CCID_PROCESSED = FEM_INTG_DIM_RULE_ENG_PKG.pv_max_ccid_to_be_mapped
WHERE DIM_RULE_OBJ_DEF_ID IN ( SELECT defs.dim_rule_obj_def_id
FROM fem_intg_dim_rules idr,
fem_object_definition_b fodb,
fem_xdim_dimensions fxd,
fem_intg_dim_rule_defs defs,
fem_tab_columns_b ftcb
WHERE ftcb.table_name = 'FEM_BALANCES'
AND ftcb.fem_data_type_code = 'DIMENSION'
AND ftcb.dimension_id = fxd.dimension_id
AND DECODE(ftcb.column_name,'INTERCOMPANY_ID', 0, fxd.dimension_id) = idr.dimension_id
AND idr.chart_of_accounts_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id
AND idr.dim_rule_obj_id = fodb.object_id
AND defs.dim_rule_obj_def_id = fodb.object_definition_id
AND defs.dim_mapping_option_code IN ('SINGLESEG','SINGLEVAL') );
p_module => pc_module_name || '.cnt_update_FEM_INTG_DIM_RULE_DEFS',
p_msg_text => v_rows_processed
);
OPEN fch_vs_cursor FOR v_fch_vs_select_stmt USING FEM_INTG_DIM_RULE_ENG_PKG.pv_fem_vs_id;
program => 'FCH_UPDATE_ENTITY_ORGS',
sub_request => FALSE);
p_msg_text => 'Submitted Update Entity Organizations Request ' || v_request_id
);
'INSERT INTO fem_intg_dim_members_gt
(dimension_id,
segment1_value,
segment2_value,
segment3_value,
segment4_value,
segment5_value,
concat_segment_value)
SELECT DISTINCT
'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_id||',
hgt.child_display_code,
''-1'',
''-1'',
''-1'',
''-1'',
hgt.child_display_code
FROM FEM_INTG_DIM_HIER_GT hgt,
FND_FLEX_VALUES ff
WHERE ff.flex_value_set_id = '||FEM_INTG_HIER_RULE_ENG_PKG.pv_aol_vs_id||'
AND ff.flex_value = hgt.child_display_code';
USING (SELECT gt.concat_segment_value,
ffv.flex_value_id
FROM fem_intg_dim_members_gt gt,
fnd_flex_values ffv
WHERE ffv.flex_value_set_id = :pv_aol_vs_id
AND ffv.flex_value = gt.concat_segment_value) s
ON (b.'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_col||' =s.flex_value_id
AND b.value_set_id = :pv_dim_vs_id)
WHEN MATCHED THEN UPDATE
SET b.last_update_date = SYSDATE
WHEN NOT MATCHED THEN
INSERT
(b.'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_col||',
b.value_set_id,
b.dimension_group_id,
b.'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_disp_col||',
b.enabled_flag,
b.personal_flag,
b.creation_date,
b.created_by,
b.last_updated_by,
b.last_update_login,
b.last_update_date,
b.read_only_flag,
b.object_version_number)
VALUES
(s.flex_value_id,
:pv_dim_vs_id,
NULL,
s.concat_segment_value,
''Y'',
''N'',
SYSDATE,
:pv_user_id,
:pv_user_id,
:pv_login_id,
SYSDATE,
''N'',
1)';
USING (SELECT tl.flex_value_id, ffv.flex_value,
tl.description,
tl.language, tl.source_lang
FROM fem_intg_dim_members_gt gt,
fnd_flex_values_tl tl,
fnd_flex_values ffv
WHERE tl.flex_value_id = ffv.flex_value_id
AND ffv.flex_value_set_id = :pv_aol_vs_id
AND ffv.flex_value = gt.concat_segment_value) s
ON (b.'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_col||' = s.flex_value_id
AND b.language = s.language
AND b.value_set_id = :pv_dim_vs_id)
WHEN MATCHED THEN UPDATE
SET b.last_update_date = SYSDATE
WHEN NOT MATCHED THEN
INSERT
(b.'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_col||',
b.value_set_id,
b.'||FEM_INTG_HIER_RULE_ENG_PKG.pv_dim_memb_name_col||',
b.language,
b.source_lang,
b.creation_date,
b.created_by,
b.last_updated_by,
b.last_update_login,
b.last_update_date,
b.description)
VALUES
(s.flex_value_id,
:pv_dim_vs_id,
s.flex_value,
s.language,
s.source_lang,
SYSDATE,
:pv_user_id,
:pv_user_id,
:pv_login_id,
SYSDATE,
s.description)';
SELECT nvl(sum(decode(intercompany_id,-1,1,0)),0)
,nvl(sum(decode(intercompany_id,-1,0,1)),0)
INTO v_unmapped_count
,v_mapped_count
FROM fem_intg_ogl_ccid_map
WHERE global_vs_combo_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_gvsc_id;
v_update_name VARCHAR2(30);
v_ccid_update_stmt VARCHAR2(20000);
SELECT ftcb.column_name target_col,
fxd.member_col source_col,
fxd.member_b_table_name source_b_table_name,
fxd.member_display_code_col source_display_code_col,
NVL(defs.fem_value_set_id,-1) fem_value_set_id,
defs.application_column_name1,
defs.default_member_id,
defs.dim_mapping_option_code
FROM fem_intg_dim_rules idr,
fem_object_definition_b fodb,
fem_xdim_dimensions fxd,
fem_intg_dim_rule_defs defs,
fem_tab_columns_b ftcb
WHERE ftcb.table_name = 'FEM_BALANCES'
AND ftcb.fem_data_type_code = 'DIMENSION'
AND ftcb.dimension_id = fxd.dimension_id
AND DECODE(ftcb.column_name,'INTERCOMPANY_ID', 0, fxd.dimension_id) = idr.dimension_id
AND idr.dim_rule_obj_id = fodb.object_id
AND idr.chart_of_accounts_id = p_coa_id
AND defs.dim_rule_obj_def_id = fodb.object_definition_id
AND defs.dim_mapping_option_code IN ('SINGLESEG','SINGLEVAL');
p_msg_text => 'Start of mapping table update worker id : '||p_Worker_Id
);
p_msg_text => '<< Start of mapping table update worker >>'
);
v_update_name := p_coa_id;
ad_parallel_updates_pkg.initialize_rowid_range( ad_parallel_updates_pkg.ROWID_RANGE,
v_table_owner,
v_table_name,
v_update_name,
p_Worker_Id,
p_Num_Workers,
p_batch_size,
0);
ad_parallel_updates_pkg.get_rowid_range( v_start_rowid,
v_end_rowid,
v_any_rows_to_process,
p_batch_size,
TRUE);
UPDATE FEM_INTG_OGL_CCID_MAP M SET ';
SELECT a.attribute_id
,v.version_id
INTO v_ext_acct_type_attr_id
,v_ext_acct_type_ver_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
attr.dim_attribute_varchar_member
FROM
fem_nat_accts_attr attr,
fem_nat_accts_b b,
gl_code_combinations g
WHERE
attr.value_set_id = b.value_set_id AND
attr.natural_account_id = b.natural_account_id AND
attr.value_set_id = '||v_upd_dim_list.FEM_VALUE_SET_ID||' AND
b.natural_account_display_code = g.' ||
v_upd_dim_list.APPLICATION_COLUMN_NAME1 || ' AND
g.chart_of_accounts_id = '||p_coa_id||' AND
attr.attribute_id = '||v_ext_acct_type_attr_id||' AND
attr.version_id = '||v_ext_acct_type_ver_id||' AND
g.summary_flag = ''N'' AND
m.code_combination_id = g.code_combination_id
), -1), ';
SELECT
b.' || v_upd_dim_list.SOURCE_COL || '
FROM
' || v_upd_dim_list.SOURCE_B_TABLE_NAME || ' B,
gl_code_combinations g
WHERE
b.value_set_id = '||v_upd_dim_list.FEM_VALUE_SET_ID||' AND
b.' || v_upd_dim_list.SOURCE_DISPLAY_CODE_COL ||
' = g.' || v_upd_dim_list.APPLICATION_COLUMN_NAME1 ||' AND
g.chart_of_accounts_id = '||p_coa_id||' AND
g.summary_flag = ''N'' AND
m.code_combination_id = g.code_combination_id
), -1), ';
v_ccid_update_stmt := v_upd_stmt1 || v_upd_stmt3;
v_ccid_cur_stmt := 'SELECT code_combination_id
FROM fem_intg_ogl_ccid_map
WHERE global_vs_combo_id = :pv_gvsc_id
AND code_combination_id BETWEEN :max_ccid_processed AND :max_ccid_to_be_mapped
AND rowid BETWEEN :rowid_low and :rowid_high';
IF (v_ccid_cur_stmt IS NOT NULL AND v_ccid_update_stmt IS NOT NULL) THEN
FEM_ENGINES_PKG.User_Message(
p_app_name => 'FEM',
p_msg_text => 'v_ccid_cur_stmt : '||v_ccid_cur_stmt
);
p_msg_text => 'v_ccid_update_stmt : '
);
p_msg_text => substr(v_ccid_update_stmt, v_start_pos, 4000)
);
EXIT WHEN v_start_pos > length(v_ccid_update_stmt);
EXECUTE IMMEDIATE v_ccid_update_stmt
USING p_gvsc_id, v_ccid_list(i);
ad_parallel_updates_pkg.processed_rowid_range( v_rows_processed,
v_end_rowid);
ad_parallel_updates_pkg.get_rowid_range( v_start_rowid,
v_end_rowid,
v_any_rows_to_process,
p_batch_size,
FALSE);
p_msg_text => '<< end of mapping table update worker >>'
);
p_msg_text => '<< end of mapping table update worker >>'
);