Dynamics GP Sales Order Processing SQL Views
- All SOP Line Items All line items from both posted and unposted SOP transactions.
- All SOP Line Items with Serial Numbers and Comments All line items from both posted and unposted SOP transactions, includes serial/lot numbers as well as header and line item comments.
- Commissions Details SOP and RM commissions (for posted and unposted transactions).
- Items with SOP-POP link All line items with the SOP- POP link (posted and unposted).
- Last Sale by Customer and Item Shows the last sale date for each customer/item combination.
- Last Sale Date by Item Shows the last sale date of each item with the customer and invoice number for that sale.
- Sales by Customer by Month Total sales (invoices minus returns) by customer by month for a selected year.
- Sales by Item by Month Total sales (invoices minus returns) by item by month for a selected year.
- Sales by Item for Rolling 12 Months Shows sales (invoices minus returns) by item by month for the last 12 full months.
- Sales by Item by Site by Year Total sales (invoices minus returns) by item by site ID by year.
- Sales by Item by Year Total sales (invoices minus returns) by item by year.
- Sales Quantities by Customer by Item by Year Total sales quantities (invoices minus returns) by customer by item by year, with an overall total column.
- Sales Quantities by Item by Site by Month Total sales quantities (invoices minus returns) by item by site by month, with a hard-coded year.
- Sales Quantities by Item by Year Total sales quantities (invoices minus returns) by item by year, with an overall total column.
- SOP Email Setup in GP 2010 Shows the status and format of the SOP email setup for each customers as well as the email addresses for the default bill to address
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!
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
Many thanks – I would love that report even without the Extender field!
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
Diane,
There are 6 types of SOP transactions possible:
Which of those do you want to include? Also, are you looking for posted, unposted, or both?
-Victoria
Victoria,
I am looking for Invoice. On only posted accounts.
Thanks!
Diane
Diane,
I just posted a new view for this here. It also includes returns, but if you want to remove them, you can change line 58 to the following:
-Victoria
Thank you so much!
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.
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:
-Victoria
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
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
Victoria,
Thanks. Once again, you come through.
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.
Mark,
Thanks, that’s great – I might have to print up some business cards with that title.
Check out my new view to see if this helps with your report.
-Victoria
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
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
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.
Hi Patrick,
There may be more, but below are at least 2 reasons I can think of to never link on the customer name:
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 distincts.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