Monday 2 January 2017

Trial Balance – Detail Report

Description
Oracle General Ledger Trial Balance report is used to satisfy legal, statutory, and internal management reporting requirements in Portugal. There are five separate reports with a standard layout; however, each report has different parameters. The reports display the current period, up to the end of the previous period, year-to-date and current balance.

SELECT   glcc.segment4 natural_account, ffv.description description,
            glcc.segment1
         || '-'
         || glcc.segment2
         || '-'
         || glcc.segment3
         || '-'
         || glcc.segment4
         || '-'
         || glcc.segment5
         || '-'
         || glcc.segment6 accounting_flexfield,
           NVL (begin_balance_dr, 0)
         - NVL (begin_balance_cr, 0) beginning_balance,
         NVL (period_net_dr, 0) - NVL (period_net_cr, 0) period_activity,
           NVL (period_net_dr, 0)
         - NVL (period_net_cr, 0)
         + NVL (begin_balance_dr, 0)
         - NVL (begin_balance_cr, 0) ending_balance
    FROM gl_balances GLB,
         gl_code_combinations glcc,
         fnd_flex_values_vl ffv,
         fnd_flex_value_sets ffvs
   WHERE GLB.actual_flag = 'A'
     AND GLB.ledger_id = :sob
     AND GLB.currency_code = :currency_code
     AND (GLB.translated_flag != 'R' OR GLB.translated_flag IS NULL)
     AND GLB.period_name = :period_name
     AND GLB.code_combination_id = glcc.code_combination_id
     AND glcc.chart_of_accounts_id = (SELECT chart_of_accounts_id
                                        FROM gl_sets_of_books
                                       WHERE set_of_books_id = :sob)
     AND glcc.template_id IS NULL
     AND (  NVL (begin_balance_dr, 0)
          - NVL (begin_balance_cr, 0)
          + NVL (period_net_dr, 0)
          - NVL (period_net_cr, 0)
          + NVL (period_net_dr, 0)
          - NVL (period_net_cr, 0)
          + NVL (begin_balance_dr, 0)
          - NVL (begin_balance_cr, 0)
         ) != 0
     AND ffv.flex_value_set_id = ffvs.flex_value_set_id
     AND ffvs.flex_value_set_name = 'MPP_ACCOUNT'
     AND ffv.flex_value = glcc.segment4
ORDER BY 1, 3;

 By
Deepak J

No comments:

Post a Comment