{"id":685,"date":"2016-05-19T16:05:09","date_gmt":"2016-05-19T08:05:09","guid":{"rendered":"http:\/\/blog.noekaz.com\/?p=685"},"modified":"2017-03-25T10:09:46","modified_gmt":"2017-03-25T02:09:46","slug":"mengolah-data-simda-keuangan-menggunakan-pivot-table-pada-excel","status":"publish","type":"post","link":"https:\/\/blog.nukasrama.com\/index.php\/kerjaan\/simda-keuangan\/mengolah-data-simda-keuangan-menggunakan-pivot-table-pada-excel\/","title":{"rendered":"Mengolah Data SIMDA Keuangan Menggunakan Pivot Table pada Excel"},"content":{"rendered":"

Sebagai pengguna SIMDA Keuangan BPKP, pada saat-saat tertentu\u00a0saya sering merasa kesulitan dalam\u00a0menghasilkan data\u00a0dengan 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\u00a0mudah untuk membuatnya? Dengan bantuan query dan Excel semua dapat\u00a0dengan mudah dibuat.<\/p>\n

Berikut\u00a0query yang saya gunakan untuk membuat pivot table LO, LRA Permendagri 13, LRA Permendagri 64. Query ini akan menghasilkan yang berisi data\u00a0unit, sub unit, program, kegiatan,\u00a0\u00a0rekening, anggaran, dan realisasi.<\/p>\n

Query LO:<\/strong><\/p>\n

DECLARE @Tahun varchar(4), @T1 datetime, @T2 datetime\r\nSET @Tahun = '2015'\r\nSET @T1 = @Tahun + '0101'\r\nSET @T2 = @Tahun + '1231'\r\n\r\nSELECT \r\n 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,\r\n 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,\r\n RIGHT('0'+CAST(A.Kd_Prog AS VARCHAR),2) + ' - ' + D.Ket_Program AS Program,\r\n RIGHT('00'+CAST(A.Kd_Prog AS VARCHAR),3) + ' - ' + C.Ket_Kegiatan AS Kegiatan,\r\n CAST(A.Kd_Akrual_1 AS VARCHAR) + ' - ' + I.Nm_Akrual_1 AS Rekening_1,\r\n CAST(A.Kd_Akrual_1 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_2 AS VARCHAR) +' - ' + H.Nm_Akrual_2 AS Rekening_2,\r\n 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,\r\n 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,\r\n 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,\r\n A.Realisasi\r\nFROM (\r\n\r\nSELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, \r\n A.Kd_Prog, A.ID_Prog, A.Kd_Keg, \r\n A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5,\r\n SUM(A.Realisasi) AS Realisasi\r\n FROM (\r\n\r\nSELECT B.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, \r\n A.Kd_Prog, A.Id_Prog, A.Kd_Keg, \r\n A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5, \r\n SUM(CASE C.SaldoNorm\r\n WHEN 'D' THEN A.Debet - A.Kredit\r\n ELSE A.Kredit - A.Debet\r\n END) AS Realisasi\r\n FROM Ta_JurnalSemuaAk_Rinc A \r\n INNER JOIN Ta_JurnalSemuaAk B ON A.Tahun = B.Tahun AND A.Kd_Source = B.Kd_Source AND A.No_Bukti = B.No_Bukti \r\n 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\r\n WHERE ((CASE A.Kd_SKPD\r\n WHEN 1 THEN B.Posting\r\n ELSE B.Posting_SKPKD\r\n END) = 1) AND (B.Tgl_Bukti BETWEEN @T1 AND @T2)\r\n AND (Kd_Jurnal NOT IN (8, 10))\r\n AND (B.Tahun = @Tahun)\r\n GROUP BY B.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, \r\n A.Kd_Prog, A.Id_Prog, A.Kd_Keg, \r\n A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5\r\n\r\n) A \r\n WHERE A.Kd_Akrual_1 IN (8,9)\r\n 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\r\n\r\n) A\r\n\r\nINNER 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\r\nINNER 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\r\nINNER JOIN Ref_Bidang B3 ON A.Kd_Urusan = B3.Kd_Urusan AND A.Kd_Bidang = B3.Kd_Bidang\r\nINNER JOIN Ref_Urusan B4 ON A.Kd_Urusan = B4.Kd_Urusan\r\nINNER 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\r\nINNER 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\r\nINNER 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\r\nINNER 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\r\nINNER 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\r\nINNER JOIN Ref_Akrual_2 H ON A.Kd_Akrual_1 = H.Kd_Akrual_1 AND A.Kd_Akrual_2 = H.Kd_Akrual_2\r\nINNER JOIN Ref_Akrual_1 I ON A.Kd_Akrual_1 = I.Kd_Akrual_1\r\n\r\nORDER 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<\/pre>\n
 <\/p>\n
Query LRA Permendagri 13:<\/strong><\/p>\n
DECLARE @Tahun varchar(4), @T1 datetime, @T2 datetime\r\nSET @Tahun = '2016'\r\nSET @T1 = @Tahun + '1231'\r\nSET @T2 = @Tahun + '0101'\r\n\r\nSELECT \r\n 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,\r\n 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,\r\n RIGHT('0'+CAST(A.Kd_Prog AS VARCHAR),2) + ' - ' + D.Ket_Program AS Program,\r\n RIGHT('00'+CAST(A.Kd_Prog AS VARCHAR),3) + ' - ' + C.Ket_Kegiatan AS Kegiatan,\r\n CAST(A.Kd_Rek_1 AS VARCHAR) + ' - ' + I.Nm_Rek_1 AS Rekening_1,\r\n CAST(A.Kd_Rek_1 AS VARCHAR) +'.'+ CAST(A.Kd_Rek_2 AS VARCHAR) + ' - ' + H.Nm_Rek_2 AS Rekening_2,\r\n 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,\r\n 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,\r\n 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,\r\n A.Anggaran, A.Realisasi\r\nFROM (\r\n\r\nSELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, \r\n A.Kd_Prog, A.ID_Prog, A.Kd_Keg, \r\n A.Kd_Rek_1, A.Kd_Rek_2, A.Kd_Rek_3, A.Kd_Rek_4, A.Kd_Rek_5,\r\n SUM(A.Anggaran) AS Anggaran, SUM(A.Realisasi) AS Realisasi\r\n FROM (\r\n\r\nSELECT B.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, \r\n CASE\r\n WHEN (A.Kd_Rek_1 = 5) AND (A.Kd_Rek_2 = 2) THEN A.Kd_Prog\r\n ELSE 0\r\n END AS Kd_Prog, \r\n CASE\r\n WHEN (A.Kd_Rek_1 = 5) AND (A.Kd_Rek_2 = 2) THEN A.Id_Prog\r\n ELSE 0\r\n END AS Id_Prog,\r\n CASE\r\n WHEN (A.Kd_Rek_1 = 5) AND (A.Kd_Rek_2 = 2) THEN A.Kd_Keg\r\n ELSE 0\r\n 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, \r\n SUM(CASE C.SaldoNorm\r\n WHEN 'D' THEN A.Debet - A.Kredit\r\n ELSE A.Kredit - A.Debet\r\n END) AS Realisasi\r\n FROM Ta_JurnalSemua_Rinc A INNER JOIN\r\n Ta_JurnalSemua B ON A.Tahun = B.Tahun AND A.Kd_Source = B.Kd_Source AND A.No_Bukti = B.No_Bukti INNER JOIN\r\n 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\r\n WHERE ((CASE A.Kd_SKPD\r\n WHEN 1 THEN B.Posting\r\n ELSE B.Posting_SKPKD\r\n END) = 1) \r\n AND (B.Tgl_Bukti BETWEEN @T2 AND @T1)\r\n AND (NOT ((A.Kd_Rek_1 = 6) AND (A.Kd_Rek_2 IN (3, 4))))\r\n AND (B.Tahun = @Tahun)\r\n 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\r\n\r\nUNION ALL\r\n\r\nSELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub,\r\n CASE\r\n WHEN (A.Kd_Rek_1 = 5) AND (A.Kd_Rek_2 = 2) THEN A.Kd_Prog\r\n ELSE 0\r\n END AS Kd_Prog,\r\n CASE\r\n WHEN (A.Kd_Rek_1 = 5) AND (A.Kd_Rek_2 = 2) THEN A.Id_Prog\r\n ELSE 0\r\n END AS Id_Prog,\r\n CASE\r\n WHEN (A.Kd_Rek_1 = 5) AND (A.Kd_Rek_2 = 2) THEN A.Kd_Keg\r\n ELSE 0\r\n END AS Kd_Keg,\r\n 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\r\n FROM Ta_RASK_Arsip A\r\n 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) \r\n 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\r\n\r\n) A \r\n WHERE A.Kd_Rek_1 IN (4,5,6)\r\n 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\r\n\r\n) A\r\n\r\nINNER 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\r\nINNER 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\r\nINNER JOIN Ref_Bidang B3 ON A.Kd_Urusan = B3.Kd_Urusan AND A.Kd_Bidang = B3.Kd_Bidang\r\nINNER JOIN Ref_Urusan B4 ON A.Kd_Urusan = B4.Kd_Urusan\r\nINNER 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\r\nINNER 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\r\nINNER 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\r\nINNER 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\r\nINNER 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\r\nINNER JOIN Ref_Rek_2 H ON A.Kd_Rek_1 = H.Kd_Rek_1 AND A.Kd_Rek_2 = H.Kd_Rek_2\r\nINNER JOIN Ref_Rek_1 I ON A.Kd_Rek_1 = I.Kd_Rek_1<\/pre>\n
 <\/p>\n
Query LRA Permendagri 64:<\/strong><\/p>\n
DECLARE @Tahun varchar(4), @T1 datetime, @T2 datetime\r\nSET @Tahun = '2016'\r\nSET @T1 = @Tahun + '1231'\r\nSET @T2 = @Tahun + '0101'\r\n\r\nSELECT \r\n 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,\r\n 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,\r\n RIGHT('0'+CAST(A.Kd_Prog AS VARCHAR),2) + ' - ' + D.Ket_Program AS Program,\r\n RIGHT('00'+CAST(A.Kd_Prog AS VARCHAR),3) + ' - ' + C.Ket_Kegiatan AS Kegiatan,\r\n CAST(A.Kd_Akrual_1 AS VARCHAR) + ' - ' + I.Nm_Akrual_1 AS Rekening_1,\r\n CAST(A.Kd_Akrual_1 AS VARCHAR) +'.'+ CAST(A.Kd_Akrual_2 AS VARCHAR) +' - ' + H.Nm_Akrual_2 AS Rekening_2,\r\n 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,\r\n 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,\r\n 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,\r\n A.Anggaran, A.Realisasi\r\nFROM (\r\n\r\nSELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, \r\n A.Kd_Prog, A.ID_Prog, A.Kd_Keg, \r\n A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5,\r\n SUM(A.Anggaran) AS Anggaran, SUM(A.Realisasi) AS Realisasi\r\n FROM (\r\n\r\nSELECT B.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, \r\n A.Kd_Prog, A.Id_Prog, A.Kd_Keg, \r\n A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5, 0 AS Anggaran, \r\n SUM(CASE C.SaldoNorm\r\n WHEN 'D' THEN A.Debet - A.Kredit\r\n ELSE A.Kredit - A.Debet\r\n END) AS Realisasi\r\n FROM Ta_JurnalSemuaAk_Rinc A \r\n INNER JOIN Ta_JurnalSemuaAk B ON A.Tahun = B.Tahun AND A.Kd_Source = B.Kd_Source AND A.No_Bukti = B.No_Bukti \r\n 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\r\n WHERE ((CASE A.Kd_SKPD\r\n WHEN 1 THEN B.Posting\r\n ELSE B.Posting_SKPKD\r\n END) = 1) AND (B.Tgl_Bukti BETWEEN @T2 AND @T1)\r\n AND (Kd_Jurnal NOT IN (8, 10))\r\n AND (B.Tahun = @Tahun)\r\n GROUP BY B.Tahun, B.Kd_Urusan, B.Kd_Bidang, B.Kd_Unit, B.Kd_Sub, \r\n A.Kd_Prog, A.Id_Prog, A.Kd_Keg, \r\n A.Kd_Akrual_1, A.Kd_Akrual_2, A.Kd_Akrual_3, A.Kd_Akrual_4, A.Kd_Akrual_5\r\n\r\nUNION ALL\r\n\r\nSELECT A.Tahun, A.Kd_Urusan, A.Kd_Bidang, A.Kd_Unit, A.Kd_Sub, \r\n A.Kd_Prog, A.Id_Prog, A.Kd_Keg,\r\n 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\r\n FROM Ta_RASK_Arsip A\r\n 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\r\n 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) \r\n 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\r\n\r\n) A \r\n WHERE A.Kd_Akrual_1 = 5 AND A.Kd_Akrual_2 = 2 --AND A.Kd_Akrual_3 = 4\r\n 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\r\n\r\n) A\r\n\r\nINNER 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\r\nINNER 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\r\nINNER JOIN Ref_Bidang B3 ON A.Kd_Urusan = B3.Kd_Urusan AND A.Kd_Bidang = B3.Kd_Bidang\r\nINNER JOIN Ref_Urusan B4 ON A.Kd_Urusan = B4.Kd_Urusan\r\nINNER 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\r\nINNER 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\r\nINNER 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\r\nINNER 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\r\nINNER 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\r\nINNER JOIN Ref_Akrual_2 H ON A.Kd_Akrual_1 = H.Kd_Akrual_1 AND A.Kd_Akrual_2 = H.Kd_Akrual_2\r\nINNER JOIN Ref_Akrual_1 I ON A.Kd_Akrual_1 = I.Kd_Akrual_1\r\n\r\nORDER 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<\/pre>\n","protected":false},"excerpt":{"rendered":"
Sebagai pengguna SIMDA Keuangan BPKP, pada saat-saat tertentu\u00a0saya sering merasa kesulitan dalam\u00a0menghasilkan data\u00a0dengan 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, … Baca Selengkapnya<\/a><\/p>\n","protected":false},"author":1,"featured_media":1648,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[103],"tags":[92,93,84,94],"class_list":["post-685","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-simda-keuangan","tag-excel","tag-pivot-table","tag-simda","tag-sql"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/05\/msms.png?fit=1116%2C921&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3MFVG-b3","jetpack-related-posts":[{"id":915,"url":"https:\/\/blog.nukasrama.com\/index.php\/kerjaan\/simda-keuangan\/apa-yang-terjadi-di-dalam-database-simda-keuangan-part-1\/","url_meta":{"origin":685,"position":0},"title":"Apa yang Terjadi di Dalam Database SIMDA Keuangan? Part 1","author":"noekaz","date":"20 Mei 2016","format":false,"excerpt":"Tulisan ini saya tulis hanya ingin\u00a0berbagi kepada\u00a0para administrator yang baru\u00a0berkecimpung dalam dunia per-SIMDA-an. Pengetahuan saya tentang apa yang terjadi di balik database SIMDA masih belum lengkap, masih perlu banyak belajar lagi.\u00a0Apa saja yang akan saya bahas di sini? Sesuai judulnya, saya akan membedah proses yang terjadi pada database SIMDA terutama\u2026","rel":"","context":"dalam "SIMDA Keuangan"","block_context":{"text":"SIMDA Keuangan","link":"https:\/\/blog.nukasrama.com\/index.php\/category\/kerjaan\/simda-keuangan\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/05\/simdakeu.jpg?fit=1200%2C656&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/05\/simdakeu.jpg?fit=1200%2C656&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/05\/simdakeu.jpg?fit=1200%2C656&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/05\/simdakeu.jpg?fit=1200%2C656&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/05\/simdakeu.jpg?fit=1200%2C656&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":1725,"url":"https:\/\/blog.nukasrama.com\/index.php\/kerjaan\/simda-keuangan\/cara-lain-input-saldo-awal-simda-keuangan-dan-mapping-opd-lama-ke-opd-baru\/","url_meta":{"origin":685,"position":1},"title":"Cara Lain Input Saldo Awal SIMDA Keuangan dan Mapping OPD Lama ke OPD Baru","author":"noekaz","date":"30 Juli 2017","format":false,"excerpt":"Menginput saldo awal pada SIMDA Keuangan merupakan rutinitas tahunan yang jika dikerjakan secara manual membutuhkan waktu yang lumayan lama. Apalagi kalau dikerjakan ketika hati sedang galau karena cicilan utang belum lunas. Memang SIMDA Keuangan sudah menyediakan menu untuk export-import saldo awal. Namun saya sendiri tidak pernah menggunakannya. Saya lebih suka\u2026","rel":"","context":"dalam "SIMDA Keuangan"","block_context":{"text":"SIMDA Keuangan","link":"https:\/\/blog.nukasrama.com\/index.php\/category\/kerjaan\/simda-keuangan\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1656,"url":"https:\/\/blog.nukasrama.com\/index.php\/kerjaan\/simda-keuangan\/kumpulan-query-sql-simda-keuangan-selama-pemeriksaan-bpk\/","url_meta":{"origin":685,"position":2},"title":"Kumpulan Query SQL SIMDA Keuangan Selama Pemeriksaan BPK","author":"noekaz","date":"30 Maret 2017","format":false,"excerpt":"Selama pemeriksaan Laporan Keuangan Pemerintah Daerah oleh BPK, saya sering dimintai berbagai jenis data. Pemeriksa dari BPK menyiapkan format yang\u00a0dan saya sebagai admin SIMDA\u00a0Keuangan 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,\u2026","rel":"","context":"dalam "SIMDA Keuangan"","block_context":{"text":"SIMDA Keuangan","link":"https:\/\/blog.nukasrama.com\/index.php\/category\/kerjaan\/simda-keuangan\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2017\/03\/excel-pivot.jpg?fit=785%2C545&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2017\/03\/excel-pivot.jpg?fit=785%2C545&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2017\/03\/excel-pivot.jpg?fit=785%2C545&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2017\/03\/excel-pivot.jpg?fit=785%2C545&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":1266,"url":"https:\/\/blog.nukasrama.com\/index.php\/kerjaan\/personal-project\/simda-bmd-berbasis-web\/","url_meta":{"origin":685,"position":3},"title":"Personal Project: SIMDA BMD Berbasis Web","author":"noekaz","date":"27 Mei 2016","format":false,"excerpt":"Kembali lagi dengan proyek iseng saya yang lain. Kali ini saya\u00a0membuat web interface untuk SIMDA BMD. Tulisan ini masih terkait dengan\u00a0tulisan saya sebelumnya. Di sana dijelaskan kenapa saya menyukai web based application. Saya merasa web based application memiliki lebih banyak keunggulannya, entah pendapat saya ini benar atau tidak. Karena tulisan\u2026","rel":"","context":"dalam "Personal Project"","block_context":{"text":"Personal Project","link":"https:\/\/blog.nukasrama.com\/index.php\/category\/kerjaan\/personal-project\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/05\/bmdweb-1024x451.png?fit=640%2C282&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/05\/bmdweb-1024x451.png?fit=640%2C282&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/05\/bmdweb-1024x451.png?fit=640%2C282&ssl=1&resize=525%2C300 1.5x"},"classes":[]},{"id":25,"url":"https:\/\/blog.nukasrama.com\/index.php\/kerjaan\/personal-project\/simda-keuangan-berbasis-web\/","url_meta":{"origin":685,"position":4},"title":"Personal Project: SIMDA Keuangan Berbasis Web","author":"noekaz","date":"30 April 2016","format":false,"excerpt":"Sebagai seorang web programmer, jika dihadapkan pada sebuah pilihan, antara\u00a0desktop application atau web based application, tentu saja saya lebih memilih web based application. Dengan keunggulan\u00a0bisa diakses dari mana saja (PC, laptop, tablet, smartphone) lewat browser\u00a0selama ada koneksi internet. Hal ini mengingatkan saya pada\u00a0aplikasi yang setiap hari saya tangani di kantor,\u2026","rel":"","context":"dalam "Personal Project"","block_context":{"text":"Personal Project","link":"https:\/\/blog.nukasrama.com\/index.php\/category\/kerjaan\/personal-project\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/04\/simdakeuweb.png?fit=1200%2C557&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/04\/simdakeuweb.png?fit=1200%2C557&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/04\/simdakeuweb.png?fit=1200%2C557&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/04\/simdakeuweb.png?fit=1200%2C557&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/04\/simdakeuweb.png?fit=1200%2C557&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":27,"url":"https:\/\/blog.nukasrama.com\/index.php\/kerjaan\/ocehan\/perlu-nggak-sih-bagan-akun-standar-yang-sama-antara-penganggaran-penatausahaan-dan-pelaporan\/","url_meta":{"origin":685,"position":5},"title":"Perlu Nggak Sih Bagan Akun Standar yang Sama dari Penganggaran, Penatausahaan, sampai dengan Pelaporan?","author":"noekaz","date":"28 April 2016","format":false,"excerpt":"Seharusnya tulisan ini saya tulis di akhir tahun 2014\u00a0yang lalu ketika Pemerintah Daerah bersiap-siap untuk menerapkan Standar Akuntansi Berbasis Akrual di tahun 2015. (Meskipun setahu saya\u00a0beberapa daerah sudah melaksanakan selama beberapa tahun) Tapi tak apalah, toh isi dari tulisan ini masih relevan untuk saya bahas. Namun dengan satu catatan penting\u2026","rel":"","context":"dalam "Ocehan"","block_context":{"text":"Ocehan","link":"https:\/\/blog.nukasrama.com\/index.php\/category\/kerjaan\/ocehan\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/blog.nukasrama.com\/wp-content\/uploads\/2016\/04\/Permendagri_No._64_Tahun_2013.jpg?fit=424%2C600&ssl=1&resize=350%2C200","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/blog.nukasrama.com\/index.php\/wp-json\/wp\/v2\/posts\/685","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.nukasrama.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.nukasrama.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.nukasrama.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.nukasrama.com\/index.php\/wp-json\/wp\/v2\/comments?post=685"}],"version-history":[{"count":0,"href":"https:\/\/blog.nukasrama.com\/index.php\/wp-json\/wp\/v2\/posts\/685\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.nukasrama.com\/index.php\/wp-json\/wp\/v2\/media\/1648"}],"wp:attachment":[{"href":"https:\/\/blog.nukasrama.com\/index.php\/wp-json\/wp\/v2\/media?parent=685"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.nukasrama.com\/index.php\/wp-json\/wp\/v2\/categories?post=685"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.nukasrama.com\/index.php\/wp-json\/wp\/v2\/tags?post=685"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}