Click HERE to see how Saviynt Intelligence is transforming the industry. |
05/30/2024 03:57 AM
Hi Team, below is the users.sav file used for importing users through schema.
#USER_NOT_IN_FILE_ACTION=NOACTION
#FILENAME=HCM_UserImport
#DELIMITER=,
#SKIP_NUMBER_OF_LINES=1
#ZERODAYPROVISIONING=TRUE
#userReconcillationField=username
#GENERATESYSTEMUSERNAME=FALSE
#GENERATEEMAIL=TRUE
#EMAILTEMPLATE= HCM_Import_Job_FailureEmail
#CHECKRULES=TRUE
#MODIFYUSERDATAJSON={ "ADDITIONALTABLES" :{ "USERS" : "SELECT username,customproperty10,comments,customproperty17,customproperty62,customproperty63,customproperty64,manager,owner,customproperty11,customproperty18,email,customproperty7,enddate FROM USERS" },"COMPUTEDCOLUMNS" : [ "statuskey","manager","firstname","lastname","preferedFirstName","middlename","customproperty45","customproperty47","enddate"],"PREPROCESSQUERIES" :["DELETE FROM NEWUSERDATA WHERE (manager is null or manager='') and customproperty10 IN ('REN_CWK','HIRE','REHIRE','HIRE_ADD_WORK_RELATION','ORA_ADD_PWK_WORK_RELATION','ADD_CWK_WORK_RELATION','ADD_CWK','ADD_PEN_WKR')","UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME=CURRENTUSERS.USERNAME SET NEWUSERDATA.STATUSKEY = (CASE WHEN (CURRENTUSERS.comments like '%Users are being terminated through Certification%') THEN '0' WHEN ((CURRENTUSERS.comments like '%User Terminated By%') AND NEWUSERDATA.customproperty17='Active' AND (NEWUSERDATA.customproperty10 in ('REN_CWK','HIRE','REHIRE','HIRE_ADD_WORK_RELATION','ORA_ADD_PWK_WORK_RELATION','ADD_CWK_WORK_RELATION','ADD_CWK'))) THEN '1' WHEN ((CURRENTUSERS.comments like '%User Terminated By%') AND NEWUSERDATA.customproperty17='Active' AND (NEWUSERDATA.customproperty10 not in ('REN_CWK','HIRE','REHIRE','HIRE_ADD_WORK_RELATION','ORA_ADD_PWK_WORK_RELATION','ADD_CWK_WORK_RELATION','ADD_CWK'))) THEN '0' WHEN ((CURRENTUSERS.customproperty10='DISABLE') AND (CURRENTUSERS.customproperty11='IT_DISABLE') AND NEWUSERDATA.customproperty17='Active') THEN '0' WHEN ((CURRENTUSERS.comments like '%Users are being terminated through Certification%') AND NEWUSERDATA.customproperty17='Active' AND (NEWUSERDATA.customproperty10 in ('REN_CWK','HIRE','REHIRE','HIRE_ADD_WORK_RELATION','ORA_ADD_PWK_WORK_RELATION','ADD_CWK_WORK_RELATION','ADD_CWK'))) THEN '1' WHEN (CURRENTUSERS.customproperty63='Y' AND NEWUSERDATA.customproperty17='Active' AND (NEWUSERDATA.customproperty10 in ('REN_CWK','HIRE','REHIRE','HIRE_ADD_WORK_RELATION','ORA_ADD_PWK_WORK_RELATION','ADD_CWK_WORK_RELATION','ADD_CWK'))) THEN '1' WHEN (CURRENTUSERS.customproperty64='Y' AND NEWUSERDATA.customproperty17='Active' AND (NEWUSERDATA.customproperty10 in ('REN_CWK','HIRE','REHIRE','HIRE_ADD_WORK_RELATION','ORA_ADD_PWK_WORK_RELATION','ADD_CWK_WORK_RELATION','ADD_CWK'))) THEN '1' WHEN (CURRENTUSERS.customproperty62='LCR' AND NEWUSERDATA.customproperty17='Active' AND (NEWUSERDATA.customproperty10 in ('REN_CWK','HIRE','REHIRE','HIRE_ADD_WORK_RELATION','ORA_ADD_PWK_WORK_RELATION','ADD_CWK_WORK_RELATION','ADD_CWK'))) THEN '1' WHEN CURRENTUSERS.customproperty17='Suspended' and CURRENTUSERS.customproperty11 not in ('LOA_MAT','LOA_PAT','LOA_MED') THEN '0' WHEN CURRENTUSERS.customproperty17='Inactive' and CURRENTUSERS.customproperty18='Y' THEN '0' WHEN (CURRENTUSERS.comments like '%User Terminated By%') THEN '0' WHEN CURRENTUSERS.customproperty17='Inactive' THEN '0' WHEN CURRENTUSERS.customproperty63='Y' THEN '0' WHEN CURRENTUSERS.customproperty64='Y' THEN '0' WHEN CURRENTUSERS.customproperty62='LCR' THEN '0' ELSE '1' END)","UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME=CURRENTUSERS.USERNAME SET NEWUSERDATA.manager = (CASE WHEN NEWUSERDATA.customproperty17='Inactive' THEN CURRENTUSERS.owner WHEN NEWUSERDATA.customproperty17='Active' THEN NEWUSERDATA.manager ELSE NEWUSERDATA.manager END)","UPDATE NEWUSERDATA SET NEWUSERDATA.firstname=replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(NEWUSERDATA.firstname,'ã','a'),'ñ','n'),'ú','u'),'ó','o'),'č','c'),'š','s'),'í','i'),'ř','r'),'ž','z'),'ů','u'),'ý','y'),'á','a'),'Š','S'),'Č','C'),'Ď','D'),'ó','o'),'ě','e'),'ň','n'),'é','e'),'ü','u'),'Ř','R'),'Ž','Z'),'é','e'),'ö','o'),'ů','u'),'ê','e'),'å','a'),'ä','a'),'ť','t'),'ç','c'),'ô','o'),'è','e')","UPDATE NEWUSERDATA SET NEWUSERDATA.lastname=replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(NEWUSERDATA.lastname,'ã','a'),'ñ','n'),'ú','u'),'ó','o'),'č','c'),'š','s'),'í','i'),'ř','r'),'ž','z'),'ů','u'),'ý','y'),'á','a'),'Š','S'),'Č','C'),'Ď','D'),'ó','o'),'ě','e'),'ň','n'),'é','e'),'ü','u'),'Ř','R'),'Ž','Z'),'é','e'),'ö','o'),'ů','u'),'ê','e'),'å','a'),'ä','a'),'ť','t'),'ç','c'),'ô','o'),'è','e')","UPDATE NEWUSERDATA SET NEWUSERDATA.preferedFirstName=replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(NEWUSERDATA.preferedFirstName,'ã','a'),'ñ','n'),'ú','u'),'ó','o'),'č','c'),'š','s'),'í','i'),'ř','r'),'ž','z'),'ů','u'),'ý','y'),'á','a'),'Š','S'),'Č','C'),'Ď','D'),'ó','o'),'ě','e'),'ň','n'),'é','e'),'ü','u'),'Ř','R'),'Ž','Z'),'é','e'),'ö','o'),'ů','u'),'ê','e'),'å','a'),'ä','a'),'ť','t'),'ç','c'),'ô','o'),'è','e')","UPDATE NEWUSERDATA SET NEWUSERDATA.middlename=replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(NEWUSERDATA.middlename,'ã','a'),'ñ','n'),'ú','u'),'ó','o'),'č','c'),'š','s'),'í','i'),'ř','r'),'ž','z'),'ů','u'),'ý','y'),'á','a'),'Š','S'),'Č','C'),'Ď','D'),'ó','o'),'ě','e'),'ň','n'),'é','e'),'ü','u'),'Ř','R'),'Ž','Z'),'é','e'),'ö','o'),'ů','u'),'ê','e'),'å','a'),'ä','a'),'ť','t'),'ç','c'),'ô','o'),'è','e')","UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME=CURRENTUSERS.USERNAME SET NEWUSERDATA.customproperty45 = (CASE WHEN NEWUSERDATA.email != '' THEN 'Y' ELSE 'N' END)","UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME=CURRENTUSERS.USERNAME SET NEWUSERDATA.customproperty47 = (CASE WHEN NEWUSERDATA.customproperty7 != '' THEN 'Y' ELSE 'N' END)","UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME=CURRENTUSERS.USERNAME SET CURRENTUSERS.enddate = (CASE WHEN (Date(NEWUSERDATA.startdate) > Date(CURRENTUSERS.enddate)) THEN CURRENTUSERS.enddate ELSE '' END)"]}
customproperty1,manager,region,location,customproperty3,customproperty4,costcenter,customproperty30,customproperty5,companyname,customproperty6,customproperty37,preferedFirstName,customproperty17,startdate,customproperty23,title,jobCode,job_function,email,customproperty27,customproperty8,firstname,lastname,middlename,customproperty42,customproperty43,customproperty44,customproperty9,username,regioncode,customproperty10,customproperty11,country,customproperty12,customproperty13,customproperty16,customproperty14,customproperty15,customproperty19,customproperty20,customproperty25,customproperty26,customproperty7,jobDescription,customproperty24,customproperty18,customproperty39
Error : Error in Users Import - Error while processing
data: Data truncation: Incorrect datetime value:
'' for column 'enddate' at row 1
In above query startdate came as : 20240512 in file
05/30/2024 07:33 AM
Remove #
# means commenting line
05/30/2024 07:42 AM
now its change of requirement now i needs to null enddate if hcm file startdate is greater than saviynt table enddate. i just try below query job is running successfully but enddate is not updated as per logic .
logic : "UPDATE CURRENTUSERS LEFT JOIN NEWUSERDATA ON CURRENTUSERS.USERNAME=NEWUSERDATA.USERNAME SET CURRENTUSERS.enddate = (CASE WHEN (Date(NEWUSERDATA.startdate)) > Date(CURRENTUSERS.enddate)) THEN '' ELSE CURRENTUSERS.enddate END)"
user enddate is updated as blank even condition fail or pass
05/30/2024 10:50 AM
Hi @Sreejith ,
Two things should be corrected in your script -
1. Update only happens on NEWUSERDATA (temp) table because only this table would be imported to users table after all the changes/updates are done.
2. Don't use '' for blank, use NULL (You get an error for '': Data truncation: Incorrect datetime value: '')
Updated Query:
"UPDATE CURRENTUSERS LEFT JOIN NEWUSERDATA ON CURRENTUSERS.USERNAME=NEWUSERDATA.USERNAME SET NEWUSERDATA.enddate = CASE WHEN Date(NEWUSERDATA.startdate) > Date(CURRENTUSERS.enddate) THEN NULL ELSE CURRENTUSERS.enddate END"
Please try!
If this helps, please consider selecting Accept As Solution and hit Kudos
05/30/2024 11:45 AM
Hi @PremMahadikar thanks for your reply.
The logic you put will not satisfy out requirements.
The requirement : our current design is enddate is only for contractor and in HR file enddate will map to CP 39 and in sav2sav it will compute the enddate based on some requirement and it is working fine in prod. But during rehire once hr is triggering new start date and definitely that will be future date than previous termination date(that is mapped to enddate when terminated) so saviynt simply ignore that user and updates are not flowing.
Currently our plan is to blank the enddate in the saviynt table whenever rehire happens in user.sav file so that's we put a logic as per above :
WHEN (Date(NEWUSERDATA.startdate)) > Date(CURRENTUSERS.enddate)) THEN ''
Please help me achieve our usecase.
kindly let me know if any further questions.
05/30/2024 09:34 PM
06/06/2024 10:45 PM
@PremMahadikar am getting below eeror
Error while processing data: Data truncation: Incorrect datetime value:
and its implemented in user.sav file
06/09/2024 10:12 PM
UPDATE CURRENTUSERS
LEFT JOIN NEWUSERDATA ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME
SET NEWUSERDATA.enddate = CASE
WHEN STR_TO_DATE(NEWUSERDATA.startdate, '%Y-%m-%d') > STR_TO_DATE(CURRENTUSERS.enddate, '%Y-%m-%d') THEN NULL
ELSE CURRENTUSERS.enddate
END;
06/07/2024 03:45 PM
can you try passing only NEWUSERDATA.startdate > CURRENTUSERS.enddate