GL account movement can be viewed using the off-the-shelf ‘Statement Of Accounts’ report. It has several parameters to filter the information to a specific result set. I encountered two issues with the off-the-shelf version. First, it doesn’t return the account element in the result set. Thus, you don’t see the account element of a particular transaction.
Second, in one of our implementations, we hijacked a table called C_Activity and used that as a repository for internal company “Departments”. We made the “Department” field mandatory in the Invoice and Payment windows. The accounting engine posts the “Department” as part of the dimension. However, the “Invoice” and “Payment” posting goes through a “View Allocation” posting. The “View Allocation” windows clears entries between the invoice and payment postings. Unfortunately, the “View Allocation” does not post the “Department” dimension. When we filter the Statement Of Accounts by account element, it fails to pick up the “View Allocation” postings because the current logic doesn’t pick up the related C_Activity results. Thus, the result set returns incomplete results i.e. missing invoice transactions that were posted in the “View Allocation” window. The following:
will return a statement without the element and without the “View Allocation” related postings.
There are two ways to solve the second issue. First, we could have updated the “View Allocation” to allow department to be included in the posting. Second, update the “Statement Of Accounts” to include related “Department” information when filtered.
We chose the latter. In order to do this, we created a new plugin with a modified version of the system “finstatement.java” code. The new plugin includes a modified SQL Statement to get the data from the Fact_Acct accounting table (accounting table) joined to two tables called C_BPartner (business partner table) and C_Activity (modified Department table). By joining the three tables, we were able to ensure that the posting from the “View Allocation” is returned with the posting from the “Invoice” and “Payment” tables. Furthermore, we also returned the accounting element name to provide further information. The new statement of account returns the following:
Figure 3 contains more details than Figure 2. We managed to include the account element which gives the results more information. We also managed to include the “department” transactions related to the invoice posting. The changes makes the information I require more complete. Please note that the main drawback to this method is the expensive processing time needed to return all the results. Thus, this should be approached with caution.