SQL for Retrieving Portal, Security, Record and Navigation

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

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, (
SELECT
CASE
WHEN ltrim(rtrim(descr)) = ''
THEN
ltrim(rtrim(menulabel))
ELSE
ltrim(rtrim(descr))
END
FROM psmenudefn
WHERE menuname = b.menuname)
|| ' > '
|| ltrim(rtrim(f.portal_label)) AS path
FROM psroleclass a,
psauthitem b,
pspgeaccessdesc c,
pspnlgroup d,
psmenuitem e,
psprsmdefn f,
psprsmperm g
WHERE a.classid = b.classid
AND d.pnlgrpname = e.pnlgrpname
AND b.menuname = e.menuname
AND b.barname = e.barname
AND 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 PNLNAME
FROM PSFSSYS.PSPNLFIELD
where RECNAME ='PYMNT_ADVICE')
AND NOT EXISTS
(
SELECT 'x'
FROM psprsmsysattrvl
WHERE portal_name = f.portal_name
AND portal_reftype = f.portal_reftype
AND portal_objname = f.portal_objname
AND portal_attr_nam = 'PORTAL_HIDE_FROM_NAV'
AND f.portal_objname NOT IN ('CO_NAVIGATION_COLLECTIONS',
'PORTAL_BASE_DATA'))
AND b.authorizedactions = c.authorizedactions
AND f.portal_reftype = 'C'
AND f.portal_cref_usgt = 'TARG'
AND f.portal_name = g.portal_name
AND f.portal_reftype = g.portal_reftype
AND f.portal_objname = g.portal_objname
AND a.classid = g.portal_permname
AND b.classid = g.portal_permname
AND 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.menuname
AND b.pnlitemname = d.itemname
WITH UR;

I really want to know what you think