Enter Ingredient get all the Product Items

Query get all the Product Items by Entering the Ingredient

 

We can use this Query to Get the Information of what are the products manufactured using one  Ingredient, Because One Ingredient is also used to Manufacture other products 

 --------------------------------------------------------------------------------------------------------------------------------------

Query:
******

 SELECT FM.FORMULA_ID,
  FM.FORMULA_NO,
  FM.FORMULA_VERS,
  PRI.SEGMENT1 PRODUCT,
  INGI.SEGMENT1 INGREDIENT
FROM FM_FORM_MST FM,
  FM_MATL_DTL ING,
  FM_MATL_DTL PR,
  MTL_SYSTEM_ITEMS INGI,
  MTL_SYSTEM_ITEMS PRI
WHERE FM.FORMULA_ID          =ING.FORMULA_ID
AND ING.LINE_TYPE            = '-1'
AND FM.FORMULA_ID            = PR.FORMULA_ID
--AND FM.FORMULA_ID            = 786
AND INGI.SEGMENT1 =  "XXXXXXXX"
AND PR.LINE_TYPE             = '1'
AND ING.INVENTORY_ITEM_ID    = INGI.INVENTORY_ITEM_ID
AND PR.INVENTORY_ITEM_ID     = PRI.INVENTORY_ITEM_ID
AND INGI.ORGANIZATION_ID     = ING.ORGANIZATION_ID
AND PRI.ORGANIZATION_ID      = PR.ORGANIZATION_ID
AND FM.OWNER_ORGANIZATION_ID = XXXX;

--------------------------------------------------------------------------------------------------------------------------------------