GP 2010 Item last sold: Including BOM components

Starting with the SQL view for last sale by customer and item by Victoria Yudin with a couple modifications.  I created some queries to recursively capture all of the components associated with the items sold as well and where they are found.

This is the modified view based on Victoria’s Item Last sold Per Customer

SELECT MAX(H.DOCDATE) AS Last_Sale,
 D.ITEMNMBR,
I.ITEMTYPE,
FROM
--this is simple view combining work and history
dbo.SOP10100_SOP30200 AS H INNER JOIN
dbo.SOP10200_SOP30300 AS D ON H.SOPTYPE = D.SOPTYPE
AND H.SOPNUMBE = D.SOPNUMBE
LEFT OUTER JOIN dbo.IV00101 AS I ON D.ITEMNMBR = I.ITEMNMBR
--looking for orders change SOPTYPE to 3 for Invoices
WHERE     (H.SOPTYPE = 2) AND (H.VOIDSTTS = 0)
GROUP BY D.ITEMNMBR, I.ITEMNMBR, I.ITEMTYPE

The above query will pull out all top level items that have been sold and the last sale date.  I made this a view to pull against.

Now the key to understanding exactly what inventory you have sold and when you sold it, requires you dive into the BOMs of an item.  Now often items are used on multiple BOMs.  It can also be helpful to know what it is found in for those components that no one remembers are even in the system.

This is the query I came up with to accomplish that.  It is surprisingly efficient too or at least I think it is for what it is doing.

declare @Months int
set @Months = 12
--create temp table to store the items
Create table #TEMPINVALL (
Lastsale datetime,
ITEM varchar(50),
FOUNDIN varchar(50),
ITEMTYPE varchar(10),
COST varchar(50),
COMPLEVEL int
)
insert into #TEMPINVALL
--top level
SELECT        Last_Sale, dj.ITEMNMBR,'' as 'FOUNDIN', dj.ITEMTYPE, STNDCOST, 0
FROM            _DJ_LASTSALEDATEPERITEM dj
inner join IV00101 iv on iv.ITEMNMBR = dj.ITEMNMBR
--pass number of months back or remove for all items ever
WHERE       (Last_Sale > DATEADD(m, - @Months, GETDATE()))
--create temp table to store component information
create table #tablecomps (
ITEM varchar(50),
FOUNDIN varchar(50),
BOMEBLEVEL int,
Lastsale datetime)
--create fetch and pass items into the BOM tables
--to recursively pull out additonal components
declare @ITEM varchar(50)
declare @lastsale datetime
declare @FOUNDIN varchar(50)
declare itemcomp Cursor For
select ITEM, Lastsale from #TEMPINVALL
Open itemcomp
fetch Next from itemcomp
into @ITEM,  @lastsale;
while @@Fetch_status = 0
begin
With BOM_No (CPN_I, PPN_I,   BOM_LEVEL) AS
(SELECT DISTINCT CPN_I, PPN_I, 0 FROM BM010115 WHERE PPN_I = @ITEM
      UNION ALL
      SELECT BM010115.CPN_I, BM010115.PPN_I, BOM_LEVEL + 1 FROM BOM_No
INNER JOIN BM010115
      ON BM010115.PPN_I = BOM_No.CPN_I)
insert into #tablecomps
SELECT distinct CPN_I, PPN_I, BOM_LEVEL, @lastsale FROM BOM_No
OPTION (MaxRecursion 100)
fetch Next from itemcomp
into @ITEM,  @lastsale;
end close itemcomp
deallocate itemcomp
--combine the two temp tables with a union
select distinct MAX(Lastsale) as Lastsale, ITEM, '' as 'FOUNDIN',
case
when ITEMTYPE = 1 then 'Sales Inventory'
when ITEMTYPE =  2 then 'Discontinued'
when ITEMTYPE = 3 then 'Kit'
when ITEMTYPE = 4 then 'Misc Charges'
when ITEMTYPE = 5 then 'Services'
when ITEMTYPE = 6 then 'Flat Fee'
End as 'ITEMTYPE'
, COST, COMPLEVEL,  QTYONHND from #TEMPINVALL
inner join IV00102 q on ITEM = q.ITEMNMBR
and RCRDTYPE = 1
group by ITEM,FOUNDIN, ITEMTYPE, COMPLEVEL, QTYONHND, COST
union
select distinct MAX(Lastsale) as Lastsale, ITEM, FOUNDIN,
case
when ITEMTYPE = 1 then 'Sales Inventory'
when ITEMTYPE =  2 then 'Discontinued'
when ITEMTYPE = 3 then 'Kit'
when ITEMTYPE = 4 then 'Misc Charges'
when ITEMTYPE = 5 then 'Services'
when ITEMTYPE = 6 then 'Flat Fee'
End as 'ITEMTYPE'
,STNDCOST, BOMEBLEVEL + 1, QTYONHND  from #tablecomps
inner join IV00101 i on ITEM = i.ITEMNMBR
inner join IV00102 q on ITEM = q.ITEMNMBR
and RCRDTYPE = 1
group by ITEM,FOUNDIN, ITEMTYPE, BOMEBLEVEL, QTYONHND, STNDCOST
--finally drop temp tables
drop table #tablecomps
drop  table #TEMPINVALL

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

About dorothyjarry

Super Dots
This entry was posted in GP, GP 2010, GP SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s