Hi Rushikesh, Pls find below. We have to bring in hierarchy to one attribute ..CP60= Manger name, senior mananger name, director name,vp name etc.. These values are different for each user
User import mapping
{
"ImportType": "RAAS",
"ResponsePath": "Report_Entry",
"ImportMapping": {
"CUSTOMPROPERTY17": "BUSINESS_UNIT~#~char",
"CUSTOMPROPERTY18": "BUSINESS_UNIT_CODE~#~char",
"CITY": "BUSINESS_CITY~#~char",
"COMPANYNAME": "COMPANY~#~char",
"CUSTOMPROPERTY14": "COMPANY_CODE~#~char",
"COSTCENTER": "COST_CENTER~#~char",
"CUSTOMPROPERTY10": "COST_CENTER_DESC~#~char",
"COUNTRY": "COUNTRY_CODE~#~char",
"DEPARTMENTNAME": "COST_CENTER_DESC~#~char",
"DEPARTMENTNUMBER": "COST_CENTER~#~char",
"CUSTOMPROPERTY16": "DIVISION~#~char",
"EMAIL": "WORK_EMAIL~#~char",
"EMPLOYEECLASS": "EMP_TYP_CD~#~char",
"EMPLOYEEID": "TWID~#~char",
"EMPLOYEETYPE": "#CONST#Employee~#~char",
"ENDDATE": "TRMNTN_DT~#~char",
"ENTITY": "COMP_HIERARCHY~#~char",
"FIRSTNAME": "PRFRRD_FRST_NM~#~char",
"CUSTOMPROPERTY7": "HIRE_REASON~#~char",
"CUSTOMPROPERTY5": "HR_REP_TWID~#~char",
"CUSTOMPROPERTY1": "IS_EMP_ASSISTANT~#~char",
"JOBCODE": "POSITION_LEVEL~#~char",
"CUSTOMPROPERTY2": "LAST_DAY_WORKED~#~char",
"LASTNAME": "PRFRRD_LAST_NM~#~char",
"LOCATIONDESC": "LOCATION_DESC~#~char",
"LOCATIONNUMBER": "EMPLOYMENT_LOC~#~char",
"CUSTOMPROPERTY44": "MANAGER_TWID~#~char",
"MIDDLENAME": "PRFRRD_MIDDLE_NM~#~char",
"CUSTOMPROPERTY4": "OFFSITE_FLAG~#~char",
"CUSTOMPROPERTY3": "PRONOUN~#~char",
"CUSTOMPROPERTY6": "VIP_FLAG~#~char",
"CUSTOMPROPERTY8": "INITIATED_DATE~#~char",
"PHONENUMBER": "WORK_PHONE~#~char",
"PREFEREDFIRSTNAME": "PRFRRD_FRST_NM~#~char",
"STARTDATE": "HIRE_DT~#~char",
"STATE": "BUSINESS_STATE~#~char",
"STATUSKEY": "EMP_STTS_CD~#~char",
"STREET": "BUSINESS_ADDRESS_1~#~char",
"SYSTEMUSERNAME": "WID~#~char",
"TERMDATE": "TRMNTN_DT~#~char",
"TITLE": "JOB_TITLE~#~char",
"LEAVESTATUS": "EMP_STTS_CD~#~char",
"Job_Function": "Job_Family_ID~#~char",
"CUSTOMPROPERTY51": "IAM_ID~#~char",
"CUSTOMPROPERTY52": "S4_Company_Code~#~char",
"CUSTOMPROPERTY53": "S4_Functional_Area_Code",
"CUSTOMPROPERTY54": "S4_Brand_Code~#~char"
}
}
I have removed email generation part in preprocessor.
MODIFYUSERDATAJSON
{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,SYSTEMUSERNAME,statuskey,USERNAME,EMPLOYEEID,email,OWNER,FIRSTNAME,MIDDLENAME,LASTNAME,CUSTOMPROPERTY20,CUSTOMPROPERTY17,CUSTOMPROPERTY15,DEPARTMENTNAME,COUNTRY,CUSTOMPROPERTY16,TITLE,CUSTOMPROPERTY29,CUSTOMPROPERTY19,CUSTOMPROPERTY42,CUSTOMPROPERTY41,CUSTOMPROPERTY45,CUSTOMPROPERTY7 FROM USERS where customproperty20='Workday'",
"ACCOUNTS": "SELECT ACCOUNTKEY,NAME,ENDPOINTKEY,CUSTOMPROPERTY2,CUSTOMPROPERTY35,CUSTOMPROPERTY54,CUSTOMPROPERTY55 FROM ACCOUNTS WHERE ENDPOINTKEY=9",
"LOCATIONDS": "SELECT ATTRIBUTE1, ATTRIBUTE2 FROM DATASET_VALUES WHERE DATASETNAME = 'Countryname'",
"LOCATIONWS": "SELECT ATTRIBUTE1, ATTRIBUTE2 FROM DATASET_VALUES WHERE DATASETNAME = 'REGION_DATASET'",
"LOCATIONRS": "SELECT ATTRIBUTE1,ATTRIBUTE2 FROM DATASET_VALUES WHERE DATASETNAME = 'UN_code_dataset'",
},
"COMPUTEDCOLUMNS": [
"customproperty20",
"DISPLAYNAME",
"OWNER",
"lastname",
"statuskey",
"customproperty11",
"customproperty7",
"CUSTOMPROPERTY42",
"CUSTOMPROPERTY41",
"CUSTOMPROPERTY29",
"CUSTOMPROPERTY19",
"EMAIL",
"customproperty12",
"customproperty27",
"country",
"region",
"customproperty32",
"customproperty33",
"employeetype",
"email",
"customproperty41",
"CUSTOMPROPERTY45"
],
"TABLEINDEXES": {
"NEWUSERDATA": [
"STATUSKEY",
"employeetype",
"LeaveStatus",
"CUSTOMPROPERTY20",
"CUSTOMPROPERTY16",
"locationnumber",
"OWNER",
"lastname",
"DISPLAYNAME",
"preferedfirstname",
"employeeid",
"systemusername",
"customproperty12",
"customproperty32",
"CUSTOMPROPERTY7",
"region",
"country",
"CUSTOMPROPERTY17",
"CUSTOMPROPERTY18",
"CUSTOMPROPERTY29",
"CUSTOMPROPERTY19",
"customproperty27",
"title",
"EMAIL"
],
"currentusers": [
"STATUSKEY",
"USERNAME",
"SYSTEMUSERNAME",
"owner",
"lastname",
"customproperty7",
"CUSTOMPROPERTY29",
"CUSTOMPROPERTY19",
"country",
"employeeid",
"email",
"CUSTOMPROPERTY17",
"CUSTOMPROPERTY20"
]
},
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.STATUSKEY = case when (LeaveStatus='INACTIVE' or LeaveStatus='SUSPEND' or LeaveStatus='Suspend') then '0' else '1' end",
"UPDATE NEWUSERDATA SET NEWUSERDATA.employeetype ='Employee'",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY20 ='Workday',NEWUSERDATA.owner = (select CURRENTUSERS.USERNAME from CURRENTUSERS where CURRENTUSERS.EMPLOYEEID=NEWUSERDATA.CUSTOMPROPERTY44 limit 1),NEWUSERDATA.DISPLAYNAME = CONCAT(NEWUSERDATA.lastname,',',' ',NEWUSERDATA.firstname),NEWUSERDATA.preferedfirstname = CASE WHEN (NEWUSERDATA.preferedfirstname ='' or NEWUSERDATA.preferedfirstname is null ) THEN NEWUSERDATA.firstname ELSE NEWUSERDATA.preferedfirstname END WHERE NEWUSERDATA.STATUSKEY=1",
"UPDATE NEWUSERDATA SET NEWUSERDATA.lastname = REPLACE(REPLACE(NEWUSERDATA.lastname, '.', ''), '''', '')",
"UPDATE NEWUSERDATA nu, currentusers cu set nu.customproperty12 = cu.email where nu.owner = cu.username and nu.STATUSKEY=1",
"UPDATE NEWUSERDATA nu, currentusers cu set nu.customproperty27 = cu.email where nu.customproperty5 = cu.employeeid and nu.STATUSKEY=1",
}
}
}
