How to make transfer quantity from inventory to another inventory ?

Your issue is a very common one in inventory management systems. Based on your schema, the current design handles incoming (Purchase) and outgoing (Consume/Sales) inventory movements, but it doesn't have a clear, distinct process for internal transfers between locations.

The core problem is that a transfer is a unique transaction type that needs to be handled as both a debit and a credit to different locations. Trying to fit it into your existing Purchase or Consume logic will lead to data integrity issues.

The solution is to add a new table to specifically handle transfers.

Proposed Solution

Here is a breakdown of the new table and how it would work within your existing schema.

1. Add a TransferHeader Table: This is the most critical change. A new table is necessary to capture the unique details of a transfer. It should reference both the source and destination locations.

CREATE TABLE [dbo].[TransferHeader](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TransferNo] [varchar](25) NOT NULL,
    [TransferDate] [datetime] NOT NULL,
    [FromInventoryLocID] [int] NOT NULL,
    [ToInventoryLocID] [int] NOT NULL,
    [EmployeeID] [int] NOT NULL,
CONSTRAINT [TransferID] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)
) ON [PRIMARY]

Update the Inventory Table: You need to modify the Inventory table to accommodate this new transaction type. When a transfer occurs, you will create two entries in this table for the same item.

• Outgoing Entry: A negative Qty entry with a TransactionTypeID for 'Transfer Out' and a reference to the TransferHeader's ID in the InvoiceID column. This decrements the inventory at the source location.

• Incoming Entry: A positive Qty entry with a TransactionTypeID for 'Transfer In' and a reference to the same TransferHeader's ID in the InvoiceID column. This increments the inventory at the destination location.

Let’s design this properly so your Inventory Transfers work cleanly and don’t break reporting. Right now you already separated PurchaseHeader and ConsumeHeader, so the best design is to treat Transfers as their own transaction type with a header and detail, just like you do for sales and purchases.

Header Table for Transfers

Keeps metadata (who, when, from/to location, status, etc.).

CREATE TABLE [dbo].[TransferHeader](
    [ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [TransferNo] VARCHAR(25) NOT NULL UNIQUE,
    [FromLocationID] INT NOT NULL,
    [ToLocationID] INT NOT NULL,
    [TransferDate] DATETIME NOT NULL DEFAULT(GETDATE()),
    [PostingDate] DATE NULL,
    [EmployeeID] INT NOT NULL,
    [Status] INT NOT NULL DEFAULT(0) -- 0 = Draft, 1 = Posted, etc.
);

Detail Table for Transfer Items

Keeps line-level details of items being moved.

CREATE TABLE [dbo].[TransferDetail](
    [ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [TransferHeaderID] INT NOT NULL,
    [ItemID] INT NOT NULL,
    [UnitOfCodeID] INT NOT NULL,
    [Quantity] DECIMAL(18,3) NOT NULL,
    [UnitPrice] DECIMAL(18,3) NULL,
    [Total] AS ([Quantity] * ISNULL([UnitPrice],0)),
    FOREIGN KEY (TransferHeaderID) REFERENCES TransferHeader(ID)
);

Inventory Posting (Double-Entry Style)

When transfer is posted, create two records in Inventory:

• Outflow from FromLocationID (-Qty)

• Inflow into ToLocationID (+Qty)

-- Suppose TransferID = 1001, moving 50 pcs of Item=2001 from Loc=1 ? Loc=2
INSERT INTO Inventory (TransactionNo, InventoryLocID, TransactionTypeID, Qty, ItemID, UnitOfCodeID, PostingDate, ToInventory)
SELECT 
    1001, -- TransactionNo = TransferHeader.ID
    TH.FromLocationID,
    3,    -- TransactionTypeID = Transfer
    -TD.Quantity,
    TD.ItemID,
    TD.UnitOfCodeID,
    TH.PostingDate,
    0
FROM TransferHeader TH
JOIN TransferDetail TD ON TH.ID = TD.TransferHeaderID
WHERE TH.ID = 1001;

INSERT INTO Inventory (TransactionNo, InventoryLocID, TransactionTypeID, Qty, ItemID, UnitOfCodeID, PostingDate, ToInventory)
SELECT 
    1001,
    TH.ToLocationID,
    3,    -- Transfer
    TD.Quantity,
    TD.ItemID,
    TD.UnitOfCodeID,
    TH.PostingDate,
    1
FROM TransferHeader TH
JOIN TransferDetail TD ON TH.ID = TD.TransferHeaderID
WHERE TH.ID = 1001;

Why This Is Better

• Separation of concerns → Purchases, Sales, Transfers each have their own header/detail.

• Auditability → You can see exactly who moved stock, when, and why.

• Scalability → You can later add approval workflows, transfer costs, or inter-branch pricing.

• Consistency → Reporting is straightforward (same pattern as sales & purchases).

High-Level Flow

1. Create TransferHeader + TransferDetail (draft).

2. Approve/post transfer → write two entries into Inventory.

3. Reports/stock balance query → always sum Inventory.Qty per location.

Right now your setup handles Purchases (inflow) and Sales (outflow) correctly by writing entries into [Inventory]. The problem comes with transfer between locations – because that’s neither purchase nor sale, it’s just movement inside the organization.

Let’s break it down.

 Current Tables Recap

• PurchaseHeader → inflow (positive stock, invoice no stored in [Inventory])

• ConsumeHeader (Sales) → outflow (negative stock, invoice no stored in [Inventory])

• Inventory → transaction table for all stock movements (positive/negative with location)

So far, good.

What happens in a Transfer?

When you transfer Item X (10 qty) from Location A to Location B:

• At Location A → stock should decrease (-10)

• At Location B → stock should increase (+10)

This means two entries in [Inventory] are required.

Do you need a new table for transfer?

You have two choices:

 Option 1: Use existing [Inventory] only

• Add a TransactionTypeID for "Transfer-Out" and "Transfer-In".

• Use the same [Inventory] table to record both sides of transfer.

• Keep a reference between the two records (so you know which out belongs to which in).
  Example: add a column TransferBatchID or ReferenceID in [Inventory].

 Pros:

• No new tables, simple design.

• Reuses same stock movement logic.

 Cons:

• Harder to query which transfer is one logical transaction unless you add a reference ID.

 

Option 2: Create a new TransferHeader + (optional TransferDetail)

CREATE TABLE TransferHeader (
    ID INT IDENTITY PRIMARY KEY,
    TransferNo VARCHAR(25) NOT NULL,
    FromLocationID INT NOT NULL,
    ToLocationID INT NOT NULL,
    TransferDate DATETIME NOT NULL,
    EmployeeID INT NOT NULL,
    Status INT NULL
);

CREATE TABLE TransferDetail (
    ID INT IDENTITY PRIMARY KEY,
    TransferHeaderID INT NOT NULL FOREIGN KEY REFERENCES TransferHeader(ID),
    ItemID INT NOT NULL,
    Qty DECIMAL(18,3) NOT NULL,
    UnitPrice DECIMAL(18,3) NULL
);

When posting a transfer:

• Insert into TransferHeader/Detail.

• Then create two entries in [Inventory]:

  • Transfer-Out (negative, FromLocation)

  • Transfer-In (positive, ToLocation)

 Pros:

• Business-friendly (audit trail of transfers).

• Easier to report "who transferred what where".

• Keeps the Inventory table clean (only movements).

 Cons:

• Slightly more tables & joins.

Suggested Diagram Update

• Keep PurchaseHeader ↔ Inventory (positive).

• Keep ConsumeHeader ↔ Inventory (negative).

• Add TransferHeader ↔ Inventory (both negative & positive).

So [Inventory] remains the universal transaction ledger, and Headers (Purchase/Sales/Transfer) are just sources.