How to handle transfer quantity from location to another ?

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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 2 months, 2 weeks 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

  • 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)
    Intro to Tally Tables and Functions

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

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