BizTech_Blog_Header

BizTech Blog

Why OBIA Financial Statements Cannot be extended through ETL Enhancements

Posted by Stephen Goldsmith

Find me on:

Sep 17, 2012 10:01:00 AM

Why it’s not possible to modify a P&L statement just through the OBIA csv files?

We recently ran into an issue that most clients who use OBIA Financials encounter. We want to show two revenue line items on the BI apps prebuilt P&L dashboard (for example: Line of business #1 Revenue and Line of business #2 Revenue). The account group names from file_group_acct_names partially determine what shows up on the balance sheet and income statement.

This is because the RPD decodes the group account number field to determine which is a revenue line, COGS line, etc. So in order to get a new revenue line on the P&L, we need to add a revenue line into the csv files: file_group_acct_names.csv and file_group_acct_codes_ora.csv files. However, we also need to add new RPD calculations in the business model, presentation layer, and P&L reports for the new line items. There are a few limitations, because the document (below) states that you cannot change the following account group names: AP, AR, COGS, REVENUE, TAX, OTHERS, however, it is possible to modify the group account codes, and not touch the group account names.

http://docs.oracle.com/cd/E20490_01/bia.7963/e19039/anyimp_oracle_apps.htm#CIHCFFIF

OBIA screenshot showing one revenue, COGS, AP and AR lines (click image for larger, more legible version)

ISSUES WITH ADDING GROUP ACCOUNTS TO MODIFY THE BALANCE SHEET AND INCOME STATEMENT

1) A GL Account is set to REVENUE via that CSV file if its account range matches the REVENUE account range.

2) There are a few mappings (3 ), SDE_ORA_Stage_GLRevenueFact_GRFDerive, SDE_ORA_Stage_Reverse_GLRevenueFact_GRFDerive, SDE_ORA_GLRevenueFact_GroupAccountReverse that reference the group code (‘REVENUE’) explicitly in its “where“ conditions, however, this is the group account NAME, not the group account number

3) It is possible to add in new accounts, however, those accounts MUST have a group account name of REVENUE

4) The P&L reports consist of creating individual measures in the RPD, so any modifications to the group account numbers must be taken into account by creating new RPD measures, and adding those measures into the reports.

(click image for larger, more legible version)

And then pulling those measures into a pivot table, so adding a second revenue line would require an additional calculated field.

(click image for larger, more legible version)

Lamonte Bradley is doing a few blog entries on a quick way to extend the RPD to allow for customized line items here: http://www.biztech.com/blog/2012/09/how-to-extend-obia-financial-statements/

Blog author: Brian Makarewicz

Brian is a contributing blog author on the Business Intelligence Practice team at BizTech.

Topics: Oracle Business Intelligence