Tips SAP Indonesia – Sometimes we need to know how many GRPO documents we have created in certain period and the details of those documents in SAP Business One.
Maybe standard GRPO information provided from SAP Busines One is not complete enough or maybe it doesn’t suite the information you need.
Below is one example of using a custom report query to show list of Header and Detail Goods Received from Vendor (GRPO) in SAP Business One, created in certain date period along with “Open Qty” information :
/* List GRPO Per Periode */
SELECT
CASE WHEN T1.[LineNum] = ” THEN T0.[DocNum] ELSE NULL END GRPO_Number,
CASE WHEN T1.[LineNum] = ” THEN T0.[DocDate] ELSE NULL END GRPO_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] = ” and T0.[Canceled] = ‘Y’ THEN ‘CANCELED’
WHEN T1.[LineNum] = ” and T0.[Canceled] = ‘C’ THEN ‘CANCELATION’
WHEN T1.[LineNum] = ” and T0.[Canceled] = ‘N’ THEN ”
ELSE NULL
END CANCEL_STATUS,
CASE WHEN T1.[LineNum] = ” THEN 1 ELSE T1.[LineNum]+1 END LINE_NO,
T1.[ItemCode],
T1.[Dscription],
T1.[Quantity],
T1.[OpenCreQty] OpenQty,
T1.[Price],
T1.[Currency],
T1.[Rate],
T1.[DiscPrcnt],
T1.[LineTotal]
FROM OPDN T0
INNER JOIN PDN1 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 GRPO 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/
