SELECT fa.application_short_name
,      frg.request_group_name
,      frt.responsibility_name
FROM   fnd_concurrent_programs_vl   fcpv
,      fnd_request_group_units      frgu
,      fnd_request_groups           frg
,      fnd_application              fa
,      fnd_responsibility           fr
,      fnd_responsibility_tl        frt
WHERE  1 = 1
AND    fcpv.concurrent_program_id = frgu.request_unit_id
AND    frg.request_group_id = frgu.request_group_id
AND    fa.application_id = frg.application_id
AND    frg.request_group_id = fr.request_group_id
AND    NVL(fr.end_date,SYSDATE + 1) > SYSDATE
AND    fr.responsibility_id = frt.responsibility_id
AND    fcpv.user_concurrent_program_name = 'Aged Creditors Report'
;

1. Firstly create a Custom Dicoverer Folder to return the list of values, as follows. In Discoverer Administrator, Right-click on the Business Area containing your reports-related Discoverer objects (Folders, etc.), and choose New Custom Folder…

2. Paste in the SQL statement which will reurn the required list of values. For example, and General Ledger accounting segment value:

SELECT 'All'                      DEPARTMENT
FROM   dual
UNION
SELECT ffv.flex_value             DEPARTMENT
FROM   apps.fnd_flex_value_sets   ffvs
,      apps.fnd_flex_values       ffv
WHERE  ffv.flex_value_set_id = ffvs.flex_value_set_id
AND    ffvs.flex_value_set_name = 'DEPARTMENT'
AND    ffv.enabled_flag = 'Y'
AND    ffv.summary_flag = 'N'

Click on Validate SQL to ensure that the syntax is correct, and that the database object names are valid.

3. Right-click on the new folder, choose Properties, and and assign useful values for the Name, Description and Identifier fields; say GL_DEPARTMENT_LOV.

4. Expand the folder to show the Department Item, right-click on the item, and choose New Item Class. Click Next > three times, accepting the default attribute values, then finally giving the new item class a meaningful name: say GL_DEPARTMENT_LOV. The Item Class should now be visible in the Item classes tab in Discoverer Administrator.

5. Return to the Data tab, and expand the folder on which the actual Discoverer report is based. Find the item for which this list of values is being built, right-click on it, and click Properties. In the Item class attribute, choose your new item class from the dropdown, and click Apply.

6. In Disoverer Desktop, re-connect to the database, to reflect the changes just made to the EUL, and open the Disoverer Workbook.

7. Go to Tools > Parameters > New, to create a new parameter (based on the Department field, in this example). Choosing the Department field in the For Item field means that the resulting parameter will automatically use the ist of values created above.

8. In order to provide an “All” option for the list of values (ie. to make the parameter effectively non-mandatory), you must ensure that the list-of-values SQL query contains a UNION (see above example).

9. Then, Discoverer Desktop, go to Tools > Conditions, highlight the condition created for the new Parameter, and click Edit.

10. Click Advanced >>, and then Add. Using the OR grouping, create a new condition :GL_DEPARTMENT = ‘All’. Click OK to save.