Click HERE to see how Saviynt Intelligence is transforming the industry. |
05/03/2024 08:18 AM
How can we set owner on terminate to all the employees of the organization.
It looks like a Saviynt internal attribute and not coming in feed from Workday.
Can someone please let me know the options available to set this for every user.
Solved! Go to Solution.
05/03/2024 11:07 AM
You can defined same in pre-processor logic as manager will be owneronterminate
05/09/2024 02:54 AM
Would a sample logic look like this?
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET OWNERONTERMINATE = user.manager"]
and how can we update the logic to skip VP and SVPs, if the user is VP(identifying attribute by job_function) then the owner on terminate should be saviynt admin
05/09/2024 02:57 AM
To update the logic to skip VPs and SVPs and set the owner on terminate as Saviynt admin for VPs, you can use the following sample logic:
"PREPROCESSQUERIES": [ "UPDATE NEWUSERDATA n SET n.OWNERONTERMINATE = (CASE WHEN n.JOB_FUNCTION = 'VP' THEN 'saviyntadmin' WHEN n.JOB_FUNCTION = 'SVP' THEN 'saviyntadmin' ELSE n.MANAGER END)" ] In this logic, the "JOB_FUNCTION"
attribute is used to identify if the user is a VP or SVP. If the user has the job function as VP, the owner on terminate is set as "saviyntadmin". For any other users, the owner on terminate is set as their manager.
05/09/2024 07:36 PM
{
"ADDITIONALTABLES": {
"USERS": "SELECT USERNAME, FIRSTNAME, LASTNAME, STATUSKEY, MANAGER, JOBCODEDESC, DEPARTMENTNAME, SECONDARYMANAGER, OWNER FROM USERS"
},
"COMPUTEDCOLUMNS": [
"OWNERONTERMINATE"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET OWNERONTERMINATE = OWNER"
]
}
05/21/2024 09:30 AM
Error in Users Import - Error while processing data: Unknown column 'nu.OWNERONTERMINATE' in 'field list'
getting this error while running the query: "UPDATE newuserdata nu SET nu.owneronterminate = (CASE WHEN nu.employeeid = '276' THEN 'saviyntadmin' ELSE END)",
05/21/2024 09:46 AM
UPDATE newuserdata nu
SET nu.OWNERONTERMINATE = (CASE WHEN nu.employeeid = '276' THEN 'saviyntadmin' ELSE nu.OWNERONTERMINATE END);
05/21/2024 09:55 AM
with both OWNERONTERMINATE and owneronterminate we are getting the same error. We even removed the case and used the below query, still same issue.
"UPDATE newuserdata nu SET nu.OWNERONTERMINATE = 'saviyntadmin' WHERE nu.employeeid = '274906'",
05/21/2024 10:07 AM
Please share full json
05/21/2024 10:06 AM - edited 05/21/2024 10:09 AM
@ruchika : Did you include OWNERONTERMINATE in select statement under ADDITIONALTABLES section?
{
"ADDITIONALTABLES": {
"USERS": "SELECT USERNAME, FIRSTNAME, LASTNAME, STATUSKEY, MANAGER, JOBCODEDESC, DEPARTMENTNAME, SECONDARYMANAGER, OWNERONTERMINATE, OWNER FROM USERS"
}
05/21/2024 10:08 AM
yes, tried that too but got the same error
05/21/2024 10:18 AM
05/21/2024 10:19 PM
{
"ADDITIONALTABLES": {
"USERS": "SELECT username, employeeid, systemusername, customproperty11, customproperty28, lastname, statuskey, employeeType, displayname, firstname, locationnumber, locationdesc, state, street, title, middlename, owner, comments, preferedFirstName, customproperty2, customproperty5, customproperty13, jobcode, customproperty14, customproperty4, job_function, customproperty1, customproperty15, customproperty6, customproperty7, customproperty9, customproperty8, customproperty10, customproperty12, customproperty16, customproperty17, customproperty18, customproperty19, customproperty21, customproperty22, customproperty3, costcenter, orgunitid, departmentNumber, departmentname, customproperty23, phonenumber, secondaryPhone, customproperty26, customproperty25, startdate, customproperty24, enddate, regioncode, entity, companyname, city, country, region, owneronterminate LOCALAUTHENABLED FROM USERS"
},
"TABLEINDEXES": {
"currentusers": [
"employeeid",
"preferedfirstname",
"firstname",
"lastname",
"systemusername",
"customproperty11",
"displayname",
"statuskey",
"startdate",
"username"
],
"newuserdata": [
"preferedfirstname",
"firstname",
"lastname",
"systemusername",
"customproperty11",
"displayname",
"statuskey",
"employeeid"
]
},
"COMPUTEDCOLUMNS": [
"USERNAME",
"CUSTOMPROPERTY11",
"DISPLAYNAME",
"SYSTEMUSERNAME"
],
"PREPROCESSQUERIES": [
"UPDATE newuserdata nu SET nu.owner = (SELECT username FROM currentusers cu WHERE cu.employeeid = nu.owner)",
"UPDATE newuserdata nu SET nu.statuskey = '1' WHERE nu.customproperty5 IN ('A', 'L', 'P', 'S', 'F')",
"UPDATE newuserdata nu SET nu.statuskey = '0' WHERE nu.customproperty5 NOT IN ('A', 'L', 'P', 'S', 'F')",
"UPDATE newuserdata nu SET nu.title = SUBSTRING(nu.title, 1, 64) WHERE LENGTH(nu.title) > 64",
"UPDATE newuserdata nu SET nu.owneronterminate = (CASE WHEN nu.employeeid = '276' THEN 'saviyntadmin' END)",
"CUSTOMFUNCTION###DOPREPROCESS",
"INSERT INTO newuserdata(username, employeeid, systemusername, customproperty28, lastname, statuskey, employeeType, displayname, firstname, locationnumber, locationdesc, state, street, title, middlename, owner, comments, preferedFirstName, customproperty2, customproperty5, customproperty13, jobcode, customproperty14, customproperty4, job_function, customproperty1, customproperty15, customproperty6, customproperty7, customproperty9, customproperty8, customproperty10, customproperty12, customproperty16, customproperty17, customproperty18, customproperty19, customproperty21, customproperty22, customproperty3, costcenter, orgunitid, departmentNumber, departmentname, customproperty23, phonenumber, secondaryPhone, customproperty26, customproperty25, startdate, customproperty24, enddate, regioncode, entity, companyname, city, country, region) SELECT cu.username, cu.employeeid, cu.systemusername, cu.customproperty28, cu.lastname, 0 as statuskey, cu.employeeType, cu.displayname, cu.firstname, cu.locationnumber, cu.locationdesc, cu.state, cu.street, cu.title, cu.middlename, cu.owner, cu.comments, cu.preferedFirstName, cu.customproperty2, cu.customproperty5, cu.customproperty13, cu.jobcode, cu.customproperty14, cu.customproperty4, cu.job_function, cu.customproperty1, cu.customproperty15, cu.customproperty6, cu.customproperty7, cu.customproperty9, cu.customproperty8, cu.customproperty10, cu.customproperty12, cu.customproperty16, cu.customproperty17, cu.customproperty18, cu.customproperty19, cu.customproperty21, cu.customproperty22, cu.customproperty3, cu.costcenter, cu.orgunitid, cu.departmentNumber, cu.departmentname, cu.customproperty23, cu.phonenumber, cu.secondaryPhone, cu.customproperty26, cu.customproperty25, cu.startdate, cu.customproperty24, sysdate() as enddate, cu.regioncode, cu.entity, cu.companyname, cu.city, cu.country, cu.region FROM currentusers cu WHERE cu.statuskey = 1 AND cu.employeeid IS NOT NULL AND LENGTH(TRIM(cu.employeeid))>0 AND cu.customproperty28 IS NOT NULL AND LENGTH(TRIM(cu.customproperty28))>0 AND cu.startdate IS NOT NULL AND (DATE(cu.startdate) < SUBDATE(DATE(sysdate()),INTERVAL 1 DAY)) AND cu.employeeid IN ('0000033','0000058','0000107','0000279','0000280','0000281') AND cu.LOCALAUTHENABLED != '1' AND cu.employeeid NOT IN (SELECT nu.employeeid FROM newuserdata nu) AND (SELECT count(*) FROM CURRENTUSERS cu1 WHERE cu1.statuskey = 1 AND cu1.employeeid IS NOT NULL AND LENGTH(TRIM(cu1.employeeid))>0 AND cu1.customproperty28 IS NOT NULL AND LENGTH(TRIM(cu1.customproperty28))>0 AND cu1.startdate IS NOT NULL AND (DATE(cu1.startdate) < SUBDATE(DATE(sysdate()),INTERVAL 1 DAY)) AND cu1.employeeid IN ('0000033','0000058','0000107','0000279','0000280','0000281') AND cu1.LOCALAUTHENABLED != '1' AND cu1.employeeid NOT IN (SELECT nu1.employeeid FROM newuserdata nu1)) < 10"
],
"CUSTOMFUNCTIONS": {
"DOPREPROCESS": {
"FULLCLASSNAME": "com.mckesson.saviynt.preprocessor.ConnectionUtilityService",
"METHODNAME": "doPreProcess"
}
}
}
05/21/2024 10:35 PM
"UPDATE newuserdata nu SET nu.owneronterminate = 'saviyntadmin' where nu.employeeid = '276'"
05/21/2024 11:04 PM
As mentioned earlier this is also giving error as owneronterminate attribute is showing up as unknown column. And we would need case statement for our original query, currently testing with one user only hence using it like you mentioned using where but that gives error too
05/22/2024 05:58 AM
05/22/2024 10:03 AM
modified the data as per below, but the owneronterminate attribute is not getting set for user. The jobs are running successfully while we run them. Is there any change expected in user account on workday level to trigger the condition?
{
"ADDITIONALTABLES": {
"USERS": "SELECT username, employeeid, systemusername, customproperty11, customproperty28, lastname, statuskey, employeeType, displayname, firstname, locationnumber, locationdesc, state, street, title, middlename, owner, comments, preferedFirstName, customproperty2, customproperty5, customproperty13, jobcode, customproperty14, customproperty4, job_function, customproperty1, customproperty15, customproperty6, customproperty7, customproperty9, customproperty8, customproperty10, customproperty12, customproperty16, customproperty17, customproperty18, customproperty19, customproperty21, customproperty22, customproperty3, costcenter, orgunitid, departmentNumber, departmentname, customproperty23, phonenumber, secondaryPhone, customproperty26, customproperty25, startdate, customproperty24, enddate, regioncode, entity, companyname, city, country, region, owneronterminate, LOCALAUTHENABLED FROM USERS"
},
"TABLEINDEXES": {
"currentusers": [
"employeeid",
"preferedfirstname",
"firstname",
"lastname",
"systemusername",
"customproperty11",
"displayname",
"statuskey",
"startdate",
"username"
],
"newuserdata": [
"preferedfirstname",
"firstname",
"lastname",
"systemusername",
"customproperty11",
"displayname",
"statuskey",
"employeeid"
]
},
"COMPUTEDCOLUMNS": [
"USERNAME",
"CUSTOMPROPERTY11",
"DISPLAYNAME",
"SYSTEMUSERNAME",
"OWNERONTERMINATE"
],
"PREPROCESSQUERIES": [
"UPDATE newuserdata nu SET nu.owner = (SELECT username FROM currentusers cu WHERE cu.employeeid = nu.owner)",
"UPDATE newuserdata nu SET nu.statuskey = '1' WHERE nu.customproperty5 IN ('A', 'L', 'P', 'S', 'F')",
"UPDATE newuserdata nu SET nu.statuskey = '0' WHERE nu.customproperty5 NOT IN ('A', 'L', 'P', 'S', 'F')",
"UPDATE newuserdata nu SET nu.title = SUBSTRING(nu.title, 1, 64) WHERE LENGTH(nu.title) > 64",
"UPDATE newuserdata nu SET nu.owneronterminate = '1' where nu.employeeid = '274906'",
"CUSTOMFUNCTION###DOPREPROCESS",
"INSERT INTO newuserdata(username, employeeid, systemusername, customproperty28, lastname, statuskey, employeeType, displayname, firstname, locationnumber, locationdesc, state, street, title, middlename, owner, comments, preferedFirstName, customproperty2, customproperty5, customproperty13, jobcode, customproperty14, customproperty4, job_function, customproperty1, customproperty15, customproperty6, customproperty7, customproperty9, customproperty8, customproperty10, customproperty12, customproperty16, customproperty17, customproperty18, customproperty19, customproperty21, customproperty22, customproperty3, costcenter, orgunitid, departmentNumber, departmentname, customproperty23, phonenumber, secondaryPhone, customproperty26, customproperty25, startdate, customproperty24, enddate, regioncode, entity, companyname, city, country, region) SELECT cu.username, cu.employeeid, cu.systemusername, cu.customproperty28, cu.lastname, 0 as statuskey, cu.employeeType, cu.displayname, cu.firstname, cu.locationnumber, cu.locationdesc, cu.state, cu.street, cu.title, cu.middlename, cu.owner, cu.comments, cu.preferedFirstName, cu.customproperty2, cu.customproperty5, cu.customproperty13, cu.jobcode, cu.customproperty14, cu.customproperty4, cu.job_function, cu.customproperty1, cu.customproperty15, cu.customproperty6, cu.customproperty7, cu.customproperty9, cu.customproperty8, cu.customproperty10, cu.customproperty12, cu.customproperty16, cu.customproperty17, cu.customproperty18, cu.customproperty19, cu.customproperty21, cu.customproperty22, cu.customproperty3, cu.costcenter, cu.orgunitid, cu.departmentNumber, cu.departmentname, cu.customproperty23, cu.phonenumber, cu.secondaryPhone, cu.customproperty26, cu.customproperty25, cu.startdate, cu.customproperty24, sysdate() as enddate, cu.regioncode, cu.entity, cu.companyname, cu.city, cu.country, cu.region FROM currentusers cu WHERE cu.statuskey = 1 AND cu.employeeid IS NOT NULL AND LENGTH(TRIM(cu.employeeid))>0 AND cu.customproperty28 IS NOT NULL AND LENGTH(TRIM(cu.customproperty28))>0 AND cu.startdate IS NOT NULL AND (DATE(cu.startdate) < SUBDATE(DATE(sysdate()),INTERVAL 1 DAY)) AND cu.employeeid IN ('0000033','0000058','0000107','0000279','0000280','0000281') AND cu.LOCALAUTHENABLED != '1' AND cu.employeeid NOT IN (SELECT nu.employeeid FROM newuserdata nu) AND (SELECT count(*) FROM CURRENTUSERS cu1 WHERE cu1.statuskey = 1 AND cu1.employeeid IS NOT NULL AND LENGTH(TRIM(cu1.employeeid))>0 AND cu1.customproperty28 IS NOT NULL AND LENGTH(TRIM(cu1.customproperty28))>0 AND cu1.startdate IS NOT NULL AND (DATE(cu1.startdate) < SUBDATE(DATE(sysdate()),INTERVAL 1 DAY)) AND cu1.employeeid IN ('0000033','0000058','0000107','0000279','0000280','0000281') AND cu1.LOCALAUTHENABLED != '1' AND cu1.employeeid NOT IN (SELECT nu1.employeeid FROM newuserdata nu1)) < 10"
],
"CUSTOMFUNCTIONS": {
"DOPREPROCESS": {
"FULLCLASSNAME": "com.mckesson.saviynt.preprocessor.ConnectionUtilityService",
"METHODNAME": "doPreProcess"
}
}
}
05/24/2024 07:25 AM
any idea on the ask above?
05/24/2024 07:38 AM - edited 05/24/2024 07:51 AM
05/30/2024 04:19 AM
The error got resolved after making below changed in modifyuserdatajson:
additional tables:
userkey, owneronterminate
tablesindexes:->currentusers
"userkey"
computedcolumns
OWNERONTERMINATE
PREPROCESSQUERIES
"UPDATE newuserdata nu SET nu.OWNERONTERMINATE = (CASE WHEN nu.JOB_FUNCTION = 'Executive' THEN '1' ELSE (SELECT cu.userkey FROM currentusers cu WHERE cu.username = nu.owner) END)",
05/30/2024 05:46 AM
{
"ADDITIONALTABLES": {
"USERS": "SELECT userkey, owneronterminate,username, employeeid, systemusername, customproperty11, customproperty28, lastname, statuskey, employeeType, displayname, firstname, locationnumber, locationdesc, state, street, title, middlename, owner, comments, preferedFirstName, customproperty2, customproperty5, customproperty13, jobcode, customproperty14, customproperty4, job_function, customproperty1, customproperty15, customproperty6, customproperty7, customproperty9, customproperty8, customproperty10, customproperty12, customproperty16, customproperty17, customproperty18, customproperty19, customproperty21, customproperty22, customproperty3, costcenter, orgunitid, departmentNumber, departmentname, customproperty23, phonenumber, secondaryPhone, customproperty26, customproperty25, startdate, customproperty24, enddate, regioncode, entity, companyname, city, country, region, owneronterminate, LOCALAUTHENABLED FROM USERS"
},
"TABLEINDEXES": {
"currentusers": [
"employeeid","OWNERONTERMINATE",
"preferedfirstname",
"firstname",
"lastname",
"systemusername",
"customproperty11",
"displayname",
"statuskey",
"startdate",
"username"
],
"newuserdata": [
"preferedfirstname",
"firstname",
"lastname",
"systemusername",
"customproperty11",
"displayname",
"statuskey",
"employeeid"
]
},
"COMPUTEDCOLUMNS": [
"USERNAME",
"CUSTOMPROPERTY11",
"DISPLAYNAME",
"SYSTEMUSERNAME",
"OWNERONTERMINATE"
],
"PREPROCESSQUERIES": [
"UPDATE newuserdata nu SET nu.owner = (SELECT username FROM currentusers cu WHERE cu.employeeid = nu.owner)",
"UPDATE newuserdata nu SET nu.statuskey = '1' WHERE nu.customproperty5 IN ('A', 'L', 'P', 'S', 'F')",
"UPDATE newuserdata nu SET nu.statuskey = '0' WHERE nu.customproperty5 NOT IN ('A', 'L', 'P', 'S', 'F')",
"UPDATE newuserdata nu SET nu.title = SUBSTRING(nu.title, 1, 64) WHERE LENGTH(nu.title) > 64","UPDATE newuserdata nu SET nu.OWNERONTERMINATE = (CASE WHEN nu.JOB_FUNCTION = 'Executive' THEN '1' ELSE (SELECT cu.userkey FROM currentusers cu WHERE cu.username = nu.owner) END)",
"CUSTOMFUNCTION###DOPREPROCESS",
"INSERT INTO newuserdata(username, employeeid, systemusername, customproperty28, lastname, statuskey, employeeType, displayname, firstname, locationnumber, locationdesc, state, street, title, middlename, owner, comments, preferedFirstName, customproperty2, customproperty5, customproperty13, jobcode, customproperty14, customproperty4, job_function, customproperty1, customproperty15, customproperty6, customproperty7, customproperty9, customproperty8, customproperty10, customproperty12, customproperty16, customproperty17, customproperty18, customproperty19, customproperty21, customproperty22, customproperty3, costcenter, orgunitid, departmentNumber, departmentname, customproperty23, phonenumber, secondaryPhone, customproperty26, customproperty25, startdate, customproperty24, enddate, regioncode, entity, companyname, city, country, region) SELECT cu.username, cu.employeeid, cu.systemusername, cu.customproperty28, cu.lastname, 0 as statuskey, cu.employeeType, cu.displayname, cu.firstname, cu.locationnumber, cu.locationdesc, cu.state, cu.street, cu.title, cu.middlename, cu.owner, cu.comments, cu.preferedFirstName, cu.customproperty2, cu.customproperty5, cu.customproperty13, cu.jobcode, cu.customproperty14, cu.customproperty4, cu.job_function, cu.customproperty1, cu.customproperty15, cu.customproperty6, cu.customproperty7, cu.customproperty9, cu.customproperty8, cu.customproperty10, cu.customproperty12, cu.customproperty16, cu.customproperty17, cu.customproperty18, cu.customproperty19, cu.customproperty21, cu.customproperty22, cu.customproperty3, cu.costcenter, cu.orgunitid, cu.departmentNumber, cu.departmentname, cu.customproperty23, cu.phonenumber, cu.secondaryPhone, cu.customproperty26, cu.customproperty25, cu.startdate, cu.customproperty24, sysdate() as enddate, cu.regioncode, cu.entity, cu.companyname, cu.city, cu.country, cu.region FROM currentusers cu WHERE cu.statuskey = 1 AND cu.employeeid IS NOT NULL AND LENGTH(TRIM(cu.employeeid))>0 AND cu.customproperty28 IS NOT NULL AND LENGTH(TRIM(cu.customproperty28))>0 AND cu.startdate IS NOT NULL AND (DATE(cu.startdate) < SUBDATE(DATE(sysdate()),INTERVAL 1 DAY)) AND cu.employeeid IN ('0000033','0000058','0000107','0000279','0000280','0000281') AND cu.LOCALAUTHENABLED != '1' AND cu.employeeid NOT IN (SELECT nu.employeeid FROM newuserdata nu) AND (SELECT count(*) FROM CURRENTUSERS cu1 WHERE cu1.statuskey = 1 AND cu1.employeeid IS NOT NULL AND LENGTH(TRIM(cu1.employeeid))>0 AND cu1.customproperty28 IS NOT NULL AND LENGTH(TRIM(cu1.customproperty28))>0 AND cu1.startdate IS NOT NULL AND (DATE(cu1.startdate) < SUBDATE(DATE(sysdate()),INTERVAL 1 DAY)) AND cu1.employeeid IN ('0000033','0000058','0000107','0000279','0000280','0000281') AND cu1.LOCALAUTHENABLED != '1' AND cu1.employeeid NOT IN (SELECT nu1.employeeid FROM newuserdata nu1)) < 10"
],
"CUSTOMFUNCTIONS": {
"DOPREPROCESS": {
"FULLCLASSNAME": "com.mckesson.saviynt.preprocessor.ConnectionUtilityService",
"METHODNAME": "doPreProcess"
}
}
}