How to make transfer quantity from inventory to another inventory?

Transact-SQL
, ,
1

work on sql server 2012 i face issue i can't handle inventory transfer order from inventory to another

inventory so How to handle that on business

CREATE TABLE [dbo].[ConsumeHeader](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ConsumeNo] [varchar](25) NOT NULL,
    [BranchID] [int] NOT NULL,
    [ConsumeDate] [datetime] NOT NULL,
    [TransactionTypeID] [int] NOT NULL,
    [PostingDate] [date] NULL,
    [OrderDate] [date] NULL,
    [EmployeeID] [int] NOT NULL,
 CONSTRAINT [ConsumeNo] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[PurchaseHeader](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PurchaseNoText] [varchar](30) NULL,
    [BranchID] [int] NOT NULL,
    [transactionTypeID] [int] NOT NULL,
    [PostingDate] [date] NULL,
    [OrderDate] [date] NULL,
    [VendorID] [int] NOT NULL,
    [Status] [int] NULL,

 CONSTRAINT [PurchaseNo] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Invenroty](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TransactionNo] [int] NOT NULL,
    [InventoryLocID] [int] NOT NULL,
    [TransactionTypeID] [int] NOT NULL,
    [InvoiceID] [int] NULL,
    [Qty] [decimal](18, 3) NOT NULL,
    [UnitPrice] [decimal](18, 3) NULL,
    [Total] [decimal](18, 3) NOT NULL,
    [ItemID] [int] NOT NULL,
    [UnitOfCodeID] [int] NOT NULL,
    [PostingDate] [date] NULL,
    [ToInventory] [bit] NOT NULL,
 CONSTRAINT [InventorySerialID] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

as above when make sales then it store invoice no on inventory as negative

on inventory invoice no

when make purchase then invoice no on inventory will be postive

on inventory and purcehase invoice no will be invoice no on inventory

so How to handle transfer quantity from inventory location to another location

meaning transfer will be minus or positive

are including new table for transfer is necessary or not

relation below

sales header id - invoice no inventory

purchase header id - invoice no inventory

when transfer quantity from location to another what i add or modify on diagram below

image
image729×526 8.38 KB

2

I think you will need to create another TransactionTypeID, and use that to insert into your Inventory table. InvoiceID is a NULL value so you don't need a InvoiceID. There is no need for a extra table or change in the Diagram based on the information you provided.

If there are extra fields needed to make a transfer you will need to create an extra table, InventoryTransfers, and link them also by InvoiceID as a transfer needs to be done by a certain Employee on a certain date.

3

can you please show me
table i will adding
and how to handle transfer
invoice id is invoice no

and i use this field to store Purchase ID AND sales or consume ID

but my issue How to handle transfer from inventory to another inventory

this is my question

4

I cannot help you much as you didn't provide any information/data about the InventoryTransfers so this next answer is only to get an idea.

Say an InventoryTransfer is a qty of items from InventoryLocID to another InventoryLocID.

INSERT INTO Inventory(TransactionNo, InventoryLocID, ItemId, Qty)
SELECT
TransactionNo, FromInventoryLocID, ItemId, Qty * -1
FROM InventoryTransfer
UNION
SELECT
TransactionNo, ToInventoryLocID, ItemId, Qty
FROM InventoryTransfer