Skip to Content
0

SAP HANA Commission Report

Sep 28, 2017 at 10:26 AM

52

avatar image
Former Member

Hi all

I am needing some help with a Commission Report.

The goal of the report is to determine how much Commission Sales Employees are meant to receive. However, when Credit notes are generated, it is meant to be subtracted from Invoice. The trick is that they do not link the Credit notes to Invoices so I have to take the Total Invoiced - Total Credit notes for a period of time (1 month).

Please can someone have a look at the query and let me know if you can help?

Many thanks in advance

SELECT DISTINCT 
T0."DocNum" AS "INVOICE NUMBER", 
T0."DocDate" AS "INVOICE DATE" ,
T0."CardCode", 
T0."CardName", 
T3."SlpName",
T1."Currency", 
T1."ECVatGroup" AS "CUSTOMER VAT CODE", 
T0."ShipToCode", 
T0."PaidToDate" AS "PAYMENT RECEIVED", 
T0."DocTotal" AS "TOTAL INVOICED", 
T0."VatSum", 
T4."descript" AS "REGION", 
T1."U_Comm" AS "USER COMMISSIONS", 
T0."DocStatus", 
(T0."DocTotal"-T0."VatSum") as "Excl VAT", 
((T1."U_Comm"/100)*T0."DocTotal") as "Commision Payable", 
T1."GroupCode" 

FROM 
OINV T0  INNER JOIN 
OCRD T1 ON T0."CardCode" = T1."CardCode" INNER JOIN 
CRD1 T2 ON T1."CardCode" = T2."CardCode" INNER JOIN 
OSLP T3 ON T0."SlpCode" = T3."SlpCode" INNER JOIN 
OTER T4 ON T1."Territory" = T4."territryID" INNER JOIN 
INV1 T5 ON T0."DocEntry" = T5."DocEntry" INNER JOIN
ITR1 T6 on T6."SrcObjAbs" = T0."DocEntry" INNER JOIN
OITR T7 on T6."ReconNum" = T7."ReconNum" 


WHERE
T7."ReconDate" >=[%0] and  T7."ReconDate" <=[%1] AND 
T0."DocStatus" = 'C' AND T6."SrcObjTyp" = '13'AND T6."ReconNum" IN


(SELECT T9."ReconNum"
FROM ITR1 T8, ITR1 T9 WHERE T8."SrcObjTyp" = '24' AND T9."SrcObjTyp" = '13' AND T8."ReconNum" = T9."ReconNum" )
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers