with

IDEN as
(select spriden_pidm PIDM,
        spriden_id IDEN_ID,
        case 
            when spbpers_pref_first_name is null then spriden_first_name 
            else spbpers_pref_first_name
        end
            || ' ' || spriden_last_name PNAME
 from spriden
    left join spbpers
        on spriden_pidm = spbpers_pidm
 where spriden_change_ind is null)

---------------------------------------------------------------------------------------
select  FY.FY,
        'U' COMMIT_TYPE,
        far2tps_fund FUND,
        far2tps_orgn ORGN,
        far2tps_acct ACCT,
        ftvorgn_prog_code_def PROG,
        far2tps_actv ACTV,
        round(sum(far2tps_amount), 2) TRAVEL_PROJECTED_SPEND,
        far2tps_data_source DATA_SOURCE,
        case 
            when far2tps_data_source = 'Travel Request' then 'Request ID'
            when far2tps_data_source = 'Expense' then 'Report Number'
            else null
        end DOCUMENT_TYPE,
        case 
            when far2tps_data_source = 'Travel Request' then far2tps_request_id
            when far2tps_data_source = 'Expense' then far2tps_report_number
            else null
        end DOCUMENT,
        far2tps_trip_name TRIP_NAME,
        far2tps_approval_status APPROVAL_STATUS,
        replace(far2tps_trip_purpose, '.') TRIP_PURPOSE,
        far2tps_trip_type TRIP_TYPE,
        far2tps_traveler_type TRAVELER_TYPE,
        far2tps_start_date TRIP_START_DATE, 
        far2tps_end_date TRIP_END_DATE,
        far2tps_ua_id TRAVELER_UA_ID,
        far2tps_traveler_first_name || ' ' || far2tps_traveler_last_name TRAVELER_NAME
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'
    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 far2tps_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 far2tps_fund = FUND_TYPE.FUND
    inner join ftvfund
        on far2tps_fund = ftvfund_fund_code
        and ftvfund_nchg_date = '31-DEC-2099'
    left join frbgrnt
        on ftvfund_grnt_code = frbgrnt_code
    left join IDEN PI 
        on frbgrnt_pi_pidm = PI.PIDM
    left join IDEN R
        on ftvfund_researcher_pidm = R.PIDM
    left join IDEN FUND_FMGR
        on ftvfund_fmgr_code_pidm = FUND_FMGR.PIDM
    left join ftvorgn_levels
        on far2tps_orgn = orgn_code
where (:FY is null or FY.FY like :FY) /*Enter the FY or leave blank for all*/
and (:GRNT is null or frbgrnt_code like :GRNT) /*Ener the Grant or leave blank for all*/
and (:FUND is null or far2tps_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 far2tps_acct like :ACCT) /*Enter the Account code or leave blank for all*/
and (:PROG is null or ftvorgn_prog_code_def like :PROG) /*Enter the Program code or leave blank for all*/
and (:ACTV is null or far2tps_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)
and (:TRIP_END_BEFORE_DATE is null or far2tps_end_date <= :TRIP_END_BEFORE_DATE) /*Enter a date to limit output to trips that ended on or before that date as MM-DD-YYYY or leave blank for all*/
and (:PI_ID is null or PI.IDEN_ID = :PI_ID) /*Enter the UA ID number for the PI associated with the Grant or leave blank for all*/
and (:RESERCHER_ID is null or R.IDEN_ID = :RESERCHER_ID) /*Enter the UA ID number for the Reasearcher associated with the Fund or leave blank for all*/
and (:FUND_FINANCIAL_MANAGER_ID is null or FUND_FMGR.IDEN_ID like :FUND_FINANCIAL_MANAGER_ID) /*Enter the Finacial Manager ID for the Financial Manager associated with the Fund or leave blank for all*/
and (: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*/
and (:TRAVELER_UA_ID is null or far2tps_ua_id = :TRAVELER_UA_ID)
group by FY.FY, far2tps_fund, far2tps_orgn, far2tps_acct, ftvorgn_prog_code_def,
         far2tps_actv, far2tps_data_source,
         case 
            when far2tps_data_source = 'Travel Request' then 'Request ID'
            when far2tps_data_source = 'Expense' then 'Report Number'
            else null
         end,
         case 
            when far2tps_data_source = 'Travel Request' then far2tps_request_id
            when far2tps_data_source = 'Expense' then far2tps_report_number
            else null
         end, far2tps_trip_name, far2tps_approval_status, replace(far2tps_trip_purpose, '.'), 
         far2tps_trip_type, far2tps_traveler_type, far2tps_start_date, far2tps_end_date, 
         far2tps_ua_id, far2tps_traveler_first_name || ' ' || far2tps_traveler_last_name
order by fund, orgn, acct, prog, actv, data_source, document
/