Project

General

Profile

Feature #3865 » PRPTSTOCKMUTASI_202509221430.sql

Tri Rizqiaty, 09/22/2025 02:32 PM

 
1
USE [MinovaES_Tulisan_Dev]
2
GO
3
/****** Object:  StoredProcedure [dbo].[PRPTSTOCKMUTASI]    Script Date: 17/09/2025 15.39.55 ******/
4
SET ANSI_NULLS ON
5
GO
6
SET QUOTED_IDENTIFIER ON
7
GO
8
ALTER PROCEDURE [dbo].[PRPTSTOCKMUTASI]
9
(
10
	@Period VARCHAR(20)
11
	,@Warehouse VARCHAR(20)
12
	,@MaterialType VARCHAR(20)
13
	,@MaterialID VARCHAR(20)
14
)
15
AS
16

    
17
--DECLARE @Period VARCHAR(20) = '202508'
18
--DECLARE @Warehouse VARCHAR(20) = ''
19
--DECLARE @MaterialType VARCHAR(20) = ''
20
--DECLARE @MaterialID VARCHAR(20) = '00000004'
21

    
22
DECLARE @StarDate VARCHAR(20) = @Period + '01'
23
DECLARE @EndDate VARCHAR(20) = CONVERT(NVARCHAR(8), DATEADD(d, -1, DATEADD(m, 1,@StarDate)), 112)
24

    
25
DECLARE @TblStock TABLE
26
(
27
	MaterialID VARCHAR(20)
28
	,CompanyID VARCHAR(20)
29
	,LogisticAreaID VARCHAR(20)
30
	,Warehouse VARCHAR(20)
31
	,Storage VARCHAR(20)
32
	,Bin VARCHAR(20)
33
	,StockStatus VARCHAR(20)
34
	,Qty DECIMAL(22,2)
35
	,MaterialDesc VARCHAR(500)
36
	,WarehouseDesc VARCHAR(500)
37
	,MatTypeDesc VARCHAR(500)
38
)
39

    
40
INSERT INTO @TblStock
41
SELECT mat4.MaterialID, mat4.CompanyID, mat4.LogisticAreaID, mat4.Warehouse, mat4.Storage, mat4.Bin, mat4.StockStatus, mat4.Qty
42
		,mat4.MaterialID + ' - ' + mat1.ExternalID AS MaterialDesc
43
		,wh.Description AS WarehouseDesc
44
		,mattype.Description AS MatTypeDesc
45
FROM dbo.PMDMAT0004 AS mat4
46
LEFT JOIN dbo.PMDMAT0001 AS mat1
47
	ON mat1.MaterialID = mat4.MaterialID
48
LEFT JOIN dbo.PCMFILOWAREHOUSE AS wh
49
	ON wh.Warehouse = mat4.Warehouse
50
LEFT JOIN PCMFILOMATTYPE AS mattype
51
	ON mattype.MaterialType = mat1.MaterialType
52
WHERE mat4.StockStatus = '0001'
53
	AND (mat4.MaterialID = @MaterialID OR @MaterialID = '')
54
	AND (mat1.MaterialType = @MaterialType OR @MaterialType = '')
55
	AND (mat4.Warehouse = @Warehouse OR @Warehouse = '')
56

    
57
DECLARE @TblDoc TABLE
58
(
59
	DocNo VARCHAR(20)
60
	,DocType VARCHAR(20)
61
	,DocDate VARCHAR(20)
62
	,Description VARCHAR(500)
63
	,MaterialID VARCHAR(20)
64
	,MaterialType VARCHAR(20)
65
	,Warehouse VARCHAR(20)
66
	,Quantity DECIMAL(22,2)
67
	,ItemFlow VARCHAR(20)
68
	,QtyAdjust DECIMAL(22,2)
69
	,DocTypeDesc VARCHAR(500)
70
	,DocDateDesc VARCHAR(20)
71
	,MaterialDesc VARCHAR(500)
72
	,MatTypeDesc VARCHAR(500)
73
	,WarehouseDesc VARCHAR(500)
74
)
75

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

    
109
--SELECT * FROM @TblDoc
110

    
111
DECLARE @TblDocQty TABLE
112
(
113
	DocNo VARCHAR(20)
114
	,DocType VARCHAR(20)
115
	,DocDate VARCHAR(20)
116
	,Description VARCHAR(500)
117
	,MaterialID VARCHAR(20)
118
	,MaterialType VARCHAR(20)
119
	,Warehouse VARCHAR(20)
120
	,Quantity DECIMAL(22,2)
121
	,ItemFlow VARCHAR(20)
122
	,QtyAdjust DECIMAL(22,2)
123
	,DocTypeDesc VARCHAR(500)
124
	,DocDateDesc VARCHAR(20)
125
	,MaterialDesc VARCHAR(500)
126
	,MatTypeDesc VARCHAR(500)
127
	,WarehouseDesc VARCHAR(500)
128
	,QtyIn DECIMAL(22,2)
129
	,QtyOut DECIMAL(22,2)
130
	,BalanceDoc DECIMAL(22,2)
131
)
132

    
133
INSERT INTO @TblDocQty
134
SELECT *
135
	,Quantity AS QtyIn
136
	,0 AS QtyOut
137
	,Quantity-0 AS BalanceDoc
138
FROM @TblDoc WHERE DocType IN ('RCSP','RTCU') 
139

    
140
INSERT INTO @TblDocQty
141
SELECT *
142
	,0 AS QtyIn
143
	,Quantity AS QtyOut
144
	,0-Quantity AS BalanceDoc
145
FROM @TblDoc WHERE DocType IN ('GICU','RTSP')
146

    
147
INSERT INTO @TblDocQty
148
SELECT *
149
	,Quantity AS QtyIn
150
	,0 AS QtyOut
151
	,Quantity-0 AS BalanceDoc
152
FROM @TblDoc WHERE DocType IN ('MTPR') AND ItemFlow = 'RCPT'
153

    
154
INSERT INTO @TblDocQty
155
SELECT *
156
	,0 AS QtyIn
157
	,Quantity AS QtyOut
158
	,0-Quantity AS BalanceDoc
159
FROM @TblDoc WHERE DocType IN ('MTPR') AND ItemFlow = 'ISSU'
160

    
161
INSERT INTO @TblDocQty
162
SELECT *
163
	,QtyAdjust AS QtyIn
164
	,0 AS QtyOut
165
	,QtyAdjust-0 AS BalanceDoc
166
FROM @TblDoc WHERE DocType IN ('SA') AND ItemFlow = 'RCPT'
167

    
168
INSERT INTO @TblDocQty
169
SELECT *
170
	,0 AS QtyIn
171
	,QtyAdjust AS QtyOut
172
	,0-QtyAdjust AS BalanceDoc
173
FROM @TblDoc WHERE DocType IN ('SA') AND ItemFlow = 'ISSU'
174

    
175
DECLARE @TblResult TABLE
176
(
177
	RowNo DECIMAL(22,0)
178
	,DocNo VARCHAR(20)
179
	,DocType VARCHAR(20)
180
	,DocDate VARCHAR(20)
181
	,Description VARCHAR(500)
182
	,MaterialID VARCHAR(20)
183
	,MaterialType VARCHAR(20)
184
	,Warehouse VARCHAR(20)
185
	,Quantity DECIMAL(22,2)
186
	,ItemFlow VARCHAR(20)
187
	,QtyAdjust DECIMAL(22,2)
188
	,DocTypeDesc VARCHAR(500)
189
	,DocDateDesc VARCHAR(20)
190
	,MaterialDesc VARCHAR(500)
191
	,MatTypeDesc VARCHAR(500)
192
	,WarehouseDesc VARCHAR(500)
193
	,QtyIn DECIMAL(22,2)
194
	,QtyOut DECIMAL(22,2)
195
	,Balance DECIMAL(22,2)
196
	,BalanceAwal DECIMAL(22,2)
197
	,BalanceAkhir DECIMAL(22,2)
198
)
199
INSERT INTO @TblResult
200
SELECT ROW_NUMBER() OVER (PARTITION BY tbldoc.MaterialID, tbldoc.Warehouse ORDER BY  tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.DocNo ASC) AS RowNo
201
		,*
202
		,0 AS BalanceAwal
203
		,0 AS BalancAkhir
204
FROM @TblDocQty AS tbldoc
205

    
206
INSERT INTO @TblResult
207
SELECT DISTINCT 0 AS RowNo
208
		,'' AS DocNo
209
		,'' AS DocType
210
		,'' AS DocDate
211
		,'Saldo awal' AS Description
212
		,tblstok.MaterialID AS MaterialID
213
		,'' AS MaterialType
214
		,tblstok.Warehouse AS Warehouse 
215
		,tblstok.Qty AS Quantity 
216
		,'' AS ItemFlow
217
		,0 AS QtyAdjust
218
		,'' AS DocTypeDesc
219
		,'' AS DocDateDesc
220
		,tblstok.MaterialDesc AS MaterialDesc 
221
		,tblstok.MatTypeDesc AS MatTypeDesc
222
		,tblstok.WarehouseDesc AS WarehouseDesc
223
		,0 AS QtyIn
224
		,0 AS QtyOut 
225
		,0 AS Balance
226
		,tblstok.Qty AS BalanceAwal
227
		,0 AS BalancAkhir
228
FROM @TblStock AS tblstok
229
LEFT JOIN ( SELECT tbldoc.Warehouse, tbldoc.MaterialID, SUM(tbldoc.BalanceDoc) AS SumBalance
230
			FROM @TblDocQty AS tbldoc
231
			GROUP BY tbldoc.Warehouse, tbldoc.MaterialID ) AS tbldocsum
232
	ON tbldocsum.MaterialID = tblstok.MaterialID
233
WHERE tbldocsum.Warehouse = tblstok.Warehouse
234

    
235
INSERT INTO @TblResult
236
SELECT DISTINCT '9999999999' AS RowNo
237
		,'' AS DocNo
238
		,'' AS DocType
239
		,'' AS DocDate
240
		,'Stock akhir' AS Description
241
		,tblstk.MaterialID AS MaterialID
242
		,'' AS MaterialType
243
		,tblstk.Warehouse AS Warehouse 
244
		,tblstk.Qty AS Quantity 
245
		,'' AS ItemFlow
246
		,ISNULL(tbldocsum.SumBalance,0) AS QtyAdjust ---- diisi sum balance dr document
247
		,'' AS DocTypeDesc
248
		,'' AS DocDateDesc
249
		,tblstk.MaterialDesc AS MaterialDesc 
250
		,tblstk.MatTypeDesc AS MatTypeDesc
251
		,tblstk.WarehouseDesc AS WarehouseDesc
252
		,0 AS QtyIn
253
		,0 AS QtyOut 
254
		,0 AS Balance
255
		,0 AS BalanceAwal
256
		,tblstk.Qty + ISNULL(tbldocsum.SumBalance,0) AS BalancAkhir
257
FROM @TblStock AS tblstk
258
LEFT JOIN ( SELECT tbldoc.Warehouse, tbldoc.MaterialID, SUM(tbldoc.BalanceDoc) AS SumBalance
259
			FROM @TblDocQty AS tbldoc
260
			GROUP BY tbldoc.Warehouse, tbldoc.MaterialID ) AS tbldocsum
261
	ON tbldocsum.MaterialID = tblstk.MaterialID
262
WHERE tbldocsum.Warehouse = tblstk.Warehouse
263

    
264
INSERT INTO @TblResult
265
SELECT DISTINCT '0' AS RowNo
266
		,'' AS DocNo
267
		,'' AS DocType
268
		,'' AS DocDate
269
		,'Stock awal' AS Description
270
		,tbldocsum.MaterialID AS MaterialID
271
		,'' AS MaterialType
272
		,tbldocsum.Warehouse AS Warehouse 
273
		,0 AS Quantity 
274
		,'' AS ItemFlow
275
		,ISNULL(tbldocsum.SumBalance,0) AS QtyAdjust ---- diisi sum balance dr document
276
		,'' AS DocTypeDesc
277
		,'' AS DocDateDesc
278
		,tbldocsum.MaterialDesc  AS MaterialDesc 
279
		,tbldocsum.MatTypeDesc AS MatTypeDesc
280
		,tbldocsum.WarehouseDesc AS WarehouseDesc
281
		,0 AS QtyIn
282
		,0 AS QtyOut 
283
		,0 AS Balance
284
		,0 AS BalanceAwal
285
		,0 AS BalancAkhir
286
FROM ( SELECT tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.WarehouseDesc, tbldoc.MaterialDesc, tbldoc.MatTypeDesc, SUM(tbldoc.BalanceDoc) AS SumBalance
287
			FROM @TblDocQty AS tbldoc
288
			GROUP BY tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.WarehouseDesc, tbldoc.MaterialDesc, tbldoc.MatTypeDesc ) AS tbldocsum
289
WHERE tbldocsum.Warehouse = ''
290

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

    
318

    
319
SELECT * 
320
		,dbo.fn_formatdatetime(@StarDate, 'dd/mm/yyyy') AS StartDateDesc
321
		,dbo.fn_formatdatetime(@EndDate, 'dd/mm/yyyy') AS EndDateDesc
322
		,dbo.fn_formatdatetime(@StarDate, 'mmmm yyyy') AS PeriodeDesc
323
FROM @TblResult
324
ORDER BY MaterialID, Warehouse, RowNo
325

    
326
--SELECT * 
327
--		,dbo.fn_formatdatetime(@StarDate, 'dd/mm/yyyy') AS StartDateDesc
328
--		,dbo.fn_formatdatetime(@EndDate, 'dd/mm/yyyy') AS EndDateDesc
329
--		,dbo.fn_formatdatetime(@StarDate, 'mmmm yyyy') AS PeriodeDesc
330
--FROM @TblDocQty AS DocQty
331

    
332

    
(5-5/12)