button

SQL view with all SOP line items in Dynamics GP


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 http://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.

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

  1. 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

    • 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

      • 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

        • Joe,

          Are you only looking at posted invoices?

          -Victoria

          Like

          • Victoria,

            Yes, Only posted invoices.

            -J

            Like

            • 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

              • 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

                • 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

                  • 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

              • 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

                • 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

  2. 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

  3. 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

    • 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

  4. 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

    • 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

      • 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

      • 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

  5. 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

    • 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

      • 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

        • 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

  6. Hi Victoria!!!

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

    Thanks
    Vic

    Like

  7. 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,

      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

      • 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, 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

          • 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,

              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

  8. 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

  9. 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

    • 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

    • 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

      • 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

      • 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

        • 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

    • 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

      • 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

        • 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

  10. 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

    • 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

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

    Like

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

    -Anna

    Like

  13. 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

    • 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

  14. 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

    • 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, the GP CSI - Microsoft Dynamics GP - Microsoft Dynamics Community - November 18, 2013

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

    Like

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,532 other followers

%d bloggers like this: