Skip to content

Instantly share code, notes, and snippets.

@nvg

nvg/component_search.sql

Created June 19, 2017 17:58
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save nvg/59ac3e4ee8bd97f579f6304a3a0df7eb to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save nvg/59ac3e4ee8bd97f579f6304a3a0df7eb to your computer and use it in GitHub Desktop.
Download ZIP
peoplecode: sql: component search
Raw
component_search.sql
---- Menu Look-up
SELECT SRCH.PORTAL_LABEL LABEL
, NVL(SRCH.PORTAL_URI_SEG2, ' ') COMPNOENT
, SRCH.PORTAL_OBJNAME
--L1.PORTAL_LABEL, L2.PORTAL_LABEL, L3.PORTAL_LABEL,
--L4.PORTAL_LABEL, L5.PORTAL_LABEL, L6.PORTAL_LABEL
,'Main Menu > ' || L1.PORTAL_LABEL || decode(L1.PORTAL_REFTYPE, 'F', ' > ')
|| L2.PORTAL_LABEL || DECODE(L2.PORTAL_REFTYPE, 'F', ' > ')
|| L3.PORTAL_LABEL || DECODE(L3.PORTAL_REFTYPE, 'F', ' > ')
|| L4.PORTAL_LABEL || DECODE(L4.PORTAL_REFTYPE, 'F', ' > ')
|| L5.PORTAL_LABEL || DECODE(L5.PORTAL_REFTYPE, 'F', ' > ')
|| L6.PORTAL_LABEL NAV_PATH,
decode(SRCH.PORTAL_REFTYPE, 'F', ' ', 'C', SRCH.DESCR254) DESCR
FROM PSPRSMDEFN L1
LEFT JOIN PSPRSMDEFN L2
ON L2.PORTAL_NAME =L1.PORTAL_NAME
AND L2.PORTAL_PRNTOBJNAME = L1.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN L3
ON L3.PORTAL_NAME =L2.PORTAL_NAME
AND L3.PORTAL_PRNTOBJNAME = L2.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN L4
ON L4.PORTAL_NAME =L3.PORTAL_NAME
AND L4.PORTAL_PRNTOBJNAME = L3.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN L5
ON L5.PORTAL_NAME =L4.PORTAL_NAME
AND L5.PORTAL_PRNTOBJNAME = L4.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN L6
ON L6.PORTAL_NAME =L5.PORTAL_NAME
AND L6.PORTAL_PRNTOBJNAME = L5.PORTAL_OBJNAME
LEFT JOIN PSPRSMDEFN SRCH
ON SRCH.PORTAL_NAME = 'EMPLOYEE'
AND SRCH.PORTAL_OBJNAME
= NVL(L6.PORTAL_OBJNAME,NVL(L5.PORTAL_OBJNAME,NVL(L4.PORTAL_OBJNAME,
NVL(L3.PORTAL_OBJNAME,(NVL(L2.PORTAL_OBJNAME,NVL(L1.PORTAL_OBJNAME,' ')))))))
WHERE L1.PORTAL_NAME ='EMPLOYEE' AND L1.PORTAL_PRNTOBJNAME = 'PORTAL_ROOT_OBJECT'
--*************************************************************************************--
-- Search for Items
--*************************************************************************************--
--AND SRCH.PORTAL_URI_SEG2 = 'HCR_SM_SEARCH' -- Component
--AND SRCH.PORTAL_LABEL LIKE 'PeopleTools%' -- Label
--AND L1.PORTAL_OBJNAME = 'PT_PEOPLETOOLS' -- Folder/Ref Object Name
ORDER BY L1.PORTAL_REFTYPE DESC, L1.PORTAL_LABEL,
L2.PORTAL_REFTYPE DESC, L2.PORTAL_LABEL, L3.PORTAL_REFTYPE DESC, L3.PORTAL_LABEL,
L4.PORTAL_REFTYPE DESC, L4.PORTAL_LABEL, L5.PORTAL_REFTYPE DESC, L5.PORTAL_LABEL,
L6.PORTAL_REFTYPE DESC, L6.PORTAL_LABEL ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment