Often times you want to replicate a form query with plain ol sql. In this little example ill show you how to do just this very thing with FRRIGITD. The sql is pretty straight foward. Let me remind you
that the grant ledger is inception to date.
Therefore this query will show inception to date data as of the current date.
SELECT
m.frrgrnl_grnt_code,
m.frrgrnl_fund_code,
m.frrgrnl_Acct_code,
m.frrgrnl_orgn_code,
m.FRRGRNL_PROG_CODE,
SUM(m.frrgrnl_14_adopt_bud + m.frrgrnl_14_bud_adjt) BUDGET,
SUM(m.frrgrnl_14_ytd_actv) frrgrnl_14_ytd_actv,
SUM(m.frrgrnl_14_bud_rsrv) frrgrnl_14_bud_rsrv,
SUM(m.frrgrnl_14_encumb) frrgrnl_14_encumb,
SUM(m.frrgrnl_14_adopt_bud + m.frrgrnl_14_bud_adjt - m.frrgrnl_14_ytd_actv - m.frrgrnl_14_bud_rsrv - m.frrgrnl_14_encumb)Grant_total
FROM frrgrnl m--, FTVACCT
WHERE frrgrnl_acct_code NOT LIKE '5%'
AND m.frrgrnl_grnt_yr =
(SELECT MAX(s.frrgrnl_grnt_yr)
FROM frrgrnl s
WHERE s.frrgrnl_grnt_code =m.frrgrnl_grnt_code
AND s.frrgrnl_acct_Code = m.frrgrnl_acct_code
AND s.frrgrnl_fund_code =m.frrgrnl_fund_code
AND s.frrgrnl_orgn_code = m.frrgrnl_orgn_code
AND s.frrgrnl_prog_code = m.frrgrnl_prog_code
AND s.frrgrnl_locn_code = m.frrgrnl_locn_code
AND s.frrgrnl_actv_code = m.frrgrnl_actv_code
)
GROUP BY m.frrgrnl_Acct_code,
m.frrgrnl_grnt_code,
m.frrgrnl_fund_Code,
m.frrgrnl_orgn_code,
m.FRRGRNL_PROG_CODE
ORDER BY m.frrgrnl_grnt_code;
Word of caution on this query. I exclude accounts that start with 5. These are revenue account in my institution so i don’t not want to include them for my purposes. The FRIGITD form allows you to exclude revenues, that is basically what i did by excluding the accounts that start with “5”. If you were trying to adapt this to your institution you should change this line to the accounts you were trying to exclude or simply just remove if you would like to see everything.