You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Related to Portal Security

No replies
Guest's picture
Guest

Hi,
Currently I am using people tool 8.42 in HRMS 8.8.
I am getting duplicate rows while fetching the navigation of a paricular Portal Object (portal security)with its menu,component & page name.Plz advise if any join condition or table is missing.find the query below-
SELECT
DISTINCT A.ROLENAME,
A.CLASSID,
K.MENUNAME,
L.PNLGRPNAME,
K.PNLITEMNAME,
CASE
WHEN LTRIM(RTRIM(J.PORTAL_LABEL)) = 'Root' THEN LTRIM(RTRIM(I.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(H.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(G.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(F.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(E.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(D.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(C.PORTAL_LABEL))
WHEN LTRIM(RTRIM(I.PORTAL_LABEL)) = 'Root' THEN LTRIM(RTRIM(H.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(G.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(F.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(E.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(D.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(C.PORTAL_LABEL))
WHEN LTRIM(RTRIM(H.PORTAL_LABEL)) = 'Root' THEN LTRIM(RTRIM(G.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(F.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(E.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(D.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(C.PORTAL_LABEL))
WHEN LTRIM(RTRIM(G.PORTAL_LABEL)) = 'Root' THEN LTRIM(RTRIM(F.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(E.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(D.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(C.PORTAL_LABEL))
WHEN LTRIM(RTRIM(F.PORTAL_LABEL)) = 'Root' THEN LTRIM(RTRIM(E.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(D.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(C.PORTAL_LABEL))
WHEN LTRIM(RTRIM(E.PORTAL_LABEL)) = 'Root' THEN LTRIM(RTRIM(D.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(C.PORTAL_LABEL))
WHEN LTRIM(RTRIM(D.PORTAL_LABEL)) = 'Root' THEN LTRIM(RTRIM(C.PORTAL_LABEL))
ELSE
LTRIM(RTRIM(H.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(G.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(F.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(E.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(D.PORTAL_LABEL)) + ' >> ' + LTRIM(RTRIM(C.PORTAL_LABEL))
END as NAVIGATION,
K.DISPLAYONLY
FROM PSROLECLASS A
LEFT OUTER JOIN PSPRSMPERM B ON A.CLASSID = B.PORTAL_PERMNAME AND B.PORTAL_NAME = 'EMPLOYEE'AND B.PORTAL_REFTYPE = 'C'
LEFT OUTER JOIN PSPRSMDEFN C ON B.PORTAL_NAME = C.PORTAL_NAME AND B.PORTAL_REFTYPE = C.PORTAL_REFTYPE AND B.PORTAL_OBJNAME = C.PORTAL_OBJNAME
LEFT OUTER JOIN PSPRSMDEFN D ON D.PORTAL_NAME = C.PORTAL_NAME AND D.PORTAL_OBJNAME = C.PORTAL_PRNTOBJNAME
LEFT OUTER JOIN PSPRSMDEFN E ON E.PORTAL_NAME = D.PORTAL_NAME AND E.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME
LEFT OUTER JOIN PSPRSMDEFN F ON F.PORTAL_NAME = E.PORTAL_NAME AND F.PORTAL_OBJNAME = E.PORTAL_PRNTOBJNAME
LEFT OUTER JOIN PSPRSMDEFN G ON G.PORTAL_NAME = F.PORTAL_NAME AND G.PORTAL_OBJNAME = F.PORTAL_PRNTOBJNAME
LEFT OUTER JOIN PSPRSMDEFN H ON H.PORTAL_NAME = G.PORTAL_NAME AND H.PORTAL_OBJNAME = G.PORTAL_PRNTOBJNAME
LEFT OUTER JOIN PSPRSMDEFN I ON H.PORTAL_NAME = I.PORTAL_NAME AND H.PORTAL_OBJNAME = I.PORTAL_PRNTOBJNAME
LEFT OUTER JOIN PSPRSMDEFN J ON I.PORTAL_NAME = J.PORTAL_NAME AND I.PORTAL_OBJNAME = J.PORTAL_PRNTOBJNAME
INNER JOIN PSAUTHITEM K ON A.CLASSID = K.CLASSID
INNER JOIN PSMENUITEM L ON K.MENUNAME = L.MENUNAME AND K.BARNAME = L.BARNAME AND L.ITEMNAME = K.BARITEMNAME
INNER JOIN PSPNLGROUP M ON M.PNLGRPNAME = L.PNLGRPNAME AND M.MARKET = L.MARKET AND K.PNLITEMNAME = M.PNLNAME
INNER JOIN PSPNLDEFN N ON N.PNLNAME = M.PNLNAME
INNER JOIN PSPGEACCESSDESC O ON O.AUTHORIZEDACTIONS = K.AUTHORIZEDACTIONS
WHERE
A.ROLENAME = :1

Thanks
Vandana