Get Item Price based On PriceList Using SQL Query SAP Business one

 Get Item Price based On PriceList Using SQL Query SAPBusiness one

       Are you looking to extract item prices based on pricelists in SAP Business One using SQL queries? In this article, we will walk you through the process step by step, allowing you to leverage the power of SQL to access the information you need efficiently and effectively.

Introduction

SAP Business One is a comprehensive business management solution that offers a wide range of functionalities to help streamline operations and drive growth. One common task that users often need to perform is retrieving item prices based on pricelists. While the SAP Business One user interface provides options to view this information, using SQL queries can offer a more efficient way to extract the data you need.

To get started with extracting item prices based on pricelists using SQL queries in SAP Business One, follow these steps:

Identify the Tables: The first step is to identify the tables within the SAP Business One database that store information related to item prices and pricelists. Common tables that may be relevant include OITM (Item Master Data) and ITM1 (Item Price).

Join the Tables: Once you have identified the relevant tables, you will need to join them together using appropriate keys to retrieve the desired information. For example, you may need to join the OITM and ITM1 tables on the ItemCode field to link item prices to specific items.

Filter by Pricelist: Next, you will need to filter the data based on the pricelist you are interested in. This can typically be done by adding a WHERE clause to your SQL query that specifies the Pricelist field you are targeting.

Select the Price Information: Finally, you can select the specific price information you want to retrieve from the tables. This can include fields such as the ItemCode, Price, Currency, and any other relevant information you need.

Example SQL Query

Here is an example SQL query that you can use to extract item prices based on pricelists in SAP Business One:

 Function GetItemPrice(ByVal strItemCode As String, ByVal PriceList As String, ByVal strFlag As String) As String
        Select Case strFlag
            Case "PriceList"
                Return "SELECT T0.""Price""  FROM ITM1 T0  INNER JOIN OPLN T1 ON T0.""PriceList""=T1.""ListNum"" AND T0.""ItemCode""='" & strItemCode & "' AND T0.""PriceList""='" & PriceList & "'"
            Case "-1"
                Return "SELECT T0.""LastPurPrc"" FROM OITM T0  WHERE T0.""ItemCode""='" & strItemCode & "' "
            Case "-2"
                Return "SELECT CASE WHEN T0.""LstEvlPric"" > 0.0 THEN T0.""LstEvlPric"" ELSE T0.""LastPurPrc"" END AS ""Price""  FROM OITM T0  WHERE T0.""ItemCode""='" & strItemCode & "'"
        End Select
        Return String.Empty
End Function
Here 

• "PriceList" is a PriceList1 To PriceList10
• "-1" -- Last Purchase Price
• "-2" -- Last Evolution Price

         Condition Display 

•               IF LAST Eva Price is zero take Last Pur Price

Conclusion

         In conclusion, utilizing SQL queries to extract item prices based on pricelists in SAP Business One can offer a more efficient and effective way to access the information you need. By following the steps outlined in this article and using the example SQL query provided, you can streamline your data retrieval process and make the most of your SAP Business One system.So, why wait? Start leveraging the power of SQL queries today to get item prices based on pricelists in SAP Business One!