660
I am being tasked to lookup specific items in GP and am not able to locate via SQL or a smartlist.
I am looking for finished assemblies that are made up of seperate parts. I am needing to see the total sold over a three month period. Naturally I began looking through BOM and SOP.
Could somebody illustrate the most efficient manner in which I can do this? I have a total of 22 items to lookup.
Thank you kindly
*This post is locked for comments
I would look at the IV Trx history tables then. These will list all type of trx with the source doc. Does that help? Are they looking for sales vs mfg use percentage? The IV related trx would be the Mfg trx. Sales for sales of course.
Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com
Parts for assemblies are consistent with the BOM, however all these parts can be sold as spare parts. My query shows all totals per part number however I am not sure how to isolate the spares.
The purpose of this information is to forcast off the total number of parts invoiced over the past three months.
Do the parts for the assemblies vary by work order? Or are they consistent with the BOM? If they vary, I am not sure it can be accomplished. Are items lot numbered? I guess I am also trying to understand what benefit this information is. Isn't the usage of the components enough?
Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com
Here is an example of the request I was given before heading home, I am not sure how to even do this at this point. My sql is getting better the more I do it but this one has me scratching my head.
Here is an example of what I am looking for. The query Francisco helped me complete gives me all the information problem is how do I know which items out of the total per part is not a seperate piece part that was sold?
PN Description Finished Good Item
M98-027-0000 1. Bracket Assembly, H24-5 to T&E K24-026
M98-026-0000 1. Bracket, H24-5 to UPA, Sand CARC K24-026
M24-956-0000 1. Bracket, Adapter, ACH, 6-Hole, 10mm K24-026/K24-095/M24-700
M24-955-BLOX 1. Block, Guide, Right, H24 Mount K24-026
M24-954-BLOX 1. Block, Guide, Left, H24 Mount K24-026
M24-950-BLOX 1. Block, Travel Lock, H24-6 K24-026/K24-095/M38-210
M24-901-0000 1. Cradle Assy H24-5, Sand CARC K24-026/K24-095/M24-700
M24-515-BLOX 1. Train Lock Block, H24-3/-5/-6 (Rev A) K24-026
M24-296-SLOT 1. Pintle, H24, Slotted for use with Adapter, H24/UPA K24-026/K24-095/M38-210
You could tie to the BOM tables or to the picklist tables so it could be done but may be tricky with the picklist tables.. The picklist tables would be a many to many relationship unless there is a SOP number on the MO (auto created). The BOM table is BM010115 orr BM010116 (I believe the first one is the component or detail). I forgot what the picklist table is although I want to say it is MO1400 or MO1410. I can doublecheck if you need. Don't have access to a table list at the moment. The pickilst would indicate the actual qty's used, and the BOM tables would be a static. Which number to they want.
Thanks for the assistance Francisco!! I have associates that assist me with scripts when I need them.
Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com
Francisco,
This appears to give me the range I need. I am now being told that the range of parts I have looked up are top level assemblies and that they want to see all parts associated with each assembly that was sold. IS it even possible for me to do this now?
If you just want to add a filter on the date for a range then see below please, I am using TWO that's why the dates are for 2017
Declare @Startdate date
Declare @EndDate date
Set @Startdate = '01/01/2017'
Set @EndDate = '03/30/2017'
SELECT
m.CUSTNMBR, d.ITEMNMBR, m.docdate,
d.ITEMDESC, SUM(d.QUANTITY) AS 'Total_QTY'
FROM SOP30200 m
inner Join SOP30300 d on m.SOPNUMBE = d.SOPNUMBE
WHERE Year(m.docdate) = 2017 AND m.SOPTYPE = 3
and m.DOCDATE between @Startdate and @EndDate
GROUP BY
m.CUSTNMBR, d.ITEMNMBR, d.ITEMDESC, M.DOCDATE
order by 3 asc
I have the following but I am not able to define my date range for a three month period.
How does this look, valid?
SELECT
m.CUSTNMBR, d.ITEMNMBR,
d.ITEMDESC, SUM(d.QUANTITY) AS 'Total_QTY'
FROM SOP30200 m
inner Join SOP30300 d on m.SOPNUMBE = d.SOPNUMBE
WHERE Year(m.docdate) = 2011 AND m.SOPTYPE = 3
GROUP BY
m.CUSTNMBR, d.ITEMNMBR, d.ITEMDESC
That is the Sales Trx History or the header info. The SOP30300 is where you would want to grab the line details. You would need to join the 2 tables. I am not a strong script writer so I cannot help out there. The sales line items Smartlist should give you item, dates, etc, so it should get you what you need. You can then get it to excel to do any slicing and dicing. You just have to limit it by SOP type to invoice as the orders and quotes also reside in history.
Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com
I am able to select SOP30200 but I do not see any date range.
Do you ahve an exmaple of this type of query? IO am thinign I need to perform some sort of a join between this tablea nd anotehr, would you be able to assist me? Additionally I do not see this capability via smarlist, is that correct?
#1
André Arnaud de Cal...
291,391
Super User 2024 Season 2
#2
Martin Dráb
230,445
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
