DWStaging
 usp_DimEmployee_Select_Old (Stored Procedure)
Home  Diagrams  Documents  Images  DWStaging  Storage  Users  Roles  Schemas  Tables  Views  Indexes  Triggers  Stored Procedures  Functions  Data Types  XML Schemas  Rules  Defaults  Fulltext Catalogs  Synonyms  Keys  Partitions  Assemblies 
Current Path:  DWStaging   Stored Procedures   usp_DimEmployee_Select_Old
  Properties
Property Value
Name usp_DimEmployee_Select_Old
Schema dbo
Is Encrypted False
Ansi Nulls Status True
Quoted Identifier Status True
Description
  Parameters
Name Data Type Direction Description
  Parent Dependencies (objects that usp_DimEmployee_Select_Old depends on)
Name Type
Table
Table
Table
Table
Table
Table
Table
Table
Table
Table
Table
Table
Table
Table
  Child Dependencies (objects that depend on usp_DimEmployee_Select_Old)
Name Type
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
  Extended Properties
Object Property Value
   Annotations
Object Property Value
  DDL
/****** Object: StoredProcedure [dbo].[usp_DimEmployee_Select_Old] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/* Primary Employee Source and NON Employee Source SELECT Statement */
/* Sophia Cowan 09/03/2014 Created for HR/Payroll Dashboards */
CREATE PROC [dbo].[usp_DimEmployee_Select_Old]
AS 
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT 
       CAST(0 AS Int) AS EmployeeAK
      ,CAST(0 AS Int) AS PersonID
      ,CAST('aaa' AS VARCHAR(8)) AS EmployeeTypeCode
      ,CAST('aaa' AS VARCHAR(30)) AS EmployeeType
      ,CAST('aaa' AS VARCHAR(80)) AS FullName
      ,CAST('aaa' AS VARCHAR(50)) AS LastName
      ,CAST('aaa' AS VARCHAR(30)) AS FirstName
      ,CAST('aaa' AS VARCHAR(3)) AS MiddleInitial
      ,CAST('aaa' AS VARCHAR(3)) AS GenderCode
      ,CAST('aaa' AS VARCHAR(30)) AS Gender
      ,CAST('aaa' AS VARCHAR(3)) AS EthnicityCode
      ,CAST('aaa' AS VARCHAR(30)) AS Ethnicity
      ,CAST('aaa' AS VARCHAR(3)) AS StateCode
      ,CAST('aaa' AS VARCHAR(30)) AS State
      ,CAST('aaa' AS VARCHAR(11)) AS ZipCode
      ,CAST('aaa' AS VARCHAR(3)) AS CountryCode 
      ,CAST('aaa' AS VARCHAR(30)) AS Country   
      ,CAST('aaa' AS VARCHAR(3)) AS CitizenshipCode
      ,CAST('aaa' AS VARCHAR(30)) AS Citizenship
      ,CAST('aaa' AS VARCHAR(3)) AS CountryOfCitizenshipCode
      ,CAST('aaa' AS VARCHAR(30)) AS CountryOfCitizenship
      ,CAST('aaa' AS VARCHAR(3)) AS HasActiveAssignment
      ,CAST(0 AS INT) AS NumberOfActiveAssignments
      ,CAST('aaa' AS Varchar(30)) AS LastActiveAssignmentDateRange  
      ,CAST('aaa' AS Varchar(30)) AS LastPayCheckDateRange          
      ,CAST('aaa' AS VARCHAR(3)) AS PositionTypeCode
      ,CAST('aaa' AS VARCHAR(60)) as PositionType
      ,CAST('aaa' AS VARCHAR(3)) AS FunctionCode
      ,CAST('aaa' AS VARCHAR(60)) as FunctionDescription  
      ,CAST('aaa' AS VARCHAR(3)) AS LocationCode
      ,CAST('aaa' AS VARCHAR(60)) as Location         
      ,CAST('aaa' AS VARCHAR(28)) AS PrimarySalaryAccountNumber   
      ,CAST('aaa' AS VARCHAR(3)) AS IsStudent   
      ,CAST('aaa' AS VARCHAR(9)) AS EmployeeStudentId
      ,CAST('aaa' AS VARCHAR(3)) AS HasDirectDeposit
      ,CAST('19000101' AS DateTime) AS HireDate
      ,CAST('aaa' AS VARCHAR(10)) AS EmployeeFulltimeParttime
      ,CAST('19000101' AS DATETIME) AS TerminationDate
      ,CAST('aaa' AS VARCHAR(30)) AS TerminationDateRange
      ,CAST(0 AS INT) AS YearsOfServiceToInstitution     
      ,CAST('19000101' AS Date) AS DateOfBirth
    END
DECLARE @CurrentDay date
SET @CurrentDay = GETDATE()
/* Determine the correct Address to use, 1st HOME, then MAIL then the first address on file */
CREATE TABLE #EmpAddr
( 
    PersonID int NOT NULL
   ,StateCode VARCHAR(3) NOT NULL
   ,ZipCode VARCHAR(11) NOT NULL
   ,CountryCode VARCHAR(3) NOT NULL
 )
 
INSERT INTO #EmpAddr
(
  PersonID 
  ,StateCode
  ,ZipCode 
  ,CountryCode
)
SELECT 
   PERSON_ID as PersonID
  ,CAST
     (CASE
         WHEN HomeAddr.STATE_CODE IS NOT NULL THEN HomeAddr.STATE_CODE
         WHEN (HomeAddr.STATE_CODE IS NULL AND MailAddr.STATE_CODE IS NOT NULL) THEN MailAddr.STATE_CODE
         WHEN (HomeAddr.STATE_CODE IS NULL AND MailAddr.STATE_CODE IS NULL AND FirstAddr.STATE_CODE IS NOT NULL)  
            THEN FirstAddr.STATE_CODE
         ELSE 'N/A'
       END AS VARCHAR(3)) AS StateCode
  ,CAST
     (CASE
         WHEN HomeAddr.ZIP IS NOT NULL THEN HomeAddr.ZIP
         WHEN (HomeAddr.ZIP IS NULL AND MailAddr.ZIP IS NOT NULL) THEN MailAddr.ZIP
         WHEN (HomeAddr.ZIP IS NULL AND MailAddr.ZIP IS NULL AND FirstAddr.ZIP IS NOT NULL)  
            THEN FirstAddr.ZIP
         ELSE 'N/A'
       END AS VARCHAR(11)) AS ZipCode       
  ,CAST
     (CASE
         WHEN HomeAddr.COUNTRY_CODE IS NOT NULL THEN HomeAddr.COUNTRY_CODE
         WHEN (HomeAddr.COUNTRY_CODE IS NULL AND MailAddr.COUNTRY_CODE IS NOT NULL) THEN MailAddr.COUNTRY_CODE
         WHEN (HomeAddr.COUNTRY_CODE IS NULL AND MailAddr.COUNTRY_CODE IS NULL AND FirstAddr.COUNTRY_CODE IS NOT NULL)  
            THEN FirstAddr.COUNTRY_CODE
         WHEN (HomeAddr.COUNTRY_CODE IS NULL AND MailAddr.COUNTRY_CODE IS NULL and FirstAddr.COUNTRY_CODE IS NULL)
            AND (HomeAddr.STATE_CODE IS NOT NULL OR MailAddr.STATE_CODE IS NOT NULL OR FirstAddr.STATE_CODE IS NOT NULL)
            THEN 'US'            
         ELSE 'N/A'
       END AS VARCHAR(3)) AS CountryCode
  FROM EMPLOYEE_A Emp1
  LEFT OUTER JOIN EMPLOYEE_A_ADDRESS_INFO HomeAddr
  ON
  (
     HomeAddr.ISN_EMPLOYEE_A = Emp1.ISN_EMPLOYEE_A
     AND HomeAddr.ADDRESS_TYPE_CODE = 'HOME'
  )
  
  LEFT OUTER JOIN EMPLOYEE_A_ADDRESS_INFO MailAddr
  ON
  (
     MailAddr.ISN_EMPLOYEE_A = Emp1.ISN_EMPLOYEE_A
     AND MailAddr.ADDRESS_TYPE_CODE = 'MAIL'
  )
    
  LEFT OUTER JOIN EMPLOYEE_A_ADDRESS_INFO FirstAddr
  ON
  (
     FirstAddr.ISN_EMPLOYEE_A = Emp1.ISN_EMPLOYEE_A
     AND FirstAddr.cnxarraycolumn = 0 
     AND FirstAddr.ADDRESS_TYPE_CODE <> 'HOME' AND FirstAddr.ADDRESS_TYPE_CODE <> 'MAIL'
  )
  WHERE Emp1.PERSON_ID IS NOT NULL
CREATE TABLE #NonEmpAddr
( 
    PersonID int NOT NULL
   ,StateCode VARCHAR(3) NOT NULL
   ,ZipCode VARCHAR(11) NOT NULL
   ,CountryCode VARCHAR(3) NOT NULL
 )
 
INSERT INTO #NonEmpAddr
(
  PersonID 
  ,StateCode
  ,ZipCode 
  ,CountryCode
)
SELECT 
   PERSON_ID as PersonID
  ,CAST
     (CASE
         WHEN HomeAddr.STATE_CODE IS NOT NULL THEN HomeAddr.STATE_CODE
         WHEN (HomeAddr.STATE_CODE IS NULL AND MailAddr.STATE_CODE IS NOT NULL) THEN MailAddr.STATE_CODE
         WHEN (HomeAddr.STATE_CODE IS NULL AND MailAddr.STATE_CODE IS NULL AND FirstAddr.STATE_CODE IS NOT NULL)  
            THEN FirstAddr.STATE_CODE
         ELSE 'N/A'
       END AS VARCHAR(3)) AS StateCode
  ,CAST
     (CASE
         WHEN HomeAddr.ZIP IS NOT NULL THEN HomeAddr.ZIP
         WHEN (HomeAddr.ZIP IS NULL AND MailAddr.ZIP IS NOT NULL) THEN MailAddr.ZIP
         WHEN (HomeAddr.ZIP IS NULL AND MailAddr.ZIP IS NULL AND FirstAddr.ZIP IS NOT NULL)  
            THEN FirstAddr.ZIP
         ELSE 'N/A'
       END AS VARCHAR(11)) AS ZipCode       
  ,CAST
     (CASE
         WHEN HomeAddr.COUNTRY_CODE IS NOT NULL THEN HomeAddr.COUNTRY_CODE
         WHEN (HomeAddr.COUNTRY_CODE IS NULL AND MailAddr.COUNTRY_CODE IS NOT NULL) THEN MailAddr.COUNTRY_CODE
         WHEN (HomeAddr.COUNTRY_CODE IS NULL AND MailAddr.COUNTRY_CODE IS NULL AND FirstAddr.COUNTRY_CODE IS NOT NULL)  
            THEN FirstAddr.COUNTRY_CODE
         WHEN (HomeAddr.COUNTRY_CODE IS NULL AND MailAddr.COUNTRY_CODE IS NULL and FirstAddr.COUNTRY_CODE IS NULL)
            AND (HomeAddr.STATE_CODE IS NOT NULL OR MailAddr.STATE_CODE IS NOT NULL OR FirstAddr.STATE_CODE IS NOT NULL)
            THEN 'US'            
         ELSE 'N/A'
       END AS VARCHAR(3)) AS CountryCode
  FROM NON_EMPLOYEE_A Emp1
  LEFT OUTER JOIN NON_EMPLOYEE_A_ADDRESS_INFO HomeAddr
  ON
  (
     HomeAddr.ISN_NON_EMPLOYEE_A = Emp1.ISN_NON_EMPLOYEE_A
     AND HomeAddr.ADDRESS_TYPE_CODE = 'HOME'
  )
  
  LEFT OUTER JOIN NON_EMPLOYEE_A_ADDRESS_INFO MailAddr
  ON
  (
     MailAddr.ISN_NON_EMPLOYEE_A = Emp1.ISN_NON_EMPLOYEE_A
     AND MailAddr.ADDRESS_TYPE_CODE = 'MAIL'
  )
    
  LEFT OUTER JOIN NON_EMPLOYEE_A_ADDRESS_INFO FirstAddr
  ON
  (
     FirstAddr.ISN_NON_EMPLOYEE_A = Emp1.ISN_NON_EMPLOYEE_A
     AND FirstAddr.cnxarraycolumn = 0 
     AND FirstAddr.ADDRESS_TYPE_CODE <> 'HOME' AND FirstAddr.ADDRESS_TYPE_CODE <> 'MAIL'
  )
  WHERE Emp1.PERSON_ID IS NOT NULL
CREATE TABLE #EmpDaysService
( 
    IsnEmp int NOT NULL
   ,NumberOfDays int NOT NULL
 )
 
/* Determine the number of days of service based on the Hire and Termination Dates */
INSERT INTO #EmpDaysService
(
  IsnEmp 
  ,NumberOfDays
)
SELECT 
     ISN_EMPLOYEE_A
    ,DATEDIFF(DD,CAST(Hire.HIRE_DATE AS DATETIME),ISNULL(CAST(Hire.TERM_DATE AS DATETIME),@CurrentDay)) AS NumberOfDays
    FROM EMPLOYEE_A_EMPLOYMENT_INFO Hire
    GROUP BY  ISN_EMPLOYEE_A , Hire.HIRE_DATE, Hire.TERM_DATE
  
CREATE TABLE #NonEmpDaysService
( 
    IsnNonEmp int NOT NULL
   ,NumberOfDays int NOT NULL
 )
 
INSERT INTO #NonEmpDaysService
(
  IsnNonEmp 
  ,NumberOfDays
)
SELECT 
     ISN_NON_EMPLOYEE_A
    ,DATEDIFF(DD,CAST(Hire.HIRE_DATE AS DATETIME),ISNULL(CAST(Hire.TERM_DATE AS DATETIME),@CurrentDay)) AS NumberOfDays
    FROM NON_EMPLOYEE_A_EMPLOYMENT_INFO Hire
    GROUP BY  ISN_NON_EMPLOYEE_A , Hire.HIRE_DATE, Hire.TERM_DATE
  
/* Find all the Employees *//  
      
SELECT CAST(Emp.PERSON_ID AS Int) AS EmployeeAK
      ,CAST(Emp.PERSON_ID AS Int) AS PersonID
      ,CAST('FROMEMPL' AS VARCHAR(8)) AS EmployeeTypeCode
      ,CAST('Employee' AS VARCHAR(30)) AS EmployeeType
      ,CAST(
        CASE  
           WHEN Emp.LASTNAME IS NULL THEN 'N/A'
           WHEN Emp.FIRSTNAME IS NULL THEN Emp.LASTNAME 
           WHEN Emp.MIDDLE IS NULL THEN Emp.LASTNAME + ', ' + Emp.FIRSTNAME
           ELSE Emp.LASTNAME + ', ' + Emp.FIRSTNAME + ' ' + LEFT(Emp.MIDDLE,1)
         END AS VARCHAR(80)) AS FullName
      ,ISNULL(Emp.LASTNAME,'N/A') AS LastName
      ,ISNULL(Emp.FIRSTNAME,'N/A') AS FirstName
      ,CAST(
         CASE 
            WHEN Emp.MIDDLE IS NULL THEN ' '
            ELSE LEFT(Emp.MIDDLE,1)
         END AS VARCHAR(1)) AS MiddleInitial
      ,CAST(ISNULL(Emp.SEX_CODE,'N/A') AS VARCHAR(3)) AS GenderCode
      ,ISNULL(GenderDesc.DESCRIPTION,'N/A') AS Gender
      ,ISNULL(Emp.ETHNIC_CODE, 'N/A') AS EthnicCode
      ,ISNULL(EthnicDesc.DESCRIPTION, 'Not Reported') AS Ethnicity
      ,ISNULL(Addr.StateCode, 'N/A') AS StateCode
      ,ISNULL(StateDesc.DESCRIPTION, 'N/A') AS State
      ,ISNULL(Addr.ZipCode, 'N/A') AS ZipCode
      ,ISNULL(Addr.CountryCode, 'N/A') AS CountryCode 
      ,ISNULL(CountryDesc.DESCRIPTION, 'N/A') AS Country   
      ,CAST(ISNULL(Emp.CITIZEN_TYPE_CODE, 'N/A') AS VARCHAR(3)) AS CitizenshipCode
      ,ISNULL(CitizenDesc.DESCRIPTION, 'N/A') AS Citizenship
      ,CAST(
         CASE
            WHEN Emp.CITIZEN_COUNTRY_CODE IS NULL AND Emp.CITIZEN_TYPE_CODE = 'C' THEN 'US'
            WHEN Emp.CITIZEN_COUNTRY_CODE IS NULL THEN 'N/A'
            ELSE Emp.CITIZEN_COUNTRY_CODE
         END AS VARCHAR(3)) AS CountryOfCitizenshipCode
      ,CAST(
         CASE
            WHEN Emp.CITIZEN_COUNTRY_CODE IS NULL AND Emp.CITIZEN_TYPE_CODE = 'C' THEN ISNULL(USCountryDesc.DESCRIPTION, 'N/A')
            ELSE ISNULL(CitCountryDesc.DESCRIPTION, 'N/A')
         END AS VARCHAR(30)) AS CountryOfCitizenship
      ,CAST(
         CASE 
           WHEN NumberOfActiveAssignments > 0 THEN 'Yes'
           ELSE 'No'
         END AS VARCHAR(3)) AS HasActiveAssignment
      ,CAST(ISNULL(NumberOfActiveAssignments ,0) AS INT) AS NumberOfActiveAssignments
      ,CAST(CASE
               WHEN NumberOfActiveAssignments > 0 THEN 'N/A'
               WHEN ISDATE(AssignNotAct.END_DATE) = 0 THEN 'N/A'
               WHEN AssignNotAct.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) <= 3)) THEN '1 to 3 Months'
               WHEN AssignNotAct.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) <= 6)) THEN '4 to 6 Months'
               WHEN AssignNotAct.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) <= 12)) THEN '7 Months to 1 Year'
               WHEN AssignNotAct.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN AssignNotAct.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A'
             END AS Varchar(30)) AS LastActiveAssignmentDateRange  
       ,CAST(CASE
               WHEN ISDATE(LastPayCheck.PAY_CHECK_DATE) = 0 THEN 'N/A'
               WHEN LastPayCheck.PAY_CHECK_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) <= 3)) THEN '1 to 3 Months'
               WHEN LastPayCheck.PAY_CHECK_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) <= 6)) THEN '4 to 6 Months'
               WHEN LastPayCheck.PAY_CHECK_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) <= 12)) THEN '7 Months to 1 Year'
               WHEN LastPayCheck.PAY_CHECK_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN LastPayCheck.PAY_CHECK_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A' 
             END AS Varchar(30)) AS LastPayCheckDateRange  
      ,CAST(
        CASE 
          WHEN TopPosBud.ACCOUNT_CODE IS NULL THEN 'N/A'
          ELSE SUBSTRING(TopPosBud.ACCOUNT_CODE,17,2) 
        END AS VARCHAR(3)) AS PositionTypeCode                     
      ,CASE 
         WHEN TopPosBud.ACCOUNT_CODE IS NOT NULL THEN ISNULL(UtlPosType.LONG_DESCRIPTION,'Unknown')
         ELSE 'Unknown'
       END AS PositionType
      ,CAST(
        CASE 
          WHEN TopPosBud.ACCOUNT_CODE IS NULL THEN 'N/A'
          ELSE LEFT(TopPosBud.ACCOUNT_CODE,2) 
        END AS VARCHAR(3)) AS FunctionCode       
      ,CASE 
         WHEN TopPosBud.ACCOUNT_CODE IS NOT NULL THEN ISNULL(UtlFunction.LONG_DESCRIPTION,'Unknown')
         ELSE 'Unknown'
       END AS FunctionDescription
      ,CAST(
         CASE 
           WHEN TopPosBud.ACCOUNT_CODE IS NULL THEN 'N/A'
           ELSE SUBSTRING(TopPosBud.ACCOUNT_CODE,15,2) 
         END AS VARCHAR(3)) AS LocationCode
      ,CAST(
        CASE 
         WHEN TopPosBud.ACCOUNT_CODE IS NOT NULL THEN ISNULL(Figadm6.TABLE_DESC,'Unknown')
         ELSE 'Unknown'
       END AS VARCHAR(60)) AS Location           
      ,CAST(
          CASE
            WHEN TopPosBud.ACCOUNT_CODE IS NOT NULL THEN SUBSTRING(TopPosBud.ACCOUNT_CODE,1,8) + '-' + 
                                                         SUBSTRING(TopPosBud.ACCOUNT_CODE,9,6) + '-' + 
                                                         SUBSTRING(TopPosBud.ACCOUNT_CODE,15,2) + '-' + 
                                                         SUBSTRING(TopPosBud.ACCOUNT_CODE,17,9) 
            ELSE 'N/A'
          END AS VARCHAR(28)) AS PrimarySalaryAccountNumber   
      ,CAST(
          CASE
             WHEN Stdnt.STUDENT_ID IS NOT NULL THEN 'Yes'
             ELSE 'No'
          END AS VARCHAR(3)) AS IsStudent   
      ,CAST(ISNULL(Stdnt.STUDENT_ID, 'N/A') AS VARCHAR(9)) AS EmployeeStudentId
      ,CAST(
          CASE 
             WHEN DirectDep.PAY_BANK_ACCOUNT_NUMBER IS NULL THEN 'No'
             ELSE 'Yes'
          END AS VARCHAR(3)) AS HasDirectDeposit
      ,CAST(ISNULL(CurrentHireDate.HIRE_DATE,'19000101') AS DateTime) AS HireDate
      ,CAST(
          CASE
             WHEN CurrentHireDate.HIRE_FT_PT_STATUS_CODE = 'P' THEN 'Part Time'
             ELSE 'Full Time'
          END AS VARCHAR(10)) AS EmployeeFulltimeParttime
      ,CAST( 
         CASE
            WHEN CurrentHireDate.TERM_DATE IS NULL THEN '19000101'
            ELSE CurrentHireDate.TERM_DATE
         END AS DATETIME) AS TerminationDate
       ,CAST(CASE
               WHEN ISDATE(CurrentHireDate.TERM_DATE) = 0 THEN 'N/A'
               WHEN CurrentHireDate.TERM_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) <= 3)) THEN '1 to 3 Months'
               WHEN CurrentHireDate.TERM_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) <= 6)) THEN '4 to 6 Months'
               WHEN CurrentHireDate.TERM_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) <= 12)) THEN '7 Months to 1 Year'
               WHEN CurrentHireDate.TERM_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN CurrentHireDate.TERM_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A' 
             END AS Varchar(30)) AS TerminationDateRange
       ,CAST(
          CASE 
             WHEN YearsService.NumberOfDays  IS NULL THEN 0
             ELSE YearsService.NumberOfDays / 365 
          END AS INT) AS YearsOfServiceToInstitution  
       ,CAST(
         CASE
          WHEN Emp.BIRTH_DATE IS NULL THEN '1900-01-01'
          WHEN ISDATE(Emp.BIRTH_DATE) = 0 THEN '1900-01-01'
          ELSE Emp.BIRTH_DATE
         END AS DATE) AS DateOfBirth              
  FROM EMPLOYEE_A Emp
  
  LEFT OUTER JOIN UTL_CODE_TABLE GenderDesc
  ON
  (
     GenderDesc.TABLE_NAME = 'GENDER'
     AND GenderDesc.CODE = Emp.SEX_CODE
     AND GenderDesc.STATUS = 'A'
  )
  
  LEFT OUTER JOIN UTL_CODE_TABLE EthnicDesc
  ON
  (
     EthnicDesc.TABLE_NAME = 'PE-ETHNIC'
     AND EthnicDesc.CODE = Emp.ETHNIC_CODE
     AND EthnicDesc.STATUS = 'A'
  )
  
  LEFT OUTER JOIN #EmpAddr Addr
  ON
  (
     Addr.PersonID = Emp.PERSON_ID
  )
  
  LEFT OUTER JOIN UTL_CODE_TABLE StateDesc
  ON
  (
     StateDesc.TABLE_NAME = 'STATE'
     AND StateDesc.CODE = Addr.StateCode
     AND StateDesc.STATUS = 'A'
  ) 
  
  LEFT OUTER JOIN UTL_CODE_TABLE CountryDesc
  ON
  (
     CountryDesc.TABLE_NAME = 'COUNTRY-ST'
     AND CountryDesc.CODE = Addr.CountryCode
     AND CountryDesc.STATUS = 'A'
  ) 
   LEFT OUTER JOIN UTL_CODE_TABLE CitizenDesc
  ON
  (
     CitizenDesc.TABLE_NAME = 'CITIZEN'
     AND CitizenDesc.CODE = Emp.CITIZEN_TYPE_CODE
     AND CitizenDesc.STATUS = 'A'
  ) 
  
  LEFT OUTER JOIN UTL_CODE_TABLE CitCountryDesc
  ON
  (
     CitCountryDesc.TABLE_NAME = 'COUNTRY-ST'
     AND CitCountryDesc.CODE = Emp.CITIZEN_COUNTRY_CODE
     AND CitCountryDesc.STATUS = 'A'
  )   
     
  LEFT OUTER JOIN UTL_CODE_TABLE USCountryDesc
  ON
  (
     USCountryDesc.TABLE_NAME = 'COUNTRY-ST'
     AND USCountryDesc.CODE = 'US'
     AND USCountryDesc.STATUS = 'A'
  )   
    
  LEFT OUTER JOIN
  (
    SELECT PERSON_ID as PersonId
            ,COUNT(*) as NumberOfActiveAssignments
    FROM ASSIGNMENT_A Assign
    WHERE 
        (CAST(Assign.START_DATE as DateTime) <= @CurrentDay AND (CAST(ISNULL(Assign.END_DATE,'20981231') as DateTime)) >= @CurrentDay)
     OR (CAST(Assign.START_DATE as DateTime) <= @CurrentDay AND Assign.END_DATE = '00000000')
     OR (CAST(Assign.START_DATE as DateTime) <= @CurrentDay AND Assign.END_DATE = ' ')
GROUP BY PERSON_ID
) Assign ON Assign.PersonId = Emp.PERSON_ID
  OUTER APPLY
  (
    SELECT TOP 1
       AssignInAct.PERSON_ID
       ,AssignInAct.END_DATE
    FROM ASSIGNMENT_A AssignInAct
    WHERE 
      (
            AssignInAct.PERSON_ID = Emp.PERSON_ID
        AND AssignInAct.POSITION_CODE IS NOT NULL
        AND
         (CAST(AssignInAct.START_DATE as DateTime) > @CurrentDay
         OR (CAST(ISNULL(AssignInAct.END_DATE,'20981231') as DateTime) < @CurrentDay AND AssignInAct.END_DATE <> '00000000' AND AssignInAct.END_DATE <> ' '))
       )
  ORDER BY PERSON_ID ASC, AssignInAct.END_DATE DESC
  ) AssignNotAct
  
 
 OUTER APPLY
 (
   SELECT TOP 1
   CheckDate.PAY_CHECK_DATE
   FROM CHECK_SUMMARY_A CheckDate
   WHERE CheckDate.PERSON_ID = Emp.PERSON_ID
   ORDER BY PERSON_ID ASC, CheckDate.PAY_CHECK_DATE DESC
 ) LastPayCheck        
   
  OUTER APPLY
  (    
    SELECT TOP 1 
           POSITION_CODE
           ,INTERNAL_SEQ
    FROM ASSIGNMENT_A Assign1
    WHERE Emp.PERSON_ID = Assign1.PERSON_ID
          AND Assign1.PRIMARY_ASSIGNMENT_IND = '1'
          AND CAST(Assign1.START_DATE as DateTime) <= @CurrentDay 
    ORDER BY 
     
      CASE
        WHEN END_DATE = ' ' THEN '20981231'
        WHEN END_DATE = '00000000' THEN '20981231'
        ELSE END_DATE
      END DESC, 
      START_DATE DESC
 ) PrimAssign
 
   OUTER APPLY
  (
    SELECT TOP 1
       ACCOUNT_POSITION_CODE
       ,ACCOUNT_CODE
   FROM POSITION_BUDGET_A PosBud
   WHERE 
      PosBud.ACCOUNT_POSITION_CODE = PrimAssign.POSITION_CODE
  AND (CAST(ISNULL(PosBud.ACCOUNT_CODE_START_DATE,'19000101') AS DateTime)) <= @CurrentDay
  AND ACCOUNT_POSITION_CODE IS NOT NULL
  AND ACCOUNT_PRIMARY_IND = 1
  ORDER BY 
         ACCOUNT_POSITION_CODE ASC
        ,CASE
           WHEN (PosBud.ACCOUNT_CODE_END_DATE IS NULL OR PosBud.ACCOUNT_CODE_END_DATE = ' ') AND MONTH(@CurrentDay) > 6 THEN YEAR(@CurrentDay) + 1
           WHEN (PosBud.ACCOUNT_CODE_END_DATE IS NULL OR PosBud.ACCOUNT_CODE_END_DATE = ' ') AND MONTH(@CurrentDay) <= 6 THEN YEAR(@CurrentDay)
           WHEN SUBSTRING(PosBud.ACCOUNT_CODE_END_DATE,5,2) > '06' THEN SUBSTRING(PosBud.ACCOUNT_CODE_END_DATE,1,4) + 1
           ELSE SUBSTRING(PosBud.ACCOUNT_CODE_END_DATE,1,4)
        END  DESC
      ,ACCOUNT_PERC DESC         
      ,ACCOUNT_CODE ASC
  ) TopPosBud
       
  LEFT OUTER JOIN UTL_CODE_TABLE UtlPosType
  ON
  (  
     UtlPosType.TABLE_NAME = 'GLC2-DESC'
     AND UtlPosType.CODE = SUBSTRING(TopPosBud.ACCOUNT_CODE,17,2)
     AND UtlPosType.STATUS = 'A'
     AND TopPosBud.ACCOUNT_CODE IS NOT NULL
   ) 
  LEFT OUTER JOIN UTL_CODE_TABLE UtlFunction
  ON
  (  
     UtlFunction.TABLE_NAME = 'F002'
     AND UtlFunction.CODE =  LEFT(TopPosBud.ACCOUNT_CODE,2) 
     AND UtlFunction.STATUS = 'A'
     AND TopPosBud.ACCOUNT_CODE IS NOT NULL
   ) 
  LEFT OUTER JOIN FIG_TABLES_FILE Figadm6
  ON
  (
     Figadm6.TABLE_NAME = 'FIGADM6'
     AND Figadm6.TABLE_CODE = SUBSTRING(TopPosBud.ACCOUNT_CODE,15,2)
  )
  
        
  LEFT OUTER JOIN ST_STDNT_A Stdnt
  ON
  (
     Stdnt.STUDENT_SSN = Emp.SSN
     AND
     Stdnt.DOB = Emp.BIRTH_DATE
  )   
 
  LEFT OUTER JOIN EMPLOYEE_A_PAY_BANKING_DATA DirectDep
  ON
  (  
     DirectDep.ISN_EMPLOYEE_A = Emp.ISN_EMPLOYEE_A
     AND DirectDep.cnxarraycolumn = 0
  )
  
 OUTER APPLY
 (
   SELECT 
      SUM(NumberOfDays) AS NumberOfDays
   FROM #EmpDaysService EmpHire
   WHERE Emp.ISN_EMPLOYEE_A = EmpHire.IsnEmp
   GROUP BY EmpHire.IsnEmp
 ) YearsService
 
 
  OUTER APPLY
 (
   SELECT TOP 1
      Hire.HIRE_DATE
     ,Hire.ISN_EMPLOYEE_A
     ,Hire.HIRE_FT_PT_STATUS_CODE
     ,Hire.TERM_DATE
   FROM EMPLOYEE_A_EMPLOYMENT_INFO Hire
   WHERE Emp.ISN_EMPLOYEE_A = Hire.ISN_EMPLOYEE_A
   ORDER BY Emp.ISN_EMPLOYEE_A ASC, Hire.HIRE_DATE DESC
 ) CurrentHireDate   
 
              
  WHERE Emp.PERSON_ID IS NOT NULL
UNION ALL
SELECT CAST(NonEmp.PERSON_ID AS Int) AS EmployeeAK
      ,CAST(NonEmp.PERSON_ID AS Int) AS PersonID
      ,CAST(ISNULL(NON_EMPLOYEE_TYPE_CODE,'N/A') AS VARCHAR(8)) AS EmployeeTypeCode
      ,CAST(ISNULL(EmpTypeDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS EmployeeType
      ,CAST(
        CASE  
           WHEN NonEmp.LASTNAME IS NULL THEN 'N/A'
           WHEN NonEmp.FIRSTNAME IS NULL THEN NonEmp.LASTNAME 
           WHEN NonEmp.MIDDLE IS NULL THEN NonEmp.LASTNAME + ', ' + NonEmp.FIRSTNAME
           ELSE NonEmp.LASTNAME + ', ' + NonEmp.FIRSTNAME + ' ' + LEFT(NonEmp.MIDDLE,1)
         END AS VARCHAR(80)) AS FullName
      ,ISNULL(NonEmp.LASTNAME,'N/A') AS LastName
      ,ISNULL(NonEmp.FIRSTNAME,'N/A') AS FirstName
      ,CAST(
         CASE 
            WHEN NonEmp.MIDDLE IS NULL THEN ' '
            ELSE LEFT(NonEmp.MIDDLE,1)
         END AS VARCHAR(1)) AS MiddleInitial
      ,CAST(ISNULL(NonEmp.SEX_CODE,'N/A') AS VARCHAR(3)) AS GenderCode
      ,ISNULL(GenderDesc.DESCRIPTION,'N/A') AS Gender
      ,ISNULL(NonEmp.ETHNIC_CODE, 'N/A') AS EthnicCode
      ,ISNULL(EthnicDesc.DESCRIPTION, 'Not Reported') AS Ethnicity
      ,ISNULL(Addr.StateCode, 'N/A') AS StateCode
      ,ISNULL(StateDesc.DESCRIPTION, 'N/A') AS State
      ,ISNULL(Addr.ZipCode, 'N/A') AS ZipCode
      ,ISNULL(Addr.CountryCode, 'N/A') AS CountryCode 
      ,ISNULL(CountryDesc.DESCRIPTION, 'N/A') AS Country   
      ,CAST(ISNULL(NonEmp.CITIZEN_TYPE_CODE, 'N/A') AS VARCHAR(3)) AS CitizenshipCode
      ,ISNULL(CitizenDesc.DESCRIPTION, 'N/A') AS Citizenship
      ,CAST(
         CASE
            WHEN NonEmp.CITIZEN_COUNTRY_CODE IS NULL AND NonEmp.CITIZEN_TYPE_CODE = 'C' THEN 'US'
            WHEN NonEmp.CITIZEN_COUNTRY_CODE IS NULL THEN 'N/A'
            ELSE NonEmp.CITIZEN_COUNTRY_CODE
         END AS VARCHAR(3)) AS CountryOfCitizenshipCode
      ,CAST(
         CASE
            WHEN NonEmp.CITIZEN_COUNTRY_CODE IS NULL AND NonEmp.CITIZEN_TYPE_CODE = 'C' THEN ISNULL(USCountryDesc.DESCRIPTION, 'N/A')
            ELSE ISNULL(CitCountryDesc.DESCRIPTION, 'N/A')
         END AS VARCHAR(30)) AS CountryOfCitizenship
      ,CAST(
         CASE 
           WHEN NumberOfActiveAssignments > 0 THEN 'Yes'
           ELSE 'No'
         END AS VARCHAR(3)) AS HasActiveAssignment
      ,CAST(ISNULL(NumberOfActiveAssignments ,0) AS INT) AS NumberOfActiveAssignments
      ,CAST(CASE
               WHEN NumberOfActiveAssignments > 0 THEN 'N/A'
               WHEN ISDATE(AssignNotAct.END_DATE) = 0 THEN 'N/A'
               WHEN AssignNotAct.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) <= 3)) THEN '1 to 3 Months'
               WHEN AssignNotAct.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) <= 6)) THEN '4 to 6 Months'
               WHEN AssignNotAct.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) <= 12)) THEN '7 Months to 1 Year'
               WHEN AssignNotAct.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN AssignNotAct.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(AssignNotAct.END_DATE AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A'
             END AS Varchar(30)) AS LastActiveAssignmentDateRange  
       ,CAST(CASE
               WHEN ISDATE(LastPayCheck.PAY_CHECK_DATE) = 0 THEN 'N/A'
               WHEN LastPayCheck.PAY_CHECK_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) <= 3)) THEN '1 to 3 Months'
               WHEN LastPayCheck.PAY_CHECK_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) <= 6)) THEN '4 to 6 Months'
               WHEN LastPayCheck.PAY_CHECK_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) <= 12)) THEN '7 Months to 1 Year'
               WHEN LastPayCheck.PAY_CHECK_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN LastPayCheck.PAY_CHECK_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(LastPayCheck.PAY_CHECK_DATE AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A' 
             END AS Varchar(30)) AS LastPayCheckDateRange     
      ,CAST(
        CASE 
          WHEN TopPosBud.ACCOUNT_CODE IS NULL THEN 'N/A'
          ELSE SUBSTRING(TopPosBud.ACCOUNT_CODE,17,2) 
        END AS VARCHAR(3)) AS PositionTypeCode                  
      ,CASE 
         WHEN TopPosBud.ACCOUNT_CODE IS NOT NULL THEN ISNULL(UtlPosType.LONG_DESCRIPTION,'Unknown')
         ELSE 'Unknown'
       END AS PositionType
      ,CAST(
        CASE 
          WHEN TopPosBud.ACCOUNT_CODE IS NULL THEN 'N/A'
          ELSE LEFT(TopPosBud.ACCOUNT_CODE,2) 
        END AS VARCHAR(3)) AS FunctionCode       
      ,CASE 
         WHEN TopPosBud.ACCOUNT_CODE IS NOT NULL THEN ISNULL(UtlFunction.LONG_DESCRIPTION,'Unknown')
         ELSE 'Unknown'
       END AS FunctionDescription
      ,CAST(
         CASE 
           WHEN TopPosBud.ACCOUNT_CODE IS NULL THEN 'N/A'
           ELSE SUBSTRING(TopPosBud.ACCOUNT_CODE,15,2) 
         END AS VARCHAR(3)) AS LocationCode
      ,CAST(
        CASE 
         WHEN TopPosBud.ACCOUNT_CODE IS NOT NULL THEN ISNULL(Figadm6.TABLE_DESC,'Unknown')
         ELSE 'Unknown'
       END AS VARCHAR(60)) AS Location           
      ,CAST(
          CASE
            WHEN TopPosBud.ACCOUNT_CODE IS NOT NULL THEN SUBSTRING(TopPosBud.ACCOUNT_CODE,1,8) + '-' + 
                                                         SUBSTRING(TopPosBud.ACCOUNT_CODE,9,6) + '-' + 
                                                         SUBSTRING(TopPosBud.ACCOUNT_CODE,15,2) + '-' + 
                                                         SUBSTRING(TopPosBud.ACCOUNT_CODE,17,9) 
            ELSE 'N/A'
          END AS VARCHAR(28)) AS PrimarySalaryAccountNumber   
      ,CAST(
          CASE
             WHEN Stdnt.STUDENT_ID IS NOT NULL THEN 'Yes'
             ELSE 'No'
          END AS VARCHAR(3)) AS IsStudent   
      ,CAST(ISNULL(Stdnt.STUDENT_ID, 'N/A') AS VARCHAR(9)) AS EmployeeStudentId
      ,CAST(
          CASE 
             WHEN DirectDep.PAY_BANK_ACCOUNT_NUMBER IS NULL THEN 'No'
             ELSE 'Yes'
          END AS VARCHAR(3)) AS HasDirectDeposit
      ,CAST(ISNULL(CurrentHireDate.HIRE_DATE,'19000101') AS DateTime) AS HireDate
      ,CAST(
          CASE
             WHEN CurrentHireDate.HIRE_FT_PT_STATUS_CODE = 'P' THEN 'Part Time'
             ELSE 'Full Time'
          END AS VARCHAR(10)) AS EmployeeFulltimeParttime
      ,CAST( 
         CASE
            WHEN CurrentHireDate.TERM_DATE IS NULL THEN '19000101'
            ELSE CurrentHireDate.TERM_DATE
         END AS DATETIME) AS TerminationDate
       ,CAST(CASE
               WHEN ISDATE(CurrentHireDate.TERM_DATE) = 0 THEN 'N/A'
               WHEN CurrentHireDate.TERM_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) <= 3)) THEN '1 to 3 Months'
               WHEN CurrentHireDate.TERM_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) <= 6)) THEN '4 to 6 Months'
               WHEN CurrentHireDate.TERM_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) <= 12)) THEN '7 Months to 1 Year'
               WHEN CurrentHireDate.TERM_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN CurrentHireDate.TERM_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(CurrentHireDate.TERM_DATE AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A' 
             END AS Varchar(30)) AS TerminationDateRange
       ,CAST(
          CASE 
             WHEN YearsService.NumberOfDays  IS NULL THEN 0
             ELSE YearsService.NumberOfDays / 365 
          END AS INT) AS YearsOfServiceToInstitution  
       ,CAST(
         CASE
          WHEN NonEmp.BIRTH_DATE IS NULL THEN '1900-01-01'
          WHEN ISDATE(NonEmp.BIRTH_DATE) = 0 THEN '1900-01-01'
          ELSE NonEmp.BIRTH_DATE
         END AS DATE) AS DateOfBirth      
  FROM NON_EMPLOYEE_A NonEmp
  
  CROSS APPLY
  (
    SELECT TOP 1
    NonEmpAssign.POSITION_CODE
    FROM ASSIGNMENT_A NonEmpAssign
    WHERE NonEmpAssign.PERSON_ID = NonEmp.PERSON_ID
  ) IsNonEmployee  
     
  LEFT OUTER JOIN UTL_CODE_TABLE EmpTypeDesc
  ON
  (
     EmpTypeDesc.TABLE_NAME = 'NON-EMPLOY'
     AND EmpTypeDesc.CODE = NonEmp.NON_EMPLOYEE_TYPE_CODE
     AND EmpTypeDesc.STATUS = 'A'   
  )
  LEFT OUTER JOIN UTL_CODE_TABLE GenderDesc
  ON
  (
     GenderDesc.TABLE_NAME = 'GENDER'
     AND GenderDesc.CODE = NonEmp.SEX_CODE
     AND GenderDesc.STATUS = 'A'
  )
  
  LEFT OUTER JOIN UTL_CODE_TABLE EthnicDesc
  ON
  (
     EthnicDesc.TABLE_NAME = 'PE-ETHNIC'
     AND EthnicDesc.CODE = NonEmp.ETHNIC_CODE
     AND EthnicDesc.STATUS = 'A'
  )
  
  LEFT OUTER JOIN #NonEmpAddr Addr
  ON
  (
     Addr.PersonID = NonEmp.PERSON_ID
  )
  
  LEFT OUTER JOIN UTL_CODE_TABLE StateDesc
  ON
  (
     StateDesc.TABLE_NAME = 'STATE'
     AND StateDesc.CODE = Addr.StateCode
     AND StateDesc.STATUS = 'A'
  ) 
  
  LEFT OUTER JOIN UTL_CODE_TABLE CountryDesc
  ON
  (
     CountryDesc.TABLE_NAME = 'COUNTRY-ST'
     AND CountryDesc.CODE = Addr.CountryCode
     AND CountryDesc.STATUS = 'A'
  ) 
   LEFT OUTER JOIN UTL_CODE_TABLE CitizenDesc
  ON
  (
     CitizenDesc.TABLE_NAME = 'CITIZEN'
     AND CitizenDesc.CODE = NonEmp.CITIZEN_TYPE_CODE
     AND CitizenDesc.STATUS = 'A'
  ) 
  
  LEFT OUTER JOIN UTL_CODE_TABLE CitCountryDesc
  ON
  (
     CitCountryDesc.TABLE_NAME = 'COUNTRY-ST'
     AND CitCountryDesc.CODE = NonEmp.CITIZEN_COUNTRY_CODE
     AND CitCountryDesc.STATUS = 'A'
  )   
     
  LEFT OUTER JOIN UTL_CODE_TABLE USCountryDesc
  ON
  (
     USCountryDesc.TABLE_NAME = 'COUNTRY-ST'
     AND USCountryDesc.CODE = 'US'
     AND USCountryDesc.STATUS = 'A'
  )   
    
  LEFT OUTER JOIN
  (
    SELECT PERSON_ID as PersonId
            ,COUNT(*) as NumberOfActiveAssignments
    FROM ASSIGNMENT_A Assign
    WHERE 
        (CAST(Assign.START_DATE as DateTime) <= @CurrentDay AND (CAST(ISNULL(Assign.END_DATE,'20981231') as DateTime)) >= @CurrentDay)
     OR (CAST(Assign.START_DATE as DateTime) <= @CurrentDay AND Assign.END_DATE = '00000000')
     OR (CAST(Assign.START_DATE as DateTime) <= @CurrentDay AND Assign.END_DATE = ' ')
GROUP BY PERSON_ID
) Assign ON Assign.PersonId = NonEmp.PERSON_ID
  OUTER APPLY
  (
    SELECT TOP 1
       AssignInAct.PERSON_ID
       ,AssignInAct.END_DATE
    FROM ASSIGNMENT_A AssignInAct
    WHERE 
      (
            AssignInAct.PERSON_ID = NonEmp.PERSON_ID
        AND AssignInAct.POSITION_CODE IS NOT NULL
        AND
         (CAST(AssignInAct.START_DATE as DateTime) > @CurrentDay
         OR (CAST(ISNULL(AssignInAct.END_DATE,'20981231') as DateTime) < @CurrentDay AND AssignInAct.END_DATE <> '00000000' AND AssignInAct.END_DATE <> ' '))
       )
  ORDER BY PERSON_ID ASC, AssignInAct.END_DATE DESC
  ) AssignNotAct
  
 
 OUTER APPLY
 (
   SELECT TOP 1
   CheckDate.PAY_CHECK_DATE
   FROM CHECK_SUMMARY_A CheckDate
   WHERE CheckDate.PERSON_ID = NonEmp.PERSON_ID
   ORDER BY PERSON_ID ASC, CheckDate.PAY_CHECK_DATE DESC
 ) LastPayCheck        
   
  OUTER APPLY
  (    
    SELECT TOP 1 
           POSITION_CODE
           ,INTERNAL_SEQ
    FROM ASSIGNMENT_A Assign1
    WHERE NonEmp.PERSON_ID = Assign1.PERSON_ID
          AND Assign1.PRIMARY_ASSIGNMENT_IND = '1'
          AND CAST(Assign1.START_DATE as DateTime) <= @CurrentDay 
    ORDER BY 
     
      CASE
        WHEN END_DATE = ' ' THEN '20981231'
        WHEN END_DATE = '00000000' THEN '20981231'
        ELSE END_DATE
      END DESC, 
      START_DATE DESC
 ) PrimAssign
 
 
    OUTER APPLY
  (
    SELECT TOP 1
       ACCOUNT_POSITION_CODE
       ,ACCOUNT_CODE
   FROM POSITION_BUDGET_A PosBud
   WHERE 
      PosBud.ACCOUNT_POSITION_CODE = PrimAssign.POSITION_CODE
  AND (CAST(ISNULL(PosBud.ACCOUNT_CODE_START_DATE,'19000101') AS DateTime)) <= @CurrentDay
  AND ACCOUNT_POSITION_CODE IS NOT NULL
  AND ACCOUNT_PRIMARY_IND = 1
  ORDER BY 
         ACCOUNT_POSITION_CODE ASC
        ,CASE
           WHEN (PosBud.ACCOUNT_CODE_END_DATE IS NULL OR PosBud.ACCOUNT_CODE_END_DATE = ' ') AND MONTH(@CurrentDay) > 6 THEN YEAR(@CurrentDay) + 1
           WHEN (PosBud.ACCOUNT_CODE_END_DATE IS NULL OR PosBud.ACCOUNT_CODE_END_DATE = ' ') AND MONTH(@CurrentDay) <= 6 THEN YEAR(@CurrentDay)
           WHEN SUBSTRING(PosBud.ACCOUNT_CODE_END_DATE,5,2) > '06' THEN SUBSTRING(PosBud.ACCOUNT_CODE_END_DATE,1,4) + 1
           ELSE SUBSTRING(PosBud.ACCOUNT_CODE_END_DATE,1,4)
        END  DESC
      ,ACCOUNT_PERC DESC         
      ,ACCOUNT_CODE ASC
  ) TopPosBud
  
 
  LEFT OUTER JOIN UTL_CODE_TABLE UtlPosType
  ON
  (  
     UtlPosType.TABLE_NAME = 'GLC2-DESC'
     AND UtlPosType.CODE = SUBSTRING(TopPosBud.ACCOUNT_CODE,17,2)
     AND UtlPosType.STATUS = 'A'
     AND TopPosBud.ACCOUNT_CODE IS NOT NULL
   ) 
  LEFT OUTER JOIN UTL_CODE_TABLE UtlFunction
  ON
  (  
     UtlFunction.TABLE_NAME = 'F002'
     AND UtlFunction.CODE =  LEFT(TopPosBud.ACCOUNT_CODE,2) 
     AND UtlFunction.STATUS = 'A'
     AND TopPosBud.ACCOUNT_CODE IS NOT NULL
   ) 
  LEFT OUTER JOIN FIG_TABLES_FILE Figadm6
  ON
  (
     Figadm6.TABLE_NAME = 'FIGADM6'
     AND Figadm6.TABLE_CODE = SUBSTRING(TopPosBud.ACCOUNT_CODE,15,2)
  )
  
  LEFT OUTER JOIN ST_STDNT_A Stdnt
  ON
  (
     Stdnt.STUDENT_SSN = NonEmp.SSN
     AND
     Stdnt.DOB = NonEmp.BIRTH_DATE
  )   
 
  LEFT OUTER JOIN NON_EMPLOYEE_A_PAY_BANKING_DATA DirectDep
  ON
  (  
     DirectDep.ISN_NON_EMPLOYEE_A = NonEmp.ISN_NON_EMPLOYEE_A
     AND DirectDep.cnxarraycolumn = 0
  )
  
 OUTER APPLY
 (
   SELECT 
      SUM(NumberOfDays) AS NumberOfDays
   FROM #NonEmpDaysService NonEmpHire
   WHERE NonEmp.ISN_NON_EMPLOYEE_A = NonEmpHire.IsnNonEmp
   GROUP BY NonEmpHire.IsnNonEmp
 ) YearsService    
  
  
  OUTER APPLY
 (
   SELECT TOP 1
      Hire.HIRE_DATE
     ,Hire.ISN_NON_EMPLOYEE_A
     ,Hire.HIRE_FT_PT_STATUS_CODE
     ,Hire.TERM_DATE
   FROM NON_EMPLOYEE_A_EMPLOYMENT_INFO Hire
   WHERE NonEmp.ISN_NON_EMPLOYEE_A = Hire.ISN_NON_EMPLOYEE_A
   ORDER BY NonEmp.ISN_NON_EMPLOYEE_A ASC, Hire.HIRE_DATE DESC
 ) CurrentHireDate    
 
  LEFT OUTER JOIN EMPLOYEE_A EmpCheck
  ON
  (
     EmpCheck.PERSON_ID = NonEmp.PERSON_ID
  )   
              
  WHERE NonEmp.PERSON_ID IS NOT NULL
  AND EmpCheck.PERSON_ID IS NULL
Powered by BI Documenter