[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
last_update_date fnd_id_flexs.last_update_date%TYPE,
last_updated_by fnd_id_flexs.last_updated_by%TYPE,
last_update_login fnd_id_flexs.last_update_login%TYPE);
l_who_rec.last_update_login := 0;
l_who_rec.last_update_date := l_who_rec.creation_date;
l_who_rec.last_updated_by := l_who_rec.created_by;
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
COUNT(*)
INTO l_rows_count
FROM user_synonyms syn, all_tab_columns col
where syn.synonym_name = p_table_name
and col.owner = syn.table_owner
and col.table_name = syn.table_name
and col.column_name = p_column_name
and col.owner = l_out_oracle_schema;
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
ft.table_type
INTO l_table_type
FROM fnd_tables ft
WHERE ft.application_id = p_application_id
AND ft.table_name = p_table_name;
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
application_id
INTO x_application_id
FROM fnd_application
WHERE application_short_name = p_appl_short_name;
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
*
INTO x_vset_rec
FROM fnd_flex_value_sets
WHERE flex_value_set_name = p_flex_value_set_name;
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
id_column_name
INTO l_vc2
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = p_vset_rec.flex_value_set_id;
FUNCTION get_sql_update
(p_application_id IN VARCHAR2,
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_who_rec IN who_rec_type)
RETURN VARCHAR2
IS
l_sql_update VARCHAR2(2000);
l_sql_update :=
'UPDATE ' || p_table_name ||
' SET ' || p_column_name || ' = :l_value_new';
IF (column_exists(p_application_id, p_table_name, 'LAST_UPDATE_DATE')) THEN
l_sql_update := l_sql_update ||
', LAST_UPDATE_DATE = to_date(''' ||
To_char(p_who_rec.last_update_date,'RRRR/MM/DD') ||
''',''RRRR/MM/DD'')';
IF (column_exists(p_application_id, p_table_name, 'LAST_UPDATED_BY')) THEN
l_sql_update := l_sql_update ||
', LAST_UPDATED_BY = ' || p_who_rec.last_updated_by;
l_sql_update := l_sql_update || ' WHERE ROWID = :l_rowid';
RETURN(l_sql_update);
END get_sql_update;
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
value INTO l_nls_numeric_characters
FROM v$nls_parameters
WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
'SELECT FROM V$NLS_PARAMETERS failed.',
Sqlerrm);
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
*
INTO l_new_vset_rec
FROM fnd_flex_value_sets
WHERE flex_value_set_name = p_new_value_set_name;
INSERT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
INTO fnd_flex_value_sets
(flex_value_set_id,
flex_value_set_name,
validation_type,
security_enabled_flag,
longlist_flag,
format_type,
maximum_size,
alphanumeric_allowed_flag,
uppercase_only_flag,
numeric_mode_enabled_flag,
description,
minimum_value,
maximum_value,
number_precision,
protected_flag,
last_update_login,
last_update_date,
last_updated_by,
creation_date,
created_by,
dependant_default_value,/* note spelling */
dependant_default_meaning,/* note spelling */
parent_flex_value_set_id)
SELECT
fnd_flex_value_sets_s.NEXTVAL,
p_new_value_set_name,
validation_type,
security_enabled_flag,
longlist_flag,
l_format_type,
l_maximum_size,
'Y', -- alphanumeric_allowed_flag
'Y', -- uppercase_only_flag
'N', -- numeric_mode_enabled_flag
description,
l_minimum_value,
l_maximum_value,
number_precision,
protected_flag,
l_who_rec.last_update_login,
l_who_rec.last_update_date,
l_who_rec.last_updated_by,
l_who_rec.creation_date,
l_who_rec.created_by,
l_dependant_default_value,
l_dependant_default_meaning,
parent_flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = p_old_value_set_name;
'INSERT INTO fnd_flex_value_sets failed.', Sqlerrm);
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
additional_where_clause
INTO l_long
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = l_old_vset_rec.flex_value_set_id;
'SELECT FROM fnd_flex_validation_tables failed.',
Sqlerrm);
INSERT INTO fnd_flex_validation_tables
(flex_value_set_id,
application_table_name,
value_column_name,
value_column_type,
value_column_size,
id_column_name,
id_column_type,
id_column_size,
meaning_column_name,
meaning_column_type,
meaning_column_size,
summary_allowed_flag,
table_application_id,
additional_where_clause,
additional_quickpick_columns,
compiled_attribute_column_name,
enabled_column_name,
hierarchy_level_column_name,
start_date_column_name,
end_date_column_name,
summary_column_name,
last_update_login,
last_update_date,
last_updated_by,
creation_date,
created_by)
SELECT
l_new_vset_rec.flex_value_set_id,
application_table_name,
value_column_name,
value_column_type,
value_column_size,
id_column_name,
id_column_type,
id_column_size,
meaning_column_name,
meaning_column_type,
meaning_column_size,
summary_allowed_flag,
table_application_id,
l_long, -- additional_where_clause,
additional_quickpick_columns,
compiled_attribute_column_name,
enabled_column_name,
hierarchy_level_column_name,
start_date_column_name,
end_date_column_name,
summary_column_name,
l_who_rec.last_update_login,
l_who_rec.last_update_date,
l_who_rec.last_updated_by,
l_who_rec.creation_date,
l_who_rec.created_by
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = l_old_vset_rec.flex_value_set_id;
'INSERT INTO fnd_flex_validation_tables failed.',
Sqlerrm);
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
COUNT(*)
INTO l_count
FROM fnd_flex_validation_events
WHERE flex_value_set_id = l_old_vset_rec.flex_value_set_id;
'SELECT COUNT fnd_flex_validation_events failed.',
Sqlerrm);
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
user_exit, event_code
INTO l_long, l_event_code
FROM fnd_flex_validation_events told
WHERE told.flex_value_set_id = l_old_vset_rec.flex_value_set_id
AND ROWNUM = 1
AND NOT exists
(SELECT NULL
FROM fnd_flex_validation_events tnew
WHERE tnew.flex_value_set_id = l_new_vset_rec.flex_value_set_id
AND tnew.event_code = told.event_code);
'SELECT FROM fnd_flex_validation_events ' ||
'failed.', Sqlerrm);
INSERT INTO fnd_flex_validation_events
(flex_value_set_id,
event_code,
user_exit,
last_update_login,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES (l_new_vset_rec.flex_value_set_id,
l_event_code,
l_long,
l_who_rec.last_update_login,
l_who_rec.last_update_date,
l_who_rec.last_updated_by,
l_who_rec.creation_date,
l_who_rec.created_by);
'INSERT INTO fnd_flex_validation_events failed.',
Sqlerrm);
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
application_id, descriptive_flexfield_name,
descriptive_flex_context_code,
application_column_name, end_user_column_name,
default_type, default_value
FROM fnd_descr_flex_column_usages
WHERE application_id = p_application_id
AND flex_value_set_id = p_flex_value_set_id
AND descriptive_flexfield_name LIKE p_report_name_like
AND enabled_flag = 'Y'
AND descriptive_flex_context_code = 'Global Data Elements'
ORDER BY application_id, descriptive_flexfield_name,
descriptive_flex_context_code, application_column_name;
UPDATE fnd_descr_flex_column_usages
SET flex_value_set_id = l_new_vset_rec.flex_value_set_id,
default_value = l_default_value,
last_update_date = l_who_rec.last_update_date,
last_updated_by = l_who_rec.last_updated_by
WHERE application_id = srs_rec.application_id
AND descriptive_flexfield_name = srs_rec.descriptive_flexfield_name
AND descriptive_flex_context_code = srs_rec.descriptive_flex_context_code
AND application_column_name = srs_rec.application_column_name;
'Failure in UPDATE FND_DESCR_FLEX_COLUMN_USAGES.',
Sqlerrm);
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
df.application_id, df.descriptive_flexfield_name,
df.table_application_id, df.application_table_name,
df.context_column_name,
dfc.descriptive_flex_context_code, dfc.global_flag,
dfcu.application_column_name, dfcu.end_user_column_name,
dfcu.default_type, dfcu.default_value,
fc.column_type, fc.width
FROM fnd_descriptive_flexs df, fnd_descr_flex_contexts dfc,
fnd_descr_flex_column_usages dfcu, fnd_columns fc
WHERE df.application_id = dfc.application_id
AND df.descriptive_flexfield_name = dfc.descriptive_flexfield_name
AND 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
AND ((fc.application_id, fc.table_id) =
(SELECT ft.application_id, ft.table_id
FROM fnd_tables ft
WHERE ft.application_id = df.table_application_id
AND ft.table_name = df.application_table_name))
AND fc.column_name = dfcu.application_column_name
AND fc.flexfield_usage_code = 'D'
AND dfcu.flex_value_set_id = p_flex_value_set_id
AND dfcu.enabled_flag = 'Y'
AND dfc.enabled_flag = 'Y'
AND df.application_id = p_application_id
AND df.descriptive_flexfield_name LIKE p_dff_name_like
AND dfc.descriptive_flex_context_code LIKE p_context_code_like
ORDER BY df.application_id, df.descriptive_flexfield_name,
dfc.descriptive_flex_context_code, dfcu.application_column_name;
UPDATE fnd_descr_flex_column_usages
SET flex_value_set_id = l_new_vset_rec.flex_value_set_id,
default_value = l_default_value,
last_update_date = l_who_rec.last_update_date,
last_updated_by = l_who_rec.last_updated_by
WHERE application_id = dff_rec.application_id
AND descriptive_flexfield_name = dff_rec.descriptive_flexfield_name
AND descriptive_flex_context_code = dff_rec.descriptive_flex_context_code
AND application_column_name = dff_rec.application_column_name;
'Failure in UPDATE FND_DESCR_FLEX_COLUMN_USAGES.',
Sqlerrm);
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
idf.application_id, idf.id_flex_code,
idf.table_application_id, idf.application_table_name,
idf.set_defining_column_name, idf.unique_id_column_name,
ifst.id_flex_num, ifst.id_flex_structure_name,
ifsg.segment_name, ifsg.application_column_name,
ifsg.default_type, ifsg.default_value,
fc.column_type, fc.width
FROM fnd_id_flexs idf, fnd_id_flex_structures_vl ifst,
fnd_id_flex_segments ifsg, fnd_columns fc
WHERE idf.application_id = ifst.application_id
AND idf.id_flex_code = ifst.id_flex_code
AND ifst.application_id = ifsg.application_id
AND ifst.id_flex_code = ifsg.id_flex_code
AND ifst.id_flex_num = ifsg.id_flex_num
AND ((fc.application_id, fc.table_id) =
(SELECT ft.application_id, ft.table_id
FROM fnd_tables ft
WHERE ft.application_id = idf.table_application_id
AND ft.table_name = idf.application_table_name))
AND fc.column_name = ifsg.application_column_name
AND fc.flexfield_usage_code = 'K'
AND ifsg.flex_value_set_id = p_flex_value_set_id
AND ifst.enabled_flag = 'Y'
AND ifsg.enabled_flag = 'Y'
AND idf.application_id = p_application_id
AND idf.id_flex_code = p_id_flex_code
AND ifst.id_flex_structure_name LIKE p_struct_name_like
AND To_char(ifst.id_flex_num) LIKE p_struct_num_like
ORDER BY idf.application_id, idf.id_flex_code,
ifst.id_flex_num, ifsg.application_column_name;
UPDATE fnd_id_flex_segments
SET flex_value_set_id = l_new_vset_rec.flex_value_set_id,
default_value = l_default_value,
last_update_date = l_who_rec.last_update_date,
last_updated_by = l_who_rec.last_updated_by
WHERE application_id = kff_rec.application_id
AND id_flex_code = kff_rec.id_flex_code
AND id_flex_num = kff_rec.id_flex_num
AND application_column_name = kff_rec.application_column_name;
'Failure in UPDATE fnd_id_flex_segments.', Sqlerrm);
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
*
FROM fnd_flex_value_sets
WHERE validation_type = 'D'
AND parent_flex_value_set_id = p_ind_vset_id;
SELECT
count(*)
INTO
l_num_segs
FROM
fnd_id_flexs f,
fnd_id_flex_segments s
WHERE
s.flex_value_set_id=l_ind_vset_rec.flex_value_set_id AND
f.application_id=s.application_id AND
f.id_flex_code=s.id_flex_code AND
f.allow_id_valuesets='N';
SELECT
' Segment ''' || fifsg.segment_name ||
''' Structure ''' || fifst.id_flex_structure_code ||
''' Key Flex Code ''' || fif.id_flex_code ||
''' Application Id ''' || fif.application_id
INTO
l_trans_msg
FROM
fnd_id_flexs fif, fnd_id_flex_structures fifst,
fnd_id_flex_segments fifsg
WHERE
fifst.application_id = fif.application_id
and fifst.id_flex_code = fif.id_flex_code
and fifsg.application_id = fifst.application_id
and fifsg.id_flex_code = fifst.id_flex_code
and fifsg.id_flex_num = fifst.id_flex_num
and fifsg.flex_value_set_id = l_ind_vset_rec.flex_value_set_id
and fif.allow_id_valuesets = 'N'
and rownum = 1;
UPDATE fnd_flex_value_sets
SET
validation_type = 'Y',
last_update_date = l_who_rec.last_update_date,
last_updated_by = l_who_rec.last_updated_by
WHERE flex_value_set_id = dep_vset_rec.flex_value_set_id;
UPDATE fnd_flex_value_sets
SET
validation_type = 'X',
last_update_date = l_who_rec.last_update_date,
last_updated_by = l_who_rec.last_updated_by
WHERE flex_value_set_id = l_ind_vset_rec.flex_value_set_id;
p_sql_select IN VARCHAR2,
p_sql_update IN VARCHAR2,
x_upg_count OUT nocopy NUMBER)
IS
l_func_name VARCHAR2(80) := (g_package_name ||
'cp_upgrade_table_column');
select_cursor cursor_type;
OPEN select_cursor FOR p_sql_select USING l_last_rowid, l_last_rowid, p_bind_value, g_block_size;
OPEN select_cursor FOR p_sql_select USING l_last_rowid, l_last_rowid, g_block_size;
FETCH select_cursor INTO l_rowid, l_value_old;
EXIT WHEN select_cursor%NOTFOUND;
EXECUTE IMMEDIATE p_sql_update USING l_value_new, l_rowid;
CLOSE select_cursor;
l_sql_select VARCHAR2(2000);
l_sql_update VARCHAR2(2000);
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
df.application_id, df.descriptive_flexfield_name,
df.table_application_id, df.application_table_name,
df.context_column_name,
dfc.descriptive_flex_context_code, dfc.global_flag,
dfcu.application_column_name, dfcu.end_user_column_name,
dfcu.default_type, dfcu.default_value, dfcu.ROWID,
fc.column_type, fc.width
FROM fnd_descriptive_flexs df, fnd_descr_flex_contexts dfc,
fnd_descr_flex_column_usages dfcu, fnd_columns fc
WHERE df.application_id = dfc.application_id
AND df.descriptive_flexfield_name = dfc.descriptive_flexfield_name
AND 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
AND ((fc.application_id, fc.table_id) =
(SELECT ft.application_id, ft.table_id
FROM fnd_tables ft
WHERE ft.application_id = df.table_application_id
AND ft.table_name = df.application_table_name))
AND fc.column_name = dfcu.application_column_name
AND fc.flexfield_usage_code = 'D'
AND dfcu.flex_value_set_id = p_flex_value_set_id
AND dfcu.enabled_flag = 'Y'
AND dfc.enabled_flag = 'Y'
AND (p_ff_last_rowid IS NULL OR p_ff_last_rowid < dfcu.ROWID)
ORDER BY dfcu.ROWID;
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
idf.application_id, idf.id_flex_code,
idf.table_application_id, idf.application_table_name,
idf.set_defining_column_name, idf.unique_id_column_name,
ifst.id_flex_num, ifst.id_flex_structure_name,
ifsg.segment_name, ifsg.application_column_name,
ifsg.default_type, ifsg.default_value, ifsg.ROWID,
fc.column_type, fc.width
FROM fnd_id_flexs idf, fnd_id_flex_structures_vl ifst,
fnd_id_flex_segments ifsg, fnd_columns fc
WHERE idf.application_id = ifst.application_id
AND idf.id_flex_code = ifst.id_flex_code
AND ifst.application_id = ifsg.application_id
AND ifst.id_flex_code = ifsg.id_flex_code
AND ifst.id_flex_num = ifsg.id_flex_num
AND ((fc.application_id, fc.table_id) =
(SELECT ft.application_id, ft.table_id
FROM fnd_tables ft
WHERE ft.application_id = idf.table_application_id
AND ft.table_name = idf.application_table_name))
AND fc.column_name = ifsg.application_column_name
AND fc.flexfield_usage_code = 'K'
AND ifsg.flex_value_set_id = p_flex_value_set_id
AND ifst.enabled_flag = 'Y'
AND ifsg.enabled_flag = 'Y'
AND (p_ff_last_rowid IS NULL OR p_ff_last_rowid < ifsg.ROWID)
ORDER BY ifsg.ROWID;
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
ROWID, flex_value
FROM fnd_flex_values
WHERE flex_value_set_id = p_flex_value_set_id
ORDER BY flex_value;
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
ROWID, parent_flex_value,
child_flex_value_low, child_flex_value_high
FROM fnd_flex_value_norm_hierarchy
WHERE flex_value_set_id = p_flex_value_set_id;
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
ROWID, parent_flex_value,
child_flex_value_low, child_flex_value_high
FROM fnd_flex_value_hierarchies
WHERE flex_value_set_id = p_flex_value_set_id;
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
ROWID, parent_flex_value_low
FROM fnd_flex_values
WHERE flex_value_set_id IN
(SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE parent_flex_value_set_id = p_flex_value_set_id
AND validation_type = 'D');
UPDATE fnd_flex_value_sets
SET
alphanumeric_allowed_flag = 'N',
minimum_value = l_vc2_tmp1,
maximum_value = l_vc2_tmp2,
dependant_default_value = l_vc2_tmp3,
last_update_date = p_who_rec.last_update_date,
last_updated_by = p_who_rec.last_updated_by
WHERE flex_value_set_id = vset_rec.flex_value_set_id;
'Failure in UPDATE FND_FLEX_VALUE_SETS.',
Sqlerrm);
UPDATE fnd_flex_value_sets
SET
maximum_size = 11,
alphanumeric_allowed_flag = 'Y',
uppercase_only_flag = 'Y',
numeric_mode_enabled_flag = 'N',
minimum_value = l_vc2_tmp1,
maximum_value = l_vc2_tmp2,
dependant_default_value = l_vc2_tmp3,
last_update_date = p_who_rec.last_update_date,
last_updated_by = p_who_rec.last_updated_by
WHERE flex_value_set_id = vset_rec.flex_value_set_id;
'Failure in UPDATE FND_FLEX_VALUE_SETS.',
Sqlerrm);
UPDATE fnd_flex_value_sets
SET
maximum_size = 20,
alphanumeric_allowed_flag = 'Y',
uppercase_only_flag = 'Y',
numeric_mode_enabled_flag = 'N',
minimum_value = l_vc2_tmp1,
maximum_value = l_vc2_tmp2,
dependant_default_value = l_vc2_tmp3,
last_update_date = p_who_rec.last_update_date,
last_updated_by = p_who_rec.last_updated_by
WHERE flex_value_set_id = vset_rec.flex_value_set_id;
'Failure in UPDATE FND_FLEX_VALUE_SETS.',
Sqlerrm);
UPDATE fnd_flex_values
SET
flex_value = l_vc2_tmp1,
last_update_date = p_who_rec.last_update_date,
last_updated_by = p_who_rec.last_updated_by
WHERE ROWID = val_rec.ROWID;
'Failure in UPDATE FND_FLEX_VALUES.',
Sqlerrm);
UPDATE fnd_flex_value_norm_hierarchy
SET
parent_flex_value = l_vc2_tmp1,
child_flex_value_low = l_vc2_tmp2,
child_flex_value_high = l_vc2_tmp3,
last_update_date = p_who_rec.last_update_date,
last_updated_by = p_who_rec.last_updated_by
WHERE ROWID = nhier_rec.ROWID;
'Failure in UPDATE FND_FLEX_VALUE_NORM_HIERARCHY.',
Sqlerrm);
UPDATE fnd_flex_value_hierarchies
SET
parent_flex_value = l_vc2_tmp1,
child_flex_value_low = l_vc2_tmp2,
child_flex_value_high = l_vc2_tmp3,
last_update_date = p_who_rec.last_update_date,
last_updated_by = p_who_rec.last_updated_by
WHERE ROWID = hier_rec.ROWID;
'Failure in UPDATE FND_FLEX_VALUE_HIERARCHIES.',
Sqlerrm);
UPDATE fnd_flex_values
SET
parent_flex_value_low = l_vc2_tmp1,
last_update_date = p_who_rec.last_update_date,
last_updated_by = p_who_rec.last_updated_by
WHERE ROWID = par_rec.ROWID;
'Failure in UPDATE PARENT FND_FLEX_VALUES.',
Sqlerrm);
UPDATE fnd_descr_flex_column_usages
SET
default_value = l_vc2_tmp1,
last_update_date = p_who_rec.last_update_date,
last_updated_by = p_who_rec.last_updated_by
WHERE application_id = dff_rec.application_id
AND descriptive_flexfield_name = dff_rec.descriptive_flexfield_name
AND descriptive_flex_context_code = dff_rec.descriptive_flex_context_code
AND application_column_name = dff_rec.application_column_name;
'Failure in UPDATE FND_DESCR_FLEX_COLUMN_USAGES.',
Sqlerrm);
l_sql_select := ('SELECT COUNT(*)' ||
' FROM ' || dff_rec.application_table_name ||
' WHERE (''/* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */'' IS NOT NULL)');
l_sql_select := l_sql_select || l_addtl_where;
EXECUTE IMMEDIATE l_sql_select INTO l_rows_count USING l_bind_value;
EXECUTE IMMEDIATE l_sql_select INTO l_rows_count;
l_sql_select :=
'SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */ ' ||
' ROWID, ' || dff_rec.application_column_name ||
' FROM ' ||
'(SELECT ROWID, ' || dff_rec.application_column_name ||
' FROM ' || dff_rec.application_table_name ||
' WHERE ((:l_last_rowid IS NULL) OR (ROWID > :l_last_rowid))';
l_sql_select := l_sql_select || l_addtl_where;
l_sql_select := l_sql_select || ' ORDER BY ROWID)';
l_sql_select := l_sql_select || ' WHERE (ROWNUM <= :b_block_size)';
l_sql_update := get_sql_update(dff_rec.application_id,
dff_rec.application_table_name,
dff_rec.application_column_name,
p_who_rec);
l_sql_select, l_sql_update, l_upg_count);
UPDATE fnd_id_flex_segments
SET
default_value = l_vc2_tmp1,
last_update_date = p_who_rec.last_update_date,
last_updated_by = p_who_rec.last_updated_by
WHERE application_id = kff_rec.application_id
AND id_flex_code = kff_rec.id_flex_code
AND id_flex_num = kff_rec.id_flex_num
AND application_column_name = kff_rec.application_column_name;
'Failure in UPDATE FND_ID_FLEX_SEGMENTS.',
Sqlerrm);
l_sql_select := ('SELECT COUNT(*)' ||
' FROM ' || kff_rec.application_table_name ||
' WHERE (''/* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */'' IS NOT NULL)');
l_sql_select := l_sql_select || l_addtl_where;
EXECUTE IMMEDIATE l_sql_select INTO l_rows_count USING l_bind_value;
EXECUTE IMMEDIATE l_sql_select INTO l_rows_count;
l_sql_select :=
'SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */ ' ||
' ROWID, ' || kff_rec.application_column_name ||
' FROM ' ||
'(SELECT ROWID, ' || kff_rec.application_column_name ||
' FROM ' || kff_rec.application_table_name ||
' WHERE ((:l_last_rowid IS NULL) OR (ROWID > :l_last_rowid))';
l_sql_select := l_sql_select || l_addtl_where;
l_sql_select := l_sql_select || ' ORDER BY ROWID)';
l_sql_select := l_sql_select || ' WHERE (ROWNUM <= :b_block_size)';
l_sql_update := get_sql_update(kff_rec.application_id,
kff_rec.application_table_name,
kff_rec.application_column_name,
p_who_rec);
l_sql_select, l_sql_update, l_upg_count);
p_sql_select IN VARCHAR2)
IS
l_flex_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
OPEN l_vset_cur FOR p_sql_select;
('SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */ ' ||
' flex_value_set_name ' ||
' FROM fnd_flex_value_sets ' ||
' WHERE format_type IN (''X'', ''Y'')');
p_sql_select => l_sql );
('SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */ ' ||
' flex_value_set_name ' ||
' FROM fnd_flex_value_sets ' ||
' WHERE (format_type = ''N'' OR ' ||
' (format_type = ''C'' AND ' ||
' alphanumeric_allowed_flag = ''N''))');
p_sql_select => l_sql);
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
fa.application_id,
fa.application_short_name,
fa.application_name,
df.descriptive_flexfield_name,
df.title,
dfc.descriptive_flex_context_code,
dfc.descriptive_flex_context_name,
fl1.meaning context_enabled_flag_lookup,
dfcu.application_column_name,
dfcu.end_user_column_name,
dfcu.form_left_prompt,
fl2.meaning segment_enabled_flag_lookup,
fvs.flex_value_set_id,
fvs.flex_value_set_name,
fvs.maximum_size,
fl3.meaning format_type_lookup
FROM fnd_descr_flex_col_usage_vl dfcu, fnd_descr_flex_contexts_vl dfc,
fnd_descriptive_flexs_vl df, fnd_application_vl fa,
fnd_lookups fl1, fnd_lookups fl2, fnd_lookups fl3,
fnd_flex_value_sets fvs
WHERE df.application_id = fa.application_id
AND dfc.application_id = df.application_id
AND dfc.descriptive_flexfield_name = df.descriptive_flexfield_name
AND dfc.enabled_flag = fl1.lookup_code
AND fl1.lookup_type = 'YES_NO'
AND dfcu.application_id = dfc.application_id
AND dfcu.descriptive_flexfield_name = dfc.descriptive_flexfield_name
AND dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
AND dfcu.enabled_flag = fl2.lookup_code
AND fl2.lookup_type = 'YES_NO'
AND dfcu.flex_value_set_id = fvs.flex_value_set_id
AND fvs.format_type in ('D', 'T')
AND fvs.format_type = fl3.lookup_code
AND fl3.lookup_type = 'FIELD_TYPE'
AND ((p_srs_or_dff = 'SRS' AND
df.descriptive_flexfield_name LIKE '$SRS$.%') OR
(p_srs_or_dff = 'DFF' AND
df.descriptive_flexfield_name NOT LIKE '$SRS$.%'));
SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
fa.application_id,
fa.application_short_name,
fa.application_name,
idf.id_flex_code,
idf.id_flex_name,
ifst.id_flex_num,
ifst.id_flex_structure_code,
ifst.id_flex_structure_name,
fl1.meaning structure_enabled_flag_lookup,
ifsg.application_column_name,
ifsg.segment_name,
ifsg.form_left_prompt,
fl2.meaning segment_enabled_flag_lookup,
fvs.flex_value_set_id,
fvs.flex_value_set_name,
fvs.maximum_size,
fl3.meaning format_type_lookup
FROM fnd_id_flex_segments_vl ifsg, fnd_id_flex_structures_vl ifst,
fnd_id_flexs idf, fnd_application_vl fa,
fnd_lookups fl1, fnd_lookups fl2, fnd_lookups fl3,
fnd_flex_value_sets fvs
WHERE idf.application_id = fa.application_id
AND ifst.application_id = idf.application_id
AND ifst.id_flex_code = idf.id_flex_code
AND ifst.enabled_flag = fl1.lookup_code
AND fl1.lookup_type = 'YES_NO'
AND ifsg.application_id = ifst.application_id
AND ifsg.id_flex_code = ifst.id_flex_code
AND ifsg.id_flex_num = ifst.id_flex_num
AND ifsg.flex_value_set_id = fvs.flex_value_set_id
AND ifsg.enabled_flag = fl2.lookup_code
AND fl2.lookup_type = 'YES_NO'
AND fvs.format_type in ('D', 'T')
AND fvs.format_type = fl3.lookup_code
AND fl3.lookup_type = 'FIELD_TYPE';
SELECT Decode
(p_menu_choice,
1, Decode
(p_step,
0, ('-- List Report Parameters: ' ||
'You will be asked to enter 6 inputs. ' ||
'Please ignore the last 4 of them.'),
1, 'Please enter the application short name [% for all] :',
2, 'Please enter the report name like [% for all] : ',
NULL),
2, Decode
(p_step,
0, ('-- List Descriptive Flexfield Segments: '||
'You will be asked to enter 6 inputs. ' ||
'Please ignore the last 3 of them.'),
1, 'Please enter the application short name [% for all] :',
2, 'Please enter the descriptive flexfield name like [% for all] : ',
3, 'Please enter the context code like [% for all] : ',
NULL),
3, Decode
(p_step,
0, ('-- List Key Flexfield Segments: ' ||
'You will be asked to enter 6 inputs. ' ||
'Please ignore the last 2 of them.'),
1, 'Please enter the application short name [% for all] :',
2, 'Please enter the key flexfield code [% for all] : ',
3, 'Please enter the structure number like [% for all] :',
4, 'Please enter the structure name like [% for all] :',
NULL),
4, Decode
(p_step,
0, ('-- Clone a value set: ' ||
'You are about to clone one of the Date or DateTime value ' ||
'sets to Standard Date or Standard DateTime value set. ' ||
'We recommend you use _STANDARD as a ' ||
'new name for your value set. ' ||
'You will be asked to enter 6 inputs. ' ||
'Please ignore the last 4 of them.'),
1, 'Please enter the old value set name :',
2, 'Please enter the new value set name :',
NULL),
5, Decode
(p_step,
0, ('-- Upgrade Report Parameters: ' ||
'You are about to upgrade report parameters which use ' ||
'Date or DateTime value sets, and these value sets will be ' ||
'replaced with Standard Date or Standard DateTime value sets. ' ||
'By using a % sign in the report name, you can upgrade multiple ' ||
'report parameters. ' ||
'You will be asked to enter 6 inputs. ' ||
'Please ignore the last 2 of them.'),
1, 'Please enter the application short name :',
2, 'Please enter the old value set name :',
3, 'Please enter the new value set name :',
4, 'Please enter the report name like [% for all] :',
NULL),
6, Decode
(p_step,
0, ('-- Upgrade Descriptive Flexfield Segments: ' ||
'You are about to upgrade descriptive flexfield segments which ' ||
'use Date or DateTime value sets, and these value sets will be ' ||
'replaced with Standard Date or Standard DateTime value sets. ' ||
'By using a % sign in the descriptive flexfield name, or context ' ||
'code, you can upgrade multiple descriptive flexfields and/or ' ||
'contexts. ' ||
'You will be asked to enter 6 inputs. ' ||
'Please ignore the last one.'),
1, 'Please enter the application short name :',
2, 'Please enter the old value set name :',
3, 'Please enter the new value set name :',
4, 'Please enter the descriptive flexfield name like [% for all] :',
5, 'Please enter the context code like [% for all] :',
NULL),
7, Decode
(p_step,
0, ('-- Upgrade Key Flexfield Segments: ' ||
'You are about to upgrade key flexfield segments which use ' ||
'Date or DateTime value sets, and these value sets will be ' ||
'replaced with Standard Date or Standard DateTime value sets. ' ||
'By using a % sign in the structure number or structure name ' ||
'you can upgrade multiple key flexfield structures. ' ||
'You will be asked to enter 6 inputs.'),
1, 'Please enter the application short name :',
2, 'Please enter the key flexfield code :',
3, 'Please enter the old value set name :',
4, 'Please enter the new value set name :',
5, 'Please enter the structure number like [% for all] :',
6, 'Please enter the structure name like [% for all] :',
NULL),
8, Decode
(p_step,
0, ('-- Upgrade to Translatable Independent/Dependent value set: ' ||
'You are about to upgrade an Independent/Dependent Value set to a ' ||
'Translatable Independent/Dependent Value set. ' ||
'You will be asked to enter the Independent value set name. ' ||
'This script will try to upgrade this value set to a Translatable ' ||
'Independent value set and it will also try to upgrade all ' ||
'dependent value sets (which depend on the given independent ' ||
'value set) to Translatable Dependent value sets. ' ||
'You will be asked to enter 6 inputs. ' ||
'Please ignore the last 5 of them.'),
1, 'Please enter the independent value set name :',
NULL),
Decode
(p_step,
0, 'Invalid menu choice.',
NULL))
INTO l_prompt
FROM dual;