MINOVA DEMO

??USE [MinovaES_Demo]

GO

/****** Object:  StoredProcedure [dbo].[PFILOUPDATEMDSTOCKINFOMTRI]    Script Date: 26/08/2022 11.10.21 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PFILOUPDATEMDSTOCKINFOMTRI]

(

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

	,@DocStatus VARCHAR(20)

)

AS

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--DECLARE @DocStatus VARCHAR(20) = '2'

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 @ExistData DECIMAL(22,0)

DECLARE @IsSuccess VARCHAR(250)

IF(@DocType = 'INTR')

BEGIN

	IF(@DocStatus = '2' )

	BEGIN

		SELECT DISTINCT @ExistStock = qty, @ExistTotalMAP = TotalValuation

		FROM PMDMAT0004 AS mat4

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

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

		--SELECT @ExistStock

		DECLARE @MaxSeqMateExist INT

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

		IF(@ExistStock > 0)

		BEGIN

			SET @IsSuccess = 'Y'

			INSERT INTO dbo.PMDMAT0004

			SELECT DISTINCT @MaterialID					

						,@CompanyID

						,@MaxSeqMateExist + 1

						,@LogisticArea

						,@Warehouse

						,@Storage

						,@Bin

						,'0004'

						,'122324'

						,CONVERT(VARCHAR(4),@Quantity) * -1

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

						,1

						,'113124'

						,@UserID AS CreateBy

						,@now16 AS CreateDate

						,@UserID AS ChangeBy

						,@now16 AS ChangeDate

						,'' AS SORDID 

						,'' AS PRODID

						,@DocIDRef AS INTRID

						,'' AS POID 

			INSERT INTO dbo.PMDMAT0004

			SELECT DISTINCT @MaterialID	

						,@MaxSeqMateExist + 2

						,@CompanyID

						,@LogisticArea

						,@ReceiverWarehouse

						,@ReceiverStorage

						,@ReceiverBin

						,'0004'

						,'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

						,@DocIDRef AS INTRID

						,'' AS POID 

		END

		ELSE

		BEGIN

			SET @IsSuccess = 'NoData' 

		END

	END

	IF(@DocStatus = '3' )

	BEGIN

		SELECT DISTINCT @ExistData = COUNT(INTRID), @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 INTRID = @DocIDRef AND (MaterialID = @MaterialID OR @MaterialID = '')

		IF(@ExistData <= 0)

		BEGIN

			SET @IsSuccess = 'NoData' 

		END

		ELSE

		BEGIN

			SET @IsSuccess = 'Y'