FCC - Doing Away with Separate Loads for Balance Sheet and Income Statement

TWO UPDATES - Sep 28

First, I have to apologize - I got the name of the variable wrong. It should be: NewLoadYTDClosingBalanceInput

Second, Oracle product management posted on Cloud Customer Connect yesterday that the feature isn't working correctly for profit and loss accounts and it will be addressed. No indication on timeline.

It's been a while since the last blog post so thought I'd do one on a new feature that's everyone has been wanting to see: loading BS and PL data together instead of separately. The funny (well, not funny) thing is that I missed the announcement - it was listed down in the Actions and Considerations section of the release notes instead of in the regular new features section. A friend pointed it out to me and I'm thankful they did.

The August release (23.08) introduced a change where you don't have to do separate loads for the balance sheet and the income statement in order for the system to reverse out the prior period closing balance. More specifically, the system would reverse the prior period's periodic closing balance and carried over the income statement from the prior year in the first period. Now, the system reverses the YTD closing balance from the prior period and does nothing for the income statement accounts in the first period.

I tried this out for myself and got the process to work. There are a couple details that aren't obvious in the current documentation, so I'm sharing them here. One is the next line and one is at the bottom.

First, there is a substitution variable that is required to enable the process: NewYTDClosingBalanceInput.

Next, set up the metadata so that income statement accounts have the FCCS_Mvmts_NetIncome member assigned as the default movement. The balance sheet accounts should already have the assignment. Note: it is recommended to keep FCCS_Mvmts_NetIncome as a level 0 member and not make it a parent. I thought about making it a parent with the non cash income statement movements as children as well as a regular income statement movement and was advised against it.

Next, in Data Exchange, populate the movement dimension for both balance sheet and income statement accounts with FCCS_ClosingBalance_Input. Here, I'm using a SQL statement to map multiple dimensions at once as it's faster than mapping dimensions separately, but either way works. Note: your movement dimension may not be UD7, so use the appropriate field. And yes, this is the new Data Exchange interface to which we should all be transitioning.

Now, when data is loaded and consolidated, the movement and reversals are properly calculated - top two rows have an asset and the bottom two rows have an expense.

I need to do further testing and verification but so far so good. 

The other not so obvious detail is that the movement member must be somewhere under FCCS_Mvmts_SubTotal. So if you also have statistical data to load, which normally goes to FCCS_No Movement, that will seemingly need to be a separate load.

I've already changed the design for two projects to use this feature. The time for the design change and the extra setup should easily be recouped in the quicker loads (one vs two).