Home » SQL & PL/SQL » SQL & PL/SQL » GL Accounting Segment Hierarcy query
GL Accounting Segment Hierarcy query [message #260824] Tue, 21 August 2007 01:30 Go to next message
Messages: 2
Registered: June 2007
Junior Member
Pls have a look at this query -

SELECT v.flex_value_set_id, h.parent_flex_value, v.flex_value, v.description,
FROM fnd_flex_values_vl v,
fnd_flex_value_norm_hierarchy h,
fnd_flex_value_sets s
WHERE h.flex_value_set_id = v.flex_value_set_id
AND s.flex_value_set_id = v.flex_value_set_id
AND h.range_attribute IN ('P','C')

the h.range_attribute column describes whether the flex value set id is P = 'Parent' OR C = 'Child'.A particular flex value set id can be both, a parent and a child. I want a query which will give me all only the parent records and will ignore the child records.

Pls help.
Re: GL Accounting Segment Hierarcy query [message #260984 is a reply to message #260824] Tue, 21 August 2007 08:03 Go to previous messageGo to next message
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If i understand your question correctly, you want the list of "flex value set id" exists only as a parent and not as a child.

This query give me all the records who are not managers but they are employees.
SQL> select empno, mgr, ename from emp a where not exists (select null from emp where mgr = a.empno);

---------- ---------- ----------
7369 7902 SMITH
7499 7698 ALLEN
7521 7698 WARD
7654 7698 MARTIN
7844 7698 TURNER
7876 7788 ADAMS
7900 7698 JAMES
7934 7782 MILLER

Try to build your query from this example.

Also could you please from next time
a) Format your post
b) You have explained what you want but there are not create scripts and no expected output.
c) Last but not least before you post read the sticky in this forum.


Re: GL Accounting Segment Hierarcy query [message #260991 is a reply to message #260824] Tue, 21 August 2007 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 63945
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To be more precise:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Re: GL Accounting Segment Hierarcy query [message #265612 is a reply to message #260991] Thu, 06 September 2007 13:24 Go to previous message
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Describe your tables
desc fnd_flex_values_vl;
desc fnd_flex_value_norm_hierarchy;
desc fnd_flex_value_sets; 
Previous Topic: Procedure Parameters
Next Topic: Help Please
Goto Forum:

Current Time: Fri Oct 28 05:45:22 CDT 2016

Total time taken to generate the page: 0.06181 seconds