Question

SQL for Position Structure

  • 17 July 2020
  • 5 replies
  • 196 views
S
Rank
Userlevel 5
Badge +10

Hello

Has anyone written SQL to present the IFS Position Structure in the same way as the Graphical Pos Structure?

So need to be ordered and show parent/child as per the screen display. 

Regards

Shaun

5 replies

durette
Rank
Userlevel 7
Badge +18

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;

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
0guz
Rank
Userlevel 4
Badge +10

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?

https://www.linkedin.com/in/o%C4%9Fuzhan-korkmaz-701596127
durette
Rank
Userlevel 7
Badge +18

same code for product structure

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

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
durette
Rank
Userlevel 7
Badge +18

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

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

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
S
Rank
Userlevel 5
Badge +10

@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

Reply


Terms & ConditionsCookie settings