Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 5 February 2017

Query to Find GL Budget Details Associated with Ledger

SELECT   period_name "PERIOD NAME", period_year "PERIOD YEAR",
         NAME "LEDGER NAME", short_name "SHORT NAME",
         description "LEDGER DESCRIPTION", segment1 "BALANCE SHEET",
         segment2 "COSTCENTER", segment3 "OBJECT CODE",
         segment4 "PROJECTCODE", currency_code "CURRENCY",
         SUM (adopted_budget) "ADOPTED BUDGET",
         SUM (operating_budget) "OPERATING BUDGET"
    FROM (SELECT gb.period_name, gb.period_year, gle.NAME, gle.short_name,
                 gle.description, gcc.segment1, gcc.segment2, gcc.segment3,
                 gcc.segment4, gb.currency_code,
                 DECODE (gb.actual_flag,
                         'B', NVL (begin_balance_dr, 0)
                          + NVL (gb.period_net_dr, 0)
                          - NVL (gb.begin_balance_cr, 0)
                          - NVL (gb.period_net_cr, 0),
                         0
                        ) adopted_budget,
                 NULL operating_budget
            FROM gl_balances gb,
                 gl_code_combinations gcc,
                 gl_ledgers gle,
                 gl_budget_versions gbv
           WHERE gb.period_name = 'NOV-13'                          --'DEC-04'
             AND gcc.segment1 = '0101'
             AND gcc.segment2 = '0202'
             AND gcc.segment3 = '53010'
             AND gcc.segment4 = '60006'
             AND gb.code_combination_id = gcc.code_combination_id
             AND gle.ledger_id = gb.ledger_id
             AND gb.actual_flag = 'B'
             AND gbv.budget_version_id = gb.budget_version_id
             AND gbv.budget_name = '2013 ADOPTED'
          UNION
          SELECT gb.period_name, gb.period_year, gle.NAME, gle.short_name,
                 gle.description, gcc.segment1, gcc.segment2, gcc.segment3,
                 gcc.segment4, gb.currency_code, NULL adopted_budget,
                 (DECODE (gb.actual_flag,
                          'B', NVL (begin_balance_dr, 0)
                           + NVL (gb.period_net_dr, 0)
                           - NVL (gb.begin_balance_cr, 0)
                           - NVL (gb.period_net_cr, 0),
                          0
                         )
                 ) operating_budget
--v2.08 changes
          FROM   gl_balances gb,
                 gl_code_combinations gcc,
                 gl_ledgers gle,
                 gl_budget_versions gbv
           WHERE gb.period_name = 'NOV-13'                          --'DEC-04'
             AND gcc.segment1 = '0101'
             AND gcc.segment2 = '0202'
             AND gcc.segment3 = '53010'
             AND gcc.segment4 = '60006'
             AND gb.code_combination_id = gcc.code_combination_id
             AND gle.ledger_id = gb.ledger_id
             AND gb.actual_flag = 'B'
             AND gbv.budget_version_id = gb.budget_version_id
             AND gbv.budget_name = '2013 OPERATING')
GROUP BY period_name,
         period_year,
         NAME,
         short_name,
         description,
         segment1,
         segment2,
         segment3,
         segment4,
         currency_code

ORDER BY period_year;
Share this article :

0 comments:

Post a Comment