SOP SQL Views


Dynamics GP Sales Order Processing SQL Views

40 Responses to “SOP SQL Views”

  1. Victoria
    Do you have a SQL view for pulling a list of SOP line items ordered, current Qty on Hand, and Qty on Order (PO)? We do not have SOP/POP linked. To complicate things I also would love to limit the view to those items with an Extender window (in Item Maint.) marked as a checkbox.
    Thanks!

    Like

    • Hi Sarah,

      I don’t have anything like this published yet, I will put that on the list of requests for future blog posts. However, anything with Extender information will need to be done custom for you specifically, as there is no way to code that generically. If you need help with creating a report, we can offer this as a consulting service, let me know if you would like to talk about that in more detail.

      -Victoria

      Like

  2. Hi Victoria,
    I am trying to write a query that will pull a summary of sales orders only by client by month. I like the Customer Period Summary for sales, however this returns Debit Memos as well. Is there a query that you have that will just pull SOP transactions by month by customer?
    Thanks Diane

    Like

  3. Hi Victoria,
    We would like to print out a daily report of items entered on orders. I am trying to get the report to include the customer item number. The smart list report gives us everything but the customer item number. The excel report I built returns duplicate records because I am unable to link tables by customer number AND item number. Any solutions out there? I frequent your blog because it provides useful information on GP tables. We are using GP 2010 version.

    Like

    • Hi Phil,

      I guess my question to you would be why you cannot link by both customer number and item number? Are you not including the SOP header table in your report? Here is how I would do it directly in SQL in case that helps:

      select h.SOPTYPE, h.SOPNUMBE, h.CUSTNMBR, d.ITEMNMBR, c.*
      from SOP10100 h -- SOP header
      inner join SOP10200 d -- SOP line items
        on h.SOPTYPE = d.SOPTYPE and h.SOPNUMBE = d.SOPNUMBE
      left outer join SOP60300 c -- customer items
        on h.CUSTNMBR= c.CUSTNMBR and d.ITEMNMBR = c.ITEMNMBR
      

      -Victoria

      Like

  4. Hi Victoria,
    I am trying to pull the demand to our items into a Crystal report but I have couple of challenges and I cannot figure out.
    I am basically taking all items from all sop types (2,3,5,6) from sop10100/sop10200 but I have couple of things that do not align and I am trying to figure out the difference among all the QTY fields:
    QUANTITY,ATYALLOC ,QTYFULFI,QTYORDER,QTYREMAI,QTYTOINV.
    It seems that each sop type stores the relevant QTY in a different field and I would like to know which one to pull when.
    Also,at what point in sop life cycle (fullfilment order/invoice) the Allocate quanity and Available quantity in inventory module is getting updated?

    Thanks A lot
    -Jeff

    Like

    • Hi Jeff,

      There is no one answer to what you’re asking, as it greatly depends on your specific GP setup and how exactly you are using SOP to enter your transactions. Which fields to pull from for each SOP type will also depend on exactly what you are trying to report on. I would recommend working with your GP partner who can take a look at your GP setup and your report requirements and help you determine the best fields to use for what you need.

      -Victoria

      Like

  5. Victoria,

    Thanks. Once again, you come through.

    Like

  6. Victoria, Queen of GP SQL Code:

    Okay, had another request from the sales staff. Building off of the Sales Qty by Year, how about customer items purchased by year? In other words, I’d like to be able to look at which items a customer has purchased over a period of, let’s say, 5 years. I’ve been working from my side and had not been able to code this.

    Like

  7. Hi Victoria…you site has been most helpful. Thanks. I’m a financial guy for a company that was acquired….parent company uses GP10 and I’ converting to that now. My solution provider is giving me advice that I must upgrade to GP2010 to use invoices because the SOP Blank Invoice is already being used by the parent company….and that I can’t simply copy the existing invoice, change the logo, reference it as an another invoice format and use it, I don’t get it……how can such a comprehensive program be so restrictive re invoice formats?
    Thanks
    Ben

    Like

    • Hi Ben,

      It is true that GP is pretty restrictive with what you can do with invoice formatting out-of-the-box. This is mostly a result of limitations of the Report Writer inside GP and while there is now Word Template functionality to somewhat help with this, past very basic stuff I do not believe Word Templates are an answer either.

      To answer your ‘how’ question…what is restrictive for one company, may be absolutely fine for another. Many of our customers either do not use invoicing in GP or have very basic needs that are satisfied out-of-the-box with no issues. One of the benefits of a system like Dynamics GP is that it offers you the opportunity to (a) customize as needed and/or (b) choose from many available add-ons for specific functionality that you might need that’s not in the box. Once you get past the plain vanilla invoice, you either have a lot of modification/customization needed, or you may decide to get an add-on that makes invoicing easier. My company has a GP add-on called GP Reports Viewer that may help mitigate some out-of-the-box limitations surrounding invoicing.

      -Victoria

      Like

  8. Victoria

    Help, I am trying to develop sales reports for management and I am getting two different data set depending on the joins I use. I am joining a view (a union of SOP10100 and SOP30200) with the RM00101 table (for the Sales territory) using left outer joins. When I join only on custnmbr I get one set of data and when I join on custnmbr and custname I get a much small set of data. Since as far as I know the and tested both the custnmbr and custname are distinct can you give me a clue as to why two links are not better than one.

    Like

    • Hi Patrick,

      There may be more, but below are at least 2 reasons I can think of to never link on the customer name:

      1. The customer name that defaults onto new SOP transactions is taken from RM00101. However, as soon as it is defaulted, the users can change it to anything they want. The changed customer name will be saved with the SOP transaction and will now be different from what is in RM00101.
      2. Periodically customers change their name. Let’s say you have 500 SOP transactions for a customer with the name of ABC Carpets. Today you receive a letter from them saying their new name is ABC Home Furnishings. You change this on the Customer Maintenance window (which updates RM00101), however this new name will only be defaulted onto future new transactions, your existing 500 SOP transactions will not be changed unless you proactively do something about that (and GP out of the box cannot do this, you would need to use the PSTL Customer Name modifier tool or do this directly in SQL, however most companies choose not to do either).

      So, as you’re seeing in your results, linking on customer name is returning only a subset of the data you are looking for. Here is a query that will give you a list of all customers in SOP30200 with more than one name for the same customer ID:
      select CUSTNMBR, COUNT(*) Unique_Names from
      (select distinct CUSTNMBR, CUSTNAME from SOP30200) a
      group by CUSTNMBR having COUNT(*) > 1

      Here is another that will show you the customer names in SOP30200 that do not match what’s in RM00101 for the same customer ID:
      select distinct
      s.CUSTNMBR, s.CUSTNAME Name_in_SOP,
      r.CUSTNAME Name_in_RM
      from SOP30200 s
      left outer join RM00101 r
      on s.CUSTNMBR = r.CUSTNMBR
      where s.CUSTNAME < > r.CUSTNAME
      order by s.CUSTNMBR

      Hope that helps.
      -Victoria

      Like

Trackbacks/Pingbacks

  1. SQL view to verify unposted SOP transaction totals in Dynamics GP | Victoria Yudin - September 12, 2014

    […] SOP SQL Views […]

    Like

  2. Identify what modules your Dynamics GP sales data originates in | Victoria Yudin - April 29, 2014

    […] SOP SQL Views […]

    Like

  3. SQL script for comparing voids between RM and SOP in Dynamics GP | Victoria Yudin - April 28, 2014

    […] SOP SQL Views […]

    Like

  4. SQL view for Dynamics GP sales quantities and amounts by item by site by month - Victoria Yudin - Microsoft Dynamics GP - Microsoft Dynamics Community - December 13, 2013

    […] Sales Order Processing (SOP) SQL views […]

    Like

  5. SQL view for Dynamics GP sales quantities and amounts by item by site by month | Victoria Yudin - October 15, 2013

    […] SOP SQL Views […]

    Like

  6. SQL view for sales quantities by item by site by month in Dynamics GP - Victoria Yudin - Microsoft Dynamics GP - Microsoft Dynamics Community - June 14, 2013

    […] Sales Order Processing (SOP) SQL views […]

    Like

  7. SQL view for sales quantities by item by site by month in Dynamics GP | Victoria Yudin - June 14, 2013

    […] SOP SQL Views […]

    Like

  8. SQL view for sales quantities by item by site by year | Victoria Yudin - February 28, 2013

    [...] SOP SQL Views [...]

    Like

  9. SQL view for rolling 12 months of sales by item in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - August 27, 2012

    [...] Sales Order Processing (SOP) SQL views [...]

    Like

  10. SQL view for rolling 12 months of sales by item in Dynamics GP – 8/27, Victoria Yudin | Partner Compete - August 27, 2012

    [...] Sales Order Processing (SOP) SQL views [...]

    Like

  11. SQL view for rolling 12 months of sales by item in Dynamics GP | Victoria Yudin - August 27, 2012

    [...] SOP SQL Views [...]

    Like

  12. SQL view for commissions details in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - July 19, 2012

    [...] SOP SQL views [...]

    Like

  13. SQL view for commissions details in Dynamics GP | Victoria Yudin - July 19, 2012

    [...] SOP SQL Views [...]

    Like

  14. Dynamics GP SOP line items with serial numbers and comments - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - June 4, 2012

    [...] more Dynamics GP SOP scripts please visit my SOP SQL Views page. Or take a look at my GP Reports page for additional reporting links. SOP table information can be [...]

    Like

  15. Dynamics GP SOP line items with serial numbers and comments | Victoria Yudin - June 4, 2012

    [...] SOP SQL Views [...]

    Like

  16. SQL view for sales quantities by customer by item by year - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - March 9, 2012

    [...] can find more code like this on my SOP SQL Views and Inventory SQL Views pages. For additional GP reporting information and links, check out my GP [...]

    Like

  17. SQL view for sales quantities by customer by item by year | Victoria Yudin - March 9, 2012

    [...] SOP SQL Views [...]

    Like

  18. Interesting Findings & Knowledge Sharing » SQL view for sales quantities by item by year - January 26, 2012

    [...] see additional Dynamics GP SOP code please take look at this page. For additional GP reporting information and links, check out my GP Reports [...]

    Like

  19. SQL view for sales quantities by item by year - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - January 23, 2012

    [...] see additional Dynamics GP SOP code please take look at this page. For additional GP reporting information and links, check out my GP Reports [...]

    Like

  20. SQL view for sales quantities by item by year | Victoria Yudin - January 23, 2012

    [...] see additional Dynamics GP SOP code please take look at this page. For additional GP reporting information and links, check out my GP Reports [...]

    Like

  21. SQL view for sales quantities by item by year | Victoria Yudin - January 23, 2012

    [...] SOP SQL Views [...]

    Like

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

Follow

Get every new post delivered to your Inbox.

Join 1,566 other followers

%d bloggers like this: