Sales analysis report query

[former_member550499]

Hello,

Can you help me get a sales report by item that gives me the exact same result as the standard sales analysis report. Including OINV, ORIN, ODPI and the discounts made in these documents.

I have this query but it gives me a small difference

SELECT

T1.[ItemCode], Case When T0.DiscPrcnt = 0 then T1.LineTotal else (T1.linetotal*T0.DiscPrcnt/100) END

FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] left outer JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode]

Where t0.canceled='n'

UNION ALL

SELECT T1.[ItemCode], Case When T0.DiscPrcnt = 0 then T1.LineTotal*-1 else (T1.linetotal*-1)*(T0.DiscPrcnt/100) END

FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] left outer JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode]

Where t0.canceled='n'

UNION ALL

SELECT T1.[ItemCode], Case When T0.DiscPrcnt = 0 then T1.LineTotal else (T1.linetotal*T0.DiscPrcnt/100) END

FROM ODPI T0 INNER JOIN DPI1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] left outer JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode]

Where t0.canceled='n'

Thanks

[Johan_H]

Hi Juan,

Can you please tell us in a little more detail what the small difference is?

Regards,

Johan

[former_member390407 · Accepted solution]

Hello Juan Carlos,

If you want to get the query under the SAP report you can use SQL Server Profiler. So if we trace this operation we get that there is a number of stored procedures for this report:

Items option: all SP started with: TmSp_Sales_An_Itm_*. SAP Calls different procedures depending on options chosen.

Customers option: all SP started with: TmSp_Sales_An_Crd_*. SAP Calls different procedures depending on options chosen.

Sales employees option: all SP started with: TmSp_Sales_An_Sng_*. SAP Calls different procedures depending on options chosen.

This procedures are encrypted and to get the list of parameters and analyse their's queries you need to decrypt it firstly. You can do that using a free tool from Devart: https://www.devart.com/dbforge/sql/sqldecryptor/

That is a nice and easy to use tool for SQL Server program modules decryption.

After analysing you can just use the existing procedures passing necessary parameters there.