We're trying to include the Prior and New Relationship to Organization and Work Type (or FTE) on our transfer report that shows work assignment changes. Has anyone else done this and how have you captured this data? It seems fairly elementary to our leadership but these fields are not populated in the Turnover tables so our reporting team is struggling to find the corresponding field and include it on the report.
We are facing this issue. Let us know if you found any solution.
Thanks! I'll let you know if we find anything.
Hi Beth:
Full disclosure: Dashboard Gear is a software/services firm that provides a reporting & analytics platform for Infor customers. Our toolkit includes all of your Infor data and pre-joined tables for Birst, Power BI, Tableau, SSRS and Crystal Reports type reporting, as well as OLAP Cubes. Providing the ability to do GHR Turnover Reporting is a major benefit to our customers using our HCM Toolkit.
Please let me know if you want to learn more about our products. I can be reached at tom.zydel@dashboardgear.com.
Thanks.
Tom Zydel, Dashboard Gear.
I use these two records which hold history to get a before and after value:
S_EMPL = Employee Record History
S_EMPWA = Workassignment Record History
WITH TURNOVER AS ( SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY EMPLOYEE ORDER BY EMPLOYEE, CASE WHEN AUDITEFFECTIVEDATE >= AUDITENTRYSTAMP THEN AUDITEFFECTIVEDATE ELSE AUDITENTRYSTAMP END DESC) AS R, EMPLOYEE, AUDITEFFECTIVEDATE, AUDITENTRYSTAMP, -- added RTRIM(INITIATINGACTION) AS INITIATINGACTION, RTRIM(INVOKINGACTION) AS INVOKINGACTION, -- end of add RTRIM(AUDITACTION) AS AUDITACTION, AUDITACTIONREASON, RTRIM(AUDITBUSINESSSUBJECT) AS AUDITBUSINESSSUBJECT, HRORGANIZATION, OLDHRORGANIZATIONUNIT, NEWHRORGANIZATIONUNIT, OLDPOSITION, NEWPOSITION, OLDJOB, NEWJOB, NEWLOCATION, OLDPAYRATE, NEWPAYRATE, OLDSTEPANDGRADESCHEDULE, NEWSTEPANDGRADESCHEDULE, OLDPAYGRADE, NEWPAYGRADE, OLDPAYSTEP, NEWPAYSTEP, OLDSALARYSTRUCTURE, NEWSALARYSTRUCTURE, OLDSALARYSTRUCTUREGRADE, NEWSALARYSTRUCTUREGRADE, AUDITUSERID FROM .TURNOVERDATA WHERE HRORGANIZATION = '' AND AUDITACTIONREASON NOT IN ('CONTRACTUAL INCREASE','CBAINC','SALMINADJ','TERMPENDINGTOFINAL') --AND AUDITACTION NOT IN ('HireResource','RehireResource','Terminate') AND AUDITACTION NOT IN ('HireResource','RehireResource','Terminate','RehireFromTA') AND INITIATINGACTION NOT IN ('JobApplication.HireApplied') AND ( CONVERT(CHAR(8),AUDITEFFECTIVEDATE,112) >= '' OR CONVERT(CHAR(8),AUDITENTRYSTAMP,112) >= '' ) AND CONVERT(CHAR(8),AUDITEFFECTIVEDATE,112) <= '' ) X ), S_EMPL AS ( SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY EMPLOYEE ORDER BY EMPLOYEE, EMPLOYEE_EFFRNGEND DESC) AS R, EMPLOYEE, EMPLOYEE_EFFRNGBEG, EMPLOYEE_EFFRNGEND, WORKTYPE, RELATIONSHIPSTATUS FROM .S_EMPL WHERE EMPLOYEE_EFFRNGBEG < '') X WHERE R = 1 ), WORKASSIGNMENT_POS AS ( SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY EMPLOYEE ORDER BY EMPLOYEE, USTIMESTAMP DESC) AS R, UNIQUEID, HRORGANIZATION, HRORGANIZATIONUNIT, EMPLOYEE, WORKASSIGNMENT, POSITION, PAYFREQUENCY, FTE FROM .WORKASSIGNMENT ) X WHERE R = 1 ), TAEEMAST AS ( SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY EMPLOYEE ORDER BY EMPLOYEE, CREATE_DATE DESC, CREATE_TIME DESC) AS R, EMPLOYEE, AVAIL_HRS_BAL FROM .TAEEMASTER WHERE COMPANY = '900' AND R_PLAN = 'PTO' AND ( GETDATE() < MSTR_ELIG_END OR YEAR(MSTR_ELIG_END) = 1753 ) ) X WHERE R = 1 ), WACOMMENT AS ( SELECT HRORGANIZATION, EMPLOYEE, WORKASSIGNMENT, REASONCOMMENT FROM .WORKASSIGNMENTEXPORT WAOE LEFT JOIN .S$WAEXP SWAEXP ON SWAEXP.UNIQUEID = WAOE.UNIQUEID WHERE CONVERT(CHAR(8),WAOE.SYSTEMTIMESTAMP,112) >= '' AND CONVERT(CHAR(8),WAOE.SYSTEMTIMESTAMP,112) <= 'CONVERT(CHAR(8),GETDATE(),112)' AND CONVERT(CHAR(8),WAOE.EFFECTIVEDATE,112) <= '' --WHERE CONVERT(CHAR(8),WAOE.SYSTEMTIMESTAMP,112) BETWEEN '' AND '' AND SWAEXP.REASONCOMMENT IS NOT NULL ), BEN AS ( SELECT EMPLOYEE, PLAN_TYPE, PLAN_CODE FROM .BENEFIT BEN WHERE COMPANY = '900' AND PLAN_TYPE IN ('DN','HL') AND PLAN_CODE NOT IN ('WVS','WDEN','WHL') AND ( GETDATE() < STOP_DATE OR YEAR(STOP_DATE) = 1753 OR GETDATE() < DED_STOP_DATE OR YEAR(DED_STOP_DATE) = 1753 ) ), EMP_EXPORT AS ( SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY EMPLOYEE ORDER BY EFFECTIVEDATE DESC, SYSTEMTIMESTAMP DESC) AS R, HRORGANIZATION, EMPLOYEE, SYSTEMTIMESTAMP, EFFECTIVEDATE, -- added RTRIM(INITIATINGACTION) AS INITIATINGACTION, RTRIM(ACTION) AS ACTION, -- end of add ACTIONREASON, SUBJECT, ACTOR, REASONCOMMENT FROM .EMPLOYEEEXPORT EXP LEFT JOIN .S$EEEXP SEEEXP ON SEEEXP.UNIQUEID = EXP.UNIQUEID WHERE HRORGANIZATION = '' AND ( CONVERT(CHAR(8),EFFECTIVEDATE,112) >= '' OR CONVERT(CHAR(8),SYSTEMTIMESTAMP,112) >= '' ) AND CONVERT(CHAR(8),EFFECTIVEDATE,112) <= '' AND RELATIONSHIPSTATUS NOT IN ('PENDING HIRE') AND ACTION NOT IN ('HireResource','RehireResource') AND ACTOR <> 'lawson' --AND AUTHENTICATEDACTOR <> 'lawson' ) X WHERE R = 1 ), NEW_WORKASSIGNMENTEXPORT AS ( SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY EMPLOYEE ORDER BY EFFECTIVEDATE DESC, SYSTEMTIMESTAMP DESC) AS R ,HRORGANIZATION, EMPLOYEE, WORKASSIGNMENT, PAYRATE, SUBJECT, ACTIONREASON, ALPHA AS TEMPLATE ,CASE WHEN PAYFREQUENCY = 1 THEN CONVERT(DECIMAL(10,2),(FTE*1950/52)) WHEN PAYFREQUENCY = 2 THEN CONVERT(DECIMAL(10,2),(FTE*1950/26)) ELSE 0 END AS BGTHRS FROM .WORKASSIGNMENTEXPORT WAEN LEFT JOIN .USERFIELD_STORAGE UFN ON UFN.UFSUNIQUEID = WAEN.UNIQUEID WHERE HRORGANIZATION = '' AND SUBJECT <> 'HCMWorkAssignmentInactivate' --AND ACTIONREASON IN ('BUDGHRS','BUDGHRSSHIFT','POSITIONCHG','PROCESSLVLCHG','SHIFTCHG','CORRECTION') AND ( CONVERT(CHAR(8),EFFECTIVEDATE,112) >= '' OR CONVERT(CHAR(8),SYSTEMTIMESTAMP,112) >= '' ) AND CONVERT(CHAR(8),EFFECTIVEDATE,112) <= '' AND UFN.UFSFIELDNAME = 'CHSPayRule2' ) X WHERE R = 1 ), OLD_WORKASSIGNMENTEXPORT AS ( SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY WAO.EMPLOYEE ORDER BY EFFECTIVEDATE DESC, SYSTEMTIMESTAMP DESC) AS R ,WAO.HRORGANIZATION, WAO.EMPLOYEE, WAO.WORKASSIGNMENT, WAO.PAYRATE, ALPHA AS TEMPLATE ,CASE WHEN PAYFREQUENCY = 1 THEN CONVERT(DECIMAL(10,2),(FTE*1950/52)) WHEN PAYFREQUENCY = 2 THEN CONVERT(DECIMAL(10,2),(FTE*1950/26)) ELSE 0 END AS BGTHRS FROM .WORKASSIGNMENTEXPORT WAO JOIN NEW_WORKASSIGNMENTEXPORT WAEN ON WAO.HRORGANIZATION = WAEN.HRORGANIZATION AND WAO.EMPLOYEE = WAEN.EMPLOYEE AND WAO.WORKASSIGNMENT = WAEN.WORKASSIGNMENT LEFT JOIN .USERFIELD_STORAGE UFO ON UFO.UFSUNIQUEID = WAO.UNIQUEID WHERE WAO.HRORGANIZATION = '' AND CONVERT(CHAR(8),EFFECTIVEDATE,112) < '' AND CONVERT(CHAR(8),SYSTEMTIMESTAMP,112) < '' AND UFO.UFSFIELDNAME = 'CHSPayRule2') X WHERE R = 1 ), ADDRESS AS ( SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY EMPLOYEE ORDER BY EMPLOYEEADDRESS DESC) AS R, HRORGANIZATION, EMPLOYEE, PACOUNTRY, PADAADDRESSLINE1, PADAADDRESSLINE2, PADAADDRESSLINE3, PAMUNICIPALITY, PASTATEPROVINCE, PAPOSTALCODE FROM .EMPLOYEEADDRESS ) X WHERE R = 1 ), TURNOVERDATA AS ( SELECT 'TURNOVERDATA' AS SOURCE ,E.UNIQUEID ,T.HRORGANIZATION ,XPL.DESTINATIONVALUE2 AS CURRPROCLVL ,T.EMPLOYEE ,UPPER(RTRIM(E.NFAMILYNAME)) AS NFAMILYNAME ,UPPER(RTRIM(E.NGIVENNAME)) AS NGIVENNAME ,CASE WHEN YEAR(T.AUDITENTRYSTAMP) = 1753 THEN ' ' ELSE RTRIM(CONVERT(VARCHAR(10),T.AUDITENTRYSTAMP,101)) END AS AUDITENTRYSTAMP ,RTRIM(CONVERT(VARCHAR(10),T.AUDITEFFECTIVEDATE,101)) AS AUDITEFFECTIVEDATE ,RTRIM(T.AUDITUSERID) AS USERID -- added ,COALESCE(T.INITIATINGACTION,' ') AS INITIATINGACTION ,COALESCE(T.INVOKINGACTION,' ') AS INVOKINGACTION -- end of added ,CASE WHEN T.AUDITACTION IS NULL THEN ' ' ELSE RTRIM(T.AUDITACTION) END AS AUDITACTION ,CASE WHEN T.AUDITACTIONREASON IS NULL THEN ' ' ELSE RTRIM(T.AUDITACTIONREASON) END AS AUDITACTIONREASON ,CASE WHEN T.AUDITBUSINESSSUBJECT IS NULL THEN ' ' ELSE RTRIM(T.AUDITBUSINESSSUBJECT) END AS AUDITBUSINESSSUBJECT ,RTRIM(WAN.LOCATION) AS LOCATION -- added ,RTRIM(RELATIONSHIPTOORGANIZATION) AS RELATIONSHIPTOORGANIZATION ,COALESCE(RTRIM(S.RELATIONSHIPSTATUS),' ') AS OLDRELATIONSHIPSTATUS ,RTRIM(E.RELATIONSHIPSTATUS) AS NEWRELATIONSHIPSTATUS -- end of added ,COALESCE(S.WORKTYPE,' ') AS OLDWORKTYPE ,COALESCE(E.WORKTYPE,' ') AS NEWWORKTYPE ,CASE WHEN PO.PAYMENTSCHEDULE IS NULL THEN ' ' WHEN PO.PAYMENTSCHEDULE = PN.PAYMENTSCHEDULE THEN ' ' ELSE PO.PAYMENTSCHEDULE END AS OLDPAYMTHD ,CASE WHEN PN.PAYMENTSCHEDULE IS NULL THEN ' ' WHEN PN.PAYMENTSCHEDULE = PO.PAYMENTSCHEDULE THEN ' ' ELSE PN.PAYMENTSCHEDULE END AS NEWPAYMTHD ,CASE WHEN T.OLDPAYRATE = T.NEWPAYRATE THEN 0 ELSE CAST(T.OLDPAYRATE AS SMALLMONEY) END AS OLDPAYRATE ,CASE WHEN T.NEWPAYRATE = T.OLDPAYRATE THEN 0 ELSE CAST(T.NEWPAYRATE AS SMALLMONEY) END AS NEWPAYRATE ,CASE WHEN T.OLDPOSITION = T.NEWPOSITION THEN 0 ELSE T.OLDPOSITION END AS OLDPOSITION ,CASE WHEN T.OLDJOB = T.NEWJOB THEN 0 ELSE T.OLDJOB END AS OLDJOB ,CASE WHEN JO.SHORTDESCRIPTION = JN.SHORTDESCRIPTION THEN ' ' ELSE JO.SHORTDESCRIPTION END AS OLDJOBTITLE ,CASE WHEN T.NEWPOSITION = T.OLDPOSITION THEN 0 ELSE T.NEWPOSITION END AS NEWPOSITION ,CASE WHEN T.NEWJOB = T.OLDJOB THEN 0 ELSE T.NEWJOB END AS NEWJOB ,CASE WHEN JN.SHORTDESCRIPTION = JO.SHORTDESCRIPTION THEN ' ' ELSE JN.SHORTDESCRIPTION END AS NEWJOBTITLE ,CASE WHEN PO.PAYGRADE = PN.PAYGRADE AND PO.SALARYSTRUCTUREGRADE = PN.SALARYSTRUCTUREGRADE THEN 0 WHEN PO.PAYGRADE = 0 THEN PO.SALARYSTRUCTUREGRADE ELSE PO.PAYGRADE END AS OLDPAYGRADE ,CASE WHEN PN.PAYGRADE = PO.PAYGRADE AND PN.SALARYSTRUCTUREGRADE = PO.SALARYSTRUCTUREGRADE THEN 0 WHEN PN.PAYGRADE = 0 THEN PN.SALARYSTRUCTUREGRADE ELSE PN.PAYGRADE END AS NEWPAYGRADE ,CASE WHEN T.OLDPAYSTEP = T.NEWPAYSTEP THEN 0 ELSE T.OLDPAYSTEP END AS OLDPAYSTEP ,CASE WHEN T.NEWPAYSTEP = T.OLDPAYSTEP THEN 0 ELSE T.NEWPAYSTEP END AS NEWPAYSTEP ,CASE WHEN PO.[UNION] IS NULL THEN ' ' WHEN PO.[UNION] = PN.[UNION] THEN ' ' ELSE PO.[UNION] END AS OLDUNION ,CASE WHEN PN.[UNION] IS NULL THEN ' ' WHEN PN.[UNION] = PO.[UNION] THEN ' ' ELSE PN.[UNION] END AS NEWUNION ,CASE WHEN XO.DESTINATIONVALUE2 IS NULL THEN ' ' WHEN XO.DESTINATIONVALUE2 = XN.DESTINATIONVALUE2 THEN ' ' ELSE XO.DESTINATIONVALUE2 END AS OLDPROCLVL ,CASE WHEN XN.DESTINATIONVALUE2 IS NULL THEN ' ' WHEN XN.DESTINATIONVALUE2 = XO.DESTINATIONVALUE2 THEN ' ' ELSE XN.DESTINATIONVALUE2 END AS NEWPROCLVL ,CASE WHEN PO.BARGAININGUNIT = PN.BARGAININGUNIT THEN ' ' ELSE PO.BARGAININGUNIT END AS OLDBARGAININGUNIT ,CASE WHEN PN.BARGAININGUNIT = PO.BARGAININGUNIT THEN ' ' ELSE PN.BARGAININGUNIT END AS NEWBARGAININGUNIT ,CASE WHEN XO.DESTINATIONVALUE3 IS NULL THEN ' ' WHEN XO.DESTINATIONVALUE3 = XN.DESTINATIONVALUE3 THEN ' ' ELSE XO.DESTINATIONVALUE3 END AS OLDDEPT ,CASE WHEN XN.DESTINATIONVALUE3 IS NULL THEN ' ' WHEN XN.DESTINATIONVALUE3 = XO.DESTINATIONVALUE3 THEN ' ' ELSE XN.DESTINATIONVALUE3 END AS NEWDEPT ,CASE WHEN WAEO.BGTHRS IS NULL THEN 0 ELSE WAEO.BGTHRS END AS OLDBGTHRS ,CASE WHEN WAEN.BGTHRS IS NULL THEN 0 ELSE WAEN.BGTHRS END AS NEWBGTHRS ,CASE WHEN WAEO.TEMPLATE IS NULL THEN ' ' WHEN WAEO.TEMPLATE = WAEN.TEMPLATE THEN ' ' ELSE COALESCE(WAEO.TEMPLATE,' ') END AS OLDTEMPLATE ,CASE WHEN WAEN.TEMPLATE IS NULL THEN ' ' WHEN WAEN.TEMPLATE = WAEO.TEMPLATE THEN ' ' ELSE RTRIM(UPPER(WAEN.TEMPLATE)) END AS NEWTEMPLATE ,COALESCE(WACMNT.REASONCOMMENT,' ') AS PTOPAYOUT ,'' AS PTOPAYOUTE ,CASE WHEN TA.AVAIL_HRS_BAL IS NULL THEN 0 ELSE TA.AVAIL_HRS_BAL END AS AVAILHRSBAL ,'TO DO' AS RETRO ,RTRIM(SUP.SUPERVISOREMPLOYEENAME) AS SUPERVISOREMPLOYEENAME ,'TO DO' AS PARKINGCHG ,CASE WHEN D.DED_CODE = 'PARK' THEN 'Yes' ELSE ' ' END AS MERCYPARKING ,CASE WHEN D.DED_CODE = 'PAR1' THEN 'Yes' ELSE ' ' END AS ARTCPARKING ,CASE WHEN E.WORKTYPE IN ('PERDIEM','PT NO BEN','PT TEMP','TEMP') THEN ' ' WHEN BENDN.EMPLOYEE IS NULL AND BENHL.EMPLOYEE IS NULL AND BENVIS.EMPLOYEE IS NULL THEN ' ' WHEN BENDN.EMPLOYEE IS NOT NULL AND BENHL.EMPLOYEE IS NOT NULL AND BENVIS.EMPLOYEE IS NOT NULL THEN 'Check arrears for HL, DN, and VIS' WHEN BENDN.EMPLOYEE IS NOT NULL AND BENHL.EMPLOYEE IS NOT NULL THEN 'Check arrears for HL and DN' WHEN BENDN.EMPLOYEE IS NOT NULL AND BENVIS.EMPLOYEE IS NOT NULL THEN 'Check arrears for DN and VIS' WHEN BENHL.EMPLOYEE IS NOT NULL AND BENVIS.EMPLOYEE IS NOT NULL THEN 'Check arrears for HL and VIS' WHEN BENHL.EMPLOYEE IS NOT NULL THEN 'Check arrears for HL' WHEN BENDN.EMPLOYEE IS NOT NULL THEN 'Check arrears for DN' ELSE 'Check arrears for VIS' END AS HEALTHDENTALBENEFIT FROM TURNOVER T JOIN .EMPLOYEE E on E.HRORGANIZATION = T.HRORGANIZATION AND E.EMPLOYEE = T.EMPLOYEE JOIN .EMPLOYEE S3 ON S3.EMPLOYEE = T.EMPLOYEE LEFT JOIN NEW_WORKASSIGNMENTEXPORT WAEN ON WAEN.EMPLOYEE = T.EMPLOYEE LEFT JOIN OLD_WORKASSIGNMENTEXPORT WAEO ON WAEO.EMPLOYEE = T.EMPLOYEE LEFT JOIN S_EMPL S on S.EMPLOYEE = T.EMPLOYEE LEFT JOIN .WORKASSIGNMENT WAN ON WAN.HRORGANIZATION = E.HRORGANIZATION AND WAN.DELETEFLAG = 0x00000000000000000000000000000000 AND WAN.EMPLOYEE = E.EMPLOYEE AND WAN.WORKASSIGNMENT = E.PRIMARYWORKASSIGNMENT LEFT JOIN .PFIXREFVALUE XO ON XO.SOURCEVALUE1 = E.HRORGANIZATION AND XO.SOURCEVALUE2 = CAST(T.OLDHRORGANIZATIONUNIT AS VARCHAR) LEFT JOIN .PFIXREFVALUE XN ON XN.SOURCEVALUE1 = E.HRORGANIZATION AND XN.SOURCEVALUE2 = CAST(T.NEWHRORGANIZATIONUNIT AS VARCHAR) LEFT JOIN .PFIXREFVALUE XPL ON XPL.SOURCEVALUE1 = E.HRORGANIZATION AND XPL.SOURCEVALUE2 = CAST(WAN.HRORGANIZATIONUNIT AS VARCHAR) LEFT JOIN .JOB JO on JO.HRORGANIZATION = E.HRORGANIZATION AND JO.JOB = T.OLDJOB LEFT JOIN .JOB JN on JN.HRORGANIZATION = E.HRORGANIZATION AND JN.JOB = T.NEWJOB LEFT JOIN .POSITION PO on PO.HRORGANIZATION = E.HRORGANIZATION AND PO.POSITION = T.OLDPOSITION LEFT JOIN .POSITION PN on PN.HRORGANIZATION = E.HRORGANIZATION AND PN.POSITION = T.NEWPOSITION LEFT JOIN WORKASSIGNMENT_POS WAP ON WAP.HRORGANIZATION = T.HRORGANIZATION -- AND WAP.EMPLOYEE = E.EMPLOYEE AND WAP.POSITION = T.NEWPOSITION AND WAP.EMPLOYEE = E.EMPLOYEE AND WAP.POSITION = WAN.POSITION LEFT JOIN WACOMMENT WACMNT ON WACMNT.HRORGANIZATION = WAP.HRORGANIZATION AND WACMNT.EMPLOYEE = WAP.EMPLOYEE AND WACMNT.WORKASSIGNMENT = WAP.WORKASSIGNMENT LEFT JOIN TAEEMAST TA on TA.EMPLOYEE = T.EMPLOYEE LEFT JOIN .SUPERVISOR SUP ON SUP.HRORGANIZATION = WAN.HRORGANIZATION AND SUP.SUPERVISOR = WAN.DIRECTSUPERVISOR LEFT JOIN .EMDEDMASTR D ON D.COMPANY = '900' AND D.EMPLOYEE = T.EMPLOYEE AND D.DED_CODE LIKE 'PAR%' AND ( GETDATE() BETWEEN D.EFFECT_DATE AND D.END_DATE OR ( GETDATE() >= D.EFFECT_DATE AND YEAR(D.END_DATE) = 1753 ) ) LEFT JOIN BEN BENDN ON BENDN.EMPLOYEE = T.EMPLOYEE AND BENDN.PLAN_TYPE = 'DN' LEFT JOIN BEN BENHL ON BENHL.EMPLOYEE = T.EMPLOYEE AND BENHL.PLAN_TYPE = 'HL' AND BENHL.PLAN_CODE <> 'VIS' LEFT JOIN BEN BENVIS ON BENVIS.EMPLOYEE = T.EMPLOYEE AND BENVIS.PLAN_TYPE = 'HL' AND BENVIS.PLAN_CODE = 'VIS' ), EMPLOYEEEXPORTDATA AS ( SELECT 'EMPLOYEEEXPORT' AS SOURCE ,E.UNIQUEID ,EE.HRORGANIZATION ,XPL.DESTINATIONVALUE2 AS CURRPROCLVL ,EE.EMPLOYEE ,UPPER(RTRIM(E.NFAMILYNAME)) AS NFAMILYNAME ,UPPER(RTRIM(E.NGIVENNAME)) AS NGIVENNAME ,RTRIM(CONVERT(VARCHAR(10),EE.SYSTEMTIMESTAMP,101)) AS AUDITENTRYSTAMP ,RTRIM(CONVERT(VARCHAR(10),EE.EFFECTIVEDATE,101)) AS AUDITEFFECTIVEDATE ,RTRIM(EE.ACTOR) AS USERID -- added ,COALESCE(EE.INITIATINGACTION,' ') AS INITIATINGACTION ,COALESCE(EE.ACTION,' ') AS INVOKINGACTION -- end of add ,CASE WHEN T.AUDITACTION IS NULL THEN ' ' ELSE RTRIM(T.AUDITACTION) END AS AUDITACTION ,CASE WHEN T.AUDITACTIONREASON IS NULL THEN ' ' ELSE RTRIM(T.AUDITACTIONREASON) END AS AUDITACTIONREASON ,CASE WHEN T.AUDITBUSINESSSUBJECT IS NULL THEN ' ' ELSE RTRIM(T.AUDITBUSINESSSUBJECT) END AS AUDITBUSINESSSUBJECT ,RTRIM(WAN.LOCATION) AS LOCATION -- added ,RTRIM(RELATIONSHIPTOORGANIZATION) AS RELATIONSHIPTOORGANIZATION ,COALESCE(RTRIM(S.RELATIONSHIPSTATUS),' ') AS OLDRELATIONSHIPSTATUS ,RTRIM(E.RELATIONSHIPSTATUS) AS NEWRELATIONSHIPSTATUS -- end of add -- changed ,COALESCE(RTRIM(S.WORKTYPE),'') AS OLDWORKTYPE ,COALESCE(RTRIM(E.WORKTYPE),'') AS NEWWORKTYPE -- end of change ,CASE WHEN PO.PAYMENTSCHEDULE IS NULL THEN ' ' WHEN PO.PAYMENTSCHEDULE = PN.PAYMENTSCHEDULE THEN ' ' ELSE PO.PAYMENTSCHEDULE END AS OLDPAYMTHD ,CASE WHEN PN.PAYMENTSCHEDULE IS NULL THEN ' ' WHEN PN.PAYMENTSCHEDULE = PO.PAYMENTSCHEDULE THEN ' ' ELSE PN.PAYMENTSCHEDULE END AS NEWPAYMTHD ,CASE WHEN WAEO.PAYRATE = WAEN.PAYRATE THEN 0 ELSE CAST(WAEO.PAYRATE AS SMALLMONEY) END AS OLDPAYRATE ,CASE WHEN WAEN.PAYRATE = WAEO.PAYRATE THEN 0 ELSE CAST(WAEN.PAYRATE AS SMALLMONEY) END AS NEWPAYRATE ,CASE WHEN T.OLDPOSITION = T.NEWPOSITION THEN 0 ELSE T.OLDPOSITION END AS OLDPOSITION ,CASE WHEN T.OLDJOB = T.NEWJOB THEN 0 ELSE T.OLDJOB END AS OLDJOB ,CASE WHEN JO.SHORTDESCRIPTION = JN.SHORTDESCRIPTION THEN ' ' ELSE JO.SHORTDESCRIPTION END AS OLDJOBTITLE ,CASE WHEN T.NEWPOSITION = T.OLDPOSITION THEN 0 ELSE T.NEWPOSITION END AS NEWPOSITION ,CASE WHEN T.NEWJOB = T.OLDJOB THEN 0 ELSE T.NEWJOB END AS NEWJOB ,CASE WHEN JN.SHORTDESCRIPTION = JO.SHORTDESCRIPTION THEN ' ' ELSE JN.SHORTDESCRIPTION END AS NEWJOBTITLE ,CASE WHEN PO.PAYGRADE = PN.PAYGRADE AND PO.SALARYSTRUCTUREGRADE = PN.SALARYSTRUCTUREGRADE THEN 0 WHEN PO.PAYGRADE = 0 THEN PO.SALARYSTRUCTUREGRADE ELSE PO.PAYGRADE END AS OLDPAYGRADE ,CASE WHEN PN.PAYGRADE = PO.PAYGRADE AND PN.SALARYSTRUCTUREGRADE = PO.SALARYSTRUCTUREGRADE THEN 0 WHEN PN.PAYGRADE = 0 THEN PN.SALARYSTRUCTUREGRADE ELSE PN.PAYGRADE END AS NEWPAYGRADE ,CASE WHEN T.OLDPAYSTEP = T.NEWPAYSTEP THEN 0 ELSE T.OLDPAYSTEP END AS OLDPAYSTEP ,CASE WHEN T.NEWPAYSTEP = T.OLDPAYSTEP THEN 0 ELSE T.NEWPAYSTEP END AS NEWPAYSTEP ,CASE WHEN PO.[UNION] IS NULL THEN ' ' WHEN PO.[UNION] = PN.[UNION] THEN ' ' ELSE PO.[UNION] END AS OLDUNION ,CASE WHEN PN.[UNION] IS NULL THEN ' ' WHEN PN.[UNION] = PO.[UNION] THEN ' ' ELSE PN.[UNION] END AS NEWUNION ,CASE WHEN XO.DESTINATIONVALUE2 IS NULL THEN ' ' WHEN XO.DESTINATIONVALUE2 = XN.DESTINATIONVALUE2 THEN ' ' ELSE XO.DESTINATIONVALUE2 END AS OLDPROCLVL ,CASE WHEN XN.DESTINATIONVALUE2 IS NULL THEN ' ' WHEN XN.DESTINATIONVALUE2 = XO.DESTINATIONVALUE2 THEN ' ' ELSE XN.DESTINATIONVALUE2 END AS NEWPROCLVL ,CASE WHEN PO.BARGAININGUNIT = PN.BARGAININGUNIT THEN ' ' ELSE PO.BARGAININGUNIT END AS OLDBARGAININGUNIT ,CASE WHEN PN.BARGAININGUNIT = PO.BARGAININGUNIT THEN ' ' ELSE PN.BARGAININGUNIT END AS NEWBARGAININGUNIT ,CASE WHEN XO.DESTINATIONVALUE3 IS NULL THEN ' ' WHEN XO.DESTINATIONVALUE3 = XN.DESTINATIONVALUE3 THEN ' ' ELSE XO.DESTINATIONVALUE3 END AS OLDDEPT ,CASE WHEN XN.DESTINATIONVALUE3 IS NULL THEN ' ' WHEN XN.DESTINATIONVALUE3 = XO.DESTINATIONVALUE3 THEN ' ' ELSE XN.DESTINATIONVALUE3 END AS NEWDEPT ,CASE WHEN WAEO.BGTHRS IS NULL THEN 0 ELSE WAEO.BGTHRS END AS OLDBGTHRS ,CASE WHEN WAEN.BGTHRS IS NULL THEN 0 ELSE WAEN.BGTHRS END AS NEWBGTHRS ,CASE WHEN WAEO.TEMPLATE IS NULL THEN ' ' WHEN WAEO.TEMPLATE = WAEN.TEMPLATE THEN ' ' ELSE COALESCE(WAEO.TEMPLATE,' ') END AS OLDTEMPLATE ,CASE WHEN WAEN.TEMPLATE IS NULL THEN ' ' WHEN WAEN.TEMPLATE = WAEO.TEMPLATE THEN ' ' ELSE RTRIM(UPPER(WAEN.TEMPLATE)) END AS NEWTEMPLATE ,COALESCE(WACMNT.REASONCOMMENT,' ') AS PTOPAYOUT ,COALESCE(EE.REASONCOMMENT,' ') AS PTOPAYOUTE ,CASE WHEN TA.AVAIL_HRS_BAL IS NULL THEN 0 ELSE TA.AVAIL_HRS_BAL END AS AVAILHRSBAL ,'TO DO' AS RETRO ,RTRIM(SUP.SUPERVISOREMPLOYEENAME) AS SUPERVISOREMPLOYEENAME ,'TO DO' AS PARKINGCHG ,CASE WHEN D.DED_CODE = 'PARK' THEN 'Yes' ELSE ' ' END AS MERCYPARKING ,CASE WHEN D.DED_CODE = 'PAR1' THEN 'Yes' ELSE ' ' END AS ARTCPARKING ,CASE WHEN E.WORKTYPE IN ('PERDIEM','PT NO BEN','PT TEMP','TEMP') THEN ' ' WHEN BENDN.EMPLOYEE IS NULL AND BENHL.EMPLOYEE IS NULL AND BENVIS.EMPLOYEE IS NULL THEN ' ' WHEN BENDN.EMPLOYEE IS NOT NULL AND BENHL.EMPLOYEE IS NOT NULL AND BENVIS.EMPLOYEE IS NOT NULL THEN 'Check arrears for HL, DN, and VIS' WHEN BENDN.EMPLOYEE IS NOT NULL AND BENHL.EMPLOYEE IS NOT NULL THEN 'Check arrears for HL and DN' WHEN BENDN.EMPLOYEE IS NOT NULL AND BENVIS.EMPLOYEE IS NOT NULL THEN 'Check arrears for DN and VIS' WHEN BENHL.EMPLOYEE IS NOT NULL AND BENVIS.EMPLOYEE IS NOT NULL THEN 'Check arrears for HL and VIS' WHEN BENHL.EMPLOYEE IS NOT NULL THEN 'Check arrears for HL' WHEN BENDN.EMPLOYEE IS NOT NULL THEN 'Check arrears for DN' ELSE 'Check arrears for VIS' END AS HEALTHDENTALBENEFIT FROM EMP_EXPORT EE JOIN .EMPLOYEE E ON E.EMPLOYEE = EE.EMPLOYEE JOIN .EMPLOYEE S3 ON S3.EMPLOYEE = E.EMPLOYEE JOIN .WORKASSIGNMENT WAN ON WAN.HRORGANIZATION = EE.HRORGANIZATION AND WAN.DELETEFLAG = 0x00000000000000000000000000000000 AND WAN.EMPLOYEE = EE.EMPLOYEE AND WAN.WORKASSIGNMENT = E.PRIMARYWORKASSIGNMENT LEFT JOIN NEW_WORKASSIGNMENTEXPORT WAEN ON WAEN.EMPLOYEE = EE.EMPLOYEE LEFT JOIN .TURNOVERDATA T ON T.EMPLOYEE = EE.EMPLOYEE AND T.AUDITEFFECTIVEDATE = EE.EFFECTIVEDATE -- AND T.AUDITACTION NOT IN ('HireResource','RehireResource') AND T.AUDITACTION NOT IN ('HireResource','RehireResource','Create','RehireFromTA') LEFT JOIN OLD_WORKASSIGNMENTEXPORT WAEO ON WAEO.EMPLOYEE = EE.EMPLOYEE LEFT JOIN S_EMPL S ON S.EMPLOYEE = EE.EMPLOYEE LEFT JOIN .PFIXREFVALUE XO ON XO.SOURCEVALUE1 = E.HRORGANIZATION AND XO.SOURCEVALUE2 = CAST(T.OLDHRORGANIZATIONUNIT AS VARCHAR) LEFT JOIN .PFIXREFVALUE XN ON XN.SOURCEVALUE1 = E.HRORGANIZATION AND XN.SOURCEVALUE2 = CAST(T.NEWHRORGANIZATIONUNIT AS VARCHAR) LEFT JOIN .PFIXREFVALUE XPL ON XPL.SOURCEVALUE1 = E.HRORGANIZATION AND XPL.SOURCEVALUE2 = CAST(WAN.HRORGANIZATIONUNIT AS VARCHAR) LEFT JOIN .JOB JO on JO.HRORGANIZATION = E.HRORGANIZATION AND JO.JOB = T.OLDJOB LEFT JOIN .JOB JN on JN.HRORGANIZATION = E.HRORGANIZATION AND JN.JOB = T.NEWJOB LEFT JOIN .POSITION PO on PO.HRORGANIZATION = E.HRORGANIZATION AND PO.POSITION = T.OLDPOSITION LEFT JOIN .POSITION PN on PN.HRORGANIZATION = E.HRORGANIZATION AND PN.POSITION = T.NEWPOSITION LEFT JOIN WORKASSIGNMENT_POS WAP ON WAP.HRORGANIZATION = EE.HRORGANIZATION -- AND WAP.EMPLOYEE = E.EMPLOYEE AND WAP.POSITION = T.NEWPOSITION AND WAP.EMPLOYEE = E.EMPLOYEE AND WAP.POSITION = WAN.POSITION LEFT JOIN WACOMMENT WACMNT ON WACMNT.HRORGANIZATION = WAP.HRORGANIZATION AND WACMNT.EMPLOYEE = WAP.EMPLOYEE AND WACMNT.WORKASSIGNMENT = WAP.WORKASSIGNMENT LEFT JOIN TAEEMAST TA on TA.EMPLOYEE = EE.EMPLOYEE LEFT JOIN .SUPERVISOR SUP ON SUP.HRORGANIZATION = WAN.HRORGANIZATION AND SUP.SUPERVISOR = WAN.DIRECTSUPERVISOR LEFT JOIN .EMDEDMASTR D ON D.COMPANY = '900' AND D.EMPLOYEE = EE.EMPLOYEE AND D.DED_CODE LIKE 'PAR%' AND ( GETDATE() BETWEEN D.EFFECT_DATE AND D.END_DATE OR ( GETDATE() >= D.EFFECT_DATE AND YEAR(D.END_DATE) = 1753 ) ) LEFT JOIN BEN BENDN ON BENDN.EMPLOYEE = T.EMPLOYEE AND BENDN.PLAN_TYPE = 'DN' LEFT JOIN BEN BENHL ON BENHL.EMPLOYEE = T.EMPLOYEE AND BENHL.PLAN_TYPE = 'HL' AND BENHL.PLAN_CODE <> 'VIS' LEFT JOIN BEN BENVIS ON BENVIS.EMPLOYEE = T.EMPLOYEE AND BENVIS.PLAN_TYPE = 'HL' AND BENVIS.PLAN_CODE = 'VIS' WHERE E.RELATIONSHIPSTATUS IN ('ACTIVE','ON LEAVE') ), WORKASSIGNMENTADDED AS ( SELECT 'WORKASSIGNMENTADDED' AS SOURCE ,E.UNIQUEID ,WAE.HRORGANIZATION ,XPL.DESTINATIONVALUE2 AS CURRPROCLVL ,WAE.EMPLOYEE ,UPPER(RTRIM(E.NFAMILYNAME)) AS NFAMILYNAME ,UPPER(RTRIM(E.NGIVENNAME)) AS NGIVENNAME ,CASE WHEN YEAR(WAE.SYSTEMTIMESTAMP) = 1753 THEN ' ' ELSE RTRIM(CONVERT(VARCHAR(10),WAE.SYSTEMTIMESTAMP,101)) END AS AUDITENTRYSTAMP ,RTRIM(CONVERT(VARCHAR(10),WAE.EFFECTIVEDATE,101)) AS AUDITEFFECTIVEDATE ,RTRIM(WAE.ACTOR) AS USERID -- added ,COALESCE(WAE.INITIATINGACTION,' ') AS INTIATINGACTION ,COALESCE(WAE.INVOKINGACTION,' ') AS INVOKINGACTION -- end of add ,CASE WHEN WAE.ACTION IS NULL THEN ' ' ELSE RTRIM(WAE.ACTION) END AS AUDITACTION ,CASE WHEN WAE.ACTIONREASON IS NULL THEN ' ' ELSE RTRIM(WAE.ACTIONREASON) END AS AUDITACTIONREASON ,CASE WHEN WAE.SUBJECT IS NULL THEN ' ' ELSE RTRIM(WAE.SUBJECT) END AS AUDITBUSINESSSUBJECT ,RTRIM(WAE.LOCATION) AS LOCATION -- added ,RELATIONSHIPTOORGANIZATION ,COALESCE(RTRIM(S.RELATIONSHIPSTATUS),' ') AS OLDRELATIONSHIPSTATUS ,RTRIM(E.RELATIONSHIPSTATUS) AS NEWRELATIONSHIPSTATUS -- end of add ,COALESCE(S.WORKTYPE,' ') AS OLDWORKTYPE ,COALESCE(E.WORKTYPE,' ') AS NEWWORKTYPE ,' ' AS OLDPAYMTHD ,CASE WHEN WAE.PAYMENTSCHEDULE IS NULL THEN ' ' ELSE WAE.PAYMENTSCHEDULE END AS NEWPAYMTHD ,0 AS OLDPAYRATE ,WAE.TOTALPAYRATE AS NEWPAYRATE ,CASE WHEN WAE.OLDPOSITION = WAE.POSITION THEN 0 ELSE WAE.OLDPOSITION END AS OLDPOSITION ,CASE WHEN PO.JOB = WAE.JOB THEN 0 ELSE PO.JOB END AS OLDJOB ,CASE WHEN JO.SHORTDESCRIPTION = JN.SHORTDESCRIPTION THEN ' ' ELSE JO.SHORTDESCRIPTION END AS OLDJOBTITLE ,CASE WHEN WAE.POSITION = WAE.OLDPOSITION THEN 0 ELSE WAE.POSITION END AS NEWPOSITION ,CASE WHEN WAE.JOB = PO.JOB THEN 0 ELSE WAE.JOB END AS NEWJOB ,CASE WHEN JN.SHORTDESCRIPTION = JO.SHORTDESCRIPTION THEN ' ' ELSE JN.SHORTDESCRIPTION END AS NEWJOBTITLE ,CASE WHEN PO.PAYGRADE = WAE.PAYGRADE AND PO.SALARYSTRUCTUREGRADE = WAE.SALARYSTRUCTUREGRADE THEN 0 WHEN PO.PAYGRADE = 0 THEN PO.SALARYSTRUCTUREGRADE ELSE PO.PAYGRADE END AS OLDPAYGRADE ,CASE WHEN WAE.PAYGRADE = PO.PAYGRADE AND WAE.SALARYSTRUCTUREGRADE = PO.SALARYSTRUCTUREGRADE THEN 0 WHEN WAE.PAYGRADE = 0 THEN WAE.SALARYSTRUCTUREGRADE ELSE WAE.PAYGRADE END AS NEWPAYGRADE ,CASE WHEN PO.PAYSTEP = WAE.PAYSTEP THEN 0 ELSE PO.PAYSTEP END AS OLDPAYSTEP ,CASE WHEN WAE.PAYSTEP = PO.PAYSTEP THEN 0 ELSE WAE.PAYSTEP END AS NEWPAYSTEP ,CASE WHEN PO.[UNION] IS NULL THEN ' ' WHEN PO.[UNION] = PN.[UNION] THEN ' ' ELSE PO.[UNION] END AS OLDUNION ,CASE WHEN PN.[UNION] IS NULL THEN ' ' WHEN PN.[UNION] = PO.[UNION] THEN ' ' ELSE PN.[UNION] END AS NEWUNION ,CASE WHEN XO.DESTINATIONVALUE2 IS NULL THEN ' ' WHEN XO.DESTINATIONVALUE2 = XN.DESTINATIONVALUE2 THEN ' ' ELSE XO.DESTINATIONVALUE2 END AS OLDPROCLVL ,CASE WHEN XN.DESTINATIONVALUE2 IS NULL THEN ' ' WHEN XN.DESTINATIONVALUE2 = XO.DESTINATIONVALUE2 THEN ' ' ELSE XN.DESTINATIONVALUE2 END AS NEWPROCLVL ,CASE WHEN PO.BARGAININGUNIT = PN.BARGAININGUNIT THEN ' ' ELSE PO.BARGAININGUNIT END AS OLDBARGAININGUNIT ,CASE WHEN PN.BARGAININGUNIT = PO.BARGAININGUNIT THEN ' ' ELSE PN.BARGAININGUNIT END AS NEWBARGAININGUNIT ,CASE WHEN XO.DESTINATIONVALUE3 IS NULL THEN ' ' WHEN XO.DESTINATIONVALUE3 = XN.DESTINATIONVALUE3 THEN ' ' ELSE XO.DESTINATIONVALUE3 END AS OLDDEPT ,CASE WHEN XN.DESTINATIONVALUE3 IS NULL THEN ' ' WHEN XN.DESTINATIONVALUE3 = XO.DESTINATIONVALUE3 THEN ' ' ELSE XN.DESTINATIONVALUE3 END AS NEWDEPT ,CASE WHEN WAE.PAYFREQUENCY = 1 THEN CONVERT(DECIMAL(10,2),WAE.FTE*1950/52) WHEN WAE.PAYFREQUENCY = 2 THEN CONVERT(DECIMAL(10,2),WAE.FTE*1950/26) ELSE 0 END AS OLDBGTHRS ,CASE WHEN WAN.PAYFREQUENCY = 1 THEN CONVERT(DECIMAL(10,2),WAN.FTE*1950/52) WHEN WAN.PAYFREQUENCY = 2 THEN CONVERT(DECIMAL(10,2),WAN.FTE*1950/26) ELSE 0 END AS NEWBGTHRS ,CASE WHEN UFO.ALPHA IS NULL THEN ' ' WHEN UFO.ALPHA = UFN.ALPHA THEN ' ' ELSE COALESCE(UFO.ALPHA,' ') END AS OLDTEMPLATE ,CASE WHEN UFN.ALPHA IS NULL THEN ' ' WHEN UFN.ALPHA = UFO.ALPHA THEN ' ' ELSE RTRIM(UPPER(UFN.ALPHA)) END AS NEWTEMPLATE ,COALESCE(WACMNT.REASONCOMMENT,' ') AS PTOPAYOUT ,'' AS PTOPAYOUTE ,CASE WHEN TA.AVAIL_HRS_BAL IS NULL THEN 0 ELSE TA.AVAIL_HRS_BAL END AS AVAILHRSBAL ,'TO DO' AS RETRO ,RTRIM(SUP.SUPERVISOREMPLOYEENAME) AS SUPERVISOREMPLOYEENAME ,'TO DO' AS PARKINGCHG ,CASE WHEN D.DED_CODE = 'PARK' THEN 'Yes' ELSE ' ' END AS MERCYPARKING ,CASE WHEN D.DED_CODE = 'PAR1' THEN 'Yes' ELSE ' ' END AS ARTCPARKING ,CASE WHEN E.WORKTYPE IN ('PERDIEM','PT NO BEN','PT TEMP','TEMP') THEN ' ' WHEN BENDN.EMPLOYEE IS NULL AND BENHL.EMPLOYEE IS NULL AND BENVIS.EMPLOYEE IS NULL THEN ' ' WHEN BENDN.EMPLOYEE IS NOT NULL AND BENHL.EMPLOYEE IS NOT NULL AND BENVIS.EMPLOYEE IS NOT NULL THEN 'Check arrears for HL, DN, and VIS' WHEN BENDN.EMPLOYEE IS NOT NULL AND BENHL.EMPLOYEE IS NOT NULL THEN 'Check arrears for HL and DN' WHEN BENDN.EMPLOYEE IS NOT NULL AND BENVIS.EMPLOYEE IS NOT NULL THEN 'Check arrears for DN and VIS' WHEN BENHL.EMPLOYEE IS NOT NULL AND BENVIS.EMPLOYEE IS NOT NULL THEN 'Check arrears for HL and VIS' WHEN BENHL.EMPLOYEE IS NOT NULL THEN 'Check arrears for HL' WHEN BENDN.EMPLOYEE IS NOT NULL THEN 'Check arrears for DN' ELSE 'Check arrears for VIS' END AS HEALTHDENTALBENEFIT FROM .WORKASSIGNMENTEXPORT WAE JOIN .EMPLOYEE E on E.HRORGANIZATION = WAE.HRORGANIZATION AND E.EMPLOYEE = WAE.EMPLOYEE LEFT JOIN S_EMPL S on S.EMPLOYEE = WAE.EMPLOYEE LEFT JOIN .WORKASSIGNMENT WAN ON WAN.HRORGANIZATION = WAE.HRORGANIZATION AND WAN.DELETEFLAG = 0x00000000000000000000000000000000 AND WAN.EMPLOYEE = WAE.EMPLOYEE AND WAN.WORKASSIGNMENT = WAE.WORKASSIGNMENT LEFT JOIN .POSITION PO on PO.HRORGANIZATION = WAE.HRORGANIZATION AND PO.POSITION = WAE.OLDPOSITION LEFT JOIN .POSITION PN on PN.HRORGANIZATION = WAE.HRORGANIZATION AND PN.POSITION = WAE.POSITION LEFT JOIN .PFIXREFVALUE XO ON XO.SOURCEVALUE1 = E.HRORGANIZATION AND XO.SOURCEVALUE2 = CAST(PO.HRORGANIZATIONUNIT AS VARCHAR) LEFT JOIN .PFIXREFVALUE XN ON XN.SOURCEVALUE1 = E.HRORGANIZATION AND XN.SOURCEVALUE2 = CAST(PN.HRORGANIZATIONUNIT AS VARCHAR) LEFT JOIN .PFIXREFVALUE XPL ON XPL.SOURCEVALUE1 = E.HRORGANIZATION AND XPL.SOURCEVALUE2 = CAST(WAE.HRORGANIZATIONUNIT AS VARCHAR) LEFT JOIN .JOB JO on JO.HRORGANIZATION = WAE.HRORGANIZATION AND JO.JOB = PO.JOB LEFT JOIN .JOB JN on JN.HRORGANIZATION = WAE.HRORGANIZATION AND JN.JOB = WAE.JOB LEFT JOIN WORKASSIGNMENT_POS WAP ON WAP.HRORGANIZATION = WAE.HRORGANIZATION AND WAP.EMPLOYEE = WAE.EMPLOYEE AND WAP.POSITION = WAE.OLDPOSITION LEFT JOIN WACOMMENT WACMNT ON WACMNT.HRORGANIZATION = WAE.HRORGANIZATION AND WACMNT.EMPLOYEE = WAE.EMPLOYEE AND WACMNT.WORKASSIGNMENT = WAE.WORKASSIGNMENT LEFT JOIN TAEEMAST TA on TA.EMPLOYEE = WAE.EMPLOYEE LEFT JOIN .USERFIELD_STORAGE UFN ON UFN.UFSCLASSNAME = 'WorkAssignment' AND UFN.UFSUNIQUEID = WAN.UNIQUEID AND UFN.UFSFIELDNAME = 'CHSPayRule2' LEFT JOIN .USERFIELD_STORAGE UFO ON UFO.UFSCLASSNAME = 'WorkAssignment' AND UFO.UFSUNIQUEID = WAP.UNIQUEID AND UFO.UFSFIELDNAME = 'CHSPayRule2' LEFT JOIN .SUPERVISOR SUP ON SUP.HRORGANIZATION = WAE.HRORGANIZATION AND SUP.SUPERVISOR = WAN.DIRECTSUPERVISOR LEFT JOIN .EMDEDMASTR D ON D.COMPANY = '900' AND D.EMPLOYEE = WAE.EMPLOYEE AND D.DED_CODE LIKE 'PAR%' AND ( GETDATE() BETWEEN D.EFFECT_DATE AND D.END_DATE OR ( GETDATE() >= D.EFFECT_DATE AND YEAR(D.END_DATE) = 1753 ) ) LEFT JOIN BEN BENDN ON BENDN.EMPLOYEE = WAE.EMPLOYEE AND BENDN.PLAN_TYPE = 'DN' LEFT JOIN BEN BENHL ON BENHL.EMPLOYEE = WAE.EMPLOYEE AND BENHL.PLAN_TYPE = 'HL' AND BENHL.PLAN_CODE <> 'VIS' LEFT JOIN BEN BENVIS ON BENVIS.EMPLOYEE = WAE.EMPLOYEE AND BENVIS.PLAN_TYPE = 'HL' AND BENVIS.PLAN_CODE = 'VIS' WHERE WAE.WORKASSIGNMENT <> E.PRIMARYWORKASSIGNMENT AND ( CONVERT(CHAR(8),WAE.EFFECTIVEDATE,112) >= '' OR CONVERT(CHAR(8),WAE.SYSTEMTIMESTAMP,112) >= '' ) AND CONVERT(CHAR(8),WAE.EFFECTIVEDATE,112) <= '' -- changed AND ( WAE.ACTION = 'AddWorkAssignment' OR WAE.INITIATINGACTION = 'ChangeRelationshipToOrganization' ) -- end of change AND WAE.AUTHENTICATEDACTOR = 'lawson' ) SELECT UNIONQUERY.* ,CASE WHEN A2.EMPLOYEE IS NULL THEN COALESCE(UFS.ALPHA,'') ELSE '' END AS PRIMARYEMPLOYEEID ,CASE WHEN A2.EMPLOYEE IS NULL THEN '' ELSE CAST(A2.EMPLOYEE AS VARCHAR) END AS SECONDARYEMPLOYEEID ,EA.PACOUNTRY ,EA.PADAADDRESSLINE1 ,EA.PADAADDRESSLINE2 ,EA.PADAADDRESSLINE3 ,EA.PAMUNICIPALITY ,EA.PASTATEPROVINCE ,EA.PAPOSTALCODE FROM ( SELECT T.* FROM TURNOVERDATA T WHERE NOT EXISTS ( SELECT * FROM EMPLOYEEEXPORTDATA E WHERE E.CURRPROCLVL = T.CURRPROCLVL AND E.EMPLOYEE = T.EMPLOYEE AND E.USERID IN (T.USERID, 'lawson') AND E.AUDITACTIONREASON = T.AUDITACTIONREASON AND E.AUDITBUSINESSSUBJECT = T.AUDITBUSINESSSUBJECT AND E.LOCATION = T.LOCATION AND E.OLDWORKTYPE = T.OLDWORKTYPE AND E.NEWWORKTYPE = T.NEWWORKTYPE AND E.OLDPAYMTHD = T.OLDPAYMTHD AND E.NEWPAYMTHD = T.NEWPAYMTHD AND E.OLDPAYRATE = T.OLDPAYRATE AND E.NEWPAYRATE = T.NEWPAYRATE AND E.OLDPOSITION = T.OLDPOSITION AND E.OLDJOB = T.OLDJOB AND E.OLDJOBTITLE = T.OLDJOBTITLE AND E.NEWPOSITION = T.NEWPOSITION AND E.NEWJOB = T.NEWJOB AND E.NEWJOBTITLE = T.NEWJOBTITLE AND E.OLDPAYGRADE = T.OLDPAYGRADE AND E.NEWPAYGRADE = T.NEWPAYGRADE AND E.OLDPAYSTEP = T.OLDPAYSTEP AND E.NEWPAYSTEP = T.NEWPAYSTEP AND E.OLDUNION = T.OLDUNION AND E.NEWUNION = T.NEWUNION AND E.OLDPROCLVL = T.OLDPROCLVL AND E.NEWPROCLVL = T.NEWPROCLVL AND E.OLDBARGAININGUNIT = T.OLDBARGAININGUNIT AND E.NEWBARGAININGUNIT = T.NEWBARGAININGUNIT AND E.OLDDEPT = T.OLDDEPT AND E.NEWDEPT = T.NEWDEPT AND E.OLDBGTHRS = T.OLDBGTHRS AND E.NEWBGTHRS = T.NEWBGTHRS AND E.OLDTEMPLATE = T.OLDTEMPLATE AND E.NEWTEMPLATE = T.NEWTEMPLATE AND E.PTOPAYOUT = T.PTOPAYOUT AND E.PTOPAYOUTE = T.PTOPAYOUTE AND E.AVAILHRSBAL = T.AVAILHRSBAL AND E.RETRO = T.RETRO AND E.SUPERVISOREMPLOYEENAME = T.SUPERVISOREMPLOYEENAME AND E.PARKINGCHG = T.PARKINGCHG AND E.MERCYPARKING = T.MERCYPARKING AND E.ARTCPARKING = T.ARTCPARKING AND E.HEALTHDENTALBENEFIT = T.HEALTHDENTALBENEFIT ) UNION SELECT * FROM EMPLOYEEEXPORTDATA UNION SELECT * FROM WORKASSIGNMENTADDED ) UNIONQUERY LEFT JOIN .USERFIELD_STORAGE UFS ON UFS.UFSUNIQUEID = UNIONQUERY.UNIQUEID AND UFS.UFSCLASSNAME = 'Employee' AND UFS.UFSFIELDNAME = 'CHSPrimaryDualEmployeeID' LEFT JOIN .USERFIELD_STORAGE UFS2 ON UFS2.ALPHA = CAST(UNIONQUERY.EMPLOYEE AS VARCHAR) AND UFS2.UFSCLASSNAME = 'Employee' AND UFS2.UFSFIELDNAME = 'CHSPrimaryDualEmployeeID' AND UFS2.UFSUNIQUEID <> UNIONQUERY.UNIQUEID LEFT JOIN .EMPLOYEE A2 ON A2.UNIQUEID = UFS2.UFSUNIQUEID LEFT JOIN ADDRESS EA ON EA.HRORGANIZATION = UNIONQUERY.HRORGANIZATION AND EA.EMPLOYEE = UNIONQUERY.EMPLOYEE WHERE -- change ( INITIATINGACTION LIKE '%ChangeRelationship%' OR OLDWORKTYPE <> NEWWORKTYPE -- end of change OR OLDPAYMTHD <> NEWPAYMTHD OR OLDPAYRATE <> NEWPAYRATE OR OLDPOSITION <> NEWPOSITION OR OLDJOB <> NEWJOB OR OLDJOBTITLE <> NEWJOBTITLE OR OLDPAYGRADE <> NEWPAYGRADE OR OLDPAYSTEP <> NEWPAYSTEP OR OLDUNION <> NEWUNION OR OLDPROCLVL <> NEWPROCLVL OR OLDBARGAININGUNIT <> NEWBARGAININGUNIT OR OLDDEPT <> NEWDEPT OR OLDBGTHRS <> NEWBGTHRS OR OLDTEMPLATE <> NEWTEMPLATE OR PTOPAYOUT <> ' ' OR PTOPAYOUTE <> ' ' ) ORDER BY CURRPROCLVL, EMPLOYEE, AUDITEFFECTIVEDATE, AUDITENTRYSTAMP, SOURCE -- The MAXDOP option is used to prevent SQL Server Error Msg 1205 - deadlock condition OPTION (MAXDOP 1)