The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NULL
INTO l_vc2
FROM fnd_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
FUNCTION update_fnd_columns(p_col IN col_type,
p_flexfield_usage_code IN VARCHAR2,
p_flexfield_application_id IN NUMBER,
p_flexfield_name IN VARCHAR2,
x_message OUT nocopy VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
UPDATE fnd_columns SET
flexfield_usage_code = p_flexfield_usage_code,
flexfield_application_id = p_flexfield_application_id,
flexfield_name = p_flexfield_name,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = p_col.application_id
AND table_id = p_col.table_id
AND column_id = p_col.column_id;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_COLUMNS. ' || Sqlerrm;
END update_fnd_columns;
l_message := 'Unable to select from ' || p_table_name || g_newline ||
Rpad(Upper(p_key1),31,' ') || ':''' || p_value1 || '''';
RETURN(text_return('Unable to select from ' || p_table_name ||
g_newline || 'SQLERRM: ' || Sqlerrm));
SELECT *
INTO x_tbl
FROM fnd_tables
WHERE application_id = p_application_id
AND table_name = p_table_name;
SELECT *
INTO x_col
FROM fnd_columns
WHERE application_id = p_tbl.application_id
AND table_id = p_tbl.table_id
AND column_name = p_column_name;
SELECT *
INTO x_vst_set
FROM fnd_flex_value_sets
WHERE flex_value_set_id = p_flex_value_set_id;
SELECT *
INTO x_vst_tbl
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = p_vst_set.flex_value_set_id;
SELECT *
INTO x_vst_evt
FROM fnd_flex_validation_events
WHERE flex_value_set_id = p_vst_set.flex_value_set_id
AND event_code = p_event_code;
SELECT *
INTO x_vst_scr
FROM fnd_flex_value_rules
WHERE flex_value_set_id = p_vst_set.flex_value_set_id
AND flex_value_rule_id = p_flex_value_rule_id;
SELECT *
INTO x_vst_scl
FROM fnd_flex_value_rule_lines
WHERE flex_value_set_id = p_vst_scr.flex_value_set_id
AND flex_value_rule_id = p_vst_scr.flex_value_rule_id
AND include_exclude_indicator = p_include_exclude_indicator
AND Nvl(flex_value_low, '$FLEX$.NULL') =
Nvl(p_flex_value_low, '$FLEX$.NULL')
AND Nvl(flex_value_high, '$FLEX$.NULL') =
Nvl(p_flex_value_high, '$FLEX$.NULL');
SELECT *
INTO x_vst_scu
FROM fnd_flex_value_rule_usages
WHERE flex_value_set_id = p_vst_scr.flex_value_set_id
AND flex_value_rule_id = p_vst_scr.flex_value_rule_id
AND application_id = p_application_id
AND responsibility_id = p_responsibility_id;
SELECT *
INTO x_vst_rlg
FROM fnd_flex_hierarchies
WHERE flex_value_set_id = p_vst_set.flex_value_set_id
AND hierarchy_id = p_hierarchy_id;
SELECT *
INTO x_vst_val
FROM fnd_flex_values
WHERE flex_value_set_id = p_vst_set.flex_value_set_id
AND flex_value_id = p_flex_value_id;
SELECT *
INTO x_dff_flx
FROM fnd_descriptive_flexs
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name;
SELECT *
INTO x_dff_ctx
FROM fnd_descr_flex_contexts
WHERE application_id = p_dff_flx.application_id
AND descriptive_flexfield_name = p_dff_flx.descriptive_flexfield_name
AND descriptive_flex_context_code = p_descriptive_flex_context_cod;
SELECT *
INTO x_dff_seg
FROM fnd_descr_flex_column_usages
WHERE application_id = p_dff_ctx.application_id
AND descriptive_flexfield_name = p_dff_ctx.descriptive_flexfield_name
AND descriptive_flex_context_code =p_dff_ctx.descriptive_flex_context_code
AND application_column_name = p_application_column_name;
SELECT *
INTO x_kff_flx
FROM fnd_id_flexs
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code;
SELECT *
INTO x_kff_str
FROM fnd_id_flex_structures
WHERE application_id = p_kff_flx.application_id
AND id_flex_code = p_kff_flx.id_flex_code
AND id_flex_num = p_id_flex_num;
SELECT *
INTO x_kff_seg
FROM fnd_id_flex_segments
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num
AND application_column_name = p_application_column_name;
SELECT *
INTO x_kff_flq
FROM fnd_segment_attribute_types
WHERE application_id = p_kff_flx.application_id
AND id_flex_code = p_kff_flx.id_flex_code
AND segment_attribute_type = p_segment_attribute_type;
SELECT *
INTO x_kff_sgq
FROM fnd_value_attribute_types
WHERE application_id = p_kff_flq.application_id
AND id_flex_code = p_kff_flq.id_flex_code
AND segment_attribute_type = p_kff_flq.segment_attribute_type
AND value_attribute_type = p_value_attribute_type;
SELECT *
INTO x_kff_qlv
FROM fnd_segment_attribute_values
WHERE application_id = p_kff_seg.application_id
AND id_flex_code = p_kff_seg.id_flex_code
AND id_flex_num = p_kff_seg.id_flex_num
AND application_column_name = p_kff_seg.application_column_name
AND segment_attribute_type = p_kff_flq.segment_attribute_type;
SELECT *
INTO x_kff_sha
FROM fnd_shorthand_flex_aliases
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num
AND alias_name = p_alias_name;
SELECT *
INTO x_kff_cvr
FROM fnd_flex_validation_rules
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num
AND flex_validation_rule_name = p_flex_validation_rule_name;
SELECT *
INTO x_kff_cvl
FROM fnd_flex_validation_rule_lines
WHERE application_id = p_kff_cvr.application_id
AND id_flex_code = p_kff_cvr.id_flex_code
AND id_flex_num = p_kff_cvr.id_flex_num
AND flex_validation_rule_name = p_kff_cvr.flex_validation_rule_name
AND rule_line_id = p_rule_line_id;
SELECT *
INTO x_kff_cvi
FROM fnd_flex_include_rule_lines
WHERE application_id = p_kff_cvr.application_id
AND id_flex_code = p_kff_cvr.id_flex_code
AND id_flex_num = p_kff_cvr.id_flex_num
AND flex_validation_rule_name = p_kff_cvr.flex_validation_rule_name
AND rule_line_id = p_rule_line_id;
SELECT *
INTO x_kff_cve
FROM fnd_flex_exclude_rule_lines
WHERE application_id = p_kff_cvr.application_id
AND id_flex_code = p_kff_cvr.id_flex_code
AND id_flex_num = p_kff_cvr.id_flex_num
AND flex_validation_rule_name = p_kff_cvr.flex_validation_rule_name
AND rule_line_id = p_rule_line_id;
SELECT *
INTO x_kff_fwp
FROM fnd_flex_workflow_processes
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num
AND wf_item_type = p_wf_item_type;
SELECT name
INTO l_return
FROM v$database
WHERE ROWNUM = 1;
SELECT release_name
INTO l_return
FROM fnd_product_groups
WHERE rownum = 1;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_sql := ('SELECT creation_date, created_by,' ||
' last_update_date, last_updated_by, last_update_login' ||
' FROM ' || p_table_name ||
' WHERE ROWID = :B1');
l_last_update_date, l_last_updated_by, l_last_update_login
USING p_rowid;
l_last_update_date, l_last_updated_by,
l_last_update_login)));
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
RETURN(line_return('CD:' || To_char(p_creation_date, 'YYYY/MM/DD') ||
' CB:' || To_char(p_created_by) ||
' LUD:' || To_char(p_last_update_date, 'YYYY/MM/DD') ||
' LUB:' || To_char(p_last_updated_by) ||
' LUL:' || To_char(p_last_update_login)));
SELECT To_char(application_id) || '/' ||
application_short_name || '/' ||
application_name
INTO l_return
FROM fnd_application_vl
WHERE application_id = p_application_id;
SELECT ft.table_name || '/' ||
To_char(table_id)
INTO l_return
FROM fnd_tables ft
WHERE ft.application_id = p_application_id
AND ft.table_name = p_table_name;
SELECT us.table_owner || '/' || l_return
INTO l_return
FROM user_synonyms us
WHERE us.synonym_name = p_table_name;
SELECT column_name || '/' ||
To_char(column_id) || '/' ||
column_type || '/' ||
To_char(width) || '/' ||
flexfield_usage_code || '/' ||
Nvl(To_char(flexfield_application_id), '') || '/' ||
Nvl(flexfield_name, '')
INTO l_return
FROM fnd_columns
WHERE ((application_id, table_id) =
(SELECT application_id, table_id
FROM fnd_tables
WHERE application_id = p_application_id
AND table_name = p_table_name))
AND column_name = p_column_name;
SELECT language_code || '/' ||
installed_flag || '/' ||
nls_language || '/' ||
nls_territory
INTO l_return
FROM fnd_languages
WHERE language_code = p_language_code;
SELECT To_char(responsibility_id) || '/' ||
responsibility_key || '/' ||
responsibility_name
INTO l_return
FROM fnd_responsibility_vl
WHERE application_id = p_application_id
AND responsibility_id = p_responsibility_id;
SELECT function_code || '/' ||
function_name || '/' ||
description
INTO l_return
FROM fnd_flexbuilder_functions
WHERE application_id = p_application_id
AND function_code = p_function_code;
SELECT flexfield_application_id
INTO l_kff_app_id
FROM fnd_flexbuilder_functions
WHERE application_id = p_application_id
AND function_code = p_function_code;
SELECT flexfield_application_id, id_flex_code
INTO l_kff_app_id, l_kff_code
FROM fnd_flexbuilder_functions
WHERE application_id = p_application_id
AND function_code = p_function_code;
SELECT flexfield_application_id, id_flex_code
INTO l_kff_app_id, l_kff_code
FROM fnd_flexbuilder_functions
WHERE application_id = p_application_id
AND function_code = p_function_code;
SELECT To_char(flex_value_set_id) || '/' ||
flex_value_set_name || '/' ||
validation_type || '/' ||
format_type || '/' ||
To_char(maximum_size) || '/' ||
Nvl(description, '')
INTO l_return
FROM fnd_flex_value_sets
WHERE flex_value_set_id = p_flex_value_set_id;
SELECT application_table_name || '/' ||
value_column_name || '/' ||
value_column_type || '/' ||
value_column_size || '/' ||
Nvl(id_column_name,'') || '/' ||
Nvl(id_column_type,'') || '/' ||
Nvl(To_char(id_column_size),'')
INTO l_return
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = p_flex_value_set_id;
SELECT fve.event_code || '/' ||
fl.meaning || '/',
fve.user_exit
INTO l_return, l_user_exit
FROM fnd_flex_validation_events fve, fnd_lookups fl
WHERE fl.lookup_type = 'FLEX_VALIDATION_EVENTS'
AND fl.lookup_code = fve.event_code
AND fve.flex_value_set_id = p_flex_value_set_id
AND fve.event_code = p_event_code;
SELECT flex_value_rule_id || '/' ||
flex_value_rule_name || '/' ||
Nvl(parent_flex_value_low,'') || '/' ||
error_message
INTO l_return
FROM fnd_flex_value_rules_vl
WHERE flex_value_set_id = p_flex_value_set_id
AND flex_value_rule_id = p_flex_value_rule_id;
SELECT include_exclude_indicator || '/' ||
Nvl(parent_flex_value_low,'') || '/' ||
Nvl(flex_value_low, '') || '/' ||
Nvl(flex_value_high, '')
INTO l_return
FROM fnd_flex_value_rule_lines
WHERE flex_value_set_id = p_flex_value_set_id
AND flex_value_rule_id = p_flex_value_rule_id
AND include_exclude_indicator = p_include_exclude_indicator
AND Nvl(flex_value_low, '$FLEX$.NULL') =
Nvl(p_flex_value_low, '$FLEX$.NULL')
AND Nvl(flex_value_high, '$FLEX$.NULL') =
Nvl(p_flex_value_high, '$FLEX$.NULL');
SELECT To_char(flex_value_rule_id) || '/' ||
To_char(application_id) || '/' ||
To_char(responsibility_id)
INTO l_return
FROM fnd_flex_value_rule_usages
WHERE flex_value_set_id = p_flex_value_set_id
AND flex_value_rule_id = p_flex_value_rule_id
AND application_id = p_application_id
AND responsibility_id = p_responsibility_id;
SELECT To_char(flex_value_id) || '/' ||
Nvl(parent_flex_value_low, '') || '/' ||
flex_value || '/' ||
enabled_flag || '/' ||
flex_value_meaning || '/' ||
Nvl(description, '')
INTO l_return
FROM fnd_flex_values_vl
WHERE flex_value_set_id = p_flex_value_set_id
AND flex_value_id = p_flex_value_id;
SELECT To_char(hierarchy_id) || '/' ||
hierarchy_name || '/' ||
Nvl(description,'')
INTO l_return
FROM fnd_flex_hierarchies_vl
WHERE flex_value_set_id = p_flex_value_set_id
AND hierarchy_id = p_hierarchy_id;
SELECT parent_flex_value || '/' ||
range_attribute || '/' ||
child_flex_value_low || '/' ||
child_flex_value_high
INTO l_return
FROM fnd_flex_value_norm_hierarchy
WHERE flex_value_set_id = p_flex_value_set_id
AND parent_flex_value = p_parent_flex_value
AND range_attribute = p_range_attribute
AND child_flex_value_low = p_child_flex_value_low
AND child_flex_value_high = p_child_flex_value_high;
SELECT parent_flex_value || '/' ||
child_flex_value_low || '/' ||
child_flex_value_high
INTO l_return
FROM fnd_flex_value_hierarchies
WHERE flex_value_set_id = p_flex_value_set_id
AND parent_flex_value = p_parent_flex_value
AND child_flex_value_low = p_child_flex_value_low
AND child_flex_value_high = p_child_flex_value_high;
DELETE
FROM fnd_flex_validation_tables fvt
WHERE flex_value_set_id = p_flex_value_set_id
AND NOT EXISTS
(SELECT null
FROM fnd_flex_value_sets fvs
WHERE fvs.flex_value_set_id = fvt.flex_value_set_id
AND fvs.validation_type = 'F');
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALIDATION_TABLES. ' || Sqlerrm;
UPDATE fnd_flex_value_sets SET
validation_type = 'N',
last_update_date = Sysdate,
last_updated_by = 1
WHERE flex_value_set_id = l_vst_set.flex_value_set_id;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_FLEX_VALUE_SETS. ' || Sqlerrm;
UPDATE fnd_flex_value_sets SET
validation_type = 'N',
parent_flex_value_set_id = NULL,
dependant_default_value = NULL,
dependant_default_meaning = NULL,
last_update_date = Sysdate,
last_updated_by = 1
WHERE flex_value_set_id = l_vst_set.flex_value_set_id;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_FLEX_VALUE_SETS. ' || Sqlerrm;
UPDATE fnd_flex_value_sets SET
validation_type = 'N',
last_update_date = Sysdate,
last_updated_by = 1
WHERE flex_value_set_id = l_vst_set.flex_value_set_id;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_FLEX_VALUE_SETS. ' || Sqlerrm;
SELECT COUNT(*) INTO l_count
FROM fnd_flex_validation_events
WHERE flex_value_set_id = l_vst_set.flex_value_set_id;
UPDATE fnd_flex_value_sets SET
validation_type = 'N',
last_update_date = Sysdate,
last_updated_by = 1
WHERE flex_value_set_id = l_vst_set.flex_value_set_id;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_FLEX_VALUE_SETS. ' || Sqlerrm;
UPDATE fnd_flex_value_sets SET
validation_type = l_vst_set.validation_type,
format_type = l_vst_set.format_type,
protected_flag = l_vst_set.protected_flag,
longlist_flag = l_vst_set.longlist_flag,
security_enabled_flag = l_vst_set.security_enabled_flag,
alphanumeric_allowed_flag = l_vst_set.alphanumeric_allowed_flag,
numeric_mode_enabled_flag = l_vst_set.numeric_mode_enabled_flag,
uppercase_only_flag = l_vst_set.uppercase_only_flag,
dependant_default_value = l_vst_set.dependant_default_value,
dependant_default_meaning = l_vst_set.dependant_default_meaning,
last_update_date = Sysdate,
last_updated_by = 1
WHERE flex_value_set_id = l_vst_set.flex_value_set_id;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_FLEX_VALUE_SETS. ' || Sqlerrm;
UPDATE fnd_flex_validation_tables SET
id_column_type = l_vst_tbl.id_column_type,
value_column_type = l_vst_tbl.value_column_type,
meaning_column_type = l_vst_tbl.meaning_column_type,
summary_allowed_flag = l_vst_tbl.summary_allowed_flag,
last_update_date = Sysdate,
last_updated_by = 1
WHERE flex_value_set_id = l_vst_tbl.flex_value_set_id;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_FLEX_VALIDATION_TABLES. ' ||
Sqlerrm;
DELETE
FROM fnd_flex_validation_events fve
WHERE flex_value_set_id = p_flex_value_set_id
AND event_code = p_event_code
AND NOT EXISTS
(SELECT null
FROM fnd_flex_value_sets fvs
WHERE fvs.flex_value_set_id = fve.flex_value_set_id
AND fvs.validation_type IN ('U', 'P'));
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALIDATION_EVENTS. ' || Sqlerrm;
DELETE
FROM fnd_flex_validation_events fve
WHERE flex_value_set_id = l_vst_evt.flex_value_set_id
AND event_code = l_vst_evt.event_code;
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALIDATION_EVENTS. ' ||
Sqlerrm;
DELETE
FROM fnd_flex_value_rules fvr
WHERE flex_value_set_id = p_flex_value_set_id
AND flex_value_rule_id = p_flex_value_rule_id
AND NOT EXISTS
(SELECT null
FROM fnd_flex_value_sets fvs
WHERE fvs.flex_value_set_id = fvr.flex_value_set_id);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALUE_RULES. ' || Sqlerrm;
DELETE
FROM fnd_flex_value_rule_lines fvrl
WHERE flex_value_set_id = p_flex_value_set_id
AND flex_value_rule_id = p_flex_value_rule_id
AND include_exclude_indicator = p_include_exclude_indicator
AND flex_value_low = p_flex_value_low
AND flex_value_high = p_flex_value_high
AND NOT EXISTS
(SELECT null
FROM fnd_flex_value_rules fvr
WHERE fvr.flex_value_set_id = fvrl.flex_value_set_id
AND fvr.flex_value_rule_id = fvrl.flex_value_rule_id);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALUE_RULE_LINES. ' || Sqlerrm;
DELETE
FROM fnd_flex_value_rule_lines fvrl
WHERE flex_value_set_id = l_vst_scl.flex_value_set_id
AND flex_value_rule_id = l_vst_scl.flex_value_rule_id
AND include_exclude_indicator=l_vst_scl.include_exclude_indicator
AND flex_value_low = l_vst_scl.flex_value_low
AND flex_value_high = l_vst_scl.flex_value_high;
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALUE_RULES. ' || Sqlerrm;
DELETE
FROM fnd_flex_value_rule_usages fvru
WHERE flex_value_set_id = p_flex_value_set_id
AND flex_value_rule_id = p_flex_value_rule_id
AND application_id = p_application_id
AND responsibility_id = p_responsibility_id
AND NOT EXISTS
(SELECT null
FROM fnd_flex_value_rules fvr
WHERE fvr.flex_value_set_id = fvru.flex_value_set_id
AND fvr.flex_value_rule_id = fvru.flex_value_rule_id);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALUE_RULE_USAGES. ' || Sqlerrm;
DELETE
FROM fnd_flex_value_rule_usages fvru
WHERE flex_value_set_id = l_vst_scu.flex_value_set_id
AND flex_value_rule_id = l_vst_scu.flex_value_rule_id
AND application_id = l_vst_scu.application_id
AND responsibility_id = l_vst_scu.responsibility_id
AND NOT EXISTS
(SELECT null
FROM fnd_application a
WHERE a.application_id = fvru.application_id);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALUE_RULE_USAGES. ' || Sqlerrm;
DELETE
FROM fnd_flex_value_rule_usages fvru
WHERE flex_value_set_id = l_vst_scu.flex_value_set_id
AND flex_value_rule_id = l_vst_scu.flex_value_rule_id
AND application_id = l_vst_scu.application_id
AND responsibility_id = l_vst_scu.responsibility_id
AND NOT EXISTS
(SELECT null
FROM fnd_responsibility r
WHERE r.application_id = fvru.application_id
AND r.responsibility_id = fvru.responsibility_id);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALUE_RULE_USAGES. ' || Sqlerrm;
DELETE
FROM fnd_flex_values fv
WHERE flex_value_set_id = p_flex_value_set_id
AND flex_value_id = p_flex_value_id
AND NOT EXISTS
(SELECT null
FROM fnd_flex_value_sets fvs
WHERE fvs.flex_value_set_id = fv.flex_value_set_id);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALUES. ' || Sqlerrm;
UPDATE fnd_flex_values fv SET
start_date_active = l_vst_val.start_date_active,
end_date_active = l_vst_val.end_date_active,
enabled_flag = l_vst_val.enabled_flag,
summary_flag = l_vst_val.summary_flag,
last_update_date = Sysdate,
last_updated_by = 1
WHERE flex_value_set_id = l_vst_val.flex_value_set_id
AND flex_value_id = l_vst_val.flex_value_id;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_FLEX_VALUES. ' || Sqlerrm;
DELETE
FROM fnd_flex_hierarchies fh
WHERE flex_value_set_id = p_flex_value_set_id
AND hierarchy_id = p_hierarchy_id
AND NOT EXISTS
(SELECT null
FROM fnd_flex_value_sets fvs
WHERE fvs.flex_value_set_id = fh.flex_value_set_id);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_HIERARCHIES. ' || Sqlerrm;
DELETE
FROM fnd_flex_value_norm_hierarchy fvnh
WHERE flex_value_set_id = p_flex_value_set_id
AND parent_flex_value = p_parent_flex_value
AND range_attribute = p_range_attribute
AND child_flex_value_low = p_child_flex_value_low
AND child_flex_value_high = p_child_flex_value_high
AND NOT EXISTS
(SELECT null
FROM fnd_flex_values fv
WHERE fv.flex_value_set_id = fvnh.flex_value_set_id
AND fv.flex_value = fvnh.parent_flex_value);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALUE_NORM_HIERARCHY. ' ||
Sqlerrm;
DELETE
FROM fnd_flex_value_hierarchies fvh
WHERE flex_value_set_id = p_flex_value_set_id
AND parent_flex_value = p_parent_flex_value
AND child_flex_value_low = p_child_flex_value_low
AND child_flex_value_high = p_child_flex_value_high
AND NOT EXISTS
(SELECT null
FROM fnd_flex_value_norm_hierarchy fvnh
WHERE fvnh.flex_value_set_id = fvh.flex_value_set_id
AND fvnh.parent_flex_value = fvh.parent_flex_value);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALUE_HIERARCHIES. ' ||
Sqlerrm;
SELECT descriptive_flexfield_name || '/' || title
INTO l_return
FROM fnd_descriptive_flexs_vl
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name;
SELECT descriptive_flex_context_code || '/' ||
global_flag || '/' ||
enabled_flag || '/' ||
descriptive_flex_context_name || '/' ||
Nvl(description, '')
INTO l_return
FROM fnd_descr_flex_contexts_vl
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name
AND descriptive_flex_context_code = p_descriptive_flex_context_cod;
SELECT application_column_name || '/' ||
enabled_flag || '/' ||
display_flag || '/' ||
end_user_column_name || '/' ||
form_left_prompt || '/' ||
Nvl(description, '')
INTO l_return
FROM fnd_descr_flex_col_usage_vl
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name
AND descriptive_flex_context_code = p_descriptive_flex_context_cod
AND application_column_name = p_application_column_name;
SELECT To_char(avl.application_id) || '/' ||
avl.application_short_name || '/' ||
avl.application_name
INTO l_return
FROM fnd_application_vl avl, fnd_descriptive_flexs df
WHERE avl.application_id = df.table_application_id
AND df.application_id = p_application_id
AND df.descriptive_flexfield_name = p_descriptive_flexfield_name;
SELECT us.table_owner || '/' ||
ft.table_name || '/' ||
To_char(table_id)
INTO l_return
FROM user_synonyms us, fnd_tables ft, fnd_descriptive_flexs df
WHERE us.synonym_name = df.application_table_name
AND ft.application_id = df.table_application_id
AND ft.table_name = df.application_table_name
AND df.application_id = p_application_id
AND df.descriptive_flexfield_name = p_descriptive_flexfield_name;
SELECT fc.column_name || '/' ||
To_char(fc.column_id) || '/' ||
fc.column_type || '/' ||
To_char(fc.width) || '/' ||
fc.flexfield_usage_code || '/' ||
Nvl(To_char(fc.flexfield_application_id), '') || '/' ||
Nvl(fc.flexfield_name, '')
INTO l_return
FROM fnd_columns fc, fnd_tables ft, fnd_descriptive_flexs df
WHERE df.application_id = p_application_id
AND df.descriptive_flexfield_name = p_descriptive_flexfield_name
AND ft.application_id = df.table_application_id
AND ft.table_name = df.application_table_name
AND fc.application_id = ft.application_id
AND fc.table_id = ft.table_id
AND fc.column_name = p_application_column_name;
DELETE
FROM fnd_descriptive_flexs df
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name
AND NOT EXISTS
(SELECT null
FROM fnd_application aa
WHERE aa.application_id = df.application_id);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_DESCRIPTIVE_FLEXS. ' || Sqlerrm;
DELETE
FROM fnd_compiled_descriptive_flexs cdf
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name
AND NOT EXISTS
(SELECT null
FROM fnd_descriptive_flexs df
WHERE df.application_id = cdf.application_id
AND df.descriptive_flexfield_name =cdf.descriptive_flexfield_name);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_COMPILED_DESCRIPTIVE_FLEXS. ' || Sqlerrm;
IF (update_fnd_columns(l_col,
'C',
l_dff_flx.application_id,
l_dff_flx.descriptive_flexfield_name,
x_message)) THEN
GOTO return_success;
SELECT COUNT(*)
INTO l_count
FROM fnd_descr_flex_contexts
WHERE application_id = l_dff_flx.application_id
AND descriptive_flexfield_name = l_dff_flx.descriptive_flexfield_name
AND global_flag = 'Y';
fnd_descr_flex_contexts_pkg.insert_row
(x_rowid => l_rowid,
x_application_id => l_dff_flx.application_id,
x_descriptive_flexfield_name => l_dff_flx.descriptive_flexfield_name,
x_descriptive_flex_context_cod => 'Global Data Elements',
x_enabled_flag => 'Y',
x_global_flag => 'Y',
x_description => 'Global Data Elements Context',
x_descriptive_flex_context_nam => 'Global Data Elements',
x_creation_date => Sysdate,
x_created_by => 1,
x_last_update_date => Sysdate,
x_last_updated_by => 1,
x_last_update_login => 0);
x_message := 'Global Data Elements Context is inserted.';
UPDATE fnd_descr_flex_contexts SET
global_flag = 'Y',
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_dff_flx.application_id
AND descriptive_flexfield_name = l_dff_flx.descriptive_flexfield_name
AND descriptive_flex_context_code = 'Global Data Elements';
'Unable to update FND_DESCR_FLEX_CONTEXTS. ' || Sqlerrm;
UPDATE fnd_descriptive_flexs SET
default_context_value = NULL,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_dff_flx.application_id
AND descriptive_flexfield_name = l_dff_flx.descriptive_flexfield_name;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_DESCRIPTIVE_FLEXS. ' || Sqlerrm;
UPDATE fnd_descriptive_flexs SET
context_required_flag = l_dff_flx.context_required_flag,
context_user_override_flag = l_dff_flx.context_user_override_flag,
freeze_flex_definition_flag = l_dff_flx.freeze_flex_definition_flag,
protected_flag = l_dff_flx.protected_flag,
context_default_type = l_dff_flx.context_default_type,
context_default_value = l_dff_flx.context_default_value,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_dff_flx.application_id
AND descriptive_flexfield_name = l_dff_flx.descriptive_flexfield_name;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_DESCRIPTIVE_FLEXS. ' || Sqlerrm;
UPDATE fnd_descriptive_flexs SET
context_override_value_set_id = NULL,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_dff_flx.application_id
AND descriptive_flexfield_name = l_dff_flx.descriptive_flexfield_name;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_DESCRIPTIVE_FLEXS. ' || Sqlerrm;
DELETE
FROM fnd_default_context_fields dcf
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name
AND default_context_field_name = p_default_context_field_name
AND NOT EXISTS
(SELECT null
FROM fnd_descriptive_flexs df
WHERE df.application_id = dcf.application_id
AND df.descriptive_flexfield_name =dcf.descriptive_flexfield_name);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_DEFAULT_CONTEXT_FIELDS. ' || Sqlerrm;
DELETE
FROM fnd_descr_flex_contexts dfc
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name
AND descriptive_flex_context_code = p_descriptive_flex_context_cod
AND NOT EXISTS
(SELECT null
FROM fnd_descriptive_flexs df
WHERE df.application_id = dfc.application_id
AND df.descriptive_flexfield_name =dfc.descriptive_flexfield_name);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_DESCR_FLEX_CONTEXTS. ' || Sqlerrm;
SELECT COUNT(*)
INTO l_count
FROM fnd_descr_flex_contexts
WHERE application_id = l_dff_ctx.application_id
AND descriptive_flexfield_name = l_dff_ctx.descriptive_flexfield_name
AND global_flag = 'Y';
UPDATE fnd_descr_flex_contexts SET
global_flag = 'N',
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_dff_ctx.application_id
AND descriptive_flexfield_name = l_dff_ctx.descriptive_flexfield_name
AND descriptive_flex_context_code = l_dff_ctx.descriptive_flex_context_code;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_DESCR_FLEX_CONTEXTS. ' || Sqlerrm;
UPDATE fnd_descr_flex_contexts SET
enabled_flag = 'Y',
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_dff_ctx.application_id
AND descriptive_flexfield_name = l_dff_ctx.descriptive_flexfield_name
AND descriptive_flex_context_code = l_dff_ctx.descriptive_flex_context_code;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_DESCR_FLEX_CONTEXTS. ' || Sqlerrm;
UPDATE fnd_descr_flex_contexts SET
global_flag = l_dff_ctx.global_flag,
enabled_flag = l_dff_ctx.enabled_flag,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_dff_ctx.application_id
AND descriptive_flexfield_name = l_dff_ctx.descriptive_flexfield_name
AND descriptive_flex_context_code = l_dff_ctx.descriptive_flex_context_code;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_DESCR_FLEX_CONTEXTS. ' || Sqlerrm;
DELETE
FROM fnd_descr_flex_column_usages dfcu
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name
AND descriptive_flex_context_code = p_descriptive_flex_context_cod
AND application_column_name = p_application_column_name
AND NOT EXISTS
(SELECT null
FROM fnd_descr_flex_contexts dfc
WHERE dfc.application_id = dfcu.application_id
AND dfc.descriptive_flexfield_name =dfcu.descriptive_flexfield_name
AND dfc.descriptive_flex_context_code = dfcu.descriptive_flex_context_code);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_DESCR_FLEX_COLUMN_USAGES. ' ||Sqlerrm;
IF (update_fnd_columns(l_col,
'D',
l_dff_flx.application_id,
l_dff_flx.descriptive_flexfield_name,
x_message)) THEN
GOTO return_success;
UPDATE fnd_descr_flex_column_usages SET
flex_value_set_id = NULL,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_dff_seg.application_id
AND descriptive_flexfield_name = l_dff_seg.descriptive_flexfield_name
AND descriptive_flex_context_code = l_dff_seg.descriptive_flex_context_code
AND application_column_name = l_dff_seg.application_column_name;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_DESCR_FLEX_COLUMN_USAGES. ' || Sqlerrm;
UPDATE fnd_descr_flex_column_usages SET
default_type = l_dff_seg.default_type,
enabled_flag = l_dff_seg.enabled_flag,
display_flag = l_dff_seg.display_flag,
range_code = l_dff_seg.range_code,
required_flag = l_dff_seg.required_flag,
security_enabled_flag = l_dff_seg.security_enabled_flag,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_dff_seg.application_id
AND descriptive_flexfield_name = l_dff_seg.descriptive_flexfield_name
AND descriptive_flex_context_code = l_dff_seg.descriptive_flex_context_code
AND application_column_name = l_dff_seg.application_column_name;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_DESCR_FLEX_COLUMN_USAGES. ' || Sqlerrm;
IF (update_fnd_columns(l_col, 'N', NULL, NULL, x_message)) THEN
GOTO return_success;
IF (update_fnd_columns(l_col, 'N', NULL, NULL, x_message)) THEN
GOTO return_success;
SELECT id_flex_code || '/' ||
id_flex_name || '/' ||
Nvl(description, '')
INTO l_return
FROM fnd_id_flexs
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code;
SELECT id_flex_num || '/' ||
enabled_flag || '/' ||
freeze_flex_definition_flag || '/' ||
concatenated_segment_delimiter || '/' ||
id_flex_structure_name
INTO l_return
FROM fnd_id_flex_structures_vl
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num;
SELECT application_column_name || '/' ||
enabled_flag || '/' ||
display_flag || '/' ||
segment_name || '/' ||
form_left_prompt || '/' ||
Nvl(description, '')
INTO l_return
FROM fnd_id_flex_segments_vl
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND application_column_name = p_application_column_name;
SELECT alias_name || '/' || enabled_flag || '/' ||
concatenated_segments || '/' || Nvl(description, '')
INTO l_return
FROM fnd_shorthand_flex_aliases
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND alias_name = p_alias_name;
SELECT flex_validation_rule_name || '/' || enabled_flag || '/' ||
error_message_text || '/' || Nvl(description, '')
INTO l_return
FROM fnd_flex_vdation_rules_vl
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND flex_validation_rule_name = p_flex_validation_rule_name;
SELECT rule_line_id || '/' || enabled_flag || '/' ||
include_exclude_indicator || '/' ||
concatenated_segments_low || '/' ||
concatenated_segments_high
INTO l_return
FROM fnd_flex_validation_rule_lines
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND flex_validation_rule_name = p_flex_validation_rule_name
AND rule_line_id = p_rule_line_id;
SELECT segment_attribute_type || '/' ||
global_flag || '/' || required_flag || '/' || unique_flag || '/' ||
segment_prompt
INTO l_return
FROM fnd_segment_attribute_types
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND segment_attribute_type = p_segment_attribute_type;
SELECT value_attribute_type || '/' || application_column_name || '/' ||
lookup_type || '/' || default_value || '/' || prompt
INTO l_return
FROM fnd_val_attribute_types_vl
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND segment_attribute_type = p_segment_attribute_type
AND value_attribute_type = p_value_attribute_type;
SELECT To_char(avl.application_id) || '/' ||
avl.application_short_name || '/' ||
avl.application_name
INTO l_return
FROM fnd_application_vl avl, fnd_id_flexs idf
WHERE avl.application_id = idf.table_application_id
AND idf.application_id = p_application_id
AND idf.id_flex_code = p_id_flex_code;
SELECT us.table_owner || '/' ||
ft.table_name || '/' ||
To_char(table_id)
INTO l_return
FROM user_synonyms us, fnd_tables ft, fnd_id_flexs idf
WHERE us.synonym_name = idf.application_table_name
AND ft.application_id = idf.table_application_id
AND ft.table_name = idf.application_table_name
AND idf.application_id = p_application_id
AND idf.id_flex_code = p_id_flex_code;
SELECT fc.column_name || '/' ||
To_char(fc.column_id) || '/' ||
fc.column_type || '/' ||
To_char(fc.width) || '/' ||
fc.flexfield_usage_code || '/' ||
Nvl(To_char(fc.flexfield_application_id), '') || '/' ||
Nvl(fc.flexfield_name, '')
INTO l_return
FROM fnd_columns fc, fnd_tables ft, fnd_id_flexs idf
WHERE idf.application_id = p_application_id
AND idf.id_flex_code = p_id_flex_code
AND ft.application_id = idf.table_application_id
AND ft.table_name = idf.application_table_name
AND fc.application_id = ft.application_id
AND fc.table_id = ft.table_id
AND fc.column_name = p_application_column_name;
SELECT wf_item_type || '/' || wf_process_name
INTO l_return
FROM fnd_flex_workflow_processes fwp
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND wf_item_type = p_wf_item_type;
SELECT *
FROM fnd_flex_validation_rule_lines
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num
ORDER BY flex_validation_rule_name;
SELECT COUNT(*)
INTO l_r_count
FROM fnd_flex_validation_rules
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num;
SELECT COUNT(*)
INTO l_l_count
FROM fnd_flex_validation_rule_lines
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num;
DELETE
FROM fnd_flex_include_rule_lines
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num;
'Unable to delete from FND_FLEX_INCLUDE_RULE_LINES. ' ||
Sqlerrm;
DELETE
FROM fnd_flex_exclude_rule_lines
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num;
'Unable to delete from FND_FLEX_EXCLUDE_RULE_LINES. ' ||
Sqlerrm;
DELETE
FROM fnd_flex_validation_rule_stats
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num;
'Unable to delete from FND_FLEX_VALIDATION_RULE_STATS. ' ||
Sqlerrm;
INSERT
INTO fnd_flex_validation_rule_stats
(application_id,
id_flex_code,
id_flex_num,
creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
rule_count, include_line_count, exclude_line_count)
VALUES
(p_kff_str.application_id,
p_kff_str.id_flex_code,
p_kff_str.id_flex_num,
Sysdate, -1,
Sysdate, -1, -1,
0, 0, 0);
'Unable to insert into FND_FLEX_VALIDATION_RULE_STATS. ' ||
Sqlerrm;
IF (fnd_flex_trigger.insert_rule_line
(kff_cvl_rec.rule_line_id,
kff_cvl_rec.application_id,
kff_cvl_rec.id_flex_code,
kff_cvl_rec.id_flex_num,
kff_cvl_rec.flex_validation_rule_name,
kff_cvl_rec.include_exclude_indicator,
kff_cvl_rec.enabled_flag,
kff_cvl_rec.created_by,
kff_cvl_rec.creation_date,
kff_cvl_rec.last_update_date,
kff_cvl_rec.last_updated_by,
kff_cvl_rec.last_update_login,
kff_cvl_rec.concatenated_segments_low,
kff_cvl_rec.concatenated_segments_high) = FALSE) THEN
x_message := fnd_message.get;
SELECT COUNT(*)
INTO l_i_count
FROM fnd_flex_include_rule_lines
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num;
SELECT COUNT(*)
INTO l_e_count
FROM fnd_flex_exclude_rule_lines
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num;
UPDATE fnd_flex_validation_rule_stats fvrs SET
last_update_date = Sysdate,
last_updated_by = 1,
rule_count = l_r_count,
include_line_count = l_i_count,
exclude_line_count = l_e_count
WHERE application_id = p_kff_str.application_id
AND id_flex_code = p_kff_str.id_flex_code
AND id_flex_num = p_kff_str.id_flex_num;
x_message := 'Unable to update FND_FLEX_VALIDATION_RULE_STATS. ' ||
Sqlerrm;
DELETE
FROM fnd_id_flexs idf
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND NOT EXISTS
(SELECT null
FROM fnd_application aa
WHERE aa.application_id = idf.application_id);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_ID_FLEXS. ' || Sqlerrm;
DELETE
FROM fnd_compiled_id_flexs cif
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND NOT EXISTS
(SELECT null
FROM fnd_id_flexs idf
WHERE idf.application_id = cif.application_id
AND idf.id_flex_code = cif.id_flex_code);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_COMPILED_ID_FLEXS. ' || Sqlerrm;
IF (update_fnd_columns(l_col,
'I',
NULL, -- l_kff_flx.application_id,
NULL, -- l_kff_flx.id_flex_code,
x_message)) THEN
GOTO return_success;
IF (update_fnd_columns(l_col,
'S',
NULL, -- l_kff_flx.application_id,
NULL, -- l_kff_flx.id_flex_code,
x_message)) THEN
GOTO return_success;
SELECT COUNT(*)
INTO l_count
FROM fnd_id_flex_structures
WHERE application_id = l_kff_flx.application_id
AND id_flex_code = l_kff_flx.id_flex_code;
fnd_id_flex_structures_pkg.insert_row
(x_rowid => l_rowid,
x_application_id => l_kff_flx.application_id,
x_id_flex_code => l_kff_flx.id_flex_code,
x_id_flex_num => 101,
x_id_flex_structure_code => Upper(l_kff_flx.id_flex_name),
x_concatenated_segment_delimit => '.',
x_cross_segment_validation_fla => 'N',
x_dynamic_inserts_allowed_flag => 'N',
x_enabled_flag => 'Y',
x_freeze_flex_definition_flag => 'N',
x_freeze_structured_hier_flag => 'N',
x_shorthand_enabled_flag => 'N',
x_shorthand_length => NULL,
x_structure_view_name => NULL,
x_id_flex_structure_name => l_kff_flx.id_flex_name,
x_description => NULL,
x_shorthand_prompt => NULL,
x_creation_date => Sysdate,
x_created_by => 1,
x_last_update_date => Sysdate,
x_last_updated_by => 1,
x_last_update_login => 0);
x_message := '101 Structure is inserted.';
l_kff_flx.dynamic_inserts_feasible_flag)) THEN
l_kff_flx.dynamic_inserts_feasible_flag := 'N';
UPDATE fnd_id_flexs SET
application_table_type = l_kff_flx.application_table_type,
allow_id_valuesets = l_kff_flx.allow_id_valuesets,
dynamic_inserts_feasible_flag = l_kff_flx.dynamic_inserts_feasible_flag,
index_flag = l_kff_flx.index_flag,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_kff_flx.application_id
AND id_flex_code = l_kff_flx.id_flex_code;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_ID_FLEXS. ' || Sqlerrm;
DELETE
FROM fnd_id_flex_structures ifst
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND NOT EXISTS
(SELECT null
FROM fnd_id_flexs idf
WHERE idf.application_id = ifst.application_id
AND idf.id_flex_code = ifst.id_flex_code);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_ID_FLEX_STRUCTURES. ' || Sqlerrm;
DELETE
FROM fnd_compiled_id_flex_structs cifs
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND NOT EXISTS
(SELECT null
FROM fnd_id_flex_structures ifst
WHERE ifst.application_id = cifs.application_id
AND ifst.id_flex_code = cifs.id_flex_code
AND ifst.id_flex_num = cifs.id_flex_num);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_COMPILED_ID_FLEX_STRUCTS. ' ||
Sqlerrm;
DELETE
FROM fnd_compiled_id_flex_structs cifs
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND NOT EXISTS
(SELECT null
FROM fnd_compiled_id_flexs cif
WHERE cif.application_id = cifs.application_id
AND cif.id_flex_code = cifs.id_flex_code);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_COMPILED_ID_FLEX_STRUCTS. ' ||
Sqlerrm;
l_kff_str.dynamic_inserts_allowed_flag)) THEN
l_kff_str.dynamic_inserts_allowed_flag := 'N';
UPDATE fnd_id_flex_structures SET
cross_segment_validation_flag = l_kff_str.cross_segment_validation_flag,
dynamic_inserts_allowed_flag = l_kff_str.dynamic_inserts_allowed_flag,
enabled_flag = l_kff_str.enabled_flag,
freeze_flex_definition_flag = l_kff_str.freeze_flex_definition_flag,
freeze_structured_hier_flag = l_kff_str.freeze_structured_hier_flag,
shorthand_enabled_flag = l_kff_str.shorthand_enabled_flag,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_kff_str.application_id
AND id_flex_code = l_kff_str.id_flex_code
AND id_flex_num = l_kff_str.id_flex_num;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_ID_FLEX_STRUCTURES. ' || Sqlerrm;
DELETE
FROM fnd_id_flex_segments ifsg
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND application_column_name = p_application_column_name
AND NOT EXISTS
(SELECT null
FROM fnd_id_flex_structures ifst
WHERE ifst.application_id = ifsg.application_id
AND ifst.id_flex_code = ifsg.id_flex_code
AND ifst.id_flex_num = ifsg.id_flex_num);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_ID_FLEX_SEGMENTS. ' || Sqlerrm;
IF (update_fnd_columns(l_col,
'K',
NULL, -- l_kff_flx.application_id,
NULL, -- l_kff_flx.id_flex_code,
x_message)) THEN
GOTO return_success;
UPDATE fnd_id_flex_segments SET
flex_value_set_id = NULL,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_kff_seg.application_id
AND id_flex_code = l_kff_seg.id_flex_code
AND id_flex_num = l_kff_seg.id_flex_num
AND application_column_name = l_kff_seg.application_column_name;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_ID_FLEX_SEGMENTS. ' || Sqlerrm;
UPDATE fnd_id_flex_segments SET
default_type = l_kff_seg.default_type,
range_code = l_kff_seg.range_code,
application_column_index_flag = l_kff_seg.application_column_index_flag,
enabled_flag = l_kff_seg.enabled_flag,
required_flag = l_kff_seg.required_flag,
display_flag = l_kff_seg.display_flag,
security_enabled_flag = l_kff_seg.security_enabled_flag,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_kff_seg.application_id
AND id_flex_code = l_kff_seg.id_flex_code
AND id_flex_num = l_kff_seg.id_flex_num
AND application_column_name = l_kff_seg.application_column_name;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_ID_FLEX_SEGMENTS. ' || Sqlerrm;
DELETE
FROM fnd_shorthand_flex_aliases sfa
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND alias_name = p_alias_name
AND NOT EXISTS
(SELECT null
FROM fnd_id_flex_structures ifst
WHERE ifst.application_id = sfa.application_id
AND ifst.id_flex_code = sfa.id_flex_code
AND ifst.id_flex_num = sfa.id_flex_num);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_SHORTHAND_FLEX_ALIASES. ' || Sqlerrm;
UPDATE fnd_shorthand_flex_aliases SET
enabled_flag = l_kff_sha.enabled_flag,
start_date_active = l_kff_sha.start_date_active,
end_date_active = l_kff_sha.end_date_active,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_kff_sha.application_id
AND id_flex_code = l_kff_sha.id_flex_code
AND id_flex_num = l_kff_sha.id_flex_num
AND alias_name = l_kff_sha.alias_name;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_SHORTHAND_FLEX_ALIASES. ' || Sqlerrm;
DELETE
FROM fnd_flex_validation_rules fvr
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND flex_validation_rule_name = p_flex_validation_rule_name
AND NOT EXISTS
(SELECT null
FROM fnd_id_flex_structures ifst
WHERE ifst.application_id = fvr.application_id
AND ifst.id_flex_code = fvr.id_flex_code
AND ifst.id_flex_num = fvr.id_flex_num);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALIDATION_RULES. ' || Sqlerrm;
UPDATE fnd_flex_validation_rules SET
error_segment_column_name = NULL,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND flex_validation_rule_name = p_flex_validation_rule_name;
x_message := SQL%rowcount || ' row(s) updated.';
x_message := 'Unable to update FND_FLEX_VALIDATION_RULES. ' ||
Sqlerrm;
UPDATE fnd_flex_validation_rules SET
enabled_flag = l_kff_cvr.enabled_flag,
start_date_active = l_kff_cvr.start_date_active,
end_date_active = l_kff_cvr.end_date_active,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_kff_cvr.application_id
AND id_flex_code = l_kff_cvr.id_flex_code
AND id_flex_num = l_kff_cvr.id_flex_num
AND flex_validation_rule_name = l_kff_cvr.flex_validation_rule_name;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_FLEX_VALIDATION_RULES. ' || Sqlerrm;
SELECT COUNT(*)
INTO l_count
FROM fnd_flex_validation_rule_lines
WHERE application_id = l_kff_cvr.application_id
AND id_flex_code = l_kff_cvr.id_flex_code
AND id_flex_num = l_kff_cvr.id_flex_num
AND flex_validation_rule_name = l_kff_cvr.flex_validation_rule_name;
UPDATE fnd_flex_validation_rules SET
enabled_flag = 'N',
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_kff_cvr.application_id
AND id_flex_code = l_kff_cvr.id_flex_code
AND id_flex_num = l_kff_cvr.id_flex_num
AND flex_validation_rule_name = l_kff_cvr.flex_validation_rule_name;
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALIDATION_RULES. ' || Sqlerrm;
DELETE
FROM fnd_flex_validation_rule_lines fvrl
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND flex_validation_rule_name = p_flex_validation_rule_name
AND rule_line_id = p_rule_line_id
AND NOT EXISTS
(SELECT null
FROM fnd_flex_validation_rules fvr
WHERE fvr.application_id = fvrl.application_id
AND fvr.id_flex_code = fvrl.id_flex_code
AND fvr.id_flex_num = fvrl.id_flex_num
AND fvr.flex_validation_rule_name =fvrl.flex_validation_rule_name);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALIDATION_RULE_LINES. ' ||
Sqlerrm;
UPDATE fnd_flex_validation_rule_lines SET
enabled_flag = l_kff_cvl.enabled_flag,
include_exclude_indicator = l_kff_cvl.include_exclude_indicator,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_kff_cvl.application_id
AND id_flex_code = l_kff_cvl.id_flex_code
AND id_flex_num = l_kff_cvl.id_flex_num
AND flex_validation_rule_name = l_kff_cvl.flex_validation_rule_name
AND rule_line_id = l_kff_cvl.rule_line_id;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_FLEX_VALIDATION_RULE_LINES. ' || Sqlerrm;
DELETE FROM fnd_flex_include_rule_lines firl
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND flex_validation_rule_name = p_flex_validation_rule_name
AND rule_line_id = p_rule_line_id
AND NOT EXISTS
(SELECT null
FROM fnd_flex_validation_rule_lines fvrl
WHERE firl.rule_line_id = fvrl.rule_line_id
AND firl.enabled_flag = fvrl.enabled_flag
AND fvrl.include_exclude_indicator = 'I');
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_INCLUDE_RULE_LINES. ' ||
Sqlerrm;
DELETE FROM fnd_flex_exclude_rule_lines ferl
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND flex_validation_rule_name = p_flex_validation_rule_name
AND rule_line_id = p_rule_line_id
AND NOT EXISTS
(SELECT null
FROM fnd_flex_validation_rule_lines fvrl
WHERE ferl.rule_line_id = fvrl.rule_line_id
AND ferl.enabled_flag = fvrl.enabled_flag
AND fvrl.include_exclude_indicator = 'E');
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_EXCLUDE_RULE_LINES. ' ||
Sqlerrm;
SELECT *
FROM fnd_id_flex_structures
ORDER BY application_id, id_flex_code, id_flex_num;
DELETE
FROM fnd_segment_attribute_types sat
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND segment_attribute_type = p_segment_attribute_type
AND NOT EXISTS
(SELECT null
FROM fnd_id_flexs idf
WHERE idf.application_id = sat.application_id
AND idf.id_flex_code = sat.id_flex_code);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_SEGMENT_ATTRIBUTE_TYPES. ' || Sqlerrm;
UPDATE fnd_segment_attribute_types SET
global_flag = l_kff_flq.global_flag,
required_flag = l_kff_flq.required_flag,
unique_flag = l_kff_flq.unique_flag,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_kff_flq.application_id
AND id_flex_code = l_kff_flq.id_flex_code
AND segment_attribute_type = l_kff_flq.segment_attribute_type;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_SEGMENT_ATTRIBUTE_TYPES. ' || Sqlerrm;
DELETE
FROM fnd_segment_attribute_values sav
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND application_column_name = p_application_column_name
AND segment_attribute_type = p_segment_attribute_type
AND NOT EXISTS
(SELECT null
FROM fnd_id_flex_segments ifsg
WHERE ifsg.application_id = sav.application_id
AND ifsg.id_flex_code = sav.id_flex_code
AND ifsg.id_flex_num = sav.id_flex_num
AND ifsg.application_column_name = sav.application_column_name);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_SEGMENT_ATTRIBUTE_VALUES. ' ||Sqlerrm;
DELETE
FROM fnd_segment_attribute_values sav
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND application_column_name = p_application_column_name
AND segment_attribute_type = p_segment_attribute_type
AND NOT EXISTS
(SELECT null
FROM fnd_segment_attribute_types sat
WHERE sat.application_id = sav.application_id
AND sat.id_flex_code = sav.id_flex_code
AND sat.segment_attribute_type = sav.segment_attribute_type);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_SEGMENT_ATTRIBUTE_VALUES. ' ||Sqlerrm;
INSERT INTO fnd_segment_attribute_values
(
application_id,
id_flex_code,
id_flex_num,
application_column_name,
segment_attribute_type,
attribute_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
l_kff_seg.application_id,
l_kff_seg.id_flex_code,
l_kff_seg.id_flex_num,
l_kff_seg.application_column_name,
l_kff_flq.segment_attribute_type,
l_kff_flq.global_flag,
1,
Sysdate,
1,
Sysdate,
0);
x_message := SQL%rowcount || ' row(s) inserted.';
'Unable to insert into FND_SEGMENT_ATTRIBUTE_VALUES. ' ||Sqlerrm;
UPDATE fnd_segment_attribute_values SET
attribute_value = l_kff_qlv.attribute_value,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_kff_qlv.application_id
AND id_flex_code = l_kff_qlv.id_flex_code
AND id_flex_num = l_kff_qlv.id_flex_num
AND application_column_name = l_kff_qlv.application_column_name
AND segment_attribute_type = l_kff_qlv.segment_attribute_type;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_SEGMENT_ATTRIBUTE_VALUES. ' || Sqlerrm;
DELETE
FROM fnd_value_attribute_types vat
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND segment_attribute_type = p_segment_attribute_type
AND value_attribute_type = p_value_attribute_type
AND NOT EXISTS
(SELECT null
FROM fnd_segment_attribute_types sat
WHERE sat.application_id = vat.application_id
AND sat.id_flex_code = vat.id_flex_code
AND sat.segment_attribute_type = vat.segment_attribute_type);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_VALUE_ATTRIBUTE_TYPES. ' || Sqlerrm;
IF (update_fnd_columns(l_col,
'Q',
NULL, -- l_kff_flx.application_id,
NULL, -- l_kff_flx.id_flex_code,
x_message)) THEN
GOTO return_success;
UPDATE fnd_value_attribute_types SET
required_flag = l_kff_sgq.required_flag,
last_update_date = Sysdate,
last_updated_by = 1
WHERE application_id = l_kff_sgq.application_id
AND id_flex_code = l_kff_sgq.id_flex_code
AND segment_attribute_type = l_kff_sgq.segment_attribute_type
AND value_attribute_type = l_kff_sgq.value_attribute_type;
x_message := SQL%rowcount || ' row(s) updated.';
'Unable to update FND_VALUE_ATTRIBUTE_TYPES. ' || Sqlerrm;
DELETE
FROM fnd_flex_validation_qualifiers fvq
WHERE id_flex_application_id = p_id_flex_application_id
AND id_flex_code = p_id_flex_code
AND segment_attribute_type = p_segment_attribute_type
AND value_attribute_type = p_value_attribute_type
AND flex_value_set_id = p_flex_value_set_id
AND NOT EXISTS
(SELECT null
FROM fnd_value_attribute_types vat
WHERE vat.application_id = fvq.id_flex_application_id
AND vat.id_flex_code = fvq.id_flex_code
AND vat.segment_attribute_type = fvq.segment_attribute_type
AND vat.value_attribute_type = fvq.value_attribute_type);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALIDATION_QUALIFIERS. ' ||
Sqlerrm;
DELETE
FROM fnd_flex_validation_qualifiers fvq
WHERE id_flex_application_id = p_id_flex_application_id
AND id_flex_code = p_id_flex_code
AND segment_attribute_type = p_segment_attribute_type
AND value_attribute_type = p_value_attribute_type
AND flex_value_set_id = p_flex_value_set_id
AND NOT EXISTS
(SELECT null
FROM fnd_flex_value_sets fvs
WHERE fvs.flex_value_set_id = fvq.flex_value_set_id);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_VALIDATION_QUALIFIERS. ' ||
Sqlerrm;
SELECT *
INTO l_kff_flx
FROM fnd_id_flexs
WHERE table_application_id = l_tbl.application_id
AND application_table_name = l_tbl.table_name
AND unique_id_column_name = l_col.column_name;
x_message := 'Unable to select from FND_ID_FLEXS. ' || Sqlerrm;
IF (update_fnd_columns(l_col, 'N', NULL, NULL, x_message)) THEN
GOTO return_success;
SELECT *
INTO l_kff_flx
FROM fnd_id_flexs
WHERE table_application_id = l_tbl.application_id
AND application_table_name = l_tbl.table_name
AND set_defining_column_name = l_col.column_name;
x_message := 'Unable to select from FND_ID_FLEXS. ' || Sqlerrm;
IF (update_fnd_columns(l_col, 'N', NULL, NULL, x_message)) THEN
GOTO return_success;
SELECT *
INTO l_kff_flx
FROM fnd_id_flexs
WHERE table_application_id = l_tbl.application_id
AND application_table_name = l_tbl.table_name;
x_message := 'Unable to select from FND_ID_FLEXS. ' || Sqlerrm;
IF (update_fnd_columns(l_col, 'N', NULL, NULL, x_message)) THEN
GOTO return_success;
SELECT *
INTO l_kff_flx
FROM fnd_id_flexs
WHERE table_application_id = l_tbl.application_id
AND application_table_name = l_tbl.table_name;
SELECT *
INTO l_kff_sgq
FROM fnd_value_attribute_types
WHERE application_id = l_kff_flx.application_id
AND id_flex_code = l_kff_flx.id_flex_code
AND application_column_name = l_col.column_name;
'Unable to select from FND_VALUE_ATTRIBUTE_TYPES. ' ||
Sqlerrm;
x_message := 'Unable to select from FND_ID_FLEXS. ' || Sqlerrm;
IF (update_fnd_columns(l_col, 'N', NULL, NULL, x_message)) THEN
GOTO return_success;
DELETE
FROM fnd_flex_workflow_processes fwp
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND wf_item_type = p_wf_item_type
AND NOT EXISTS
(SELECT null
FROM fnd_id_flex_structures ifst
WHERE ifst.application_id = fwp.application_id
AND ifst.id_flex_code = fwp.id_flex_code
AND ifst.id_flex_num = fwp.id_flex_num);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_WORKFLOW_PROCESSES. ' || Sqlerrm;
DELETE
FROM fnd_flex_workflow_processes fwp
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND wf_item_type = p_wf_item_type
AND NOT exists
(SELECT null
FROM wf_item_types wit
WHERE wit.name = fwp.wf_item_type);
x_message := SQL%rowcount || ' row(s) deleted.';
'Unable to delete from FND_FLEX_WORKFLOW_PROCESSES. ' || Sqlerrm;