|
Administration Guide for Oracle Self-Service E-Billing > Recurring Payments >
Examples of Recurring Payment
This topic describes four examples of recurring payment with additional details about the relevant database interactions. Example of Amount Due and Before Due Date
This example shows how Oracle Self-Service E-Billing processes a recurring payment using the amount due before the due date.
- On date 04/09/2009, a customer with account number acct1111 creates a recurring payment. The amount is amount due, the pay date is one day before due date, the start date is 04/10/2009, and the end date is 06/10/2009. The following table gives an example of a recurring payment created with Amount Due and Before Due Date.
|
|
payer_account_number |
acct1111 |
bill_scheduled |
Y |
status |
Active |
last_process_time |
04/10/2009. This value is the same as start date |
last_pay_date |
01/01/1970. This value means the bill has not been paid yet. |
next_pay_date |
01/01/3000. This future date ensures there is no due date available yet. |
bill_id |
Null. |
max_num_payments |
2147483647. This large number means the recurring payment is only deactivated when the pay date is after the end date |
- The Oracle Self-Service E-Billing ETL load runs and indexes one bill (the doc ID is bill1, in this example) on 03/10/2009. On 04/10/2009, the ETL load runs again and indexes two more bills: bill2 and bill3. The following table gives an example of ETL load producing bills (bill1, bill2, and bill3); it is a combination of the edx_rpt_account_dim,edx_rpt_account_fact, and edx_rpt_statement_fact tables from the OLAP database.
|
|
|
|
|
acct1111 |
bill1 |
03/10/2009 |
100.01 |
04/15/2009 |
acct1111 |
bill2 |
04/10/2009 |
50.00 |
04/25/2009 |
acct1111 |
bill3 |
04/10/2009 |
100.00 |
05/15/2009 |
- The pmtRecurringPayment job runs on 04/10/2009 23:59:00PM, after the ETL load. The job searches the recurring_payments table to find all recurring payments whose bill_scheduled is Y and status is active. The job finds the example recurring payment and then asks Oracle Self-Service E-Billing to return all bills whose account number is acct1111 and whose STATEMENT_LOAD_DATE is between 04/10/2009 (last_process_time) and 04/10/2009 23:59:00PM (job run time). Two bills, bill2 and bill3, are returned. The pmtRecurringPayment job then finds the bill with latest due date bill3 and bill2 is ignored because only the latest bill is paid.
- After finding the latest bill from Oracle Self-Service E-Billing, the pmtRecurringPayment job checks whether the due date of this bill is after the due date of the bill used in the last payment (last bill info can be retrieved from payment_bill_summaries using the bill_id). If not, it indicates that it is an old bill and must not be paid. In this case, since there is no last payment, the bill, bill3 is paid.
- Bill3 is inserted into the payment_bill_summaries table and the recurring_payment table is recalculated as shown in the following table.
|
|
payer_account_number |
acct1111 |
bill_scheduled |
N. This bill has not been paid or scheduled. |
status |
Active. The next pay date is within the effective period. |
last_process_time |
04/10/2009 23:59:00PM. This value changes to the job run time. |
last_pay_date |
01/01/1970, unchanged |
next_pay_date |
05/14/2009. This value is one day before the due date, 05/15/2009. |
bill_id |
bill3 |
- If the pmtRecurringPayment job runs between 04/11/2009 and 05/10/2009, nothing happens to this recurring payment because synchronization and scheduling does not happen. The table remains unchanged.
- On 05/11/2009 11:59:00PM, three days before next_pay_date, pmtRecurringPayment runs again. The recurring payment mentioned previously is not synchronized, because its bill_scheduled is N. However, it will be scheduled. pmtRecurringPayment finds all recurring payments whose bill_scheduled is N, status is active and next_pay_date is equal to or before 05/14/2009 (05/11/2009 + 3 days). The previously mentioned recurring payment is picked up and a payment is inserted into the check_payments or creditcard_payments table. The amount of the payment is $100.00, and the pay date is 05/14/2009. After this, the recurring payment table is changed, as described in the following table.
|
|
payer_account_number |
acct1111 |
bill_scheduled |
Y. The bill has been paid. |
status |
Active. The next pay date is within the effective period. |
last_process_time |
04/10/2009 23:59:00PM. This value is unchanged since there was no synchronization. |
last_pay_date |
05/14/2009. This date changes to the check pay date. |
next_pay_date |
05/14/2009. This value is unchanged. |
bill_id |
bill3 |
payment_id |
The new payment ID inserted into the check_payments or creditcard_payments table. |
The customer can now view the payment from Future Payments in the example interface and update or cancel the scheduled payment.
- On 05/12/2009 23:59:00PM, the pmtRecurringPayment job runs again and finds bills with a doc date between 04/10/2009 11:59:00PM and 05/12/2009 23:59:00PM. No bills exist, and the last process time is updated to 05/12/2009 23:59:00PM. Everything else remains the same.
- On 05/13/2009, the ETL load occurs again and inserts a new bill, bill4, as shown in the following table. This bill is a combination of the edx_rpt_account_dim, edx_rpt_account_fact, and edx_rpt_statement_fact tables from the OLAP database.
Value in the ACCOUNT_NUM Column |
Value in the STATEMENT_NUMBER Column |
Value in the STATEMENT_LOAD_DATE Column |
Value in the AmountDue Column |
Value in the DueDate Column |
acct1111 |
bill1 |
03/10/2009 |
100.01 |
04/15/2009 |
acct1111 |
bill2 |
04/10/2009 |
50.00 |
04/25/2009 |
acct1111 |
bill3 |
04/10/2009 |
100.00 |
05/15/2009 |
acct1111 |
bill4 |
05/13/2009 |
80.00 |
06/15/2009 |
- On 05/13/2009 23:59:00PM, the pmtRecurringPayment job runs again, and retrieves bills with dates between 05/12/2009 23:59:00PM and 05/13/2009 23:59:00PM. In this case, the job retrieves bill4 and updates the recurring_payments table, as shown in the following table.
|
|
payer_account_number |
acct1111 |
bill_scheduled |
N. The bill has not been paid. |
status |
Inactive, because the next pay date is beyond the effective period. |
last_process_time |
05/15/2009 23:59:00PM. The value changes to the job run time. |
last_pay_date |
05/14/2009. This value is unchanged. |
next_pay_date |
06/14/2009. This value is one day before the due date, 06/15/2009. |
bill_id |
bill4 |
After synchronization, the recurring payment is deactivated, and it will never be synchronized or scheduled again.
Example of Amount Due and Fixed Pay Date
This example shows how Oracle Self-Service E-Billing processes a recurring payment using the amount due on a fixed pay date.
- On 04/09/2009, a customer with account number acct1111 creates a recurring payment. The amount is the amount due, the pay date is day 31 of each month, the start date is 04/10/2009, and the recurring payment stops after 10 payments, as shown in the following table.
|
|
payer_account_number |
acct1111 |
bill_scheduled |
Y |
status |
Active |
last_process_time |
04/10/2009 |
last_pay_date |
01/01/1970 |
next_pay_date |
4/30/2009. This is the first available pay date after 04/10/2009; there is no 31st of April. |
bill_id |
Null |
end_date |
01/01/3000. The end date is so far into the future that the recurring payment will only be deactivated when the number of payments reaches the maximum allowed. |
curr_num_payments |
0. No payments have been made yet. |
The following table shows an example of an ETL load that produces three bills (bill1, bill2, and bill3). This table is a combination of the edx_rpt_account_dim, edx_rpt_account_fact, and edx_rpt_statement_fact tables from the OLAP database.
Value in the ACCOUNT_NUM Column |
Value in the STATEMENT_NUMBER Column |
Value in the STATEMENT_LOAD_DATE Column |
Value in the AmountDue Column |
Value in the DueDate Column |
acct1111 |
bill1 |
03/10/2009 |
100.01 |
04/15/2009 |
acct1111 |
bill2 |
04/10/2009 |
50.00 |
04/25/2009 |
acct1111 |
bill3 |
04/10/2009 |
100.00 |
05/15/2009 |
Although the pay date is not related to the due date, the value in the DueDate column is indexed because it is used to determine which bill is the latest.
- The pmtRecurringPayment job runs on 04/10/2009 23:59:00PM, after the ETL load. The load finds bill3 from OLAP database tables edx_rpt_account_dim, edx_rpt_account_fact, and edx_rpt_statement_fact and inserted in the payment_bill_summaries table.
The recurring_payments table is recalculated as shown in the following table.
|
|
payer_account_number |
acct1111 |
bill_scheduled |
N. This bill has not been paid. |
status |
Active. The value in the curr_num_payments column is less than the value in the max_num_payments column. |
last_process_time |
04/10/2009 23:59:00PM. This column changed to the job run time. |
last_pay_date |
01/01/1970. This value is unchanged. |
next_pay_date |
04/30/2009. There is no 31st of April. |
bill_id |
bill3 |
curr_num_payments |
0 |
- On 04/27/2001, three days before the next pay date, the pmtRecurringPayment job runs again. There is no synchronization (bill_scheduled is N), but a payment is inserted into the check_payments or creditcard_payments table. The amount of the check is $100.00 and the pay date is 04/30/2001. Changes to the recurring payment table are shown here.
|
|
payer_account_number |
acct1111 |
bill_scheduled |
Y. The bill has been paid. |
status |
Active. The value in the curr_num_payments column is less than the value in the max_num_payments column. |
last_process_time |
04/10/2009 23:59:00PM. This value is unchanged since there has been no synchronization. |
payer_account_number |
acct1111 |
bill_scheduled |
Y. The bill has been paid. |
status |
Active. The value in the curr_num_payments column is less than the value in the max_num_payments column. |
last_process_time |
04/10/2009 23:59:00PM. This value is unchanged since there has been no synchronization. |
- Steps 2, 3, and 4 repeat until the value of the curr_num_payments column reaches 10. At Step 4 of the tenth payment, the status changes to inactive.
If no bills arrive for a month, then the next pay date automatically moves to the next month. For example, if there is no bill for April, then the next pay date automatically moves from 04/30/2009 to 05/31/2009 when the current job run time is May First.
Example of Fixed Amount and Before Due Date
This example shows how Oracle Self-Service E-Billing processes a recurring payment using a fixed amount before the due date.
- On 04/09/2009, a customer with account number as acct1111 creates a recurring payment. The amount is $50, the pay date is one day before the due date, the start date is 04/10/2009 and the recurring payment stops after ten payments, as shown in the following table.
|
|
payer_account_number |
acct1111 |
bill_scheduled |
Y |
status |
Active |
last_process_time |
04/10/2009 |
last_pay_date |
01/01/1970 |
next_pay_date |
01/01/3000 |
bill_id |
Null |
end_date |
01/01/3000. The end date is so far into the future that the recurring payment will only be deactivated when the number of payments reaches the maximum allowed. |
curr_num_payments |
0. No payments have been made yet. |
The following table shows the Index table entries, which are a combination of the edx_rpt_account_dim, edx_rpt_account_fact, and edx_rpt_statement_fact tables from the OLAP database.
Value in the ACCOUNT_NUM Column |
Value in the STATEMENT_NUMBER Column |
Value in the STATEMENT_LOAD_DATE Column |
Value in the AmountDue Column |
acct1111 |
bill1 |
03/10/2009 |
04/15/2009 |
acct1111 |
bill2 |
04/10/2009 |
04/25/2009 |
acct1111 |
bill3 |
04/10/2009 |
05/15/2009 |
Amount due is not required for this case.
- The pmtRecurringPayment job runs on 04/10/2009 23:59:00PM, after the ETL load. In this case, bill3 is found in the index table and inserted into the payment_bill_summaries table. The values in the recurring payments table is recalculated as listed in the following table.
|
|
payer_account_number |
acct1111 |
bill_scheduled |
N. The bill has not been paid. |
status |
Active. The value in the curr_num_payments is less than the value in the max_num_payments column. |
last_process_time |
04/10/2009 23:59:00PM. This value changes to the job run time. |
last_pay_date |
01/01/1970. The value is unchanged. |
next_pay_date |
05/14/2009. This value is one day before the due date, 05/15/2009. |
bill_id |
bill3 |
curr_num_payments |
0 |
- On 05/11/2009, three days before the next pay date, the pmtRecurringPayment job runs again. There is no synchronization because bill_scheduled is N, but a payment is inserted into the check_payments or creditcard_payments table. The amount of the payment is $50.00 and its pay date is 05/14/2009. The recurring_payments table changes as shown in the following table.
|
|
payer_account_number |
acct1111 |
bill_scheduled |
Y. The bill has been paid. |
status |
Active. The value in the next_pay_date column is not after the value in the end_date column. |
last_process_time |
04/10/2009 23:59:00PM. This value is unchanged since there was no synchronization. |
last_pay_date |
05/11/2009. This value changes to the value in the next_pay_date column. |
next_pay_date |
05/11/2009. This value is unchanged, the next bill is not known. |
bill_id |
bill3 |
payment_id |
The new payment ID inserted into the check_payments or creditcard_payments table. |
curr_num_payments |
1 |
- Steps 2, 3, and 4 repeat until next_pay_date is after end_date, when status changes to inactive.
Example of Fixed Amount and Fixed Pay Date
This example shows how Oracle Self-Service E-Billing processes a recurring payment using a fixed amount and a fixed pay date.
- On 04/09/2009, a customer with account number acct1111 creates a recurring payment. The amount is $50 and the pay date is day 1 of each month. The recurring payment starts at 04/10/2009 and ends at 06/10/2009. The columns in the recurring_payments table are updated, as shown in the following table.
|
|
payer_account_number |
acct1111 |
bill_scheduled |
N |
status |
active |
last_process_time |
04/10/2009 |
last_pay_date |
01/01/1970 |
next_pay_date |
05/01/2009 |
bill_id |
Null |
end_date |
06/10/2009 |
curr_num_payments |
0. No payment has been made yet. |
- On 04/28/2009, three days before the next pay date, the pmtRecurringPayment job runs again. There is no synchronization because the value in the bill_scheduled column is always N, however a payment is inserted into the check_payments or creditcard_payments table. The amount of the check is $50.00 and its pay date is 05/01/2009.
The columns in the recurring_payments table are updated, as shown in the following table.
|
|
payer_account_number |
acct1111 |
bill_scheduled |
N. This bill has been paid. |
status |
Active. The value in the next_pay_date column is not after the value in the end_date column. |
last_process_time |
04/10/2009. This value is unchanged since there was no synchronization. |
last_pay_date |
05/01/2009. This value changes to the next pay date. |
next_pay_date |
06/01/2009. This value changes to the next available pay date. |
bill_id |
Null |
payment_id |
The payment ID inserted into the check_payments or creditcard_payments table. |
curr_num_payments |
1 |
- This step repeats until the next pay date is after the end date, when the status changes to inactive.
|
