Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Accepted answer
33
Views
7
Comments
Lynda G
Lynda G Rank 2 - Community Beginner

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?

Tagged:

Best Answers

  • Darwin HP
    Darwin HP Rank 2 - Community Beginner
    Answer ✓

    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
    Nathan CCC Rank 7 - Analytics Coach
    Answer ✓

    Hi Lynda,

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

    1. "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.

Answers

  • Lynda G
    Lynda G Rank 2 - Community Beginner

    @Darwin HP - Thanks, I will try this.

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach
    edited November 2024

    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
    Lynda G Rank 2 - Community Beginner

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

  • Lynda G
    Lynda G Rank 2 - Community Beginner

    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