Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Sunday, 5 February 2017

Query To Find GL Balances

SELECT   gld.NAME, gb.actual_flag, gb.period_name, gcc.code_combination_id,
            gcc.segment1
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '-'
         || gcc.segment4 "DISTRIBUTION",
         gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4,
         SUM (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)
             ) "OPEN BAL",
         NVL (gb.period_net_dr, 0) "DEBIT", NVL (gb.period_net_cr,
                                                 0) "CREDIT",
         SUM (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)
             ) "NET MOVEMENT",
           SUM ((NVL (gb.period_net_dr, 0) + NVL (gb.begin_balance_dr, 0))
               )
         - SUM (NVL (gb.period_net_cr, 0) + NVL (gb.begin_balance_cr, 0))
                                                                  "CLOSE BAL",
         gb.translated_flag, gb.template_id
    FROM gl_balances gb,
         gl_code_combinations gcc,
         gl_ledgers gld
   WHERE gcc.code_combination_id = gb.code_combination_id
     AND gb.actual_flag = 'A'
     AND gb.template_id IS NULL
     AND gb.ledger_id = gld.ledger_id
     AND gb.period_name = 'APR-05'
GROUP BY gld.NAME,
         gb.actual_flag,
         gb.period_name,
         gcc.code_combination_id,
            gcc.segment1
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '-'
         || gcc.segment4,
         gcc.segment1,
         gcc.segment2,
         gcc.segment3,
         gcc.segment4,
         NVL (gb.period_net_dr, 0),
         NVL (gb.period_net_cr, 0),
         gb.translated_flag,
         gb.template_id
  HAVING   SUM ((NVL (gb.period_net_dr, 0) + NVL (gb.begin_balance_dr, 0)))

         - SUM (NVL (gb.period_net_cr, 0) + NVL (gb.begin_balance_cr, 0)) <> 0;
Share this article :

4 comments:

  1. QUERY RESULT IS BLANK IN 12.1.3

    ReplyDelete
    Replies
    1. Some hard coded values are there ,remove those and it will work

      Delete
  2. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.

    ReplyDelete
  3. I wish to show thanks to you just for bailing me out of this particular trouble. As a result of checking through the net and meeting techniques that were not productive, Same as your blog I found another one Oracle Cloud Applications .Actually I was looking for the same information on internet for Oracle Cloud Applications Consultant and came across your blog. I am impressed by the information that you have on this blog. Thanks once more for all the details.

    ReplyDelete