PT Sterling Tulus Cemerlang
www.sterling-team.com

Phone
+62-877-8655-5055
+62 (21) 580-6055

Email
sales-enquiry@sterling-team.com

Address
Sampoerna Strategic Square
South Tower 18th
Jl Jend. Sudirman kav 45-46
Jakarta 12930 - Indonesia

400+ FREE Tips for you from Sterling Tulus Cemerlang (STEM) - SAP Indonesia Gold Partner, and Become SAP B1 Super User

Custom Report – Query PR VS PO VS GRPO

SAP Business One Tips Custom Report Query PR vs PO vs GRPO
SHARE ON


As we know, SAP Business One provides facilities/features to make it easier for us to retrieve data from the system by the query to the database.

Like this example, sometimes you need a comparison between a Purchase Request (PR), Purchase Order (PO) and how many items have been received from the PO process, namely Goods Receipt PO (GRPO).

In SAP Business One, there may already be standard reports regarding PR, PO, and GRPO documents separately, but if you want to check the three reports, you have to open the reports separately, while what you want is to open the three reports (PO, PR, GRPO) in one document to see the comparison.

Below is an example to display a list (PO, PR, GRPO) to customers in SAP Business One using a query, based on a certain period:

/* select from [dbo].[OPRQ] T0 */
DECLARE DateF TIMESTAMP;
DECLARE DateT TIMESTAMP;
DateF := /* T0."DocDate" */ '[%0]';
DateT := /* T0."DocDate" */ '[%1]';

SELECT
     T1."DocDate" AS "PR Date"
     ,T1."ReqDate" AS "Required Date"
     ,T1."DocNum" AS "PR No."
     ,T2."ItemCode" AS "Item Code"
     ,T2."Dscription" AS "Item Description"
     ,T2."unitMsr" AS "UoM"
     ,T2."Quantity" AS "Required Qty"  
     ,T2."OpenQty" AS "Open Qty"  
     ,CASE T1."DocStatus"
           WHEN 'C'
                THEN (
                     CASE T1."CANCELED"
                           WHEN 'Y'
                                THEN 'Canceled'
                           ELSE 'Closed'
                           END
                     )
           WHEN 'O'
                THEN 'Open'
           ELSE T1."DocStatus"
           END AS "Status PR"   
     ,T1."Comments" AS "Comments"
     ,'' AS " "
     ,T4."DocDate" AS "PO Date"
     ,T4."DocEntry" AS "PO#"
     ,T4."DocNum" AS "No PO"
     ,T4."CardCode" AS "Vendor Code"
     ,T4."CardName" AS "Vendor Name"
     ,IFNULL(T3."Quantity",0) AS "Qty PO"
     ,T6."DocNum" AS "No GR"
     ,T6."DocDate" AS "GR Date"
     ,T5."WhsCode" AS "Whs"
     ,T5."unitMsr" AS "UoM"
     ,IFNULL(T5."Quantity",0) AS "Qty GR"
     ,IFNULL(T7."Quantity",0) AS "Qty Retur"
     ,T5."OpenQty" AS "Open Qty"
     ,CASE T4."DocStatus"
           WHEN 'C'
                THEN (
                           CASE T4."CANCELED"
                                WHEN 'Y'
                                     THEN 'Canceled'
                                ELSE 'Closed'
                                END
                           )
           WHEN 'O'
                THEN 'Open'
           ELSE T4."DocStatus"
           END AS "Status PO"
     ,T6."Comments"
FROM OPRQ T1
LEFT JOIN PRQ1 T2 ON T1."DocEntry" = T2."DocEntry"
LEFT JOIN POR1 T3 ON T3."BaseEntry" = T1."DocEntry" AND T3."BaseLine" = T2."LineNum" AND T3."BaseType" = '1470000113'
LEFT JOIN OPOR T4 ON T4."DocEntry" = T3."DocEntry"   
LEFT JOIN PDN1 T5 ON T5."BaseEntry" = T4."DocEntry" AND T5."BaseLine" = T3."LineNum" AND T5."BaseType" = '22'
LEFT JOIN OPDN T6 ON T6."DocEntry" = T5."DocEntry" AND T6."CANCELED" = 'N'
LEFT JOIN RPD1 T7 ON T7."BaseEntry" = T6."DocEntry" AND T7."BaseLine" = T5."LineNum" AND T7."BaseType" = '20'
LEFT JOIN ORPD T8 ON T8."DocEntry" = T7."DocEntry" AND T8."CANCELED" = 'N'
LEFT JOIN OITM TA ON TA."ItemCode" = T2."ItemCode"
WHERE T1."DocDate"
BETWEEN DateF AND DateT         
ORDER BY T1."DocDate",T1."DocNum",T2."ItemCode", T2."Dscription", T2."unitMsr";

When this query is executed, SAP Business One will display the date parameters of the PR document that you want to output data:

SAP Business One Tips - Custom Report – Query PR VS PO VS GRPO

SAP Business One Tips – Custom Report – Query PR VS PO VS GRPO

And when we click the “OK” button, SAP Business One will display the results as shown below:

SAP Business One Tips - Custom Report – Query PR VS PO VS GRPO

SAP Business One Tips – Custom Report – Query PR VS PO VS GRPO

Run Your Business With The Piece of Mind Using SAP Business One

This article was written by PT. Sterling Tulus Cemerlang, SAP Business One GOLD Partner in Indonesia.
More details about SAP Business One,
email to: sales-enquiry@sterling-team.com or call +6221-5806336 or wa +6287786555055

, , , , , , , ,