Project Ledgers Financial Updates
Project Table Updates
IC_CUME
SELECT
glb.account_nbr,
ifnull(SUM(glb.contr_gr_bb_ac_amt), 0.0) + ifnull(SUM(glb.acln_annl_bal_amt), 0.0) ic_cume
FROM
gl_balance_t glb
JOIN ca_account_t ca ON glb.account_nbr = ca.account_nbr
AND glb.fin_coa_cd = ca.fin_coa_cd
WHERE
glb.fin_balance_typ_cd = 'AC'
AND ca.fin_coa_cd = 'CO'
AND glb.fin_object_cd = '9500'
AND ca.sub_fund_grp_cd IN ( 'SPONPR', 'ARRA' )
AND glb.univ_fiscal_yr IN ( ?, ? )
GROUP BY
glb.account_nbr
CUME_EXPENSE
SELECT
ca.account_nbr,
ifnull(SUM(glb.contr_gr_bb_ac_amt), 0.0) + ifnull(SUM(glb.acln_annl_bal_amt), 0.0) cume_expense
FROM
gl_balance_t glb
JOIN ca_account_t ca ON glb.account_nbr = ca.account_nbr
AND glb.fin_coa_cd = ca.fin_coa_cd
WHERE
glb.fin_balance_typ_cd = 'AC'
AND ca.fin_coa_cd = 'CO'
AND ca.sub_fund_grp_cd IN ( 'SPONPR', 'ARRA' )
AND glb.univ_fiscal_yr IN ( ?, ? )
AND ( glb.fin_object_cd BETWEEN '5000' AND '9999'
OR glb.fin_object_cd = '4434' )
AND glb.fin_object_cd NOT IN ( '9700' )
GROUP BY
ca.account_nbr
RECEIVABLES
Receivables
Sum of account line actual balance amount from the GL Account Balances table for object code 14xx
Unbilled
Sum of account line actual balance amount from the GL Account Balances table for object code 146x
Billed
Sum of account line actual balance amount from the GL Account Balances table for object code 145x
SELECT
ca.account_nbr,
ifnull(r.receivables, 0.0) receivables,
ifnull(b.receivables_billed, 0.0) receivables_billed,
ifnull(u.receivables_unbilled, 0.0) receivables_unbilled
FROM
ca_account_t ca
LEFT OUTER JOIN (
SELECT
glb.fin_coa_cd,
glb.account_nbr,
ifnull(SUM(glb.acln_actls_bal_amt), 0.0) receivables
FROM
gl_acct_balances_t glb
WHERE
glb.univ_fiscal_yr IN ( ?, ? )
AND glb.fin_object_cd LIKE '14%'
GROUP BY
glb.fin_coa_cd,
glb.account_nbr
) r ON r.fin_coa_cd = ca.fin_coa_cd
AND r.account_nbr = ca.account_nbr
LEFT OUTER JOIN (
SELECT
glb.fin_coa_cd,
glb.account_nbr,
ifnull(SUM(glb.acln_actls_bal_amt), 0.0) receivables_billed
FROM
gl_acct_balances_t glb
WHERE
glb.univ_fiscal_yr IN ( ?, ? )
AND glb.fin_object_cd LIKE '145%'
GROUP BY
glb.fin_coa_cd,
glb.account_nbr
) b ON b.fin_coa_cd = ca.fin_coa_cd
AND b.account_nbr = ca.account_nbr
LEFT OUTER JOIN (
SELECT
glb.fin_coa_cd,
glb.account_nbr,
ifnull(SUM(glb.acln_actls_bal_amt), 0.0) receivables_unbilled
FROM
gl_acct_balances_t glb
WHERE
glb.univ_fiscal_yr IN ( ?, ? )
AND glb.fin_object_cd LIKE '146%'
GROUP BY
glb.fin_coa_cd,
glb.account_nbr
) u ON u.fin_coa_cd = ca.fin_coa_cd
AND u.account_nbr = ca.account_nbr
WHERE
ca.fin_coa_cd = 'CO'
AND ca.sub_fund_grp_cd IN ( 'SPONPR', 'ARRA' )
TOTAL_RECEIVED
From GL Balance table
where object code is in (1100, 1429, 1740, 1790, 4434) or between 2100 and 2199 or between 2400 and 2499 or between 5000 and 9999
and balance type is 'AC'
Total received is calculated as Cash - payables + expenses
Cash: object codes 1100, 1429, 1740, 1790 from the Beginning Balance Line Amount
Payables: object codes 2100 to 2199 and 2400 to 2499 from the Beginning Balance Line Amount and account Line Annual Balance Amount
Expenses: object codes 4434 and 5000 to 9999 from the Contract & Grant Base Budget Amount and Account Line Annual Balance Amount
SELECT
caa.account_nbr,
ifnull(SUM(
CASE
WHEN glb.fin_object_cd IN('1100', '1429', '1740','1790') THEN
glb.fin_beg_bal_ln_amt
WHEN(glb.fin_object_cd BETWEEN '2100' AND '2199'
OR glb.fin_object_cd BETWEEN '2400' AND '2499') THEN
- 1 * glb.fin_beg_bal_ln_amt
ELSE
glb.contr_gr_bb_ac_amt
END
), 0.0) + ifnull(SUM(
CASE
WHEN(glb.fin_object_cd BETWEEN '2100' AND '2199'
OR glb.fin_object_cd BETWEEN '2400' AND '2499') THEN
- 1 * glb.acln_annl_bal_amt
ELSE
glb.acln_annl_bal_amt
END
), 0.0) total_received
FROM
ca_account_t caa
JOIN gl_balance_t glb ON caa.fin_coa_cd = glb.fin_coa_cd
AND caa.account_nbr = glb.account_nbr
WHERE
caa.fin_coa_cd = 'CO'
AND caa.sub_fund_grp_cd IN ( 'SPONPR', 'ARRA' )
AND glb.univ_fiscal_yr IN ( ?, ? )
AND glb.fin_balance_typ_cd = 'AC'
AND ( ( glb.fin_object_cd BETWEEN '5000' AND '9999'
OR glb.fin_object_cd = '4434' ) -- CUME_EXPENSE
OR glb.fin_object_cd IN ( '1100', '1429', '1740','1790' ) -- CASH
OR glb.fin_object_cd BETWEEN '2100' AND '2199' -- PAYABLES
OR glb.fin_object_cd BETWEEN '2400' AND '2499' ) -- PAYABLES
GROUP BY
glb.account_nbr
ENCUMBERANCE Flag
SELECT
ca.account_nbr,
'Y' encumberance
FROM
ca_account_t ca
JOIN gl_balance_t glb ON glb.account_nbr = ca.account_nbr
AND glb.fin_coa_cd = ca.fin_coa_cd
WHERE
glb.fin_balance_typ_cd IN ( 'IE', 'EX' )
AND ca.fin_coa_cd = 'CO'
AND ca.sub_fund_grp_cd IN ( 'SPONPR', 'ARRA' )
AND glb.univ_fiscal_yr IN ( ?, ? )
GROUP BY
glb.account_nbr
HAVING
SUM(glb.fin_beg_bal_ln_amt) + SUM(glb.acln_annl_bal_amt) != 0.0