用友U9 使用SQL语句创建报表-采购到货及时率报表

用友U9 使用SQL语句创建报表-采购到货及时率报表

逻辑：使用采购订单行的要求交货日期减去收货行的确认入库日期

SQL语句如下：

select 

a.CreatedOn as 创建时间,

b.DocNo as 收货单号,

b.Supplier_Code as 供应商编号,

d.Name as 供应商名称,

a.DocLineNo as 收货单行号,

a.ItemInfo_ItemCode as 料号,

a.ItemInfo_ItemName as 物料名称,

c.Name as 仓库,

a.RcvQtyCU as 入库数量,

a.ConfirmDate as 入库确认日期,

a.SrcPO_SrcDocNo as 采购订单号,

a.SrcPO_SrcDocLineNo as 采购订单行号,

g.DeliveryDate as 要求交货日期,

DATEDIFF(DAY,a.ConfirmDate,g.DeliveryDate) as 延期天数,

a.Status as 状态  

from PM_RcvLine a 

inner join PM_Receivement b on a.Receivement=b.ID

inner join CBO_Wh_Trl c on a.Wh=c.ID

inner join CBO_Supplier_Trl d on b.Supplier_Supplier=d.ID

inner join PM_POShipLine g on g.ID=a.SrcDoc_SrcDocSubLine_EntityID   -- 用收货单的来源单号的采购子行实体ID进行关联

where a.Status=5 and  a.SrcDocType=1 

显示效果：