We use this complex SQL query in an IPA to produce a csv file of employment changes that can be imported into Excel. It was developed to notify the Payroll dept. of changes. The query is a union of separate queries of the TURNOVER, EMPLOYEEEXPORT, and WORKASSIGNMENT tables joined with many other tables.
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)