push the gear

Dynamics GP SOP line items with serial numbers and 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. This new view will return all posted (history) or unposted (open) Sales Order Processing line items with the associated serial numbers (or lot numbers). The serial/lot numbers for each line will be concatenated into one field. The results will also include the header and line item comments.

For more Dynamics GP SOP scripts please visit my SOP SQL Views page. Or take a look at my GP Reports page for additional reporting links. SOP table information can be found on this page.

CREATE VIEW view_SOP_Line_Items_Serial
AS

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ 
-- view_SOP_Line_Items_Serial
-- Created on June 4, 2012 by Victoria Yudin, 
--     Flexible Solutions, Inc.
-- For updates please see http://victoriayudin.com/gp-reports/
-- All line items for posted and unposted SOP transactions 
--     with serial and/or lot numbers
-- Returns Functional amounts only
-- Component Items are not taken into consideration
-- Fields that can have different values on the document header 
-- & line item are both returned prefaced by 'Header' or 'Line'
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~  

SELECT S.*,
coalesce(C.CMMTTEXT,'') Line_Item_Comment,
coalesce(SN.Serial_Lot_Numbers,'') Serial_Lot_Numbers,
coalesce(HC.CMMTTEXT,'') Header_Comment

FROM  --sop lines
(SELECT Doc_Status = 'Open',
H.SOPNUMBE SOP_Number, H.SOPTYPE SOP_Type,
H.DOCDATE Document_Date, H.GLPOSTDT GL_Posting_Date,
H.ORDRDATE Order_Date, H.DUEDATE Due_Date,
H.MSTRNUMB Master_Number,
CASE H.PSTGSTUS
  WHEN 0 THEN 'Unposted'
  WHEN 2 THEN 'Posted'
  ELSE 'Error'
  END Posting_Status,
H.CUSTNMBR Customer_ID, H.CUSTNAME Customer_Name,
H.CSTPONBR Customer_PO, H.BACHNUMB Batch_Number,
H.LOCNCODE Header_Site_ID, L.LOCNCODE Line_Site_ID,
CASE H.VOIDSTTS
  WHEN 0 THEN 'Not Voided'
  WHEN 1 THEN 'Voided'
  ELSE ''
  END Void_Status,
H.SLPRSNID Header_Salesperson, L.SLPRSNID Line_Salesperson,
H.SALSTERR Header_Territory, L.SALSTERR Line_Territory,
H.PYMTRMID Payment_Terms_ID,
H.SHIPMTHD Header_Shipping_Method, 
L.SHIPMTHD Line_Shipping_Method,
H.PRBTADCD Bill_To_Address_ID,
H.PRSTADCD Header_Ship_To_Address_ID, 
H.ShipToName Header_Ship_To_Name,
H.ADDRESS1 Header_Address_1, H.ADDRESS2 Header_Address_2,
H.ADDRESS3 Header_Address_3, H.CITY Header_City,
H.[STATE] Header_State, H.ZIPCODE Header_Zip_Code,
H.COUNTRY Header_Country,
L.PRSTADCD Line_Ship_To_Address_ID, 
L.ShipToName Line_Ship_To_Name,
L.ADDRESS1 Line_Address_1, L.ADDRESS2 Line_Address_2,
L.ADDRESS3 Line_Address_3, L.CITY Line_City,
L.[STATE] Line_State, L.ZIPCODE Line_Zip_Code,
L.COUNTRY Line_Country,
H.DOCAMNT Total_Document_Amount, 
H.MRKDNAMT Total_Markdown_Amount,
H.SUBTOTAL Document_Subtotal,
H.FRTAMNT Freight_Amount, H.MISCAMNT Misc_Amount,
H.TAXAMNT Tax_Amount, H.CURNCYID Currency_ID,
H.ReqShipDate Header_ReqShipDate, 
L.ReqShipDate Line_ReqShipDate,
H.USER2ENT User_to_Enter,
H.COMMNTID Header_Comment_ID, L.COMMNTID Line_Comment_ID,
L.LNITMSEQ Line_Item_Sequence, L.CMPNTSEQ Component_Sequence,
CASE L.NONINVEN
  WHEN 0 THEN 'Inventory'
  WHEN 1 THEN 'Non-Inventory'
  ELSE ''
  END Item_Type,
L.ITEMNMBR Item_Number, L.ITEMDESC Item_Description,
L.QUANTITY Quantity, L.UOFM U_of_M, 
L.QTYBSUOM Qty_in_Base_U_of_M,
L.QTYREMAI Qty_Remaining, L.UNITPRCE Unit_Price,
L.XTNDPRCE Extended_Price,
CASE L.MRKDNTYP
  WHEN 0 THEN 'Percentage'
  WHEN 1 THEN 'Amount'
  ELSE ''
  END Markdown_Type,
L.MRKDNAMT Markdown_Amount, L.MRKDNPCT/100 Markdown_Percentage,
L.TRDISAMT Trade_Discount_Amount,
L.UNITCOST Unit_Cost, L.EXTDCOST Extended_Cost

FROM SOP10100 H
INNER JOIN SOP10200 L
	ON H.SOPTYPE = L.SOPTYPE AND H.SOPNUMBE = L.SOPNUMBE

UNION

SELECT Doc_Status = 'History',
H.SOPNUMBE SOP_Number, H.SOPTYPE SOP_Type,
H.DOCDATE Document_Date, H.GLPOSTDT GL_Posting_Date,
H.ORDRDATE Order_Date, H.DUEDATE Due_Date,
H.MSTRNUMB Master_Number,
CASE H.PSTGSTUS
  WHEN 0 THEN 'Unposted'
  WHEN 2 THEN 'Posted'
  ELSE 'Error'
  END Posting_Status,
H.CUSTNMBR Customer_ID, H.CUSTNAME Customer_Name,
H.CSTPONBR Customer_PO, H.BACHNUMB Batch_Number,
H.LOCNCODE Header_Site_ID, L.LOCNCODE Line_Site_ID,
CASE H.VOIDSTTS
  WHEN 0 THEN 'Not Voided'
  WHEN 1 THEN 'Voided'
  ELSE ''
  END Void_Status,
H.SLPRSNID Header_Salesperson, L.SLPRSNID Line_Salesperson,
H.SALSTERR Header_Territory, L.SALSTERR Line_Territory,
H.PYMTRMID Payment_Terms_ID,
H.SHIPMTHD Header_Shipping_Method, 
L.SHIPMTHD Line_Shipping_Method,
H.PRBTADCD Bill_To_Address_ID,
H.PRSTADCD Header_Ship_To_Address_ID, 
H.ShipToName Header_Ship_To_Name,
H.ADDRESS1 Header_Address_1, H.ADDRESS2 Header_Address_2,
H.ADDRESS3 Header_Address_3, H.CITY Header_City,
H.[STATE] Header_State, H.ZIPCODE Header_Zip_Code,
H.COUNTRY Header_Country,
L.PRSTADCD Line_Ship_To_Address_ID, 
L.ShipToName Line_Ship_To_Name,
L.ADDRESS1 Line_Address_1, L.ADDRESS2 Line_Address_2,
L.ADDRESS3 Line_Address_3, L.CITY Line_City,
L.[STATE] Line_State, L.ZIPCODE Line_Zip_Code,
L.COUNTRY Line_Country,
H.DOCAMNT Total_Document_Amount, 
H.MRKDNAMT Total_Markdown_Amount,
H.SUBTOTAL Document_Subtotal,
H.FRTAMNT Freight_Amount, H.MISCAMNT Misc_Amount,
H.TAXAMNT Tax_Amount, H.CURNCYID Currency_ID,
H.ReqShipDate Header_ReqShipDate, 
L.ReqShipDate Line_ReqShipDate,
H.USER2ENT User_to_Enter,
H.COMMNTID Header_Comment_ID, L.COMMNTID Line_Comment_ID,
L.LNITMSEQ Line_Item_Sequence, L.CMPNTSEQ Component_Sequence,
CASE L.NONINVEN
  WHEN 0 THEN 'Inventory'
  WHEN 1 THEN 'Non-Inventory'
  ELSE ''
  END Item_Type,
L.ITEMNMBR Item_Number, L.ITEMDESC Item_Description,
L.QUANTITY Quantity, L.UOFM U_of_M, 
L.QTYBSUOM Qty_in_Base_U_of_M,
L.QTYREMAI Qty_Remaining, L.UNITPRCE Unit_Price,
L.XTNDPRCE Extended_Price,
CASE L.MRKDNTYP
  WHEN 0 THEN 'Percentage'
  WHEN 1 THEN 'Amount'
  ELSE ''
  END Markdown_Type,
L.MRKDNAMT Markdown_Amount, 
L.MRKDNPCT/100 Markdown_Percentage,
L.TRDISAMT Trade_Discount_Amount,
L.UNITCOST Unit_Cost, L.EXTDCOST Extended_Cost

FROM SOP30200 H
INNER JOIN SOP30300 L
   ON H.SOPTYPE = L.SOPTYPE 
      AND H.SOPNUMBE = L.SOPNUMBE) S --sop lines

LEFT OUTER JOIN
(SELECT p.SOPNUMBE, p.SOPTYPE, p.LNITMSEQ,
LEFT(p.serial_numbers, 
     len(p.serial_numbers)-1) Serial_Lot_Numbers
FROM
 (SELECT p1.SOPNUMBE, p1.SOPTYPE, p1.ITEMNMBR, p1.LNITMSEQ,
  (SELECT coalesce(rtrim(SERLTNUM) + ', ','')
   FROM SOP10201 p2
   WHERE p1.SOPNUMBE = p2.SOPNUMBE AND p1.SOPTYPE = p2.SOPTYPE
      AND p1.ITEMNMBR = p2.ITEMNMBR 
      AND p1.LNITMSEQ = p2.LNITMSEQ
   ORDER BY SOPNUMBE
   FOR XML PATH('')) serial_numbers
  FROM SOP10201 p1
 GROUP BY SOPNUMBE, SOPTYPE, ITEMNMBR, 
          LNITMSEQ) p) SN --serial/lot numbers
   ON SN.SOPNUMBE = S.SOP_Number 
      AND SN.SOPTYPE = S.SOP_Type 
      AND SN.LNITMSEQ = S.Line_Item_Sequence

LEFT OUTER JOIN SOP10202 C --line item comments
   ON C.SOPNUMBE = S.SOP_Number AND C.SOPTYPE = S.SOP_Type 
      AND C.LNITMSEQ = S.Line_Item_Sequence

LEFT OUTER JOIN SOP10106 HC --header comments
   ON HC.SOPNUMBE = S.SOP_Number AND HC.SOPTYPE = S.SOP_Type

--add permissions
GO
GRANT SELECT ON view_SOP_Line_Items_Serial 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.

11 Responses to “Dynamics GP SOP line items with serial numbers and comments”

  1. Victoria – First, THANK-YOU for all that you do to help the Great Plains community. Your posts have been helpful to me an my company on numerous occasions. Generally I can take your scripts and do enough manipulation to solve my own problems. However, we are currently trying to do something I can’t seem to figure out…

    Based on the way our company works, we don’t really have “inventory”. Instead, we post revenue by line item to the SOP module using integration files. I would ideally like to post multiple entries for each line item, or multiple line items with the same description (and different account numbers) and then be able to use report writer to roll these up by line item description for invoicing.

    Do you have any thoughts on the best way to approach this?

    Like this

    • Hi James,

      I don’t think there is any problem with importing multiple line items, each with a different account number. You didn’t mention what tool you’re using for importing, but that should be fairly straightforward. However, using the GP out-of-the-box Report Writer “to roll these up by line item description for invoicing” might be an entirely different matter. I am not sure that will be easily doable and you might find that you need to go to a different reporting tool, like Crystal Reports or SSRS to accomplish something like that without a herculean effort.

      -Victoria

      Like this

  2. Victoria,

    This view is great. Is there a way to add the Quantity of the Serial\Lot Item next to the Serial\Lot Item. An example that I am thinking of is (Lots) 1 – 11065, 5 – 11066, 10 – 12070. The number before the dash is the quantity of that particular lot for that line item.

    Like this

    • David,

      Great question. I didn’t want to change the view, as some people may not want this, but to accomplish this you can replace line 154 with the following code:

        (SELECT 
          case SERLTQTY when 0 then '' 
          else cast(cast(round(SERLTQTY,0) as int) as varchar) + ' - ' end +
          coalesce(rtrim(SERLTNUM) + ', ','')
      

      -Victoria

      Like this

  3. This is great. Thank you!

    Like this

    • You got it! Thank you for the good idea – it gave me a chance to pull some existing things together and also add comments, which I have had several requests for in the past. :-)

      -Victoria

      Like this

Trackbacks/Pingbacks

  1. Dynamics GP SOP line items with serial numbers and comments - Microsoft Dynamics GP DBA - GP Technical Blogs - Microsoft Dynamics Community - June 5, 2012

    [...] Comments 0 Victoria shows us how to  get the Dynamics GP SOP line items with serial numbers and comments [...]

    Like this

  2. Dynamics GP SOP line items with serial numbers and comments | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - June 5, 2012

    [...] Comments 0 Victoria Yudin has a new SQL view for Dynamics GP SOP line items with serial numbers and comments [...]

    Like this

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

    [...] the original post: Dynamics GP SOP line items with serial numbers and comments VN:F [1.9.17_1161]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F [...]

    Like this

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

    [...] Continue reading on Source Blog [...]

    Like this

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,358 other followers

%d bloggers like this: