In database SBO-COMMON select * from SINF for know the SAP version

In database SBO-COMMON


select * from SINF

 for know the SAP version

Customer Master Data with all feilds

Q:-Customer Master Data with all feilds 

Ans:-select DISTINCT t0.CardCode as CustomerCode,t0.CardName as CustomerName,t1.GroupName,T2.SlpName as SalesEmployee,
 t0.CntctPrsn ContactPersion,
 T3.Tel1 as Telephone1,T3.Tel2 as Telephone2,T3.E_MailL,T4.Address as BillToAddress,T4.Street as BillToStreet, T4.Block as BillToBlock,
  T4.City as BillToCity,T4.State as BillToSTATE,
 T4.ZipCode as BillToZipCode, T4.Country AS BillTOCountry,T5.Address as ShipToAddress,T5.Street as ShipToStreet, T5.Block as ShipToBlock,
  T5.City as ShipToCity,T5.State as ShipToSTATE,
 T5.ZipCode as ShipToZipCode, T5.Country AS ShipTOCountry
from ocrd t0
inner join ocrg t1 on t1.GroupCode=t0.GroupCode
INNER JOIN OSLP T2 ON T2.SlpCode = T0.SlpCode
INNER JOIN OCPR T3 ON T3.CardCode = t0.CardCode
INNER JOIN CRD1 T4 ON T4.CardCode =T0.CardCode and T4.AdresType='B'
INNER JOIN CRD1 T5 ON T5.CardCode =T0.CardCode and T5.AdresType='S'

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