SOP SQL Views


Dynamics GP Sales Order Processing SQL Views

58 Responses to “SOP SQL Views”

  1. Hi Victoria
    I need help,Im new to GP i need to pull data from GP into another warehouse .My question is does my SOP tables give me all the invoices with orders posted ,returns , or do i need to connect other tables from RM etc to get all the invoices details of a particular customer .Can you please help me on this

    Like

    • Pria,

      Unfortunately, the answer to your question is “it depends”. Since there are many different ways of using Dynamics GP and entering sales transactions into it, it really depends on exactly how your company is using Dynamics GP and also what data needs to be pulled into the warehouse.

      -Victoria

      Like

      • Victoria

        Thanks for the quick reply.I have one more question I need all the customer master data Im trying to pull data from
        RM00101 RM Customer MSTR
        RM00102 Customer Master Address File
        would this be enough or do i need to look into another table .can you help me out with the tables that give me all my customer master data.

        -pria

        Like

        • Pria,

          Due to the complexity of some environments, it’s impossible to say whether all the data you need will be in those 2 tables without actually seeing a list of all the data you need. And there is no generic answer for all the tables you need, for example, you might have some Extender windows storing data that is specific to your customers that others would not have. I typically try to turn the question around – first list all the data you need, then determine what table(s) to use for each piece of data.

          -Victoria

          Like

  2. Hi Victoria,

    I need to be able to create a view that shows all open orders and all invoices by customer. If possible even by customer by item. What I am currently doing is exporting the work and history tables and manually combining them so I can report on it. The goal is to have this;

    customer salesPY orders by month, invoices by month, Total of orders and invoices YTD

    Is this even possible?

    Like

    • Hi Ralph,

      This is possible, but would require some more complicated coding than what I typically post on this blog. If you need help creating this report, this is something we can do as a consulting project for you.

      -Victoria

      Like

  3. Greetings and Salutations,

    I’ve been trying to run the following script in SQL to return the dollar amount and number of sales documents (sales invoices, debit memos only) in a given year. What am I missing as the transaction count returned gives me the customer transactions over the life of the account including payments, etc.

    SELECT TOP (100) PERCENT T.CUSTNMBR AS [Customer ID], C.CUSTNAME AS [Customer Name], C.CUSTCLAS AS [Class ID], C.SLPRSNID AS Salesperson,
    SUM(CASE WHEN YEAR(T .DOCDATE) = 2016 THEN T .Amount ELSE 0 END) AS [2016 Sales], COUNT(CASE WHEN YEAR(T .DOCDATE)
    = 2016 THEN T .Amount ELSE 0 END) AS TRANSACTIONS, C.STATE, C.USERDEF2 AS MEMBERSHIP
    FROM (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR, CASE WHEN RMDTYPAL IN (1, 3, 4) THEN (SLSAMNT + MISCAMNT – TRDISAMT) WHEN RMDTYPAL IN (7, 8) THEN – 1 * (SLSAMNT + MISCAMNT – TRDISAMT) ELSE 0 END AS Amount
    FROM dbo.RM20101
    WHERE (VOIDSTTS = 0)
    UNION ALL
    SELECT CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR, CASE WHEN RMDTYPAL IN (1, 3, 4) THEN (SLSAMNT + MISCAMNT – TRDISAMT) WHEN RMDTYPAL IN (7,
    8) THEN – 1 * (SLSAMNT + MISCAMNT – TRDISAMT) ELSE 0 END AS Amount
    FROM dbo.RM30101
    WHERE (VOIDSTTS = 0)) AS T LEFT OUTER JOIN
    dbo.RM00101 AS C ON T.CUSTNMBR = C.CUSTNMBR
    GROUP BY T.CUSTNMBR, C.CUSTNAME, C.CUSTCLAS, C.SLPRSNID, C.STATE, C.USERDEF2
    ORDER BY TRANSACTIONS DESC, [Customer Name], Salesperson, [Customer ID]

    Like

  4. Hi Victoria,
    Big FAN..
    I’ve been using your SOP script “view_SOP_Line_Items” and trying to link it to GL Accounts and ultimately to GL postings for each Invoice Line Item. A number of SOP transactions seem to have been inadvertently posted under incorrect GL Accounts.
    Thanks,
    Gus

    Like

  5. Victoria,
    I’m working on a report and based on your view All SOP Line Items. I’ve removed the fields not required and added a where clause for testing. I’ve made several attempts at joining the Inventory Item Master table (IV00102) in order to add the Quantity on Hand field but have had no success. The end result would be to prompt the user for a document date range and get quantities ordered and invoiced for the requested date range and include the current quantity on hand from the Item Master. Below is your view modified without the Item Master Quantity on Hand.

    SELECT OH.SOPNUMBE SOP_Number,
    OH.DOCDATE Document_Date,
    CASE OH.PSTGSTUS
    WHEN 0 THEN ‘Unposted’
    WHEN 2 THEN ‘Posted’
    ELSE ‘Error’
    END Posting_Status,
    CASE OH.SOPTYPE
    WHEN 1 THEN ‘Quote’
    WHEN 2 THEN ‘Order’
    WHEN 3 THEN ‘Invoice’
    WHEN 4 THEN ‘Invoice’
    WHEN 5 THEN ‘BackOrder’
    WHEN 6 THEN ‘FUllfillment’
    ELSE ‘Error’
    END SOP_Type,
    OH.CUSTNMBR Customer_ID, OH.CUSTNAME Customer_Name,
    OH.CSTPONBR Customer_PO, OH.BACHNUMB Batch_Number,
    OL.LOCNCODE Line_Site_ID,
    OL.ITEMNMBR Item_Number, OL.ITEMDESC Item_Description,
    OL.QUANTITY Quantity_Used,
    OL.QTYREMAI Qty_Remaining

    FROM    SOP10100 OH
    INNER JOIN SOP10200 OL
    ON OH.SOPTYPE = OL.SOPTYPE
    AND OH.SOPNUMBE = OL.SOPNUMBE
    

    WHERE OH.DOCDATE = ‘2017-04-12 00:00:00.000’
    AND OL.ITEMNMBR = ‘128 SDRAM’
    AND OH.VOIDSTTS = ‘0’

    UNION ALL

    SELECT HH.SOPNUMBE SOP_Number,
    HH.DOCDATE Document_Date,
    CASE HH.PSTGSTUS
    WHEN 0 THEN ‘Unposted’
    WHEN 2 THEN ‘Posted’
    ELSE ‘Error’
    END Posting_Status,
    CASE HH.SOPTYPE
    WHEN 1 THEN ‘Quote’
    WHEN 2 THEN ‘Order’
    WHEN 3 THEN ‘Invoice’
    WHEN 4 THEN ‘Invoice’
    WHEN 5 THEN ‘BackOrder’
    WHEN 6 THEN ‘FUllfillment’
    ELSE ‘Error’
    END SOP_Type,
    HH.CUSTNMBR Customer_ID, HH.CUSTNAME Customer_Name,
    HH.CSTPONBR Customer_PO, HH.BACHNUMB Batch_Number,
    HL.LOCNCODE Line_Site_ID,
    HL.ITEMNMBR Item_Number, HL.ITEMDESC Item_Description,
    HL.QUANTITY Quantity_Used,
    HL.QTYREMAI Qty_Remaining
    FROM SOP30200 HH
    INNER JOIN SOP30300 HL
    ON HH.SOPTYPE = HL.SOPTYPE
    AND HH.SOPNUMBE = HL.SOPNUMBE

    WHERE HH.DOCDATE = ‘2017-04-12 00:00:00.000’
    AND HL.ITEMNMBR = ‘128 sdram’
    AND HH.VOIDSTTS = ‘0’

    Thanks in advance!

    Debi

    Like

    • Hi Debi,

      Try this:

      select s.*,
      q.QTYONHND Qty_on_Hand
      from
      (SELECT OH.SOPNUMBE SOP_Number,
      OH.DOCDATE Document_Date,
      CASE OH.PSTGSTUS
      WHEN 0 THEN 'Unposted'
      WHEN 2 THEN 'Posted'
      ELSE 'Error'
      END Posting_Status,
      CASE OH.SOPTYPE
      WHEN 1 THEN 'Quote'
      WHEN 2 THEN 'Order'
      WHEN 3 THEN 'Invoice'
      WHEN 4 THEN 'Invoice'
      WHEN 5 THEN 'BackOrder'
      WHEN 6 THEN 'Fullfillment'
      ELSE 'Error'
      END SOP_Type,
      OH.CUSTNMBR Customer_ID, OH.CUSTNAME Customer_Name,
      OH.CSTPONBR Customer_PO, OH.BACHNUMB Batch_Number,
      OL.LOCNCODE Line_Site_ID,
      OL.ITEMNMBR Item_Number, OL.ITEMDESC Item_Description,
      OL.QUANTITY Quantity_Used,
      OL.QTYREMAI Qty_Remaining
      FROM SOP10100 OH
      INNER JOIN SOP10200 OL
      ON OH.SOPTYPE = OL.SOPTYPE
      AND OH.SOPNUMBE = OL.SOPNUMBE
      WHERE OH.DOCDATE = '2017-04-12 00:00:00.000'
      AND OL.ITEMNMBR = '128 SDRAM'
      AND OH.VOIDSTTS = '0'
      UNION ALL
      SELECT HH.SOPNUMBE SOP_Number,
      HH.DOCDATE Document_Date,
      CASE HH.PSTGSTUS
      WHEN 0 THEN 'Unposted'
      WHEN 2 THEN 'Posted'
      ELSE 'Error'
      END Posting_Status,
      CASE HH.SOPTYPE
      WHEN 1 THEN 'Quote'
      WHEN 2 THEN 'Order'
      WHEN 3 THEN 'Invoice'
      WHEN 4 THEN 'Invoice'
      WHEN 5 THEN 'BackOrder'
      WHEN 6 THEN 'Fullfillment'
      ELSE 'Error'
      END SOP_Type,
      HH.CUSTNMBR Customer_ID, HH.CUSTNAME Customer_Name,
      HH.CSTPONBR Customer_PO, HH.BACHNUMB Batch_Number,
      HL.LOCNCODE Line_Site_ID,
      HL.ITEMNMBR Item_Number, HL.ITEMDESC Item_Description,
      HL.QUANTITY Quantity_Used,
      HL.QTYREMAI Qty_Remaining
      FROM SOP30200 HH
      INNER JOIN SOP30300 HL
      ON HH.SOPTYPE = HL.SOPTYPE
      AND HH.SOPNUMBE = HL.SOPNUMBE
      WHERE HH.DOCDATE = '2017-04-12 00:00:00.000'
      AND HL.ITEMNMBR = '128 sdram'
      AND HH.VOIDSTTS = '0') s

      left outer join IV00102 q
      on s.Item_Number = q.ITEMNMBR
      and s.Line_Site_ID = q.LOCNCODE

      -Victoria

      Like

  6. what table holds the information for ‘Batched’ Invoices\Returns.
    I am trying to develop a script \ trigger that will run when Item Cost is higher than Sell Price. Currently, we batch everything, but I can’t find the table that holds the information.

    Like

  7. 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

  8. 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

  9. 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

  10. 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

  11. Victoria,

    Thanks. Once again, you come through.

    Like

  12. 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

  13. 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

  14. 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 2,326 other followers

%d bloggers like this: