Inquery

USE SPEX_DB;

IF EXISTS (
SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'spex'
    AND SPECIFIC_NAME = N'SP_AMANAH_RE_FILL_ROLLBACK'
    AND ROUTINE_TYPE = N'PROCEDURE'
)
DROP PROCEDURE spex.SP_AMANAH_RE_FILL_ROLLBACK
GO

CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_ROLLBACK
(
    @zoneCd VARCHAR(MAX),
    @partNo VARCHAR(MAX),
    @btNo VARCHAR(MAX),
    @reserveLoc VARCHAR(MAX),
    @username VARCHAR(MAX)
)
AS
    DECLARE @qty INT
    DECLARE @transferNo VARCHAR(MAX)
BEGIN

    SET @qty = 0;

    -- Execute TB_R_REFILL
    SELECT @qty = QTY FROM dbo.TB_R_REFILL WHERE PART_NO=@partNo AND BT_NO=@btNo AND (CREATED_BY=@username OR CHANGED_BY=@username) AND RACK_ADDRESS=@reserveLoc;
    DELETE FROM dbo.TB_R_REFILL WHERE PART_NO=@partNo AND BT_NO=@btNo AND (CREATED_BY=@username OR CHANGED_BY=@username) AND RACK_ADDRESS=@reserveLoc;
    PRINT '@qty:' + CONVERT(VARCHAR(MAX), @qty);

    -- Execute TB_R_PART_STOCK_INFO
    -- SELECT * FROM spex.TB_R_PART_STOCK_INFO WHERE PART_NO=@partNo
    UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR = SONDR + @qty WHERE PART_NO=@partNo
    UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = SOH + @qty WHERE PART_NO=@partNo

    -- Execute TB_R_BINNING
    -- SELECT * FROM spex.TB_R_BINNING WHERE PART_NO=@partNo AND RACK_ADDRESS=@reserveLoc AND LEFT([RACK_ADDRESS], 1)=@zoneCd
    UPDATE spex.TB_R_BINNING SET QTY = QTY + @qty WHERE PART_NO=@partNo AND RACK_ADDRESS=@reserveLoc AND LEFT([RACK_ADDRESS], 1)=@zoneCd

    -- Execute TB_T_BUCKET_TRANSFER
    -- SELECT * FROM spex.TB_T_BUCKET_TRANSFER WHERE BT_NO=@btNo
    DELETE FROM spex.TB_T_BUCKET_TRANSFER WHERE BT_NO=@btNo

    -- Execute TB_R_REPORT_PROBLEM
    -- SELECT * FROM spex.TB_R_REPORT_PROBLEM WHERE PART_NO=@partNo AND TYPE_PROBLEM=2
    UPDATE spex.TB_R_REPORT_PROBLEM SET REFILL_QTY = NULL WHERE PART_NO=@partNo AND TYPE_PROBLEM=2

    -- Execute TB_R_BUCKET_TRANSFER_H
    SELECT @transferNo=TRANSFER_NO FROM spex.TB_R_BUCKET_TRANSFER_H WHERE BT_NO=@btNo AND OPEN_BY=@username
    DELETE FROM spex.TB_R_BUCKET_TRANSFER_H WHERE BT_NO=@btNo AND OPEN_BY=@username
    PRINT '@transferNo:' + CONVERT(VARCHAR(MAX), @transferNo);

    -- Execute TB_R_BUCKET_TRANSFER_D
    -- SELECT * FROM spex.TB_R_BUCKET_TRANSFER_D WHERE TRANSFER_NO=@transferNo AND PART_NO=@partNo
    DELETE FROM spex.TB_R_BUCKET_TRANSFER_D WHERE TRANSFER_NO=@transferNo AND PART_NO=@partNo

    -- Execute TB_R_BUCKET_TRANSFER_D
    -- SELECT * FROM spex.TB_R_STOCK_CARD WHERE PART_NO=@partNo AND FLAG='0'
    UPDATE A SET A.QTY = A.QTY - @qty, END_STOCK = B.SOH - @qty
    FROM spex.TB_R_STOCK_CARD A
    JOIN spex.TB_R_PART_STOCK_INFO B ON A.PART_NO = B.PART_NO
    WHERE A.PART_NO=@partNo
    AND CONVERT ( DATE, [DATE] ) = CONVERT ( DATE, CURRENT_TIMESTAMP )
    AND A.FLAG='0'

    SELECT  1

    END
GO

-- EXECUTE spex.SP_AMANAH_RE_FILL_ROLLBACK 'M','16572BZ270', 'REBI-M-0106200001', 'M-UTAN-001', 'AMANAH.Yusup'