https://aporaclepayables.blogspot.com/2018/07/p.html
---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'
-------------------------------------------------------------------------------------------------------------------
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
