We are trying to identify which assets are in a kit for a Jasper Report. We wish to exclude inventory that is in a kit from the Total Stock Holding.
Thanks for any advise.
Solved
Does anyone know how KIT contents is tagged within the Data Base, that it is "IN KIT"?
Best answer by Robert Bellemare
Hi Reg,
The h_inv_no_id and nh_inv_no_id in the inv_inv table are the basis for forming inventory hierarchies and relationships between BATCH, SER, TRK, ASSY, and ACFT.
Contrary to these other inventory type, KITs are using their own mapping table to maintain this relationship as there are additional requirements for tracking these relationships. You can find this information, as you explained, in the inv_kit_map table.
Here is a sample query of a relationship map:
SELECT *
FROM inv_inv kit_inventory
INNER JOIN inv_kit_map ON
inv_kit_map.kit_inv_no_db_id = kit_inventory.inv_no_db_id AND
inv_kit_map.kit_inv_no_id = kit_inventory.inv_no_id
INNER JOIN inv_inv kit_contents ON
kit_contents.inv_no_db_id = inv_kit_map.inv_no_db_id AND
kit_contents.inv_no_id = inv_kit_map.inv_no_id;
You can eliminate kit content from your query by using a where clause like this:
SELECT *
FROM inv_inv TotalStockHolding
WHERE NOT EXISTS (
SELECT 1
FROM inv_kit_map
WHERE
inv_kit_map.inv_no_db_id = TotalStockHolding.inv_no_db_id AND
inv_kit_map.inv_no_id = TotalStockHolding.inv_no_id
);
+5Since the INV_KIT table seems to hold the core details about a KIT, which link directly to the INV_INV table and there is an INV_CLASS_CD of KIT in INV_INV, I’m suspecting you would be able to use INV_INV table where INV_CLASS_CD=’KIT’ to find the parent containers, then I assume the inventory assigned to that kit would be connected in the NH_INV_NO_ID or H_INV_NO_ID columns...but I don’t have data that I can validate this with currently. Does that help give some initial direction to explore in?
Thanks.
We have used this to subtract from the Total Stock Holding.
(SELECT *
-- FROM INV_KIT_MAP
-- WHERE INV_INV.INV_NO_ID = INV_KIT_MAP.INV_NO_ID
-- AND INV_INV.INV_NO_DB_ID = INV_KIT_MAP.INV_NO_DB_ID
-- )
This seems to work.
+9On the INV_INV table you can join back to INV_INV on these fields to get the kit the part is in.
+10Hi Reg,
The h_inv_no_id and nh_inv_no_id in the inv_inv table are the basis for forming inventory hierarchies and relationships between BATCH, SER, TRK, ASSY, and ACFT.
Contrary to these other inventory type, KITs are using their own mapping table to maintain this relationship as there are additional requirements for tracking these relationships. You can find this information, as you explained, in the inv_kit_map table.
Here is a sample query of a relationship map:
SELECT *
FROM inv_inv kit_inventory
INNER JOIN inv_kit_map ON
inv_kit_map.kit_inv_no_db_id = kit_inventory.inv_no_db_id AND
inv_kit_map.kit_inv_no_id = kit_inventory.inv_no_id
INNER JOIN inv_inv kit_contents ON
kit_contents.inv_no_db_id = inv_kit_map.inv_no_db_id AND
kit_contents.inv_no_id = inv_kit_map.inv_no_id;
You can eliminate kit content from your query by using a where clause like this:
SELECT *
FROM inv_inv TotalStockHolding
WHERE NOT EXISTS (
SELECT 1
FROM inv_kit_map
WHERE
inv_kit_map.inv_no_db_id = TotalStockHolding.inv_no_db_id AND
inv_kit_map.inv_no_id = TotalStockHolding.inv_no_id
);
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.