Ever needed to import data from multiple columns from the same source file without processing the same source file multiple times? How do we solve this puzzle when we know that the expression DRCRSPLIT in Oracle Enterprise Performance Management Data Management does not work for a delimited file? The import format in Data Management does not allow adding the Amount column for multiple times. Hence, it is not possible to import data from two columns using the standard configurations.
We have a need to integrate a flat file with FCCS via Data Management. However, the file contains the closing debit balance and the closing credit balance in different columns. The requirement is to import data by processing the source file only once.
Using the native Oracle EPM toolset, it’s possible to import data from multiple columns from the same source file.
The solution would involve:
- A Lookup dimension in Target Application
- Evaluation order change in the Target Application
- Debit column is mapped to Amount field in Import Format
- Credit column is mapped to Lookup field in Import Format
- Data mapping script for the Lookup dimension
It is important to use NZP for the Amount column. This approach will also bring 0 in Data Management.
AMOUNT = NVL(AMOUNT,0) - NVL(TO_NUMBER(UD10),0)
The import will import the Debit balances in the Amount field and will import the Credit balances in the Lookup dimension field. The script then will apply Debit – Credit logic and will import credit as negative balances. Don’t forget to flip the sign in the account dimension for the credit balances.