Clarity PPM

Expand all | Collapse all

Query to find the portlet configured under which Menu Manager

Jump to Best Answer
  • 1.  Query to find the portlet configured under which Menu Manager

    Posted 12-01-2017 10:41 AM

     

    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???



  • 2.  Re: Query to find the portlet configured under which Menu Manager
    Best Answer

    Posted 12-01-2017 04:21 PM

    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