4 Replies Latest reply on Sep 13, 2013 8:58 AM by raghu_ram

    GL Balances - SQL Query Help

    user612037

      Hello All,

       

      I am trying to build a query which will give a monthly bucket for GL Balance which are due.I have built the below query which has the basic information but i am trying to rollup GL Periods which i am not able to find any link also. Could any one please help me in deriving the monthly bucket using GL Periods  or is there any other way for this.

       

      SELECT gb.code_combination_id,

             gb.currency_code,

             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",

             gcck.concatenated_segments ,

             flvt.description    account_type,

             gcck.segment2 department

      FROM apps.gl_balances gb,

           apps.gl_code_combinations_kfv gcck,

           apps.fnd_flex_values_tl  flvt

      WHERE  gb.currency_code = 'INR'

      AND    gb.code_combination_id  = gcck.code_combination_id

      AND    gcck.segment3           = flvt.flex_value_meaning

      AND   ((nvl(gcck.segment3,'') >= '5000' AND  nvl(gcck.segment3,'') <= '7000'))

      GROUP BY gb.code_combination_id,

              gb.currency_code,

              gcck.concatenated_segments,

              flvt.description, 

              gcck.segment2

       

       

      My output should look like this

       

      Department      Account Type   Concatinated Segment    Closing Bal            JAN    FEB   MAR  APR  MAY JUN JUL  AUG SEP OCT NOV DEC

       

       

       

       

      Please help me on this ....Need to get this done ASAP.

        • 1. Re: GL Balances - SQL Query Help
          Octopus Rex

          Hi,

           

          Add PERIOD_NAME column from GL_BALANCES table and group for it also.

           

          Octavio


          • 2. Re: GL Balances - SQL Query Help
            user612037

            It is not working it is getting duplications ,somebody pls help on the above query

            • 3. Re: GL Balances - SQL Query Help
              Octopus Rex

              Hi,

               

              Use the PIVOT function.

               

              Octavio

              • 4. Re: GL Balances - SQL Query Help
                raghu_ram

                Hi,

                check if this can be useful

                 

                select period_name,segment2,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

                from

                (select gp.period_name,gcck.segment2 ,lag(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)))

                over (partition by gp.period_name order by gp.period_name) Jan,

                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)) Feb,

                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))Mar,

                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)) Apr,

                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)) May,

                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)) Jun,

                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))Jul,

                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))Aug,

                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)) Sep,

                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)) Oct,

                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)) Nov,

                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))DEc

                from gl_periods gp,gl_balances gb,gl_code_combinations_kfv gcck

                where gp.period_name = gb.period_name

                and gb.code_combination_id  = gcck.code_combination_id

                and gp.period_set_name = 'Accounting'

                and gp.period_year = '2010'

                and gp.period_type = 'Month'

                group by gp.period_name,gcck.segment2 )

                order by period_name

                 

                 

                thanks & regards

                raghu