Vendor Performance report

Vendor Performance report



Declare  @FD  Date,@TD  Date

Set @FD=(Select MIN(T0.DocDate) From OINV T0 where T0.DocDate>=[%0])
Set @TD=(Select MAX(T1.DocDate) From OINV T1 where T1.DocDate<=[%1])
   
select    
P.CardName,   
(select COUNT(ItemCode) From PDN1,OPDN  where PDN1.DocEntry=P.DocEntry and OPDN.cardcode=P.CardCode and pdn1.DocEntry=opdn.DocEntry   
 and opdn.DocDate between @FD and @TD)noofitem,   
(select COUNT(ItemCode) From PDN1,OPDN where PDN1.DocEntry=P.DocEntry and PDN1.U_QUALITY !='yes' and OPDN.cardcode=P.CardCode and    
pdn1.DocEntry=opdn.DocEntry and opdn.DocDate between @FD and @TD)noofokitems,   
(Select COUNT(1) from OPRR A where a.CardCode=p.cardcode and a.DocDate between @FD and @TD)noofConplains,   
(select COUNT(A.ItemCode) From PDN1 A inner join OPDN s on a.DocEntry=s.DocEntry LEFT JOIN POR1 B ON A.ItemCode=B.ItemCode AND A.BaseEntry=B.DocEntry AND A.BaseLine=B.LineNum    
        WHERE A.DocDate<=B.ShipDate AND A.DocEntry=P.DocEntry and s.CardCode=p.CardCode and s.DocDate between @FD and @TD)ONTIME,   
 CONVERT(NUMERIC(19,3),0)QUALITYPER,   
 CONVERT(NUMERIC(19,3),0)QUALITYPER1,   
 CONVERT(NUMERIC(19,3),0)QUALITYPER2,   
 CONVERT(NUMERIC(19,3),0)QUALITYPER3   
    
        INTO #TEMP          
from OPDN P    
   
update #TEMP set QUALITYPER=noofokitems/nullif(noofitem,0)*100   
update #TEMP set QUALITYPER1=ONTIME/nullif(noofitem,0)*100   
update #TEMP set QUALITYPER2=((noofitem-noofConplains)/nullif(noofitem,0))*100   
update #TEMP set QUALITYPER3=isnull((QUALITYPER*30/100),0)+ isnull((QUALITYPER1*30/100),0)+isnull((QUALITYPER2*40/100),0)   
   
--select * from #TEMP where CardName='USHA POLY CRAFTS PVT LTD' order by CardName return   


DECLARE @FinalResult TABLE
(
  CardCode NVARCHAR(100) ,
  Lots_Received DECIMAL(18,2),
  Quality DECIMAL(18,2),
  Lots_OnTime DECIMAL(18,2),
  Customer_Complaints DECIMAL(18,2),
  Quality_Performance_W30 DECIMAL(18,2),
  Delivery_Rating_W30 DECIMAL(18,2),
  Quality_Performance_W40 DECIMAL(18,2),
  Supplier_Rating DECIMAL(18,2)
)   

INSERT INTO @FinalResult
( CardCode , Lots_Received ,Quality ,Lots_OnTime , Customer_Complaints
 ,  Quality_Performance_W30 , Delivery_Rating_W30, Quality_Performance_W40 , Supplier_Rating )

SELECT CardName,SUM(noofitem)[Total no of Lots Received],SUM(noofokitems)[Quality],SUM(ONTIME)[Total no. of Lots on Time],  
(noofConplains)[Total no. of product related Customer Complaints],SUM(QUALITYPER)[Quality Performance (Internal)(W 30)]   
--,SUM(ISNULL(ONTIME,0))/ISNULL(NULLIF(SUM(ISNULL(noofitem,0)),0),1) * 100 [Delivery Rating(W30)]
 , 0 [Delivery Rating(W30)]
-- ,SUM(ISNULL(noofitem-noofConplains,0))/ISNULL(NULLIF(SUM(ISNULL(noofitem,0)),0),1) * 100 [Quality Performance (Customer Complaint)(W40)],

 , 0 [Quality Performance (Customer Complaint)(W40)]   
 -- SUM(QUALITYPER3)[Supplier Rating]  
  ,0  [Supplier Rating]
 FROM #TEMP group by CardName,noofConplains order by CardName   

 UPDATE @FinalResult SET Quality_Performance_W30 =  ROUND((Quality / ISNULL(NULLIF(Lots_Received,0),1))* 100 ,0)
                        , Delivery_Rating_W30 = ROUND((Lots_OnTime / ISNULL(NULLIF(Lots_Received,0),1)) * 100 , 0)
                        , Quality_Performance_W40 = ROUND( ((Lots_Received - Customer_Complaints)/ISNULL(NULLIF(Lots_Received,0),1)) *100,0)


 UPDATE  @FinalResult SET Supplier_Rating = ROUND(Quality_Performance_W30 *0.3 + Delivery_Rating_W30 *0.3 + Quality_Performance_W40 *0.4,0)

  select CardCode , Lots_Received ,Quality ,Lots_OnTime , Customer_Complaints
  , Quality_Performance_W30, Delivery_Rating_W30, Quality_Performance_W40 , Supplier_Rating
  from @FinalResult

  DROP TABLE #TEMP