[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT coa_mapping_id,
from_coa_id,
to_coa_id,
start_date_active,
end_date_active
FROM gl_coa_mappings
WHERE name = mapping_name;
SELECT COUNT(*)
INTO g_num_account_rules
FROM gl_cons_flexfield_map
WHERE coa_mapping_id = mapping_id;
SELECT COUNT(DISTINCT to_application_column_name)
INTO g_num_segment_rules
FROM gl_cons_segment_map
WHERE coa_mapping_id = mapping_id;
SELECT COUNT(*)
INTO nRows
FROM GL_ACCTS_MAP_INT_GT;
insert_1 VARCHAR2(3000);
insert_1_col fnd_flex_validation_tables.application_table_name%TYPE;
insert_1_table fnd_flex_validation_tables.value_column_name%TYPE;
insert_1_join VARCHAR2(100);
insert_1_select_type VARCHAR2(40);
SELECT fvt.application_table_name,
fvt.value_column_name
FROM fnd_flex_validation_tables fvt,
fnd_flex_value_sets fvs
WHERE fvs.flex_value_set_id = from_value_set_id
AND fvs.validation_type = 'F'
AND fvs.flex_value_set_id = fvt.flex_value_set_id;
SELECT decode(max(decode(map.segment_map_type, 'P', 1, 0)),
1, 'Y', 'N'),
decode(max(decode(map.segment_map_type, 'R', 1, 0)),
1, 'Y', 'N'),
decode(max(decode(map.segment_map_type, 'U', 1, 0)),
1, 'Y', 'N'),
decode(max(decode(map.segment_map_type, 'V', 1, 0)),
1, 'Y', 'N')
INTO detail_parent_flag,
detail_ranges_flag,
summary_ranges_flag,
summary_parent_flag
FROM GL_CONS_SEGMENT_MAP map
WHERE map.coa_mapping_id = mapping_id
AND map.to_application_column_name =
interim_rollup_map.to_application_column_name;
EXECUTE IMMEDIATE 'DELETE FROM GL_ACCTS_MAP_SEG' ||
substr(to_application_column_name,8,2) || '_GT';
insert_1 := 'INSERT INTO GL_ACCTS_MAP_SEG' ||
substr(to_application_column_name,8,2) || '_GT (';
insert_1_col := val_column_name;
insert_1_table := val_table_name;
insert_1_join := '';
insert_1_select_type := 'SELECT DISTINCT';
insert_1_col := 'flex_value';
insert_1_table := 'fnd_flex_values';
insert_1_join := 'AND GL_FV.flex_value_set_id = GL_CSM.from_value_set_id ' ||
'AND GL_FV.summary_flag = ''N'' ';
insert_1_select_type := 'SELECT DISTINCT';
insert_1 := insert_1 || insert_1_select_type || ' GL_FV.' || insert_1_col ||
' source_flex_value, ' ||
'GL_CSM.single_value target_flex_value, ''N'' summary_flag ' ||
'FROM '|| insert_1_table || ' GL_FV, ' ||
'fnd_flex_value_hierarchies FVH, gl_cons_segment_map GL_CSM ' ||
'WHERE GL_CSM.coa_mapping_id = ' || TO_CHAR(mapping_id) ||
' AND GL_CSM.to_application_column_name = ''' ||
to_application_column_name || ''' ' ||
'AND GL_CSM.parent_rollup_value = FVH.parent_flex_value ' ||
'AND GL_CSM.segment_map_type = ''P'' ' ||
'AND FVH.flex_value_set_id = GL_CSM.from_value_set_id ' ||
insert_1_join ||
'AND GL_FV.' || insert_1_col || ' BETWEEN ' ||
'FVH.child_flex_value_low AND FVH.child_flex_value_high';
insert_1 := insert_1 || ' UNION ';
insert_1 := insert_1 || insert_1_select_type || ' GL_FV.' || insert_1_col ||
' source_flex_value, ' ||
'GL_CSM.single_value target_flex_value, ''N'' summary_flag ' ||
'FROM ' || insert_1_table || ' GL_FV, ' ||
'gl_cons_flex_hierarchies CFH, gl_cons_segment_map GL_CSM ' ||
'WHERE GL_CSM.coa_mapping_id = ' || TO_CHAR(mapping_id) ||
' AND GL_CSM.to_application_column_name = ''' ||
to_application_column_name || ''' ' ||
'AND GL_CSM.segment_map_id = CFH.segment_map_id ' ||
'AND GL_CSM.single_value = CFH.parent_flex_value ' ||
'AND GL_CSM.segment_map_type = ''R'' ' ||
insert_1_join ||
'AND GL_FV.' || insert_1_col || ' BETWEEN ' ||
'CFH.child_flex_value_low AND CFH.child_flex_value_high';
insert_1 := insert_1 || ' UNION ';
insert_1 := insert_1 ||
'SELECT GL_CSM.parent_rollup_value source_flex_value, ' ||
'GL_CSM.single_value target_flex_value, ''Y'' summary_flag ' ||
'FROM gl_cons_segment_map GL_CSM ' ||
'WHERE GL_CSM.coa_mapping_id = ' || TO_CHAR(mapping_id) ||
' AND GL_CSM.to_application_column_name = ''' ||
to_application_column_name || ''' ' ||
'AND GL_CSM.segment_map_type = ''V''';
insert_1 := insert_1 || ' UNION ';
insert_1 := insert_1 || 'SELECT GL_FV.flex_value source_flex_value, ' ||
'GL_CSM.single_value target_flex_value, ''Y'' summary_flag ' ||
'FROM fnd_flex_values GL_FV, gl_cons_flex_hierarchies CFH, ' ||
'gl_cons_segment_map GL_CSM ' ||
'WHERE GL_CSM.coa_mapping_id = ' || TO_CHAR(mapping_id) ||
' AND GL_CSM.to_application_column_name = ''' ||
to_application_column_name || ''' ' ||
'AND GL_CSM.segment_map_id = CFH.segment_map_id ' ||
'AND GL_CSM.single_value = CFH.parent_flex_value ' ||
'AND GL_CSM.segment_map_type = ''U'' ' ||
'AND GL_FV.flex_value_set_id = GL_CSM.from_value_set_id ' ||
'AND GL_FV.summary_flag = ''Y'' ' ||
'AND GL_FV.flex_value BETWEEN CFH.child_flex_value_low ' ||
'AND CFH.child_flex_value_high';
insert_1 := insert_1 || ')';
writedebug('insert statement for rollup rules is: ');
FOR i IN 0..(lengthb(insert_1)-1)/2000 LOOP
writedebug(SUBSTRB(insert_1, i*2000+1, 2000));
EXECUTE IMMEDIATE insert_1;
update_1 VARCHAR2(600);
select_1 VARCHAR2(1500);
SELECT 'Y'
FROM gl_cons_segment_map
WHERE coa_mapping_id = mapping_id
AND segment_map_type IN ('V','U');
SELECT max(map.segment_map_id) SEGMENT_MAP_ID,
decode(max(map.segment_map_type), '', 'N',
'C', 'C',
'S', 'S',
'P', 'R',
'R', 'R',
'V', 'R',
'U', 'R',
'N')
SEGMENT_MAP_TYPE,
max(ffs1.application_column_name) TO_APPLICATION_COLUMN_NAME,
ffs1.segment_num TO_SEGMENT_NUM,
ffs1.flex_value_set_id TO_VALUE_SET_ID,
ffs2.application_column_name FROM_APPLICATION_COLUMN_NAME,
ffs2.segment_num FROM_SEGMENT_NUM,
ffs2.flex_value_set_id FROM_VALUE_SET_ID,
decode(max(map.segment_map_type), 'S', max(map.single_value),
NULL)
SINGLE_VALUE
FROM FND_ID_FLEX_SEGMENTS ffs2,
GL_CONS_SEGMENT_MAP map,
FND_ID_FLEX_SEGMENTS ffs1
WHERE ffs1.application_id = g_application_id
AND ffs1.id_flex_code = g_id_flex_code
AND ffs1.enabled_flag = 'Y'
AND ffs1.id_flex_num = to_coa_id
AND map.to_value_set_id (+)= ffs1.flex_value_set_id
AND map.to_application_column_name (+)= ffs1.application_column_name
AND map.coa_mapping_id (+)= mapping_id
AND ffs2.application_id (+)= g_application_id
AND ffs2.id_flex_code (+)= g_id_flex_code
AND ffs2.enabled_flag (+)= 'Y'
AND ffs2.id_flex_num (+)= from_coa_id
AND ffs2.application_column_name (+)=
nvl(map.from_application_column_name, -1)
AND ffs2.flex_value_set_id (+)= nvl(map.from_value_set_id, -1)
GROUP BY map.coa_mapping_id,
ffs1.segment_num,
ffs1.flex_value_set_id,
ffs2.application_column_name,
ffs2.segment_num,
ffs2.flex_value_set_id
ORDER BY ffs1.segment_num;
SELECT ffs.application_column_name UNMAPPED_FROM_SEGMENT
FROM FND_ID_FLEX_SEGMENTS ffs
WHERE ffs.application_id = g_application_id
AND ffs.id_flex_code = g_id_flex_code
AND ffs.enabled_flag = 'Y'
AND ffs.id_flex_num = from_coa_id
AND ffs.application_column_name NOT IN (
SELECT map.from_application_column_name
FROM GL_CONS_SEGMENT_MAP map
WHERE map.coa_mapping_id = mapping_id
AND map.from_application_column_name IS NOT NULL
);
update_1 := 'UPDATE GL_ACCTS_MAP_INT_GT map ' ||
'SET (from_summary_flag, to_ccid';
select_1 := 'SELECT from_cc.summary_flag, null';
update_1 := update_1 || ', to_' || to_app_col_name;
select_1 := select_1 || ', from_cc.' || from_app_col_name;
'(EXISTS (SELECT ''X'' FROM GL_SUMMARY_TEMPLATES st ' ||
'WHERE st.template_id = from_cc.template_id';
select_1 := select_1 || ', ''' || seg_rule.single_value || '''';
select_1 := select_1 || ', int_' || to_app_col_name ||
'.target_flex_value';
update_1 := update_1 || ') ';
writedebug('This is the update statement for segment map: ');
FOR i IN 0..(lengthb(update_1 || '= ('|| select_1 || from_1 || where_1 || ') ' || where_2)-1)/2000 LOOP
writedebug(SUBSTRB(update_1 || '= ('|| select_1 || from_1 || where_1 || ') ' || where_2, i*2000+1, 2000));
update_1 || '= ('|| select_1 || from_1 || where_1 || ') ' || where_2;
update_1 VARCHAR2(500);
select_1 VARCHAR2(1000);
SELECT ffs1.application_column_name TO_APPLICATION_COLUMN_NAME
FROM FND_ID_FLEX_SEGMENTS ffs1
WHERE ffs1.application_id = g_application_id
AND ffs1.id_flex_code = g_id_flex_code
AND ffs1.enabled_flag = 'Y'
AND ffs1.id_flex_num = to_coa_id
ORDER BY ffs1.segment_num;
SELECT ffs1.application_column_name FROM_APPLICATION_COLUMN_NAME
FROM FND_ID_FLEX_SEGMENTS ffs1
WHERE ffs1.application_id = g_application_id
AND ffs1.id_flex_code = g_id_flex_code
AND ffs1.enabled_flag = 'Y'
AND ffs1.id_flex_num = from_coa_id
ORDER BY ffs1.segment_num;
update_1 := 'UPDATE /*+ cardinality(map 1) */ GL_ACCTS_MAP_INT_GT map ' ||
'SET(from_summary_flag, to_ccid';
select_1 := 'SELECT from_cc.summary_flag, to_cc.code_combination_id';
' AND EXISTS (SELECT ''X'' ' ||
'FROM gl_cons_flexfield_map cons_flex, ' ||
'gl_code_combinations cc1 WHERE ';
update_1 := update_1 || ', to_' || app_col_name;
select_1 := select_1 || ', to_cc.' || app_col_name;
update_1 := update_1 || ') = ';
select_1 := select_1 || ' FROM gl_cons_flexfield_map f, ' ||
'gl_code_combinations from_cc, ' ||
'gl_code_combinations to_cc ';
writedebug('This is the update statement for account map: ');
FOR i IN 0..(lengthb(update_1 || '(' || select_1 || where_1 || ') ' || where_2)-1)/2000 LOOP
writedebug(SUBSTRB(update_1 || '(' || select_1 || where_1 || ') ' || where_2, i*2000+1, 2000));
EXECUTE IMMEDIATE update_1 || '(' || select_1 || where_1 || ') ' || where_2;
update_1 VARCHAR2(8000);
update_2 VARCHAR2(4000);
SELECT ffs1.application_column_name TO_APPLICATION_COLUMN_NAME
FROM FND_ID_FLEX_SEGMENTS ffs1
WHERE ffs1.application_id = g_application_id
AND ffs1.id_flex_code = g_id_flex_code
AND ffs1.enabled_flag = 'Y'
AND ffs1.id_flex_num = to_coa_id
ORDER BY ffs1.segment_num;
SELECT from_ccid
FROM GL_ACCTS_MAP_INT_GT map
WHERE map.coa_mapping_id = mapping_id
AND map.to_ccid IS NULL
AND map.from_summary_flag IS NOT NULL;
update_1 := 'UPDATE GL_ACCTS_MAP_INT_GT map SET to_ccid = ' ||
'(SELECT to_cc.code_combination_id ' ||
'FROM gl_code_combinations to_cc ' ||
'WHERE to_cc.chart_of_accounts_id = ' || TO_CHAR(to_coa_id);
update_2 := 'UPDATE GL_ACCTS_MAP_INT_GT map SET to_ccid = ' ||
'(SELECT cc.code_combination_id ' ||
'FROM GL_CODE_COMBINATIONS cc ' ||
'WHERE cc.chart_of_accounts_id = ' || TO_CHAR(to_coa_id);
update_1 := update_1 || ' AND to_cc.' || app_col_name ||
' (+) = map.TO_' || app_col_name;
update_2 := update_2 || ' AND cc.' || app_col_name ||
' = map.TO_' || app_col_name;
update_1 := update_1 || '), error_code = ' ||
'(SELECT decode(NVL(map.from_summary_flag, ''X''), ''X'', ' ||
'decode(COUNT(*), 0, ''INVALID_FROM_CCID'', ' ||
'''NO_MAPPING'')) ' ||
'FROM GL_CODE_COMBINATIONS from_cc ' ||
'WHERE from_cc.code_combination_id = map.from_ccid ' ||
'AND from_cc.chart_of_accounts_id = ' ||
TO_CHAR(from_coa_id) || ') ' ||
'WHERE map.coa_mapping_id = ' || to_char(mapping_id) ||
' AND map.to_ccid IS NULL ' ||
'AND map.from_summary_flag IS NULL';
update_2 := update_2 || ') ' ||
'WHERE map.coa_mapping_id = ' || to_char(mapping_id) ||
' AND map.to_ccid IS NULL';
writedebug('This updates the error codes as necessary: ');
FOR i IN 0..(lengthb(update_1)-1)/2000 LOOP
writedebug(SUBSTRB(update_1, i*2000+1, 2000));
EXECUTE IMMEDIATE update_1;
'SELECT TO_' || app_col_name_list(i) ||
' FROM GL_ACCTS_MAP_INT_GT ' ||
'WHERE coa_mapping_id = :1 ' ||
' AND from_ccid = :2 '
INTO new_flex_combination(i)
USING IN mapping_id, IN missing_account.from_ccid;
writedebug('This update statement populates the target ccids: ');
FOR i IN 0..(lengthb(update_2)-1)/2000 LOOP
writedebug(SUBSTRB(update_2, i*2000+1, 2000));
EXECUTE IMMEDIATE update_2;
UPDATE GL_ACCTS_MAP_INT_GT map
SET error_code = 'UNABLE_TO_CREATE_NEW_CCID'
WHERE coa_mapping_id = mapping_id
AND to_ccid IS NULL
AND error_code IS NULL;
SELECT coa_mapping_id,
from_coa_id,
to_coa_id,
start_date_active,
end_date_active
FROM gl_coa_mappings
WHERE name = p_mapping_name;
SELECT application_column_name
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = c_coa_id
AND segment_attribute_type = p_qualifier
AND attribute_value = 'Y';
SELECT csm.segment_map_type,
csm.from_application_column_name,
csm.single_value
FROM gl_cons_segment_map csm
WHERE csm.coa_mapping_id = c_mapping_id
AND csm.to_application_column_name = c_to_segment;
UPDATE GL_ACCTS_MAP_BSV_GT
SET target_bsv = p_single_value;
UPDATE GL_ACCTS_MAP_BSV_GT
SET target_bsv = source_bsv;
'UPDATE GL_ACCTS_MAP_BSV_GT bm ' ||
'SET target_bsv = ' ||
'(SELECT ami.target_flex_value ' ||
'FROM GL_ACCTS_MAP_SEG' || substr(p_to_segment,8,2) || '_GT ami ' ||
'WHERE ami.source_flex_value = bm.source_bsv)';