Kintana Script : Synchronize Contacts And Users

Purpose

Kintana doesn't make it easy to maintain the contact list.

What ought to happen when you enter or change a user record (for example to set the end date, or change the user names for a married name change) is that either a contact entry dialog should pop up or a [Contact] button should be live.

Sadly, this is not the case. It's all on you to keep these lists correlated. Rather than fix the problem Kintana have a report that will attempt to sync up user and contact records.

Or you may prefer to use this script.

Tested

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

Usage

Paste the script into Toad or SQL Runner

--  Generic queries

select count(*)
from KCRT_CONTACTS cc
where cc.enabled_flag = 'Y'

select count(*)
from KNTA_USERS nu
where nu.end_date is NULL

select *
from KCRT_CONTACTS cc
where cc.enabled_flag = 'Y'
and cc.full_name like ('%Zabrinski%' )
order by cc.creation_date

-- Step 1
-- List enabled contacts for which the user record end date is set
-- These ought to be disabled in the contact list.

select cc.full_name
from KCRT_CONTACTS cc,
     KNTA_USERS nu
where cc.enabled_flag = 'Y'
  and cc.user_id = nu.user_id
  and nu.end_date is not NULL

-- Step 2 :
-- List Contact-User Record name variations and clean them up
-- *** In particular, clean up critical errors such as invalid emails!

select cc.first_name    "cc.first_name",    nu.first_name     "nu.first_name",
       cc.last_name     "cc.last_name",     nu.last_name      "nu.last_name",
       cc.email_address "cc.email_address", nu.email_address  "nu.email_address",
       cc.phone_number  "cc.phone_number",  nu.phone_number   "nu.phone_number"
from KCRT_CONTACTS cc,
     KNTA_USERS nu
where cc.user_id = nu.user_id
 and  cc.enabled_flag = 'Y'
 and (    cc.first_name != nu.first_name
       or cc.last_name != nu.last_name
       or cc.email_address != nu.email_address
       or cc.phone_number != nu.phone_number
     )

-- Step 3
-- List enabled contacts for which there is no user record
-- (It's Ok to have contacts with no user record).
-- Note 1 : A blank in the Demand - Contacts dialog ''username' column
--          indicates the same thing
-- Note 2 : There is no 'username' column in the KCRT_CONTACTS table
-- Note 3 : If the User_ID field is not blank then the user appears in
--          the Demand Mgmt - Contacts List as an enabled user
--          and also in the Sys Admin - Users List
--          BUT the user end-date is set in the user_record
-- Note 4 : If the User_ID field is blank
--          then the user appears in the Demand Mgmt - Contacts list
--          as an enabled user but there is no record for him in
--          the Sys-Admin - Users List.

select cc.user_id, cc.first_name, cc.last_name
from KCRT_CONTACTS cc
where cc.enabled_flag = 'Y'
minus
select nu.user_id, nu.first_name, nu.last_name
from KNTA_USERS nu
where nu.end_date is NULL

-- Step 4
-- List active users (with null end_dates) for which there is either
-- no enabled contacts record or one with exactly matching fields
-- It's not Ok to have an active user record without a contact record

select nu.user_id, nu.first_name, nu.last_name,
       nu.email_address, nu.phone_number
from KNTA_USERS nu
where nu.end_date is NULL
minus
select cc.user_id, cc.first_name, cc.last_name,
       cc.email_address, cc.phone_number
from KCRT_CONTACTS cc
where cc.enabled_flag = 'Y'

Download

KintanaScripts.zip (64 Kb)