PeopleSoft Student Financial Tables and Application of Payments to Charges

GENERAL POSTING

This article will explain relationships between the core Student Financials tables and will also explain what happens during the application of payments to charges.  The tables being reviewed include:

Tables used to track transactions on a student’s account:

PAYMENT_TBL

ITEM_SF

ITEM_LINE_SF

ITEM_DUE_SF

ACCOUNT_SF

ITEM_XREF

Tables used during the posting process:

QUICK_POST_TBL

GROUP_CONT_INFO

GROUP_LINE

The key structure for most of the Student Financials tables consists of a minimum of three fields used to identify the student for whom the transaction is relevant.  This key structure consists of BUSINESS_UNIT, COMMON_ID and the SA_ID_TYPE.  For the purposes of brevity, this key structure will be referred to as either the COMMON_ID or as the student.

Please also note that where a SQL statement is not displayed, the PS_ has been dropped from the database level name of the table.  When a SQL statement is shown, however, the PS_ prefix will be used.

OVERVIEW OF THE POSTING PROCESS:

With all transactions, an insert will first be made into either QUICK_POST_TBL, or if the transaction is part of a batch GROUP_CONT_INFO (for batch header information only) and GROUP_LINE.

When a credit (Payment, Financial Aid, Waiver and Pay Plan Credit Item Type Codes) is posted to a student’s account, inserts will be made into the PAYMENT_TBL, ITEM_SF and ITEM_LINE_SF.  A new row will be inserted into ACCOUNT_SF if necessary, or an existing row will be updated.

When a debit (Charge or Refund Item Type Codes) is posted to the student’s account, no row will be inserted into PAYMENT_TBL, but the inserts will be made to ITEM_SF, ITEM_LINE_SF and also to ITEM_DUE_SF.  A new row will be inserted into ACCOUNT_SF if necessary, or an existing row will be updated.  For refunds, additional data will be created, but this is not discussed in this document.

In the event that a credit is posted and there is an eligible, unpaid debit on the student’s account, or if a debit is posted and an unapplied credit that can pay off that debit already exists on the account, a row may be inserted into ITEM_XREF.  During this application of payments to charges it is also possible that existing rows in ITEM_XREF will be updated as well.

THE TABLE RELATIONSHIPS

In the basic relational database model, it is customary to normalize data such that a summary amount can be derived based upon the related amounts in child table(s).  In Student Financials, most data is normalized but we do maintain hard aggregates for our parent tables.  ACCOUNT_SF.ACCOUNT_BALANCE, for instance, is a maintained field despite the fact that it should be the sum of related entries for ITEM_SF.ITEM_AMT.  Because of this, it is necessary to make sure that the following parent/child relationships are properly enforced when it comes to these aggregates.

ACCOUNT_SF.ACCOUNT_BALANCE must equal the sum of

          Related entries for ITEM_SF.ITEM_AMT

PAYMENT_TBL.PAYMENT_AMT must equal the sum of

          Related entries for ITEM_SF.ITEM_AMT (as positive amounts)

ITEM_SF.ITEM_AMT must equal the sum of

          Related entries for ITEM_LINE_SF.LINE_AMT

ITEM_SF.APPLIED_AMT must equal the sum of

          Related entries for ITEM_DUE_SF.LINE_AMT

ITEM_SF.APPLIED_AMT must equal the sum of

          Related entries for ITEM_XREF.XREF_AMT for debits

ITEM_SF.ITEM_AMT less the ITEM_SF.ITEM_BALANCE must equal the sum of

          Related entries for ITEM_XREF.XREF_AMT (as negative amounts) for credits

When discrepancies exist between these relationships, it will be necessary to correct the data so that the referential integrity of the data is maintained.  Table Validation is a process that will do this for you in most cases by reporting the discrepancy and fixing it where it can.  Where it cannot do so, it may be necessary to correct the data through the process of reversing the offending transaction and reposting it.  As a last resort, it may be necessary to correct the data using SQL.  When doing this it is important to note that the master table for credits is PAYMENT_TBL while the master table for debits is ITEM_SF and any corrections should be made so that other tables agree with the master data.  This is also true if you ever have to make changes to ACCOUNT_SF, as ACCOUNT_SF will have to be adjusted to agree with the data in these two master tables.

Also, when correcting any data through the use of SQL, you should run Table Validation after corrections have been made so you can insure the validity of your corrections. 

 APPLICATION OF PAYMENTS TO CHARGES AND ITEM_XREF:

When a payment is applied to a charge, two major events will occur.  One is an adjustment to the active balance for both charge and payment on ITEM_SF, and the other is an insert of one or more rows on ITEM_XREF.  The active balance on ITEM_SF represents the amount of a charge still outstanding or the amount of a payment not applied to any charges.  It is considered to be the following:

-          For charges active balance is ITEM_AMT minus APPLIED_AMT

-          For payments active balance is ITEM_BALANCE

Existing rows on ITEM_XREF are not updated as the table is effective dated and history will always be maintained.

In the event that a single payment is applied to multiple charges, additional rows on ITEM_LINE_SF will be inserted to reflect the splitting of the payment.

The best way to illustrate what happens is to walk through a few examples. 

EXAMPLE A

PAYMENT APPLYING TO AN EXISTING CHARGE

POSTING THE CHARGE:

When a charge is posted to a student’s account, a row will be inserted into ITEM_SF, ITEM_LINE_SF and ITEM_DUE_SF to reflect that charge.  Assume for this example that a charge is posted for $450.  After the transaction will be posted, you will see the following results on the table:

On ITEM_SF:

ITEM_NBR             ITEM_AMT   ITEM_BALANCE     APPLIED_AMT

Charge             000000000000001           450.00           .00                               .00    

There will be no rows on ITEM_XREF at this point

POSTING A PAYMENT THAT APPLIES TO THE CHARGE:

Now, when we post a payment that applies to this particular charge, the following will result:

On ITEM_SF:

ITEM_NBR             ITEM_AMT   ITEM_BALANCE     APPLIED_AMT

Charge             000000000000001       450.00                   .00                   300.00

Payment           000000000000002       -300.00             .00                          .00

On ITEM_XREF:

ITEM_NBR_CHARGE        ITEM_NBR_PAYMENT                XREF_AMT

000000000000001                  000000000000002                             300.00       

Notice in the above example that the ITEM_BALANCE for the payment is zero because the payment is fully applied to the charge and therefore has no active balance.


EXAMPLE B
CHARGE PAID BY AN EXISTING PAYMENT

POSTING THE PAYMENT:

Posting a payment to a student’s account is similar in nature to posting a charge with a couple of differences.  First, a row is going to be inserted into PAYMENT_TBL containing the properties of this payment, such as the Charge Priority that was used at the time of posting and which will always be used for subsequent swapping of the payment.  Like the charge, a row will also be inserted into ITEM_SF and ITEM_LINE_SF, although no row will be inserted into ITEM_DUE_SF.

Assume for this example that a payment is posted for $200.  The option for Moving Excess Payments is turned OFF.  After the transaction will be posted, you will see the following results on the table:

On ITEM_SF:

ITEM_NBR             ITEM_AMT   ITEM_BALANCE     APPLIED_AMT

Payment           000000000000001      -200.00            -200.00                       .00    

There will be no rows on ITEM_XREF at this point.  If the Move Excess Payment functionality is turned on, this payment will have an ACCOUNT_TERM value that reflects the Excess Account Number.

POSTING A CHARGE THAT IS PAID BY THE PAYMENT:

Now, when we post a $75 charge that can be paid by this payment, the following will result:

On ITEM_SF:

ITEM_NBR             ITEM_AMT   ITEM_BALANCE     APPLIED_AMT

Payment           000000000000001     -200.00             -125.00                     .00

Charge             000000000000002       75.00                  .00                         75.00

On ITEM_XREF:

ITEM_NBR_CHARGE        ITEM_NBR_PAYMENT                XREF_AMT

000000000000002                  000000000000001                              75.00      

Note that the APPLIED_AMT value for the charge is 75 because the charge is fully paid by the payment. 

If the Move Excess Payment functionality had been turned on for this example, you would see the move of the originaly payment from the Excess Account to the Account of the charge represented by an additional row on ITEM_SF.  The original row would have an ITEM_AMT of zero while the new row (and new Item Number) would show the new information.

EXAMPLE C

PAYMENT SPLIT ACROSS MULTIPLE CHARGES

POSTING 2 CHARGES –

Assume for this example that a charge is posted for $450, and another charge is posted using a different Item Type for $300.  After the transactions are posted, you will see the following results on the table:

On ITEM_SF:

ITEM_NBR             ITEM_AMT   ITEM_BALANCE     APPLIED_AMT

Charge             000000000000001      450.00            .00                             .00    

Charge             000000000000001      300.00            .00                             .00    

There will be no rows on ITEM_XREF at this point

POSTING A PAYMENT THAT APPLIES TO BOTH CHARGES

Now, when we post an $800 payment that applies to both of these charges, the following will result:

On ITEM_SF:

ITEM_NBR             ITEM_AMT   ITEM_BALANCE     APPLIED_AMT

Charge            000000000000001        450.00             .00                           .00

Charge            000000000000002        300.00             .00                           .00

Payment          000000000000003        -800.00            -50.00                      .00

On ITEM_XREF:

ITEM_NBR_CHARGE        ITEM_NBR_PAYMENT                XREF_AMT

000000000000001                  000000000000003                            450.00       

000000000000002                  000000000000003                            300.00       

Notice in the above example that the ITEM_BALANCE for the payment is -50 because the payment is applied to both charges, but still has $50 remaining to be applied to other eligible charges. 

REPORTING CONSIDERATIONS:

ACCOUNT_SF: 

The key structure on this table includes a term value.  When determining the Items in ITEM_SF which must roll up to the balance in this account, you will need to look at ITEM_SF.ACCOUNT_TERM.  The value in ITEM_SF.ITEM_TERM is not relevant to the balances in ACCOUNT_SF. 

It is also important to note that if the Account Type has been setup with the Account Per Term checkbox turned OFF, the value in ACCOUNT_SF.ACCOUNT_TERM and ITEM_SF.ACCOUNT_TERM should both be blank.  If this checkbox is changed after transactions have been posted to the students’ accounts, data may be displayed incorrectly because of a mismatch between these values (it would be blank on one table and populated on another).

ACCOUNT_SF uses a key field of EMPLID to identify the student for whom the account is relevant.  A similar table exists for External Organizations called ACCOUNT_ORG_SF, and the key field on that table is EXT_ORG_ID.