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

2 comments:

  1. Hi Harry. I have written similar view to one of my customers. However, that view returned duplicate entries if same product is purchased from more than one vendor.

    Either the logic of purchasing same product from more than one vendor is wrong OR we may have to modify this view to check who's the primary vendor for this product and take that vendor into consideration.

    Not sure if you have faced similar challenges before.

    ReplyDelete
  2. Hey Vaidhyanathan!

    You're point is correct. That being said, the purpose of this report typically would be to provide a particular vendor with data on items they supply, so when the SmartList or SSRS report is run, the user should restrict the output to the particular vendor, which would resolve the problem.

    As an aside, since there are multiple vendors of for some items, it is important to let the client know, the report contains data, which isn't vendor specific.

    ReplyDelete