cancel
Showing results for 
Search instead for 
Did you mean: 

Monthly Sales Employee Analysis

larryenet
Participant
0 Kudos

Hello SAP Gurus!!

Can someone please help me complete this query with the following information added:

Gross Profit

Gross %

Totals at the bottom of Query

and also I need the Query to list Invoices that have been Paid only.

Here is the Query:

SELECT 'Invoice' "Invoice/AR Credit Memo", T0.CardCode "BP Code", T0.CardCode "Cust Name", T0.DocDate,T0.DocNum, T0.DocTotal "Gross Amt", T0.TotalExpns "Freight/Svc Charge", (T0.DocTotal - T0.VatSum-T0.TotalExpns) "Net Order", T1.SlpName "Sales Rep"

FROM [dbo].[OINV] T0 INNER JOIN [dbo].[OSLP] T1 ON T0.SlpCode = T1.SlpCode

WHERE T1.SlpName like '[%0]%' AND (T0.DocDate BETWEEN '[%1]' AND '[%2]')

Thank you in advance for your help.

Kind regards,

Larry T.

Accepted Solutions (1)

Accepted Solutions (1)

larryenet
Participant
0 Kudos

Here is my query:

SELECT 'Invoice' "Invoice/AR Credit Memo", T0.DocNum,T0.CardCode "BP Code", T0.CardCode "Cust Name", T0.DocDate, T0.DocTotal "Sales Amt", T0.GrosProfit, T1.SlpName "Sales Rep"

FROM [dbo].[OINV] T0 INNER JOIN [dbo].[OSLP] T1 ON T0.SlpCode = T1.SlpCode

WHERE T1.SlpName like '[%0]%' AND (T0.DocDate BETWEEN '[%1]' AND '[%2]') AND T0.PaidSum <> 0

Can someone please help add AR Credit Memos to query?

former_member227598
Active Contributor
0 Kudos

Hi Larry ,

Please try below query .....

SELECT  CASE WHEN T0.ObjType = 13 THEN 'AR Invoice' WHEN T0.ObjType = 14 THEN 'AR Credit Memo' ELSE 'Error' END AS 'TransType',

T0.CardCode "BP Code",

T0.CardCode "Cust Name",

T0.DocDate,

T0.DocNum,

T0.DocTotal "Gross Amt",

T0.TotalExpns "Freight/Svc Charge",

(T0.DocTotal - T0.VatSum-T0.TotalExpns) "Net Order",

T1.SlpName "Sales Rep"

FROM [dbo].[OINV] T0 INNER JOIN [dbo].[OSLP] T1 ON T0.SlpCode = T1.SlpCode

WHERE

T1.SlpName like '[%0]%' AND

(T0.DocDate BETWEEN '[%1]' AND '[%2]')

AND T0.PaidSum <> 0

UNION ALL

SELECT CASE WHEN T0.ObjType = 13 THEN 'AR Invoice' WHEN T0.ObjType = 14 THEN 'AR Credit Memo' ELSE 'Error' END AS 'TransType',

T0.CardCode "BP Code",

T0.CardCode "Cust Name",

T0.DocDate,

T0.DocNum,

(T0.DocTotal*-1) "Gross Amt",

(T0.TotalExpns*-1) "Freight/Svc Charge",

((T0.DocTotal - T0.VatSum-T0.TotalExpns)*-1) "Net Order",

T1.SlpName "Sales Rep"

FROM [dbo].[ORIN] T0 INNER JOIN [dbo].[OSLP] T1 ON T0.SlpCode = T1.SlpCode

WHERE

T1.SlpName like '[%0]%' AND

(T0.DocDate BETWEEN '[%1]' AND '[%2]')

AND T0.PaidSum <> 0

Rgds,

Kamlesh Naware

larryenet
Participant
0 Kudos

Hello Kamlesh Naware,

Thank you very much!!

That was very close and I tweaked it a little bit with the data I needed.

Here it is:

SELECT  CASE WHEN T0.ObjType = 13 THEN 'AR Invoice' WHEN T0.ObjType = 14 THEN 'AR Credit Memo' ELSE 'Error' END AS 'TransType',

T0.CardCode "BP Code",

T0.CardCode "Cust Name",

T0.DocDate,

T0.DocNum,

T0.DocTotal "Gross Amt",

T0.GrosProfit,

T1.SlpName "Sales Rep"

FROM [dbo].[OINV] T0 INNER JOIN [dbo].[OSLP] T1 ON T0.SlpCode = T1.SlpCode

WHERE

T1.SlpName like '[%0]%' AND

(T0.DocDate BETWEEN '[%1]' AND '[%2]')

AND T0.PaidSum <> 0

UNION ALL

SELECT CASE WHEN T0.ObjType = 13 THEN 'AR Invoice' WHEN T0.ObjType = 14 THEN 'AR Credit Memo' ELSE 'Error' END AS 'TransType',

T0.CardCode "BP Code",

T0.CardCode "Cust Name",

T0.DocDate,

T0.DocNum,

(T0.DocTotal*-1) "Gross Amt",

T0.GrosProfit,

T1.SlpName "Sales Rep"

FROM [dbo].[ORIN] T0 INNER JOIN [dbo].[OSLP] T1 ON T0.SlpCode = T1.SlpCode

WHERE

T1.SlpName like '[%0]%' AND

(T0.DocDate BETWEEN '[%1]' AND '[%2]')

AND T0.PaidSum <> 0

Kind regards,

Larry T.

larryenet
Participant
0 Kudos

I added T0.GrosProfit to the query and it shows up, but it is not showing when there should be a negative dollar amount for Credit Memos.

See pic:

Thank you.

Larry T.

Answers (1)

Answers (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Please use standard sales analysis report instead of customized query.

Thanks

larryenet
Participant
0 Kudos

How do I make it show only the invoices that have already been paid?

Thanks

former_member185296
Active Participant
0 Kudos

SELECT 'Invoice' "Invoice/AR Credit Memo", T0.CardCode "BP Code", T0.CardCode "Cust Name", T0.DocDate,T0.DocNum, T0.DocTotal "Gross Amt", T0.TotalExpns "Freight/Svc Charge", (T0.DocTotal - T0.VatSum-T0.TotalExpns) "Net Order", T1.SlpName "Sales Rep"

FROM [dbo].[OINV] T0 INNER JOIN [dbo].[OSLP] T1 ON T0.SlpCode = T1.SlpCode

WHERE T1.SlpName like '[%0]%' AND (T0.DocDate BETWEEN '[%1]' AND '[%2]') AND T0.PaidSum <> 0

USE THIS QUERY TO GET THE DETAILS OF PAID AR INVOICES.

Regards,

Vinoth

larryenet
Participant
0 Kudos

Thank you, that is very helpful. Is it possible to add the following:

2 more columns added:


Gross Profit

Gross Profit %


Credit Memos are not showing up


if possible, Totals at the bottom of Query



larryenet
Participant
0 Kudos

I figured out how to put in Gross Profit, but I still need it to show Credit Memos as well:

SELECT 'Invoice' "Invoice/AR Credit Memo", T0.CardCode "BP Code", T0.CardCode "Cust Name", T0.DocDate,T0.DocNum, T0.DocTotal "Gross Amt", T0.TotalExpns "Freight/Svc Charge", (T0.DocTotal - T0.VatSum-T0.TotalExpns) "Net Order",T0.GrosProfit, T1.SlpName "Sales Rep"

FROM [dbo].[OINV] T0 INNER JOIN [dbo].[OSLP] T1 ON T0.SlpCode = T1.SlpCode

WHERE T1.SlpName like '[%0]%' AND (T0.DocDate BETWEEN '[%1]' AND '[%2]') AND T0.PaidSum <> 0

Thank you for all of your help.

Larry T.