<\/p>\n
Creation Of\u00a0Independent\u00a0Value Set:<\/b><\/u> <\/HTML><\/HTML><\/p>\n","protected":false},"excerpt":{"rendered":" Creation Of\u00a0Independent\u00a0Value Set:DECLARE\u00a0 value_set_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0VARCHAR2 (200);\u00a0 description \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);\u00a0 security_available \u00a0 \u00a0 \u00a0VARCHAR2 (200);\u00a0 enable_longlist… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[127,138],"tags":[],"class_list":["post-15618","post","type-post","status-publish","format-standard","hentry","category-applications","category-sql"],"_links":{"self":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/15618","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=15618"}],"version-history":[{"count":0,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/15618\/revisions"}],"wp:attachment":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=15618"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=15618"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=15618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
DECLARE<\/span>
\u00a0 value_set_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0VARCHAR2 (200);<\/span>
\u00a0 description \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);<\/span>
\u00a0 security_available \u00a0 \u00a0 \u00a0VARCHAR2 (200);<\/span>
\u00a0 enable_longlist \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);<\/span>
\u00a0 format_type \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);<\/span>
\u00a0 maximum_size \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0NUMBER;<\/span>
\u00a0 PRECISION \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 NUMBER;<\/span>
\u00a0 numbers_only \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0VARCHAR2 (200);<\/span>
\u00a0 uppercase_only \u00a0 \u00a0 \u00a0 \u00a0 \u00a0VARCHAR2 (200);<\/span>
\u00a0 right_justify_zero_fill VARCHAR2 (200);<\/span>
\u00a0 min_value \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);<\/span>
\u00a0 max_value \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);<\/span>
\u00a0 v_session_mode \u00a0 \u00a0 \u00a0 \u00a0 \u00a0VARCHAR2 (20) := ‘customer_data’;<\/span>
\u00a0 x \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);<\/span>
\u00a0 v_msg \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (2000);<\/span>
BEGIN<\/span>
\u00a0 fnd_flex_val_api.set_session_mode (v_session_mode);<\/span>
\u00a0 value_set_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0:= ‘SIMHA_US_JOB_DEPT’;<\/span>
\u00a0 description \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 := ‘SIMHA US JOB DEPT’;<\/span>
\u00a0 security_available \u00a0 \u00a0 \u00a0:= ‘N’;<\/span>
\u00a0 enable_longlist \u00a0 \u00a0 \u00a0 \u00a0 := ‘N’;<\/span>
\u00a0 format_type \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 := ‘C’;<\/span>
\u00a0 maximum_size \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0:= 30;<\/span>
\u00a0 PRECISION \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 := 1;<\/span>
\u00a0 numbers_only \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0:= ‘N’;<\/span>
\u00a0 uppercase_only \u00a0 \u00a0 \u00a0 \u00a0 \u00a0:= ‘N’;<\/span>
\u00a0 right_justify_zero_fill := ‘N’;<\/span>
\u00a0 min_value \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 := NULL;<\/span>
\u00a0 max_value \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 := NULL;<\/span>
\u00a0 IF (apps.fnd_flex_val_api.valueset_exists(value_set_name))<\/span>
\u00a0 THEN<\/span>
\u00a0 \u00a0DBMS_OUTPUT.put_line (‘Value set exists.. Deleting it…’);<\/span>
\u00a0 \u00a0apps.fnd_flex_val_api.delete_valueset(value_set_name);<\/span>
\u00a0 END IF;<\/span>
\u00a0 IF NOT(apps.fnd_flex_val_api.valueset_exists(value_set_name))<\/span>
\u00a0 THEN<\/span>
\u00a0 \u00a0DBMS_OUTPUT.put_line (‘Value set doesn”t exists.. Creating it…’);<\/span>
\u00a0 \u00a0apps.fnd_flex_val_api.create_valueset_independent (value_set_name, description, security_available, enable_longlist, format_type, maximum_size, PRECISION, numbers_only, uppercase_only,<\/span>
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0right_justify_zero_fill, min_value, max_value );<\/span>
\u00a0 \u00a0commit;<\/span>
\u00a0 \u00a0IF (apps.fnd_flex_val_api.valueset_exists(value_set_name))<\/span>
\u00a0 \u00a0THEN<\/span>
\u00a0 \u00a0DBMS_OUTPUT.put_line (‘Value set:’||value_set_name||’ got created’);<\/span>
\u00a0 END IF;<\/span>
\u00a0 END IF;<\/span>
EXCEPTION<\/span>
WHEN OTHERS THEN<\/span>
\u00a0 v_msg := fnd_flex_val_api.MESSAGE;<\/span>
\u00a0 DBMS_OUTPUT.put_line (v_msg);<\/span>
\u00a0 DBMS_OUTPUT.put_line (SQLERRM);<\/span>
END;<\/span><\/p>\n
Creation Of Dependent\u00a0Value Set:<\/b><\/u>
<\/u>DECLARE<\/span>
\u00a0 value_set_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0VARCHAR2 (200);<\/span>
\u00a0 parent_value_set_name \u00a0 VARCHAR2 (200);<\/span>
\u00a0 description \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);<\/span>
\u00a0 security_available \u00a0 \u00a0 \u00a0VARCHAR2 (200);<\/span>
\u00a0 enable_longlist \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);<\/span>
\u00a0 format_type \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);<\/span>
\u00a0 maximum_size \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0NUMBER;<\/span>
\u00a0 PRECISION \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 NUMBER;<\/span>
\u00a0 numbers_only \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0VARCHAR2 (200);<\/span>
\u00a0 uppercase_only \u00a0 \u00a0 \u00a0 \u00a0 \u00a0VARCHAR2 (200);<\/span>
\u00a0 right_justify_zero_fill VARCHAR2 (200);<\/span>
\u00a0 min_value \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);<\/span>
\u00a0 max_value \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);<\/span>
\u00a0 v_session_mode \u00a0 \u00a0 \u00a0 \u00a0 \u00a0VARCHAR2 (20) := ‘customer_data’;<\/span>
\u00a0 x \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (200);<\/span>
\u00a0 v_msg \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VARCHAR2 (2000);<\/span>
BEGIN<\/span>
\u00a0 fnd_flex_val_api.set_session_mode (v_session_mode);<\/span>
\u00a0 value_set_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0:= ‘SIMHA_US_JOB_NAME’;<\/span>
\u00a0 parent_value_set_name \u00a0 := ‘SIMHA_US_JOB_DEPT’;<\/span>
\u00a0 description \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 := ‘SIMHA US JOB NAME’;<\/span>
\u00a0 security_available \u00a0 \u00a0 \u00a0:= ‘N’;<\/span>
\u00a0 enable_longlist \u00a0 \u00a0 \u00a0 \u00a0 := ‘N’;<\/span>
\u00a0 format_type \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 := ‘C’;<\/span>
\u00a0 maximum_size \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0:= 30;<\/span>
\u00a0 PRECISION \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 := 1;<\/span>
\u00a0 numbers_only \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0:= ‘N’;<\/span>
\u00a0 uppercase_only \u00a0 \u00a0 \u00a0 \u00a0 \u00a0:= ‘N’;<\/span>
\u00a0 right_justify_zero_fill := ‘N’;<\/span>
\u00a0 min_value \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 := NULL;<\/span>
\u00a0 max_value \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 := NULL;<\/span>
\u00a0 IF (apps.fnd_flex_val_api.valueset_exists(value_set_name))<\/span>
\u00a0 THEN<\/span>
\u00a0 \u00a0DBMS_OUTPUT.put_line (‘Value set exists.. Deleting it…’);<\/span>
\u00a0 \u00a0apps.fnd_flex_val_api.delete_valueset(value_set_name);<\/span>
\u00a0 END IF;<\/span>
\u00a0 IF NOT(apps.fnd_flex_val_api.valueset_exists(value_set_name))<\/span>
\u00a0 THEN<\/span>
\u00a0 \u00a0DBMS_OUTPUT.put_line (‘Value set doesn”t exists.. Creating it…’);<\/span>
\u00a0 \u00a0apps.fnd_flex_val_api.create_valueset_dependent (value_set_name, description, security_available, enable_longlist, format_type, maximum_size, PRECISION, numbers_only, uppercase_only,<\/span>
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0right_justify_zero_fill, min_value, max_value,parent_value_set_name,<\/span>
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0‘DEFAULT’,’Default’);<\/span>
\u00a0 \u00a0commit;<\/span>
\u00a0 \u00a0IF (apps.fnd_flex_val_api.valueset_exists(value_set_name))<\/span>
\u00a0 \u00a0THEN<\/span>
\u00a0 \u00a0DBMS_OUTPUT.put_line (‘Value set:’||value_set_name||’ got created’);<\/span>
\u00a0 END IF;<\/span>
END IF;<\/span>
EXCEPTION<\/span>
WHEN OTHERS THEN<\/span>
\u00a0 v_msg := fnd_flex_val_api.MESSAGE;<\/span>
\u00a0 DBMS_OUTPUT.put_line (v_msg);<\/span>
\u00a0 DBMS_OUTPUT.put_line (SQLERRM);<\/span>
END;<\/span><\/p>\n
<\/u>DECLARE<\/span>
\u00a0 v_userid NUMBER;<\/span>
\u00a0 v_respid NUMBER;<\/span>
\u00a0 v_appid \u00a0NUMBER;<\/span>
\u00a0 X \u00a0 \u00a0 \u00a0 \u00a0VARCHAR2(200);<\/span>
\u00a0 v_msg \u00a0 \u00a0VARCHAR2(2000);<\/span>
BEGIN<\/span>
\u00a0 SELECT fnd.user_id ,<\/span>
\u00a0 \u00a0 fresp.responsibility_id,<\/span>
\u00a0 \u00a0 fresp.application_id<\/span>
\u00a0 INTO v_userid,<\/span>
\u00a0 \u00a0 v_respid,<\/span>
\u00a0 \u00a0 v_appid<\/span>
\u00a0 FROM fnd_user fnd ,<\/span>
\u00a0 \u00a0 fnd_responsibility_tl fresp<\/span>
\u00a0 WHERE fnd.user_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 = ‘HRSIMHA’<\/span>
\u00a0 AND fresp.responsibility_name = ‘Application Developer’;<\/span>
\u00a0 fnd_global.apps_initialize(user_id => v_userid, resp_id => v_respid, resp_appl_id => v_appid);<\/span>
\u00a0 dbms_output.put_line(‘The Value is :’||fnd_global.USER_NAME);<\/span>
\u00a0 FND_FLEX_VAL_API.create_independent_vset_value (‘SIMHA_US_JOB_LEVEL’,’EXEC’,’Executives’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);<\/span>
\u00a0 dbms_output.put_line(‘The Value of X is :’||X);<\/span>
\u00a0 FND_FLEX_VAL_API.create_independent_vset_value (‘SIMHA_US_JOB_LEVEL’,’I’,’Level I’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);<\/span>
\u00a0 dbms_output.put_line(‘The Value of X is :’||X);<\/span>
\u00a0 FND_FLEX_VAL_API.create_independent_vset_value (‘SIMHA_US_JOB_LEVEL’,’II’,’Level II’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);<\/span>
\u00a0 dbms_output.put_line(‘The Value of X is :’||X);<\/span>
\u00a0 FND_FLEX_VAL_API.create_independent_vset_value (‘SIMHA_US_JOB_LEVEL’,’III’,’Level III’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);<\/span>
\u00a0 dbms_output.put_line(‘The Value of X is :’||X);<\/span>
\u00a0 FND_FLEX_VAL_API.create_independent_vset_value (‘SIMHA_US_JOB_LEVEL’,’MGR’,’MGR’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);<\/span>
\u00a0 dbms_output.put_line(‘The Value of X is :’||X);<\/span>
\u00a0 FND_FLEX_VAL_API.create_independent_vset_value (‘SIMHA_US_JOB_LEVEL’,’SUPR’,’SUPR’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);<\/span>
\u00a0 dbms_output.put_line(‘The Value of X is :’||X);<\/span>
\u00a0 commit;<\/span>
EXCEPTION<\/span>
WHEN OTHERS THEN<\/span>
\u00a0 v_msg:=fnd_flex_val_api.message;<\/span>
\u00a0 DBMS_OUTPUT.PUT_LINE(v_msg);<\/span>
\u00a0 dbms_output.put_line(‘The Value of v_msg is :’||v_msg);<\/span>
\u00a0 dbms_output.put_line(‘The Error is :’||SQLERRM);<\/span>
END;<\/span>
Creation of Dependent ValueSet Values:<\/u><\/b>
DECLARE<\/span>
\u00a0 v_userid NUMBER;<\/span>
\u00a0 v_respid NUMBER;<\/span>
\u00a0 v_appid \u00a0NUMBER;<\/span>
\u00a0 X \u00a0 \u00a0 \u00a0 \u00a0VARCHAR2(200);<\/span>
\u00a0 v_msg \u00a0 \u00a0VARCHAR2(2000);<\/span>
BEGIN<\/span>
\u00a0 SELECT fnd.user_id ,<\/span>
\u00a0 \u00a0 fresp.responsibility_id,<\/span>
\u00a0 \u00a0 fresp.application_id<\/span>
\u00a0 INTO v_userid,<\/span>
\u00a0 \u00a0 v_respid,<\/span>
\u00a0 \u00a0 v_appid<\/span>
\u00a0 FROM fnd_user fnd ,<\/span>
\u00a0 \u00a0 fnd_responsibility_tl fresp<\/span>
\u00a0 WHERE fnd.user_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 = ‘HRSIMHA’<\/span>
\u00a0 AND fresp.responsibility_name = ‘Application Developer’;<\/span>
\u00a0 fnd_global.apps_initialize(user_id => v_userid, resp_id => v_respid, resp_appl_id => v_appid);<\/span>
\u00a0 dbms_output.put_line(‘The Value is :’||fnd_global.USER_NAME);<\/span>
\u00a0 FND_FLEX_VAL_API.create_dependent_vset_value (‘SIMHA_US_JOB_NAME’,’IT’,’Vice President’,’Vice President’,’Y’,sysdate,NULL,NULL,X);<\/span>
\u00a0 dbms_output.put_line(‘The Value of X is :’||X);<\/span>
\u00a0 FND_FLEX_VAL_API.create_dependent_vset_value (‘SIMHA_US_JOB_NAME’,’IT’,’Director’,’Director’,’Y’,sysdate,NULL,NULL,X);<\/span>
\u00a0 dbms_output.put_line(‘The Value of X is :’||X);<\/span>
\u00a0 FND_FLEX_VAL_API.create_dependent_vset_value (‘SIMHA_US_JOB_NAME’,’IT’,’Manager’,’Manager’,’Y’,sysdate,NULL,NULL,X);<\/span>
\u00a0 dbms_output.put_line(‘The Value of X is :’||X);<\/span>
\u00a0 FND_FLEX_VAL_API.create_dependent_vset_value (‘SIMHA_US_JOB_NAME’,’IT’,’Analyst’,’Analyst’,’Y’,sysdate,NULL,NULL,X);<\/span>
\u00a0 dbms_output.put_line(‘The Value of X is :’||X);<\/span>
\u00a0 FND_FLEX_VAL_API.create_dependent_vset_value (‘SIMHA_US_JOB_NAME’,’FIN’,’Developer’,’Developer’,’Y’,sysdate,NULL,NULL,X);<\/span>
\u00a0 dbms_output.put_line(‘The Value of X is :’||X);<\/span>
\u00a0\u00a0<\/span>
\u00a0 commit;<\/span>
\u00a0 dbms_output.put_line(‘Commited….’);<\/span>
EXCEPTION<\/span>
WHEN OTHERS THEN<\/span>
\u00a0 v_msg:=fnd_flex_val_api.message;<\/span>
\u00a0 DBMS_OUTPUT.PUT_LINE(v_msg);<\/span>
\u00a0 dbms_output.put_line(‘The Value of v_msg is :’||v_msg);<\/span>
\u00a0 dbms_output.put_line(‘The Error is :’||SQLERRM);<\/span>
END;<\/span><\/p>\n