SELECT c.*,
nv.*,
PGST.*,
PRCS1.*
FROM
(SELECT pnlgrpname AS Kpnlgrpname,
pnlname AS kpnlname,
recname AS key_recname,
Record_used_no_times
FROM
(SELECT pnlgrpname,
pnlname,
recname,
count1 as Record_used_no_times,
row_number() OVER(partition BY pnlgrpname, pnlname order by pnlgrpname, pnlname, count1 DESC) AS rank1
FROM
(SELECT DISTINCT a.pnlgrpname,
b.pnlname,
b.recname AS recname,
COUNT(1) OVER(partition BY a.pnlgrpname, b.pnlname, b.recname order by a.pnlgrpname) AS count1
FROM pspnlgroup a,
pspnlfield b
WHERE a.pnlname = b.pnlname
AND a.pnlgrpname <> ' '
)
)
WHERE rank1 = 1
) C ,
(SELECT SYS_CONNECT_BY_PATH(A.PORTAL_LABEL, '->') navigation,
'/EMPLOYEE/ERP/c/'
|| PORTAL_URI_SEG1
|| '.'
|| PORTAL_URI_SEG2
|| '.'
|| PORTAL_URI_SEG3 url,
PORTAL_URI_SEG2,
DESCR254
FROM
(SELECT DISTINCT A.PORTAL_NAME,
A.PORTAL_LABEL,
A.PORTAL_OBJNAME,
a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1,
A.PORTAL_URI_SEG2,
A.PORTAL_URI_SEG3,
A.PORTAL_REFTYPE,
A.DESCR254
FROM PSPRSMDEFN A
WHERE portal_name = 'EMPLOYEE'
) A
START WITH A.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY nocycle prior A.portal_objname = A.portal_prntobjname
) nv ,
(SELECT PNLGRPNAME,
rtrim(MAX(SYS_CONNECT_BY_PATH(Page_label_String,'->')),' ') AS Page_label_String
FROM
(SELECT PNLNAME,
REPLACE(ITEMLABEL,'&','')
||DECODE(hidden,1,'(HIDDEN)') AS Page_label_String,
PNLGRPNAME,
row_number() over(partition BY PNLGRPNAME order by subitemnum) rn
FROM PSPNLGROUP
) PGLBL
START WITH rn = 1
CONNECT BY prior rn = rn - 1
AND prior PNLGRPNAME =PNLGRPNAME
GROUP BY PNLGRPNAME
) PGST ,(
SELECT prcs.prcsname,
prcs.prcstype,
prcs.descr,
prcs.descrlong,
PNLGRPNAME
FROM ps_prcsdefnpnl pr,
ps_prcsdefn prcs
WHERE prcs.prcsname=pr.prcsname
AND pr.prcstype =prcs.prcstype
) PRCS1
WHERE nv.PORTAL_URI_SEG2=c.Kpnlgrpname(+)
—AND upper(navigation) LIKE upper('%PM WorkBench%')
AND PGST.pnlgrpname(+) = c.Kpnlgrpname
and PRCS1.pnlgrpname(+)=nv.PORTAL_URI_SEG2
order by navigation