Dynamics GP Sales Order Processing SQL Views
- All Line Items All line items from both posted and unposted SOP 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 Item by Month Total sales (invoices minus returns) by item by month for a selected year.
- Sales by Item by Year Total sales (invoices minus returns) by item by 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
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