Data JSON RKA
Kabupaten Magetan

SQL untuk select total rincian per kelompok belanja (#)

    SELECT 
        k.kode_urusan,
        k.nama_urusan,
        k.kode_bidang_urusan,
        k.nama_bidang_urusan,
        k.kode_program,
        k.nama_program,
        k.kode_giat,
        k.nama_giat,
        k.kode_skpd,
        k.nama_skpd,
        k.kode_sub_skpd,
        k.nama_sub_skpd,
        k.kode_sub_giat,
        k.nama_sub_giat,
        r.subs_bl_teks,
        sum(r.rincian) as rincian,
        coalesce(ms.id_dana, 281) as id_dana,
        coalesce(ms.nama_dana, '[DANA UMUM] - PENDAPATAN ASLI DAERAH (PAD)') as nama_dana
    FROM data_sub_keg_bl as k 
    INNER JOIN data_rka as r on k.kode_sbl=r.kode_sbl 
        and r.active=k.active 
        and r.tahun_anggaran=k.tahun_anggaran 
    LEFT JOIN data_mapping_sumberdana as s on r.id_rinci_sub_bl=s.id_rinci_sub_bl 
        and s.active=k.active 
        and s.tahun_anggaran=k.tahun_anggaran 
    LEFT JOIN data_sumber_dana as ms on ms.id_dana=s.id_sumber_dana 
        and ms.tahun_anggaran=k.tahun_anggaran 
    WHERE
        k.tahun_anggaran=2023
        AND k.active=1
        AND k.id_sub_skpd IN (2185,2186,2326,2304,2305,2306,2307,2308,2309,2310,2311,2312,2313,2314,2315,2316,2317,2318,2319,2320,2321,2322,2323,2324,2325,2934,6595,2187,4051,4052,4053,4054,4055,4056,4057,4058,4059,2222,2224,2225,2226,2227,2228,2229,2230,2231,2232,2233,2234,2235,2236,2237,2238,2239,2240,2241,2242,2266,2267,2268,2269,2271,2272,2273,2274,2275,2261,2263,2264,2265,2262,2243,2276,2277,2278,2279,2280,2281,2282,2283,2284,2244,2245,2285,2246,2286,2287,2247,2248,2288,2249,2289,2250,2290,2291,2292,2251,2293,2294,2252,2253,2295,2296,2254,2255,2297,2256,2298,2299,2300,2257,2301,2258,2259,2302,2260,2303)
    GROUP BY k.kode_sub_skpd ASC, k.kode_sub_giat, r.subs_bl_teks
    ORDER BY k.kode_sub_skpd ASC, k.kode_sub_giat ASC
    

SQL untuk select total rincian per kode akun dan sumber dana untuk keperluan SPD FMIS

Get Data Download Excel

    SELECT 
        k.kode_urusan,
        k.nama_urusan,
        k.kode_bidang_urusan,
        k.nama_bidang_urusan,
        k.kode_program,
        k.nama_program,
        k.kode_giat,
        k.nama_giat,
        k.kode_skpd,
        k.nama_skpd,
        k.kode_sub_skpd,
        k.nama_sub_skpd,
        k.kode_sub_giat,
        k.nama_sub_giat,
        r.kode_akun,
        r.nama_akun,
        sum(r.rincian) as rincian,
        coalesce(ms.id_dana, 281) as id_dana,
        coalesce(ms.nama_dana, '[DANA UMUM] - PENDAPATAN ASLI DAERAH (PAD)') as nama_dana
    FROM data_sub_keg_bl as k 
    INNER JOIN data_rka as r on k.kode_sbl=r.kode_sbl 
        and r.active=k.active 
        and r.tahun_anggaran=k.tahun_anggaran 
    LEFT JOIN data_mapping_sumberdana as s on r.id_rinci_sub_bl=s.id_rinci_sub_bl 
        and s.active=k.active 
        and s.tahun_anggaran=k.tahun_anggaran 
    LEFT JOIN data_sumber_dana as ms on ms.id_dana=s.id_sumber_dana 
        and ms.tahun_anggaran=k.tahun_anggaran 
    WHERE
        k.tahun_anggaran=2023
        AND k.active=1
        AND k.id_sub_skpd IN (2185,2186,2326,2304,2305,2306,2307,2308,2309,2310,2311,2312,2313,2314,2315,2316,2317,2318,2319,2320,2321,2322,2323,2324,2325,2934,6595,2187,4051,4052,4053,4054,4055,4056,4057,4058,4059,2222,2224,2225,2226,2227,2228,2229,2230,2231,2232,2233,2234,2235,2236,2237,2238,2239,2240,2241,2242,2266,2267,2268,2269,2271,2272,2273,2274,2275,2261,2263,2264,2265,2262,2243,2276,2277,2278,2279,2280,2281,2282,2283,2284,2244,2245,2285,2246,2286,2287,2247,2248,2288,2249,2289,2250,2290,2291,2292,2251,2293,2294,2252,2253,2295,2296,2254,2255,2297,2256,2298,2299,2300,2257,2301,2258,2259,2302,2260,2303)
    GROUP BY k.kode_sub_skpd ASC, k.kode_sub_giat, r.kode_akun
    ORDER BY k.kode_sub_skpd ASC, k.kode_sub_giat ASC
    

SQL untuk select semua rincian

    SELECT
        k.id,
        k.id_sub_skpd,
        k.id_lokasi,
        k.id_label_kokab,
        k.nama_dana,
        k.no_sub_giat,
        k.kode_giat,
        k.id_program,
        k.nama_lokasi,
        k.waktu_akhir,
        k.pagu_n_lalu,
        k.id_urusan,
        k.id_unik_sub_bl,
        k.id_sub_giat,
        k.label_prov,
        k.kode_program,
        k.kode_sub_giat,
        k.no_program,
        k.kode_urusan,
        k.kode_bidang_urusan,
        k.nama_program,
        k.target_4,
        k.target_5,
        k.id_bidang_urusan,
        k.nama_bidang_urusan,
        k.target_3,
        k.no_giat,
        k.id_label_prov,
        k.waktu_awal,
        k.pagumurni,
        k.pagu,
        k.pagu_simda,
        k.output_sub_giat,
        k.sasaran,
        k.indikator,
        k.id_dana,
        k.nama_sub_giat,
        k.pagu_n_depan,
        k.satuan,
        k.id_rpjmd,
        k.id_giat,
        k.id_label_pusat,
        k.nama_giat,
        k.kode_skpd,
        k.nama_skpd,
        k.kode_sub_skpd,
        k.id_skpd,
        k.id_sub_bl,
        k.nama_sub_skpd,
        k.target_1,
        k.nama_urusan,
        k.target_2,
        k.label_kokab,
        k.label_pusat,
        k.pagu_keg,
        k.pagu_fmis,
        k.id_bl,
        k.kode_bl,
        k.kode_sbl,
        k.active,
        k.update_at,
        k.tahun_anggaran,
        r.id as id_rka,
        r.created_user,
        r.createddate,
        r.createdtime,
        r.harga_satuan,
        r.harga_satuan_murni,
        r.id_daerah,
        r.id_rinci_sub_bl,
        r.id_standar_nfs,
        r.is_locked,
        r.jenis_bl,
        r.ket_bl_teks,
        r.substeks,
        r.id_dana,
        r.nama_dana,
        r.is_paket,
        r.kode_dana,
        r.subtitle_teks,
        r.kode_akun,
        r.koefisien,
        r.koefisien_murni,
        r.lokus_akun_teks,
        r.nama_akun,
        r.nama_komponen,
        r.spek_komponen,
        r.satuan,
        r.spek,
        r.sat1,
        r.sat2,
        r.sat3,
        r.sat4,
        r.volum1,
        r.volum2,
        r.volum3,
        r.volum4,
        r.volume,
        r.volume_murni,
        r.subs_bl_teks,
        r.total_harga,
        r.rincian,
        r.rincian_murni,
        r.totalpajak,
        r.pajak,
        r.pajak_murni,
        r.updated_user,
        r.updateddate,
        r.updatedtime,
        r.user1,
        r.user2,
        r.active,
        r.update_at,
        r.tahun_anggaran,
        r.idbl,
        r.idsubbl,
        r.kode_bl,
        r.kode_sbl,
        r.id_prop_penerima,
        r.id_camat_penerima,
        r.id_kokab_penerima,
        r.id_lurah_penerima,
        r.id_penerima,
        r.idkomponen,
        r.idketerangan,
        r.idsubtitle,
        coalesce(ms.id_dana, 281) as id_dana,
        coalesce(ms.nama_dana, '[DANA UMUM] - PENDAPATAN ASLI DAERAH (PAD)') as nama_dana
    FROM data_sub_keg_bl as k
    INNER JOIN data_rka as r on k.kode_sbl=r.kode_sbl
        and r.active=k.active
        and r.tahun_anggaran=k.tahun_anggaran
    LEFT JOIN data_mapping_sumberdana as s on r.id_rinci_sub_bl=s.id_rinci_sub_bl
        and s.active=k.active
        and s.tahun_anggaran=k.tahun_anggaran
    LEFT JOIN data_sumber_dana as ms on ms.id_dana=s.id_sumber_dana
        and ms.tahun_anggaran=k.tahun_anggaran
    WHERE
        k.tahun_anggaran=2023
        AND k.active=1
        AND k.id_sub_skpd IN (2185,2186,2326,2304,2305,2306,2307,2308,2309,2310,2311,2312,2313,2314,2315,2316,2317,2318,2319,2320,2321,2322,2323,2324,2325,2934,6595,2187,4051,4052,4053,4054,4055,4056,4057,4058,4059,2222,2224,2225,2226,2227,2228,2229,2230,2231,2232,2233,2234,2235,2236,2237,2238,2239,2240,2241,2242,2266,2267,2268,2269,2271,2272,2273,2274,2275,2261,2263,2264,2265,2262,2243,2276,2277,2278,2279,2280,2281,2282,2283,2284,2244,2245,2285,2246,2286,2287,2247,2248,2288,2249,2289,2250,2290,2291,2292,2251,2293,2294,2252,2253,2295,2296,2254,2255,2297,2256,2298,2299,2300,2257,2301,2258,2259,2302,2260,2303)
    

SQL untuk select kontrol realisasi dan P3DN

Get Data Download Excel

    SELECT 
        k.nama_skpd as OPD,
        k.nama_program as PROGRAM,
        k.nama_giat as KEGIATAN,
        k.nama_sub_giat as "SUB KEGIATAN",
        r.nama_akun as BELANJA,
        r.subs_bl_teks as "[#]",
        r.ket_bl_teks as "[-]",
        concat(r.nama_komponen, ' ', r.spek_komponen) as "SPESIFIKASI BELANJA",
        ms.nama_dana as "SUMBER DANA",
        r.rincian_murni as "ANGGARAN SEBELUM PERUBAHAN",
        r.rincian as "ANGGARAN SETELAH PERUBAHAN",
        '' as "REALISASI (Rp)",
        '' as "Uraian SPM",
        '' as "SISA",
        '' as "KETERANGAN"
    FROM data_sub_keg_bl as k 
    INNER JOIN data_rka as r on k.kode_sbl=r.kode_sbl 
        and r.active=k.active 
        and r.tahun_anggaran=k.tahun_anggaran 
    LEFT JOIN data_mapping_sumberdana as s on r.id_rinci_sub_bl=s.id_rinci_sub_bl 
        and s.active=k.active 
        and s.tahun_anggaran=k.tahun_anggaran 
    LEFT JOIN data_sumber_dana as ms on ms.id_dana=s.id_sumber_dana 
        and ms.tahun_anggaran=k.tahun_anggaran 
    LEFT JOIN data_realisasi_akun as a on k.kode_sbl=a.kode_sbl 
        and a.tahun_anggaran=k.tahun_anggaran 
    LEFT JOIN data_sub_keg_bl as u on k.kode_sbl=u.kode_sbl 
        and u.tahun_anggaran=k.tahun_anggaran
    WHERE
        k.tahun_anggaran=2023
        AND k.active=1
        AND k.id_sub_skpd IN (2185,2186,2326,2304,2305,2306,2307,2308,2309,2310,2311,2312,2313,2314,2315,2316,2317,2318,2319,2320,2321,2322,2323,2324,2325,2934,6595,2187,4051,4052,4053,4054,4055,4056,4057,4058,4059,2222,2224,2225,2226,2227,2228,2229,2230,2231,2232,2233,2234,2235,2236,2237,2238,2239,2240,2241,2242,2266,2267,2268,2269,2271,2272,2273,2274,2275,2261,2263,2264,2265,2262,2243,2276,2277,2278,2279,2280,2281,2282,2283,2284,2244,2245,2285,2246,2286,2287,2247,2248,2288,2249,2289,2250,2290,2291,2292,2251,2293,2294,2252,2253,2295,2296,2254,2255,2297,2256,2298,2299,2300,2257,2301,2258,2259,2302,2260,2303)
    GROUP BY k.kode_sub_skpd ASC, k.kode_sub_giat, r.subs_bl_teks
    ORDER BY k.kode_sub_skpd ASC, k.kode_sub_giat ASC