Question

SQL for Visualizing Product Structure

  • 19 July 2020
  • 7 replies
  • 546 views

Userlevel 7
Badge +18

@0guz  asked:

I want to see structure levels for parts from '0' as top product until last one.

I tried to write that code, but a part can exist in case x at level 2, same part can exist in case y at level 4. So we had to use LISTAGG maybe idk. Can u help me for this problem?


7 replies

Userlevel 7
Badge +18

@0guz , here you go.

Userlevel 4
Badge +10

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;

Userlevel 4
Badge +7

How about structure multilevel tab in part cost?

Badge +2

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

Userlevel 4

,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"

Badge +2

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

 

 

Badge +2

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

Reply