PFILOUPDATEMDSTOCKINFOPROD_KJL_20250214.sql - KJL - Redmine

Feature #3301 » PFILOUPDATEMDSTOCKINFOPROD_KJL_20250214.sql

Tri Rizqiaty, 02/14/2025 10:15 AM

    
      ??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

« Previous
1
2
3
Next »

(1-1/3)

Project

General

Profile

KJL

Feature #3301 » PFILOUPDATEMDSTOCKINFOPROD_KJL_20250214.sql