Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

Total items sold

Posted on by 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

  • Dan Liebl Profile Picture
    Dan Liebl 7,320 on at
    Re: Total items sold

    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

  • manelson05 Profile Picture
    manelson05 660 on at
    Re: Total items sold

    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.

  • Dan Liebl Profile Picture
    Dan Liebl 7,320 on at
    Re: Total items sold

    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

  • manelson05 Profile Picture
    manelson05 660 on at
    Re: Total items sold

    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

  • Dan Liebl Profile Picture
    Dan Liebl 7,320 on at
    Re: Total items sold

    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

  • manelson05 Profile Picture
    manelson05 660 on at
    Re: Total items sold

    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?

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Total items sold

    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

  • manelson05 Profile Picture
    manelson05 660 on at
    Re: Total items sold

    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

  • Dan Liebl Profile Picture
    Dan Liebl 7,320 on at
    Re: Total items sold

    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

  • manelson05 Profile Picture
    manelson05 660 on at
    Re: Total items sold

    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?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,240 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans