Home » SQL & PL/SQL » SQL & PL/SQL » Query to find application table column details associated with descriptive flex field
Query to find application table column details associated with descriptive flex field [message #442171] Fri, 05 February 2010 01:37 Go to next message
sreedevi_83
Messages: 43
Registered: October 2005
Location: India
Member
Hi All,
I was looking for application column name corresponding to "Draft Invoice Number" ra_interface_lines_all table.
I tried the below.

/* Get descriptive_flexfield_name for the application table name*/
SELECT *
FROM FND_DESCRIPTIVE_FLEXS_VL
WHERE application_id = 222
AND APPLICATION_TABLE_NAME=upper('ra_interface_lines_all');

/* Get the application column name and end user column name*/
SELECT *
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE application_id = 222
AND descriptive_flexfield_name = 'RA_INTERFACE_LINES';

There are many DESCRIPTIVE_FLEX_CONTEXT_CODEs obtained.
I could finally trace out that draft invoice number corresponds to INTERFACE_LINE_ATTRIBUTE2. How can I know what DESCRIPTIVE_FLEX_CONTEXT_CODE should I look for?

I want to build a single query to fetch the application column name and flex field name for a specific table

Please share your thoughts on this.

Thanks in Advance,
Sreedevi.
Re: Query to find application table column details associated with descriptive flex field [message #542627 is a reply to message #442171] Wed, 08 February 2012 04:18 Go to previous messageGo to next message
mvmkandan
Messages: 68
Registered: May 2010
Location: Trivendrum
Member
<code>
Select * from
(SELECT
XMLELEMENT("<ColumnName>",
XMLAGG(XMLELEMENT("<To Merge>", <Colname>)))

FROM FND_DESCRIPTIVE_FLEXS_VL
WHERE application_id = 222
AND APPLICATION_TABLE_NAME=upper('ra_interface_lines_all')
group by ColumnName)a,
(SELECT
XMLELEMENT("<ColumnName>",
XMLAGG(XMLELEMENT("<To Merge>", <Colname>)))

FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE application_id = 222
AND descriptive_flexfield_name = 'RA_INTERFACE_LINES')
group by <columnName>
</code>
Re: Query to find application table column details associated with descriptive flex field [message #542629 is a reply to message #542627] Wed, 08 February 2012 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Code tags need [] not <> as described in How to use [code] tags and make your code easier to read? which you have been asked to read before.

Also this thread is 2 years old, I hope the OP has found the answer by now. I appreciate you are trying to help people but It'd be more useful all round if you picked an up to date thread.
Re: Query to find application table column details associated with descriptive flex field [message #542630 is a reply to message #542627] Wed, 08 February 2012 04:27 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@mvmkandan, you should use square [] brackets, not angle <> ones for CODE tags. However, there's no much use in enclosing unformatted code into the [code] tags, it will be as unreadable as before. So: format it first, enclose it into the [code] tags next.
Re: Query to find application table column details associated with descriptive flex field [message #542638 is a reply to message #542630] Wed, 08 February 2012 05:07 Go to previous messageGo to next message
mvmkandan
Messages: 68
Registered: May 2010
Location: Trivendrum
Member
k Little,
I will correct it
Thanks
Veera
Re: Query to find application table column details associated with descriptive flex field [message #542639 is a reply to message #542627] Wed, 08 February 2012 05:09 Go to previous messageGo to next message
mvmkandan
Messages: 68
Registered: May 2010
Location: Trivendrum
Member
Select * from 
(SELECT 
XMLELEMENT("<ColumnName>",
XMLAGG(XMLELEMENT("<To Merge>", <Colname>)))

FROM FND_DESCRIPTIVE_FLEXS_VL
WHERE application_id = 222
AND APPLICATION_TABLE_NAME=upper('ra_interface_lines_all')
group by ColumnName)a,
(SELECT 
XMLELEMENT("<ColumnName>",
XMLAGG(XMLELEMENT("<To Merge>", <Colname>)))

FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE application_id = 222
AND descriptive_flexfield_name = 'RA_INTERFACE_LINES')
group by <columnName>
Re: Query to find application table column details associated with descriptive flex field [message #542645 is a reply to message #542639] Wed, 08 February 2012 05:20 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's not good. You didn't format it.

Please, have a look here - you'll get the idea as soon as you click that link, it contains two tiny, but descriptive images and won't take more than a few seconds of your precious time.

However, following what you see in there will significantly improve your future messages.
Previous Topic: Split Gender to Two Column (merged 2)
Next Topic: Date formating
Goto Forum:
  


Current Time: Fri Dec 02 12:17:18 CST 2016

Total time taken to generate the page: 0.10749 seconds