Skip to content

Instantly share code, notes, and snippets.

@ccjeng
Last active December 15, 2015 19:08
Show Gist options
  • Save ccjeng/5308533 to your computer and use it in GitHub Desktop.
Save ccjeng/5308533 to your computer and use it in GitHub Desktop.
PeopleSoft - Query to determine which records user can see
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)))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment