Since 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?

On the INV_INV table you can join back to INV_INV on these fields to get the kit the part is in.

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
   );

Thanks Rob. Appreciate your answer.