Creating a Query

Creating a query is the first step in creating a custom report. There are three ways to approach creating a query:

Use a predefined report as a starting point and duplicate the query, give it a new name and use the SQL and modify it as needed
Use Generate Query and select existing attributes to be queried and/or filtered against and using the information in the Oracle Enterprise Performance Management Cloud Tables and Views for Account Reconciliation Guide, select more columns to add and/or modify filter conditions. See Overview of Tables and Views
Create the SQL query yourself, if you are already familiar with the table information. See Overview of Tables and Views

To create a query:

From Home, select Application, then Report Configuration.
Select Queries, then Actions, and then New.
In New Query, enter Name and Description.
In Type, select one of the following types:
- Parameter Query
  
  A Parameter Query type is used to present a list of options that you can specify for this parameter's value. Parameter Query allows you to present a list of options used when filling in a parameter's value for a Report Query, where the list of options is not a simple Attribute already defined, but is instead a complex query that you need to define.
  
  See Adding Parameter Query to Reports for an example on selecting a parameter query to a report.
- Report Query
  
  Select the records to be included in the report. If the report you are designing will contain parameters, you can design the report to display no records or all records.
  
  For Reconciliation Compliance, 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:
  
  Reconciliation Compliance: $ARM_SECURITY_CLAUSE$
  
  WHERE $ARM_SECURITY_CLAUSE$ AND ReconciliationEO
  
  When using $ARM_SECURITY_CLAUSE$ in the query, the ARM_RECONCILIATIONS must be aliased to ReconciliationEO.
  
  Note:
  
  Because many predefined queries included with Account Reconciliation have the Security Filter applied, you can use them as examples when building your own.
  
  See Reconciliation Compliance Examples below for query examples.
Click Generate Query to generate the report query. See Generating a Query below for details.

After you provide the required details and close the Generate Query dialog, the generated report query is displayed in Query.

Note:
If you need parameters in your report, add the parameters to the report query SQL. The parameter name can be any name, but it must be enclosed in tildes (~). See the examples in the sections below.
Perform one of the following actions:
- Click Validate to test the query for errors.
- Click Validate and Explain Plan to test the query for errors and generate the plan that will be used to run this query. The generated plan is stored in a .txt file that uses the same name as the query. You can view or download the generated plan.
  
  The generated plan contains the set of steps used to run the query. Use the plan to identify issues that may cause suboptimal query performance. Subsequently, you can tune the query to improve its performance.
Note:
By default, generation of execution plans is only available for OCI (Gen 2) environments. To enable it for Classic environments, contact Oracle using a service request.
You can either Save or Save and Close this query.
To generate a sample XML file from the Query to use as a template with BI Publisher, click Generate Sample XML.

Note:

You can easily delete a query, or duplicate a query using the Action menu.

Generating a Query

The Generate Query dialog assists you in creating a query against the database by allowing you to select any existing attribute in the product to be queried and/or filtered against. The dialog then generates the SQL to match the attributes and filters specified, at which time you can modify and enhance it.

To generate the report query:

In Module, select Reconciliation Manager or Transaction Matching.
In Query, select the type of query.
- If you selected Reconciliation Manager in Module, select one of the following options: Reconciliations, Profiles, or Transactions.
- If you selected Transaction Matching in Module, select a query from the ones listed in the drop-down list.
Select Apply Security to apply a security filter so that users see only the data that they are authorized to see based on their roles and the reconciliations to which they are assigned.

Note:
The Apply Security option is not displayed when you select Match Types under Query
Click Next.
Select the columns you want to include in your query and click Next.

Note:
Transaction Matching supports automatic generation of the query for transactions.

Note:
For Transaction Matching, the Account ID column is included in the generated query even if it is not selected from the list of Available columns. To exclude the Account ID column from the report, remove the Account ID column from the SELECT list of the generated query.
Select the filters you want and click OK.

Adding Parameter Query to Reports

To add a parameter query to a report:

From Home, select Application, then Report Configuration.
Select Queries, then select a Parameter Query Type(for example, CurrencyList).
In the Edit Query dialog, review and/or edit the parameter query, then click Save and Close.

You return to the Report Configuration screen.
Select Reports, then the desired report (for example, Balance by Reconciliation).
In the Edit Report dialog, select Parameters.
For the desired Parameter Code, select the Parameter Type as Query and Attribute/Query as CurrencyList from the drop-downs.

The Parameter Value drop-down presents the list of currencies.

Note:
The Attribute/Query drop down displays the list of parameter queries only if you select Query in the Parameter Type drop-down.

Note:
The Parameter Type drop down allows you to pick a Module attribute (Reconciliation Manager or Transaction Matching) for each of those modules. If a module attribute is chosen for the Parameter Type, the Attribute/Query drop down will display a list of that module's attributes.
Click Save and Close.

Reconciliation Compliance Examples

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.

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