Dynamics GP - SQL View - SOP Sales History

CREATE VIEW [dbo].[BI_Voided]
AS
SELECT     TOP (100) PERCENT rm.CUSTNMBR AS Customer_ID, rm.DOCDATE AS Doc_Date, rm.DOCNUMBR AS Doc_Number,
                      CASE s.SOPTYPE WHEN 3 THEN 'Invoice' ELSE 'Return' END AS Doc_Type, CASE rm.VOIDSTTS WHEN 0 THEN 'No' ELSE 'Yes' END AS Voided_in_RM,
                      CASE s.VOIDSTTS WHEN 0 THEN 'No' ELSE 'Yes' END AS Voided_in_SOP
FROM         (SELECT     CUSTNMBR, DOCDATE, DOCNUMBR, CASE RMDTYPAL WHEN 1 THEN 3 ELSE 4 END AS SOPTYPE, VOIDSTTS
                       FROM          dbo.RM20101
                       WHERE      (RMDTYPAL IN (1, 8))
                       UNION
                       SELECT     CUSTNMBR, DOCDATE, DOCNUMBR, CASE RMDTYPAL WHEN 1 THEN 3 ELSE 4 END AS SOPTYPE, VOIDSTTS
                       FROM         dbo.RM30101
                       WHERE     (RMDTYPAL IN (1, 8))) AS rm INNER JOIN
                      dbo.SOP30200 AS s ON rm.SOPTYPE = s.SOPTYPE AND rm.DOCNUMBR = s.SOPNUMBE AND rm.VOIDSTTS <> s.VOIDSTTS
ORDER BY Doc_Date DESC

GO



CREATE VIEW [dbo].[BI_Cust_First_Sale]
AS
SELECT     CUSTNMBR, MIN(DOCDATE) AS FirstSalesDate
FROM         dbo.SOP30200
GROUP BY CUSTNMBR
GO

CREATE VIEW [dbo].[BI_Sales_History0]
AS
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 - SOP30300.TRDISAMT) * - 1 ELSE (sop30300.xtndprce - SOP30300.TRDISAMT) 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, ISNULL(dbo.SOP30200.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2,
                      dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number], dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName,
                      dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP30300.UNITCOST, dbo.RM00101.CREATDDT, dbo.BI_Cust_First_Sale.FirstSalesDate,
                      LEFT(dbo.SOP30300.ITEMNMBR, 2) AS AB, dbo.SOP30300.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department],
                      dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name], YEAR(dbo.SOP30200.DOCDATE) AS DocYear,
                      RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP30200.DOCDATE) AS VARCHAR)
                      + '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP30200.CUSTNMBR)
                      + ' | ' + RTRIM(dbo.SOP30200.CUSTNAME) AS CusIdName, RTRIM(dbo.SOP30300.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP30300.ITEMDESC) AS ItemIdName,
                      dbo.SOP30300.UOFM, CASE LEFT(dbo.SOP30200.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
                      WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP30200.CUSTNMBR, 1)
                      END AS Div, ISNULL(dbo.RM00301.SALSTERR, '(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm,
                      LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT, DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge,
                      dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
                      CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, dbo.BI_Voided.Doc_Number
FROM         dbo.RM00101 INNER JOIN
                      dbo.SOP30200 INNER JOIN
                      dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE ON
                      dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR LEFT OUTER JOIN
                      dbo.IV40400 INNER JOIN
                      dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                      dbo.RM00301 ON dbo.SOP30200.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP30300.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
                      dbo.BI_Voided ON dbo.SOP30200.SOPNUMBE = dbo.BI_Voided.Doc_Number LEFT OUTER JOIN
                      dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
                      dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID RIGHT OUTER JOIN
                      dbo.BI_Cust_First_Sale ON dbo.RM00101.CUSTNMBR = dbo.BI_Cust_First_Sale.CUSTNMBR
WHERE     (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE IN (3, 4)) AND (dbo.SOP30300.CMPNTSEQ = 0)
ORDER BY [GL Post Date] DESC

GO