SQL view with all SOP line items

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
*******************************************************************/

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

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

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.