SQL View for Sales Margin Analysis

Guys

The next post that I have decided to make is on a SQL view for generating a sales margin analysis report in Dynamics GP.

 
CREATE   VIEW dbo.vw_SOPSalesAnalysis
AS  SELECT  CASE dbo.SOP10200.SOPTYPE
              WHEN 1 THEN 'Quote'
              WHEN 2 THEN 'Order'
              WHEN 3 THEN 'Invoice'
              WHEN 4 THEN 'Returns'
              WHEN 5 THEN 'Back Order'
              WHEN 6 THEN 'Fulfillment Order'
            END AS TYPE,
            dbo.SOP10100.SOPNUMBE AS DOCUMENTNO,
            dbo.SOP10100.DOCID AS TYPEID,
            dbo.SOP10100.DOCDATE AS DOCUMENTDATE,
            dbo.SOP10100.BACHNUMB AS BATCHNUMBER,
            dbo.SOP10100.CUSTNMBR AS CUSTOMERID,
            dbo.SOP10100.CUSTNAME AS CUSTOMERNAME,
            dbo.SOP10100.CURNCYID AS CURRENCY,
            dbo.SOP10200.ITEMNMBR AS ITEMNO,
            dbo.SOP10200.ITEMDESC AS ITEMNAME,
            dbo.SOP10200.UOFM AS UOM,
            dbo.SOP10200.LOCNCODE AS LOCATIONID,
            dbo.IV40700.LOCNDSCR AS LOCATIONNAME,
            dbo.SOP10200.UNITCOST,
            dbo.SOP10200.UNITPRCE AS UNITPRICE,
            CASE dbo.SOP10100.SOPTYPE
              WHEN 4 THEN ( dbo.SOP10200.XTNDPRCE * -1 )
              ELSE dbo.SOP10200.XTNDPRCE
            END AS TOTALPRICE,
            CASE dbo.SOP10100.SOPTYPE
              WHEN 4 THEN ( dbo.SOP10200.EXTDCOST * -1 )
              ELSE dbo.SOP10200.EXTDCOST
            END AS TOTALCOST,
            dbo.SOP10200.SALSTERR AS TERRITORYID,
            dbo.RM00303.SLTERDSC AS TERRITORYNAME,
            dbo.SOP10200.SLPRSNID AS SALESPERSONID,
            dbo.RM00301.EMPLOYID AS EMPLOYEEID,
            ( LTRIM(RTRIM(dbo.UPR00100.FRSTNAME)) + ' '
              + LTRIM(RTRIM(dbo.UPR00100.MIDLNAME)) + ' '
              + LTRIM(RTRIM(dbo.UPR00100.LASTNAME)) ) AS EMPLOYEENAME,
            ( LTRIM(RTRIM(dbo.RM00301.SLPRSNFN)) + ' '
              + LTRIM(RTRIM(dbo.RM00301.SPRSNSMN)) + ' '
              + LTRIM(RTRIM(dbo.RM00301.SPRSNSLN)) ) AS SALESPERSONNAME,
            dbo.SOP10200.PRCLEVEL AS PRIELEVEL,
            dbo.RM00201.CLASDSCR AS CUSTOMERCLASSNAME,
            dbo.RM00101.CUSTCLAS AS CUSTOMERCLASSID,
            dbo.RM00101.COUNTRY AS COUNTRY,
            dbo.IV00101.ITMCLSCD AS ITEMCLASSID,
            dbo.IV40400.ITMCLSDC AS ITEMCLASSNAME,
            dbo.IV00101.USCATVLS_1 AS ITEMUDF1,
            dbo.IV00101.USCATVLS_2 AS ITEMUDF2,
            dbo.IV00101.USCATVLS_3 AS ITEMUDF3,
            dbo.IV00101.USCATVLS_4 AS ITEMUDF4,
            dbo.IV00101.USCATVLS_5 AS ITEMUDF5,
            dbo.IV00101.USCATVLS_6 AS ITEMUDF6,
            CASE MONTH(dbo.SOP10100.DOCDATE)
              WHEN 1
              THEN 'JAN - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 2
              THEN 'FEB - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 3
              THEN 'MAR - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 4
              THEN 'APR - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 5
              THEN 'MAY - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 6
              THEN 'JUN - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 7
              THEN 'JUL - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 8
              THEN 'AUG - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 9
              THEN 'SEP - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 10
              THEN 'OCT - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 11
              THEN 'NOV - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
              WHEN 12
              THEN 'DEC - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP10100.DOCDATE))))
            END AS DOCPERIOD,
            YEAR(dbo.SOP10100.DOCDATE) AS DOCYEAR,
            CASE MONTH(dbo.SOP10100.DOCDATE)
              WHEN 1 THEN 'JANUARY'
              WHEN 2 THEN 'FEBRUARY'
              WHEN 3 THEN 'MARCH'
              WHEN 4 THEN 'APRIL'
              WHEN 5 THEN 'MAY'
              WHEN 6 THEN 'JUNE'
              WHEN 7 THEN 'JULY'
              WHEN 8 THEN 'AUGUST'
              WHEN 9 THEN 'SEPTEMBER'
              WHEN 10 THEN 'OCTOBER'
              WHEN 11 THEN 'NOVEMBER'
              WHEN 12 THEN 'DECEMBER'
            END AS DOCMONTH,
            'Open' AS STATUS,
            CASE dbo.SOP10100.SOPTYPE
              WHEN 4
              THEN ( ( dbo.SOP10200.XTNDPRCE * -1 ) - ( dbo.SOP10200.EXTDCOST
                                                        * -1 ) )
              ELSE ( dbo.SOP10200.XTNDPRCE - dbo.SOP10200.EXTDCOST )
            END AS MARGIN,
            CASE WHEN dbo.SOP10100.SOPTYPE = 4
                      AND dbo.SOP10200.EXTDCOST > 0
                 THEN ( ( ( ( dbo.SOP10200.XTNDPRCE * -1 )
                            - ( dbo.SOP10200.EXTDCOST * -1 ) )
                          / ( dbo.SOP10200.EXTDCOST * -1 ) ) - 1 )
                 WHEN dbo.SOP10100.SOPTYPE = 4
                      AND dbo.SOP10200.EXTDCOST = 0 THEN -1
                 WHEN dbo.SOP10100.SOPTYPE <> 4
                      AND dbo.SOP10200.EXTDCOST > 0
                 THEN ( ( dbo.SOP10200.XTNDPRCE - dbo.SOP10200.EXTDCOST )
                        / ( dbo.SOP10200.EXTDCOST ) )
                 ELSE 1
            END AS MARGINPERCENT,
            LNITMSEQ AS SALESLINENO
    FROM    dbo.SOP10100
            INNER JOIN dbo.SOP10200
                ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE
                   AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE
            INNER JOIN dbo.RM00101
                ON dbo.RM00101.CUSTNMBR = dbo.SOP10100.CUSTNMBR
            INNER JOIN dbo.IV40700
                ON dbo.IV40700.LOCNCODE = dbo.SOP10200.LOCNCODE
            LEFT OUTER JOIN dbo.RM00303
                ON dbo.SOP10200.SALSTERR = dbo.RM00303.SALSTERR
            LEFT OUTER JOIN dbo.IV00101
                ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR
            LEFT OUTER JOIN dbo.IV40400
                ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD
            LEFT OUTER JOIN dbo.RM00301
                ON dbo.SOP10200.SLPRSNID = dbo.RM00301.SLPRSNID
            LEFT OUTER JOIN dbo.UPR00100
                ON dbo.RM00301.EMPLOYID = dbo.UPR00100.EMPLOYID
            LEFT OUTER JOIN dbo.RM00201
                ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
    UNION
    SELECT  CASE dbo.SOP30300.SOPTYPE
              WHEN 1 THEN 'Quote'
              WHEN 2 THEN 'Order'
              WHEN 3 THEN 'Invoice'
              WHEN 4 THEN 'Returns'
              WHEN 5 THEN 'Back Order'
              WHEN 6 THEN 'Fulfillment Order'
            END AS TYPE,
            dbo.SOP30200.SOPNUMBE AS DOCUMENTNO,
            dbo.SOP30200.DOCID AS TYPEID,
            dbo.SOP30200.DOCDATE AS DOCUMENTDATE,
            dbo.SOP30200.BACHNUMB AS BATCHNUMBER,
            dbo.SOP30200.CUSTNMBR AS CUSTOMERID,
            dbo.SOP30200.CUSTNAME AS CUSTOMERNAME,
            dbo.SOP30200.CURNCYID AS CURRENCY,
            dbo.SOP30300.ITEMNMBR AS ITEMNO,
            dbo.SOP30300.ITEMDESC AS ITEMNAME,
            dbo.SOP30300.UOFM AS UOM,
            dbo.SOP30300.LOCNCODE AS LOCATIONID,
            dbo.IV40700.LOCNDSCR AS LOCATIONNAME,
            dbo.SOP30300.UNITCOST,
            dbo.SOP30300.UNITPRCE AS UNITPRICE,
            CASE dbo.SOP30200.SOPTYPE
              WHEN 4 THEN ( dbo.SOP30300.XTNDPRCE * -1 )
              ELSE dbo.SOP30300.XTNDPRCE
            END AS TOTALPRICE,
            CASE dbo.SOP30200.SOPTYPE
              WHEN 4 THEN ( dbo.SOP30300.EXTDCOST * -1 )
              ELSE dbo.SOP30300.EXTDCOST
            END AS TOTALCOST,
            dbo.SOP30300.SALSTERR AS TERRITORYID,
            dbo.RM00303.SLTERDSC AS TERRITORYNAME,
            dbo.SOP30300.SLPRSNID AS SALESPERSONID,
            dbo.RM00301.EMPLOYID AS EMPLOYEEID,
            ( LTRIM(RTRIM(dbo.UPR00100.FRSTNAME)) + ' '
              + LTRIM(RTRIM(dbo.UPR00100.MIDLNAME)) + ' '
              + LTRIM(RTRIM(dbo.UPR00100.LASTNAME)) ) AS EMPLOYEENAME,
            ( LTRIM(RTRIM(dbo.RM00301.SLPRSNFN)) + ' '
              + LTRIM(RTRIM(dbo.RM00301.SPRSNSMN)) + ' '
              + LTRIM(RTRIM(dbo.RM00301.SPRSNSLN)) ) AS SALESPERSONNAME,
            dbo.SOP30300.PRCLEVEL AS PRIELEVEL,
            dbo.RM00201.CLASDSCR AS CUSTOMERCLASSNAME,
            dbo.RM00101.CUSTCLAS AS CUSTOMERCLASSID,
            dbo.RM00101.COUNTRY AS COUNTRY,
            dbo.IV00101.ITMCLSCD AS ITEMCLASSID,
            dbo.IV40400.ITMCLSDC AS ITEMCLASSNAME,
            dbo.IV00101.USCATVLS_1 AS ITEMUDF1,
            dbo.IV00101.USCATVLS_2 AS ITEMUDF2,
            dbo.IV00101.USCATVLS_3 AS ITEMUDF3,
            dbo.IV00101.USCATVLS_4 AS ITEMUDF4,
            dbo.IV00101.USCATVLS_5 AS ITEMUDF5,
            dbo.IV00101.USCATVLS_6 AS ITEMUDF6,
            CASE MONTH(dbo.SOP30200.DOCDATE)
              WHEN 1
              THEN 'JAN - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 2
              THEN 'FEB - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 3
              THEN 'MAR - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 4
              THEN 'APR - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 5
              THEN 'MAY - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 6
              THEN 'JUN - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 7
              THEN 'JUL - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 8
              THEN 'AUG - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 9
              THEN 'SEP - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 10
              THEN 'OCT - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 11
              THEN 'NOV - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
              WHEN 12
              THEN 'DEC - ' + LTRIM(RTRIM(STR(YEAR(dbo.SOP30200.DOCDATE))))
            END AS DOCPERIOD,
            YEAR(dbo.SOP30200.DOCDATE) AS DOCYEAR,
            CASE MONTH(dbo.SOP30200.DOCDATE)
              WHEN 1 THEN 'JANUARY'
              WHEN 2 THEN 'FEBRUARY'
              WHEN 3 THEN 'MARCH'
              WHEN 4 THEN 'APRIL'
              WHEN 5 THEN 'MAY'
              WHEN 6 THEN 'JUNE'
              WHEN 7 THEN 'JULY'
              WHEN 8 THEN 'AUGUST'
              WHEN 9 THEN 'SEPTEMBER'
              WHEN 10 THEN 'OCTOBER'
              WHEN 11 THEN 'NOVEMBER'
              WHEN 12 THEN 'DECEMBER'
            END AS DOCMONTH,
            'History' AS STATUS,
            CASE dbo.SOP30200.SOPTYPE
              WHEN 4
              THEN ( ( dbo.SOP30300.XTNDPRCE * -1 ) - ( dbo.SOP30300.EXTDCOST
                                                        * -1 ) )
              ELSE ( dbo.SOP30300.XTNDPRCE - dbo.SOP30300.EXTDCOST )
            END AS MARGIN,
            CASE WHEN dbo.SOP30200.SOPTYPE = 4
                      AND dbo.SOP30300.EXTDCOST > 0
                 THEN ( ( ( dbo.SOP30300.XTNDPRCE - dbo.SOP30300.EXTDCOST )
                          / ( dbo.SOP30300.EXTDCOST ) ) - 1 )
                 WHEN dbo.SOP30200.SOPTYPE = 4
                      AND dbo.SOP30300.EXTDCOST = 0 THEN -1
                 WHEN dbo.SOP30200.SOPTYPE <> 4
                      AND dbo.SOP30300.EXTDCOST > 0
                 THEN ( ( dbo.SOP30300.XTNDPRCE - dbo.SOP30300.EXTDCOST )
                        / ( dbo.SOP30300.EXTDCOST ) )
                 ELSE 1
            END AS MARGINPERCENT,
            LNITMSEQ AS SALESLINENO
    FROM    dbo.SOP30200
            INNER JOIN dbo.SOP30300
                ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE
                   AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE
            INNER JOIN dbo.RM00101
                ON dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR
            INNER JOIN dbo.IV40700
                ON dbo.IV40700.LOCNCODE = dbo.SOP30300.LOCNCODE
            LEFT OUTER JOIN dbo.RM00303
                ON dbo.SOP30300.SALSTERR = dbo.RM00303.SALSTERR
            LEFT OUTER JOIN dbo.IV00101
                ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
            LEFT OUTER JOIN dbo.IV40400
                ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD
            LEFT OUTER JOIN dbo.RM00301
                ON dbo.SOP30300.SLPRSNID = dbo.RM00301.SLPRSNID
            LEFT OUTER JOIN dbo.UPR00100
                ON dbo.RM00301.EMPLOYID = dbo.UPR00100.EMPLOYID
            LEFT OUTER JOIN dbo.RM00201
                ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID

Until next post, happy scripting…

Siva

VN:F [1.9.13_1145]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.13_1145]
Rating: 0 (from 0 votes)

May 2, 2009 В· veeyeskay В· 2 Comments
Posted in: Dynamics, Great Plains, Sales Order Processing Total Views: 824

  • veeyeskay

    @Jason

    Can u be more elaborate on what your requirement is?

    VA:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.13_1145]
    Rating: 0 (from 0 votes)
  • Jason

    Hello there, any tips on hitting non-company databases without creating every GP user in that database?

    VA:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.13_1145]
    Rating: 0 (from 0 votes)