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
Loading ...
Hello there, any tips on hitting non-company databases without creating every GP user in that database?
@Jason
Can u be more elaborate on what your requirement is?