How to handle transfer quantity from location to another ?

Question

Post reply

How to handle transfer quantity from location to another ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

July 3, 2021 at 9:54 pm

#3903215

I 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 positive

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

This topic was modified 3 years, 4 months ago by ahmed_elbarbary.2010. Reason: more details

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

First, you keep talking about "invoice no" but you have nothing in any of the tables that is an "invoice no". Do you mean "InvoiceID" in the [Inventory] table?

And, surely, you not talking about making the InvoiceID negative for sales and positive for purchases are you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

yes 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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

ahmed_elbarbary.2010 wrote:

yes 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

You question seems to revolve around whether or not the InvoiceID is postitive or negative. If that's true, then I don't want to help you accomplish it because it's that bad an idea.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

This looks extremely similar to a garment stock control system I spent a couple of years working on, some time ago. It makes more sense if the “Inventory” table is named “stock movements” or “stock transactions”.

To move stock from one location to another, you write two new rows to the Inventory table: one for the source inventory location, the other for the destination inventory location. Before you even think about doing this, discover whether or not the transfer is broken up into smaller steps. It could involve many more than two:

Pick from source location

Box up for transit

Transit to destination location

Unpack & stockcheck at destination location

Place in correct stock bin at destination location

Each of these steps is distinguished by the TransactionTypeID, the code for the activity which possesses the stock at this point. It would look something like this:

Insert 1 row with negative quantity for the ItemID / InventoryLocID, TransactionTypeID = reserved for transfer

Insert 1 row with positive quantity for the ItemID / InventoryLocID, TransactionTypeID = in picking

Insert 1 row with negative quantity for the ItemID / InventoryLocID, TransactionTypeID = in picking

Insert 1 row with positive quantity for the ItemID / InventoryLocID, TransactionTypeID = in packing

Insert 1 row with negative quantity for the ItemID / InventoryLocID, TransactionTypeID = in packing

Insert 1 row with positive quantity for the ItemID / InventoryLocID, TransactionTypeID = in transit

Insert 1 row with negative quantity for the ItemID / InventoryLocID, TransactionTypeID = in transit

Insert 1 row with positive quantity for the ItemID / new InventoryLocID, TransactionTypeID = in stockcheck

Insert 1 row with negative quantity for the ItemID / new InventoryLocID, TransactionTypeID = in stockcheck

Insert 1 row with positive quantity for the ItemID / new InventoryLocID, TransactionTypeID = in stock

This system is particularly effective at ferreting out where “shrinkage” might be taking place.

This reply was modified 3 years, 4 months ago by ChrisM@Work.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

Ah. Yep. I can see change a quantity to a negative or positive... I read it that the OP wanted to change the ID "number" to negative or positive, which would be a terrible idea.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)