select * from (
(select DATEDIFF(i.date,'2020-08-01') as day_diff,i.date, i.costing_no as 'tran_no', 'CO' as 'type' ,i.id as 'costing_id',i.total_amount,x.received,(ifnull(i.total_amount,0)-ifnull(x.received,0)) as 'debit', 0 as 'credit' , i.supplier_invoice_no as 'ref_no'
from 0p4_costing i left join
(select t.costing_id, sum(ifnull(t.received,0)) as received from
((select o.costing_id, sum(ifnull(o.receive_amount,0)) as received from 0p4_account_payable_oustanding o
left join 0p4_account_payable ar on  o.account_payable_id=ar.account_payable_id
where o.costing_id is not null and ar.post_status_id=2  and ar.supplier_id=520 and ar.date <='2020-08-01'
group by o.costing_id)
union all
(select o.costing_id, sum(ifnull(o.receive_amount,0)) as received from 0p4_journal_supplier_oustanding o
left join 0p4_journal ar on  o.journal_id=ar.journal_id
where o.costing_id is not null and ar.supplier_id=520 and ar.date <='2020-08-01'
group by o.costing_id)) t
group by t.costing_id) x on i.id=x.costing_id
where i.supplier_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.vendor_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_vendor_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_payable_oustanding o
left join 0p4_account_payable ar on  o.account_payable_id=ar.account_payable_id
where o.credit_note_id is not null and ar.post_status_id=2  and ar.supplier_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_supplier_oustanding o
left join 0p4_journal ar on  o.journal_id=ar.journal_id
where o.credit_note_id is not null and ar.supplier_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.supplier_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.vendor_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_vendor_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_payable_oustanding o
left join 0p4_account_payable ar on  o.account_payable_id=ar.account_payable_id
where o.debit_note_id is not null and ar.post_status_id=2  and ar.supplier_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_supplier_oustanding o
left join 0p4_journal ar on  o.journal_id=ar.journal_id
where o.debit_note_id is not null and ar.supplier_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.supplier_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_payable_payment_number as 'tran_no','AP' as 'type' ,i.account_payable_id as 'account_payable_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_payable i left join
(select t.account_payable_id, sum(ifnull(t.received,0)) as received from
((select o.unapplied_account_payable_id as 'account_payable_id', sum(ifnull(o.receive_amount,0)) as received from 0p4_account_payable_oustanding o
left join 0p4_account_payable ar on  o.unapplied_account_payable_id=ar.account_payable_id
where o.unapplied_account_payable_id is not null and ar.post_status_id=2  and ar.supplier_id=520 and ar.date <='2020-08-01'
group by o.unapplied_account_payable_id)
union all
(select o.account_payable_id, sum(ifnull(o.receive_amount,0)) as received from 0p4_journal_supplier_oustanding o
left join 0p4_journal ar on  o.journal_id=ar.journal_id
where o.account_payable_id is not null and ar.supplier_id=520 and ar.date <='2020-08-01'
group by o.account_payable_id)) t
group by t.account_payable_id) x on i.account_payable_id=x.account_payable_id
where ifnull(i.unapplied_amount,0) >0 and i.supplier_id=520 and i.date <='2020-08-01' and i.unapplied_amount>ifnull(x.received,0))
) tb