TimArcher.com A blog about technology...and more.

Are you a Sungard HE customer using the Banner ERP Software?
Have you ever wanted to know how to easily select a student's account balance?

Well, all of a student's charges and payments are held in the tbraccd table. Banner defines the tbraccd table as the "Account Charge/Payment Detail Table". Furthermore, each entry in the tbraccd table has a specific detail code defined in the tbraccd_detail_code column. This column relates to the tbbdetc table, which Banner defines as the "Detail Charge/Payment Code Definition Table".

Essentially, the tbraccd table stores the type of transaction, and the transaction amount. The tbbdetc table tells us whether that transaction type is a charge or a payment.

So, to get the AR balance for a student, we join the tbraccd table to the tbbdetc table, decode the tbraccd_amount column such that if the transaction type is a payment then we convert it to a negative number, and then we sum it all up. The result is the AR balance.

An example is below:

SELECT NVL(SUM(
           DECODE(tbbdetc_type_ind, 
                  'P', (NVL(tbraccd_amount, 0) * -1), 
                   NVL(tbraccd_amount, 0))
		 ), 0)
       AS total_amount
FROM   tbbdetc,
       tbraccd
WHERE  tbraccd_pidm = 35689
AND    tbraccd_detail_code = tbbdetc_detail_code

If we only wanted to get the payments a student made, we can add on one more AND clause to the query so it looks as follows:

SELECT NVL(SUM(
           DECODE(tbbdetc_type_ind, 
                  'P', (NVL(tbraccd_amount, 0) * -1), 
                   NVL(tbraccd_amount, 0))
		 ), 0)
       AS total_amount
FROM   tbbdetc,
       tbraccd
WHERE  tbraccd_pidm = 35689
AND    tbraccd_detail_code = tbbdetc_detail_code
AND    tbbdetc_type_ind IN ('P')

From there you can easily figure out how to only get the charges for a specific term, or date range by restricting your queries on one of the many other columns in the tbraccd table such as tbraccd_term_code or tbraccd_effective_date.