Anam Farooque

CREATE PROCEDURE [dbo].[sp_ProcessBranchStockTransfer]
    @SourceBranchId INT,
    @TargetBranchId INT,
    @ProductId INT,
    @TransferQty DECIMAL(18,2),
    @OperatorUser NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- Check source stock balance
        IF (SELECT CurrentStock FROM BranchInventory 
            WHERE BranchId = @SourceBranchId AND ProductId = @ProductId) < @TransferQty
        BEGIN
            RAISERROR('Insufficient branch stock for transfer.', 16, 1);
        END

        -- Deduct from Source & Credit Target Branch
        UPDATE BranchInventory 
        SET CurrentStock = CurrentStock - @TransferQty,
            ModifiedAt = GETUTCDATE(), ModifiedBy = @OperatorUser
        WHERE BranchId = @SourceBranchId AND ProductId = @ProductId;

        UPDATE BranchInventory 
        SET CurrentStock = CurrentStock + @TransferQty,
            ModifiedAt = GETUTCDATE(), ModifiedBy = @OperatorUser
        WHERE BranchId = @TargetBranchId AND ProductId = @ProductId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;