PFILOUPDATEMDSTOCKINFO_20251210.sql - MINOVA DEMO - Redmine

Bug #4055 » PFILOUPDATEMDSTOCKINFO_20251210.sql

Tri Rizqiaty, 12/10/2025 03:23 PM

    
      ??ALTER PROCEDURE [dbo].[PFILOUPDATEMDSTOCKINFO]

      (

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

      )

      AS

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

      --DECLARE @Quantity DECIMAL(22,2) = 10

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

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

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

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

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

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

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

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

      --DECLARE @DocTypeRef VARCHAR(20) = 'FLEX'

      --DECLARE @TransTypeRef VARCHAR(20) = 'PRD'

      --DECLARE @ItemFlow VARCHAR(50) = ''--'ISSU' --ISSU --RCPT

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

      --DECLARE @DocStatus VARCHAR(20) = '3' -- 1:Draft , 2:In Process, 3:Done, 4:Cancel

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

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

      DECLARE @IsSuccess VARCHAR(250)

      IF(@StockStatus = '')

      BEGIN SET @StockStatus = '0001' END

      IF(@Quantity < 0)

      BEGIN SET @Quantity = @Quantity * -1 END

      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 = 'GICU' AND (@DocStatus = '3' OR @DocStatus = '2') )

      BEGIN

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

      	IF(@DocIDRef <> '' AND @DocTypeRef <> '' AND @TransTypeRef <>'')		---- // jika doc ref dr sales order

      	BEGIN

      		DECLARE @SumMat4Aloc DECIMAL(22,2) = 0

      		SELECT @SumMat4Aloc = SUM(Qty)	FROM @TblMAT4 

      		WHERE MaterialID = @MaterialID AND DocIDRef = @DocIDRef AND DocTypeRef = @DocTypeRef AND TransTypeRef = @TransTypeRef AND Warehouse = @Warehouse --AND StockStatus = @StockStatus

      		IF(@Quantity > @SumMat4Aloc)

      		BEGIN

      			SET @IsSuccess = 'OVERQTY'

      		END

      		ELSE

      		BEGIN 

      			DECLARE @QtyAlocateMAT4 DECIMAL(22,2) = 0

      			DECLARE @c_materialidA VARCHAR(20)

      			DECLARE @c_seqA VARCHAR(20)

      			DECLARE @c_qtyA VARCHAR(20)

      			DECLARE cur_mat4A CURSOR FOR (SELECT MaterialID, SeqID, Qty FROM @TblMAT4 WHERE MaterialID = @MaterialID

      										AND DocIDRef = @DocIDRef AND DocTypeRef = @DocTypeRef AND TransTypeRef = @TransTypeRef AND Warehouse = @Warehouse) --AND StockStatus = @StockStatus )

      			OPEN cur_mat4A

      			FETCH cur_mat4A INTO @c_materialidA, @c_seqA, @c_qtyA

      			WHILE @@Fetch_Status = 0

      			BEGIN

      				SET @QtyAlocateMAT4 = @c_qtyA

      				IF(@SisaQty = 0)

      				BEGIN

      					BREAK

      				END

      				ELSE IF(@QtyAlocateMAT4 > @SisaQty)				----// case good issue sebagian(partial)

      				BEGIN

      					DECLARE @SisaQtyAlocateMAT4 DECIMAL(22,2) = 0

      					SET @SisaQtyAlocateMAT4 = @QtyAlocateMAT4 - @SisaQty

      					UPDATE dbo.PMDMAT0004					----// update quantity mat4

      					SET Qty = @SisaQtyAlocateMAT4

      						,TotalValuation = @SisaQtyAlocateMAT4 * @MAP_MAT1

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					--SELECT 'update qty mat4', @SisaQtyAlocateMAT4, * FROM dbo.PMDMAT0004

      					WHERE MaterialID = @MaterialID

      						AND DocIDRef = @DocIDRef 

      						AND DocTypeRef = @DocTypeRef 

      						AND TransTypeRef = @TransTypeRef

      						AND Warehouse = @Warehouse 

      						AND SeqID = @c_seqA

      					DECLARE @MaxDocItemID DECIMAL(22,0)

      					SELECT @MaxDocItemID = MAX(DocItemID)

      					FROM dbo.PTRALLOCATION WITH(NOLOCK)

      					WHERE DocNo = @DocIDRef

      					UPDATE dbo.PTRALLOCATION					----// update quantity ptraloc

      					SET StockAllocation = @SisaQtyAlocateMAT4

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					--SELECT  'udpate stock ptraloc', @SisaQtyAlocateMAT4, * FROM dbo.PTRALLOCATION WITH(NOLOCK)

      					WHERE DocNo = @DocIDRef

      						AND MaterialID = @MaterialID

      						AND WareHouse = @Warehouse

      						AND ItemStatus = '1'

      						AND StockAllocation = @c_qtyA

      					INSERT INTO PTRALLOCATION

      					SELECT DISTINCT	[DocNo] 

      					--SELECT DISTINCT 'add ptraloc', DocNo

      						,CONVERT(VARCHAR(20),@MaxDocItemID + 1) AS [DocItemID]

      						,@SisaQty AS StockAllocation

      						,[WareHouse]

      						,[Storage]

      						,[Bin]

      						,[TransferOrderRef]

      						,[TransferOrderItem]

      						,[PurchaseOrderRef]

      						,[PurchaseOrderItem]

      						,[ProductionOrderRef]

      						,[ProductionOrderItem]

      						,@UserID

      						,@now16

      						,@UserID

      						,@now16

      						,[MaterialID]

      						,[StockType]

      						,[MAP]

      						,'2' AS ItemStatus				----// 2 = Done

      						,[ItemType]

      						,IsAllocated

      					FROM dbo.PTRALLOCATION

      					WHERE MaterialID = @MaterialID

      						AND DocNo = @DocIDRef 

      						AND Warehouse = @Warehouse 

      						AND ItemStatus = '1'

      					SET @IsSuccess = 'Y' 

      					BREAK

      				END

      				ELSE IF(@SisaQty >= @QtyAlocateMAT4)

      				BEGIN

      					SET @SisaQtyAlocateMAT4 = 0

      					SET @SisaQty = @SisaQty - @QtyAlocateMAT4

      					DELETE FROM dbo.PMDMAT0004						--// delete mat4

      					--SELECT 'delete mat4',* FROM dbo.PMDMAT0004 

      					WHERE MaterialID = @MaterialID AND SeqID = @c_seqA

      					UPDATE dbo.PTRALLOCATION						--// update ptraloc

      					SET ItemStatus = '2'

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					--SELECT'update items status ptraloc jd 2', * FROM dbo.PTRALLOCATION

      					WHERE MaterialID = @c_materialidA

      						AND DocNo = @DocIDRef 

      						AND Warehouse = @Warehouse

      						AND ItemStatus = '1'

      						AND StockAllocation = @c_qtyA	

      					SET @IsSuccess = 'Y'

      				END

      				FETCH cur_mat4A INTO @c_materialidA, @c_seqA, @c_qtyA 

      			END

      			CLOSE cur_mat4A

      			DEALLOCATE cur_mat4A

      			SELECT @CekItemNotDoneALoc = COUNT(ItemStatus)

      			FROM dbo.PTRALLOCATION 

      			WHERE DocNo = @DocIDRef AND (ItemStatus = '0' OR ItemStatus = '1') AND MaterialID = @MaterialID

      			IF(@CekItemNotDoneALoc = 0)

      			BEGIN

      				UPDATE dbo.PTRSALESITEMS

      				SET ItemStatus = '2'

      				WHERE DocNo = @DocIDRef AND Material = @MaterialID

      			END

      			ELSE

      			BEGIN

      				UPDATE dbo.PTRSALESITEMS

      				SET ItemStatus = '1'

      				WHERE DocNo = @DocIDRef AND Material = @MaterialID

      			END

      			SELECT @CekItemNotDone = COUNT(ItemStatus)

      			FROM dbo.PTRALLOCATION 

      			WHERE DocNo = @DocIDRef AND (ItemStatus = '0' OR ItemStatus = '1')

      			IF(@CekItemNotDone = 0)

      			BEGIN

      				UPDATE dbo.PTRSALESHEADER

      				SET ProcessStatus = 'C'

      				WHERE DocNo = @DocIDRef 

      			END

      		END

      	END

      	ELSE

      	BEGIN

      		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 StockStatus = @StockStatus

      		IF(@Quantity <= @SumQtyMAT4)

      		BEGIN

      			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 (Storage = @Bin OR @Bin = '') AND StockStatus = @StockStatus)

      			OPEN cur_mat4

      			FETCH cur_mat4 INTO @c_materialid, @c_seq, @c_qty

      			WHILE @@Fetch_Status = 0

      			BEGIN

      				IF(@SisaQuantity <= 0)

      				BREAK

      				DECLARE @SisaQtyMat4 DECIMAL(22,2)

      				IF(@SisaQuantity > @c_qty)

      				BEGIN 

      					SET @SisaQtyMat4 = 0

      					SET @SisaQuantity = @SisaQuantity - @c_qty

      				END

      				ELSE IF(@SisaQuantity < @c_qty)

      				BEGIN  

      					SET @SisaQtyMat4 = @c_qty - @SisaQuantity

      					SET @SisaQuantity = @SisaQuantity - @c_qty	 

      				END

      				ELSE IF(@SisaQuantity = @c_qty)

      				BEGIN 

      					SET @SisaQtyMat4 = 0

      					SET @SisaQuantity = @SisaQuantity - @c_qty

      				END

      				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

      			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

      END

      IF(@DocType = 'RCSP' AND (@DocStatus = '3')) -- OR @DocStatus = '2') )

      BEGIN 

      	DECLARE @IsMat4Exist VARCHAR(20) = ISNULL((SELECT COUNT(*) FROM @TblMAT4),0)

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

      	DECLARE @TotalValuationAllMAT4 DECIMAL(22,2)

      	DECLARE @TotalQtyAllMAT4 DECIMAL(22,2)

      	DECLARE @TotalValuationAll DECIMAL(22,2)

      	DECLARE @TotalQtyAll DECIMAL(22,2)

      	DECLARE @NewMAP DECIMAL(22,2)

      	IF(@IsMat4Exist <= 0)

      	BEGIN

      		INSERT INTO PMDMAT0004

      		SELECT @MaterialID

      			  ,'1'

      			  ,@CompanyID

      			  ,@LogisticArea

      			  ,@Warehouse

      			  ,@Storage

      			  ,@Bin

      			  ,@StockStatus --0001

      			  ,''

      			  ,@Quantity

      			  ,@Quantity * @MAP_MAT1 AS TotalValuation

      			  ,'1' AS [Sequence]

      			  ,'' AS [SerialNumber]

      			  ,@UserID

      			  ,@now16

      			  ,@UserID

      			  ,@now16

      			  ,'' AS [SORDID]

      			  ,'' AS [PRODID]

      			  ,'' AS [INTRID]

      			  ,'' AS [POID]

      			  ,'1' AS [StockType]

      			  ,'' AS [DocIDRef]

      			  ,'' AS [DocTypeRef]

      			  ,'' AS [TransTypeRef]

      		SET @IsSuccess = 'Y'

      	END

      	ELSE

      	BEGIN 

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

      		DECLARE @IsMat4ExistDetail VARCHAR(20) 

      		SELECT @IsMat4ExistDetail = COUNT(MaterialID)

      		FROM @TblMAT4

      		WHERE (CompanyID = @CompanyID OR @CompanyID = '')

      			AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')

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

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

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

      			AND (StockStatus = @StockStatus)

      		DECLARE @PricePurSatuan DECIMAL(22,2) = 0

      		SELECT @PricePurSatuan = (CONVERT(DECIMAL(22,2),TotAmount) / CONVERT(DECIMAL(22,0),MaterialQuantity))

      		FROM dbo.PTRPURCHITEM

      		WHERE DocNo = @DocIDRef

      			AND Material = @MaterialID

      		IF(@PricePurSatuan < 0)

      		BEGIN

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

      			SET @PricePurSatuan = @MAP_MAT1

      		END

      		SET @TotalValuationAllMAT4 = (SELECT SUM(CONVERT(DECIMAL(22,2),TotalValuation)) FROM @TblMAT4)

      		SET @TotalQtyAllMAT4 = (SELECT SUM(CONVERT(DECIMAL(22,2),Qty)) FROM @TblMAT4)

      		SET @TotalValuationAll = @TotalValuationAllMAT4 + (@PricePurSatuan * @Quantity) --@TotalPrice

      		SET @TotalQtyAll = @TotalQtyAllMAT4 + @Quantity

      		SET @NewMAP = @TotalValuationAll / @TotalQtyAll

      		SET @NewTotalValuation = @NewMAP * @Quantity

      		IF(@IsMat4ExistDetail <= 0)

      		BEGIN

      			IF(@DocIDRef <> '' AND @TransTypeRef = 'PRD')

      			BEGIN

      				INSERT INTO PMDMAT0004

      				SELECT @MaterialID

      					  ,@MaxSeqMat4Detail + 1

      					  ,@CompanyID

      					  ,@LogisticArea

      					  ,@Warehouse

      					  ,@Storage

      					  ,@Bin

      					  ,@StockStatus--'0001'

      					  ,''

      					  ,@Quantity

      					  ,@NewTotalValuation AS TotalValuation

      					  ,'1' AS [Sequence]

      					  ,'' AS [SerialNumber]

      					  ,@UserID

      					  ,@now16

      					  ,@UserID

      					  ,@now16

      					  ,'' AS [SORDID]

      					  ,@DocIDRef AS [PRODID]

      					  ,'' AS [INTRID]

      					  ,'' AS [POID]

      					  ,'1' AS [StockType]

      					  ,@DocIDRef AS [DocIDRef]

      					  ,@DocTypeRef AS [DocTypeRef]

      					  ,@DocTypeRef AS [TransTypeRef]

      				UPDATE dbo.PMDMAT0001

      				SET MAP = @NewMAP

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				FROM dbo.PMDMAT0001

      				WHERE MaterialID = @MaterialID

      				UPDATE PMDMATWIP

      				SET StockStatus = '0005'

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				--SELECT * FROM PMDMATWIP

      				WHERE ProdID = @DocIDRef

      					AND MaterialID = @MaterialID

      				DECLARE @SPK_ VARCHAR(20)

      				SELECT @SPK_ = SpkID

      				FROM PMDMATWIP

      				WHERE ProdID = @DocIDRef

      					AND MaterialID = @MaterialID

      				UPDATE PMDMATWIP

      				SET DocStatus = '4'

      					,StockStatus = '0005'

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				--SELECT * FROM PMDMATWIP

      				WHERE ProdID = @SPK_

      					AND MaterialID = @MaterialID 

      				UPDATE PTRPRODUCTIONITEM

      				SET Status = '2'

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				--SELECT * FROM PTRPRODUCTIONITEM

      				WHERE DocNo = @DocIDRef

      					AND FlowType = 'RCPT'

      				UPDATE PTRPRODUCTIONHEAD

      				SET DocStatus = '4'

      					,ProcessStatus = 'C'

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				--SELECT * FROM PTRPRODUCTIONHEAD

      				WHERE DocNo = @SPK_

      				UPDATE PTRPRODUCTIONITEM

      				SET Status = '2'

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				--SELECT * FROM PTRPRODUCTIONITEM

      				WHERE DocNo = @SPK_

      					AND FlowType = 'RCPT'

      				SET @IsSuccess = 'Y'

      			END

      			ELSE

      			BEGIN

      				INSERT INTO PMDMAT0004

      				SELECT @MaterialID

      					  ,@MaxSeqMat4Detail + 1

      					  ,@CompanyID

      					  ,@LogisticArea

      					  ,@Warehouse

      					  ,@Storage

      					  ,@Bin

      					  ,@StockStatus--'0001'

      					  ,''

      					  ,@Quantity

      					  ,@NewTotalValuation AS TotalValuation

      					  ,'1' AS [Sequence]

      					  ,'' AS [SerialNumber]

      					  ,@UserID

      					  ,@now16

      					  ,@UserID

      					  ,@now16

      					  ,'' AS [SORDID]

      					  ,'' AS [PRODID]

      					  ,'' AS [INTRID]

      					  ,'' AS [POID]

      					  ,'1' AS [StockType]

      					  ,'' AS [DocIDRef]

      					  ,'' AS [DocTypeRef]

      					  ,'' AS [TransTypeRef]

      				UPDATE dbo.PMDMAT0001

      				SET MAP = @NewMAP

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				FROM dbo.PMDMAT0001

      				WHERE MaterialID = @MaterialID

      				SET @IsSuccess = 'Y'

      			END

      		END

      		ELSE

      		BEGIN

      			SET @TotalValuationAllMAT4 = (SELECT SUM(CONVERT(DECIMAL(22,2),TotalValuation)) FROM @TblMAT4)

      			SET @TotalQtyAllMAT4 = (SELECT SUM(CONVERT(DECIMAL(22,2),Qty)) FROM @TblMAT4)

      			SET @TotalValuationAll = @TotalValuationAllMAT4 + @TotalPrice

      			SET @TotalQtyAll = @TotalQtyAllMAT4 + @Quantity

      			SET @NewMAP = @TotalValuationAll / @TotalQtyAll

      			DECLARE @MinSeqMat4 VARCHAR(20)

      			SELECT @MinSeqMat4 = MIN(SeqID)

      			FROM @TblMAT4

      			WHERE (CompanyID = @CompanyID OR @CompanyID = '')

      				AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')

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

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

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

      				AND (StockStatus = @StockStatus)

      			DECLARE @NewQty DECIMAL(22,2)

      			SELECT @NewQty = CONVERT(DECIMAL(22,2),Qty) + @Quantity

      				,@NewTotalValuation = (CONVERT(DECIMAL(22,2),Qty) + @Quantity) * @NewMAP

      			FROM @TblMAT4

      			WHERE (CompanyID = @CompanyID OR @CompanyID = '')

      				AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')

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

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

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

      				AND (SeqID = @MinSeqMat4)

      			IF(@DocIDRef <> '' AND @DocTypeRef = 'PO' AND @TransTypeRef <> '')

      			BEGIN

      				DECLARE @QtyPurchaseReal DECIMAL(22,1) = 0

      				SELECT @QtyPurchaseReal = PurchaseRealization

      				FROM dbo.PTRPURCHREALIZATION

      				WHERE DocNo = @DocIDRef

      					AND MaterialID = @MaterialID 

      					AND ItemStatus = '1'

      				--SELECT @Quantity, @QtyPurchaseReal

      				IF(@Quantity < @QtyPurchaseReal)

      				BEGIN

      					UPDATE dbo.PMDMAT0004

      					SET Qty = @NewQty

      						,TotalValuation = @NewTotalValuation

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					WHERE (CompanyID = @CompanyID OR @CompanyID = '')

      						AND (MaterialID = @MaterialID)

      						AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')

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

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

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

      						AND (SeqID = @MinSeqMat4)

      					UPDATE dbo.PMDMAT0001

      					SET MAP = @NewMAP

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					FROM dbo.PMDMAT0001

      					WHERE MaterialID = @MaterialID

      					UPDATE dbo.PMDMAT0004

      					SET TotalValuation = (CONVERT(DECIMAL(22,2),Qty) * @NewMAP)

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					WHERE (MaterialID = @MaterialID)

      						AND (SeqID <> @MinSeqMat4)	

      					DECLARE @MaxSeqPurReal DECIMAL(22,2) = 0

      					SELECT @MaxSeqPurReal = MAX(DocItemID)

      					FROM dbo.PTRPURCHREALIZATION

      					WHERE DocNo = @DocIDRef

      					INSERT INTO PTRPURCHREALIZATION

      					SELECT @DocIDRef AS [DocNo]

      						  ,@MaxSeqPurReal + 1 AS [DocItemID]

      						  ,[ItemType]

      						  ,[MaterialID]

      						  ,@Quantity

      						  ,[MaterialUnit]

      						  ,@CompanyID

      						  ,@LogisticArea

      						  ,@Warehouse

      						  ,@Storage

      						  ,@Bin

      						  ,'2' AS ItemStatus

      						  ,@Quantity * @PricePurSatuan AS TotAmount

      						  ,[StockType]

      						  ,@UserID

      						  ,@now16

      						  ,@UserID

      						  ,@now16

      					FROM dbo.PTRPURCHREALIZATION 

      					WHERE DocNo = @DocIDRef 

      						AND MaterialID = @MaterialID

      						AND ItemStatus = '1'

      					UPDATE dbo.PTRPURCHREALIZATION

      					SET PurchaseRealization = (PurchaseRealization - @Quantity)

      						,TotAmount = (PurchaseRealization - @Quantity) * @PricePurSatuan

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					WHERE DocNo = @DocIDRef 

      						AND MaterialID = @MaterialID

      						AND ItemStatus = '1'

      					UPDATE dbo.PTRPURCHITEM 

      					SET ItemStatus = '1'

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					WHERE DocNo = @DocIDRef

      						AND Material = @MaterialID

      					SET @IsSuccess = 'Y'

      				END

      				ELSE IF(@Quantity = @QtyPurchaseReal)

      				BEGIN

      					UPDATE dbo.PMDMAT0004

      					SET Qty = @NewQty

      						,TotalValuation = @NewTotalValuation

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					WHERE (CompanyID = @CompanyID OR @CompanyID = '')

      						AND (MaterialID = @MaterialID)

      						AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')

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

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

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

      						AND (SeqID = @MinSeqMat4)

      					UPDATE dbo.PMDMAT0001

      					SET MAP = @NewMAP

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					FROM dbo.PMDMAT0001

      					WHERE MaterialID = @MaterialID

      					UPDATE dbo.PMDMAT0004

      					SET TotalValuation = (CONVERT(DECIMAL(22,2),Qty) * @NewMAP)

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					WHERE (MaterialID = @MaterialID)

      						AND (SeqID <> @MinSeqMat4)	

      					UPDATE dbo.PTRPURCHREALIZATION

      					SET ItemStatus = '2'

      						,Warehouse = @Warehouse

      						,LogisticAreaID = @LogisticArea

      						,Storage = @Storage

      						,Bin = @Bin

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					WHERE DocNo = @DocIDRef 

      						AND MaterialID = @MaterialID

      						AND ItemStatus = '1'

      					UPDATE dbo.PTRPURCHITEM 

      					SET ItemStatus = '2'

      						,ChangeBy = @UserID

      						,ChangeDate = @now16

      					WHERE DocNo = @DocIDRef

      						AND Material = @MaterialID

      					SET @IsSuccess = 'Y'

      				END

      				ELSE 

      				BEGIN

      					SET @IsSuccess = 'OVERQTY'

      				END

      				SELECT @CekItemNotDone = COUNT(ItemStatus)

      				FROM dbo.PTRPURCHITEM 

      				WHERE DocNo = @DocIDRef AND (ItemStatus = '0' OR ItemStatus = '1')

      				IF(@CekItemNotDone = 0)

      				BEGIN

      					UPDATE dbo.PTRPURCHHEAD

      					SET ProcessStatus = 'C'

      					WHERE DocNo = @DocIDRef 

      				END

      			END

      			IF(@DocIDRef <> ''AND @TransTypeRef = 'PRD')

      			BEGIN

      				UPDATE dbo.PMDMAT0004

      				SET Qty = @NewQty

      					,TotalValuation = @NewTotalValuation

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				WHERE (CompanyID = @CompanyID OR @CompanyID = '')

      					AND (MaterialID = @MaterialID)

      					AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')

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

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

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

      					AND (SeqID = @MinSeqMat4)

      				UPDATE dbo.PMDMAT0001

      				SET MAP = @NewMAP

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				FROM dbo.PMDMAT0001

      				WHERE MaterialID = @MaterialID

      				UPDATE dbo.PMDMAT0004

      				SET TotalValuation = (CONVERT(DECIMAL(22,2),Qty) * @NewMAP)

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				WHERE (MaterialID = @MaterialID)

      					AND (SeqID <> @MinSeqMat4)

      				UPDATE PMDMATWIP

      				SET StockStatus = '0005'

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				--SELECT * FROM PMDMATWIP

      				WHERE ProdID = @DocIDRef

      					AND MaterialID = @MaterialID

      				DECLARE @SPK VARCHAR(20)

      				SELECT @SPK = SpkID

      				FROM PMDMATWIP

      				WHERE ProdID = @DocIDRef

      					AND MaterialID = @MaterialID

      				UPDATE PMDMATWIP

      				SET DocStatus = '4'

      					,StockStatus = '0005'

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				--SELECT * FROM PMDMATWIP

      				WHERE ProdID = @SPK

      					AND MaterialID = @MaterialID 

      				UPDATE PTRPRODUCTIONITEM

      				SET Status = '2'

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				--SELECT * FROM PTRPRODUCTIONITEM

      				WHERE DocNo = @DocIDRef

      					AND FlowType = 'RCPT'

      				UPDATE PTRPRODUCTIONHEAD

      				SET DocStatus = '4'

      					,ProcessStatus = 'C'

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				--SELECT * FROM PTRPRODUCTIONHEAD

      				WHERE DocNo = @SPK

      				UPDATE PTRPRODUCTIONITEM

      				SET Status = '2'

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				--SELECT * FROM PTRPRODUCTIONITEM

      				WHERE DocNo = @SPK

      					AND FlowType = 'RCPT'

      				SET @IsSuccess = 'Y'

      			END

      			ELSE

      			BEGIN

      				UPDATE dbo.PMDMAT0004

      				SET Qty = @NewQty

      					,TotalValuation = @NewTotalValuation

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				WHERE (CompanyID = @CompanyID OR @CompanyID = '')

      					AND (MaterialID = @MaterialID)

      					AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')

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

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

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

      					AND (SeqID = @MinSeqMat4)

      				UPDATE dbo.PMDMAT0001

      				SET MAP = @NewMAP

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				FROM dbo.PMDMAT0001

      				WHERE MaterialID = @MaterialID

      				UPDATE dbo.PMDMAT0004

      				SET TotalValuation = (CONVERT(DECIMAL(22,2),Qty) * @NewMAP)

      					,ChangeBy = @UserID

      					,ChangeDate = @now16

      				WHERE (MaterialID = @MaterialID)

      					AND (SeqID <> @MinSeqMat4)

      				SET @IsSuccess = 'Y'

      			END

      		END

      	END

      END

      --IF(@DocType = 'MTPR')

      --BEGIN

      --	IF(@ItemFlow = 'ISSU')

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

      --				IF(@DocIDRef <> '' AND @DocTypeRef <> '' AND @TransTypeRef <> '')

      --				BEGIN

      --					DELETE FROM PMDMAT0004

      --					--SELECT * FROM PMDMAT0004

      --					WHERE MaterialID = @MaterialID

      --						AND DocIDRef = @DocIDRef 

      --						AND DocTypeRef = @DocTypeRef 

      --						AND TransTypeRef = @TransTypeRef

      --				END

      --				ELSE

      --				BEGIN

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

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

      --						AND SeqID = @MinSeqMatExist

      --				END

      --			END

      --		END

      --	END

      --	ELSE IF(@ItemFlow = 'RCPT')

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

      --				AND SeqID = @MinSeqMatExist

      --		END

      --		ELSE IF (@IsExist = 0)

      --		BEGIN

      --			SET @IsSuccess = 'Y'	

      --			INSERT INTO dbo.PMDMAT0004

      --			SELECT DISTINCT @MaterialID	

      --							,@MaxSeqMatExist + 1

      --							,@CompanyID

      --							,@LogisticArea

      --							,@Warehouse

      --							,@Storage

      --							,@Bin

      --							,'0001'

      --							,'1' AS StockType

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

      --							,'' AS DocIDREf

      --							,'' AS DocTypeRef

      --							,'' AS TransTypeRef

      --		END

      --	END

      --END

      SELECT @IsSuccess AS IsSuccess

      DELETE FROM @TblMAT4

(1-1/1)

Project

General

Profile

MINOVA DEMO

Bug #4055 » PFILOUPDATEMDSTOCKINFO_20251210.sql