Sales Reporting for Vendor Items

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