Projects:Editable Payment Plan/Technical Documentation

Contents 1 Editable Payment Plan 1.1 Overview 1.2 Concepts 1.3 Data Model 1.4 Creation of a Payment Plan 1.4.1 Current Flow 1.4.2 To Develop 1.4.3 Example. Step 1 1.5 Modification of a Payment Plan 1.5.1 To Develop 1.5.2 Example. Step 2 1.6 Payment completed against Payment Plan line/s 1.6.1 Full Payment 1.6.1.1 Current Flow 1.6.1.2 To Develop 1.6.2 Example. Step 3 1.6.3 Partial Payment 1.6.3.1 Current Flow 1.6.3.2 To Develop 1.6.4 Example. Step 4 1.6.5 Example. Step 5 1.7 Payment reactivation 1.8 Development Phases and Status 1.8.1 Phase I 1.8.2 Phase II

Editable Payment Plan

Overview

In order to cause the lowest impact to existing modules and pieces of functionality in the erp, the information about the actual payment plan will continue to be stored in the same database table: fin_payment_schedule.

New tables will be created to store the information about the archived payment plan.

When creating the new payment plan, both tables will be filled in: fin_payment_schemdule and new one for the archived payment plan. If the plan is modified, the fin_payment_schedule is modified. A third table will link, when payments are processed, the payment schedule detail elements with the archived payment plan.

Concepts

• Payment Plan: Stored in the fin_Payment_Schedule table, reflects the way an invoice is going to be paid, according to the payment terms defined in it's header.
• Payment: Stored in the fin_Payment table, stores each Payments In or Payments Out defined in the system.
• Payment Detail: Stored in the fin_Payment_Detail table, break downs the information of each of the items (invoices, orders, ...) paid/withdrawn by a concrete payment.
• Payment Plan Detail: Stored in the fin_Payment_ScheduleDetail table, associates one payment plan line with every Payment Detail that, partially or fully, pays it.
• Archived Payment Plan: Original payment plan.
• Payment Plan New Version: Current payment plan. At the beginning both: archived payment plan and payment plan new version will be the same.

Data Model

Data model needs to be enhanced with two new tables:

FIN_ORIG_PAYMENT_SCHEDULE
Column Name	Type	Description
Fin_Orig_Payment_Schedule_Id	ID	ID
Ad_Client_ID	TableDir	Client
Ad_Org_ID	TableDir	Organization
isActive	yesno	Active
created	date	audit
createdBy	Table (Ad_User_ID)	audit
updated	date	audit
updatedBy	Table (Ad_User_ID)	audit
c_Invoice_ID	TableDir	invoice
c_Order_ID	TableDir	order
dueDate	date	date planned for this payment plan line
fin_PaymentMethod_ID	TableDir	payment method of the payment plan
c_Currency_ID	TableDir	currency
amount	Amount	total amount of the payment plan line
paidAmount	Amount	amount that has been paid for this payment plan line
outstandingAmount	Amount	amount pending to be paid (outstandingAmount + pendingAmount = amount)
fin_Payment_Priority_ID	TableDir	priority of this payment plan line
update_Payment_Plan	yes	is it used??

FIN_ORIG_PAYM_SCHEDDETAIL
Column Name	Type	Description
Fin_Orig_Paym_SchedDetail_Id	ID	ID
Ad_Client_ID	TableDir	Client
Ad_Org_ID	TableDir	Organization
isActive	yesno	Active
created	date	audit
createdBy	Table (Ad_User_ID)	audit
updated	date	audit
updatedBy	Table (Ad_User_ID)	audit
fin_Orig_Payment_Schedule_id	TableDir	archived payment plan line paid (partial or totally) with a payment
fin_Payment_ScheduleDetail_ID	TableDir	payment plan line paid (partial or totally) with a payment
amount	Amount	amount of a payment used to pay the referenced payment plan line
writeOffAmount	Amount	written off amount of a payment used to pay the referenced payment plan line
isCanceled	yesno	true in case the payment plan line is fully paid

Creation of a Payment Plan

Current Flow

Right now, when completing an invoice, the payment plan is created, creating new lines in the fin_Payment_Schedule table.

To Develop

In order to keep the archived payment plan information, at this moment, in the fin_Orig_Payment_Schedule table, it must be saved the same information that is nowadays saved in the fin_Payment_Schedule table.

Furthermore, if the invoice is reactivated, the same way that fin_Payment_Schedule table content is deleted, same will happen with the rows in the fin_Orig_Payment_Schedule table.

Let's have a look to the whole process through an example through all this document:

Example. Step 1

Let's take as an example, an invoice with grand total amount of 200, with a payment plan of two lines of 100 each:

All the information about the payment plan is also stored in the fin_Orig_Payment_Schedule table; apart from the fin_Payment_Schedule, as it is doing as of now.

Modification of a Payment Plan

To Develop

If user modifies a payment plan, the new payment plan will be stored in fin_Payment_Schedule table, overwriting previous payment plan information. Not all payment plan lines are suitable to be modified:

• If no payment has paid that payment plan line, user will be able to modify it.
• If a payment do totally pay a payment plan line (there exists one or more fin_Payment_ScheduleDetail rows linked to the payment plan line, and the sum of its amounts is equals to the amount of the payment plan line), it will NOT be possible to modify this payment plan line.
• If a payment do partially pay a payment plan line (there exists one or more fin_Payment_ScheduleDetail rows linked to the payment plan line, and the sum of its amounts is lower than the amount of the payment plan line), it will only be possible to modify the amount pending to be paid. In fact, the existing payment plan line will change it's amount to the one that has been paid, and a new payment plan line will be created, for the outstanding amount.

When a payment plan is modified, all the not paid payment plan lines are shown (all of them with outstanding amount not 0). User will be able to modify, for each line, it's amount, and due date. Also delete existing lines, and create new ones.

There exists two different options: modifying the archived payment plan, or creating a new version for it:

• If a new version of the payment plan is created, the new payment plan information will update the records in the fin_Payment_Schedule table. No change will be done to the fin_Orig_Payment_Schedule table.
• If archived payment plan is re-defined, then in addition to the modifications on the fin_Payment_Schedule table, also the fin_Orig_Payment_Schedule information is modified, so that previous information about the archived payment plan is lost.

In any case, when modifying a payment plan, the sum of amounts before modifying, and after modifying, must be the same.

Let's have a look to all this stuff with our example:

Example. Step 2

Now, a new version of the payment plan is is saved, so the two lines of 100 each are transformed into two lines: one of amount 25, and other one of 175.

In case the archived payment plan is modified, the difference will be that, only in this case, the archived payment plan information is also modified.

Payment completed against Payment Plan line/s

Full Payment

Current Flow

If a payment do totally pay a payment plan line (this is: a payment detail is linked to the payment plan line and the amount of the payment detail is equals to the amount of the payment plan line), system updates the fin_Payment_Schedule table to set new values for

• outstandingAmount = zero
• paidAmount = paymentDetailAmount + paymentDetailWriteOffAmount (if any)

only in case there exists a write-off amount, fin_Payment_ScheduleDetail row is modified to set new values for

• amount = paymentDetailAmount
• writeOffAmount = paymentDetailWriteOffAmount

To Develop

In this case, some code must be added when completing the payment (bellow this line) to reflect this payment in the archived payment plan information.

In the fin_Orig_Payment_Schedule table, there will already exist some rows, created when completing the invoice (or when modifying the archived payment plan). The code to be developed, will modify these rows to set following values:

• paidAmount = paymentDetailAmount + paymentDetailWriteOffAmount (if any)
• outstandingAmount = zero

Furthermore, one row will have to be created in fin_Orig_Paym_SchedDetail table with these values:

• amount = paymentDetailAmount
• writeOffAmount = paymentDetailWriteOffAmount
• fin_Payment_ScheduleDetail_ID = fin_Payment_ScheduleDetail row that links the fin_Payment_Detail (payment line) that pays the fin_Payment_Schedule (payment plan line).

Example. Step 3

Let's now, before modifying the payment plan, think about a total payment for a payment plan line:

A new element in fin_Orig_Paym_SchedDetail table will link the fin_Payment_ScheduleDetail row and the fin_Orig_Payment_Schedule one. In this case all the amounts are 100, so no other adjustment is required. Let's see now what happens in case the payment is not complete, but partial.

Partial Payment

Current Flow

In addition to the actions taken for a full payment, some other actions are taken in case the payment amount is lower that the payment plan amount, and users wants the difference, not to be written off, but to still as outstanding amount for the payment plan line.

In this case, a new row is added to the fin_Payment_ScheduleDetail table, as a copy of the line associated to the payment plan line that has been partially paid, with the only difference that the amount will be the outstanding amount of the payment plan line.

To Develop

In case a partial payment takes place, the piece of code that is going to be developed bellow this line, and will create the entries in the fin_Orig_Paym_SchedDetail table, will need to do some calculations for each of the fin_Payment_ScheduleDetail entries processed when completing a payment:

• Retrieve all the outstanding amounts of the archived payment plan lines existing fin_Orig_Payment_Schedule entries, order by due date ascending.
• Retrieve all the fin_Payment_ScheduleDetail entries created when completing the payment (actually all those not referenced by any fin_Orig_Paym_SchedDetail entry).
• Go through both lists matching them:
  • If the archived payment plan line outstanding amount is higher or equal to the fin_Payment_ScheduleDetail amount:
    • A new entry is created in fin_Orig_Paym_SchedDetail table with the fin_Payment_ScheduleDetail amount, linked to the archived payment plan record and the fin_Payment_ScheduleDetail record.
    • Outstanding amount of the archived payment plan line is set to zero, and paid amount to amount
  • If the fin_Payment_ScheduleDetail amount is higher than the archived payment plan line outstanding amount:
    • A new entry is created in fin_Orig_Paym_SchedDetail table with the archived payment plan outstanding amount
    • Amount and outstanding amount of the archived payment plan line are updated with the fin_Payment_ScheduleDetail amount.

This process will continue until all the amount in the fin_Payment_ScheduleDetail entries is distributed through the archived payment plan, through the fin_Orig_Paym_SchedDetail entries.

Please notice that both tables are slightly different:

• fin_Payment_ScheduleDetail table is always showing all the details for the total amount of the payment plan new version: no matter if belong to an already paid amount, or outstanding amount, as the entries in this table are created at the same time the payment plan is created.
• on the other hand, the records in fin_Orig_Paym_SchedDetail table will only have the details for the paid amount, not for the outstanding amount, as the rows in the fin_Orig_Paym_SchedDetail table are written down just when the payment is completed, never before.

Let's continue with our example.

Example. Step 4

Let's think now that a new version of the payment plan has been created. Now let's create a Payment for 75:

In this case, the 75 payment detail needs to be detailed in the payment plan detail table, as 25 do pay the 25 payment plan line, and the other 50 do pay part of the 100 payment plan line.

Furthermore, each of these payment plan detail elements (25 and 50) do have it's corresponding archived payment plan detail entries: both of them related to the first 100 archived payment plan element.

Example. Step 5

The last step of the example completes another payment for 100:

In this case, the 100 payment do provoke the split of the 125 payment plan detail element, into one of 100 and other one of 25. The one of 100 is linked to TWO records in the archived payment plan detail elements: one for 25 that paids the last 25 pending of the first 100 archived payment plan elements. The rest (75) partially paids the second 100 archived payment plan element.

Payment reactivation

If a payment is reactivated, same way payment schedule detail lines are deleted, when possible, original payment plan detail lines must be also deleted.

Development Phases and Status

Phase I

While Pick and Execute development is ready, these are the steps that can be implemented:

Id	Requirement	Importance	Status
1.1	Tables Creation	Must have	Done
1.2	Application Dictionary Elements	Must have	Done
1.3	Archived Payment Plan table population when processing the invoice	Must have	Done
1.4	Archived Payment Plan table delete when reactivating the invoice	Must have	Done
1.5	Dummy Payment Plan modification process (new version or archived payment plan modification)	Must have	Done
1.6	Process to be launched when completing a payment: update payment monitor information, populate the original payment plan details table	Must have	Done
1.7	Process to be launched when reactivating a payment: update payment monitor information, delete the original payment plan details table rows	Must have	Done
1.8	Payment monitor modifications	Must have	Done
1.9	Module-script development	Must have	Done

Phase II

Once Pick and Execute development is ready, these are the steps that will be implemented:

Id	Requirement	Importance	Status
2.1	Pick And Execute AD elements	Must have	Done
2.2	Validation of expected amounts	Must have	Done
2.3	Execute function development	Must have	Done
2.4	Modify Original Payment Plan implementation	Must have	Done

Category: Projects ERP