CustomQuery - Copying Account owner from Endpoint A to Endpoint B

[ejeong]

hello I am trying to copy account owner from one endpoint to another. account name in both endpoint are same. 

here is query I tried 

update accountowners ao, users u,accounts a,accountowners ao1,accounts a1
set ao1.owneruserkey = u.userkey
where ao.rank = 1
and u.userkey = ao.owneruserkey
and a.endpointkey = 207
and a1.endpointkey = 1311
and a.accountkey = ao.accountkey
and a1.accountkey = ao1.accountkey
and a.name = a1.name
and a.status in ('ACTIVE','Manually Provisioned','1');

This is account owners in Endpoint A 

This is account owner in Endpoint B

I didn't have any error when ran the job but owner is not being updated.

Can anyone help me what's wrong in my query? 

[rushikeshvartak · Accepted solution]

-- SET PRIMARY CERTIFIER FOR NEW SERVICE/SHARED ACCOUNTS 

INSERT INTO accountowners ( ACCOUNTKEY, OWNERUSERKEY, RANK,UPDATEDATE,UPDATEUSER) 
 SELECT  acc.ACCOUNTKEY, OWNERUSERKEY,26,sysdate(),1 FROM accountowners accown,accounts  acc 
 where acc.accounttype in ('Service Account','Shared Account') and accown.accountkey=acc.accountkey 
 and accown.rank=1 and acc.ACCOUNTKEY not in ( 
 select acco.accountkey from accountowners acco where acco.accountkey = acc.accountkey and rank=26 
 ) ; 

Please update query as per your requirement

[ejeong]

Thanks. 

I made query like below  and it worked as expected. but when running job mutiple times,, same users are being added as additional record. any recommendation to prevent this? 

INSERT INTO accountowners ( ACCOUNTKEY, OWNERUSERKEY, RANK,UPDATEDATE,UPDATEUSER)
select distinct a1.accountkey,ao.owneruserkey,1,sysdate(),1 as 'updateuser'
from accountowners ao, users u,accounts a,accountowners ao1,accounts a1
where ao.rank = 1
and u.userkey = ao.owneruserkey
and a.endpointkey = 207
and a1.endpointkey = 1311
and a.accountkey = ao.accountkey
and a.name = a1.name
and a.status in ('ACTIVE','Manually Provisioned','1');

[ejeong]

Could you please help me when you have time? 

Here is query I am using. This made duplicated record not get created but.. when there is user who is owner for one of accounts became an owner for another account.. we can't handle that use case with this query... 

INSERT INTO accountowners ( ACCOUNTKEY, OWNERUSERKEY, RANK,UPDATEDATE,UPDATEUSER)
select distinct a1.accountkey,ao.owneruserkey,1,sysdate(),1 as 'updateuser'
from accountowners ao, users u,accounts a,accountowners ao1,accounts a1
where ao.rank = 1
and u.userkey = ao.owneruserkey
and a.endpointkey = 207
and a1.endpointkey = 1311
and a.accountkey = ao.accountkey
and a.name = a1.name
and a.status in ('ACTIVE','Manually Provisioned','1')
AND u.userkey not in (
 select ao.owneruserkey from accounts a,accountowners ao where ao.accountkey = a.accountkey and ao.rank=1 and a.endpointkey = 1311);

[rushikeshvartak]

INSERT INTO accountowners ( ACCOUNTKEY, OWNERUSERKEY, RANK,UPDATEDATE,UPDATEUSER)
select distinct a1.accountkey,ao.owneruserkey,1,sysdate(),1 as 'updateuser'
from accountowners ao, users u,accounts a,accountowners ao1,accounts a1
where ao.rank = 1
and u.userkey = ao.owneruserkey
and a.endpointkey = 207
and a1.endpointkey = 1311
and a.accountkey = ao.accountkey
and a.name = a1.name
and a.status in ('ACTIVE','Manually Provisioned','1')
AND concat(u.userkey,'-',a.accountkey,'-',ao.rank) not in (
 select concat(ao.owneruserkey,'-',ao.accountkey,'-',ao.rank)from accounts a,accountowners ao where ao.accountkey = a.accountkey and ao.rank=1 and a.endpointkey = 1311);

[ejeong]

Thanks but I am getting error like below..

[ejeong]

Please disregard.. 

This query is working as expected. 

INSERT INTO accountowners ( ACCOUNTKEY, OWNERUSERKEY, RANK,UPDATEDATE,UPDATEUSER)
select distinct a1.accountkey,ao.owneruserkey,1,sysdate(),1 as 'updateuser'
from accountowners ao, users u,accounts a,accountowners ao1,accounts a1
where ao.rank = 1
and u.userkey = ao.owneruserkey
and a.endpointkey = 207
and a1.endpointkey = 1311
and a.accountkey = ao.accountkey
and a.name = a1.name
and a.status in ('ACTIVE','Manually Provisioned','1')
AND u.userkey not in (
 select ao.owneruserkey from accounts a,accountowners ao where ao.accountkey = a.accountkey and ao.rank=1 and a.endpointkey = 1311);

Thanks you!

[sharmas1]

Is there a way to do it through Saviynt For Saviynt DB import. If yes, can you please share the sample xml file.

Thanks in advance.

[sahajranajee]

@sharmas1 ,

Ownership update via recon is not supported by the DB Connector. Please use schema file uploads to do it.