Have you ever made a custom query? example: GRPO Receipt List Query and it working, but still have difficulty when checking or seeing the documents directly one by one? whether you want to check the Business Partner, or the other because there is no golden arrow directly to the destination document.
Below is one example of the query results to add the golden arrow as desired as shown.
The thing that needs to be done is to create a query view made in SQL Server Management Studio, follow these step:
- Open the SSMS application (SQL Server Management Studio).
- Select the Database that is used in SAP with right-click and choose a new Query.
- Enter the query view (here using the example query view that is changed from Goods Receipt PO), and select execute (F5).
Example query view for GRPO list:
CREATE VIEW [LIST_GRPO] AS 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] ,T0.[DocDate] FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
- Open the SAP Business One application, and enter the query that is joined with the query view, so that the golden arrow can appear according to the document.
Examples of queries joined with query view:
- Select the Execute, and enter the date that you want, select OK.
- The golden arrow appears.
The golden arrow can be raised by displaying a key in the query view as a column, then joined to the query used in the SAP Business One query manager.