We are currently using the following query to calculate commission for sales employees:
SELECT T0.DocNum as "Doc #", T0.CardName as "Customer", T0.NumAtCard [PO Number], T0.DocTotal as "Doc. Total", T0.DocDate, T1.SlpName, T1.Commission as "% Commission", ( ( T0.DocTotal - T0.VatSum)*( T1.Commission/100)) As "Sum_Commissions" FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode WHERE T1.SlpName = N'[%0]' AND T0.DocDate >= '[%1]' AND T0.DocDate <= '[%2]'
What this seems to do is show only invoices. So if a customer has to return a shipment for us to fix and we send it back out, the sales employee associated with that will get commission on two shipments when in reality it should only be one. The credit memo issued for that replacement shipment doesn't show up on the commission report to offset that extra invoice. How would I change this query to include credit memos?