You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
100 lines
2.8 KiB
SQL
100 lines
2.8 KiB
SQL
DROP PROCEDURE IF EXISTS laporan_neraca_saldo;
|
|
CREATE PROCEDURE laporan_neraca_saldo(
|
|
IN _app VARCHAR(10),
|
|
IN _dates VARCHAR(10),
|
|
IN _thn VARCHAR(10)
|
|
)
|
|
BEGIN
|
|
|
|
-- neraca tahun lalu
|
|
SELECT rek, main, nama_main, nama, ds, ROUND( sum(debit) ,0) debit, ROUND( sum(kredit) ,0) kredit FROM (
|
|
SELECT
|
|
kode rek
|
|
, main
|
|
, nama_main
|
|
, nama, dk ds
|
|
, IF(dk = 'D', total, 0) debit
|
|
, IF(dk = 'K', total, 0) kredit
|
|
FROM bbtahunan WHERE tahun = (_thn - 1) AND nr = 'N' AND (_app = '' OR app = _app)
|
|
UNION ALL
|
|
-- neraca berjalan
|
|
SELECT * FROM (
|
|
SELECT rek, main, nama_main, nama, ds, IF(ds='D', sum(total), 0) debit, IF(ds='K', sum(total), 0) kredit FROM (
|
|
SELECT rek, main, nama_main, nama, nr, ds, dk, total FROM (
|
|
SELECT
|
|
b.rekdebit AS rek,
|
|
acc.main,
|
|
acc.nama_main,
|
|
acc.nama,
|
|
acc.nr,
|
|
acc.dk ds,
|
|
'D' AS dk,
|
|
IF(acc.dk = 'K', SUM(b.debit) * -1, SUM(b.debit)) AS total
|
|
FROM lap_bb b
|
|
LEFT JOIN acc ON b.rekdebit = acc.kode
|
|
WHERE b.tgl >= concat(_thn,'-01-01') AND b.tgl <= LAST_DAY(_dates) AND acc.nr = 'N' AND (_app = '' OR acc.app = _app)
|
|
GROUP BY rek
|
|
HAVING rek <> ''
|
|
) b
|
|
UNION ALL
|
|
SELECT rek, main, nama_main, nama, nr, ds, dk, total FROM (
|
|
SELECT
|
|
b.rekkredit AS rek,
|
|
acc.main,
|
|
acc.nama_main,
|
|
acc.nama,
|
|
acc.nr,
|
|
acc.dk ds,
|
|
'K' AS dk,
|
|
IF(acc.dk = 'D', SUM(b.kredit) * -1, SUM(b.kredit)) AS total
|
|
FROM lap_bb b
|
|
LEFT JOIN acc ON b.rekkredit = acc.kode
|
|
WHERE b.tgl >= concat(_thn,'-01-01') AND b.tgl <= LAST_DAY(_dates) AND acc.nr = 'N' AND (_app = '' OR acc.app = _app)
|
|
GROUP BY rek
|
|
HAVING rek <> ''
|
|
) b
|
|
) tot GROUP BY rek
|
|
) a
|
|
UNION ALL
|
|
-- laba rugi
|
|
SELECT * FROM (
|
|
SELECT rek, main, nama_main, nama, ds, IF(ds='D', sum(total), 0) debit, IF(ds='K', sum(total), 0) kredit FROM (
|
|
SELECT rek, main, nama_main, nama, nr, ds, dk, total FROM (
|
|
SELECT
|
|
b.rekdebit AS rek,
|
|
acc.main,
|
|
acc.nama_main,
|
|
acc.nama,
|
|
acc.nr,
|
|
acc.dk ds,
|
|
'D' AS dk,
|
|
IF(acc.dk = 'K', SUM(b.debit) * -1, SUM(b.debit)) AS total
|
|
FROM lap_bb b
|
|
LEFT JOIN acc ON b.rekdebit = acc.kode
|
|
WHERE b.tgl >= concat(_thn,'-01-01') AND b.tgl <= LAST_DAY(_dates) AND acc.nr = 'L' AND (_app = '' OR acc.app = _app)
|
|
GROUP BY rek
|
|
HAVING rek <> ''
|
|
) b
|
|
UNION ALL
|
|
SELECT rek, main, nama_main, nama, nr, ds, dk, total FROM (
|
|
SELECT
|
|
b.rekkredit AS rek,
|
|
acc.main,
|
|
acc.nama_main,
|
|
acc.nama,
|
|
acc.nr,
|
|
acc.dk ds,
|
|
'K' AS dk,
|
|
IF(acc.dk = 'D', SUM(b.kredit) * -1, SUM(b.kredit)) AS total
|
|
FROM lap_bb b
|
|
LEFT JOIN acc ON b.rekkredit = acc.kode
|
|
WHERE b.tgl >= concat(_thn,'-01-01') AND b.tgl <= LAST_DAY(_dates) AND acc.nr = 'L' AND (_app = '' OR acc.app = _app)
|
|
GROUP BY rek
|
|
HAVING rek <> ''
|
|
) b
|
|
) tot GROUP BY rek
|
|
) a
|
|
) a GROUP BY rek;
|
|
|
|
END;
|