select  MRG.ACCT "Account",
        ACCT_TYPE."TYPE" "Type",
        ACCT_TYPE.TITLE "Description",
        sum(MRG.ADJUSTED_BUDGET) "Adjusted Budget",
        sum(MRG.YTD) "Activity",
        sum(MRG.COMMITMENTS) "Commitments",
        sum(MRG.TRAVEL_PROJECTED_SPEND) "Travel Projected Spend",
        sum(MRG.AVAILABLE_BALANCE - MRG.TRAVEL_PROJECTED_SPEND) "Available Balance"
from
    (select SRC.GRNT_YR,
            SRC.GRNT,
            SRC.FUND,
            SRC.ORGN,
            SRC.ACCT,
            SRC.PROG,
            SRC.ACTV,
            sum(nvl(SRC.ADJUSTED_BUDGET,0)) ADJUSTED_BUDGET,
            sum(nvl(SRC.YTD,0)) YTD,
            sum(nvl(SRC.COMMITMENTS,0)) COMMITMENTS,
            sum(nvl(SRC.TRAVEL_PROJECTED_SPEND,0)) TRAVEL_PROJECTED_SPEND,
            sum(nvl(SRC.AVAILABLE_BALANCE,0)) AVAILABLE_BALANCE  
    from
        (select frrgrnl_grnt_yr GRNT_YR,
                frrgrnl_grnt_code GRNT,
                frrgrnl_fund_code FUND,
                frrgrnl_orgn_code ORGN,
                frrgrnl_acct_code ACCT,
                frrgrnl_prog_code PROG,
                decode(frrgrnl_ACTV_CODE,' ','',frrgrnl_actv_code) ACTV,
                sum(frrgrnl_14_adopt_bud + frrgrnl_14_bud_adjt) ADJUSTED_BUDGET,
                sum(frrgrnl_14_ytd_actv) YTD,
                sum(frrgrnl_14_encumb + frrgrnl_14_bud_rsrv) COMMITMENTS,
                null TRAVEL_PROJECTED_SPEND,
                sum(frrgrnl_14_adopt_bud + frrgrnl_14_bud_adjt)
               -sum(frrgrnl_14_ytd_actv + frrgrnl_14_encumb + frrgrnl_14_bud_rsrv) AVAILABLE_BALANCE
        from frrgrnl
            inner join
            (select max(frrgrnl_grnt_yr) GRNT_YR,
                    frrgrnl_grnt_code GRNT,
                    frrgrnl_fund_code FUND,
                    frrgrnl_orgn_code ORGN,
                    frrgrnl_acct_code ACCT,
                    frrgrnl_prog_code PROG,
                    frrgrnl_actv_code ACTV,
                    frrgrnl_locn_code LOCN
            from frrgrnl
            group by frrgrnl_grnt_code, frrgrnl_fund_code, frrgrnl_orgn_code, 
                     frrgrnl_acct_code, frrgrnl_prog_code, frrgrnl_actv_code,
                     frrgrnl_actv_code, frrgrnl_locn_code
            order by acct)YR
                on frrgrnl_grnt_yr = YR.GRNT_YR
                and frrgrnl_grnt_code = YR.GRNT
                and frrgrnl_fund_code = YR.FUND
                and frrgrnl_orgn_code = YR.ORGN
                and frrgrnl_acct_code = YR.ACCT
                and frrgrnl_prog_code = YR.PROG
                and frrgrnl_actv_code = YR.ACTV
                and frrgrnl_locn_code = YR.LOCN
        group by frrgrnl_grnt_code, frrgrnl_fund_code, frrgrnl_orgn_code, 
                 frrgrnl_acct_code, frrgrnl_prog_code, frrgrnl_actv_code,
                 frrgrnl_actv_code, frrgrnl_grnt_yr

        union

        select  to_char(sysdate, 'YY') GRNT_YR,
                FUND.GRNT,
                far2tps_fund FUND,
                far2tps_orgn ORGN,
                far2tps_acct ACCT,
                ftvorgn_prog_code_def PROG,
                far2tps_actv ACTV,
                null ADJUSTED_BUDGET,
                null YTD,
                null COMMITMENTS,
                round(sum(far2tps_amount), 2) TRAVEL_PROJECTED_SPEND,
                null AVAILABLE_BALANCE
        from far2tps
            inner join
            (select ftvfund_fund_code FUND,
                    ftvfund_grnt_code GRNT
            from ftvfund
            where ftvfund_nchg_date = '31-DEC-2099')FUND
                on far2tps_fund = FUND.FUND
            inner join
            (select frbgrnt_code GRNT
            from frbgrnt)GRNT
                on FUND.GRNT = GRNT.GRNT
            left join ftvorgn
                on far2tps_orgn = ftvorgn_orgn_code
                and ftvorgn_nchg_date = '31-DEC-2099'
        where (:TRIP_FROM_APPROVED_REQUEST is null or
                case
                    when far2tps_data_source = 'Travel Request' 
                        and far2tps_approval_status in ('Approved', 'Pending on-line Booking')
                        then 'Y'
                    when far2tps_data_source = 'Expense' then 'Y'
                    else 'N'
                end = :TRIP_FROM_APPROVED_REQUEST) /*Enter Y to limit output to when Request is Approved, N to limit to Request not Approved, or leave blank for all*/
        group by to_char(sysdate, 'YY'), FUND.GRNT, far2tps_fund, far2tps_orgn, 
                 far2tps_acct, ftvorgn_prog_code_def, far2tps_actv)SRC
    group by SRC.GRNT_YR, SRC.GRNT, SRC.FUND, SRC.ORGN, SRC.ACCT, SRC.PROG, SRC.ACTV
    order by SRC.ACCT)MRG
inner join 
        (select ftvacct_acct_code ACCT,
                ftvacct_title TITLE,
                ATYP.ATYP_CODE,
                ATYP.INTERNAL_ATYP_CODE IATYP_CODE,
                case
                    when ATYP.INTERNAL_ATYP_CODE = 50 then 'R'
                    when ATYP.INTERNAL_ATYP_CODE = 60 then 'L'
                    when ATYP.INTERNAL_ATYP_CODE = 70 then 'E'
                    when ATYP.INTERNAL_ATYP_CODE = 80 then 'T'
                    else null
                end "TYPE"
         from ftvacct
            left join
            (select ftvatyp_atyp_code ATYP_CODE,
                    ftvatyp_title ATYP_TITLE,
                    ftvatyp_internal_atyp_code INTERNAL_ATYP_CODE,
                    IATYP.INTERNAL_ATYP_TITLE
            from ftvatyp
                inner join
                    (select ftvatyp_atyp_code ATYP_CODE,
                            ftvatyp_title INTERNAL_ATYP_TITLE
                     from ftvatyp
                     where ftvatyp_nchg_date = '31-DEC-2099'
                     and ftvatyp_atyp_code = ftvatyp_internal_atyp_code)IATYP
                on ftvatyp_internal_atyp_code = IATYP.ATYP_CODE
            where ftvatyp_nchg_date = '31-DEC-2099')ATYP
                on ftvacct_atyp_code = ATYP.ATYP_CODE
        where ftvacct_nchg_date = '31-DEC-2099')ACCT_TYPE
        on MRG.ACCT = ACCT_TYPE.ACCT
    inner join
    (select ftvfund_fund_code FUND,
            ftvfund_ftyp_code FTYP_CODE,
            IFTP.IFTP_CODE
    from ftvfund
        left join
        (select ftvftyp_ftyp_code FTYP_CODE,
                ftvftyp_internal_ftyp_code IFTP_CODE
        from ftvftyp
        where ftvftyp_nchg_date = '31-DEC-2099')IFTP
            on ftvfund_ftyp_code = IFTP.FTYP_CODE
    where ftvfund_nchg_date = '31-DEC-2099')FUND_TYPE
        on MRG.FUND = FUND_TYPE.FUND
    left join ftvorgn_levels
        on MRG.ORGN = orgn_code
where (:GRNT is null or MRG.GRNT like :GRNT) /*Ener the Grant or leave blank for all*/
and (:FUND is null or MRG.FUND like :FUND) /*Enter the Fund or leave blank for all*/
and (:ORG_LEVEL is null or level1 like :ORG_LEVEL /*Enter any Org level or leave blank for all*/
    or level2 like :ORG_LEVEL
    or level3 like :ORG_LEVEL
    or level4 like :ORG_LEVEL
    or level6 like :ORG_LEVEL
    or level5 like :ORG_LEVEL
    or level7 like :ORG_LEVEL
    or level8 like :ORG_LEVEL)
and (:ACCT is null or MRG.ACCT like :ACCT) /*Enter the Account code or leave blank for all*/
and (:PROG is null or MRG.PROG like :PROG) /*Enter the Program code or leave blank for all*/
and (:ACTV is null or MRG.ACTV like :ACTV) /*Enter the Activity code or leave blank for all*/
and (:ACCT_TYPE is null or ACCT_TYPE.ATYP_CODE like :ACCT_TYPE /*Enter an Account type or leave blank for all*/
 or ACCT_TYPE.IATYP_CODE like :ACCT_TYPE)
and (:FUND_TYPE is null or FUND_TYPE.FTYP_CODE like :FUND_TYPE /*Enter a Fund type or leave blank for all*/
 or  FUND_TYPE.IFTP_CODE like :FUND_TYPE)
having (:PROJECTED_SPEND_NOT_0 is null or 
        case when sum(MRG.TRAVEL_PROJECTED_SPEND) <> 0 
             then 'Y' else null end =:PROJECTED_SPEND_NOT_0) /*Enter Y to limit data to when there is a Projected Spend amount or leave blank for all*/
group by MRG.ACCT, ACCT_TYPE.TITLE, ACCT_TYPE."TYPE"
order by MRG.ACCT