WITH

POSN_INCUMBENT_COUNT AS
(SELECT NBBPOSN.NBBPOSN_POSN AS POSN, COUNT (N_POSITION_INCUMBENTS.POSN) AS INCUMB_COUNT
 FROM POSNCTL.NBBPOSN LEFT OUTER JOIN REPORTS.N_POSITION_INCUMBENTS ON (NBBPOSN.NBBPOSN_POSN = N_POSITION_INCUMBENTS.POSN)
 WHERE (NBBPOSN.NBBPOSN_STATUS = 'A' OR NBBPOSN.NBBPOSN_STATUS = 'F')
       AND (N_POSITION_INCUMBENTS.END_DATE >SYSDATE OR N_POSITION_INCUMBENTS.END_DATE IS NULL)
 GROUP BY NBBPOSN.NBBPOSN_POSN),

EMP_NAME_LIST AS
(SELECT DISTINCT NBBPOSN.NBBPOSN_POSN AS PCN,
      NBBPOSN.NBBPOSN_TITLE AS "PCN TITLE",
      DECODE (POSN_INCUMBENT_COUNT.INCUMB_COUNT, '0', 'VACANT', '1', N_POSITION_INCUMBENTS.NAME, 'MULTIPLE') AS EMPLOYEE_NAME
 FROM POSNCTL.NBBPOSN LEFT OUTER JOIN REPORTS.N_POSITION_INCUMBENTS ON (NBBPOSN.NBBPOSN_POSN = N_POSITION_INCUMBENTS.POSN)
      LEFT OUTER JOIN POSN_INCUMBENT_COUNT ON (NBBPOSN.NBBPOSN_POSN = POSN_INCUMBENT_COUNT.POSN)
 WHERE (NBBPOSN.NBBPOSN_STATUS = 'A' OR NBBPOSN.NBBPOSN_STATUS = 'F')
       AND (N_POSITION_INCUMBENTS.END_DATE >SYSDATE OR N_POSITION_INCUMBENTS.END_DATE IS NULL)
 ORDER BY PCN ASC)

-----------------------------MAIN QUERY BELOW THIS LINE-----------------------------


SELECT DISTINCT
  TO_NUMBER (NBBPOSN.NBBPOSN_POSN) AS PCN,
  CASE WHEN EMP_NAME_LIST.EMPLOYEE_NAME IS NULL THEN 'VACANT' ELSE EMP_NAME_LIST.EMPLOYEE_NAME END AS "EMPLOYEE NAME",
  NBBPOSN.NBBPOSN_BPRO_CODE AS BPRO,
  NBBPOSN.NBBPOSN_AUTH_NUMBER AS "AUTH #",
  NBBPOSN.NBBPOSN_STATUS AS "PCN STATUS",
  NBBPOSN.NBBPOSN_BEGIN_DATE AS "BEGIN DATE",
  NBBPOSN.NBBPOSN_END_DATE AS "END DATE",
  DECODE(NBBPOSN.NBBPOSN_BARG_CODE, 'AC', 'UNAC - Rep Faculty', 'AD','UNAD - Rep Adjuncts', 'AG','AGWA - Rep Grad Workers','CS','CAUSE - Rep Staff',
    'FF','IAFF - Rep Firefighters', 'L6','L6070 - Rep Crafts & Trades','NB','Non-Bargaining Unit / Non-Rep','Not Defined') AS BARGAIN_UNIT,
  NBBPOSN.NBBPOSN_ECLS_CODE AS "E-CLASS",
  NBBPOSN.NBBPOSN_TYPE AS "PCN TYPE",
  NBBPOSN.NBBPOSN_PCLS_CODE AS PCLS,
  NBBPOSN.NBBPOSN_TITLE AS "PCN TITLE",
  NBBPOSN.NBBPOSN_SGRP_CODE AS SGRP,
  NBBPOSN.NBBPOSN_TABLE AS "SALARY TABLE",
  NBBPOSN.NBBPOSN_GRADE AS GRADE,
  NBBPOSN.NBBPOSN_STEP AS STEP,
  NBRPTOT.NBRPTOT_FISC_CODE AS "FISCAL YEAR",
  NBRPTOT.NBRPTOT_BUDGET AS BUDGET,
  TO_NUMBER (NBRPTOT.NBRPTOT_ORGN_CODE) AS ORG,
  FTVORGN_LEVELS.TITLE3 AS UNIT,
  FTVORGN_LEVELS.TITLE6 AS DEPT,
  NBBPOSN.NBBPOSN_ACTIVITY_DATE AS "LAST ACTIVITY DATE"
FROM POSNCTL.NBBPOSN INNER JOIN POSNCTL.NBRPTOT ON (NBBPOSN.NBBPOSN_POSN = NBRPTOT.NBRPTOT_POSN)
     INNER JOIN REPORTS.FTVORGN_LEVELS ON (NBRPTOT.NBRPTOT_ORGN_CODE = FTVORGN_LEVELS.ORGN_CODE)
     LEFT OUTER JOIN EMP_NAME_LIST ON (NBBPOSN.NBBPOSN_POSN = EMP_NAME_LIST.PCN)
WHERE  NBBPOSN.NBBPOSN_STATUS = 'A'
       AND (FTVORGN_LEVELS.LEVEL1 Like :Org OR FTVORGN_LEVELS.LEVEL2 Like :Org OR FTVORGN_LEVELS.LEVEL3 Like :Org
        OR FTVORGN_LEVELS.LEVEL4 Like :Org OR FTVORGN_LEVELS.LEVEL5 Like :Org OR FTVORGN_LEVELS.LEVEL6 Like :Org
        OR FTVORGN_LEVELS.LEVEL7 Like :Org OR FTVORGN_LEVELS.LEVEL8 Like :Org) /*Enter % or Org code between levels 1 and 8*/
AND (NBRPTOT.NBRPTOT_FISC_CODE = :Fiscal_Year_YYYY)/*'YYYY' Fiscal Year*/
ORDER BY PCN ASC