SQL view for commissions details in Dynamics GP

Commissions are typically difficult to create generic reports for, as many companies have unique commission structures. We find that out-of-the-box functionality in GP does not really work for most companies and often the commissions are recorded in ways other than what was intended. That said, if you find that you are using the commissions functionality in any capacity at all, you may find yourself needing to report on the commissions data in the GP tables.

The view below returns all the commissions from the SOP (Sales Order Processing) module and also includes commissions from RM (Receivables Management) transactions that did not come from SOP. Both posted and unposted transactions are included, but I have added a column called Posted to show the posting status so you can easily exclude the unposted transactions if you need to. More notes are found in the comments in the view. This code was tested on a few different sets of data, however, it may be that this will not work for your specific data – before using this to base commissions on, please test the results.

Related code and table information:

• Receivables Management tables
• Sales Order Processing tables
• Receivables SQL views
• SOP SQL views

CREATE VIEW view_Commissions_Details
AS

--***********************************************************************************
-- view_Commissions_Details
-- Created July 19, 2012 by Victoria Yudin - Flexible Solutions, Inc.
-- For updates see http://victoriayudin.com/gp-reports/
-- Returns all commissions details for SOP transactions (invoices and returns only)
--     and also adds in non-SOP transactions commissions from RM tables
-- Only shows functional amounts
-- Excludes voided transactions
--***********************************************************************************

select CUSTNMBR Customer_ID, DOCDATE Document_Date, GLPOSTDT GL_Posting_Date, Data_Source,
case RMDTYPAL 
 when 1 then 'Invoice'
 when 3 then 'Debit Memo'
 when 5 then 'Service / Repair'
 when 7 then 'Credit Memo'
 when 8 then 'Return'
 end Document_Type,
DOCNUMBR Document_Number, SEQNUMBR Sequence_Number,
TRXSORCE Trx_Source, SLPRSNID Salesperson, SALSTERR Territory, 
case when RMDTYPAL < 7 then Sales_Amount else Sales_Amount*-1 end Sales_Amount, 
case when RMDTYPAL < 7 then Non_Comm_Amount else Non_Comm_Amount*-1 end Non_Comm_Amount,
cast(PRCTOSAL as numeric)/100 Percent_of_Sale, 
case when RMDTYPAL < 7 then Commission_Amount else Commission_Amount*-1 end Commission_Amount,
cast(COMPRCNT as numeric)/100 Commission_Percent, 
case POSTED when 0 then 'No' else 'Yes' end Posted
 
from

(select r.CUSTNMBR, r.DOCDATE, r.GLPOSTDT, c.*
from
(select 
 Data_Source = 'RM History', RMDTYPAL, DOCNUMBR, SEQNUMBR, TRXSORCE, SLPRSNID, 
 SALSTERR, SLSAMNT Sales_Amount, NCOMAMNT Non_Comm_Amount, PRCTOSAL, 
 COMDLRAM Commission_Amount, COMPRCNT, POSTED
 from RM30501 -- RM Commission History
 where left(TRXSORCE,5) <> 'SLSTE'
  union all
 select 
 Data_Source = 'RM Work', RMDTYPAL, DOCNUMBR, SEQNUMBR, TRXSORCE, SLPRSNID, 
 SALSTERR, SLSAMNT Sales_Amount, NCOMAMNT Non_Comm_Amount, PRCTOSAL, 
 COMDLRAM Commission_Amount, COMPRCNT, POSTED
 from rm10501 -- RM Commission Work 
 where left(TRXSORCE,5) <> 'SLSTE') c -- RM commissions

inner join 
(select RMDTYPAL, DOCNUMBR, DOCDATE, GLPOSTDT, CUSTNMBR 
 from RM10301 --RM Work
  union 
 select RMDTYPAL, DOCNUMBR, DOCDATE, GLPOSTDT, CUSTNMBR 
 from RM20101 --RM Open
 where VOIDSTTS = 0
  union 
 select RMDTYPAL, DOCNUMBR, DOCDATE, GLPOSTDT, CUSTNMBR 
 from RM30101 --RM History
 where VOIDSTTS = 0) r --all RM transactions
 on r.RMDTYPAL = c.RMDTYPAL and r.DOCNUMBR = c.DOCNUMBR

union all
select
s.CUSTNMBR, s.DOCDATE, s.GLPOSTDT, Data_Source = 'SOP',  
case c.SOPTYPE when 3 then 1 else 8 end RMDTYPAL,
c.SOPNUMBE DOCNUMBR, c.SEQNUMBR, c.TRXSORCE, c.SLPRSNID, 
c.SALSTERR, c.ACTSLAMT Sales_Amount, c.NCOMAMNT Non_Comm_Amount, 
c.PRCTOSAL, c.COMMAMNT Commission_Amount, c.COMPRCNT, 
case c.TRXSORCE when '' then 0 else 1 end POSTED
from SOP10101 c --SOP commissions (work and history)

inner join 
(select SOPTYPE, SOPNUMBE, DOCDATE, GLPOSTDT, CUSTNMBR
 from SOP10100 
 where SOPTYPE in (3,4) and VOIDSTTS = 0
  union 
 select SOPTYPE, SOPNUMBE, DOCDATE, GLPOSTDT, CUSTNMBR
 from SOP30200
 where SOPTYPE in (3,4) and VOIDSTTS = 0) s --SOP transactions
 on s.SOPTYPE = c.SOPTYPE and s.SOPNUMBE = c.SOPNUMBE
where c.SOPTYPE in (3,4)) a --all data

GO
GRANT SELECT ON view_Commissions_Details to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code, SOP SQL code Tagged: featured, GP Reports code, Receivables, Sales Order Processing, SQL code

Continue Reading:
SQL view for commissions details in Dynamics GP

Tags: dwight-specht, Dynamics GP, extender, featured, fixed-assets, knowledge, microsoft, upgrade

Category: Blogs I Follow