Mengolah Data SIMDA Keuangan Menggunakan Pivot Table pada Excel

oleh

Sebagai pengguna SIMDA Keuangan BPKP, pada saat-saat tertentu saya sering merasa kesulitan dalam menghasilkan data dengan format tertentu langsung dari preview report SIMDA. Hal ini sering terjadi saat pemeriksaan BPK. Misalnya, pemeriksan meminta Laporan Operasional yang sampai dengan rincian obyek. Di SIMDA laporan ini tidak tersedia, jika pun mau bersusah-susah data tersebut sudah ada di Buku Besar Pembantu, namun harus di-preview satu per satu. Lalu bagaimana cara mudah untuk membuatnya? Dengan bantuan query dan Excel semua dapat dengan mudah dibuat.

Berikut query yang saya gunakan untuk membuat pivot table LO, LRA Permendagri 13, LRA Permendagri 64. Query ini akan menghasilkan yang berisi data unit, sub unit, program, kegiatan,  rekening, anggaran, dan realisasi.

Query LO:

DECLARE @Tahun varchar(4), @T1 datetime, @T2 datetime
SET @Tahun = '2015'
SET @T1 = @Tahun + '0101'
SET @T2 = @Tahun + '1231'

SELECT 
 CAST(A.Kd_Urusan AS VARCHAR) +'.'+ RIGHT('0'+CAST(A.Kd_Bidang AS VARCHAR),2) +'.'+ RIGHT('0'+CAST(A.Kd_Unit AS VARCHAR),2) +'.'+ ' - ' + B2.Nm_Unit AS Unit,
 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) + ' - ' + B1.Nm_Sub_Unit AS Sub_Unit,
 RIGHT('0'+CAST(A.Kd_Prog AS VARCHAR),2) + ' - ' + D.Ket_Program AS Program,
 RIGHT('00'+CAST(A.Kd_Prog AS VARCHAR),3) + ' - ' + C.Ket_Kegiatan AS Kegiatan,
 CAST(A.Kd_Akrual_1 AS VARCHAR) + ' - ' + I.Nm_Akrual_1 AS Rekening_1,
 CAST(A.Kd_Akrual_1 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_2 AS VARCHAR) +' - ' + H.Nm_Akrual_2 AS Rekening_2,
 CAST(A.Kd_Akrual_1 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_2 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_3 AS VARCHAR) + ' - ' + G.Nm_Akrual_3 AS Rekening_3,
 CAST(A.Kd_Akrual_1 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_2 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_3 AS VARCHAR) + '.' + RIGHT('0'+CAST(A.Kd_Akrual_4 AS VARCHAR),2) +' - ' + F.Nm_Akrual_4 AS Rekening_4,
 CAST(A.Kd_Akrual_1 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_2 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_3 AS VARCHAR) + '.' + RIGHT('0'+CAST(A.Kd_Akrual_4 AS VARCHAR),2) +'.'+ RIGHT('0'+CAST(A.Kd_Akrual_5 AS VARCHAR),2) + ' - ' + E.Nm_Akrual_5 AS Rekening_5,
 A.Realisasi
FROM (

SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 A.Kd_Prog, A.ID_Prog, A.Kd_Keg, 
 A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5,
 SUM(A.Realisasi) AS Realisasi
 FROM (

SELECT B.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, 
 A.Kd_Prog, A.Id_Prog, A.Kd_Keg, 
 A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5, 
 SUM(CASE C.SaldoNorm
 WHEN 'D' THEN A.Debet - A.Kredit
 ELSE A.Kredit - A.Debet
 END) AS Realisasi
 FROM Ta_JurnalSemuaAk_Rinc A 
 INNER JOIN Ta_JurnalSemuaAk B ON A.Tahun = B.Tahun AND A.Kd_Source = B.Kd_Source AND A.No_Bukti = B.No_Bukti 
 INNER JOIN Ref_Akrual_3 C ON A.Kd_Akrual_1 = C.Kd_Akrual_1 AND A.Kd_Akrual_2 = C.Kd_Akrual_2 AND A.Kd_Akrual_3 = C.Kd_Akrual_3
 WHERE ((CASE A.Kd_SKPD
 WHEN 1 THEN B.Posting
 ELSE B.Posting_SKPKD
 END) = 1) AND (B.Tgl_Bukti BETWEEN @T1 AND @T2)
 AND (Kd_Jurnal NOT IN (8, 10))
 AND (B.Tahun = @Tahun)
 GROUP BY B.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, 
 A.Kd_Prog, A.Id_Prog, A.Kd_Keg, 
 A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5

) A 
 WHERE A.Kd_Akrual_1 IN (8,9)
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, A.Kd_Prog, A.Id_Prog, A.Kd_Keg, A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5

) A

INNER JOIN Ref_Sub_Unit B1 ON A.Kd_Urusan = B1.Kd_Urusan AND A.Kd_Bidang = B1.Kd_Bidang AND A.Kd_Unit = B1.Kd_Unit AND A.Kd_Sub = B1.Kd_Sub
INNER JOIN Ref_Unit B2 ON A.Kd_Urusan = B2.Kd_Urusan AND A.Kd_Bidang = B2.Kd_Bidang AND A.Kd_Unit = B2.Kd_Unit
INNER JOIN Ref_Bidang B3 ON A.Kd_Urusan = B3.Kd_Urusan AND A.Kd_Bidang = B3.Kd_Bidang
INNER JOIN Ref_Urusan B4 ON A.Kd_Urusan = B4.Kd_Urusan
INNER JOIN Ta_Kegiatan C ON A.Tahun = C.Tahun AND 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 AND A.Kd_Prog = C.Kd_Prog AND A.Id_Prog = C.Id_Prog AND A.Kd_Keg = C.Kd_Keg
INNER JOIN Ta_Program D ON A.Tahun = D.Tahun AND 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 AND A.Kd_Prog = D.Kd_Prog AND A.Id_Prog = D.Id_Prog
INNER JOIN Ref_Akrual_5 E ON A.Kd_Akrual_1 = E.Kd_Akrual_1 AND A.Kd_Akrual_2 = E.Kd_Akrual_2 AND A.Kd_Akrual_3 = E.Kd_Akrual_3 AND A.Kd_Akrual_4 = E.Kd_Akrual_4 AND A.Kd_Akrual_5 = E.Kd_Akrual_5
INNER JOIN Ref_Akrual_4 F ON A.Kd_Akrual_1 = F.Kd_Akrual_1 AND A.Kd_Akrual_2 = F.Kd_Akrual_2 AND A.Kd_Akrual_3 = F.Kd_Akrual_3 AND A.Kd_Akrual_4 = F.Kd_Akrual_4
INNER JOIN Ref_Akrual_3 G ON A.Kd_Akrual_1 = G.Kd_Akrual_1 AND A.Kd_Akrual_2 = G.Kd_Akrual_2 AND A.Kd_Akrual_3 = G.Kd_Akrual_3
INNER JOIN Ref_Akrual_2 H ON A.Kd_Akrual_1 = H.Kd_Akrual_1 AND A.Kd_Akrual_2 = H.Kd_Akrual_2
INNER JOIN Ref_Akrual_1 I ON A.Kd_Akrual_1 = I.Kd_Akrual_1

ORDER BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, A.Kd_Prog, A.Id_Prog, A.Kd_Keg, A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5

 

Query LRA Permendagri 13:

DECLARE @Tahun varchar(4), @T1 datetime, @T2 datetime
SET @Tahun = '2016'
SET @T1 = @Tahun + '1231'
SET @T2 = @Tahun + '0101'

SELECT 
 CAST(A.Kd_Urusan AS VARCHAR) +'.'+ RIGHT('0'+CAST(A.Kd_Bidang AS VARCHAR),2) +'.'+ RIGHT('0'+CAST(A.Kd_Unit AS VARCHAR),2) +'.'+ ' - ' + B2.Nm_Unit AS Unit,
 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) + ' - ' + B1.Nm_Sub_Unit AS Sub_Unit,
 RIGHT('0'+CAST(A.Kd_Prog AS VARCHAR),2) + ' - ' + D.Ket_Program AS Program,
 RIGHT('00'+CAST(A.Kd_Prog AS VARCHAR),3) + ' - ' + C.Ket_Kegiatan AS Kegiatan,
 CAST(A.Kd_Rek_1 AS VARCHAR) + ' - ' + I.Nm_Rek_1 AS Rekening_1,
 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) + ' - ' + E.Nm_Rek_5 AS Rekening_5,
 A.Anggaran, A.Realisasi
FROM (

SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 A.Kd_Prog, A.ID_Prog, A.Kd_Keg, 
 A.Kd_Rek_1, A.Kd_Rek_2, A.Kd_Rek_3, A.Kd_Rek_4, A.Kd_Rek_5,
 SUM(A.Anggaran) AS Anggaran, SUM(A.Realisasi) AS Realisasi
 FROM (

SELECT B.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, 
 CASE
 WHEN (A.Kd_Rek_1 = 5) AND (A.Kd_Rek_2 = 2) THEN A.Kd_Prog
 ELSE 0
 END AS Kd_Prog, 
 CASE
 WHEN (A.Kd_Rek_1 = 5) AND (A.Kd_Rek_2 = 2) THEN A.Id_Prog
 ELSE 0
 END AS Id_Prog,
 CASE
 WHEN (A.Kd_Rek_1 = 5) AND (A.Kd_Rek_2 = 2) THEN A.Kd_Keg
 ELSE 0
 END AS Kd_Keg, A.Kd_Rek_1, A.Kd_Rek_2, A.Kd_Rek_3, A.Kd_Rek_4, A.Kd_Rek_5, 0 AS Anggaran, 
 SUM(CASE C.SaldoNorm
 WHEN 'D' THEN A.Debet - A.Kredit
 ELSE A.Kredit - A.Debet
 END) AS Realisasi
 FROM Ta_JurnalSemua_Rinc A INNER JOIN
 Ta_JurnalSemua B ON A.Tahun = B.Tahun AND A.Kd_Source = B.Kd_Source AND A.No_Bukti = B.No_Bukti INNER JOIN
 Ref_Rek_3 C ON A.Kd_Rek_1 = C.Kd_Rek_1 AND A.Kd_Rek_2 = C.Kd_Rek_2 AND A.Kd_Rek_3 = C.Kd_Rek_3
 WHERE ((CASE A.Kd_SKPD
 WHEN 1 THEN B.Posting
 ELSE B.Posting_SKPKD
 END) = 1) 
 AND (B.Tgl_Bukti BETWEEN @T2 AND @T1)
 AND (NOT ((A.Kd_Rek_1 = 6) AND (A.Kd_Rek_2 IN (3, 4))))
 AND (B.Tahun = @Tahun)
 GROUP BY B.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, A.Kd_Prog, A.Id_Prog, A.Kd_Keg, A.Kd_Rek_1, A.Kd_Rek_2, A.Kd_Rek_3, A.Kd_Rek_4, A.Kd_Rek_5

UNION ALL

SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub,
 CASE
 WHEN (A.Kd_Rek_1 = 5) AND (A.Kd_Rek_2 = 2) THEN A.Kd_Prog
 ELSE 0
 END AS Kd_Prog,
 CASE
 WHEN (A.Kd_Rek_1 = 5) AND (A.Kd_Rek_2 = 2) THEN A.Id_Prog
 ELSE 0
 END AS Id_Prog,
 CASE
 WHEN (A.Kd_Rek_1 = 5) AND (A.Kd_Rek_2 = 2) THEN A.Kd_Keg
 ELSE 0
 END AS Kd_Keg,
 A.Kd_Rek_1, A.Kd_Rek_2, A.Kd_Rek_3, A.Kd_Rek_4, A.Kd_Rek_5, SUM(A.Total) AS Anggaran, 0 AS Realisasi
 FROM Ta_RASK_Arsip A
 WHERE (A.Kd_Perubahan = (SELECT MAX(Kd_Perubahan) FROM Ta_RASK_Arsip_Perubahan WHERE Tahun = @Tahun AND Kd_Perubahan IN (4, 6, 8) AND Tgl_Perda <= @T1)) AND (A.Tahun = @Tahun) 
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, A.Kd_Prog, A.Id_Prog, A.Kd_Keg, A.Kd_Rek_1, A.Kd_Rek_2, A.Kd_Rek_3, A.Kd_Rek_4, A.Kd_Rek_5

) A 
 WHERE A.Kd_Rek_1 IN (4,5,6)
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, A.Kd_Prog, A.Id_Prog, A.Kd_Keg, A.Kd_Rek_1, A.Kd_Rek_2, A.Kd_Rek_3, A.Kd_Rek_4, A.Kd_Rek_5

) A

INNER JOIN Ref_Sub_Unit B1 ON A.Kd_Urusan = B1.Kd_Urusan AND A.Kd_Bidang = B1.Kd_Bidang AND A.Kd_Unit = B1.Kd_Unit AND A.Kd_Sub = B1.Kd_Sub
INNER JOIN Ref_Unit B2 ON A.Kd_Urusan = B2.Kd_Urusan AND A.Kd_Bidang = B2.Kd_Bidang AND A.Kd_Unit = B2.Kd_Unit
INNER JOIN Ref_Bidang B3 ON A.Kd_Urusan = B3.Kd_Urusan AND A.Kd_Bidang = B3.Kd_Bidang
INNER JOIN Ref_Urusan B4 ON A.Kd_Urusan = B4.Kd_Urusan
INNER JOIN Ta_Kegiatan C ON A.Tahun = C.Tahun AND 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 AND A.Kd_Prog = C.Kd_Prog AND A.Id_Prog = C.Id_Prog AND A.Kd_Keg = C.Kd_Keg
INNER JOIN Ta_Program D ON A.Tahun = D.Tahun AND 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 AND A.Kd_Prog = D.Kd_Prog AND A.Id_Prog = D.Id_Prog
INNER JOIN Ref_Rek_5 E ON A.Kd_Rek_1 = E.Kd_Rek_1 AND A.Kd_Rek_2 = E.Kd_Rek_2 AND A.Kd_Rek_3 = E.Kd_Rek_3 AND A.Kd_Rek_4 = E.Kd_Rek_4 AND A.Kd_Rek_5 = E.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
INNER JOIN Ref_Rek_1 I ON A.Kd_Rek_1 = I.Kd_Rek_1

 

Query LRA Permendagri 64:

DECLARE @Tahun varchar(4), @T1 datetime, @T2 datetime
SET @Tahun = '2016'
SET @T1 = @Tahun + '1231'
SET @T2 = @Tahun + '0101'

SELECT 
 CAST(A.Kd_Urusan AS VARCHAR) +'.'+ RIGHT('0'+CAST(A.Kd_Bidang AS VARCHAR),2) +'.'+ RIGHT('0'+CAST(A.Kd_Unit AS VARCHAR),2) +'.'+ ' - ' + B2.Nm_Unit AS Unit,
 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) + ' - ' + B1.Nm_Sub_Unit AS Sub_Unit,
 RIGHT('0'+CAST(A.Kd_Prog AS VARCHAR),2) + ' - ' + D.Ket_Program AS Program,
 RIGHT('00'+CAST(A.Kd_Prog AS VARCHAR),3) + ' - ' + C.Ket_Kegiatan AS Kegiatan,
 CAST(A.Kd_Akrual_1 AS VARCHAR) + ' - ' + I.Nm_Akrual_1 AS Rekening_1,
 CAST(A.Kd_Akrual_1 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_2 AS VARCHAR) +' - ' + H.Nm_Akrual_2 AS Rekening_2,
 CAST(A.Kd_Akrual_1 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_2 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_3 AS VARCHAR) + ' - ' + G.Nm_Akrual_3 AS Rekening_3,
 CAST(A.Kd_Akrual_1 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_2 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_3 AS VARCHAR) + '.' + RIGHT('0'+CAST(A.Kd_Akrual_4 AS VARCHAR),2) +' - ' + F.Nm_Akrual_4 AS Rekening_4,
 CAST(A.Kd_Akrual_1 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_2 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_3 AS VARCHAR) + '.' + RIGHT('0'+CAST(A.Kd_Akrual_4 AS VARCHAR),2) +'.'+ RIGHT('0'+CAST(A.Kd_Akrual_5 AS VARCHAR),2) + ' - ' + E.Nm_Akrual_5 AS Rekening_5,
 A.Anggaran, A.Realisasi
FROM (

SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 A.Kd_Prog, A.ID_Prog, A.Kd_Keg, 
 A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5,
 SUM(A.Anggaran) AS Anggaran, SUM(A.Realisasi) AS Realisasi
 FROM (

SELECT B.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, 
 A.Kd_Prog, A.Id_Prog, A.Kd_Keg, 
 A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5, 0 AS Anggaran, 
 SUM(CASE C.SaldoNorm
 WHEN 'D' THEN A.Debet - A.Kredit
 ELSE A.Kredit - A.Debet
 END) AS Realisasi
 FROM Ta_JurnalSemuaAk_Rinc A 
 INNER JOIN Ta_JurnalSemuaAk B ON A.Tahun = B.Tahun AND A.Kd_Source = B.Kd_Source AND A.No_Bukti = B.No_Bukti 
 INNER JOIN Ref_Akrual_3 C ON A.Kd_Akrual_1 = C.Kd_Akrual_1 AND A.Kd_Akrual_2 = C.Kd_Akrual_2 AND A.Kd_Akrual_3 = C.Kd_Akrual_3
 WHERE ((CASE A.Kd_SKPD
 WHEN 1 THEN B.Posting
 ELSE B.Posting_SKPKD
 END) = 1) AND (B.Tgl_Bukti BETWEEN @T2 AND @T1)
 AND (Kd_Jurnal NOT IN (8, 10))
 AND (B.Tahun = @Tahun)
 GROUP BY B.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, 
 A.Kd_Prog, A.Id_Prog, A.Kd_Keg, 
 A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5

UNION ALL

SELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, 
 A.Kd_Prog, A.Id_Prog, A.Kd_Keg,
 B.Kd_Akrual_1, B.Kd_Akrual_2, B.Kd_Akrual_3, B.Kd_Akrual_4, B.Kd_Akrual_5, SUM(A.Total) AS Anggaran, 0 AS Realisasi
 FROM Ta_RASK_Arsip A
 INNER JOIN Ref_Akrual_Rek B ON A.Kd_Rek_1 = B.Kd_Rek_1 AND A.Kd_Rek_2 = B.Kd_Rek_2 AND A.Kd_Rek_3 = B.Kd_Rek_3 AND A.Kd_Rek_4 = B.Kd_Rek_4 AND A.Kd_Rek_5 = B.Kd_Rek_5
 WHERE (A.Kd_Perubahan = (SELECT MAX(Kd_Perubahan) FROM Ta_RASK_Arsip_Perubahan WHERE Tahun = @Tahun AND Kd_Perubahan IN (4, 6, 8) AND Tgl_Perda <= @T1)) AND (A.Tahun = @Tahun) 
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, A.Kd_Prog, A.Id_Prog, A.Kd_Keg, B.Kd_Akrual_1, B.Kd_Akrual_2, B.Kd_Akrual_3, B.Kd_Akrual_4, B.Kd_Akrual_5

) A 
 WHERE A.Kd_Akrual_1 = 5 AND A.Kd_Akrual_2 = 2 --AND A.Kd_Akrual_3 = 4
 GROUP BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, A.Kd_Prog, A.Id_Prog, A.Kd_Keg, A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5

) A

INNER JOIN Ref_Sub_Unit B1 ON A.Kd_Urusan = B1.Kd_Urusan AND A.Kd_Bidang = B1.Kd_Bidang AND A.Kd_Unit = B1.Kd_Unit AND A.Kd_Sub = B1.Kd_Sub
INNER JOIN Ref_Unit B2 ON A.Kd_Urusan = B2.Kd_Urusan AND A.Kd_Bidang = B2.Kd_Bidang AND A.Kd_Unit = B2.Kd_Unit
INNER JOIN Ref_Bidang B3 ON A.Kd_Urusan = B3.Kd_Urusan AND A.Kd_Bidang = B3.Kd_Bidang
INNER JOIN Ref_Urusan B4 ON A.Kd_Urusan = B4.Kd_Urusan
INNER JOIN Ta_Kegiatan C ON A.Tahun = C.Tahun AND 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 AND A.Kd_Prog = C.Kd_Prog AND A.Id_Prog = C.Id_Prog AND A.Kd_Keg = C.Kd_Keg
INNER JOIN Ta_Program D ON A.Tahun = D.Tahun AND 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 AND A.Kd_Prog = D.Kd_Prog AND A.Id_Prog = D.Id_Prog
INNER JOIN Ref_Akrual_5 E ON A.Kd_Akrual_1 = E.Kd_Akrual_1 AND A.Kd_Akrual_2 = E.Kd_Akrual_2 AND A.Kd_Akrual_3 = E.Kd_Akrual_3 AND A.Kd_Akrual_4 = E.Kd_Akrual_4 AND A.Kd_Akrual_5 = E.Kd_Akrual_5
INNER JOIN Ref_Akrual_4 F ON A.Kd_Akrual_1 = F.Kd_Akrual_1 AND A.Kd_Akrual_2 = F.Kd_Akrual_2 AND A.Kd_Akrual_3 = F.Kd_Akrual_3 AND A.Kd_Akrual_4 = F.Kd_Akrual_4
INNER JOIN Ref_Akrual_3 G ON A.Kd_Akrual_1 = G.Kd_Akrual_1 AND A.Kd_Akrual_2 = G.Kd_Akrual_2 AND A.Kd_Akrual_3 = G.Kd_Akrual_3
INNER JOIN Ref_Akrual_2 H ON A.Kd_Akrual_1 = H.Kd_Akrual_1 AND A.Kd_Akrual_2 = H.Kd_Akrual_2
INNER JOIN Ref_Akrual_1 I ON A.Kd_Akrual_1 = I.Kd_Akrual_1

ORDER BY A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, A.Kd_Prog, A.Id_Prog, A.Kd_Keg, A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5

2 pemikiran pada “Mengolah Data SIMDA Keuangan Menggunakan Pivot Table pada Excel”

Tinggalkan Balasan

Situs ini menggunakan Akismet untuk mengurangi spam. Pelajari bagaimana data komentar Anda diproses.