Mysteries of the Financials Group Account Mapping Files

One of the more befuddling aspects of configuring the Financials Analytics Application for EBS is the mysterious workings of the Group Account / Financial Statement Item mapping files. Existing documentation is clear enough on how to modify them but touches only partially on the relationship bewteen these files and the overall system.

In this posting I'd like to a) add a fresh perspective to the functionality and configuration of these files and b) propose a change to what I consider a flaw in related ETL logic.

First, let me clarify exactly which files I am discussing and their content:

file_group_acct_codes_ora.csv - maps Natural Accounts to a Group Account Number (GROUP_ACCT_NUM)

file_group_acct_names.csv - provides a user-friendly name (GROUP_ACCOUNT_NAME) for each Group Account Number

file_grpact_fstmt.csv - does two things: a) categorizes Group Account Numbers according to the corresponding Subledger (AP, AR, COGS, REVENUE, TAX, OTHER) and b) further designates the account as either a Balance Sheet (BS) or P&L (PL) account

Thus in combination, these files enable a de-facto account categorization hierarchy like so:

Natural Account -> Group Account -> Financial Statement Item -> GL Account Category

The file file_group_acct_codes_ora.csv defines the first level of this hierarchy by grouping the accounts (actually, ranges of accounts) into GAAP-like categories called "Group Accounts," which have corresponding metrics pre-built in the Business Model layer. For the best examples of these metrics see the "Fact - Fins - GL Balance" fact table. The functionality enabled by this level of the categoriztion is very straightforward and if nothing else demonstrates the flexibility of the Business Model & Mapping Layer and a viable method of account aggregation for financial reporting (notwithstanding its "so-twentieth-century" reliance on a CSV file of all things to provide that categorization). However, even though the "seeded" Group Accounts provided out-of-the-box seem reasonable enough, I have yet to implement them "as is" for production usage without some degree of customization.

The second and third level of the hierarchy are simultaneously defined in the file "file_grpact_fstmt.csv". In this file, each Group Account is assigned both a Financial Statement Item code (FIN_STMT_ITEM_CODE), whose values correspond to the subledgers (AP, AR, COGS, REVN, TAX, OTHER) -- and a GL Account Category, whose values are either "BS" (for Balance Sheet) or "PL" (for Profit & Loss). Both attributes find their way into the Presentation Layer as dimensions against which the end user can create Answers Requests, so this categorization also provides some useful reporting functionality to the end user, again notwithstanding the CSV method.

But here's the twist: the "Financial Statement Item" level of the heirarchy also plays a role in the ETL itself -- and this role is exactly where I find fault with the functionality of this categorization.

To understand this role let's start with Oracle's explanation in their configuration documentation, which as far as I can tell is the sum total of all documentation they provide on the subject:

"Financial Statement Item codes are internal codes used by the ETL process to processthe GL journal records during the GL reconciliation process against the subledgers.When the ETL process reconciles a GL journal record, it looks at the FinancialStatement Item code associated with the GL account that the journal is chargingagainst, and then uses the value of the Financial Statement item code to decide whichbase fact the GL journal should reconcile against. For example, when processing a GLjournal that charges to a GL account which is associate to 'AP' Financial StatementItem code, then the ETL process will try to go against AP base fact table (W_AP_XACT_F), and try to locate the corresponding matching AP accounting entry. If thatGL account is associated with the 'REVENUE' Financial Statement Item code, then theETL program will try to go against the Revenue base fact table (W_GL_REVN_F), andtry to locate the corresponding matching Revenue accounting entry."

Somewhat enlightening, but let's dig into Informatica to get to the meat of the matter. The reconciliation process (a SILOS mapping named "SIL_ARTransactionFact_Gl_Info_Update") determines the final "Status" of the transaction fact -- in other words, a transaction is considered "Open" until it can be reconciled. According to the logic in the SILOS mapping, if an account is a member of a Group Account whose FIN_STMT_ITEM_CODE = 'AR', the reconciliation process will only reconcile transactions posted to this account that have been extracted to the W_AR_XACT_F table. Any transactions NOT extracted to W_AR_XACT_F will NOT be reconciled, and their Status will remain "Open" and, more importantly, will not be included in the W_AR_BALANCE_F table.

For this reason it is imperative to configure the seed files accurately, particularly making sure that every natural account is associated to one and only one group account code in file_group_acct_codes_ora.csv -- else of course the balance fact tables will be empty or, worse, corrupt.

However, I find this particular reconciliation behavior troubling. Consider another way of looking at the same logic using EBS as an example: Given that the Receivables module is the source of all data in the W_AR_XACT_F table, any activity posted via the Receivables module to an account that is anything BUT 'AR' will likewise NOT be reconciled -- and therefore never considered "Closed" and never included in the balance fact tables.

Here's my problem with this logic: In practice it is not unreasonable for a company to post transactions in a Receivables application against accounts that are not categorized as "AR." More bluntly: EBS does not specifically restrict the Receivables module from posting non-Receivables accounts! Therefore this reconciliation logic is inherently flawed.

To resolve this issue, I wonder what would be the repurcussions of simply disabling the FIN_STMT_ITEM_CODE restriction in the ETL? For a specific example, refer to mapping "SDE_ORA_Stage_ARTransactionFact_GRFDerive" (which populates the staging table used by the AR reconciliation process). What I am proposing is to modify source qualifier "SQ_TI_STAGE_ARGLRF", disabling the clause "W_ORA_GLRF_F_TMP.FIN_STMT_ITEM_CODE='AR'":

SELECT
W_AR_XACT_F.DOC_STATUS_WID ...
FROM
W_ORA_GLRF_F_TMP, W_AR_XACT_F, W_GL_ACCOUNT_D
WHERE W_ORA_GLRF_F_TMP.JE_SOURCE='Receivables' AND ... W_ORA_GLRF_F_TMP.FIN_STMT_ITEM_CODE='AR'

However, even if this adjustment does solve the problem, it begs the question as to why this logic was incorporated in the first place? Could it simply be some evolutionary artifact from its Siebel ancestry that was never really addressed when porting to EBS? Moreover, why not simply use the posting status from the source data instead of relying on the reconciliation to set the status?

Ultimately the answer lies in applying the change and thoroughly validating the result, but I expect that others have encountered the same difficulty and am curious to hear your feedback.

Has this behavior become an issue in any of your projects? How have you tackled it?