Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Commission report to include credit memos

Hello,

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?

Thank You,

David

Tags:
Former Member
replied

Hi David,

Please try Below Query

SELECT 'AR Invocie' [Type], 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 T0.Canceled ='N'

and T1.SlpName  = N'[%0]'

AND T0.DocDate >= '[%1]'

AND  T0.DocDate <= '[%2]'

Union All

SELECT 'AR Credit Memo' [Type], 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 ORIN T0

INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

WHERE T0.Canceled ='N'

and T1.SlpName  = N'[%0]'

AND T0.DocDate >= '[%1]'

AND  T0.DocDate <= '[%2]'

Thanks

Unnikrishnan

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question