By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

invoicing question

P: n/a
I have an app which includes invoicing, which is working fine.
I am wondering how best to deal with the situation where the amount paid is
different from the amount invoiced.
Would it be best to have a separate table or just an extra field in the table
that stores invoicenum, amountinvoiced.
Any thoughts ?
TIA
David B
Hexham UK

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"David B" <Da***@marleycotenospam.fsnet.co.uk> wrote in message
news:c1**********@newsg2.svr.pol.co.uk...
I have an app which includes invoicing, which is working fine.
I am wondering how best to deal with the situation where the amount paid is different from the amount invoiced.
Would it be best to have a separate table or just an extra field in the table that stores invoicenum, amountinvoiced.
Any thoughts ?
TIA
David B
Hexham UK

It could be even worse than that. You will probably need at least two extra
tables: tblPayments and tblPaymentAllocations. It is not uncommon to
receive an amount say 1,750.00 which is to cover two invoices, say 1,200
for INV10236 and 550 for INV10362. Now, if the customer sent a cheque for
2,000 you would have one payment record, two invoice records and three
allocation records. The final allocation record would be 250 not against
any invoice - which is what I believe the accountants call a 'payment on
account'.

A huge help here might be to talk to an accountant / book-keeper and ask how
they record these sorts of details. Alternatively, see if you can get a
trial version of a standard accounting piece of software (perhaps Sage).
This will come complete with help files to show you how to operate the
software plus odbc drivers to allow you a sneaky peak at their table
structures - just for inspiration, of course.
Fletcher
Nov 12 '05 #2

P: n/a
and you probably need a write-off table
to handle invoices that never get paid, or invoices that never get
fully paid (ie dispute over prices, taxes, freight, etc)
this would be written off to a GL account

something like, but not restricted to

tblCashReceipt
cashReceiptId
customerId
bankId
paymentTypeId
paymentDate
paymentAmount
paymentReference (ie. cheque #, cc #)

tblCashReceiptDetail
cashReceiptId
invoiceId
discountTakenAmount
paymentAmount

tblWriteOff
writeOffId
glAccountId
glAmount
reason

tblOnAccount
onAccountId
cashReceiptId
onAccountAmount

tblCashReceipt.paymentAmount = sum(tblCashReceiptDetail.paymentAmount)
+
tblWriteoff.glAmount

amtDue = invoice.invoiceAmount -
sum(tblCashReceiptDetail.paymentAmount) -

sum(tblCashReceiptDetail.discountTakenAmount)
"Fletcher Arnold" <fl****@home.com> wrote in message news:<c1**********@titan.btinternet.com>...
"David B" <Da***@marleycotenospam.fsnet.co.uk> wrote in message
news:c1**********@newsg2.svr.pol.co.uk...
I have an app which includes invoicing, which is working fine.
I am wondering how best to deal with the situation where the amount paid

is
different from the amount invoiced.
Would it be best to have a separate table or just an extra field in the

table
that stores invoicenum, amountinvoiced.
Any thoughts ?
TIA
David B
Hexham UK

It could be even worse than that. You will probably need at least two extra
tables: tblPayments and tblPaymentAllocations. It is not uncommon to
receive an amount say 1,750.00 which is to cover two invoices, say 1,200
for INV10236 and 550 for INV10362. Now, if the customer sent a cheque for
2,000 you would have one payment record, two invoice records and three
allocation records. The final allocation record would be 250 not against
any invoice - which is what I believe the accountants call a 'payment on
account'.

A huge help here might be to talk to an accountant / book-keeper and ask how
they record these sorts of details. Alternatively, see if you can get a
trial version of a standard accounting piece of software (perhaps Sage).
This will come complete with help files to show you how to operate the
software plus odbc drivers to allow you a sneaky peak at their table
structures - just for inspiration, of course.
Fletcher

Nov 12 '05 #3

P: n/a
"Roger" <le*********@natpro.com> wrote in message
news:8c**************************@posting.google.c om...
and you probably need a write-off table
to handle invoices that never get paid, or invoices that never get
fully paid (ie dispute over prices, taxes, freight, etc)
this would be written off to a GL account

something like, but not restricted to

tblCashReceipt
cashReceiptId
customerId
bankId
paymentTypeId
paymentDate
paymentAmount
paymentReference (ie. cheque #, cc #)

tblCashReceiptDetail
cashReceiptId
invoiceId
discountTakenAmount
paymentAmount

tblWriteOff
writeOffId
glAccountId
glAmount
reason

tblOnAccount
onAccountId
cashReceiptId
onAccountAmount

tblCashReceipt.paymentAmount = sum(tblCashReceiptDetail.paymentAmount)
+
tblWriteoff.glAmount

amtDue = invoice.invoiceAmount -
sum(tblCashReceiptDetail.paymentAmount) -

sum(tblCashReceiptDetail.discountTakenAmount)



I'll take your word for it and count myself lucky not to have the task of
creating a whole accounting package from scratch!

Fletcher
Nov 12 '05 #4

P: n/a
yes... a full feature accounting system is quite an undertaking...

do you have an accounting package of choice that you interface to your
applications ?

"Fletcher Arnold" <fl****@home.com> wrote in message news:<c1**********@titan.btinternet.com>...
"Roger" <le*********@natpro.com> wrote in message
news:8c**************************@posting.google.c om...
and you probably need a write-off table
to handle invoices that never get paid, or invoices that never get
fully paid (ie dispute over prices, taxes, freight, etc)
this would be written off to a GL account

something like, but not restricted to

tblCashReceipt
cashReceiptId
customerId
bankId
paymentTypeId
paymentDate
paymentAmount
paymentReference (ie. cheque #, cc #)

tblCashReceiptDetail
cashReceiptId
invoiceId
discountTakenAmount
paymentAmount

tblWriteOff
writeOffId
glAccountId
glAmount
reason

tblOnAccount
onAccountId
cashReceiptId
onAccountAmount

tblCashReceipt.paymentAmount = sum(tblCashReceiptDetail.paymentAmount)
+
tblWriteoff.glAmount

amtDue = invoice.invoiceAmount -
sum(tblCashReceiptDetail.paymentAmount) -

sum(tblCashReceiptDetail.discountTakenAmount)



I'll take your word for it and count myself lucky not to have the task of
creating a whole accounting package from scratch!

Fletcher

Nov 12 '05 #5

P: n/a
"Roger" <le*********@natpro.com> wrote in message
news:8c**************************@posting.google.c om...
yes... a full feature accounting system is quite an undertaking...

do you have an accounting package of choice that you interface to your
applications ?


No, I'll just do my best to work with what the customer has. My (UK)
experience has been with products from Sage ( http://www.sage.com ) and
Pegasus ( http://www.sage.com )

Fletcher
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.