PT Sterling Tulus Cemerlang
www.sterling-team.com

Whatsapp
+62-877-8655-5055

Email
sales-enquiry@sterling-team.com

Address
Sampoerna Strategic Square
South Tower Level 30
Jl Jend. Sudirman No. 45-46
Jakarta 12930 – Indonesia

500+ FREE Tips SAP Business One yang dapat menjadikan anda SAP B1 Super User dari Sterling Tulus Cemerlang (STEM)

Custom Report – Query PR VS PO VS GRPO

Custom Report – Query PR VS PO VS GRPO

Seperti yang kita ketahui, SAP Business One menyediakan fasilitas / feature untuk memudahkan kita mengambil data dari system dengan melalui query ke database.

Seperti contoh ini, terkadang anda membutuhkan perbandingan antara Purchase Request (PR), Purchase Order (PO) dan berapa banyak barang yang telah di terima dari proses PO yaitu Goods Receipt PO(GRPO).

Di SAP Business One mungkin sudah ada laporan standard/baku mengenai dokumen PR, PO, dan GRPO  secara terpisah, namun apabila ingin melakukan cek dari ketiga laporan tersebut, maka harus membuka laporannya secara terpisah, sedangkan yang diinginkan adalah membuka ketiga laporan (PO, PR, GRPO) dalam satu dokumen untuk melihat perbandingannya.

Dibawah ini adalah salah satu contoh untuk menampilkan list (PO, PR, GRPO) kepada customer di SAP Business One dengan menggunakan query, berdasarkan periode tertentu :

/* 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";

Saat query ini dijalankan, SAP Business One akan menampilkan parameter tanggal dokumen PR yang ingin dikeluarkan datanya :

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

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

Dan saat kita meng-click “OK” button, SAP Business One akan menampilkan hasil nya seperti pada tampilan berikut :

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

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

Baca juga:

Originally posted 2020-10-15 01:30:49.

Mau Tahu Cara Mudah Mengatur Perusahaan Dengan SAP Business One

Artikel ini ditulis oleh PT. Sterling Tulus Cemerlang
Lebih detail tentang SAP Business One,
email ke: sales-enquiry@sterling-team.com atau hubungi +6221-5806336 atau wa +6287786555055

, , , , , , , ,