· 6 years ago · Oct 16, 2019, 04:08 AM
1/****** Object: Table [dbo].[TrxSp2d_temp] Script Date: 10/08/2019 16:50:37 ******/
2SET ANSI_NULLS ON
3GO
4SET QUOTED_IDENTIFIER ON
5GO
6SET ANSI_PADDING ON
7GO
8CREATE TABLE [dbo].[TrxSp2d_temp](
9 [Kd_Urusan] [tinyint] NOT NULL,
10 [Kd_Bidang] [tinyint] NOT NULL,
11 [Kd_Unit] [tinyint] NOT NULL,
12 [Kd_Sub] [tinyint] NOT NULL,
13 [Tahun] [smallint] NOT NULL,
14 [No_Sp2d] [varchar](50) NOT NULL,
15 [Tgl_Sp2d] [date] NOT NULL,
16 [No_Spm] [varchar](50) NOT NULL,
17 [Tgl_Spm] [date] NOT NULL,
18 [Jn_Spm] [varchar](2) NOT NULL,
19 [Nm_Penerima] [varchar](100) NOT NULL,
20 [Keterangan] [varchar](255) NULL,
21 [NPWP] [varchar](20) NOT NULL,
22 [Bank_Penerima] [varchar](50) NOT NULL,
23 [Rek_Penerima] [varchar](50) NOT NULL,
24 [Tgl_Penguji] [date] NOT NULL,
25 [Nm_Bank] [varchar](50) NOT NULL,
26 [No_Rekening] [varchar](50) NOT NULL,
27 [Nilai] [money] NOT NULL,
28 [DateCreate] [date] NOT NULL,
29 [Cair] [tinyint] NOT NULL,
30 [TglCair] [date] NULL,
31 [Gaji] [tinyint] NOT NULL,
32 [Nm_Unit] [varchar](255) NOT NULL,
33 [Nm_Sub_Unit] [varchar](255) NOT NULL,
34 [Uraian] [varchar](255) NULL,
35 [Kirim] [tinyint] NOT NULL
36) ON [PRIMARY]
37GO
38SET ANSI_PADDING OFF
39GO
40/****** Object: Table [dbo].[TrxSp2d_Potongan] Script Date: 10/08/2019 16:50:37 ******/
41SET ANSI_NULLS ON
42GO
43SET QUOTED_IDENTIFIER ON
44GO
45SET ANSI_PADDING ON
46GO
47CREATE TABLE [dbo].[TrxSp2d_Potongan](
48 [Tahun] [nchar](10) NULL,
49 [Kd_Urusan] [smallint] NOT NULL,
50 [Kd_Bidang] [tinyint] NOT NULL,
51 [Kd_Unit] [tinyint] NOT NULL,
52 [Kd_Sub] [tinyint] NOT NULL,
53 [No_Sp2d] [varchar](50) NOT NULL,
54 [Kd_Rek_1] [tinyint] NOT NULL,
55 [Kd_Rek_2] [tinyint] NOT NULL,
56 [Kd_Rek_3] [tinyint] NOT NULL,
57 [Kd_Rek_4] [tinyint] NOT NULL,
58 [Kd_Rek_5] [tinyint] NOT NULL,
59 [No_SPM] [varchar](50) NOT NULL,
60 [Jn_SPM] [varchar](2) NOT NULL,
61 [Nm_Rekening] [varchar](255) NOT NULL,
62 [Nilai] [money] NOT NULL,
63 [kirim] [tinyint] NOT NULL,
64 CONSTRAINT [PK_TrxSp2d_Potongan] PRIMARY KEY CLUSTERED
65(
66 [No_Sp2d] ASC,
67 [Kd_Rek_1] ASC,
68 [Kd_Rek_2] ASC,
69 [Kd_Rek_3] ASC,
70 [Kd_Rek_4] ASC,
71 [Kd_Rek_5] ASC
72)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
73) ON [PRIMARY]
74GO
75SET ANSI_PADDING OFF
76GO
77/****** Object: Table [dbo].[TrxSp2d] Script Date: 10/08/2019 16:50:37 ******/
78SET ANSI_NULLS ON
79GO
80SET QUOTED_IDENTIFIER ON
81GO
82SET ANSI_PADDING ON
83GO
84CREATE TABLE [dbo].[TrxSp2d](
85 [Kd_Urusan] [tinyint] NOT NULL,
86 [Kd_Bidang] [tinyint] NOT NULL,
87 [Kd_Unit] [tinyint] NOT NULL,
88 [Kd_Sub] [tinyint] NOT NULL,
89 [Tahun] [smallint] NOT NULL,
90 [No_Sp2d] [varchar](50) NOT NULL,
91 [Tgl_Sp2d] [datetime] NOT NULL,
92 [No_Spm] [varchar](50) NOT NULL,
93 [Tgl_Spm] [datetime] NOT NULL,
94 [Jn_Spm] [varchar](2) NOT NULL,
95 [Nm_Penerima] [varchar](100) NOT NULL,
96 [Keterangan] [varchar](255) NULL,
97 [NPWP] [varchar](20) NOT NULL,
98 [Bank_Penerima] [varchar](50) NOT NULL,
99 [Rek_Penerima] [varchar](50) NOT NULL,
100 [Tgl_Penguji] [datetime] NOT NULL,
101 [Nm_Bank] [varchar](50) NOT NULL,
102 [No_Rekening] [varchar](50) NOT NULL,
103 [Nilai] [money] NOT NULL,
104 [DateCreate] [datetime] NOT NULL,
105 [Cair] [tinyint] NOT NULL,
106 [TglCair] [datetime] NULL,
107 [Gaji] [tinyint] NOT NULL,
108 [Nm_Unit] [varchar](255) NOT NULL,
109 [Nm_Sub_Unit] [varchar](255) NOT NULL,
110 [Uraian] [varchar](255) NULL,
111 [Kirim] [tinyint] NOT NULL,
112 CONSTRAINT [PK_TrxSp2d] PRIMARY KEY CLUSTERED
113(
114 [No_Sp2d] ASC
115)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
116) ON [PRIMARY]
117GO
118SET ANSI_PADDING OFF
119GO
120/****** Object: Table [dbo].[Trx_Spm_temp] Script Date: 10/08/2019 16:50:37 ******/
121SET ANSI_NULLS ON
122GO
123SET QUOTED_IDENTIFIER ON
124GO
125SET ANSI_PADDING ON
126GO
127CREATE TABLE [dbo].[Trx_Spm_temp](
128 [Tahun] [nchar](10) NOT NULL,
129 [No_Spm] [varchar](50) NOT NULL,
130 [Nm_Penerima] [varchar](100) NOT NULL,
131 [Bank_Penerima] [varchar](50) NOT NULL,
132 [Rek_Penerima] [varchar](50) NOT NULL,
133 [Status] [tinyint] NOT NULL,
134 [DataCreate] [datetime] NOT NULL,
135 [Uraian] [varchar](max) NULL,
136 [Kirim] [tinyint] NULL,
137 [Telp] [varchar](20) NULL,
138 [Keterangan] [varchar](max) NULL
139) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
140GO
141SET ANSI_PADDING OFF
142GO
143/****** Object: Table [dbo].[Trx_Spm] Script Date: 10/08/2019 16:50:37 ******/
144SET ANSI_NULLS ON
145GO
146SET QUOTED_IDENTIFIER ON
147GO
148SET ANSI_PADDING ON
149GO
150CREATE TABLE [dbo].[Trx_Spm](
151 [Tahun] [nchar](10) NOT NULL,
152 [No_Spm] [varchar](50) NOT NULL,
153 [Nm_Penerima] [varchar](100) NOT NULL,
154 [Bank_Penerima] [varchar](50) NOT NULL,
155 [Rek_Penerima] [varchar](50) NOT NULL,
156 [Status] [tinyint] NOT NULL,
157 [DataCreate] [datetime] NOT NULL,
158 [Uraian] [varchar](max) NULL,
159 [Kirim] [tinyint] NULL,
160 [telp] [varchar](20) NULL,
161 [Keterangan] [varchar](max) NULL,
162 CONSTRAINT [PK_Trx_Spm] PRIMARY KEY CLUSTERED
163(
164 [No_Spm] ASC,
165 [Status] ASC
166)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
167) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
168GO
169SET ANSI_PADDING OFF
170GO
171/****** Object: Table [dbo].[DP_temp] Script Date: 10/08/2019 16:50:37 ******/
172SET ANSI_NULLS ON
173GO
174SET QUOTED_IDENTIFIER ON
175GO
176SET ANSI_PADDING ON
177GO
178CREATE TABLE [dbo].[DP_temp](
179 [NODP] [char](30) NULL,
180 [TGLDP] [datetime] NULL,
181 [BERITA] [varchar](254) NULL,
182 [TGLVALID] [datetime] NULL,
183 [NOSP2D] [char](50) NULL,
184 [UNITKEY] [char](10) NULL,
185 [ID_STAT] [char](1) NULL
186) ON [PRIMARY]
187GO
188SET ANSI_PADDING OFF
189GO
190/****** Object: Table [dbo].[MstPengguna] Script Date: 10/08/2019 16:50:37 ******/
191SET ANSI_NULLS ON
192GO
193SET QUOTED_IDENTIFIER ON
194GO
195CREATE TABLE [dbo].[MstPengguna](
196 [id] [nvarchar](20) NOT NULL,
197 [username] [nvarchar](25) NOT NULL,
198 [ip] [nvarchar](15) NOT NULL,
199 [password] [nvarchar](100) NOT NULL,
200 [nama] [nvarchar](100) NOT NULL,
201 [status] [int] NULL,
202 [token_akses] [nvarchar](100) NULL,
203 [created_at] [datetime] NOT NULL,
204 [updated_at] [datetime] NULL,
205 [created_by] [nvarchar](20) NOT NULL,
206 [updated_by] [nvarchar](20) NULL,
207 CONSTRAINT [pk-MstPengguna] PRIMARY KEY CLUSTERED
208(
209 [id] ASC
210)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
211) ON [PRIMARY]
212GO
213/****** Object: Table [dbo].[migration] Script Date: 10/08/2019 16:50:37 ******/
214SET ANSI_NULLS ON
215GO
216SET QUOTED_IDENTIFIER ON
217GO
218SET ANSI_PADDING ON
219GO
220CREATE TABLE [dbo].[migration](
221 [version] [varchar](180) NOT NULL,
222 [apply_time] [int] NULL,
223PRIMARY KEY CLUSTERED
224(
225 [version] ASC
226)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
227) ON [PRIMARY]
228GO
229SET ANSI_PADDING OFF
230GO
231/****** Object: Table [dbo].[MapUnit] Script Date: 10/08/2019 16:50:37 ******/
232SET ANSI_NULLS ON
233GO
234SET QUOTED_IDENTIFIER ON
235GO
236SET ANSI_PADDING ON
237GO
238CREATE TABLE [dbo].[MapUnit](
239 [UNITKEY] [char](10) NOT NULL,
240 [KDUNIT] [varchar](30) NULL,
241 [KDLEVEL] [char](2) NOT NULL,
242 [TYPE] [char](2) NULL,
243 [NMUNIT] [varchar](200) NULL,
244 [Kd_Urusan] [varchar](1) NULL,
245 [Kd_Bidang] [varchar](2) NULL,
246 [Kd_Unit] [varchar](2) NULL,
247 [Kd_Sub] [varchar](2) NULL
248) ON [PRIMARY]
249GO
250SET ANSI_PADDING OFF
251GO
252/****** Object: StoredProcedure [dbo].[wsp_kirimCSV] Script Date: 10/08/2019 16:50:39 ******/
253SET ANSI_NULLS ON
254GO
255SET QUOTED_IDENTIFIER ON
256GO
257CREATE PROCEDURE [dbo].[wsp_kirimCSV]
258AS
259SET DATEFORMAT dmy
260
261DECLARE @nmtahun CHAR(4)
262SET @nmtahun='2019'
263
264IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME LIKE '##sp2d%')
265 DROP TABLE ##sp2d
266IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME LIKE '##spm%')
267 DROP TABLE ##spm
268
269--select * INTO ##sp2d FROM [Sp2dOnline_BPP].[dbo].[TrxSp2d]
270select * INTO ##spm FROM [Sp2dOnline_BPP].[dbo].[Trx_Spm]
271
272declare @sqlsp2d nvarchar(4000),@sqlspm nvarchar(4000), @nmsp2d varchar(100),@nmspm varchar(100),@TEMP VARCHAR(4000)
273declare @SVR VARCHAR(100),@instnm varchar(100)
274set @TEMP = 'C:\SP2DonLine\'
275set @nmsp2d ='SP2D'+rtrim(@nmtahun)+replace(convert(char(10), GETDATE(), 103),'/','')
276set @nmspm ='SPM'+rtrim(@nmtahun)+replace(convert(char(10), GETDATE(), 103),'/','')
277select @instnm = (case when SERVERPROPERTY ('InstanceName') is null then '' else '\' + cast(SERVERPROPERTY ('InstanceName') as varchar(100)) end)
278set @SVR = (SELECT cast(SERVERPROPERTY('MachineName') as varchar(100)) + @instnm)
279
280SELECT @sqlsp2d = 'bcp "select distinct * from ##sp2d" '+ 'queryout ' + @TEMP + @nmsp2d+'.csv -c -t, -R -T -S ' + @SVR
281SELECT @sqlspm = 'bcp "select distinct * from ##spm" '+ 'queryout ' + @TEMP + @nmspm+'.csv -c -t, -R -T -S ' + @SVR
282
283--SELECT @sql = 'bcp "select distinct * from ##sp2d" '+ 'queryout ' + @TEMP + @nmfile+'.xls -c -T -S '+ @SVR
284
285SELECT @sqlsp2d
286EXEC master..xp_cmdshell @sqlsp2d, no_output
287
288SELECT @sqlspm
289EXEC master..xp_cmdshell @sqlspm, no_output
290GO
291/****** Object: StoredProcedure [dbo].[Insert_Test] Script Date: 10/08/2019 16:50:39 ******/
292SET ANSI_NULLS ON
293GO
294SET QUOTED_IDENTIFIER ON
295GO
296CREATE PROCEDURE [dbo].[Insert_Test]
297@nosp2d varchar(50)
298AS
299
300SET DATEFORMAT dmy
301
302--declare @nosp2d varchar(50)
303declare @nospm varchar(50)
304--set @nosp2d = LTRIM(RTRIM('0395/BTL/GJ/2018'))
305set @nospm = (SELECT LTRIM(RTRIM(NOSPM)) FROM V@LID49V6_2019..SP2D WHERE NOSP2D=@nosp2d)
306
307insert into [Sp2dOnline_BPP].[dbo].[Trx_Spm] ([Tahun],[No_Spm],[Nm_Penerima],[Bank_Penerima],[Rek_Penerima],[Status],[DataCreate],[Uraian],[Kirim])
308select distinct CAST(year([TGLSPM]) AS smallint) as [Tahun],ISNULL(tb3.NOSPM,'') AS NOSPM,
309case when tb3.KDP3 IS null then tb10.NAMA else tb11.NMP3 end AS NamaPenerima,
310case when tb3.KDP3 IS null then ISNULL(tb12.NMBANK,'') else ISNULL(tb11.NMBANK,'') end AS BankPenerima,
311case when tb3.KDP3 IS null then cast(tb9.REKBEND as varchar(50)) else cast(tb11.NORCP3 as varchar(50)) end AS RekPenerima,
3120 as Status,cast(GETDATE() as DATE) as datecreate,null AS uraian, 0 as kirim
313from V@LID49V6_2019..ANTARBYR tb3
314left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb3.KEYBEND
315left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
316left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb3.KDP3
317left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
318where tb3.NOSPM = @nospm
319
320update [Sp2dOnline_BPP].[dbo].[Trx_Spm]
321set status=1 where status=0
322
323insert into Sp2dOnline_BPP.dbo.TrxSP2D (Kd_Urusan,Kd_Bidang, Kd_Unit, Kd_Sub, Tahun, No_SP2D, Tgl_SP2D, No_SPM, Tgl_SPM,
324Jn_SPM, Nm_Penerima, Keterangan ,NPWP, Bank_Penerima, Rek_Penerima, Tgl_Penguji, Nm_Bank,No_Rekening, Nilai, DateCreate,
325Cair,TglCair,gaji,Nm_Unit,Nm_Sub_Unit,uraian)
326select Kd_Urusan,Kd_Bidang,Kd_Unit,Kd_Sub,Tahun,ltrim(rtrim(sp.NOSP2D)),TGLSP2D,sp.NOSPM,TGLSPM,LBLSTATUS,NamaPenerima,Keterangan,
327NPWPPenerima,BankPenerima,RekPenerima,Tgl_Penguji,Nm_Bank,REKBEND,nilai-isnull(NilPot,0) as nilai,datecreate,cair,
328TglCair,gaji,Nm_Unit,Nm_Sub_Unit,uraian
329from (
330select LEFT(tb4.KDUNIT,1) as [Kd_Urusan],SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],
331SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub],CAST(year([TGLSP2D]) AS smallint) as [Tahun],ltrim(rtrim(tb1.NOSP2D)) as [NOSP2D],tb1.TGLSP2D,
332ISNULL(ltrim(rtrim(tb3.NOSPM)),'') AS NOSPM,ISNULL(tb3.TGLSPM,0) AS TGLSPM,cast(tb7.LBLSTATUS as varchar(2)) as LBLSTATUS,
333case when tb1.KDP3 IS null then tb10.NAMA else tb11.NMP3 end AS NamaPenerima,'' AS Keterangan,
334case when tb1.KDP3 IS null then cast(tb9.NPWPBEND as varchar(20)) else cast(tb11.NPWP as varchar(20)) end AS NPWPPenerima,
335case when tb1.KDP3 IS null then ISNULL(tb12.NMBANK,'') else ISNULL(tb11.NMBANK,'') end AS BankPenerima,
336case when tb1.KDP3 IS null then cast(tb9.REKBEND as varchar(50)) else cast(tb11.NORCP3 as varchar(50)) end AS RekPenerima,
337ISNULL(tb1.TGLVALID,0) as Tgl_Penguji,ISNULL(r.NMBKAS,'') as Nm_Bank, cast(r.NOREKB as varchar(50)) as REKBEND,
338tb.NilSp2d-ISNULL(tb2.NilPot,0) as nilai,GETDATE() as datecreate,0 as cair,null as TglCair,
339case when CHARINDEX('GJ', tb1.NOSP2D)=0 then 0 else 1 end as gaji,tbu.NMUNIT as Nm_Unit,tb4.nmunit as Nm_Sub_Unit,
340left(tb1.KEPERLUAN,255) as uraian
341from V@LID49V6_2019..SP2D tb1
342left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=tb1.NOSPM
343left join V@LID49V6_2019..DAFTUNIT tb4 on tb4.UNITKEY=tb1.UNITKEY
344left join V@LID49V6_2019..DAFTUNIT tbu on tbu.KDUNIT=LEFT(tb4.KDUNIT,1)+'.'+SUBSTRING(tb4.KDUNIT,3,2)+'.'+SUBSTRING(tb4.KDUNIT,6,2)+'.'
345left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
346left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb1.KEYBEND
347left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
348left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb1.KDP3
349left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
350left join (select NOSP2D,SUM(nilai) as NilSp2d from V@LID49V6_2019..SP2DDETR GROUP BY NOSP2D ) tb on tb.NOSP2D=tb1.NOSP2D
351left join (select NOSP2D,SUM(nilai) as NilPot from V@LID49V6_2019..SP2DDETB GROUP BY NOSP2D ) tb2 on tb2.NOSP2D=tb1.NOSP2D,
352(SELECT NMBKAS,NOREKB from V@LID49V6_2019..bkbkas where NOBBANTU='01') r
353where NilSp2d<>0
354and tb1.NOSP2D = @nosp2d
355
356--and cast(CONVERT(varchar(11),tb1.TGLVALID,103)AS date) > cast(CONVERT(varchar(11),getdate()-4,103)AS date)
357--and tb1.NOSP2D in (SELECT NOSP2D FROM V@LID49V6_2019..[DP_temp])
358--and tb1.NOSPM in (SELECT No_Spm FROM Sp2dOnline_BPP..Trx_Spm where Status=1)
359
360union
361select LEFT(tb4.KDUNIT,1) as [Kd_Urusan],SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],
362SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub],CAST(year([TGLSP2D]) AS smallint) as [Tahun],ltrim(rtrim(tb1.NOSP2D)) as [NOSP2D],tb1.TGLSP2D,
363ISNULL(ltrim(rtrim(tb3.NOSPM)),''),ISNULL(tb3.TGLSPM,0),cast(tb7.LBLSTATUS as varchar(2)) as LBLSTATUS,
364case when tb1.KDP3 IS null then tb10.NAMA else tb11.NMP3 end AS NamaPenerima,'' AS Keterangan,
365case when tb1.KDP3 IS null then cast(tb9.NPWPBEND as varchar(20)) else cast(tb11.NPWP as varchar(20)) end AS NPWPPenerima,
366case when tb1.KDP3 IS null then ISNULL(tb12.NMBANK,'') else ISNULL(tb11.NMBANK,'') end AS BankPenerima,
367case when tb1.KDP3 IS null then cast(tb9.REKBEND as varchar(50)) else cast(tb11.NORCP3 as varchar(50)) end AS RekPenerima,
368ISNULL(tb1.TGLVALID,0) as Tgl_Penguji,ISNULL(r.NMBKAS,'') as Nm_Bank, cast(r.NOREKB as varchar(50)) as REKBEND,tb.NilSp2d, GETDATE() as datecreate,
3690 as cair,null as TglCair,case when CHARINDEX('GJ', tb1.NOSP2D)=0 then 0 else 1 end as gaji
370,tbu.NMUNIT as Nm_Unit,tb4.nmunit as Nm_Sub_Unit,left(tb1.KEPERLUAN,255) as uraian
371from V@LID49V6_2019..SP2D tb1
372left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=tb1.NOSPM
373left join V@LID49V6_2019..DAFTUNIT tb4 on tb4.UNITKEY=tb1.UNITKEY
374left join V@LID49V6_2019..DAFTUNIT tbu on tbu.KDUNIT=LEFT(tb4.KDUNIT,1)+'.'+SUBSTRING(tb4.KDUNIT,3,2)+'.'+SUBSTRING(tb4.KDUNIT,6,2)+'.'
375left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
376left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb1.KEYBEND
377left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
378left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb1.KDP3
379left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
380left join (select tb2.NOSP2D,SUM(nilai) as NilSp2d from V@LID49V6_2019..SP2DDETRTL tb2 GROUP BY NOSP2D ) tb on tb.NOSP2D=tb1.NOSP2D,
381(SELECT NMBKAS,NOREKB from V@LID49V6_2019..bkbkas where NOBBANTU='01') r
382where NilSp2d<>0 and tb1.NOSP2D = @nosp2d
383--and tb1.NOSP2D in (SELECT NOSP2D FROM V@LID49V6_2019..[DP_temp] )
384--and cast(CONVERT(varchar(11),tb1.TGLVALID,103)AS date) > cast(CONVERT(varchar(11),getdate()-4,103)AS date)
385--and tb1.NOSPM in (SELECT No_Spm FROM Sp2dOnline_BPP..Trx_Spm where Status=1)
386) sp
387 left join (select p.NOSP2D,s.NOSPM,sum(isnull(NILAI,0)) as NilPot from V@LID49V6_2019..SP2DPJK p
388 left join V@LID49V6_2019..SP2D s on s.NOSP2D=p.NOSP2D and s.UNITKEY=p.UNITKEY
389 group by p.NOSP2D,s.NOSPM) p on p.NOSP2D=sp.NOSP2D and p.NOSPM=sp.NOSPM
390WHERE NOT EXISTS ( SELECT * FROM Sp2dOnline_BPP.dbo.TrxSP2D sptr where sptr.No_SP2D=sp.NOSP2D AND sptr.Cair IN(0,1)
391AND sptr.Nm_Sub_Unit=sp.Nm_Sub_Unit)
392
393----- ISI NILAI SP2D POTONGAN
394--delete FROM TrxSP2D_Potongan
395insert into Sp2dOnline_BPP.dbo.TrxSP2D_Potongan
396(Tahun, Kd_Urusan, Kd_Bidang, Kd_Unit, Kd_Sub, No_SP2D,Kd_Rek_1, Kd_Rek_2, Kd_Rek_3, Kd_Rek_4, Kd_Rek_5, No_SPM, Jn_SPM, Nm_Rekening, Nilai)
397select cast(year([TGLSP2D]) as smallint) as [Tahun],LEFT(KDUNIT,1) as [Kd_Urusan],SUBSTRING(KDUNIT,3,2) as [Kd_Bidang],
398SUBSTRING(KDUNIT,6,2) as [Kd_Unit],SUBSTRING(KDUNIT,9,2) as [Kd_Sub],ltrim(rtrim(sp.NOSP2D)) as No_SP2D,
399cast(substring(KDPER,1,1) as tinyint) as Kd_Rek_1,
400cast(substring(KDPER,3,1) as tinyint) as Kd_Rek_2,
401cast(substring(KDPER,5,1) as tinyint) as Kd_Rek_3,
402cast(substring(KDPER,7,2) as tinyint) as Kd_Rek_4,
403cast(substring(KDPER,10,3) as tinyint) as Kd_Rek_5,
404ltrim(rtrim(sp.NOSPM)),cast(tb7.LBLSTATUS as varchar(2)) as jns,sp.NMPER,sp.NILAI
405from (
406select s.NOSP2D,s.TGLSP2D,s.NOSPM,s.UNITKEY,m.KDPER,m.NMPER,r.NILAI
407from V@LID49V6_2019..SP2D s
408left join V@LID49V6_2019..SP2DDETB r on r.NOSP2D=s.NOSP2D and r.UNITKEY=s.UNITKEY
409 and (r.NOJETRA = '23' OR r.NOJETRA = '10')
410LEFT JOIN V@LID49V6_2019..MATANGB m ON m.MTGKEY = r.MTGKEY
411where KDPER is not null
412--and s.NOSP2D in (select No_Sp2d from Sp2dOnline_BPP.dbo.TrxSP2D)
413and s.NOSP2D = @nosp2d
414
415
416union all
417
418select s.NOSP2D,s.TGLSP2D,s.NOSPM,s.UNITKEY,m.KDPER,m.NMPER,r.NILAI
419from V@LID49V6_2019..SP2D s
420left join V@LID49V6_2019..SP2DDETR r on r.NOSP2D=s.NOSP2D and r.UNITKEY=s.UNITKEY and
421 (r.NOJETRA = '23' OR r.NOJETRA = '10')
422LEFT JOIN V@LID49V6_2019..MATANGR m ON m.MTGKEY = r.MTGKEY
423where KDPER is not null and s.NOSP2D in (select No_Sp2d from Sp2dOnline_BPP.dbo.TrxSP2D)
424union all
425select s.NOSP2D,s.TGLSP2D,s.NOSPM,s.UNITKEY,
426case when tb2.PJKKEY ='8_' then '4.1.1.02.01' else
427case when tb2.PJKKEY ='9_' then '4.1.1.02.02' else
428case when tb2.PJKKEY ='10_' then '4.1.1.02.05' else
429case when tb2.PJKKEY ='1_' then '4.1.1.12.01' else
430case when tb2.PJKKEY ='11_' then '4.1.1.12.08' else
431case when tb2.PJKKEY ='12_' then '4.1.1.12.09' else
432case when tb2.PJKKEY ='2_' then '4.1.1.12.02' else
433case when tb2.PJKKEY ='3_' then '4.1.1.12.03' else
434case when tb2.PJKKEY ='13_' then '4.1.1.12.04' else
435case when tb2.PJKKEY ='5_' then '4.1.1.21.01' end end end end end end end end end end as KDPER,
436NMPAJAK as NMPER,p.NILAI from V@LID49V6_2019..SP2D s
437left join V@LID49V6_2019..SP2DDETR r on r.NOSP2D=s.NOSP2D and r.UNITKEY=s.UNITKEY
438left join V@LID49V6_2019..SP2DPJK p on p.NOSP2D=s.NOSP2D and p.UNITKEY=s.UNITKEY
439LEFT JOIN V@LID49V6_2019..JPAJAK tb2 ON p.PJKKEY = tb2.PJKKEY
440where KDPAJAK is not null
441and s.NOSP2D = @nosp2d
442) sp
443left join V@LID49V6_2019..DAFTUNIT u ON u.UNITKEY=sp.UNITKEY
444left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=sp.NOSPM
445left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
446WHERE NOT EXISTS ( SELECT spot.No_SP2D,spot.No_SPM FROM Sp2dOnline_BPP.dbo.TrxSP2D_Potongan spot
447where spot.No_SP2D=sp.NOSP2D AND spot.No_SPM=sp.NOSPM)
448GO
449/****** Object: StoredProcedure [dbo].[sp_InsertSpm] Script Date: 10/08/2019 16:50:39 ******/
450SET ANSI_NULLS ON
451GO
452SET QUOTED_IDENTIFIER ON
453GO
454CREATE PROCEDURE [dbo].[sp_InsertSpm]
455--@UNITKEY CHAR(10)
456AS
457
458SET DATEFORMAT dmy
459
460--if not exists (select name from [Sp2dOnline_BPP]..sysobjects where name = 'DP_temp')
461-- begin
462-- CREATE TABLE [Sp2dOnline_BPP].[dbo].[DP_temp]
463-- ([NODP] char(30),[TGLDP] datetime,[BERITA] varchar(254),[TGLVALID] datetime,
464-- [NOSP2D] char(50),[UNITKEY] char(10), ID_STAT char(1))
465-- end
466
467if not exists (select name from [Sp2dOnline_BPP]..sysobjects where name = 'Trx_Spm_temp')
468 begin
469 CREATE TABLE [dbo].[Trx_Spm_temp]
470 ([Tahun] [nchar](10) NOT NULL,
471 [No_Spm] [varchar](50) NOT NULL,
472 [Nm_Penerima] [varchar](100) NOT NULL,
473 [Bank_Penerima] [varchar](50) NOT NULL,
474 [Rek_Penerima] [varchar](50) NOT NULL,
475 [Status] [tinyint] NOT NULL,
476 [DataCreate] [datetime] NOT NULL,
477 [Uraian] [varchar](max) NULL,
478 [Kirim] [tinyint] NULL,
479 [Telp] varchar(20),
480 [Keterangan] [varchar](max) NULL)
481 end
482
483--delete [Sp2dOnline_BPP].[dbo].[DP_temp]
484--INSERT INTO [Sp2dOnline_BPP].[dbo].[DP_temp]
485--SELECT t.[NODP],[TGLDP],[BERITA],[TGLVALID],[NOSP2D],[UNITKEY],'0' AS ID_STAT
486--FROM V@LID49V6_2019..[DP] d LEFT JOIN V@LID49V6_2019..[DPDET] t ON t.NODP=d.NODP
487--WHERE cast(CONVERT(varchar(11),[TGLDP],103)AS date) > cast(CONVERT(varchar(11),getdate()-4,103)AS date)
488--AND NOT EXISTS (SELECT * FROM [Sp2dOnline_BPP].[dbo].[DP_temp] dt
489-- WHERE dt.NODP=t.NODP and dt.NOSP2D=t.NOSP2D AND dt.UNITKEY=t.UNITKEY)
490
491delete [Sp2dOnline_BPP].[dbo].[Trx_Spm_temp]
492insert into [Sp2dOnline_BPP].[dbo].[Trx_Spm_temp]
493select Tahun_u,NOSPM_u,NamaPenerima_u,BankPenerima_u,RekPenerima_u,Status_u,datecreate_u,null,kirim_u,TELEPON,uraian_u
494from (
495select distinct CAST(year([TGLSPM]) AS smallint) as [Tahun_u],ISNULL(ltrim(rtrim(tb3.NOSPM)),'') AS NOSPM_u,
496case when tb3.KDP3 IS null then tb10.NAMA else tb11.NMP3 end AS NamaPenerima_u,
497case when tb3.KDP3 IS null then ISNULL(tb12.NMBANK,'') else ISNULL(tb11.NMBANK,'') end AS BankPenerima_u,
498case when tb3.KDP3 IS null then cast(tb9.REKBEND as varchar(50)) else cast(tb11.NORCP3 as varchar(50)) end AS RekPenerima_u,
4990 as Status_u,cast(GETDATE() as DATE) as datecreate_u,keperluan AS uraian_u, 0 as kirim_u,
500case when tb3.KDP3 IS null then isnull(u.TELEPON,'') else isnull(tb11.TELEPON,'') end AS TELEPON
501from V@LID49V6_2019..ANTARBYR tb3
502left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb3.KEYBEND
503left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
504left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb3.KDP3
505left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
506left join V@LID49V6_2019..DAFTUNIT u on u.UNITKEY=tb9.UNITKEY
507where tb3.NOSPM in
508(select No_Spm FROM [Sp2dOnline_BPP].[dbo].[Trx_Spm] where Status=2)) tmp
509left join [Sp2dOnline_BPP].[dbo].[Trx_Spm] sm on sm.No_Spm=tmp.NOSPM_u
510where sm.Nm_Penerima<>tmp.NamaPenerima_u or sm.Rek_Penerima<>tmp.RekPenerima_u
511
512delete from [Sp2dOnline_BPP].[dbo].[Trx_Spm] where Status=2 and No_Spm in (select No_Spm from [Trx_Spm_temp])
513insert into [Sp2dOnline_BPP].[dbo].[Trx_Spm]
514select * from [Sp2dOnline_BPP].[dbo].[Trx_Spm_temp]
515
516insert into [Sp2dOnline_BPP].[dbo].[Trx_Spm] ([Tahun],[No_Spm],[Nm_Penerima],[Bank_Penerima],[Rek_Penerima],[Status],[DataCreate],[Uraian],[Kirim],[Telp],[Keterangan])
517select distinct CAST(year([TGLSPM]) AS smallint) as [Tahun],ISNULL(tb3.NOSPM,'') AS NOSPM,
518case when tb3.KDP3 IS null then tb10.NAMA else tb11.NMP3 end AS NamaPenerima,
519case when tb3.KDP3 IS null then ISNULL(tb12.NMBANK,'') else ISNULL(tb11.NMBANK,'') end AS BankPenerima,
520case when tb3.KDP3 IS null then cast(tb9.REKBEND as varchar(50)) else cast(tb11.NORCP3 as varchar(50)) end AS RekPenerima,
5210 as Status,cast(GETDATE() as DATE) as datecreate,null AS uraian, 0 as kirim,
522case when tb3.KDP3 IS null then isnull(u.TELEPON,'') else isnull(tb11.TELEPON,'') end AS TELEPON,tb3.KEPERLUAN as Keterangan
523from V@LID49V6_2019..ANTARBYR tb3
524left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb3.KEYBEND
525left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
526left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb3.KDP3
527left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
528left join V@LID49V6_2019..DAFTUNIT u on u.UNITKEY=tb9.UNITKEY
529where tb3.NOSPM in (select NOSPM FROM [V@LID49V6_2019].[dbo].[SP2D] s
530 where s.NOSP2D in (SELECT [NOSP2D]FROM V@LID49V6_2019..[DP] d LEFT JOIN V@LID49V6_2019..[DPDET] t ON t.NODP=d.NODP
531 WHERE cast(CONVERT(varchar(11),[TGLDP],103)AS date) > cast(CONVERT(varchar(11),getdate()-4,103)AS date)
532 ))
533 and
534 NOT EXISTS ( SELECT spmt.No_Spm FROM Sp2dOnline_BPP.dbo.[Trx_Spm] spmt where spmt.No_Spm=tb3.NOSPM)
535GO
536/****** Object: StoredProcedure [dbo].[sp_InsertSp2d_PotonganSp2d] Script Date: 10/08/2019 16:50:39 ******/
537SET ANSI_NULLS ON
538GO
539SET QUOTED_IDENTIFIER ON
540GO
541CREATE PROCEDURE [dbo].[sp_InsertSp2d_PotonganSp2d]
542--@UNITKEY CHAR(10)
543AS
544
545--SET DATEFORMAT dmy
546
547exec [sp_InsertSpm]
548
549--if not exists (select name from V@LID49V6_2019..sysobjects where name = 'DP_temp')
550-- begin
551-- CREATE TABLE V@LID49V6_2019..[DP_temp]
552-- ([NODP] char(30),[TGLDP] datetime,[BERITA] varchar(254),[TGLVALID] datetime,
553-- [NOSP2D] char(50),[UNITKEY] char(10), ID_STAT char(1))
554-- end
555--INSERT INTO V@LID49V6_2019..[DP_temp]
556--SELECT top 1 t.[NODP],[TGLDP],[BERITA],[TGLVALID],[NOSP2D],[UNITKEY],'0' AS ID_STAT
557--FROM V@LID49V6_2019..[DP] d LEFT JOIN V@LID49V6_2019..[DPDET] t ON t.NODP=d.NODP
558--WHERE cast(CONVERT(varchar(11),[TGLDP],103)AS date) > cast(CONVERT(varchar(11),getdate()-4,103)AS date)
559--AND NOT EXISTS (SELECT * FROM V@LID49V6_2019..[DP_temp] dt
560-- WHERE dt.NODP=t.NODP and dt.NOSP2D=t.NOSP2D AND dt.UNITKEY=t.UNITKEY)
561
562--select UNITKEY,KDUNIT,KDLEVEL,TYPE,NMUNIT,LEFT(tb4.KDUNIT,1) as [Kd_Urusan],SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],
563--SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub]
564--INTO Sp2dOnline_BPP..MapUnit
565--from V@LID49V6_2019..DAFTUNIT tb4 where KDLEVEL=3
566--order by KDUNIT
567
568insert into Sp2dOnline_BPP.dbo.TrxSP2D (
569 Kd_Urusan,
570 Kd_Bidang,
571 Kd_Unit,
572 Kd_Sub,
573 Tahun,
574 No_SP2D,
575 Tgl_SP2D,
576 No_SPM,
577 Tgl_SPM,
578 Jn_SPM,
579 Nm_Penerima,
580 Keterangan,
581 NPWP,
582 Bank_Penerima,
583 Rek_Penerima,
584 Tgl_Penguji,
585 Nm_Bank,
586 No_Rekening,
587 Nilai,
588 DateCreate,
589 Cair,
590 TglCair,
591 gaji,
592 Nm_Unit,
593 Nm_Sub_Unit,
594 uraian
595)
596
597select
598 Kd_Urusan,
599 Kd_Bidang,
600 Kd_Unit,
601 Kd_Sub,
602 Tahun,
603 ltrim(rtrim(sp.NOSP2D)),
604 TGLSP2D,
605 sp.NOSPM,
606 TGLSPM,
607 LBLSTATUS,
608 NamaPenerima,
609 Keterangan,
610 NPWPPenerima,
611 BankPenerima,
612 RekPenerima,
613 Tgl_Penguji,
614 Nm_Bank,
615 REKBEND,
616 nilai-isnull(NilPot,0) as nilai,
617 datecreate,
618 cair,
619 TglCair,
620 gaji,
621 Nm_Unit,
622 Nm_Sub_Unit,
623 uraian
624from (
625 select
626 LEFT(tb4.KDUNIT,1) as [Kd_Urusan],
627 SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],
628 SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],
629 SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub],
630 CAST(year([TGLSP2D]) AS smallint) as [Tahun],
631 ltrim(rtrim(tb1.NOSP2D)) as [NOSP2D],
632 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLSP2D,120)), '.000',''), 103) as date) as TGLSP2D,
633 ISNULL(ltrim(rtrim(tb3.NOSPM)),'') AS NOSPM,
634 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb3.TGLSPM,120)), '.000',''), 103) as date) AS TGLSPM,
635 cast(tb7.LBLSTATUS as varchar(2)) as LBLSTATUS,
636 case
637 when tb1.KDP3 IS null then tb10.NAMA
638 else tb11.NMP3
639 end AS NamaPenerima,
640 '' AS Keterangan,
641 case
642 when tb1.KDP3 IS null then cast(tb9.NPWPBEND as varchar(20))
643 else cast(tb11.NPWP as varchar(20))
644 end AS NPWPPenerima,
645 case
646 when tb1.KDP3 IS null then ISNULL(tb12.NMBANK,'')
647 else ISNULL(tb11.NMBANK,'')
648 end AS BankPenerima,
649 case
650 when tb1.KDP3 IS null then cast(tb9.REKBEND as varchar(50))
651 else cast(tb11.NORCP3 as varchar(50))
652 end AS RekPenerima,
653 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLVALID,120)), '.000',''), 103) as date) as Tgl_Penguji,
654 ISNULL(r.NMBKAS,'') as Nm_Bank,
655 cast(r.NOREKB as varchar(50)) as REKBEND,
656 tb.NilSp2d-ISNULL(tb2.NilPot,0) as nilai,
657 GETDATE() as datecreate,
658 0 as cair,
659 null as TglCair,
660 case
661 when CHARINDEX('GJ', tb1.NOSP2D)=0 then 0
662 else 1
663 end as gaji,
664 tbu.NMUNIT as Nm_Unit,
665 tb4.nmunit as Nm_Sub_Unit,
666 left(tb1.KEPERLUAN,255) as uraian
667 from V@LID49V6_2019..SP2D tb1
668 left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=tb1.NOSPM
669 left join V@LID49V6_2019..DAFTUNIT tb4 on tb4.UNITKEY=tb1.UNITKEY
670 left join V@LID49V6_2019..DAFTUNIT tbu on tbu.KDUNIT=LEFT(tb4.KDUNIT,1)+'.'+SUBSTRING(tb4.KDUNIT,3,2)+'.'+SUBSTRING(tb4.KDUNIT,6,2)+'.'
671 left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
672 left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb1.KEYBEND
673 left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
674 left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb1.KDP3
675 left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
676 left join (select NOSP2D,SUM(nilai) as NilSp2d from V@LID49V6_2019..SP2DDETR GROUP BY NOSP2D) tb on tb.NOSP2D=tb1.NOSP2D
677 left join (select NOSP2D,SUM(nilai) as NilPot from V@LID49V6_2019..SP2DDETB GROUP BY NOSP2D) tb2 on tb2.NOSP2D=tb1.NOSP2D,
678 (SELECT NMBKAS,NOREKB from V@LID49V6_2019..bkbkas where NOBBANTU='01') r
679 where
680 NilSp2d<>0
681 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()-5,120)), '.000',''), 103) as date)
682 --and tb1.NOSP2D in (SELECT NOSP2D FROM V@LID49V6_2019..[DP_temp])
683 and tb1.NOSPM in (SELECT No_Spm FROM Sp2dOnline_BPP..Trx_Spm where Status=1)
684
685 union
686
687 select
688 LEFT(tb4.KDUNIT,1) as [Kd_Urusan],
689 SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],
690 SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],
691 SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub],
692 CAST(year([TGLSP2D]) AS smallint) as [Tahun],
693 ltrim(rtrim(tb1.NOSP2D)) as [NOSP2D],
694 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLSP2D,120)), '.000',''), 103) as date) as TGLSP2D,
695 ISNULL(ltrim(rtrim(tb3.NOSPM)),''),
696 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb3.TGLSPM,120)), '.000',''), 103) as date) as TGLSPM,
697 cast(tb7.LBLSTATUS as varchar(2)) as LBLSTATUS,
698 case
699 when tb1.KDP3 IS null then tb10.NAMA
700 else tb11.NMP3
701 end AS NamaPenerima,
702 '' AS Keterangan,
703 case
704 when tb1.KDP3 IS null then cast(tb9.NPWPBEND as varchar(20))
705 else cast(tb11.NPWP as varchar(20))
706 end AS NPWPPenerima,
707 case
708 when tb1.KDP3 IS null then ISNULL(tb12.NMBANK,'')
709 else ISNULL(tb11.NMBANK,'')
710 end AS BankPenerima,
711 case
712 when tb1.KDP3 IS null then cast(tb9.REKBEND as varchar(50))
713 else cast(tb11.NORCP3 as varchar(50))
714 end AS RekPenerima,
715 cast(CONVERT(VARCHAR(20), REPLACE(CONVERT(DATETIME, CONVERT(varchar(20),tb1.TGLVALID,120)), '.000',''), 103) as date) as Tgl_Penguji,
716 ISNULL(r.NMBKAS,'') as Nm_Bank,
717 cast(r.NOREKB as varchar(50)) as REKBEND,
718 tb.NilSp2d-ISNULL(tb2.NilPot,0) as nilai,
719 GETDATE() as datecreate,
720 0 as cair,
721 null as TglCair,
722 case
723 when CHARINDEX('GJ', tb1.NOSP2D)=0 then 0
724 else 1 end
725 as gaji,
726 tbu.NMUNIT as Nm_Unit,
727 tb4.nmunit as Nm_Sub_Unit,
728 left(tb1.KEPERLUAN,255) as uraian
729 from V@LID49V6_2019..SP2D tb1
730 left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=tb1.NOSPM
731 left join V@LID49V6_2019..DAFTUNIT tb4 on tb4.UNITKEY=tb1.UNITKEY
732 left join V@LID49V6_2019..DAFTUNIT tbu on tbu.KDUNIT=LEFT(tb4.KDUNIT,1)+'.'+SUBSTRING(tb4.KDUNIT,3,2)+'.'+SUBSTRING(tb4.KDUNIT,6,2)+'.'
733 left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
734 left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb1.KEYBEND
735 left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
736 left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb1.KDP3
737 left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
738 left join (select tb2.NOSP2D,SUM(nilai) as NilSp2d from V@LID49V6_2019..SP2DDETRTL tb2 GROUP BY NOSP2D ) tb on tb.NOSP2D=tb1.NOSP2D
739 left join (select NOSP2D,SUM(nilai) as NilPot from V@LID49V6_2019..SP2DDETB GROUP BY NOSP2D) tb2 on tb2.NOSP2D=tb1.NOSP2D,
740 (SELECT NMBKAS,NOREKB from V@LID49V6_2019..bkbkas where NOBBANTU='01') r
741 where
742 NilSp2d<>0 --and tb1.NOSP2D in (SELECT NOSP2D FROM V@LID49V6_2019..[DP_temp] )
743 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() -5,120)), '.000',''), 103) as date)
744 and tb1.NOSPM in (SELECT No_Spm FROM Sp2dOnline_BPP..Trx_Spm where Status=1)
745) sp
746left join (
747 select
748 p.NOSP2D,
749 s.NOSPM,
750 sum(isnull(NILAI,0)) as NilPot
751 from V@LID49V6_2019..SP2DPJK p
752 left join V@LID49V6_2019..SP2D s on s.NOSP2D=p.NOSP2D and s.UNITKEY=p.UNITKEY
753 group by p.NOSP2D,s.NOSPM
754) p on p.NOSP2D=sp.NOSP2D and p.NOSPM=sp.NOSPM
755WHERE
756 NOT EXISTS (
757 SELECT
758 *
759 FROM Sp2dOnline_BPP.dbo.TrxSP2D sptr
760 where
761 sptr.No_SP2D=sp.NOSP2D
762 AND sptr.Cair IN(0,1)
763 AND sptr.Nm_Sub_Unit=sp.Nm_Sub_Unit
764 )
765
766----- ISI NILAI SP2D POTONGAN
767--delete FROM TrxSP2D_Potongan
768insert into Sp2dOnline_BPP.dbo.TrxSP2D_Potongan (
769 Tahun,
770 Kd_Urusan,
771 Kd_Bidang,
772 Kd_Unit,
773 Kd_Sub,
774 No_SP2D,
775 Kd_Rek_1,
776 Kd_Rek_2,
777 Kd_Rek_3,
778 Kd_Rek_4,
779 Kd_Rek_5,
780 No_SPM,
781 Jn_SPM,
782 Nm_Rekening,
783 Nilai
784)
785
786select
787 cast(year([TGLSP2D]) as smallint) as [Tahun],
788 LEFT(KDUNIT,1) as [Kd_Urusan],
789 SUBSTRING(KDUNIT,3,2) as [Kd_Bidang],
790 SUBSTRING(KDUNIT,6,2) as [Kd_Unit],
791 SUBSTRING(KDUNIT,9,2) as [Kd_Sub],
792 ltrim(rtrim(sp.NOSP2D)) as No_SP2D,
793 cast(substring(KDPER,1,1) as tinyint) as Kd_Rek_1,
794 cast(substring(KDPER,3,1) as tinyint) as Kd_Rek_2,
795 cast(substring(KDPER,5,1) as tinyint) as Kd_Rek_3,
796 cast(substring(KDPER,7,2) as tinyint) as Kd_Rek_4,
797 cast(substring(KDPER,10,3) as tinyint) as Kd_Rek_5,
798 ltrim(rtrim(sp.NOSPM)) as No_SPM,
799 cast(tb7.LBLSTATUS as varchar(2)) as jns,
800 sp.NMPER,
801 sp.NILAI
802from (
803 /*
804 select
805 s.NOSP2D,
806 s.TGLSP2D,
807 s.NOSPM,
808 s.UNITKEY,
809 m.KDPER,
810 m.NMPER,
811 r.NILAI
812 from V@LID49V6_2019..SP2D s
813 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')
814 LEFT JOIN V@LID49V6_2019..MATANGB m ON m.MTGKEY = r.MTGKEY
815 where
816 KDPER is not null and s.NOSP2D in (select No_Sp2d from Sp2dOnline_BPP.dbo.TrxSP2D)
817
818 union all
819
820 select
821 s.NOSP2D,
822 s.TGLSP2D,
823 s.NOSPM,
824 s.UNITKEY,
825 m.KDPER,
826 m.NMPER,
827 r.NILAI
828 from V@LID49V6_2019..SP2D s
829 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')
830 LEFT JOIN V@LID49V6_2019..MATANGR m ON m.MTGKEY = r.MTGKEY
831 where KDPER is not null and s.NOSP2D in (select No_Sp2d from Sp2dOnline_BPP.dbo.TrxSP2D)
832
833 union all
834 */
835 select
836 s.NOSP2D,
837 s.TGLSP2D,
838 s.NOSPM,
839 s.UNITKEY,
840 case
841 when tb2.PJKKEY ='8_' then '4.1.1.02.01'
842 else
843 case
844 when tb2.PJKKEY ='9_' then '4.1.1.02.02'
845 else
846 case
847 when tb2.PJKKEY ='10_' then '4.1.1.02.05'
848 else
849 case
850 when tb2.PJKKEY ='1_' then '4.1.1.12.01'
851 else
852 case
853 when tb2.PJKKEY ='11_' then '4.1.1.12.08'
854 else
855 case
856 when tb2.PJKKEY ='12_' then '4.1.1.12.09'
857 else
858 case
859 when tb2.PJKKEY ='2_' then '4.1.1.12.02'
860 else
861 case
862 when tb2.PJKKEY ='3_' then '4.1.1.12.03'
863 else
864 case
865 when tb2.PJKKEY ='13_' then '4.1.1.12.04'
866 else
867 case
868 when tb2.PJKKEY ='5_' then '4.1.1.21.01'
869 end
870 end
871 end
872 end
873 end
874 end
875 end
876 end
877 end
878 end as KDPER,
879 NMPAJAK as NMPER,
880 p.NILAI
881 from V@LID49V6_2019..SP2D s
882 --left join V@LID49V6_2019..SP2DDETR r on r.NOSP2D=s.NOSP2D and r.UNITKEY=s.UNITKEY
883 left join V@LID49V6_2019..SP2DPJK p on p.NOSP2D=s.NOSP2D and p.UNITKEY=s.UNITKEY
884 LEFT JOIN V@LID49V6_2019..JPAJAK tb2 ON p.PJKKEY = tb2.PJKKEY
885 where
886 KDPAJAK is not null
887 and s.NOSP2D in (
888 select
889 No_Sp2d
890 from Sp2dOnline_BPP.dbo.TrxSP2D
891 )
892) sp
893left join V@LID49V6_2019..DAFTUNIT u ON u.UNITKEY=sp.UNITKEY
894left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=sp.NOSPM
895left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
896WHERE
897 NOT EXISTS (
898 SELECT
899 spot.No_Sp2d,
900 cast(substring(sp.KDPER,1,1) as tinyint),
901 spot.Kd_Rek_1,
902 cast(substring(sp.KDPER,3,1) as tinyint),
903 spot.Kd_Rek_2,
904 cast(substring(sp.KDPER,5,1) as tinyint),
905 spot.Kd_Rek_3,
906 cast(substring(sp.KDPER,7,2) as tinyint),
907 spot.Kd_Rek_4,
908 cast(substring(sp.KDPER,10,3) as tinyint),
909 spot.Kd_Rek_5
910 FROM Sp2dOnline_BPP.dbo.TrxSP2D_Potongan spot
911 where
912 spot.No_Sp2d=sp.NOSP2D
913 and cast(substring(sp.KDPER,1,1) as tinyint)=spot.Kd_Rek_1
914 and cast(substring(sp.KDPER,3,1) as tinyint)=spot.Kd_Rek_2
915 and cast(substring(sp.KDPER,5,1) as tinyint)=spot.Kd_Rek_3
916 and cast(substring(sp.KDPER,7,2) as tinyint)=spot.Kd_Rek_4
917 and cast(substring(sp.KDPER,10,3) as tinyint)=spot.Kd_Rek_5
918 )
919GO
920/****** Object: StoredProcedure [dbo].[sp_InsertSp2d_Potongan_Sp2d] Script Date: 10/08/2019 16:50:39 ******/
921SET ANSI_NULLS ON
922GO
923SET QUOTED_IDENTIFIER ON
924GO
925CREATE PROCEDURE [dbo].[sp_InsertSp2d_Potongan_Sp2d]
926--@UNITKEY CHAR(10)
927AS
928
929SET DATEFORMAT dmy
930
931exec [sp_InsertSpm]
932
933--if not exists (select name from V@LID49V6_2019..sysobjects where name = 'DP_temp')
934-- begin
935-- CREATE TABLE V@LID49V6_2019..[DP_temp]
936-- ([NODP] char(30),[TGLDP] datetime,[BERITA] varchar(254),[TGLVALID] datetime,
937-- [NOSP2D] char(50),[UNITKEY] char(10), ID_STAT char(1))
938-- end
939--INSERT INTO V@LID49V6_2019..[DP_temp]
940--SELECT top 1 t.[NODP],[TGLDP],[BERITA],[TGLVALID],[NOSP2D],[UNITKEY],'0' AS ID_STAT
941--FROM V@LID49V6_2019..[DP] d LEFT JOIN V@LID49V6_2019..[DPDET] t ON t.NODP=d.NODP
942--WHERE cast(CONVERT(varchar(11),[TGLDP],103)AS date) > cast(CONVERT(varchar(11),getdate()-4,103)AS date)
943--AND NOT EXISTS (SELECT * FROM V@LID49V6_2019..[DP_temp] dt
944-- WHERE dt.NODP=t.NODP and dt.NOSP2D=t.NOSP2D AND dt.UNITKEY=t.UNITKEY)
945
946--select UNITKEY,KDUNIT,KDLEVEL,TYPE,NMUNIT,LEFT(tb4.KDUNIT,1) as [Kd_Urusan],SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],
947--SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub]
948--INTO Sp2dOnline_BPP..MapUnit
949--from V@LID49V6_2019..DAFTUNIT tb4 where KDLEVEL=3
950--order by KDUNIT
951
952insert into Sp2dOnline_BPP.dbo.TrxSP2D (Kd_Urusan,Kd_Bidang, Kd_Unit, Kd_Sub, Tahun, No_SP2D, Tgl_SP2D, No_SPM, Tgl_SPM,
953Jn_SPM, Nm_Penerima, Keterangan ,NPWP, Bank_Penerima, Rek_Penerima, Tgl_Penguji, Nm_Bank,No_Rekening, Nilai, DateCreate,
954Cair,TglCair,gaji,Nm_Unit,Nm_Sub_Unit,uraian)
955select Kd_Urusan,Kd_Bidang,Kd_Unit,Kd_Sub,Tahun,ltrim(rtrim(sp.NOSP2D)),TGLSP2D,sp.NOSPM,TGLSPM,LBLSTATUS,NamaPenerima,Keterangan,
956NPWPPenerima,BankPenerima,RekPenerima,Tgl_Penguji,Nm_Bank,REKBEND,nilai-isnull(NilPot,0) as nilai,datecreate,cair,
957TglCair,gaji,Nm_Unit,Nm_Sub_Unit,uraian
958from (select LEFT(tb4.KDUNIT,1) as [Kd_Urusan],SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],
959SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub],CAST(year([TGLSP2D]) AS smallint) as [Tahun],ltrim(rtrim(tb1.NOSP2D)) as [NOSP2D],tb1.TGLSP2D,
960ISNULL(ltrim(rtrim(tb3.NOSPM)),'') AS NOSPM,ISNULL(tb3.TGLSPM,0) AS TGLSPM,cast(tb7.LBLSTATUS as varchar(2)) as LBLSTATUS,
961case when tb1.KDP3 IS null then tb10.NAMA else tb11.NMP3 end AS NamaPenerima,'' AS Keterangan,
962case when tb1.KDP3 IS null then cast(tb9.NPWPBEND as varchar(20)) else cast(tb11.NPWP as varchar(20)) end AS NPWPPenerima,
963case when tb1.KDP3 IS null then ISNULL(tb12.NMBANK,'') else ISNULL(tb11.NMBANK,'') end AS BankPenerima,
964case when tb1.KDP3 IS null then cast(tb9.REKBEND as varchar(50)) else cast(tb11.NORCP3 as varchar(50)) end AS RekPenerima,
965ISNULL(tb1.TGLVALID,0) as Tgl_Penguji,ISNULL(r.NMBKAS,'') as Nm_Bank, cast(r.NOREKB as varchar(50)) as REKBEND,
966tb.NilSp2d-ISNULL(tb2.NilPot,0) as nilai,GETDATE() as datecreate,0 as cair,null as TglCair,
967case when CHARINDEX('GJ', tb1.NOSP2D)=0 then 0 else 1 end as gaji,tbu.NMUNIT as Nm_Unit,tb4.nmunit as Nm_Sub_Unit,
968left(tb1.KEPERLUAN,255) as uraian
969from V@LID49V6_2019..SP2D tb1
970left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=tb1.NOSPM
971left join V@LID49V6_2019..DAFTUNIT tb4 on tb4.UNITKEY=tb1.UNITKEY
972left join V@LID49V6_2019..DAFTUNIT tbu on tbu.KDUNIT=LEFT(tb4.KDUNIT,1)+'.'+SUBSTRING(tb4.KDUNIT,3,2)+'.'+SUBSTRING(tb4.KDUNIT,6,2)+'.'
973left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
974left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb1.KEYBEND
975left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
976left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb1.KDP3
977left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
978left join (select NOSP2D,SUM(nilai) as NilSp2d from V@LID49V6_2019..SP2DDETR GROUP BY NOSP2D ) tb on tb.NOSP2D=tb1.NOSP2D
979left join (select NOSP2D,SUM(nilai) as NilPot from V@LID49V6_2019..SP2DDETB GROUP BY NOSP2D ) tb2 on tb2.NOSP2D=tb1.NOSP2D,
980(SELECT NMBKAS,NOREKB from V@LID49V6_2019..bkbkas where NOBBANTU='01') r
981where NilSp2d<>0
982and cast(CONVERT(varchar(11),tb1.TGLVALID,103)AS date) > cast(CONVERT(varchar(11),getdate()-4,103)AS date)
983--and tb1.NOSP2D in (SELECT NOSP2D FROM V@LID49V6_2019..[DP_temp])
984and tb1.NOSPM in (SELECT No_Spm FROM Sp2dOnline_BPP..Trx_Spm where Status=1)
985union
986select LEFT(tb4.KDUNIT,1) as [Kd_Urusan],SUBSTRING(tb4.KDUNIT,3,2) as [Kd_Bidang],SUBSTRING(tb4.KDUNIT,6,2) as [Kd_Unit],
987SUBSTRING(tb4.KDUNIT,9,2) as [Kd_Sub],CAST(year([TGLSP2D]) AS smallint) as [Tahun],ltrim(rtrim(tb1.NOSP2D)) as [NOSP2D],tb1.TGLSP2D,
988ISNULL(ltrim(rtrim(tb3.NOSPM)),''),ISNULL(tb3.TGLSPM,0),cast(tb7.LBLSTATUS as varchar(2)) as LBLSTATUS,
989case when tb1.KDP3 IS null then tb10.NAMA else tb11.NMP3 end AS NamaPenerima,'' AS Keterangan,
990case when tb1.KDP3 IS null then cast(tb9.NPWPBEND as varchar(20)) else cast(tb11.NPWP as varchar(20)) end AS NPWPPenerima,
991case when tb1.KDP3 IS null then ISNULL(tb12.NMBANK,'') else ISNULL(tb11.NMBANK,'') end AS BankPenerima,
992case when tb1.KDP3 IS null then cast(tb9.REKBEND as varchar(50)) else cast(tb11.NORCP3 as varchar(50)) end AS RekPenerima,
993ISNULL(tb1.TGLVALID,0) as Tgl_Penguji,ISNULL(r.NMBKAS,'') as Nm_Bank, cast(r.NOREKB as varchar(50)) as REKBEND,NilSp2d, GETDATE() as datecreate,
9940 as cair,null as TglCair,case when CHARINDEX('GJ', tb1.NOSP2D)=0 then 0 else 1 end as gaji
995,tbu.NMUNIT as Nm_Unit,tb4.nmunit as Nm_Sub_Unit,left(tb1.KEPERLUAN,255) as uraian
996from V@LID49V6_2019..SP2D tb1
997left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=tb1.NOSPM
998left join V@LID49V6_2019..DAFTUNIT tb4 on tb4.UNITKEY=tb1.UNITKEY
999left join V@LID49V6_2019..DAFTUNIT tbu on tbu.KDUNIT=LEFT(tb4.KDUNIT,1)+'.'+SUBSTRING(tb4.KDUNIT,3,2)+'.'+SUBSTRING(tb4.KDUNIT,6,2)+'.'
1000left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
1001left join V@LID49V6_2019..BEND tb9 on tb9.KEYBEND=tb1.KEYBEND
1002left join V@LID49V6_2019..PEGAWAI tb10 on tb10.NIP=tb9.NIP
1003left join V@LID49V6_2019..DAFTPHK3 tb11 on tb11.KDP3=tb1.KDP3
1004left join V@LID49V6_2019..DAFTBANK tb12 on tb12.KDBANK=tb9.KDBANK
1005left join (select tb2.NOSP2D,SUM(nilai) as NilSp2d from V@LID49V6_2019..SP2DDETRTL tb2 GROUP BY NOSP2D ) tb on tb.NOSP2D=tb1.NOSP2D,
1006(SELECT NMBKAS,NOREKB from V@LID49V6_2019..bkbkas where NOBBANTU='01') r
1007where NilSp2d<>0 --and tb1.NOSP2D in (SELECT NOSP2D FROM V@LID49V6_2019..[DP_temp] )
1008and cast(CONVERT(varchar(11),tb1.TGLVALID,103)AS date) > cast(CONVERT(varchar(11),getdate()-4,103)AS date)
1009and tb1.NOSPM in (SELECT No_Spm FROM Sp2dOnline_BPP..Trx_Spm where Status=1)
1010) sp
1011 left join (select p.NOSP2D,s.NOSPM,sum(isnull(NILAI,0)) as NilPot from V@LID49V6_2019..SP2DPJK p
1012 left join V@LID49V6_2019..SP2D s on s.NOSP2D=p.NOSP2D and s.UNITKEY=p.UNITKEY
1013 group by p.NOSP2D,s.NOSPM) p on p.NOSP2D=sp.NOSP2D and p.NOSPM=sp.NOSPM
1014WHERE NOT EXISTS ( SELECT * FROM Sp2dOnline_BPP.dbo.TrxSP2D sptr where sptr.No_SP2D=sp.NOSP2D AND sptr.Cair IN(0,1)
1015AND sptr.Nm_Sub_Unit=sp.Nm_Sub_Unit)
1016
1017----- ISI NILAI SP2D POTONGAN
1018--delete FROM TrxSP2D_Potongan
1019insert into Sp2dOnline_BPP.dbo.TrxSP2D_Potongan
1020(Tahun, Kd_Urusan, Kd_Bidang, Kd_Unit, Kd_Sub, No_SP2D,Kd_Rek_1, Kd_Rek_2, Kd_Rek_3, Kd_Rek_4, Kd_Rek_5, No_SPM, Jn_SPM, Nm_Rekening, Nilai)
1021select cast(year([TGLSP2D]) as smallint) as [Tahun],
1022LEFT(KDUNIT,1) as [Kd_Urusan],SUBSTRING(KDUNIT,3,2) as [Kd_Bidang],
1023SUBSTRING(KDUNIT,6,2) as [Kd_Unit],SUBSTRING(KDUNIT,9,2) as [Kd_Sub],ltrim(rtrim(sp.NOSP2D)) as No_SP2D,
1024cast(substring(KDPER,1,1) as tinyint) as Kd_Rek_1,
1025cast(substring(KDPER,3,1) as tinyint) as Kd_Rek_2,
1026cast(substring(KDPER,5,1) as tinyint) as Kd_Rek_3,
1027cast(substring(KDPER,7,2) as tinyint) as Kd_Rek_4,
1028cast(substring(KDPER,10,3) as tinyint) as Kd_Rek_5,
1029ltrim(rtrim(sp.NOSPM)) as No_SPM,cast(tb7.LBLSTATUS as varchar(2)) as jns,sp.NMPER,sp.NILAI
1030from (
1031select s.NOSP2D,s.TGLSP2D,s.NOSPM,s.UNITKEY,m.KDPER,m.NMPER,r.NILAI
1032from V@LID49V6_2019..SP2D s
1033left join V@LID49V6_2019..SP2DDETB r on r.NOSP2D=s.NOSP2D and r.UNITKEY=s.UNITKEY
1034 and (r.NOJETRA = '23' OR r.NOJETRA = '10')
1035LEFT JOIN V@LID49V6_2019..MATANGB m ON m.MTGKEY = r.MTGKEY
1036where KDPER is not null and s.NOSP2D in (select No_Sp2d from Sp2dOnline_BPP.dbo.TrxSP2D)
1037union all
1038select s.NOSP2D,s.TGLSP2D,s.NOSPM,s.UNITKEY,m.KDPER,m.NMPER,r.NILAI
1039from V@LID49V6_2019..SP2D s
1040left join V@LID49V6_2019..SP2DDETR r on r.NOSP2D=s.NOSP2D and r.UNITKEY=s.UNITKEY and
1041 (r.NOJETRA = '23' OR r.NOJETRA = '10')
1042LEFT JOIN V@LID49V6_2019..MATANGR m ON m.MTGKEY = r.MTGKEY
1043where KDPER is not null and s.NOSP2D in (select No_Sp2d from Sp2dOnline_BPP.dbo.TrxSP2D)
1044union all
1045select s.NOSP2D,s.TGLSP2D,s.NOSPM,s.UNITKEY,
1046case when tb2.PJKKEY ='8_' then '4.1.1.02.01' else
1047case when tb2.PJKKEY ='9_' then '4.1.1.02.02' else
1048case when tb2.PJKKEY ='10_' then '4.1.1.02.05' else
1049case when tb2.PJKKEY ='1_' then '4.1.1.12.01' else
1050case when tb2.PJKKEY ='11_' then '4.1.1.12.08' else
1051case when tb2.PJKKEY ='12_' then '4.1.1.12.09' else
1052case when tb2.PJKKEY ='2_' then '4.1.1.12.02' else
1053case when tb2.PJKKEY ='3_' then '4.1.1.12.03' else
1054case when tb2.PJKKEY ='13_' then '4.1.1.12.04' else
1055case when tb2.PJKKEY ='5_' then '4.1.1.21.01' end end end end end end end end end end as KDPER,
1056NMPAJAK as NMPER,p.NILAI from V@LID49V6_2019..SP2D s
1057--left join V@LID49V6_2019..SP2DDETR r on r.NOSP2D=s.NOSP2D and r.UNITKEY=s.UNITKEY
1058left join V@LID49V6_2019..SP2DPJK p on p.NOSP2D=s.NOSP2D and p.UNITKEY=s.UNITKEY
1059LEFT JOIN V@LID49V6_2019..JPAJAK tb2 ON p.PJKKEY = tb2.PJKKEY
1060where KDPAJAK is not null and s.NOSP2D in (select No_Sp2d from Sp2dOnline_BPP.dbo.TrxSP2D)
1061) sp
1062left join V@LID49V6_2019..DAFTUNIT u ON u.UNITKEY=sp.UNITKEY
1063left join V@LID49V6_2019..ANTARBYR tb3 on tb3.NOSPM=sp.NOSPM
1064left join V@LID49V6_2019..STATTRS tb7 on tb7.KDSTATUS=tb3.KDSTATUS
1065WHERE NOT EXISTS (SELECT spot.No_Sp2d,
1066cast(substring(sp.KDPER,1,1) as tinyint),spot.Kd_Rek_1,
1067cast(substring(sp.KDPER,3,1) as tinyint),spot.Kd_Rek_2,
1068cast(substring(sp.KDPER,5,1) as tinyint),spot.Kd_Rek_3,
1069cast(substring(sp.KDPER,7,2) as tinyint),spot.Kd_Rek_4,
1070cast(substring(sp.KDPER,10,3) as tinyint),spot.Kd_Rek_5
1071FROM Sp2dOnline_BPP.dbo.TrxSP2D_Potongan spot
1072where spot.No_Sp2d=sp.NOSP2D
1073and cast(substring(sp.KDPER,1,1) as tinyint)=spot.Kd_Rek_1
1074and cast(substring(sp.KDPER,3,1) as tinyint)=spot.Kd_Rek_2
1075and cast(substring(sp.KDPER,5,1) as tinyint)=spot.Kd_Rek_3
1076and cast(substring(sp.KDPER,7,2) as tinyint)=spot.Kd_Rek_4
1077and cast(substring(sp.KDPER,10,3) as tinyint)=spot.Kd_Rek_5)
1078GO
1079/****** Object: Default [DF__MstPenggu__statu__0BC6C43E] Script Date: 10/08/2019 16:50:37 ******/
1080ALTER TABLE [dbo].[MstPengguna] ADD DEFAULT ((1)) FOR [status]
1081GO
1082/****** Object: Default [DF__MstPenggu__token__0CBAE877] Script Date: 10/08/2019 16:50:37 ******/
1083ALTER TABLE [dbo].[MstPengguna] ADD DEFAULT (NULL) FOR [token_akses]
1084GO
1085/****** Object: Default [DF__MstPenggu__updat__0DAF0CB0] Script Date: 10/08/2019 16:50:37 ******/
1086ALTER TABLE [dbo].[MstPengguna] ADD DEFAULT (NULL) FOR [updated_at]
1087GO
1088/****** Object: Default [DF__MstPenggu__updat__0EA330E9] Script Date: 10/08/2019 16:50:37 ******/
1089ALTER TABLE [dbo].[MstPengguna] ADD DEFAULT (NULL) FOR [updated_by]
1090GO
1091/****** Object: Default [DF_Trx_Spm_Kirim] Script Date: 10/08/2019 16:50:37 ******/
1092ALTER TABLE [dbo].[Trx_Spm] ADD CONSTRAINT [DF_Trx_Spm_Kirim] DEFAULT ((0)) FOR [Kirim]
1093GO
1094/****** Object: Default [DF_TrxSp2d_Kirim] Script Date: 10/08/2019 16:50:37 ******/
1095ALTER TABLE [dbo].[TrxSp2d] ADD CONSTRAINT [DF_TrxSp2d_Kirim] DEFAULT ((0)) FOR [Kirim]
1096GO
1097/****** Object: Default [DF_TrxSp2d_Potongan_kirim] Script Date: 10/08/2019 16:50:37 ******/
1098ALTER TABLE [dbo].[TrxSp2d_Potongan] ADD CONSTRAINT [DF_TrxSp2d_Potongan_kirim] DEFAULT ((0)) FOR [kirim]
1099GO