Dynamics GP Views - Cube Views

Historical Sales Line Items - Includes all SOP types, ignores nulls and kit components, returns are negatives, sales line item salesperson and territory details



SELECT dbo.SOP30200.SOPNUMBE, dbo.SOP30200.SOPTYPE, dbo.SOP30200.DOCDATE, dbo.SOP30200.GLPOSTDT, dbo.SOP30300.ITEMNMBR, dbo.SOP30300.UOFM,
                  dbo.SOP30300.LOCNCODE, CASE WHEN sop30200.soptype = 4 THEN sop30300.XTNDPRCE * - 1 ELSE sop30300.xtndprce END AS ExtSls,
                  CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS ExtCost,
                  CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty, dbo.SOP30300.DEX_ROW_ID, dbo.SOP30200.VOIDSTTS,
                  dbo.SOP30300.CMPNTSEQ, dbo.SOP30300.LNITMSEQ, dbo.SOP30200.CUSTNMBR,
                  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 SOPTYPEDESC, dbo.RM00301.SLPRSNID AS SOPSlsprsnid,
                  dbo.RM00301.SLPRSNFN, dbo.RM00301.SPRSNSMN, dbo.RM00301.SPRSNSLN, dbo.RM00303.SALSTERR AS SOPSlsTerr, dbo.RM00303.SLTERDSC
FROM     dbo.SOP30200 INNER JOIN
                  dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE INNER JOIN
                  dbo.RM00301 ON dbo.SOP30300.SLPRSNID = dbo.RM00301.SLPRSNID INNER JOIN
                  dbo.RM00303 ON dbo.SOP30200.SALSTERR = dbo.RM00303.SALSTERR
WHERE  (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30300.CMPNTSEQ = 0)

---

Item Master - Including all Non-inventory Sales Line Items, creates a flag for Noninventory items



SELECT AllInv.ITEMNMBR, AllInv.NonInven, IV00101_1.ITEMDESC, IV00101_1.CURRCOST, IV00101_1.ITEMSHWT, IV00101_1.USCATVLS_1, IV00101_1.USCATVLS_2,
                  IV00101_1.USCATVLS_3, IV00101_1.USCATVLS_4, IV00101_1.USCATVLS_5, IV00101_1.USCATVLS_6, IV00101_1.ITEMTYPE, IV00101_1.ITMGEDSC, IV00101_1.ITMCLSCD,
                  dbo.IV40400.ITMCLSDC, RTRIM(IV00101_1.ITEMNMBR) + '-' + IV00101_1.ITEMDESC AS ItemNumAndName, RTRIM(IV00101_1.ITMCLSCD)
                  + '-' + dbo.IV40400.ITMCLSDC AS ItemClassNumAndDesc
FROM     dbo.IV00101 AS IV00101_1 INNER JOIN
                  dbo.IV40400 ON IV00101_1.ITMCLSCD = dbo.IV40400.ITMCLSCD RIGHT OUTER JOIN
                      (SELECT ITEMNMBR, 0 AS NonInven
                       FROM      dbo.IV00101
                       UNION ALL
                       SELECT ITEMNMBR, NONINVEN
                       FROM     dbo.SOP30300) AS AllInv ON IV00101_1.ITEMNMBR = AllInv.ITEMNMBR

---

Customer Master

SELECT dbo.RM00101.CUSTNMBR, dbo.RM00101.CUSTNAME, dbo.RM00201.CLASDSCR, dbo.RM00101.PYMTRMID, dbo.RM00101.COMMENT1, dbo.RM00101.COMMENT2,
                  dbo.RM00101.USERDEF1, dbo.RM00101.USERDEF2, RTRIM(dbo.RM00101.CUSTNMBR) + '-' + dbo.RM00101.CUSTNAME AS CustNmbrAndName, RTRIM(dbo.RM00201.CLASSID)
                  + '-' + dbo.RM00201.CLASDSCR AS ClassNumberAndName, dbo.RM00101.SLPRSNID, dbo.RM00101.CUSTCLAS
FROM     dbo.RM00101 INNER JOIN
                  dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID