money

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:


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.

7 Responses to “SQL view for commissions details in Dynamics GP”

  1. Hi Victoria,
    I, too, am extremely grateful for everything you publish. I frequently rely on your articles, queries, and tips. I found what I believe to be an issue with the query. The credit memos are not included in the RM10501 or RM30501 tables since they aren’t used for commissions calculations. They are retrieved from the RM transaction tables, but then dropped by the inner join with the commission tables. If/when I come up with an alternate query, I will post that.
    Thanks again!
    Courtney

    Like

  2. Victoria – I am testing this at a customer’s site, and I’ve discovered something I don’t understand. My issue is with a SOP document that has been posted. It looks like the view pulls SOP10101.ACTSLSAMT for this document as the sales_amount in the view. My sales document has two commissions rows, split 50/50 between two salespeople/territories. In SOP10101, actslamt and orslsamt are both 0.00 for ONE line of this document (the second commission). The cmmslamt and orcosamt fields are both populated correctly with the commission sales amount. Can you provide any insight into why you are using the actslamt field instead of the commslamt field for SOP documents in the view?

    Thank you for all that you do/provide. I visit your site just about every day.

    Like

    • I did a bit more testing on this. If I have a sales order with one line item at $10, that line item can only be assigned to ONE sales person. That drives the value of the sop10101.actslamt field – $10. That gets assigned to the row in SOP10101 that is for the salesperson assigned to the line item in Sales Item Detail Entry. Any other split commissions in Sales Commission Entry will have 0.00 for Line Item Sale Amount (sop10101.actslamt).

      Like

      • Hi Sherry,

        Thanks for the update. That’s right, even though you can enter the split commission (you have to trick GP into it, right?) it’s not stored the same way. Do you think I should change how this is shown?

        Thanks,
        -Victoria

        Like

Trackbacks/Pingbacks

  1. SQL view for commissions details in Dynamics GP | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - July 23, 2012

    […] Comments 0 Victoria Yudin shows off a SQL view for commissions details in Dynamics GP […]

    Like

  2. SQL view for commissions details in Dynamics GP – 7/19, Victoria Yudin | Partner Compete - July 19, 2012

    […] Continue reading on Source Blog […]

    Like

  3. SQL view for commissions details in Dynamics GP | Interesting Findings & Knowledge Sharing - July 19, 2012

    […] Reading: SQL view for commissions details in Dynamics GP VN:F [1.9.18_1163]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,871 other followers

%d bloggers like this: