Here is a script that I have found useful on numerous occasions – it returns the lines items for all SOP (Sales Order Processing) transactions, both posted and unposted. This view will work with either SmartList Builder or Crystal Reports. For more scripts like this, take a look at my GP Reports page.
~~~~~
CREATE VIEW view_SOP_Line_Items AS
/*******************************************************************
view_SOP_Line_Items
Created on May 17, 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see https://victoriayudin.com/gp-reports/
All line items for posted and unposted SOP transactions
Returns Functional amounts only
Fields that can have different values on the document header and
line item are both returned prefaced by 'header' or 'line'
OH - Open Header - SOP10100
OL - Open Line - SOP10200
HH - History Header - SOP30200
HL - History Line - SOP30300
Updated Sep 22, 2010 to add Gross Margin and Gross Margin Pct
*******************************************************************/
SELECT OH.SOPNUMBE SOP_Number, OH.SOPTYPE SOP_Type, OH.DOCDATE Document_Date, OH.GLPOSTDT GL_Posting_Date, OH.ORDRDATE Order_Date, OH.DUEDATE Due_Date, OH.MSTRNUMB Master_Number, CASE OH.PSTGSTUS WHEN 0 THEN 'Unposted' WHEN 2 THEN 'Posted' ELSE 'Error' END Posting_Status, OH.CUSTNMBR Customer_ID, OH.CUSTNAME Customer_Name, OH.CSTPONBR Customer_PO, OH.BACHNUMB Batch_Number, OH.LOCNCODE Header_Site_ID, OL.LOCNCODE Line_Site_ID, CASE OH.VOIDSTTS WHEN 0 THEN 'Not Voided' WHEN 1 THEN 'Voided' ELSE '' END Void_Status, OH.SLPRSNID Header_Salesperson, OL.SLPRSNID Line_Salesperson, OH.SALSTERR Header_Territory, OL.SALSTERR Line_Territory, OH.PYMTRMID Payment_Terms_ID, OH.SHIPMTHD Header_Shipping_Method, OL.SHIPMTHD Line_Shipping_Method, OH.PRBTADCD Bill_To_Address_ID, OH.PRSTADCD Header_Ship_To_Address_ID, OH.ShipToName Header_Ship_To_Name, OH.ADDRESS1 Header_Address_1, OH.ADDRESS2 Header_Address_2, OH.ADDRESS3 Header_Address_3, OH.CITY Header_City, OH.[STATE] Header_State, OH.ZIPCODE Header_Zip_Code, OH.COUNTRY Header_Country, OL.PRSTADCD Line_Ship_To_Address_ID, OL.ShipToName Line_Ship_To_Name, OL.ADDRESS1 Line_Address_1, OL.ADDRESS2 Line_Address_2, OL.ADDRESS3 Line_Address_3, OL.CITY Line_City, OL.[STATE] Line_State, OL.ZIPCODE Line_Zip_Code, OL.COUNTRY Line_Country, OH.DOCAMNT Total_Document_Amount, OH.MRKDNAMT Total_Markdown_Amount, OH.SUBTOTAL Document_Subtotal, OH.FRTAMNT Freight_Amount, OH.MISCAMNT Misc_Amount, OH.TAXAMNT Tax_Amount, OH.CURNCYID Currency_ID, OH.ReqShipDate Header_ReqShipDate, OL.ReqShipDate Line_ReqShipDate, OH.USER2ENT User_to_Enter, OH.COMMNTID Header_Comment_ID, OL.COMMNTID Line_Comment_ID, OL.LNITMSEQ Line_Item_Sequence, OL.CMPNTSEQ Component_Sequence, CASE OL.NONINVEN WHEN 0 THEN 'Inventory' WHEN 1 THEN 'Non-Inventory' ELSE '' END Item_Type, OL.ITEMNMBR Item_Number, OL.ITEMDESC Item_Description, OL.QUANTITY Quantity, OL.UOFM U_of_M, OL.QTYBSUOM Qty_in_Base_U_of_M, OL.QTYREMAI Qty_Remaining, OL.UNITPRCE Unit_Price, OL.XTNDPRCE Extended_Price, CASE OL.MRKDNTYP WHEN 0 THEN 'Percentage' WHEN 1 THEN 'Amount' ELSE '' END Markdown_Type, OL.MRKDNAMT Markdown_Amount, OL.MRKDNPCT/100 Markdown_Percentage, OL.TRDISAMT Trade_Discount_Amount, OL.UNITCOST Unit_Cost, OL.EXTDCOST Extended_Cost, OL.XTNDPRCE - OL.EXTDCOST Gross_Margin, CASE OL.XTNDPRCE WHEN 0 THEN 0 ELSE (OL.XTNDPRCE-OL.EXTDCOST)/OL.XTNDPRCE*100 END Gross_Margin_Pct FROM SOP10100 OH INNER JOIN SOP10200 OL ON OH.SOPTYPE = OL.SOPTYPE AND OH.SOPNUMBE = OL.SOPNUMBE UNION ALL SELECT HH.SOPNUMBE SOP_Number, HH.SOPTYPE SOP_Type, HH.DOCDATE Document_Date, HH.GLPOSTDT GL_Posting_Date, HH.ORDRDATE Order_Date, HH.DUEDATE Due_Date, HH.MSTRNUMB Master_Number, CASE HH.PSTGSTUS WHEN 0 THEN 'Unposted' WHEN 2 THEN 'Posted' ELSE 'Error' END Posting_Status, HH.CUSTNMBR Customer_ID, HH.CUSTNAME Customer_Name, HH.CSTPONBR Customer_PO, HH.BACHNUMB Batch_Number, HH.LOCNCODE Header_Site_ID, HL.LOCNCODE Line_Site_ID, CASE HH.VOIDSTTS WHEN 0 THEN 'Not Voided' WHEN 1 THEN 'Voided' ELSE '' END Void_Status, HH.SLPRSNID Header_Salesperson, HL.SLPRSNID Line_Salesperson, HH.SALSTERR Header_Territory, HL.SALSTERR Line_Territory, HH.PYMTRMID Payment_Terms_ID, HH.SHIPMTHD Header_Shipping_Method, HL.SHIPMTHD Line_Shipping_Method, HH.PRBTADCD Bill_To_Address_ID, HH.PRSTADCD Header_Ship_To_Address_ID, HH.ShipToName Header_Ship_To_Name, HH.ADDRESS1 Header_Address_1, HH.ADDRESS2 Header_Address_2, HH.ADDRESS3 Header_Address_3, HH.CITY Header_City, HH.[STATE] Header_State, HH.ZIPCODE Header_Zip_Code, HH.COUNTRY Header_Country, HL.PRSTADCD Line_Ship_To_Address_ID, HL.ShipToName Line_Ship_To_Name, HL.ADDRESS1 Line_Address_1, HL.ADDRESS2 Line_Address_2, HL.ADDRESS3 Line_Address_3, HL.CITY Line_City, HL.[STATE] Line_State, HL.ZIPCODE Line_Zip_Code, HL.COUNTRY Line_Country, HH.DOCAMNT Total_Document_Amount, HH.MRKDNAMT Total_Markdown_Amount, HH.SUBTOTAL Document_Subtotal, HH.FRTAMNT Freight_Amount, HH.MISCAMNT Misc_Amount, HH.TAXAMNT Tax_Amount, HH.CURNCYID Currency_ID, HH.ReqShipDate Header_ReqShipDate, HL.ReqShipDate Line_ReqShipDate, HH.USER2ENT User_to_Enter, HH.COMMNTID Header_Comment_ID, HL.COMMNTID Line_Comment_ID, HL.LNITMSEQ Line_Item_Sequence, HL.CMPNTSEQ Component_Sequence, CASE HL.NONINVEN WHEN 0 THEN 'Inventory' WHEN 1 THEN 'Non-Inventory' ELSE '' END Item_Type, HL.ITEMNMBR Item_Number, HL.ITEMDESC Item_Description, HL.QUANTITY Quantity, HL.UOFM U_of_M, HL.QTYBSUOM Qty_in_Base_U_of_M, HL.QTYREMAI Qty_Remaining, HL.UNITPRCE Unit_Price, HL.XTNDPRCE Extended_Price, CASE HL.MRKDNTYP WHEN 0 THEN 'Percentage' WHEN 1 THEN 'Amount' ELSE '' END Markdown_Type, HL.MRKDNAMT Markdown_Amount, HL.MRKDNPCT/100 Markdown_Percentage, HL.TRDISAMT Trade_Discount_Amount, HL.UNITCOST Unit_Cost, HL.EXTDCOST Extended_Cost, HL.XTNDPRCE - HL.EXTDCOST Gross_Margin, CASE HL.XTNDPRCE WHEN 0 THEN 0 ELSE (HL.XTNDPRCE-HL.EXTDCOST)/HL.XTNDPRCE*100 END Gross_Margin_Pct FROM SOP30200 HH INNER JOIN SOP30300 HL ON HH.SOPTYPE = HL.SOPTYPE AND HH.SOPNUMBE = HL.SOPNUMBE
/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO GRANT SELECT ON view_SOP_Line_Items TO DYNGRP
~~~~~
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Hi Victoria,
Would you know why a quote would be showing in this view, but not show on the Sales Quote History Report?
I’ve been looking just at one customer and some of their quotes show on the report and some don’t. Doesn’t seem to be related to whether they are voided or unposted.
I’m not putting any criteria on the report except for the customer. I’ve also run it wide open and experienced the same.
LikeLike
Hi Kelly,
Without seeing what you’re pulling for the report and/or your data, this is very difficult to answer. For the quotes that don’t show up on the GP report – can you see them in SmartList? In both Sales Transactions and Sales Line Items?
-Victoria
LikeLike
Victoria,
I am attempting to add CURTRXAM to the view, but I am not sure how to do this. I attempted to add it as a column and then realized that it is not a column in any of the tables in the FROM field. What can I do to add this column?
Thanks in advance,
Joe
LikeLike
Joe,
CURTRXAM is the unapplied/unpaid amount on a posted transaction. The SOP module does not track what is due on transactions once they are posted and go to the RM module. Also, this view contains both posted and unposted transactions. Can you explain what exactly you’re trying to accomplish? Maybe I can offer some suggestions once I understand your requirements.
-Victoria
LikeLike
I have currently connected this view to excel. I have also created a WHERE argument within the view that filters out SOP type 3 (invoices) for a particular client ID. My goal now is to create another filter using CURTRXAM to remove Invoices that have been paid. The end result is essentially a manual invoicing system. There are some calculations that I must do with a certain set of invoices outside of Great Plains and then manually create those invoices. Right now it is a lot of typing, cutting, and pasting. pulling all the data into excel will allow me to automate the process. I have built the automation but I would like to further filter what data is pulled from GP.
Thanks in advance,
Joe
LikeLike
Joe,
Are you only looking at posted invoices?
-Victoria
LikeLike
Victoria,
Yes, Only posted invoices.
-J
LikeLike
Joe,
Ok, then you only really need the bottom section of this view, after the UNION ALL line.
What you can do is add the following to it at the end to link to the RM20101 table:
Then you can add the r.CURTRXAM column. If it is NULL, that means the invoice is already in history, so it is paid/fully applied. Important note – this will only work for posted SOP invoices. Let me know if you need further help on this.
-Victoria
LikeLike
It appears to be working but below is the code just to make sure I understand what you mean.
/ HL.XTNDPRCE * 100 END AS Gross_Margin_Pct, r.CURTRXAM AS Current_Trx_Amount
FROM
dbo.SOP30200 AS HH INNER JOIN
dbo.SOP30300 AS HL ON HH.SOPTYPE = HL.SOPTYPE AND HH.SOPNUMBE = HL.SOPNUMBE LEFT OUTER JOIN
dbo.RM20101 AS r ON HH.SOPNUMBE = r.DOCNUMBR AND r.RMDTYPAL = 1 AND HH.SOPTYPE = 3
WHERE
(HH.DOCDATE >= ‘1/1/2013’) AND (HH.CUSTNMBR = ‘93786376’) AND (HL.XTNDPRCE >= ‘1’) AND
(HH.SOPTYPE = ‘3’) AND
(r.CURTRXAM = HH.DOCAMNT)
LikeLike
Joe,
I don’t think some of the stuff you have at the end in the where clause is correct. I would change it to:
The reasons for the changes:
AND r.CURTRXAM > 0
Also, just confirming – you want the extended price to be greater than or equal to $1? What if it is $0.50? That should be excluded?
-Victoria
LikeLike
Victoria,
I changed the extended price to >0 (good observation) and I am trying to exclude partially paid items. Thank you so much for your assistance.
-Joey
LikeLike
Victoria – I’m missing something here. I tagged your code on at the end but do not have the fields from RM20101 avail. What am I missing?
FROM SOP30200 HH
INNER JOIN SOP30300 HL
ON HH.SOPTYPE = HL.SOPTYPE
AND HH.SOPNUMBE = HL.SOPNUMBE
left outer join RM20101 r
on HH.SOPNUMBE = r.DOCNUMBR
and r.RMDTYPAL = 1
and HH.SOPTYPE = 3
Thanks a ton for your site. It is the best resource I’ve ever come across for anything ERP related.
LikeLike
Hi Jason,
That won’t work because my code is already a union, so at the very least you would need to add the RM20101 table to both both parts of the union. Depending on what information you’re looking for from RM, you might also need to use the RM30101 table. If you only need RM20101, you could create a new view that uses the following code:
select s.*, r.* --change as needed
from view_SOP_Line_Items s
left outer join RM20101 r
on s.SOP_Number = r.DOCNUMBR
and r.RMDTYPAL = 1
and s.SOP_Type = 3
-Victoria
LikeLike
Hi Victoria,
How can I add items to show serial numbers? That would be a huge help with this list.
Thank you for all you do!
John
LikeLike
Hi John,
Give me a few days and I will post a new view that includes serial numbers.
-Victoria
LikeLike
You are too kind. Thank you so much!
John
LikeLike
Hi Victoria, I love this view and I trimed it to the fileds that I really use.
My question is can we add a column for the Audit Trail Number of the posted documents?
LikeLike
Hi Raouf,
Do you mean the field that shows up as the Originating TRX Source in the GL? If so, that is TRXSORCE in both the SOP10100 and SOP30200 tables, you can just add it to each section. If you mean something else, please let me know where you see the field in GP.
-Victoria
LikeLike
Victoria
I am using view_SOP_Line_Items for some reports. But When I take the item price times the qty for all the posted invoices (Number 3) for a particular customer for all of 2010 and add that up , it does not equal the number for total sales in the RM00104 table it is off by 57,763 dollars. Any Ideas as to why???
Thanks
Vic
LikeLike
Hi Vic,
We keep going around and around on this. 🙂 What is the goal for this exercise?
Just to give you a few reasons/ideas for why these would not be the same… SOP is a subset of RM. The Sales total in RM00104 may be including things other than just the subtotals which is what you’re getting from SOP. Also what dates are you using from SOP? And are you only using HISTTYPE = 0 from RM00104? Otherwise, you may be doubling up your numbers.
-Victoria
LikeLike
Victoria
I know and I am sorry, but these people here cant get that thought through their heads….LOL….They keep insisting on Tying things out and dont understand how a real system like this works.
This is actually someone else now. They are only hist type 0. The dates are all last year 2010. I am also netting out returns from the RM00104 table. I am taking only Posted invoices from you SOP view.
When you say
“The Sales total in RM00104 may be including things other than just the subtotals”. What might some of those be?? Maybe if i can explain that to them I can stop the maddness and just go tomorrow and enjoy convergence with my fellow Geeks that get it!!!!
Thanks
Vic
LikeLike
Vic,
I have answered this twice recently…take a look at my recent replies to your questions on the SOP Tables and RM Tables pages.
-Victoria
LikeLike
Victoria
I may have found something, Is there anything in this view that would be restricting the results from your view
SELECT GL_Posting_Date AS Invoice_DATE, Item_Number AS ITEM_CODE, Quantity AS QTY_SHIP, Customer_ID AS CUST_CODE,
Unit_Price AS ITEM_PRICE
FROM view_SOP_Line_Items
WHERE (SOP_Type = 3) AND (Posting_Status = ‘POSTED’)
GROUP BY GL_Posting_Date, Item_Number, Quantity, Customer_ID, Unit_Price
UNION
SELECT DATE_FLD AS Invoice_DATE, ITEM_CODE, QTY_SHIP, CUST_CODE, ITEM_PRICE
FROM [x_EVEREST_ITEMS_TO _GP]
GROUP BY DATE_FLD, ITEM_CODE, QTY_SHIP, CUST_CODE, ITEM_PRICE
LikeLike
Vic,
I don’t have any idea of what x_EVEREST_ITEMS_TO _GP is, so I cannot speak to that.
However I can tell you for sure that the RM00104 table will not be using the GL Posting Date from SOP. It will be using DOCDATE.
Why not use the view here: https://victoriayudin.com/2009/04/24/sql-view-with-all-posted-receivables-transactions/ and see if you can find a better way to total up to what you are seeing in RM00104.
-Victoria
LikeLike
Victoria
I am trying to do something with report writer but I am not sure where to get the data from. I need to add the original SOR number to the printout on BAK orders that have been transferred to new SOR’s. I found the BAK number on the SOP10100 table but the only references I find to the original SOR that created the back order are in the pack slip number field and the pick number field. I don’t know if these fields are stable and or trust worthy for that kind of data. Any suggestions? My users are requiring this data be added to the SOR print outs.
Patrick
LikeLike
Hi Patrick,
Each SOP transaction tracks an ‘Originating’ SOP Number and Type (ORIGNUMB and ORIGTYPE in SOP10100 and SOP30200). These will hold the transaction type and number of the transaction that was transferred to create the one you are looking at. That’s probably the fields I would use for this if I am understanding your question.
If you are using Master Numbers in SOP (most people are, but it’s not required), all related transactions will also have the same master number (MSTRNUMB in SOP10100 and SOP30200), but that may be a little more difficult to use if you have a lot of transfers between related transactions.
Also, you’re not really using Report Writer, right? You’re creating your reports in Crystal or SSRS and using GP Reports Viewer to run them? 🙂
-Victoria
LikeLike
Victoria
I was planning to use the report writer to modify our current report. I thought it would be a simple adding a field. What I am trying to retrieve is the first SOR number related to the current SOR (a back order being fulfilled) Would I be better off redesigning the report from scratch using Crystal?
Patrick
LikeLike
Hi Patrick,
I make no secret of not liking Report Writer, so not the best question for me. 🙂
You need to talk to your GP users and look at your GP data to determine if there is a possibility that you will ever need to go back more than one pass.
If not, you should be able to simply use those fields I mentioned. If yes, then this can get pretty complicated because you will need to determine how to get the ‘first’ one.
This may be a good question to get your GP Partner to help you with, as this can get very involved very quickly.
-Victoria
LikeLike
Thanks
I’ll dig deeper and see where it leads me. As always thanks for the help.
Patrick
LikeLike
Hi Victoria!!!
Can the item price level be added to this view???
Thanks
Vic
LikeLike
Vic,
At the line item level or the header level?
-Victoria
LikeLike
Hi Victoria,
At the item level.
Thanks
Vic
LikeLike
Victoria
Nevermind I figured it out and got it added. Since your views are written so beautifully it was pretty easy, Thanks again for all you do!!!!
Vic
LikeLike
Vic,
That’s awesome – thank you for letting me know. 🙂
-Victoria
LikeLike
Hi Victoria,
I am trying to create a view that will display all orders for an specific customers, but I do not want to show all documerns, I want to show the order (tpe 0 or 2), the items and the status of the items, which is based on the rest of the documents,(ex. backorder, invoiced, …)
Is thispossible on GP?
LikeLike
Victoria,
Are you saying you want to link from each order to all the transactions that order was transferred to and report on that? That should be possible if you are using Master Numbers in GP, but may get pretty complicated and is beyond the scope of what I would be able to help with in a blog post. Also, I am not sure what you mean by “tpe 0 or 2”. If you are referring to SOPTYPE, 0 is not a valid option for that, orders are SOPTYPE = 2.
-Victoria
LikeLike
Thank you for your promptly response. I am trying to display all orders(type 2) for an specific customer and the status of each item within the orders. I do not want to show all the documents in between, I just want to go straight to the last document and find out if the item was invoiced, voided or still on backorder. Sometimes we can get a lot of backorder before it become an order and then and invoice and we do not want to confuse the customer with all those documents. I was wondering if there is a way in GP otherwise I might have to create a store procedure to get the output I want. Thank you in advance for your help.
I forgot to mention this output will be used on a web where user can check on the status of their orders.
LikeLike
Victoria, the answer to this may depend on your exact workflow in GP. Do you enter an order and then use that order all the way through? So you might change the status on each item, but you don’t actually transfer the order to a backorder? In that case, you can use the status of each item from the order.
Or do you have multiple transfers, like order >> backorder >> invoice >> backorder >> invoice? In that case, this gets much more complicated. One issue you may run into is that GP does not track original line numbers when documents get transferred, so if you ever have a situation where there are 2 of the same item on an order, this may not be easily achievable without consolidating those items into one line.
-Victoria
LikeLike
This is my first time working with GP, the way I see is configured here is that, like you said, orders become an invoice, but also can transferred into backorder if one of the item is not available, the the backorder become an order when the item is available and then turn into an invoice. I do not want to display all those documents in between because it can become pretty confusing to the customer, I would like just to get the final status of the item, if it is possible.
LikeLike
Victoria,
While this may be possible, it will not be easy and it’s not something I can help with on a blog, without access to the data. Since you say it is your first time working with GP, I would recommend getting some help with this from someone that knows the GP table structure and transaction flow – perhaps your GP Partner can offer this.
-Victoria
LikeLike
We will do.Thank you so much for you time. I really appreciate it.
LikeLike
Victoria,
Great info on your site! Thanks for sharing all this information with us. Would it be possible to add the extended cost field and calculate the Gross Margin(%) per line item?
Thanks,
Jean
LikeLike
Hi Jean,
The extended cost was already there, but I added gross margin and gross margin percentage columns at the end for you.
-Victoria
LikeLike
Thanks for such a quick response! I will test later today.
LikeLike
I am looking for a way in sql to create a batch and put all my quotes entered for the year of 2007 in just one batch. Once i get them in the batch i won’t to delete the batch. I tried using the GP utility to delete them but it never works
LikeLike
Larry,
If you can, test this in a test environment first to make sure it doesn’t break anything and make a backup prior to running any scripts.
This should move all your 2007 quotes into a batch called ‘2007 QUOTES’:
UPDATE SOP10100
SET BACHNUMB = '2007 QUOTES'
WHERE SOPTYPE = 1
AND YEAR(DOCDATE) = 2007
-Victoria
LikeLike
Master Posting problem once i choose the batch i want to master post
I have given a user the appropriate rights to master post and when they
select the batch they want to master post it gives them this error:
You have not marked any batches for posting
If I go in and give the user power user rights it works fine.
Any suggestions?
LikeLike
Larry,
To be able to post in GP the user must be granted explicit permissions to post that particular type of transaction, simply having access to the Master Posting window is not enough. If you look at the predefined security tasks, you will see ones labeled ‘Purchasing series post’, ‘Sales series post’, ‘Post Financial series’, etc.
-Victoria
LikeLike
i’m in the security task under gp->series posting permissions and i have granted access to all series and it still doesn’t work
LikeLike
Larry,
You might want to see if the Support Debugging Tool will help you identify the issue: http://blogs.msdn.com/developingfordynamicsgp/archive/2010/03/10/update-to-support-debugging-tool-for-build-12.aspx.
-Victoria
LikeLike
HI Victoria
How can i add the date criteria to limit all sales orders from 01/01/10 . as we have data from 2003 , this will make big list, your help will be appreicated
Regards,
Vijay
LikeLike
Vijay,
If you already have the view created using my code above, you can use the following script:
SELECT * FROM view_SOP_Line_Items WHERE Document_Date >= '1/1/2010'
If you want to change the view, you would need to add a WHERE clause to the end of each of the two sections in the view (the 2 sections are separated by the UNION ALL in the middle). For the top section you can add the following:
WHERE OH.DOCDATE >= '1/1/2010'
And for the bottom section you can add:
WHERE HH.DOCDATE >= '1/1/2010'
Hope that helps.
-Victoria
LikeLike
HI
This script is fantastic, i am tracking some data in Item Master like, Item Short name, Item generic description, warranty days, vendor, Track, Public/TM, Inhouse/Onsite etc ( last 4 are user defined alternative fields)
can you include the above in the script so that i can get this results from sales orders
Vijay
LikeLike
Vijay,
Are you talking about the Categories on the Item Maintenance Options windows? If so, you should be able to add them by joining the IV00101 table on the ITEMNMBR field. I am not sure how useful this would be to others, so I would prefer not to complicate the code posted above.
-Victoria
LikeLike
Hi Victroria
thanks for the quick response, i just want add in the same sop line items, some more detals which are tracked in the item master file
like you are taking item number, descritption etc which are stored in item master
Thanks
Vijay
LikeLike
Hi Victoria,
This is very helpful. Using the view you provided, how could I incorporate pulling in the SOP10106 User-Defined Work History table as well as the SOP10202 Line Comment Work and History table? Especially since you are using a Union.
I would love to use this view for a Smartlist Builder, but I have a couple of clients that need to be able to search on the Line Item comment fields as well as the SOP User Defined fields. I know the pre-built Smartlist incorporates the 10106 table, but it doesn’t incorporate the SOP10202 table.
I tried to use the existing view which Ron pointed out, and linked the SOP10202 table to the pre-defined view, but it didn’t like the linking.
Any suggestions?
LikeLike
Lisa,
If I were designing this from scratch this may not be the approach I would take, but here is a quick way to do it:
1. Add the following above the UNION ALL line:
2. Add the following at the very end, above the blue text:
3. Add the fields you want to both sections, above the FROM lines.
Hope that helps,
-Victoria
LikeLike
Can we add in this view Customer Class ID, Item Class ID, Customer user defined 1 and site ID.
LikeLike
Hossam,
I will e-mail you for further details on this. Thanks.
-Victoria
LikeLike
Victoria,
Thank you!! and for responding so quickly!
-Anna
LikeLike
Victoria
Thanks for the great info, I have been using a few of the views on this page.
I really like this SOP lin item detail view. Do you know what table to use if I need to add the GL posting account and GL Journal entry number. I need to be able to tie the sales joournal to Financials.
There’s a Sales Journal smartlist in Financials but due to the Posting setup set to “batch” I can seem to get a detail of the summary on the GL to tie to the sales line item.
Thansk,
Anan
LikeLike
Hi Anna,
The GL distributions will be in the SOP10102 table for all SOP transactions (work and history). To link to the GL tables (for the journal entry), I believe you would link the SOP10102.TRXSORCE to the ORTRXSRC in the GL tables. To translate the ACTINDX to an actual account number, you can link in GL00105.
-Victoria
LikeLike
I’ve written queries like this many times before prior to version 10.0 of Microsoft Dynamics GP. However, with version 10.0 several dozen new SQL views come pre-packaged with GP. They correspond to the Smartlist default objects, so if there’s a smartlist object, there’s probably a SQL view.
The new SQL View for sales line items is conveniently called “SalesLineItems”. Since SQL treats views like tables, the results of this view can be joined to other tables. THE BEST PART of this new view is that it gives meaningful names to ambiguous numeric values, like SOPTYPE.
Try it out:
SELECT * FROM SalesLineItems
Ron Draganowski
Solution Services Practice Manager
Olsen Thielen Technologies, Inc.
St Paul, Minnesota
rdrag@ottechnologies.com
http://www.ottechnologies.com
Find me on LinkedIn: http://www.linkedin.com/in/rondraganowski
LikeLike
Ron,
Thanks for pointing this out.
I have seen the SalesLineItems view in GP 10.0, however, I personally have a few issues with it:
1. It has ALL the columns which would take much longer to run if you have a lot of data. And I have never seen anyone need all the columns in there for anything they’re doing. Even in my view there may be too many columns, but at least it would be easy to delete them.
2. God help you if you need to edit the view. It’ll take you a while just to get it into a format you can work with. Just looking at it makes my eyes hurt.
3. You can’t use it with Crystal which typically freaks out if you have spaces in either table names or column names.
4. As you mention, this is only in version 10.0, so for customers on earlier versions this is not available unless they ask their GP Partner or install GP 10.0 elsewhere.
On the other hand, this view is probably great to see how all the tables are linked and to have the user friendly names for fields all in one place. And the functions are useful, I forgot about those. 🙂
-Victoria
LikeLike