Các truy vấn SQL trong SAP Business One

Các truy vấn SQL có thể giúp trích xuất dữ liệu từ SAP Business One. Bài viết này sẽ giới thiệu hai công cụ bạn có thể sử dụng để tạo truy vấn và tài liệu eLearning có thể giúp giải quyết các vấn đề về SQL. Trong phần cuối của bài viết, chúng tôi cũng đưa ra một số ví dụ tham khảo.

I/ Công cụ để tạo truy vấn

Ngôn ngữ truy vấn có cấu trúc (SQL) dựa trên ý tưởng là đặt tất cả dữ liệu vào cơ sở dữ liệu và sử dụng ngôn ngữ máy tính cụ thể để tìm và chỉnh sửa dữ liệu đó. Để truy vấn dữ liệu ra khỏi SAP Business One, chúng tôi có hai công cụ để tạo câu lệnh SQL: Trình hướng dẫn truy vấn (Query Wizard) và Trình tạo truy vấn (Query Generator). Bạn có thể tìm thấy cả hai công cụ này trong Tools > Queries.

Query Wizard: KHÔNG yêu cầu kiến thức SQL. Trong đó có 5 bước trong trình hướng dẫn sẽ hướng dẫn bạn từng bước để tạo các truy vấn. Dựa trên lựa chọn của bạn, hệ thống sẽ tạo ra câu lệnh và hiển thị kết quả trực tiếp.

Query Generator:  yêu cầu kiến thức SQL. Giao diện thân thiện với người dùng để tạo một câu lệnh SQL. Hệ thống cũng hiển thị các lệnh SQL để bạn có thể chỉnh sửa chúng trực tiếp.

II/ eLearning Materials 

Nếu bạn cần eLearning miễn phí cho hai công cụ này, hãy truy cập SAP Business One Academy, nhấp vào Implementation and Support > Customization Tools và xem tài liệu có sẵn cho Truy vấn ( Queries 9.0).

Lưu ý

1. Đảm bảo rằng người dùng chịu trách nhiệm viết các truy vấn chính xác.
2. Trong SAP Business One, người dùng chỉ có thể chạy câu lệnh SELECT. Một số câu lệnh khác, chẳng hạn như INSERT, UPDATE, DELETE và ALTER, không thể chỉnh sửa được trong SAP Business one.
3. Thông tin hệ thống và các tham chiếu cơ sở dữ liệu SAP có thể giúp xác định bảng nào sẽ lấy dữ liệu từ đó. 

III/ Examples

Dưới đây là một số truy vấn mẫu được sử dụng trong SAP Business One. 

1/ Open Sales orders by customer

SELECT T0.”CardCode”, T0.”CardName”, T0.”DocNum”, T0.”DocDate”, T0.”DocTotal”

FROM ORDR T0

WHERE T0.”DocStatus” =’O’

ORDER BY T0.”CardCode”

Use [%0] to allow a user to select customers with specific characteristic

SELECT T0.”CardCode”, T0.”CardName”, T0.”DocNum”, T0.”DocDate”, T0.”DocTotal”

FROM ORDR T0

WHERE T0.”DocStatus” =’O’ AND T0.”CardCode” Like ‘%%[%0]%%’

ORDER BY T0.”CardCode”

2/ The amount of Open Sales Orders grouped by customer

SELECT T0.”CardCode”, T0.”CardName”, SUM(T0.”DocTotal”)

FROM ORDR T0

WHERE T0.”DocStatus” =’O’

ORDER BY T0.”CardCode”, T0.”CardName”

3/ Open Sales Orders by Customer Group

SELECT T0.”CardCode”, T0.”CardName”, T0.”DocNum”, T0.”DocDate”, T0.”DocTotal”, T2.”GroupName”

FROM ORDR T0 INNER JOIN OCRD T1 ON T0.”CardCode” = T1.”CardCode” INNER JOIN OCRG T2 ON T1.”GroupCode” = T2.”GroupCode”

WHERE T0.”DocStatus” =’O’

ORDER BY T2.”GroupName”

4/ Select customers with specific characteristic in specific period

SELECT T0.”CardCode”, T0.”CardName”, T0.”DocNum”, T0.”DocDate”, T1.”ItemCode”, T1.”Dscription”, T1.”Quantity”, T1.”OpenQty”, T1.”Price”, T1.”LineTotal”

FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.”DocEntry” = T1.”DocEntry”

WHERE T0.”DocStatus” =’O’ AND  T1.”LineStatus” =’O’

AND T0.”CardName” LIKE ‘%%[%0]%%’

AND T0.”DocDate”  >= [%1] AND T0.”DocDate” <=[%2]

5/ The amount of open quantity and order value grouped by item

SELECT T0.”ItemCode”, T0.”Dscription”, SUM(T0.”OpenQty”) AS “Total Open Qty”, SUM(T0.”LineTotal”) AS “Total Order Value”

FROM RDR1 T0

GROUP BY T0.”ItemCode”, T0.”Dscription”

6/ Closed Sales Order lines that short shipped or never delivered

SELECT T1.”ItemCode”, T1.”Dscription”, T1.”Quantity” AS “Order Qty”, T2.”Quantity” AS “Delivered Qty”, T1.”Price”, T1.”LineTotal”

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.”DocEntry” = T1.”DocEntry” LEFT JOIN DLN1 T2 ON T1.”DocEntry”= T2.”BaseEntry” AND T1.”LineNum” = T2.”BaseLine”

WHERE T0.”DocStatus” =’C’ AND T1.”LineStatus” =’C’ AND ( T1.”Quantity” – T2.”Quantity” >0 OR  T2.”Quantity” IS NULL)

ORDER BY T1.”ItemCode”

7/ Open Purchase Orders by Vendor

SELECT T0.”CardCode”, T0.”CardName”, T0.”DocNum”, T0.”DocDate”, T0.”DocDueDate”, T0.”DocTotal”

FROM OPOR T0

WHERE T0.”DocStatus” = ‘O’

ORDER BY T0.”CardName”

8/ Items on Open Purchase Orders

SELECT T1.”ItemCode”, T1.”Dscription”, T1.”Quantity”, T1.”OpenQty”, T0.”DocDueDate”, T0.”CardCode”, T0.”CardName”

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.”DocEntry” = T1.”DocEntry”

WHERE T1.”LineStatus” =’O’

ORDER BY T1.”ItemCode”

9/ Items in stock sorted by item group

SELECT T0.”ItemCode”, T0.”ItemName”, T0.”ItmsGrpCod”, T1.”ItmsGrpNam”, T0.”OnHand”, T0.”OnOrder”, T0.”CardCode”

FROM “SBODEMOUS”.”OITM” T0 INNER JOIN “SBODEMOUS”.”OITB” T1 ON T0.”ItmsGrpCod” = T1.”ItmsGrpCod” INNER JOIN OITW T2 ON T0.”ItemCode” = T2.”ItemCode”

ORDER BY T1.”ItmsGrpNam”

10/ Items in stock with specific warehouse

SELECT T0.”ItemCode”, T0.”ItemName”, T0.”ItmsGrpCod”, T1.”ItmsGrpNam”, T2.”WhsCode”, T2.”OnHand”, T0.”OnOrder”, T0.”CardCode”

FROM “SBODEMOUS”.”OITM” T0 INNER JOIN “SBODEMOUS”.”OITB”  T1 ON T0.”ItmsGrpCod” = T1.”ItmsGrpCod” INNER JOIN OITW T2 ON T0.”ItemCode” = T2.”ItemCode”

WHERE T2.”WhsCode” LIKE ‘%%[%0]%%’

ORDER BY T1.”ItmsGrpNam”

11/ Business Partner account balances and billing addresses

SELECT T0.”CardCode”, T0.”CardName”, T0.”GroupCode”, T0.”CntctPrsn”, T0.”Balance”, T1.”Street”, T1.”Block”, T1.”City”, T1.”State”, T1.”ZipCode”

FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.”CardCode” = T1.”CardCode”

WHERE T1.”AdresType” =’B’ AND  T0.”Balance” >=1000

ORDER BY T0.”GroupCode”, T0.”CardCode”

12/ Balances by business partner group

SELECT T1.”GroupName”, SUM(T0.”Balance”)

FROM OCRD T0  INNER JOIN OCRG T1 ON T0.”GroupCode” = T1.”GroupCode”

GROUP BY T1.”GroupName”