Dear All,
As following code condition statement show that transfer data from Location A to Location B. May i know how if to update the exist data in Location B ?
Example,from Location A Qty is 10 , transfer Qty to Location B is 5, then Location A is 5.
So,How if transfer again from location A qty 2 to Location B. then update the location b qty.
USE [CIMProRPT01]
GO
/****** Object: StoredProcedure [dbo].[MMSLocTrans_InsertOrUpdate] Script Date: 01/03/2014 13:46:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MMSLocTrans_InsertOrUpdate]
@INV_TRANS_ID VARCHAR(40)
,@INV_ID VARCHAR(40)
,@INV_LOCATION VARCHAR(40)
,@INV_QTY FLOAT
,@INV_TRANS_REFNO VARCHAR(40)
,@INV_TRANS_REMARK VARCHAR(255)
,@INV_REASON_ID VARCHAR(40)
,@INV_REASON_REMARK VARCHAR(255)
,@INV_CREATE_DATE DATETIME
,@INV_CREATE_USER VARCHAR (255)
--,@INV_VENDORS VARCHAR (40)
,@INV_FROMLOC VARCHAR (40)
,@RecordFound INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM OTH_INV_QTY_LOC WHERE INV_ID = @INV_ID and INV_LOCATION = @INV_LOCATION)
BEGIN
UPDATE OTH_INV_QTY_LOC SET [INV_ID] = @INV_ID,INV_LOCATION = @INV_LOCATION , INV_QTY = INV_QTY - @INV_QTY WHERE INV_ID = @INV_ID AND INV_LOCATION = @INV_LOCATION
INSERT INTO OTH_INV_QTY_LOC (INV_ID,INV_LOCATION,INV_QTY)Values(@INV_ID,@INV_FROMLOC,@INV_QTY)
BEGIN
SELECT @RecordFound = 1
END
END
ELSE
IF EXISTS(SELECT * FROM OTH_INV_QTY_LOC WHERE INV_ID = @INV_ID and INV_LOCATION = @INV_FROMLOC)
BEGIN
UPDATE OTH_INV_QTY_LOC SET [INV_ID] = @INV_ID,INV_LOCATION = @INV_LOCATION , INV_QTY = INV_QTY + @INV_QTY WHERE INV_ID = @INV_ID AND INV_LOCATION =@INV_FROMLOC
BEGIN
SELECT @RecordFound = 1
END
END
else
BEGIN
INSERT INTO OTH_INV_TRANSACTION (INV_TRANS_ID,INV_ID,INV_TRANS_LOCATION,INV_TRANS_QTY,INV_TRANS_REFNO,INV_TRANS_REMARK,INV_REASON_ID,INV_REASON_REMARK,INV_CREATE_DATE,INV_CREATE_USER,INV_VENDORS)VALUES (@INV_TRANS_ID,@INV_ID,@INV_LOCATION,@INV_QTY,@INV_TRANS_REFNO,@INV_TRANS_REMARK,@INV_REASON_ID,@INV_REASON_REMARK,@INV_CREATE_DATE,@INV_CREATE_USER,@INV_FROMLOC)
SELECT @RecordFound = 0
END
END
