Blogs

Do you know about PS Tree Flattener ? August 2020

By Alan Hwang posted 08-04-2020 07:50 AM

  

Do you know Tree Flattener can improve query performance when you need to use the tree information to join with the transactional data? Do you know how the tree related information is stored in the PeopleSoft (PS) system?

You can use tree structure in PeopleSoft to store the GL Chartfield Element. We use tree structure to store the GL Chartfield elements in hierarchy levels for representing the parent-child family tree relationship among the data defined in the system. The data stored in tree nodes or tree leaves represents a relationship or a true value of the elements in either a single value or a range. The tree related data are decoupled and stored in, but not limited to, the four metadata tables including PSTREEDEFN, PSTREENODE, PSTREELEVEL, PSTREELEAF.

Stay tuned for a detailed 2-part blog series on the topic of Tree Flattener!

8 comments
10 views

Related Content

Permalink

Comments

Alan Hwang
08-05-2020 05:40 PM

Missing code from last posting

Please copy and  paste the code at the end by overwriting some duplicate code by yourself.

 

/* and A.Tree_Strct_Id = 'ACCESS_GROUP' and

A.tree_name = '???DEBUGGING_TREE' */ AND

A.TREE_NAME = B.tree_name)

GROUP BY B.SETID,

B.TREE_NAME,

B.EFFDT,

B.TREE_NAME,

B.PARENT_NODE_NUM,

B.PARENT_NODE_NAME,

B.TREE_NODE_NUM,

B.TREE_NODE

ORDER BY 1, 2, 3, 4, 5, 6) X) Y

GROUP BY Y.SETID,

Y.TREE_NAME,

Y.EFFDT,

Y.TREE_NAME,

Y.PARENT_NODE_NUM,

Y.PARENT_NODE_NAME;

 

Thanks

Alan Hwang

 

 

 

 

 

Alan Hwang
08-05-2020 05:36 PM

Hopefully , this one will work!! No chopping

select Y.SETID,

Y.TREE_NAME as tn,

Y.EFFDT,

Y.TREE_NAME,

Y.PARENT_NODE_NUM,

Y.PARENT_NODE_NAME,

MAX(Y.L1_TREE_NODE) as L1_TREE_NODE,

MAX(Y.L2_TREE_NODE) as L2_TREE_NODE,

MAX(Y.L3_TREE_NODE) as L3_TREE_NODE,

MAX(Y.L4_TREE_NODE) as L4_TREE_NODE,

MAX(Y.L5_TREE_NODE) as L5_TREE_NODE,

MAX(Y.L6_TREE_NODE) as L6_TREE_NODE,

MAX(Y.L7_TREE_NODE) as L7_TREE_NODE,

MAX(Y.L8_TREE_NODE) as L8_TREE_NODE,

MAX(Y.L9_TREE_NODE) as L9_TREE_NODE,

MAX(Y.L10_TREE_NODE) as L10_TREE_NODE,

MAX(Y.L11_TREE_NODE) as L11_TREE_NODE,

MAX(Y.L12_TREE_NODE) as L12_TREE_NODE,

MAX(Y.L13_TREE_NODE) as L13_TREE_NODE,

MAX(Y.L14_TREE_NODE) as L14_TREE_NODE,

MAX(Y.L15_TREE_NODE) as L15_TREE_NODE,

MAX(Y.L16_TREE_NODE) as L16_TREE_NODE,

MAX(Y.L17_TREE_NODE) as L17_TREE_NODE,

MAX(Y.L18_TREE_NODE) as L18_TREE_NODE,

MAX(Y.L19_TREE_NODE) as L19_TREE_NODE,

MAX(Y.L20_TREE_NODE) as L20_TREE_NODE,

MAX(Y.L21_TREE_NODE) as L21_TREE_NODE

From

(select X.*,

( case when X.C_RANK = 1 then X.TREE_NODE else NULL end) as L1_TREE_NODE,

( case when X.C_RANK = 2 then X.TREE_NODE else NULL end) as L2_TREE_NODE,

( case when X.C_RANK = 3 then X.TREE_NODE else NULL end) as L3_TREE_NODE,

( case when X.C_RANK = 4 then X.TREE_NODE else NULL end) as L4_TREE_NODE,

( case when X.C_RANK = 5 then X.TREE_NODE else NULL end) as L5_TREE_NODE,

( case when X.C_RANK = 6 then X.TREE_NODE else NULL end) as L6_TREE_NODE,

( case when X.C_RANK = 7 then X.TREE_NODE else NULL end) as L7_TREE_NODE,

( case when X.C_RANK = 8 then X.TREE_NODE else NULL end) as L8_TREE_NODE,

( case when X.C_RANK = 9 then X.TREE_NODE else NULL end) as L9_TREE_NODE,

( case when X.C_RANK = 10 then X.TREE_NODE else NULL end) as L10_TREE_NODE,

( case when X.C_RANK = 11 then X.TREE_NODE else NULL end) as L11_TREE_NODE,

( case when X.C_RANK = 12 then X.TREE_NODE else NULL end) as L12_TREE_NODE,

( case when X.C_RANK = 13 then X.TREE_NODE else NULL end) as L13_TREE_NODE,

( case when X.C_RANK = 14 then X.TREE_NODE else NULL end) as L14_TREE_NODE,

( case when X.C_RANK = 15 then X.TREE_NODE else NULL end) as L15_TREE_NODE,

( case when X.C_RANK = 16 then X.TREE_NODE else NULL end) as L16_TREE_NODE,

( case when X.C_RANK = 17 then X.TREE_NODE else NULL end) as L17_TREE_NODE,

( case when X.C_RANK = 18 then X.TREE_NODE else NULL end) as L18_TREE_NODE,

( case when X.C_RANK = 19 then X.TREE_NODE else NULL end) as L19_TREE_NODE,

( case when X.C_RANK = 20 then X.TREE_NODE else NULL end) as L20_TREE_NODE,

( case when X.C_RANK = 21 then X.TREE_NODE else NULL end) as L21_TREE_NODE

From

(

SELECT B.SETID,

B.TREE_NAME,

B.EFFDT,

B.PARENT_NODE_NUM,

B.PARENT_NODE_NAME,

B.TREE_NODE_NUM,

B.TREE_NODE,

RANK() OVER(PARTITION BY B.SETID, B.TREE_NAME, B.PARENT_NODE_NUM ORDER BY B.SETID, B.TREE_NAME, B.PARENT_NODE_NUM, B.PARENT_NODE_NAME, B.TREE_NODE_NUM) AS C_RANK

FROM PSTREEDEFN A, PSTREENODE B

WHERE (A.EFFDT = (SELECT MAX(A_ED.EFFDT)

FROM PSTREEDEFN A_ED

WHERE A.SETID = A_ED.SETID

AND A.SETCNTRLVALUE = A_ED.SETCNTRLVALUE

AND A.TREE_NAME = A_ED.TREE_NAME

AND A_ED.EFFDT <= SYSDATE) AND

A.SETID = B.SETID AND

A.SETCNTRLVALUE = B.SETCNTRLVALUE AND

A.TREE_NAME = B.TREE_NAME AND A.EFFDT = B.EFFDT AND

B.EFFDT = (SELECT MAX(C_ED.EFFDT)

FROM PSTREENODE C_ED

WHERE B.SETID = C_ED.SETID

AND B.SETCNTRLVALUE = C_ED.SETCNTRLVALUE

AND B.TREE_NAME = C_ED.TREE_NAME

AND C_ED.EFFDT <= SYSDATE)

/* and A.Tree_Strct_Id = 'ACCESS_GROUP' and

A.tree_name = '???DEBUGGING_TREE' */ AND

A.TREE_NAME = B.tree_name)

GROUP BY

Alan Hwang
08-05-2020 05:28 PM

Here is the new code without customized table views needed

 

Please ignore all the codes provided above. This is the one will run in all pillars without utilizing some customized views. Please let me if you have some questions on this.

 

select Y.SETID,

Y.TREE_NAME as tn,

Y.EFFDT,

Y.TREE_NAME,

Y.PARENT_NODE_NUM,

Y.PARENT_NODE_NAME,

MAX(Y.L1_TREE_NODE) as L1_TREE_NODE,

MAX(Y.L2_TREE_NODE) as L2_TREE_NODE,

MAX(Y.L3_TREE_NODE) as L3_TREE_NODE,

MAX(Y.L4_TREE_NODE) as L4_TREE_NODE,

MAX(Y.L5_TREE_NODE) as L5_TREE_NODE,

MAX(Y.L6_TREE_NODE) as L6_TREE_NODE,

MAX(Y.L7_TREE_NODE) as L7_TREE_NODE,

MAX(Y.L8_TREE_NODE) as L8_TREE_NODE,

MAX(Y.L9_TREE_NODE) as L9_TREE_NODE,

MAX(Y.L10_TREE_NODE) as L10_TREE_NODE,

MAX(Y.L11_TREE_NODE) as L11_TREE_NODE,

MAX(Y.L12_TREE_NODE) as L12_TREE_NODE,

MAX(Y.L13_TREE_NODE) as L13_TREE_NODE,

MAX(Y.L14_TREE_NODE) as L14_TREE_NODE,

MAX(Y.L15_TREE_NODE) as L15_TREE_NODE,

MAX(Y.L16_TREE_NODE) as L16_TREE_NODE,

MAX(Y.L17_TREE_NODE) as L17_TREE_NODE,

MAX(Y.L18_TREE_NODE) as L18_TREE_NODE,

MAX(Y.L19_TREE_NODE) as L19_TREE_NODE,

MAX(Y.L20_TREE_NODE) as L20_TREE_NODE,

MAX(Y.L21_TREE_NODE) as L21_TREE_NODE

From

(select X.*,

( case when X.C_RANK = 1 then X.TREE_NODE else NULL end) as L1_TREE_NODE,

( case when X.C_RANK = 2 then X.TREE_NODE else NULL end) as L2_TREE_NODE,

( case when X.C_RANK = 3 then X.TREE_NODE else NULL end) as L3_TREE_NODE,

( case when X.C_RANK = 4 then X.TREE_NODE else NULL end) as L4_TREE_NODE,

( case when X.C_RANK = 5 then X.TREE_NODE else NULL end) as L5_TREE_NODE,

( case when X.C_RANK = 6 then X.TREE_NODE else NULL end) as L6_TREE_NODE,

( case when X.C_RANK = 7 then X.TREE_NODE else NULL end) as L7_TREE_NODE,

( case when X.C_RANK = 8 then X.TREE_NODE else NULL end) as L8_TREE_NODE,

( case when X.C_RANK = 9 then X.TREE_NODE else NULL end) as L9_TREE_NODE,

( case when X.C_RANK = 10 then X.TREE_NODE else NULL end) as L10_TREE_NODE,

( case when X.C_RANK = 11 then X.TREE_NODE else NULL end) as L11_TREE_NODE,

( case when X.C_RANK = 12 then X.TREE_NODE else NULL end) as L12_TREE_NODE,

( case when X.C_RANK = 13 then X.TREE_NODE else NULL end) as L13_TREE_NODE,

( case when X.C_RANK = 14 then X.TREE_NODE else NULL end) as L14_TREE_NODE,

( case when X.C_RANK = 15 then X.TREE_NODE else NULL end) as L15_TREE_NODE,

( case when X.C_RANK = 16 then X.TREE_NODE else NULL end) as L16_TREE_NODE,

( case when X.C_RANK = 17 then X.TREE_NODE else NULL end) as L17_TREE_NODE,

( case when X.C_RANK = 18 then X.TREE_NODE else NULL end) as L18_TREE_NODE,

( case when X.C_RANK = 19 then X.TREE_NODE else NULL end) as L19_TREE_NODE,

( case when X.C_RANK = 20 then X.TREE_NODE else NULL end) as L20_TREE_NODE,

( case when X.C_RANK = 21 then X.TREE_NODE else NULL end) as L21_TREE_NODE

From

(

SELECT B.SETID,

B.TREE_NAME,

B.EFFDT,

B.PARENT_NODE_NUM,

B.PARENT_NODE_NAME,

B.TREE_NODE_NUM,

B.TREE_NODE,

RANK() OVER(PARTITION BY B.SETID, B.TREE_NAME, B.PARENT_NODE_NUM ORDER BY B.SETID, B.TREE_NAME, B.PARENT_NODE_NUM, B.PARENT_NODE_NAME, B.TREE_NODE_NUM) AS C_RANK

FROM PSTREEDEFN A, PSTREENODE B

WHERE (A.EFFDT = (SELECT MAX(A_ED.EFFDT)

FROM PSTREEDEFN A_ED

WHERE A.SETID = A_ED.SETID

AND A.SETCNTRLVALUE = A_ED.SETCNTRLVALUE

AND A.TREE_NAME = A_ED.TREE_NAME

AND A_ED.EFFDT <= SYSDATE) AND

A.SETID = B.SETID AND

A.SETCNTRLVALUE = B.SETCNTRLVALUE AND

A.TREE_NAME = B.TREE_NAME AND A.EFFDT = B.EFFDT AND

B.EFFDT = (SELECT MAX(C_ED.EFFDT)

FROM PSTREENODE C_ED

WHERE B.SETID = C_ED.SETID

AND B.SETCNTRLVALUE = C_ED.SETCNTRLVALUE

AND B.TREE_NAME = C_ED.TREE_NAME

AND C_ED.EFFDT <= SYSDATE)

/* and A.Tree_Strct_Id = 'ACCESS_GROUP' and

A.tree_name = '???DEBUGGING_TREE' */ AND

A.TREE_NAME = B.tree_name)

Alan Hwang
08-05-2020 04:55 PM

Last codes were chopped, Here is the new one.

select Y.SETID,

       Y.TREE_NAME,

       Y.EFFDT,

       Y.TREE_NAME,

       Y.PARENT_NODE_NUM,

       Y.PARENT_NODE_NAME,

       MAX(Y.L1_TREE_NODE) as L1_TREE_NODE,

       MAX(Y.L2_TREE_NODE) as L2_TREE_NODE,

       MAX(Y.L3_TREE_NODE) as L3_TREE_NODE,

       MAX(Y.L4_TREE_NODE) as L4_TREE_NODE,

       MAX(Y.L5_TREE_NODE) as L5_TREE_NODE,

       MAX(Y.L6_TREE_NODE) as L6_TREE_NODE,

       MAX(Y.L7_TREE_NODE) as L7_TREE_NODE,

       MAX(Y.L8_TREE_NODE) as L8_TREE_NODE,

       MAX(Y.L9_TREE_NODE) as L9_TREE_NODE,

       MAX(Y.L10_TREE_NODE) as L10_TREE_NODE,

       MAX(Y.L11_TREE_NODE) as L11_TREE_NODE,

       MAX(Y.L12_TREE_NODE) as L12_TREE_NODE,

       MAX(Y.L13_TREE_NODE) as L13_TREE_NODE,

       MAX(Y.L14_TREE_NODE) as L14_TREE_NODE,

       MAX(Y.L15_TREE_NODE) as L15_TREE_NODE,

       MAX(Y.L16_TREE_NODE) as L16_TREE_NODE,

       MAX(Y.L17_TREE_NODE) as L17_TREE_NODE,

       MAX(Y.L18_TREE_NODE) as L18_TREE_NODE,

       MAX(Y.L19_TREE_NODE) as L19_TREE_NODE,

       MAX(Y.L20_TREE_NODE) as L20_TREE_NODE,

       MAX(Y.L21_TREE_NODE) as L21_TREE_NODE

From

(select X.*,

( case when X.C_RANK = 1 then X.TREE_NODE else NULL end) as L1_TREE_NODE,

( case when X.C_RANK = 2 then X.TREE_NODE else NULL end) as L2_TREE_NODE,

( case when X.C_RANK = 3 then X.TREE_NODE else NULL end) as L3_TREE_NODE,

( case when X.C_RANK = 4 then X.TREE_NODE else NULL end) as L4_TREE_NODE,

( case when X.C_RANK = 5 then X.TREE_NODE else NULL end) as L5_TREE_NODE,

( case when X.C_RANK = 6 then X.TREE_NODE else NULL end) as L6_TREE_NODE,

( case when X.C_RANK = 7 then X.TREE_NODE else NULL end) as L7_TREE_NODE,

( case when X.C_RANK = 8 then X.TREE_NODE else NULL end) as L8_TREE_NODE,

( case when X.C_RANK = 9 then X.TREE_NODE else NULL end) as L9_TREE_NODE,

( case when X.C_RANK = 10 then X.TREE_NODE else NULL end) as L10_TREE_NODE,

( case when X.C_RANK = 11 then X.TREE_NODE else NULL end) as L11_TREE_NODE,

( case when X.C_RANK = 12 then X.TREE_NODE else NULL end) as L12_TREE_NODE,

( case when X.C_RANK = 13 then X.TREE_NODE else NULL end) as L13_TREE_NODE,

( case when X.C_RANK = 14 then X.TREE_NODE else NULL end) as L14_TREE_NODE,

( case when X.C_RANK = 15 then X.TREE_NODE else NULL end) as L15_TREE_NODE,

( case when X.C_RANK = 16 then X.TREE_NODE else NULL end) as L16_TREE_NODE,

( case when X.C_RANK = 17 then X.TREE_NODE else NULL end) as L17_TREE_NODE,

( case when X.C_RANK = 18 then X.TREE_NODE else NULL end) as L18_TREE_NODE,

( case when X.C_RANK = 19 then X.TREE_NODE else NULL end) as L19_TREE_NODE,

( case when X.C_RANK = 20 then X.TREE_NODE else NULL end) as L20_TREE_NODE,

( case when X.C_RANK = 21 then X.TREE_NODE else NULL end) as L21_TREE_NODE

From

(

SELECT B.SETID,

                       B.TREE_NAME,

                     B.EFFDT,

                       B.PARENT_NODE_NUM,

                       B.PARENT_NODE_NAME,

                       B.TREE_NODE_NUM,

                       B.TREE_NODE,

                       RANK() OVER(PARTITION BY B.SETID, B.TREE_NAME, B.PARENT_NODE_NUM ORDER BY B.SETID, B.TREE_NAME, B.PARENT_NODE_NUM, B.PARENT_NODE_NAME, B.TREE_NODE_NUM) AS C_RANK

                 FROM PS_NC_TrNoLeaf_MVW B, pstreedefn C

                 WHERE (B.SETID = C.Setid and

                       C.Tree_Strct_Id = 'ACCESS_GROUP' and

                       b.tree_name = 'QUERY_TREE_BI' AND

                       B.TREE_NAME = c.tree_name)

                 GROUP BY B.SETID,

                         B.TREE_NAME,

                         B.EFFDT,

                         B.TREE_NAME,

                         B.PARENT_NODE_NUM,

                         B.PARENT_NODE_NAME,

                         B.TREE_NODE_NUM,

                         B.TREE_NODE

                 ORDER BY 1, 2, 3, 4, 5, 6) X) Y

GROUP BY Y.SETID,

         Y.TREE_NAME,

         Y.EFFDT,

         Y.TREE_NAME,

         Y.PARENT_NODE_NUM,

         Y.PARENT_NODE_NAME;

Alan Hwang
08-05-2020 04:42 PM

Existing query?

Anna,

I don't think that there is any existing delivered PS query which use the flatten tables. You can check that by running the sql :

select * from psqryrecord where recname like '%_R_TFLT_%' ;

The code I provided for Alexei. You may want to run that to see will whether that will get what you want or not. If that is not what you want, I do have some other tree flatten code that will do the same thing as PS delivered. I shall provide that in the incoming blog posting.

Thanks,

Alan Hwang

Alan Hwang
08-05-2020 04:30 PM

Is PS Tree Flattener available in campus solutions?

Hi Alexei,

Based on the page and component name of the Flattener, I don't think that is available in CS. Based on your code, I don't think that this is the process that can help you to get what you want. This Flattener is for level defined in the PSTreeLevel. Your codes looks like is trying to flatten the TREE_NODE for the same Parent_Tree_Num or Parent_Tree_Name. I have the code to flatten the tree_node in parent child relationship below. Hopefully, that is what you need and that can help you. Please let me know either way.

 

select Y.SETID,

Y.TREE_NAME,

Y.EFFDT,

Y.TREE_NAME,

Y.PARENT_NODE_NUM,

Y.PARENT_NODE_NAME,

MAX(Y.L1_TREE_NODE) as L1_TREE_NODE,

MAX(Y.L2_TREE_NODE) as L2_TREE_NODE,

MAX(Y.L3_TREE_NODE) as L3_TREE_NODE,

MAX(Y.L4_TREE_NODE) as L4_TREE_NODE,

MAX(Y.L5_TREE_NODE) as L5_TREE_NODE,

MAX(Y.L6_TREE_NODE) as L6_TREE_NODE,

MAX(Y.L7_TREE_NODE) as L7_TREE_NODE,

MAX(Y.L8_TREE_NODE) as L8_TREE_NODE,

MAX(Y.L9_TREE_NODE) as L9_TREE_NODE,

MAX(Y.L10_TREE_NODE) as L10_TREE_NODE,

MAX(Y.L11_TREE_NODE) as L11_TREE_NODE,

MAX(Y.L12_TREE_NODE) as L12_TREE_NODE,

MAX(Y.L13_TREE_NODE) as L13_TREE_NODE,

MAX(Y.L14_TREE_NODE) as L14_TREE_NODE,

MAX(Y.L15_TREE_NODE) as L15_TREE_NODE,

MAX(Y.L16_TREE_NODE) as L16_TREE_NODE,

MAX(Y.L17_TREE_NODE) as L17_TREE_NODE,

MAX(Y.L18_TREE_NODE) as L18_TREE_NODE,

MAX(Y.L19_TREE_NODE) as L19_TREE_NODE,

MAX(Y.L20_TREE_NODE) as L20_TREE_NODE,

MAX(Y.L21_TREE_NODE) as L21_TREE_NODE

From (select X.*,

(case

when X.C_RANK = 1 then

X.TREE_NODE

else

NULL

end) as L1_TREE_NODE,

(case

when X.C_RANK = 2 then

X.TREE_NODE

else

NULL

end) as L2_TREE_NODE,

(case

when X.C_RANK = 3 then

X.TREE_NODE

else

NULL

end) as L3_TREE_NODE,

(case

when X.C_RANK = 4 then

X.TREE_NODE

else

NULL

end) as L4_TREE_NODE,

(case

when X.C_RANK = 5 then

X.TREE_NODE

else

NULL

end) as L5_TREE_NODE,

(case

when X.C_RANK = 6 then

X.TREE_NODE

else

NULL

end) as L6_TREE_NODE,

(case

when X.C_RANK = 7 then

X.TREE_NODE

else

NULL

end) as L7_TREE_NODE,

(case

when X.C_RANK = 8 then

X.TREE_NODE

else

NULL

end) as L8_TREE_NODE,

(case

when X.C_RANK = 9 then

X.TREE_NODE

else

NULL

end) as L9_TREE_NODE,

(case

when X.C_RANK = 10 then

X.TREE_NODE

else

NULL

end) as L10_TREE_NODE,

(case

when X.C_RANK = 11 then

X.TREE_NODE

else

NULL

end) as L11_TREE_NODE,

(case

when X.C_RANK = 12 then

X.TREE_NODE

else

NULL

end) as L12_TREE_NODE,

(case

when X.C_RANK = 13 then

X.TREE_NODE

else

NULL

end) as L13_TREE_NODE,

(case

when X.C_RANK = 14 then

X.TREE_NODE

else

NULL

end) as L14_TREE_NODE,

(case

when X.C_RANK = 15 then

X.TREE_NODE

else

NULL

end) as L15_TREE_NODE,

(case

when X.C_RANK = 16 then

X.TREE_NODE

else

NULL

end) as L16_TREE_NODE,

(case

when X.C_RANK = 17 then

X.TREE_NODE

else

NULL

end) as L17_TREE_NODE,

(case

when X.C_RANK = 18 then

X.TREE_NODE

else

NULL

end) as L18_TREE_NODE,

(case

when X.C_RANK = 19 then

X.TREE_NODE

else

NULL

end) as L19_TREE_NODE,

(case

when X.C_RANK = 20 then

X.TREE_NODE

else

NULL

end) as L20_TREE_NODE,

(case

when X.C_RANK = 21 then

X.TREE_NODE

else

NULL

end) as L21_TREE_NODE

From (SELECT B.SETID,

B.TREE_NAME,

B.EFFDT,

B.PARENT_NODE_NUM,

B.PARENT_NODE_NAME,

B.TREE_NODE_NUM,

B.TREE_NODE,

RANK() OVER(PARTITION BY B.SETID, B.TREE_NAME, B.PARENT_NODE_NUM ORDER BY B.SETID, B.TREE_NAME, B.PARENT_NODE_NUM, B.PARENT_NODE_NAME, B.TREE_NODE_NUM) AS C_RANK

FROM PSTREEDEFN A, PSTREENODE B

WHERE (A.EFFDT = (SELECT MAX(A_ED.EFFDT)

FROM PSTREEDEFN A_ED

WHERE A.SETID = A_ED.SETID

AND A.SETCNTRLVALUE = A_ED.SETCNTRLVALUE

AND A.TREE_NAME = A_ED.TREE_NAME

AND A_ED.EFFDT <= SYSDATE) AND

A.SETID = B.SETID AND

A.SETCNTRLVALUE = B.SETCNTRLVALUE AND

A.TREE_NAME = B.TREE_NAME AND A.EFFDT = B.EFFDT AND

B.EFFDT = (SELECT MAX(C_ED.EFFDT)

FROM PSTREENODE C_ED

WHERE B.SETID = C_ED.SETID

AND B.SETCNTRLVALUE = C_ED.SETCNTRLVALUE

AND B.TREE_NAME = C_ED.TREE_NAME

AND C_ED.EFFDT <= SYSDATE

Alexei Tetenov
08-04-2020 03:33 PM

Is PS Tree Flattener available in campus solutions?

Is PS Tree Flattener available in campus solutions?

 

I use the following query to flatten the list of navigations.  It does not work when the parent and the child node has the same name.

select nav.*
from (select replace(navigation,'',' > ') pia_navigation,
url, menu_name, component_name, portal_objname, portal_prntobjname,
portal_uri_seg3, portal_label, object_owner_id
from (select sys_connect_by_path (a.portal_label,'>>') navigation,
'/EMPLOYEE/ERP/c/' || a.portal_uri_seg1 || '.' || a.portal_uri_seg2 || '.' || a.portal_uri_seg3 url,
a.portal_uri_seg1 menu_name,
a.portal_uri_seg2 component_name,
a.portal_objname portal_objname,
a.portal_prntobjname portal_prntobjname,
a.portal_uri_seg3 portal_uri_seg3,
a.portal_reftype portal_reftype,
a.portal_label portal_label,
a.objectownerid object_owner_id
--doing a subquery to filter out all of the non-EMPLOYEE portal objects so that they won't be included in the hierarchical query.
--the CONNECT BY condition is evaluated before the WHERE clause.
--This is needed because the CONNECT BY condition is evaluated before the WHERE clause.
from (select /*distinct*/ a.portal_name, a.portal_label, a.portal_objname, a.portal_prntobjname,
a.portal_uri_seg1, a.portal_uri_seg2, a.portal_uri_seg3, a.portal_reftype, objectownerid
from psprsmdefn a
where portal_name = 'EMPLOYEE' and
--
-- Deficiencies:
--1. In crm, the same navigation can point to different components.
-- ex. Financial Services > First Notice of Loss
-- points to Menu: RBI_CHANGE_POLICY, Component: RBI_FNOL and (portal object name: CR_RBI_FNOL_INS)
-- points to Menu: RBI_CHANGE_POLICY, Component: RBI_FNOL_APPFORM (portal object name: CR_RBI_FNOL_APPFORM_INS)
--2. In peoplesoft, the folder name and the content reference name can be the same.
-- ex. The portal_objname for the "PeopleTools > Web Profile" folder is PT_WEB_PROFILE and
-- the portal_objname for the "PeopleTools > Web Profile > Web Profile Configuration" content reference is also PT_WEB_PROFILE.
-- so it looks like the content reference is pointing back to itself.
-- EMPLOYEE Web Profile Configuration PT_WEB_PROFILE PT_WEB_PROFILE WEB_PROFILE WEB_PROFILE GBL C PPT
--
--exclude content references that point to themselves. not sure if this is for Pagelets or just bad data...
--needed to weed out the "ORA-01436: CONNECT BY loop in user data" error message
portal_objname <> portal_prntobjname
--commenting out the hiding of
/* and
not exists (select 'x'
from psprsmsysattrvl
where portal_name = a.portal_name and
portal_reftype = a.portal_reftype and
portal_objname = a.portal_objname and
portal_attr_nam = 'PORTAL_HIDE_FROM_NAV' and --The name of the folder or content reference
a.portal_objname not in ('CO_NAVIGATION_COLLECTIONS','PORTAL_BASE_DATA')
)*/
) a
--where portal_uri_seg2 like 'NAME_OF_THE_PS_COMPONENT'
start with a.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
connect by nocycle prior a.portal_objname = a.portal_prntobjname and
prior a.portal_name = a.portal_name)
where navigation not like '%Navigation Collections%'
) nav
;

 

I use the following to show levels in pstreenode:

--all trees, groups, and the associated permission list
select *
from (
select setid,
setcntrlvalue,
tree_name,
tree_branch,
case when lvl = 1 then tree_node else ' ' end level1,
case when lvl = 2 then tree_node else ' ' end level2,
case when lvl = 3 then tree_node else ' ' end level3,
case when lvl = 4 then tree_node else ' ' end level4,
case when lvl = 5 then tree_node else ' ' end level5,
case when lvl = 6 then tree_node else ' ' end level6,
case when lvl = 7 then tree_node else ' ' end level7,
case when lvl = 8 then tree_node else ' ' end level8,
case when lvl = 9 then tree_node else ' ' end level9,
case when lvl = 10 then tree_node else ' ' end level10,
case when lvl = 11 then tree_node else ' ' end level11,
case when lvl = 12 then tree_node else ' ' end level12
from ( select rownum rn, level lvl, setid, setcntrlvalue, tree_name, tree_node, tree_branch
from (select setid, setcntrlvalue, tree_name, effdt, tree_node_num, tree_node, tree_branch, parent_node_num, parent_node_name
from pstreenode a
where effdt >=
all (select effdt
from pstreenode
where setid = a.setid
and setcntrlvalue = a.setcntrlvalue
and tree_name = a.tree_name
and tree_node_num = a.tree_node_num
and tree_node = a.tree_node
and tree_branch = a.tree_branch)
--and tree_name = 'RIT_QUERY_TREE'
)
start with parent_node_name = ' '
connect by prior setid = setid
-- and prior setcntrlvalue = setcntrlvalue
and prior tree_name = tree_name
and prior tree_node_num = parent_node_num
and prior tree_node = parent_node_name
and prior tree_branch = tree_branch
) tree
) tree
join ps_scrty_acc_grp on (ps_scrty_acc_grp.tree_name = tree.tree_name and
(ps_scrty_acc_grp.access_group = tree.level1 or
ps_scrty_acc_grp.access_group = tree.level2 or
ps_scrty_acc_grp.access_group = tree.level3 or
ps_scrty_acc_grp.access_group = tree.level4 or
ps_scrty_acc_grp.access_group = tree.level5 or
ps_scrty_acc_grp.access_group = tree.level6 or
ps_scrty_acc_grp.access_group = tree.level7 or
ps_scrty_acc_grp.access_group = tree.level8)
)
order by tree.tree_name, classid, access_group;

 

This is another one:

--query security - tree and access group
select parent.tree_name, parent.parent_node_name, parent.tree_node, parent.tree_node_type,
level1.tree_name, level1.parent_node_name, level1.tree_node,
level2.tree_name, level2.parent_node_name, level2.tree_node,
level3.tree_name, level3.parent_node_name, level3.tree_node,
level4.tree_name, level4.parent_node_name, level4.tree_node,
level5.tree_name, level5.parent_node_name, level5.tree_node,
level6.tree_name, level6.parent_node_name, level6.tree_node,
level7.tree_name, level7.parent_node_name, level7.tree_node,
level8.tree_name, level8.parent_node_name, level8.tree_node
,rec.recdescr, objectownerid, descrlong
from pstreenode parent
join pstreedefn on (parent.tree_name = pstreedefn.tree_name and pstreedefn.tree_strct_id = 'ACCESS_GROUP')
--what are the children of this access group
--child at level 1
left join pstreenode level1 on (level1.tree_name = parent.tree_name and level1.parent_node_name = parent.tree_node)
--child at level 2
left join pstreenode level2 on (level2.tree_name = level1.tree_name and level2.parent_node_name = level1.tree_node)
--child at level 3
left join pstreenode level3 on (level3.tree_name = level2.tree_name and level3.parent_node_name = level2.tree_node)
--child at level 4
left join pstreenode level4 on (level4.tree_name = level3.tree_name and level4.parent_node_name = level3.tree_node)
--child at level 5
left join pstreenode level5 on (level5.tree_name = level4.tree_name and level5.parent_node_name = level4.tree_node)
--child at level 6
left join pstreenode level6 on (level6.tree_name = level5.tree_name and level6.parent_node_name = level5.tree_node)
--child at level 7
left join pstreenode level7 on (level7.tree_name = level6.tree_name and level7.parent_node_name = level6.tree_node)
--child at level 8
left join pstreenode level8 on (level8.tree_name = level7.tree_name and level8.parent_node_name = level7.tree_node)
left join psrecdefn rec on (rec.recname = level3.tree_node)
where trim(parent.parent_node_name) is null

 

Anna Kourouniotis
08-04-2020 11:09 AM

Existing query?

Alan, do you know if there is there a delivered query that we could use to flatten out a tree?