DARTS : UM_F_POS_BUD_VW - view of Posi & Budget extract

WITH INCUMBENT_VW AS
 (SELECT 
   ARC.POSITION_NBR,
   ARC.EMPLID,
   ARC.EMPL_RCD,
   ARC.ACTION_REASON,
   ARC.ANNL_BENEF_BASE_RT,
   ARC.ANNUAL_RT,
   ARC.DEPT_DESCR,
   ARC.EG_ACADEMIC_RANK,
   ARC.UM_EG_RANK_DESCR,
   ARC.TENURE_STATUS,
   ARC.UM_TENURE_DESCR,
   ARC.EMPL_CLASS,
   ARC.UM_EMPL_CLASS_DESC,
   ARC.EMPL_STATUS,
   ARC.UM_EMP_STATUS_DESC,
   ARC.UM_FTE_TOTAL,
   ARC.FTE,
   ARC.FULL_PART_TIME,
   ARC.HOURLY_RT,
   ARC.IPEDSSCODE,
   ARC.UM_IPEDSCODE_DESCR,
   ARC.MONTHLY_RT,
   ARC.REG_TEMP,
   ARC.UM_SALBASE,
   ARC.UM_SUPV_LVL_ID,
   ARC.UM_SUP_LVL_ID_DESC,
   ARC.UNION_CD,
   ARC.UM_UNION_DESCR,
   ARC.JOBCODE,
   ARC.UM_PRIM_FUNCTION,
   ARC.SAL_ADMIN_PLAN,
   COUNT(ARC.EMPLID) OVER(PARTITION BY POSITION_NBR, B.FISCAL_YEAR) AS NUMBER_OF_EMPL,
   MAX(ARC.EMPLID) OVER(PARTITION BY POSITION_NBR, B.FISCAL_YEAR) AS MAX_EMPLID,
   B.FISCAL_YEAR
    FROM PS_UM_EMPLOYE_ARC ARC, PS_UM_POS_BUD_FY_V B
   WHERE ARC.EMPL_STATUS IN ('A', 'L', 'P', 'S', 'W')
     AND B.END_DT <= SYSDATE
     AND ARC.ARCHIVE_DT =
         (SELECT MAX(A2.ARCHIVE_DT)
            FROM PS_UM_EMPLOYE_ARC A2
           WHERE A2.EMPLID = ARC.EMPLID
             AND A2.EMPL_RCD = ARC.EMPL_RCD
             AND A2.ARCHIVE_DT <= B.END_DT
             AND A2.ARCHIVE_DT >= B.BEGIN_DT)
     AND NOT EXISTS
   (SELECT /*+
           UNNEST */
           'x'
            FROM SYSADM.PS_UM_EMPLOYE_ARC ARC2
           WHERE ARC2.EMPLID = ARC.EMPLID
             AND ARC2.POSITION_NBR = ARC.POSITION_NBR
             AND ARC2.EMPL_RCD > ARC.EMPL_RCD
             AND ARC2.ARCHIVE_DT = ARC.ARCHIVE_DT
             AND ARC2.EMPL_STATUS IN ('A', 'L', 'P', 'S', 'W'))
  UNION
  SELECT CUR.POSITION_NBR,
         CUR.EMPLID,
         CUR.EMPL_RCD,
         CUR.ACTION_REASON,
         CUR.ANNL_BENEF_BASE_RT,
         CUR.ANNUAL_RT,
         CUR.DEPT_DESCR,
         CUR.EG_ACADEMIC_RANK,
         CUR.UM_EG_RANK_DESCR,
         CUR.TENURE_STATUS,
         CUR.UM_TENURE_DESCR,
         CUR.EMPL_CLASS,
         CUR.UM_EMPL_CLASS_DESC,
         CUR.EMPL_STATUS,
         CUR.UM_EMP_STATUS_DESC,
         CUR.UM_FTE_TOTAL,
         CUR.FTE,
         CUR.FULL_PART_TIME,
         CUR.HOURLY_RT,
         CUR.IPEDSSCODE,
         CUR.UM_IPEDSCODE_DESCR,
         CUR.MONTHLY_RT,
         CUR.REG_TEMP,
         CUR.UM_SALBASE,
         CUR.UM_SUPV_LVL_ID,
         CUR.UM_SUP_LVL_ID_DESC,
         CUR.UNION_CD,
         CUR.UM_UNION_DESCR,
         CUR.JOBCODE,
         CUR.UM_PRIM_FUNCTION,
         CUR.SAL_ADMIN_PLAN,
         COUNT(CUR.EMPLID) OVER(PARTITION BY POSITION_NBR, B.FISCAL_YEAR) AS NUMBER_OF_EMPL,
         MAX(CUR.EMPLID) OVER(PARTITION BY POSITION_NBR, B.FISCAL_YEAR) AS MAX_EMPLID,
         B.FISCAL_YEAR
    FROM PS_UM_F_EMPLOYEE CUR, PS_UM_POS_BUD_FY_V B
   WHERE CUR.EMPL_STATUS IN ('A', 'L', 'P', 'S', 'W')
     AND SYSDATE <= B.END_DT
     AND NOT EXISTS
   (SELECT 'x'
            FROM PS_UM_F_EMPLOYEE CUR2
           WHERE CUR2.EMPLID = CUR.EMPLID
             AND CUR2.POSITION_NBR = CUR.POSITION_NBR
             AND CUR2.EMPL_RCD > CUR.EMPL_RCD
             AND CUR2.EMPL_STATUS IN ('A', 'L', 'P', 'S', 'W')))
SELECT /*+
 ORDERED */
 A.FISCAL_YEAR,
 A.BUSINESS_UNIT,
 A.POSITION_NBR,
 A.POSN_STATUS,
 A.MAX_HEAD_COUNT,
 A.FTE,
 A.UM_DEPTID_HR,
 A.CLASS_INDC,
 A.DESCR254,
 A.NAME,
 A.ACCT_CD,
 A.UM_ACCT_CD_FRINGE,
 A.ACCOUNT,
 A.UM_ACCT_DESCR,
 A.DEPTID,
 A.UM_DEPT_DESCR,
 A.PROJECT_ID,
 A.UM_PROJECT_DESCR,
 A.FUND_CODE,
 A.UM_FUND_DESCR,
 A.PROGRAM_CODE,
 A.UM_PROGRAM_DESCR,
 A.OPERATING_UNIT,
 A.UM_OPUNIT_DESCR,
 A.CLASS_FLD,
 A.UM_POS_BUD_BAS_AMT,
 A.UM_POS_BUD_FRB_AMT,
 A.UM_POS_BUD_PER_AMT,
 A.UM_POS_BUD_FRP_AMT,
 A.UM_POS_BUD_TEM_AMT,
 A.UM_POS_BUD_FRT_AMT,
 A.UM_NEW_INITIATIVE,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.EMPLID
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) > 1 THEN
    'MULTI'
   WHEN A.FISCAL_YEAR <= 2007 THEN
    'NO DATA'
   ELSE
    'VACANT'
 END,
 A.EFF_STATUS,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.EMPL_RCD
   ELSE
    0
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.ACTION_REASON
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.ANNL_BENEF_BASE_RT
   ELSE
    0
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.ANNUAL_RT
   ELSE
    0
 END,
 A.CONTRCT_EXP_END_DT,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.DEPT_DESCR
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.EG_ACADEMIC_RANK
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.UM_EG_RANK_DESCR
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.TENURE_STATUS
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.UM_TENURE_DESCR
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.EMPL_CLASS
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.UM_EMPL_CLASS_DESC
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.EMPL_STATUS
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.UM_EMP_STATUS_DESC
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.UM_FTE_TOTAL
   ELSE
    0
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.FTE
   ELSE
    0
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.FULL_PART_TIME
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.HOURLY_RT
   ELSE
    0
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.IPEDSSCODE
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.UM_IPEDSCODE_DESCR
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.MONTHLY_RT
   ELSE
    0
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.REG_TEMP
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.UM_SALBASE
   ELSE
    0
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.UM_SUPV_LVL_ID
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.UM_SUP_LVL_ID_DESC
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.UNION_CD
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.UM_UNION_DESCR
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.JOBCODE
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.UM_PRIM_FUNCTION
   ELSE
    ' '
 END,
 CASE
   WHEN NVL(Z.NUMBER_OF_EMPL, 0) = 1 THEN
    Z.SAL_ADMIN_PLAN
   ELSE
    ' '
 END,
 A.UM_COLLEGE,
 A.UM_POS_RM_BOARD,
 A.CONTRACT_TYPE,
 A.CONTRACT_BEGIN_DT,
 A.CONTRACT_END_DT,
 A.UM_CONTRCT_MAX
  FROM PS_UM_F_POS_MGMT_B A, INCUMBENT_VW Z
 WHERE A.POSITION_NBR = Z.POSITION_NBR(+)
   AND A.FISCAL_YEAR = Z.FISCAL_YEAR(+)
   AND (Z.EMPLID = Z.MAX_EMPLID OR Z.EMPLID IS NULL);