Stored Procedure - usp_DimAssignment

Property	Value
Name	usp_DimAssignment_Select
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name

Data Type

Direction

Description

Name	Type
ASSIGNMENT_A	Table
CHECK_COMPONENT_A	Table
JOB_CLASS_AND_CHILDREN_A	Table
JOB_GROUP_AND_CHILDREN_A	Table
POSITION_A	Table
POSITION_BUDGET_A	Table
UTL_CODE_TABLE	Table

Name	Type
UTL_CODE_TABLE	TABLE
ASSIGNMENT_A	TABLE
CHECK_COMPONENT_A	TABLE
JOB_CLASS_AND_CHILDREN_A	TABLE
JOB_GROUP_AND_CHILDREN_A	TABLE
POSITION_A	TABLE
POSITION_BUDGET_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_DimAssignment_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/* Primary Assignment Source SELECT Statement */
/* Sophia Cowan 09/03/2014 Created for HR/Payroll Dashboards */
CREATE PROC [dbo].[usp_DimAssignment_Select]
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
       CAST('aaa' AS VARCHAR(20)) AS AssignmentAK
      ,CAST(0 AS INT) AS PersonID
      ,CAST('aaa' AS VARCHAR(8)) AS PositionCode
      ,CAST(0 AS SMALLINT) AS AssignmentSequenceNumber
      ,CAST('aaa' AS VARCHAR(30))  AS JobTitle
      ,CAST('aaa' AS VARCHAR(10)) AS AssignmentStatus
      ,CAST('aaa'  AS VARCHAR(3)) AS AssignmentTypeCode
      ,CAST('aaa' AS VARCHAR(60)) AS AssignmentType
      ,CAST('aaa'  AS VARCHAR(3)) AS FunctionCode
      ,CAST('aaa' AS VARCHAR(60)) AS FunctionDescription
      ,CAST('aaa'  AS VARCHAR(28)) AS PrimarySalaryAccountNumber
      ,CAST('19000101' AS DATETIME) AS StartDate
      ,CAST('19000101' AS DATETIME) AS EndDate
      ,CAST('aaa'  AS VARCHAR(8)) AS JobGroupCode
      ,CAST('aaa'  AS VARCHAR(60)) AS JobGroup
      ,CAST('aaa'  AS VARCHAR(8)) AS JobClassCode
      ,CAST('aaa'  AS VARCHAR(8)) AS PayBasis
      ,CAST('aaa'  AS VARCHAR(8)) AS InstitutionalAssignmentTypeCode
      ,CAST('aaa' AS VARCHAR(30)) AS InstitutionalAssignmentType
      ,CAST('aaa' AS VARCHAR(3)) AS PrimaryAssignment
      ,CAST('aaa' AS VARCHAR(10)) AS RegularTemporary
      ,CAST('aaa' AS VARCHAR(3)) AS ExemptStatus
      ,CAST('aaa' AS VARCHAR(10)) AS AssignmentFulltimeParttime
      ,CAST('aaa' AS Varchar(20)) AS AssignmentEndDateRange
      ,CAST('aaa' AS Varchar(20)) AS LastPaymentDateRange
      ,CAST('aaa' AS VARCHAR(8)) AS EndBasisCode
      ,CAST('aaa' AS VARCHAR(30)) AS EndBasis
      ,CAST(0 AS NUMERIC(11,2)) AS BaseSalary
      ,CAST(0 AS NUMERIC(11,2)) AS ActualSalary
      ,CAST(0 AS NUMERIC(9,2)) AS DailyRate
      ,CAST(0 AS NUMERIC(9,4)) AS HourlyRate
      ,CAST(0 AS NUMERIC(9,4)) AS OvertimeRate
      ,CAST(0 AS NUMERIC(11,2)) AS TotalSalary
    END

DECLARE @CurrentDay date
DECLARE @FiscalYear VARCHAR(4)
SET @CurrentDay = GETDATE()
SET @FiscalYear =
  CASE WHEN MONTH(@CurrentDay) > 6 THEN YEAR(@CurrentDay) + 1
       ELSE  YEAR(@CurrentDay)
  END
CREATE TABLE #PayCheck
(
    PersonID int NOT NULL
   ,PositionCode VARCHAR(8) NOT NULL
   ,InternalSeq int NOT NULL
   ,LastCheckDate VARCHAR(8) NOT NULL
)

INSERT INTO #PayCheck
(
  PersonID
  ,PositionCode
  ,InternalSeq
  ,LastCheckDate
)
   SELECT
      CheckDate.PERSON_ID
     ,CheckDate.POSITION_CODE
     ,CheckDate.INTERNAL_SEQ
     ,MAX(CheckDate.PAY_CHECK_DATE)
   FROM CHECK_COMPONENT_A CheckDate
   WHERE LEFT(CheckDate.PAY_CHECK_COMPONENT_TYPE_CODE,10) = '01ASSIGNRG' AND POSITION_CODE IS NOT NULL
   GROUP BY PERSON_ID, POSITION_CODE, INTERNAL_SEQ

-- Get all Assignment Data for Assignments that started on or before today
SELECT
       CAST(
            REPLICATE('0', 9 - LEN(Assign.PERSON_ID)) + CAST(Assign.PERSON_ID AS VARCHAR(9))
            +
            CAST(Assign.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(Assign.POSITION_CODE))
            +
            REPLICATE('0', 3 - LEN(Assign.INTERNAL_SEQ)) + CAST(Assign.INTERNAL_SEQ AS VARCHAR(3))
            AS VARCHAR(20)) AS AssignmentAK
      ,Assign.PERSON_ID AS PersonID
      ,Assign.POSITION_CODE AS PositionCode
      ,Assign.INTERNAL_SEQ AS AssignmentSequenceNumber
      ,CAST(ISNULL(JobClass.JOB_TITLE, 'N/A') AS VARCHAR(30)) AS JobTitle
      ,CAST(
         CASE
           WHEN Assign.END_DATE IS NULL THEN 'Active'
           WHEN Assign.END_DATE = ' ' THEN 'Active'
           WHEN CAST(Assign.END_DATE AS DateTime) >= @CurrentDay THEN 'Active'
           ELSE 'Ended'
           END
         AS VARCHAR(10)) AS AssignmentStatus
      ,CAST(
        CASE
          WHEN TopPosBud.ACCOUNT_CODE IS NULL THEN 'N/A'
          ELSE SUBSTRING(TopPosBud.ACCOUNT_CODE,17,2)
        END AS VARCHAR(3)) AS AssignmentTypeCode
      ,CASE
          WHEN TopPosBud.ACCOUNT_CODE IS NULL THEN 'Unknown'
          ELSE ISNULL(UtlPosType.LONG_DESCRIPTION,'Unknown')
       END AS AssignmentType
      ,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 NULL THEN 'Unknown'
          ELSE ISNULL(UtlFunction.LONG_DESCRIPTION,'Unknown')
       END AS FunctionDescription
      ,CAST(
          CASE
            WHEN TopPosBud.ACCOUNT_CODE IS NULL THEN 'N/A'
            ELSE SUBSTRING(TopPosBud.ACCOUNT_CODE,1,8) + '-' +
                 SUBSTRING(TopPosBud.ACCOUNT_CODE,9,6) + '-' +
                 SUBSTRING(TopPosBud.ACCOUNT_CODE,15,2) + '-' +
                 SUBSTRING(TopPosBud.ACCOUNT_CODE,17,9)
          END AS VARCHAR(28)) AS PrimarySalaryAccountNumber
      ,CAST(ISNULL(Assign.START_DATE, '19000101') AS DATETIME) AS StartDate
      ,CAST(
         CASE
           WHEN Assign.END_DATE = '00000000' THEN '20981231'
           WHEN Assign.END_DATE = ' ' THEN '20981231'
           WHEN Assign.END_DATE IS NULL THEN '20981231'
           ELSE Assign.END_DATE
         END
         AS DATETIME) AS EndDate
      ,Pos.JOB_GROUP_CODE AS JobGroupCode
      ,JobGrp.DESCRIPTION AS JobGroup
      ,Pos.JOB_CLASS_CODE AS JobClassCode
      ,Pos.PAY_BASIS_CODE AS PayBasis
      ,Assign.ASSIGNMENT_TYPE AS InstitutionalAssignmentTypeCode
      ,CAST(ISNULL(AssignType.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS InstitutionalAssignmentType
      ,CAST(
         CASE
           WHEN Assign.PRIMARY_ASSIGNMENT_IND = '1' THEN 'Yes'
           ELSE 'No'
         END
         AS VARCHAR(3)) AS PrimaryAssignment
      ,CAST(
         CASE
           WHEN Assign.REG_TEMP_IND = 'R' THEN 'Regular'
           ELSE 'Temporary'
         END
         AS VARCHAR(10)) AS RegularTemporary
      ,CAST(
         CASE
           WHEN Assign.EXEMPT_NON_EXEMPT = 'E' THEN 'Yes'
           ELSE 'No'
         END
         AS VARCHAR(3)) AS ExemptStatus
      ,CAST(
         CASE
           WHEN Assign.FT_PT_STATUS_CODE = 'P' THEN 'Part Time'
           ELSE 'Full Time'
         END
         AS VARCHAR(10)) AS AssignmentFulltimeParttime
      ,CAST(CASE
               WHEN ISDATE(Assign.END_DATE) = 0 THEN 'N/A'
               WHEN Assign.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) <= 3)) THEN '1 to 3 Months'
               WHEN Assign.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) <= 6)) THEN '4 to 6 Months'
               WHEN Assign.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) <= 12)) THEN '7 Months to 1 Year'
               WHEN Assign.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN Assign.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A'
             END AS Varchar(20)) AS AssignmentEndDateRange
       ,CAST(CASE
               WHEN ISDATE(#PayCheck.LastCheckDate) = 0 THEN 'N/A'
               WHEN #PayCheck.LastCheckDate IS NOT NULL AND (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) <= 3)) THEN '1 to 3 Months'
               WHEN #PayCheck.LastCheckDate IS NOT NULL AND (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) <= 6)) THEN '4 to 6 Months'
               WHEN #PayCheck.LastCheckDate IS NOT NULL AND (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) <= 12)) THEN '7 Months to 1 Year'
               WHEN #PayCheck.LastCheckDate IS NOT NULL AND (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN #PayCheck.LastCheckDate IS NOT NULL AND (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A'
             END AS Varchar(20)) AS LastPaymentDateRange
      ,CAST(ISNULL(Assign.TERMINATION_BASIS_CODE,'N/A') AS VARCHAR(8)) AS EndBasisCode
      ,CAST(ISNULL(EndBasisDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS EndBasis
      ,CAST(ISNULL(Assign.BASE_SALARY,0) AS NUMERIC(11,2)) AS BaseSalary
      ,CAST(ISNULL(Assign.ACTUAL_SALARY,0) AS NUMERIC(11,2)) AS ActualSalary
      ,CAST(ISNULL(Assign.DAILY_RATE,0) AS NUMERIC(9,2)) AS DailyRate
      ,CAST(ISNULL(Assign.HOURLY_RATE,0) AS NUMERIC(9,4)) AS HourlyRate
      ,CAST(ISNULL(Assign.OVERTIME_RATE,0) AS NUMERIC(9,4)) AS OvertimeRate
      ,CAST(ISNULL(Assign.TOTAL_SALARY,0) AS NUMERIC(11,2)) AS TotalSalary
  FROM ASSIGNMENT_A Assign

  LEFT OUTER JOIN POSITION_A Pos
  ON
  (
    Pos.POSITION_CODE = Assign.POSITION_CODE
  )

  LEFT OUTER JOIN JOB_CLASS_AND_CHILDREN_A JobClass
  ON
  (
    JobClass.JOB_CLASS_CODE = Pos.JOB_CLASS_CODE
    AND
    (JobClass.START_DATE  <= Assign.START_DATE
      AND Assign.START_DATE <= ISNULL(JobClass.END_DATE,'20981231')
      AND ISNULL(JobClass.END_DATE,'20981231') >= Assign.END_DATE)
  )
  OUTER APPLY
  (
    SELECT TOP 1
       ACCOUNT_POSITION_CODE
       ,ACCOUNT_CODE
   FROM POSITION_BUDGET_A PosBud
   WHERE
      PosBud.ACCOUNT_POSITION_CODE = Assign.POSITION_CODE
      -- Get the Position Budget data for the Fiscal Year in which the Assignment Ends. Since
      -- Assignments can cross multiple years, we need to get the most current Position Data.
      -- Just looking at accounts that fall into the Starting and Ending Dates, are not good enough
      AND PosBud.ACCOUNT_FISCAL_YEAR =
        CASE
           WHEN (Assign.END_DATE IS NULL OR Assign.END_DATE = ' ') AND MONTH(@CurrentDay) > 6 THEN YEAR(@CurrentDay) + 1
           WHEN (Assign.END_DATE IS NULL OR Assign.END_DATE = ' ') AND MONTH(@CurrentDay) <= 6 THEN YEAR(@CurrentDay)
           WHEN SUBSTRING(Assign.END_DATE,5,2) > '06' THEN SUBSTRING(Assign.END_DATE,1,4) + 1
           ELSE SUBSTRING(Assign.END_DATE,1,4)
        END
        AND ACCOUNT_POSITION_CODE IS NOT NULL
        AND ACCOUNT_PRIMARY_IND = 1
        AND PosBud.ACCOUNT_FISCAL_YEAR =
          CASE WHEN Assign.END_DATE IS NULL THEN @FiscalYear
               WHEN Assign.END_DATE = ' ' THEN @FiscalYear
               WHEN Assign.END_DATE = '00000000' THEN @FiscalYear
               WHEN Assign.END_DATE IS NOT NULL AND SUBSTRING(Assign.END_DATE,5,2) > '06'
                  THEN CAST((LEFT(Assign.END_DATE,4) + 1) AS VARCHAR(4))
               ELSE  (LEFT(Assign.END_DATE,4))
          END
        AND PosBud.ACCOUNT_CODE_START_DATE  <=
          CASE WHEN Assign.END_DATE IS NULL THEN @CurrentDay
               WHEN Assign.END_DATE = ' ' THEN @CurrentDay
               WHEN Assign.END_DATE = '00000000' THEN @CurrentDay
               ELSE Assign.END_DATE
          END
        AND PosBud.ACCOUNT_CODE_END_DATE >= Assign.START_DATE
        ORDER BY ACCOUNT_POSITION_CODE ASC
          ,ACCOUNT_PERC DESC
          ,CASE
             WHEN Assign.END_DATE = ' ' OR Assign.END_DATE IS NULL THEN DATEDIFF(DD,PosBud.ACCOUNT_CODE_START_DATE,@CurrentDay)
             ELSE DATEDIFF(DD,PosBud.ACCOUNT_CODE_START_DATE,PosBud.ACCOUNT_CODE_END_DATE)
            END 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 JOB_GROUP_AND_CHILDREN_A JobGrp
  ON
  (
    JobGrp.JOB_GROUP_CODE = Pos.JOB_GROUP_CODE
    AND JobGrp.PAY_BASIS_CODE = Pos.PAY_BASIS_CODE
    AND JobGrp.JOB_GROUP_CODE IS NOT NULL
    AND JobGrp.PAY_BASIS_CODE IS NOT NULL
   )

  LEFT OUTER JOIN UTL_CODE_TABLE AssignType
  ON
  (
     AssignType.TABLE_NAME = 'ASSIGN'
     AND AssignType.CODE =  Assign.ASSIGNMENT_TYPE
     AND AssignType.STATUS = 'A'
   )

LEFT OUTER JOIN #PayCheck
ON
(
   #PayCheck.PersonID = Assign.PERSON_ID
   AND #PayCheck.PositionCode = Assign.POSITION_CODE
   AND #PayCheck.InternalSeq = Assign.INTERNAL_SEQ
  )
  LEFT OUTER JOIN UTL_CODE_TABLE EndBasisDesc
  ON
  (
     EndBasisDesc.TABLE_NAME = 'TERMINAT-U'
     AND EndBasisDesc.CODE =  Assign.TERMINATION_BASIS_CODE
     AND EndBasisDesc.STATUS = 'A'
   )
WHERE Assign.START_DATE <= @CurrentDay
UNION ALL
-- Get all Assignment Data for Assignments that started after today
SELECT
       CAST(
            REPLICATE('0', 9 - LEN(Assign.PERSON_ID)) + CAST(Assign.PERSON_ID AS VARCHAR(9))
            +
            CAST(Assign.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(Assign.POSITION_CODE))
            +
            REPLICATE('0', 3 - LEN(Assign.INTERNAL_SEQ)) + CAST(Assign.INTERNAL_SEQ AS VARCHAR(3))
            AS VARCHAR(20)) AS AssignmentAK
      ,Assign.PERSON_ID AS PersonID
      ,Assign.POSITION_CODE AS PositionCode
      ,Assign.INTERNAL_SEQ AS AssignmentSequenceNumber
      ,CAST(ISNULL(JobClass.JOB_TITLE, 'N/A') AS VARCHAR(30)) AS JobTitle
      ,CAST(
         CASE
           WHEN Assign.END_DATE IS NULL THEN 'Active'
           WHEN Assign.END_DATE = ' ' THEN 'Active'
           WHEN CAST(Assign.END_DATE AS DateTime) >= @CurrentDay THEN 'Active'
           ELSE 'Ended'
           END
         AS VARCHAR(10)) AS AssignmentStatus
      ,CAST(
        CASE
          WHEN TopPosBud.ACCOUNT_CODE IS NULL THEN 'N/A'
          ELSE SUBSTRING(TopPosBud.ACCOUNT_CODE,17,2)
        END AS VARCHAR(3)) AS AssignmentTypeCode
      ,CASE
          WHEN TopPosBud.ACCOUNT_CODE IS NULL THEN 'Unknown'
          ELSE ISNULL(UtlPosType.LONG_DESCRIPTION,'Unknown')
       END AS AssignmentType
      ,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 NULL THEN 'Unknown'
          ELSE ISNULL(UtlFunction.LONG_DESCRIPTION,'Unknown')
       END AS FunctionDescription
      ,CAST(
          CASE
            WHEN TopPosBud.ACCOUNT_CODE IS NULL THEN 'N/A'
            ELSE SUBSTRING(TopPosBud.ACCOUNT_CODE,1,8) + '-' +
                 SUBSTRING(TopPosBud.ACCOUNT_CODE,9,6) + '-' +
                 SUBSTRING(TopPosBud.ACCOUNT_CODE,15,2) + '-' +
                 SUBSTRING(TopPosBud.ACCOUNT_CODE,17,9)
          END AS VARCHAR(28)) AS PrimarySalaryAccountNumber
      ,CAST(ISNULL(Assign.START_DATE, '19000101') AS DATETIME) AS StartDate
      ,CAST(
         CASE
           WHEN Assign.END_DATE = '00000000' THEN '20981231'
           WHEN Assign.END_DATE = ' ' THEN '20981231'
           WHEN Assign.END_DATE IS NULL THEN '20981231'
           ELSE Assign.END_DATE
         END
         AS DATETIME) AS EndDate
      ,Pos.JOB_GROUP_CODE AS JobGroupCode
      ,JobGrp.DESCRIPTION AS JobGroup
      ,Pos.JOB_CLASS_CODE AS JobClassCode
      ,Pos.PAY_BASIS_CODE AS PayBasis
      ,Assign.ASSIGNMENT_TYPE AS InstitutionalAssignmentTypeCode
      ,CAST(ISNULL(AssignType.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS InstitutionalAssignmentType
      ,CAST(
         CASE
           WHEN Assign.PRIMARY_ASSIGNMENT_IND = '1' THEN 'Yes'
           ELSE 'No'
         END
         AS VARCHAR(3)) AS PrimaryAssignment
      ,CAST(
         CASE
           WHEN Assign.REG_TEMP_IND = 'R' THEN 'Regular'
           ELSE 'Temporary'
         END
         AS VARCHAR(10)) AS RegularTemporary
      ,CAST(
         CASE
           WHEN Assign.EXEMPT_NON_EXEMPT = 'E' THEN 'Yes'
           ELSE 'No'
         END
         AS VARCHAR(3)) AS ExemptStatus
      ,CAST(
         CASE
           WHEN Assign.FT_PT_STATUS_CODE = 'P' THEN 'Part Time'
           ELSE 'Full Time'
         END
         AS VARCHAR(10)) AS AssignmentFulltimeParttime
      ,CAST(CASE
               WHEN ISDATE(Assign.END_DATE) = 0 THEN 'N/A'
               WHEN Assign.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) <= 3)) THEN '1 to 3 Months'
               WHEN Assign.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) <= 6)) THEN '4 to 6 Months'
               WHEN Assign.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) <= 12)) THEN '7 Months to 1 Year'
               WHEN Assign.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN Assign.END_DATE IS NOT NULL AND (DATEDIFF(MM, CAST(Assign.END_DATE AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A'
             END AS Varchar(20)) AS AssignmentEndDateRange
       ,CAST(CASE
               WHEN ISDATE(#PayCheck.LastCheckDate) = 0 THEN 'N/A'
               WHEN #PayCheck.LastCheckDate IS NOT NULL AND (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) <= 3)) THEN '1 to 3 Months'
               WHEN #PayCheck.LastCheckDate IS NOT NULL AND (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) <= 6)) THEN '4 to 6 Months'
               WHEN #PayCheck.LastCheckDate IS NOT NULL AND (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) <= 12)) THEN '7 Months to 1 Year'
               WHEN #PayCheck.LastCheckDate IS NOT NULL AND (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN #PayCheck.LastCheckDate IS NOT NULL AND (DATEDIFF(MM, CAST(#PayCheck.LastCheckDate AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A'
             END AS Varchar(20)) AS LastPaymentDateRange
      ,CAST(ISNULL(Assign.TERMINATION_BASIS_CODE,'N/A') AS VARCHAR(8)) AS EndBasisCode
      ,CAST(ISNULL(EndBasisDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS EndBasis
      ,CAST(ISNULL(Assign.BASE_SALARY,0) AS NUMERIC(11,2)) AS BaseSalary
      ,CAST(ISNULL(Assign.ACTUAL_SALARY,0) AS NUMERIC(11,2)) AS ActualSalary
      ,CAST(ISNULL(Assign.DAILY_RATE,0) AS NUMERIC(9,2)) AS DailyRate
      ,CAST(ISNULL(Assign.HOURLY_RATE,0) AS NUMERIC(9,4)) AS HourlyRate
      ,CAST(ISNULL(Assign.OVERTIME_RATE,0) AS NUMERIC(9,4)) AS OvertimeRate
      ,CAST(ISNULL(Assign.TOTAL_SALARY,0) AS NUMERIC(11,2)) AS TotalSalary
  FROM ASSIGNMENT_A Assign

  LEFT OUTER JOIN POSITION_A Pos
  ON
  (
    Pos.POSITION_CODE = Assign.POSITION_CODE
  )

  LEFT OUTER JOIN JOB_CLASS_AND_CHILDREN_A JobClass
  ON
  (
    JobClass.JOB_CLASS_CODE = Pos.JOB_CLASS_CODE
    AND
    (CAST(JobClass.START_DATE AS DATETIME)  <= @CurrentDay
      AND CAST(ISNULL(JobClass.END_DATE,'20981231') AS DATETIME)  >= @CurrentDay)
  )
  OUTER APPLY
  (
    SELECT TOP 1
       ACCOUNT_POSITION_CODE
       ,ACCOUNT_CODE
   FROM POSITION_BUDGET_A PosBud
   WHERE
      PosBud.ACCOUNT_POSITION_CODE = Assign.POSITION_CODE
      -- Get the Position Budget data for the Fiscal Year in which the Assignment Ends. Since
      -- Assignments can cross multiple years, we need to get the most current Position Data.
      -- Just looking at accounts that fall into the Starting and Endding Dates, are not good enough
    AND ACCOUNT_POSITION_CODE IS NOT NULL
    AND ACCOUNT_PRIMARY_IND = 1
    AND PosBud.ACCOUNT_FISCAL_YEAR = CASE
    WHEN Assign.END_DATE IS NULL THEN @FiscalYear
    WHEN Assign.END_DATE = ' ' THEN @FiscalYear
    WHEN Assign.END_DATE = '00000000' THEN @FiscalYear
    WHEN Assign.END_DATE IS NOT NULL AND SUBSTRING(Assign.END_DATE,5,2) > '06'
         THEN CAST((LEFT(Assign.END_DATE,4) + 1) AS VARCHAR(4))
    ELSE  (LEFT(Assign.END_DATE,4))
    END
  AND PosBud.ACCOUNT_CODE_START_DATE  <=
    CASE
      WHEN Assign.END_DATE IS NULL THEN @CurrentDay
      WHEN Assign.END_DATE = ' ' THEN @CurrentDay
      WHEN Assign.END_DATE = '00000000' THEN @CurrentDay
      ELSE Assign.END_DATE
     END
  AND PosBud.ACCOUNT_CODE_END_DATE >= Assign.START_DATE
    ORDER BY ACCOUNT_POSITION_CODE ASC
            ,ACCOUNT_PERC DESC
            ,CASE
               WHEN Assign.END_DATE = ' ' OR Assign.END_DATE IS NULL THEN DATEDIFF(DD,PosBud.ACCOUNT_CODE_START_DATE,@CurrentDay)
               ELSE DATEDIFF(DD,PosBud.ACCOUNT_CODE_START_DATE,PosBud.ACCOUNT_CODE_END_DATE)
              END 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 JOB_GROUP_AND_CHILDREN_A JobGrp
  ON
  (
    JobGrp.JOB_GROUP_CODE = Pos.JOB_GROUP_CODE
    AND JobGrp.PAY_BASIS_CODE = Pos.PAY_BASIS_CODE
    AND JobGrp.JOB_GROUP_CODE IS NOT NULL
    AND JobGrp.PAY_BASIS_CODE IS NOT NULL
   )

  LEFT OUTER JOIN UTL_CODE_TABLE AssignType
  ON
  (
     AssignType.TABLE_NAME = 'ASSIGN'
     AND AssignType.CODE =  Assign.ASSIGNMENT_TYPE
     AND AssignType.STATUS = 'A'
   )

LEFT OUTER JOIN #PayCheck
ON
(
   #PayCheck.PersonID = Assign.PERSON_ID
   AND #PayCheck.PositionCode = Assign.POSITION_CODE
   AND #PayCheck.InternalSeq = Assign.INTERNAL_SEQ
  )
  LEFT OUTER JOIN UTL_CODE_TABLE EndBasisDesc
  ON
  (
     EndBasisDesc.TABLE_NAME = 'TERMINAT-U'
     AND EndBasisDesc.CODE =  Assign.TERMINATION_BASIS_CODE
     AND EndBasisDesc.STATUS = 'A'
   )
WHERE Assign.START_DATE > @CurrentDay