Here are some good SQL's to have in your library.
When a process name is known:
SELECT DISTINCT
PRCS.PRCSTYPE,
PRCS.PRCSNAME,
PRCS.DESCR,
PAGE.PNLGRPNAME as Component,
'Home > ' || RTRIM(M.MENUGROUP) || ' > ' || RTRIM(M.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as Location
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 = 'PY_PULL_COST'
When a component name is known:
SELECT DISTINCT PRCS.PRCSTYPE , PRCS.PRCSNAME , PRCS.DESCR ,
PAGE.PNLGRPNAME as Component , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PS_PRCSDEFNPNL PAGE , PS_PRCSDEFN PRCS
WHERE MENU.MENUNAME = ITEM.MENUNAMEAND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PAGE.PNLGRPNAME = 'component-name'
When a record name is known:
SELECT DISTINCT PFLD.RECNAME ,
PFLD.PNLNAME as Page , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP , PSPNLFIELD PFLD
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = PFLD.PNLNAME
AND PFLD.RECNAME = 'record-name'
When a page name is known:
SELECT DISTINCT COMP.PNLNAME as Page , 'Home > ' ||
RTRIM(MENU.MENUGROUP) || ' > ' || RTRIM(MENU.MENULABEL) || ' > ' ||
RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = 'page-name'
6 comments:
You might need to add a || for concat.
When a component name is known:
SELECT DISTINCT PRCS.PRCSTYPE , PRCS.PRCSNAME , PRCS.DESCR ,
PAGE.PNLGRPNAME as Component , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PS_PRCSDEFNPNL PAGE , PS_PRCSDEFN PRCS
WHERE MENU.MENUNAME = ITEM.MENUNAMEAND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PAGE.PNLGRPNAME = 'component-name'
When a record name is known:
SELECT DISTINCT
PFLD.RECNAME,
PFLD.PNLNAME as Page,
'Home > ' || RTRIM(MEN.MENUGROUP) || ' > ' || RTRIM(MEN.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MEN,
PSMENUITEM ITEM,
PSPNLGROUP COMP,
PSPNLFIELD PFLD
WHERE MEN.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = PFLD.PNLNAME
AND PFLD.RECNAME = 'CONTRACT_DATA'
select rtrim(sysdate) from dual
select * from PSMENUDEFN
When a page name is known:
SELECT DISTINCT COMP.PNLNAME as Page , 'Home > ' ||
RTRIM(MENU.MENUGROUP) || ' > ' || RTRIM(MENU.MENULABEL) || ' > ' ||
RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = 'page-name'
Hi jeromy,
can i post this in our tips and tricks for our team? This is very useful..
ronie
Good post. If you want to remove all the &'s from the path names, you could substitute RTRIM(column_name) with REPLACE(RTRIM(column_name),'&','').
Ex.
BEFORE: Home > Manage Student &Records > Track &Student Careers > &Use > Student &Program/Plan
AFTER: Home > Manage Student Records > Track Student Careers > Use > Student Program/Plan
Occasionally, that function would remove an & that you would actually want (like the one in 'Process Interest & Payment'). But, that would be rare.
Yes, you can freely use this code.
Jeromy, you seem to be very good with this stuff.
I need to replicate the following SQL in the PeoplTools Query tool so my users can run it. I am having trouble defining it. I know I need to use an expression, but is is not going well. Can you help. Feel free to call me at 720-913-4931. Thank you. Bonnie
select X.OPRCLASS, count(*) from PS_SEC_BU_CLS X
where X.OPRCLASS like ('D8%')
having count(*) > '1'
group by X.OPRCLASS
Post a Comment