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' |