On Time Delivery BAQ

Kinetic ERP
1

Hi Guys, Does anyone have a Sample BAQ for Customer or Supplier Ontime Delivery Report

2

This is a simple on-time for customer shipments you can expand on. I just wrote it to show differences in Required Date and ShipDate… so, doesn’t calculate any customer based on-time percentage, or anything like that.

Also doesn’t take into account variations, like customers having multiple ship-tos and you want to combine, not combine, etc. etc. You’d have to adjust for your needs.

select 
	[Customer].[CustID] as [Customer_CustID],
	[OrderRel].[PartNum] as [OrderRel_PartNum],
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	[OrderRel].[OrderNum] as [OrderRel_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[ShipDtl].[PackNum] as [ShipDtl_PackNum],
	[ShipDtl].[PackLine] as [ShipDtl_PackLine],
	(ShipDtl.OurInventoryShipQty+ ShipDtl.OurJobShipQty) as [Calculated_TotalShipped],
	[OrderRel].[ReqDate] as [OrderRel_ReqDate],
	[ShipHead].[ShipDate] as [ShipHead_ShipDate],
	(datediff(day, OrderRel.ReqDate, ShipHead.ShipDate)) as [Calculated_DaysEarlyLate]
from Erp.Customer as Customer
left outer join Erp.OrderHed as OrderHed on 
	Customer.Company = OrderHed.Company
	and Customer.CustNum = OrderHed.CustNum

left outer join Erp.OrderRel as OrderRel on 
	OrderHed.Company = OrderRel.Company
	and OrderHed.OrderNum = OrderRel.OrderNum

left outer join Erp.ShipDtl as ShipDtl on 
	OrderRel.Company = ShipDtl.Company
	and OrderRel.OrderNum = ShipDtl.OrderNum
	and OrderRel.OrderLine = ShipDtl.OrderLine
	and OrderRel.OrderRelNum = ShipDtl.OrderRelNum

left outer join Erp.ShipHead as ShipHead on 
	ShipDtl.Company = ShipHead.Company
	and ShipDtl.PackNum = ShipHead.PackNum
	and ( ShipHead.ShipDate is not null )
1 Like
3

Thanks, much appreciated, let me go through it and modify

1 Like