-- cek nilai Hutang Harus Dibayar di trial balance Januari 2016
SELECT * FROM gl_trial_balance WHERE coa_id = 1430 and date_year_month = '201612'

WITH summary_subledger AS (
SELECT c.partner_id, c.partner_code, c.partner_name, sum(a.item_amount) as sisa
FROM pu_po_balance_invoice a
INNER JOIN m_partner c ON A.partner_id = c.partner_id
WHERE SUBSTRING(a.ref_doc_date, 1, 6) <= '201612' and a.flg_invoice <> 'Y' and a.ref_doc_type_id = 111
GROUP BY c.partner_id, c.partner_code, c.partner_name

UNION ALL

-- ambil data hutang harus dibayar antara tanggal 1 Jan 2016 - 30 Nov 2016
-- yang sudah digunakan di CN AP / DN AP dengan tanggal CN AP / DN AP > 30 Nov '16 -- 3043396688
select d.partner_id, d.partner_code, d.partner_name,sum(a.item_amount)
from pu_po_balance_invoice a
INNER JOIN fi_invoice_ap c ON a.invoice_id = c.invoice_ap_id
INNER JOIN m_partner d ON A.partner_id = d.partner_id
INNER JOIN pu_po e ON c.ref_id = e.po_id and e.ref_doc_type_id = 106
where SUBSTRING(c.doc_date, 1, 6) > '201612'
and a.ref_doc_type_id = 111
and a.flg_invoice = 'Y'
and a.invoice_id <> -99
and SUBSTRING(a.ref_doc_date, 1, 6) <= '201612'
group by d.partner_id, d.partner_code, d.partner_name

UNION ALL

-- ambil data hutang harus dibayar antara tanggal 1 Jan 2016 - 30 Nov 2016
-- yang sudah digunakan di Purchase Invoice dengan tanggal invoice > 30 Nov '16 -- 4850000
select e.partner_id, e.partner_code, e.partner_name,sum(a.item_amount)
from pu_po_balance_invoice a
INNER JOIN pu_invoice c ON a.invoice_id = c.invoice_id and a.po_id = c.ref_id
inner join pu_po d on c.ref_id = d.po_id and a.po_id = d.po_id and d.ref_doc_type_id = -99
INNER JOIN m_partner e ON a.partner_id = e.partner_id
where SUBSTRING(c.doc_date, 1, 6) > '201612'
and a.ref_doc_type_id = 111
and a.flg_invoice = 'Y'
and a.invoice_id <> -99
and SUBSTRING(a.ref_doc_date, 1, 6) <= '201612'
group by e.partner_id, e.partner_code, e.partner_name
) SELECT SUM(sisa) FROM summary_subledger A;