select * from (
(select pt.name as 'terms' ,DATEDIFF(i.date,'2020-08-01') as day_diff,i.date, i.invoice_no as 'tran_no','IN' as 'type' ,i.invoice_id,i.total_amount,x.received,(ifnull(i.total_amount,0)-ifnull(x.received,0)) as 'debit', 0 as 'credit' , i.customer_po_no as 'ref_no'
from 0p4_invoice i left join
(select t.invoice_id, sum(ifnull(t.received,0)) as received from
((select o.invoice_id, sum(ifnull(o.receive_amount,0)) as received from 0p4_account_receivable_oustanding o
left join 0p4_account_receivable ar on  o.account_receivable_id=ar.account_receivable_id
where o.invoice_id is not null and ar.post_status_id=2  and ar.customer_id=520 and ar.date <='2020-08-01'
group by o.invoice_id)
union all
(select o.invoice_id, sum(ifnull(o.receive_amount,0)) as received from 0p4_journal_customer_oustanding o
left join 0p4_journal ar on  o.journal_id=ar.journal_id
where o.invoice_id is not null and ar.customer_id=520 and ar.date <='2020-08-01'
group by o.invoice_id)) t
group by t.invoice_id) x on i.invoice_id=x.invoice_id
left join 0p4_payment_terms pt on pt.payment_terms_id=i.payment_terms_id
where i.customer_id=520 and i.date <='2020-08-01' and i.total_amount>ifnull(x.received,0))

union all

(select DATEDIFF(i.date,'2020-08-01') as day_diff,i.date, i.credit_note_no as 'tran_no','CN' as 'type' ,i.id as 'credit_note_id',i.total_amount,x.received,0 as 'debit', (ifnull(i.total_amount,0)-ifnull(x.received,0)) as 'credit', i.reference_no as 'ref_no'
from 0p4_credit_note i left join
(select t.credit_note_id, sum(ifnull(t.received,0)) as received from
((select o.credit_note_id, sum(ifnull(o.receive_amount,0)) as received from 0p4_account_receivable_oustanding o
left join 0p4_account_receivable ar on  o.account_receivable_id=ar.account_receivable_id
where o.credit_note_id is not null and ar.post_status_id=2  and ar.customer_id=520 and ar.date <='2020-08-01'
group by o.credit_note_id)
union all
(select o.credit_note_id, sum(ifnull(o.receive_amount,0)) as received from 0p4_journal_customer_oustanding o
left join 0p4_journal ar on  o.journal_id=ar.journal_id
where o.credit_note_id is not null and ar.customer_id=520 and ar.date <='2020-08-01'
group by o.credit_note_id)) t
group by t.credit_note_id) x on i.id=x.credit_note_id
where i.customer_id=520 and i.date <='2020-08-01' and i.total_amount>ifnull(x.received,0))

union all

(select DATEDIFF(i.date,'2020-08-01') as day_diff,i.date, i.debit_note_no as 'tran_no','DN' as 'type' ,i.id as 'debit_note_id',i.total_amount,x.received,(ifnull(i.total_amount,0)-ifnull(x.received,0)) as 'debit', 0 as 'credit', i.reference_no as 'ref_no'
from 0p4_debit_note i left join
(select t.debit_note_id, sum(ifnull(t.received,0)) as received from
((select o.debit_note_id, sum(ifnull(o.receive_amount,0)) as received from 0p4_account_receivable_oustanding o
left join 0p4_account_receivable ar on  o.account_receivable_id=ar.account_receivable_id
where o.debit_note_id is not null and ar.post_status_id=2  and ar.customer_id=520 and ar.date <='2020-08-01'
group by o.debit_note_id)
union all
(select o.debit_note_id, sum(ifnull(o.receive_amount,0)) as received from 0p4_journal_customer_oustanding o
left join 0p4_journal ar on  o.journal_id=ar.journal_id
where o.debit_note_id is not null and ar.customer_id=520 and ar.date <='2020-08-01'
group by o.debit_note_id)) t
group by t.debit_note_id) x on i.id=x.debit_note_id
where i.customer_id=520 and i.date <='2020-08-01' and i.total_amount>ifnull(x.received,0))

union all

(select DATEDIFF(i.date,'2020-08-01') as day_diff,i.date, i.account_receivable_receive_number as 'tran_no','AR' as 'type' ,i.account_receivable_id as 'account_receivable_id',i.unapplied_amount as 'total_maount',x.received,0 as 'debit', (ifnull(i.unapplied_amount,0)-ifnull(x.received,0)) as 'credit', i.reference_number as 'ref_no'
from 0p4_account_receivable i left join
(select t.account_receivable_id, sum(ifnull(t.received,0)) as received from
((select o.unapplied_account_receivable_id as 'account_receivable_id', sum(ifnull(o.receive_amount,0)) as received from 0p4_account_receivable_oustanding o
left join 0p4_account_receivable ar on  o.unapplied_account_receivable_id=ar.account_receivable_id
where o.unapplied_account_receivable_id is not null and ar.post_status_id=2  and ar.customer_id=520 and ar.date <='2020-08-01'
group by o.unapplied_account_receivable_id)
union all
(select o.account_receivable_id, sum(ifnull(o.receive_amount,0)) as received from 0p4_journal_customer_oustanding o
left join 0p4_journal ar on  o.journal_id=ar.journal_id
where o.account_receivable_id is not null and ar.customer_id=520 and ar.date <='2020-08-01'
group by o.account_receivable_id)) t
group by t.account_receivable_id) x on i.account_receivable_id=x.account_receivable_id
where ifnull(i.unapplied_amount,0) >0 and i.customer_id=520 and i.date <='2020-08-01' and i.unapplied_amount>ifnull(x.received,0))
) xx