2024-05-07/14:18:01.141 [{}] [https-jsse-nio-443-exec-41]  DEBUG auth.LoginController - Default Setup
2024-05-07/14:18:01.141 [{}] [https-jsse-nio-443-exec-41]  DEBUG auth.LoginController - URL TO SAML LOGIN2=/ECM/login/index?login=true&idp=https://sts.windows.net/7a7c2d99-79a9-4222-93e8-4d200704e629/ JSESSIONID =B7DB447D57EA8E82EB4C4BB9647991AE
2024-05-07/14:18:12.432 [{}] [https-jsse-nio-443-exec-40]  DEBUG controllers.HomeController - check session
2024-05-07/14:18:31.136 [{}] [https-jsse-nio-443-exec-31]  DEBUG auth.LoginController - Default Setup
2024-05-07/14:18:31.136 [{}] [https-jsse-nio-443-exec-31]  DEBUG auth.LoginController - URL TO SAML LOGIN2=/ECM/login/index?login=true&idp=https://sts.windows.net/7a7c2d99-79a9-4222-93e8-4d200704e629/ JSESSIONID =17F609FE2EDD6D920607E92AD842FC19
2024-05-07/14:18:31.615 [{}] [https-jsse-nio-443-exec-41]  DEBUG println.PrintlnToLogger - Println :: patterns: \bDELETE\b(.)*\bFROM\b\s+(`\bSAVIYNT_DB_TABLE\b`|\bSAVIYNT_DB_TABLE\b)
2024-05-07/14:18:31.616 [{}] [https-jsse-nio-443-exec-41]  DEBUG println.PrintlnToLogger - Println :: patterns: \bDELETE\b(.)*\bFROM\b\s+(`\bSAVIYNT_DB_TABLE\b`|\bSAVIYNT_DB_TABLE\b)
2024-05-07/14:18:31.669 [{}] [https-jsse-nio-443-exec-41]  DEBUG println.PrintlnToLogger - Println :: patterns: \bUPDATE\b\s+(`\bSAVIYNT_DB_TABLE\b`(, |,| )*|\bSAVIYNT_DB_TABLE\b(, |,| )*)+(.)*\bSET\b(.*)\s+(.)*\.*(\bSAVIYNT_DB_COLUMN\b|`\bSAVIYNT_DB_COLUMN\b`)
2024-05-07/14:18:31.671 [{}] [https-jsse-nio-443-exec-41]  DEBUG println.PrintlnToLogger - Println :: patterns: \bUPDATE\b\s+(`\bSAVIYNT_DB_TABLE\b`(, |,| )*|\bSAVIYNT_DB_TABLE\b(, |,| )*)+(.)*\bSET\b(.*)\s+(.)*\.*(\bSAVIYNT_DB_COLUMN\b|`\bSAVIYNT_DB_COLUMN\b`)
2024-05-07/14:18:32.044 [{}] [https-jsse-nio-443-exec-41]  DEBUG controllers.UsersController - params[modifyuserdatajson:{
    "ADDITIONALTABLES": {
        "USERS": "SELECT username, customproperty55, customproperty57, customproperty65, customproperty59, location, locationnumber, userkey, PREFEREDFIRSTNAME, CUSTOMPROPERTY4 from USERS"
    },
    "COMPUTEDCOLUMNS": [
        "statuskey",
        "customproperty55",
        "customproperty57",
        "customproperty59",
        "customproperty65",
        "userkey",
        "PREFEREDFIRSTNAME",
        "CUSTOMPROPERTY4"
    ],
    "PREPROCESSQUERIES": [
        "Delete from NEWUSERDATA where username not in ('503949_test2', '874666',  '875021', '875022')",
        "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME set NEWUSERDATA.customproperty57 = case when (CURRENTUSERS.customproperty57 is null or CURRENTUSERS.customproperty57 = '') then CURRENTUSERS.userkey + 20000 else CURRENTUSERS.customproperty57 end where NEWUSERDATA.username in ('503949_test2', '874666',  '875021', '875022')",
        "Update NEWUSERDATA NU join (select USRFILTER.USRNAME AS ausername, USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN, NU2.PREFEREDFIRSTNAME, NU2.CUSTOMPROPERTY4, USRFILTER.RECORD_HR_STATUS, CASE WHEN USRFILTER.RECORD_HR_STATUS = 'VALID' THEN 'PROCESSED' ELSE 'Reported to HR' END AS processed, CASE WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' ELSE 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' END AS userdn, ( CASE WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) >= 8 AND USRFILTER.PLN IS NULL) THEN LEFT(USRFILTER.PFN, 8) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NULL) THEN USRFILTER.PFN WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN = USRFILTER.PMN) THEN USRFILTER.PFN WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN))) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) > 3) THEN CONCAT(LEFT(USRFILTER.PFN, 4), LEFT(USRFILTER.PLN, 4)) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) >= 8) THEN CONCAT(LEFT(USRFILTER.PFN, (8-LENGTH(USRFILTER.PLN))), USRFILTER.PLN ) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN = USRFILTER.PMN) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN)-LENGTH(USRFILTER.PLN)), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PLN, 8-(LENGTH(USRFILTER.PFN)))) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8) AND USRFILTER.PFN = USRFILTER.PMN THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-(LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN))), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN = USRFILTER.PMN) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN)-LENGTH(USRFILTER.PLN)), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PMN IS NULL) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) ELSE NU2.customproperty59 END ) AS samaccountname from NEWUSERDATA NU2, ( SELECT INTUSERS.UCASE AS UCASE, INTUSERS.RECORD_HR_STATUS AS RECORD_HR_STATUS, NU1.USERNAME AS USRNAME, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PFN),'.',''),'-','')) AS PFN, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PMN),'.',''),'-','')) AS PMN, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PLN),'.',''),'-','')) AS PLN from NEWUSERDATA NU1, ( SELECT 'CASE 1' AS UCASE, 'INVALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, '' AS PLN, '' AS PMN from NEWUSERDATA where ((PREFEREDFIRSTNAME IS NOT NULL AND LENGTH(PREFEREDFIRSTNAME) > 0 AND LENGTH(PREFEREDFIRSTNAME)-LENGTH(REPLACE(PREFEREDFIRSTNAME, ' ', '')) > 2) OR (customproperty4 IS NOT NULL AND LENGTH(customproperty4) > 0 AND LENGTH(customproperty4)-LENGTH(REPLACE(customproperty4, ' ', '')) > 2)) UNION SELECT 'CASE 2' AS UCASE, 'INVALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, '' AS PLN, '' AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NULL OR LENGTH(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NULL OR LENGTH(FIRSTNAME) < 1) AND (customproperty4 IS NULL OR LENGTH(customproperty4) < 1) AND (LASTNAME IS NULL OR LENGTH(LASTNAME) < 1) UNION SELECT 'CASE 3' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 4' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 5' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 6' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 7' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 8' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 9' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, '' AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS null OR length(LASTNAME) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' UNION SELECT 'CASE 10' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, '' AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS null OR length(LASTNAME) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' UNION SELECT 'CASE 11' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 12' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 13' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 14' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-1) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 15' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 16' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 17' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 18' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 19' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 20' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 21' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 22' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 23' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 24' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 25' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 26' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 27' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 28' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 29' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 30' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 31' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 32' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 ) AS INTUSERS WHERE NU1.USERNAME = INTUSERS.UNAME ) AS USRFILTER WHERE NU2.USERNAME = USRFILTER.USRNAME AND NU2.statuskey='1' AND NU2.employeeclass IN ('Contingent Worker', 'Employee') AND (NU2.customproperty65 is null OR NU2.customproperty59 is null OR NU2.customproperty55 is null)) AS NU3 on NU.USERNAME = NU3.ausername left join CURRENTUSERS CU on NU3.ausername=CU.username set NU.customproperty55= case when (CU.customproperty55 is null or CU.customproperty55 = '') then NU3.userdn else CU.customproperty55 end, NU.customproperty65=case when (CU.customproperty65 is null or CU.customproperty65 = '') then NU3.processed else CU.customproperty65 end, NU.customproperty59=case when (CU.customproperty59 is null or CU.customproperty59 = '') then NU3.samaccountname else CU.customproperty59 end where NU.username in ('503949_test2', '874666',  '875021', '875022')"
    ]
}, delimeter:,, generateEmail:NO, zeroDayProvisioning:NO, checkrules:YES, source:admin, sourceaction:userUpload, isApi:FALSE, savmodule:, firstrow:YES, valideRule:NO, generateSystemUsername:NO, userReconcillationField:username, isCreateUser:NA, passwordExpired:0, uploadfile:org.springframework.web.multipart.commons.CommonsMultipartFile@1b0bbfff, controller:users, action:uploadusersubmit]
2024-05-07/14:18:36.679 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ArsRequestAllowedService - user =868598 roles=[2, 1]
2024-05-07/14:18:36.681 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ArsRequestAllowedService - vendorList-[]
2024-05-07/14:18:36.795 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Number of lines to skip : 1
2024-05-07/14:18:36.795 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Delimiter : ,
2024-05-07/14:18:36.850 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - importDataFromFile for ImportSAvDataUserService is started
2024-05-07/14:18:36.850 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - modifyuserdatajsonconfig= {
    "ADDITIONALTABLES": {
        "USERS": "SELECT username, customproperty55, customproperty57, customproperty65, customproperty59, location, locationnumber, userkey, PREFEREDFIRSTNAME, CUSTOMPROPERTY4 from USERS"
    },
    "COMPUTEDCOLUMNS": [
        "statuskey",
        "customproperty55",
        "customproperty57",
        "customproperty59",
        "customproperty65",
        "userkey",
        "PREFEREDFIRSTNAME",
        "CUSTOMPROPERTY4"
    ],
    "PREPROCESSQUERIES": [
        "Delete from NEWUSERDATA where username not in ('503949_test2', '874666',  '875021', '875022')",
        "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME set NEWUSERDATA.customproperty57 = case when (CURRENTUSERS.customproperty57 is null or CURRENTUSERS.customproperty57 = '') then CURRENTUSERS.userkey + 20000 else CURRENTUSERS.customproperty57 end where NEWUSERDATA.username in ('503949_test2', '874666',  '875021', '875022')",
        "Update NEWUSERDATA NU join (select USRFILTER.USRNAME AS ausername, USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN, NU2.PREFEREDFIRSTNAME, NU2.CUSTOMPROPERTY4, USRFILTER.RECORD_HR_STATUS, CASE WHEN USRFILTER.RECORD_HR_STATUS = 'VALID' THEN 'PROCESSED' ELSE 'Reported to HR' END AS processed, CASE WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' ELSE 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' END AS userdn, ( CASE WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) >= 8 AND USRFILTER.PLN IS NULL) THEN LEFT(USRFILTER.PFN, 8) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NULL) THEN USRFILTER.PFN WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN = USRFILTER.PMN) THEN USRFILTER.PFN WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN))) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) > 3) THEN CONCAT(LEFT(USRFILTER.PFN, 4), LEFT(USRFILTER.PLN, 4)) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) >= 8) THEN CONCAT(LEFT(USRFILTER.PFN, (8-LENGTH(USRFILTER.PLN))), USRFILTER.PLN ) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN = USRFILTER.PMN) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN)-LENGTH(USRFILTER.PLN)), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PLN, 8-(LENGTH(USRFILTER.PFN)))) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8) AND USRFILTER.PFN = USRFILTER.PMN THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-(LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN))), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN = USRFILTER.PMN) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN)-LENGTH(USRFILTER.PLN)), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PMN IS NULL) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) ELSE NU2.customproperty59 END ) AS samaccountname from NEWUSERDATA NU2, ( SELECT INTUSERS.UCASE AS UCASE, INTUSERS.RECORD_HR_STATUS AS RECORD_HR_STATUS, NU1.USERNAME AS USRNAME, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PFN),'.',''),'-','')) AS PFN, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PMN),'.',''),'-','')) AS PMN, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PLN),'.',''),'-','')) AS PLN from NEWUSERDATA NU1, ( SELECT 'CASE 1' AS UCASE, 'INVALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, '' AS PLN, '' AS PMN from NEWUSERDATA where ((PREFEREDFIRSTNAME IS NOT NULL AND LENGTH(PREFEREDFIRSTNAME) > 0 AND LENGTH(PREFEREDFIRSTNAME)-LENGTH(REPLACE(PREFEREDFIRSTNAME, ' ', '')) > 2) OR (customproperty4 IS NOT NULL AND LENGTH(customproperty4) > 0 AND LENGTH(customproperty4)-LENGTH(REPLACE(customproperty4, ' ', '')) > 2)) UNION SELECT 'CASE 2' AS UCASE, 'INVALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, '' AS PLN, '' AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NULL OR LENGTH(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NULL OR LENGTH(FIRSTNAME) < 1) AND (customproperty4 IS NULL OR LENGTH(customproperty4) < 1) AND (LASTNAME IS NULL OR LENGTH(LASTNAME) < 1) UNION SELECT 'CASE 3' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 4' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 5' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 6' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 7' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 8' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 9' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, '' AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS null OR length(LASTNAME) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' UNION SELECT 'CASE 10' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, '' AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS null OR length(LASTNAME) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' UNION SELECT 'CASE 11' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 12' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 13' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 14' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-1) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 15' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 16' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 17' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 18' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 19' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 20' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 21' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 22' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 23' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 24' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 25' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 26' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 27' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 28' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 29' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 30' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 31' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 32' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 ) AS INTUSERS WHERE NU1.USERNAME = INTUSERS.UNAME ) AS USRFILTER WHERE NU2.USERNAME = USRFILTER.USRNAME AND NU2.statuskey='1' AND NU2.employeeclass IN ('Contingent Worker', 'Employee') AND (NU2.customproperty65 is null OR NU2.customproperty59 is null OR NU2.customproperty55 is null)) AS NU3 on NU.USERNAME = NU3.ausername left join CURRENTUSERS CU on NU3.ausername=CU.username set NU.customproperty55= case when (CU.customproperty55 is null or CU.customproperty55 = '') then NU3.userdn else CU.customproperty55 end, NU.customproperty65=case when (CU.customproperty65 is null or CU.customproperty65 = '') then NU3.processed else CU.customproperty65 end, NU.customproperty59=case when (CU.customproperty59 is null or CU.customproperty59 = '') then NU3.samaccountname else CU.customproperty59 end where NU.username in ('503949_test2', '874666',  '875021', '875022')"
    ]
}
2024-05-07/14:18:36.850 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - connectionid= null
2024-05-07/14:18:36.850 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Start doImportDataPreprocessing
2024-05-07/14:18:36.850 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Checking for modifyuserdatajson config from file user import
2024-05-07/14:18:36.850 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - MODIFYUSERDATAJSON= {
    "ADDITIONALTABLES": {
        "USERS": "SELECT username, customproperty55, customproperty57, customproperty65, customproperty59, location, locationnumber, userkey, PREFEREDFIRSTNAME, CUSTOMPROPERTY4 from USERS"
    },
    "COMPUTEDCOLUMNS": [
        "statuskey",
        "customproperty55",
        "customproperty57",
        "customproperty59",
        "customproperty65",
        "userkey",
        "PREFEREDFIRSTNAME",
        "CUSTOMPROPERTY4"
    ],
    "PREPROCESSQUERIES": [
        "Delete from NEWUSERDATA where username not in ('503949_test2', '874666',  '875021', '875022')",
        "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME set NEWUSERDATA.customproperty57 = case when (CURRENTUSERS.customproperty57 is null or CURRENTUSERS.customproperty57 = '') then CURRENTUSERS.userkey + 20000 else CURRENTUSERS.customproperty57 end where NEWUSERDATA.username in ('503949_test2', '874666',  '875021', '875022')",
        "Update NEWUSERDATA NU join (select USRFILTER.USRNAME AS ausername, USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN, NU2.PREFEREDFIRSTNAME, NU2.CUSTOMPROPERTY4, USRFILTER.RECORD_HR_STATUS, CASE WHEN USRFILTER.RECORD_HR_STATUS = 'VALID' THEN 'PROCESSED' ELSE 'Reported to HR' END AS processed, CASE WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' ELSE 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' END AS userdn, ( CASE WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) >= 8 AND USRFILTER.PLN IS NULL) THEN LEFT(USRFILTER.PFN, 8) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NULL) THEN USRFILTER.PFN WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN = USRFILTER.PMN) THEN USRFILTER.PFN WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN))) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) > 3) THEN CONCAT(LEFT(USRFILTER.PFN, 4), LEFT(USRFILTER.PLN, 4)) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) >= 8) THEN CONCAT(LEFT(USRFILTER.PFN, (8-LENGTH(USRFILTER.PLN))), USRFILTER.PLN ) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN = USRFILTER.PMN) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN)-LENGTH(USRFILTER.PLN)), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PLN, 8-(LENGTH(USRFILTER.PFN)))) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8) AND USRFILTER.PFN = USRFILTER.PMN THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-(LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN))), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN = USRFILTER.PMN) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN)-LENGTH(USRFILTER.PLN)), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PMN IS NULL) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) ELSE NU2.customproperty59 END ) AS samaccountname from NEWUSERDATA NU2, ( SELECT INTUSERS.UCASE AS UCASE, INTUSERS.RECORD_HR_STATUS AS RECORD_HR_STATUS, NU1.USERNAME AS USRNAME, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PFN),'.',''),'-','')) AS PFN, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PMN),'.',''),'-','')) AS PMN, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PLN),'.',''),'-','')) AS PLN from NEWUSERDATA NU1, ( SELECT 'CASE 1' AS UCASE, 'INVALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, '' AS PLN, '' AS PMN from NEWUSERDATA where ((PREFEREDFIRSTNAME IS NOT NULL AND LENGTH(PREFEREDFIRSTNAME) > 0 AND LENGTH(PREFEREDFIRSTNAME)-LENGTH(REPLACE(PREFEREDFIRSTNAME, ' ', '')) > 2) OR (customproperty4 IS NOT NULL AND LENGTH(customproperty4) > 0 AND LENGTH(customproperty4)-LENGTH(REPLACE(customproperty4, ' ', '')) > 2)) UNION SELECT 'CASE 2' AS UCASE, 'INVALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, '' AS PLN, '' AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NULL OR LENGTH(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NULL OR LENGTH(FIRSTNAME) < 1) AND (customproperty4 IS NULL OR LENGTH(customproperty4) < 1) AND (LASTNAME IS NULL OR LENGTH(LASTNAME) < 1) UNION SELECT 'CASE 3' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 4' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 5' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 6' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 7' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 8' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 9' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, '' AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS null OR length(LASTNAME) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' UNION SELECT 'CASE 10' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, '' AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS null OR length(LASTNAME) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' UNION SELECT 'CASE 11' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 12' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 13' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 14' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-1) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 15' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 16' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 17' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 18' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 19' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 20' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 21' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 22' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 23' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 24' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 25' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 26' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 27' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 28' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 29' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 30' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 31' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 32' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 ) AS INTUSERS WHERE NU1.USERNAME = INTUSERS.UNAME ) AS USRFILTER WHERE NU2.USERNAME = USRFILTER.USRNAME AND NU2.statuskey='1' AND NU2.employeeclass IN ('Contingent Worker', 'Employee') AND (NU2.customproperty65 is null OR NU2.customproperty59 is null OR NU2.customproperty55 is null)) AS NU3 on NU.USERNAME = NU3.ausername left join CURRENTUSERS CU on NU3.ausername=CU.username set NU.customproperty55= case when (CU.customproperty55 is null or CU.customproperty55 = '') then NU3.userdn else CU.customproperty55 end, NU.customproperty65=case when (CU.customproperty65 is null or CU.customproperty65 = '') then NU3.processed else CU.customproperty65 end, NU.customproperty59=case when (CU.customproperty59 is null or CU.customproperty59 = '') then NU3.samaccountname else CU.customproperty59 end where NU.username in ('503949_test2', '874666',  '875021', '875022')"
    ]
}
2024-05-07/14:18:36.851 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - PREPROCESSQUERIES= [Delete from NEWUSERDATA where username not in ('503949_test2', '874666',  '875021', '875022'), UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME set NEWUSERDATA.customproperty57 = case when (CURRENTUSERS.customproperty57 is null or CURRENTUSERS.customproperty57 = '') then CURRENTUSERS.userkey + 20000 else CURRENTUSERS.customproperty57 end where NEWUSERDATA.username in ('503949_test2', '874666',  '875021', '875022'), Update NEWUSERDATA NU join (select USRFILTER.USRNAME AS ausername, USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN, NU2.PREFEREDFIRSTNAME, NU2.CUSTOMPROPERTY4, USRFILTER.RECORD_HR_STATUS, CASE WHEN USRFILTER.RECORD_HR_STATUS = 'VALID' THEN 'PROCESSED' ELSE 'Reported to HR' END AS processed, CASE WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' ELSE 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' END AS userdn, ( CASE WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) >= 8 AND USRFILTER.PLN IS NULL) THEN LEFT(USRFILTER.PFN, 8) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NULL) THEN USRFILTER.PFN WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN = USRFILTER.PMN) THEN USRFILTER.PFN WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN))) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) > 3) THEN CONCAT(LEFT(USRFILTER.PFN, 4), LEFT(USRFILTER.PLN, 4)) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) >= 8) THEN CONCAT(LEFT(USRFILTER.PFN, (8-LENGTH(USRFILTER.PLN))), USRFILTER.PLN ) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN = USRFILTER.PMN) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN)-LENGTH(USRFILTER.PLN)), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PLN, 8-(LENGTH(USRFILTER.PFN)))) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8) AND USRFILTER.PFN = USRFILTER.PMN THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-(LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN))), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN = USRFILTER.PMN) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN)-LENGTH(USRFILTER.PLN)), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PMN IS NULL) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) ELSE NU2.customproperty59 END ) AS samaccountname from NEWUSERDATA NU2, ( SELECT INTUSERS.UCASE AS UCASE, INTUSERS.RECORD_HR_STATUS AS RECORD_HR_STATUS, NU1.USERNAME AS USRNAME, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PFN),'.',''),'-','')) AS PFN, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PMN),'.',''),'-','')) AS PMN, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PLN),'.',''),'-','')) AS PLN from NEWUSERDATA NU1, ( SELECT 'CASE 1' AS UCASE, 'INVALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, '' AS PLN, '' AS PMN from NEWUSERDATA where ((PREFEREDFIRSTNAME IS NOT NULL AND LENGTH(PREFEREDFIRSTNAME) > 0 AND LENGTH(PREFEREDFIRSTNAME)-LENGTH(REPLACE(PREFEREDFIRSTNAME, ' ', '')) > 2) OR (customproperty4 IS NOT NULL AND LENGTH(customproperty4) > 0 AND LENGTH(customproperty4)-LENGTH(REPLACE(customproperty4, ' ', '')) > 2)) UNION SELECT 'CASE 2' AS UCASE, 'INVALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, '' AS PLN, '' AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NULL OR LENGTH(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NULL OR LENGTH(FIRSTNAME) < 1) AND (customproperty4 IS NULL OR LENGTH(customproperty4) < 1) AND (LASTNAME IS NULL OR LENGTH(LASTNAME) < 1) UNION SELECT 'CASE 3' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 4' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 5' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 6' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 7' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 8' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 9' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, '' AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS null OR length(LASTNAME) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' UNION SELECT 'CASE 10' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, '' AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS null OR length(LASTNAME) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' UNION SELECT 'CASE 11' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 12' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 13' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 14' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-1) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 15' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 16' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 17' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 18' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 19' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 20' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 21' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 22' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 23' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 24' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, customproperty4 AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 25' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 26' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 27' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 28' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 29' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 30' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 31' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 32' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from NEWUSERDATA where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 ) AS INTUSERS WHERE NU1.USERNAME = INTUSERS.UNAME ) AS USRFILTER WHERE NU2.USERNAME = USRFILTER.USRNAME AND NU2.statuskey='1' AND NU2.employeeclass IN ('Contingent Worker', 'Employee') AND (NU2.customproperty65 is null OR NU2.customproperty59 is null OR NU2.customproperty55 is null)) AS NU3 on NU.USERNAME = NU3.ausername left join CURRENTUSERS CU on NU3.ausername=CU.username set NU.customproperty55= case when (CU.customproperty55 is null or CU.customproperty55 = '') then NU3.userdn else CU.customproperty55 end, NU.customproperty65=case when (CU.customproperty65 is null or CU.customproperty65 = '') then NU3.processed else CU.customproperty65 end, NU.customproperty59=case when (CU.customproperty59 is null or CU.customproperty59 = '') then NU3.samaccountname else CU.customproperty59 end where NU.username in ('503949_test2', '874666',  '875021', '875022')]
2024-05-07/14:18:36.851 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - ADDITIONALTABLES= [USERS:SELECT username, customproperty55, customproperty57, customproperty65, customproperty59, location, locationnumber, userkey, PREFEREDFIRSTNAME, CUSTOMPROPERTY4 from USERS]
2024-05-07/14:18:36.851 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - COMPUTEDCOLUMNS= [STATUSKEY, CUSTOMPROPERTY55, CUSTOMPROPERTY57, CUSTOMPROPERTY59, CUSTOMPROPERTY65, USERKEY, PREFEREDFIRSTNAME, CUSTOMPROPERTY4]
2024-05-07/14:18:36.851 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - dopreprocess= true
2024-05-07/14:18:36.851 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - newCsvMetadata= USERNAME,FIRSTNAME,LASTNAME,CUSTOMPROPERTY5,CITY,STATUSKEY,STARTDATE,CUSTOMPROPERTY8,COMPANYNAME,COSTCENTER,CUSTOMPROPERTY16,DISPLAYNAME,JOBCODE,LOCATION,COUNTRY,LOCATIONNUMBER,MANAGER,CUSTOMPROPERTY1,CUSTOMPROPERTY14,TITLE,CUSTOMPROPERTY6,PREFEREDFIRSTNAME,CUSTOMPROPERTY4,STATE,CUSTOMPROPERTY7,CUSTOMPROPERTY12,CUSTOMPROPERTY11,CUSTOMPROPERTY24,CUSTOMPROPERTY25,JOB_FUNCTION,CUSTOMPROPERTY26,CUSTOMPROPERTY27,EMPLOYEECLASS,EMPLOYEEID,MIDDLENAME,CUSTOMPROPERTY13,CUSTOMPROPERTY21,PHONENUMBER,CUSTOMPROPERTY3,CUSTOMPROPERTY2,TERMDATE,CUSTOMPROPERTY54,CUSTOMPROPERTY55,CUSTOMPROPERTY57,CUSTOMPROPERTY59,CUSTOMPROPERTY65,USERKEY
2024-05-07/14:18:36.851 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.SaviyntCommonUtilityService - dataAnalyzerURL:false
2024-05-07/14:18:36.851 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.SaviyntCommonUtilityService - URL::jdbc:mysql://kontoordevmysql.mysql.database.azure.com:3306/ssminlp?serverTimezone=UTC&useUnicode=yes&characterEncoding=UTF-8
2024-05-07/14:18:36.928 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Start validateCustomFunctionsParams
2024-05-07/14:18:36.930 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - usingCustomFunction= false
2024-05-07/14:18:36.930 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - End validateCustomFunctionsParams
2024-05-07/14:18:36.930 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Creating additional tables
2024-05-07/14:18:36.930 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Start createTempTables
2024-05-07/14:18:36.930 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Creating temp table TEMPUSERS_1840069 from existing table USERS
2024-05-07/14:18:36.930 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Executing Qry: 
                    CREATE TABLE ssminlp.TEMPUSERS_1840069
                    SELECT username, customproperty55, customproperty57, customproperty65, customproperty59, location, locationnumber, userkey, PREFEREDFIRSTNAME, CUSTOMPROPERTY4 from USERS
                
2024-05-07/14:18:39.285 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Time taken to create temp table TEMPUSERS_1840069 = 2355 ms
2024-05-07/14:18:39.285 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - End createTempTables
2024-05-07/14:18:39.289 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Db field name and field type map: [HCP5:tinytext, CUSTOMPROPERTY41:tinytext, CUSTOMPROPERTY40:varchar(255), CUSTOMPROPERTY43:tinytext, CUSTOMPROPERTY42:tinytext, CUSTOMPROPERTY45:tinytext, ACCOUNTLOCKED:varchar(25), CUSTOMPROPERTY44:tinytext, CUSTOMPROPERTY47:tinytext, SAVUPDATEDATE:varchar(25), CUSTOMPROPERTY46:tinytext, OWNERONTERMINATE:varchar(255), CUSTOMPROPERTY49:tinytext, FIRSTNAME:varchar(255), CUSTOMPROPERTY48:tinytext, JOB_FUNCTION:varchar(255), OWNER:varchar(255), JOBCODE:varchar(255), DEPARTMENTNUMBER:varchar(255), CUSTOMER:varchar(255), MANAGER:varchar(255), USERNAME:varchar(255), CUSTOMPROPERTY50:tinytext, ECP2:longtext, ECP3:longtext, TERMDATE:varchar(25), CUSTOMPROPERTY52:tinytext, ECP4:longtext, CUSTOMPROPERTY51:tinytext, ECP5:longtext, CUSTOMPROPERTY54:tinytext, CUSTOMPROPERTY53:tinytext, CUSTOMPROPERTY56:tinytext, CREATEDATE:varchar(25), CUSTOMPROPERTY55:tinytext, CUSTOMPROPERTY4:varchar(255), CUSTOMPROPERTY58:tinytext, CUSTOMPROPERTY5:varchar(255), CUSTOMPROPERTY57:tinytext, CUSTOMPROPERTY2:varchar(255), CUSTOMPROPERTY3:varchar(255), CUSTOMPROPERTY59:tinytext, CUSTOMPROPERTY1:varchar(255), REGION:varchar(255), DONOTDISTURBDELEGATE:varchar(255), CUSTOMPROPERTY8:varchar(255), CUSTOMPROPERTY9:varchar(255), FAILEDTRIES:bigint(20), CUSTOMPROPERTY6:varchar(255), CUSTOMPROPERTY7:varchar(255), SECONDARYEMAIL:varchar(255), LASTPASSWORDUPDATEDATE:varchar(25), HCP2:tinytext, LOCATIONNUMBER:varchar(255), HCP1:tinytext, HCP4:tinytext, HCP3:tinytext, LOCATION:varchar(255), UPDATEUSER:bigint(20), JOB_ID:bigint(20), CUSTOMPROPERTY21:varchar(255), CUSTOMPROPERTY20:varchar(255), CUSTOMPROPERTY23:varchar(255), CUSTOMPROPERTY22:varchar(255), CUSTOMPROPERTY25:varchar(255), COMMENTS:varchar(255), CUSTOMPROPERTY24:varchar(255), CUSTOMPROPERTY27:varchar(255), PREFEREDFIRSTNAME:varchar(255), COSTCENTER:varchar(255), CUSTOMPROPERTY26:varchar(255), CUSTOMPROPERTY29:varchar(255), SYSTEMUSERNAME:varchar(255), CUSTOMPROPERTY28:varchar(255), SECONDARYPHONE:varchar(255), LASTSYNCDATE:varchar(25), LASTCERTIFIEDDATE:varchar(25), EMPLOYEECLASS:varchar(255), CUSTOMPROPERTY30:varchar(255), CUSTOMPROPERTY32:varchar(255), CUSTOMPROPERTY31:varchar(255), CUSTOMPROPERTY34:varchar(255), CUSTOMPROPERTY33:varchar(255), LOCALAUTHENABLED:varchar(25), CUSTOMPROPERTY36:varchar(255), CUSTOMPROPERTY35:varchar(255), CUSTOMPROPERTY38:varchar(255), LOCATIONDESC:varchar(255), CUSTOMPROPERTY37:varchar(255), STREET:varchar(255), SITEID:varchar(255), CUSTOMPROPERTY39:varchar(255), LEAVESTATUS:varchar(255), RISKSCORE:bigint(20), LOCALE:tinytext, CITY:varchar(255), LASTCERTIFIEDUSER:bigint(20), COMPANYNAME:varchar(255), SECURITYANSWERS:varchar(255), USERSOURCE:varchar(255), EMAIL:varchar(255), SECONDARYMANAGER:varchar(255), USERKEY:bigint(20), MIDDLENAME:varchar(255), COUNTRY:varchar(255), LASTNAME:varchar(255), DISPLAYNAME:varchar(255), CUSTOMPROPERTY10:varchar(255), EMPLOYEEID:varchar(255), CUSTOMPROPERTY12:varchar(255), CUSTOMPROPERTY11:varchar(255), CUSTOMPROPERTY14:varchar(255), CUSTOMPROPERTY13:varchar(255), CUSTOMPROPERTY16:varchar(255), CUSTOMPROPERTY15:varchar(255), CUSTOMPROPERTY18:varchar(255), VENDORMANAGER:varchar(255), CUSTOMPROPERTY17:varchar(255), DEPARTMENTNAME:varchar(255), REGIONCODE:varchar(255), CUSTOMPROPERTY19:varchar(255), LASTCERTIFIEDCAMPAIGNNAME:longtext, PASSWORD:varchar(255), UPDATEDATE:varchar(25), STATUSKEY:varchar(25), CUSTOMPROPERTY61:tinytext, JOBCODEDESC:varchar(255), CUSTOMPROPERTY60:tinytext, EMPLOYEETYPE:varchar(255), CUSTOMPROPERTY63:tinytext, CUSTOMPROPERTY62:tinytext, CUSTOMPROPERTY65:tinytext, PHONENUMBER:varchar(255), STARTDATE:varchar(25), CUSTOMPROPERTY64:tinytext, TASKCREATEDFORTERMINATE:bigint(20), STATE:varchar(255), PIIERASURESTATUS:bigint(20), HANARULEKEY:longtext, ENTITY:varchar(255), ACCOUNTEXPIRED:varchar(25), RULEACTION:varchar(255), CREATEDBY:varchar(255), ECP1:longtext, PASSWORDEXPIRED:varchar(25), ENDDATE:varchar(25), ORGUNITID:varchar(255), USERSOURCEKEY:bigint(20), ENABLED:varchar(25), TITLE:varchar(255), JOBDESCRIPTION:varchar(255)]
2024-05-07/14:18:39.289 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - getting users persistentProperties
2024-05-07/14:18:39.289 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - finished getting users persistentProperties.. usersDomainProps size = 148
2024-05-07/14:18:39.290 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - columnTypeMap importSavDataUserService .. [HCP5:TINYTEXT, CUSTOMPROPERTY41:TINYTEXT, CUSTOMPROPERTY40:VARCHAR(255), CUSTOMPROPERTY43:TINYTEXT, CUSTOMPROPERTY42:TINYTEXT, CUSTOMPROPERTY45:TINYTEXT, ACCOUNTLOCKED:VARCHAR(25), CUSTOMPROPERTY44:TINYTEXT, CUSTOMPROPERTY47:TINYTEXT, SAVUPDATEDATE:VARCHAR(25), CUSTOMPROPERTY46:TINYTEXT, OWNERONTERMINATE:VARCHAR(255), CUSTOMPROPERTY49:TINYTEXT, FIRSTNAME:VARCHAR(255), CUSTOMPROPERTY48:TINYTEXT, JOB_FUNCTION:VARCHAR(255), OWNER:VARCHAR(255), JOBCODE:VARCHAR(255), DEPARTMENTNUMBER:VARCHAR(255), CUSTOMER:VARCHAR(255), MANAGER:VARCHAR(255), USERNAME:VARCHAR(255), CUSTOMPROPERTY50:TINYTEXT, ECP2:LONGTEXT, TERMDATE:VARCHAR(25), ECP3:LONGTEXT, CUSTOMPROPERTY52:TINYTEXT, ECP4:LONGTEXT, CUSTOMPROPERTY51:TINYTEXT, ECP5:LONGTEXT, CUSTOMPROPERTY54:TINYTEXT, CUSTOMPROPERTY53:TINYTEXT, CUSTOMPROPERTY56:TINYTEXT, CREATEDATE:VARCHAR(25), CUSTOMPROPERTY55:TINYTEXT, CUSTOMPROPERTY4:VARCHAR(255), CUSTOMPROPERTY58:TINYTEXT, CUSTOMPROPERTY5:VARCHAR(255), CUSTOMPROPERTY57:TINYTEXT, ENCRYPTEDSECURITYANSWERS:VARCHAR(255), CUSTOMPROPERTY2:VARCHAR(255), CUSTOMPROPERTY3:VARCHAR(255), CUSTOMPROPERTY59:TINYTEXT, CUSTOMPROPERTY1:VARCHAR(255), REGION:VARCHAR(255), DONOTDISTURBDELEGATE:VARCHAR(255), CUSTOMPROPERTY8:VARCHAR(255), CUSTOMPROPERTY9:VARCHAR(255), FAILEDTRIES:BIGINT(20), CUSTOMPROPERTY6:VARCHAR(255), CUSTOMPROPERTY7:VARCHAR(255), SECONDARYEMAIL:VARCHAR(255), LOCATIONNUMBER:VARCHAR(255), HCP2:TINYTEXT, HCP1:TINYTEXT, HCP4:TINYTEXT, HCP3:TINYTEXT, LOCATION:VARCHAR(255), UPDATEUSER:BIGINT(20), CUSTOMPROPERTY21:VARCHAR(255), CUSTOMPROPERTY20:VARCHAR(255), CUSTOMPROPERTY23:VARCHAR(255), CUSTOMPROPERTY22:VARCHAR(255), CUSTOMPROPERTY25:VARCHAR(255), COMMENTS:VARCHAR(255), CUSTOMPROPERTY24:VARCHAR(255), CUSTOMPROPERTY27:VARCHAR(255), PREFEREDFIRSTNAME:VARCHAR(255), COSTCENTER:VARCHAR(255), CUSTOMPROPERTY26:VARCHAR(255), CUSTOMPROPERTY29:VARCHAR(255), SYSTEMUSERNAME:VARCHAR(255), CUSTOMPROPERTY28:VARCHAR(255), JOBID:VARCHAR(255), SECONDARYPHONE:VARCHAR(255), LASTSYNCDATE:VARCHAR(25), EMPLOYEECLASS:VARCHAR(255), CUSTOMPROPERTY30:VARCHAR(255), AUTHORITIES:VARCHAR(255), CUSTOMPROPERTY32:VARCHAR(255), CUSTOMPROPERTY31:VARCHAR(255), CUSTOMPROPERTY34:VARCHAR(255), CUSTOMPROPERTY33:VARCHAR(255), LOCALAUTHENABLED:VARCHAR(25), CUSTOMPROPERTY36:VARCHAR(255), CUSTOMPROPERTY35:VARCHAR(255), CUSTOMPROPERTY38:VARCHAR(255), LOCATIONDESC:VARCHAR(255), CUSTOMPROPERTY37:VARCHAR(255), STREET:VARCHAR(255), SITEID:VARCHAR(255), CUSTOMPROPERTY39:VARCHAR(255), LEAVESTATUS:VARCHAR(255), RISKSCORE:BIGINT(20), LOCALE:TINYTEXT, CITY:VARCHAR(255), COMPANYNAME:VARCHAR(255), SECURITYANSWERS:VARCHAR(255), USERSOURCE:VARCHAR(255), EMAIL:VARCHAR(255), SECONDARYMANAGER:VARCHAR(255), USERKEY:BIGINT(20), MIDDLENAME:VARCHAR(255), COUNTRY:VARCHAR(255), LASTNAME:VARCHAR(255), DISPLAYNAME:VARCHAR(255), CUSTOMPROPERTY10:VARCHAR(255), EMPLOYEEID:VARCHAR(255), CUSTOMPROPERTY12:VARCHAR(255), CUSTOMPROPERTY11:VARCHAR(255), CUSTOMPROPERTY14:VARCHAR(255), CUSTOMPROPERTY13:VARCHAR(255), CUSTOMPROPERTY16:VARCHAR(255), CUSTOMPROPERTY15:VARCHAR(255), CUSTOMPROPERTY18:VARCHAR(255), VENDORMANAGER:VARCHAR(255), CUSTOMPROPERTY17:VARCHAR(255), DEPARTMENTNAME:VARCHAR(255), REGIONCODE:VARCHAR(255), CUSTOMPROPERTY19:VARCHAR(255), PASSWORD:VARCHAR(255), UPDATEDATE:VARCHAR(25), STATUSKEY:VARCHAR(25), CUSTOMPROPERTY61:TINYTEXT, JOBCODEDESC:VARCHAR(255), CUSTOMPROPERTY60:TINYTEXT, EMPLOYEETYPE:VARCHAR(255), CUSTOMPROPERTY63:TINYTEXT, CUSTOMPROPERTY62:TINYTEXT, CUSTOMPROPERTY65:TINYTEXT, PHONENUMBER:VARCHAR(255), STARTDATE:VARCHAR(25), CUSTOMPROPERTY64:TINYTEXT, TASKCREATEDFORTERMINATE:BIGINT(20), STATE:VARCHAR(255), HANARULEKEY:LONGTEXT, ENTITY:VARCHAR(255), ACCOUNTEXPIRED:VARCHAR(25), RULEACTION:VARCHAR(255), CREATEDBY:VARCHAR(255), PASSWORDEXPIRED:VARCHAR(25), ECP1:LONGTEXT, ENDDATE:VARCHAR(25), ORGUNITID:VARCHAR(255), USERSOURCEKEY:BIGINT(20), ENABLED:VARCHAR(25), TITLE:VARCHAR(255), JOBDESCRIPTION:VARCHAR(255)]
2024-05-07/14:18:39.290 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - createTempUsersQry= 
                    CREATE TABLE ssminlp.TEMPNEWUSERS_1840069 (
                        USERNAME VARCHAR(255)  DEFAULT NULL,
FIRSTNAME VARCHAR(255)  DEFAULT NULL,
LASTNAME VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY5 VARCHAR(255)  DEFAULT NULL,
CITY VARCHAR(255)  DEFAULT NULL,
STATUSKEY VARCHAR(25)  DEFAULT NULL,
STARTDATE VARCHAR(25)  DEFAULT NULL,
CUSTOMPROPERTY8 VARCHAR(255)  DEFAULT NULL,
COMPANYNAME VARCHAR(255)  DEFAULT NULL,
COSTCENTER VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY16 VARCHAR(255)  DEFAULT NULL,
DISPLAYNAME VARCHAR(255)  DEFAULT NULL,
JOBCODE VARCHAR(255)  DEFAULT NULL,
LOCATION VARCHAR(255)  DEFAULT NULL,
COUNTRY VARCHAR(255)  DEFAULT NULL,
LOCATIONNUMBER VARCHAR(255)  DEFAULT NULL,
MANAGER VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY1 VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY14 VARCHAR(255)  DEFAULT NULL,
TITLE VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY6 VARCHAR(255)  DEFAULT NULL,
PREFEREDFIRSTNAME VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY4 VARCHAR(255)  DEFAULT NULL,
STATE VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY7 VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY12 VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY11 VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY24 VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY25 VARCHAR(255)  DEFAULT NULL,
JOB_FUNCTION VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY26 VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY27 VARCHAR(255)  DEFAULT NULL,
EMPLOYEECLASS VARCHAR(255)  DEFAULT NULL,
EMPLOYEEID VARCHAR(255)  DEFAULT NULL,
MIDDLENAME VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY13 VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY21 VARCHAR(255)  DEFAULT NULL,
PHONENUMBER VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY3 VARCHAR(255)  DEFAULT NULL,
CUSTOMPROPERTY2 VARCHAR(255)  DEFAULT NULL,
TERMDATE VARCHAR(25)  DEFAULT NULL,
CUSTOMPROPERTY54 TINYTEXT  DEFAULT NULL,
CUSTOMPROPERTY55 TINYTEXT  DEFAULT NULL,
CUSTOMPROPERTY57 TINYTEXT  DEFAULT NULL,
CUSTOMPROPERTY59 TINYTEXT  DEFAULT NULL,
CUSTOMPROPERTY65 TINYTEXT  DEFAULT NULL,
USERKEY BIGINT(20)  DEFAULT NULL
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
                
2024-05-07/14:18:39.416 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Table TEMPNEWUSERS_1840069 created
2024-05-07/14:18:39.416 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Storing data in table TEMPNEWUSERS_1840069
2024-05-07/14:18:39.417 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - insertqry= INSERT INTO ssminlp.TEMPNEWUSERS_1840069 (USERNAME,FIRSTNAME,LASTNAME,CUSTOMPROPERTY5,CITY,STATUSKEY,STARTDATE,CUSTOMPROPERTY8,COMPANYNAME,COSTCENTER,CUSTOMPROPERTY16,DISPLAYNAME,JOBCODE,LOCATION,COUNTRY,LOCATIONNUMBER,MANAGER,CUSTOMPROPERTY1,CUSTOMPROPERTY14,TITLE,CUSTOMPROPERTY6,PREFEREDFIRSTNAME,CUSTOMPROPERTY4,STATE,CUSTOMPROPERTY7,CUSTOMPROPERTY12,CUSTOMPROPERTY11,CUSTOMPROPERTY24,CUSTOMPROPERTY25,JOB_FUNCTION,CUSTOMPROPERTY26,CUSTOMPROPERTY27,EMPLOYEECLASS,EMPLOYEEID,MIDDLENAME,CUSTOMPROPERTY13,CUSTOMPROPERTY21,PHONENUMBER,CUSTOMPROPERTY3,CUSTOMPROPERTY2,TERMDATE,CUSTOMPROPERTY54) VALUES ('503949_test2','Arturo','Rodriguez','Highway 601 South','Mocksville','1','4/4/2024 0:00','Jeanswear NA','Kontoor US, LLC','Mocksville Sp Handl','158055010','Arturo Rodriguez','JC-101322','USCA &gt; USA &gt; North Carolina &gt; Mocksville Hwy 601 S - JWR','United States of America','Distribution Center','10112','10/7/2013','P-20231206-8660','Piece Rate','27028-9326','Arturo','Rodriguez','NC','SO-11466 Mocksville Distribution (Norma Paulin (725342))','US/Eastern','Regular','Piece Rate','','','United States of America','','Employee','503949','','10 Individual Contributor','KB MOK Mocksville Weekly','','','','8/11/2014 0:00','Tom Waldron (100463); Jorge Oliver (873438); Ezio Garciamendez (868181); Scott Baxter (433803); Sergio Murillo (100627); Norma Paulin (725342)')
2024-05-07/14:18:39.436 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Start adding indexes for temp tables
2024-05-07/14:18:39.436 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - End adding indexes for temp tables
2024-05-07/14:18:39.436 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Executing the pre-processing queries
2024-05-07/14:18:39.436 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Processing query: Delete from TEMPNEWUSERS_1840069 where username not in ('503949_test2', '874666',  '875021', '875022')
2024-05-07/14:18:39.437 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Records updated: 0
2024-05-07/14:18:39.438 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Processing query: UPDATE TEMPNEWUSERS_1840069 LEFT JOIN TEMPUSERS_1840069 ON TEMPNEWUSERS_1840069.USERNAME = TEMPUSERS_1840069.USERNAME set TEMPNEWUSERS_1840069.customproperty57 = case when (TEMPUSERS_1840069.customproperty57 is null or TEMPUSERS_1840069.customproperty57 = '') then TEMPUSERS_1840069.userkey + 20000 else TEMPUSERS_1840069.customproperty57 end where TEMPNEWUSERS_1840069.username in ('503949_test2', '874666',  '875021', '875022')
2024-05-07/14:18:39.476 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Records updated: 1
2024-05-07/14:18:39.477 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Processing query: Update TEMPNEWUSERS_1840069 NU join (select USRFILTER.USRNAME AS ausername, USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN, NU2.PREFEREDFIRSTNAME, NU2.CUSTOMPROPERTY4, USRFILTER.RECORD_HR_STATUS, CASE WHEN USRFILTER.RECORD_HR_STATUS = 'VALID' THEN 'PROCESSED' ELSE 'Reported to HR' END AS processed, CASE WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER NOT IN ('Retail Store','Outlet','Shop IN Shop (Concession)') and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=NonRetail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and (LOCATION like 'USCA%')) THEN 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and (LOCATION like 'MXLA%')) THEN 'OU=Users,OU=LATM,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and LOCATION like 'EMEA%') THEN 'OU=Users,OU=EMEA,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' WHEN (LOCATION IS NOT null and LOCATIONNUMBER IS null and LOCATION like 'ASIA%') THEN 'OU=Users,OU=APAC,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' ELSE 'OU=Users,OU=AMER,OU=Retail,OU=ktb,DC=ktb,DC=kontoorbrands,DC=com' END AS userdn, ( CASE WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) >= 8 AND USRFILTER.PLN IS NULL) THEN LEFT(USRFILTER.PFN, 8) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NULL) THEN USRFILTER.PFN WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN = USRFILTER.PMN) THEN USRFILTER.PFN WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN))) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 8 AND USRFILTER.PLN IS NULL AND USRFILTER.PMN IS NOT NULL AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) > 3) THEN CONCAT(LEFT(USRFILTER.PFN, 4), LEFT(USRFILTER.PLN, 4)) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) >= 8) THEN CONCAT(LEFT(USRFILTER.PFN, (8-LENGTH(USRFILTER.PLN))), USRFILTER.PLN ) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN = USRFILTER.PMN) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN)-LENGTH(USRFILTER.PLN)), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) > 3 AND LENGTH(USRFILTER.PLN) < 4 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PLN, 8-(LENGTH(USRFILTER.PFN)))) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8) AND USRFILTER.PFN = USRFILTER.PMN THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-(LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN))), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) > 3 AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN = USRFILTER.PMN) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) >= 8) THEN CONCAT(USRFILTER.PFN, LEFT(USRFILTER.PMN, 8-LENGTH(USRFILTER.PFN)-LENGTH(USRFILTER.PLN)), USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND LENGTH(USRFILTER.PFN) < 4 AND LENGTH(USRFILTER.PLN) < 4 AND USRFILTER.PFN != USRFILTER.PMN AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)+LENGTH(USRFILTER.PMN)) < 8) THEN CONCAT(USRFILTER.PFN, USRFILTER.PMN, USRFILTER.PLN) WHEN ((NU2.customproperty59 IS NULL OR LENGTH(NU2.customproperty59) < 1) AND USRFILTER.RECORD_HR_STATUS='VALID' AND (LENGTH(USRFILTER.PFN)+LENGTH(USRFILTER.PLN)) < 8 AND USRFILTER.PMN IS NULL) THEN CONCAT(USRFILTER.PFN, USRFILTER.PLN) ELSE NU2.customproperty59 END ) AS samaccountname from TEMPNEWUSERS_1840069 NU2, ( SELECT INTUSERS.UCASE AS UCASE, INTUSERS.RECORD_HR_STATUS AS RECORD_HR_STATUS, NU1.USERNAME AS USRNAME, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PFN),'.',''),'-','')) AS PFN, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PMN),'.',''),'-','')) AS PMN, UPPER(REPLACE(REPLACE(FN_REMOVE_ACCENTS(INTUSERS.PLN),'.',''),'-','')) AS PLN from TEMPNEWUSERS_1840069 NU1, ( SELECT 'CASE 1' AS UCASE, 'INVALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, '' AS PLN, '' AS PMN from TEMPNEWUSERS_1840069 where ((PREFEREDFIRSTNAME IS NOT NULL AND LENGTH(PREFEREDFIRSTNAME) > 0 AND LENGTH(PREFEREDFIRSTNAME)-LENGTH(REPLACE(PREFEREDFIRSTNAME, ' ', '')) > 2) OR (customproperty4 IS NOT NULL AND LENGTH(customproperty4) > 0 AND LENGTH(customproperty4)-LENGTH(REPLACE(customproperty4, ' ', '')) > 2)) UNION SELECT 'CASE 2' AS UCASE, 'INVALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, '' AS PLN, '' AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NULL OR LENGTH(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NULL OR LENGTH(FIRSTNAME) < 1) AND (customproperty4 IS NULL OR LENGTH(customproperty4) < 1) AND (LASTNAME IS NULL OR LENGTH(LASTNAME) < 1) UNION SELECT 'CASE 3' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 4' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 5' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 6' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, right(PREFEREDFIRSTNAME, length(PREFEREDFIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 7' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 8' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 9' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, '' AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS null OR length(LASTNAME) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' UNION SELECT 'CASE 10' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, '' AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS null OR length(LASTNAME) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' UNION SELECT 'CASE 11' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 12' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-3) AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 13' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 14' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(PREFEREDFIRSTNAME, LENGTH(PREFEREDFIRSTNAME)-1) AS PFN, LASTNAME AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 15' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 16' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, PREFEREDFIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS NOT null AND length(PREFEREDFIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND UPPER(left(PREFEREDFIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 17' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 18' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) != 'MD.' UNION SELECT 'CASE 19' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 20' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, customproperty4 AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 21' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 22' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, customproperty4 AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 23' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, RIGHT(customproperty4, LENGTH(customproperty4)-3) AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) > 3 UNION SELECT 'CASE 24' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, customproperty4 AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS NOT null AND length(customproperty4) > 0) AND UPPER(left(customproperty4, 3)) = 'MD.' AND LENGTH(customproperty4) = 3 UNION SELECT 'CASE 25' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 26' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, '' AS PFN, LASTNAME AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS null OR length(FIRSTNAME) < 1) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 27' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 28' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) != 'MD.' UNION SELECT 'CASE 29' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 30' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, FIRSTNAME AS PFN, LASTNAME AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) != 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 UNION SELECT 'CASE 31' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, RIGHT(LASTNAME, LENGTH(LASTNAME)-3) AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) > 3 UNION SELECT 'CASE 32' AS UCASE, 'VALID' AS RECORD_HR_STATUS, USERNAME AS UNAME, RIGHT(FIRSTNAME, LENGTH(FIRSTNAME)-3) AS PFN, LASTNAME AS PLN, middlename AS PMN from TEMPNEWUSERS_1840069 where (PREFEREDFIRSTNAME IS null OR length(PREFEREDFIRSTNAME) < 1) AND (FIRSTNAME IS NOT null AND length(FIRSTNAME) > 0) AND (customproperty4 IS null OR length(customproperty4) < 1) AND (LASTNAME IS NOT null AND length(LASTNAME) > 0) AND UPPER(left(FIRSTNAME, 3)) = 'MD.' AND UPPER(left(LASTNAME, 3)) = 'MD.' AND LENGTH(LASTNAME) = 3 ) AS INTUSERS WHERE NU1.USERNAME = INTUSERS.UNAME ) AS USRFILTER WHERE NU2.USERNAME = USRFILTER.USRNAME AND NU2.statuskey='1' AND NU2.employeeclass IN ('Contingent Worker', 'Employee') AND (NU2.customproperty65 is null OR NU2.customproperty59 is null OR NU2.customproperty55 is null)) AS NU3 on NU.USERNAME = NU3.ausername left join TEMPUSERS_1840069 CU on NU3.ausername=CU.username set NU.customproperty55= case when (CU.customproperty55 is null or CU.customproperty55 = '') then NU3.userdn else CU.customproperty55 end, NU.customproperty65=case when (CU.customproperty65 is null or CU.customproperty65 = '') then NU3.processed else CU.customproperty65 end, NU.customproperty59=case when (CU.customproperty59 is null or CU.customproperty59 = '') then NU3.samaccountname else CU.customproperty59 end where NU.username in ('503949_test2', '874666',  '875021', '875022')
2024-05-07/14:18:39.487 [{}] [https-jsse-nio-443-exec-34]  ERROR services.ImportSAvDataUserService - Error while processing data: 
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: FUNCTION ssminlp.FN_REMOVE_ACCENTS does not exist
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
	at com.mysql.jdbc.Util.getInstance(Util.java:383)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1759)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1679)
	at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure9.doCall(ImportSAvDataUserService.groovy:362)
	at com.saviynt.ecm.services.ImportSAvDataUserService.doImportDataPreprocessing(ImportSAvDataUserService.groovy:351)
	at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:614)
	at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:584)
	at com.saviynt.ecm.identitywarehouse.controllers.UsersController$_closure24.doCall(UsersController.groovy:1579)
	at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
	at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:171)
	at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62)
	at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59)
	at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69)
	at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
2024-05-07/14:18:39.487 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Start dropTempTables
2024-05-07/14:18:39.487 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Dropping table TEMPNEWUSERS_1840069
2024-05-07/14:18:39.487 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Executing Qry: DROP TABLE IF EXISTS ssminlp.TEMPNEWUSERS_1840069
2024-05-07/14:18:39.561 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Time taken to drop temp table TEMPNEWUSERS_1840069 = 74 ms
2024-05-07/14:18:39.561 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Dropping table TEMPUSERS_1840069
2024-05-07/14:18:39.561 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Executing Qry: DROP TABLE IF EXISTS ssminlp.TEMPUSERS_1840069
2024-05-07/14:18:39.630 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Time taken to drop temp table TEMPUSERS_1840069 = 69 ms
2024-05-07/14:18:39.630 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - End dropTempTables
2024-05-07/14:18:39.631 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Total time taken to preprocess data = 2781 ms
2024-05-07/14:18:39.631 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Exit doImportDataPreprocessing
2024-05-07/14:18:39.631 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - CSVMetaData - USERNAME,FIRSTNAME,LASTNAME,CUSTOMPROPERTY5,CITY,STATUSKEY,STARTDATE,CUSTOMPROPERTY8,COMPANYNAME,COSTCENTER,CUSTOMPROPERTY16,DISPLAYNAME,JOBCODE,LOCATION,COUNTRY,LOCATIONNUMBER,MANAGER,CUSTOMPROPERTY1,CUSTOMPROPERTY14,TITLE,CUSTOMPROPERTY6,PREFEREDFIRSTNAME,CUSTOMPROPERTY4,STATE,CUSTOMPROPERTY7,CUSTOMPROPERTY12,CUSTOMPROPERTY11,CUSTOMPROPERTY24,CUSTOMPROPERTY25,JOB_FUNCTION,CUSTOMPROPERTY26,CUSTOMPROPERTY27,EMPLOYEECLASS,EMPLOYEEID,MIDDLENAME,CUSTOMPROPERTY13,CUSTOMPROPERTY21,PHONENUMBER,CUSTOMPROPERTY3,CUSTOMPROPERTY2,TERMDATE,CUSTOMPROPERTY54,CUSTOMPROPERTY55,CUSTOMPROPERTY57,CUSTOMPROPERTY59,CUSTOMPROPERTY65,USERKEY
2024-05-07/14:18:39.636 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - User not in feed action : NOACTION
2024-05-07/14:18:39.639 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - importUserMS: null
2024-05-07/14:18:39.641 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - SAV Role to be added to all new users - ROLE_USERS
2024-05-07/14:18:39.641 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - runzerodayprovforall val = null
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - VENDOR IS IN CSV FEED = false
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - VENDOR INDEX = -1
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Allow vendor in import = []
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - ownerNameField = USERNAME
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Setting CheckRules to true
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - buildUserMap = false
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - userCachedMap size = 0
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Setting Zero Day Limit = 100
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Setting User Termination Limit = 200
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Setting USERSOURCE = FILE-MANUAL
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Allow User Operations= null
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Getting existing users in the system
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - totaljobstorunstr=null
2024-05-07/14:18:39.642 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - total Rule run jobs to invoke = 2
2024-05-07/14:18:39.643 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - User Termination Configuration - 
2024-05-07/14:18:39.643 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - terminateuserviaconfig = false
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Existing users in the system: 7281
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Number of Records in Feed = 1
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Distinct users in feed = 0
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Active Users not in feed = 7281
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Number of Users inserted = 0
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Number of Users updated = 0
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Number of Users inactivated = 0
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Total Number of Users= 0
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  ERROR services.ImportSAvDataUserService - Error in Users Import - Import failed as no Users were inserted or updated
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - terminateuserviaconfig before calling terminateuser - false
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Not calling terminateuser since terminateuserviaconfig=false
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Populating and updating Users Managers
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - FINISHED READING DATA FEED
2024-05-07/14:18:39.648 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.SaviyntCommonUtilityService - Executing Qry - select max(cast(u.username as integer )) from Users u
2024-05-07/14:18:39.657 [{}] [https-jsse-nio-443-exec-34]  ERROR util.JDBCExceptionReporter - '1.1122233344E10' in column '1' is outside valid range for the datatype INTEGER.
2024-05-07/14:18:39.658 [{}] [https-jsse-nio-443-exec-34]  ERROR services.SaviyntCommonUtilityService - could not execute query; SQL [select max(cast(users0_.USERNAME as signed)) as col_0_0_ from USERS users0_]; nested exception is org.hibernate.exception.DataException: could not execute query
2024-05-07/14:18:39.658 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.SaviyntCommonUtilityService - Error finding the max user key from username so trying userkey-Executing Qry - select max(u.id) from Users u
2024-05-07/14:18:39.659 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.SaviyntCommonUtilityService - MAXUSERKEY - 100000
2024-05-07/14:18:39.659 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.WorkflowService - Building User Map..
2024-05-07/14:18:39.906 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Value of endpointsToAssociateOrphanAccounts - 
2024-05-07/14:18:39.906 [{}] [https-jsse-nio-443-exec-34]  DEBUG services.ImportSAvDataUserService - Total time taken to import = 3056 ms
2024-05-07/14:18:39.906 [{}] [Thread-183]  DEBUG services.ImportSAvDataUserService - Start method triggerRuleRunJobs
2024-05-07/14:18:39.907 [{}] [Thread-183]  DEBUG services.ImportSAvDataUserService - Triggering RuleRun job for group : 0, jobid: 1840069 .. new way
2024-05-07/14:18:39.941 [{}] [Thread-183]  DEBUG services.ImportSAvDataUserService - Sleeping for 10 seconds
2024-05-07/14:18:39.972 [{}] [https-jsse-nio-443-exec-34]  ERROR controllers.UsersController - DATA FILE DataAnalyzer_ExportData_2024-05-07_13-21-34 (UTC).csv05-07-2024 MOVED TO FAILED DIR = true. NOT IMPORTED SUCESSFULLY.
2024-05-07/14:18:45.727 [{}] [https-jsse-nio-443-exec-29]  DEBUG log.LoggerService - logFileAndPath: /datadrive/sharedappdrive/saviynt/logs/error-192.168.101.40.log
2024-05-07/14:18:45.728 [{}] [https-jsse-nio-443-exec-29]  DEBUG log.LoggerService - Entered getLogFiles
2024-05-07/14:18:45.757 [{}] [https-jsse-nio-443-exec-29]  DEBUG log.LoggerService - logFiles before sorting :
2024-05-07/14:18:45.757 [{}] [https-jsse-nio-443-exec-29]  DEBUG log.LoggerService - logFiles after sorting :
2024-05-07/14:18:45.757 [{}] [https-jsse-nio-443-exec-29]  DEBUG log.LoggerService - Enter getFileName
2024-05-07/14:18:45.757 [{}] [https-jsse-nio-443-exec-29]  DEBUG log.LoggerService - finalFileName = error.log
2024-05-07/14:18:45.757 [{}] [https-jsse-nio-443-exec-29]  DEBUG log.LoggerService - Exit getFileName
2024-05-07/14:18:45.757 [{}] [https-jsse-nio-443-exec-29]  DEBUG log.LoggerService - Get 100 lines for error.log
2024-05-07/14:18:45.770 [{}] [https-jsse-nio-443-exec-29]  DEBUG log.LoggerService - File not found error.log
2024-05-07/14:18:49.641 [{}] [https-jsse-nio-443-exec-48]  DEBUG controllers.LogsController - Entered downloadMultiLogs
2024-05-07/14:18:49.641 [{}] [https-jsse-nio-443-exec-48]  DEBUG log.LoggerService - logFileAndPath: /datadrive/sharedappdrive/saviynt/logs/error-192.168.101.40.log
2024-05-07/14:18:49.641 [{}] [https-jsse-nio-443-exec-48]  DEBUG log.LoggerService - Entered getLogFiles
2024-05-07/14:18:49.677 [{}] [https-jsse-nio-443-exec-48]  DEBUG log.LoggerService - logFiles before sorting :
2024-05-07/14:18:49.677 [{}] [https-jsse-nio-443-exec-48]  DEBUG log.LoggerService - logFiles after sorting :
2024-05-07/14:18:49.677 [{}] [https-jsse-nio-443-exec-48]  DEBUG log.LoggerService - Enter getFileName
2024-05-07/14:18:49.677 [{}] [https-jsse-nio-443-exec-48]  DEBUG log.LoggerService - finalFileName = debugLog-192.168.101.40.log
2024-05-07/14:18:49.677 [{}] [https-jsse-nio-443-exec-48]  DEBUG log.LoggerService - Exit getFileName
2024-05-07/14:18:49.678 [{}] [https-jsse-nio-443-exec-48]  DEBUG log.LoggerService - logFileAndPath: /datadrive/sharedappdrive/saviynt/logs/error-192.168.101.40.log
2024-05-07/14:18:49.678 [{}] [https-jsse-nio-443-exec-48]  DEBUG log.LoggerService - Files to Zip = [/datadrive/sharedappdrive/saviynt/logs/debugLog-192.168.101.40.log]
2024-05-07/14:18:49.678 [{}] [https-jsse-nio-443-exec-48]  DEBUG export.ExcelExportService - Enter createZipFile
2024-05-07/14:18:49.737 [{}] [https-jsse-nio-443-exec-48]  ERROR export.ExcelExportService - Error creating zip file: /datadrive/sharedappdrive/saviynt/logs/MultiLogs.zip (No such file or directory)
java.io.FileNotFoundException: /datadrive/sharedappdrive/saviynt/logs/MultiLogs.zip (No such file or directory)
	at java.io.FileOutputStream.open(FileOutputStream.java:270)
	at java.io.FileOutputStream.<init>(FileOutputStream.java:213)
	at java.io.FileOutputStream.<init>(FileOutputStream.java:101)
	at com.saviynt.export.ExcelExportService.createZipFile(ExcelExportService.groovy:411)
	at com.saviynt.log.LoggerService.downloadMultiLogFiles(LoggerService.groovy:101)
	at com.saviynt.ecm.identitywarehouse.controllers.LogsController$_closure4.doCall(LogsController.groovy:69)
	at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
	at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:171)
	at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62)
	at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59)
	at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69)
	at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
2024-05-07/14:18:49.738 [{}] [https-jsse-nio-443-exec-48]  DEBUG export.ExcelExportService - Exit createZipFile
2024-05-07/14:18:49.738 [{}] [https-jsse-nio-443-exec-48]  DEBUG export.ExcelExportService - Enter downloadreport
2024-05-07/14:18:49.741 [{}] [https-jsse-nio-443-exec-48]  ERROR export.ExcelExportService - Exception
java.io.FileNotFoundException: /datadrive/sharedappdrive/saviynt/logs/MultiLogs.zip (No such file or directory)
	at java.io.FileInputStream.open(FileInputStream.java:195)
	at java.io.FileInputStream.<init>(FileInputStream.java:138)
	at java.io.FileInputStream.<init>(FileInputStream.java:93)
	at com.saviynt.export.ExcelExportService.downloadreport(ExcelExportService.groovy:1166)
	at com.saviynt.log.LoggerService.downloadMultiLogFiles(LoggerService.groovy:105)
	at com.saviynt.ecm.identitywarehouse.controllers.LogsController$_closure4.doCall(LogsController.groovy:69)
	at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
	at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:171)
	at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62)
	at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59)
	at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69)
	at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
2024-05-07/14:18:49.741 [{}] [https-jsse-nio-443-exec-48]  DEBUG export.ExcelExportService - Exit downloadreport
2024-05-07/14:18:49.741 [{}] [https-jsse-nio-443-exec-48]  DEBUG log.LoggerService - Downloaded zip file Successful!!!!
2024-05-07/14:18:49.941 [{}] [Thread-183]  DEBUG services.ImportSAvDataUserService - Triggering RuleRun job for group : 1, jobid: 1840069 .. new way
2024-05-07/14:18:49.972 [{}] [Thread-183]  DEBUG services.ImportSAvDataUserService - Sleeping for 10 seconds
2024-05-07/14:18:50.069 [{}] [quartzScheduler_Worker-19]  DEBUG jobs.RuleRunJob - RuleRunJob Execute Start
2024-05-07/14:18:50.284 [{}] [quartzScheduler_Worker-20]  DEBUG jobs.RuleRunJob - RuleRunJob Execute Start
2024-05-07/14:18:51.534 [{}] [https-jsse-nio-443-exec-42]  DEBUG log.LoggerService - logFileAndPath: /datadrive/sharedappdrive/saviynt/logs/error-192.168.101.40.log
2024-05-07/14:18:51.534 [{}] [https-jsse-nio-443-exec-42]  DEBUG log.LoggerService - Entered getLogFiles
2024-05-07/14:18:51.570 [{}] [https-jsse-nio-443-exec-42]  DEBUG log.LoggerService - logFiles before sorting :
2024-05-07/14:18:51.570 [{}] [https-jsse-nio-443-exec-42]  DEBUG log.LoggerService - logFiles after sorting :
2024-05-07/14:18:51.570 [{}] [https-jsse-nio-443-exec-42]  DEBUG log.LoggerService - Enter getFileName
2024-05-07/14:18:51.570 [{}] [https-jsse-nio-443-exec-42]  DEBUG log.LoggerService - finalFileName = debugLog-192.168.101.40.log
2024-05-07/14:18:51.570 [{}] [https-jsse-nio-443-exec-42]  DEBUG log.LoggerService - Exit getFileName
2024-05-07/14:18:51.571 [{}] [https-jsse-nio-443-exec-42]  DEBUG log.LoggerService - Get 1000 lines for debugLog-192.168.101.40.log