Tips SAP Indonesia – As we know, SAP Business One provides a feature / facilities to make it easier for us in retrieving data from the system using queries to the database.
In this example, the standard Purchase Order report from SAP Business One does not satisfy you because it does not directly display Header Data and Details, or there are certain additional fields that you want to display, even custom fields.
Below is one example of using a query to show list of Header and Detail Purchase Orders (PO) in SAP Business One, created in certain date period :
/* List PO Per Periode */
SELECT
CASE WHEN T1.[LineNum] = ” THEN T0.[DocNum] ELSE NULL END PO_Number,
CASE WHEN T1.[LineNum] = ” THEN T0.[DocDate] ELSE NULL END PO_Date,
CASE WHEN T1.[LineNum] = ” THEN T0.[CardCode] ELSE NULL END Vendor_Code,
CASE WHEN T1.[LineNum] = ” THEN T0.[CardName] ELSE NULL END Vendor_Name,
CASE WHEN T1.[LineNum] = ” THEN 1 ELSE T1.[LineNum]+1 END LINE_NO,
T1.[ItemCode],
T1.[Dscription],
T1.[Quantity],
T1.[Price],
T1.[Currency],
T1.[LineTotal]
FROM OPOR T0
INNER JOIN POR1 T1
ON T0.[DocEntry] = T1.[DocEntry]
AND T0.[DocDate] >= [%0]
AND T0.[DocDate] <= [%1]
ORDER BY T0.[DocNum], T1.[LineNum]
When we execute the query, SAP Business One will ask for period of PO date that we want to retrieve
And when we click the “OK” button, SAP Business One will execute the query and show the result as below :
You can see other tips related with query manager in SAP Business One in the link below :
- How to Use Custom Parameter on Query Manager
https://www.sap-business-one-tips.com/how-to-use-custom-parameter-on-query-manager/ - Easier Way to Search Query on Query Manager
https://www.sap-business-one-tips.com/easier-way-to-search-query-on-query-manager/ - How to Display Total for a Column in SAP Business One Query Manager
https://www.sap-business-one-tips.com/how-to-display-total-for-a-column-in-sap-business-one-query-manager/
