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.

Amount in Words using XML/BI Publisher Tags

I once had a requirement to display the amount(number) to words(letters). And below is the approach i have followed.

We can convert the Amount into Words in two different ways:

1)Using the XML/BI Publisher Tag (Applicable only to R12).

2)Using the function IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(TOTAL_AMOUNTS1)

1) Using XML/BI Publisher Tag:

a)  

--> CASE_INIT_CAP, CASE_UPPER, and CASE_LOWER are available to display the text  in Initial Caps,Upper Case,Lower Case respectively.

--> DECIMAL_STYLE_FRACTION1(default), DECIMAL_STYLE_FRACTION2, and DECIMAL_STYLE_WORDS are available.

--> USD in the above tag can be replaced by the curreny code required as per the requirement.

b)  

[ Sorry, there was a problem writing the tag in my browser,so i have taken a snapshot of it and pasted here. ]

2)  Using IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(TOTAL_AMOUNTS1)

 Example :

declare
v1 VARCHAr2(2000);
begin
v1:=IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(p_amount =>101324546014);
dbms_output.put_line('amount: '||v1);

end; 

If you expect the Amount in Words to show as shown below

--> One Thousand Pounds and 14 Pence.

 --> One Hundred Euros and 1 Cent.
--> 1 Dollar and 99 Cents.

  

Refer the Oracle Document for setting the currency unit and sub_unit in the system. Click ME.

Challa.