MINOVA DEMO

??ALTER PROCEDURE [dbo].[PFILOUPDATEMDSTOCKINFO]

(

	@MaterialID VARCHAR(20)

	,@Quantity DECIMAL(22,0)

	,@TotalPrice DECIMAL(20,0)

	,@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)

)

AS

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

--DECLARE @Quantity DECIMAL(22,0) = 1

--DECLARE @TotalPrice DECIMAL(20,0) = 0

--DECLARE @DocType VARCHAR(20) = 'GICU'

--DECLARE @UserID VARCHAR(20) = 'chani'

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

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

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

--DECLARE @Storage VARCHAR(20) = 'ST01'

--DECLARE @Bin VARCHAR(20) = 'R1-L1-0001'

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

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

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

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

--DECLARE @DocIDRef VARCHAR(20) = '00000020'

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

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

DECLARE @IsExist VARCHAR(20)

DECLARE @ExistStock DECIMAL(22,0)

DECLARE @ExistTotalMAP DECIMAL(22,0)

DECLARE @NewStock DECIMAL(22,0)

DECLARE @NewTotalMAP DECIMAL(22,0)

DECLARE @NewEachMAP DECIMAL(22,0)

DECLARE @IsSuccess VARCHAR(250)

SELECT DISTINCT @ExistStock = SUM(CONVERT(DECIMAL(22,0),Qty)), @ExistTotalMAP = SUM(CONVERT(DECIMAL(22,0),mat4.TotalValuation)), @IsExist = COUNT(mat4.MaterialID)

FROM dbo.PMDMAT0004 AS mat4

WHERE mat4.MaterialID = @MaterialID AND StockStatus = '0001' AND (CompanyID = @CompanyID OR @CompanyID = '')

	AND (Warehouse = @Warehouse OR @Warehouse = '') AND (Storage = @Storage OR @Storage = '') AND (Bin = @Bin OR @Bin = '') 

DECLARE @MaxSeqMateExist INT

SELECT @MaxSeqMateExist = MAX(SeqID) FROM PMDMAT0004 WHERE MaterialID = @MaterialID

IF(@DocType = 'RCSP')

BEGIN

	SET @NewStock = @ExistStock + @Quantity

	SET @NewEachMAP = @TotalPrice / @Quantity

	SET @NewTotalMAP = @NewEachMAP * @NewStock

	IF (@IsExist > 0)

	BEGIN

		SET @IsSuccess = 'Y'

		UPDATE dbo.PMDMAT0004

		SET Qty = @NewStock

			,TotalValuation = @NewTotalMAP

			,ChangeBy = @UserID

			,ChangeDate = @now16

		WHERE MaterialID = @MaterialID AND StockStatus = '0001' AND (CompanyID = @CompanyID OR @CompanyID = '')

			AND (Warehouse = @Warehouse OR @Warehouse = '') AND (Storage = @Storage OR @Storage = '') AND (Bin = @Bin OR @Bin = '')

	END

	ELSE IF (@IsExist = 0)

	BEGIN

		SET @IsSuccess = 'Y'	

		INSERT INTO dbo.PMDMAT0004

		SELECT DISTINCT @MaterialID	

						,@MaxSeqMateExist + 1

						,@CompanyID

						,@LogisticArea

						,@Warehouse

						,@Storage

						,@Bin

						,'0001'

						,'122324'

						,CONVERT(VARCHAR(4),@Quantity)

						,CONVERT(DECIMAL(18,2),@TotalPrice)

						,1

						,'113124'

						,@UserID AS CreateBy

						,@now16 AS CreateDate

						,@UserID AS ChangeBy

						,@now16 AS ChangeDate

						,'' AS SORDID 

						,'' AS PRODID

						,'' AS INTRID

						,'' AS POID 

	END

END

IF(@DocType = 'GICU')

BEGIN

	IF(ISNULL(@ExistStock,0) < @Quantity)

	BEGIN 

		SET @IsSuccess = 'OVERQTY' 

	END

	ELSE

	BEGIN

		SET @NewStock = @ExistStock - @Quantity

		SET @NewEachMAP = @ExistTotalMAP / @ExistStock  ---- menggunakan MAP sebelumnya

		SET @NewTotalMAP = @NewEachMAP * @NewStock

		--SELECT @NewStock, @NewEachMAP, @NewTotalMAP

		IF (@IsExist > 0)

		BEGIN

			SET @IsSuccess = 'Y'

			UPDATE dbo.PMDMAT0004

			SET Qty = CONVERT(VARCHAR(4), ISNULL(@NewStock,0))

				,TotalValuation = ISNULL(@NewTotalMAP,0)

				,ChangeBy = @UserID

				,ChangeDate = @now16

			WHERE MaterialID = @MaterialID AND StockStatus = '0001' AND (CompanyID = @CompanyID OR @CompanyID = '')