PeopleSoft - Query to determine which records user can see

gistfile1.sql

SELECT DISTINCT B.TREE_NODE, Z.RECDESCR 
FROM PSTREEDEFN A, 
PSTREENODE B, 
PS_SCRTY_ACC_GRP C, 
PSTREENODE E, 
PSROLECLASS X, 
PSROLEUSER Y, 
PSRECDEFN Z 
WHERE A.SETID = ' ' 
AND A.TREE_STRCT_ID = 'ACCESS_GROUP' 
AND A.EFF_STATUS = 'A' 
AND A.EFFDT = 
(SELECT MAX (D.EFFDT) 
FROM PSTREEDEFN D 
WHERE D.SETID = ' ' 
AND D.TREE_NAME = A.TREE_NAME 
AND D.EFFDT <= 
TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'), 
'YYYY-MM-DD' 
)) 
AND Y.ROLEUSER = :1 
AND Y.ROLENAME = X.ROLENAME 
AND X.CLASSID = C.CLASSID 
AND C.TREE_NAME = A.TREE_NAME 
AND C.ACCESSIBLE = 'Y' 
AND B.SETID = ' ' 
AND B.TREE_NAME = E.TREE_NAME 
AND B.EFFDT = E.EFFDT 
AND B.TREE_NODE_TYPE = 'R' 
AND B.TREE_NODE = Z.RECNAME 
AND Z.RECTYPE IN (0, 1, 6) 
AND E.SETID = ' ' 
AND E.TREE_NAME = A.TREE_NAME 
AND E.EFFDT = A.EFFDT 
AND E.TREE_NODE_TYPE = 'G' 
AND B.TREE_NODE_NUM BETWEEN E.TREE_NODE_NUM AND E.TREE_NODE_NUM_END 
AND C.ACCESS_GROUP = E.TREE_NODE 
AND ( (NOT EXISTS 
(SELECT 'X' 
FROM PS_SCRTY_ACC_GRP F 
WHERE F.CLASSID = X.CLASSID 
AND F.TREE_NAME = A.TREE_NAME 
AND F.ACCESSIBLE = 'N')) 
OR (E.TREE_NODE_NUM = 
(SELECT MAX (G.TREE_NODE_NUM) 
FROM PSTREENODE G, PS_SCRTY_ACC_GRP H 
WHERE G.SETID = ' ' 
AND G.TREE_NAME = A.TREE_NAME 
AND G.EFFDT = A.EFFDT 
AND G.TREE_NODE_TYPE = 'G' 
AND B.TREE_NODE_NUM BETWEEN G.TREE_NODE_NUM 
AND G.TREE_NODE_NUM_END 
AND H.CLASSID = X.CLASSID 
AND H.TREE_NAME = A.TREE_NAME 
AND H.ACCESS_GROUP = G.TREE_NODE)))