Creation Of 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 := ‘SIMHA_US_JOB_DEPT’;
description := ‘SIMHA US JOB DEPT’;
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_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 );
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;
Creation Of 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 := ‘SIMHA_US_JOB_NAME’;
parent_value_set_name := ‘SIMHA_US_JOB_DEPT’;
description := ‘SIMHA US JOB NAME’;
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;
DECLARE
v_userid NUMBER;
v_respid NUMBER;
v_appid NUMBER;
X VARCHAR2(200);
v_msg VARCHAR2(2000);
BEGIN
SELECT fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
INTO v_userid,
v_respid,
v_appid
FROM fnd_user fnd ,
fnd_responsibility_tl fresp
WHERE fnd.user_name = ‘HRSIMHA’
AND fresp.responsibility_name = ‘Application Developer’;
fnd_global.apps_initialize(user_id => v_userid, resp_id => v_respid, resp_appl_id => v_appid);
dbms_output.put_line(‘The Value is :’||fnd_global.USER_NAME);
FND_FLEX_VAL_API.create_independent_vset_value (‘SIMHA_US_JOB_LEVEL’,’EXEC’,’Executives’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);
dbms_output.put_line(‘The Value of X is :’||X);
FND_FLEX_VAL_API.create_independent_vset_value (‘SIMHA_US_JOB_LEVEL’,’I’,’Level I’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);
dbms_output.put_line(‘The Value of X is :’||X);
FND_FLEX_VAL_API.create_independent_vset_value (‘SIMHA_US_JOB_LEVEL’,’II’,’Level II’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);
dbms_output.put_line(‘The Value of X is :’||X);
FND_FLEX_VAL_API.create_independent_vset_value (‘SIMHA_US_JOB_LEVEL’,’III’,’Level III’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);
dbms_output.put_line(‘The Value of X is :’||X);
FND_FLEX_VAL_API.create_independent_vset_value (‘SIMHA_US_JOB_LEVEL’,’MGR’,’MGR’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);
dbms_output.put_line(‘The Value of X is :’||X);
FND_FLEX_VAL_API.create_independent_vset_value (‘SIMHA_US_JOB_LEVEL’,’SUPR’,’SUPR’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);
dbms_output.put_line(‘The Value of X is :’||X);
commit;
EXCEPTION
WHEN OTHERS THEN
v_msg:=fnd_flex_val_api.message;
DBMS_OUTPUT.PUT_LINE(v_msg);
dbms_output.put_line(‘The Value of v_msg is :’||v_msg);
dbms_output.put_line(‘The Error is :’||SQLERRM);
END;
Creation of Dependent ValueSet Values:
DECLARE
v_userid NUMBER;
v_respid NUMBER;
v_appid NUMBER;
X VARCHAR2(200);
v_msg VARCHAR2(2000);
BEGIN
SELECT fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
INTO v_userid,
v_respid,
v_appid
FROM fnd_user fnd ,
fnd_responsibility_tl fresp
WHERE fnd.user_name = ‘HRSIMHA’
AND fresp.responsibility_name = ‘Application Developer’;
fnd_global.apps_initialize(user_id => v_userid, resp_id => v_respid, resp_appl_id => v_appid);
dbms_output.put_line(‘The Value is :’||fnd_global.USER_NAME);
FND_FLEX_VAL_API.create_dependent_vset_value (‘SIMHA_US_JOB_NAME’,’IT’,’Vice President’,’Vice President’,’Y’,sysdate,NULL,NULL,X);
dbms_output.put_line(‘The Value of X is :’||X);
FND_FLEX_VAL_API.create_dependent_vset_value (‘SIMHA_US_JOB_NAME’,’IT’,’Director’,’Director’,’Y’,sysdate,NULL,NULL,X);
dbms_output.put_line(‘The Value of X is :’||X);
FND_FLEX_VAL_API.create_dependent_vset_value (‘SIMHA_US_JOB_NAME’,’IT’,’Manager’,’Manager’,’Y’,sysdate,NULL,NULL,X);
dbms_output.put_line(‘The Value of X is :’||X);
FND_FLEX_VAL_API.create_dependent_vset_value (‘SIMHA_US_JOB_NAME’,’IT’,’Analyst’,’Analyst’,’Y’,sysdate,NULL,NULL,X);
dbms_output.put_line(‘The Value of X is :’||X);
FND_FLEX_VAL_API.create_dependent_vset_value (‘SIMHA_US_JOB_NAME’,’FIN’,’Developer’,’Developer’,’Y’,sysdate,NULL,NULL,X);
dbms_output.put_line(‘The Value of X is :’||X);
commit;
dbms_output.put_line(‘Commited….’);
EXCEPTION
WHEN OTHERS THEN
v_msg:=fnd_flex_val_api.message;
DBMS_OUTPUT.PUT_LINE(v_msg);
dbms_output.put_line(‘The Value of v_msg is :’||v_msg);
dbms_output.put_line(‘The Error is :’||SQLERRM);
END;