Saturday, October 31, 2015

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