Implementation Guide for Oracle Self-Service E-Billing > Payment Processing >

About Payment Auditing

Oracle Self-Service E-Billing Payment audits some Oracle Self-Service E-Billing Payment jobs to track a variety of transaction failures. Audits are kept for actions taken through the UI, as well as jobs.

Payment Jobs That Are Audited

The following jobs write to the audit tables:

• pmtCheckSubmit. Writes the following audited information:
  • Payments that failed during submission.
  • Encryption exceptions.
• pmtPaymentReminder. Writes payment reminders that were not sent, including:
  • Regular payment reminders that failed to send, for any reason, such as bad email address.
  • Check payment email that failed to send, for any reason, such as encryption error, bad email address.
  • Credit card payment email failed to send, for any reason, such as encryption error or bad email address.
• pmtCreditCardSubmit. Writes credit card payments that failed to submit, for example, because of encryption errors, invalid credit card information (such as invalid account) or network errors.
• pmtIntegrator (AR). Writes check and credit card payments that were not written to the AR file, such as because of encryption errors or file write errors.
• pmtRecurringPayment Job. Check and credit card payments that failed.
• pmtCheckSubmit and pmtCreditCardSubmit. Submits check and credit card payments.

UI Actions That Are Audited

Lists successful and unsuccessful payments along with a reason code.

The UI actions that trigger an audit entry are:

• Create Recurring Payment
• Update Recurring Payment
• Delete Recurring Payment
• Create Schedule Payment
• Create Instant Payment
• Cancel Future Payment (Credit Card Payment)
• Update Future Payment (Credit Card Payment)
• Cancel Future Payment (Check Payment)
• Update Future Payment (Check Payment)
• Create Payment Reminder
• Update Payment Reminder
• Delete Payment Reminder
• Create Check Account
• Edit Check Account
• Delete Check Account
• Create Credit Card Account
• Edit Credit Card Account
• Delete Credit Card Account

Example UI Audit Flow

The following steps show how a UI audit flow processes:

1. The customer selects the Setup of recurring payment option, populates the information to initially set up recurring payment, and submits it. The following information is recorded as the audit data in the recurring_payments_history table in addition to the columns defined in the recurring _payments table. (This history table contains all the columns defined in the recurring_payments (regular table) table plus the additional following columns).

   Column	Value	Description
   audit_operation	1001	This constant value for the operation is explained in the recurring_payment_const table.
   audit_status	1	Status constant value successful operation. This constant value for the status is explained in the recurring_payment_const table.
   audit_reason	none	Description of the audit.
   Job_id	0	Because this is an UI operation, the job ID value is 0 (not a job).
   Job_name	NULL	Because this is a UI operation, job name is NULL.
   Timestamp	none	The current system time when an audit occurs.

2. The customer selects Recurring Payment option, and then selects Update, and updates the recurring payment information and submits it, the following information is recorded as the audit data in recurring_payments_history table other than the columns defined in the regular recurring _payments table. (This history table contains all the columns defined in the recurring_payments (regular table) table and additional following columns).

   Column	Value	Description
   audit_operation	1002	This constant value for the operation is explained in the recurring_payment_const table.
   audit_status	1	Status constant value for successful operation. This constant value for the status is explained in the recurring_payment_const table.
   audit_reason	none	Description of the audit.
   Job_id	0	Because this is a UI operation, the job ID value is 0.
   Job_name	NULL	Because this is a UI operation, the job name is NULL.
   Timestamp	none	The current system time when an audit occurs.

3. The customer selects Recurring Payment option, and then selects Delete, the following information is recorded as the audit data in recurring_payments_history table other than the columns defined in the regular recurring _payments table. (This history table contains all the columns defined in the recurring_payments (regular table) table and additional following columns).

   Column	Value	Description
   audit_operation	1003	This constant value for the operation is described in the recurring_payment_const table.
   audit_status	1	Status constant value for successful operation. This constant value for the status is described in the recurring_payment_const table.
   audit_reason	none	Description of the audit.
   Job_id	0	Because this is a UI operation, the job ID value is 0.
   Job_name	NULL	Because this is a UI operation, the job name is NULL.
   Timestamp	none	The current system time when an audit occurs.

4. The customer selects Create Check account in the User Profile UI, and submits the new check account information, the following audit data is recorded in payment_accounts_history table other than the columns defined in the regular payment_accounts table. (This history table contains all the columns defined in the payment_accounts (regular table) table and additional following columns).

   Column	Value	Description
   audit_operation	1001	This constant value for the operation is explained in the payment_account_const table.
   audit_status	1	Status constant value for successful operation. This constant value for the status is explained in the payment_account_const table.
   audit_reason	none	Description of the audit.
   Job_id	0	Because this is a UI operation, the job ID value is 0.
   Job_name	NULL	Because this is a UI operation, the job name is NULL.
   Timestamp	none	The current system time when an audit occurs.

5. The customer selects Update Check account in the User Profile UI, and submits the updated check account information, the following audit data is recorded in payment_accounts_history table other than the columns defined in the regular payment_accounts table. (This history table contains all the columns defined in the payment_accounts (regular table) table and additional following columns).

   Column	Value	Description
   audit_operation	1002	This constant value for the operation is explained in the payment_account_const table.
   audit_status	1	Status constant value for successful operation. This constant value for the status is explained in the payment_account_const table.
   audit_reason	none	Description of the audit.
   Job_id	0	Because this is a UI operation, the job ID value is 0.
   Job_name	NULL	Because this is a UI operation, the job name is NULL.
   Timestamp	none	The current system time when an audit occurs.

6. The customer selects Delete Check account in the User Profile UI, and submits the delete request, the following audit data is recorded in payment_accounts_history table other than the columns defined in the regular payment_accounts table. (This history table contains all the columns defined in the payment_accounts (regular table) table and additional following columns).

   Column	Value	Description
   audit_operation	1003	This constant value for the operation is explained in the payment_account_const table).
   audit_status	1	Status constant value for successful operation. (this constant value for the status is explained in the payment_account_const table).
   audit_reason	none	Description of the audit.
   Job_id	0	Because this is a UI operation, the job ID value is 0.
   Job_name	NULL	Because this is a UI operation, the job name is NULL.
   Timestamp	none	The current system time when an audit occurs.

7. The customer selects Create Credit Card account in the User Profile UI, and submits the new credit card account information, the following audit data is recorded in payment_accounts_history table other than the columns defined in the regular payment_accounts table. (This history table contains all the columns defined in the payment_accounts (regular table) table and additional following columns).

   Column	Value	Description
   audit_operation	1001	This constant value for the operation is explained in the payment_account_const table.
   audit_status	1	Status constant value for successful operation. This constant value for the status is explained in the payment_account_const table.
   audit_reason	none	Description of the audit.
   Job_id	0	Because this is a UI operation, the job ID value is 0.
   Job_name	NULL	Because this is a UI operation, the job name is NULL.
   Timestamp	none	The current system time when an audit occurs.

8. The customer selects Update Credit Card account in the User Profile UI, and submits the updated credit card account information, the following audit data is recorded in payment_accounts_history table other than the columns defined in the regular payment_accounts table. This history table contains all the columns defined in the payment_accounts (regular table) table and additional following columns:

   Column	Value	Description
   audit_operation	1002	This constant value for the operation is explained in the payment_account_const table.
   audit_status	1	Status constant value for successful operation. This constant value for the status is explained in the payment_account_const table.
   audit_reason	none	Description of the audit.
   Job_id	0	Because this is a UI operation, the job ID value is 0.
   Job_name	NULL	Because this is a UI operation, the job name is NULL.
   Timestamp	none	The current system time when an audit occurs.

9. The customer selects Delete Credit Card account in the User Profile UI, and submits the delete request, the following audit data is recorded in payment_accounts_history table other than the columns defined in the regular payment_accounts table. This history table contains all the columns defined in the payment_accounts (regular table) table and additional following columns:

   Column	Value	Description
   audit_operation	1003	This constant value for the operation is explained in the payment_account_const table.
   audit_status	1	Status constant value for successful operation. This constant value for the status is explained in the payment_account_const table.
   audit_reason	none	Description of the audit.
   Job_id	0	Because this is a UI operation, the job ID value is 0.
   Job_name	NULL	Because this is a UI operation, the job name is NULL.
   Timestamp	none	The current system time when an audit occurs.

10. The customer selects Create payment reminder in the User Profile UI, and submits the new payment reminder information, the following audit data is recorded in payment_reminders_history table other than the columns defined in the regular payment_reminders table. (This history table contains all the columns defined in the payment_reminders (regular table) table and additional following columns).

    Column	Value	Description
    audit_operation	1001	This constant value for the operation is explained in the payment_reminder_const table.
    audit_status	1	Status constant value for successful operation. This constant value for the status is explained in the payment_reminder_const table.
    audit_reason	none	Description of the audit.
    Job_id	0	Because this is a UI operation, the job ID value is 0.
    Job_name	NULL	Because this is a UI operation, the job name is NULL.
    Timestamp	none	The current system time when an audit occurs.

11. The customer selects Update payment reminder in the User Profile UI, and submits the updated payment reminder information, the following audit data is recorded in payment_reminders_history table other than the columns defined in the regular payment_reminders table. (This history table contains all the columns defined in the payment_reminders (regular table) table and additional following columns).

    Column	Value	Description
    audit_operation	1002	This constant value for the operation is explained in the payment_reminder_const table.
    audit_status	1	Status constant value for successful operation. This constant value for the status is explained in the payment_reminder_const table.
    audit_reason	none	Description of the audit.
    Job_id	0	Because this is a UI operation, the job ID value is 0.
    Job_name	NULL	Because this is a UI operation, the job name is NULL.
    Timestamp	none	The current system time when an audit occurs.

12. The customer selects Delete payment reminder in the User Profile UI, and submits the delete request for the payment reminder, the following audit data is recorded in payment_reminders_history table other than the columns defined in the regular payment_reminders table. This history table contains all the columns defined in the payment_reminders (regular table) table and additional following columns:

    Column	Value	Description
    audit_operation	1003	This constant value for the operation is explained in the payment_reminder_const table.
    audit_status	1	Status constant value for successful operation. This constant value for the status is explained in the payment_reminder_const table.
    audit_reason	none	Description of the audit.
    Job_id	0	Because this is a UI operation, the job ID value is 0.
    Job_name	NULL	Because this is a UI operation, the job name is NULL.
    Timestamp	none	The current system time when an audit occurs.

About Query Files

The following files are provided for each operating system to support queries of the audit tables:

• UNIX:
  • getAuditInfoByAccount.sh
  • getAuditInfoByAccount.sql
  • getAuditInfoByPaymentId.sh
  • getAuditInfoByPaymentId.sql
  • getAuditInfoByPid.sh
  • getAuditInfoByPid.sql
• Windows:
  • getAuditDataByAccount.bat
  • getAuditDataByAccount.sql
  • getAuditDataByPaymentId.bat
  • getAuditDataByPaymentId.sql
  • getAuditDataByPid.bat
  • getAuditDataByPid.sql
  • set_audit_isql_options.bat

Running Audit Queries

Audit queries require one of the following arguments:

• Payment ID
• User Account Number
• PID

The audit queries are implemented in batch files, which require the user argument and date range. The results are displayed on the console.

Before running the queries, you must perform setup. The description for each query describes the setup.

Query Audit Data by Payment ID

Displays data from all history tables which have a payment ID column. This query performs a select on each table where the Payment ID matches and the time_stamp is between the fromTime and toTime values. The following tables are queried:

• check_payments_history
• creditcard_payments_history
• payment_bill_summaries_history
• payment_email_history

Query Audit Data by User Account Number

Displays data from all history tables which have a payer ID column. This query performs a select on each table where the payer ID matches Account Number, and whose time_stamp is between fromTime and toTime. The AccountNumber is the account number with the biller (payee_id column). The following tables are queried:

• check_payments_history
• creditcard_payments_history
• payment_bill_summaries_history
• recurring_payments_history

Query Audit Data by PID

Displays data from all the history tables which have a PID column. This query performs a select on each table where the PID matches and whose time_stamp is between fromTime and toTime. The following tables are queried:

• check_payments_history
• creditcard_payments_history
• payment_accounts_history
• recurring_payments_history

Setting Up a Query

Before running the queries, you must perform setup tasks.

To set up a query

1. Set the Oracle Self-Service E-Billing database connection parameters.
2. Configure TNS Listener for Oracle (Client/Server).
3. Configure DB2 Clients for Windows.
4. Check execution permissions for shell scripts.
5. Specify the Oracle Self-Service E-Billing database connection parameters.

Follow the configuration instructions for your operating system.

Configuring Windows

For Windows, you must edit set_isql_options.bat before running the queries. The file constrains the following line:

set ISQL_OPTIONS=-U <username> -P <password> -S <sqlsvr-Servername> -d <database name>

Edit this file and enter your values for username, password, server name and the Oracle Self-Service E-Billing database name. For example:

set ISQL_OPTIONS=-U edx1 -P edx1 -S EDXSERVER -d edxDB

Configuring UNIX

For UNIX, the database connection string is embedded in the file. You must edit the connection parameters in each file before running the queries. The connection parameters are as follows:

On Oracle:

sqlplus <username>/<password>@<TNS name>

For example:

sqlplus edx1/edxadmin@edxdb

TNS Listener for Oracle (Client/Server)

The TNS Listener has to be configured for the Oracle database in Windows and UNIX for client/server.

Permissions for UNIX

Grant execution permissions for shell scripts to run successfully. For example:

$ chmod 755 *.sh

Running the Queries in Windows and MSSQL

This topic describes how to run queries in Windows and MSSQL.

Querying Audit Data by Payment ID

Change your working directory to the location of the query script files, and run getAuditDataByPaymentId.bat. This file requires three parameters: Payment ID, From Timestamp, and To Timestamp. The execution format is:

getAuditDataByPaymentId Payment_ID, From Date, To Date

For example:

getAuditDataByPaymentId 123465564,'2011-01-01','2012-12-12'

where:

• Payment_ID is numeric.
• From Date and To Date are in YYYY-MM-DD format.

Querying Audit Data by Account

Change your working directory to the location of the query script files, and run getAuditDataByAccount.bat. This file requires three parameters: Account Number, From Timestamp, and To Timestamp. The execution format is:

getAuditDataByAccount Account_Number, From Date, To Date

For example:

getAuditDataByAccount '123465564','2011-01-01','2012-12-12'

where:

• Account_Number is a string.
• From Date and To Date are in YYYY-MM-DD format.

Querying Audit Data by PID

Change your working directory to the location of the query script files, and run getAuditDataByPid.bat. This file requires three parameters: PID, From Timestamp, and To Timestamp. The execution format is:

getAuditDataByPid PID, From Date, To Date

For example:

getAuditDataByPid '123465564','2011-01-01','2012-12-12'

where:

• PID is a string.
• From Date and To Date are in YYYY-MM-DD format.

Running the Queries in Oracle Database

This topic describes how to run queries in Oracle Database.

Querying Audit Data by Payment ID

Change your working directory to the location of the query script files, and run getAuditDataByPaymentId.bat. This file requires three parameters: Payment ID, From Timestamp, and To Timestamp. The execution format is:

getAuditDataByPaymentId Payment_ID, From Date, To Date

For example:

getAuditDataByPaymentId 123465564,'2011-01-01','2012-12-12'

where:

• Payment_ID is numeric.
• From Date and To Date are in YYYY-MM-DD format.

Querying Audit data by Account

Change your working directory to the location of the query script files, and run getAuditDataByAccount.bat. This file requires three parameters: Account Number, From Timestamp, and To Timestamp. The execution format is:

getAuditDataByAccount Account_Number,From Date, To Date

For example:

getAuditDataByAccount '123465564','2011-01-01','2012-12-12'

where:

• Account_Number is a string.
• From Date and To Date are in YYYY-MM-DD format.

Querying Audit Data by PID

Change your working directory to the location of the query script files, and run getAuditDataByPid.bat. This file requires three parameters: PID, From Timestamp, and To Timestamp. The execution format is:

getAuditDataByPid PID, From Date, To Date

For example:

getAuditDataByPid '123465564','2011-01-01','2012-12-12'

where:

• PID is a string.
• From Date and To Date are in YYYY-MM-DD format.

Running the Queries in UNIX

This topic explains running queries in UNIX and the Oracle Database.

Querying Audit Data by Payment ID

Change your working directory to the location of the query script files, and run getAuditInfoByPaymentId.sh. This file requires three parameters: Payment ID, From Timestamp, and To Timestamp. The execution format is:

$ ./getAuditInfoByPaymentId.sh Payment_ID, From Date, To Date

For example:

$ ./getAuditInfoByPaymentId.sh 123465564 '2011-01-01','2012-12-12'

where:

• Payment_ID is numeric.
• From Date and To Date are in YYYY-MM-DD format.
• Arguments are separated by spaces.

Querying Audit Data by Account

Change your working directory to the location of the query script files, and run getAuditInfoByAccount.sh. This file requires three parameters: Account Number, From Timestamp, and To Timestamp. The execution format is:

$ ./getAuditInfoByAccount.sh Account_Number, From Date, To Date

For example:

& ./getAuditInfoByAccount.sh '123465564' '2011-01-01','2012-12-12'

where:

• Account_Number is a string
• From Date and To Date are in YYYY-MM-DD format
• Arguments are separated by spaces

Query Audit Data by PID

Change your working directory to the location of the query script files, and run getAuditInfoByPid.sh. This file requires three parameters: PID, From Timestamp, and To Timestamp. The execution format is:

$ ./getAuditInfoByPid.sh PID, From Date, To Date

For example:

$ ./getAuditInfoByPid '123465564' '2011-01-01','2012-12-12'

where:

• PID is a string.
• From Date and To Date are in YYYY-MM-DD format.
• Arguments are separated by spaces.

Change your working directory to the location of the query script files, and run getAuditDataByPid.sh. This file requires three parameters: PID, From Timestamp, and To Timestamp.

Audit Database

The Oracle Self-Service E-Billing database supports auditing.

Columns for Audit

The following tables have the new columns:

• check_payments_history
• creditcard_payments_history

The history tables have all the columns that the base table has (check_payments and creditcard_payments) plus the columns listed in Table 73.

New Tables

The following tables are based on the table name with _history at the end. They have all the columns in the base table, plus the new columns listed in Table 73 to support auditing.

• payment_accounts_history
• payment_bill_summeries_history
• payment_reminder_history
• recurring_payments_history

payment_email_history

This table is new, and not based on a previous table. It has the columns listed in Table 74 plus the columns listed in the preceding table to support auditing.

Table 75 lists the possible values for email types and description.

Audit Table Constants

Table 76 lists the tables that have audit information and the names of the corresponding code tables that explain the numeric codes for audit columns. See the tables in your Oracle Self-Service E-Billing database for the code descriptions.

Job Name Entries

User job names are combined with a shortened version of the task name to keep database entries manageable. The name of the job given by the user is combined with a shortened version of the task name as follows:

<job name given by the Admin>-<shorten task name>

Table 77 shows the shortened name for each job.