/*
The output from this script lists all open encumbrance lines on a closed FOAPA 
value based on the term date entered. When you run this you will be prompted for
the TERM_DATE this is the term date for any FOAPAL value. This date controls the 
output, e.g. If 30-JUN-2022 is entered the output will be for any encumbrance 
lines on FOAPAL values that have a term date on or before 30-JUN-2022.

Please note the University of èßäÉçÇø¹ÙÍø does not use Location from FOAPAL, 
Location is not a part of this script. For this reason FOAPAL is without the L 
in the acronym for the purposes of this script: Fund-Orgn-Acct-Prog-Actv.

COMMENTS Column
The COMMENTS column will identify what FOAPAL value is termed and list the 
term date. If the fund expenditure end date is greater than the term date then 
the expenditure end date is listed. Additionally if there is a FOAPAL value
that is not data enterable or active it will also be noted in this column too.

SNGAJ 4/22/25
*/

select  ENCB_FY.FY,
        fgbencd_num ENCB_NUMBER,
        spriden_id VENDOR_ID,
        case 
            when spriden_first_name is null then spriden_last_name
            else spriden_last_name || ', ' || spriden_first_name
        end
            VENDOR_NAME,
        fgbench_status_ind ENCB_STATUS,
        fgbencd_status LINE_STATUS,
        fgbench_type ENCB_TYPE,
        fgbencd_cmt_type CMT_TYPE,
        ENCB_AMT.AMT ENCB_TOTAL,
        fgbencd_item ITEM,
        fgbencd_seq_num SEQ,
        fgbencd_fund_code FUND,
        fgbencd_orgn_code ORGN,
        fgbencd_acct_code ACCT,
        fgbencd_prog_code PROG,
        fgbencd_actv_code ACTV,
        AMT.AMT LINE_AMT,
        replace (level1, 'TOT') MAU,
        title2 CABINET,
        title3 UNIT,
        title4 DIVISION,
        title5 "CLUSTER",
        level6 DLEVEL,
        title6 DEPARTMENT,
        title7 PROGRAM,
        case
            when trunc(ftvfund_term_date) <= :TERM_DATE /*Use date format DD-MON-YYYY. The term date entered will limit output to open lines prior to this date*/ 
                and ftvfund_expend_end_date is null
                or trunc(ftvfund_expend_end_date) <= :TERM_DATE then trunc(ftvfund_term_date)
            else null
        end FUND_TERM_DATE,
        case
            when trunc(ftvfund_expend_end_date) <= :TERM_DATE then trunc(ftvfund_expend_end_date)
            else null
        end FUND_EXPEND_END_DATE,
        case
            when trunc(ftvorgn_term_date) <= :TERM_DATE then trunc(ftvorgn_term_date)
        else null
        end ORG_TERM_DATE,
        case
            when trunc(ftvacct_term_date) <= :TERM_DATE then trunc(ftvacct_term_date)
        else null
        end ACCT_TERM_DATE,
        case
            when trunc(ftvprog_term_date) <= :TERM_DATE then trunc(ftvprog_term_date)
        else null
        end PROG_TERM_DATE,
        case
            when trunc(ftvactv_term_date) <= :TERM_DATE then trunc(ftvactv_term_date)
        else null
        end ACTV_TERM_DATE,
        COMMENTS
from fgbencd
    left join ftvorgn_levels
        on fgbencd_orgn_code = orgn_code
    inner join ftvfund
        on fgbencd_fund_code = ftvfund_fund_code
        and ftvfund_nchg_date = '31-DEC-2099'
    left join ftvorgn
        on fgbencd_orgn_code = ftvorgn_orgn_code
        and ftvorgn_nchg_date = '31-DEC-2099'
    left join ftvacct
        on fgbencd_acct_code = ftvacct_acct_code
        and ftvacct_nchg_date = '31-DEC-2099'
    left join ftvprog
        on fgbencd_prog_code = ftvprog_prog_code
        and ftvprog_nchg_date = '31-DEC-2099'
    left join ftvactv
        on fgbencd_actv_code = ftvactv_actv_code
        and ftvactv_nchg_date = '31-DEC-2099'
    inner join lateral (select listagg(msg, ', ') within group ( order by priority ) COMMENTS
                        from ((select case
                                          when trunc(ftvfund_term_date) <= :TERM_DATE
                                              and (trunc(ftvfund_expend_end_date) = trunc(ftvfund_term_date)
                                                or ftvfund_expend_end_date is null)
                                              then ftvfund_fund_code || ' Fund term date is ' || trunc(ftvfund_term_date)
                                          when TRUNC(ftvfund_term_date) <= :TERM_DATE
                                              and TRUNC(ftvfund_expend_end_date) > trunc(ftvfund_term_date)
                                              and TRUNC(ftvfund_expend_end_date) <= :TERM_DATE
                                              and ftvfund_expend_end_date is not null
                                              then ftvfund_fund_code || ' Fund expenditure end date is ' ||
                                                   TRUNC(ftvfund_expend_end_date)
                                          end msg,
                                      1       priority
                                 from dual)
                         union all
                        (select case
                                    when ftvfund_status_ind <> 'A'
                                        then ftvfund_fund_code || ' Fund status is not active'
                                    end,
                                2
                           from dual)
                         union all
                        (select case
                                    when ftvfund_data_entry_ind <> 'Y'
                                        then ftvfund_fund_code || ' Fund is not data enterable'
                                    end,
                                3
                           from dual)
                         union all
                        (select case
                                    when trunc(ftvorgn_term_date) <= :TERM_DATE
                                        then ftvorgn_orgn_code || ' ORG term date is ' || trunc(ftvorgn_term_date)
                                    end,
                                4
                           from dual)
                         union all
                        (select case
                                    when ftvorgn_status_ind <> 'A'
                                        then ftvorgn_orgn_code || ' ORG status is not active'
                                    end,
                                5
                           from dual)
                         union all
                        (select case
                                    when ftvorgn_data_entry_ind <> 'Y'
                                        then ftvorgn_orgn_code || ' ORG is not data enterable'
                                    end,
                                6
                           from dual)
                         union all
                        (select case
                                    when TRUNC(ftvacct_term_date) <= :TERM_DATE
                                        then ftvacct_acct_code || ' ACCT term date is ' || trunc(ftvacct_term_date)
                                    end,
                                7
                           from dual)
                         union all
                        (select case
                                    when ftvacct_status_ind <> 'A'
                                        then ftvacct_acct_code || ' ACCT status is not active'
                                    end,
                                8
                           from dual)
                         union all
                        (select case
                                    when substr(ftvacct_acct_code, 3, 2) <> '01' and ftvacct_data_entry_ind <> 'Y'
                                        then ftvacct_acct_code || ' ACCT is not data enterable'
                                    end,
                                9
                           from dual)
                         union all
                        (select case
                                    when trunc(ftvprog_term_date) <= :TERM_DATE
                                        then ftvprog_prog_code || ' PROG term date is ' || trunc(ftvprog_term_date)
                                    end,
                                10
                           from dual)
                         union all
                        (select case
                                    when ftvprog_status_ind <> 'A'
                                        then ftvprog_prog_code || ' PROG status is not active'
                                    end,
                                11
                           from dual)
                         union all
                        (select case
                                    when ftvprog_data_entry_ind <> 'Y'
                                        then ftvprog_prog_code || ' PROG code is not data enterable'
                                    end,
                                12
                           from dual)
                         union all
                        (select case
                                    when trunc(ftvactv_term_date) <= :TERM_DATE
                                        then ftvactv_actv_code || ' ACTV term date is ' || TRUNC(ftvactv_term_date)
                                    end,
                                13
                           from dual)
                         union all
                        (select case
                                    when ftvactv_status_ind <> 'A'
                                        then ftvactv_actv_code || ' ACTV status is not active'
                                    end,
                                14
                           from dual)))
             on comments is not null 
    inner join fgbench
        on fgbencd_num = fgbench_num
    left join spriden
        on fgbench_vendor_pidm = spriden_pidm
        and spriden_change_ind is null
    inner join 
        (select fgbencp_num PO, fgbencp_item ITEM, fgbencp_seq_num SEQ,
                max(fgbencp_fsyr_code) FY
        from fgbencp
        group by fgbencp_num, fgbencp_item, fgbencp_seq_num)ENCB_FY
            on fgbencd_num = ENCB_FY.PO
            and fgbencd_item = ENCB_FY.ITEM
            and fgbencd_seq_num = ENCB_FY.SEQ
    inner join
        (select fgbencp_num PO,
                fgbencp_item ITEM,
                fgbencp_seq_num SEQ,
        sum  (fgbencp_orig_encb_amt
            + fgbencp_sum_encb_adjt
            + fgbencp_sum_encb_liq) AMT
        from fgbencp
        where fgbencp_period <> 00
        group by fgbencp_num, fgbencp_item, fgbencp_seq_num) AMT
            on fgbencd_num = AMT.PO
            and fgbencd_item = AMT.ITEM
            and fgbencd_seq_num = AMT.SEQ
    inner join
        (select fgbencp_num PO,
                  sum(nvl(fgbencp_orig_encb_amt, 0))
                + sum(nvl(fgbencp_sum_encb_adjt, 0))
                + sum(nvl(fgbencp_sum_encb_liq, 0)) AMT
        from fgbencp
        where fgbencp_period <> 00
        group by fgbencp_num)ENCB_AMT
            on fgbencd_num = ENCB_AMT.PO
    left join fabinvh
        on fgbencd_num = fabinvh_pohd_code
        and (fabinvh_appr_ind <> 'Y'
          or fabinvh_complete_ind <> 'Y')
    left join fpbpohd
        on fgbencd_num = fpbpohd_code
        and (fgbench_status = 'I'
         or (fpbpohd_complete_ind = 'N'
         or  fpbpohd_appr_ind = 'N'))
where ENCB_FY.FY = :FY /*REQUIRED - Enter the FY*/
and (:FUND is null or fgbencd_fund_code 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 fgbencd_acct_code like :ACCT) /*Enter the Account code or leave blank for all*/
and (:PROG is null or fgbencd_prog_code like :PROG) /*Enter the Program code or leave blank for all*/
and (:ACTV is null or fgbencd_actv_code like :ACTV) /*Enter the Activity code or leave blank for all*/
and ENCB_AMT.AMT <> 0
and fgbencd_status = 'O'
and fgbench_type <> 'R'
order by encb_number, item, seq