SELECT a.PORTAL_NAME,
f.PORTAL_LABEL AS
parent5_folder,
e.PORTAL_LABEL AS
parent4_folder,
d.PORTAL_LABEL AS
parent3_folder,
c.PORTAL_LABEL AS
parent2_folder,
b.PORTAL_LABEL AS parent_folder,
a.PORTAL_LABEL AS
component
FROM PSPRSMDEFN a
LEFT JOIN PSPRSMDEFN b ON b.PORTAL_NAME = a.PORTAL_NAME
AND b.PORTAL_OBJNAME =
a.PORTAL_PRNTOBJNAME
LEFT JOIN PSPRSMDEFN c ON c.PORTAL_NAME = b.PORTAL_NAME
AND c.PORTAL_OBJNAME = b.PORTAL_PRNTOBJNAME
LEFT JOIN PSPRSMDEFN d ON d.PORTAL_NAME = c.PORTAL_NAME
AND d.PORTAL_OBJNAME = c.PORTAL_PRNTOBJNAME
LEFT JOIN PSPRSMDEFN e ON e.PORTAL_NAME = d.PORTAL_NAME
AND e.PORTAL_OBJNAME = d.PORTAL_PRNTOBJNAME
LEFT JOIN PSPRSMDEFN f ON f.PORTAL_NAME = e.PORTAL_NAME
AND f.PORTAL_OBJNAME =
e.PORTAL_PRNTOBJNAME
WHERE a.PORTAL_REFTYPE = 'C'
AND a.PORTAL_URI_SEG2 =
'JOB_DATA' /* This is component name */
Query 2
SELECT PORTAL_NAME,
PORTAL_OBJNAME AS CONTENT_REFERENCE,
PORTAL_LABEL,
PORTAL_URI_SEG1 AS MENU,
PORTAL_URI_SEG2 AS COMPONENT,
PORTAL_URI_SEG3 AS MARKET
FROM PSPRSMDEFN
WHERE PORTAL_NAME = 'EMPLOYEE' /*Folder name */
AND PORTAL_URI_SEG2 = 'ENV_EMP_CMP1' /*component name */
To get the
Absolute Navigation path.
PeopleTools – Portal – View Menu Item Details –give portal
label (got from above query) and search...
Query 3 :- Need to change something (I don’t know what to do)
SELECT l0.PORTAL_LABEL + ' > ' + l1.PORTAL_LABEL + ' > ' + l2.PORTAL_LABEL + ' > ' +
l3.PORTAL_LABEL PATH_TO_COMPONENT
FROM
PSPRSMDEFN l3
,
PSPRSMDEFN l2
,
PSPRSMDEFN l1
,
PSPRSMDEFN l0
WHERE
l3.PORTAL_PRNTOBJNAME =
l2.PORTAL_OBJNAME
AND l2.PORTAL_PRNTOBJNAME =
l1.PORTAL_OBJNAME
AND l1.PORTAL_PRNTOBJNAME =
l0.PORTAL_OBJNAME
AND l3.PORTAL_NAME = l2.PORTAL_NAME
AND l2.PORTAL_NAME = l1.PORTAL_NAME
AND l1.PORTAL_NAME = l0.PORTAL_NAME
l3.PORTAL_URI_SEG2
in (SELECT DISTINCT
PAGE.PNLGRPNAME
FROM
PSMENUDEFN M,
PSMENUITEM ITEM,
PS_PRCSDEFNPNL PAGE,
PS_PRCSDEFN PRCS
WHERE M.MENUNAME = ITEM.MENUNAME
AND
ITEM.PNLGRPNAME =
PAGE.PNLGRPNAME
AND
PAGE.PRCSTYPE =
PRCS.PRCSTYPE
AND
PAGE.PRCSNAME =
PRCS.PRCSNAME AND
PRCS.PRCSNAME =
'BAS005A')
Query 4 :-
SELECT l0.PORTAL_LABEL + ' > ' + l1.PORTAL_LABEL + ' > ' + l2.PORTAL_LABEL + ' > ' + l3.PORTAL_LABEL PATH_TO_COMPONENT
FROM PSPRSMDEFN l3
, PSPRSMDEFN l2
, PSPRSMDEFN l1
, PSPRSMDEFN l0
WHERE
l3.PORTAL_PRNTOBJNAME =
l2.PORTAL_OBJNAME
AND l2.PORTAL_PRNTOBJNAME = l1.PORTAL_OBJNAME
AND l1.PORTAL_PRNTOBJNAME = l0.PORTAL_OBJNAME
AND l3.PORTAL_NAME = l2.PORTAL_NAME
AND l2.PORTAL_NAME = l1.PORTAL_NAME
AND l1.PORTAL_NAME = l0.PORTAL_NAME
AND l3.PORTAL_URI_SEG2 in('JOB_DATA')
Here is a version I wrote in PSQUERY for HR 9.0, it assumes no more than 6 levels deep of menus.
ReplyDeleteSELECT E.PRCSTYPE, E.PRCSNAME, F.DESCR, DECODE( H.PORTAL_LABEL,' ','','','','Root','Root ','Root > ' || H.PORTAL_LABEL) || ' > ' || G.PORTAL_LABEL || ' > ' || C.PORTAL_LABEL || ' > ' || B.PORTAL_LABEL || ' > ' || A.PORTAL_LABEL || ' > ' || D.PORTAL_LABEL , D.DESCR254, E.PNLGRPNAME,D.PORTAL_NAME,D.PORTAL_REFTYPE,D.PORTAL_OBJNAME,F.PRCSTYPE,F.PRCSNAME
FROM PSPRSMDEFN D, PS_PRCSDEFNPNL E, PS_PRCSDEFN F, PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN G, PSPRSMDEFN H
WHERE E.PNLGRPNAME = D.PORTAL_URI_SEG2
AND E.PRCSTYPE = F.PRCSTYPE
AND E.PRCSNAME = F.PRCSNAME
AND D.PORTAL_NAME = 'EMPLOYEE'
AND D.PORTAL_REFTYPE = 'C'
AND E.PNLGRPNAME > ' '
AND A.PORTAL_NAME = 'EMPLOYEE'
AND A.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME
AND B.PORTAL_NAME = 'EMPLOYEE'
AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME
AND C.PORTAL_NAME (+) = 'EMPLOYEE'
AND C.PORTAL_OBJNAME(+) = B.PORTAL_PRNTOBJNAME
AND G.PORTAL_NAME(+) = 'EMPLOYEE'
AND G.PORTAL_OBJNAME(+) = C.PORTAL_PRNTOBJNAME
AND H.PORTAL_NAME(+) = 'EMPLOYEE'
AND H.PORTAL_OBJNAME(+) = G.PORTAL_PRNTOBJNAME
ORDER BY 1, 2, 4
Wonderful Mike! This query pulls all required information into single ouput.
DeleteThanks Ismail for all important information on your blog.