nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Which Oracle Fusion Cloud GL tables do I join to get YTD nominal balances?

Lynda G

Trying to build an opex report but only getting the current period values not year to date. Do I have to add multiple rows of sql into the data model to add all the periods together? Is there a field from a table that will give me YTD when I select a period?

Find more posts tagged with

Reporting

SQL

OTBI

Accepted answers

Darwin HP

Hello, Have you try with the GL_BALANCES table?

https://docs.oracle.com/en/cloud/saas/financials/24d/oedmf/glbalances-23859.html#Foreign-Keys

Nathan CCC

Hi Lynda,

IMHO I do not agree your only option is a report in otbi with physical sql from the application database FSCM.

"limited to 1 subject area" not true. You can query as many subject areas as you like. On the analysis editor on criteria tab you have a button to add subject areas. In the column formulas the additional subject area is added as a prefix to the presentation table and column. Alternative you can write your own logical sql with multiple sql area which is my preferred method as I get to control what columns are used in the joins and the only method if you need anything other than inner joins. On t'internet you will find sheets people have built with formulas to help you generate that sort of thing.

2. "no separate segments". not true. If your application implementation consultant has implemented your chart of accounts correctly then you will get in OTBI in addition to the concatenated segments column you will get all the other segments too as their own folders with a column for just that segment value and description. The balancing segment, the natural account segment and any custom segments from key flexible fields etc. The AIC must have run the biextender scheduled process to import the flexible field columns into the subject areas in the metadata repository database in OTBI.

3. "cant use analysis because i need CSV" not true. In a data model in a report in OTBI yes you can query a subject area by create a data set of either 1) type SQL with data source Oracle BI EE (ask the bi admin to grant this data source to a role your consumers have like BI Consumer Role) or 2) type analysis with a path to your analysis.

4. The subject area for "general ledger balances cubes" is not for internal processing only. The FRS reports on balances cube are also a valid option as this is designed exactly for this use case to get YTD. But yes you should maybe not use any of the underlying "cube" tables and views direct in physical SQL if you have a concern they may change in a future release or something. But it is ok to use the subject area or FRS.

All comments

Darwin HP

Hello, Have you try with the GL_BALANCES table?

https://docs.oracle.com/en/cloud/saas/financials/24d/oedmf/glbalances-23859.html#Foreign-Keys

Lynda G

@Darwin HP - Thanks, I will try this.

Nathan CCC

Hi Lynda,

The "to date" data is NOT stored in transaction database table in GL_BALANCES.

GL_BALANCES https://docs.oracle.com/en/cloud/saas/financials/24a/oedmf/glbalances-22676.html#Details table has the begin balance and period activity debit and credit as a "transaction" balance for a balance type, encumbrance type, period for an account code combination for a currency for a ledger. The end balance is a in row calculation of begin pus activity. But there are no calculations as denormal columns as aggregations across rows for for "to date" or "ago". To get this transaction balance data query subject area https://docs.oracle.com/en/cloud/saas/financials/24d/faofb/General-Ledger--Transactional-Balances-Real-Time-SA-18.html

However if your application implementation consultant has done the configuration as per the implementation guide then you may also have a "General Ledger Balances Cubes" which is multi-dimensional including time dimension to give you pre-calculated aggregates by year etc. for faster performance. This hyperion essbase database cube is refreshed from the data in the gl balances transaction table. To get this balance data cube data query subject area https://docs.oracle.com/en/cloud/saas/financials/24d/faofb/General-Ledger--Balances-Real-Time-SA-14.html You can view log from issue sql to see what vies may exist in physical sql on the cubes.

Or alternatively you can use Financial Reporting Studio to build financial reports directly using the cubes which are very good at "to date" financial statement type reports on your general ledger balances.

For more information see

Oracle Fusion Cloud Financials
Implementing Enterprise Structures and General Ledger G12433-03 24D

https://docs.oracle.com/en/cloud/saas/financials/24d/faigl/overview-of-oracle-general-ledger-balances-cubes.html

Lynda G

@Nathan CCC - thanks for your response. The issue with building a report from a subject area is that you are limited to 1 subject area. I have tried this but unable to get the segments into separate columns as I only want the report to show 5 of the segments but the subject area displays all segments as concatenated. I also want the description for the entity and nominal fields.

I can't use an analysis report (which could use more than 1 subject area) as I need to be able to call the report as a csv ready file through an interface into Workiva.

I also can't use reporting studio for this purpose as it is not csv format for interfacing.

The only option is to build a report off a data model and so far I get everything I need apart from the values being YTD.

I have looked up the literature on the general ledger balances cubes table but it says it is for internal processing only so not sure how I would be able to join this into my SQL to benefit from it.

Thanks for the options. I will try to incorporate a join to the GL_BALANCES table and built a formula to aggregate the opening balances to periodic movements somehow to get the YTD.

Nathan CCC

Hi Lynda,

IMHO I do not agree your only option is a report in otbi with physical sql from the application database FSCM.

"limited to 1 subject area" not true. You can query as many subject areas as you like. On the analysis editor on criteria tab you have a button to add subject areas. In the column formulas the additional subject area is added as a prefix to the presentation table and column. Alternative you can write your own logical sql with multiple sql area which is my preferred method as I get to control what columns are used in the joins and the only method if you need anything other than inner joins. On t'internet you will find sheets people have built with formulas to help you generate that sort of thing.

Lynda G

Hi @Nathan CCC

Re point 2. Thanks for this. It appears we have never run the BI Extender job and now that I have run it in TEST I am able to separate out the segments I need from the subject area General Ledger - Transactional Balances Real Time. I will play around with the other subject areas as I think this will work.

Thanks

Lynda G

https://community.oracle.com/products/oracleanalytics/discussion/comment/60700#Comment_60700

This worked. Thanks