Thursday, March 14, 2024

FCC Constant Currency

An occasional requirement for an FCC implementation is to translate data at multiple currency rates. There's always actuals at the actual rates, but then you can translate actuals at last year's rate, actuals at the forecast rate, actuals at next year's budget rate, etc. These different translations provide the data to show whether operations performed well against the comparative data or did the exchange rate just work in their favor.

There are a couple of different ways to set this up. The first question is whether to use a scenario or a custom dimension to hold the translation versions. If the requirement includes the balance sheet, is used occasionally, or the customs are already used, then one or more scenarios is the recommended direction. In the case I'm showing here none of those apply, so this example will use a custom dimension. The requirement is for income statement only, the output is used extensively during close and subsequent reporting, and a custom dimension is available.

After creating additional members in the custom dimension to hold the results, the next step is to create a custom translation rule (as shown below). The top half of the rule shows where to source the exchange rates. In this case, the rates are sourced from scenario Actual for the prior year. The "current period" is specified, so Dec 23 actuals will translate at the Dec 22 exchange rates, Nov 23 at Nov 22, etc. The second half of the screen controls the source and destination members. The left side shows the source data: no constant rate (custom), descendant accounts of net income, and only applies to the actual scenario. On the right hand side is any redirection (target) members. In this case, the results are stored in LastYrRate (so No ConstantRate is translated and then stored in LastYrRate). The account and scenario dimensions do not need redirection, so those fields are left blank so that those members simply pass through.


Once the rule is created it just needs to be deployed to be in effect.

So that will take care of the translations. But if the analysis will include operations from the total company's currency, what to do there? Example: if total company is in US Dollars and there are US Dollar entities that roll up to the total with only US Dollar parents, then there is no translation. As there is no translation, the above translation rule will not include that data. This can be addressed by adding an insertion rule to simply copy the data over from No ConstantRate to LastYrRate. Note that the FIX statement should specify level 0 members of the intercompany and other custom dimensions.

One note on the @ISUDA function used. IsUSD is a UDA that is applied to the level 0 entities that have the "home" currency. Apparently there is a function that can read the entity currency directly but it isn't visible as of now in Calculation Manager. Oracle has filed an internal SR to expose the function which will remove the requirement to tag the entities with a separate UDA like this.




The custom translation and insertion rule will run with each consolidation. After consolidating, the users can simply switch their POV in reports, Smart View, etc. between the custom members No ConstantRate (for actual numbers) and LastYrRate (for actual numbers at last year's rates).



Wednesday, January 17, 2024

Data Exchange - DRCRSplit for Delimited Files

Recently had to solve a problem during an implementation and as I've done before, I'm adding the solution here so I can find it again.

An incoming trial balance file is delimited and has two columns for the amount: a debit column and a credit column. Each record will have a value in one of these but not both. If the file was fixed format instead of delimited, there is a built-in import format expression called DRCRSplit that will deal with the two columns. With the function, you specify the midpoint of the overall character count but with delimited files you don't have a consistent midpoint character count, so it doesn't work with delimited files.

Like maybe some of you reading this, I did a Google search on this topic and found multiple Oracle forum posts, CloudCustomerConnect posts, etc. asking the same question over several years but didn't find an easy solution. There was a posted answer that combined the DRCRSplit function with the Column function, but as pointed out by someone else that solution causes data problems. So, below is what I did. There may be a more elegant way of solving this problem, but the solution worked for me.

I mapped the debit and credit balance columns to separate attribute fields and for the regular amount field, I mapped the account field to it. Each record needs a numeric amount; otherwise, it will get dropped on the import. I'm thinking the year could be used as the placeholder amount if it were available on each record. I'm guessing that one of the amount fields would probably work in conjunction with the no zero suppress expression.

[Editor note: if the right sidebar is covering part of the picture, click it to see the full detail]


Next, I used a SQL map on the entity to (a) pass the entity through and (b) to populate the amount. Below, the entity portion isn't shown. The AMOUNTX field (the target amount) is populated from the debit column if it is not equal to zero and from the credit column if the debit is equal to zero. Anything placed in an attribute field is stored as a string, so the TO_NUMBER function converts the string back to a number. If there was a need to deal with quotes, spaces, commas, etc. in the number as thousands separators or decimals, that could be addressed with either a format on TO_NUMBER or using one of the REGEXP functions.





I attached this SQL to the entity dimension mapping. If the entity dimension needs more mapping and the SQL gets in the way of using the other mapping types (explicits, etc.) then attach the SQL to another dimension, like Data Source. Any of them will work, so the SQL should be placed somewhere inconspicuous.








Saturday, October 7, 2023

Cloud Customer Connect - the Importance of Upvoting Ideas

The #1 best place to go for Oracle Cloud EPM info (yes, better than this blog) is Cloud Customer Connect. It is part community forum, Oracle training, announcements and guidance from Oracle product managers, and the ONE way to submit enhancements or customer requests for new features, etc. This last component is the focus for this post.

Idea Lab is where everyone can go in and submit an enhancement request and explain why, the business case, etc. Others can review and upvote to indicate their interest in the same thing or downvote if they don't see the need. When I say "others can review," that includes Oracle product managers, who review each idea and, once a direction has been decided by Oracle, will tag for future inclusion, deny, or say the idea is already in the product.

The upvoting portion of the process is the most important for the community and is probably the least underutilized portion of the process. This is the mechanism for the community to review and indicate their interest in the ideas. Ideas that receive a lot of upvotes are considered more seriously by Oracle as they have more demonstrated appeal.

Here's an example. In a navigation flow, you can create a new card to Narrative Reporting report, package, snapshot, or book (see below). All that is great. But what would be much more useful would be to create a card that links to a Narrative Reporting folder. That way, for instance, with one click on a card in FCC, users would be able to access all of the FCC reports. With the current ability, the admin would have to create a card for each one. If there are 50, then that would be 50 cards. Add a new report, add a new card. This does not make sense!



In Idea Lab, there is an idea to add a folder to the above list. Here's the link (login with free Cloud Customer Connect account required):

https://community.oracle.com/customerconnect/discussion/652748/add-narrative-reporting-folder-to-navigation-flow

Down near the bottom is the place to upvote/downvote. To do so, click the "thumbs up" or "thumbs down" icons. Maybe this navigation, which isn't exactly obvious, is the reason there isn't much up/down voting. Once the choice is clicked, it will fill in (here, I have already upvoted this idea). Users can also add comments to explain their support, add additional business cases, add workarounds, etc.



I think that upvoting ideas is highly underutilized by the community. By upvoting ideas that make sense to them, users can add their influence to the idea which will increase the appeal of the idea to Oracle.

So, get out and vote! 





Thursday, September 7, 2023

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).



Thursday, June 1, 2023

Audit Log - Showing Internal References

 Just a heads up on an issue with the audit log. When a member is moved from one parent to another, the audit log captures that and shows the "from member" and "to member." Only, it doesn't. What shows is an internal reference, which isn't helpful in determining the member labels that are seen in the application.

Apparently this is a platform bug, so not just related to FCC. It's also not always been this way, so hopefully it gets fixed soon.




Monday, March 6, 2023

FCC Release 23.03 - Metadata Validation for Movement Members

In the 23.03 release of FCC there is a new metadata validation that requires that all members under FCCS_ClosingBalance have a consolidation operator of Addition. On the surface this makes sense; however, it will cause major issues with cash flow.

It is common for cash flow to require various types of manual adjustments and calculations that are not part of the closing balance calculation. But, if a movement member is not under FCCS_ClosingBalance, then the data does not consolidate.

So what I and many others have done is to put these members somewhere under FCCS_ClosingBalance with a consolidation operator of Ignore. That way the data consolidates and does not impact the closing balance.

With the 23.03 release, when a metadata validation is run, this happens:


I filed an SR for the company I'm currently working with for this issue. Unless Oracle removes the restriction that members must be under FCCS_ClosingBalance to consolidate, I expect that this validation will get pulled back.

Monday, February 27, 2023

Oracle EPM: Hierarchy Calculations

Most implementations have some statistical calculations, particularly with accounts. There are multiple levels of calculation complexity. Member formulas is usually the go-to place to write these; however, for simple calculations, there's another option. And it has a benefit that member formulas don't have!

Create an alternate hierarchy with the parent member representing the result and the children representing the inputs. Then use the consolidation operator to add/subtract/multiply/divide or calculate a percentage. In the below example, FCCS_Gross Profit is being divided by FCCS_Gross Sales to calculate a gross margin percentage. And with the percentage option there is no need to multiply the results by 100.

If the calculation needs parentheses, then create a mid level parent to represent that part of the formula.

Now, the benefit. A user can view the result on a report, data grid, etc. and then drill down into it to see the components. The result member is just a parent member, after all. So that's helpful.