Inventory Management Database

[Thinh]

how do i handle the order of new parts

partNumber Table:
PartID, Primary Key, Autonumber
TypeID, Int
PartNumber, Text
SafetyStock, Int
LifeCycle, Int

Inventory Table:
InventoryID, Primary Key, AutoNumber
PartID, FK to partNumber
SerialNr, Text
Life, Int
needOrder, Yes/No

Order Table:
OrderID, Primary Key, Autonumber
OrderNumber: Text
OrderDate: Date/Time

OrderItem Table:
OrderItemID, Primary Key, AutoNumber
OrderID: FK to Order Table
PartID, Int
InventoryID, int
Recieved, YES/NO
RecievedDate Date/Time


Requirements:1 if quantity of a part number is equal to safety Stock trigger new order
Requirements:2 if the life of partnumber reach 33%(1-(life/LifeCycle))<=0.33 then trigger new order

I have generate a query that give me all the ones that needs to reorder but there is no way for me to tell what has been order or not. the problem that i face is that life is on individual level while quantity is on aggregate level. with life triggering i do have a partID and inventoryID but when the Quantity trigger i only have partID. if the quantity is greater or equal to safety stock order one. if quantity is less than safety stock order safety stock. i can create new table to tell me what to order but i cannot tell the query the item has been ordered since partnumber is not unique in order table.

I run out of idea any suggestion or recommendation is more than welcome

thanks in advance guys

 

[gemma-the-husky]

not sure how commecial systems do this

one way i might consider is to add notional "stock on order" transaction, linked to the order - so you can pick it up when the order is received

this will take the stock back over the min stock qty, and enable you to know which order relates to the transaction - all you need to do is make sure you dont pick this notional stock for processing/sale etc

 

[The_Doc_Man]

I might do this as a UNION query.

For any given part, your quantity on hand is always and only the sum of all ADD and REMOVE transactions. (Plus SHRINKAGE and RESTOCK, if you allow for those.)

But for ordering purposes, your quantity is the sum of those transactions plus the sum of pending orders and the sum of pending sales (committed but not yet shipped). So you would form this sum by querying a UNION of the transactions table and the orders table and the pending table. Maybe.

I would not do what you are doing the way you are doing it.

To me, inventory is the sum of these things:

tblTransAct
XACTID, PK, transaction identifier (if you need separate identifiers for this - you might not)
PartID, For. Key to part table
XACTType, a code for SHIPMENT ADD, SALES REMOVE, INVENTORY SHRINK, RETURN RESTOCK - plus a possible INVENTORY CHECKPOINT that you use to archive older entries.
XACTNum, the number of items in the transaction line item
XACTSense, the direction of the operation, +1 for add, -1 for remove.

Then your inventory on-hand is given by the sum in this query:

SELECT [PartID], Sum( [XACTNum] * [XACTSense] ) From tblTransAct Group By [PartID];

BUT to figure out what is on order, you must add in a couple of factors.

Let's say you have an order table with a part ID and a quantity, and a committed sales table with a part ID. Build a query,

SELECT [PartID], [XACTNum] as Qty, [XACTSense] as Sense from tblTransact UNION
SELECT [PartID], [OrdAmt] as Qty, +1 as Sense from tblOrders UNION
SELECT [PartID], [CmtdAmt] as Qty, -1 as Sense from tblCommittedSales;

Then do a sum of the UNION query grouped by part ID summing (Qty * Sense). THAT gives you the numbers for which you want to run your orders.

Now, if that makes too many records so that the summation is slow, you need to archive your tables now and then. Also, good keys based on PartID will make that work better. Finally, if you need to further qualify the order table entries, you can add a date selection criterion in the UNION query to filter out orders in the past or too far into the future. Lots of ways to approach that, but the idea is that "stock quantity" has multiple meanings for the model you have described. So you need to take the various meanings into account, each where applicable.

This is an overview and not to be taken as even slightly intended as a complete solution. Treat it as a signpost at best.

 

[Thinh]

Thanks for the suggestion and feedback. I figure away around it.

 

[Thinh]

What i figured was that all activity is based on two actions. one is if the user scrap the parts or the life of it actually goes down to 33%. these event occurred both at the individual level. so i just wrote one function to check the total count of the part number when the user click on scrap or when user input a number that would bring life down to 33% life. This function would then append an entry into a demand table with the inventory ID and the reason for this demand. This way you know what inventory item trigger the new order and the reason for it. this not perfect solutions as but it will do for my situation. If it not cleared enough ask me more and i might provide a sample as soon as i strip out all the important data

 

[The_Doc_Man]

As always, it is the original questioner who must decide the optimum solution for his/her individual problem. We can suggest what we believe to be right, but in the end analysis, it must be whatever works for you, not for us.

It is good, though, that you recognize the shortcomings of the problem yourself. That will eventually drive you to revisit the problem once you are out of firefighting mode - if you ever are. (And trust me, I know what that can be like.)

 

[Thinh]

i finally perfected the design by using SQL logic and application logic. the inventory and and ordering process is completed. The thing i ran into trouble now is the transition from demand table into order and then from order back into inventory again. the demand can be satisfied in two ways, one is by order brand new part which is the easy way. the other is to send part out to re machine into a different parts. this is the harder part as it require some part to change to a different parts based on the replace part. Sorry if i didnt make it clear enought. Demand for part A, we have a part C in scrap that could be send out to be re machine into part A. When part C comes back into inventory it have to be change into Part A. Hope that makes it a little bit more clearer.

Demand:
demandID, AutoNumber Primary Key
PartID, FK to partNumber
DateIssue, Date/Time
OrderID, Number FK to Order Table
Recieved, Yes/NO
RecievedDate, Date/Time

Order:
OrderID, AutoNumber Primary Key
OrderNumber, Text
OrderDate, Date/Time
Approved, YES/NO

Work:
WorkID, Autonumber primaryKey
DemandID, FK to Demand Table
inventoryID, FK to Inventory Table

that what i got so far. any suggestion would be great.

 

[gemma-the-husky]

you would need a bit of paper presumably to manage the reworking

so just book out the old stock under that reference, and book in the new stock under the new reference, based on the bit of paper - add a new transaction type if necessary