invoicing question

[David B]

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

[Fletcher Arnold]

"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

[Roger]

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

[Fletcher Arnold]

"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

[Roger]

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

[Fletcher Arnold]

"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