iStock-623273520.jpg

Often times you need to track different activities in SAP Business One, manage KPI's or generate ad-hoc data sets for further analysis. The SAP Business One Query Tools are great to do this but sometimes it can take a while to master the process of building queries and understanding the different concepts of table joins asnd also the data structures in SAP Business One.

 

This is where the assistance of your peers can be invaluable to help you understand how something is achieved. If you are like me, when you are trying to understand how a technology works it's helpful to have examples to refer to and its even better when those examples can be leveraged in day to day operations of the solution.

 

 

Fortunately I found a recent scenario on the Facebook group I moderate ehre a member of the group wanted to find out  which suppliers were late with their deliveries and two other members stepped up and provided the necessary queries to assist and they agreed to share them with you here.

 

 

Heres the first that shows the deliveries that were late written by John Eduardo Alfaro Vivanco from Cartagena in Bolivia

 

 

SELECT A.DocEntry , A.DocNum , C.DocDueDate AS FechaEntrega , A.DocDate
FROM OPDN A INNER JOIN PDN1 B ON A.DocEntry = B.DocEntry
INNER JOIN OPOR C ON B.BaseEntry = C.DocEntry

WHERE A.DocDate > C.DocDueDate
GROUP BY A.DocEntry , A.DocNum , C.DocDueDate , A.DocDate

 

And another variation that shows deliveries not yet received that are running late written by Joerg Aldinger from SAP Business One partner Consultoria in the Dominican Republic

 

SELECT T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate", T0."DocDueDate" FROM OPOR T0 WHERE T0."DocDueDate" < CURRENT_DATE AND T0."DocStatus" = 'O'


For MS SQL instead of HANA exchange the string CURRENT_DATE for GETDATE()..

 

Thanks to both John and Joerg for their willingness to share their work.