Commission report to include credit memos

[Former Member]

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

[former_member212181 · Accepted solution]

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

[Former Member · Accepted solution]

This one did it. You sir, are a wizard.

[KennedyT21 · Accepted solution]

HI david

have you checked with my reply. if you got answer close the thread with the correct and helpful answers....

Rgds

Kennedy

[Former Member · Accepted solution]

Yes I tried yours and saw the error message "(WLS1) in a red band across the bottom.

[KennedyT21]

Hi David...

Try this

SELECT tt.[Doc #], tt.Customer, tt.[PO Number], tt.[Doc. Total], tt.DocDate,

       tt.SlpName, tt.[% Commission], (tt.Sum_Commissions- tt.ReturnCom) AS Comm

  FROM (

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",0 AS ReturnCom 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]'

union all

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",0,(  ( ( T0.DocTotal - T0.VatSum)*( T1.Commission/100))   )As "Sum_Commissions Ret" FROM orin T0

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

  WHERE T1.SlpName  = N'[%0]' AND T0.DocDate >= '[%1]' AND  T0.DocDate <= '[%2]'

  ) AS tt

regards

Kennedy