Oracle Self-Service E-Billing v6.0: Managing the Payment Module Database

Running an application in a live production environment can generate a large volume of historical data in an application's database. You are responsible for monitoring and maintaining your own database server.

It is recommended that you monitor your database server on a weekly or other regular basis to perform the following tasks:

There are specific tasks required in Oracle Self-Service E-Billing to administer the database of the Payment module.

Preventing Multiple Payments

By default, the Payment module allows a bill to be paid more than one time. To make sure that a bill can only be paid one time, you need to add a unique key constraint on the bill_id field of the check_payments table. Run the set_unique_bill_id.sql script, found in the EDX_HOME/db/DB_NAME directory (or in the EDX_HOME/db/DB_NAME/migration/to42 directory), to set the unique constraint. In the path, EDX_HOME is the directory where you installed Oracle Self-Service E-Billing. The bill_id in the Payment module is the same as the doc ID in Oracle Self-Service E-Billing.

If a customer tries to pay a bill that has already been paid after the unique key constraint has been added (either from the UI or by a previously scheduled recurring payment), the customer will receive an error message stating that the bill has been already paid. If the bill is paid from the UI and a recurring payment tries to pay it again, the payment will fail and an email notification message will be sent to the customer (if recurring payments are configured for that email notification).

Adding this constraint will not prevent a customer from making a payment using a bill ID. For example, a customer can still make a payment directly from the Make Check Payment link, which allows them to make a payment without specifying a bill.

The unique key constraint only informs a customer that the bill has been paid when he or she tries to pay a bill that has already been paid. If you want to provide additional features, for example, disabling the payment option when the bill has already been paid, you must query the database to get that information. Use caution when adding extra functions because performing additional database queries can deteriorate Payment module performance. Make sure to create the proper index if you plan to create a new query.

UI Actions and Database Changes

Table 56 lists user actions and describes their impact on the Payment module database.

Table 56. UI Actions and Database Changes
UI Action	Payment Module Database
Create payment setting (Command Center)	Payment setting information is saved in the payment_profile table. The param_name user_account_accessor must point to the right IUserAccountAccessor implementation and payment_account_accessor must point to the right IPaymentAccountAccessor implementation.
User enrolls	User information is inserted into CDA and payment accounts are inserted into the payment_accounts table.
Run pmtSubmitEnroll	Finds all payment accounts whose account_status is pnd_active, txn_date is null, and sends to the ACH payment gateway. After that, it sets txn_date to the current date (yyyyMMddHHmm).
Run pmtConfirmEnroll	Changes account_status to bad_active if returned or to active if there is no return after three days. Updates notify_status to N.
User logs in	The user's ID and password is checked against the user_id and hash in the enrollment table.
User makes a check payment	The payment is saved into the check_payments table. The status of the check is Scheduled (6).
User clicks on Future Payments	Displays a list of scheduled payments for this user. The user can use the list to cancel or update scheduled payments.
User cancels or updates a check	When a user cancels a check, the status of that check is set to Canceled (9). The check is not deleted from the database. When a user updates a check, the same entry in check_payments will be updated. A check can only be cancelled or updated when the status of that check is Scheduled (6).
Run pmtCheckSubmit	Finds all checks due by tomorrow (or before), and sends them to the ACH payment gateway. The status of the check changed to processed (7). The txn_number field now holds the trace number of the check, and the reminded field is set to N. A batch report file is written to the payment_log table, whose type is summary. You can view this report from the Command Center.
User clicks on Payment History	Displays processed check payments as well as paid, returned, failed and cancelled checks.

Payment Table Sizing

The size to which the Payment tables grow depends on the number of enrolled users.

Table 57 describes the Payment tables and other Oracle Self-Service E-Billing tables that are related to enrollment. Differences are noted for Microsoft SQL Server. The numbers in the table assume that there are 100,000 registered users.

Table 57. Payment Table Sizing Information
Payment Table	Projected Row Count	Notes
check_payments	1.2 million based on 100,000 users kept for one year	Customer dependent. Assuming one user makes one check payment each month, and check history is kept in the database for one year, then the total number of rows is approximately 12 times the number of users.
check_payments_ history	Approximately 3 times the size of check_ payments table	Tracks the state of a check payment. Usually a check passes through three states before it is cleared or returned.
check_payments_ status	10	This table is a reference to the meanings of check payment status. It is not used by the Payment module.
credit_card_ payments	1.2 million based on 100,000 users a year	Customer dependent. Assuming one user makes one credit card payment a month, and credit card payments are kept in the database for one year, then the total number of rows will be approximately 12 times the number of users.
payment_accounts	200,000	The estimated row size is approximately 1,400 for each user, or 280MB for 100,000 users.
payment_bill_ summaries	Approximately 33,000 times 12, which equals 400,000, assuming one-third of the register with recurring payment	Saves bill information related to recurring payments.
payment_invoices	12 million based on each payment averaging 10 invoices	Typically used for business customers. Some billers might choose not to use this feature.
payment_log	Less than 10,000 a year	Approximately 20 rows will be inserted when a pmtCheckSubmit batch job is run.
payment_profile	Less than 100	There are approximately 20 rows for each DDN and payment type.
payment_reminders	Less than 100,000 based on 100,000 users	Customer dependent. Each customer can set up one reminder (each reminder creates one row in the table), but not all customers will use reminders.
recurring_payments	Approximately 33,000, assuming one third of the register with recurring payment	If recurring payment is turned off, then this table will be empty.

Payment Table Maintenance

For check payments, there are two tables which might grow quickly: check_payments and check_payments_history.

The check_payments table records the check payments made by users. The check_payments_history table records the history (status changes) for each check in check_payments. The check_payments_history table is approximately three times the size of the check_payments table.

Payments that are of a certain age (for example, one year) can be backed up and deleted from the Payment module database for proper performance with a high volume of users. The create_time field in the check_payments table records when a check is created, and can be used to determine a check's age.

Backup and Recovery

All Payment database transactions operate in their own transaction context. If a single operation fails (for example, failure to enroll or submit a payment), the Payment database automatically rolls back to its original state.

To recover all transactions for a certain period, the database administrator must back up the database regularly so that the database can be restored to the previous day. It is best to back up the database before running the Payment Submit and Update jobs, so there will be no question about whether the jobs were still running during the backup. The frequency of backup depends on how long the period is for payment processing.

Do not backup the master database. The master database is used by the database itself for internal purposes.

Backing up Tables

All tables must be backed up, but the check_payments, check_payments_history, creditcard_payments and creditcard_payments_history tables in particular must be backed up on a regular basis.

Backing up Stored Procedures

Stored procedures must be backed up, especially procedures modified by Oracle Professional Services.

Administration Guide for Oracle Self-Service E-Billing		Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices.