Project

General

Profile

Feature #3865 » PRPTSTOCKMUTASI_202509231536.sql

Tri Rizqiaty, 09/23/2025 04:08 PM

 
1
ALTER PROCEDURE [dbo].[PRPTSTOCKMUTASI]
2
(
3
	@Period VARCHAR(20)
4
	,@Warehouse VARCHAR(20)
5
	,@MaterialType VARCHAR(20)
6
	,@MaterialID VARCHAR(20)
7
)
8
AS
9

    
10
--DECLARE @Period VARCHAR(20) = '202508'
11
--DECLARE @Warehouse VARCHAR(20) = ''
12
--DECLARE @MaterialType VARCHAR(20) = ''
13
--DECLARE @MaterialID VARCHAR(20) = ''
14

    
15
DECLARE @StarDate VARCHAR(20) = @Period + '01'
16
DECLARE @EndDate VARCHAR(20) = CONVERT(NVARCHAR(8), DATEADD(d, -1, DATEADD(m, 1,@StarDate)), 112)
17

    
18
DECLARE @TblStock TABLE
19
(
20
	MaterialID VARCHAR(20)
21
	,CompanyID VARCHAR(20)
22
	,LogisticAreaID VARCHAR(20)
23
	,Warehouse VARCHAR(20)
24
	,Storage VARCHAR(20)
25
	,Bin VARCHAR(20)
26
	,StockStatus VARCHAR(20)
27
	,Qty DECIMAL(22,2)
28
	,MaterialDesc VARCHAR(500)
29
	,WarehouseDesc VARCHAR(500)
30
	,MatTypeDesc VARCHAR(500)
31
)
32

    
33
INSERT INTO @TblStock
34
SELECT mat4.MaterialID, mat4.CompanyID, mat4.LogisticAreaID, mat4.Warehouse, mat4.Storage, mat4.Bin, mat4.StockStatus, mat4.Qty
35
		,mat4.MaterialID + ' - ' + mat1.ExternalID AS MaterialDesc
36
		,wh.Description AS WarehouseDesc
37
		,mattype.Description AS MatTypeDesc
38
FROM dbo.PMDMAT0004 AS mat4
39
LEFT JOIN dbo.PMDMAT0001 AS mat1
40
	ON mat1.MaterialID = mat4.MaterialID
41
LEFT JOIN dbo.PCMFILOWAREHOUSE AS wh
42
	ON wh.Warehouse = mat4.Warehouse
43
LEFT JOIN PCMFILOMATTYPE AS mattype
44
	ON mattype.MaterialType = mat1.MaterialType
45
WHERE mat4.StockStatus = '0001'
46
	AND (mat4.MaterialID = @MaterialID OR @MaterialID = '')
47
	AND (mat1.MaterialType = @MaterialType OR @MaterialType = '')
48
	AND (mat4.Warehouse = @Warehouse OR @Warehouse = '')
49

    
50
DECLARE @TblDoc TABLE
51
(
52
	DocNo VARCHAR(20)
53
	,DocType VARCHAR(20)
54
	,DocDate VARCHAR(20)
55
	,Description VARCHAR(500)
56
	,MaterialID VARCHAR(20)
57
	,MaterialType VARCHAR(20)
58
	,Warehouse VARCHAR(20)
59
	,Quantity DECIMAL(22,2)
60
	,ItemFlow VARCHAR(20)
61
	,QtyAdjust DECIMAL(22,2)
62
	,DocTypeDesc VARCHAR(500)
63
	,DocDateDesc VARCHAR(20)
64
	,MaterialDesc VARCHAR(500)
65
	,MatTypeDesc VARCHAR(500)
66
	,WarehouseDesc VARCHAR(500)
67
)
68

    
69
INSERT INTO @TblDoc
70
SELECT DISTINCT head.DocNo
71
				,head.DocType
72
				,head.DocDate
73
				,head.Description
74
				,item.MaterialID
75
				,item.MaterialType
76
				,ISNULL(CASE WHEN item.Warehouse <> '' THEN item.Warehouse ELSE head.Warehouse END,'') AS Warehouse
77
				,item.Quantity
78
				,item.ItemFlow
79
				,item.QtyAdjust
80
				,dt.DocTypeDesc
81
				,dbo.fn_formatdatetime(head.DocDate,'dd mmmm yyyy') AS DocDateDesc
82
				,item.MaterialID + ' - ' + mat1.ExternalID AS MaterialDesc
83
				,mattype.Description AS MatTypeDesc
84
				,wh.Description AS WarehouseDesc
85
FROM dbo.PTRMATERIALHEAD AS head WITH(NOLOCK)
86
LEFT JOIN dbo.PTRMATERIALITEM AS item WITH(NOLOCK)
87
	ON head.DocNo = item.DocNo
88
LEFT JOIN dbo.PCMBSDOCTYPE AS dt
89
	ON dt.DocType = head.DocType
90
LEFT JOIN dbo.PMDMAT0001 AS mat1
91
	ON mat1.MaterialID = item.MaterialID
92
LEFT JOIN dbo.PCMFILOMATTYPE AS mattype
93
	ON mattype.MaterialType = item.MaterialType
94
LEFT JOIN dbo.PCMFILOWAREHOUSE AS wh
95
	ON wh.Warehouse = head.SendWarehouse
96
WHERE ((CASE WHEN item.Warehouse <> '' THEN item.Warehouse ELSE head.Warehouse END) = @Warehouse OR @Warehouse = '')
97
	AND (item.MaterialType = @MaterialType OR @MaterialType = '')
98
	AND (item.MaterialID = @MaterialID OR @MaterialID = '')
99
	AND (head.DocDate >= @StarDate AND head.DocDate <= @EndDate)
100
	AND item.MaterialID IS NOT NULL
101

    
102
INSERT INTO @TblDoc
103
SELECT DISTINCT head.DocNo
104
				,head.DocType
105
				,head.DocDate
106
				,head.Description
107
				,item.MaterialID
108
				,mat1.MaterialType
109
				,head.SendWarehouse AS Warehouse
110
				,(CONVERT(DECIMAL(18,2),item.Quantity)) AS Quantity
111
				,'ISSU' AS ItemFlow
112
				,(CONVERT(DECIMAL(18,2),item.Quantity)) AS QtyAdjust
113
				,dt.DocTypeDesc
114
				,dbo.fn_formatdatetime(head.DocDate,'dd mmmm yyyy') AS DocDateDesc
115
				,item.MaterialID + ' - ' + mat1.ExternalID AS MaterialDesc
116
				,mattype.Description AS MatTypeDesc
117
				,wh.Description AS WarehouseDesc
118
FROM dbo.PTRMATERIALHEADINTERNAL AS head WITH(NOLOCK)
119
LEFT JOIN dbo.PTRMATERIALITEMINTERNAL AS item WITH(NOLOCK)
120
	ON head.DocNo = item.DocNo
121
LEFT JOIN dbo.PCMBSDOCTYPE AS dt
122
	ON dt.DocType = head.DocType
123
LEFT JOIN dbo.PMDMAT0001 AS mat1
124
	ON mat1.MaterialID = item.MaterialID
125
LEFT JOIN dbo.PCMFILOMATTYPE AS mattype
126
	ON mattype.MaterialType = mat1.MaterialType
127
LEFT JOIN dbo.PCMFILOWAREHOUSE AS wh
128
	ON wh.Warehouse = head.SendWarehouse
129
WHERE (head.SendWarehouse = @Warehouse OR @Warehouse = '')
130
	AND (mat1.MaterialType = @MaterialType OR @MaterialType = '')
131
	AND (item.MaterialID = @MaterialID OR @MaterialID = '')
132
	AND (head.DocDate >= @StarDate AND head.DocDate <= @EndDate)
133
	AND item.MaterialID IS NOT NULL
134

    
135
INSERT INTO @TblDoc
136
SELECT DISTINCT head.DocNo
137
				,head.DocType
138
				,head.DocDate
139
				,head.Description
140
				,item.MaterialID
141
				,mat1.MaterialType
142
				,head.ReceiverWarehouse AS Warehouse
143
				,(CONVERT(DECIMAL(18,2),item.Quantity)) AS Quantity
144
				,'RCPT' AS ItemFlow
145
				,(CONVERT(DECIMAL(18,2),item.Quantity)) AS QtyAdjust
146
				,dt.DocTypeDesc
147
				,dbo.fn_formatdatetime(head.DocDate,'dd mmmm yyyy') AS DocDateDesc
148
				,item.MaterialID + ' - ' + mat1.ExternalID AS MaterialDesc
149
				,mattype.Description AS MatTypeDesc
150
				,wh.Description AS WarehouseDesc
151
FROM dbo.PTRMATERIALHEADINTERNAL AS head WITH(NOLOCK)
152
LEFT JOIN dbo.PTRMATERIALITEMINTERNAL AS item WITH(NOLOCK)
153
	ON head.DocNo = item.DocNo
154
LEFT JOIN dbo.PCMBSDOCTYPE AS dt
155
	ON dt.DocType = head.DocType
156
LEFT JOIN dbo.PMDMAT0001 AS mat1
157
	ON mat1.MaterialID = item.MaterialID
158
LEFT JOIN dbo.PCMFILOMATTYPE AS mattype
159
	ON mattype.MaterialType = mat1.MaterialType
160
LEFT JOIN dbo.PCMFILOWAREHOUSE AS wh
161
	ON wh.Warehouse = head.ReceiverWarehouse
162
WHERE (head.ReceiverWarehouse = @Warehouse OR @Warehouse = '')
163
	AND (mat1.MaterialType = @MaterialType OR @MaterialType = '')
164
	AND (item.MaterialID = @MaterialID OR @MaterialID = '')
165
	AND (head.DocDate >= @StarDate AND head.DocDate <= @EndDate)
166
	AND item.MaterialID IS NOT NULL
167

    
168
--SELECT * FROM @TblDoc
169

    
170
DECLARE @TblDocQty TABLE
171
(
172
	DocNo VARCHAR(20)
173
	,DocType VARCHAR(20)
174
	,DocDate VARCHAR(20)
175
	,Description VARCHAR(500)
176
	,MaterialID VARCHAR(20)
177
	,MaterialType VARCHAR(20)
178
	,Warehouse VARCHAR(20)
179
	,Quantity DECIMAL(22,2)
180
	,ItemFlow VARCHAR(20)
181
	,QtyAdjust DECIMAL(22,2)
182
	,DocTypeDesc VARCHAR(500)
183
	,DocDateDesc VARCHAR(20)
184
	,MaterialDesc VARCHAR(500)
185
	,MatTypeDesc VARCHAR(500)
186
	,WarehouseDesc VARCHAR(500)
187
	,QtyIn DECIMAL(22,2)
188
	,QtyOut DECIMAL(22,2)
189
	,BalanceDoc DECIMAL(22,2)
190
)
191

    
192
INSERT INTO @TblDocQty
193
SELECT *
194
	,Quantity AS QtyIn
195
	,0 AS QtyOut
196
	,Quantity-0 AS BalanceDoc
197
FROM @TblDoc WHERE DocType IN ('RCSP','RTCU') 
198

    
199
INSERT INTO @TblDocQty
200
SELECT *
201
	,0 AS QtyIn
202
	,Quantity AS QtyOut
203
	,0-Quantity AS BalanceDoc
204
FROM @TblDoc WHERE DocType IN ('GICU','RTSP')
205

    
206
INSERT INTO @TblDocQty
207
SELECT *
208
	,Quantity AS QtyIn
209
	,0 AS QtyOut
210
	,Quantity-0 AS BalanceDoc
211
FROM @TblDoc WHERE DocType IN ('MTPR') AND ItemFlow = 'RCPT'
212

    
213
INSERT INTO @TblDocQty
214
SELECT *
215
	,0 AS QtyIn
216
	,Quantity AS QtyOut
217
	,0-Quantity AS BalanceDoc
218
FROM @TblDoc WHERE DocType IN ('MTPR') AND ItemFlow = 'ISSU'
219

    
220
INSERT INTO @TblDocQty
221
SELECT *
222
	,QtyAdjust AS QtyIn
223
	,0 AS QtyOut
224
	,QtyAdjust-0 AS BalanceDoc
225
FROM @TblDoc WHERE DocType IN ('SA', 'INTR') AND ItemFlow = 'RCPT'
226

    
227
INSERT INTO @TblDocQty
228
SELECT *
229
	,0 AS QtyIn
230
	,QtyAdjust AS QtyOut
231
	,0-QtyAdjust AS BalanceDoc
232
FROM @TblDoc WHERE DocType IN ('SA', 'INTR') AND ItemFlow = 'ISSU'
233

    
234

    
235

    
236
DECLARE @TblResult TABLE
237
(
238
	RowNo DECIMAL(22,0)
239
	,DocNo VARCHAR(20)
240
	,DocType VARCHAR(20)
241
	,DocDate VARCHAR(20)
242
	,Description VARCHAR(500)
243
	,MaterialID VARCHAR(20)
244
	,MaterialType VARCHAR(20)
245
	,Warehouse VARCHAR(20)
246
	,Quantity DECIMAL(22,2)
247
	,ItemFlow VARCHAR(20)
248
	,QtyAdjust DECIMAL(22,2)
249
	,DocTypeDesc VARCHAR(500)
250
	,DocDateDesc VARCHAR(20)
251
	,MaterialDesc VARCHAR(500)
252
	,MatTypeDesc VARCHAR(500)
253
	,WarehouseDesc VARCHAR(500)
254
	,QtyIn DECIMAL(22,2)
255
	,QtyOut DECIMAL(22,2)
256
	,Balance DECIMAL(22,2)
257
	,BalanceAwal DECIMAL(22,2)
258
	,BalanceAkhir DECIMAL(22,2)
259
)
260
INSERT INTO @TblResult
261
SELECT ROW_NUMBER() OVER (PARTITION BY tbldoc.MaterialID, tbldoc.Warehouse ORDER BY  tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.DocNo ASC) AS RowNo
262
		,*
263
		,0 AS BalanceAwal
264
		,0 AS BalancAkhir
265
FROM @TblDocQty AS tbldoc
266

    
267
INSERT INTO @TblResult
268
SELECT DISTINCT 0 AS RowNo
269
		,'' AS DocNo
270
		,'' AS DocType
271
		,'' AS DocDate
272
		,'Saldo awal' AS Description
273
		,tblstok.MaterialID AS MaterialID
274
		,'' AS MaterialType
275
		,tblstok.Warehouse AS Warehouse 
276
		,tblstok.Qty AS Quantity 
277
		,'' AS ItemFlow
278
		,0 AS QtyAdjust
279
		,'' AS DocTypeDesc
280
		,'' AS DocDateDesc
281
		,tblstok.MaterialDesc AS MaterialDesc 
282
		,tblstok.MatTypeDesc AS MatTypeDesc
283
		,tblstok.WarehouseDesc AS WarehouseDesc
284
		,0 AS QtyIn
285
		,0 AS QtyOut 
286
		,0 AS Balance
287
		,tblstok.Qty AS BalanceAwal
288
		,0 AS BalancAkhir
289
FROM @TblStock AS tblstok
290
LEFT JOIN ( SELECT tbldoc.Warehouse, tbldoc.MaterialID, SUM(tbldoc.BalanceDoc) AS SumBalance
291
			FROM @TblDocQty AS tbldoc
292
			GROUP BY tbldoc.Warehouse, tbldoc.MaterialID ) AS tbldocsum
293
	ON tbldocsum.MaterialID = tblstok.MaterialID
294
WHERE tbldocsum.Warehouse = tblstok.Warehouse
295

    
296
INSERT INTO @TblResult
297
SELECT DISTINCT '9999999999' AS RowNo
298
		,'' AS DocNo
299
		,'' AS DocType
300
		,'' AS DocDate
301
		,'Stock akhir' AS Description
302
		,tblstk.MaterialID AS MaterialID
303
		,'' AS MaterialType
304
		,tblstk.Warehouse AS Warehouse 
305
		,tblstk.Qty AS Quantity 
306
		,'' AS ItemFlow
307
		,ISNULL(tbldocsum.SumBalance,0) AS QtyAdjust ---- diisi sum balance dr document
308
		,'' AS DocTypeDesc
309
		,'' AS DocDateDesc
310
		,tblstk.MaterialDesc AS MaterialDesc 
311
		,tblstk.MatTypeDesc AS MatTypeDesc
312
		,tblstk.WarehouseDesc AS WarehouseDesc
313
		,0 AS QtyIn
314
		,0 AS QtyOut 
315
		,0 AS Balance
316
		,0 AS BalanceAwal
317
		,tblstk.Qty + ISNULL(tbldocsum.SumBalance,0) AS BalancAkhir
318
FROM @TblStock AS tblstk
319
LEFT JOIN ( SELECT tbldoc.Warehouse, tbldoc.MaterialID, SUM(tbldoc.BalanceDoc) AS SumBalance
320
			FROM @TblDocQty AS tbldoc
321
			GROUP BY tbldoc.Warehouse, tbldoc.MaterialID ) AS tbldocsum
322
	ON tbldocsum.MaterialID = tblstk.MaterialID
323
WHERE tbldocsum.Warehouse = tblstk.Warehouse
324

    
325
INSERT INTO @TblResult
326
SELECT DISTINCT '0' AS RowNo
327
		,'' AS DocNo
328
		,'' AS DocType
329
		,'' AS DocDate
330
		,'Stock awal' AS Description
331
		,tbldocsum.MaterialID AS MaterialID
332
		,'' AS MaterialType
333
		,tbldocsum.Warehouse AS Warehouse 
334
		,0 AS Quantity 
335
		,'' AS ItemFlow
336
		,ISNULL(tbldocsum.SumBalance,0) AS QtyAdjust ---- diisi sum balance dr document
337
		,'' AS DocTypeDesc
338
		,'' AS DocDateDesc
339
		,tbldocsum.MaterialDesc  AS MaterialDesc 
340
		,tbldocsum.MatTypeDesc AS MatTypeDesc
341
		,tbldocsum.WarehouseDesc AS WarehouseDesc
342
		,0 AS QtyIn
343
		,0 AS QtyOut 
344
		,0 AS Balance
345
		,0 AS BalanceAwal
346
		,0 AS BalancAkhir
347
FROM ( SELECT tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.WarehouseDesc, tbldoc.MaterialDesc, tbldoc.MatTypeDesc, SUM(tbldoc.BalanceDoc) AS SumBalance
348
			FROM @TblDocQty AS tbldoc
349
			GROUP BY tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.WarehouseDesc, tbldoc.MaterialDesc, tbldoc.MatTypeDesc ) AS tbldocsum
350
WHERE tbldocsum.Warehouse = ''
351

    
352
INSERT INTO @TblResult
353
SELECT DISTINCT '9999999999' AS RowNo
354
		,'' AS DocNo
355
		,'' AS DocType
356
		,'' AS DocDate
357
		,'Stock akhir' AS Description
358
		,tbldocsum.MaterialID AS MaterialID
359
		,'' AS MaterialType
360
		,tbldocsum.Warehouse AS Warehouse 
361
		,0 AS Quantity 
362
		,'' AS ItemFlow
363
		,ISNULL(tbldocsum.SumBalance,0) AS QtyAdjust ---- diisi sum balance dr document
364
		,'' AS DocTypeDesc
365
		,'' AS DocDateDesc
366
		,tbldocsum.MaterialDesc AS MaterialDesc 
367
		,tbldocsum.MatTypeDesc AS MatTypeDesc
368
		,tbldocsum.WarehouseDesc AS WarehouseDesc
369
		,0 AS QtyIn
370
		,0 AS QtyOut 
371
		,0 AS Balance
372
		,0 AS BalanceAwal
373
		,ISNULL(tbldocsum.SumBalance,0) AS BalancAkhir
374
FROM ( SELECT tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.WarehouseDesc, tbldoc.MaterialDesc, tbldoc.MatTypeDesc, SUM(tbldoc.BalanceDoc) AS SumBalance
375
			FROM @TblDocQty AS tbldoc
376
			GROUP BY tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.WarehouseDesc, tbldoc.MaterialDesc, tbldoc.MatTypeDesc ) AS tbldocsum
377
WHERE tbldocsum.Warehouse = ''
378

    
379

    
380
SELECT * 
381
		,dbo.fn_formatdatetime(@StarDate, 'dd/mm/yyyy') AS StartDateDesc
382
		,dbo.fn_formatdatetime(@EndDate, 'dd/mm/yyyy') AS EndDateDesc
383
		,dbo.fn_formatdatetime(@StarDate, 'mmmm yyyy') AS PeriodeDesc
384
FROM @TblResult
385
ORDER BY MaterialID, Warehouse, RowNo
386

    
387
--SELECT * 
388
--		,dbo.fn_formatdatetime(@StarDate, 'dd/mm/yyyy') AS StartDateDesc
389
--		,dbo.fn_formatdatetime(@EndDate, 'dd/mm/yyyy') AS EndDateDesc
390
--		,dbo.fn_formatdatetime(@StarDate, 'mmmm yyyy') AS PeriodeDesc
391
--FROM @TblDocQty AS DocQty
392

    
393

    
(7-7/12)