Kintana Script : Object and GL Migrator Current Versions

Purpose

Get currently installed OM and GL Migration version numbers.

Tested

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

Usage

Paste the script into Toad or SQL Runner

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

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

-- 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')

Download

KintanaScripts.zip (64 Kb)