Unanswered: Hopefully SQL can handle processing alternative part numbers?
I'm building a small-scale MRP system for an electronics manufacturing business. There are assemblies that are built, and parts that go into those assemblies. The individual component parts that go into each assembly have their own component part numbers, and since many manufacturers make those basic components, often times alternative part numbers (and quantities) exist in the inventory system for a given component part.
Fortunately, we assign internal part numbers, so there's a one-to-many relationship between the internal part numbers that we use to build assemblies by and the actual manufacturer's part numbers that we've purchased to maintain inventory for that internal part number.
So, we could have more than one MfgPN for any given internal PN (for example):
So, in other words, either of those MfgPN's is completely appropriate to use wherever the assembly calls out RES-000293-R.
And our operators will use up existing, opened reels of parts before mounting new reels. So, in this example, we'll use up the stock of 150 CRCW0603330RJNEA before mounting the unopened reel of RM06J301CT-ND-TR.
Parts don't have expiration dates, so we use up small quantities before moving on to larger ones.
The dillema this creates is in creating SQL code to decrement inventory appropriately. Consider building 500 assemblies where this part is used once per assembly:
In reality, we'd use up the 150 first, then use 350 of the RM06J301CT-ND-TR.
So, after running, I'd have this:
From a SQL standpoint, I have: Inventory table has MfgPN and Qty. Parts table has IntPN and MfgPN. Assemblies table has data on each assembly location, including IntPN.
I created a stored query (used as a view) to provide IntPN, count(*) for any given assembly, so I know how many IntPN's I need to decrement from inventory.
How could I then process that against [functionally] potentially multiple instances of that IntPN in inventory, so that it subtracts from the smallest inventory item first, then the next smallest, and so on until the full decrement quantity has been reached?
I'd like to stay within SQL if possible to do this in a query.
I think what I'm going to do is create two stored queries... --- One to identify the min Qty and min MfgPN (since I at least need it returned but need the 'group' by to work) where Qty>0 ---The second to identify the same thing, where Qty <0.
I can then process my decrementing against the first view, ensuring I have only one CustPN, and it's the one that we have the lowest Qty of all CustPN's.
If that particular MfgPN doesn't have enough parts, it'll turn into a negative number in my inventory.
I can then repeatedly process querying the second view to find negative numbers, and using those numbers (multiplied by -1) to process against the first view again, iterating until I no longer have any negative numbers in my Inventory table. And I'll probably need a temporary table to track the numbers I find as negatives so I can zero just those after each iteration...
Sound reasonable? It keeps me in queryland, other than that I'll need some VBA to control the looping.
Do you need to know for sure whether the assembly includes parts from one manufacturer or the other? In other words, if we're making 500 assemblies, do we have to know which have CRCW0603330RJNEA and which have RM06J301CT-ND-TR?
If not, your solution will work, although it is quite combersome. It would probably be better to drop the Manufacturer Part Number from the inventory table and add another table that gives all the possible manufacturer part numbers for this component.
Well, the logic is "if there are approved alternate parts available, any of them can be used in the assembly they've been approved for."
So the answer to your question is no.
However, for many reasons (including traceability, although obviously not on a per-assembly level) inventory has to be managed by manufacturer part number, because it actually IS what is in inventory.
For simplification purposes, I said "internal part number". It's really "customer part number". We're a contract manufacturer, so this database handles OUR inventory, for MANY customers - in some cases, we have a customer part number, and multiple MfgPN possibilities, in others, we have a MfgPN and that is also the Customer PN. And in many cases, we have parts that are common (if not shared) among projects and customers. I'm also worried about making a database change that has unknown implications on the flexibility of the system in the future for satisfying unknown future clients/situations - I'm trying to keep the system mirroring reality as closely as possible on the back end... I can make conveniences happen on the front-end, is my theory anyway.
But believe me - if the system was really as simplified of a scenario as I described, I'd be jumping all over getting rid of MfgPN from the Inventory table, because I could then just aggregate all the like CustPN part numbers together into one bulk number! Oh, that sounds so nice and easy...
If that's the case, you might want to track the component parts by lot number. There will be a separate inventory record for each lot, so you can see which MfgPN is in each lot and which lots get issued to which assembly.
When you recieve the parts into inventory, you'll have to apply some logic to see what the next lot number is and apply it to the receipt. You'll also be able to see when and where each lot was purchased, which will help with traceability. For planning purposes, you'll give totals for each CustPN - you don't need to buy more RES-000293-R if the total of all the lots on hand is enough for the next production run.
When you issue parts to the assembly, you can subtract the quantity from the oldest (smallest number) lot until you reach qty=0 and then move on to the next lot - almost the same logic you're already thinking about using. It would also tend to enforce FIFO (first in first out) on your inventory.
If you have time work on it, you can use VBA to do all the transactions - not just control the looping. It will be more efficient than staying in queryland, but the learning curve can be steep.
Yep, I think I'm going to do that, virtually. That's what the CustPN is for - so in a query, I can sum the totals to the CustPN level- I can use that in conjunction with a temp table to process and find those real inventory "alternates", plus I can use FIFO logic to help me sort through which PN's to decrement first.