Report Query
Select the records to be included in the report. You can apply a security filter, so users see only the data that they are authorized to see based on their roles and the reconciliations to which they are assigned. To apply a Security Filter to a report query, add the following syntax to the end of the query WHERE CLAUSE statement:
Close Manager Queries:$CM_SECURITY_CLAUSE$
A sample Close Managerr query with the Security Filter applied: SELECT Task_name FROM fcc_tasks WHERE schedule_id = ~Schedule~ AND $CM_SECURITY_CLAUSE$
A sample Close Manager query with the Security Filter applied: SELECT Task_name FROM fcc_tasks WHERE schedule_id = ~Schedule~ AND $CM_SECURITY_CLAUSE$
Account Reconciliation Manager Queries: $ARM_SECURITY_CLAUSE$
Note:
Because many predefined queries included with Oracle Hyperion Financial Close Management
have the Security Filter applied, you can use them as examples when building your own.
If the report you are designing will contain parameters, you can design the report to display no records or all records. Examples of each are provided below. These examples assume that you want to run a report that displays the list of periods that contain the "Monthly" frequency.
-
Parameter Query: Select frequency_id, frequency_name from arm_frequencies
-
Report Query, Option 1: (Return no periods if the user does not provide a frequency value):
Select p.period_name from arm_periods p, arm_period_frequencies pf where p.period_id = pf.period_id and pf.frequency_id = ~FREQUENCY~
-
Report Query Option 2: (Return all periods if the user does not provide a frequency value):
Select p.period_name from arm_periods p, arm_period_frequencies pf where p.period_id = pf.period_id and pf.frequency_id = coalesce(~FREQUENCY~,pf.frequency_id)
In the second option, the coalesce()
function returns the first non-null value in the list; therefore, if the FREQUENCY was null, it would return pf.frequency_id
and in that case that condition would always be true (pf.frequency_id = pf.frequency_id
), causing all records to be returned.