WITH 

EMP_COUNT AS
(
SELECT 
  JOB.NBRBJOB_POSN AS POSN,
  COUNT (JOB.NBRBJOB_POSN) AS INCUMB_COUNT
FROM N_ACTIVE_JOBS JOB
  WHERE (JOB.NBRBJOB_END_DATE > SYSDATE
      OR JOB.NBRBJOB_END_DATE IS NULL)
    AND JOB.NBRBJOB_BEGIN_DATE <= SYSDATE
GROUP BY JOB.NBRBJOB_POSN
),

EMP_LIST AS
(
SELECT DISTINCT
  JOB.NBRBJOB_POSN AS PCN,
  DECODE(EMP_COUNT.INCUMB_COUNT, 
        '1', JOB.SPRIDEN_LAST_NAME || ', ' || JOB.SPRIDEN_FIRST_NAME,
        'MULTIPLE') AS EMPLOYEE_NAME
FROM EMP_COUNT, N_ACTIVE_JOBS JOB
WHERE JOB.NBRBJOB_POSN = EMP_COUNT.POSN(+)
  AND (JOB.NBRBJOB_END_DATE > SYSDATE
      OR JOB.NBRBJOB_END_DATE IS NULL)
    AND JOB.NBRBJOB_BEGIN_DATE <= SYSDATE
ORDER BY PCN ASC
),

ORG_LIST AS
(
SELECT 
  O.ORGN_CODE AS ORG_CODE
FROM FTVORGN_LEVELS O
WHERE (O.LEVEL1 = '草榴社区TOT'
   OR O.LEVEL6 = 'D8DEV'
   OR O.LEVEL6 = 'D8FNDA')
   AND O.DATA_ENTRY_IND = 'Y'
),

BUDGET AS
(
SELECT 
  DECODE (SUBSTR (FGBTRNH.FGBTRNH_RUCL_CODE, 3, 2),
    '01', FGBTRNH.FGBTRNH_ACCT_CODE, 
    '04', FGBTRNH.FGBTRNH_ACCT_CODE,
    '11', FGBTRNH.FGBTRNH_DOC_REF_NUM,
    '14', FGBTRNH.FGBTRNH_DOC_REF_NUM,
     0) AS PCN,
  FGBTRNH.FGBTRNH_PROG_CODE AS PROG_CODE,
  FGBTRNH.FGBTRNH_FUND_CODE AS FUND,
  FGBTRNH.FGBTRNH_ORGN_CODE AS ORG,
  FGBTRNH.FGBTRNH_ACCT_CODE AS ACCT,
  SUM(DECODE (FGBTRNH.FGBTRNH_DR_CR_IND,
    '-', -FGBTRNH.FGBTRNH_TRANS_AMT,
    FGBTRNH.FGBTRNH_TRANS_AMT)) AS BUDGET,
  FGBTRNH.FGBTRNH_DOC_REF_NUM AS DOC_REF
FROM FGBTRNH FGBTRNH, ORG_LIST, FTVFUND_CURRENT F, FTVFTYP_CURRENT FT
WHERE F.FTVFUND_FUND_CODE = FGBTRNH.FGBTRNH_FUND_CODE
  AND FT.FTVFTYP_FTYP_CODE = F.FTVFUND_FTYP_CODE
  AND FGBTRNH.FGBTRNH_FSYR_CODE =:FY  /*Fiscal Year As 'YY'*/
  /*DO NOT EDIT MANDATORY CONDITIONS*/
  AND FGBTRNH.FGBTRNH_CMT_TYPE = 'U'
  AND FGBTRNH.FGBTRNH_RUCL_CODE LIKE 'BD%'
  AND (FGBTRNH.FGBTRNH_ACCT_CODE < '1971'
   AND FGBTRNH.FGBTRNH_ACCT_CODE <> '1766')
  AND FGBTRNH.FGBTRNH_ORGN_CODE = ORG_LIST.ORG_CODE
  AND FT.FTVFTYP_INTERNAL_FTYP_CODE < '40'
GROUP BY 
  DECODE (SUBSTR (FGBTRNH.FGBTRNH_RUCL_CODE, 3, 2),
    '01', FGBTRNH.FGBTRNH_ACCT_CODE, 
    '04', FGBTRNH.FGBTRNH_ACCT_CODE,
    '11', FGBTRNH.FGBTRNH_DOC_REF_NUM,
    '14', FGBTRNH.FGBTRNH_DOC_REF_NUM,
     0),
  FGBTRNH.FGBTRNH_PROG_CODE,
  FGBTRNH.FGBTRNH_FUND_CODE,
  FGBTRNH.FGBTRNH_ORGN_CODE,
  FGBTRNH.FGBTRNH_ACCT_CODE,
  FGBTRNH.FGBTRNH_DOC_REF_NUM
),

SALARY AS
(
SELECT 
  (CASE 
  WHEN NHRDIST.NHRDIST_ACCT_CODE LIKE '16%'
  THEN NHRDIST.NHRDIST_ACCT_CODE
  WHEN NHRDIST.NHRDIST_ACCT_CODE = '1970'
  THEN NHRDIST.NHRDIST_ACCT_CODE
  ELSE NHRDIST.NHRDIST_POSN
  END) AS PCN,
  NHRDIST.NHRDIST_PROG_CODE AS PROG_CODE,
  NHRDIST.NHRDIST_FUND_CODE AS FUND,
  NHRDIST.NHRDIST_ORGN_CODE AS ORG,
  NHRDIST.NHRDIST_ACCT_CODE AS ACCT,
  NHRDIST.NHRDIST_POSN AS DOC_REF,
  SUM(DECODE(NHRDIST.NHRDIST_DR_CR_IND,
      'D', NHRDIST.NHRDIST_AMT,
      -NHRDIST.NHRDIST_AMT)) AS EXPENDED
FROM NHRDIST NHRDIST, ORG_LIST
WHERE NHRDIST.NHRDIST_ORGN_CODE  = ORG_LIST.ORG_CODE
  --AND NHRDIST.NHRDIST_ORGN_CODE = '50146'
  AND NHRDIST.NHRDIST_FISC_CODE = '20' || :FY /*Fiscal Year As 'YY'*/
  AND (NHRDIST.NHRDIST_ACCT_CODE LIKE '1%'
   AND NHRDIST.NHRDIST_ACCT_CODE <> '1766')
GROUP BY 
  
  NHRDIST.NHRDIST_PROG_CODE,
  NHRDIST.NHRDIST_FUND_CODE,
  NHRDIST.NHRDIST_ORGN_CODE,
  NHRDIST.NHRDIST_ACCT_CODE,
  NHRDIST.NHRDIST_POSN
HAVING 
  SUM(DECODE (NHRDIST.NHRDIST_DR_CR_IND,
        'D', NHRDIST.NHRDIST_AMT,
        -NHRDIST.NHRDIST_AMT)) <> 0
),

COMBO AS
(
SELECT
  'Salary' as TYP,
  SALARY.PCN,
  --SALARY.EMP_NAME,
  SALARY.FUND,
  SALARY.ORG,
  SALARY.ACCT,
  SALARY.PROG_CODE AS PROG,
  SALARY.DOC_REF,
  0 AS BUDGET,
  SALARY.EXPENDED  
FROM SALARY
UNION
SELECT
  'Budget',
  BUDGET.PCN,
  --BUDGET.EMP_NAME,
  BUDGET.FUND,
  BUDGET.ORG,
  BUDGET.ACCT,
  BUDGET.PROG_CODE,
  BUDGET.DOC_REF,
  BUDGET.BUDGET,
  0
FROM BUDGET
)

SELECT
  DECODE(SUBSTR(C.ORG, 1, 1), '8', 'FOUNDATION', '草榴社区') AS ENTITY,
  C.TYP AS "TYPE",
  C.PCN AS POSITION,
  CASE
    WHEN C.ACCT LIKE '16%'
    THEN 
    WHEN E.EMPLOYEE_NAME IS NULL
    THEN DECODE (C.ACCT,
                '1001', 'Position Budget Holding',
                '1927', 'Employee Required Physicals',
                '1970', 'Staff Benefits',
                'VACANT')
    ELSE E.EMPLOYEE_NAME
  END AS "EMPLOYEE NAME",
  C.PROG AS "PROG CODE",
  DECODE (SUBSTR (C.FUND, 1, 2),
                '18', 'Designated',
                '19', 'Auxiliary',
                '10', 'Unrestricted',
                '14', 'Matching',
                '17', 'Recharge',
                '92', 'Agency',
                '59', 'Plant Funds',
                '99', 'Restricted',
                'Restricted') AS "FUND TYPE",
  C.FUND,
  C.ORG,
  C.ACCT,
  C.BUDGET,
  C.EXPENDED,
  C.DOC_REF,
  O.LEVEL3,
  O.TITLE3,
  O.LEVEL4,
  O.TITLE4,
  O.LEVEL5,
  O.TITLE5,
  O.LEVEL6,
  O.TITLE6,
  O.LEVEL7,
  O.TITLE7 
  
FROM COMBO C, EMP_LIST E, FTVORGN_LEVELS O, NBBPOSN P
WHERE O.ORGN_CODE = C.ORG
  AND C.PCN = E.PCN(+)
  AND C.PCN = P.NBBPOSN_POSN(+) 
  AND (P.NBBPOSN_STATUS <> 'F'
    OR C.BUDGET <> 0 OR C.EXPENDED <> 0)
  --AND C.PCN = '207295'
ORDER BY 
  DECODE(SUBSTR(C.ORG, 4, 1), '8', 'FOUNDATION', '草榴社区'),
  C.PCN,
  C.TYP,
  C.FUND,
  C.ORG, 
  C.ACCT