Jump to content

Invoices and Payments


Ninja

This topic is 2712 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have a FM solution for the haulage company I work for. Each job is booked into a "Loads" table, and the price and any additional charges for each load is created in a "Charges" table (although charges can also be created that are not related to Loads e.g. storage charges).

When ready to be invoiced a new record is created in the "Invoices" table and the invoice is allocated to a particular company. At which point all available charges for that company are displayed and can be pulled onto the invoice (by simply adding the invoice's serial number to the charge selected).

I'm having some issues, however, managing payments. At the moment any payment is logged in a "Payments" table. On leaving this table a script imports all invoices and payments into a "Transactions" table so that statements can be displayed. However, this is cumbersome and I still have to manually mark specific invoices as paid (as one payment may pay for multiple invoices)

I was hoping to use the "Invoices" table to record payments as a type of invoice with a negative total, which would allow me to pull invoices onto the payments and therefore simultaneously mark them as paid, but then I have the issue of viewing payments when searching for invoices as I'm using the one table for two purposes.

Does anyone have any experience of managing invoices and payments who could offer any advice please?

Link to comment
Share on other sites

Payments definitely should be its own table and not mingled in with Invoices.

This part of the description is not very clear.  Why import both invoices and payments into a transaction table?  If you want to preserve historical statements in the database then you'll need a statements table - is that what the 'transactions' table is for?

59 minutes ago, Ninja said:

At the moment any payment is logged in a "Payments" table. On leaving this table a script imports all invoices and payments into a "Transactions" table so that statements can be displayed.

You defnitely need the 'Payments' table.  In addition to that you can use a 'Payment allocation' table that is a join between the Payment and the invoice line items.  That way any one payment can be set up to cover one or more full invoices or just as a partial payment for one invoice.

Running a statement is then just doing a search on all invoice line items (charges) that are not marked as paid.

 

Link to comment
Share on other sites

You're right, I think I've just had a vision of providing statements that show invoices and payments, however I guess this is unnecessary. (Yes this was the transactions table)

 

So how would you suggest allocating payments to invoices? I can imagine a number of ways, but it becomes complicated when considering partial payments.

 

Thanks for your help

Link to comment
Share on other sites

4 hours ago, Ninja said:

So how would you suggest allocating payments to invoices? I can imagine a number of ways, but it becomes complicated when considering partial payments.

 

Partial payments happen in real life so you have to allow the system to handle it. 

- for each payment received, create a payment record (from who, amount, method - cheque / wire transfer / credit card / ..., date)

- for each payment record create a workflow where you show open invoices with the individual charges for that customer

- let the user select a whole invoice or individual charges, creating 'payment allocation records for each 'charge' record that gets fully or partially paid.

If you haven't used it before: this is a typical workflow that you'd want to use transactions for so that when something goes wrong in committing the payment allocation, that you can back out completely and revert all changes.

 

Link to comment
Share on other sites

Hmm that's an interesting workflow I hadn't considered.

It would be unusual for one of our customers to not pay for a specific charge, more likely that they would simply make an error and pay £1,032.23 when they meant to pay £1,023.32.

Do you forsee an issue whereby the database simply offers the customer's unpaid invoices at the point of recording a payment, and the user can tick those that should relate to the payment?

 

Link to comment
Share on other sites

If a customer overpays then that should become 'cash on account' and be registered somewhere to be used in future allocations.  Unless you always immediately refund the difference.

I don't know your business well enough, but in general I would build the system for what could happen, including the unusual events - like a customer contending a charge and refusing to pay it.  Otherwise the system does not reflect reality and reports will be off, users will ignore the system,... 

Link to comment
Share on other sites

Great topic. The "somewhere" I use is a billing account. Each client has one. That's where I apply deposit payments, such as retainers, before the order is available. So, my payment_allocations table has id_billing account, id_order, id_invoice and id_payment.

Link to comment
Share on other sites

This topic is 2712 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.