Supplier Perfomance Query

[Former Member]

Good Afternoon,

I have a query built to identify supplier performance between us and our sister company in Germany. We have a free text field (WDE Prop) were they supply us a date for delivery that normally varies to the delivery date we requested. We are now looking to split the results by product category A, B or C - is this possible?

Or are there any better solutions to measuring supplier perfomance.

Kind regards,

James

SELECT T1.[DocNum] as 'GRPO Number', T1.[DocDate], T1.[CardCode], T1.[CardName], T0.[ItemCode], T0.[Dscription],

[WUK Proposed Date] = T0.[ShipDate],

[WDE Proposed Date] = CONVERT(date, convert(varchar (10), T0.[FreeTxt])),

[Actual Delivery Date] = T0.[ActDelDate],

[Days Difference to WUK]= DATEDIFF(day,T0.ShipDate,  T0.[ActDelDate]) ,

[Days Difference to WDE]= DATEDIFF(day,CONVERT(date, convert(varchar (10), T0.[FreeTxt])), T0.[ActDelDate])

FROM PDN1 T0  INNER JOIN OPDN T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE DATEDIFF(day,CONVERT(date, convert(varchar (10), T0.[FreeTxt])), T0.[ActDelDate]) < 1000

and T1.[DocDate] >= Case '[%0]' when '' then '01.01.2001' else '[%0]' end and T1.[DocDate] <= Case '[%1]' when '' then '31.12.2099' else '[%1]' end

and T1.[CardCode] = '[%2]'

Order by T1.[CardCode] , T1.[DocNum]

[agustin_marcoscividanes]

Hi

try this sql query:

SELECT T0.[ItemCode], T0.[Dscription], T0.[DocEntry], T0.[LineNum] + 1, T0.[ObjType], T0.[WhsCode], T0.[Quantity], T1.DocDueDate, T5.DocDueDatee, DATEDIFF(DD, T5.DocDueDate, T1.DocDueDate),  T0.[DelivrdQty], T0.[OpenCreQty], T0.[unitMsr], T0.[BackOrdr], T0.[SlpCode], T0.[TrnsCode], T0.[ShipToCode], T0.[ShipToDesc], T0.[TreeType], T0.[UseBaseUn], T1.[DocNum], T1.[CardCode], T1.[CardName], T1.[NumAtCard], T1.[DocDate], T2.[Priority], T2.[GroupCode], T3.[OnHand], T0.[NumPerMsr], T0.[unitMsr2], T0.[NumPerMsr2] FROM  [dbo].[PDN1] T0  INNER  JOIN [dbo].[OPDN] T1  ON  T1.[DocEntry] = T0.[DocEntry]  inner join POR1 T4 ON T0.baseentry = T4.DocEntry and T0.BaseLine = T4.LineNum inner join OPOR T5 ON T4.DocEntry = T5.DocEntry INNER  JOIN [dbo].[OCRD] T2  ON  T2.[CardCode] = T1.[CardCode]  INNER  JOIN [dbo].[OITM] T3  ON  T3.[ItemCode] = T0.[ItemCode]  WHERE T1.[DocType] = 'I'  AND  T1.[DocStatus] = 'O'  AND  T0.[LineStatus] = 'O' AND  T0.[OpenCreQty] <> 0

Kind regards

Agustín Marcos Cividanes

[Former Member]

Hi Agustin,

Thank you for this - it is very useful!

Can you recommend a very simple way of measuring this based on each supplier. Currently our supplier proposes a delivery date (WDE Prop Date - That is a free text field) and we convert this into a date based on the GRN.

Could you recommend any other queries to measure supplier performance? Our main supplier code would be S1184.

Kind regards,

James

[agustin_marcoscividanes]

Hi

if the user records and updates the purchase order with the right dates, and add the good receipts with the right date, the query solves your question.

Kind regards

Agustín Marcos Cividanes