[QODBC-Desktop] How to Receive a Payment not be Applied to an Invoice ( How to handle Customer credits on account )
Posted by brad waddell on 12 March 2009 05:21 PM
|
|
How to Receive a Payment not be Applied to an Invoice ( How to handle Customer credits on account )Note: Below is how to receive a payment from a customer that will not be applied to an invoice. In other words, it will be a credit on account, or a deposit received. This will be applied later, once the services have been rendered and the invoice is generated. Also, about how to apply for that credit to account to the invoice at that time. Instruction of a Received Payment has not Invoice AppliedIn QuickBooks In QuickBooks, when you receive a payment against a customer that has no outstanding invoices as follows: the Payment Credit isn't a Credit Memo: In QODBC But a ReceivePaymentToDeposit line within QODBC found by doing: SELECT TxnID, CustomerRefFullName, RefNumber, Amount FROM ReceivePaymentToDeposit
Table Schema of ReceivePaymentToDeposit Examination of the ReceivePaymentToDeposit table using: sp_columns ReceivePaymentToDeposit shows the table to be read-only, meaning we can't insert or update the table directly. To get around this we need to first receive the payment and then apply it to the invoice later.
TO RECEIVE THE PAYMENT FOR THE CUSTOMER WITHOUT ANY INVOICESLocate Customer ListID To do this using QODBC you first you need to locate the ListID for the customer by doing: Select * from Customer where FullName='Data Access Worldwide' Here for the "Data Access Worldwide" customer ListID is AE0000-1197765289. Receive the payment for the customer without any invoices INSERT INTO ReceivePayment (CustomerRefListID, ARAccountRefListID, Locate Transaction ID for this payment The transaction ID for the payment we just did is found by doing: SP_LASTINSERTID ReceivePayment It returned a TxnID of: 5C29-1197772163 Locate ReceivePaymentToDeposit Line The insert also created a ReceivePaymentToDeposit line found by doing: Select * from ReceivePaymentToDeposit where TxnID='5C29-1197772163'
CREATE THE INVOICECreate an Invoice using QODBC INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber", Locate Transaction ID for the Invoice To locate the Transaction ID for the Invoice you can do: select TxnID, RefNumber from InvoiceLine where CustomerRefListID = 'AE0000-1197765289' The TxnID of the Invoice is: 5C2C-1197773093
TO APPLY THE PAYMENT TO THE INVOICE OR APPLY CREDITMEMO TO THE INVOICEApply the Payment to the Invoice using QODBC So now there's an invoice you can do a ReceivePaymentLine to apply that payment to the Invoice and credit the unapplied payment! For existing Credit Memos, you can use the CreditMemo TxnID and apply it in the AppliedToTxnSetCreditCreditTxnID column in the ReceivePaymentLine insert for payments: INSERT INTO ReceivePaymentLine (CustomerRefListID, ARAccountRefListID, Display in QuickBooks The Invoice now displays as PAID in QuickBooks. Data Location Information Where:
| |
|
INSERT INTO ReceivePaymentLine ( CustomerRefListID, ARAccountRefListID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnSetCreditCreditTxnID, AppliedToTxnSetCreditAppliedAmount )
SELECT '80001447-1314282533', '620000-1084332528', '1B2B8C-1481781578', 300, '1B10DD-1481065253', 300;
It works when the invoice and payment is for the parent customer, but it doesn't when the payment is applied to the parent customer but the invoice is listed for the customer job. Quickbooks lets you apply payments from parent customers to their child jobs, but I cannot get it to work with the insert statement.
I get the following error
[QODBC] Error: 3120 - OBJECT "1B10DD-1481065253" specified in the request cannot be found. (#10053)
I verified 1B10DD-1481065253 is a valid TxnID in the RecievePayment table.
I would like to share that AppliedToTxnSetCreditCreditTxnID should be TxnID from ReceivePaymentToDeposit Table not TxnID in the RecievePayment table.
If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit
We may need the following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
How can we view today's received amounts from Customers ,
Thanks
You can refer ReceivePayment table for that details. You can filter it by TxnDate & CustomerRefFullName.
For Example:
Select * from ReceivePayment where CustomerRefFullName = ‘John’ & TxnDate = {d’2018-01-29’}
If you are still the facing issue, Please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit
We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm