[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
language_code
FROM fnd_languages
WHERE installed_flag IN ('I', 'B');
FUNCTION last_update_date_f RETURN DATE IS
BEGIN
RETURN creation_date_f;
FUNCTION last_updated_by_f RETURN NUMBER IS
BEGIN
RETURN created_by_f;
FUNCTION last_update_login_f RETURN NUMBER IS
BEGIN
RETURN 0;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
application_id
INTO application_id_ret
FROM fnd_application
WHERE application_short_name = application_short_name_in;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
table_id
INTO table_id_ret
FROM fnd_tables
WHERE table_name = table_name_in
AND application_id = application_id_in;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
flex_value_set_id
INTO value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = value_set_name;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
NULL INTO dummy
FROM fnd_descriptive_flexs
WHERE application_id = application_id_in
AND descriptive_flexfield_name = descriptive_flexfield_name_in;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
NULL INTO dummy
FROM fnd_descr_flex_contexts
WHERE application_id = application_id_in
AND descriptive_flexfield_name = descriptive_flexfield_name_in
AND descriptive_flex_context_code = descr_flex_context_code_in;
/* insert functions */
/* ------------------------------------------------------------ */
PROCEDURE ins_descriptive_flexs(
application_id_in IN NUMBER,
application_table_name IN VARCHAR2,
descriptive_flexfield_name IN VARCHAR2,
table_application_id IN NUMBER,
concatenated_segs_view_name IN VARCHAR2,
context_required_flag IN VARCHAR2,
context_column_name IN VARCHAR2,
context_user_override_flag IN VARCHAR2,
concatenated_segment_delimiter IN VARCHAR2,
freeze_flex_definition_flag IN VARCHAR2,
protected_flag IN VARCHAR2,
default_context_field_name IN VARCHAR2,
default_context_value IN VARCHAR2)
IS
last_update_login fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
NULL INTO dummy
FROM fnd_tables
WHERE table_name = application_table_name
AND application_id = table_application_id;
INSERT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
INTO fnd_descriptive_flexs(application_id,
application_table_name,
descriptive_flexfield_name,
table_application_id,
concatenated_segs_view_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
context_required_flag,
context_synchronization_flag,
context_column_name,
context_user_override_flag,
concatenated_segment_delimiter,
freeze_flex_definition_flag,
protected_flag,
default_context_field_name,
default_context_value)
VALUES(application_id_in,
application_table_name,
descriptive_flexfield_name,
table_application_id,
concatenated_segs_view_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
context_required_flag,
'X',
context_column_name,
context_user_override_flag,
concatenated_segment_delimiter,
freeze_flex_definition_flag,
protected_flag,
default_context_field_name,
default_context_value);
println('inserted into fnd_descriptive_flexs');
message('insert to fnd_descriptive_flexs failed - ' ||
'duplicate flexfield name or application id');
message('insert to fnd_descriptive_flexs failed - ' ||
'value too large');
last_update_login_i fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date_i fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_descriptive_flexs SET
context_required_flag = context_required_flag_in,
context_user_override_flag = context_user_override_flag_in,
concatenated_segment_delimiter = concat_segment_delimiter_in,
freeze_flex_definition_flag = freeze_flex_definition_flag_in,
default_context_field_name = default_context_field_name_in,
default_context_value = default_context_value_in,
context_default_type = p_context_default_type,
context_default_value = p_context_default_value,
context_override_value_set_id = p_context_override_value_set_i,
context_runtime_property_funct = p_context_runtime_property_fun
WHERE application_id = application_id_in
AND descriptive_flexfield_name = descriptive_flexfield_name_in;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_descriptive_flexs SET
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = application_id_in
AND descriptive_flexfield_name = descriptive_flexfield_name_in;
message('update on fnd_descriptive_flexs failed');
/* insert records, one for each installed language */
PROCEDURE insmul_descriptive_flexs_tl(
application_id IN NUMBER,
descriptive_flexfield_name IN VARCHAR2,
title IN VARCHAR2,
description IN VARCHAR2,
form_context_prompt IN VARCHAR2)
IS
last_update_login fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
println('inserting into fnd_descriptive flexs tl');
INSERT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
INTO fnd_descriptive_flexs_tl(application_id,
descriptive_flexfield_name,
title,
description,
form_context_prompt,
language,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
source_lang)
VALUES(application_id,
descriptive_flexfield_name,
title,
description,
form_context_prompt,
lang_rec.language_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
userenv('LANG'));
message('insert failed - duplicate language, flexfield name, or application id');
message('insert failed - value too large');
last_update_login fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
INSERT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
INTO fnd_descr_flex_contexts(application_id,
descriptive_flexfield_name,
descriptive_flex_context_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
enabled_flag,
global_flag)
VALUES(application_id,
descriptive_flexfield_name,
descriptive_flex_context_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
enabled_flag,
global_flag);
message('insert failed - duplicate value on index');
message('insert failed - value too large');
last_update_login fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
INSERT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
INTO fnd_descr_flex_contexts_tl(application_id,
descriptive_flexfield_name,
descriptive_flex_context_code,
descriptive_flex_context_name,
description,
language,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
source_lang)
VALUES(application_id,
descriptive_flexfield_name,
descriptive_flex_context_code,
descriptive_flex_context_name,
description,
lang_rec.language_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
userenv('LANG'));
message('insert to fnd_descr_flex_contexts failed - ' ||
'duplicate value on index');
message('insert to fnd_descr_flex_contexts failed - ' ||
'value too large');
last_update_login fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
INSERT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
INTO fnd_descr_flex_column_usages(application_id,
descriptive_flexfield_name,
descriptive_flex_context_code,
application_column_name,
end_user_column_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
column_seq_num,
enabled_flag,
required_flag,
security_enabled_flag,
display_flag,
display_size,
maximum_description_len,
concatenation_description_len,
flex_value_set_id,
range_code,
default_type,
default_value,
runtime_property_function,
srw_param)
VALUES(application_id,
descriptive_flexfield_name,
descriptive_flex_context_code,
application_column_name,
end_user_column_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
column_seq_num,
enabled_flag,
required_flag,
security_enabled_flag,
display_flag,
display_size,
maximum_description_len,
concatenation_description_len,
flex_value_set_id,
range_code,
default_type,
default_value,
runtime_property_function,
srw_param);
message('insert failed - duplicate value on index');
message('insert failed - value too large');
last_update_login fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
INSERT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
INTO fnd_descr_flex_col_usage_tl(application_id,
descriptive_flexfield_name,
descriptive_flex_context_code,
application_column_name,
form_left_prompt,
form_above_prompt,
description,
language,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
source_lang)
VALUES(application_id,
descriptive_flexfield_name,
descriptive_flex_context_code,
application_column_name,
form_left_prompt,
form_above_prompt,
description,
lang_rec.language_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
userenv('LANG'));
message('insert failed - duplicate value on index');
message('insert failed - value too large');
last_update_login fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
INSERT
INTO fnd_default_context_fields(application_id,
descriptive_flexfield_name,
default_context_field_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
description)
VALUES(application_id_in,
flexfield_name_in,
context_field_name_in,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
description_in);
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
NULL INTO dummy
FROM fnd_columns c, fnd_tables t, fnd_descriptive_flexs df
WHERE df.application_id = application_id_in
AND df.descriptive_flexfield_name = descriptive_flexfield_name_in
AND t.application_id = df.table_application_id
AND t.table_name = df.application_table_name
AND c.table_id = t.table_id
AND c.application_id = t.application_id
AND c.column_name = application_column_name_in
AND c.flexfield_name = descriptive_flexfield_name_in
AND c.flexfield_application_id = application_id_in
AND c.flexfield_usage_code = 'D';
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
descriptive_flex_context_code
INTO global_context_code_i
FROM fnd_descr_flex_contexts
WHERE application_id = application_id_in
AND descriptive_flexfield_name = descriptive_flexfield_name_in
AND global_flag = 'Y';
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
NULL INTO dummy FROM dual
WHERE NOT EXISTS
(SELECT NULL
FROM fnd_descr_flex_column_usages cu
WHERE cu.application_id = application_id_in
AND cu.descriptive_flexfield_name = descriptive_flexfield_name_in
AND cu.application_column_name = application_column_name_in
AND ( -- already in use in the current context
(descriptive_flex_context_code = descr_flex_context_code_in)
OR -- already in the global context
(descriptive_flex_context_code = global_context_code_i)
OR -- we are in the global context
(descr_flex_context_code_in = global_context_code_i)));
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
NULL INTO dummy
FROM fnd_default_context_fields
WHERE application_id = application_id_in
AND descriptive_flexfield_name = descriptive_flexfield_name_in
AND default_context_field_name = context_field_in;
last_update_login_i fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date_i fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
flexfield_usage_code INTO ffucode
FROM fnd_columns
WHERE application_id = register.table_application_id_i
AND table_id = register.application_table_id_i
-- AND flexfield_usage_code = 'N'
AND column_name = register.structure_column;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_columns SET
flexfield_usage_code = 'C'
WHERE application_id = register.table_application_id_i
AND table_id = register.application_table_id_i
-- AND flexfield_usage_code = 'N'
AND column_name = register.structure_column;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_columns SET
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = register.table_application_id_i
AND table_id = register.application_table_id_i
AND flexfield_usage_code = 'N'
AND column_name = register.structure_column;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_columns SET
flexfield_usage_code = 'C',
flexfield_application_id = register.application_id_i,
flexfield_name = register.flexfield_name
WHERE application_id = register.table_application_id_i
AND table_id = register.application_table_id_i
AND flexfield_usage_code = 'N'
AND column_name = register.structure_column;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_columns SET
flexfield_usage_code = 'D'
WHERE application_id = register.table_application_id_i
AND table_id = register.application_table_id_i
AND flexfield_usage_code = 'N'
AND column_name LIKE Nvl(enable_columns, 'ATTRIBUTE%')
AND ((column_name IS NOT NULL)
OR (Rtrim(column_name, '0123456789') = 'ATTRIBUTE'));
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
COUNT(*) INTO numcount
FROM fnd_columns
WHERE application_id = register.table_application_id_i
AND table_id = register.application_table_id_i
AND flexfield_usage_code = 'D'
AND column_name LIKE Nvl(enable_columns, 'ATTRIBUTE%')
AND ((column_name IS NOT NULL)
OR (Rtrim(column_name, '0123456789') = 'ATTRIBUTE'));
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_columns SET
last_update_date = register.last_update_date_i,
last_updated_by = register.last_updated_by_i,
last_update_login = register.last_update_login_i
WHERE application_id = register.table_application_id_i
AND table_id = register.application_table_id_i
AND flexfield_usage_code = 'N'
AND column_name LIKE Nvl(enable_columns, 'ATTRIBUTE%')
AND ((column_name IS NOT NULL)
OR (Rtrim(column_name, '0123456789') = 'ATTRIBUTE'));
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_columns SET
flexfield_usage_code = 'D',
flexfield_application_id = register.application_id_i,
flexfield_name = register.flexfield_name
WHERE application_id = register.table_application_id_i
AND table_id = register.application_table_id_i
AND flexfield_usage_code = 'N'
AND column_name LIKE Nvl(enable_columns, 'ATTRIBUTE%')
AND ((column_name IS NOT NULL)
OR (Rtrim(column_name, '0123456789') = 'ATTRIBUTE'));
last_update_login_i fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date_i fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
table_application_id, table_id
INTO table_application_id_i, application_table_id_i
FROM fnd_descriptive_flexs df, fnd_tables t
WHERE df.application_id = application_id_i
AND df.descriptive_flexfield_name = flexfield_name
AND t.application_id = df.table_application_id
AND t.table_name = df.application_table_name;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_columns SET
flexfield_usage_code = 'D',
flexfield_application_id = application_id_i,
flexfield_name = enable_columns.flexfield_name
WHERE application_id = table_application_id_i
AND table_id = application_table_id_i
AND flexfield_usage_code = 'N'
AND column_name LIKE pattern;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_columns SET
flexfield_usage_code = 'D',
flexfield_application_id = application_id_i,
flexfield_name = enable_columns.flexfield_name,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = table_application_id_i
AND table_id = application_table_id_i
AND flexfield_usage_code = 'N'
AND column_name LIKE pattern;
message('could not update fnd_columns:' ||
Sqlerrm);
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
flex_value_set_id
INTO l_context_override_value_set_i
FROM fnd_flex_value_sets
WHERE flex_value_set_name = context_override_value_set_nam;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_descriptive_flexs_tl
SET
form_context_prompt = prompt
WHERE application_id = application_id_i
AND descriptive_flexfield_name = flexfield_name;
last_update_login_i fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date_i fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_descriptive_flexs SET
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i,
freeze_flex_definition_flag = 'Y'
WHERE application_id = appid
AND flexfield_name = descriptive_flexfield_name;
println('inserting into desc flex contexts');
println('inserting into desc flex contexts tl');
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
Decode(validation_type, 'P', 'P', NULL)
INTO range_code_i
FROM fnd_flex_value_sets v
WHERE v.flex_value_set_id = value_set_id_i;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
NULL
INTO dummy
FROM fnd_flex_value_sets v
WHERE v.flex_value_set_id = value_set_id_i
AND Decode(v.validation_type, 'P', 'P', 'A')
= Decode(range_code_i, 'P', 'P', 'A');
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
c.column_type, c.width,
df.application_table_name
INTO application_column_type_i, application_column_width_i,
application_table_name_i
FROM fnd_columns c, fnd_tables t, fnd_descriptive_flexs df
WHERE c.application_id = t.application_id
AND c.table_id = t.table_id
AND c.column_name = create_segment.column
AND t.table_name = df.application_table_name
AND t.application_id = df.table_application_id
AND df.application_id = create_segment.application_id_i
AND df.descriptive_flexfield_name = create_segment.flexfield_name;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
NULL INTO dummy
FROM fnd_flex_value_sets v, fnd_lookup_values vt,
fnd_lookup_values ft
WHERE v.flex_value_set_id = value_set_id_i
AND vt.lookup_type = 'SEG_VAL_TYPES'
AND vt.lookup_code = v.validation_type
AND ft.lookup_type = 'FIELD_TYPE'
AND ft.lookup_code = v.format_type
AND ROWNUM = 1;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
NULL INTO dummy
FROM fnd_flex_value_sets v, fnd_flex_validation_tables t
WHERE v.flex_value_set_id = value_set_id_i
AND v.flex_value_set_id = t.flex_value_set_id (+)
AND (application_column_type_i IN ('C', 'V')
OR v.validation_type = 'U'
OR application_column_type_i = Nvl(t.id_column_type,
Decode(v.format_type,
'M', 'N',
'T', 'D',
'I', 'D',
'X', 'D',
'Y', 'D',
'Z', 'D',
v.format_type)))
AND (application_column_type_i = 'D'
OR application_column_width_i
>= Nvl(t.id_column_size, v.maximum_size))
AND(v.validation_type <> 'D'
OR EXISTS (SELECT NULL
FROM fnd_descr_flex_column_usages u
WHERE application_id = application_id_i
AND descriptive_flexfield_name
= create_segment.flexfield_name
AND descriptive_flex_context_code
= create_segment.context_name
AND u.flex_value_set_id
= v.parent_flex_value_set_id));
l_last_update_login fnd_descr_flex_col_usage_vl.last_update_login%TYPE
:= last_update_login_f;
l_last_update_date fnd_descr_flex_col_usage_vl.last_update_date%TYPE
:= last_update_date_f;
l_last_updated_by fnd_descr_flex_col_usage_vl.last_updated_by%TYPE
:= last_updated_by_f;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
description, column_seq_num, enabled_flag,
display_flag, flex_value_set_id, default_type,
default_value, required_flag, security_enabled_flag,
display_size, maximum_description_len, concatenation_description_len,
form_above_prompt, form_left_prompt, range_code, srw_param,
runtime_property_function,
application_column_name,
end_user_column_name
INTO l_description, l_sequence_number, l_enabled,
l_displayed, l_flex_value_set_id, l_default_type,
l_default_value, l_required, l_security_enabled,
l_display_size, l_description_size, l_concat_desc_size,
l_lov_prompt, l_window_prompt, l_range, l_srw_parameter,
l_runtime_property_function,
l_column_name,
l_segment_name
FROM fnd_descr_flex_col_usage_vl
WHERE application_id = l_application_id
AND descriptive_flexfield_name = p_flexfield_name
AND descriptive_flex_context_code = p_context_code
AND (((p_column_name IS NOT NULL) AND
(application_column_name = p_column_name)) OR
((p_segment_name IS NOT NULL) AND
(end_user_column_name = p_segment_name)));
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
NULL INTO dummy
FROM fnd_flex_value_sets v, fnd_lookup_values vt,
fnd_lookup_values ft
WHERE v.flex_value_set_id = l_flex_value_set_id
AND vt.lookup_type = 'SEG_VAL_TYPES'
AND vt.lookup_code = v.validation_type
AND ft.lookup_type = 'FIELD_TYPE'
AND ft.lookup_code = v.format_type
AND ROWNUM = 1;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
c.column_type, c.width, df.application_table_name
INTO l_column_type, l_column_width, l_table_name
FROM fnd_columns c, fnd_tables t, fnd_descriptive_flexs df
WHERE c.application_id = t.application_id
AND c.table_id = t.table_id
AND c.column_name = l_column_name
AND t.table_name = df.application_table_name
AND t.application_id = df.table_application_id
AND df.application_id = l_application_id
AND df.descriptive_flexfield_name = p_flexfield_name;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
NULL INTO dummy
FROM fnd_flex_value_sets v, fnd_flex_validation_tables t
WHERE v.flex_value_set_id = l_flex_value_set_id
AND v.flex_value_set_id = t.flex_value_set_id (+)
AND (l_column_type IN ('C', 'V')
OR v.validation_type = 'U'
OR l_column_type = Nvl(t.id_column_type,
Decode(v.format_type,
'M', 'N',
'T', 'D',
'I', 'D',
'X', 'D',
'Y', 'D',
'Z', 'D',
v.format_type)))
AND (l_column_type = 'D'
OR l_column_width
>= Nvl(t.id_column_size, v.maximum_size))
AND(v.validation_type <> 'D'
OR EXISTS (SELECT NULL
FROM fnd_descr_flex_column_usages u
WHERE application_id = l_application_id
AND descriptive_flexfield_name
= p_flexfield_name
AND descriptive_flex_context_code
= p_context_code
AND u.flex_value_set_id
= v.parent_flex_value_set_id));
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
Decode(validation_type, 'P', 'P', NULL)
INTO l_range
FROM fnd_flex_value_sets
WHERE flex_value_set_id = l_flex_value_set_id;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
NULL
INTO dummy
FROM fnd_flex_value_sets
WHERE flex_value_set_id = l_flex_value_set_id
AND Decode(validation_type, 'P', 'P', 'A') =
Decode(l_range, 'P', 'P', 'A');
fnd_descr_flex_col_usage_pkg.update_row
(x_application_id => l_application_id,
x_descriptive_flexfield_name => p_flexfield_name,
x_descriptive_flex_context_cod => p_context_code,
x_application_column_name => l_column_name,
x_end_user_column_name => l_segment_name,
x_column_seq_num => l_sequence_number,
x_enabled_flag => l_enabled,
x_required_flag => l_required,
x_security_enabled_flag => l_security_enabled,
x_display_flag => l_displayed,
x_display_size => l_display_size,
x_maximum_description_len => l_description_size,
x_concatenation_description_le => l_concat_desc_size,
x_flex_value_set_id => l_flex_value_set_id,
x_range_code => l_range,
x_default_type => l_default_type,
x_default_value => l_default_value,
x_runtime_property_function => l_runtime_property_function,
x_srw_param => l_srw_parameter,
x_form_left_prompt => l_window_prompt,
x_form_above_prompt => l_lov_prompt,
x_description => l_description,
x_last_update_date => l_last_update_date,
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_update_login);
PROCEDURE delete_reference_field(appl_short_name IN VARCHAR2,
flexfield_name IN VARCHAR2,
context_field_name IN VARCHAR2)
IS
application_id_i fnd_application.application_id%TYPE;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
count('x')
INTO rec_count
FROM fnd_descriptive_flexs
WHERE application_id = application_id_i
AND descriptive_flexfield_name = flexfield_name
AND default_context_field_name = context_field_name;
last_update_login_i fnd_flex_value_sets.last_update_login%TYPE
:= last_update_login_f;
last_update_date_i fnd_flex_value_sets.last_update_date%TYPE
:= last_update_date_f;
last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE
:= last_updated_by_f;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
table_application_id, table_id
INTO table_application_id_i, application_table_id_i
FROM fnd_descriptive_flexs df, fnd_tables t
WHERE df.application_id = application_id_i
AND df.descriptive_flexfield_name = flexfield_name
AND t.application_id = df.table_application_id
AND t.table_name = df.application_table_name;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_columns SET
flexfield_usage_code = 'N',
flexfield_application_id = NULL,
flexfield_name = NULL
WHERE application_id = table_application_id_i
AND table_id = application_table_id_i
AND flexfield_usage_code = 'D'
AND column_name LIKE pattern;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_columns SET
flexfield_usage_code = 'N',
flexfield_application_id = application_id_i,
flexfield_name = disable_columns.flexfield_name,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = table_application_id_i
AND table_id = application_table_id_i
AND flexfield_usage_code = 'D'
AND column_name LIKE pattern;
message('could not update fnd_columns:' ||
Sqlerrm);
PROCEDURE delete_flexfield(appl_short_name IN VARCHAR2,
flexfield_name IN VARCHAR2)
IS
application_id_i fnd_application.application_id%TYPE;
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_descr_flex_col_usage_tl
WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
AND application_id = application_id_i;
println('deleted from fnd_descr_col_usage_tl');
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_descr_flex_column_usages
WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
AND application_id = application_id_i;
println('deleted from fnd_descr_column_usages');
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_descr_flex_contexts_tl
WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
AND application_id = application_id_i;
println('deleted from fnd_descr_flex_contexts_tl');
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_descr_flex_contexts
WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
AND application_id = application_id_i;
println('deleted from fnd_descr_flex_contexts');
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_descriptive_flexs_tl
WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
AND application_id = application_id_i;
println('deleted from fnd_descriptive_flexs_tl');
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
table_application_id
INTO table_application_id_i
FROM fnd_descriptive_flexs
WHERE descriptive_flexfield_name = flexfield_name
AND application_id = application_id_i;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_columns SET
flexfield_usage_code = 'N',
flexfield_name = NULL
WHERE flexfield_name = delete_flexfield.flexfield_name
AND application_id = table_application_id_i;
println('updated fnd_columns');
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_descriptive_flexs
WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
AND application_id = application_id_i;
println('deleted from fnd_descriptive_flexs');
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_default_context_fields
WHERE application_id = application_id_i
AND descriptive_flexfield_name = flexfield_name;
println('deleted from fnd_default_context_fields');
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_compiled_descriptive_flexs
WHERE application_id = application_id_i
AND descriptive_flexfield_name = flexfield_name;
println('deleted fnd_compiled_descriptive_flexs');
println('deleted flexfield:' || flexfield_name);
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_descr_flex_contexts
SET enabled_flag = enabled_flag_i
WHERE descriptive_flex_context_code = context
AND descriptive_flexfield_name = flexfield_name
AND application_id = application_id_i;
PROCEDURE update_context(
p_appl_short_name IN VARCHAR2,
p_flexfield_name IN VARCHAR2,
p_desc_flex_context_code IN VARCHAR2,
p_desc_flex_context_name IN VARCHAR2 DEFAULT NULL,
p_description IN VARCHAR2 DEFAULT NULL,
p_enabled_flag IN VARCHAR2 DEFAULT NULL,
p_language IN VARCHAR2)
IS
l_application_id fnd_application.application_id%TYPE;
select 1 into l_lang_exists from fnd_languages where
language_code = p_language and installed_flag in ('B','I') and rownum < 2;
raise_application_error(-20204, 'FND_FLEX_DSC_API.UPDATE_CONTEXT raised exception: No language exist with language_code ='|| p_language, TRUE);
SELECT * into l_dflex_contexts_tl
from fnd_descr_flex_contexts_tl
WHERE descriptive_flex_context_code = p_desc_flex_context_code
AND descriptive_flexfield_name = p_flexfield_name
AND application_id = l_application_id
AND language = p_language;
UPDATE fnd_descr_flex_contexts_tl
SET descriptive_flex_context_name = nvl(p_desc_flex_context_name, l_dflex_contexts_tl.descriptive_flex_context_name),
description = nvl(p_description, l_dflex_contexts_tl.description)
WHERE descriptive_flex_context_code = p_desc_flex_context_code
AND descriptive_flexfield_name = p_flexfield_name
AND application_id = l_application_id
AND language = p_language;
SELECT enabled_flag into l_enabled_flag from fnd_descr_flex_contexts
WHERE descriptive_flex_context_code = p_desc_flex_context_code
AND descriptive_flexfield_name = p_flexfield_name
AND application_id = l_application_id;
UPDATE fnd_descr_flex_contexts
SET enabled_flag = nvl(p_enabled_flag, l_enabled_flag)
WHERE descriptive_flex_context_code = p_desc_flex_context_code
AND descriptive_flexfield_name = p_flexfield_name
AND application_id = l_application_id;
PROCEDURE delete_context(appl_short_name IN VARCHAR2,
flexfield_name IN VARCHAR2,
context IN VARCHAR2)
IS
application_id_i fnd_application.application_id%TYPE;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
count('x')
INTO rec_count
FROM fnd_descriptive_flexs
WHERE application_id = application_id_i
AND descriptive_flexfield_name = flexfield_name
AND default_context_field_name = context;
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_descr_flex_col_usage_tl
WHERE descriptive_flex_context_code = context
AND descriptive_flexfield_name = delete_context.flexfield_name
AND application_id = application_id_i;
println('deleted from fnd_descr_col_usage_tl');
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_descr_flex_column_usages
WHERE descriptive_flex_context_code = context
AND descriptive_flexfield_name = delete_context.flexfield_name
AND application_id = application_id_i;
println('deleted from fnd_descr_column_usages');
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_descr_flex_contexts_tl
WHERE descriptive_flex_context_code = context
AND descriptive_flexfield_name = delete_context.flexfield_name
AND application_id = application_id_i;
println('deleted from fnd_descr_flex_contexts_tl');
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_descr_flex_contexts
WHERE descriptive_flex_context_code = context
AND descriptive_flexfield_name = delete_context.flexfield_name
AND application_id = application_id_i;
println('deleted from fnd_descr_flex_contexts');
println('deleted flexfield context:' ||
flexfield_name || '.' || context);
PROCEDURE delete_segment(appl_short_name IN VARCHAR2,
flexfield_name IN VARCHAR2,
context IN VARCHAR2,
segment IN VARCHAR2)
IS
application_id_i fnd_application.application_id%TYPE;
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_descr_flex_col_usage_tl t
WHERE
EXISTS (SELECT *
FROM fnd_descr_flex_column_usages cu
WHERE cu.end_user_column_name = segment
AND cu.descriptive_flex_context_code = context
AND cu.descriptive_flexfield_name = flexfield_name
AND cu.application_id = application_id_i
/* and join cond: */
AND cu.application_column_name = t.application_column_name
AND cu.descriptive_flex_context_code =
t.descriptive_flex_context_code
AND cu.descriptive_flexfield_name = t.descriptive_flexfield_name
AND cu.application_id = t.application_id);
println('deleted from fnd_descr_col_usage_tl');
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_descr_flex_column_usages
WHERE end_user_column_name = segment
AND descriptive_flex_context_code = context
AND descriptive_flexfield_name = flexfield_name
AND application_id = application_id_i;
println('deleted from fnd_descr_column_usages');
println('deleted flexfield segment:' ||
flexfield_name || '.' || context || '.' || segment);
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
COUNT(*)
INTO cnt
FROM fnd_descriptive_flexs
WHERE application_id = application_id_i
AND descriptive_flexfield_name = flexfield_name;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
COUNT(*) INTO cnt
FROM fnd_descr_flex_column_usages
WHERE application_id = l_application_id
AND descriptive_flexfield_name = p_flexfield_name
AND descriptive_flex_context_code = p_context_code
AND (((p_column_name IS NOT NULL) AND
(application_column_name = p_column_name)) OR
((p_segment_name IS NOT NULL) AND
(end_user_column_name = p_segment_name)));
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
application_id, descriptive_flexfield_name
INTO l_a_id, l_dff_name
FROM fnd_descriptive_flexs
WHERE table_application_id = p_application_id
AND Upper(application_table_name) = up_table_name
AND ROWNUM = 1;
'SELECT FROM FND_DESCRIPTIVE_FLEXS is failed. ' || chr_newline ||
'SQLERRM : ' || Sqlerrm;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
application_id, descriptive_flexfield_name,
context_column_name
INTO l_a_id, l_dff_name, l_context_col
FROM fnd_descriptive_flexs
WHERE table_application_id = p_application_id
AND Upper(application_table_name) = up_table_name
AND Upper(context_column_name) = up_column_name
AND ROWNUM = 1;
'SELECT FROM FND_DESCRIPTIVE_FLEXS is failed.'||chr_newline ||
'SQLERRM : ' || Sqlerrm;
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
dfcu.application_id, dfcu.descriptive_flexfield_name,
dfcu.descriptive_flex_context_code, dfcu.end_user_column_name
INTO l_a_id, l_dff_name, l_context, l_segment
FROM fnd_descr_flex_column_usages dfcu, fnd_descriptive_flexs df
WHERE df.application_id = dfcu.application_id
AND df.descriptive_flexfield_name = dfcu.descriptive_flexfield_name
AND df.table_application_id = p_application_id
AND Upper(df.application_table_name) = up_table_name
AND Upper(dfcu.application_column_name) = up_column_name
AND ROWNUM = 1;
'SELECT FROM FND_DESCR_FLEX_COLUMN_USAGES is failed.'||chr_newline ||
'SQLERRM : ' || Sqlerrm;
SELECT *
INTO x_application
FROM fnd_application fa
WHERE fa.application_short_name = p_application_short_name;
SELECT *
INTO x_table
FROM fnd_tables ft
WHERE ft.application_id = p_application_id
AND ft.table_name = p_table_name;
SELECT *
INTO x_descriptive_flexfield
FROM fnd_descriptive_flexs fdff
WHERE fdff.application_id = p_application_id
AND fdff.descriptive_flexfield_name = p_descriptive_flexfield_name;
l_last_update_date fnd_descriptive_flexs.last_update_date%TYPE;
l_last_updated_by fnd_descriptive_flexs.last_updated_by%TYPE;
l_last_update_login fnd_descriptive_flexs.last_update_login%TYPE;
l_last_update_date := SYSDATE;
l_last_updated_by := fnd_global.user_id();
l_last_update_login := fnd_global.login_id();
IF ((l_last_updated_by IS NULL) OR (l_last_updated_by = -1)) OR (l_last_update_login IS NULL) THEN
l_error_message := 'Application Security Context is not set.Please set the context using fnd_global.apps_initialize() and try again.';
SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
descriptive_flexfield_name
INTO l_dff_name
FROM fnd_descriptive_flexs
WHERE application_id = l_new_application.application_id
AND descriptive_flexfield_name = p_new_dff_name;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_descriptive_flexs
SET application_id = l_new_application.application_id,
descriptive_flexfield_name = p_new_dff_name,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE application_id = l_old_application.application_id
AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_descriptive_flexs_tl
SET application_id = l_new_application.application_id,
descriptive_flexfield_name = p_new_dff_name,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE application_id = l_old_application.application_id
AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_default_context_fields
SET application_id = l_new_application.application_id,
descriptive_flexfield_name = p_new_dff_name,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE application_id = l_old_application.application_id
AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_descr_flex_contexts
SET application_id = l_new_application.application_id,
descriptive_flexfield_name = p_new_dff_name,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE application_id = l_old_application.application_id
AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_descr_flex_contexts_tl
SET application_id = l_new_application.application_id,
descriptive_flexfield_name = p_new_dff_name,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE application_id = l_old_application.application_id
AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_descr_flex_column_usages
SET application_id = l_new_application.application_id,
descriptive_flexfield_name = p_new_dff_name,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE application_id = l_old_application.application_id
AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_descr_flex_col_usage_tl
SET application_id = l_new_application.application_id,
descriptive_flexfield_name = p_new_dff_name,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE application_id = l_old_application.application_id
AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_columns
SET flexfield_application_id = l_new_application.application_id,
flexfield_name = p_new_dff_name,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE application_id = l_table.application_id
AND table_id = l_table.table_id
AND flexfield_application_id = l_old_dff.application_id
AND flexfield_name = l_old_dff.descriptive_flexfield_name
AND flexfield_usage_code IN ('C','D');
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_compiled_descriptive_flexs
WHERE application_id = l_old_application.application_id
AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
FROM fnd_compiled_descriptive_flexs
WHERE application_id = l_new_application.application_id
AND descriptive_flexfield_name = l_new_dff.descriptive_flexfield_name;
l_last_update_date fnd_descriptive_flexs.last_update_date%TYPE;
l_last_updated_by fnd_descriptive_flexs.last_updated_by%TYPE;
l_last_update_login fnd_descriptive_flexs.last_update_login%TYPE;
SELECT old.column_name old_column_name,
old.column_type old_column_type,
old.width old_width,
old.flexfield_usage_code old_flexfield_usage_code,
old.flexfield_application_id old_flexfield_application_id,
old.flexfield_name old_flexfield_name,
new.column_name new_column_name,
new.column_type new_column_type,
new.width new_width,
new.flexfield_usage_code new_flexfield_usage_code,
new.flexfield_application_id new_flexfield_application_id,
new.flexfield_name new_flexfield_name
FROM fnd_columns old, fnd_columns new
WHERE old.application_id = p_old_table.application_id
AND old.table_id = p_old_table.table_id
AND old.flexfield_usage_code in ('C', 'D')
AND old.flexfield_application_id = p_dff.application_id
AND old.flexfield_name = p_dff.descriptive_flexfield_name
AND new.application_id(+) = p_new_table.application_id
AND new.table_id(+) = p_new_table.table_id
AND new.column_name(+) = old.column_name
ORDER BY old.column_sequence;
l_last_update_date := SYSDATE;
l_last_updated_by := fnd_global.user_id();
l_last_update_login := fnd_global.login_id();
IF ((l_last_updated_by IS NULL) OR (l_last_updated_by = -1)) OR (l_last_update_login IS NULL) THEN
l_error_message := 'Application Security Context not set.Please set the context using fnd_global.apps_initialize() and try again.';
UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
fnd_descriptive_flexs
SET application_table_name = p_new_table_name,
table_application_id = l_new_table.application_id,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE application_id = l_dff.application_id
AND descriptive_flexfield_name = l_dff.descriptive_flexfield_name;
UPDATE fnd_columns
SET flexfield_usage_code = rec_columns_reg.old_flexfield_usage_code,
flexfield_application_id = l_dff.application_id,
flexfield_name = l_dff.descriptive_flexfield_name,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE application_id = l_new_table.application_id
AND table_id = l_new_table.table_id
AND column_name = rec_columns_reg.new_column_name;
UPDATE fnd_columns
SET flexfield_usage_code = 'N',
flexfield_application_id = NULL,
flexfield_name = NULL,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE application_id = l_old_table.application_id
AND table_id = l_old_table.table_id
AND column_name = rec_columns_reg.old_column_name;