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