SQL for Retrieving Portal, Security, Record and Navigation

SELECT DISTINCT       ltrim (rtrim (a.classid))  AS classid, ltrim (rtrim (b.menuname))  AS       menuname, ltrim (rtrim (b.barname))  AS barname, ltrim          (rtrim (b.baritemname))  AS baritemname, ltrim(rtrim(d.pnlname))  AS       pnlname, ltrim (rtrim (d.itemlabel))  AS itemlabel, ltrim          (rtrim (e.itemlabel))  AS itemlabel, ltrim(rtrim(c.pageaccessdescr))         AS pageaccessdescr, b.displayonly, ltrim (rtrim (f.portal_label))  AS       portal_label, ltrim (rtrim (f.portal_objname))  AS portal_objname,       ltrim(rtrim(f.portal_uri_seg1))  AS portal_uri_seg1, ltrim (rtrim (f.portal_uri_seg2)       )  AS portal_uri_seg2, (SELECTCASE          WHEN ltrim(rtrim(descr)) = ''THEN             ltrim(rtrim(menulabel))ELSE             ltrim(rtrim(descr))ENDFROM psmenudefnWHERE menuname = b.menuname)       || ' > '       || ltrim(rtrim(f.portal_label))  AS pathFROM psroleclass a,       psauthitem b,       pspgeaccessdesc c,       pspnlgroup d,       psmenuitem e,       psprsmdefn f,       psprsmperm gWHERE a.classid = b.classidAND d.pnlgrpname = e.pnlgrpnameAND b.menuname = e.menunameAND b.barname = e.barnameAND b.baritemname = e.itemname--AND a.rolename LIKE 'GLIC_PO%'AND b.menuname NOT IN ('APPLICATION_DESIGNER',                              'CLIENTPROCESS',                              'DATA_MOVER ',                              'IMPORT_MANAGER ',                              'OBJECT_SECURITY ',                              'QUERY ',                              'PERFMONPPMI ')AND b.menuname NOT LIKE 'WEBLIB%'

AND B.PNLITEMNAME IN (SELECT DISTINCT PNLNAMEFROM PSFSSYS.PSPNLFIELDwhere RECNAME ='PYMNT_ADVICE')AND NOT EXISTS             (SELECT 'x'FROM psprsmsysattrvlWHERE portal_name = f.portal_nameAND portal_reftype = f.portal_reftypeAND portal_objname = f.portal_objnameAND portal_attr_nam = 'PORTAL_HIDE_FROM_NAV'AND f.portal_objname NOT IN ('CO_NAVIGATION_COLLECTIONS',                                                  'PORTAL_BASE_DATA'))AND b.authorizedactions = c.authorizedactionsAND f.portal_reftype = 'C'AND f.portal_cref_usgt = 'TARG'AND f.portal_name = g.portal_nameAND f.portal_reftype = g.portal_reftypeAND f.portal_objname = g.portal_objnameAND a.classid = g.portal_permnameAND b.classid = g.portal_permnameAND f.portal_name = 'EMPLOYEE'AND f.portal_uri_seg1 <> ' 'AND f.portal_uri_seg2 <> ' 'AND f.portal_uri_seg3 <> ' 'AND f.portal_uri_seg1 = b.menunameAND b.pnlitemname = d.itemname  WITH UR;