When I run the "SalesByItemDetail" report on a cash basis, the report returns both payment and time discount amounts.  I need to differentiate between the actual payment amount and the discount amount because I pay commissions on only the payment made and not any discounts given.  How can I tell the difference between the payment and discount amount?  If the report doesn't allow this, what table would help me tell the difference?

thanks.
Pete Lee

First we need to understand, what happens when we apply a discount to a invoice payment. The following shows a $100 discount applied to a $1,040 invoice and the resulting $940 payment made by the Customer:

Using QODBC we can extract the same info by doing:

SELECT ReceivePaymentLine.TxnDate as "Date", Invoice.CustomerRefFullName as "Job",
ReceivePaymentLine.AppliedToTxnRefNumber as "Number", Invoice.SubTotal as "Orig. Amt.",
ReceivePaymentLine.AppliedToTxnDiscountAmount as "Discount",
ReceivePaymentLine.AppliedToTxnAmount as "Payment" FROM ReceivePaymentLine, Invoice
where ReceivePaymentLine.AppliedToTxnTxnID= Invoice.TxnID
and ReceivePaymentLine.CustomerRefFullName='Baker, Chris'

If I run a Cash basis Sales By Item Detail report for the Customer in QuickBooks I see:

and I can extract the same by using the following stored procedure in QODBC:

sp_report SalesByItemDetail show TxnType as Type, Date, RefNumber as Num, Memo, Name,
Quantity as Qty, UnitPrice as "Sales Price", (Quantity * UnitPrice) as "Original Amount",
Amount as "Paid Amount" parameters DateMacro = 'ThisMonthToDate',
ReportBasis = 'Cash' where Name='Baker, Chris:Family Room'

As you can see, the original amount and the paid amount to pay the commision on is displayed. If I had a fixed 10% commision rate I could even add a column like: (0.1 * Amount) as "Commission" to the report:

sp_report SalesByItemDetail show TxnType as Type, Date, RefNumber as Num, Memo, Name,
Quantity as Qty, UnitPrice as "Sales Price", (Quantity * UnitPrice) as "Original Amount",
Amount as "Paid Amount", (0.1 * Amount) as "Commission" parameters DateMacro = 'ThisMonthToDate',
ReportBasis = 'Cash' where Name='Baker, Chris:Family Room'

Hi Tom,  Thank you for quick response.  The info you gave me was usefull.  The one part that I still need help with is in your last screen shot of "SalesByItemDetail" report.  Lines 2,3, and 6 appear to be the discount details and I wouldn't want to pay commissions on the discount.  So is there some way I can tell that those lines are discount items and not actual payments.  I know I can't just assume that the lower amounts are discount items because we have customers that do partial payments on an invoice making the payment amounts small also.  So what I really need to determine is when the detail line on the "SalesByItemDetail" report is an actual payment versus the discount given.

Thanks.
Pete

Sorry, QuickBooks doesn't label the discount line for us in the Sales By Item Detail report. Wouldn't that be nice? That's why I also showed you how to see if a REAL discount was applied to a invoice or not? You can also do a date range like this:-

SELECT ReceivePaymentLine.TxnDate as "Date", Invoice.CustomerRefFullName as "Job",
ReceivePaymentLine.AppliedToTxnRefNumber as "Number", Invoice.SubTotal as "Orig. Amt.",
ReceivePaymentLine.AppliedToTxnDiscountAmount as "Discount",
ReceivePaymentLine.AppliedToTxnAmount as "Payment" FROM ReceivePaymentLine, Invoice
where ReceivePaymentLine.AppliedToTxnTxnID= Invoice.TxnID
and (ReceivePaymentLine.TxnDate >= {d '2006-06-01'} and ReceivePaymentLine.TxnDate < {d '2006-07-01'})

You can also add the Sales Rep info like this:-

SELECT Invoice.SalesRepRefFullName as "Rep", ReceivePaymentLine.TxnDate as "Date",
Invoice.CustomerRefFullName as "Job",ReceivePaymentLine.AppliedToTxnRefNumber as "Inv No.",
Invoice.SubTotal as "Orig. Amt.", ReceivePaymentLine.AppliedToTxnDiscountAmount as "Discount",
ReceivePaymentLine.AppliedToTxnAmount as "Payment" FROM ReceivePaymentLine, Invoice
where ReceivePaymentLine.AppliedToTxnTxnID= Invoice.TxnID
and (ReceivePaymentLine.TxnDate >= {d '2006-06-01'} and ReceivePaymentLine.TxnDate < {d '2008-07-01'})
Order by Invoice.SalesRepRefFullName, Invoice.CustomerRefFullName