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

  • 16 November 2023
  • 5 replies

Userlevel 1
Badge +2
  • Do Gooder (Customer)
  • 4 replies

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.


Best answer by Robert Bellemare 17 November 2023, 22:50

View original

5 replies

Badge +4

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?

Userlevel 1
Badge +2


We have used this to subtract from the Total Stock Holding.


--                            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.

Userlevel 1
Badge +9

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





Userlevel 5
Badge +10

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:

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:

FROM inv_inv TotalStockHolding
   FROM inv_kit_map
      inv_kit_map.inv_no_db_id = TotalStockHolding.inv_no_db_id AND
      inv_kit_map.inv_no_id    = TotalStockHolding.inv_no_id

Userlevel 1
Badge +2

Thanks Rob. Appreciate your answer.