/*
Run this script to provide a list of Committed Encumbrances.

SNGAJ 4/22/25
*/
select  ENCB_FY.FY,
        fgbencd_num ENCB_NUMBER,
        fgbench_desc ENCB_DESC,
        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,
        fgbencd_item ITEM,
        fgbencd_seq_num SEQ,
        fgbencd_fund_code FUND,
        fgbencd_orgn_code ORG,
        fgbencd_acct_code ACCT,
        fgbencd_prog_code PROG,
        fgbencd_actv_code ACTV,
        AMT.ORIG_ENCB_AMT,
        AMT.SUM_ENCB_ADJT,
        AMT.SUM_ENCB_LIQ,
        AMT.NET,
        replace (level1, 'TOT') MAU,
        title2 CABINET,
        title3 UNIT,
        title4 DIVISION,
        title5 "CLUSTER",
        level6 DLEVEL,
        title6 DEPARTMENT,
        title7 PROGRAM
from fgbencd
    inner join fgbench
        on fgbencd_num = fgbench_num
    left join ftvorgn_levels
        on fgbencd_orgn_code = orgn_code
    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(nvl(fgbencp.fgbencp_orig_encb_amt, 0)) ORIG_ENCB_AMT,
                sum(nvl(fgbencp.fgbencp_sum_encb_adjt, 0)) SUM_ENCB_ADJT,
                sum(nvl(fgbencp.fgbencp_sum_encb_liq, 0)) SUM_ENCB_LIQ,
                  sum(nvl(fgbencp.fgbencp_orig_encb_amt, 0))
                + sum(nvl(fgbencp.fgbencp_sum_encb_adjt, 0))
                + sum(nvl(fgbencp.fgbencp_sum_encb_liq, 0)) NET
        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 AMT.NET <> 0 --Comment this out if wanting to see open committed encumbrance lines at $0.00.
and fgbencd_status = 'O'
and fgbencd_cmt_type = 'C'
and fgbench_type <> 'R'
order by encb_number, item, seq
/