SQL for Visualizing Product Structure

@0guz , here you go.

Hello @durette thank you so much for help.

I modified your code a little bit, so i want to share for future requests. I specified changes with red background color.

Kind Regards.

          SELECT ps.part_no AS parent_part,
             RPAD('+', LEVEL, '+') || ps.component_part AS child_part,
             LEVEL,
             SYS_CONNECT_BY_PATH(ps.component_part, '/'),
             CONNECT_BY_ISLEAF,
             CASE WHEN ps.QTY_PER_ASSEMBLY<0 then 'BY-PRODUCT' else 'COMPONENTS' end as TYPE /* for seperate by-products and components*/
        FROM prod_structure_head psh
        JOIN part_revision pr
          ON pr.contract = psh.contract
         AND pr.part_no = psh.part_no
         AND pr.eng_chg_level = psh.eng_chg_level
        JOIN prod_struct_alternate psa
          ON psa.contract = psh.contract
         AND psa.part_no = psh.part_no
         AND psa.eng_chg_level = psh.eng_chg_level
         AND psa.bom_type_db = psh.bom_type_db
         AND psa.alternative_no = '*'
        JOIN prod_structure ps
          ON ps.contract = psa.contract
         AND ps.part_no = psa.part_no
         AND ps.eng_chg_level = psa.eng_chg_level
         AND ps.bom_type_db = psa.bom_type_db
         AND ps.alternative_no = psa.alternative_no
       WHERE psh.eff_phase_out_date IS NULL
         AND psh.bom_type_db = 'M'
         --AND pr.part_revision_status_db = 'E' /*It's the origin code but my database returned Y or N values here*/
         AND pr.part_revision_status_db = 'N' /*So i modified this sentence acording to my DB*/
  START WITH ps.contract = '&ANCESTOR_CONTRACT'
         AND ps.part_no = '&ANCESTOR_PART_NO'
  CONNECT BY PRIOR ps.component_part = ps.part_no
         AND PRIOR ps.contract = ps.contract;

How about structure multilevel tab in part cost?

How would you multiply the qty_per_assembly as you go down each level?

,nvl(prior qty_per_assembly, 1) * qty_per_assembly quant   --this will return qty from whole stucture 

,SYS_CONNECT_BY_PATH(part_no, ' / ') "Path"
,SYS_CONNECT_BY_PATH(qty_per_assembly, '*') "qty"

not exactly what im lookiing for..

for example SYS_CONNECT_BY_PATH(qty_per_assembly, '*')  will go down say 3 levels

and give me *1*11*.022222,

But I want this to multiply as it goes down , so this should show be evaluated as 1x11x.022222

so would have Coolum converting this to say 0.22

need it to keep going up and multiplying each qty be each other to reached top level