balance column not updating in accountreceivable table

Mar 29, 2005 2 Replies
K

I have a database related question. When a customer charges a sale, the transaction information gets written to the AccountReceivable table, and the original amount and the balance at that point are the same amount. However when the customer pays the balance off, does this affect the record that was originally written to the AR table. Shouldn't it change the balance to 0.00 in the database table? If not, where does it get recorded in the database that this particular charge sale has been paid?



I need to know because I am putting together a commission table and want to mark the charge sales as either paid or unpaid, and thought I could do it this way:



CASE WHEN AccountReceivable.Balance > 0 THEN 'Unpaid' ELSE 'Paid' END



However, there is an invoice showing up as 'Unpaid', but when we go into the customer properties and look at the account information for this customer, it shows that this invoice was paid over two weeks ago


thank you, kevin


Vote
Twitter Facebook LinkedIn
K

here's some more information - there are some transactionnumbers in the AR table where the originalamount equals the balance even though they have been paid, and then there are some that have been paid where the originalamount and the balance do not equal (the balance now shows as zero) ???

for the ones where the originalamount and the balance still equal, there is an additional record written to the AR database that represents the payment on account. is there anyway i can tie these two records together in my SQL query somehow, so that i can tell if the original transactionnumber has been paid on account?

thanks, kevin

Vote
Twitter Facebook LinkedIn
K

never mind, i've got a workaround for this that seems to be working. i just referred to the stored procedure ListOpenAccountsReceivable, and then did a JOIN to #ListOpenAccountsReceivable ON Customer.ID #ListOpenAccountsReceivable.CustomerID

similar to the way this is used in the A/R reports. then i just replaced this:

CASE WHEN AccountReceivable.Balance > 0 THEN 'Unpaid' ELSE 'Paid' END

with this:

CASE WHEN #ListOpenAccountReceivable.Balance > 0 THEN 'Unpaid' ELSE 'Paid' END

thanks, kevin

Vote
Twitter Facebook LinkedIn

Join the Discussion

Have something to add? Share your thoughts — no account required.

Didn't find your answer?

Ask the community — no account required