PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 03/05/2021 9:50 AM by  pjnowak
GHR Turnover Reporting
 5 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Beth E Edwards
HRIS Analyst
Private
New Member
(7 points)
New Member
Posts:3


Send Message:

--
02/17/2021 4:17 PM

    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
    Developer
    Private
    Basic Member
    (46 points)
    Basic Member
    Posts:24


    Send Message:

    --
    02/21/2021 11:15 PM

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

    Beth E Edwards
    HRIS Analyst
    Private
    New Member
    (7 points)
    New Member
    Posts:3


    Send Message:

    --
    02/22/2021 11:03 AM

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

    Tom Zydel
    VP
    Private
    New Member
    (6 points)
    New Member
    Posts:2


    Send Message:

    --
    03/01/2021 9:02 AM

    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
    Sr. Applications Analyst
    Summa Health System
    New Member
    (6 points)
    New Member
    Posts:4


    Send Message:

    --
    03/01/2021 9:05 AM

    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
    Application Analyst
    Private
    (9 points)
    Posts:3


    Send Message:

    --
    03/05/2021 9:50 AM
    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.