We are attempting to create a saved search to show how many of our assembly items or kits we have sufficient stock in inventory to build. Here is what we currently have set for the saved search:
Criteria:
Type - Assembly/Bill of Materials, Kit/Package
Member Item : Inventory Location - Desired Location
Matrix Item - False (as we build matrix sub-items, not matrix items)
Inactive - False
Results:
Name - Group
Description - Group
Member Item
Member Quantity
Member Item : Location on Hand
Formula (Numeric) trunc(nvl(to_number({memberitem.locationquantityonhand}),0)/to_number({memberquantity}),0) - Minimum
The issue we are having is we have several assembly items built with sub-assembly items. Those final assemblies are not showing correct numbers. The way we have it setup now, it will show the quantity that can be built as the quantity of a sub-assembly that is in stock.
We would like to be able to see the total we can build based upon component availability. For example, we want to see how many of Assembly A we can build. Assembly A is composed of Assembly B and Item A. We currently have qty. 1 of Assembly B in stock, but we have the components to build qty. 2 more of Assembly B. We would like this saved search to be able to show us that the maximum quantity of Assembly A we could build would be 3 because we have the quantity of components for Assembly B to be able to build 2 more of them to go along with the 1 we currently have in stock. This is, of course, assuming we have enough quantity of Item A to build a total of 3 Assembly A.
Does anybody know what we need to use to make this happen? The way it is right now, it works great for any assembly that is not composed of sub-assemblies. For those assemblies that do have sub-assemblies, it will only show quantities that can be built based upon the quantity of the sub-assemblies that are already built and in stock.