Implementation Guide for Oracle Self-Service E-Billing > Customizing Payment >
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.
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:
- 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).
|
|
|
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. |
- 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).
|
|
|
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. |
- 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).
|
|
|
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. |
- 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).
|
|
|
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. |
- 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).
|
|
|
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. |
- 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).
|
|
|
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. |
- 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).
|
|
|
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. |
- 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:
|
|
|
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. |
- 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:
|
|
|
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. |
- 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).
|
|
|
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. |
- 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).
|
|
|
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. |
- 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:
|
|
|
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
- Set the database connection parameters.
- Configure TNS Listener for Oracle (Client/Server).
- Configure DB2 Clients for Windows.
- Check execution permissions for shell scripts.
- Specify 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 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,'2008-01-01','2009-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','2008-01-01','2009-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','2008-01-01','2009-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,'2008-01-01','2009-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','2008-01-01','2009-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','2008-01-01','2009-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 '2008-01-01' '2009-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' '2008-01-01' '2009-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' '2008-01-01' '2009-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 Payment 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 91.
Table 91. Additional Columns in History Tables
|
|
audit_operation |
Defined in corresponding constant tables |
audit_status |
Defined in corresponding constant tables |
audit_reason |
Description of the audit |
job_id |
Pwc job ID |
job_name |
User given job name (see Job Name Entries) |
time_stamp |
The record insertion time. For example: 1/18/2004 11:47:38 AM |
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 91 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 92 plus the columns listed in the preceding table to support auditing.
Table 92. Payment Email History Table Columns
|
|
type |
This indicates the purpose of the email. Possible values are listed in Table 93. |
payee id |
DDN |
payer_id |
User ID |
account_number |
Check or credit card number |
payment_id |
Payment ID |
to_address |
Receivers email address. If there are multiple addresses, they will be separated by a semicolon. |
content |
Content; Length of the email content must be truncated based on the Email Content Audit Length configuration parameter. |
audit_operation |
Defined in corresponding constant tables |
audit_status |
Defined in corresponding constant tables |
audit_reason |
Description of the audit |
job_id |
Pwc job ID |
job_name |
User given job name (see Job Name Entries) |
time_stamp |
The record insertion time. For example: 1/18/2004 11:47:38 AM |
to_address |
Receivers email address. If there are multiple addresses, they will be separated by a semicolon. |
Table 93 lists the possible values for email types and description.
Table 93. Email Types
|
|
0 |
Unknown email type. |
1 |
A fixed date payment reminder email. |
2 |
Before due date payment reminder email. |
3 |
After due date payment reminder email. |
4 |
Check status notification email. |
5 |
Credit card status notification email. |
6 |
Recurring payment cancelled email. |
7 |
Recurring payment scheduled email. |
8 |
Payment account status notification email. |
9 |
Credit card expiration notification email. |
Audit Table Constants
Table 94 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 Payment database for the latest descriptions for each code.
Table 94. Audit Table Constants
|
|
credit_card_const |
creditcard_payments_history |
check_const |
check_payments_history |
recurring_payment_const |
recurring_payment_history |
payment_email_const |
payment_email_history |
payment_bill_summaries_const |
payment_bill_summaries history |
payment_account_const |
payment_accounts_history |
payment_reminders_const |
payment_reminders_history |
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 95 shows the shortened name for each job.
Table 95. Job Name Entries
|
|
CheckSubmitTask |
ChkSubTsk |
CheckUpdateTask |
ChkUpdTsk |
PaymentIntegratorTask |
PmtIntTsk |
CreditCardExpNotifyTask |
CCExpNTsk |
CreditCardSubmitTask |
CCSubTsk |
CreditCardUpdateTask |
CCUpdTsk |
ConfirmEnrollTask |
ConEnrTsk |
NotifyEnrollTask |
NotEnrTsk |
RecurPaymentSchedulerTask |
RcuSchTsk |
RecurPaymentSynchronizerTask |
RcuSynTsk |
PaymentReminderTask |
PmtRmdTsk |
SubmitEnrollTask |
SubEnrTsk |
CustomTask |
CustomTsk |
|