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).
- Compare Voids in RM and SOP modules Shows a list of all transactions voided in one, but not both modules.
- 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 Amounts and Quantities by Item for Rolling 12 Months Shows sales amounts (invoices minus returns) and also quantities by item by month for the last 12 full months.
- Sales Amounts by Item for Rolling 12 Months Shows sales amounts (invoices minus returns) by item by month for the last 12 full months.
- Sales Amounts by Item by Site for Rolling 12 Months Shows sales amounts (invoices minus returns) by item by site 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.
- Verify Unposted SOP Transaction Totals Shows unposted SOP transactions where the sum of the extended prices does not equal the document subtotal.
Victoria Yudin 
Victoria
I am attempting to use this query to create an SSRS report that is calendar based. Can you help with this?
SELECT DISTINCT SOP10200.QUANTITY,
SOP10200.ITEMNMBR,
SOP10200.Address1,
SOP10100.CSTPONBR,
SOP10200.ReqShipDate,
IV00101.ITMSHNAM,
SOP10200.SOPNUMBE,
RM00102.USERDEF2,
SOP10200.CITY,
SOP10200.ShipToName,
SOP10200.QTYTBAOR,
SOP10100.LOCNCODE,
Datename (weekday,SOP10200.ReqShipDate) as ‘WeekDay’,
Datename (dd,SOP10200.ReqShipDate) as ‘WeekDate’,
Datename (mm,SOP10200.ReqShipDate) as ‘Month’,
Datename (yyyy,SOP10200.ReqShipDate) as ‘Year’,
cast(SOP10100.CREATDDT as Date) as CreatedDate
FROM RM00102 RM00102 INNER JOIN ((SOP10200 SOP10200 INNER JOIN IV00101 IV00101 ON SOP10200.ITEMNMBR=IV00101.ITEMNMBR) INNER JOIN SOP10100 SOP10100 ON SOP10200.SOPNUMBE=SOP10100.SOPNUMBE) ON (RM00102.CUSTNMBR=SOP10100.CUSTNMBR) AND (RM00102.ADRSCODE=SOP10100.PRSTADCD)
WHERE SOP10200.QUANTITY>=1
AND (SOP10200.SOPNUMBE LIKE ‘p%’ or SOP10200.SOPNUMBE LIKE ‘sp%’) AND SOP10200.QTYTBAOR<>0
AND SOP10200.ReqShipDate>=CURRENT_TIMESTAMP
— and SOP10100.LOCNCODE=@Location
group by
SOP10200.ReqShipDate,
SOP10100.LOCNCODE,
SOP10200.sopnumbe,
SOP10200.QUANTITY,
SOP10200.ITEMNMBR,
SOP10200.Address1,
SOP10100.CSTPONBR,
IV00101.ITMSHNAM,
RM00102.USERDEF2,
SOP10200.CITY,
SOP10200.ShipToName,
SOP10200.QTYTBAOR,
sop10100.CREATDDT
order by reqshipdate, sopnumbe,locncode
LikeLike
Hi Philip,
I am not sure what you mean by calendar based? Also not sure what you are asking? Are you getting an error? Is this a modification of a query I posted?
-Victoria
LikeLike
No not a modification just trying to have a ssrs report calendar based to show the item and qty on the date for reqshipdate.
LikeLike
No it is not a query you wrote. I am trying to get help creating a SSRS report that is a month by month calendar showing the Item Number and Qty on the ReqShipDate on the days of the calendar.
LikeLike
Ok, so what is the question/issue?
-Victoria
LikeLike
I am asking for help creating the ssrs report similar to this:
https://stackoverflow.com/questions/43772697/ssrs-report-creating-calendar-style-report-with-multiple-data-in-a-single-cale
but using my query instead.
LikeLike
Philip,
Have you tried creating the report and it’s not working? If so, is there something specific not working? Or are you looking for someone to create the report for you from scratch?
-Victoria
LikeLike
I have tried and it is not working for me. The days are all on one row for evey month and the months are all together.
LikeLike
Ok, so it sounds like you will need to get some help with SSRS – I don’t have experience with creating a calendar report like the one you’re looking for, so not sure I can easily help. Is your GP Partner possibly a resource that can help you with it?
-Victoria
LikeLike
We had a new employee enter and transfer 100 or so orders that should have been recurring but were not set up that way. Do you know of any way that I could unpost those orders all at once in the front end or through SQL so that we can set them up to be recurring without having to recreate each one?
LikeLike
Hi Scotty,
There is no way to “unpost” either in the GP application or in SQL. The only thing that might help at this point is the Copy feature – you can create a new order and copy from either the original order or the invoice. But this will have to be done one order at a time.
-Victoria
LikeLike
okay thanks. I have written a query to pull the info from the posted orders to create a file I can then import using integration manager. It might have taken less time to manually re-enter them but now I’ll be ready if it happens again!
LikeLike
Hi Victoria,
Have you ever written a view to compare the time and order is started to when the last line item was entered? My thought was to compare the dex row time stamp from the SOP Work table against the dex row times stamp in the amounts work table to determine how long it to the order to be entered start to finish.
I appreciate any advice. Here is my query so far.
select
SH.SOPNUMBE, SH.SOPTYPE, DATEDIFF(MINUTE,SH.DEX_ROW_TS, MAX(SW.DEX_ROW_TS)) as [Order Entry Time (Minutes)]
from sop10100 SH
left outer join SOP10200 SW ON SH.SOPNUMBE = SW.SOPNUMBE
and SH.SOPTYPE = SW.SOPTYPE
where SH.SOPTYPE = ‘2’
group by SH.SOPNUMBE, SH.SOPTYPE, SH.DEX_ROW_TS order by [Order Entry Time (Minutes)] desc
Thanks,
Kirk
LikeLike
Hi Kirk,
I don’t believe this will work, as the DEX_ROW_TS in the SOP10100 table will get updated by any change to the transaction; it will also get updated if someone simply pulls it up on the screen, makes no changes, then clicks Save.
If you really need to track something like this, you will probably have to create triggers on the tables and store the results in a custom table.
-Victoria
LikeLike
Hi Victoria!
We’re trying to generate reporting on invoice line items to be able to show the GL account each invoice line item was posted to. So far I’ve got:
“`SELECT HIST_INV_HEADER.CUSTNMBR Customer_ID, HIST_INV_HEADER.CUSTNAME Customer_Name,
HIST_INV_HEADER.SOPNUMBE SOP_Number, HIST_INV_HEADER.SOPTYPE SOP_Type,
HIST_INV_HEADER.DOCDATE Document_Date, HIST_INV_HEADER.GLPOSTDT GL_Posting_Date,
CASE HIST_INV_HEADER.PSTGSTUS
WHEN 0 THEN ‘Unposted’
WHEN 2 THEN ‘Posted’
ELSE ‘Error’
END Posting_Status,
HIST_INV_HEADER.CSTPONBR Customer_PO, HIST_INV_HEADER.BACHNUMB Batch_Number,
HIST_INV_HEADER.USER2ENT User_to_Enter, HIST_INV_HEADER.LOCNCODE Header_Site_ID,
HIST_INV_LINE.LOCNCODE Line_Site_ID,
CASE HIST_INV_HEADER.VOIDSTTS
WHEN 0 THEN ‘Not Voided’
WHEN 1 THEN ‘Voided’
ELSE ”
END Void_Status,
HIST_INV_HEADER.PRBTADCD Bill_To_Address_ID,
HIST_INV_HEADER.PRSTADCD Header_Ship_To_Address_ID,
HIST_INV_HEADER.ShipToName Header_Ship_To_Name,
HIST_INV_LINE.PRSTADCD Line_Ship_To_Address_ID,
HIST_INV_LINE.ShipToName Line_Ship_To_Name,
HIST_INV_LINE.ITEMNMBR Item_Number, HIST_INV_LINE.CONTITEMNBR Contract_Item_Number, HIST_INV_LINE.ITEMDESC Item_Description,
HIST_INV_HEADER.CURNCYID Currency_ID,
CASE HIST_INV_LINE.SOPTYPE
WHEN 4 THEN (HIST_INV_LINE.OXTNDPRC * – 1)
ELSE HIST_INV_LINE.OXTNDPRC
END Orig_Extended_price,
CONCAT(CONVERT(INT, HIST_INV_LINE.CONTNBR), ‘-‘, CONVERT(INT, HIST_INV_LINE.CONTLNSEQNBR)) as Contract_and_Line,
ACCTS.ACTNUMBR_1,
FROM SOP30200 HIST_INV_HEADER
INNER JOIN SOP30300 HIST_INV_LINE
ON HIST_INV_HEADER.SOPTYPE = HIST_INV_LINE.SOPTYPE
AND HIST_INV_HEADER.SOPNUMBE = HIST_INV_LINE.SOPNUMBE
LEFT OUTER JOIN SOP10102 INV_DIST
ON HIST_INV_LINE.SOPNUMBE = INV_DIST.SOPNUMBE
AND HIST_INV_LINE.LNITMSEQ = INV_DIST.SEQNUMBR
LEFT OUTER JOIN GL00100 ACCTS
ON INV_DIST.ACTINDX = ACCTS.ACTINDX“`
Any help you can provide is much appreciated!
LikeLike
Hi Steve,
I don’t think this will work because GP does not have line item GL distributions in SOP, only summary for the entire transaction. While they are originally calculated from the defaults on each line item (which can be seen in the SOP10200 and SOP30300 tables), those can be overridden at the summary level and there is no direct tie from there to each line item.
-Victoria
LikeLike
Good morning 🙂 Hopefully this is still being monitored,
I am trying to create a SQL view for a smartlist/view off of table SOP30300. We are needing to make a report for all invoices that have part numbers abc, def, ghi but not part number xyz or uvw on that same invoice but am not having any luck. I cannot figure out how to restrict it to be the same sop number for the three part numbers i need the invoice to contain. In case this doesn’t make sense, here are three examples:
INV0001 abc QTY 1 $100.00
INV0001 def QTY 2 $200.00
INV0001 ghi QTY 1 $100.00
INV0002 abc QTY 1 $100.00
INV0002 def QTY 2 $200.00
INV0002 jkl QTY 1 $100.00
INV0003 abc QTY 1 $100.00
INV0003 def QTY 2 $200.00
INV0003 ghi QTY 1 $100.00
INV0003 xyz QTY 1 $100.00
With the above list, i would want the view to include INV0001 since it has all three parts but exclude INV0002 because it does NOT have all three parts and exclude INV0003 because it also includes xyz.
Typically we create a part number to tack to that we will put on orders for tracking purposes but unfortunately this department didn’t come to me prior to processing orders. Can this be done? Thank you for your help!
LikeLike
Hi Casey,
I think the following should do it:
select * from SOP30300where
SOPNUMBE in
(select SOPNUMBE
from SOP30300 where ITEMNMBR = 'ABC')
and SOPNUMBE in
(select SOPNUMBE
from SOP30300 where ITEMNMBR = 'DEF')
and SOPNUMBE in
(select SOPNUMBE
from SOP30300 where ITEMNMBR = 'GHI')
and SOPNUMBE not in
(select SOPNUMBE
from SOP30300 where ITEMNMBR in ('XYZ','UVW'))
-Victoria
LikeLike
It looks like that may do it 🙂 I use the select * when field in (select….) ALL the time too lol. I just for some reason never stacked it like that >< This reminds me when SQL 2012 came with the IIF statement active ha ha! Thank you for opening my mind 🙂
LikeLike
I m trying to calculate the total number of invoice of an customer through sql query.
QUERY
select CUSTNMBR , Count(DISTINCT SOPNUMBE ) from SOP30200 where SOPTYPE=3 GROUP BY CUSTNMBR
I get the following results:44 as total invoices
smartlist total invoice YTD is :55
why i m not getting the exact result as smartlist.
LikeLike
It could be that you have invoices entered in other modules, not just SOP. Try this:
select a.CUSTNMBR, count(a.DOCNUMBR) Invoicesfrom
(select CUSTNMBR, DOCNUMBR
from RM20101
where VOIDSTTS = 0 and RMDTYPAL = 1
union
select CUSTNMBR, DOCNUMBR
from RM30101
where VOIDSTTS = 0 and RMDTYPAL = 1) a
group by a.CUSTNMBR
-Victoria
LikeLike
thank you very much for responding.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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]
LikeLike
Hi Mark,
I don’t think COUNT works in this type of query. Try changing your COUNT line to the following:
SUM(CASE WHEN YEAR(T.DOCDATE) = 2016 THEN 1 ELSE 0 END) AS TRANSACTIONS,
-Victoria
LikeLike
No, unfortunately not it. This still returns the dollar value sum of the sales transactions. What I am trying to get is the number (i.e. 15 transactions) in a given year.
LikeLike
Mark,
I can’t see how that would give you a sum of the dollar amounts. That logic is adding 1 for every transaction in 2016 and 0 for every transaction that is not in 2016. It seems to work correctly on my data. Are you sure you changed the right line?
-Victoria
LikeLike
Can I send you some screen shots??
LikeLike
Sure!
-Victoria
LikeLike
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
LikeLike
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
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
LikeLike
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
LikeLike
Perfect ~ you’re awesome!
LikeLike
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.
LikeLike
Eddie,
Are you talking about Sales Order Processing transactions? If so, the unposted transactions are in SOP10100 (header) and SOP10200 (line items).
-Victoria
LikeLike
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!
LikeLike
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
LikeLike
Many thanks – I would love that report even without the Extender field!
LikeLike
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
LikeLike
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
LikeLike
Victoria,
I am looking for Invoice. On only posted accounts.
Thanks!
Diane
LikeLike
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
LikeLike
Thank you so much!
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Victoria,
Thanks. Once again, you come through.
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike