Overview
This article describes how to proceed when you have no 'Contact' information displayed in a transaction for a certain product type (e.g a 'Commercial Loan') to a certain account, and the record shows only the secondary owner.
Solution
This issue likely happens because the 'Contact' record for the account is on the unmatched records database, and the account has an empty shell as the primary contact for the account.
If you want to proceed with the queries mentioned in the following section, please make sure you back up your entire database, and more precisely, the following tables which can be found in the BANK DB:
- Contacts (In all the DBs)
- Activity
Once you execute the queries listed below, the data might be lost and will be unrecoverable. You can use the method described in the Backing Up Tables for Security KB Article for this purpose.
Input information
- The Account Number.
- The Contact ID should be associated with that account.
Query to execute
To resolve the issue, you must access your database and manually move the unmatched record assigning it as the primary contact for the account.
First, you should run a set of queries to obtain information and help you better understand the issue. Below you can see the queries used for a client called 'Michael Jordan'.
select *
from <DB>..Contacts
where First_Name like '%Michael%' --wildcard search to find the 'Michael Jordan' client
or Last_Name like '%Jordan%' --wildcard search to find the 'Michael Jordan' client
or Company_Name like '%JordanFlight%' --wildcard search to find the 'Michael Jordan' client
or ExternalKey_ID = '277F3AE0-63B' --identifier for the bank's client in their core system
or Contact_ID = '08905733-D932-47CB-A9B5-320A250D6186' --person or a company (if has an account, is a contact. Otherwise, is a prospect)
select *
FROM [CTExternal_Source].[dbo].[coretrac]
where [P028-CISM-TAX-ID-NUMBER] = '277F3AE0-63B' --field imported from the bank core system which, in this case is also the External Key ID
or [NA-ACCOUNT-NUMBER] = '75660' --field imported from the bank's core system having the account number
select *
from Activity_Status
select top 30 GroupKey_ID, count(GroupKey_ID)
from Activity_Contacts AC
join Activity A on A.Activity_ID = AC.Activity_ID
and A.Activity_Status_ID = '31C9B5D5-FFF3-4228-9B9E-1D0FA07282B9' --indicates the status of the account (e.g open, in progress, closed)
group by GroupKey_ID having count(GroupKey_ID) > 1-- '75660' --same as [NA-ACCOUNT-NUMBER]
select *
from Activity_Contacts AC
join Activity A on A.Activity_ID = AC.Activity_ID
and A.Activity_Status_ID = '31C9B5D5-FFF3-4228-9B9E-1D0FA07282B9'
where GroupKey_ID ='1005' --identifier of a group of contacts for an account
select *
from Activity
where Activity_ID = 'D8DDD103-34E8-4E16-8DBD-7BC7D345523A' --primary key for an activity
select *
from Activity_Detail
where Activity_ID = 'FDE806FA-B4A1-4897-B6C3-7CE997DF6B8A' --primary key for the details for an activity
select top 30 *
from Matching_History
where Activity_ID = 'FDE806FA-B4A1-4897-B6C3-7CE997DF6B8A' --primary key for the details for an activity
select *
from users
where User_ID = '87F5B75F-5B5D-4F80-8A4C-ACAFFDFA9796' --identifier of the employee that needs to be assigned
-
If it is found in the Activity table, it will be the account number. If the ExternalKey_ID is empty, it is a prospect (an account not opened yet).
-
If it is found in the Contacts table, it's the identifier for a bank client in their core system
-
if it is found in the Users table, it's the User ID of the bank employee in the bank's core system
-
If it is found in the Products table, it's the identifier of the product on the bank's core system.
Now that you have the information, you must manually move the unmatched record assigning it as the primary contact for the account by using the queries below - also an example for 'Michael Jordan' (account number #LAS1308411) as above:
Insert Into <DB>..Contacts
(
select *
from EX_<DB>..Contacts
where ExternalKey_ID = '277F3AE0-63B' --identifier for the bank's client in their core system
select *
from Contacts
where ExternalKey_ID = '277F3AE0-63B'
delete
from EX_<DB>..Contacts
where ExternalKey_ID = '277F3AE0-63B'
--Select *
--into Activity_BK_ZD2596969
--from
--update
--Activity
--set Primary_Contact_ID = '08905733-D932-47CB-A9B5-320A250D6186' --the contact/prospect from the previous query
--where ExternalKey_ID like '%1234567%' --the account number for the client
select *
from contacts
where Contact_ID = '5E2E63F3-629D-40ED-996B-30CF7CD77A43' --previous value for Primary Contact ID
select *
from Activity_Contacts
where Activity_ID = 'FDE806FA-B4A1-4897-B6C3-7CE997DF6B8A'
insert into Activity_Contacts
Values
(
newID(),
'FDE806FA-B4A1-4897-B6C3-7CE997DF6B8A' --Activity ID,
'08905733-D932-47CB-A9B5-320A250D6186' --Primary Contact ID,
'75660' --Group Key ID,
1,
null,
'87F5B75F-5B5D-4F80-8A4C-ACAFFDFA9796' --User ID,
getdate(),
null,
null
)
Once you are done, wait for the system to complete a new import to confirm the issue is resolved.
Contacting Support
If you want support to execute this backend modification on your behalf, please contact the Support staff and provide them with the information described in the Input information section. A customer support representative will contact you back with the result of the execution of the aforementioned query(ies).
Comments
0 comments
Please sign in to leave a comment.