WebCOBRA.com Community Message Boards

dtech
Posts: 1
Since: 07/26/2010

how do you query admin fee and unapplied payments by qb group id. i'd like to aggregate admin fee and group by qb and month.

bptillman
Posts: 22
Since: 12/21/2007

You can find admin fees in the AccountingInvoiceLineItem table. In there they will have an AccountingInvoiceLineItemTypeID of 2 and you will be able to find the InvoiceID there that will link back to the InvoiceID in AccountingInvoice, which will be able to tell you the month it was for. You can then use the InvoiceID to look in AccountingRegister to get the QBGroupID for the QB.

To find unapplied payments you can get look in the AccoutingRegisterPaymentDetails table and find the PaymentHistoryId and AmountAppliedtoLineItemFromPayment in there and then subtract that amount from the AmountPayed field in AccountingPayment for that PaymentHistoryID. If the amount is anything but zero, then that’s the unapplied amount.