Average days Once pick list (OPKL) is generated and Delivery note (ODLN) is created in SAP B1 9.1

[shahmed]

Hello folks.

A query that tells me days that once pick list (OPKL) is generated and Delivery note (ODLN) is created in SAP B1 9.1, but i am looking for Average No of days (Consolidated by customer) for same pick date and delivery date difference.

SELECT T0.AbsEntry,
T1.PickEntry, 
T1.OrderEntry,
T1.OrderLine,
T1.BaseObject,
T7.Cardcode,
T7.Cardname,
T3.[NumAtCard],
T3.DocNum AS 'SO #',
T3.Docdate AS 'SO Date',
T0.AbsEntry AS 'Pick #',
T0.Pickdate AS 'Pick Date',
T5.DocNum AS 'DN #',
T5.[DocDate] AS 'DN Date',
T7.DocNum AS 'Invoice #',
T7.Docdate AS 'Invoice date',
Case when T0.Pickdate=T5.DocDate then 1 else datediff(dd,T0.Pickdate, T5.DocDate) end as 'days at legacy', 
T3.[DocStatus]
FROM
OPKL T0
INNER JOIN PKL1 T1 ON T0.AbsEntry = T1.AbsEntry
INNER JOIN RDR1 T2 ON T2.LineNum = T1.OrderLine AND T1.OrderEntry = T2.DocEntry AND T1.BaseObject = 17
INNER JOIN ORDR T3 ON T3.DocEntry = T2.DocEntry
LEFT JOIN DLN1 T4 ON T4.BaseEntry = T2.DocEntry AND T4.BaseLine = T2.LineNum AND T4.BaseType = T3.ObjType
LEFT JOIN ODLN T5 ON T5.DocEntry = T4.DocEntry
LEFT JOIN INV1 T6 ON T6.BaseEntry = T4.DocEntry AND T6.BaseLine = T4.LineNum AND T6.BaseType = T5.ObjType
LEFT JOIN OINV T7 ON T7.DocEntry = T6.DocEntry
where

T3.[DocDate] >=[%0] and 
T3.[DocDate] <=[%1] and T3.[CANCELED] = 'N' 
Order by T1.OrderEntry

Thanks

Shahzad Ahmed

[shahmed]

The above query is also not giving me correct result once i have a back order. For example if a sales order has 10 line items and we dispatched 4 at one time and another 6 second time, them result of 6 items would be negative. i don' t know why its showing like that.

However, based on above query i tried to consolidate my requirement which is as follows. query below is also has a problem due to negative days.

SELECT 
T7.Cardcode,
T7.Cardname,
sum(datediff(dd,T0.Pickdate, T5.DocDate))/cOUNT(T5.dOCNUM) as 'days at legacy'

FROM
OPKL T0
INNER JOIN PKL1 T1 ON T0.AbsEntry = T1.AbsEntry
INNER JOIN RDR1 T2 ON T2.LineNum = T1.OrderLine AND T1.OrderEntry = T2.DocEntry AND T1.BaseObject = 17
INNER JOIN ORDR T3 ON T3.DocEntry = T2.DocEntry
LEFT JOIN DLN1 T4 ON T4.BaseEntry = T2.DocEntry AND T4.BaseLine = T2.LineNum AND T4.BaseType = T3.ObjType
LEFT JOIN ODLN T5 ON T5.DocEntry = T4.DocEntry
LEFT JOIN INV1 T6 ON T6.BaseEntry = T4.DocEntry AND T6.BaseLine = T4.LineNum AND T6.BaseType = T5.ObjType
LEFT JOIN OINV T7 ON T7.DocEntry = T6.DocEntry

WHERE

t7.[DocDate] >=[%0] and 
T7.[DocDate] <=[%1] and T7.[CANCELED] = 'N' 
gROUP BY

T7.Cardcode,
T7.Cardname