Kumpulan Query SQL SIMDA Keuangan Selama Pemeriksaan BPK

Selama pemeriksaan Laporan Keuangan Pemerintah Daerah oleh BPK, saya sering dimintai berbagai jenis data. Pemeriksa dari BPK menyiapkan format yang dan saya sebagai admin SIMDA Keuangan yang kebetulan mengerti query SQL harus memutar otak untuk merangkai query SQL sesuai permintaan. Beberapa query SQL yang paling sering diminta adalah Register SPP, SPM, SP2D, lengkap dengan rincian belanjanya. Kemudian ada register STS, register pengembalian belanja, yang semua itu akan digunakan untuk menguji angka di dalam Laporan Realisasi Anggaran (LRA). Sayangnya di dalam SIMDA Keuangan laporan yang seperti itu tidak ada. Maka dari itu diperlukanlah query SQL untuk mengambil data tersebut. Namun data yang diambil menggunakan query SQL kadang memerlukan pengolah kembali menggunakan Excel, misalnya menggunakan pivot table agar format tabel sesuai dengan yang diinginkan.

Beberapa query yang saya gunakan selama pemeriksaan BPK adalah sebagai berikut:

1. Register STS per SKPD dengan kode akun pendapatan sampai dengan rincian obyek.
2. Register SPP, SPM, SP2D per SKPD dengan kode akun belanja sampai dengan rincian obyek.
3. Jumlah SP2D per SKPD, per Jenis SP2D, dilengkapi dengan jumlah pengembalian belanja, setoran sisa UP, setoran sisa TU.

Query-query tersebut dapat saya jelaskan sebagai berikut:

1. Register STS per SKPD dengan kode akun pendapatan sampai dengan rincian obyek.

SELECT A.Tahun, 
 CAST(A.Kd_Urusan AS VARCHAR) +'.'+ RIGHT('0'+CAST(A.Kd_Bidang AS VARCHAR),2) +'.'+ RIGHT('0'+CAST(A.Kd_Unit AS VARCHAR),2) +'.'+ RIGHT('0'+CAST(A.Kd_Sub AS VARCHAR),2) + ' - ' + C.Nm_Sub_Unit AS Sub_Unit,
 CAST(A.Kd_Rek_1 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_2 AS VARCHAR) + ' - ' + H.Nm_Rek_2 AS Rekening_2,
 CAST(A.Kd_Rek_1 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_2 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_3 AS VARCHAR) + ' - ' + G.Nm_Rek_3 AS Rekening_3,
 CAST(A.Kd_Rek_1 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_2 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_3 AS VARCHAR) + '.' + RIGHT('0'+CAST(A.Kd_Rek_4 AS VARCHAR),2) +' - ' + F.Nm_Rek_4 AS Rekening_4,
 CAST(A.Kd_Rek_1 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_2 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_3 AS VARCHAR) + '.' + RIGHT('0'+CAST(A.Kd_Rek_4 AS VARCHAR),2) +'.'+ RIGHT('0'+CAST(A.Kd_Rek_5 AS VARCHAR),2) + ' - ' + D.Nm_Rek_5 AS Rekening_5, 
 B.No_STS, B.Tgl_STS, B.Keterangan,
 SUM(A.Nilai) AS Nilai_STS
FROM Ta_STS_Rinc A INNER JOIN
 Ta_STS B ON A.Tahun = B.Tahun AND A.No_STS = B.No_STS
 INNER JOIN Ref_Sub_Unit C ON A.Kd_Urusan = C.Kd_Urusan AND A.Kd_Bidang = C.Kd_Bidang AND A.Kd_Unit = C.Kd_Unit AND A.Kd_Sub = C.Kd_Sub
 INNER JOIN Ref_Rek_5 D ON A.Kd_Rek_1 = D.Kd_Rek_1 AND A.Kd_Rek_2 = D.Kd_Rek_2 AND A.Kd_Rek_3 = D.Kd_Rek_3 AND A.Kd_Rek_4 = D.Kd_Rek_4 AND A.Kd_Rek_5 = D.Kd_Rek_5
 INNER JOIN Ref_Rek_4 F ON A.Kd_Rek_1 = F.Kd_Rek_1 AND A.Kd_Rek_2 = F.Kd_Rek_2 AND A.Kd_Rek_3 = F.Kd_Rek_3 AND A.Kd_Rek_4 = F.Kd_Rek_4
 INNER JOIN Ref_Rek_3 G ON A.Kd_Rek_1 = G.Kd_Rek_1 AND A.Kd_Rek_2 = G.Kd_Rek_2 AND A.Kd_Rek_3 = G.Kd_Rek_3
 INNER JOIN Ref_Rek_2 H ON A.Kd_Rek_1 = H.Kd_Rek_1 AND A.Kd_Rek_2 = H.Kd_Rek_2
GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, C.Nm_Sub_Unit, A.Kd_Rek_1, A.Kd_Rek_2, A.Kd_Rek_3, A.Kd_Rek_4, A.Kd_Rek_5, H.Nm_Rek_2, G.Nm_Rek_3, F.Nm_Rek_4, D.Nm_Rek_5, 
 B.No_STS, B.Tgl_STS, B.Keterangan

2. Register SPP, SPM, SP2D per SKPD dengan kode akun belanja sampai dengan rincian obyek.

SELECT A.Tahun, 
 CAST(A.Kd_Urusan AS VARCHAR) +'.'+ RIGHT('0'+CAST(A.Kd_Bidang AS VARCHAR),2) +'.'+ RIGHT('0'+CAST(A.Kd_Unit AS VARCHAR),2) +'.'+ RIGHT('0'+CAST(A.Kd_Sub AS VARCHAR),2) + ' - ' + C.Nm_Sub_Unit AS Sub_Unit,
 CAST(A.Kd_Rek_1 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_2 AS VARCHAR) + ' - ' + H.Nm_Rek_2 AS Rekening_2,
 CAST(A.Kd_Rek_1 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_2 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_3 AS VARCHAR) + ' - ' + G.Nm_Rek_3 AS Rekening_3,
 CAST(A.Kd_Rek_1 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_2 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_3 AS VARCHAR) + '.' + RIGHT('0'+CAST(A.Kd_Rek_4 AS VARCHAR),2) +' - ' + F.Nm_Rek_4 AS Rekening_4,
 CAST(A.Kd_Rek_1 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_2 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_3 AS VARCHAR) + '.' + RIGHT('0'+CAST(A.Kd_Rek_4 AS VARCHAR),2) +'.'+ RIGHT('0'+CAST(A.Kd_Rek_5 AS VARCHAR),2) + ' - ' + D.Nm_Rek_5 AS Rekening_5,
 CASE A.Jn_SPM
 WHEN 1 THEN 'UP'
 WHEN 2 THEN 'GU'
 WHEN 3 THEN 'LS'
 WHEN 4 THEN 'TU'
 WHEN 5 THEN 'NIHIL'
 END AS Jn_SPM, A.Uraian, A.Tgl_SPM, A.No_SPM, B.Tgl_SP2D, B.No_SP2D, 
 A.Nilai,
 A.Nm_Penerima, A.Rek_Penerima, A.NPWP, A.Bank_Penerima
FROM
 (
 SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub,
 A.Kd_Rek_1, A.Kd_Rek_2, A.Kd_Rek_3, A.Kd_Rek_4, A.Kd_Rek_5,
 B.No_SPM, B.Tgl_SPM, B.Jn_SPM, B.Uraian,
 SUM(ROUND(A.Nilai,2)) AS Nilai,
 B.Bank_Penerima, B.NPWP, B.Nm_Penerima, B.Rek_Penerima
 FROM Ta_SPM_Rinc A INNER JOIN
 Ta_SPM B ON A.Tahun = B.Tahun AND A.No_SPM = B.No_SPM
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 A.Kd_Rek_1, A.Kd_Rek_2, A.Kd_Rek_3, A.Kd_Rek_4, A.Kd_Rek_5,
 B.No_SPM, B.Tgl_SPM, B.Jn_SPM, B.Uraian,
 B.Bank_Penerima, B.NPWP, B.Nm_Penerima, B.Rek_Penerima
 ) A 
INNER JOIN Ta_SP2D B ON A.Tahun = B.Tahun AND A.No_SPM = B.No_SPM
INNER JOIN Ref_Sub_Unit C ON A.Kd_Urusan = C.Kd_Urusan AND A.Kd_Bidang = C.Kd_Bidang AND A.Kd_Unit = C.Kd_Unit AND A.Kd_Sub = C.Kd_Sub
INNER JOIN Ref_Rek_5 D ON A.Kd_Rek_1 = D.Kd_Rek_1 AND A.Kd_Rek_2 = D.Kd_Rek_2 AND A.Kd_Rek_3 = D.Kd_Rek_3 AND A.Kd_Rek_4 = D.Kd_Rek_4 AND A.Kd_Rek_5 = D.Kd_Rek_5
INNER JOIN Ref_Rek_4 F ON A.Kd_Rek_1 = F.Kd_Rek_1 AND A.Kd_Rek_2 = F.Kd_Rek_2 AND A.Kd_Rek_3 = F.Kd_Rek_3 AND A.Kd_Rek_4 = F.Kd_Rek_4
INNER JOIN Ref_Rek_3 G ON A.Kd_Rek_1 = G.Kd_Rek_1 AND A.Kd_Rek_2 = G.Kd_Rek_2 AND A.Kd_Rek_3 = G.Kd_Rek_3
INNER JOIN Ref_Rek_2 H ON A.Kd_Rek_1 = H.Kd_Rek_1 AND A.Kd_Rek_2 = H.Kd_Rek_2
ORDER BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, B.Tgl_SP2D, B.No_SP2D

3. Jumlah SP2D per SKPD, per Jenis SP2D, dilengkapi dengan jumlah pengembalian belanja, setoran sisa UP, setoran sisa TU.

SELECT A.*, ISNULL(B.Nilai,0) AS UP, ISNULL(C.Nilai,0) AS GU, ISNULL(D.Nilai,0) AS LS, ISNULL(E.Nilai,0) AS TU, ISNULL(F.Nilai,0) AS NIHIL, 
 ISNULL(G.Nilai,0) AS CP, ISNULL(H.Nilai,0) AS SUP, ISNULL(I.Nilai,0) AS STU, ISNULL(J.Nilai,0) AS CPJ
FROM Ref_Sub_Unit A
LEFT JOIN (
 SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, SUM(A.Nilai_SPM) AS Nilai
 FROM
 (
 SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 B.No_SPM, SUM(ROUND(A.Nilai,2)) AS Nilai_SPM
 FROM Ta_SPM_Rinc A INNER JOIN
 Ta_SPM B ON A.Tahun = B.Tahun AND A.No_SPM = B.No_SPM
 WHERE B.Jn_SPM = 1
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 B.No_SPM, B.Tgl_SPM, B.Jn_SPM, B.Uraian
 ) A
 INNER JOIN Ta_SP2D B ON A.Tahun = B.Tahun AND A.No_SPM = B.No_SPM
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub
 ) B ON A.Kd_Urusan = B.Kd_Urusan AND A.Kd_Bidang = B.Kd_Bidang AND A.Kd_Unit = B.Kd_Unit AND A.Kd_Sub = B.Kd_Sub

LEFT JOIN (
 SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, SUM(A.Nilai_SPM) AS Nilai
 FROM
 (
 SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 B.No_SPM, SUM(ROUND(A.Nilai,2)) AS Nilai_SPM
 FROM Ta_SPM_Rinc A INNER JOIN
 Ta_SPM B ON A.Tahun = B.Tahun AND A.No_SPM = B.No_SPM
 WHERE B.Jn_SPM = 2
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 B.No_SPM, B.Tgl_SPM, B.Jn_SPM, B.Uraian
 ) A
 INNER JOIN Ta_SP2D B ON A.Tahun = B.Tahun AND A.No_SPM = B.No_SPM
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub
 ) C ON A.Kd_Urusan = C.Kd_Urusan AND A.Kd_Bidang = C.Kd_Bidang AND A.Kd_Unit = C.Kd_Unit AND A.Kd_Sub = C.Kd_Sub

LEFT JOIN (
 SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, SUM(A.Nilai_SPM) AS Nilai
 FROM
 (
 SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 B.No_SPM, SUM(ROUND(A.Nilai,2)) AS Nilai_SPM
 FROM Ta_SPM_Rinc A INNER JOIN
 Ta_SPM B ON A.Tahun = B.Tahun AND A.No_SPM = B.No_SPM
 WHERE B.Jn_SPM = 3
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 B.No_SPM, B.Tgl_SPM, B.Jn_SPM, B.Uraian
 ) A
 INNER JOIN Ta_SP2D B ON A.Tahun = B.Tahun AND A.No_SPM = B.No_SPM
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub
 ) D ON A.Kd_Urusan = D.Kd_Urusan AND A.Kd_Bidang = D.Kd_Bidang AND A.Kd_Unit = D.Kd_Unit AND A.Kd_Sub = D.Kd_Sub
 
LEFT JOIN (
 SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, SUM(A.Nilai_SPM) AS Nilai
 FROM
 (
 SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 B.No_SPM, SUM(ROUND(A.Nilai,2)) AS Nilai_SPM
 FROM Ta_SPM_Rinc A INNER JOIN
 Ta_SPM B ON A.Tahun = B.Tahun AND A.No_SPM = B.No_SPM
 WHERE B.Jn_SPM = 4
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 B.No_SPM, B.Tgl_SPM, B.Jn_SPM, B.Uraian
 ) A
 INNER JOIN Ta_SP2D B ON A.Tahun = B.Tahun AND A.No_SPM = B.No_SPM
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub
 ) E ON A.Kd_Urusan = E.Kd_Urusan AND A.Kd_Bidang = E.Kd_Bidang AND A.Kd_Unit = E.Kd_Unit AND A.Kd_Sub = E.Kd_Sub

LEFT JOIN (
 SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, SUM(A.Nilai_SPM) AS Nilai
 FROM
 (
 SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 B.No_SPM, SUM(ROUND(A.Nilai,2)) AS Nilai_SPM
 FROM Ta_SPM_Rinc A INNER JOIN
 Ta_SPM B ON A.Tahun = B.Tahun AND A.No_SPM = B.No_SPM
 WHERE B.Jn_SPM = 5
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 B.No_SPM, B.Tgl_SPM, B.Jn_SPM, B.Uraian
 ) A
 INNER JOIN Ta_SP2D B ON A.Tahun = B.Tahun AND A.No_SPM = B.No_SPM
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub
 ) F ON A.Kd_Urusan = F.Kd_Urusan AND A.Kd_Bidang = F.Kd_Bidang AND A.Kd_Unit = F.Kd_Unit AND A.Kd_Sub = F.Kd_Sub

LEFT JOIN (
 SELECT A.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, SUM(ROUND(A.Nilai,2)) AS Nilai
 FROM Ta_Penyesuaian_Rinc A INNER JOIN
 Ta_Penyesuaian B ON A.Tahun = B.Tahun AND A.No_Bukti = B.No_Bukti
 WHERE (B.Jns_P1 = 1) AND (B.Jns_P2 = 3)
 GROUP BY A.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub
 ) G ON A.Kd_Urusan = G.Kd_Urusan AND A.Kd_Bidang = G.Kd_Bidang AND A.Kd_Unit = G.Kd_Unit AND A.Kd_Sub = G.Kd_Sub
 
LEFT JOIN (
 SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, SUM(ROUND(A.Nilai,2)) AS Nilai
 FROM Ta_S3UP A
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub
 ) H ON A.Kd_Urusan = H.Kd_Urusan AND A.Kd_Bidang = H.Kd_Bidang AND A.Kd_Unit = H.Kd_Unit AND A.Kd_Sub = H.Kd_Sub
 
LEFT JOIN (
 SELECT A.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, SUM(ROUND(A.Nilai,2)) AS Nilai
 FROM Ta_SPJ_Sisa A INNER JOIN
 Ta_SPJ B ON A.Tahun = B.Tahun AND A.No_SPJ = B.No_SPJ
 WHERE (B.Jn_SPJ = 4)
 GROUP BY A.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub
 ) I ON A.Kd_Urusan = I.Kd_Urusan AND A.Kd_Bidang = I.Kd_Bidang AND A.Kd_Unit = I.Kd_Unit AND A.Kd_Sub = I.Kd_Sub
 
LEFT JOIN (
 SELECT A.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, SUM(ROUND(A.Nilai,2)) AS Nilai
 FROM Ta_Jurnal_Rinc A INNER JOIN
 Ta_Jurnal B ON A.Tahun = B.Tahun AND A.No_Bukti = B.No_Bukti
 WHERE (A.Kd_Rek_1 = 5) AND (A.D_K = 'K') AND (A.No_Bukti NOT LIKE '%PENUTUP%')
 GROUP BY A.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub
 ) J ON A.Kd_Urusan = J.Kd_Urusan AND A.Kd_Bidang = J.Kd_Bidang AND A.Kd_Unit = J.Kd_Unit AND A.Kd_Sub = J.Kd_Sub