Date Wise Reconciliation of Subledger and GL PLSQL Query - Oracle EBS R12

https://generalledger-gl.blogspot.com/2017/10/date-wise-reconciliation-of-subledger.html

PLSQL Query to Get Date Wise Reconciliation of Subledger and GL 

Subledger

select gcc.SEGMENT4,
     
       --H.JE_CATEGORY,H.JE_SOURCE,
     
       --h.je_header_id, h.name,
     
       L.ACCOUNTING_DATE,
     
       ----------------------------------------
     
       (select
       
        --hh.JE_CATEGORY,hh.JE_SOURCE,
       
        --hh.je_header_id, hh.name,
       
        --LL.ENTERED_DR, LL.ENTERED_CR, (nvl(LL.ENTERED_DR,0) - nvl(LL.ENTERED_CR,0)) BAL
       
         SUM(nvl(LL.ACCOUNTED_DR, 0) - nvl(LL.ACCOUNTED_CR, 0))
       
          from xla_ae_headers hh, xla_ae_lines ll, gl_code_combinations gccc --, gl_import_references v
       
         where 1 = 1
             
           and hh.AE_HEADER_ID = LL.AE_HEADER_ID
             
           and LL.code_combination_id = gccc.code_combination_id
             
              -- and v.GL_SL_LINK_ID = ll.GL_SL_LINK_ID
             
           and hh.LEDGER_ID = ll.LEDGER_ID
             
           and gccc.SEGMENT4 = gcc.SEGMENT4
             
              --and hh.default_effective_date<='31-OCT-15'
             
              --and hh.period_name='OCT-15'
             
           AND trunc(LL.ACCOUNTING_DATE) < trunc(L.ACCOUNTING_DATE)
             
              --and hh.je_category='Purchase Invoices'
             
           and gccc.segment4 = &ACCOUNT
             
              --AND hh.CURRENCY_CODE='PKR'
             
           AND hh.LEDGER_ID = &LEDGER
       
        --  and hh.STATUS = 'P'
       
        --  and ll.STATUS = 'P'
       
        --  and hh.Actual_flag = 'A'
       
        --AND hh.JE_SOURCE='Payables'
       
        --AND hh.JE_CATEGORY='Purchase Invoices'
       
        --GROUP BY LL.EFFECTIVE_DATE
       
        --group by ll.EFFECTIVE_DATE
       
        ) OPENING_BALANCE_XLA,
     
       (select
       
        --hh.JE_CATEGORY,hh.JE_SOURCE,
       
        --hh.je_header_id, hh.name,
       
        --LL.ENTERED_DR, LL.ENTERED_CR, (nvl(LL.ENTERED_DR,0) - nvl(LL.ENTERED_CR,0)) BAL
       
         SUM(nvl(LL.ACCOUNTED_DR, 0) - nvl(LL.ACCOUNTED_CR, 0))
       
          from xla_ae_headers       hh,
               xla_ae_lines         ll,
               gl_code_combinations gccc,
               gl_import_references p
       
         where 1 = 1
             
           and hh.AE_HEADER_ID = LL.AE_HEADER_ID
             
           and LL.code_combination_id = gccc.code_combination_id
             
           and p.GL_SL_LINK_ID = ll.GL_SL_LINK_ID
             
              --and p.GL_SL_LINK_ID = v.GL_SL_LINK_ID
             
           and hh.LEDGER_ID = ll.LEDGER_ID
             
           and gccc.SEGMENT4 = gcc.SEGMENT4
             
              --and hh.default_effective_date<='31-OCT-15'
             
              --and hh.period_name='OCT-15'
             
           AND trunc(LL.ACCOUNTING_DATE) < trunc(L.ACCOUNTING_DATE)
             
              --and hh.je_category='Purchase Invoices'
             
           and gccc.segment4 = &ACCOUNT
             
              --AND hh.CURRENCY_CODE='PKR'
             
           AND hh.LEDGER_ID = &LEDGER
       
        --  and hh.STATUS = 'P'
       
        --  and ll.STATUS = 'P'
       
        --  and hh.Actual_flag = 'A'
       
        --AND hh.JE_SOURCE='Payables'
       
        --AND hh.JE_CATEGORY='Purchase Invoices'
       
        --GROUP BY LL.EFFECTIVE_DATE
       
        --group by ll.EFFECTIVE_DATE
       
        ) OPENING_BALANCE_IMPORT_REF,
     
       -----------------------------------------------
     
       --L.ENTERED_DR, L.ENTERED_CR, (nvl(L.ENTERED_DR,0) - nvl(L.ENTERED_CR,0)) BAL
     
       SUM(NVL(L.ACCOUNTED_DR, 0)) DATE_DR_XLA,
     
       (select
       
        --hh.JE_CATEGORY,hh.JE_SOURCE,
       
        --hh.je_header_id, hh.name,
       
        --LL.ENTERED_DR, LL.ENTERED_CR, (nvl(LL.ENTERED_DR,0) - nvl(LL.ENTERED_CR,0)) BAL
       
         SUM(NVL(LL.ACCOUNTED_DR, 0))
       
          from xla_ae_headers       hh,
               xla_ae_lines         ll,
               gl_code_combinations gccc,
               gl_import_references q
       
         where 1 = 1
             
           and hh.AE_HEADER_ID = LL.AE_HEADER_ID
             
           and LL.code_combination_id = gccc.code_combination_id
             
           and q.GL_SL_LINK_ID = ll.GL_SL_LINK_ID
             
              -- and q.GL_SL_LINK_ID=v.GL_SL_LINK_ID
             
           and hh.LEDGER_ID = ll.LEDGER_ID
             
           and gccc.SEGMENT4 = gcc.SEGMENT4
             
              --and hh.default_effective_date<='31-OCT-15'
             
              --and hh.period_name='OCT-15'
             
           AND trunc(LL.ACCOUNTING_DATE) = L.ACCOUNTING_DATE
             
              --and hh.je_category='Purchase Invoices'
             
           and gccc.segment4 = &ACCOUNT
             
              --AND hh.CURRENCY_CODE='PKR'
             
           AND hh.LEDGER_ID = &LEDGER
       
        --  and hh.STATUS = 'P'
       
        --  and ll.STATUS = 'P'
       
        --  and hh.Actual_flag = 'A'
       
        --AND hh.JE_SOURCE='Payables'
       
        --AND hh.JE_CATEGORY='Purchase Invoices'
       
        --GROUP BY LL.EFFECTIVE_DATE
       
        --group by ll.EFFECTIVE_DATE
       
        ) DATE_DR_IMPORT_REF,
     
       SUM(NVL(L.ACCOUNTED_CR, 0)) DATE_CR_XLA
     
      ,
       (select
       
        --hh.JE_CATEGORY,hh.JE_SOURCE,
       
        --hh.je_header_id, hh.name,
       
        --LL.ENTERED_DR, LL.ENTERED_CR, (nvl(LL.ENTERED_DR,0) - nvl(LL.ENTERED_CR,0)) BAL
       
         SUM(NVL(LL.ACCOUNTED_CR, 0))
       
          from xla_ae_headers       hh,
               xla_ae_lines         ll,
               gl_code_combinations gccc,
               gl_import_references j
       
         where 1 = 1
             
           and hh.AE_HEADER_ID = LL.AE_HEADER_ID
             
           and LL.code_combination_id = gccc.code_combination_id
             
           and j.GL_SL_LINK_ID = ll.GL_SL_LINK_ID
             
              --   and j.GL_SL_LINK_ID=v.GL_SL_LINK_ID
             
           and hh.LEDGER_ID = ll.LEDGER_ID
             
           and gccc.SEGMENT4 = gcc.SEGMENT4
             
              --and hh.default_effective_date<='31-OCT-15'
             
              --and hh.period_name='OCT-15'
             
           AND trunc(LL.ACCOUNTING_DATE) = L.ACCOUNTING_DATE
             
              --and hh.je_category='Purchase Invoices'
             
           and gccc.segment4 = &ACCOUNT
             
              --AND hh.CURRENCY_CODE='PKR'
             
           AND hh.LEDGER_ID = &LEDGER
       
        --  and hh.STATUS = 'P'
       
        --  and ll.STATUS = 'P'
       
        --  and hh.Actual_flag = 'A'
       
        --AND hh.JE_SOURCE='Payables'
       
        --AND hh.JE_CATEGORY='Purchase Invoices'
       
        --GROUP BY LL.EFFECTIVE_DATE
       
        --group by ll.EFFECTIVE_DATE
       
        ) DATE_CR_IMPORT_REF
     
       /*,SUM(nvl(L.ENTERED_DR,0) - nvl(L.ENTERED_CR,0)) BAL*/
     
      ,
     
       (select
       
        --hh.JE_CATEGORY,hh.JE_SOURCE,
       
        --hh.je_header_id, hh.name,
       
        --LL.ENTERED_DR, LL.ENTERED_CR, (nvl(LL.ENTERED_DR,0) - nvl(LL.ENTERED_CR,0)) BAL
       
         SUM(nvl(LL.ACCOUNTED_DR, 0) - nvl(LL.ACCOUNTED_CR, 0))
       
          from xla_ae_headers hh, xla_ae_lines ll, gl_code_combinations gccc
       
         where 1 = 1
             
           and hh.AE_HEADER_ID = LL.AE_HEADER_ID
             
           and LL.code_combination_id = gccc.code_combination_id
             
              --  and k.GL_SL_LINK_ID=v.GL_SL_LINK_ID
             
           and hh.LEDGER_ID = ll.LEDGER_ID
             
           and gccc.SEGMENT4 = gcc.SEGMENT4
             
              --and hh.default_effective_date<='31-OCT-15'
             
              --and hh.period_name='OCT-15'
             
           AND trunc(LL.ACCOUNTING_DATE) < trunc(L.ACCOUNTING_DATE)
             
              --and hh.je_category='Purchase Invoices'
             
           and gccc.segment4 = &ACCOUNT
             
              --AND hh.CURRENCY_CODE='PKR'
             
           AND hh.LEDGER_ID = &LEDGER
       
        --  and hh.STATUS = 'P'
       
        --  and ll.STATUS = 'P'
       
        --  and hh.Actual_flag = 'A'
       
        --AND hh.JE_SOURCE='Payables'
       
        --AND hh.JE_CATEGORY='Purchase Invoices'
       
        --GROUP BY LL.EFFECTIVE_DATE
       
        --group by ll.EFFECTIVE_DATE
       
        ) + SUM(NVL(L.ACCOUNTED_DR, 0)) - SUM(NVL(L.ACCOUNTED_CR, 0)) ENDING_BALANCE_XLA
     
      ,
     
       (select
       
        --hh.JE_CATEGORY,hh.JE_SOURCE,
       
        --hh.je_header_id, hh.name,
       
        --LL.ENTERED_DR, LL.ENTERED_CR, (nvl(LL.ENTERED_DR,0) - nvl(LL.ENTERED_CR,0)) BAL
       
         SUM(nvl(LL.ACCOUNTED_DR, 0) - nvl(LL.ACCOUNTED_CR, 0))
       
          from xla_ae_headers       hh,
               xla_ae_lines         ll,
               gl_code_combinations gccc,
               gl_import_references k
       
         where 1 = 1
             
           and hh.AE_HEADER_ID = LL.AE_HEADER_ID
             
           and LL.code_combination_id = gccc.code_combination_id
             
           and k.GL_SL_LINK_ID = ll.GL_SL_LINK_ID
             
              --  and k.GL_SL_LINK_ID=v.GL_SL_LINK_ID
             
           and hh.LEDGER_ID = ll.LEDGER_ID
             
           and gccc.SEGMENT4 = gcc.SEGMENT4
             
              --and hh.default_effective_date<='31-OCT-15'
             
              --and hh.period_name='OCT-15'
             
           AND trunc(LL.ACCOUNTING_DATE) < trunc(L.ACCOUNTING_DATE)
             
              --and hh.je_category='Purchase Invoices'
             
           and gccc.segment4 = &ACCOUNT
             
              --AND hh.CURRENCY_CODE='PKR'
             
           AND hh.LEDGER_ID = &LEDGER
       
        --  and hh.STATUS = 'P'
       
        --  and ll.STATUS = 'P'
       
        --  and hh.Actual_flag = 'A'
       
        --AND hh.JE_SOURCE='Payables'
       
        --AND hh.JE_CATEGORY='Purchase Invoices'
       
        --GROUP BY LL.EFFECTIVE_DATE
       
        --group by ll.EFFECTIVE_DATE
       
        ) + SUM(NVL(L.ACCOUNTED_DR, 0)) - SUM(NVL(L.ACCOUNTED_CR, 0)) ENDING_BALANCE_IMPORT_REF

  from xla_ae_headers       h,
       xla_ae_lines         l,
       gl_code_combinations gcc,
       gl_import_references v

 where 1 = 1
     
   and h.AE_HEADER_ID = l.AE_HEADER_ID
     
   and l.code_combination_id = gcc.code_combination_id
     
   and v.GL_SL_LINK_ID = l.GL_SL_LINK_ID
     
      --and h.default_effective_date<='31-OCT-15'
     
      --and h.period_name='OCT-15'
     
   AND trunc(L.ACCOUNTING_DATE) BETWEEN &FROM_DATE AND &TO_DATE
     
      --and h.je_category='Purchase Invoices'
     
   and gcc.segment4 = &ACCOUNT
     
      --AND H.CURRENCY_CODE='PKR'
     
   AND H.LEDGER_ID = &LEDGER

--and H.STATUS = 'P'

-- and l.STATUS = 'P'

-- and H.Actual_flag = 'A'

--AND H.JE_SOURCE='Payables'

--AND H.JE_CATEGORY='Purchase Invoices'

 GROUP BY L.ACCOUNTING_DATE, gcc.SEGMENT4

--GROUP BY H.JE_CATEGORY,H.JE_SOURCE


 ORDER BY L.ACCOUNTING_DATE

#############################################################################

GL

select gcc.SEGMENT4,
     
       --H.JE_CATEGORY,H.JE_SOURCE,
     
       --h.je_header_id, h.name,
     
       L.EFFECTIVE_DATE,
     
       ----------------------------------------
     
       (select
       
        --hh.JE_CATEGORY,hh.JE_SOURCE,
       
        --hh.je_header_id, hh.name,
       
        --LL.ENTERED_DR, LL.ENTERED_CR, (nvl(LL.ENTERED_DR,0) - nvl(LL.ENTERED_CR,0)) BAL
       
         SUM(nvl(LL.ACCOUNTED_DR, 0) - nvl(LL.ACCOUNTED_CR, 0))
       
          from gl_je_headers hh, gl_je_lines ll, gl_code_combinations gccc
       
         where 1 = 1
             
           and hh.je_header_id = LL.je_header_id
             
           and LL.code_combination_id = gccc.code_combination_id
             
           and hh.LEDGER_ID = ll.LEDGER_ID
             
           and gccc.SEGMENT4 = gcc.SEGMENT4
             
              --and hh.default_effective_date<='31-OCT-15'
             
              --and hh.period_name='OCT-15'
             
           AND trunc(LL.EFFECTIVE_DATE) < trunc(L.EFFECTIVE_DATE)
             
              /*                    and hh.JE_CATEGORY not in ('1',
                             
                             
                             
              '3',
                             
                             
                             
                             
                             
              'Revaluation',
                             
              '2'
                             
              )*/
             
           and hh.JE_FROM_SLA_FLAG = 'Y'
             
              --and hh.je_category='Purchase Invoices'
             
           and gccc.segment4 = &ACCOUNT
             
              --AND hh.CURRENCY_CODE='PKR'
             
           AND hh.LEDGER_ID = &LEDGER
             
           and hh.STATUS = 'P'
             
           and ll.STATUS = 'P'
             
           and hh.Actual_flag = 'A'
       
        --AND hh.JE_SOURCE='Payables'
       
        --AND hh.JE_CATEGORY='Purchase Invoices'
       
        --GROUP BY LL.EFFECTIVE_DATE
       
        --group by ll.EFFECTIVE_DATE
       
        ) OPENING_BALANCE,
     
       -----------------------------------------------
     
       --L.ENTERED_DR, L.ENTERED_CR, (nvl(L.ENTERED_DR,0) - nvl(L.ENTERED_CR,0)) BAL
     
       SUM(NVL(L.ACCOUNTED_DR, 0)) DATE_DR,
     
       SUM(NVL(L.ACCOUNTED_CR, 0)) DATE_CR
     
       /*,SUM(nvl(L.ENTERED_DR,0) - nvl(L.ENTERED_CR,0)) BAL*/
     
      ,
     
       (select
       
        --hh.JE_CATEGORY,hh.JE_SOURCE,
       
        --hh.je_header_id, hh.name,
       
        --LL.ENTERED_DR, LL.ENTERED_CR, (nvl(LL.ENTERED_DR,0) - nvl(LL.ENTERED_CR,0)) BAL
       
         SUM(nvl(LL.ACCOUNTED_DR, 0) - nvl(LL.ACCOUNTED_CR, 0))
       
          from gl_je_headers hh, gl_je_lines ll, gl_code_combinations gccc
       
         where 1 = 1
             
           and hh.je_header_id = LL.je_header_id
             
           and LL.code_combination_id = gccc.code_combination_id
             
           and hh.LEDGER_ID = ll.LEDGER_ID
             
              /*                     and hh.JE_CATEGORY not in ('1',
                             
                             
                             
              '3',
                             
                             
                             
                             
                             
              'Revaluation',
                             
              '2'
                             
              )
                             
              */
           and hh.JE_FROM_SLA_FLAG = 'Y'
             
           and gccc.SEGMENT4 = gcc.SEGMENT4
             
              --and hh.default_effective_date<='31-OCT-15'
             
              --and hh.period_name='OCT-15'
             
           AND trunc(LL.EFFECTIVE_DATE) < trunc(L.EFFECTIVE_DATE)
             
              --and hh.je_category='Purchase Invoices'
             
           and gccc.segment4 = &ACCOUNT
             
              --AND hh.CURRENCY_CODE='PKR'
             
           AND hh.LEDGER_ID = &LEDGER
             
           and hh.STATUS = 'P'
             
           and ll.STATUS = 'P'
             
           and hh.Actual_flag = 'A'
       
        --AND hh.JE_SOURCE='Payables'
       
        --AND hh.JE_CATEGORY='Purchase Invoices'
       
        --GROUP BY LL.EFFECTIVE_DATE
       
        --group by ll.EFFECTIVE_DATE
       
        ) + SUM(NVL(L.ACCOUNTED_DR, 0)) - SUM(NVL(L.ACCOUNTED_CR, 0)) ENDING_BALANCE

  from gl_je_headers h, gl_je_lines l, gl_code_combinations gcc

 where 1 = 1
     
   and h.je_header_id = l.je_header_id
     
   and l.code_combination_id = gcc.code_combination_id
     
   and h.LEDGER_ID = l.LEDGER_ID
     
      --and h.default_effective_date<='31-OCT-15'
     
      --and h.period_name='OCT-15'
     
   AND trunc(L.EFFECTIVE_DATE) BETWEEN &FROM_DATE AND &TO_DATE
     
      /*            and h.JE_CATEGORY not in ('1',
             
             
             
      '3',
             
             
             
             
             
      'Revaluation',
             
      '2'
             
      )*/
     
   and h.JE_FROM_SLA_FLAG = 'Y'
     
      --and h.je_category='Purchase Invoices'
     
   and gcc.segment4 = &ACCOUNT
     
      --AND H.CURRENCY_CODE='PKR'
     
   AND H.LEDGER_ID = &LEDGER
     
   and H.STATUS = 'P'
     
   and l.STATUS = 'P'
     
   and H.Actual_flag = 'A'

--AND H.JE_SOURCE='Payables'

--AND H.JE_CATEGORY='Purchase Invoices'

 GROUP BY L.EFFECTIVE_DATE, gcc.SEGMENT4

--GROUP BY H.JE_CATEGORY,H.JE_SOURCE

 ORDER BY L.EFFECTIVE_DATE