User Fields and Crystal Reports

I have been asked about this a few times recently and wanted to share what we did to make user fields easier in Crystal Reports:   If course, the field_name field should be your field names... 

We added the following as a database view:

SELECT     COMPANY, EMPLOYEE,
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'AAP Job Groups') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [AAP Job Groups],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Acquisition') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS Acquisition,
                          (SELECT     ufv.D_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Acquisition Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Acquisition Date],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Acquisition source') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [Acquisition source],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Agreements') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS Agreements,
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Announcement needed?') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [Announcement needed?],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Application Received') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [Application Received],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'BN-EL/DI  Date 5') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [BN-EL/DI  Date 5],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'BN-HL/DN  Date 2') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [BN-HL/DN  Date 2],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'BTI Completed') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [BTI Completed],
                          (SELECT     ufv.D_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'BenSal1_Start') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS BenSal1_Start,
                          (SELECT     ufv.D_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'BenSal1_Stop') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS BenSal1_Stop,
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'CLEAVER C') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [CLEAVER C],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'CLEAVER D') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [CLEAVER D],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'CLEAVER I') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [CLEAVER I],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'CLEAVER S') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [CLEAVER S],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Ceridian ID') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Ceridian ID],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Checklist Completed') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [Checklist Completed],
                          (SELECT     ufv.N_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Code of Ethics') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Code of Ethics],
                          (SELECT     ufv.D_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Computer Loan Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [Computer Loan Date],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'DB Code') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [DB Code],
                          (SELECT     ufv.N_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Drug Policy') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Drug Policy],
                          (SELECT     ufv.D_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Drug Test Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Drug Test Date],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Equity-Holder') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Equity-Holder],
                          (SELECT     ufv.D_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Expected Term Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [Expected Term Date],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Fingerprint') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS Fingerprint,
                          (SELECT     ufv.D_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Fingerprint Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Fingerprint Date],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'I-9 Received') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [I-9 Received],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Kronos') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS Kronos,
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Kronos Accrual Prof') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [Kronos Accrual Prof],
                          (SELECT     ufv.N_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Lump Sum Merit Amt') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [Lump Sum Merit Amt],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Merit Increase') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Merit Increase],
                          (SELECT     ufv.N_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Midpoint2006') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS Midpoint2006,
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'NT Logon') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [NT Logon],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Name Separator - IS') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [Name Separator - IS],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'NonEmp/Retire Health') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [NonEmp/Retire Health],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Offer Letter') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Offer Letter],
                          (SELECT     ufv.N_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'PC Policy') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [PC Policy],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Pay Range') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Pay Range],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Pay Rule') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Pay Rule],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Pension Code') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Pension Code],
                          (SELECT     ufv.N_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Policy Manual') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Policy Manual],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'President/LOB') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [President/LOB],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'RS Acct Type') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [RS Acct Type],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'RS Bank Acct') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [RS Bank Acct],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'RS Rollover') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [RS Rollover],
                          (SELECT     ufv.D_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'RS Rollover Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                       AS [RS Rollover Date],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'RS Route') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [RS Route],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Rehire') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS Rehire,
                          (SELECT     ufv.D_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Rehire Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Rehire Date],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Salary Review Rating') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [Salary Review Rating],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Security Profile') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Security Profile],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Status Change') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Status Change],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Term Code') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Term Code],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Term Controllable') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [Term Controllable],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Term PIP?') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [Term PIP?],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Term Regrettable?') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                   (ufv.EMP_APP = 0)) AS [Term Regrettable?],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'VAC Adj Reason') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [VAC Adj Reason],
                          (SELECT     ufv.N_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'VAC Hours Adj') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [VAC Hours Adj],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'VAC Supv/Officer') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                      AS [VAC Supv/Officer],
                          (SELECT     ufv.A_FIELD AS FIELD_VALUE
                            FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                   dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                            WHERE      (ufn.FIELD_NAME = 'Walking Program') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                       AS [Walking Program]