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

1. 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	Value
   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.

2. 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.
3. 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.
4. bill3 is inserted into the payment_bill_summaries table and the recurring_payment table is recalculated as follows:

   Column Name	Value
   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

5. 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.
6. 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:

   Column Name	Value
   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.

7. 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.
8. 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_PRIMARY-ACCOUNT_NUM	Z_DOC_ID-STATEMENT_NUMBER	Z_DOC_DATE-STATEMENT_LOAD_DATE	AmountDue	DueDate
   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

9. 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:

   Column Name	Value
   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

1. 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.

   Column Name	Value
   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_PRIMARY-ACCOUNT_NUM	Z_DOC_ID-STATEMENT_NUMBER	Z_DOC_DATE-STATEMENT_LOAD_DATE	AmountDue	DueDate
   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.

2. 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:

   Column Name	Value
   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

3. 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:

   Column Name	Value
   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

4. 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

1. 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.

   Column Name	Value
   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_PRIMARY-ACCOUNT_NUM	Z_DOC_ID-STATEMENT_NUMBER	Z_DOC_DATE-STATEMENT_LOAD_DATE	DueDate
   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.

2. 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:

   Column Name	Value
   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

3. 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:

   Column Name	Value
   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

1. 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:

   Column Name	Value
   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.

2. 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:

   Column Name	Value
   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.