AKI-REFILL-8

USE SPEX_DB;

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

CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_5_DO_RE_FILL
(
    @PART_NO NVARCHAR(MAX),
    @PRIMARY_LOC NVARCHAR(MAX),
    @RESERVE_LOC NVARCHAR(MAX),
    @ADD_QTY INT,
    @SOR INT,
    @MAX_STOCK INT,
    @STOCK_REQ INT,
    @RESERVE_CURRENT_QTY INT,
    @SOURCE VARCHAR(MAX),
    @BT_NO VARCHAR(MAX),
    @IS_PROBLEM_SHORTAGE INT = 1,
    @ZONE_CD VARCHAR(1),
    @USERNAME VARCHAR(MAX) = ''
)
AS
BEGIN

    DECLARE @MSG_TEXT VARCHAR(MAX) = ''

    CREATE TABLE #TB_T_DATA (
        RESULT VARCHAR(MAX),
        Message VARCHAR(MAX)
    )

    DECLARE @@temp_AMANAH_PART_STOCK TABLE (
        [PART_NO] VARCHAR(15) NOT NULL,
        [PART_NAME] VARCHAR(100) NOT NULL,
        [RACK_ADDRESS_CD] VARCHAR(12) NOT NULL,
        [DOCK_CD] VARCHAR(2) NULL,
        [DOCK_PRD] VARCHAR(2) NULL,
        [DAD] DECIMAL(6,2)  NULL,
        [MAD] INT  NOT NULL,
        [ORDER_CYCLE] DECIMAL(6,2)  NOT NULL,
        [PROCUREMENT_LT] DECIMAL(6,2)  NOT NULL,
        [RECEIVING_LT] DECIMAL(6,2)  NOT NULL,
        [ALFA] DECIMAL(6,2)  NOT NULL,
        [ROP] INT  NOT NULL,
        [MIN_STOCK] INT  NOT NULL,
        [MAX_STOCK] INT  NOT NULL,
        [KANBAN_QTY] DECIMAL(6) NOT NULL,
        [PART_DIMENSION] VARCHAR(2) NULL)

    DECLARE @MaxQtyPart INT, @QtyRack INT, @RACK_ADDRESS_CD VARCHAR(22)
    DECLARE @ERR_MSG VARCHAR(MAX), @isValid bit = 1

    DECLARE @transferNo VARCHAR(MAX)
    DECLARE @counterBtTransExist INT = 0
    DECLARE @counterBtTempExist INT = 0
    DECLARE @rowCount INT = 0
    DECLARE @rowCountRefill INT = 0
    DECLARE @transferItemNo INT
    DECLARE @successQtyRefill INT = 0

    DECLARE @SET_RESERVE_LOC_CURRENT VARCHAR(MAX) = '1'
    DECLARE @SET_RESERVE_LOC_SCAN VARCHAR(MAX) = '0'

    SELECT @SET_RESERVE_LOC_CURRENT = VALUE FROM STRING_SPLIT(@RESERVE_LOC, ',') WHERE RTRIM(VALUE) <> '' ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
    SELECT @SET_RESERVE_LOC_SCAN = VALUE FROM STRING_SPLIT(@RESERVE_LOC, ',') WHERE RTRIM(VALUE) <> '' ORDER BY (SELECT 0) OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
    PRINT '@SET_RESERVE_LOC_CURRENT, @SET_RESERVE_LOC_SCAN:' + CONVERT(VARCHAR(MAX), @SET_RESERVE_LOC_CURRENT) + ', ' + CONVERT(VARCHAR(MAX), @SET_RESERVE_LOC_SCAN)

    IF (@SET_RESERVE_LOC_CURRENT = @SET_RESERVE_LOC_SCAN)
    BEGIN

        EXEC spex.usp_CutPartNo @PART_NO OUTPUT

    --  [START]   backup at 16 april 2020 by yusup
    --     IF EXISTS (SELECT TOP 1 ID FROM spex.TB_R_BINNING WHERE PART_NO = @PART_NO AND (SOURCE = 'NON-DIRECT') AND LEFT([RACK_ADDRESS], 1) = @ZONE_CD AND ISNULL(QTY, 0) > 0)
    --  [END]
        IF EXISTS (SELECT TOP 1 ID FROM spex.TB_R_BINNING WHERE PART_NO = @PART_NO AND RACK_ADDRESS = @SET_RESERVE_LOC_CURRENT AND ISNULL(QTY, 0) > 0)
        BEGIN

            --     1. Cek jika add qty lebih dari reserve qty maka error.
            IF (@ADD_QTY > @RESERVE_CURRENT_QTY)
            BEGIN

                SET @MSG_TEXT = 'Gagal, kamu menambahkan qty melebihi kapasitas qty yang tersedia di reserve lokasi. Saran coba ' +  CONVERT(VARCHAR(MAX), @RESERVE_CURRENT_QTY) + 'qty.'
                INSERT INTO #TB_T_DATA
                VALUES (
                    'Failed'
                    ,@MSG_TEXT
                    )
            END
            ELSE
            BEGIN

        --      2. Cek jika add qty lebih dari stock req untuk primary loc
                IF (@ADD_QTY > @STOCK_REQ)
                BEGIN

                    SET @MSG_TEXT = 'Gagal, kamu menambahkan qty melebihi maximal stock yang bisa disimpan di primary lokasi. Saran coba ' + CONVERT(VARCHAR(MAX), @STOCK_REQ) +' qty.' -- ID: Mohon maaf qty yang di scan melebihi stok yang bisa disimpan di primary location. Butuh 2000 qty.
                    INSERT INTO #TB_T_DATA
                    VALUES (
                        'Failed'
                        ,@MSG_TEXT
                        )
                END
                ELSE
                BEGIN

                    SELECT @counterBtTransExist = COUNT(1)
                    FROM   spex.TB_R_BUCKET_TRANSFER_H BTH
                    WHERE  BTH.BT_NO = @BT_NO
                    AND    CLOSE_BY IS NULL
                    AND    CLOSE_DT IS NULL

                    IF (@counterBtTransExist = 0)
                    BEGIN
                        SELECT @transferNo = 'TR' + (CONVERT(VARCHAR, YEAR(CURRENT_TIMESTAMP)) +
                                CONVERT(VARCHAR, RIGHT('0' + RTRIM(MONTH(CURRENT_TIMESTAMP)), 2)) +
                                CONVERT(VARCHAR, RIGHT('0' + RTRIM(DAY(CURRENT_TIMESTAMP)), 2)) +
                                RIGHT('0000' + CONVERT(VARCHAR, RTRIM(ISNULL(MAX(SUBSTRING(TRANSFER_NO, 10, 13)), 0) + 1)), 5))
                        FROM   spex.TB_R_BUCKET_TRANSFER_H
                        WHERE  DAY(CREATED_DT) = DAY(CURRENT_TIMESTAMP)
                        AND    MONTH(CREATED_DT) = MONTH(CURRENT_TIMESTAMP)
                        AND    YEAR(CREATED_DT) = YEAR(CURRENT_TIMESTAMP)
                    END
                    ELSE
                    BEGIN
                        SELECT @transferNo = TRANSFER_NO
                        FROM   spex.TB_R_BUCKET_TRANSFER_H BTH
                        WHERE  BTH.BT_NO = @BT_NO
                        AND    CLOSE_BY IS NULL
                        AND    CLOSE_DT IS NULL
                    END

                    SELECT @counterBtTempExist = COUNT(1)
                    FROM   spex.TB_T_BUCKET_TRANSFER TBT
                    WHERE  TBT.BT_NO = @BT_NO

                    IF (@counterBtTransExist = 0)
                    BEGIN
                        INSERT INTO spex.TB_R_BUCKET_TRANSFER_H
                        (TRANSFER_NO, BT_NO, OPEN_BY, OPEN_DT, CLOSE_BY, CLOSE_DT, CREATED_BY, CREATED_DT)
                        VALUES
                        (@transferNo, @BT_NO, @USERNAME, CURRENT_TIMESTAMP, NULL, NULL, @USERNAME, CURRENT_TIMESTAMP)
                    END

                    IF (@counterBtTempExist = 0)
                    BEGIN
                        INSERT INTO spex.TB_T_BUCKET_TRANSFER
                        (TRANSFER_NO, BT_NO, OPEN_BY, OPEN_DT, CREATED_BY, CREATED_DT)
                        VALUES
                        (@transferNo, @BT_NO, @USERNAME, CURRENT_TIMESTAMP, @USERNAME, CURRENT_TIMESTAMP)
                    END

                    PRINT 'IS_PROBLEM_SHORTAGE = ' + CONVERT(VARCHAR(MAX), @IS_PROBLEM_SHORTAGE)
                    IF (@IS_PROBLEM_SHORTAGE = 1)
                    BEGIN

                        IF OBJECT_ID('tempdb..#tmpReportProblem') IS NOT NULL
                            DROP TABLE #tmpReportProblem

                        CREATE TABLE #tmpReportProblem
                        (
                            ID INT IDENTITY(1,1) PRIMARY KEY,
                            PROBLEM_NO VARCHAR(MAX),
                            TRANSFER_NO VARCHAR(MAX),
                            MANIFEST_NO VARCHAR(MAX),
                            BT_NO VARCHAR(MAX)
                        )

                        INSERT INTO #tmpReportProblem
                        SELECT
                            PROBLEM_NO
                            , TRANSFER_NO
                            , MANIFEST_NO
                            , BT_NO
                                FROM [SPEX_DB].[spex].[TB_R_REPORT_PROBLEM]
                                    WHERE PART_NO = @PART_NO
                                        AND TYPE_PROBLEM = 2
    --                              [START]   backup at 17 april 2020 by yusup
    --                                     AND MANIFEST_NO IS NOT NULL
    --                             GROUP BY TRANSFER_NO, MANIFEST_NO, BT_NO
    --                             [END]

                        DECLARE @LoopCenter INT, @problemNoTmpReportProblem VARCHAR(MAX), @MaxTmpReportProblem INT, @transferNoTmpReportProblem VARCHAR(MAX), @manifestNoTmpReportProblem VARCHAR(MAX), @btNoTmpReportProblem VARCHAR(MAX), @qtyTmpReportProblem INT = 0, @refillQtyTmpReportProblem INT = 0, @needQtyTmpReportProblem INT = 0, @isQtyTmpReportProblem INT = 0
                        SELECT @LoopCenter = MIN(ID), @MaxTmpReportProblem = MAX(ID) FROM #tmpReportProblem

                        PRINT 'Check ADD_QTY, LoopCenter, MaxTmpReportProblem: ' + CONVERT(VARCHAR(MAX), @ADD_QTY) + ', ' +  CONVERT(VARCHAR(MAX), @LoopCenter) + ', ' + CONVERT(VARCHAR(MAX), @MaxTmpReportProblem)
                        WHILE(@ADD_QTY > 0 AND @LoopCenter IS NOT NULL AND @LoopCenter <= @MaxTmpReportProblem)
                        BEGIN

                            SET @MSG_TEXT = 'Update Problem Shortage \n '

                            SELECT
                                @problemNoTmpReportProblem = PROBLEM_NO
                                , @transferNoTmpReportProblem = TRANSFER_NO
                                , @manifestNoTmpReportProblem = MANIFEST_NO
                                , @btNoTmpReportProblem = BT_NO
                                    FROM #tmpReportProblem
                                        WHERE ID = @LoopCenter
                            PRINT '@transferNoTmpReportProblem, @manifestNoTmpReportProblem, @btNoTmpReportProblem, @problemNoTmpReportProblem :' + CONVERT(VARCHAR(MAX), @transferNoTmpReportProblem) + ', ' + CONVERT(VARCHAR(MAX), @manifestNoTmpReportProblem) + ', ' + CONVERT(VARCHAR(MAX), @btNoTmpReportProblem) + ', ' + CONVERT(VARCHAR(MAX), @problemNoTmpReportProblem)

                            DECLARE @foundRowReportProblem INT = 0
                            SELECT
                                @foundRowReportProblem = 1
                                , @qtyTmpReportProblem = ISNULL(QTY, 0)
                                , @refillQtyTmpReportProblem = ISNULL(REFILL_QTY, 0)
                                FROM spex.TB_R_REPORT_PROBLEM
                                    WHERE TYPE_PROBLEM = 2
                                        AND TRANSFER_NO = @transferNoTmpReportProblem
                                        AND MANIFEST_NO = @manifestNoTmpReportProblem
                                        AND BT_NO = @btNoTmpReportProblem
                                        AND PART_NO = @PART_NO
                                        AND PROBLEM_NO = @problemNoTmpReportProblem
                                        AND ISNULL(REFILL_QTY, 0) < ISNULL(QTY, 0)
                                    ORDER BY REFILL_QTY ASC
                            PRINT '@foundRowReportProblem : ' + CONVERT(VARCHAR(MAX), @foundRowReportProblem)
                            PRINT '@qtyTmpReportProblem, @refillQtyTmpReportProblem :' + CONVERT(VARCHAR(MAX), @qtyTmpReportProblem) + ', ' + CONVERT(VARCHAR(MAX), @refillQtyTmpReportProblem)

                            IF (@foundRowReportProblem > 0)
                            BEGIN

                                SET @needQtyTmpReportProblem = @qtyTmpReportProblem - @refillQtyTmpReportProblem
                                PRINT '@needQtyTmpReportProblem : ' + CONVERT(VARCHAR(MAX), @needQtyTmpReportProblem)

                                SET @isQtyTmpReportProblem = @ADD_QTY
                                IF (@ADD_QTY >= @needQtyTmpReportProblem)
                                BEGIN
                                    SET @isQtyTmpReportProblem = @needQtyTmpReportProblem
                                END
                                PRINT '@isQtyTmpReportProblem : ' + CONVERT(VARCHAR(MAX), @isQtyTmpReportProblem)

                                SET NOCOUNT ON;
                                BEGIN TRY
                                    BEGIN TRANSACTION
                                        UPDATE spex.TB_R_REPORT_PROBLEM SET REFILL_QTY = ISNULL(REFILL_QTY, 0) + @isQtyTmpReportProblem, CHANGED_BY = @USERNAME, CHANGED_DT = CURRENT_TIMESTAMP WHERE TYPE_PROBLEM = 2 AND TRANSFER_NO = @transferNoTmpReportProblem AND MANIFEST_NO = @manifestNoTmpReportProblem AND BT_NO = @btNoTmpReportProblem AND PART_NO = @PART_NO AND PROBLEM_NO = @problemNoTmpReportProblem
                                        --SELECT * FROM spex.TB_R_REPORT_PROBLEM WHERE TYPE_PROBLEM = 2 AND TRANSFER_NO = @transferNoTmpReportProblem AND MANIFEST_NO = @manifestNoTmpReportProblem AND BT_NO = @btNoTmpReportProblem AND PART_NO = @PART_NO
                                        PRINT 'Update REFILL_QTY In TB_R_REPORT_PROBLEM Success'

                                        SET @MSG_TEXT = @MSG_TEXT + 'TRANSFER_NO@' + CONVERT(VARCHAR(MAX), @transferNoTmpReportProblem) + ' MANIFEST_NO@' + CONVERT(VARCHAR(MAX), @manifestNoTmpReportProblem) + ' BT_NO@' + CONVERT(VARCHAR(MAX), @btNoTmpReportProblem) + ' PART_NO@' + CONVERT(VARCHAR(MAX), @PART_NO) + ' REFILLQTY@' + CONVERT(VARCHAR(MAX), @isQtyTmpReportProblem) + ' \n '

                                        SET @successQtyRefill = @successQtyRefill + @isQtyTmpReportProblem

                                    COMMIT TRANSACTION
                                END TRY
                                BEGIN CATCH
                                    PRINT 'Update REFILL_QTY In TB_R_REPORT_PROBLEM Error'
                                END CATCH


                                SET @ADD_QTY = @ADD_QTY - @isQtyTmpReportProblem
                                PRINT '@ADD_QTY, @LoopCenter ' + CONVERT(VARCHAR(MAX), @ADD_QTY) + ', ' + CONVERT(VARCHAR(MAX), @LoopCenter)

                            END

                            SET @LoopCenter = @LoopCenter + 1
                        END
                        PRINT '@successQtyRefill ' + CONVERT(VARCHAR(MAX), @successQtyRefill)

                    END

                    DECLARE @addQtyRefill INT = 0

                    IF (@IS_PROBLEM_SHORTAGE = 1)
                    BEGIN
                        SET @addQtyRefill = @successQtyRefill + ISNULL(@ADD_QTY, 0)
                    END
                    ELSE
                    BEGIN
                        SET @addQtyRefill = @ADD_QTY
                    END
                    PRINT '@addQtyRefill ' + CONVERT(VARCHAR(MAX), @addQtyRefill)

                    DECLARE @getCurrentQtyNonDirect INT = 0, @setRefillQtyNonDirect INT = @addQtyRefill, @getCurrentQtyDirect INT = 0, @setRefillQtyDirect INT = @addQtyRefill, @setLeftQtyRefill INT  = 0
                    IF (EXISTS(SELECT PART_NO FROM [spex].[TB_R_PART_STOCK_INFO] WHERE PART_NO = @PART_NO AND SONDR > 0))
                    BEGIN

                        SELECT @getCurrentQtyNonDirect = SONDR FROM [spex].[TB_R_PART_STOCK_INFO] WHERE PART_NO = @PART_NO
                        IF (@SOURCE = 'NON-DIRECT')
                        BEGIN
                            IF (@addQtyRefill >= @getCurrentQtyNonDirect)
                            BEGIN
                                SET @setRefillQtyNonDirect = @getCurrentQtyNonDirect
                                SET @setLeftQtyRefill = @addQtyRefill - @getCurrentQtyNonDirect
                            END
                            ELSE
                                BEGIN
                                    SET @setRefillQtyNonDirect = @addQtyRefill
                                    IF (@addQtyRefill >= @getCurrentQtyNonDirect)
                                    BEGIN
                                        SET @setRefillQtyNonDirect = @getCurrentQtyNonDirect
                                        SET @setLeftQtyRefill = @addQtyRefill - @getCurrentQtyNonDirect
                                    END
                                END
                                UPDATE [spex].[TB_R_PART_STOCK_INFO]
                                SET
                                    SOH = SOH - @setRefillQtyNonDirect
                                    ,SONDR = SONDR - @setRefillQtyNonDirect
                                    ,CHANGED_BY = @USERNAME
                                    ,CHANGED_DT = CURRENT_TIMESTAMP
                                WHERE PART_NO = @PART_NO
                                PRINT 'Update NON-DIRECT ' + CONVERT(VARCHAR(MAX), @getCurrentQtyNonDirect) + ', Refill Qty = ' + CONVERT(VARCHAR(MAX), @setRefillQtyNonDirect) + ', Left Qty = ' + CONVERT(VARCHAR(MAX), @setLeftQtyRefill)

                                SET @rowCount = 0

                                SELECT @rowCount = COUNT(1)
                                FROM   spex.TB_R_BUCKET_TRANSFER_D
                                WHERE  TRANSFER_NO = @transferNo
                                AND    PART_NO = @PART_NO

                                IF (@rowCount > 0)
                                BEGIN
                                    UPDATE spex.TB_R_BUCKET_TRANSFER_D
                                    SET    KANBAN_QTY = isnull(KANBAN_QTY, 0) + @setRefillQtyNonDirect,
                                        CHANGED_BY = @USERNAME,
                                        CHANGED_DT = CURRENT_TIMESTAMP
                                    WHERE  TRANSFER_NO = @transferNo
                                    AND    PART_NO = @PART_NO
                                    PRINT 'Update KANBAN_QTY In TB_R_BUCKET_TRANSFER_D'
                                END
                                ELSE
                                BEGIN
                                    SELECT @transferItemNo = ISNULL(MAX(TRANSFER_ITEM_NO), 0) + 1
                                    FROM   spex.TB_R_BUCKET_TRANSFER_D
                                    WHERE  TRANSFER_NO = @transferNo

                                    INSERT INTO spex.TB_R_BUCKET_TRANSFER_D
                                    (
                                        TRANSFER_NO
                                        , TRANSFER_ITEM_NO
                                        , PART_NO
                                        , KANBAN_QTY
                                        , DELIVERED_QTY
                                        , PROBLEM_FLAG
                                        , CREATED_BY
                                        , CREATED_DT
                                    )
                                    SELECT
                                        @transferNo
                                        , @transferItemNo
                                        , R_PART_STOCK_INFO.PART_NO
                                        , @setRefillQtyNonDirect
                                        , 0
                                        , NULL
                                        , @USERNAME
                                        , CURRENT_TIMESTAMP
                                    FROM [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] R_PART_STOCK_INFO
                                        INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] AS M_PART_STOCK
                                            ON R_PART_STOCK_INFO.PART_NO = M_PART_STOCK.PART_NO
                                        WHERE ISNULL(R_PART_STOCK_INFO.SOR, 0) < ISNULL(M_PART_STOCK.MAX_STOCK, 0)
                                            AND (ISNULL(R_PART_STOCK_INFO.SODR, 0) > 0 OR ISNULL(R_PART_STOCK_INFO.SONDR, 0) > 0)
                                            AND R_PART_STOCK_INFO.PART_NO = @PART_NO
                                    PRINT 'Insert New KANBAN_QTY In TB_R_BUCKET_TRANSFER_D'
                                END

                                SET @rowCountRefill = 0
                                SELECT @rowCountRefill = COUNT(1) FROM dbo.TB_R_REFILL WHERE TRANSFER_NO = @transferNo AND PART_NO = @PART_NO AND SOURCE = @SOURCE AND STATUS <> 3 AND RACK_ADDRESS = @SET_RESERVE_LOC_SCAN

                                IF(@rowCountRefill > 0)
                                BEGIN
                                    UPDATE dbo.TB_R_REFILL SET
                                        QTY = ISNULL(QTY, 0) + @addQtyRefill
                                        , CHANGED_BY = @USERNAME
                                        , CHANGED_DT = CURRENT_TIMESTAMP
                                            WHERE TRANSFER_NO = @transferNo AND PART_NO = @PART_NO AND RACK_ADDRESS = @SET_RESERVE_LOC_SCAN
                                    PRINT 'Update QTY In TB_R_REFILL ' + CONVERT(VARCHAR(MAX), @addQtyRefill)
                                END
                                ELSE
                                BEGIN
                                    INSERT INTO dbo.TB_R_REFILL
                                        (TRANSFER_NO, BT_NO, PART_NO, QTY, CREATED_BY, CREATED_DT, SOURCE, STATUS, RACK_ADDRESS)
                                        VALUES
                                        (@transferNo, @BT_NO, @PART_NO, @addQtyRefill, @USERNAME, CURRENT_TIMESTAMP, @SOURCE, 0, @SET_RESERVE_LOC_SCAN)
                                    PRINT 'Insert New QTY In TB_R_REFILL ' + CONVERT(VARCHAR(MAX), @addQtyRefill)
                                END

                                PRINT '@transferNo:' + CONVERT(VARCHAR(MAX), @transferNo)

                                UPDATE spex.TB_R_BINNING SET QTY = QTY - @addQtyRefill, CHANGED_BY = @USERNAME, CHANGED_DT = CURRENT_TIMESTAMP WHERE ID IN (SELECT TOP 1 ID FROM spex.TB_R_BINNING WHERE PART_NO = @PART_NO AND RACK_ADDRESS = @SET_RESERVE_LOC_CURRENT AND LEFT([RACK_ADDRESS], 1) = @ZONE_CD AND ISNULL(QTY, 0) > 0)
                                IF NOT EXISTS(SELECT 1 FROM spex.TB_R_STOCK_CARD WHERE PART_NO = @PART_NO AND CONVERT ( DATE, [DATE] ) = CONVERT ( DATE, CURRENT_TIMESTAMP ) AND FLAG = '0' )
                                BEGIN
                                    INSERT INTO spex.TB_R_STOCK_CARD (
                                        [DATE],
                                        [FLAG],
                                        [SEQ],
                                        [PART_NO],
                                        [PART_NAME],
                                        [SUPPLIER_CD],
                                        [SUB_SUPPLIER_CD],
                                        [SUPPLIER_PLANT],
                                        [SUB_SUPPLIER_PLANT],
                                        [QTY],
                                        [BEGIN_STOCK],
                                        [END_STOCK],
                                        [REF_FILE],
                                        [CREATED_BY],
                                        [CREATED_DT],
                                        [CHANGED_BY],
                                        [CHANGED_DT]
                                    ) SELECT
                                        CURRENT_TIMESTAMP AS [DATE],
                                        0,
                                        ( SELECT ISNULL( MAX ( SEQ ), 0 ) + 1 AS SEQ  FROM spex.TB_R_STOCK_CARD A  WHERE CONVERT ( DATE, A.DATE ) = CONVERT ( DATE, CURRENT_TIMESTAMP ) ),
                                        @PART_NO,
                                        P.PART_NAME,
                                        P.SUPPLIER_CD,
                                        P.SUB_SUPPLIER_CD,
                                        P.SUPPLIER_PLANT,
                                        P.SUB_SUPPLIER_PLANT,
                                        @addQtyRefill,
                                        SOH,
                                        SOH + @addQtyRefill,
                                        NULL,
                                        @USERNAME,
                                        CURRENT_TIMESTAMP,
                                        @addQtyRefill,
                                        CURRENT_TIMESTAMP
                                    FROM spex.TB_M_PACKING_PART P
                                    JOIN spex.TB_R_PART_STOCK_INFO S ON P.PART_NO = S.PART_NO
                                    WHERE P.PART_NO = @PART_NO
                                END
                                ELSE
                                BEGIN
                                    UPDATE A
                                    SET A.QTY = A.QTY + @addQtyRefill,
                                    END_STOCK = B.SOH + @addQtyRefill,
                                    CHANGED_BY = @USERNAME,
                                    CHANGED_DT = CURRENT_TIMESTAMP
                                    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 = @PART_NO
                                    AND CONVERT ( DATE, [DATE] ) = CONVERT ( DATE, CURRENT_TIMESTAMP )
                                    AND FLAG = 0
                                END

                                SET @MSG_TEXT = @MSG_TEXT + ' | Remaining qty refilled @' +  CONVERT(VARCHAR(MAX), @ADD_QTY)
                                INSERT INTO #TB_T_DATA
                                VALUES ('Success', @MSG_TEXT)

                            END
                    END

                END

            END

        END
        ELSE
        BEGIN
            SET @MSG_TEXT = 'Part No ' + @PART_NO + ' on Reserve Location ' + @SET_RESERVE_LOC_CURRENT + ' not found or All done'
            INSERT INTO #TB_T_DATA
            VALUES ('Failed', @MSG_TEXT)
        END

    END
    ELSE
    BEGIN
        SET @MSG_TEXT = 'INVALID RESERVE ADDRESS!'
        INSERT INTO #TB_T_DATA
        VALUES ('Failed', @MSG_TEXT)
    END

    SELECT RESULT
        ,Message
    FROM #TB_T_DATA

END
GO

-- https://pastebin.com/Hnr7T7G4
-- EXECUTE spex.SP_AMANAH_RE_FILL_5_DO_RE_FILL '851430D030', 'S0104-3-B', 'S-UPARMAN-001,S-UPARMAN-001', 2, 1006, 2020, 994, 3, 'NON-DIRECT', 'REBI-S-240220-001', 1, 'S', 'AMANAH.Yusup';