how to make FRIGITD with sql.

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 straightforward. 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 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.

Leave a Reply

Your email address will not be published. Required fields are marked *