Custom Query Examples

Reconciliation Compliance Examples

Example 1

These Reconciliation Compliance examples assume that you want to run a report that displays the list of periods that contain the "Monthly" frequency.

If the report you are designing will contain parameters, you can design the report to display no records or all records.

• 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)

  Note:

  The parameter name can be any name but must be enclosed in tildes (~).

  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.

Example 2

You can pass multiple values such as Login1, Login2, Login3 using a single TEXT parameter (~INPUT_PARAMETER~) to a report query that uses the Oracle Database INSTR function.

The following report query example returns rows from the FCM_USERS table after being passed a TEXT parameter of Login1,Login2,Login3.

SELECT first_name, last_name FROM fcm_users 
       WHERE INSTR(','||'~INPUT_PARAMETER~'||',', ','||USER_LOGIN||',') > 0;

Transaction Matching Examples

These examples assume you want to see list of reconciliations for any given reconciliation type.

• Parameter Query

  SELECT TM_RECON_TYPE.TEXT_ID AS RECONCILIATION_TYPE_ID, (TM_RECON_TYPE.NAME || ' (' || TM_RECON_TYPE.TEXT_ID || ')') AS RECONCILIATION_TYPE_NAME FROM TM_RECON_TYPE TM_RECON_TYPE

• Report Query, Option 1: Return no reconciliations if the user has not selected a reconciliation type.

  SELECT TM_RECON.TEXT_ID AS RECONCILIATION_ID,TM_RECON.NAME AS RECONCILIATION_NAME, TM_RECON_TYPE.TEXT_ID AS RECONCILIATION_TYPE_ID, TM_RECON_TYPE.NAME AS RECONCILIATION_TYPE_NAME, NVL ((SELECT P_FCM_USERS.USER_NAME FROM FCM_USERS_V P_FCM_USERS WHERE TM_RECON.PREPARER = P_FCM_USERS.USER_ID ), TM_RECON.PREPARER ) AS PREPARER, TM_RECON.DESCRIPTION DESCRIPTION FROM TM_RECON TM_RECON, TM_RECON_TYPE TM_RECON_TYPE WHERE TM_RECON.RECON_TYPE_ID = TM_RECON_TYPE.RECON_TYPE_ID AND (TM_RECON_TYPE.TEXT_ID = '~RECONCILIATION_TYPE_ID~')

• Report Query, Option 2: Return all reconciliations if the user has not selected any reconciliation type.

  SELECT TM_RECON.TEXT_ID AS RECONCILIATION_ID,TM_RECON.NAME AS RECONCILIATION_NAME, TM_RECON_TYPE.TEXT_ID AS RECONCILIATION_TYPE_ID, TM_RECON_TYPE.NAME AS RECONCILIATION_TYPE_NAME, NVL ((SELECT P_FCM_USERS.USER_NAME FROM FCM_USERS_V P_FCM_USERS WHERE TM_RECON.PREPARER = P_FCM_USERS.USER_ID ), TM_RECON.PREPARER ) AS PREPARER, TM_RECON.DESCRIPTION DESCRIPTION FROM TM_RECON TM_RECON, TM_RECON_TYPE TM_RECON_TYPE WHERE TM_RECON.RECON_TYPE_ID = TM_RECON_TYPE.RECON_TYPE_ID AND COALESCE('~RECONCILIATION_TYPE_ID~',TM_RECON_TYPE.TEXT_ID)

  Note:

  The parameter name can be any name but must be enclosed in tildes (~).

  In the second option, the coalesce() function returns the first non-null value in the list; therefore, if the RECONCILIATION_TYPE_ID was null, it would return TM_RECON_TYPE.TEXT_ID and in that case that condition would always be true ((TM_RECON_TYPE.TEXT_ID = TM_RECON_TYPE.TEXT_ID), causing all records to be returned.

• Report Query, Option 3: Return all reconciliations if the user has access).

  SELECT TM_RECON.TEXT_ID AS RECONCILIATION_ID, TM_RECON.NAME AS RECONCILIATION_NAME, TM_RECON_TYPE.TEXT_ID AS RECONCILIATION_TYPE_ID, TM_RECON_TYPE.NAME AS RECONCILIATION_TYPE_NAME, TM_RECON.DESCRIPTION DESCRIPTION FROM TM_RECON TM_RECON, TM_RECON_TYPE TM_RECON_TYPE, ARM_RECONCILIATIONS ProfileEO WHERE TM_RECON.RECON_TYPE_ID = TM_RECON_TYPE.RECON_TYPE_ID AND TM_RECON.TEXT_ID = ProfileEO.RECONCILIATION_ACCOUNT_ID AND $ARM_SECURITY_CLAUSE$ AND ProfileEO.PERIOD_ID = -2