select  SRC.ACCT "Account",
        ACCT_TYPE."TYPE" "Type",
        ACCT_TYPE.TITLE "Title",
        sum(nvl(SRC.ADJUSTED_BUDGET,0)) "Adjusted Budget",
        sum(nvl(SRC.YTD,0)) "YTD Activity",
        sum(nvl(SRC.COMMITMENTS,0)) "Commitments",
        sum(nvl(SRC.TRAVEL_PROJECTED_SPEND,0)) "Travel Projected Spend",
        sum(nvl(SRC.AVAILABLE_BALANCE,0))-sum(nvl(SRC.TRAVEL_PROJECTED_SPEND,0))"Available Balance"  
from
    (select fgbopal_fsyr_code FY,
            fgbopal_cmt_type COMMIT_TYPE,
            fgbopal_fund_code FUND,
            fgbopal_orgn_code ORGN,
            fgbopal_acct_code ACCT,
            fgbopal_prog_code PROG,
            decode(FGBOPAL_ACTV_CODE,' ','',fgbopal_actv_code) ACTV,
            sum(fgbopal_14_adopt_bud + fgbopal_14_bud_adjt) ADJUSTED_BUDGET,
            sum(fgbopal_14_ytd_actv) YTD,
            sum(fgbopal_14_encumb + fgbopal_14_bud_rsrv) COMMITMENTS,
            null TRAVEL_PROJECTED_SPEND,
            sum(fgbopal_14_adopt_bud + fgbopal_14_bud_adjt)
           -sum(fgbopal_14_ytd_actv + fgbopal_14_encumb + fgbopal_14_bud_rsrv) AVAILABLE_BALANCE
    from fgbopal
    group by fgbopal_fsyr_code, fgbopal_cmt_type, fgbopal_fund_code, fgbopal_orgn_code, 
             fgbopal_acct_code, fgbopal_prog_code, fgbopal_actv_code, fgbopal_actv_code

    union

    select  FY.FY,
            'U' COMMIT_TYPE,
            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 distinct far2tps_start_date START_DATE,
                case 
                    when far2tps_start_date <=
                         (select max(ftvfspd_prd_end_date)
                          from ftvfspd
                          where ftvfspd_fsyr_code = (select min(ftvfspd_fsyr_code) FY
                                                     from ftvfspd
                                                     where ftvfspd_prd_status_ind = 'O'))
                         then (select min(ftvfspd_fsyr_code) FY
                               from ftvfspd
                               where ftvfspd_prd_status_ind = 'O')
                    when far2tps_start_date >
                         (select max(ftvfspd_prd_end_date)
                          from ftvfspd
                          where ftvfspd_fsyr_code = (select min(ftvfspd_fsyr_code) FY
                                                     from ftvfspd
                                                     where ftvfspd_prd_status_ind = 'O'))
                    and extract(month from far2tps_start_date) between 7 and 12
                        then substr(to_char(extract(year from far2tps_start_date) + 1),-2)
                    else to_char(far2tps_start_date, 'YY')
                 end FY
        from far2tps)FY
            on far2tps_start_date = FY.START_DATE
        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 FY.FY, far2tps_fund, far2tps_orgn, far2tps_acct, ftvorgn_prog_code_def,
             far2tps_actv)SRC
    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 SRC.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 SRC.FUND = FUND_TYPE.FUND
    left join ftvorgn_levels
        on SRC.ORGN = orgn_code
where SRC.FY = :FY /*Required - Enter the FY*/
and (:COMMIT_TYPE is null or SRC.COMMIT_TYPE like :COMMIT_TYPE) /*Enter the Commit Type or leave blank for all*/
and (:FUND is null or SRC.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 SRC.ACCT like :ACCT) /*Enter the Account code or leave blank for all*/
and (:PROG is null or SRC.PROG like :PROG) /*Enter the Program code or leave blank for all*/
and (:ACTV is null or SRC.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(SRC.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 SRC.ACCT, ACCT_TYPE.TITLE, ACCT_TYPE."TYPE" 
order by SRC.ACCT
/