Administration Guide for Oracle Billing Insight > 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 Billing Insight processes a recurring payment using the amount due before the due date.

1. On date 04/09/2012, 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/2012, and the end date is 06/10/2012. The following table gives an example of a recurring payment created with Amount Due and Before Due Date.

   Column Name	Value
   payer_account_number	act1111
   bill_scheduled	Y
   status	Active
   last_process_time	04/10/2012. 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.

2. A billing load processes and indexes one bill on 03/10/2012. The doc ID is bill1, in this example. On 04/10/2012, the data load runs again and indexes two more bills: bill2 and bill3. The following table gives an example of data load producing bills, and is a combination of the edx_rpt_account_dim,edx_rpt_account_fact, and edx_rpt_statement_fact tables from the OLAP database.

   ACCOUNT_NUM	STATEMENT_NUMBER	STATEMENT_LOAD_DATE	AmountDue	DueDate
   acct1111	bill1	03/10/2012	100.01	04/15/2012
   acct1111	bill2	04/10/2012	50.00	04/25/2012
   acct1111	bill3	04/10/2012	100.00	05/15/2012

3. The pmtRecurringPayment job runs on 04/10/2012 23:59:00PM, after the billing data 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 Billing Insight to return all bills whose account number is acct1111 and whose STATEMENT_LOAD_DATE is between 04/10/2012 the value of last_process_time, and 04/10/2012 23:59:00PM, the 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.
4. After finding the latest bill from Oracle Billing Insight, the pmtRecurringPayment job checks whether the due date of this bill is after the due date of the bill used in the last payment. The last bill info can be retrieved from payment_bill_summaries using the bill_id. Otherwise, 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.
5. Bill3 is inserted into the payment_bill_summaries table and the recurring_payment table is recalculated as shown in the following table.

   Column Name	Value
   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/2012 23:59:00PM. This value changes to the job run time.
   last_pay_date	01/01/1970, unchanged
   next_pay_date	05/14/2012. This value is one day before the due date, 05/15/2012.
   bill_id	bill3

6. If the pmtRecurringPayment job runs between 04/11/2012 and 05/10/2012, then nothing happens to this recurring payment because synchronization and scheduling does not happen. The table remains unchanged.
7. On 05/11/2012 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/2012, which is 05/11/2012 + three 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/2012. After this, the recurring payment table is changed, as described in the following table.

   Column Name	Value
   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/2012 23:59:00PM. This value is unchanged since there was no synchronization.
   last_pay_date	05/14/2012. This date changes to the check pay date.
   next_pay_date	05/14/2012. 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.

8. On 05/12/2012 23:59:00PM, the pmtRecurringPayment job runs again and finds bills with a doc date between 04/10/2012 11:59:00PM and 05/12/2012 23:59:00PM. No bills exist, and the last process time is updated to 05/12/2012 23:59:00PM. Everything else remains the same.
9. On 05/13/2012, the biling data 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/2012	100.01	04/15/2012
   acct1111	bill2	04/10/2012	50.00	04/25/2012
   acct1111	bill3	04/10/201	100.00	05/15/2012
   acct1111	bill4	05/13/2012	80.00	06/15/2012

10. On 05/13/2012 23:59:00PM, the pmtRecurringPayment job runs again, and retrieves bills with dates between 05/12/2012 23:59:00PM and 05/13/2012 23:59:00PM. In this case, the job retrieves bill4 and updates the recurring_payments table, as shown in the following table.

    Column Name	Value
    payer_account_number	act1111
    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/2012 23:59:00PM. The value changes to the job run time.
    last_pay_date	05/14/2012. This value is unchanged.
    next_pay_date	06/14/2012. This value is one day before the due date, 06/15/2012.
    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 Billing Insight processes a recurring payment using the amount due on a fixed pay date.

1. On 04/09/2012, 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/2012, and the recurring payment stops after 10 payments, as shown in the following table.

   Column Name	Value
   payer_account_number	acct1111
   bill_scheduled	Y
   status	Active
   last_process_time	04/10/2012
   last_pay_date	01/01/1970
   next_pay_date	4/30/2012. This is the first available pay date after 04/10/2012. 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 a billing data load that produces three bills. 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/2012	100.01	04/15/2012
   acct1111	bill2	04/10/2012	50.00	04/25/2012
   acct1111	bill3	04/10/2012	100.00	05/15/2012

   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.

2. The pmtRecurringPayment job runs on 04/10/2012 23:59:00PM, after the billing data 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.

   Column Name	Value
   payer_account_number	acct111
   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/2012 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/2012. There is no 31st of April.
   bill_id	bill3
   curr_num_payments	0

3. On 04/27/2012, three days before the next pay date, the pmtRecurringPayment job runs again. There is no synchronization, since the value of 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/2012. Changes to the recurring payment table are shown here.

   Column Name	Value
   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/2012 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/2012 23:59:00PM. This value is unchanged since there has been no synchronization.

4. Step 1, Step 2, and Step 3 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/2012 to 05/31/2012 when the current job run time is May First.

Example of Fixed Amount and Before Due Date

This example shows how Oracle Billing Insight processes a recurring payment using a fixed amount before the due date.

1. On 04/09/2012, 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/2012 and the recurring payment stops after ten payments, as shown in the following table.

   Column Name	Value
   payer_account_number	acct1111
   bill_scheduled	Y
   status	Active
   last_process_time	04/10/2012
   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/2012	04/15/2012
   acct1111	bill2	04/10/2012	04/25/2012
   acct1111	bill3	04/10/2012	05/15/2012

   Amount due is not required for this case.

2. The pmtRecurringPayment job runs on 04/10/2012 23:59:00PM, after the billing data 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.

   Column Name	Value
   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/2012 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/2012. This value is one day before the due date, 05/15/2012.
   bill_id	bill3
   curr_num_payment	0

3. On 05/11/2012, 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/2012. The recurring_payments table changes as shown in the following table.

   Column Name	Value
   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/2012 23:59:00PM. This value is unchanged since there was no synchronization.
   last_pay_date	05/11/2012. This value changes to the value in the next_pay_date column.
   next_pay_date	05/11/2012. 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

4. Step 1, Step 2, and Step 3 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 Billing Insight processes a recurring payment using a fixed amount and a fixed pay date.

1. On 04/09/2012, 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/2012 and ends at 06/10/2012. The columns in the recurring_payments table are updated, as shown in the following table.

   Column Name	Value
   payer_account_number	acct1111
   bill_scheduled	N
   status	active
   last_process_time	04/10/2012
   last_pay_date	01/01/1970
   next_pay_date	05/01/2012
   bill_id	Null
   payment_id	06/10/2012
   curr_num_payments	0. No payment has been made yet.

2. On 04/28/2012, 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/2012.

   The columns in the recurring_payments table are updated, as shown in the following table.

   Column Name	Value
   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/2012. This value is unchanged since there was no synchronization.
   last_pay_date	05/01/2012. This value changes to the next pay date.
   next_pay_date	06/01/2012. 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

3. This step repeats until the next pay date is after the end date, when the status changes to Inactive.