Vendor on time delivery query

[Former Member]

Hello Experts,

     I'm looking for a query that will report the average days late for all of our vendors within a particular date range.  Would any of you have a query that will report these averages by vendor or know of a report already in SBO 9.0 that will perform this evaluation? 

[kothandaraman_nagarajan · Accepted solution]

Hi,

There is no standard report for late delivery. We have to create query to get average delays.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Thanks Nagarajan,

I'm working with the query below but don't understand why it reports many more lines than i have line items in the purchase order.  Would you be able to explain what is going on?

Also, is there a way to write a formula into the query that will average the days late for the vendors?

SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardName], T0.[NumAtCard], T3.[DocDate], DateDiff (dd, T0.DocDueDate,T3.DocDate) 'Delay' FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN PDN1 T2 ON T0.DocEntry=T2.BaseEntry INNER JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry WHERE T0.DocDate>= [%0]  and T0.DocDate<=[%1]

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query without Avg delay.

SELECT T0.[DocNum], T0.[DocDate] , T0.[DocDueDate] , T0.[CardName] , T0.[NumAtCard] , T3.[DocDate] , cast(DateDiff (dd, T0.DocDueDate,T3.DocDate) as Numeric) as Delay

FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN PDN1 T2 ON T0.DocEntry=T2.BaseEntry and T2.[BaseLine] = T1.[LineNum] INNER JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry WHERE T0.DocDate>= [%0]  and T0.DocDate<=[%1]

group by T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardName], T0.[NumAtCard], T3.[DocDate]

[Former Member · Accepted solution]

     So buy grouping the data this way... is it reporting only the times a Goods Receipt PO has been added against the Production Order?

[kothandaraman_nagarajan · Accepted solution]

To avoid more lines, added T2.[BaseLine] = T1.[LineNum] in your query and CAST function is added to get numeric value for delay.

It is not related to production order.

[Former Member · Accepted solution]

I apologizes I meant Purchase Order.

This way still doesn't make sense to me.  The lines in the query report vary in number compared to the number of line items in the Purchase Order?

[kothandaraman_nagarajan · Accepted solution]

Try:

SELECT T0.[DocNum], T0.[DocDate] , T0.[DocDueDate] , T0.[CardName] , T0.[NumAtCard] , T3.[DocDate] , cast(DateDiff (dd, T0.DocDueDate,T3.DocDate) as Numeric) as Delay

FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN PDN1 T2 ON T0.DocEntry=T2.BaseEntry and T2.[BaseLine] = T1.[LineNum] left JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry WHERE T0.DocDate>= [%0]  and T0.DocDate<=[%1]

group by T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardName], T0.[NumAtCard], T3.[DocDate]

[Former Member · Accepted solution]

That doesn't seem to give me what i need either.  Maybe i need to start over.

[kothandaraman_nagarajan · Accepted solution]

Please explain your requirement with an example to create query.

[Former Member · Accepted solution]

Hi,

Try:

SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardName], T0.[NumAtCard], IsNull(T3.[DocDate],'') 'GRPO Date', DateDiff (dd, T0.DocDueDate,T3.DocDate) 'Delay'

FROM OPOR T0

INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN PDN1 T2 ON T0.DocEntry=T2.BaseEntry AND T1.LineNum=T2.BaseLine

LEFT JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry

WHERE T0.DocDate>= [%0]  and T0.DocDate<=[%1]

What do you get? Show a screenshot.

Thanks,

Gordon

[Former Member · Accepted solution]

This is looking correct to me Gordon. Thank you very much!

Is there a simple way to explain what you did to prevent the additional lines in the previous query's and where we were going wrong with them?

Also, can a formula be written into the query to average out the days late by vendor?

[Former Member · Accepted solution]

Nagarajan,

     This was my original vision for the information I need.

[Former Member · Accepted solution]

Gordon, I see another issue.  At times when we place a PO with a vendor they give different delivery dates for the various items.  In those cases we put the item specific delivery dates on the line item.  It appears the query you have suggested is looking at the delivery date for the PO put i also need it to look at the line item delivery date being that those dates may vary.  Can this be done?

[Former Member · Accepted solution]

It can be done. Change T3.DocDate to T1.ShipDate to try.

[Former Member · Accepted solution]

That brings up another problem.  Can i put an if/then statement of some sort in there that would use the T1.ShipDate if there is one there or the T0.DocDueDate if not?

DateDiff (dd, T1.ShipDate,T3.DocDate) 'Delay'

[Former Member · Accepted solution]

You can add case clause if that is what you want.

[Former Member · Accepted solution]

Can you help me out with that Gordon?  I'm not familiar with how to set that up. 

[Former Member · Accepted solution]

Adding case to grouping query would be extremely difficult. Without actual data, there is no way to help you.

[Former Member · Accepted solution]

Thank you Gordon,

     Is there a good reference on these case clauses that you could recommend?

[Former Member · Accepted solution]

My book has some info for that. Have you got chance to read?

[Former Member · Accepted solution]

That sounds like a great resource Gordon.  Whats the title and cost?  Is it geared toward SBO? 

[Former Member]

Ok Gentlemen,

     I have received the Mastering SQL Queries for SBO book and have read through it.  It is an excellent resource and I am most positive that I will be referring to it quite often but I still need a little guidance.

     The query below is what I have ended up with thus far.  I plan to export the query into excel and use the subtotal function to calculate the average days late for each vendor.  The dilemma I have now is that the current query returns the 'Delay' as both early deliveries (calculation returns negative number), on time (result of ''), as well as late ones (calculated numbers are positive). 

     All I need are late deliveries.  The 'Delay' that is > 0.  What would be the best way to "exclude" the 'Delay' that is <= 0 and only return the positive calculations?  Or if you look at it the other way... only return the values of 'Delay' that or > 0. 

SELECT T0.[DocNum] 'PO #', T0.[DocDate] 'PO Posting Date', T1.[ShipDate] 'PO Line Item Due Date', T0.[DocDueDate] 'PO Due Date', T0.[CardName], T0.[NumAtCard], IsNull(T3.[DocDate],'') 'GRPO Date', DateDiff (dd, ISNULL(T1.ShipDate,T0.DocDueDate),T3.DocDate) 'Delay'

FROM OPOR T0

INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN PDN1 T2 ON T0.DocEntry=T2.BaseEntry AND T1.LineNum=T2.BaseLine

LEFT JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry

WHERE T0.DocDate>= [%0]  and T0.DocDate<=[%1]

[kothandaraman_nagarajan]

Hi,

Great. You have correct resource to learn queries in SAP B1. My suggestions, read and try to write simple queries to understand SAP B1 tables and its links.

Try this query:

SELECT T0.[DocNum] 'PO #', T0.[DocDate] 'PO Posting Date', T1.[ShipDate] 'PO Line Item Due Date', T0.[DocDueDate] 'PO Due Date', T0.[CardName], T0.[NumAtCard], IsNull(T3.[DocDate],'') 'GRPO Date', DateDiff (dd, ISNULL(T1.ShipDate,T0.DocDueDate),T3.DocDate) 'Delay'

FROM OPOR T0

INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN PDN1 T2 ON T0.DocEntry=T2.BaseEntry AND T1.LineNum=T2.BaseLine

LEFT JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry

WHERE T0.DocDate>= [%0]  and T0.DocDate<=[%1]

group by T0.[DocNum], T0.[DocDate],  T1.[ShipDate] , T0.[DocDueDate] , T0.[CardName], T0.[NumAtCard], T3.[DocDate], T1.ShipDate,T0.DocDueDate,T3.DocDate

Having DateDiff (dd, ISNULL(T1.ShipDate,T0.DocDueDate),T3.DocDate) >0

All the best.

Thanks,

Nagarajan

[Former Member]

Thank you Nagarajan,

     Honestly I totally forgot about the Having clause but it appears to raise an issue because of the Group By clause.  I see that my averages are a little off due to the fact that some items on the same PO are received on the same day and the Group By is combining them. 

     I added another "and" to the Where clause just to see if it would take it and it appears to be working.  Do you see any issue with setting up the Where clause this way?

WHERE T0.DocDate>= [%0]  and T0.DocDate<=[%1] and DateDiff (dd, ISNULL(T1.ShipDate,T0.DocDueDate),T3.DocDate) >0

[kothandaraman_nagarajan]

I think no problem in where clause. Try this query:

SELECT T0.[DocNum] 'PO #', T0.[DocDate] 'PO Posting Date', T1.[ShipDate] 'PO Line Item Due Date', T0.[DocDueDate] 'PO Due Date', T0.[CardName], T0.[NumAtCard], IsNull(T3.[DocDate],'') 'GRPO Date', DateDiff (dd, ISNULL(T1.ShipDate,T0.DocDueDate),T3.DocDate) 'Delay'

FROM OPOR T0

INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN PDN1 T2 ON T0.DocEntry=T2.BaseEntry AND T1.LineNum=T2.BaseLine

LEFT JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry

WHERE T0.DocDate>= [%0]  and T0.DocDate<=[%1]  and DateDiff (dd, ISNULL(T1.ShipDate,T0.DocDueDate),T3.DocDate) >0

group by T0.[DocNum], T0.[DocDate],  T1.[ShipDate] , T0.[DocDueDate] , T0.[CardName], T0.[NumAtCard], T3.[DocDate], T1.ShipDate,T0.DocDueDate,T3.DocDate

Having DateDiff (dd, ISNULL(T1.ShipDate,T0.DocDueDate),T3.DocDate) >0

[Former Member]

     I'm trying to avoid the Group By clause.  I believe it is grouping some of the data and I don't want it to.

     This is what I have now and it seems to be working.

SELECT T0.[DocNum] 'PO #', T0.[DocDate] 'PO Posting Date', T1.[ShipDate] 'PO Line Item Due Date', T0.[DocDueDate] 'PO Due Date', T0.[CardName], T0.[NumAtCard], IsNull(T3.[DocDate],'') 'GRPO Date', DateDiff (dd, ISNULL(T1.ShipDate,T0.DocDueDate),T3.DocDate) 'Delay'

FROM OPOR T0

INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN PDN1 T2 ON T0.DocEntry=T2.BaseEntry AND T1.LineNum=T2.BaseLine

LEFT JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry

WHERE T0.DocDate>= [%0]  and T0.DocDate<=[%1] and DateDiff (dd, ISNULL(T1.ShipDate,T0.DocDueDate),T3.DocDate) >0

Order By T0.[CardName] ASC

[kothandaraman_nagarajan]

Thanks for feeback. Finally you wrote required query.

Before close this thread, i would like to clarify that, why you have added field T0.DocDueDate is "Delay" calculation.

[Former Member]

     I'm not sure I understand the question Nagarajan.  If you are referring to the DateDiff calculation:

DateDiff (dd, ISNULL(T1.ShipDate,T0.DocDueDate),T3.DocDate) 'Delay'

     In some cases our purchasing department will receive different delivery dates for various items on the same PO.  When that occurs... they put the differing delivery dates on the related line items.  So in my DateDiff calculation I needed it to look at the line item Due Date from POR1 and if it is empty use the Doc Due Date from the PO (OPOR) to calculate the difference in days from when it was received with a goods receipt (OPDN).

Not sure if that is what you where asking?

[kothandaraman_nagarajan]

Yes your understanding is correct. But if there is no due date, system automatically updated with Doc due date.

Please correct me, if I am wrong.

[Former Member]

     I'm getting a bit confused. 

"But if there is no due date, system automatically updated with Doc due date."

     1) If there is no due date where?

     2) The system will automatically update what with which doc due date? 

[kothandaraman_nagarajan]

1.If there is no due date where?

POR1.shipdate

2) The system will automatically update what with which doc due date?

OPOR.docduedate

Hope this clears your doubt.

[Former Member]

     So your saying i don't need the IsNull statement?

DateDiff (dd,T1.ShipDate,T3.DocDate) 'Delay'

Will return the same result as:

DateDiff (dd, ISNULL(T1.ShipDate,T0.DocDueDate),T3.DocDate) 'Delay'

Because the system automatically looks at T0. DocDue Date if T1.ShipDate is empty?

I just want to make sure I understand why you where asking the question...

"Before close this thread, i would like to clarify that, why you have added field T0.DocDueDate is "Delay" calculation."

[kothandaraman_nagarajan]

Yes correct. The system automatically looks at T0. DocDue Date if T1.ShipDate is empty.

[Former Member]

     Ok, I wasn't aware of that.  I will take the IsNull statement out to simplify the query. 

     Now I need to figure out how to report the number of on time deliveries vs the number of deliveries that where late. 

[kothandaraman_nagarajan]

OK. Let us know if you face any issue.

[Former Member]

I just removed the IsNull and it appears that some of the data I need was not returned.

I believe I will need to leave it in there?

[kothandaraman_nagarajan]

If you use ISNULL, then above query will fetch all PO (item and service type). Without ISNULL, then query will fetch only Item type PO. That's why there is diffference in query result.

[kothandaraman_nagarajan]

Any update? Did you find the book?

[Former Member]

I did!  Ordered it last week and i believe it will be here tomorrow. 

[kothandaraman_nagarajan]

Hope you have started to learning SQL queries. All the best.

[kothandaraman_nagarajan]

Check these links:

Mastering SQL Queries for SAP Business One: Gordon Du: 9781849682367: Amazon.com: Books

https://www.packtpub.com/big-data-and-business-intelligence/mastering-sql-queries-sap-business-one

[Former Member]

thanks