Oracle reports [message #581697] |
Wed, 10 April 2013 00:34 |
|
bhawnakaamra
Messages: 66 Registered: March 2013 Location: delhi
|
Member |
|
|
Hello All,
I am trying to get a list of all the custom reports that we have made in all the modules and there parameter name and the module name.
Is there a query that can be written against to get this listing?
[Updated on: Wed, 10 April 2013 00:36] Report message to a moderator
|
|
|
|
Re: Oracle reports [message #581749 is a reply to message #581718] |
Wed, 10 April 2013 05:36 |
|
bhawnakaamra
Messages: 66 Registered: March 2013 Location: delhi
|
Member |
|
|
select a1 as Module_name,
a2 as Program_name,
a3 as Program_Status,
a4 as Executable_Name,
a5 as Parameter_Name,
a6 as User_display
from
(SELECT UNIQUE APP.application_name as a1,
fcpt.user_concurrent_program_name as a2,
fc.enabled_flag as a3,
e.executable_name as a4,
param.END_USER_COLUMN_NAME as a5,
param.display_flag as a6
FROM fnd_responsibility fr,
fnd_responsibility_tl frt,
fnd_request_groups frg,
fnd_descr_flex_col_usage_vl param,
fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_programs_vl fc,
fnd_application_all_view APP,
fnd_concurrent_programs fcp,
fnd_executables e
WHERE fr.responsibility_id = frt.responsibility_id
AND fcp.executable_id = e.executable_id
AND fcp.application_id = param.application_id(+)
AND fc.user_concurrent_program_name = fcpt.user_concurrent_program_name
AND fr.request_group_id = frg.request_group_id
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpt.concurrent_program_id
AND fcpt.language = 'US'
AND frt.language = 'US'
AND param.display_flag = 'Y'
AND param.descriptive_flexfield_name = '$SRS$.'
|| fcp.concurrent_program_name
AND fc.enabled_flag = 'Y'
AND fcp.concurrent_program_id = fcpt.concurrent_program_id
AND FRT.application_id = APP.application_id
AND fcpt.user_concurrent_program_name LIKE 'STL%'
order by app.application_name,fcpt.user_concurrent_program_name )
This is right...if any body need help can get it from here
|
|
|
Re: Oracle reports [message #581761 is a reply to message #581749] |
Wed, 10 April 2013 07:27 |
|
vamsi kasina
Messages: 2112 Registered: October 2003 Location: Cincinnati, OH
|
Senior Member |
|
|
Quote:FROM fnd_responsibility fr,
fnd_responsibility_tl frt,
fnd_request_groups frg,
fnd_descr_flex_col_usage_vl param,
fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_programs_vl fc,
fnd_application_all_view APP,
fnd_concurrent_programs fcp,
fnd_executables e
The following tables are not needed.
As you have joined these unnecessary tables / views, you had to use DISTINCT / UNIQUE.
fnd_responsibility
fnd_responsibility_tl
fnd_request_groups
fnd_request_group_units
fnd_concurrent_programs_tl
fnd_concurrent_programs -- you have already fnd_concurrent_programs_vl
The following should be sufficient for your requirement.SELECT fa.application_name Module_Name
,fcp.user_concurrent_program_name Program_Name
,fcp.enabled_flag Program_Status
,fe.executable_name Executable_Name
,fdfcu.end_user_column_name Parameter_Name
,fdfcu.display_flag User_Display
FROM fnd_concurrent_programs_vl fcp
,fnd_executables fe
,fnd_application_vl fa
,fnd_descr_flex_col_usage_vl fdfcu
WHERE fcp.concurrent_program_name LIKE 'XLA%'
AND fcp.application_id = fa.application_id
AND fcp.executable_id = fe.executable_id
AND fdfcu.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
ORDER BY 1,2,fdfcu.column_seq_num; By
Vamsi
|
|
|
|
|