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) = '00000004'

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)
	,MatTypeDesc 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
		,mattype.Description AS MatTypeDesc
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
LEFT JOIN PCMFILOMATTYPE AS mattype
	ON mattype.MaterialType = mat1.MaterialType
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)
	,DocDateDesc VARCHAR(20)
	,MaterialDesc VARCHAR(500)
	,MatTypeDesc VARCHAR(500)
	,WarehouseDesc VARCHAR(500)
	,QtyIn DECIMAL(22,2)
	,QtyOut DECIMAL(22,2)
	,BalanceDoc DECIMAL(22,2)
)

INSERT INTO @TblDocQty
SELECT *
	,Quantity AS QtyIn
	,0 AS QtyOut
	,Quantity-0 AS BalanceDoc
FROM @TblDoc WHERE DocType IN ('RCSP','RTCU') 

INSERT INTO @TblDocQty
SELECT *
	,0 AS QtyIn
	,Quantity AS QtyOut
	,0-Quantity AS BalanceDoc
FROM @TblDoc WHERE DocType IN ('GICU','RTSP')

INSERT INTO @TblDocQty
SELECT *
	,Quantity AS QtyIn
	,0 AS QtyOut
	,Quantity-0 AS BalanceDoc
FROM @TblDoc WHERE DocType IN ('MTPR') AND ItemFlow = 'RCPT'

INSERT INTO @TblDocQty
SELECT *
	,0 AS QtyIn
	,Quantity AS QtyOut
	,0-Quantity AS BalanceDoc
FROM @TblDoc WHERE DocType IN ('MTPR') AND ItemFlow = 'ISSU'

INSERT INTO @TblDocQty
SELECT *
	,QtyAdjust AS QtyIn
	,0 AS QtyOut
	,QtyAdjust-0 AS BalanceDoc
FROM @TblDoc WHERE DocType IN ('SA') AND ItemFlow = 'RCPT'

INSERT INTO @TblDocQty
SELECT *
	,0 AS QtyIn
	,QtyAdjust AS QtyOut
	,0-QtyAdjust AS BalanceDoc
FROM @TblDoc WHERE DocType IN ('SA') AND ItemFlow = 'ISSU'

DECLARE @TblResult TABLE
(
	RowNo DECIMAL(22,0)
	,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)
	,QtyIn DECIMAL(22,2)
	,QtyOut DECIMAL(22,2)
	,Balance DECIMAL(22,2)
	,BalanceAwal DECIMAL(22,2)
	,BalanceAkhir DECIMAL(22,2)
)
INSERT INTO @TblResult
SELECT ROW_NUMBER() OVER (PARTITION BY tbldoc.MaterialID, tbldoc.Warehouse ORDER BY  tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.DocNo ASC) AS RowNo
		,*
		,0 AS BalanceAwal
		,0 AS BalancAkhir
FROM @TblDocQty AS tbldoc

INSERT INTO @TblResult
SELECT DISTINCT 0 AS RowNo
		,'' AS DocNo
		,'' AS DocType
		,'' AS DocDate
		,'Saldo awal' AS Description
		,tblstok.MaterialID AS MaterialID
		,'' AS MaterialType
		,tblstok.Warehouse AS Warehouse 
		,tblstok.Qty AS Quantity 
		,'' AS ItemFlow
		,0 AS QtyAdjust
		,'' AS DocTypeDesc
		,'' AS DocDateDesc
		,tblstok.MaterialDesc AS MaterialDesc 
		,tblstok.MatTypeDesc AS MatTypeDesc
		,tblstok.WarehouseDesc AS WarehouseDesc
		,0 AS QtyIn
		,0 AS QtyOut 
		,0 AS Balance
		,tblstok.Qty AS BalanceAwal
		,0 AS BalancAkhir
FROM @TblStock AS tblstok
LEFT JOIN ( SELECT tbldoc.Warehouse, tbldoc.MaterialID, SUM(tbldoc.BalanceDoc) AS SumBalance
			FROM @TblDocQty AS tbldoc
			GROUP BY tbldoc.Warehouse, tbldoc.MaterialID ) AS tbldocsum
	ON tbldocsum.MaterialID = tblstok.MaterialID
WHERE tbldocsum.Warehouse = tblstok.Warehouse

INSERT INTO @TblResult
SELECT DISTINCT '9999999999' AS RowNo
		,'' AS DocNo
		,'' AS DocType
		,'' AS DocDate
		,'Stock akhir' AS Description
		,tblstk.MaterialID AS MaterialID
		,'' AS MaterialType
		,tblstk.Warehouse AS Warehouse 
		,tblstk.Qty AS Quantity 
		,'' AS ItemFlow
		,ISNULL(tbldocsum.SumBalance,0) AS QtyAdjust ---- diisi sum balance dr document
		,'' AS DocTypeDesc
		,'' AS DocDateDesc
		,tblstk.MaterialDesc AS MaterialDesc 
		,tblstk.MatTypeDesc AS MatTypeDesc
		,tblstk.WarehouseDesc AS WarehouseDesc
		,0 AS QtyIn
		,0 AS QtyOut 
		,0 AS Balance
		,0 AS BalanceAwal
		,tblstk.Qty + ISNULL(tbldocsum.SumBalance,0) AS BalancAkhir
FROM @TblStock AS tblstk
LEFT JOIN ( SELECT tbldoc.Warehouse, tbldoc.MaterialID, SUM(tbldoc.BalanceDoc) AS SumBalance
			FROM @TblDocQty AS tbldoc
			GROUP BY tbldoc.Warehouse, tbldoc.MaterialID ) AS tbldocsum
	ON tbldocsum.MaterialID = tblstk.MaterialID
WHERE tbldocsum.Warehouse = tblstk.Warehouse

INSERT INTO @TblResult
SELECT DISTINCT '0' AS RowNo
		,'' AS DocNo
		,'' AS DocType
		,'' AS DocDate
		,'Stock awal' AS Description
		,tbldocsum.MaterialID AS MaterialID
		,'' AS MaterialType
		,tbldocsum.Warehouse AS Warehouse 
		,0 AS Quantity 
		,'' AS ItemFlow
		,ISNULL(tbldocsum.SumBalance,0) AS QtyAdjust ---- diisi sum balance dr document
		,'' AS DocTypeDesc
		,'' AS DocDateDesc
		,tbldocsum.MaterialDesc  AS MaterialDesc 
		,tbldocsum.MatTypeDesc AS MatTypeDesc
		,tbldocsum.WarehouseDesc AS WarehouseDesc
		,0 AS QtyIn
		,0 AS QtyOut 
		,0 AS Balance
		,0 AS BalanceAwal
		,0 AS BalancAkhir
FROM ( SELECT tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.WarehouseDesc, tbldoc.MaterialDesc, tbldoc.MatTypeDesc, SUM(tbldoc.BalanceDoc) AS SumBalance
			FROM @TblDocQty AS tbldoc
			GROUP BY tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.WarehouseDesc, tbldoc.MaterialDesc, tbldoc.MatTypeDesc ) AS tbldocsum
WHERE tbldocsum.Warehouse = ''

INSERT INTO @TblResult
SELECT DISTINCT '9999999999' AS RowNo
		,'' AS DocNo
		,'' AS DocType
		,'' AS DocDate
		,'Stock akhir' AS Description
		,tbldocsum.MaterialID AS MaterialID
		,'' AS MaterialType
		,tbldocsum.Warehouse AS Warehouse 
		,0 AS Quantity 
		,'' AS ItemFlow
		,ISNULL(tbldocsum.SumBalance,0) AS QtyAdjust ---- diisi sum balance dr document
		,'' AS DocTypeDesc
		,'' AS DocDateDesc
		,tbldocsum.MaterialDesc AS MaterialDesc 
		,tbldocsum.MatTypeDesc AS MatTypeDesc
		,tbldocsum.WarehouseDesc AS WarehouseDesc
		,0 AS QtyIn
		,0 AS QtyOut 
		,0 AS Balance
		,0 AS BalanceAwal
		,ISNULL(tbldocsum.SumBalance,0) AS BalancAkhir
FROM ( SELECT tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.WarehouseDesc, tbldoc.MaterialDesc, tbldoc.MatTypeDesc, SUM(tbldoc.BalanceDoc) AS SumBalance
			FROM @TblDocQty AS tbldoc
			GROUP BY tbldoc.Warehouse, tbldoc.MaterialID, tbldoc.WarehouseDesc, tbldoc.MaterialDesc, tbldoc.MatTypeDesc ) AS tbldocsum
WHERE tbldocsum.Warehouse = ''


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

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


