Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
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 business



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 purchase 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

What I have tried:

SQL
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]
Posted
Updated 4-Jul-21 5:45am
Comments
ahmed_sa 4-Jul-21 2:36am    
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

1 solution

A "transfer" creates at least 2 transactions; a credit (to inventory) to the sender; and a debit to the receiver.

To balance each end, they would also need to debit and credit "total transfers" respectively (at each location).
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900