Projects:Editable Payment Plan/Technical Documentation
Contents |
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.
- If the archived payment plan line outstanding amount is higher or equal to 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 |