ALTER PROCEDURE [dbo].[PFILOUPDATEMDSTOCKINFOPROD_KJL]
(
	@MaterialID VARCHAR(20)
	,@Quantity DECIMAL(22,2)
	,@TotalPrice DECIMAL(20,2)
	,@DocType VARCHAR(20)
	,@UserID VARCHAR(20)
	,@CompanyID VARCHAR(20)
	,@LogisticArea VARCHAR(20)
	,@Warehouse VARCHAR(20)
	,@Storage VARCHAR(20)
	,@Bin VARCHAR(20)
	,@ReceiverLogisticArea VARCHAR(20)
	,@ReceiverWarehouse VARCHAR(20) 
	,@ReceiverStorage VARCHAR(20)
	,@ReceiverBin VARCHAR(20)
	,@DocIDRef VARCHAR(20)
	,@DocTypeRef VARCHAR(20)
	,@TransTypeRef VARCHAR(20)
	,@ItemFlow VARCHAR(50)
	,@StockStatus VARCHAR(20)
	,@DocStatus VARCHAR(20)
	,@DocNoHead VARCHAR(20)
	,@SPK VARCHAR(20)
	,@ProcessID VARCHAR(20)
)
AS

--DECLARE @MaterialID VARCHAR(20) = '00000003'
--DECLARE @Quantity DECIMAL(22,2) = 5
--DECLARE @TotalPrice DECIMAL(20,2) = '0'
--DECLARE @DocType VARCHAR(20) = 'MIX'
--DECLARE @UserID VARCHAR(20) = 'tri'
--DECLARE @CompanyID VARCHAR(20) = '1000'
--DECLARE @LogisticArea VARCHAR(20) = ''
--DECLARE @Warehouse VARCHAR(20) = '0001'
--DECLARE @Storage VARCHAR(20) = ''
--DECLARE @Bin VARCHAR(20) = 'LOT1'
--DECLARE @ReceiverLogisticArea VARCHAR(20) = ''
--DECLARE @ReceiverWarehouse VARCHAR(20) = ''
--DECLARE @ReceiverStorage VARCHAR(20) = ''
--DECLARE @ReceiverBin VARCHAR(20) = ''
--DECLARE @DocIDRef VARCHAR(20) = '00000336'
--DECLARE @DocTypeRef VARCHAR(20) = 'SPK'
--DECLARE @TransTypeRef VARCHAR(20) = 'PRD'
--DECLARE @ItemFlow VARCHAR(50) = 'ISSU'--'ISSU' --ISSU --RCPT
--DECLARE @StockStatus VARCHAR(20) = '0001'
--DECLARE @DocStatus VARCHAR(20) = '4' -- 1:Draft , 2:In Process, 3:Done, 4:Cancel
--DECLARE @DocNoHead VARCHAR(20) = '00000337'
--DECLARE @SPK VARCHAR(20) = '00000336'

DECLARE @now VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')
DECLARE @now16 VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmddhhnnss')

DECLARE @IsSuccess VARCHAR(250)

DECLARE @TblMAT4 TABLE
(
	[MaterialID] [VARCHAR](10) NOT NULL,
	[SeqID] [BIGINT] NOT NULL,
	[CompanyID] [VARCHAR](4) NOT NULL,
	[LogisticAreaID] [VARCHAR](10) NOT NULL,
	[Warehouse] [VARCHAR](20) NOT NULL,
	[Storage] [VARCHAR](10) NOT NULL,
	[Bin] [VARCHAR](10) NOT NULL,
	[StockStatus] [VARCHAR](10) NOT NULL,
	[GLValuationID] [VARCHAR](20) NULL,
	[Qty] DECIMAL(22,2) NULL,
	[TotalValuation] [DECIMAL](18, 2) NULL,
	[Sequence] [INT] NULL,
	[SerialNumber] [VARCHAR](50) NULL,
	[CreateBy] [VARCHAR](18) NULL,
	[CreateDate] [VARCHAR](14) NULL,
	[ChangeBy] [VARCHAR](18) NULL,
	[ChangeDate] [VARCHAR](14) NULL,
	[SORDID] [VARCHAR](18) NULL,
	[PRODID] [VARCHAR](18) NULL,
	[INTRID] [VARCHAR](18) NULL,
	[POID] [VARCHAR](18) NULL,
	[StockType] [VARCHAR](10) NULL,
	[DocIDRef] [VARCHAR](10) NULL,
	[DocTypeRef] [VARCHAR](10) NULL,
	[TransTypeRef] [VARCHAR](10) NULL
)
INSERT INTO @TblMAT4
SELECT [MaterialID]
      ,[SeqID]
      ,[CompanyID]
      ,[LogisticAreaID]
      ,[Warehouse]
      ,[Storage]
      ,[Bin]
      ,[StockStatus]
      ,[GLValuationID]
      ,[Qty]
      ,[TotalValuation]
      ,[Sequence]
      ,[SerialNumber]
      ,[CreateBy]
      ,[CreateDate]
      ,[ChangeBy]
      ,[ChangeDate]
      ,[SORDID]
      ,[PRODID]
      ,[INTRID]
      ,[POID]
      ,[StockType]
      ,[DocIDRef]
      ,[DocTypeRef]
      ,[TransTypeRef]
FROM dbo.PMDMAT0004
WHERE MaterialID = @MaterialID

DECLARE @MAP_MAT1 DECIMAL(22,2) = 0 
DECLARE @NewTotalValuation DECIMAL(22,2)
DECLARE @CekItemNotDoneAloc VARCHAR(20)
DECLARE @CekItemNotDone VARCHAR(20)
DECLARE @SisaQty DECIMAL(22,2) = @Quantity

IF(@DocType != 'SPK' AND @ItemFLow = 'ISSU' AND (@DocTypeRef = '' OR @DocTypeRef = 'SPK'))
BEGIN

	SELECT @MAP_MAT1 = MAP FROM dbo.PMDMAT0001 WHERE MaterialID = @MaterialID

	DECLARE @SumQtyMAT4 DECIMAL(22,2)
	SELECT @SumQtyMAT4 =  SUM(CONVERT(DECIMAL(22,2),Qty)) FROM @TblMAT4 
	WHERE (CompanyID = @CompanyID OR @CompanyID = '')
		AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')
		AND (Warehouse = @Warehouse OR @Warehouse = '')
		AND (Storage = @Storage OR @Storage = '')
		AND (Bin = @Bin OR @Bin = '')
		AND StockStatus = '0001'

	IF(@Quantity <= @SumQtyMAT4)
	BEGIN
		DECLARE @row DECIMAL(22,0) = 0
		DECLARE @SisaQuantity DECIMAL(22,2) = @Quantity
		DECLARE @c_materialid VARCHAR(20)
		DECLARE @c_seq VARCHAR(20)
		DECLARE @c_qty VARCHAR(20)
		DECLARE cur_mat4 CURSOR FOR (SELECT MaterialID, SeqID, Qty FROM @TblMAT4 WHERE (CompanyID = @CompanyID OR @CompanyID = '') 
									AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '') AND (Warehouse = @Warehouse OR @Warehouse = '')	
									AND (Storage = @Storage OR @Storage = '') AND (Bin = @Bin OR @Bin = '') AND StockStatus = '0001')
		OPEN cur_mat4
		FETCH cur_mat4 INTO @c_materialid, @c_seq, @c_qty
		WHILE @@Fetch_Status = 0
		BEGIN
			SET @row = @row + 1
			DECLARE @QuantityAlocate DECIMAL(22,2)
			IF(@SisaQuantity <= 0)
			BREAK

			DECLARE @SisaQtyMat4 DECIMAL(22,2)
			IF(@SisaQuantity > @c_qty)
			BEGIN 
				SET @SisaQtyMat4 = 0
				SET @SisaQuantity = @SisaQuantity - @c_qty
				SET @QuantityAlocate = @c_qty
			END
			ELSE IF(@SisaQuantity < @c_qty)
			BEGIN  
				SET @SisaQtyMat4 = @c_qty - @SisaQuantity
				SET @SisaQuantity = @SisaQuantity - @c_qty	 
				SET @QuantityAlocate = @c_qty - @SisaQtyMat4
			END
			ELSE IF(@SisaQuantity = @c_qty)
			BEGIN 
				SET @SisaQtyMat4 = 0
				SET @SisaQuantity = @SisaQuantity - @c_qty
				SET @QuantityAlocate = @c_qty
			END

			DECLARE @MaxSeqMat4Detail VARCHAR(20) = (SELECT MAX(SeqID) FROM @TblMAT4)

			IF(@DocStatus = '2')
			BEGIN
				
				SET @NewTotalValuation = @MAP_MAT1 * @SisaQtyMat4
				UPDATE dbo.PMDMAT0004
				SET Qty = @SisaQtyMat4
					,TotalValuation = @NewTotalValuation
					,ChangeBy = @UserID
					,ChangeDate = @now16
				----SELECT * FROM dbo.PMDMAT0004
				WHERE MaterialID = @c_materialid
					AND SeqID = @c_seq
				--SELECT @SisaQuantity, @QuantityAlocate
				IF(@SisaQuantity <= 0)
				BEGIN
					INSERT INTO PMDMAT0004
					SELECT @MaterialID AS MaterialID
						  ,@MaxSeqMat4Detail + 1 -- @row --pakai row jika quantity alocate displit
						  ,@CompanyID
						  ,@LogisticArea
						  ,@Warehouse
						  ,@Storage
						  ,@Bin
						  ,'0004' AS StockStatus
						  ,'' AS [GLValuationID]
						  ,@Quantity --,@QuantityAlocate  --jika quantity alocate displit
						  ,@MAP_MAT1 * @Quantity AS [TotalValuation]
						  ,@MaxSeqMat4Detail + 1 --@roq --pakai row jika quantity alocate displit
						  ,'' AS [SerialNumber]
						  ,@UserID
						  ,@now16
						  ,@UserID
						  ,@now16
						  ,'' AS [SORDID]
						  ,@DocNoHead AS [PRODID]
						  ,'' AS [INTRID]
						  ,'' AS [POID]
						  ,'1'[StockType]
						  ,@DocIDRef AS [DocIDRef]
						  ,@DocTypeRef AS [DocTypeRef]
						  ,@TransTypeRef AS [TransTypeRef]
					FROM @TblMAT4 
					WHERE SeqID = @MaxSeqMat4Detail	
				END
			END
			IF(@DocStatus = '4')
			BEGIN
				DECLARE @DataProdExist DECIMAL(22,0)
				SELECT @DataProdExist = COUNT(@MaterialID) FROM @TblMAT4 
				WHERE MaterialID = @MaterialID
					AND PRODID = @DocNoHead
					AND StockStatus = '0004'
					--AND Qty = @Quantity

				IF(@DataProdExist > 0)
				BEGIN
					DELETE FROM PMDMAT0004
					--SELECT * FROM PMDMAT0004 
					WHERE MaterialID = @MaterialID
						AND PRODID = @DocNoHead
						AND Qty = @Quantity
						AND StockStatus = '0004'
				END
				ELSE IF(@DataProdExist <= 0)
				BEGIN
					SET @NewTotalValuation = @MAP_MAT1 * @SisaQtyMat4
					UPDATE dbo.PMDMAT0004
					SET Qty = @SisaQtyMat4
						,TotalValuation = @NewTotalValuation
						,ChangeBy = @UserID
						,ChangeDate = @now16
					--SELECT * FROM dbo.PMDMAT0004
					WHERE MaterialID = @c_materialid
						AND SeqID = @c_seq
				END

				UPDATE PMDMATWIP
				SET DocStatus = @DocStatus
					,StockStatus = '0005'
					,ChangeBy = @UserID
					,ChangeDate = @now16
				--SELECT * FROM PMDMATWIP
				WHERE MaterialID = @MaterialID
					AND ProdID = @DocNoHead
					AND FlowType = @ItemFlow

				UPDATE PTRPRODUCTIONITEM
				SET Status = '2'
				,ChangeBy = @UserID
				,ChangeDate = @now16
				--SELECT * FROM PTRPRODUCTIONITEM
				WHERE DocNo = @DocNoHead
					AND MaterialID = @MaterialID
					END

		FETCH cur_mat4 INTO @c_materialid, @c_seq, @c_qty 
		END
		CLOSE cur_mat4
		DEALLOCATE cur_mat4

		SET @IsSuccess = 'Y'
	END
	ELSE 
	BEGIN
		SET @IsSuccess = 'OVERQTY'
	END
END

IF(@DocType != 'SPK' AND @ItemFLow = 'ISSU' AND (@DocTypeRef <> 'SPK'))
BEGIN
	
	UPDATE PMDMATWIP
	SET StockStatus = '0005'
		,ChangeBy = @UserID
		,ChangeDate = @now16
	--SELECT * FROM PMDMATWIP
	WHERE SpkID = @SPK
		AND MaterialID = @MaterialID
		AND StockStatus = '0001'
		AND ProdID = @DocIDRef

	UPDATE PTRPRODUCTIONITEM
	SET Status = '2'
		,ChangeBy = @UserID
		,ChangeDate = @now16
	--SELECT * FROM PTRPRODUCTIONITEM
	WHERE DocNo = @DocIDRef
		AND MaterialID = @MaterialID

	IF(@DocStatus = '4')
	BEGIN
		UPDATE PMDMATWIP
		SET DocStatus = @DocStatus
			,StockStatus = '0005'
			,ChangeBy = @UserID
			,ChangeDate = @now16
		--SELECT * FROM PMDMATWIP
		WHERE SpkID = @SPK
			AND MaterialID = @MaterialID
			AND ProdID = @DocNoHead

		UPDATE PTRPRODUCTIONITEM
		SET Status = '2'
		,ChangeBy = @UserID
		,ChangeDate = @now16
		--SELECT * FROM PTRPRODUCTIONITEM
		WHERE DocNo = @DocNoHead
			AND MaterialID = @MaterialID
	END

	SET @IsSuccess = 'Y'
END


IF(@DocType != 'SPK' AND @ItemFLow = 'RCPT' AND @DocStatus = '4')
BEGIN
	UPDATE PMDMATWIP
	SET DocStatus = @DocStatus
		,StockStatus = '0001'
		,ChangeBy = @UserID
		,ChangeDate = @now16
	--SELECT * FROM PMDMATWIP
	WHERE MaterialID = @MaterialID
		AND ProdID = @DocNoHead
		AND FlowType = @ItemFlow

	UPDATE PMDMATWIP
	SET --DocStatus = @DocStatus
		StockStatus = '0001'
		,ChangeBy = @UserID
		,ChangeDate = @now16
	--SELECT * FROM PMDMATWIP
	WHERE MaterialID = @MaterialID
		AND ProdID = @SPK

	UPDATE PTRPRODUCTIONITEM
	SET Status = '1'
		,ChangeBy = @UserID
		,ChangeDate = @now16
	--SELECT * FROM PTRPRODUCTIONITEM
	WHERE DocNo = @SPK
		AND MaterialID = @MaterialID

	UPDATE PTRPRODUCTIONITEM
	SET Status = '1'
		,ChangeBy = @UserID
		,ChangeDate = @now16
	--SELECT * FROM PTRPRODUCTIONITEM
	WHERE DocNo = @DocNoHead
		AND MaterialID = @MaterialID

	----UPDATE PTRPRODUCTIONHEAD
	----SET DocStatus = @DocStatus
	----	,ProcessStatus = 'C'
	----	,ChangeBy = @UserID
	----	,ChangeDate = @now16
	------SELECT * FROM PTRPRODUCTIONHEAD
	----WHERE DocNo = @SPK
	----	AND FinishGoods = @MaterialID

	SET @IsSuccess = 'Y'
END
ELSE SET @IsSuccess = 'Y'
--IF(@DocType != 'SPK' AND @ItemFLow = 'RCPT' AND @DocStatus = '4' AND @FinishGood = @MaterialID)
--BEGIN
--	SELECT 
--END

SELECT @IsSuccess AS IsSuccess

DELETE FROM @TblMAT4