Oracle Billing Insight v7.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 Billing Insight 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 must add a unique key constraint on the bill_id field of the check_payments table using the set_unique_bill_id.sql script. The bill_id in the Payment module is the same as the doc ID in Oracle Billing Insight.

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, then the customer receives 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, then the payment fails and an email notification message is 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 the customer 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, then 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 75 lists user actions and describes their impact on the Payment module database.

Table 75. 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 correct IUserAccountAccessor implementation and payment_account_accessor must point to the correct 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, in the format 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 canceled 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 checks with Paid, Returned, Failed and Canceled status.

Payment Table Sizing

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

Table 76 describes the Payment tables and other Oracle Billing Insight 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 76. 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, but not all customers will use reminders. Each reminder creates one row in the table.
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 of 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, then 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 Billing Insight		Copyright © 2015, Oracle and/or its affiliates. All rights reserved. Legal Notices.