Clarity

We have just completed an upgrade in our DEV environment to V15.4.1, which basically passed all our validation with only around 5 items that needed to be adjusted.

I then noticed that when an Object is updated in V15.4.1, it's _V view is no longer available and went back to the Release Note for V15.4 which include

This release mitigates an Oracle limitation in previous releases that reduced performance for object attributes. The limitation resulted from the use of _v views instead of  _v2 views (odf_x_v2) in portlet queries. We updated the core add-ins including APM, PMO, PMBOK, PRINCE2, and NPD. We also recommend that you use _v2 views (odf_x_v2) for custom queries. Do not create _V views on bootstrap or attribute changes.

It should be more than a recommentation given that the _V views no longer are used, and in our case, the _V view was deleted from the database when the object was modified.  The _V views existed in our DEV environment when we undertook our initial verification, and am now concerned that they may automatically get deleted in the future which will then impact on other custom code.

We are working with CA Support to determine if this is an anomolie with our environment,  but initial feedback is that _V views won't be provided in this version.

When you plan to undertake your upgrade to PPM V15.4, and you have some customisation in your environment, you will need to consider if any of your code uses the _V views, and if so, will need to address it.

Sample scripts to help you identify if you are using _V views in your custom code.  They will return some false positive cases, for example I have not catered for all the OOTB _V views which are there to support items like Status Report.

NSQL Queries

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
replace(UPPER(cnq.nsql_text),'CMN_LOOKUPS_V','') LIKE UPPER('%ODF%\_V %') escape '\' or replace(UPPER(cnq.nsql_text),'CMN_LOOKUPS_V','') LIKE UPPER('%ODF%\_V,%') escape '\'
--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

Dynamic Lookup NSQL

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 (replace(UPPER(cnq.nsql_text),'CMN_LOOKUPS_V','') like UPPER('%ODF%\_V %') escape '\'   OR     replace(UPPER(cnq.nsql_text),'CMN_LOOKUPS_V','') like UPPER('%ODF%\_V,%') escape '\')

Process GEL Scripts

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 (     replace(UPPER(ccs.script_text),'CMN_LOOKUPS_V','') LIKE UPPER('%ODF%\_V %') escape '\'  or replace(UPPER(ccs.script_text),'CMN_LOOKUPS_V','') LIKE UPPER('%ODF%\_V,%') escape '\'      )
ORDER BY ccnp.name, ccns.name, ccna.name, bdp.created_date

Example of a false positive which the above don't take into account cop_prj_statusrpt_latest_v, hopefully this view will remain in V15.4.1.

To review the code, it is available in the above scripts (eg the third script use ccs.script_text).  To modify the code, go back to Studio to undertake where required.

If you have custom JasperSoft reports, I couldn't find a way to check these except by individually searching the SQL in the reports.