SQL view with all SOP line items in Dynamics GP

May 17, 2009 by Victoria Yudin 72 Comments


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.

Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, , ,

72 Responses to “SQL view with all SOP line items in Dynamics GP”

  1. Kelly Reply May 21, 2018 at 12:17 pm

    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.

    Like

    • Victoria Yudin Reply May 21, 2018 at 12:25 pm

      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

      Like

  2. Joey Magnus Reply April 16, 2013 at 5:08 pm

    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

    Like

    • Victoria Yudin Reply April 17, 2013 at 9:22 am

      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

      Like

      • Joey Magnus Reply April 17, 2013 at 9:31 am

        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

        Like

        • Victoria Yudin Reply April 17, 2013 at 9:36 am

          Joe,

          Are you only looking at posted invoices?

          -Victoria

          Like

          • Joey Magnus Reply April 17, 2013 at 9:37 am

            Victoria,

            Yes, Only posted invoices.

            -J

            Like

            • Victoria Yudin Reply April 17, 2013 at 9:49 am

              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: 

              left outer join RM20101 r
on HH.SOPNUMBE = r.DOCNUMBR 
and r.RMDTYPAL = 1
and HH.SOPTYPE = 3

              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

              Like

              • Joey Magnus Reply April 17, 2013 at 10:01 am

                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)

                Like

                • Victoria Yudin Reply April 17, 2013 at 10:12 am

                  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:

                  WHERE
HH.DOCDATE >= '01/01/2013' 
AND HH.CUSTNMBR = '93786376' 
AND HL.XTNDPRCE >= 1 
AND HH.SOPTYPE = 3

                  The reasons for the changes:

                  • You don’t want to put quotes around amounts (SOPTYPE and XTNDPRCE)
                  • You don’t need the parentheses around everything, I try to code with the least amount of characters required to keep it as clean as possible, but you can leave them in, they will not hurt anything 🙂
                  • The last line will exclude anything partially paid/applied and I am guessing that is not what you want. If you want to only show anything that’s not fully paid/applied, you can add the following:
                    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

                  Like

                  • Joey Magnus Reply April 17, 2013 at 10:20 am

                    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

                    Like

              • Jason Gossrau Reply June 20, 2013 at 5:10 pm

                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.

                Like

                • Victoria Yudin Reply June 26, 2013 at 9:29 am

                  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

                  Like

  3. John Olley Reply May 31, 2012 at 10:28 am

    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

    Like

  4. Raouf Reply February 22, 2012 at 7:20 pm

    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?

    Like

    • Victoria Yudin Reply February 22, 2012 at 7:36 pm

      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

      Like

  5. Vic Reply April 8, 2011 at 1:13 pm

    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

    Like

    • Victoria Yudin Reply April 8, 2011 at 1:30 pm

      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

      Like

      • Vic Reply April 8, 2011 at 1:39 pm

        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

        Like

      • Vic Reply April 8, 2011 at 1:58 pm

        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

        Like

  6. Patrick Reply March 25, 2011 at 10:52 am

    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

    Like

    • Victoria Yudin Reply March 26, 2011 at 12:25 pm

      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

      Like

      • Patrick Reply March 28, 2011 at 8:59 am

        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

        Like

        • Victoria Yudin Reply March 28, 2011 at 9:18 am

          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

          Like

  7. Vic Reply March 16, 2011 at 6:38 am

    Hi Victoria!!!

    Can the item price level be added to this view???

    Thanks
    Vic

    Like

  8. Victoria Peralta Reply January 17, 2011 at 2:16 pm

    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?

    Like

    • Victoria Yudin Reply January 18, 2011 at 6:42 am

      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

      Like

      • Victoria Reply January 18, 2011 at 8:29 am

        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.

        Like

        • Victoria Yudin Reply January 18, 2011 at 8:41 am

          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

          Like

          • Victoria Reply January 18, 2011 at 8:47 am

            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.

            Like

            • Victoria Yudin Reply January 18, 2011 at 4:42 pm

              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

              Like

  9. Jean Reply September 21, 2010 at 9:51 pm

    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

    Like

  10. Larry Reply February 4, 2010 at 11:20 am

    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

    Like

    • Victoria Yudin Reply February 4, 2010 at 1:03 pm

      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

      Like

    • Larry Reply March 11, 2010 at 9:33 am

      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?

      Like

      • Victoria Yudin Reply March 11, 2010 at 9:37 am

        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

        Like

      • vijay Reply April 21, 2010 at 5:46 am

        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

        Like

        • Victoria Yudin Reply April 21, 2010 at 5:53 am

          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

          Like

    • Vijay Reply March 24, 2010 at 9:18 am

      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

      Like

      • Victoria Yudin Reply March 25, 2010 at 4:14 am

        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

        Like

        • Vijay Reply March 25, 2010 at 4:20 am

          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

          Like

  11. Lisa Sorensen Reply January 29, 2010 at 1:55 pm

    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?

    Like

    • Victoria Yudin Reply January 29, 2010 at 2:53 pm

      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:

      LEFT OUTER JOIN SOP10202 OC
ON OC.SOPTYPE = OL.SOPTYPE
AND OC.SOPNUMBE = OL.SOPNUMBE
AND OC.CMPNTSEQ = OL.CMPNTSEQ
AND OC.LNITMSEQ = OL.LNITMSEQ
LEFT OUTER JOIN SOP10106 OU
ON OH.SOPTYPE = OU.SOPTYPE
AND OH.SOPNUMBE = OU.SOPNUMBE

      2. Add the following at the very end, above the blue text:

      LEFT OUTER JOIN SOP10202 HC
ON HC.SOPTYPE = HL.SOPTYPE
AND HC.SOPNUMBE = HL.SOPNUMBE
AND HC.CMPNTSEQ = HL.CMPNTSEQ
AND HC.LNITMSEQ = HL.LNITMSEQ
LEFT OUTER JOIN SOP10106 HU
ON HH.SOPTYPE = HU.SOPTYPE
AND HH.SOPNUMBE = HU.SOPNUMBE

      3. Add the fields you want to both sections, above the FROM lines.

      Hope that helps,
      -Victoria

      Like

  12. Hossam Fathy Reply November 6, 2009 at 6:51 am

    Can we add in this view Customer Class ID, Item Class ID, Customer user defined 1 and site ID.

    Like

  13. Anna Rovinsky Reply September 4, 2009 at 12:37 pm

    Victoria,
    Thank you!! and for responding so quickly!

    -Anna

    Like

  14. Anna Rovinsky Reply September 3, 2009 at 2:30 pm

    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

    Like

    • Victoria Yudin Reply September 3, 2009 at 2:37 pm

      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

      Like

  15. Ron Draganowski Reply May 18, 2009 at 2:36 pm

    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

    Like

    • Victoria Yudin Reply May 18, 2009 at 3:41 pm

      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

      Like

Trackbacks/Pingbacks

  1. Microsoft Dynamics GP – Excel Power Pivot SOP Dashboard (with video) – Belinda Allen, Microsoft MVP Business Solutions - August 2, 2016

    […] SQL view with all SOP line items in Microsoft Dynamics GP […]

    Like
  2. DynamicAccounting.net | SQL View with All Dynamics GP SOP LinesSQL View with All Dynamics GP SOP Lines - DynamicAccounting.net - April 25, 2016

    […] on May 18, 2009 by Mark Polino Victoria Yudin has a new SQL View up to show all of the Sales Order Processing transaction lines, posted and […]

    Like
  3. Microsoft Dynamics GP - Excel Power Pivot SOP Dashboard (with video) - Belinda, the GP CSI - Microsoft Dynamics GP - Microsoft Dynamics Community - November 18, 2013

    […] SQL view with all SOP line items in Microsoft Dynamics GP […]

    Like
  4. Dynamics GP SOP line items with serial numbers and comments | Interesting Findings & Knowledge Sharing - June 4, 2012

    […] on some reader requests, this new view is a combination of  my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. […]

    Like
  5. Dynamics GP SOP line items with serial numbers and comments – 6/4, Victoria Yudin | Partner Compete - June 4, 2012

    […] on some reader requests, this new view is a combination of  my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. […]

    Like
  6. Dynamics GP SOP line items with serial numbers and comments - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - June 4, 2012

    […] 0 Based on some reader requests, this new view is a combination of  my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. […]

    Like
  7. Dynamics GP SOP line items with serial numbers and comments | Victoria Yudin - June 4, 2012

    […] Victoria Yudin 0 Comments Based on some reader requests, this new view is a combination of  my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. […]

    Like
  8. SQL View with All Dynamics GP SOP Lines - DynamicAccounting.net - May 18, 2009

    […] View with All Dynamics GP SOP Lines Victoria Yudin has a new SQL View up to show all of the Sales Order Processing transaction lines, posted and unposted. Published: Monday, May 18, 2009, 04:00 […]

    Like

Leave a comment