Does anyone know how KIT contents is tagged within the Data Base, that it is "IN KIT"?

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