Implementation Guide for Oracle Self-Service E-Billing > About Payment Processing > About Recurring Payments >
Example of Scheduling Amount Due and Before Due Date
This topic shows an example of how a recurring payment processes for amount due, before the due date. You could use this feature differently, depending on your business model. To schedule Amount Due and Before 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.
Column Name in the recurring_payments Table |
|
payer_account_number |
acct1111 |
bill_scheduled |
Y |
status |
active |
last_process_time |
04/10/2009; Same as start date. |
last_pay_date |
01/01/1970; Not 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 will only be deactivated when the pay date is after the end date. |
- The pmtRecurringPayment job runs on 04/10/2009 23:59:00PM. The job searches the recurring_payments table to find all recurring payments whose bill_scheduled is Y and status is Active. It finds the example recurring payment and then asks Command Center 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. pmtRecurringPayment then finds the bill with latest due date bill3. bill2 is ignored because only the latest bill is paid.
- After finding the latest bill from Command Center, pmtRecurringPayment checks whether the due date of this bill is after the due date of the bill used in the last payment (last bill information can be retrieved from payment_bill_summaries using the bill_id). If not, that means this is an old bill and must not be paid. In this case, because there is no last payment, bill3 is paid.
- bill3 is inserted into the payment_bill_summaries table and the recurring_payment table is recalculated as follows:
|
|
payer_account_number |
acct1111 |
bill_scheduled |
N, means this bill has not been paid or scheduled |
status |
active, because next_pay_date is within the effective period |
last_process_time |
04/10/2009 23:59:00PM, changes to job run time |
last_pay_date |
01/01/1970, unchanged |
next_pay_date |
05/14/2009, 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 do 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 will not be 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 to:
|
|
payer_account_number |
acct1111 |
bill_scheduled |
Y, means this bill has been paid |
status |
Active because next_pay_date is within the effective period |
last_process_time |
04/10/2009 23:59:00PM, unchanged because there was no synchronization |
last_pay_date |
05/14/2009, change to check's pay date |
next_pay_date |
05/14/2009, unchanged |
bill_id |
bill3 |
payment_id |
points to 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. He or she can update or cancel the scheduled payment if desired.
- On 05/12/2009 23:59:00PM, pmtRecurringPayment runs again and finds bills whose doc date is 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 job runs again and inserts a new bill, bill4. ACCOUNT_NUM is obtained from the EDX_RPT_ACCOUNT_DIM table from OLAP. Everything else is from the EDX_RPT_STATMENT_FACT table. The table details are a combination of data from EDX_RPT_ACCOUNT_DIM, EDX_RPT_ACCOUNT_FACT, and the EDX_RPT_STATEMENT_FACT tables.
|
Z_DOC_ID-STATEMENT_NUMBER |
Z_DOC_DATE-STATEMENT_LOAD_DATE |
|
|
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. It contacts Command Center and retrieves bills whose doc date are between 05/12/2009 23:59:00PM and 05/13/2009 23:59:00PM. bill4 is retrieved and the recurring_payments table is updated like this:
|
|
payer_account_number |
acct1111 |
bill_scheduled |
N means this bill has not been paid |
status |
Inactive because next_pay_date is beyond the effective period |
last_process_time |
05/15/2009 23:59:00PM, changes to job run time |
last_pay_date |
05/14/2009, unchanged |
next_pay_date |
06/14/2009, one day before due date, 06/15/2009 |
bill_id |
bill4 |
After synchronization, the recurring payment is deactivated, and is never synchronized or scheduled again.
Example of Scheduling Amount Due And Fixed Pay Date
This topic shows an example of how a recurring payment processes for the amount sue scheduled on a fixed pay date. You could use this feature differently, depending on your business model. To schedule Amount Due and Fixed Pay Date
- On 04/09/2009, a customer with account number acct1111 creates a recurring payment. The amount is 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.
|
|
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; the first available pay date after 04/10/2009 (because there is no April 31). |
bill_id |
null |
end_date |
01/01/3000; The end date is so far in the future that the recurring payment will only be deactivated when the number of payments reaches maximum allowed. |
curr_num_payments |
0; no payments yet. |
The Bill table has the following values:
|
Z_DOC_ID-STATEMENT_NUMBER |
Z_DOC_DATE-STATEMENT_LOAD_DATE |
|
|
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 |
Even though the pay date is not related to the due date, DueDate must still be indexed because it is used to decide which bill is the latest.
- The pmtRecurringPayment job runs on 04/10/2009 23:59:00PM. bill3 is found in the index table and inserted into the payment_bill_summaries table. The recurring_payments table is recalculated as follows:
|
|
payer_account_number |
acct1111 |
bill_scheduled |
N, this bill has not been paid. |
status |
Active, curr_num_payments is less than max_num_payments. |
last_process_time |
01/01/1970; unchanged. |
last_pay_date |
01/01/1970; unchanged. |
next_pay_date |
04/30/2009; there is no April 31. |
bill_id |
bill3 |
curr_num_payments |
0 |
- On 04/27/2009, three days before next_pay_date, pmtRecurringPayment 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 its pay date is 04/30/2009. The recurring payment table is changed as follows:
|
|
payer_account_number |
acct1111 |
bill_scheduled |
Y, means this bill has been paid. |
status |
Active, curr_num_payments is less than max_num_payments. |
last_process_time |
04/10/2009 23:59:00PM: not changed because there has been no synchronization. |
last_pay_date |
04/30/2009; changed to next_pay_date. |
next_pay_date |
05/31/2009; changed to next available pay date. |
bill_id |
bill3 |
payment_id |
Points to the new payment_id inserted into the check_payments or creditcard_payments table. |
curr_num_payments |
1 |
- Repeat steps 2, 3 and 4 until curr_num_payments reaches 10. At step 4 of the tenth payment, the status changes to Inactive.
If no bills arrive for a month, then next_pay_date is automatically moved to next month. For example, if there is no bill for April, then the next_pay_date is automatically moved from 04/30/2009 to 05/31/2009 when the current job run time is May 1. Example of Scheduling Fixed Amount and Before Due Date
This topic shows an example of how a recurring payment processes for a fixed amount scheduled before the due date. You could use this feature differently, depending on your business model. To schedule Fixed Amount and Before Due Date
- On 04/09/2009, a customer with account number as acct1111 creates a recurring payment from the UI. 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 10 payments.
|
|
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/300 |
bill_id |
null |
end_date |
01/01/3000; the end date is so far in the future that the recurring payment will only be deactivated when the number of payments reaches the maximum allowed. |
curr_num_payments |
0; no payment yet. |
Index table entries are as follows:
|
Z_DOC_ID-STATEMENT_NUMBER |
Z_DOC_DATE-STATEMENT_LOAD_DATE |
|
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 running the ETL load and after the new bill has been inserted. In this case, bill3 is found in the index table and inserted into the payment_bill_summaries table. bill3 details are retrieved from the OLAP database tables and inserted into the payment_bill_summaries table. The recurring_payments table is recalculated as follows:
|
|
payer_account_number |
acct1111 |
bill_scheduled |
N; this bill has not been paid. |
status |
Active, curr_num_payments is less than max_num_payments. |
last_process_time |
04/10/2009 23:59:00PM; changes to job run time. |
last_pay_date |
01/01/1970; unchanged. |
next_pay_date |
05/14/2009; one day before due date, 05/15/2009. |
bill_id |
bill3 |
curr_num_payments |
0 |
- On 05/11/2009, three days before next_pay_date, pmtRecurringPayment 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 is changed as follows:
|
|
payer_account_number |
acct1111 |
bill_scheduled |
Y means this bill has been paid. |
status |
Active, next_pay_date is not after end_date. |
last_process_time |
04/10/2009 23:59:00PM; unchanged, because there was no synchronization. |
last_pay_date |
05/11/2009; changed to next_pay_date. |
next_pay_date |
05/11/2009; unchanged, the next bill is not known. |
bill_id |
bill3 |
payment_id |
Points to the new payment_id inserted into the check_payments or creditcard_payments table. |
curr_num_payments |
1 |
Repeat steps 2, 3 and 4 until next_pay_date is after end_date, when status changes to inactive. Example of Scheduling Fixed Amount and Fixed Pay Date
This topic shows an example of how a recurring payment processes for a fixed amount scheduled on a fixed pay date. You could use this feature differently, depending on your business model. To schedule Fixed Amount and 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 follows:
|
|
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 yet. |
- On 04/28/2009, three days before next_pay_date, pmtRecurringPayment runs again. There is no synchronization (bill_scheduled is always N) but 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 follows:
|
|
payer_account_number |
acct1111 |
bill_scheduled |
N; this bill has been paid. |
status |
Active, next_pay_date is not after end_date. |
last_process_time |
04/10/2009; unchanged, because there was no synchronization. |
last_pay_date |
05/01/2009; changed to next_pay_date. |
next_pay_date |
06/01/2009; changed to the next available pay date. |
bill_id |
null |
payment_id |
Points to the new payment_id inserted into the check_payments or creditcard_payments table |
curr_num_payments |
1 |
Repeat step 2 until next_pay_date is after end_date. Then the status changes to Inactive.
|