At the University for which I work, we had a need to integrate our Sungard HE Banner ERP system with Barnes and Noble (our outsourced bookstore) to process book vouchers, and with our credit card processing API (Intellipay). Both of these applications required us to post charges and payments directly to the student’s account.
With our Barnes and Noble integration, the process works as such:
- We send Barnes and Noble a file of how much money the student has in book vouchers, which is equal to the credit amount the student has in their AR file (the TSAAREV screen). Barnes and Noble loads this information into their system.
- The student purchases and returns books to Barnes and Noble. The student gives Barnes and Noble their ID and then Barnes and Noble will allow books to be purchased using the voucher amount they have on file in their system.
- Each day Barnes and Noble sends us a file detailing the previous day’s activity. We post any purchases and refunds directly to the students account in Banner.
With our credit card processing system, we wrote a small web application within our portal that:
- Retrieves the students account balance and shows it on screen.
- Allows the student to enter in an amount to pay and their credit card information.
- A servlet then takes that information and communicates with the Intellipay API to process the transaction, and if successful the amount charged is posted directly to their account.
- The student can immediately see their new AR balance. No human intervention is required.
Right now I’m not going to describe all of the specifics on how we got our book voucher integration with Barnes and Noble or our integration with our Internet payment gateway working, but rather I’m going to describe the basic Banner API that allows us to easily post information to a students account. This Banner API is a small, but key, component to getting the transaction posted to the student’s account. In the future I’ll go into details on how we made all of this integration happen.
The procedure we need to utilize is in the TB_RECEIVABLE package, and is named P_CREATE.
A sample PL/SQL call to this procedure looks like:
tb_receivable.p_create (
p_pidm => 35689,
p_term_code => '200720',
p_detail_code => 'BKST',
p_desc => SUBSTR('Test Transaction', 1, 30),
p_user => 'BARNESNOBLE',
p_amount => 100.29,
p_effective_date => SYSDATE,
p_trans_date => '01-MAR-2006',
p_entry_date => SYSDATE,
p_srce_code => 'T',
p_data_origin => NULL,
p_tran_number_paid => NULL,
p_tran_number_out => tranNumber,
p_rowid_out => rowIdValue
)
The call to the API actually turns out to be really simple as shown above. Below I describe each parameter I pass in and the two out parameters. Note that this procedure can take many more parameters, and is fairly well documented in the PL/SQL code. Simply run a desc tb_receivable to see the comments and parameters that this procedure can take.
- p_pidm: The pidm of the student for whom to post the charge/payment to.
- p_term_code: The term associated with the transaction. For student transactions it must be a valid record in STVTERM, or ARTERM for non-student transactions.
- p_detail_code: A valid detail code associated with the transaction from the TBBDETC table.
- p_desc: Up to a 30 character description of the transaction.
- p_user: User ID of the cashier who created the transaction.
- p_amount: The transaction amount.
- p_effective_date: The effective date of the transaction used to calculate amount due, determine placement on the bill, and control feed to finance system based on Accounts Receivable System Control setting
- p_trans_date: The transaction date used by the finance system to control posting to the ledgers.
- p_entry_date: The entry date and time of the transaction, when initially created.
- p_srce_code: The source of the transaction when created on the account, including registration, housing, deposit, user entered, and so on. The default value is user entered transaction (T), valid in TTVSRCE.
- p_data_origin: The source system that last created or updated the data.
- p_tran_number_paid: The transaction Number Paid is used in application of payment to force this transaction to liquidate a specific transaction on the account.
- p_tran_number_out: OUT PARAMETER that returns the transaction number assigned for detail transaction on the account.
- p_rowid_out: OUT PARAMETER that returns the database rowid of the record created.
As you can see, using this API is very easy and straightforward. We call it both directly from PL/SQL procedures and through java applications that we have written.
I will provide an example of the java method we use to create an AR transaction:
public void createArTransaction(Connection conn,
String pidm,
double trxAmount,
String trxDescr,
String detailCode,
String cashierUserId,
String termCode,
java.util.Date trxDate) throws Exception {
try {
log.debug("Posting AR Transaction to account for pidm " + pidm);
//
//Run the SQL statement to post the charge
//
String sqlStmt = "";
sqlStmt = "{ call tb_receivable.p_create( " +
"p_pidm => ?," +
"p_term_code => ?," +
"p_detail_code => ?," +
"p_desc => SUBSTR(?, 1, 30)," +
"p_user => ?," +
"p_amount => ?," +
"p_effective_date => SYSDATE," +
"p_trans_date => ?," +
"p_entry_date => SYSDATE," +
"p_srce_code => 'T'," +
"p_data_origin => NULL," +
"p_tran_number_paid => NULL," +
"p_tran_number_out => ?," +
"p_rowid_out => ?) }";
CallableStatement cs = conn.prepareCall(sqlStmt);
cs.setString(1, pidm);
cs.setString(2, termCode);
cs.setString(3, detailCode);
cs.setString(4, trxDescr);
cs.setString(5, cashierUserId);
cs.setDouble(6, trxAmount);
cs.setDate(7, new java.sql.Date(trxDate.getTime()));
cs.registerOutParameter(8, Types.INTEGER);
cs.registerOutParameter(9, Types.VARCHAR);
cs.executeUpdate();
String tranId = cs.getString(8);
cs.close();
log.debug("Student Account transaction tbraccd Tran ID: "+tranId);
} catch (Exception e) {
log.error("An error occurred posting the transaction to the student account record. "+e.toString(), e);
throw e;
}
}
We then call the method with the appropriate parameters and the transaction is posted. Both our Barnes and Noble book voucher interface and our credit card processing API use the above method to process transactions and post directly to AR.
Note that after using this API, you still have to go through your normal steps of closing the cashiering sessions, applying payments, feeding to finance, etc. At our university we setup different cashiering sessions for our various applications that integrate with AR. For example, we have a user named BARNESNOBLE that all the book voucher transactions happen under. This helps us reconcile and make sure that the total in the file matches the cashiering session total displayed in Banner.
del.icio.us
Digg
StumbleUpon