ORA-01791: not a SELECTed expression during valeset creation

I have come across this error while i was trying to use a select statement in the table column while creating the Table value set.I used the below sql statement

SELECT DISTINCT XIH.BATCH_NO FROM XXMCNG_IREQ_HEADERS XIH ORDER BY XIH.REQ_DATE;

Cause : There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.

Action :
Remove the order by clause from the SQL statement.
(or)
create a view with the same query and the call the view in the Table column while creating the Table value set.

Note :
=====
Restrictions on the ORDER BY Clause.The following restrictions apply to the ORDER BY clause:

•If you have specified the DISTINCT operator in the statement,then this clause cannot refer to columns unless they appear in the select list.
•An order_by_clause can contain no more than 255 expressions.
•You cannot order by a LOB, LONG, or LONG RAW column, nested table, or varray.
•If you specify a group_by_clause in the same statement, then this order_by_clause is restricted to the following expressions:

◦Constants
◦Aggregate functions
◦Analytic functions
◦The functions USER, UID, and SYSDATE
◦Expressions identical to those in the group_by_clause
◦Expressions comprising the preceding expressions that evaluate to the same value for all rows in a group.

 Also See : Creating Table Valuesets from backend in Oracle Applications.

Challa.

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.