Constructing a Payment File - Brainstorm

[Ugo DI LUCA]

I'm totally rebuilding my database but the Payments give me such a pain that I'm quite stuck with this. So I would like to have some ideas (or example) on how you reached to establish a Payment File in a Relational design (if you did actually create a file for it)

In my business, for both the Customer and the Supplier (of goods and services related to the activity - not Tax collector,...), the Invoicing system is based on shipments.

When involving such a system, here is the kind of relationships you will come accross

- [color:"red"]One to Many: One Customer order shipped in several times, invoiced in several times

- [color:"red"]Many to One: Many Customer order shipped in one time, invoiced once

- [color:"red"]Many to Many: Many Customer order shipped in several times, invoiced in several times.

There can be obviously no "direct" relationships from the Orders to the Invoices (both Customers and Suppliers).

Here is where I am for the moment :

Payments are usually registered (as for all of you I presume) on :

- a Customer Order (account for individuals and new clients in my case)

- a Customer Invoice

- a Purchase Order (for special orders or new suppliers in my case)

- a Purchase Invoice

I have those files in my database : Customer Order, Customer Invoice, Purchase Order, Purchase Invoice and obviously a Client and Supplier Files (actually I use only one file as in the particular case, a client can also be a supplier).

Of course, all the files are related to a Line Item, and I did actually find a way to create a "kind" of relationship from this File, using a unique ID on a product to product basis (same ID autoentered for same product+samecustomer+same first module - (thanks again Steve Brown )Link to the thread - . Then each line in the line item is related to a unique Module (could be Quote, Customer Order, Purchase Order,...), but "attached" to a set of records...to avoid orphans while creating new records in the Line Item via portals.

So I thought about using this kind of structure for payments too, by creating 2 Payment Files (Income & Outcome) and a specific Payment Line Item, linked to the 4 Operational Files (Customer and Purchase Orders, Customer and Purchase Invoices) and the Client/Supplier File.

The records would be pulled to the Payment Line Item from each Operational File, using a new unique ID, that would also be stored in the Line Item File.

There would therefore be a complete relationship design where the records modified from the Line Item would show up in Invoices and Orders, and in the Payment Line Item.

But this sounds to me very complex.

If anybody wants to share its payment procedure/structure, I'm quite sure I'm not the only one lost on this one.

Just pull some lines as if it was a discussion, as any ideas (even worse than mines) could help.

[Kurt Knippel]

OK, you say that order can be broken up into an unknown number of shipments and invoices and that there is no direct relationship between them. Ok, so embrace this, do not try to create one, it will only complicate the system.

a CUSTOMER places an ORDER, which is broken up into one or more SHIPMENTs which PAYMENTs are revied from one or more INVOICES.

So there is a one-many relationship between CUSTOMER and ORDER. There is a many-many relationship between ORDER and SHIPMENT. There is a many-many relationship between SHIPMENT and INVOICE (as I assume that you do not invoice for things not shipped). Finally there is a one-many relationship between INVOICE and PAYMENT.

Does this help?

[Ugo DI LUCA]

Trying to design your post into a plan...and comparing mine...Hum...You were quite there...

Looking the client side at the startpoint. CL 1 and CL 2 are individuals, CL 3 and CL 4 are registered professionals. God I appreciate colors on this forum Oh God I hope you'll get courage for this long post. Pleaaaase...

Clients to Order [color:"red"]One to Many

[color:"blue"]CL 1---->CUORD 1, CUORD 3

[color:"blue"]CL 2---->CUORD 4

[color:"green"]CL 3----> CUORD 2, CUORD 6

[color:"green"]CL 4----> CUORD 5 (Special Order)

I would add :

Orders to Part-Payments(Accounts)

[color:"blue"]CUORD 1 for CL 1----> PPAYM 1

[color:"green"]CUORD 2 for CL3----> 0

[color:"blue"]CUORD 3 for CL 1----> PPAYM 2

[color:"blue"]CUORD 4 for CL 2----> PPAYM 3

[color:"green"]CUORD 5 for CL 4----> PPAYM 4

[color:"green"]CUORD 6 for CL 3----> 0

So...this tend to be :

[color:"blue"]Individual Orders to PartPayments : [color:"red"]One to One

[color:"green"]Professional Orders to PartPayments : [color:"red"]One to Nothing (is O a record ?)

[color:"green"]Special Professional Orders to PartPayments:[color:"red"]One to One

Orders to Shipments :[color:"red"]Many to Many

[color:"blue"]CUORD 1 for CL 1---->[color:"blue"]SHIP 1, SHIP 11

[color:"green"]CUORD 2 for CL 3 --->[color:"green"]SHIP 2, SHIP 7

[color:"blue"]CUORD 3 for CL 1 --->[color:"blue"]SHIP 3

[color:"blue"]CUORD 4 for CL 2 --->[color:"blue"]SHIP 4, SHIP 5

[color:"green"]CUORD 5 for CL 4---> [color:"green"]SHIP 8, SHIP 9

[color:"green"]CUORD 6 for CL 3----> [color:"green"]SHIP 6, SHIP 10

Shipments to Invoices :[color:"red"]Many to One or

Invoices to Shipments :[color:"red"]One to Many (does it make a difference in your dev terms ?)

[color:"blue"]SHIP 1, SHIP 3 for CL 1 ---> [color:"blue"]INV 1 for CL 1

[color:"green"]SHIP 2 for CL 3 ---> [color:"green"]INV 2 for CL 3

[color:"blue"]SHIP 4 for CL2 ---> [color:"blue"]INV 3 for CL 2

[color:"blue"]SHIP 5 for CL 2 ---> [color:"blue"]INV 4 for CL 2

[color:"green"]SHIP 6,SHIP 10 for CL 3 ---> [color:"green"]INV 5 for CL 3

[color:"green"]SHIP 7 for CL 3 ---> [color:"green"]INV 6 for CL 3

[color:"green"]SHIP 8, SHIP 9 for CL 4 ---> [color:"green"]INV 7

[color:"blue"]SHIP 11 for CL 1 ---> [color:"blue"]INV 8 for CL 1

Invoice to Payments : : ?

That is where I'm getting stuck as [color:"red"]Orders to Invoice will be a Many to Many relationship, as the invoice is the result of several shipments, and could not be only linked to one customer order. The total amount of INV 1 for CL 1 will be different from the CUORD 1 !

Turning this in understanding text/numbers for CL 1

CUORD 1 : 5,000 $ - PPAYM 1 = 1,500$

CUORD 3 : 4,500 $ - PPAYM 2 = 1,250$

When Invoicing :

INV 1 : 2,200 $ - PPAYM ?

Setting it to PPAYM 1 wouldn't be true..Setting it to PPAYM1 + PPAYM 2 would turn in ridiculous as I believe my guys would say to the customer : "Well, the amount due is 2,200 $ - (1,500,00 $- 1,250,00 $) = - 550,00 $. Go ask for a cheque to Mr DI LUCA."

So you are right, [color:"red"]One to Many should be the correct answer as the standard basis, considering that accounts paid on customer orders (if there was an order, as I can also sell on over the counter) moved to the Invoice.

But how can I come to this result, "importing" the correct accounts to have :

1 Individual invoice = 1 PartPayment (accounts on Order) + ( 1 Payment for "closure" OR 1/3 payments for "credit closure").

1 SpecialOrder Professional Invoice = 1 PartPayment (accounts on Order) + 1 Payment for "closure".

1 Professional Invoice = 1 Closure Payment.

If I leave apart some atypical case where :

- I have to deal with the classic accountant nightmare : One payment for multiple Invoices in the case of late professional payments.

- I could also have (but this is very very particular and unusual) a client (a Wholesaler that is also a supplier for other goods) that pays the difference from his purchase invoice and my Customer Invoice (just to tell, do not even think of helping on this one as it is odd).

Thank you very much. Spending some time on this curious colored answer almost give me some clues about relationships I could/couldn't establish.

[Ugo DI LUCA]

Rereading my previous detailed answer, I did find out.

I was almostly too much concentrated on my files structure, and suddenly realised that the whole process could still be done within the same Line Item File.

[color:"red"] But I still have a question at the end of this post.

As an Invoice is the result of calculations from the line items, and that it is set for printing (in my structure) as a summary report from this lines

1. Get this summary to sort by Order.

2. Have a summary field by order at the end of invoices that displays :

Order N

[Razumovsky]

Hi Ugo,

This is kind of the model I would use.

Orders (from a single customer)

Invoices (for a single or mult. order by single customer)

LineItems (represents a qty of a single product for a single order)

PO (shipments- requests a qty of mult or single products from a single vendor)

Payments (adds or subtracts from the balance of a single Order or PO)

PaymentLineItem (represents the distribution of the payment over mult. Invoices or PO's)

Orders set LineItems through portal. An order cannot be split into mult. invoices.

An Invoice can represent any number of orders from the same customer by scripting a multikey

Invoice MultiKey = OrderID1& "PP"& OrderID2 &"PP"&OrderID3...

Invoice-InvoiceMulti=OrderID-LineItems

Once an Order has been invoiced, the line items can be assigned to a PO.

PO is for a single vendor and can request mult products. May also have mult line items of the same product (4 sprockets for Cust. A, 7 sprockets for cust.

Payments (in this model, it is also a credits file) are applied only to Invoices and PO's- they have nothing to do with Orders or LineItems. If a cust. wants to know how much they owe for a parrticular order, you say "that order was on invoice#227 along with 2 other of your orders. The total due for that invoice is $X." Invoices represent discrete accounting blocks concerning $ Due. If a cust. insists on getting seperate totals for each order, you insist on seperate invoices for each order. Accounting deals only in terms of invoices, not orders.

there are 2 types of payments: CustPayment (when a cust reduces the balance of an invoice)and VendorPayment (when you reduce the balance of owed on a single PO).

Each Payment is actually stored in PaymentLineItem to keep track of when there is

One payment for multiple Invoices in the case of late professional payments.

For this situation, there is a "Mult Invoice Payment" button/script that takes

user to a layout in Payments file, creates new record, and lists through portal all invoices for a customer that have a balance remaining.

Payments-t_CustID=t_CustID-Invoice

You could even use the script to apply the payment to the earliest open invoice balance, apply the remainder of the payment to the next earliest open invoice...

CompletePayment script would verify that g_PaymtAmt= sum(Payments-t_CustID=t_CustID-Invoice::n_PaymtAmt).

Your balance due on invoices would be:

sum(Invoices-InvoiceID=InvoiceID-LineItems::ItemAmt) - sum(Invoices-InvoiceID=InvoiceID-PaymentLineItem::PymtAmt)

Your balance due on PO's would be similiarly:

sum(PO-POID=POID-LineItems::WholeSaleItemAmt) - sum(PO-POID=POID-PaymentLineItems::POPymtAmt)

Hope that helps.

-Raz

[Ugo DI LUCA]

Thank you for your help Raz,

I'm confused as I realize that I may have used bad terms here.

1. Please consider Shipments as the Client delivery, not the PO. And Client Purchase Order as Customer Order.

2. The PPAYM is in fact the "account" (isn't the good term) the client is paying to guarantee his order (he pays 30% at the order, then the rest at the delivery).

Very good ideas though, specially for the "one payment for multiple invoices".

Looking back to the structure of my line items, as I said, I use a relatively complex line item file, but it gives me a lot of possibilities to link each record each other.

So I surely can set a relationship into the line item for that damned Payments, using other selfjoin, but I'm sure your line item for Payment is a better idea, as it will also be used for my future accounting files.

I know you suggested that payments on order would not be useful in this Payment file, but I need them to go somewhere (always for accounting - at least to know who pays when and why-).

What could be the relationship from that new Payment Line Item to the Invoice File ?

Quote

Invoice MultiKey = Order_ID1&"PP"&Order_ID2&"PP"&Order_ID3...

How can you create this multikey in the Invoice File to relate to that Payment Line Item ?

As it may help you to understand better and others on this forum trying to create a multiple line item file, here it is :

Each line will be created by portal in other related files.

When first entry is processed from a portal into the lines for a given product, [color:"red"] a universal ULine_ID will be attributed to this line.

Imagine an entry from the Quote File :

----> [color:"red"]ULine_ID : 000XF54GM

Next to it will be

----> [color:"green"] OpLine_ID = ""(see later)

----> [color:"blue"] Product_ID = A

----> [color:"#666666"]Quote N

[Razumovsky]

1. Please consider Shipments as the Client delivery, not the PO. And Client Purchase Order as Customer Order.

This should not really effect the model- Shipments are the same as PO except that instead of being for a single vendor, they are for a single customer.

2. The PPAYM is in fact the "account" (isn't the good term) the client is paying to guarantee his order (he pays 30% at the order, then the rest at the delivery).

Same with this- just include a field in your payment line items that identifies the type of payment being made. BTW - I think "deposit" is a common term for this.

I know you suggested that payments on order would not be useful in this Payment file, but I need them to go somewhere (always for accounting - at least to know who pays when and why-).

They will be going somewhere- to the invoice This would identify who pays and why - or you could note it in your comments field of the payments file. relating the payments to the orders complicates things unneccesarily, as the orders are already related to an invoice to get a total amt due. If someone bought 12 apples from you (12 orders), you would not ask for 30% down on each apple and then itemize this amnt for each item, but for 30% of the total sum.

What could be the relationship from that new Payment Line Item to the Invoice File ?

I would assign payments from a button on the invoice screen that would take you to a payments file and set the Invoice# (in Payments) to the relevant invoice. Payments could be entered into a portal (to pymt line items) that would auto enter the related InvoiceID into the Pymt Line item. PymtLineItem-InvoiceID=InvoiceID-Invoice. you would set the reverse relationship from invoices.

Invoice MultiKey = Order_ID1&"PP"&Order_ID2&"PP"&Order_ID3...

How can you create this multikey in the Invoice File to relate to that Payment Line Item ?

you wouldnt. You only need to relate payments to invoices, not orders.

-Raz

[Ugo DI LUCA]

Thanks for replying Raz.

I now got the concept. Just have to see it on a screen to see it better.

Another question though. Looking to this post and back to others you've made, it seems that you systematically set your autoentered "Module" N

[Razumovsky]

Hi Ugo-

Glad that things are working out.

it seems that you systematically set your autoentered "Module" N