· 6 years ago · Nov 27, 2019, 01:04 PM
1-- Table
2
3CREATE TABLE [dbo].[DP_Temp](
4 [NODP] [char](30) NULL,
5 [TGLDP] [datetime] NULL,
6 [BERITA] [varchar](254) NULL,
7 [TGLVALID] [datetime] NULL,
8 [NOSP2D] [char](50) NULL,
9 [UNITKEY] [char](10) NULL,
10 [ID_STAT] [char](1) NULL
11) ON [PRIMARY]
12
13GO
14
15CREATE TABLE [dbo].[Ref_Potongan](
16 [id] [bigint] IDENTITY(1,1) NOT NULL,
17 [Kd_Rek_1] [tinyint] NULL,
18 [Kd_Rek_2] [tinyint] NULL,
19 [Kd_Rek_3] [tinyint] NULL,
20 [Kd_Rek_4] [tinyint] NULL,
21 [Kd_Rek_5] [tinyint] NULL,
22 [Nm_Potongan] [nvarchar](255) NULL,
23PRIMARY KEY CLUSTERED
24(
25 [id] ASC
26)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
27) ON [PRIMARY]
28
29GO
30
31CREATE TABLE [dbo].[Ref_Sub_Unit](
32 [id] [bigint] IDENTITY(1,1) NOT NULL,
33 [Kd_Urusan] [tinyint] NULL,
34 [Kd_Bidang] [tinyint] NULL,
35 [Kd_Unit] [tinyint] NULL,
36 [Kd_Sub] [smallint] NULL,
37 [Kd_Level] [smallint] NULL,
38 [Nm_Sub_Unit] [nvarchar](255) NULL,
39 [Is_BPKAD] [bit] NULL,
40PRIMARY KEY CLUSTERED
41(
42 [id] ASC
43)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
44) ON [PRIMARY]
45
46GO
47
48
49CREATE TABLE [dbo].[TrxSP2D](
50 [Kd_Urusan] [int] NULL,
51 [Kd_Bidang] [int] NULL,
52 [Kd_Unit] [int] NULL,
53 [Kd_Sub] [int] NULL,
54 [Tahun] [smallint] NULL,
55 [No_SP2D] [varchar](50) NULL,
56 [Tgl_SP2D] [datetime] NULL,
57 [No_SPM] [varchar](50) NULL,
58 [Tgl_SPM] [datetime] NULL,
59 [Jn_SPM] [varchar](2) NULL,
60 [Nm_Penerima] [varchar](100) NULL,
61 [Keterangan] [varchar](255) NOT NULL,
62 [NPWP] [varchar](20) NULL,
63 [Bank_Penerima] [varchar](50) NULL,
64 [Rek_Penerima] [varchar](50) NULL,
65 [Tgl_Penguji] [datetime] NULL,
66 [Nm_Bank] [varchar](50) NULL,
67 [No_Rekening] [varchar](50) NULL,
68 [Nilai] [money] NULL,
69 [DateCreate] [datetime] NULL,
70 [Cair] [tinyint] NULL,
71 [TglCair] [datetime] NULL,
72 [gaji] [money] NULL,
73 [Nm_Unit] [varchar](255) NULL,
74 [No_Kontrak] [varchar](50) NULL,
75 [uraian] [varchar](255) NULL,
76 [Nm_Sub_Unit] [varchar](50) NULL
77) ON [PRIMARY]
78
79GO
80
81
82CREATE TABLE [dbo].[TrxSP2D_Potongan](
83 [Tahun] [smallint] NOT NULL,
84 [Kd_Urusan] [varchar](2) NOT NULL,
85 [Kd_Bidang] [varchar](2) NOT NULL,
86 [Kd_Unit] [varchar](2) NOT NULL,
87 [Kd_Sub] [varchar](2) NOT NULL,
88 [No_SP2D] [varchar](50) NOT NULL,
89 [Kd_Rek_1] [tinyint] NOT NULL,
90 [Kd_Rek_2] [tinyint] NOT NULL,
91 [Kd_Rek_3] [tinyint] NOT NULL,
92 [Kd_Rek_4] [tinyint] NOT NULL,
93 [Kd_Rek_5] [tinyint] NOT NULL,
94 [No_SPM] [varchar](50) NOT NULL,
95 [Jn_SPM] [varchar](2) NOT NULL,
96 [Nm_Rekening] [varchar](255) NOT NULL,
97 [Nilai] [money] NOT NULL,
98 CONSTRAINT [PK__TrxSP2D___C8A01A3503317E3D] PRIMARY KEY CLUSTERED
99(
100 [Tahun] ASC,
101 [Kd_Urusan] ASC,
102 [Kd_Bidang] ASC,
103 [Kd_Unit] ASC,
104 [Kd_Sub] ASC,
105 [No_SP2D] ASC,
106 [Kd_Rek_1] ASC,
107 [Kd_Rek_2] ASC,
108 [Kd_Rek_3] ASC,
109 [Kd_Rek_4] ASC,
110 [Kd_Rek_5] ASC
111)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
112) ON [PRIMARY]
113
114
115CREATE TABLE [dbo].[TrxSP2D_Temp](
116 [Kd_Urusan] [tinyint] NOT NULL,
117 [Kd_Bidang] [tinyint] NOT NULL,
118 [Kd_Unit] [tinyint] NOT NULL,
119 [Kd_Sub] [tinyint] NOT NULL,
120 [Tahun] [smallint] NOT NULL,
121 [No_Sp2d] [varchar](50) NOT NULL,
122 [Tgl_Sp2d] [date] NOT NULL,
123 [No_Spm] [varchar](50) NOT NULL,
124 [Tgl_Spm] [date] NOT NULL,
125 [Jn_Spm] [varchar](2) NOT NULL,
126 [Nm_Penerima] [varchar](100) NOT NULL,
127 [Keterangan] [varchar](255) NULL,
128 [NPWP] [varchar](20) NOT NULL,
129 [Bank_Penerima] [varchar](50) NOT NULL,
130 [Rek_Penerima] [varchar](50) NOT NULL,
131 [Tgl_Penguji] [date] NOT NULL,
132 [Nm_Bank] [varchar](50) NOT NULL,
133 [No_Rekening] [varchar](50) NOT NULL,
134 [Nilai] [money] NOT NULL,
135 [DateCreate] [date] NOT NULL,
136 [Cair] [tinyint] NOT NULL,
137 [TglCair] [date] NULL,
138 [Gaji] [tinyint] NOT NULL,
139 [Nm_Unit] [varchar](255) NOT NULL,
140 [Nm_Sub_Unit] [varchar](255) NOT NULL,
141 [Uraian] [varchar](255) NULL,
142 [Kirim] [tinyint] NOT NULL
143) ON [PRIMARY]
144
145GO
146
147
148CREATE TABLE [dbo].[TrxSPM](
149 [Tahun] [smallint] NULL,
150 [No_SPM] [nvarchar](50) NULL,
151 [Nm_Penerima] [nvarchar](100) NULL,
152 [Bank_Penerima] [nvarchar](50) NULL,
153 [Rek_Penerima] [nvarchar](50) NULL,
154 [Status] [tinyint] NULL,
155 [DateCreate] [datetime] NULL,
156 [Uraian] [nvarchar](max) NULL,
157 [Telp] [varchar](20) NULL,
158 [Kirim] [tinyint] NULL,
159 [Keterangan] [nvarchar](max) NULL,
160 [skpd] [varchar](max) NULL
161) ON [PRIMARY]
162
163GO
164
165
166CREATE TABLE [dbo].[TrxSPM_Temp](
167 [Tahun] [nchar](10) NOT NULL,
168 [No_Spm] [varchar](50) NOT NULL,
169 [Nm_Penerima] [varchar](100) NOT NULL,
170 [Bank_Penerima] [varchar](50) NOT NULL,
171 [Rek_Penerima] [varchar](50) NOT NULL,
172 [Status] [tinyint] NOT NULL,
173 [DataCreate] [datetime] NOT NULL,
174 [Uraian] [varchar](max) NULL,
175 [Telp] [varchar](20) NULL,
176 [Kirim] [tinyint] NULL,
177 [Keterangan] [varchar](max) NULL,
178 [SKPD] [varchar](max) NULL
179) ON [PRIMARY]
180
181GO
182
183-- Store Procedure
184
185CREATE PROCEDURE [dbo].[sp_InsertSp2d_PotonganSp2d]
186---@UNITKEY CHAR(10)
187AS
188
189--SET DATEFORMAT dmy
190
191exec [sp_InsertSpm]
192
193--if not exists (select name from V@LID49V6_2019..sysobjects where name = 'DP_Temp')
194-- begin
195-- CREATE TABLE V@LID49V6_2019..[DP_Temp]
196-- ([NODP] char(30),[TGLDP] datetime,[BERITA] varchar(254),[TGLVALID] datetime,
197-- [NOSP2D] char(50),[UNITKEY] char(10), ID_STAT char(1))
198-- end
199--INSERT INTO V@LID49V6_2019..[DP_Temp]
200--SELECT top 1 t.[NODP],[TGLDP],[BERITA],[TGLVALID],[NOSP2D],[UNITKEY],'0' AS ID_STAT
201--FROM V@LID49V6_2019..[DP] d LEFT JOIN V@LID49V6_2019..[DPDET] t ON t.NODP=d.NODP
202--WHERE cast(CONVERT(varchar(11),[TGLDP],103)AS date) > cast(CONVERT(varchar(11),getdate()-4,103)AS date)
203--AND NOT EXISTS (SELECT * FROM V@LID49V6_2019..[DP_Temp] dt
204-- WHERE dt.NODP=t.NODP and dt.NOSP2D=t.NOSP2D AND dt.UNITKEY=t.UNITKEY)
205
206--select UNITKEY,KDUNIT,KDLEVEL,TYPE,NMUNIT,LEFT(tb4.KDUNIT,1) as [Kd_Urusan],SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],
207--SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub]
208--INTO DB_ANTARABTH_2019_Development..MapUnit
209--from V@LID49V6_2019..DAFTUNIT tb4 where KDLEVEL=3
210--order by KDUNIT
211
212delete from DB_ANTARABTH_2019_Development.dbo.TrxSp2d where No_Sp2d in (
213 select
214 ltrim(rtrim(sp.NOSP2D))
215 from (
216 select
217 LEFT(tb4.KDUNIT,1) as [Kd_Urusan],
218 SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],
219 SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],
220 SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub],
221 CAST(year([TGLSP2D]) AS smallint) as [Tahun],
222 ltrim(rtrim(tb1.NOSP2D)) as [NOSP2D],
223 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLSP2D,120)), '.000',''), 103) as date) as TGLSP2D,
224 ISNULL(ltrim(rtrim(tb3.NOSPM)),'') AS NOSPM,
225 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb3.TGLSPM,120)), '.000',''), 103) as date) AS TGLSPM,
226 cast(tb7.LBLSTATUS as varchar(2)) as LBLSTATUS,
227 case
228 when tb1.KDP3 IS null then tb10.NAMA
229 else tb11.NMP3
230 end AS NamaPenerima,
231 'No. Penguji: ' + (SELECT TOP 1 NODP FROM V@LID49V6_2019..DPDET WHERE NOSP2D = tb1.NOSP2D) + '\n' + (
232 SELECT '-' + rtrim(ltrim(NOSP2D)) + '\n' AS [text()]
233 FROM V@LID49V6_2019..DPDET
234 WHERE NODP = (SELECT TOP 1 NODP FROM V@LID49V6_2019..DPDET WHERE NOSP2D = tb1.NOSP2D)
235 ORDER BY NOSP2D
236 FOR XML PATH ('')
237 ) AS Keterangan,
238 case
239 when tb1.KDP3 IS null then cast(tb9.NPWPBEND as varchar(20))
240 else cast(tb11.NPWP as varchar(20))
241 end AS NPWPPenerima,
242 case
243 when tb1.KDP3 IS null then ISNULL(tb12.NMBANK,'')
244 else ISNULL(tb11.NMBANK,'')
245 end AS BankPenerima,
246 case
247 when tb1.KDP3 IS null then cast(tb9.REKBEND as varchar(50))
248 else cast(tb11.NORCP3 as varchar(50))
249 end AS RekPenerima,
250 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLVALID,120)), '.000',''), 103) as date) as Tgl_Penguji,
251 ISNULL(r.NMBKAS,'') as Nm_Bank,
252 cast(r.NOREKB as varchar(50)) as REKBEND,
253 tb.NilSp2d-ISNULL(tb2.NilPot,0) as nilai,
254 GETDATE() as datecreate,
255 0 as cair,
256 null as TglCair,
257 case
258 when CHARINDEX('GJ', tb1.NOSP2D)=0 then 0
259 else 1
260 end as gaji,
261 tbu.NMUNIT as Nm_Unit,
262 tb4.nmunit as Nm_Sub_Unit,
263 left(tb1.KEPERLUAN,255) as uraian
264 from V@LID49V6_2019..SP2D tb1
265 left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=tb1.NOSPM
266 left join V@LID49V6_2019..DAFTUNIT tb4 on tb4.UNITKEY=tb1.UNITKEY
267 left join V@LID49V6_2019..DAFTUNIT tbu on tbu.KDUNIT=LEFT(tb4.KDUNIT,1)+'.'+SUBSTRING(tb4.KDUNIT,3,2)+'.'+SUBSTRING(tb4.KDUNIT,6,2)+'.'
268 left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
269 left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb1.KEYBEND
270 left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
271 left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb1.KDP3
272 left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
273 left join (select NOSP2D,SUM(nilai) as NilSp2d from V@LID49V6_2019..SP2DDETR GROUP BY NOSP2D) tb on tb.NOSP2D=tb1.NOSP2D
274 left join (select NOSP2D,SUM(nilai) as NilPot from V@LID49V6_2019..SP2DDETB GROUP BY NOSP2D) tb2 on tb2.NOSP2D=tb1.NOSP2D,
275 (SELECT NMBKAS,NOREKB from V@LID49V6_2019..bkbkas where NOBBANTU='01') r
276 where
277 NilSp2d<>0
278 and cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLVALID,120)), '.000',''), 103) as date) > cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),GETDATE()-51,120)), '.000',''), 103) as date)
279 and tb1.NOSPM in (SELECT No_Spm FROM DB_ANTARABTH_2019_Development..TrxSPM where Status=1)
280
281 union
282
283 select
284 LEFT(tb4.KDUNIT,1) as [Kd_Urusan],
285 SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],
286 SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],
287 SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub],
288 CAST(year([TGLSP2D]) AS smallint) as [Tahun],
289 ltrim(rtrim(tb1.NOSP2D)) as [NOSP2D],
290 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLSP2D,120)), '.000',''), 103) as date) as TGLSP2D,
291 ISNULL(ltrim(rtrim(tb3.NOSPM)),''),
292 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb3.TGLSPM,120)), '.000',''), 103) as date) as TGLSPM,
293 cast(tb7.LBLSTATUS as varchar(2)) as LBLSTATUS,
294 case
295 when tb1.KDP3 IS null then tb10.NAMA
296 else tb11.NMP3
297 end AS NamaPenerima,
298 'No. Penguji: ' + (SELECT TOP 1 NODP FROM V@LID49V6_2019..DPDET WHERE NOSP2D = tb1.NOSP2D) + '\n' + (
299 SELECT '-' + rtrim(ltrim(NOSP2D)) + '\n' AS [text()]
300 FROM V@LID49V6_2019..DPDET
301 WHERE NODP = (SELECT TOP 1 NODP FROM V@LID49V6_2019..DPDET WHERE NOSP2D = tb1.NOSP2D)
302 ORDER BY NOSP2D
303 FOR XML PATH ('')
304 ) AS Keterangan,
305 case
306 when tb1.KDP3 IS null then cast(tb9.NPWPBEND as varchar(20))
307 else cast(tb11.NPWP as varchar(20))
308 end AS NPWPPenerima,
309 case
310 when tb1.KDP3 IS null then ISNULL(tb12.NMBANK,'')
311 else ISNULL(tb11.NMBANK,'')
312 end AS BankPenerima,
313 case
314 when tb1.KDP3 IS null then cast(tb9.REKBEND as varchar(50))
315 else cast(tb11.NORCP3 as varchar(50))
316 end AS RekPenerima,
317 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLVALID,120)), '.000',''), 103) as date) as Tgl_Penguji,
318 ISNULL(r.NMBKAS,'') as Nm_Bank,
319 cast(r.NOREKB as varchar(50)) as REKBEND,
320 tb.NilSp2d-ISNULL(tb2.NilPot,0) as nilai,
321 GETDATE() as datecreate,
322 0 as cair,
323 null as TglCair,
324 case
325 when CHARINDEX('GJ', tb1.NOSP2D)=0 then 0
326 else 1 end
327 as gaji,
328 tbu.NMUNIT as Nm_Unit,
329 tb4.nmunit as Nm_Sub_Unit,
330 left(tb1.KEPERLUAN,255) as uraian
331 from V@LID49V6_2019..SP2D tb1
332 left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=tb1.NOSPM
333 left join V@LID49V6_2019..DAFTUNIT tb4 on tb4.UNITKEY=tb1.UNITKEY
334 left join V@LID49V6_2019..DAFTUNIT tbu on tbu.KDUNIT=LEFT(tb4.KDUNIT,1)+'.'+SUBSTRING(tb4.KDUNIT,3,2)+'.'+SUBSTRING(tb4.KDUNIT,6,2)+'.'
335 left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
336 left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb1.KEYBEND
337 left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
338 left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb1.KDP3
339 left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
340 left join (select tb2.NOSP2D,SUM(nilai) as NilSp2d from V@LID49V6_2019..SP2DDETRTL tb2 GROUP BY NOSP2D ) tb on tb.NOSP2D=tb1.NOSP2D
341 left join (select NOSP2D,SUM(nilai) as NilPot from V@LID49V6_2019..SP2DDETB GROUP BY NOSP2D) tb2 on tb2.NOSP2D=tb1.NOSP2D,
342 (SELECT NMBKAS,NOREKB from V@LID49V6_2019..bkbkas where NOBBANTU='01') r
343 where
344 NilSp2d<>0
345 and cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLVALID,120)), '.000',''), 103) as date) > cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),GETDATE() -15,120)), '.000',''), 103) as date)
346 and tb1.NOSPM in (SELECT No_Spm FROM DB_ANTARABTH_2019_Development..TrxSPM where Status=1)
347 ) sp
348 left join (
349 select
350 p.NOSP2D,
351 s.NOSPM,
352 sum(isnull(NILAI,0)) as NilPot
353 from V@LID49V6_2019..SP2DPJK p
354 left join V@LID49V6_2019..SP2D s on s.NOSP2D=p.NOSP2D and s.UNITKEY=p.UNITKEY
355 group by p.NOSP2D,s.NOSPM
356 ) p on p.NOSP2D=sp.NOSP2D and p.NOSPM=sp.NOSPM
357 left join DB_ANTARABTH_2019_Development..TrxSp2d tranx on tranx.No_Sp2d = sp.NOSP2D
358 where
359 (tranx.Kd_Urusan != sp.Kd_Urusan
360 or tranx.Kd_Bidang != sp.Kd_Bidang
361 or tranx.Kd_Unit != sp.Kd_Unit
362 or tranx.Kd_Sub != sp.Kd_Sub
363 or tranx.Tgl_Sp2d != sp.TGLSP2D
364 or tranx.No_Spm != sp.NOSPM
365 or tranx.Tgl_Spm != sp.TGLSPM
366 or tranx.Jn_Spm != sp.LBLSTATUS
367 or tranx.Nm_Penerima != sp.NamaPenerima
368 or tranx.NPWP != sp.NPWPPenerima
369 or tranx.Bank_Penerima != sp.BankPenerima
370 or tranx.Rek_Penerima != sp.RekPenerima
371 or tranx.Tgl_Penguji != sp.Tgl_Penguji
372 or tranx.Nm_Bank != sp.Nm_Bank
373 or tranx.No_Rekening != sp.REKBEND
374 or tranx.Nilai != sp.nilai
375 or tranx.Gaji != sp.gaji
376 or tranx.Nm_Unit != sp.Nm_Unit
377 or tranx.Nm_Sub_Unit != sp.Nm_Sub_Unit
378 or tranx.Uraian != sp.uraian)
379 and tranx.Cair = 0
380);
381
382insert into DB_ANTARABTH_2019_Development.dbo.TrxSP2D (
383 Kd_Urusan,
384 Kd_Bidang,
385 Kd_Unit,
386 Kd_Sub,
387 Tahun,
388 No_SP2D,
389 Tgl_SP2D,
390 No_SPM,
391 Tgl_SPM,
392 Jn_SPM,
393 Nm_Penerima,
394 Keterangan,
395 NPWP,
396 Bank_Penerima,
397 Rek_Penerima,
398 Tgl_Penguji,
399 Nm_Bank,
400 No_Rekening,
401 Nilai,
402 DateCreate,
403 Cair,
404 TglCair,
405 gaji,
406 Nm_Unit,
407 Nm_Sub_Unit,
408 uraian
409)
410
411select
412 Kd_Urusan,
413 Kd_Bidang,
414 Kd_Unit,
415 Kd_Sub,
416 Tahun,
417 ltrim(rtrim(sp.NOSP2D)),
418 TGLSP2D,
419 sp.NOSPM,
420 TGLSPM,
421 LBLSTATUS,
422 NamaPenerima,
423 Keterangan,
424 NPWPPenerima,
425 BankPenerima,
426 RekPenerima,
427 Tgl_Penguji,
428 Nm_Bank,
429 REKBEND,
430 nilai-isnull(NilPot,0) as nilai,
431 datecreate,
432 cair,
433 TglCair,
434 gaji,
435 Nm_Unit,
436 Nm_Sub_Unit,
437 uraian
438from (
439 select
440 LEFT(tb4.KDUNIT,1) as [Kd_Urusan],
441 SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],
442 SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],
443 SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub],
444 CAST(year([TGLSP2D]) AS smallint) as [Tahun],
445 ltrim(rtrim(tb1.NOSP2D)) as [NOSP2D],
446 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLSP2D,120)), '.000',''), 103) as date) as TGLSP2D,
447 ISNULL(ltrim(rtrim(tb3.NOSPM)),'') AS NOSPM,
448 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb3.TGLSPM,120)), '.000',''), 103) as date) AS TGLSPM,
449 cast(tb7.LBLSTATUS as varchar(2)) as LBLSTATUS,
450 case
451 when tb1.KDP3 IS null then tb10.NAMA
452 else tb11.NMP3
453 end AS NamaPenerima,
454 ISNULL('No. Penguji: ' + (SELECT TOP 1 NODP FROM V@LID49V6_2019..DPDET WHERE NOSP2D = tb1.NOSP2D) + '\n' + (
455 SELECT '-' + rtrim(ltrim(NOSP2D)) + '\n' AS [text()]
456 FROM V@LID49V6_2019..DPDET
457 WHERE NODP = (SELECT TOP 1 NODP FROM V@LID49V6_2019..DPDET WHERE NOSP2D = tb1.NOSP2D)
458 ORDER BY NOSP2D
459 FOR XML PATH ('')
460 ), '') AS Keterangan,
461 case
462 when tb1.KDP3 IS null then cast(tb9.NPWPBEND as varchar(20))
463 else cast(tb11.NPWP as varchar(20))
464 end AS NPWPPenerima,
465 case
466 when tb1.KDP3 IS null then ISNULL(tb12.NMBANK,'')
467 else ISNULL(tb11.NMBANK,'')
468 end AS BankPenerima,
469 case
470 when tb1.KDP3 IS null then cast(tb9.REKBEND as varchar(50))
471 else cast(tb11.NORCP3 as varchar(50))
472 end AS RekPenerima,
473 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLVALID,120)), '.000',''), 103) as date) as Tgl_Penguji,
474 ISNULL(r.NMBKAS,'') as Nm_Bank,
475 cast(r.NOREKB as varchar(50)) as REKBEND,
476 tb.NilSp2d-ISNULL(tb2.NilPot,0) as nilai,
477 GETDATE() as datecreate,
478 0 as cair,
479 null as TglCair,
480 case
481 when CHARINDEX('GJ', tb1.NOSP2D)=0 then 0
482 else 1
483 end as gaji,
484 tbu.NMUNIT as Nm_Unit,
485 tb4.nmunit as Nm_Sub_Unit,
486 left(tb1.KEPERLUAN,255) as uraian
487 from V@LID49V6_2019..SP2D tb1
488 left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=tb1.NOSPM
489 left join V@LID49V6_2019..DAFTUNIT tb4 on tb4.UNITKEY=tb1.UNITKEY
490 left join V@LID49V6_2019..DAFTUNIT tbu on tbu.KDUNIT=LEFT(tb4.KDUNIT,1)+'.'+SUBSTRING(tb4.KDUNIT,3,2)+'.'+SUBSTRING(tb4.KDUNIT,6,2)+'.'
491 left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
492 left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb1.KEYBEND
493 left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
494 left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb1.KDP3
495 left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
496 left join (select NOSP2D,SUM(nilai) as NilSp2d from V@LID49V6_2019..SP2DDETR GROUP BY NOSP2D) tb on tb.NOSP2D=tb1.NOSP2D
497 left join (select NOSP2D,SUM(nilai) as NilPot from V@LID49V6_2019..SP2DDETB GROUP BY NOSP2D) tb2 on tb2.NOSP2D=tb1.NOSP2D,
498 (SELECT NMBKAS,NOREKB from V@LID49V6_2019..bkbkas where NOBBANTU='01') r
499 where
500 NilSp2d<>0
501 and cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLVALID,120)), '.000',''), 103) as date) > cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),GETDATE()-30,120)), '.000',''), 103) as date)
502 --and tb1.NOSP2D in (SELECT NOSP2D FROM V@LID49V6_2019..[DP_Temp])
503 and tb1.NOSPM in (SELECT No_Spm FROM DB_ANTARABTH_2019_Development..TrxSPM where Status=1 or Status = 3)
504 and not exists (select No_Sp2d from DB_ANTARABTH_2019_Development..TrxSp2d where ltrim(rtrim(No_Sp2d)) = ltrim(rtrim(tb1.NOSP2D)))
505
506 union
507
508 select
509 LEFT(tb4.KDUNIT,1) as [Kd_Urusan],
510 SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],
511 SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],
512 SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub],
513 CAST(year([TGLSP2D]) AS smallint) as [Tahun],
514 ltrim(rtrim(tb1.NOSP2D)) as [NOSP2D],
515 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLSP2D,120)), '.000',''), 103) as date) as TGLSP2D,
516 ISNULL(ltrim(rtrim(tb3.NOSPM)),''),
517 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb3.TGLSPM,120)), '.000',''), 103) as date) as TGLSPM,
518 cast(tb7.LBLSTATUS as varchar(2)) as LBLSTATUS,
519 case
520 when tb1.KDP3 IS null then tb10.NAMA
521 else tb11.NMP3
522 end AS NamaPenerima,
523 ISNULL('No. Penguji: ' + (SELECT TOP 1 NODP FROM V@LID49V6_2019..DPDET WHERE NOSP2D = tb1.NOSP2D) + '\n' + (
524 SELECT '-' + rtrim(ltrim(NOSP2D)) + '\n' AS [text()]
525 FROM V@LID49V6_2019..DPDET
526 WHERE NODP = (SELECT TOP 1 NODP FROM V@LID49V6_2019..DPDET WHERE NOSP2D = tb1.NOSP2D)
527 ORDER BY NOSP2D
528 FOR XML PATH ('')
529 ), '') AS Keterangan,
530 case
531 when tb1.KDP3 IS null then cast(tb9.NPWPBEND as varchar(20))
532 else cast(tb11.NPWP as varchar(20))
533 end AS NPWPPenerima,
534 case
535 when tb1.KDP3 IS null then ISNULL(tb12.NMBANK,'')
536 else ISNULL(tb11.NMBANK,'')
537 end AS BankPenerima,
538 case
539 when tb1.KDP3 IS null then cast(tb9.REKBEND as varchar(50))
540 else cast(tb11.NORCP3 as varchar(50))
541 end AS RekPenerima,
542 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLVALID,120)), '.000',''), 103) as date) as Tgl_Penguji,
543 ISNULL(r.NMBKAS,'') as Nm_Bank,
544 cast(r.NOREKB as varchar(50)) as REKBEND,
545 tb.NilSp2d-ISNULL(tb2.NilPot,0) as nilai,
546 GETDATE() as datecreate,
547 0 as cair,
548 null as TglCair,
549 case
550 when CHARINDEX('GJ', tb1.NOSP2D)=0 then 0
551 else 1 end
552 as gaji,
553 tbu.NMUNIT as Nm_Unit,
554 tb4.nmunit as Nm_Sub_Unit,
555 left(tb1.KEPERLUAN,255) as uraian
556 from V@LID49V6_2019..SP2D tb1
557 left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=tb1.NOSPM
558 left join V@LID49V6_2019..DAFTUNIT tb4 on tb4.UNITKEY=tb1.UNITKEY
559 left join V@LID49V6_2019..DAFTUNIT tbu on tbu.KDUNIT=LEFT(tb4.KDUNIT,1)+'.'+SUBSTRING(tb4.KDUNIT,3,2)+'.'+SUBSTRING(tb4.KDUNIT,6,2)+'.'
560 left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
561 left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb1.KEYBEND
562 left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
563 left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb1.KDP3
564 left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
565 left join (select tb2.NOSP2D,SUM(nilai) as NilSp2d from V@LID49V6_2019..SP2DDETRTL tb2 GROUP BY NOSP2D ) tb on tb.NOSP2D=tb1.NOSP2D
566 left join (select NOSP2D,SUM(nilai) as NilPot from V@LID49V6_2019..SP2DDETB GROUP BY NOSP2D) tb2 on tb2.NOSP2D=tb1.NOSP2D,
567 (SELECT NMBKAS,NOREKB from V@LID49V6_2019..bkbkas where NOBBANTU='01') r
568 where
569 NilSp2d<>0 --and tb1.NOSP2D in (SELECT NOSP2D FROM V@LID49V6_2019..[DP_Temp] )
570 and cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLVALID,120)), '.000',''), 103) as date) > cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),GETDATE() -30,120)), '.000',''), 103) as date)
571 and tb1.NOSPM in (SELECT No_Spm FROM DB_ANTARABTH_2019_Development..TrxSPM where Status=1 or Status = 3)
572 and not exists (select No_Sp2d from DB_ANTARABTH_2019_Development..TrxSp2d where ltrim(rtrim(No_Sp2d)) = ltrim(rtrim(tb1.NOSP2D)))
573) sp
574left join (
575 select
576 p.NOSP2D,
577 s.NOSPM,
578 sum(isnull(NILAI,0)) as NilPot
579 from V@LID49V6_2019..SP2DPJK p
580 left join V@LID49V6_2019..SP2D s on s.NOSP2D=p.NOSP2D and s.UNITKEY=p.UNITKEY
581 group by p.NOSP2D,s.NOSPM
582) p on p.NOSP2D=sp.NOSP2D and p.NOSPM=sp.NOSPM
583WHERE
584 NOT EXISTS (
585 SELECT
586 *
587 FROM DB_ANTARABTH_2019_Development.dbo.TrxSP2D sptr
588 where
589 sptr.No_SP2D=sp.NOSP2D
590 AND sptr.Cair IN(0,1)
591 AND sptr.Nm_Sub_Unit=sp.Nm_Sub_Unit
592 )
593
594----- ISI NILAI SP2D POTONGAN
595--delete FROM TrxSP2D_Potongan
596insert into DB_ANTARABTH_2019_Development.dbo.TrxSP2D_Potongan (
597 Tahun,
598 Kd_Urusan,
599 Kd_Bidang,
600 Kd_Unit,
601 Kd_Sub,
602 No_SP2D,
603 Kd_Rek_1,
604 Kd_Rek_2,
605 Kd_Rek_3,
606 Kd_Rek_4,
607 Kd_Rek_5,
608 No_SPM,
609 Jn_SPM,
610 Nm_Rekening,
611 Nilai
612)
613
614select
615 cast(year([TGLSP2D]) as smallint) as [Tahun],
616 LEFT(KDUNIT,1) as [Kd_Urusan],
617 SUBSTRING(KDUNIT,3,2) as [Kd_Bidang],
618 SUBSTRING(KDUNIT,6,2) as [Kd_Unit],
619 SUBSTRING(KDUNIT,9,2) as [Kd_Sub],
620 ltrim(rtrim(sp.NOSP2D)) as No_SP2D,
621 cast(replace(substring(KDPER,1,1), '.', '') as tinyint) as Kd_Rek_1,
622 cast(replace(substring(KDPER,3,1), '.', '') as tinyint) as Kd_Rek_2,
623 cast(replace(substring(KDPER,5,1), '.', '') as tinyint) as Kd_Rek_3,
624 cast(replace(substring(KDPER,7,2), '.', '') as tinyint) as Kd_Rek_4,
625 cast(replace(substring(KDPER,10,3), '.', '') as tinyint) as Kd_Rek_5,
626 ltrim(rtrim(sp.NOSPM)) as No_SPM,
627 cast(tb7.LBLSTATUS as varchar(2)) as jns,
628 sp.NMPER,
629 sp.NILAI
630from (
631 /*
632 select
633 s.NOSP2D,
634 s.TGLSP2D,
635 s.NOSPM,
636 s.UNITKEY,
637 m.KDPER,
638 m.NMPER,
639 r.NILAI
640 from V@LID49V6_2019..SP2D s
641 left join V@LID49V6_2019..SP2DDETB r on r.NOSP2D=s.NOSP2D and r.UNITKEY=s.UNITKEY and (r.NOJETRA = '23' OR r.NOJETRA = '10')
642 LEFT JOIN V@LID49V6_2019..MATANGB m ON m.MTGKEY = r.MTGKEY
643 where
644 KDPER is not null and s.NOSP2D in (select No_Sp2d from DB_ANTARABTH_2019_Development.dbo.TrxSP2D)
645
646 union all
647
648 select
649 s.NOSP2D,
650 s.TGLSP2D,
651 s.NOSPM,
652 s.UNITKEY,
653 m.KDPER,
654 m.NMPER,
655 r.NILAI
656 from V@LID49V6_2019..SP2D s
657 left join V@LID49V6_2019..SP2DDETR r on r.NOSP2D=s.NOSP2D and r.UNITKEY=s.UNITKEY and (r.NOJETRA = '23' OR r.NOJETRA = '10')
658 LEFT JOIN V@LID49V6_2019..MATANGR m ON m.MTGKEY = r.MTGKEY
659 where KDPER is not null and s.NOSP2D in (select No_Sp2d from DB_ANTARABTH_2019_Development.dbo.TrxSP2D)
660
661 union all
662 */
663 select
664 s.NOSP2D,
665 s.TGLSP2D,
666 s.NOSPM,
667 s.UNITKEY,
668 case
669 when tb2.PJKKEY ='9036_' then '4.1.1.12.01' -- PPh 21
670 else
671 case
672 when tb2.PJKKEY ='9046_' then '4.2.3.01.08' -- DAK-PRTN
673 else
674 case
675 when tb2.PJKKEY ='9037_' then '4.1.1.12.02' -- PPh 22
676 else
677 case
678 when tb2.PJKKEY ='9038_' then '4.1.1.12.03' -- PPh 23
679 else
680 case
681 when tb2.PJKKEY ='9994_' then '4.1.1.12.04' -- PPh Final
682 else
683 case
684 when tb2.PJKKEY ='9035_' then '4.1.1.21.01' -- PPN
685 end
686 end
687 end
688 end
689 end
690 end as KDPER,
691 NMPAJAK as NMPER,
692 p.NILAI
693 from V@LID49V6_2019..SP2D s
694 --left join V@LID49V6_2019..SP2DDETR r on r.NOSP2D=s.NOSP2D and r.UNITKEY=s.UNITKEY
695 left join V@LID49V6_2019..SP2DPJK p on p.NOSP2D=s.NOSP2D and p.UNITKEY=s.UNITKEY
696 LEFT JOIN V@LID49V6_2019..JPAJAK tb2 ON p.PJKKEY = tb2.PJKKEY
697 where KDPAJAK is not null
698 and s.NOSP2D in (
699 select
700 No_Sp2d
701 from DB_ANTARABTH_2019_Development.dbo.TrxSP2D
702 )
703) sp
704left join V@LID49V6_2019..DAFTUNIT u ON u.UNITKEY=sp.UNITKEY
705left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=sp.NOSPM
706left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
707WHERE
708 KDPER is NOT NULL
709 and NOT EXISTS (
710 SELECT
711 spot.No_Sp2d,
712 cast(substring(sp.KDPER,1,1) as tinyint),
713 spot.Kd_Rek_1,
714 cast(substring(sp.KDPER,3,1) as tinyint),
715 spot.Kd_Rek_2,
716 cast(substring(sp.KDPER,5,1) as tinyint),
717 spot.Kd_Rek_3,
718 cast(substring(sp.KDPER,7,2) as tinyint),
719 spot.Kd_Rek_4,
720 cast(substring(sp.KDPER,10,3) as tinyint),
721 spot.Kd_Rek_5
722 FROM DB_ANTARABTH_2019_Development.dbo.TrxSP2D_Potongan spot
723 where
724 spot.No_Sp2d=sp.NOSP2D
725 and cast(substring(sp.KDPER,1,1) as tinyint)=spot.Kd_Rek_1
726 and cast(substring(sp.KDPER,3,1) as tinyint)=spot.Kd_Rek_2
727 and cast(substring(sp.KDPER,5,1) as tinyint)=spot.Kd_Rek_3
728 and cast(substring(sp.KDPER,7,2) as tinyint)=spot.Kd_Rek_4
729 and cast(substring(sp.KDPER,10,3) as tinyint)=spot.Kd_Rek_5
730 )
731
732GO
733
734
735
736
737
738CREATE PROCEDURE [dbo].[sp_InsertSpm]
739--@UNITKEY CHAR(10)
740AS
741
742SET DATEFORMAT dmy
743
744
745if not exists (select name from [DB_ANTARABTH_2019_Development]..sysobjects where name = 'TrxSPM_Temp')
746 begin
747 CREATE TABLE [dbo].[TrxSPM_Temp]
748 ([Tahun] [nchar](10) NOT NULL,
749 [No_Spm] [varchar](50) NOT NULL,
750 [Nm_Penerima] [varchar](100) NOT NULL,
751 [Bank_Penerima] [varchar](50) NOT NULL,
752 [Rek_Penerima] [varchar](50) NOT NULL,
753 [Status] [tinyint] NOT NULL,
754 [DataCreate] [datetime] NOT NULL,
755 [Uraian] [varchar](max) NULL,
756 [Telp] varchar(20),
757 [Kirim] [tinyint] NULL,
758 [Keterangan] [varchar](max) NULL,
759 [SKPD] [varchar](max))
760 end
761
762--delete [DB_ANTARABTH_2019_Development].[dbo].[DP_Temp]
763--INSERT INTO [DB_ANTARABTH_2019_Development].[dbo].[DP_Temp]
764--SELECT t.[NODP],[TGLDP],[BERITA],[TGLVALID],[NOSP2D],[UNITKEY],'0' AS ID_STAT
765--FROM V@LID49V6_2019..[DP] d LEFT JOIN V@LID49V6_2019..[DPDET] t ON t.NODP=d.NODP
766--WHERE cast(CONVERT(varchar(11),[TGLDP],103)AS date) > cast(CONVERT(varchar(11),getdate()-4,103)AS date)
767--AND NOT EXISTS (SELECT * FROM [DB_ANTARABTH_2019_Development].[dbo].[DP_Temp] dt
768-- WHERE dt.NODP=t.NODP and dt.NOSP2D=t.NOSP2D AND dt.UNITKEY=t.UNITKEY)
769
770delete [DB_ANTARABTH_2019_Development].[dbo].[TrxSPM_Temp]
771insert into [DB_ANTARABTH_2019_Development].[dbo].[TrxSPM_Temp]
772select Tahun_u,NOSPM_u,NamaPenerima_u,BankPenerima_u,RekPenerima_u,Status_u,datecreate_u,null,TELEPON,kirim_u,uraian_u, tmp.SKPD
773from (
774select distinct CAST(year([TGLSPM]) AS smallint) as [Tahun_u],ISNULL(ltrim(rtrim(tb3.NOSPM)),'') AS NOSPM_u,
775case when tb3.KDP3 IS null then tb10.NAMA else tb11.NMP3 end AS NamaPenerima_u,
776case when tb3.KDP3 IS null then ISNULL(tb12.NMBANK,'') else ISNULL(tb11.NMBANK,'') end AS BankPenerima_u,
777case when tb3.KDP3 IS null then cast(tb9.REKBEND as varchar(50)) else cast(tb11.NORCP3 as varchar(50)) end AS RekPenerima_u,
7780 as Status_u,cast(GETDATE() as DATE) as datecreate_u,keperluan AS uraian_u,
779case when tb3.KDP3 IS null then isnull(u.TELEPON,'') else isnull(tb11.TELEPON,'') end AS TELEPON, 0 as kirim_u, tb3.KEPERLUAN as Keterangan, LTRIM(rtrim(tb4.NMUNIT)) as SKPD
780from V@LID49V6_2019..ANTARBYR tb3
781left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb3.KEYBEND
782left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
783left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb3.KDP3
784left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
785left join V@LID49V6_2019..DAFTUNIT u on u.UNITKEY=tb9.UNITKEY
786left join V@LID49V6_2019..DAFTUNIT tb4 on tb4.UNITKEY=tb3.UNITKEY) tmp
787left join [DB_ANTARABTH_2019_Development].[dbo].[TrxSPM] sm on sm.No_Spm=tmp.NOSPM_u
788where sm.Nm_Penerima!=tmp.NamaPenerima_u or sm.Rek_Penerima!=tmp.RekPenerima_u or not exists (select * from [DB_ANTARABTH_2019_Development].[dbo].[TrxSPM] sm where sm.No_Spm=tmp.NOSPM_u)
789
790delete from [DB_ANTARABTH_2019_Development].[dbo].[TrxSPM] where Status=2 or No_Spm in (select No_Spm from [TrxSPM_Temp])
791insert into [DB_ANTARABTH_2019_Development].[dbo].[TrxSPM]
792select * from [DB_ANTARABTH_2019_Development].[dbo].[TrxSPM_Temp]
793
794insert into [DB_ANTARABTH_2019_Development].[dbo].[TrxSPM] ([Tahun],[No_Spm],[Nm_Penerima],[Bank_Penerima],[Rek_Penerima],[Status],[DateCreate],[Uraian],[Telp],[Kirim],[Keterangan],SKPD)
795select distinct CAST(year([TGLSPM]) AS smallint) as [Tahun],ISNULL(tb3.NOSPM,'') AS NOSPM,
796case when tb3.KDP3 IS null then tb10.NAMA else tb11.NMP3 end AS NamaPenerima,
797case when tb3.KDP3 IS null then ISNULL(tb12.NMBANK,'') else ISNULL(tb11.NMBANK,'') end AS BankPenerima,
798case when tb3.KDP3 IS null then cast(tb9.REKBEND as varchar(50)) else cast(tb11.NORCP3 as varchar(50)) end AS RekPenerima,
7990 as Status,cast(GETDATE() as DATE) as datecreate,null AS uraian, 0 as kirim,
800case when tb3.KDP3 IS null then isnull(u.TELEPON,'') else isnull(tb11.TELEPON,'') end AS TELEPON,tb3.KEPERLUAN as Keterangan, ltrim(rtrim(tb4.NMUNIT)) as SKPD
801from V@LID49V6_2019..ANTARBYR tb3
802left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb3.KEYBEND
803left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
804left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb3.KDP3
805left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
806left join V@LID49V6_2019..DAFTUNIT u on u.UNITKEY=tb9.UNITKEY
807left join V@LID49V6_2019..DAFTUNIT tb4 on tb4.UNITKEY=tb3.UNITKEY
808where tb3.NOSPM in (select NOSPM FROM [V@LID49V6_2019].[dbo].[SP2D] s
809 where s.NOSP2D in (SELECT [NOSP2D]FROM V@LID49V6_2019..[DP] d LEFT JOIN V@LID49V6_2019..[DPDET] t ON t.NODP=d.NODP
810 WHERE cast([TGLDP]AS date) > cast(getdate()-30 AS date)
811 ))
812 and
813 NOT EXISTS ( SELECT spmt.No_Spm FROM DB_ANTARABTH_2019_Development.dbo.[TrxSPM] spmt where spmt.No_Spm=tb3.NOSPM)
814GO
815
816-- JOB
817
818USE [msdb]
819GO
820
821/****** Object: Job [run_sp2d_batanghari_development] Script Date: 11/27/2019 19:56:27 ******/
822BEGIN TRANSACTION
823DECLARE @ReturnCode INT
824SELECT @ReturnCode = 0
825/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 11/27/2019 19:56:27 ******/
826IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
827BEGIN
828EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
829IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
830
831END
832
833DECLARE @jobId BINARY(16)
834EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'run_sp2d_batanghari_development',
835 @enabled=1,
836 @notify_level_eventlog=0,
837 @notify_level_email=0,
838 @notify_level_netsend=0,
839 @notify_level_page=0,
840 @delete_level=0,
841 @description=N'No description available.',
842 @category_name=N'[Uncategorized (Local)]',
843 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
844IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
845/****** Object: Step [run_sp2d_step_development] Script Date: 11/27/2019 19:56:27 ******/
846EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run_sp2d_step_development',
847 @step_id=1,
848 @cmdexec_success_code=0,
849 @on_success_action=1,
850 @on_success_step_id=0,
851 @on_fail_action=2,
852 @on_fail_step_id=0,
853 @retry_attempts=0,
854 @retry_interval=0,
855 @os_run_priority=0, @subsystem=N'TSQL',
856 @command=N'sp_InsertSp2d_PotonganSp2d',
857 @database_name=N'DB_ANTARABTH_2019_Development',
858 @flags=0
859IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
860EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
861IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
862EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'run_sp2d_step_development',
863 @enabled=1,
864 @freq_type=4,
865 @freq_interval=1,
866 @freq_subday_type=4,
867 @freq_subday_interval=1,
868 @freq_relative_interval=0,
869 @freq_recurrence_factor=0,
870 @active_start_date=20180101,
871 @active_end_date=99991231,
872 @active_start_time=70000,
873 @active_end_time=220059,
874 @schedule_uid=N'6dd2e9ba-bf3e-4fd7-8c39-570c553728e2'
875IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
876EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
877IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
878COMMIT TRANSACTION
879GOTO EndSave
880QuitWithRollback:
881 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
882EndSave:
883
884GO