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)))

Views: 119

Reply to This

Replies to This Discussion

I reallyy liked this post.It was really very helpfull thankyouu.

Thanks for the information !! I really Appreciate it..

it's a pleasure to meet you. I am USA Army personnel, i have an important thing to discus with you.
Please write me on my email (captkristen899@gmail.com)

RSS

PeopleSoft Jobs in US

Videos

  • Add Videos
  • View All

© 2022   Created by Maisam Agha.   Powered by

Badges  |  Report an Issue  |  Terms of Service