Hello everyone,
I need help with identifying an issue, i found a solution for my requirement of "assigning the CUSTOMER in users based on the ORGUNITID "
i did the following:
"UPDATE NEWUSERDATA NU SET NU.customer = (select customername from currentcustomer where currentcustomer.CUSTOMERNAME = NU.Orgunitid)",
it worked and the customer was assigned yesterday.
i tried to test today and the issue is back, it is not getting assigned although i am using the same exact modifyuserjson, i saved a full copy in case anything changes and i am using it. i am not sure what the problem and spent hours debugging the logs..
full import json :
{
"connection": "acctAuth",
"url": "https:xxxxxxxxxxxxxxxx,
"httpMethod": "GET",
"httpHeaders": {
"Authorization": "${access_token}",
"Accept": "axxxxxxl",
"NamespaceM": "xxxxx",
"NamespaceD": "xxxxxxxxxxxxxx"
},
"userResponsePath": "xxxxxxxxxxxxxxrties",
"colsToPropsMap": {
"secondaryemail": "d:Alternativeemail~#~char",
"email": "d:email~#~char",
"employeeType": "d:Type~#~char",
"employeeclass": "d:Acctype~#~char",
"customproperty18": "d:Btrtl~#~char",
"customproperty13": "d:Bwplant~#~char",
"city": "d:Compcity~#~char",
"companyname": "d:Compname~#~char",
"customproperty12": "d:Comppostalcode~#~char",
"street": "d:Compstreet~#~char",
"costcenter": "d:Costcenter~#~char",
"country": "d:Countrycode~#~char",
"customproperty2": "d:Dateofbirth~#~char",
"customproperty7": "d:Emphier~#~char",
"startdate": "d:Entrydate~#~char",
"enddate": "d:Exitdate~#~char",
"job_function": "d:Function~#~char",
"customproperty30": "d:Futurelinemanagerpernr~#~char",
"customproperty9": "d:Futureorgunitid~#~char",
"customproperty21": "d:Perid~#~char",
"employeeId": "d:Persno~#~char",
"customproperty4": "d:Hcmsystem~#~char",
"manager": "d:Linemanagerpernr~#~char",
"phonenumber": "d:Mobnr~#~char",
"lastname": "d:Nachn~#~char",
"customproperty23": "d:Noprov~#~char",
"customproperty8": "d:Orgpath~#~char",
"customproperty11": "d:Orgunitchangedate~#~char",
"departmentname": "d:Short~#~char",
"customproperty10": "d:Futurelinemanagerperid~#~char",
"middlename": "d:Midnm~#~char",
"orgunitid": "d:Orgunitid~#~char",
"departmentnumber": "d:Orgunitid~#~char",
"displayname": "d:Short~#~char",
"customproperty22": "d:Zasesclnt~#~char",
"customproperty19": "d:Persg~#~char",
"customproperty20": "d:Persk~#~char",
"customproperty16": "d:Plans~#~char",
"customproperty5": "d:Status~#~char",
"secondaryPhone": "d:Telnr~#~char",
"title": "d:Title1~#~char",
"customproperty1": "d:Title2~#~char",
"firstname": "d:Vorna~#~char",
"customproperty17": "d:Werks~#~char",
"customproperty15": "d:Zstelle~#~char"
},
"pagination": {
"offset": {
"offsetParam": "$skip",
"batchParam": "$top",
"batchSize": 1000000,
"totalCountPath": 10000
}
},
"statuskey": {
"1": "A",
"0": "I",
"1": "P"
},
"successResponses": {
"statusCode": [
200
]
}
}
Full modifyuserdatajson:
{
"ADDITIONALTABLES": {
"USERS": "SELECT customproperty2, customproperty11, customproperty9, lastname, firstname, departmentname, employeeType, Orgunitid, customproperty4, country, statuskey, customproperty5, username, startdate, enddate, customproperty3, manager, customproperty14, customproperty21, userkey, customproperty13, customer, secondaryManager, owneronterminate, customproperty30, owner, customproperty25, customproperty34 FROM USERS",
"CUSTOMER": "SELECT CUSTOMERKEY, customername, DISPLAYNAME FROM customer",
"CUST_USERS": "SELECT USERKEY, CUSTOMERKEY FROM customer_users"
},
"COMPUTEDCOLUMNS": [
"customproperty2",
"customproperty11",
"customproperty9",
"displayname",
"Orgunitid",
"username",
"statuskey",
"startdate",
"customer",
"enddate",
"statuskey",
"manager",
"customproperty14",
"customproperty15",
"customproperty13",
"secondaryManager",
"userkey",
"owneronterminate",
"customproperty30",
"customproperty3",
"customproperty25",
"customproperty34"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU SET NU.customproperty2 = DATE_FORMAT(STR_TO_DATE(NU.customproperty2, '%Y%m%d'), '%d.%m.%Y')",
"UPDATE NEWUSERDATA NU SET NU.customproperty11 = CASE WHEN NU.customproperty11 = '00000000' THEN NULL ELSE DATE_FORMAT(STR_TO_DATE(NU.customproperty11, '%Y%m%d'), '%d.%m.%Y') END",
"UPDATE NEWUSERDATA NU SET NU.customproperty9 = CASE WHEN NU.customproperty9 = '00000000' THEN NULL ELSE NU.customproperty9 END",
"UPDATE NEWUSERDATA NU SET NU.departmentname = CASE WHEN COALESCE(NU.departmentname, '') <> '' THEN NU.departmentname ELSE NU.departmentname END",
"UPDATE NEWUSERDATA NU SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', NU.departmentname, IF(NU.employeeType = 'E', ' (EXT)', ''), ')') WHERE NU.departmentname IS NOT NULL",
"UPDATE NEWUSERDATA NU SET NU.Orgunitid = CONCAT(NU.customproperty4, '-', NU.Orgunitid, '')",
"UPDATE NEWUSERDATA NU SET NU.username = CASE WHEN NU.username IS NULL OR NU.username = '' THEN CONCAT(NU.country, NU.employeeId) ELSE NU.username END",
"UPDATE NEWUSERDATA NU SET NU.manager = CONCAT(NU.country, NU.manager)",
"UPDATE NEWUSERDATA NU SET NU.customproperty30 = CONCAT(NU.country, NU.customproperty30)",
"UPDATE NEWUSERDATA NU SET NU.secondaryManager = NU.customproperty30",
"UPDATE NEWUSERDATA NU SET NU.statuskey = CASE WHEN NU.customproperty5 IN ('P', 'A') THEN 1 WHEN NU.customproperty5 = 'I' THEN 0 END",
"UPDATE NEWUSERDATA NU SET NU.startdate = DATE_FORMAT(STR_TO_DATE(NU.startdate, '%Y%m%d'), '%b %d, %Y')",
"UPDATE NEWUSERDATA NU SET NU.enddate = CASE WHEN NU.customproperty5 = 'I' THEN DATE_FORMAT(NU.enddate, '%m/%d/%Y') WHEN NU.customproperty5 IN ('P', 'A') THEN NULL ELSE NU.enddate END",
"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.username = CU.username SET NU.customproperty3 = NU.username WHERE NU.customproperty3 IS NULL OR NU.customproperty3 = '' ",
"UPDATE NEWUSERDATA NU SET NU.OWNERONTERMINATE = CASE WHEN NU.manager IS NOT NULL THEN NU.manager ELSE '' END",
"UPDATE NEWUSERDATA NU SET NU.customer = (select customername from currentcustomer where currentcustomer.CUSTOMERNAME = NU.Orgunitid)",
"UPDATE NEWUSERDATA NU SET NU.customproperty14 = CASE WHEN NU.customproperty15 = '50404368' THEN 'Poolberater' WHEN NU.customproperty15 = '50404373' THEN 'Projektberater' WHEN NU.customproperty15 = '50404374' THEN 'Fachberater' ELSE NULL END",
"UPDATE NEWUSERDATA NU SET NU.customproperty13 = CONCAT(NU.customproperty13)",
"UPDATE NEWUSERDATA NU SET NU.customproperty25 = CASE WHEN NU.customproperty4 LIKE '%.92' AND NU.employeeId LIKE '40%' THEN 'ro' WHEN NU.customproperty4 LIKE '%.92' THEN 'de' WHEN NU.customproperty4 LIKE '%.H2' THEN 'hu' WHEN NU.customproperty4 LIKE '%.K2' THEN 'hr' WHEN NU.customproperty4 LIKE '%.S2' THEN 'sl' WHEN NU.customproperty4 LIKE '%.I2' THEN 'it' WHEN NU.customproperty4 LIKE '%.C2' THEN 'cs' ELSE 'en' END"
]
}
i will attach the logs from yesterday "when it worked"
and also today.
Thank you for your help! because i already tried everything to debug it..
