ahmed elbarbary

ahmed elbarbary

  • 936
  • 1.5k
  • 104.6k

How to make transfer quantity from inventory to another inventory ?

Jul 3 2021 9:33 PM

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