SQL for Position Structure

SELECT company_id, LEVEL, pos_code, sup_pos_code
  FROM company_position
 WHERE company_id = '10'
 START WITH sup_pos_code = '*'
CONNECT BY PRIOR pos_code = sup_pos_code
       AND PRIOR company_id = company_id;

SELECT company_id, RPAD('+', LEVEL, '+') || pos_code
  FROM company_position
 START WITH sup_pos_code = '*'
CONNECT BY PRIOR pos_code = sup_pos_code
       AND PRIOR company_id = company_id;

Hello @durette can it possible to write same code for product structure?

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

I tried to wroten 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?

Sure, but it’s better to start a new question so future users of the forum can find it.

https://community.ifs.com/technology-infrastructure-cloud-integration-dev-tools-50/sql-for-visualizing-product-structure-2864

@durette Thank you for the SQL.

I want to only show the valid positions as of the current date in the hierarchy structure. However when i run IFS debug and capture the SQL produced by IFS it seems to be using IFSAPP.company_position_structure.valid_from AND
IFSAPP.company_position_structure.valid_to. But the two positions show as not valid (red cross) in the user interface but are shown in the SQL query as they have values of 01/01/0001 and 31/12/9999. 

The dates for IFSAPP.company_position.valid_from and IFSAPP.company_position.valid_to show as 08/10/1950 09:20:12 and 26/06/2016 though. 

Any guidance on this?

Thanks