Clarity PPM

Expand all | Collapse all

TIP : Documenting your Configuration (via SQL)

frean0411-27-2017 10:48 AM

  • 1.  TIP : Documenting your Configuration (via SQL)

    Posted 08-02-2011 10:45 AM

    (I just posted part of this SQL on a 'normal' thread; and it occurred to me that this is quite useful and is perhaps deserving of "TIP status" on its own)

    Do you ever find yourself having to document your Clarity configuration? You know how "they" like documenting everything and "we" all know its a pain, the system documents itself doesn't it??? :wacko:

    Anyway, to save yourself having to revisit your OBJECT and VIEW setup in Clarity studio and slavishly copy or cut/paste all the settings that you have made in studio into a document (to keep on a shelf somewhere), try running this sort of SQL instead;

    select ( select name from cmn_captions_nls where language_code = 'en'   
    and table_name = 'ODF_CUSTOM_ATTRIBUTES'   and pk_id = A.id ) AS "Attribute"
    ,( select description from cmn_captions_nls where language_code = 'en'  
    and table_name = 'ODF_CUSTOM_ATTRIBUTES'   and pk_id = A.id ) AS "Description"
    , initcap(data_type) as "Data Type" , default_value AS "Default" 
    , internal_name AS "Database Column / Attribute ID"
    , decode(lookup_type,null,'','Lookup name '''||
    (select C.name from   cmn_captions_nls C , cmn_lookup_types L
    where L.lookup_type = A.lookup_type and C.pk_id = L.id
    and language_code = 'en' and table_name = 'CMN_LOOKUP_TYPES')||' / '||(lookup_type)||''' ')
    || decode(A.is_editable,0,'Read-only "Checked" ',null)
    || decode(A.extended_type,'lookup',null
    ,decode(A.data_type,'string','Maximum Size: '||data_size||' ',null))
    || decode(A.extended_type,'lookup',null
    ,decode(A.data_type,'number','Decimal places: '||scale,null)) AS "Miscellaneous"
    from odf_custom_attributes A where object_name = 'xxxxxxxx'
    and internal_name != 'partition_code'
    order by A.created_date asc

    Replacing 'xxxxxxxx' with an object name, for example 'project'

    For VIEW configurations, I use the following;

    select ovs.label As "Section"
    , VAT.name  AS "Property Label"
    ,decode(ova.col,1,'Left',2,'Right',ova.col) As "Column"
    ,attribute_code||DECODE(CAT.name,null,null,' ('||CAT.name||')') as "Attribute"
    ,decode(widget_type,'text','String','textarea','String','browse','Lookup','select','Lookup','datepicker','Date','checkbox','Boolean',widget_type) As "Data Type"
    ,decode(widget_type,'text','Text Entry','textarea','Text Entry','browse','Browse','select','Pull-Down','datepicker','Date','checkbox','Check Box',widget_type) As "Display Type"
    ,decode(ova.is_required,'1','Y') As "Required"
    ,decode(ova.is_editable,'1','',0,'Y') As "Read-Only"
    ,CASE WHEN widget_type = 'checkbox'
    THEN decode(ova.default_value,0,'Unchecked','Checked')
    ELSE decode(ova.default_value,'date_today','Today','date_tomorrow','Tomorrow','userId','Current User','',''    
    ,'Value '''||ova.default_value||''' from lookup') END  AS "Default"
    FROM odf_views ov
    JOIN odf_objects oo on ( oo.code = ov.object_code )
    JOIN odf_view_attributes ova on ( ova.view_id = ov.id )
    LEFT JOIN cmn_captions_nls VAT ON ( ova.label_pk_id=VAT.pk_id and VAT.language_code = 'en'   
    and VAT.table_name='ODF_VIEW_ATTRIBUTES' )
    LEFT JOIN odf_custom_attributes oca on ( oca.object_name = oo.code    
    and oca.internal_name = ova.attribute_code )
    LEFT JOIN cmn_captions_nls CAT ON ( CAT.table_name = 'ODF_CUSTOM_ATTRIBUTES'     
    and CAT.pk_id = oca.id and CAT.language_code = 'en' ) 
    LEFT JOIN odf_view_sections ovs on ( ovs.id=ova.section_id )
    WHERE ov.code = 'yyyyyyyyy'
    and hidden != 1
    and section_id != -1
    ORDER BY ova.section_id , ova.col , ova.display_order asc

    Replacing 'yyyyyyyyy' with the view "ID" that you see on the View/Page screen (or Subpage screen for edit views) - for example 'projectGeneral'

    I don't think I'm saving you from all cut/pasting, but this does make the job a lot easier!

    (Of course you can extend this as you need, the above was all I needed at the time I worked it out - and its Oracle syntax, hard coded 'en', working OK for me in 8.1, YMMV etc)

    :bashful:

     

    Message was edited by: DAVID MORTON - just correcting some formatting in Jive



  • 2.  RE: TIP : Documenting your Configuration (via SQL)

    Posted 08-02-2011 03:11 PM
    Hey Dave thanks for jumping in and contributing to Tuesday Tips!

    Chris


  • 3.  RE: TIP : Documenting your Configuration (via SQL)

    Posted 08-02-2011 03:43 PM
    Well it is Tuesday. -_-


  • 4.  RE: TIP : Documenting your Configuration (via SQL)

    Posted 08-03-2011 12:08 PM
    This is good Dave ..
    Though it can be further refined, customized as per needs.. ( Like including partition info )

    - Sangeet


  • 5.  RE: TIP : Documenting your Configuration (via SQL)

    Posted 08-03-2011 12:22 PM
    Yeah, I don't use different partitions! :tongue


  • 6.  RE: TIP : Documenting your Configuration (via SQL)

    Posted 08-29-2011 06:00 AM
    This looks cool, but why not take a XOG output of the objects (need to query ODF_OBJECTS to get the list), then run an XSLT on that to get the format you want. That way new data types would appear without changing the script, you'd also get display mappings and views if you want to use them.

    If you only want the object definition then try the no_dependencies argument to the XOG. Also the nls_language argument will reduce the nls elements in views and pages (although an XSLT can remove these anyway).


  • 7.  RE: TIP : Documenting your Configuration (via SQL)

    Posted 08-29-2011 09:02 AM

    betda05 wrote:

    This looks cool, but why not take a XOG output of the objects (need to query ODF_OBJECTS to get the list), then run an XSLT on that to get the format you want..
    It would be nice if the product did that for us automatically wouldn't it (i.e. CA provided the XSLTs for us)? :tongue

    (and my SQL solution is only because I am a SQL-techy not an XSLT-techy! :*) )


  • 8.  RE: TIP : Documenting your Configuration (via SQL)

    Posted 08-30-2011 01:18 PM
    Good discussion and thanks for contributing, Dave!

    (I somehow missed this one when it was first pubished :sad )

    -shawn


  • 9.  RE: TIP : Documenting your Configuration (via SQL)

    Posted 08-31-2011 06:42 AM

    betda05 wrote:

    Also the nls_language argument will reduce the nls elements in views and pages
    Also could you explain that statement please? What version is this supported against and how do we use it*? Is it limited to VIEWS and PAGES (fairly useless then?) - Where is it documented (I can't see this in the manuals anywhere?)

    [ * - I have done a quick test (albeit in 8.1) and can't seem to get any joy ]

    If its works then you could also post the detail against this thread 37405558 (and contradict me totally :grin:!) which was asking about this sort of thing a little while ago.


  • 10.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 08-06-2014 02:23 AM

    Hi Dave,

     

    Awesome! These queries are really useful

     

    Regards,

    Georgy



  • 11.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 09-03-2014 01:44 PM

    agree that sounds great, if anyone gets bored, can you translate to SQL to help those of us who are not experts in coding?   



  • 12.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 09-08-2014 11:10 AM

    vtleogal2 wrote:

     

    agree that sounds great, if anyone gets bored, can you translate to SQL to help those of us who are not experts in coding? 

     

    Change the string concatenation || to + everywhere.

     

    Change the decodes to case statements everywhere;

     

    eg : decode(ova.is_required,'1','Y','N') As "Required"

    becomes : CASE ova.is_required WHEN '1' THEN 'Y' ELSE 'N' END As "Required"


    (I think that is it for this statement?)



  • 13.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 09-08-2014 06:00 PM

    Thank you.. I will  give a try at some point. I appreciate it!



  • 14.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 09-08-2014 04:18 PM

    Thanks Dave!

     

    You heading to CA World?



  • 15.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 09-09-2014 04:32 AM


  • 16.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 06-09-2015 12:20 PM

    I found the need to list out query attribute and portlet screen-names to document an XOG-query-read today, so I used this SQL to dump it out;

     

    select col.position , col.col_code , cmn.name ,col.col_type
    from
    cmn_portlets p
    JOIN cmn_grids gr ON gr.portlet_id = p.id
    JOIN cmn_grid_cols col ON col.grid_id = gr.id
    LEFT JOIN cmn_captions_nls cmn ON ( language_code = 'en' AND pk_id = col.id AND table_name = 'CMN_GRID_COLS' )
    where p.portlet_code = 'xxxxxxxxxxx'
    and gr.principal_type = 'SYSTEM'
    order by col.position


    --


    Obviously replace xxxxxxxxxxx with the relevant portlet id.

    Position -1 means that the column is not on the default list view for the portlet.



  • 17.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 06-10-2015 01:09 AM

    Thanks for sharing this, Dave

     

    NJ



  • 18.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 09-02-2015 11:50 AM

    Where is a lookup used?

     

    Some NSQL that finds it I think;

     

    SELECT @SELECT:DIM:USER_DEF:IMPLIED:OUTP:rownum:UQ_ID@,

           @SELECT:DIM_PROP:USER_DEF:IMPLIED:OUTP:type:type@,

           @SELECT:DIM_PROP:USER_DEF:IMPLIED:OUTP:name:name@,

           @SELECT:DIM_PROP:USER_DEF:IMPLIED:OUTP:id:id@,

           @SELECT:DIM_PROP:USER_DEF:IMPLIED:OUTP:attribute:attribute@

    FROM

    (SELECT 'Object' AS type,

            cap.name AS name,

            obj.code AS id,      

            cap2.name AS attribute

    FROM   cmn_captions_nls cap,

            odf_objects obj,

            odf_custom_attributes att,

            cmn_captions_nls cap2      

    WHERE  cap.table_name = 'ODF_OBJECTS'

    AND    cap.language_code = 'en'

    AND    cap.pk_id = obj.id

    AND    obj.code = att.object_name

    AND    att.lookup_type = @WHERE:PARAM:USER_DEF:STRING:LOOKUP@

    AND    att.is_active = 1

    AND    att.id = cap2.pk_id

    AND    cap2.table_name = 'ODF_CUSTOM_ATTRIBUTES'

    AND    cap2.language_code = 'en'

    --

    UNION

    --

    SELECT 'Lookup' AS type,

            cap.name AS lookup_name,

            lov.lookup_type_code AS lookup_id,

            att.column_name AS attribute_id

    FROM   cmn_attributes att,

            cmn_list_of_values lov,

            cmn_captions_nls cap

    WHERE  att.referenced_object_id = @WHERE:PARAM:USER_DEF:STRING:LOOKUP@

    AND    att.pk_id = lov.id

    AND    att.pk_id = cap.pk_id

    AND    cap.table_name = 'CMN_LOOKUP_TYPES'

    AND    cap.language_code = 'en'

    --

    UNION

    --

    SELECT 'Query' AS type,

            cap.name AS name,

            qry.query_code id,

            fil.filter_association AS attribute

    FROM   cmn_captions_nls cap,

            cmn_gg_nsql_queries qry,

            cmn_nsql_query_filters fil

    WHERE  cap.TABLE_NAME = 'CMN_GG_NSQL_QUERIES'

    AND    cap.LANGUAGE_CODE = 'en'

    AND    cap.PK_ID = qry.ID

    AND    qry.cmn_nsql_queries_id = fil.cmn_nsql_queries_id

    AND    fil.LOV = @WHERE:PARAM:USER_DEF:STRING:LOOKUP@

    --

    UNION

    --

    SELECT 'Report' || DECODE(def.is_active,0,' (inactive)') AS type ,

            cap.name AS name,

            def.job_code AS id,

            att.column_name AS attribute  -- parameter

    FROM   cmn_captions_nls cap,

            cmn_sch_job_definitions def,

            cmn_business_objects obj,

            cmn_attributes att

    WHERE  cap.table_name = 'CMN_SCH_JOB_DEFINITIONS'

    AND    cap.language_code = 'en'

    AND    cap.pk_id = def.id

    AND    def.id = obj.pk_id

    AND    obj.id = att.pk_id

    AND    att.referenced_object_id = @WHERE:PARAM:USER_DEF:STRING:LOOKUP@)

    WHERE @FILTER@

     

    Use the "Active lookups" lookup to populate that @WHERE:PARAM:USER_DEF:STRING:LOOKUP@ parameter



  • 19.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 09-02-2015 12:01 PM

    Brilliant

     

    You are awesome, Dave

     

    Regards,

    NJ



  • 20.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 03-15-2019 06:25 AM

    Hi, i want to get the lookup associated for all ootb attributes for all objects. 

    From the query written above:
    1st Union- i can get info for all custom attributes
    2nd Union- i can not get the object code and also not getting all OOTB attributes with this query. to get the object code, i joined this query with cmn_view_attributes/cmn_object_views/odf_objects. i am able to get object code but could not get all ootb attribute info.
    can I get help to get lookup info for all ootb attributes.



  • 21.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 11-24-2017 05:12 AM

    Hi Dave ,

     

    Thank you for your sharing Answers...

    How do we find the default values assigned to the Attributes in Portlet for the below query. Please advice.

     

    SELECT
    col.position,
    col.col_code,
    cmn.name,
    col.col_type
    FROM
    cmn_portlets p
    INNER JOIN cmn_grids gr ON gr.portlet_id = p.id
    INNER JOIN cmn_grid_cols col ON col.grid_id = gr.id AND col.position != -1
    INNER JOIN cmn_sec_users u ON u.id = gr.principal_id
    LEFT JOIN cmn_captions_nls cmn ON ( language_code = 'en' AND pk_id = col.id AND table_name = 'CMN_GRID_COLS' )
    WHERE p.portlet_code = 'pmo_ewo_hours'
    AND gr.principal_type = 'USER'
    and u.user_name='X663686'
    ORDER BY col.position



  • 22.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 11-24-2017 05:14 AM

    "Portlets" don't have default values (apart from in the filter), so not really sure what you are asking.

     

    For the default value of an attribute you would look at the object definition - I think the SQL above has default_value in it.



  • 23.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 11-24-2017 06:31 AM

    Hi David,

     

    Am asking filter only, How to find what are the Default values assigned in Attributes using the below query.

    SELECT
    col.position,
    col.col_code,
    cmn.name,
    col.col_type
    FROM
    cmn_portlets p
    INNER JOIN cmn_grids gr ON gr.portlet_id = p.id
    INNER JOIN cmn_grid_cols col ON col.grid_id = gr.id AND col.position != -1
    INNER JOIN cmn_sec_users u ON u.id = gr.principal_id
    LEFT JOIN cmn_captions_nls cmn ON ( language_code = 'en' AND pk_id = col.id AND table_name = 'CMN_GRID_COLS' )
    WHERE p.portlet_code = 'pmo_ewo_hours'
    AND gr.principal_type = 'USER'
    and u.user_name='X663686'
    ORDER BY col.position



  • 24.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 11-24-2017 03:58 PM

    That SQL is not going to help you at all - that SQL is showing grid layouts, the user join in the sql is trying to pickup user-configured layout for a grid portlet.

     

    Saved user filter-sets (which I think you are trying to find?) are nothing to do with the definition of a portlet, they are held in the tables CMN_ATTRIBUTE_VALUE_SETS and CMN_ATTRIBUTE_VALUES.



  • 25.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 11-27-2017 08:06 AM

    Hi David,

    Thanks...

    Yes I want to find saved user filter sets along with Portlet name, Username .Example Is it possible to find the below list

     

    Portname   Username  AttributeName   Default Values

        A              Muthu           Active                Yes



  • 26.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 11-27-2017 09:13 AM

    Yes, that is exactly what is held in those tables (CMN_ATTRIBUTE_VALUE_SETS and CMN_ATTRIBUTE_VALUES) - the default saved filter set is marked with is_default = 1.

     

    Simple values are easily read from the tables, lookups (eg OBS values) can be a bit trickier to determine - I don't have a "generic" piece of code that would work it out though since I have only ever looked at the data for some very specific values. Search in this community for those table names and you should find examples.



  • 27.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 11-28-2017 08:58 AM

    Hi David,

     

    We are getting the below columns username,Attribute code,default_values using this tables(CMN_ATTRIBUTE_VALUE_SETS and CMN_ATTRIBUTE_VALUES) but here am facing how to get the Portlet name/Portlet code using this tables

     

     

    Portletname  Username  AttributeName   Default Values

           A              Muthu           Active                Yes



  • 28.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 11-28-2017 09:22 AM

    Come on its not very hard if you look at the data - do a little investigation for yourself! (Set up some data in the application, look at the data in the database and spot the joins etc.)

     

    CMN_ATTRIBUTE_VALUE_SETS.VIEW_CODE = CMN_PORTLETS.ID

     

    (data types are different though so you will probably have to cast the join)



  • 29.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 07-22-2016 06:31 AM

    Was just poking around looking for configured OBJECT view details today, thought I'd save my queries;

     

    How many OBJECT configs have we got;

     

    select 
      g.code
    , count(*) ct
    --
    FROM CMN_SEC_USERS u
    JOIN CMN_GRIDS g ON
          ( u.id = g.PRINCIPAL_ID and g.PRINCIPAL_TYPE = 'USER'
            AND g.DAL_TYPE = 'object'
            AND g.DAL_PARTITION_CODE = 'NIKU.ROOT' )
    GROUP BY g.code
    

     

    Who has them ?  (projectList as example)

    select 
      g.code
    , U.user_name
    , g.created_date when_config
    --
    FROM CMN_SEC_USERS u
    JOIN CMN_GRIDS g ON
          ( u.id = g.PRINCIPAL_ID and g.PRINCIPAL_TYPE = 'USER'
            AND g.DAL_TYPE = 'object'
            AND g.DAL_PARTITION_CODE = 'NIKU.ROOT' )
    WHERE g.code = 'projmgr.projectList'
    

     

    Configuration details

    (NB remove the ORDER BY if using in a dummy-loopup because it will not work!)

    select 
      c.ID
    , c.COL_CODE
    , c.COL_TYPE type
    , nls.name
    , c.POSITION
    , c.created_date when_col_added
    --
    FROM CMN_SEC_USERS u
    JOIN CMN_GRIDS g ON ( u.id = g.PRINCIPAL_ID and g.PRINCIPAL_TYPE = 'USER' AND g.DAL_PARTITION_CODE = 'NIKU.ROOT' )
    JOIN CMN_GRID_COLS c ON ( g.ID = c.GRID_ID and c.POSITION <> -1 )
    JOIN CMN_LANGUAGES L ON ( u.language_id = l.id )
    JOIN CMN_CAPTIONS_NLS nls ON ( nls.pk_id = c.id AND nls.table_name = 'CMN_GRID_COLS' AND nls.language_code = l.language_code )
    --
    WHERE g.CODE = 'projmgr.projectList' 
    AND u.user_name = 'xxxxxxxxxx'
    --
    ORDER by c.position ASC
    


  • 30.  Re: TIP : Documenting your Configuration (via SQL)

    Posted 07-22-2016 10:27 AM

    Dave you should really write a book! Love this stuff! Thanks!



  • 31.  Re: TIP : Documenting your Configuration (via SQL)

     
    Posted 11-27-2017 10:48 AM

    Thanks, quite useful information!