Dynamics GP - SQL View - Sales Header and Detail, Work and History

--------------------------------------
--Sales Open
-------------------------------------

SELECT     TOP (100) PERCENT dbo.SOP10100.SOPNUMBE AS [SOP Number], dbo.SOP10100.DOCDATE AS [Document Date], dbo.SOP10100.GLPOSTDT AS [GL Post Date],
                  CASE WHEN SOP10100.soptype = 4 THEN SOP10200.XTNDPRCE * - 1 ELSE SOP10200.xtndprce END AS Sales, CASE WHEN SOP10100.soptype = 4 THEN SOP10200.EXTDCOST * - 1 ELSE SOP10200.extdcost END AS Costs,
                  CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END AS Qty, dbo.SOP10100.CUSTNMBR AS [Customer Number],
                  CASE WHEN SOP10100.soptype = 1 THEN 'Quote' WHEN SOP10100.soptype = 2 THEN 'Order' WHEN SOP10100.soptype = 3 THEN 'Invoice' WHEN SOP10100.soptype = 4 THEN 'Return' WHEN SOP10100.soptype = 5 THEN 'BackOrder' WHEN SOP10100.soptype = 6
                   THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code], dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class],
                  dbo.SOP10200.QTYBSUOM AS [Base Unit Of Measure], (CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END) * dbo.SOP10200.QTYBSUOM AS [Units Sold], dbo.SOP10200.STATE, dbo.SOP10200.PRSTADCD,
                  dbo.SOP10100.SLPRSNID AS [Salesperson ID], dbo.IV00101.ITEMNMBR AS [Item Number], dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP10100.ShipToName, dbo.RM00101.COMMENT1, dbo.RM00101.SLPRSNID AS Customer_Slprsnid,
                  CASE WHEN sop10100.soptype = 4 THEN sop10200.trdisamt * - 1 ELSE sop10200.trdisamt END AS TrDisamt, CASE WHEN sop10100.soptype = 4 THEN sop10200.mrkdnamt * - 1 ELSE sop10200.mrkdnamt END AS Mrkdnamt,
                  CASE WHEN sop10100.soptype = 4 THEN sop10100.trdisamt * - 1 ELSE sop10100.trdisamt END AS DocDiscount
FROM        dbo.SOP10100 INNER JOIN
                  dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE INNER JOIN
                  dbo.IV00101 ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                  dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD INNER JOIN
                  dbo.RM00101 ON dbo.SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR
WHERE     (dbo.SOP10100.VOIDSTTS = 0) AND (dbo.SOP10200.CMPNTSEQ = 0) AND (dbo.SOP10100.SOPTYPE IN (3, 4))

--------------------------------------
--Sales History
-------------------------------------
SELECT     TOP (100) PERCENT dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date], dbo.SOP30200.GLPOSTDT AS [GL Post Date],
                  CASE WHEN sop30200.soptype = 4 THEN sop30300.XTNDPRCE * - 1 ELSE sop30300.xtndprce END AS Sales, CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs,
                  CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty, dbo.SOP30200.CUSTNMBR AS [Customer Number],
                  CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype = 4 THEN 'Return' WHEN sop30200.soptype = 5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN
                   'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code], dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class],
                  dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure], (CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold], dbo.SOP30300.STATE, dbo.SOP30300.PRSTADCD,
                  dbo.SOP30200.SLPRSNID AS [Salesperson ID], dbo.IV00101.ITEMNMBR AS [Item Number], dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName, dbo.RM00101.COMMENT1, dbo.RM00101.SLPRSNID AS Customer_Slprsnid,
                  CASE WHEN sop30200.soptype = 4 THEN sop30300.trdisamt * - 1 ELSE sop30300.trdisamt END AS Trdisamt, CASE WHEN sop30200.soptype = 4 THEN sop30300.mrkdnamt * - 1 ELSE sop30300.mrkdnamt END AS Mrkdnamt,
                  CASE WHEN sop30200.soptype = 4 THEN sop30200.trdisamt * - 1 ELSE sop30200.trdisamt END AS DocDiscount
FROM        dbo.SOP30200 INNER JOIN
                  dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE INNER JOIN
                  dbo.IV00101 ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                  dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD INNER JOIN
                  dbo.RM00101 ON dbo.SOP30200.CUSTNMBR = dbo.RM00101.CUSTNMBR
WHERE     (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30300.CMPNTSEQ = 0) AND (dbo.SOP30200.SOPTYPE IN (3, 4))