/*
Run this script to provide a list of Uncommitted Encumbrances that will roll as
committed in the next FY.

SNGAJ 4/22/25
*/

select  ENCB_FY.FY,
        trunc(ENCB_TDATE.CREATE_TRANS_DATE) CREATE_TRANS_DATE,
        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 (fgbencp_orig_encb_amt
            + fgbencp_sum_encb_adjt
            + fgbencp_sum_encb_liq) AMT
        from fgbencp
        where fgbencp_period <> 00
        group by fgbencp_num)ENCB_AMT
            on fgbencd_num = ENCB_AMT.PO
    inner join ftvfund
        on fgbencd_fund_code = ftvfund_fund_code
        and ftvfund_nchg_date = '31-DEC-2099'
    inner join
    (select ftvftyp_ftyp_code FTYP_CODE,
            ftvftyp_title FUND_TYP_TITLE,
            ftvftyp_internal_ftyp_code,
            ftvftyp_rucl_code_roll_enc ROLL_ENC,
            ftvftyp_commit_type ENCB_COMMIT_TYPE,
            ftvftyp_ftyp_code_pred PARENT_FUND_TYPE,
            PRED.PARENT_FUND_TYPE_TITLE,
            PRED.PARRENT_ROLL_ENC,
            PRED.PARRENT_COMMIT_TYPE
    from ftvftyp
         inner join
        (select ftvftyp_ftyp_code FUND_TYPE_PRED,
                ftvftyp_title PARENT_FUND_TYPE_TITLE,
                ftvftyp_rucl_code_roll_enc PARRENT_ROLL_ENC,
                ftvftyp_commit_type PARRENT_COMMIT_TYPE
        from ftvftyp
        where ftvftyp_nchg_date = '31-DEC-2099'
        and (ftvftyp_term_date is null
          or ftvftyp_term_date >= trunc(sysdate)))PRED
            on ftvftyp_ftyp_code_pred = PRED.FUND_TYPE_PRED
    where ftvftyp_nchg_date = '31-DEC-2099'
    and ((ftvftyp_rucl_code_roll_enc = 'E090'
    and ftvftyp_commit_type = 'C')
     or (ftvftyp_rucl_code_roll_enc is null
     and PRED.PARRENT_ROLL_ENC = 'E090'
     and PRED.PARRENT_COMMIT_TYPE = 'C'))
    and (ftvftyp_term_date is null
     or ftvftyp_term_date >= trunc(sysdate)))COMMIT_FTYP 
        on ftvfund_ftyp_code = COMMIT_FTYP.FTYP_CODE --limit output to only encumbrance lines that will roll as committed
    inner join
    (select fgbtrnd_doc_code DOC,
            fgbtrnd_encd_item_num ITEM,
            fgbtrnd_encd_seq_num SEQ,
            min(fgbtrnh_trans_date) CREATE_TRANS_DATE
    from fgbtrnd
        inner join fgbtrnh
            on fgbtrnd_rucl_code = fgbtrnh_rucl_code
            and fgbtrnd_doc_seq_code = fgbtrnh_doc_seq_code
            and fgbtrnd_doc_code = fgbtrnh_doc_code
            and fgbtrnd_submission_number = fgbtrnh_submission_number
            and fgbtrnd_item_num = fgbtrnh_item_num
            and fgbtrnd_seq_num = fgbtrnh_seq_num
    where fgbtrnd_field_code = '04'
    group by fgbtrnd_doc_code, fgbtrnd_encd_item_num, fgbtrnd_encd_seq_num
    order by doc, item, seq)ENCB_TDATE
        on fgbencd_num = ENCB_TDATE.DOC
        and fgbencd_item = ENCB_TDATE.ITEM
        and fgbencd_seq_num = ENCB_TDATE.SEQ
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 AMT.NET <> 0 --Encumbrances at $0.00 are closed prior to the roll
and fgbencd_status = 'O'
and fgbencd_cmt_type = 'U'
and fgbench_type <> 'R' --Excluding Requisitions since these docs do not roll into the next FY
order by encb_number, item, seq
/