SELECT 	DISTINCT 
	r.ClientID, c.ClientName, 
	r.ItemID, r.StockID, r.StockName, 
	r.ExpectedQty, r.QCQty, r.AVAQty, r.PNDQty as pnd, 	
	r.VMIQCQty, r.VMIAVAQty, r.VMIPNDQty as vmipnd,
	a.VMIPNDQty,
	b.PNDQty,
	e.StockAtCustAVAQty,
	e.StockAtCustPNDQty,
	e.StockAtCustQCQty
FROM	tblItemCrossRef r
INNER JOIN tblClient c ON c.ClientID = r.ClientID
INNER JOIN tblItemClients i on i.Supplier = r.ClientID
INNER JOIN (
		Select 	d.itemid, isnull( SUM(d.HoldQty) ,0) AS VMIPNDQty
		FROM 	tblItemdetail d 
		WHERE 	d.ConsignorID 	= @ClientID 
		AND 	d.Ownerstatus	='VMI'
		GROUP BY d.itemid
	   ) a	on	a.itemid	= r.itemid
INNER JOIN (
		Select 	d.itemid, d.Ownership, isnull( SUM(d.HoldQty) ,0) AS PNDQty
		FROM 	tblItemdetail d 
		WHERE 	d.ConsignorID	= @ClientID 
		AND 	d.Ownerstatus   = 'VMI'
		GROUP BY d.itemid
           ) b	on	b.itemid	= r.itemid 	and 	b.Ownership	= i.Supplier
INNER JOIN (
		Select 	d.Ownership, 
			isnull(SUM(d.OriginQty - d.PickQty -d.HoldQty -d.qcqty), 0) AS StockAtCustAVAQty,
			isnull(SUM(d.HoldQty),0) as StockAtCustPNDQty,
			isnull(SUM(d.qcqty),0) 	as StockAtCustQCQty
		FROM 	tblItemDetail d 
		WHERE 	d.ConsignorID	= @ClientID 
		AND 	d.Ownerstatus	= 'OWN'
	   ) e	on	e.Ownership	= i.Supplier 
WHERE	r.ClientID 	= @ClientID 
AND 	r.StockID 	LIKE @StockID + '%'