From time-to-time, key vendors request sales figures on items they supply. Below is a SQL Query, which creates a view that can be used as a foundation for a SmartList or SSRS Report to support this effort.
USE [COMPANYID]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE View [dbo].[_Vendor_Item_Sales]
as
Select
ivv.VNDITNUM
Vendor_Item,
ivv.VENDORID
Vendor_ID,
pmv.VENDNAME
Vendor_Name,
SHL.ITEMNMBR
Item_ID,
case shl.soptype
when 1 then 'Quote'
when 2 then 'Order'
when 3 then 'Invoice'
when 4 then 'Return'
when 5 then 'Back Order'
when 6 then 'Fulfillment Order'
else 'ERROR'
End Document_Type,
shl.SOPNUMBE
Document_Number,
(shl.LNITMSEQ/16384) Line_Number,
SHL.ITEMDESC
Item_Description,
shl.UOFM
Unit_of_Measure,
convert(varchar(10),shh.DOCDATE,101) Document_Date,
CONVERT(varchar(10),shh.INVODATE,101) Invoice_Date,
shl.CNTCPRSN
Ship_to_Contact,
shl.ShipToName
Ship_to_Name,
shl.ADDRESS1
Address_1,
shl.ADDRESS2
Address_2,
shl.ADDRESS3
Address_3,
shl.CITY Ship_City,
shl.STATE Ship_State,
shl.ZIPCODE
Ship_Zip,
shl.QUANTITY
Quantity,
shl.QTYTOINV
Invoice_Quantity,
cast(shl.UNITPRCE as money) Unit_Price,
cast(shl.XTNDPRCE as money) Extended_Price,
cast(shl.UNITCOST as money) Unit_Cost,
cast(shl.EXTDCOST as money) Extended_Cost
from SOP30200 SHH
left join SOP30300 SHL on SHH.SOPTYPE = SHL.SOPTYPE and SHH.SOPNUMBE = SHL.SOPNUMBE
left join IV00101 IVI on shl.ITEMNMBR = IVI.ITEMNMBR
left join IV00103 IVV on ivi.ITEMNMBR = ivv.ITEMNMBR
left join PM00200 PMV on ivv.VENDORID = pmv.VENDORID
GO
GRANT SELECT ON _Vendor_Item_Sales TO
DYNGRP
GO
