Stored Procedure - usp_DimPosition

Property	Value
Name	usp_DimPosition_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
FIG_TABLES_FILE	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
FIG_TABLES_FILE	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_DimPosition_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_DimPosition_Select
Title:            Primary Select for the Position Dimension
Date:            06/25/2014
System/Project:    Integrated Financials
Description:    This procedure will combine the PPosition information from DWStaging for
the creation of the DimPosition
Revision History:
06/25/2014 Sophia Cowan        Created
*/
CREATE PROC [dbo].[usp_DimPosition_Select]
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT

    CAST('aaa' AS VARCHAR(8)) as PositionAK
      ,CAST('aaa' AS VARCHAR(8)) as PositionCode
      ,CAST('aaa' AS VARCHAR(30))as PositionTitle
      ,CAST('aaa' AS VARCHAR(10)) AS PositionStatus
      ,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('1900-01-01' AS DateTime) DateEstablished
      ,CAST('1900-01-01' AS DateTime)  AS DateInactivated
      ,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(3)) AS Budgeted
      ,CAST('aaa' AS VARCHAR(10)) AS RegularTemporary
      ,CAST('aaa' AS VARCHAR(3)) AS PooledIndicator
      ,CAST('aaa' AS VARCHAR(3)) AS ExempStatus
      ,CAST('aaa' AS VARCHAR(10)) AS PositionFulltimeParttime
      ,CAST('aaa' AS VARCHAR(28)) AS PrimarySalaryAccountNumber
      ,CAST('aaa' AS INT) AS CountOfActiveAssignments
      ,CAST('aaa' AS INT) AS CountOfInactiveAssignments
      ,CAST('aaa' AS VARCHAR(8)) AS InactiveReasonCode
      ,CAST('aaa' AS VARCHAR(60)) AS InactiveReason
      ,CAST('aaa' AS VARCHAR(30)) InactiveVacantDateRange
      ,CAST('0' AS NUMERIC(5,2)) AS FulltimeEquivalent
      ,CAST('0' AS NUMERIC(5,2))  as WeeklyAuthorizedHours
      ,CAST('aaa' AS VARCHAR(3)) AS MultipleFunds
     END

DECLARE @CurrentDay date
DECLARE @FiscalYear VARCHAR(4)
SET @CurrentDay = GETDATE()
SET @FiscalYear =
(
  CASE
    WHEN MONTH(@CurrentDay) <= 06 THEN YEAR(@CurrentDay)
    ELSE Year(DATEADD(yy, 1,@CurrentDay))
  END
)

-- Create Temporary tables to calculate Number of Active Assignments and Inactive Assignments
CREATE TABLE #TempActiveAssignments
(
     PositionCode varchar(8) NOT NULL
    ,NumberOfActiveAssignments int NOT NULL
  )
CREATE TABLE #TempInActiveAssignments
(
     PositionCode varchar(8) NOT NULL
    ,NumberOfInActiveAssignments int NOT NULL
    ,MaxEndDate date NULL
    ,InactiveVacantDateRange VARCHAR(30)
  )

-- Find Inactive Assignments, where the Start Date is after today, or the Assignments have an End Date that is prior to today's date
INSERT INTO #TempInActiveAssignments
(
  PositionCode
,NumberOfInActiveAssignments
)
SELECT POSITION_CODE as PositionCode
       ,COUNT(*) as NumberOfInActiveAssignments
  FROM ASSIGNMENT_A Assign
    WHERE ((CAST(Assign.START_DATE as DateTime)) > @CurrentDay
    OR (CAST(ISNULL(Assign.END_DATE,'20981231') as DateTime)) < @CurrentDay AND Assign.END_DATE <> '00000000' AND Assign.END_DATE <> ' ')
   GROUP BY POSITION_CODE
-- Find out how long the assignments with the latest end date has been inactive
UPDATE #TempInActiveAssignments
SET MaxEndDate = Pos.MaxEndDate
    ,InactiveVacantDateRange =
      (CASE
               WHEN ISDATE(Pos.MaxEndDate) = 0 THEN 'N/A'
               WHEN Pos.MaxEndDate IS NOT NULL AND (DATEDIFF(MM, CAST(Pos.MaxEndDate AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(Pos.MaxEndDate AS DATETIME), @CurrentDay) <= 3)) THEN '01 to 3 Months'
               WHEN Pos.MaxEndDate IS NOT NULL AND (DATEDIFF(MM, CAST(Pos.MaxEndDate AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(Pos.MaxEndDate AS DATETIME), @CurrentDay) <= 6)) THEN '04 to 6 Months'
               WHEN Pos.MaxEndDate IS NOT NULL AND (DATEDIFF(MM, CAST(Pos.MaxEndDate AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(Pos.MaxEndDate AS DATETIME), @CurrentDay) <= 12)) THEN '07 Months to 1 Year'
               WHEN Pos.MaxEndDate IS NOT NULL AND (DATEDIFF(MM, CAST(Pos.MaxEndDate AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(Pos.MaxEndDate AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN Pos.MaxEndDate IS NOT NULL AND (DATEDIFF(MM, CAST(Pos.MaxEndDate AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
              END
            )
FROM
(SELECT POSITION_CODE as PositionCode
       ,MAX(Assign.END_DATE) AS MaxEndDate
  FROM ASSIGNMENT_A Assign
    WHERE ((CAST(Assign.START_DATE as DateTime)) > @CurrentDay
    OR (CAST(ISNULL(Assign.END_DATE,'20981231') as DateTime)) < @CurrentDay AND Assign.END_DATE <> '00000000' AND Assign.END_DATE <> ' ')
   GROUP BY POSITION_CODE) Pos
   WHERE Pos.PositionCode = #TempInActiveAssignments.PositionCode

-- Get all the Active Assigments
INSERT INTO #TempActiveAssignments
(
  PositionCode
,NumberOfActiveAssignments
)
SELECT POSITION_CODE as PositionCode
      ,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 POSITION_CODE
SELECT Pos.POSITION_CODE as PositionAK
      ,Pos.POSITION_CODE as PositionCode
      ,ISNULL(JobClass.JOB_TITLE, 'N/A') as PositionTitle
      ,CAST(
        CASE
          WHEN Pos.POSITION_STATUS = 'I' THEN 'Inactive'
          WHEN ISNULL(TempActive.NumberOfActiveAssignments,0) > 0 THEN 'Filled'
          ELSE 'Vacant'
         END AS VARCHAR(10)) AS PositionStatus
      ,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 ISDATE(POSITION_ESTABLISH_DATE) = 1 THEN POSITION_ESTABLISH_DATE
           WHEN ISDATE(POSITION_ESTABLISH_DATE) = 0 THEN '19000101'
         END AS DATETIME) AS DateEstablished
      ,CAST(CASE
           WHEN POSITION_INACTIVITY_DATE = '00000000' THEN '20981231'
           WHEN POSITION_INACTIVITY_DATE IS NULL THEN '20981231'
           WHEN ISDATE(POSITION_INACTIVITY_DATE) = 1 THEN POSITION_INACTIVITY_DATE
           WHEN ISDATE(POSITION_INACTIVITY_DATE) = 0 THEN '20981231'
         END AS DATETIME)  AS DateInactivated
      ,ISNULL(Pos.JOB_GROUP_CODE,'N/A') AS JobGroupCode
      ,ISNULL(JobGrp.DESCRIPTION,'N/A') AS JobGroup
      ,ISNULL(Pos.JOB_CLASS_CODE,'N/A') AS JobClassCode
      ,ISNULL(Pos.PAY_BASIS_CODE,'N/A') AS PayBasis
      ,CAST(
         CASE
           WHEN PosBudget.ORIGINAL_BUDGET_AMT > 0 OR PosBudget.REVISED_BUDGET_AMT > 0 THEN 'Yes'
           ELSE 'No'
         END AS Varchar(3)) AS Budgeted
      ,CAST(
         CASE
            WHEN REG_TEMP_IND = 'R' THEN 'Regular'
            ELSE 'Temporary'
         END AS VARCHAR(10)) AS RegularTemporary
      ,CAST(
         CASE
            WHEN Pos.POOLED_POSITION_IND = '1' THEN 'Yes'
            ELSE 'No'
         END AS VARCHAR(3)) AS PooledIndicator
       ,CAST(
          CASE
            WHEN Pos.EXEMPT_NON_EXEMPT_CODE = 'E' THEN 'Yes'
            ELSE 'No'
          END AS VARCHAR(3)) AS ExempStatus
       ,CAST(
          CASE
            WHEN Pos.FT_PT_STATUS_CODE = 'P' THEN 'Part Time'
            ELSE 'Full Time'
          END AS VARCHAR(10)) AS PositionFulltimeParttime
       ,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
       ,ISNULL(TempActive.NumberOfActiveAssignments,0) AS CountOfActiveAssignments
       ,ISNULL(TempInActive.NumberOfInActiveAssignments,0) AS CountOfInactiveAssignments
       ,ISNULL(Pos.INACTIVITY_CODE,'N/A') AS InactiveReasonCode
       ,ISNULL(InActivity.LONG_DESCRIPTION,'N/A') AS InactiveReason
       ,CAST(
          CASE
                /* Position is Inactive */
            WHEN Pos.POSITION_STATUS = 'I' THEN
             CASE
               WHEN Pos.POSITION_INACTIVITY_DATE IS NULL AND TempInActive.InactiveVacantDateRange IS NULL AND Pos.POSITION_STATUS_CHANGE_DATE IS NULL THEN 'N/A'
               WHEN Pos.POSITION_INACTIVITY_DATE = '00000000' AND TempInActive.InactiveVacantDateRange IS NULL AND Pos.POSITION_STATUS_CHANGE_DATE IS NULL THEN 'N/A'
               WHEN ISDATE(Pos.POSITION_INACTIVITY_DATE) = 0 AND TempInActive.InactiveVacantDateRange IS NULL AND Pos.POSITION_STATUS_CHANGE_DATE IS NULL THEN 'N/A'
               WHEN ISDATE(Pos.POSITION_INACTIVITY_DATE) = 0 AND TempInActive.InactiveVacantDateRange IS NOT NULL THEN TempInActive.InactiveVacantDateRange
                 /* Calculate since when Inactive */
               WHEN ISDATE(Pos.POSITION_INACTIVITY_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_INACTIVITY_DATE AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(Pos.POSITION_INACTIVITY_DATE AS DATETIME), @CurrentDay) <= 3)) THEN '01 to 3 Months'
               WHEN ISDATE(Pos.POSITION_INACTIVITY_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_INACTIVITY_DATE AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(Pos.POSITION_INACTIVITY_DATE AS DATETIME), @CurrentDay) <= 6)) THEN '04 to 6 Months'
               WHEN ISDATE(Pos.POSITION_INACTIVITY_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_INACTIVITY_DATE AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(Pos.POSITION_INACTIVITY_DATE AS DATETIME), @CurrentDay) <= 12)) THEN '07 Months to 1 Year'
               WHEN ISDATE(Pos.POSITION_INACTIVITY_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_INACTIVITY_DATE AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(Pos.POSITION_INACTIVITY_DATE AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN ISDATE(Pos.POSITION_INACTIVITY_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_INACTIVITY_DATE AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
                 /* The Inactive Date is not valid, and there has never been an Assignment, use the Status Change Date */
               WHEN ISDATE(Pos.POSITION_STATUS_CHANGE_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) <= 3)) THEN '01 to 3 Months'
               WHEN ISDATE(Pos.POSITION_STATUS_CHANGE_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) <= 6)) THEN '04 to 6 Months'
               WHEN ISDATE(Pos.POSITION_STATUS_CHANGE_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) <= 12)) THEN '07 Months to 1 Year'
               WHEN ISDATE(Pos.POSITION_STATUS_CHANGE_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN ISDATE(Pos.POSITION_STATUS_CHANGE_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A'
             END
                /* There are active assignments, position is not vacant *//
            WHEN ISNULL(TempActive.NumberOfActiveAssignments,0) > 0 THEN 'N/A'
              /* There has never been an assignment and the status has not changed, look at Established Date */
            WHEN TempInActive.InactiveVacantDateRange IS NULL AND Pos.POSITION_STATUS_CHANGE_DATE IS NULL AND Pos.POSITION_ESTABLISH_DATE IS NOT NULL THEN
              CASE
               WHEN ISDATE(Pos.POSITION_ESTABLISH_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_ESTABLISH_DATE AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(Pos.POSITION_ESTABLISH_DATE AS DATETIME), @CurrentDay) <= 3)) THEN '01 to 3 Months'
               WHEN ISDATE(Pos.POSITION_ESTABLISH_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_ESTABLISH_DATE AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(Pos.POSITION_ESTABLISH_DATE AS DATETIME), @CurrentDay) <= 6)) THEN '04 to 6 Months'
               WHEN ISDATE(Pos.POSITION_ESTABLISH_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_ESTABLISH_DATE AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(Pos.POSITION_ESTABLISH_DATE AS DATETIME), @CurrentDay) <= 12)) THEN '07 Months to 1 Year'
               WHEN ISDATE(Pos.POSITION_ESTABLISH_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_ESTABLISH_DATE AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(Pos.POSITION_ESTABLISH_DATE AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN ISDATE(Pos.POSITION_ESTABLISH_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_ESTABLISH_DATE AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A'
              END
            WHEN TempInActive.InactiveVacantDateRange IS NULL AND Pos.POSITION_STATUS_CHANGE_DATE IS NULL AND Pos.POSITION_ESTABLISH_DATE IS NULL THEN 'N/A'
              /* There has never been an assignment and the status has changed, look at Status Change Date */
            WHEN TempInActive.InactiveVacantDateRange IS NULL AND Pos.POSITION_STATUS_CHANGE_DATE IS NOT NULL THEN
              CASE
               WHEN ISDATE(Pos.POSITION_STATUS_CHANGE_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) > 0 and (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) <= 3)) THEN '01 to 3 Months'
               WHEN ISDATE(Pos.POSITION_STATUS_CHANGE_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) > 3 and (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) <= 6)) THEN '04 to 6 Months'
               WHEN ISDATE(Pos.POSITION_STATUS_CHANGE_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) > 6 and (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) <= 12)) THEN '07 Months to 1 Year'
               WHEN ISDATE(Pos.POSITION_STATUS_CHANGE_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) > 12 and (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) <= 24)) THEN '1 to 2 Years'
               WHEN ISDATE(Pos.POSITION_STATUS_CHANGE_DATE) = 1 AND (DATEDIFF(MM, CAST(Pos.POSITION_STATUS_CHANGE_DATE AS DATETIME), @CurrentDay) >  24) THEN 'More than 2 Years'
               ELSE 'N/A'
              END
            ELSE  /* The position is vacant, use the latest End Date of the Assignments */
              TempInActive.InactiveVacantDateRange
          END AS Varchar(20)) AS InactiveVacantDateRange
      ,ISNULL(Pos.FULLTIME_EQUIVALENT,0) AS FulltimeEquivalent
      ,ISNULL(Pos.WEEKLY_HOURS_AUTHORIZED,0) as WeeklyAuthorizedHours
         /* When the Smallest Fund Code (pos 1 of account) is not the same as the Largest Fund Code for the position, it is funded from
multiple funds */
      ,CAST(
         CASE
           WHEN PosBudFundMin.ACCOUNT_CODE IS NULL OR PosBudFundMax.ACCOUNT_CODE IS NULL THEN 'No'
           WHEN LEFT(PosBudFundMin.ACCOUNT_CODE,1) <> LEFT(PosBudFundMax.ACCOUNT_CODE,1) THEN 'Yes'
           ELSE 'No'
           END AS VARCHAR(3)) AS MultipleFunds
  FROM POSITION_A Pos
  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
  )
  LEFT OUTER JOIN #TempActiveAssignments TempActive
  ON
  (
     TempActive.PositionCode = Pos.POSITION_CODE
  )
  LEFT OUTER JOIN #TempInActiveAssignments TempInActive
  ON
  (
     TempInActive.PositionCode = Pos.POSITION_CODE
  )

-- Get the Accounts with the End Date in the most recent Fiscal Year, Highest Percentage and lowest Account Number (i.e. Fund 1)
  OUTER APPLY
  (
    SELECT TOP 1
       ACCOUNT_POSITION_CODE
       ,ACCOUNT_CODE
   FROM POSITION_BUDGET_A PosBud
   WHERE
      PosBud.ACCOUNT_POSITION_CODE = Pos.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 = ' ') THEN @FiscalYear
           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 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 POSITION_BUDGET_A PosBudget
  ON
  (
     PosBudget.FISCAL_YEAR = @FiscalYear
     AND PosBudget.POSITION_CODE = Pos.POSITION_CODE
     AND PosBudget.FISCAL_YEAR IS NOT NULL
     AND PosBudget.POSITION_CODE IS NOT NULL
     AND (PosBudget.ORIGINAL_BUDGET_AMT IS NOT NULL OR PosBudget.REVISED_BUDGET_AMT IS NOT NULL)
  )

  LEFT OUTER JOIN UTL_CODE_TABLE InActivity
  ON
  (
     InActivity.TABLE_NAME = 'INACTIVITY'
     AND InActivity.CODE = Pos.INACTIVITY_CODE
     AND InActivity.STATUS = 'A'
   )
/* Get the accounts associated with the Position Budget for where the Account Start Date is for the closest Fiscal year
and the smallest Account Code, i.e. Fund 1 */

  OUTER APPLY
  (
    SELECT TOP 1
      ACCOUNT_CODE
   FROM POSITION_BUDGET_A PosBud
   WHERE
      PosBud.ACCOUNT_POSITION_CODE = Pos.POSITION_CODE
  AND (CAST(ISNULL(PosBud.ACCOUNT_CODE_START_DATE,'19000101') AS DateTime)) <= @CurrentDay
  AND ACCOUNT_POSITION_CODE IS NOT NULL
  ORDER BY
       CASE
           WHEN (PosBud.ACCOUNT_CODE_END_DATE IS NULL OR PosBud.ACCOUNT_CODE_END_DATE = ' ') THEN @FiscalYear
           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_CODE ASC

  ) PosBudFundMin
/* Get the accounts associated with the Position Budget for where the Account Start Date is for the closest Fiscal year
and the largest Account Code, i.e. Fund 7 */

  OUTER APPLY
  (
    SELECT TOP 1
      ACCOUNT_CODE
   FROM POSITION_BUDGET_A PosBud
   WHERE
      PosBud.ACCOUNT_POSITION_CODE = Pos.POSITION_CODE
  AND (CAST(ISNULL(PosBud.ACCOUNT_CODE_START_DATE,'19000101') AS DateTime)) <= @CurrentDay
  AND ACCOUNT_POSITION_CODE IS NOT NULL
  ORDER BY
       CASE
           WHEN (PosBud.ACCOUNT_CODE_END_DATE IS NULL OR PosBud.ACCOUNT_CODE_END_DATE = ' ') THEN @FiscalYear
           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_CODE DESC
  ) PosBudFundMax