Creating Table valuesets from backend in oracle applications

1) Creation of value set through API :

BEGIN
/*You need to initialize the session mode before the call*/ 
FND_FLEX_VAL_API.SET_SESSION_MODE('customer_data');/*Mandatory*/ 
FND_FLEX_VAL_API.CREATE_VALUESET_TABLE
(
 VALUE_SET_NAME =>'PO_TEST_VALUE_SET',
 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 => 'Purchasing',
 table_appl_short_name =>'PO' ,
 TABLE_NAME =>'PO_REQUISITION_HEADERS PRH',
 ALLOW_PARENT_VALUES =>'N',
 VALUE_COLUMN_NAME =>'PRH.SEGMENT1',
 VALUE_COLUMN_TYPE  =>'Char',
 value_column_size  =>20,
 meaning_column_name  => NULL,
 meaning_column_type  => NULL,
 MEANING_COLUMN_SIZE  => NULL,
 ID_COLUMN_NAME    =>NULL,--'PRH.SEGMENT1',
 ID_COLUMN_TYPE   =>NULL,--'Char',
 ID_COLUMN_SIZE   =>null,--u20,
 WHERE_ORDER_BY    =>'where rownum<=100',
 ADDITIONAL_COLUMNS => NULL
  );
commit;
  Exception
  WHEN OTHERS THEN
  dbms_output.put_line(sqlerrm);
  end;

After executing the above code it will generate a message saying Anonymus Block created. To verify that if it is created. Please check the table Fnd_Flex_Value_Sets

select * 
from fnd_flex_value_sets 
where flex_value_set_name = 'PO_VALUE_SET';


2) Deletion of Valueset can be done by executing the below code :

BEGIN
FND_FLEX_VAL_API.DELETE_VALUESET(VALUE_SET => 'PO_VALUE_SET');
END;

This will delete the Valueset.

3) Query to extract all the valusets :

/*Query to extract all the valuesets*/
SELECT FFVS.FLEX_VALUE_SET_ID ,
FFVS.FLEX_VALUE_SET_NAME ,
FFVS.DESCRIPTION SET_DESCRIPTION ,
FFVS.VALIDATION_TYPE,
FFV.FLEX_VALUE,
FFVT.DESCRIPTION VALUE_DESCRIPTION,
FFV.ENABLED_FLAG,
FFV.LAST_UPDATE_DATE,
FFV.LAST_UPDATED_BY,
FFV.ATTRIBUTE1,
FFV.ATTRIBUTE2,
FFV.ATTRIBUTE3 --INCLUDE ATTRIBUTE VALUES BASED ON DFF SEGMENTS
FROM FND_FLEX_VALUE_SETS FFVS ,
            FND_FLEX_VALUES FFV ,
            FND_FLEX_VALUES_TL FFVT
WHERE
FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
AND FFV.FLEX_VALUE_ID = FFVT.FLEX_VALUE_ID
AND FFVT.LANGUAGE = USERENV('LANG')
AND FLEX_VALUE_SET_NAME LIKE 'VALUE_SET_NAME'
ORDER BY FLEX_VALUE ASC;

Challa.