getting error while running user.sav file

[Sreejith]

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

[rushikeshvartak]

Remove # 

# means commenting line

[Sreejith]

@rushikeshvartak 

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

[PremMahadikar]

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.

• Thus, you need to set NEWUSERDATA.enddate 
• I also notice, there is no enddate column in your csv file. Please add enddate in your csv and sav file.

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

[Sreejith]

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.

[PremMahadikar]

@Sreejith ,

Are you using sav4sav DB connector?

[Sreejith]

@PremMahadikar  am getting below eeror

 Error while processing data: Data truncation: Incorrect datetime value: 

and its implemented in user.sav file

[rushikeshvartak]

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;

[PremMahadikar]

@Sreejith ,

can you try passing only NEWUSERDATA.startdate > CURRENTUSERS.enddate