Start With [CONNECT BY PRIOR]

 Hi ,

The following  script is designed to get the ingredients and the ingredients of the ingredients in opm formula 

for example if  

product = "X"

Ingredients = "Y" ,  "Z"

"Y" is a product in other formula 

and it's ingredients = " K  "  ,  " L "

and " L " could be product in other formula 

SELECT     LPAD ('   ', 10 * (LEVEL - 1)) || TO_CHAR (fmn.ingredient) x,

           fmn.*

      FROM (SELECT fmm.formula_id, fmm.formula_no, fmm.formula_desc1, :p_qty,

                   c.organization_id, c.segment1 ingredient, c.description,

                   fmd.qty, fmd.detail_uom uom,

                   (  (  :p_qty

                       / (SELECT b.qty

                            FROM fm_matl_dtl b

                           WHERE b.line_type = 1