SBO查询

1。订单状态查询－－按客户 SELECT T0.DocNum, T0.DocType, T0.DocStatus, T0.CardCode, T0.CardName, T0.NumAtCard FROM ORDR T0 WHERE T0.CardCode ='[%0]' 2。订单已发货未开票查询 SELECT T0.DocNum,T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription, T1.Quantity, T1.ShipDate, T1.OpenQty, T1.Price,VatPrcnt,openqty*price*(VatPrcnt/100 ) as'未开票金额' FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.DocStatus = 'o' ORDER BY T0.DocNum 3。生产订单逾期查询 SELECT T0.DocNum as '生产订单号', T0.ItemCode, T0.Status, T0.PlannedQty as '计划数量', T0.CmpltQty as '已完工数量', T0.PostDate as '下达日期', T0.DueDate as '计划完工日期' FROM OWOR T0 WHERE T0.CmpltQty < T0.PlannedQty AND T0.DueDate <=Getdate() AND T0.Status <> 'c' 4。订单需要发货日期和发货单上实际的发货日期进行查询和对比 SELECT T0.DocNum as 销售订单号, T0.DocDate, T0.CardCode, T0.CardName, T1.DocNum as 发货单号, T1.DocDate, T2.BaseRef FROM ORDR T0, ODLN T1 INNER JOIN DLN1 T2 ON T1.DocEntry = T2.DocEntry WHERE T2.BaseRef = T0.DocNum FOR BROWSE 5。显示客户参考编码(PO号)-应收发票 1 在应收发票行添加用户字段； 2 建立查询：Select t0.numatcard from ordr t0 left join dln1 t1 on t0.docnum = t1. baseentry and t1.basetype = 17 where ($[$38.43.Number] = 15 and $[$38.45.Number] = t1.docentry and $[$38.46.Number] = t1.linenum ) or ($[$38.43.Number] =17 and $[$38.45.Number] = t0.docnum) 3 在自定义字段上面链接查询，选择物料更新自动更新 6。客户信贷额度偏移 SELECT T0.CardCode, T0.CardName, T0.CreditLine, T0.Balance,T0.CreditLine -T0.Balance as 偏离 FROM OCRD T0 WHERE T0.CardType = 'c' and T0.CreditLine < T0.Balance FOR BROWSE SAP Business One 查询向导 a:业务伙伴Marketing -----Direct Shipment – Customer 客户收货地 SELECT T0.CardCode AS '业务伙伴代码', T0.CardName AS '业务伙伴名称', T0.CntctPrsn AS '联系人', T0.Address AS '收票方街道', T0.ZipCode AS '收票方邮政编码', T0.City AS '收票方城市' FROM [dbo].[OCRD] 业务伙伴T0 WHERE T0.CardType = N'c' ORDER BY T0.CardCode FOR BROWSE b:发票 Sales- Invoice List SELECT T0.DocNum AS '凭证编号', T0.CardCode AS '客户/供应商代码', T0.CardName AS '客户/供应商名称', T0.DocTotal AS '凭证总计', T0.DocDate AS '过帐日期' FROM [dbo].[OINV] T0 WHERE (T0.DocDate >= CONVERT(DATETIME, '[%0]', 112) ) AND (T0.DocDate >= CONVERT(DATETIME, '[%1]', 112) ) ORDER BY T0.DocDate FOR BROWSE C:Sales---Open quotations OQUT报价, QUT1报价－行, OSLP销售代表 and OITM物料 SELECT T0.CardCode AS '客户/供应商代码', T0.CardName AS '客户/供应商名称', T1.ItemCode AS '物料代码', T3.ItemName AS '物料描述', T0.DocTotal AS '凭证总计', T2.SlpName AS '销售代表姓名' FROM [dbo].[QUT1] T1 INNER JOIN [dbo].[OQUT] T0 ON T1.DocEntry = T0.DocEntry INNER JOIN [dbo].[OSLP] T2 ON T2.SlpCode = T1.SlpCode INNER JOIN [dbo].[OITM] T3 ON T3.ItemCode = T1.ItemCode AND T3.ItemCode = T1.OrigItem AND T2.SlpCode = T0.SlpCode WHERE T0.DocStatus = N'o' FOR BROWSE