Well, in this blog, which is my second one covering logic accounts in FDMEE, I talk about implementing bank balance movement to either Assets or Liabilities in the balance sheet using FDMEE. I loved working on this requirement since it showed internally how powerful a simple tool like FDMEE is in terms of functional clarity that it brings about.
So, lets jump onto the requirement. The requirement is simple. Suppose I have a set of 100 accounts which represent my bank accounts for different entities. The requirement is that I need these 100 accounts to be parked in the correct place (Assets or Liabilities) based on whether the sum of these 100 accounts is positive or negative. If the summation of bank balances is positive, it moves as an Asset to “Cash in Hand” account whereas if the sum is negative, it should move to Liabilities under “Overdraft in Bank”
The next snapshot shows the sample file that I have for my Bank account balances.
The next couple of snapshots show the FDMEE data load rule and the workbench after the file has been imported successfully.
The next snapshot shows me creating a Logic account using a Complex logic.
I add an entry into the Logic group for the summation of the bank balance accounts as shown in the below snapshot.
The criteria for the logic accounts is as shown in the below snapshot.
The above criteria can be logically treated as two steps.
On running the import, observe that the Logic account data has come through as shown in the next snapshot.
Now the next step is to ignore the individual Bank Balance accounts, which is do with a simple rule and map only the logic account for Cash. After applying the new mappings, the data load workbench is as shown in the next snapshot.
In order to implement the movement to either Assets or Liabilities, I now create a SQL mapping as shown in the below snapshots.
The snapshot of the data load workbench after importing the data once again is as shown in the below snapshot. Observe that the data has move to the “Cash In Bank” account after FDMEE mappings
Now, let us check if the ‘Overdraft in Bank’ is working as expected. I update the file to make most of the data negative as shown in the next snapshot.
So, lets jump onto the requirement. The requirement is simple. Suppose I have a set of 100 accounts which represent my bank accounts for different entities. The requirement is that I need these 100 accounts to be parked in the correct place (Assets or Liabilities) based on whether the sum of these 100 accounts is positive or negative. If the summation of bank balances is positive, it moves as an Asset to “Cash in Hand” account whereas if the sum is negative, it should move to Liabilities under “Overdraft in Bank”
The next snapshot shows the sample file that I have for my Bank account balances.
The next couple of snapshots show the FDMEE data load rule and the workbench after the file has been imported successfully.
The next snapshot shows me creating a Logic account using a Complex logic.
I add an entry into the Logic group for the summation of the bank balance accounts as shown in the below snapshot.
The above criteria can be logically treated as two steps.
- Whenever the Account name begins with “BankAccount”, I replace it with SumCash-Logic. Whenever I have any Entity and Version, I replace them with Entity_NA and Final respectively.
- Once this mapping happens I aggregate the mapped data to get the final SumCash-Logic value.
The next snapshot shows me tagging the logic account group to a location.
On running the import, observe that the Logic account data has come through as shown in the next snapshot.
Now the next step is to ignore the individual Bank Balance accounts, which is do with a simple rule and map only the logic account for Cash. After applying the new mappings, the data load workbench is as shown in the next snapshot.
In order to implement the movement to either Assets or Liabilities, I now create a SQL mapping as shown in the below snapshots.
The snapshot of the data load workbench after importing the data once again is as shown in the below snapshot. Observe that the data has move to the “Cash In Bank” account after FDMEE mappings
Now, let us check if the ‘Overdraft in Bank’ is working as expected. I update the file to make most of the data negative as shown in the next snapshot.
On running the import once again, observe that the data is now parked in ‘Overdraft in Bank’ which would be under Liabilities.
Hi Sibin,
ReplyDeleteGood One. Liked the approach
Good one..
ReplyDeleteExcellent! Still helpful 6 years later.
ReplyDeleteI followed everything that you had mentioned in the post. I have been able to successfully create the logic group, and it is also summing up the accounts as required. However, this summed up value is NOT getting exported to Target (FCCS) application, even though the Export Step shows Orange Fish. Have you encountered this before?
ReplyDeleteAny help is appreciated.