June 4, 2012 
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 https://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.
Victoria Yudin 
This is Great View Victoria, Thank you so much. If I want to view only sales with GL Posted Dated between Jan 1, 2015 and March 31, 2015 where can I add the where clause
LikeLike
Hi George,
You can do a few different things:
Hope that helps,
-Victoria
LikeLike
Victoria, can I add Drop Ship field to this view?
LikeLike
Hi Diana,
You can add the following after line 86 and line 158:
,L.DROPSHIP Drop_ShipThat should do it.
-Victoria
LikeLike
Thank you so much.
Thank you,
Diana Feile, CPA
LikeLike
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?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Victoria,
Thanks, works great!!!!!!’
David
LikeLike
This is great. Thank you!
LikeLike
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
LikeLike