Tình huống
Người dùng muốn truy vấn danh sách Batch/Serial đã được giữ trước (reserve / allocate) cho các chứng từ nào trong hệ thống SAP Business One
Giải pháp
Sử dụng câu query sau
SELECTS0.”AllocateTp” AS “DocType”, S0.”AllocatEnt” AS “DocEntry”, S0.”AllocateLn” AS “DocLine”, S0.”ManagedBy” AS “ManagedBy”,S0.”ItemCode” AS “ItemCode”, S0.”SysNumber” AS “SysNumber”, S0.”LocCode” AS “Warehouse”, S1.”DistNumber” AS “BatchNumber”,SUM(S0.”AllocQty”) AS “Quantity”FROM(SELECTT0.”AllocateTp”, T0.”AllocatEnt”, T0.”AllocateLn”, T0.”ManagedBy”, T1.”LogEntry”,T0.”ItemCode”, T1.”SysNumber”, T0.”LocCode”, T0.”TransId”, T1.”AllocQty”FROM OITL T0 INNER JOIN ITL1 T1 ON T1.”LogEntry” = T0.”LogEntry”WHERE T0.”AllocateTp” IN (13, 17, 1250000001, 202 , 234000032)) S0left join OBTN S1 on S0.”ItemCode”=S1.”ItemCode” and S0.”SysNumber”=S1.”SysNumber”GROUP BY S0.”AllocateTp”, S0.”AllocatEnt”, S0.”AllocateLn”, S0.”ManagedBy”,S0.”ItemCode”, S0.”SysNumber”, S0.”LocCode”, S1.”DistNumber”HAVING SUM(S0.”AllocQty”) <> 0
- 13: AR Reserve Invoice
- 17: Sales Order
- 1250000001: Inventory Transfer Request
- 202: Production Order
- 234000032: Goods Return Request
