Kintana Script : Get current OM /GL/accelerator version info

Purpose

You need to know what you have installed if you're upgrading. If you're filing a service request about a problem with these components you can save yourself some time by supplying this info before they ask you for it.

Tested

With HP PPM (Project and Portfolio Management) Kintana v6.0.

Usage

Paste the script into Toad or SQL Runner

Omit the comment, spool, and linesize statements if you're using SQL RUNNER.
SQL Runner only supports select statements and you'll get a KNTA-10648 error.

If using PL-SQL change 'yyyymmdd' in the output file name to today's date.

spool c:\version_info_yyyymmdd.txt
set linesize 1000

-- To get current information on installed accelerators

select ac.last_update_date, ac.short_name, ac.accelerator_name, ac.enabled_flag, ac.version
from KACC_ACCELERATORS ac

LAST_UPDATE_DATE        SHORT_NAME  ACCELERATOR_NAME     ENABLED_FLAG  VERSION
08/07/2004 11:28:58 AM  OracleApps  Oracle Applications  Y             5.5.0
08/07/2004 11:28:58 AM  OracleTech  Oracle Technology    Y             5.5.0

-- Get current version information for Object Migrator.

select decode(to_number(substr(cii60.latest_upgrade_version,1,3))*10, 60, '6.0',
decode(to_number(substr(cii51.latest_upgrade_version,1,3))*10, 51,'5.1',
decode(round((to_number(substr(cii50.latest_upgrade_version,1,3))/9)),0,
decode(utc42.column_name, null,
decode (utb41.table_name, null,
decode (utb40.table_name, null,
decode(utb31.table_name, null,
decode(cii3.program_name, null,
decode(cii2.program_name, null,
decode(cii1.program_name, null, '1.0','2.0'),
 '2.1'),
 '3.0'),
 '3.1'),
 '4.0'),
 '4.1'),
 '4.2'),
 '5.0'))) current_rel_version
from clm_install_info cii60,
     clm_install_info cii51,
     clm_install_info cii50,
     user_tab_columns utc42,
     user_tables utb41,
     user_tables utb40,
     user_tables utb31,
     clm_install_info cii1,
     clm_install_info cii2,
     clm_install_info cii3,
     clm_install_info cii4
where cii60.PROGRAM_NAME = 'CLMRMCP1'
and cii51.PROGRAM_NAME = 'CLMRMCP1'
and cii50.PROGRAM_NAME = 'CLMRMCP1'
and cii4.PROGRAM_NAME = 'CLMRMCP1'
and cii3.PROGRAM_NAME (+) = decode(1,2,cii4.PROGRAM_NAME,'CLMRMMS1')
and cii2.PROGRAM_NAME (+) = decode(1,2,cii4.PROGRAM_NAME,'CLMRMFD1')
and cii1.PROGRAM_NAME (+) = decode(1,2,cii4.PROGRAM_NAME,'CLMRMFN1')
and utb31.table_name (+) = decode(1,2,cii4.PROGRAM_NAME, 'CLM_REQUEST_SET_STAGES')
and utb40.table_name (+) = decode(1,2,cii4.PROGRAM_NAME, 'CLM_USER_RESP_GROUPS')
and utb41.table_name (+) = decode(1,2,cii4.PROGRAM_NAME, 'CLM_FLEX_HIERARCHIES_TL')
and utc42.table_name(+) = decode(1,2,cii4.PROGRAM_NAME, 'CLM_FORM_FUNCTIONS_ARCH')
and utc42.column_name(+) = decode(1,2,cii4.PROGRAM_NAME, 'REGION_CODE')
and rownum = 1

-- Takes a very long time to run but does complete!

-- To get current version information for GL*Migrator.

select decode(utb1.table_name,null,'1.0','2.0') curr_ver
from USER_TABLES utb1, CLM_INSTALL_INFO cii
where cii.PROGRAM_NAME(+) = 'CLGMRJS1'
  and utb1.table_name(+) = decode(1,2,cii.PROGRAM_NAME,'CLGM_JE_SOURCES_TL')

spool off

Download

KintanaScripts.zip (58 Kb)