PROCEDURE get_parameter_create_sql
(
p_program_name VARCHAR2,
p_seq_num_b NUMBER DEFAULT 1,
p_seq_num_e NUMBER DEFAULT 999
)IS
CURSOR c1 IS
SELECT REPLACE(descriptive_flexfield_name, '$SRS$.', '') descriptive_flexfield_name,
application_id,
column_seq_num,
end_user_column_name,
description,
enabled_flag,
flex_value_set_id,
default_type,
default_value,
required_flag,
security_enabled_flag,
decode(range_code, 'H', 'High',
'L', 'Low',
'P', 'Pair') range_code,
display_flag,
display_size,
maximum_description_len,
concatenation_description_len,
form_left_prompt,
srw_param
FROM fnd_descr_flex_col_usage_vl
WHERE REPLACE(descriptive_flexfield_name, '$SRS$.', '') = p_program_name
AND column_seq_num BETWEEN nvl(p_seq_num_b, 1)
AND nvl(p_seq_num_e, 999)
ORDER BY column_seq_num;
v_application_name VARCHAR2(240);
v_flex_value_set_name VARCHAR2(60);
v_default_type VARCHAR2(80);
v_err_flag VARCHAR2(1) := 'N';
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
BEGIN
BEGIN
SELECT responsibility_id,
application_id
INTO v_resp_id,
v_resp_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_key = 'SYSTEM_ADMINISTRATOR';
EXCEPTION
WHEN no_data_found THEN
v_err_flag := 'Y';
dbms_output.put_line('Responsibility not found');
END;
FOR rec_c1 IN c1 LOOP
v_application_name := NULL;
v_flex_value_set_name := NULL;
BEGIN
SELECT application_name
INTO v_application_name
FROM fnd_application_vl
WHERE application_id = rec_c1.application_id;
EXCEPTION
WHEN no_data_found THEN
v_err_flag := 'Y';
dbms_output.put_line('Application not found');
END;
BEGIN
SELECT flex_value_set_name
INTO v_flex_value_set_name
FROM fnd_flex_value_sets
WHERE flex_value_set_id = rec_c1.flex_value_set_id;
EXCEPTION
WHEN no_data_found THEN
v_err_flag := 'Y';
dbms_output.put_line('Value set name not found');
END;
IF rec_c1.default_type IS NOT NULL THEN
BEGIN
SELECT meaning
INTO v_default_type
FROM fnd_lookups
WHERE lookup_type = 'FLEX_DEFAULT_TYPE'
AND lookup_code = rec_c1.default_type;
EXCEPTION
WHEN no_data_found THEN
v_err_flag := 'Y';
dbms_output.put_line('Default_type not found');
END;
END IF;
IF nvl(v_err_flag, 'N') = 'N' THEN
dbms_output.put_line('
DECLARE
v_cnt NUMBER := 0;
BEGIN'
);
dbms_output.put_line('
fnd_global.apps_initialize(
user_id => ' || fnd_profile.value('USER_ID') || ', --user_id,
resp_id => ' || v_resp_id || ', --responsibility id,
resp_appl_id => ' || v_resp_appl_id || '
);'
);
dbms_output.put_line('
fnd_program.parameter(
program_short_name => ''' || rec_c1.descriptive_flexfield_name || ''',
application => ''' || v_application_name || ''',
SEQUENCE => ''' || rec_c1.column_seq_num || ''',
parameter => ''' || rec_c1.end_user_column_name || ''',
description => ''' || rec_c1.description || ''',
enabled => ''' || rec_c1.enabled_flag || ''',
value_set => ''' || v_flex_value_set_name || ''',
default_type => ''' || v_default_type || ''',
default_value => ''' || rec_c1.default_value || ''',
REQUIRED => ''' || rec_c1.required_flag || ''',
enable_security => ''' || rec_c1.security_enabled_flag || ''',
RANGE => ''' || rec_c1.range_code || ''',
display => ''' || rec_c1.display_flag || ''',
display_size => ''' || rec_c1.display_size || ''',
description_size => ''' || rec_c1.maximum_description_len || ''',
concatenated_description_size => ''' || rec_c1.concatenation_description_len || ''',
PROMPT => ''' || rec_c1.form_left_prompt || ''',
token => ''' || rec_c1.srw_param || ''',
cd_parameter => ''N''
);'
);
dbms_output.put_line('
SELECT count(1)
INTO v_cnt
FROM fnd_descr_flex_col_usage_vl
WHERE REPLACE(descriptive_flexfield_name, ''$SRS$.'', '''') = ''' || p_program_name || '''
AND column_seq_num = ' || rec_c1.column_seq_num || ';
IF nvl(v_cnt, 0) != 0 THEN
COMMIT;
dbms_output.put_line(''Creation paramerer ' || rec_c1.column_seq_num || ' completed'');
ELSE
ROLLBACK;
dbms_output.put_line(''Creation paramerer ' || rec_c1.column_seq_num || ' failed'');
END IF;'
);
dbms_output.put_line('
END;'
);
END IF;
END LOOP;
END get_parameter_create_sql;
留言列表
Record (2)
