General Ledger - EBS (MOSC)

MOSC Banner

11.5.9 SQL to generate a FSG report on a Daily Basis

Comments

  • user12047086
    user12047086 Posts: 749 Bronze Trophy
    Just schedule the FSG reports to run every day after close of all types postings for the day. Always report on PTD. Alternatively you can look at features like balance score card functionality which claim are supposed to provide a complete picture of financial status at the end of each day. I am assuming this includes P&L and B/S.

    But I cant really see how you can run FSG reports using SQL.

  • Lider Configuración
    Lider Configuración Posts: 20 Green Ribbon
    Just schedule the FSG reports to run every day after close of all types postings for the day. Always report on PTD. Alternatively you can look at features like balance score card functionality which claim are supposed to provide a complete picture of financial status at the end of each day. I am assuming this includes P&L and B/S.

    But I cant really see how you can run FSG reports using SQL.

    Many thanks for your promptly answer.

    The main issue was the company has a fired incident in July 2009, and the insurance company is asking to generate P&L at the mid of the month.

    Thanks for your help

    Regards

     

  • user12047086
    user12047086 Posts: 749 Bronze Trophy
    Many thanks for your promptly answer.

    The main issue was the company has a fired incident in July 2009, and the insurance company is asking to generate P&L at the mid of the month.

    Thanks for your help

    Regards

     

    Ok, so it is in the past. You cannot FSG reports and you cannot achieve this using simple SQL. As this is a one off, you can accomplish this by using a combination of scripts and manual reporting using excel or some such tool. Your script should do the following:

    for each code combination, query up the opening balances at the beginning of July 2009 - effectively trial balance of prior period. This data can be queried from GL balances table, or you can use one of the standard reports or FSG report

    Again, for each combination, sum up the total of accounted dr.s and  accounted cr.s in functional currency from the journal tables for the periods between 01-Jul-2009 and 15-Jul-2009 (assuming this is the reporting date). Make sure the journal line is posted and the effective date of the journal is in the desired date range.

    For each combination, the balance from 1, adjusted with the net dr/cr from 2 will give the closing dr. or cr. balance as on 15-Jul-2009. You now a have trial balance, from which you should be able to prepare P&L and B/S with some manual effort. If you wish you can also write script to generate those reports based on corresponding FSG report definition, its your choice.

  • Lider Configuración
    Lider Configuración Posts: 20 Green Ribbon
    Ok, so it is in the past. You cannot FSG reports and you cannot achieve this using simple SQL. As this is a one off, you can accomplish this by using a combination of scripts and manual reporting using excel or some such tool. Your script should do the following:

    for each code combination, query up the opening balances at the beginning of July 2009 - effectively trial balance of prior period. This data can be queried from GL balances table, or you can use one of the standard reports or FSG report

    Again, for each combination, sum up the total of accounted dr.s and  accounted cr.s in functional currency from the journal tables for the periods between 01-Jul-2009 and 15-Jul-2009 (assuming this is the reporting date). Make sure the journal line is posted and the effective date of the journal is in the desired date range.

    For each combination, the balance from 1, adjusted with the net dr/cr from 2 will give the closing dr. or cr. balance as on 15-Jul-2009. You now a have trial balance, from which you should be able to prepare P&L and B/S with some manual effort. If you wish you can also write script to generate those reports based on corresponding FSG report definition, its your choice.

    Hello

    Many thanks for your comments.

    Please close the thread

    Regards

     

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center