TULISAN

??USE [MinovaES_Tulisan_Dev]

GO

/****** Object:  StoredProcedure [dbo].[PRPTSTOCKMUTASI]    Script Date: 17/09/2025 15.39.55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PRPTSTOCKMUTASI]

(

	@Period VARCHAR(20)

	,@Warehouse VARCHAR(20)

	,@MaterialType VARCHAR(20)

	,@MaterialID VARCHAR(20)

)

AS

--DECLARE @Period VARCHAR(20) = '202508'

--DECLARE @Warehouse VARCHAR(20) = ''

--DECLARE @MaterialType VARCHAR(20) = ''

--DECLARE @MaterialID VARCHAR(20) = '00000003'

DECLARE @StarDate VARCHAR(20) = @Period + '01'

DECLARE @EndDate VARCHAR(20) = CONVERT(NVARCHAR(8), DATEADD(d, -1, DATEADD(m, 1,@StarDate)), 112)

DECLARE @TblStock TABLE

(

	MaterialID VARCHAR(20)

	,CompanyID VARCHAR(20)

	,LogisticAreaID VARCHAR(20)

	,Warehouse VARCHAR(20)

	,Storage VARCHAR(20)

	,Bin VARCHAR(20)

	,StockStatus VARCHAR(20)

	,Qty DECIMAL(22,2)

	,MaterialDesc VARCHAR(500)

	,WarehouseDesc VARCHAR(500)

)

INSERT INTO @TblStock

SELECT mat4.MaterialID, mat4.CompanyID, mat4.LogisticAreaID, mat4.Warehouse, mat4.Storage, mat4.Bin, mat4.StockStatus, mat4.Qty

		,mat4.MaterialID + ' - ' + mat1.ExternalID AS MaterialDesc

		,wh.Description AS WarehouseDesc

FROM dbo.PMDMAT0004 AS mat4

LEFT JOIN dbo.PMDMAT0001 AS mat1

	ON mat1.MaterialID = mat4.MaterialID

LEFT JOIN dbo.PCMFILOWAREHOUSE AS wh

	ON wh.Warehouse = mat4.Warehouse

WHERE mat4.StockStatus = '0001'

	AND (mat4.MaterialID = @MaterialID OR @MaterialID = '')

	AND (mat1.MaterialType = @MaterialType OR @MaterialType = '')

	AND (mat4.Warehouse = @Warehouse OR @Warehouse = '')

DECLARE @TblDoc TABLE

(

	DocNo VARCHAR(20)

	,DocType VARCHAR(20)

	,DocDate VARCHAR(20)

	,Description VARCHAR(500)

	,MaterialID VARCHAR(20)

	,MaterialType VARCHAR(20)

	,Warehouse VARCHAR(20)

	,Quantity DECIMAL(22,2)

	,ItemFlow VARCHAR(20)

	,QtyAdjust DECIMAL(22,2)

	,DocTypeDesc VARCHAR(500)

	,DocDateDesc VARCHAR(20)

	,MaterialDesc VARCHAR(500)

	,MatTypeDesc VARCHAR(500)

	,WarehouseDesc VARCHAR(500)

)

INSERT INTO @TblDoc

SELECT DISTINCT head.DocNo

				,head.DocType

				,head.DocDate

				,head.Description

				,item.MaterialID

				,item.MaterialType

				,ISNULL(CASE WHEN item.Warehouse <> '' THEN item.Warehouse ELSE head.Warehouse END,'') AS Warehouse

				,item.Quantity

				,item.ItemFlow

				,item.QtyAdjust

				,dt.DocTypeDesc

				,dbo.fn_formatdatetime(head.DocDate,'dd mmmm yyyy') AS DocDateDesc

				,item.MaterialID + ' - ' + mat1.ExternalID AS MaterialDesc

				,mattype.Description AS MatTypeDesc

				,wh.Description AS WarehouseDesc

FROM dbo.PTRMATERIALHEAD AS head WITH(NOLOCK)

LEFT JOIN dbo.PTRMATERIALITEM AS item WITH(NOLOCK)

	ON head.DocNo = item.DocNo

LEFT JOIN dbo.PCMBSDOCTYPE AS dt

	ON dt.DocType = head.DocType

LEFT JOIN dbo.PMDMAT0001 AS mat1

	ON mat1.MaterialID = item.MaterialID

LEFT JOIN dbo.PCMFILOMATTYPE AS mattype

	ON mattype.MaterialType = item.MaterialType

LEFT JOIN dbo.PCMFILOWAREHOUSE AS wh

	ON wh.Warehouse = (CASE WHEN item.Warehouse <> '' THEN item.Warehouse ELSE head.Warehouse END)

WHERE ((CASE WHEN item.Warehouse <> '' THEN item.Warehouse ELSE head.Warehouse END) = @Warehouse OR @Warehouse = '')

	AND (item.MaterialType = @MaterialType OR @MaterialType = '')

	AND (item.MaterialID = @MaterialID OR @MaterialID = '')

	AND (head.DocDate >= @StarDate AND head.DocDate <= @EndDate)

	AND item.MaterialID IS NOT NULL

--SELECT * FROM @TblDoc

DECLARE @TblDocQty TABLE

(

	DocNo VARCHAR(20)

	,DocType VARCHAR(20)

	,DocDate VARCHAR(20)

	,Description VARCHAR(500)

	,MaterialID VARCHAR(20)

	,MaterialType VARCHAR(20)

	,Warehouse VARCHAR(20)

	,Quantity DECIMAL(22,2)

	,ItemFlow VARCHAR(20)

	,QtyAdjust DECIMAL(22,2)

	,DocTypeDesc VARCHAR(500)