Kintana Script : Users with Oracle Apps Usernames

Purpose

List Kintana user records which have been set up with the user's Oracle Applications username set on the 'Extension Data' tab.

You want to make sure this is always the case for users who migrate AOL: objects so that the individual user name will appear to identify the migrator on concurrent request migration logs, and not the 'SYSADMIN' default.

This file also includes scripts to identify differences between Kintana and Oracle Applications usernames.

Your life will be very much easier if the usernames set up in Oracle Applications (OA) match the Kintana usernames!

Tested

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

Usage

Paste the script into Toad or SQL Runner

-- Assuming you've adopted a policy of setting the Oracle Apps username
-- on the Kintana 'Extension Data' tab for users doing OA migrations
-- this script will list your active Object Migrator users.

select apti.parameter1                       "Oracle Apps Username",
       nu.first_name || ' ' || nu.last_name  "User",
       apti.enabled_flag                     "Enabled On Ext Data Tab",
       apti.plug_tab_id,
from KACC_PLUG_TAB_INSTANCES apti,
     KACC_PLUG_TABS apt,
     KNTA_USERS nu
where  apti.plug_tab_id = apt.plug_tab_id
   and apt.description = 'Oracle Applications tab on Users'
-- and apti.enabled_flag = 'Y'
-- and apti.parameter1 is not NULL
and apti.parent_id = nu.user_id
and nu.end_date is NULL
order by 1, 2

-- And this will list your 'Kintana' users without the oracle apps
-- user name set. There shouldn't be any user's on it that you know
-- do AOL: object migrations.

select nu,username
from KNTA_USERS nu
where nu.end_date is NULL
minus
select nu.username
from KACC_PLUG_TAB_INSTANCES apti,
     KACC_PLUG_TABS apt,
     KNTA_USERS nu
where apti.plug_tab_id = apt.plug_tab_id
  and apt.description = 'Oracle Applications tab on Users'
  and apti.parameter1 is not NULL
  and apti.parameter1 = nu.username
  and nu.end_date is NULL

-- List OA usernames that match up to the Kintana username

select  nu.username     "User Info Tab Username",
        apti.parameter1 "Ext Data Tab OA Username"
from KACC_PLUG_TAB_INSTANCES apti,
     KNTA_USERS nu
where apti.parent_id = nu.user_id
  and apti.parameter1 = nu.username
  and apti.plug_tab_id = 4
  and nu.end_date is NULL
order by nu.username

User Info Tab Username  Ext Data Tab OA Username
hfong                   hfong
bcastell                bcastell

-- Flip the username test to list names that don't match.

-- List OA usernames that don't match up to the Kintana username

select  nu.username     "User Info Tab Username",
        apti.parameter1 "Ext Data Tab OA Username"
from KACC_PLUG_TAB_INSTANCES apti,
     KNTA_USERS nu
where apti.parent_id = nu.user_id
  and apti.parameter1 != nu.username
  and apti.plug_tab_id = 4
  and nu.end_date is  NULL

User Info Tab Username Ext Data Tab OA Username
jforcelli              jkforcelli
pkstrauss              pstrauss

Download

KintanaScripts.zip (58 Kb)