Georgy N Joseph

Search All NSQL And GEL Scripts

Blog Post created by Georgy N Joseph on Jan 5, 2016

Hi Friends,

 

Many of you might have come across scenarios where you are removing/modifying an attribute and you want to know where all this attribute is being used at present, taking into consideration any NSQLs or GEL Scripts in CA PPM. Or just like my present scenario when it is the year end and I want to know if the last year is hard-coded in any of the GEL Scripts or NSQL (be it NSQL for a portlet or a lookup). The following Oracle SQL queries will help you in such scenarios.

 

Search All NSQL Grid Portlets

 

SELECT

qry.portlet_code "Portlet ID",

CASE qry.is_active WHEN 1 THEN 'Yes' ELSE 'No' End "Active",

qry.created_date "Created Date",

qry.full_name "Created By",

qry.query_code "Query ID",

nsql.nsql_text "Query NSQL"

FROM cmn_nsql_queries nsql

INNER JOIN

(

SELECT cp.portlet_code,cp.is_active,cp.created_date,sr.full_name,cgnq.query_code,cgnq.cmn_nsql_queries_id

FROM cmn_grids cg INNER JOIN cmn_portlets cp ON cp.id=cg.portlet_id

INNER JOIN cmn_nsql_queries cnq ON cg.dal_id = cnq.id

INNER JOIN cmn_gg_nsql_queries cgnq ON cnq.id = cgnq.cmn_nsql_queries_id

INNER JOIN srm_resources sr ON sr.user_id=cp.created_by

WHERE

UPPER(cnq.nsql_text) LIKE UPPER('%your search keyword%')

--cgnq.query_code='Query ID'

GROUP BY cp.portlet_code,cp.is_active,cp.created_date,sr.full_name,cgnq.query_code,cgnq.cmn_nsql_queries_id

)

qry ON qry.cmn_nsql_queries_id=nsql.id

order by qry.query_code, qry.portlet_code

 

NB: The same query above can also be used if you want to know the list of Grid portlets made from a particular NSQL query. In that case, you just have to comment out the Keyword search line of code and uncomment the following line of SQL code substituting the "Query ID" text with that particular NSQL Query ID.

 

Search All NSQL Lookups

 

SELECT

clt.lookup_type "Lookup ID",

CASE clt.is_active WHEN 1 THEN 'Yes' ELSE 'No' End "Active",

cnq.created_date "Created Date",

sr.full_name "Created By",

cnq.nsql_text "Query NSQL"

FROM

cmn_list_of_values clov, cmn_lookup_types clt, cmn_nsql_queries cnq, srm_resources sr

WHERE clt.lookup_type=clov.lookup_type_code

and cnq.id=clov.sql_text_id

and sr.user_id=cnq.created_by

and UPPER(cnq.nsql_text) like UPPER('%your search keyword%')

 

Search All GEL Scripts

 

This SQL code below is a slight improvisation to my SQL code at Search All GEL Scripts to handle keyword searches in any case - upper or lower.

 

SELECT ccnp.name Process_Name,

  bdp.process_code,

  ccns.name Step,

  ccna.name Action,

  ccs.script_text,

  bdpv.user_status_code Process_Status,

  bdp.created_date

FROM BPM_DEF_PROCESSES bdp,

     CMN_CAPTIONS_NLS ccnp,

     BPM_DEF_PROCESS_VERSIONS bdpv,

     BPM_DEF_STAGES bdstg,

     BPM_DEF_STEPS bds,

     CMN_CAPTIONS_NLS ccns,

     BPM_DEF_STEP_ACTIONS bdsa,

     CMN_CAPTIONS_NLS ccna,

     CMN_CUSTOM_SCRIPTS ccs

WHERE ccnp.table_name       ='BPM_DEF_PROCESSES'

AND ccnp.language_code      ='en'

AND ccnp.pk_id              =bdp.id

AND bdpv.process_id         =bdp.id

AND bdstg.process_version_id=bdpv.id

AND bds.stage_id            =bdstg.id

AND ccns.table_name         ='BPM_DEF_STEPS'

AND ccns.language_code      ='en'

AND ccns.pk_id              =bds.id

AND bdsa.step_id            =bds.id

AND ccna.table_name         ='BPM_DEF_STEP_ACTIONS'

AND ccna.language_code      ='en'

AND ccna.pk_id              =bdsa.id

AND ccs.id                  =bdsa.script_id

AND ccs.language_code       = 'gel'

AND UPPER(ccs.script_text) LIKE UPPER('%your search keyword%')

ORDER BY ccnp.name, ccns.name, ccna.name, bdp.created_date

 

 

Feel free to modify these queries to suit your needs. I hope these will give you a high-level idea on the database tables involved.

Wish you all happy searching in CA PPM

 

Regards,

Georgy

Outcomes