Within 11.1.2.1 FDM, users complete full financial statement loads using a workbook with multiple sheets. Unfortunately, upgrading to 11.1.2.4 FDMEE, the workbooks issue a multitude of errors after attempting the FDMEE Import Data step! Oracle’s answer is the workbook template functionality was fully supported and should work. However, they do not shed light on the problem. So after much research and trial and error, we found a way to make these excel template loads into FDMEE and fully tie out.
Excel Template Load:
Tabs numbered 1-6 are loading data, while the general tab is defining period, year, currency, and entity. This workbook is intertwined with formulas and look up tables. This general tab is very important for the rest of the book. I should also note this whole work book was protected and what FDMEE actually loads is hidden to the end user. As a result, the end user only sees the drop down menu’s and input tables, while the formulas push the data to the named ranges that FDMEE reads.
FDMEE reads all these work sheet data sets using the named ranges. Named ranges begin with “ups” to flag FDMEE that this is the data set to load in. The column headers within the named ranges are important because that is how FDMEE maps each dimension. Import format does not map the columns. The column headers within the named ranges does.
The dimension header and the mapped to dimension are outlined in the table below.
Below is the balance Sheet tab showing the named ranges for the work book as well as the column headers:
Fix #1
First of all, within this file there are many long formulas being read as values. Plus many ‘IF’ statements and ‘VLOOKUPs’ that give the ‘if false’ value to be equal to “ “. Formula results created the error within FDMEE stating there are no values for (X) period we loaded to. We did a Find/Replace for all ,””, to replace with ,”0”, to solve this. This changed the formulas that said if false <blank> and replaced with if false 0, making FMDEE read a value.
Fix #2
After that, set up a VLOOKUP to the general tab with the value header. The header changes from V to V:MM/DD/YYYY matching the period key from the period mapping within FDMEE. This header confirms we loaded the value to that month using the period key value within FDMEE period mappings.
Fix #3
Add a row between the header and the data set. Next, delete a row between the header and the top of the work sheet keeping the accounts in line. FDMEE only reads the file after the third row of data within the ‘ups’ named range. Name each range of data within all sheets of the workbook. Note that some sheets have multiple named ranges.
Fix #4
Finally, add a mapping script within FDMEE accounts to say that if amount is 0, then Ignore. That way we aren’t loading 0’s into the application.
This brief overview shows resolution of pesky migration errors for FDM to FDMEE excel templates. We hope this points you in the right direction should you encounter similar issues.
Questions? Comments? Feel free to reach us at:
Courtney Belden, cbelden@ecapitaladvisors.com
Chad Anderson, canderson@ecapitaladvisors.com
