realisasi_pad.sql
13.6 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
select tglcetak, bulan, substring(rekeningkd, 1,25) as kode, rekeningnm as nama, levelid,issummary,
sum(murni) murni, sum(perubahan) perubahan, sum(realisasi_lalu) realisasi_lalu, sum(realisasi) realisasi
, d.nip as dnip, d.pejabatnm as dpejabatnm, d.jabatan as djabatan, d.pangkat as dpangkat
, e.nip as enip, e.pejabatnm as epejabatnm, e.jabatan as ejabatan, e.pangkat as epangkat
from (
with
r as (
select id, rekeningkd, rekeningnm, levelid, issummary, enabled from tblrekening
),
a5 as (
select r.rekeningkd,
substring(r.rekeningkd,1,1)||'%' rlevel0,
substring(r.rekeningkd,1,2)||'%' rlevel1,
substring(r.rekeningkd,1,3)||'%' rlevel2,
substring(r.rekeningkd,1,6)||'%' rlevel3,
substring(r.rekeningkd,1,8)||'%' rlevel4,
substring(r.rekeningkd,1,12)||'%' rlevel5,
sum(a.murni) murni, sum(a.perubahan) perubahan
from rka_kegiatan_line a
left join r on r.id=a.rekening_id
where a.tahun::text='{tahun}'
group by r.rekeningkd
),
a4 as (
select r.rekeningkd, sum(a.murni) murni, sum(a.perubahan) perubahan
from a5 a
left join r on r.rekeningkd ilike a.rlevel4
where r.levelid=4 and r.issummary=1
group by r.rekeningkd
),
a3 as (
select r.rekeningkd, sum(a.murni) murni, sum(a.perubahan) perubahan
from a5 a
left join r on r.rekeningkd ilike a.rlevel3
where r.levelid=3 and r.issummary=1
group by r.rekeningkd
),
a2 as (
select r.rekeningkd, sum(a.murni) murni, sum(a.perubahan) perubahan
from a5 a
left join r on r.rekeningkd ilike a.rlevel2
where r.levelid=2 and r.issummary=1
group by r.rekeningkd
),
a1 as (
select r.rekeningkd, sum(a.murni) murni, sum(a.perubahan) perubahan
from a5 a
left join r on r.rekeningkd ilike a.rlevel1
where r.levelid=1 and r.issummary=1
group by r.rekeningkd
),
a0 as (
select r.rekeningkd,sum(a.murni) murni, sum(a.perubahan) perubahan
from a5 a
left join r on r.rekeningkd ilike a.rlevel0
where r.levelid=0 and r.issummary=1
group by r.rekeningkd
),
lv5 as (
select r.rekeningkd,
substring(r.rekeningkd,1,1)||'%' rlevel0,
substring(r.rekeningkd,1,2)||'%' rlevel1,
substring(r.rekeningkd,1,3)||'%' rlevel2,
substring(r.rekeningkd,1,6)||'%' rlevel3,
substring(r.rekeningkd,1,8)||'%' rlevel4,
substring(r.rekeningkd,1,12)||'%' rlevel5,
sum(
case when ss.hitung_bunga in (0,2) then ss.jml_bayar
when s.type_id in (5) then s.pajak_terhutang
when s.type_id in (13) then 0
else ss.jml_bayar-ss.bunga
end
) realisasi
from pad_sspd ss
left join pad_spt s on s.id=ss.spt_id
left join pad_pajak p on p.id=s.pajak_id
left join r on r.id=p.rekening_id
where ss.is_valid=1
and extract(month FROM ss.sspdtgl) = {bulan}
and extract(year FROM ss.sspdtgl) = {tahun}
group by r.rekeningkd
union
select r.rekeningkd,
substring(r.rekeningkd,1,1)||'%' rlevel0,
substring(r.rekeningkd,1,2)||'%' rlevel1,
substring(r.rekeningkd,1,3)||'%' rlevel2,
substring(r.rekeningkd,1,6)||'%' rlevel3,
substring(r.rekeningkd,1,8)||'%' rlevel4,
substring(r.rekeningkd,1,12)||'%' rlevel5,
sum(
case when ss.hitung_bunga in (0,2) then 0
when s.type_id in (5) then ss.jml_bayar-s.pajak_terhutang
when s.type_id in (13) then ss.jml_bayar
else ss.bunga
end
) realisasi
from pad_sspd ss
left join pad_spt s on s.id=ss.spt_id
left join pad_pajak p on p.id=s.pajak_id
left join r on r.id=p.rekdenda_id
where ss.is_valid=1
and extract(month FROM ss.sspdtgl) = {bulan}
and extract(year FROM ss.sspdtgl) = {tahun}
group by r.rekeningkd
),
lv4 as (
select r.rekeningkd, sum(realisasi) realisasi
from lv5 tl
left join r on r.rekeningkd ilike tl.rlevel4
where r.levelid=4 and r.issummary=1
group by r.rekeningkd
),
lv3 as (
select r.rekeningkd, sum(realisasi) realisasi
from lv5 tl
left join r on r.rekeningkd ilike tl.rlevel3
where r.levelid=3 and r.issummary=1
group by r.rekeningkd
),
lv2 as (
select r.rekeningkd, sum(realisasi) realisasi
from lv5 tl
left join r on r.rekeningkd ilike tl.rlevel2
where r.levelid=2 and r.issummary=1
group by r.rekeningkd
),
lv1 as (
select r.rekeningkd, sum(realisasi) realisasi
from lv5 tl
left join r on r.rekeningkd ilike tl.rlevel1
where r.levelid=1 and r.issummary=1
group by r.rekeningkd
),
lv0 as (
select r.rekeningkd, sum(realisasi) realisasi
from lv5 tl
left join r on r.rekeningkd ilike tl.rlevel0
where r.levelid=0 and r.issummary=1
group by r.rekeningkd
),
lu5 as (
select r.rekeningkd,
substring(r.rekeningkd,1,1)||'%' rlevel0,
substring(r.rekeningkd,1,2)||'%' rlevel1,
substring(r.rekeningkd,1,3)||'%' rlevel2,
substring(r.rekeningkd,1,6)||'%' rlevel3,
substring(r.rekeningkd,1,8)||'%' rlevel4,
substring(r.rekeningkd,1,12)||'%' rlevel5,
sum(
case when ss.hitung_bunga in (0,2) then ss.jml_bayar
when s.type_id in (5) then s.pajak_terhutang
when s.type_id in (13) then 0
else ss.jml_bayar-ss.bunga
end
) realisasi
from pad_sspd ss
left join pad_spt s on s.id=ss.spt_id
left join pad_pajak p on p.id=s.pajak_id
left join r on r.id=p.rekening_id
where r.levelid=5 and ss.is_valid=1
and extract(month FROM ss.sspdtgl) < {bulan}
and extract(year FROM ss.sspdtgl) = {tahun}
group by r.rekeningkd
union
select r.rekeningkd,
substring(r.rekeningkd,1,1)||'%' rlevel0,
substring(r.rekeningkd,1,2)||'%' rlevel1,
substring(r.rekeningkd,1,3)||'%' rlevel2,
substring(r.rekeningkd,1,6)||'%' rlevel3,
substring(r.rekeningkd,1,8)||'%' rlevel4,
substring(r.rekeningkd,1,12)||'%' rlevel5,
sum(
case when ss.hitung_bunga in (0,2) then 0
when s.type_id in (5) then ss.jml_bayar-s.pajak_terhutang
when s.type_id in (13) then ss.jml_bayar
else ss.bunga
end
) realisasi
from pad_sspd ss
left join pad_spt s on s.id=ss.spt_id
left join pad_pajak p on p.id=s.pajak_id
left join r on r.id=p.rekdenda_id
where (r.levelid=5 or (r.levelid=4 and issummary=0)) and ss.is_valid=1
and extract(month FROM ss.sspdtgl) < {bulan}
and extract(year FROM ss.sspdtgl) = {tahun}
group by r.rekeningkd
),
lu4 as (
select r.rekeningkd, sum(realisasi) realisasi
from lu5 tl
left join r on r.rekeningkd ilike tl.rlevel4
where r.levelid=4 and r.issummary=1
group by r.rekeningkd
),
lu3 as (
select r.rekeningkd, sum(realisasi) realisasi
from lu5 tl
left join r on r.rekeningkd ilike tl.rlevel3
where r.levelid=3 and r.issummary=1
group by r.rekeningkd
),
lu2 as (
select r.rekeningkd, sum(realisasi) realisasi
from lu5 tl
left join r on r.rekeningkd ilike tl.rlevel2
where r.levelid=2 and r.issummary=1
group by r.rekeningkd
),
lu1 as (
select r.rekeningkd, sum(realisasi) realisasi
from lu5 tl
left join r on r.rekeningkd ilike tl.rlevel1
where r.levelid=1 and r.issummary=1
group by r.rekeningkd
),
lu0 as (
select r.rekeningkd, sum(realisasi) realisasi
from lu5 tl
left join r on r.rekeningkd ilike tl.rlevel0
where r.levelid=0 and r.issummary=1
group by r.rekeningkd
)
select format_tgl(date(now()), false, false) tglcetak, upper(get_bulan({bulan}::int, false))||{tahun} as bulan, r.rekeningkd, r.rekeningnm, r.levelid, r.issummary,
coalesce(case
when r.levelid=0 then a0.murni
when r.levelid=1 then a1.murni
when r.levelid=2 then a2.murni
when r.levelid=3 then a3.murni
when r.levelid=4 then a4.murni
when r.levelid=5 then a5.murni
end, 0
) murni,
coalesce(case
when r.levelid=0 then a0.perubahan
when r.levelid=1 then a1.perubahan
when r.levelid=2 then a2.perubahan
when r.levelid=3 then a3.perubahan
when r.levelid=4 then a4.perubahan
when r.levelid=5 then a5.perubahan
end, 0
) perubahan,
coalesce(case
when r.levelid=0 then lu0.realisasi
when r.levelid=1 then lu1.realisasi
when r.levelid=2 then lu2.realisasi
when r.levelid=3 then lu3.realisasi
when r.levelid=4 then lu4.realisasi
when r.levelid=5 then lu5.realisasi
end, 0
) realisasi_lalu,
coalesce(case
when r.levelid=0 then lv0.realisasi
when r.levelid=1 then lv1.realisasi
when r.levelid=2 then lv2.realisasi
when r.levelid=3 then lv3.realisasi
when r.levelid=4 then lv4.realisasi
when r.levelid=5 then lv5.realisasi
end, 0
) realisasi
from r
left join a0 on a0.rekeningkd=r.rekeningkd
left join a1 on a1.rekeningkd=r.rekeningkd
left join a2 on a2.rekeningkd=r.rekeningkd
left join a3 on a3.rekeningkd=r.rekeningkd
left join a4 on a4.rekeningkd=r.rekeningkd
left join a5 on a5.rekeningkd=r.rekeningkd
left join lv0 on lv0.rekeningkd=r.rekeningkd
left join lv1 on lv1.rekeningkd=r.rekeningkd
left join lv2 on lv2.rekeningkd=r.rekeningkd
left join lv3 on lv3.rekeningkd=r.rekeningkd
left join lv4 on lv4.rekeningkd=r.rekeningkd
left join lv5 on lv5.rekeningkd=r.rekeningkd
left join lu0 on lu0.rekeningkd=r.rekeningkd
left join lu1 on lu1.rekeningkd=r.rekeningkd
left join lu2 on lu2.rekeningkd=r.rekeningkd
left join lu3 on lu3.rekeningkd=r.rekeningkd
left join lu4 on lu4.rekeningkd=r.rekeningkd
left join lu5 on lu5.rekeningkd=r.rekeningkd
where r.enabled=1
) as data
left join tblpejabat d on d.id=1
left join tblpejabat e on e.id=1
where 1=1
{kondisi}
group by 1,2,3,4,5,6,11,12,13,14,15,16,17,18
order by kode