Step1: Create custom valuesets in source instance. Use the below API’s to move the value sets to the target instance.
Step2: The custom value sets can be passed to the below API’s to insert data to Oracle standard tables fnd_flex_valuesets
Ø fnd_flex_val_api.create_valueset_independent f
Ø fnd_flex_val_api.create_valueset_dependent
Ø fnd_flex_val_api.create_valueset_table
Ø fnd_flex_val_api.create_valueset_special
Ø fnd_flex_val_api.create_valueset_none
Step3: Sample script with description to upload the Independent/Dependent/Table Value sets are explained below
Independent Value Set
-------------------------------
DECLARE
value_set_name VARCHAR2 (200);
description VARCHAR2 (200);
security_available VARCHAR2 (200);
enable_longlist VARCHAR2 (200);
format_type VARCHAR2 (200);
maximum_size NUMBER;
PRECISION NUMBER;
numbers_only VARCHAR2 (200);
uppercase_only VARCHAR2 (200);
right_justify_zero_fill VARCHAR2 (200);
min_value VARCHAR2 (200);
max_value VARCHAR2 (200);
v_session_mode VARCHAR2 (20) := 'customer_data';
x VARCHAR2 (200);
v_msg VARCHAR2 (2000);
BEGIN
fnd_flex_val_api.set_session_mode (v_session_mode);
value_set_name := 'CUST_VAL_SET_DEMO';
description := 'Custom Valueset';
security_available := 'N';
enable_longlist := 'N';
format_type := 'C';
maximum_size := 30;
PRECISION := 1;
numbers_only := 'N';
uppercase_only := 'N';
right_justify_zero_fill := 'N';
min_value := '1';
max_value := '30';
apps.fnd_flex_val_api.create_valueset_independent
(value_set_name,
description,
security_available,
enable_longlist,
format_type,
maximum_size,
PRECISION,
numbers_only,
uppercase_only,
right_justify_zero_fill,
min_value,
max_value
);
EXCEPTION
WHEN OTHERS
THEN
v_msg := fnd_flex_val_api.MESSAGE;
DBMS_OUTPUT.put_line (v_msg);
DBMS_OUTPUT.put_line (SQLERRM);
END;
COMMIT ;
Dependent Value Set
----------------------------
DECLARE
value_set_name VARCHAR2 (200);
parent_value_set_name VARCHAR2 (200);
description VARCHAR2 (200);
security_available VARCHAR2 (200);
enable_longlist VARCHAR2 (200);
format_type VARCHAR2 (200);
maximum_size NUMBER;
PRECISION NUMBER;
numbers_only VARCHAR2 (200);
uppercase_only VARCHAR2 (200);
right_justify_zero_fill VARCHAR2 (200);
min_value VARCHAR2 (200);
max_value VARCHAR2 (200);
v_session_mode VARCHAR2 (20) := 'customer_data';
x VARCHAR2 (200);
v_msg VARCHAR2 (2000);
BEGIN
fnd_flex_val_api.set_session_mode (v_session_mode);
value_set_name := 'CUST_DEPENDENT_VAL_SET';
parent_value_set_name := 'CUST_VAL_SET_DEMO';
description := 'Dependent Value Set';
security_available := 'N';
enable_longlist := 'N';
format_type := 'C';
maximum_size := 30;
PRECISION := 1;
numbers_only := 'N';
uppercase_only := 'N';
right_justify_zero_fill := 'N';
min_value := NULL;
max_value := NULL;
IF (apps.fnd_flex_val_api.valueset_exists (value_set_name))
THEN
DBMS_OUTPUT.put_line ('Value set exists.. Deleting it...');
apps.fnd_flex_val_api.delete_valueset (value_set_name);
END IF;
IF NOT (apps.fnd_flex_val_api.valueset_exists (value_set_name))
THEN
DBMS_OUTPUT.put_line ('Value set doesn''t exists.. Creating it...');
apps.fnd_flex_val_api.create_valueset_dependent
(value_set_name,
description,
security_available,
enable_longlist,
format_type,
maximum_size,
PRECISION,
numbers_only,
uppercase_only,
right_justify_zero_fill,
min_value,
max_value, parent_value_set_name,
'DEFAULT',
'Default'
);
COMMIT;
IF (apps.fnd_flex_val_api.valueset_exists (value_set_name))
THEN
DBMS_OUTPUT.put_line ('Value set:' || value_set_name
|| ' got created'
);
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_msg := fnd_flex_val_api.MESSAGE;
DBMS_OUTPUT.put_line (v_msg);
DBMS_OUTPUT.put_line (SQLERRM);
END;
Table Value Sets
BEGIN
fnd_flex_val_api.set_session_mode ('customer_data'); /*Mandatory*/
fnd_flex_val_api.create_valueset_table
(value_set_name => 'XX_VALUESET',
description => 'createdfrombackend',
security_available => 'N',
enable_longlist => 'N',
format_type => 'Char',
maximum_size => 20,
PRECISION => NULL,
numbers_only => 'N',
uppercase_only => 'N',
right_justify_zero_fill => 'N',
min_value => NULL,
max_value => NULL,
table_application => 'Application Object Library',
table_appl_short_name => 'AOL',
table_name => 'FND_LOOKUP_VALUES FND',
allow_parent_values => 'N',
value_column_name => 'FND.LOOKUP_CODE',
value_column_type => 'Char',
value_column_size => 40,
meaning_column_name => NULL,
meaning_column_type => NULL,
meaning_column_size => 40,
id_column_name => 'FND.MEANING', id_column_type => 'Char',
id_column_size => 40,
where_order_by => 'where lookup_type=''XXTEST_LOOKUP''',
additional_columns => NULL
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
Step4: Value sets will be created in the Database successfully and is shown in 2.1
2.1 Screenshot for Value sets created through Backend
Independent Value Sets
Dependent Value Set
Table Value Sets
Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion HCM Training