I work on sql server 2012 i face issue i can't handle invenotry tranfer order from inventory to another
inventory so How to handle that on bussiness
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 postive
are including new table for transfer is nesecary 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