What is the best approach to know if BP has special pricing?

[Former Member]

Hi everyone,

my goal is to export special prices from SAP 9.0 to be used later on in another system. In order to minimize the amount of data needed and to have the fastest approach I would like to only touch business partners who really have special prices defined. Is there an easy way (except of building some queries to check the relevant special price tables) to find out, if a BP has special pricing applied? Maybe there is a function in the DI API or a flag somewhere?

I would appreciate any information on this.

Thanks

Beat Meier

[Johan_H · Accepted solution]

Hi Beat,

There are no properties that can tell you whether a BP has special prices, but you can retrieve a list with a very simple query:

SELECT DISTINCT T0.[CardCode]

FROM OSPP T0

LEFT OUTER JOIN SPP1 T1 ON T0.CardCode = T1.ItemCode

WHERE T0.[Discount] <> 0

OR (GETDATE() BETWEEN  T1.[FromDate] AND T1.[ToDate] AND T1.[Discount] <> 0)

Regards,

Johan

[Former Member · Accepted solution]

Hi Johan,

thanks for your help. Before I go into SQL I wanted to check if there are any other options. I believe your query should be extended to SPP2-table and added possibility that the "ToDate" is NULL:

SELECT DISTINCT T0.[CardCode]

FROM OSPP T0

LEFT OUTER JOIN SPP1 T1 ON T0.CardCode = T1.CardCode

LEFT OUTER JOIN SPP2 T2 ON T0.CardCode = T2.CardCode

WHERE T0.[Discount] <> 0

OR (GETDATE() BETWEEN  T1.[FromDate] AND ISNULL(T1.[ToDate], GETDATE()) AND T1.[Discount] <> 0)

OR T2.[Discount] <> 0

Regards,

Beat

[Johan_H · Accepted solution]

Hi Beat,

You are quite right. I forgot about SPP2, because I happen not to use it. And it never hurts to use ISNULL.

But yes, unfortunately there is no simpler way to get this information. Unless perhaps you create a view in SQL Server using this query. With that you could simplify the query in your code to:

SELECT CardCode FROM view_YourView

Regards,

Johan