Query to find the portlet configured under which Menu Manager

[SiddiqSyed]

 

I was trying to build a query to fetch the details of portlet where it is configured by passing portlet code. i.e.,to find Portlet Pages and Menu Manager details.

The below query help to get the portlet page details by using Portlet code.

Query to get Portlet Internal ID

SELECT *   --ID, PORTLET_CODE, IS_ACTIVE, PORTLET_TYPE_CODE 
FROM CLARITY.CMN_PORTLETS

WHERE PORTLET_CODE IN (
'PORTLET_CODE');                                 --ID 5102073

 

Query to get Page Internal ID

SELECT *   --ID, PAGE_ID, PORTLET_ID, PORTLET_TITLE_TABLE 
FROM CLARITY.CMN_PAGE_PORTLETS 
WHERE PORTLET_ID IN (
'5102073');                                                     --page id 5063137

 

Query to get Page details

SELECT *--ID, PAGE_CODE, PAGE_TYPE_CODE, PARENT_PAGE_ID, SPACE_CODE 
FROM CLARITY.CMN_PAGES
WHERE ID IN (
'5063137');

 

Could you please help me to built a Query to find MenuID or MenuItemID???

[Senthil Kumar Lakshmanan]

Here is the query.

 

SELECT portlet.id id, portlet.portlet_code code, cc.name name, cc.description description
        FROM cmn_portlets portlet, cmn_captions_nls cc
        WHERE portlet.portlet_type_code = 'SYSTEM'
        AND portlet.portlet_view_type_code = 'toc'
        AND portlet.id = cc.pk_id
        AND cc.table_name = 'CMN_PORTLETS'
        AND cc.language_code = 'en'  order by name

 

 

SELECT DISTINCT action.action_code code, ac.name
        FROM cmn_menu_items mi, cmn_actions action, cmn_captions_nls ac
        WHERE mi.container_id = 403
        AND NVL(mi.is_hidden, 0 ) = 0
        AND mi.action_id = action.id
        AND NVL(action.page_id, 0 ) = 0
        AND NVL(action.object_action_id, 0 ) = 0
        AND ac.pk_id = action.id
        AND ac.table_name = 'CMN_ACTIONS'
        AND ac.language_code = 'en'

 

SELECT DISTINCT action.action_code code, ac.name
        FROM cmn_menu_items mi, cmn_actions action, cmn_captions_nls ac
        WHERE mi.container_id = 405
        AND NVL(mi.is_hidden, 0 ) = 0
        AND mi.action_id = action.id
        AND NVL(action.page_id, 0 ) = 0
        AND NVL(action.object_action_id, 0 ) = 0
        AND ac.pk_id = action.id
        AND ac.table_name = 'CMN_ACTIONS'
        AND ac.language_code = 'en'

 

Thanks

Senthil