GHR Turnover Reporting

Sort:
You are not authorized to post a reply.
Author
Messages
Beth E Edwards
New Member
Posts: 3
New Member

    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.

    Quarantine
    Basic Member
    Posts: 24
    Basic Member

      We are facing this issue. Let us know if you found any solution.

      Beth E Edwards
      New Member
      Posts: 3
      New Member

        Thanks! I'll let you know if we find anything.

        Tom Zydel
        New Member
        Posts: 2
        New Member

          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.

           

          Terry Wilson
          New Member
          Posts: 6
          New Member

            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

            pjnowak
            Posts: 3
              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) 
              You are not authorized to post a reply.