If you’ve ever tried to report on comments posted on reconciliations in Oracle’s Account Reconciliation (AR)–formerly known as ARCS–you may know that the only obvious option is to report on the number of comments per reconciliation. While this number may be useful, to see the content of the comments, you have to open each individual reconciliation. Sounds tedious, right?
However, although it isn’t obvious, the comment text is stored within a SQL table in AR. This means that although this field isn’t technically available for selection during report creation, it is possible to edit a reporting query and access the comment text.
Example Requirements
For this example, we will assume the following requirements:
- Period will be selectable and displayed for the overall report.
- Display Columns: Reconciliation ID, Reconciliation Name, Comment Text, and Comment User.
- Only Reconciliation IDs with one or more comments will be displayed.
Creating the Query
The first phase in completing a report in AR is creating the query. Navigate to the Reports Configuration screen and create a new Reconciliation Compliance query. Name the query, ensure the type of query is set to Report Query, and insert the following script:
SELECT
ReconciliationEO.RECONCILIATION_ACCOUNT_ID AS “Account ID”,
ReconciliationEO.RECONCILIATION_NAME AS “Account Name”,
(SELECT PERIOD_NAME FROM ARM_PERIODS WHERE Period_ID = ~PERIOD_ID~) PERIOD_NAME,
(SELECT COMMENT_TEXT ||
(SELECT ‘ Comment By: ‘ || Users.USER_LOGIN
FROM FCM_USERS Users WHERE
Users.USER_ID = Comments.CREATOR_ID)
FROM ARM_COMMENTS Comments WHERE Comments.OBJECT_ID = ReconciliationEO.RECONCILIATION_ID) AS “Comment_UserLogin”
FROM ARM_RECONCILIATIONS ReconciliationEO
WHERE (ReconciliationEO.PERIOD_ID <> -2 AND $ARM_SECURITY_CLAUSE$)
AND (((((SELECT count(*) FROM ARM_COMMENTS Comments WHERE Comments.OBJECT_ID = ReconciliationEO.RECONCILIATION_ID) > 0 ))))
Once the script has been inserted, validate and then save the script. Finally, click Generate Sample XML and save the file when prompted in preparation for the next phase of the process.
Creating the Template
The next phase in completing a report in AR is creating the template. For this, you will need to have BI Publisher already installed and enabled in Microsoft Word. Open a blank Word file, navigate to the BI Publisher tab, and select Sample XML. From here, browse to where the generated sample XML file we created in the last phase is saved. You will then receive a message indicating that the data has loaded successfully.
Now, the report must be designed. Luckily, this is quite easy with BI Publisher using the “Table Wizard” to walk through the process. On the first and second screens, you will not need to change the default options. On the third screen, you will select which fields you would like displayed on the report. In this example, we will select all fields.
On the fourth screen, Group By PERIOD_NAME and select Section for the Break. Feel free to deviate from these selections as you are building your custom report. Then, click Finish.
BI Publisher will now generate the components of the report based on the selections made within the wizard. Once this is complete, add any desired static headings or text, and save the file as Rich Text Format (.rtf). Now, onto the final phase of creating this report.
Creating the Report
The final phase in completing a report in AR is bringing together the query and the template in the report. Navigate to the Reports Configuration screen and create a new Reconciliation Compliance Report. Name the report, select the query we created during the first phase, select the template we created during the second phase, and select the desired report group. If you would like to make changes to the Period parameter, do so on the Parameter tab. Finally, add security on the Access tab and save the report.
Final Results
Finally, the moment we’ve been waiting for! Navigate to the Reports screen and generate the report we just created. You now have a dynamic report that meets all of the initial requirements:
[sch_pullbox] Interested in learning more about Oracle’s Account Reconciliation? Connect with our team today. [/sch_pullbox]
