[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
last_update_login_i fnd_flex_value_sets.last_update_login%TYPE;
last_update_date_i fnd_flex_value_sets.last_update_date%TYPE;
last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE;
SELECT id_flex_structure_name structure_name,
id_flex_num structure_number
FROM fnd_id_flex_structures_vl
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND (structure_c.enabled IS NULL OR enabled_flag = 'Y')
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL
ORDER BY id_flex_code;
SELECT segment_name,
application_column_name
FROM fnd_id_flex_segments_vl
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND (enabled IS NULL or enabled_flag = 'Y')
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL
ORDER BY segment_num;
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;
last_update_login_i := last_update_login_f;
last_update_date_i := last_update_date_f;
last_updated_by_i := last_updated_by_f;
SELECT NULL
INTO dummy
FROM fnd_columns c --, fnd_lookups ct
WHERE c.application_id = flexfield.table_application_id
AND c.table_id = flexfield.table_id
AND c.column_name = column_name_in
-- AND c.flexfield_application_id = flexfield.application_id
-- AND c.flexfield_name = flexfield.flex_code
AND c.flexfield_usage_code = 'K'
-- AND ct.lookup_type = 'COLUMN_TYPE'
-- AND ct.lookup_code = column_type
-- check that it is not already in use
AND NOT EXISTS (SELECT NULL FROM fnd_id_flex_segments
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND application_column_name = c.column_name)
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT width, column_type
INTO application_column_size_i,
application_column_type_i
FROM fnd_columns
WHERE application_id = flexfield.table_application_id
AND table_id = flexfield.table_id
AND column_name = segment.column_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT NULL
INTO dummy
FROM fnd_flex_value_sets v,
fnd_flex_validation_tables t
WHERE v.flex_value_set_id = vset
AND v.flex_value_set_id = t.flex_value_set_id (+)
AND (flexfield.allow_id_value_sets = 'Y'
OR (flexfield.allow_id_value_sets = 'N' AND t.id_column_name IS NULL))
AND v.flex_value_set_name NOT LIKE '$FLEX$.%'
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', 't', 'D', 'X', 'D', 'Y', 'D', 'Z', 'D',
v.format_type))))
AND (application_column_type_i = 'D'
OR application_column_size_i >= Nvl(id_column_size, maximum_size))
AND (validation_type <> 'D' OR EXISTS
(SELECT NULL FROM fnd_id_flex_segments s
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND s.flex_value_set_id = v.parent_flex_value_set_id
AND segment_num < segment.segment_number))
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
fnd_flex_types.validate_yes_no_flag(flexfield.dynamic_inserts);
fnd_flex_types.validate_yes_no_flag(structure.dynamic_insert_flag);
SELECT count(*)
INTO row_count
FROM fnd_id_flex_structures_vl
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_structure_name = structure_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT count(*)
INTO row_count
FROM fnd_id_flex_segments_vl v
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND v.segment_name = check_duplicate_segment.segment_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT application_id
INTO application_id_ret
FROM fnd_application
WHERE application_short_name = application_short_name_in
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT table_id
INTO table_id_ret
FROM fnd_tables
WHERE table_name = table_name_in
AND application_id = application_id_in
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT flex_value_set_id
INTO value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = value_set_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
UPDATE fnd_columns SET
flexfield_usage_code = 'S',
-- flexfield_application_id = flexfield.application_id,
-- flexfield_name = flexfield.flex_code,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = flexfield.table_application_id
AND table_id = flexfield.table_id
AND column_name = structure_column
AND flexfield_usage_code = 'N'
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
UPDATE fnd_columns SET
flexfield_usage_code = 'N',
-- flexfield_application_id = flexfield.application_id,
-- flexfield_name = flexfield.flex_code,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = flexfield.table_application_id
AND table_id = flexfield.table_id
AND column_name = flexfield.structure_column
AND flexfield_usage_code = 'S'
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
UPDATE fnd_columns SET
flexfield_usage_code = 'I',
-- flexfield_application_id = flexfield.application_id,
-- flexfield_name = flexfield.flex_code,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = flexfield.table_application_id
AND table_id = flexfield.table_id
AND column_name = id_column
AND flexfield_usage_code = 'N'
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
UPDATE fnd_columns SET
flexfield_usage_code = 'N',
-- flexfield_application_id = flexfield.application_id,
-- flexfield_name = flexfield.flex_code,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = flexfield.table_application_id
AND table_id = flexfield.table_id
AND column_name = flexfield.unique_id_column
AND flexfield_usage_code = 'I'
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
f.dynamic_inserts := compose(f1.dynamic_inserts, f2.dynamic_inserts, dcnt);
s.dynamic_insert_flag := compose(s1.dynamic_insert_flag, s2.dynamic_insert_flag, dcnt);
dynamic_insert_flag IN VARCHAR2 DEFAULT 'N',
shorthand_enabled_flag IN VARCHAR2 DEFAULT 'N',
shorthand_prompt IN VARCHAR2,
shorthand_length IN NUMBER,
flex_num IN NUMBER)
IS
application_id_i fnd_id_flexs.application_id%TYPE;
fnd_id_flex_structures_pkg.insert_row(
X_ROWID => rowid_i,
X_APPLICATION_ID => application_id_i,
X_ID_FLEX_CODE => flex_code,
X_ID_FLEX_NUM => flex_num,
X_ID_FLEX_STRUCTURE_CODE => structure_code,
X_CONCATENATED_SEGMENT_DELIMIT => segment_separator,
X_CROSS_SEGMENT_VALIDATION_FLA => cross_val_flag,
X_DYNAMIC_INSERTS_ALLOWED_FLAG => dynamic_insert_flag,
X_ENABLED_FLAG => enabled_flag,
X_FREEZE_FLEX_DEFINITION_FLAG => freeze_flag,
X_FREEZE_STRUCTURED_HIER_FLAG => freeze_rollup_flag,
X_SHORTHAND_ENABLED_FLAG => shorthand_enabled_flag,
X_SHORTHAND_LENGTH => shorthand_length,
X_STRUCTURE_VIEW_NAME => view_name,
X_ID_FLEX_STRUCTURE_NAME => structure_title,
X_DESCRIPTION => description,
X_SHORTHAND_PROMPT => shorthand_prompt,
X_CREATION_DATE => creation_date_i,
X_CREATED_BY => created_by_i,
X_LAST_UPDATE_DATE => last_update_date_i,
X_LAST_UPDATED_BY => last_updated_by_i,
X_LAST_UPDATE_LOGIN => last_update_login_i);
INSERT INTO FND_FLEX_WORKFLOW_PROCESSES
(APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM, WF_ITEM_TYPE,
WF_PROCESS_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN)
SELECT application_id_i, flex_code,
flex_num, FWP.WF_ITEM_TYPE,
'DEFAULT_ACCOUNT_GENERATION',
last_update_date_i, last_updated_by_i, creation_date_i,
created_by_i, last_update_login_i
FROM FND_FLEX_WORKFLOW_PROCESSES FWP
WHERE FWP.APPLICATION_ID = application_id_i
AND FWP.ID_FLEX_CODE = flex_code
AND FWP.ID_FLEX_NUM = 101
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT COUNT(*)
INTO cnt
FROM fnd_id_flexs
WHERE application_id = application_id_i
AND (id_flex_code = flex_code
OR id_flex_name = flex_title)
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
dynamic_inserts IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
allow_id_value_sets IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
index_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
concat_seg_len_max IN NUMBER DEFAULT fnd_api.g_miss_num,
concat_len_warning IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
concatenated_segs_view_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char)
RETURN flexfield_type
IS
flexfield flexfield_type;
set_value(dynamic_inserts, flexfield.dynamic_inserts);
SELECT 'Y',
find_flexfield.appl_short_name,
id_flex_code,
id_flex_name,
idf.description,
tap.application_short_name,
application_table_name,
concatenated_segs_view_name,
unique_id_column_name,
set_defining_column_name structure_column,
dynamic_inserts_feasible_flag,
allow_id_valuesets,
index_flag,
maximum_concatenation_len,
concatenation_len_warning,
idf.application_id,
tap.application_id,
tab.table_id
INTO flexfield
FROM fnd_id_flexs idf, fnd_application tap, fnd_tables tab
WHERE idf.application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND idf.table_application_id = tap.application_id
AND tab.application_id = table_application_id
AND tab.table_name = application_table_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
INSERT
INTO fnd_id_flexs (application_id,
id_flex_code,
id_flex_name,
table_application_id,
application_table_name,
concatenated_segs_view_name,
allow_id_valuesets,
dynamic_inserts_feasible_flag,
index_flag,
unique_id_column_name,
description,
application_table_type,
set_defining_column_name,
maximum_concatenation_len,
concatenation_len_warning,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES (flexfield.application_id,
flexfield.flex_code,
flexfield.flex_title,
flexfield.table_application_id,
flexfield.table_name,
flexfield.concatenated_segs_view_name,
flexfield.allow_id_value_sets,
flexfield.dynamic_inserts,
flexfield.index_flag,
flexfield.unique_id_column,
flexfield.description,
NULL,
flexfield.structure_column,
flexfield.concat_seg_len_max,
flexfield.concat_len_warning,
last_update_date_i,
last_updated_by_i,
creation_date_i,
created_by_i,
last_update_login_i);
UPDATE fnd_columns SET
flexfield_usage_code = 'K',
-- flexfield_application_id = flexfield.application_id,
-- flexfield_name = flexfield.flex_code,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = flexfield.table_application_id
AND table_id = flexfield.table_id
AND column_name like 'SEGMENT%'
AND rtrim(column_name, '0123456789') = 'SEGMENT'
AND flexfield_usage_code = 'N'
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
dynamic_insert_flag => 'N',
shorthand_enabled_flag => 'N',
shorthand_prompt => NULL,
shorthand_length => NULL,
flex_num => 101);
UPDATE fnd_columns SET
flexfield_usage_code = 'K',
-- flexfield_application_id = flexfield.application_id,
-- flexfield_name = flexfield.flex_code,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = flexfield.table_application_id
AND table_id = flexfield.table_id
AND fnd_columns.column_name = enable_column.column_name
AND flexfield_usage_code = 'N'
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
UPDATE fnd_columns SET
flexfield_usage_code = 'N',
-- flexfield_application_id = NULL,
-- flexfield_name = NULL,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = flexfield.table_application_id
AND table_id = flexfield.table_id
AND fnd_columns.column_name = enable_column.column_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
INSERT
INTO fnd_segment_attribute_types(application_id,
id_flex_code,
segment_attribute_type,
global_flag,
required_flag,
unique_flag,
segment_prompt,
description,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES(flexfield.application_id,
flexfield.flex_code,
qualifier_name,
global_flag,
required_flag,
unique_flag,
prompt,
description,
creation_date_i,
created_by_i,
last_update_date_i,
last_updated_by_i,
last_update_login_i);
INSERT INTO fnd_segment_attribute_values
(application_id,
id_flex_code,
id_flex_num,
application_column_name,
segment_attribute_type,
attribute_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT ifsg.application_id,
ifsg.id_flex_code,
ifsg.id_flex_num,
ifsg.application_column_name,
add_flex_qualifier.qualifier_name,
add_flex_qualifier.global_flag,
creation_date_i,
created_by_i,
last_update_date_i,
last_updated_by_i,
last_update_login_i
FROM fnd_id_flex_segments ifsg
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
FUNCTION delete_flex_qualifier(flexfield IN flexfield_type,
qualifier_name IN VARCHAR2,
recursive_delete IN BOOLEAN DEFAULT TRUE)
RETURN NUMBER
IS
CURSOR vat_cur(p_application_id IN NUMBER,
p_id_flex_code IN VARCHAR2,
p_segment_attribute_type IN VARCHAR2)
IS
SELECT value_attribute_type
FROM fnd_value_attribute_types vat
WHERE vat.application_id = p_application_id
AND vat.id_flex_code = p_id_flex_code
AND vat.segment_attribute_type = p_segment_attribute_type
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
l_recursive_delete BOOLEAN := Nvl(recursive_delete, FALSE);
SELECT 0
INTO l_return
FROM fnd_segment_attribute_types sat
WHERE sat.application_id = flexfield.application_id
AND sat.id_flex_code = flexfield.flex_code
AND sat.segment_attribute_type = qualifier_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
message('SELECT FROM SAT is failed ' || chr_newline ||
'SQLERRM : ' || Sqlerrm);
SELECT s.application_id || '/' ||
s.id_flex_code || '/' ||
s.id_flex_num || '/' ||
s.application_column_name || '/' ||
s.segment_name
INTO l_vc2
FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav
WHERE s.application_id = sav.application_id
AND s.id_flex_code = sav.id_flex_code
AND s.id_flex_num = sav.id_flex_num
AND s.application_column_name = sav.application_column_name
AND s.enabled_flag = 'Y'
AND sav.application_id = flexfield.application_id
AND sav.id_flex_code = flexfield.flex_code
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = qualifier_name
AND ROWNUM < 2
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
message('Delete is not allowed. Flexfield qualifier <' || qualifier_name || '> is used by segment: <' || l_vc2 || '>');
IF ((NOT l_recursive_delete) AND (l_number > 0)) THEN
message('There are segment qualifiers for this flexfield ' ||
'qualifier, and you passed recursive_delete => FALSE.');
l_number := delete_seg_qualifier(flexfield,
qualifier_name,
vat_rec.value_attribute_type);
DELETE FROM fnd_segment_attribute_values sav
WHERE sav.application_id = flexfield.application_id
AND sav.id_flex_code = flexfield.flex_code
AND sav.segment_attribute_type = qualifier_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_segment_attribute_types sat
WHERE sat.application_id = flexfield.application_id
AND sat.id_flex_code = flexfield.flex_code
AND sat.segment_attribute_type = qualifier_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
END delete_flex_qualifier;
INSERT INTO fnd_segment_attribute_values
(application_id,
id_flex_code,
id_flex_num,
application_column_name,
segment_attribute_type,
attribute_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT ifsg.application_id,
ifsg.id_flex_code,
ifsg.id_flex_num,
ifsg.application_column_name,
sat.segment_attribute_type,
sat.global_flag,
creation_date_i,
created_by_i,
last_update_date_i,
last_updated_by_i,
last_update_login_i
FROM fnd_id_flex_segments ifsg,
fnd_segment_attribute_types sat
WHERE sat.application_id = ifsg.application_id
AND sat.id_flex_code = ifsg.id_flex_code
AND NOT exists
(SELECT NULL
FROM fnd_segment_attribute_values sav
WHERE sav.application_id = ifsg.application_id
AND sav.id_flex_code = ifsg.id_flex_code
AND sav.id_flex_num = ifsg.id_flex_num
AND sav.application_column_name = ifsg.application_column_name
AND sav.segment_attribute_type = sat.segment_attribute_type)
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT 1
INTO dummy
FROM dual
WHERE EXISTS
(SELECT 1
FROM fnd_segment_attribute_types sat
WHERE sat.application_id = flexfield.application_id
AND sat.id_flex_code = flexfield.flex_code
AND sat.segment_attribute_type = flex_qualifier)
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT 1
INTO dummy
FROM dual
WHERE EXISTS
(SELECT 1
FROM fnd_columns c
WHERE c.application_id = flexfield.table_application_id
AND c.table_id = flexfield.table_id
AND c.column_name = derived_column
AND c.flexfield_usage_code = 'N')
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT 1
INTO dummy
FROM dual
WHERE EXISTS
(SELECT 1
FROM fnd_lookups
WHERE lookup_type = quickcode_type
AND lookup_code = default_value
AND enabled_flag = 'Y'
AND ( (start_date_active IS NULL)
OR (start_date_active <= sysdate))
AND ( (end_date_active IS NULL)
OR (end_date_active >= sysdate)))
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
FND_VAL_ATTRIBUTE_TYPES_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_APPLICATION_ID => flexfield.application_id,
X_ID_FLEX_CODE => flexfield.flex_code,
X_SEGMENT_ATTRIBUTE_TYPE => flex_qualifier,
X_VALUE_ATTRIBUTE_TYPE => qualifier_name,
X_REQUIRED_FLAG => 'Y',
X_APPLICATION_COLUMN_NAME => derived_column,
X_DESCRIPTION => description,
X_DEFAULT_VALUE => default_value,
X_LOOKUP_TYPE => quickcode_type,
X_DERIVATION_RULE_CODE => 'G12',
X_DERIVATION_RULE_VALUE1 => 'N',
X_DERIVATION_RULE_VALUE2 => 'Y',
X_PROMPT => prompt,
X_CREATION_DATE => creation_date_i,
X_CREATED_BY => created_by_i,
X_LAST_UPDATE_DATE => last_update_date_i,
X_LAST_UPDATED_BY => last_updated_by_i,
X_LAST_UPDATE_LOGIN => last_update_login_i);
UPDATE fnd_columns
SET flexfield_usage_code = 'Q',
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = flexfield.table_application_id
AND table_id = flexfield.table_id
AND column_name = derived_column
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
INSERT INTO fnd_flex_validation_qualifiers (flex_value_set_id,
id_flex_application_id,
id_flex_code,
segment_attribute_type,
value_attribute_type,
assignment_date)
SELECT DISTINCT
ifsg.flex_value_set_id,
flexfield.application_id,
flexfield.flex_code,
flex_qualifier,
qualifier_name,
SYSDATE
FROM fnd_segment_attribute_values sav,
fnd_id_flex_segments ifsg
WHERE sav.application_id = flexfield.application_id
AND sav.id_flex_code = flexfield.flex_code
AND sav.segment_attribute_type = flex_qualifier
AND sav.attribute_value = 'Y'
AND 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
AND ifsg.flex_value_set_id IS NOT NULL
AND ifsg.enabled_flag = 'Y'
AND NOT EXISTS
(SELECT NULL
FROM fnd_flex_validation_qualifiers q
WHERE q.flex_value_set_id = ifsg.flex_value_set_id
AND q.id_flex_application_id = flexfield.application_id
AND q.id_flex_code = flexfield.flex_code
AND q.segment_attribute_type = flex_qualifier
AND q.value_attribute_type = qualifier_name)
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
FUNCTION delete_seg_qualifier(flexfield IN flexfield_type,
flex_qualifier IN VARCHAR2,
qualifier_name IN VARCHAR2) RETURN NUMBER
IS
l_return NUMBER := 0;
SELECT 0
INTO l_return
FROM fnd_segment_attribute_types sat
WHERE sat.application_id = flexfield.application_id
AND sat.id_flex_code = flexfield.flex_code
AND sat.segment_attribute_type = flex_qualifier
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
message('SELECT FROM SAT is failed ' || chr_newline ||
'SQLERRM : ' || Sqlerrm);
SELECT 0
INTO l_return
FROM fnd_value_attribute_types vat
WHERE vat.application_id = flexfield.application_id
AND vat.id_flex_code = flexfield.flex_code
AND vat.segment_attribute_type = flex_qualifier
AND vat.value_attribute_type = qualifier_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
message('SELECT FROM VAT is failed ' || chr_newline ||
'SQLERRM : ' || Sqlerrm);
DELETE FROM fnd_flex_validation_qualifiers fvq
WHERE fvq.id_flex_application_id = flexfield.application_id
AND fvq.id_flex_code = flexfield.flex_code
AND fvq.segment_attribute_type = flex_qualifier
AND fvq.value_attribute_type = qualifier_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_val_attribute_types_tl vat
WHERE vat.application_id = flexfield.application_id
AND vat.id_flex_code = flexfield.flex_code
AND vat.segment_attribute_type = flex_qualifier
AND vat.value_attribute_type = qualifier_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_value_attribute_types vat
WHERE vat.application_id = flexfield.application_id
AND vat.id_flex_code = flexfield.flex_code
AND vat.segment_attribute_type = flex_qualifier
AND vat.value_attribute_type = qualifier_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
END delete_seg_qualifier;
UPDATE fnd_id_flexs SET
id_flex_name = flexfield.flex_title,
table_application_id = flexfield.table_application_id,
application_table_name = flexfield.table_name,
concatenated_segs_view_name = flexfield.concatenated_segs_view_name,
allow_id_valuesets = flexfield.allow_id_value_sets,
dynamic_inserts_feasible_flag = flexfield.dynamic_inserts,
index_flag = flexfield.index_flag,
unique_id_column_name = flexfield.unique_id_column,
description = flexfield.description,
application_table_type = NULL,
set_defining_column_name = flexfield.structure_column,
maximum_concatenation_len = flexfield.concat_seg_len_max,
concatenation_len_warning = flexfield.concat_len_warning,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = original.application_id
AND id_flex_code = original.flex_code
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
PROCEDURE delete_flexfield(appl_short_name IN VARCHAR2,
flex_code IN VARCHAR2)
IS
flexfield flexfield_type;
delete_flexfield(flexfield);
message('delete_flexfield: Either this flexfield is already deleted');
message('delete_flexfield: ' || Sqlerrm);
PROCEDURE delete_flexfield(flexfield IN flexfield_type)
IS
structure structure_type;
delete_structure(flexfield => flexfield,
structure => structure);
DELETE FROM fnd_val_attribute_types_tl
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_value_attribute_types
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_segment_attribute_types
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
UPDATE fnd_columns SET
flexfield_usage_code = 'N',
-- flexfield_application_id = NULL,
-- flexfield_name = NULL,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = flexfield.table_application_id
AND table_id = flexfield.table_id
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_id_flexs
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_compiled_id_flexs
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
println('deleted flexfield: ' || to_string(flexfield));
message('delete_flexfield: ' || Sqlerrm);
dynamic_insert_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
shorthand_enabled_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
shorthand_prompt IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
shorthand_length IN NUMBER DEFAULT fnd_api.g_miss_num)
RETURN structure_type
IS
structure structure_type;
SELECT fnd_id_flex_structures_s.NEXTVAL
INTO structure.structure_number
FROM dual;
SELECT NVL(MAX(ifs.id_flex_num),0) + 1
INTO structure.structure_number
FROM fnd_id_flex_structures ifs
WHERE ifs.application_id = flexfield.application_id
AND ifs.id_flex_code = flexfield.flex_code
AND ifs.id_flex_num < 101
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
set_value(dynamic_insert_flag, structure.dynamic_insert_flag);
SELECT 'Y',
id_flex_num,
id_flex_structure_code,
id_flex_structure_name,
description,
structure_view_name,
freeze_flex_definition_flag,
enabled_flag,
concatenated_segment_delimiter,
cross_segment_validation_flag,
freeze_structured_hier_flag,
dynamic_inserts_allowed_flag,
shorthand_enabled_flag,
shorthand_prompt,
shorthand_length
INTO structure
FROM fnd_id_flex_structures_vl
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_structure_code = structure_code
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT 'Y',
id_flex_num,
id_flex_structure_code,
id_flex_structure_name,
description,
structure_view_name,
freeze_flex_definition_flag,
enabled_flag,
concatenated_segment_delimiter,
cross_segment_validation_flag,
freeze_structured_hier_flag,
dynamic_inserts_allowed_flag,
shorthand_enabled_flag,
shorthand_prompt,
shorthand_length
INTO structure
FROM fnd_id_flex_structures_vl
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = find_structure.structure_number
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
dynamic_insert_flag IN VARCHAR2 DEFAULT 'N',
shorthand_enabled_flag IN VARCHAR2 DEFAULT 'N',
shorthand_prompt IN VARCHAR2 DEFAULT NULL,
shorthand_length IN NUMBER DEFAULT NULL)
RETURN structure_type
IS
structure structure_type;
dynamic_insert_flag => dynamic_insert_flag,
shorthand_enabled_flag => shorthand_enabled_flag,
shorthand_prompt => shorthand_prompt,
shorthand_length => shorthand_length);
dynamic_insert_flag => structure.dynamic_insert_flag,
shorthand_enabled_flag => structure.shorthand_enabled_flag,
shorthand_prompt => structure.shorthand_prompt,
shorthand_length => structure.shorthand_length,
flex_num => structure.structure_number);
fnd_id_flex_structures_pkg.update_row
(X_APPLICATION_ID => flexfield.application_id,
X_ID_FLEX_CODE => flexfield.flex_code,
X_ID_FLEX_NUM => structure.structure_number,
X_ID_FLEX_STRUCTURE_CODE => structure.structure_code,
X_CONCATENATED_SEGMENT_DELIMIT => structure.segment_separator,
X_CROSS_SEGMENT_VALIDATION_FLA => structure.cross_val_flag,
X_DYNAMIC_INSERTS_ALLOWED_FLAG => structure.dynamic_insert_flag,
X_ENABLED_FLAG => structure.enabled_flag,
X_FREEZE_FLEX_DEFINITION_FLAG => structure.freeze_flag,
X_FREEZE_STRUCTURED_HIER_FLAG => structure.freeze_rollup_flag,
X_SHORTHAND_ENABLED_FLAG => structure.shorthand_enabled_flag,
X_SHORTHAND_LENGTH => structure.shorthand_length,
X_STRUCTURE_VIEW_NAME => structure.view_name,
X_ID_FLEX_STRUCTURE_NAME => structure.structure_name,
X_DESCRIPTION => structure.description,
X_SHORTHAND_PROMPT => structure.shorthand_prompt,
X_LAST_UPDATE_DATE => last_update_date_i,
X_LAST_UPDATED_BY => last_updated_by_i,
X_LAST_UPDATE_LOGIN => last_update_login_i);
PROCEDURE delete_structure(flexfield IN flexfield_type,
structure IN structure_type)
IS
segment segment_type;
delete_segment(flexfield => flexfield,
structure => structure,
segment => segment);
DELETE FROM fnd_shorthand_flex_aliases
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_flex_validation_rules
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_flex_vdation_rules_tl
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_flex_validation_rule_lines
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_flex_include_rule_lines
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_flex_exclude_rule_lines
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_flex_validation_rule_stats
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_id_flex_structures_tl
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_id_flex_structures
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_compiled_id_flex_structs
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
println('deleted structure: ' || to_string(flexfield, structure));
message('delete_structure: ' || Sqlerrm);
SELECT 'Y' instantiated,
seg.segment_name,
seg.description,
seg.application_column_name,
seg.segment_num,
seg.enabled_flag,
seg.display_flag,
seg.application_column_index_flag,
seg.flex_value_set_id,
NULL,
seg.default_type,
seg.default_value,
seg.runtime_property_function,
seg.additional_where_clause,
seg.required_flag,
seg.security_enabled_flag,
seg.range_code,
seg.display_size,
seg.maximum_description_len,
seg.concatenation_description_len,
seg.form_above_prompt,
seg.form_left_prompt
INTO segment
FROM fnd_id_flex_segments_vl seg
WHERE seg.application_id = flexfield.application_id
AND seg.id_flex_code = flexfield.flex_code
AND seg.id_flex_num = structure.structure_number
AND seg.segment_name = find_segment.segment_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
INSERT
INTO fnd_id_flex_segments (application_id,
id_flex_code,
id_flex_num,
application_column_name,
segment_name,
segment_num,
application_column_index_flag,
enabled_flag,
required_flag,
display_flag,
display_size,
security_enabled_flag,
maximum_description_len,
concatenation_description_len,
-- form_left_prompt,
-- form_above_prompt,
-- description,
flex_value_set_id,
range_code,
default_type,
default_value,
runtime_property_function,
additional_where_clause,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES (flexfield.application_id,
flexfield.flex_code,
structure.structure_number,
segment.column_name,
segment.segment_name,
segment.segment_number,
segment.indexed_flag,
segment.enabled_flag,
segment.required_flag,
segment.displayed_flag,
segment.display_size,
segment.security_flag,
segment.description_size,
segment.concat_size,
-- segment.window_prompt,
-- segment.lov_prompt,
-- segment.description,
segment.value_set_id,
segment.range_code,
segment.default_type,
segment.default_value,
segment.runtime_property_function,
segment.additional_where_clause,
creation_date_i,
created_by_i,
last_update_date_i,
last_updated_by_i,
last_update_login_i);
INSERT INTO fnd_segment_attribute_values(id_flex_code,
id_flex_num,
application_column_name,
segment_attribute_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute_value,
application_id)
SELECT
s.id_flex_code,
s.id_flex_num,
s.application_column_name,
segment_attribute_type,
creation_date_i,
created_by_i,
last_update_date_i,
last_updated_by_i,
last_update_login_i,
t.global_flag,
s.application_id
FROM fnd_id_flex_segments s, fnd_segment_attribute_types t
WHERE s.application_id = flexfield.application_id
AND s.application_column_name = segment.column_name
AND s.id_flex_code = flexfield.flex_code
AND s.id_flex_num = structure.structure_number
AND t.application_id = s.application_id
AND t.id_flex_code = s.id_flex_code
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
INSERT INTO fnd_flex_validation_qualifiers (flex_value_set_id,
id_flex_application_id,
id_flex_code,
segment_attribute_type,
value_attribute_type,
assignment_date)
SELECT
segment.value_set_id,
flexfield.application_id,
flexfield.flex_code,
sav.segment_attribute_type,
vat.value_attribute_type,
sysdate
FROM fnd_segment_attribute_values sav,
fnd_value_attribute_types vat
WHERE segment.value_set_id IS NOT NULL
AND segment.enabled_flag = 'Y'
AND sav.application_id = flexfield.application_id
AND sav.id_flex_code = flexfield.flex_code
AND sav.id_flex_num = structure.structure_number
AND sav.application_column_name = segment.column_name
AND sav.attribute_value = 'Y'
AND sav.application_id = vat.application_id
AND sav.id_flex_code = vat.id_flex_code
AND sav.segment_attribute_type = vat.segment_attribute_type
AND NOT EXISTS
(SELECT NULL
FROM fnd_flex_validation_qualifiers q
WHERE q.flex_value_set_id = segment.value_set_id
AND q.id_flex_application_id = flexfield.application_id
AND q.id_flex_code = flexfield.flex_code
AND q.segment_attribute_type = sav.segment_attribute_type
AND q.value_attribute_type = vat.value_attribute_type)
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
INSERT INTO fnd_id_flex_segments_tl(application_id,
id_flex_code,
id_flex_num,
application_column_name,
language,
form_above_prompt,
form_left_prompt,
description,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
source_lang)
SELECT
flexfield.application_id,
flexfield.flex_code,
structure.structure_number,
segment.column_name,
l.language_code,
segment.lov_prompt,
segment.window_prompt,
segment.description,
creation_date_i,
created_by_i,
last_update_date_i,
last_updated_by_i,
last_update_login_i,
userenv('LANG')
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS
(SELECT NULL
FROM fnd_id_flex_segments_tl t
WHERE t.application_id = flexfield.application_id
AND t.id_flex_code = flexfield.flex_code
AND t.id_flex_num = structure.structure_number
AND t.application_column_name = segment.column_name
AND t.language = l.language_code)
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
UPDATE fnd_segment_attribute_values SET
attribute_value = enable_flag,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND application_column_name = segment.column_name
AND segment_attribute_type = flexfield_qualifier
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
INSERT INTO fnd_flex_validation_qualifiers (flex_value_set_id,
id_flex_application_id,
id_flex_code,
segment_attribute_type,
value_attribute_type,
assignment_date)
SELECT
segment.value_set_id,
flexfield.application_id,
flexfield.flex_code,
sav.segment_attribute_type,
vat.value_attribute_type,
sysdate
FROM fnd_segment_attribute_values sav,
fnd_value_attribute_types vat
WHERE segment.value_set_id IS NOT NULL
AND segment.enabled_flag = 'Y'
AND sav.application_id = flexfield.application_id
AND sav.id_flex_code = flexfield.flex_code
AND sav.id_flex_num = structure.structure_number
AND sav.application_column_name = segment.column_name
AND sav.attribute_value = 'Y'
AND sav.application_id = vat.application_id
AND sav.id_flex_code = vat.id_flex_code
AND sav.segment_attribute_type = vat.segment_attribute_type
AND sav.segment_attribute_type = flexfield_qualifier
AND NOT EXISTS
(SELECT NULL
FROM fnd_flex_validation_qualifiers q
WHERE q.flex_value_set_id = segment.value_set_id
AND q.id_flex_application_id = flexfield.application_id
AND q.id_flex_code = flexfield.flex_code
AND q.segment_attribute_type = sav.segment_attribute_type
AND q.value_attribute_type = vat.value_attribute_type)
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
UPDATE fnd_id_flex_segments SET
-- application_column_name = segment.column_name,
segment_name = segment.segment_name,
segment_num = segment.segment_number,
application_column_index_flag = segment.indexed_flag,
enabled_flag = segment.enabled_flag,
required_flag = segment.required_flag,
display_flag = segment.displayed_flag,
display_size = segment.display_size,
security_enabled_flag = segment.security_flag,
maximum_description_len = segment.description_size,
concatenation_description_len = segment.concat_size,
flex_value_set_id = segment.value_set_id,
range_code = segment.range_code,
default_type = segment.default_type,
default_value = segment.default_value,
runtime_property_function = segment.runtime_property_function,
additional_where_clause = segment.additional_where_clause,
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND application_column_name = original.column_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
UPDATE fnd_segment_attribute_values SET
application_column_name = segment.column_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 = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND application_column_name = original.column_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
INSERT INTO fnd_flex_validation_qualifiers
(flex_value_set_id,
id_flex_application_id,
id_flex_code,
segment_attribute_type,
value_attribute_type,
assignment_date)
SELECT
segment.value_set_id,
flexfield.application_id,
flexfield.flex_code,
sav.segment_attribute_type,
vat.value_attribute_type,
Sysdate
FROM fnd_segment_attribute_values sav,
fnd_value_attribute_types vat
WHERE segment.value_set_id IS NOT NULL
AND segment.enabled_flag = 'Y'
AND sav.application_id = flexfield.application_id
AND sav.id_flex_code = flexfield.flex_code
AND sav.id_flex_num = structure.structure_number
AND sav.application_column_name = segment.column_name
AND sav.attribute_value = 'Y'
AND sav.application_id = vat.application_id
AND sav.id_flex_code = vat.id_flex_code
AND sav.segment_attribute_type = vat.segment_attribute_type
AND NOT EXISTS
(SELECT NULL FROM fnd_flex_validation_qualifiers q
WHERE q.flex_value_set_id = segment.value_set_id
AND q.id_flex_application_id = flexfield.application_id
AND q.id_flex_code = flexfield.flex_code
AND q.segment_attribute_type = sav.segment_attribute_type
AND q.value_attribute_type = vat.value_attribute_type)
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
UPDATE fnd_id_flex_segments_tl SET
application_column_name = segment.column_name,
form_left_prompt = segment.window_prompt,
form_above_prompt = segment.lov_prompt,
description = segment.description,
source_lang = userenv('LANG'),
last_update_date = last_update_date_i,
last_updated_by = last_updated_by_i,
last_update_login = last_update_login_i
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND application_column_name = original.column_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
PROCEDURE delete_segment(flexfield IN flexfield_type,
structure IN structure_type,
segment IN segment_type)
IS
BEGIN
message_init;
DELETE FROM fnd_segment_attribute_values
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND application_column_name = segment.column_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_id_flex_segments_tl
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND application_column_name = segment.column_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
DELETE FROM fnd_id_flex_segments
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND id_flex_num = structure.structure_number
AND segment_name = segment.segment_name
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
println('deleted segment: ' || to_string(flexfield, structure, segment));
message('delete_segment: ' || Sqlerrm);
dynamic_inserts => 'Y',
allow_id_value_sets => 'Y',
concat_seg_len_max => '81',
concat_len_warning => 'len overflow warning',
concatenated_segs_view_name => NULL);
SELECT 'test' || To_char(MAX(To_number(Substr(id_flex_structure_name, 4))) + 1)
INTO sname
FROM fnd_id_flex_structures_vl
WHERE application_id = 0
AND id_flex_code = 'RW2'
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT column_name
FROM fnd_columns
WHERE application_id = flexfield.table_application_id
AND table_id = flexfield.table_id
-- AND flexfield_application_id = flexfield.application_id
-- AND flexfield_name = flexfield.flex_code
AND flexfield_usage_code = 'K'
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
printbuf('dynamic_inserts => ' || quot(flexfield.dynamic_inserts) || ',');
SELECT *
FROM fnd_segment_attribute_types
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT *
FROM fnd_val_attribute_types_vl
WHERE application_id = flexfield.application_id
AND id_flex_code = flexfield.flex_code
AND segment_attribute_type = flex_qualifier
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
printbuf('dynamic_insert_flag => ' || quot(structure.dynamic_insert_flag) || ',');
SELECT sav.segment_attribute_type
FROM fnd_segment_attribute_values sav,
fnd_segment_attribute_types sat
WHERE sav.application_id = flexfield.application_id
AND sav.id_flex_code = flexfield.flex_code
AND sav.id_flex_num = structure.structure_number
AND sav.application_column_name = segment.column_name
AND sav.attribute_value = 'Y'
-- and not global
AND sat.application_id = sav.application_id
AND sat.id_flex_code = sav.id_flex_code
AND sat.segment_attribute_type = sav.segment_attribute_type
AND sat.global_flag = 'N'
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT application_id, id_flex_code, id_flex_name
INTO l_a_id, l_flex_code, l_flex_name
FROM fnd_id_flexs
WHERE table_application_id = p_application_id
AND Upper(application_table_name) = up_table_name
AND ROWNUM = 1
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
'SELECT FROM FND_ID_FLEXS is failed. ' || chr_newline ||
'SQLERRM : ' || Sqlerrm;
SELECT application_id, id_flex_code, id_flex_name,
unique_id_column_name
INTO l_a_id, l_flex_code, l_flex_name, l_id_col
FROM fnd_id_flexs
WHERE table_application_id = p_application_id
AND Upper(application_table_name) = up_table_name
AND Upper(unique_id_column_name) = up_column_name
AND ROWNUM = 1
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
'SELECT FROM FND_ID_FLEXS is failed. ' || chr_newline ||
'SQLERRM : ' || Sqlerrm;
SELECT application_id, id_flex_code, id_flex_name,
set_defining_column_name
INTO l_a_id, l_flex_code, l_flex_name, l_set_col
FROM fnd_id_flexs
WHERE table_application_id = p_application_id
AND Upper(application_table_name) = up_table_name
AND set_defining_column_name IS NOT NULL
AND Upper(set_defining_column_name) = up_column_name
AND ROWNUM = 1
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
'SELECT FROM FND_ID_FLEXS is failed. ' || chr_newline ||
'SQLERRM : ' || Sqlerrm;
SELECT idf.application_id, idf.id_flex_code,
idf.id_flex_name, ifst.id_flex_num, ifst.segment_name
INTO l_a_id, l_flex_code, l_flex_name, l_flex_num, l_segment
FROM fnd_id_flexs idf, fnd_id_flex_segments ifst
WHERE idf.application_id = ifst.application_id
AND idf.id_flex_code = ifst.id_flex_code
AND idf.table_application_id = p_application_id
AND Upper(idf.application_table_name) = up_table_name
AND Upper(ifst.application_column_name) = up_column_name
AND ROWNUM = 1
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
'SELECT FROM FND_ID_FLEX_SEGMENTS is failed. ' || chr_newline ||
'SQLERRM : ' || Sqlerrm;
SELECT idf.application_id, idf.id_flex_code,
idf.id_flex_name, vat.segment_attribute_type, vat.value_attribute_type
INTO l_a_id, l_flex_code, l_flex_name, l_seg_att, l_val_att
FROM fnd_id_flexs idf, fnd_value_attribute_types vat
WHERE idf.application_id = vat.application_id
AND idf.id_flex_code = vat.id_flex_code
AND idf.table_application_id = p_application_id
AND Upper(idf.application_table_name) = up_table_name
AND Upper(vat.application_column_name) = up_column_name
AND ROWNUM = 1
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
'SELECT FROM FND_VALUE_ATTRIBUTE_TYPES is failed. ' ||chr_newline||
'SQLERRM : ' || Sqlerrm;
SELECT s.segment_num
INTO l_segment_num
FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
fnd_segment_attribute_types sat
WHERE s.application_id = p_application_id
AND s.id_flex_code = p_id_flex_code
AND s.id_flex_num = p_id_flex_num
AND s.enabled_flag = 'Y'
AND s.application_column_name = sav.application_column_name
AND sav.application_id = p_application_id
AND sav.id_flex_code = p_id_flex_code
AND sav.id_flex_num = p_id_flex_num
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = sat.segment_attribute_type
AND sat.application_id = p_application_id
AND sat.id_flex_code = p_id_flex_code
AND sat.unique_flag = 'Y'
AND sat.segment_attribute_type = p_segment_attribute_type
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT count(segment_num)
INTO l_segment_order
FROM fnd_id_flex_segments
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND enabled_flag = 'Y'
AND segment_num <= l_segment_num
AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
SELECT fa.*
INTO x_app
FROM fnd_application fa
WHERE fa.application_short_name = p_application_short_name;
PROCEDURE delete_compiled_definition
(p_application_id IN fnd_application.application_id%TYPE,
p_id_flex_code IN fnd_id_flexs.id_flex_code%TYPE,
p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE)
IS
BEGIN
DELETE FROM fnd_compiled_id_flexs
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code;
DELETE FROM fnd_compiled_id_flex_structs
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num;
END delete_compiled_definition;
SELECT additional_where_clause
INTO l_additional_where_clause
FROM fnd_id_flex_segments
WHERE application_id = l_app.application_id
AND id_flex_code = p_flexfield.flex_code
AND id_flex_num = p_structure.structure_number
AND application_column_name = p_segment.column_name;
UPDATE fnd_id_flex_segments
SET additional_where_clause = l_additional_where_clause
WHERE application_id = l_app.application_id
AND id_flex_code = p_flexfield.flex_code
AND id_flex_num = p_structure.structure_number
AND application_column_name = p_segment.column_name;
delete_compiled_definition(l_app.application_id, p_flexfield.flex_code, p_structure.structure_number);
PROCEDURE delete_awc(p_flexfield IN flexfield_type,
p_structure IN structure_type,
p_segment IN segment_type,
p_tag IN varchar2)
IS
l_numof_awc_elements NUMBER := 0;
UPDATE fnd_id_flex_segments
SET additional_where_clause = l_additional_where_clause
WHERE application_id = l_app.application_id
AND id_flex_code = p_flexfield.flex_code
AND id_flex_num = p_structure.structure_number
AND application_column_name = p_segment.column_name;
delete_compiled_definition(l_app.application_id, p_flexfield.flex_code, p_structure.structure_number);
END delete_awc;