version 4
select NV.navigation,
NV.DESCR254 as Navigation_Descr,
a.PNLGRPNAME,
replace(a.Page_label_String, '&', '') as Page_label_in_Component,
chr(39) || 'Panel Name ' || b.PNLNAME || ' Record names#' ||
Replace(RECNAME_STRING, '>', chr(13)) as Records_in_pages,
key_recname as Key_Record_in_a_page,
Replace(RECNAME_FIELD_STRING, '>', chr(13)),
Replace(b.LBLTEXT_STRING, '>', chr(13)) as Page_label_String,
PRCSDETAIL,
QRYNAME
FROM (SELECT PNLNAME, ITEMLABEL as Page_label_String, PNLGRPNAME
FROM PSPNLGROUP) A,
(SELECT PNLNAME,
LTRIM(MAX(SYS_CONNECT_BY_PATH(RECNAME, '>')), '>') RECNAME_STRING,
LTRIM(MAX(SYS_CONNECT_BY_PATH(RECNAME || '.' || FIELDNAME,
'>')),
'>') RECNAME_FIELD_STRING,
LTRIM(MAX(SYS_CONNECT_BY_PATH(Replace(LBLTEXT, '>', ' '), '>')),
'>') LBLTEXT_STRING
FROM (SELECT distinct PNLNAME,
RECNAME,
FIELDNAME,
LBLTEXT,
fieldtype,
row_number() OVER(PARTITION BY PNLNAME ORDER BY fieldtop, fieldleft) rn
FROM (select distinct PNLNAME,
RECNAME,
FIELDNAME,
LBLTEXT,
fieldtype,
fieldtop,
fieldleft
from PSPNLFIELD
where RECNAME <> ' '
and pnlname in
(select distinct pnlname
from pspnlfield
group by pnlname
having sum(length(LBLTEXT || '>')) < 4000)
and pnlname in
(select distinct pnlname
from pspnlfield
group by pnlname
having sum(length(RECNAME || '.' || FIELDNAME || '>')) < 4000)))
CONNECT BY nocycle PNLNAME = PRIOR PNLNAME
AND rn = PRIOR rn + 1
START WITH rn = 1
GROUP BY PNLNAME) B,
(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'
and portal_objname <> portal_prntobjname
and not exists
(select 'x'
from PSPRSMSYSATTRVL
where portal_name = A.PORTAL_NAME
AND portal_Reftype = A.PORTAL_REFTYPE
and portal_objname = A.PORTAL_OBJNAME
and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
and A.PORTAL_OBJNAME not in
('CO_NAVIGATION_COLLECTIONS',
'PORTAL_BASE_DATA'))) A
start with A.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY nocycle prior A.portal_objname = A.portal_prntobjname) nv,
(select pnlgrpname as Kpnlgrpname,
pnlname as kpnlname,
recname as key_recname
from (select pnlgrpname,
pnlname,
recname,
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, pskeydefn d
where a.pnlname = b.pnlname
and b.recname = d.recname
and b.fieldname = d.fieldname
and b.fieldname = d.fieldname
and a.pnlgrpname <> ' '
AND D.INDEXID = '_'))
where rank1 = 1) C,
(select PNLGRPNAME,
max(replace(sys_connect_by_path(replace(PRCSDETAIL, '#', ' '),
'#'),
'#',
chr(13))) as PRCSDETAIL
from (SELECT B.PNLGRPNAME,
A.PRCSNAME ||
decode(B.PNLGRPNAME,
'AE_REQUEST',
' ',
'-Type ' || A.PRCSTYPE || '-' ||
decode(B.PNLGRPNAME, 'AE_REQUEST', ' ', A.DESCR)) as PRCSDETAIL,
row_number() over(partition by B.PNLGRPNAME order by b.PRCSNAME) rn
FROM PS_PRCSDEFN A, PS_PRCSDEFNPNL B
WHERE A.PRCSTYPE = B.PRCSTYPE
AND A.PRCSNAME = B.PRCSNAME)
where 1 = 1
start with rn = 1
connect by prior rn = rn - 1
and prior PNLGRPNAME = PNLGRPNAME
group by PNLGRPNAME) P,
(select recname,
Replace(LTRIM(MAX(SYS_CONNECT_BY_PATH(Replace(QRYNAME,
'>',
' '),
'>')),
'>'),
'>',
chr(13)) as QRYNAME
from (select recname,
QRYNAME,
row_number() over(partition by recname order by recname) rn
from (SELECT DISTINCT r.recname,
Decode(r.oprid,
' ',
'PUBLIC',
'Oprid ' || r.oprid) || ' # ' ||
r.QRYNAME || ':-' || d.descr as QRYNAME
FROM PSQRYRECORD R, PSQRYDEFN D
where R.QRYNAME = D.QRYNAME
AND R.OPRID = D.OPRID
GROUP BY r.QRYNAME, r.OPRID, r.recname, d.descr))
start with rn = 1
connect by prior rn = rn - 1
and prior recname = recname
and rn < 50
group by recname) Q
WHERE NV.PORTAL_URI_SEG2 = A.PNLGRPNAME
and A.PNLGRPNAME <> ' '
and a.PNLGRPNAME = c.Kpnlgrpname
and a.PNLGRPNAME = p.pnlgrpname(+)
and b.pnlname = C.kpnlname
AND C.key_recname = q.recname(+)
and b.pnlname in (SELECT distinct pnlname
FROM PSPNLFIELD
START WITH PNLNAME = a.PNLNAME
CONNECT BY nocycle PRIOR SUBPNLNAME = PNLNAME
and PRIOR FIELDTYPE = 11)
AND (upper(NV.navigation) LIKE upper('%payab%') and
NV.navigation not like '%Portal Objects%')
order by NV.navigation, Page_label_in_Component