PLSQL Query for Payables Subledger Account Analysis Report - Oracle EBS R12

https://aporaclepayables.blogspot.com/2018/07/p.html

Subledger Account Analysis Report PLSQL Query for Payables

---Begning Balance 

   select sum(b.ACCOUNTED_DR) , sum(b.ACCOUNTED_CR) ,  sum(b.ACCOUNTED_DR) - sum(b.ACCOUNTED_CR) from xla_ae_headers a, xla_ae_lines b, gl_code_combinations c
    where a.AE_HEADER_ID=b.AE_HEADER_ID
    and a.LEDGER_ID=b.LEDGER_ID
    and a.APPLICATION_ID=b.APPLICATION_ID
    and b.CODE_COMBINATION_ID=c.CODE_COMBINATION_ID
    and a.JE_CATEGORY_NAME in ('Purchase Invoices' , 'Reconciled Payments')
    and a.APPLICATION_ID = 200
    and a.LEDGER_ID = 2031
    and b.ACCOUNTING_DATE < = '30-JUN-2017'
    and c.SEGMENT1 = '02'
    and c.SEGMENT2 = '002'
    and c.SEGMENT3 = '000'
    and c.SEGMENT4 = '248111100'
 

 --Period Debit Credit  ----   Depends on the Accounting date you provide   

    select a.DOC_SEQUENCE_VALUE , a.JE_CATEGORY_NAME , b.ACCOUNTING_DATE, b.ACCOUNTED_DR , b.ACCOUNTED_CR from xla_ae_headers a, xla_ae_lines b, gl_code_combinations c
    where a.AE_HEADER_ID=b.AE_HEADER_ID
    and a.LEDGER_ID=b.LEDGER_ID
    and a.APPLICATION_ID=b.APPLICATION_ID
    and b.CODE_COMBINATION_ID=c.CODE_COMBINATION_ID
    and a.JE_CATEGORY_NAME in ('Purchase Invoices' , 'Reconciled Payments')
    and a.APPLICATION_ID = 200
    and a.LEDGER_ID = 2031
    and b.ACCOUNTING_DATE between '01-JUL-17' and '31-JUL-17'
    and c.SEGMENT1 = '02'
    and c.SEGMENT2 = '002'
    and c.SEGMENT3 = '000'
    and c.SEGMENT4 = '248111100'

 -------------------------------------------------------------------------------------------------------------------

Summary Report

select    BR.PERIOD_NAME, BEG  , DR , CR  , sum(BEG-CR+DR) END   from  

 

( select  /*a.DOC_SEQUENCE_VALUE DOC1 , a.JE_CATEGORY_NAME  J1, b.ACCOUNTING_DATE A1, */ c.SEGMENT4, c.SEGMENT2,c.SEGMENT1,c.SEGMENT3, a.LEDGER_ID ,sum(b.ACCOUNTED_DR) - sum( b.ACCOUNTED_CR) BEG

from xla_ae_headers a, xla_ae_lines b, gl_code_combinations c

where a.AE_HEADER_ID=b.AE_HEADER_ID

and a.LEDGER_ID=b.LEDGER_ID

and a.APPLICATION_ID=b.APPLICATION_ID

and b.CODE_COMBINATION_ID=c.CODE_COMBINATION_ID

and a.JE_CATEGORY_NAME in ('Purchase Invoices' , 'Reconciled Payments')

and a.APPLICATION_ID = 200

and a.LEDGER_ID = 2031

and b.ACCOUNTING_DATE < = '30-SEP-2017'

and c.SEGMENT1 = '02'

and c.SEGMENT2 = '002'

and c.SEGMENT3 = '000'

and c.SEGMENT4 = '248111100'

group by c.SEGMENT4, c.SEGMENT2,c.SEGMENT1,c.SEGMENT3, a.LEDGER_ID 

)  AR

,

    

( select/* a.DOC_SEQUENCE_VALUE DOC2 , a.JE_CATEGORY_NAME  J2, b.ACCOUNTING_DATE A2,*/ a.PERIOD_NAME,c.SEGMENT4, c.SEGMENT2,c.SEGMENT1,c.SEGMENT3, a.LEDGER_ID ,sum(b.ACCOUNTED_DR) DR , sum( b.ACCOUNTED_CR )  CR 

from xla_ae_headers a, xla_ae_lines b, gl_code_combinations c

where a.AE_HEADER_ID=b.AE_HEADER_ID

and a.LEDGER_ID=b.LEDGER_ID

and a.APPLICATION_ID=b.APPLICATION_ID

and b.CODE_COMBINATION_ID=c.CODE_COMBINATION_ID

and a.JE_CATEGORY_NAME in ('Purchase Invoices' , 'Reconciled Payments')

and a.APPLICATION_ID = 200

and a.LEDGER_ID = 2031

and b.ACCOUNTING_DATE between '01-OCT-17' and '31-OCT-17'

and c.SEGMENT1 = '02'

and c.SEGMENT2 = '002'

and c.SEGMENT3 = '000'

and c.SEGMENT4 = '248111100'

group by a.PERIOD_NAME,c.SEGMENT4, c.SEGMENT2,c.SEGMENT1,c.SEGMENT3, a.LEDGER_ID 

)  BR

where AR.SEGMENT4 = BR.SEGMENT4

and AR.SEGMENT2 = BR.SEGMENT2

and AR.SEGMENT1 = BR.SEGMENT1

and AR.SEGMENT3 = BR.SEGMENT3

and AR.LEDGER_ID = BR.LEDGER_ID

group by  BR.PERIOD_NAME,BEG  , DR , CR