cooking

Identify what modules your Dynamics GP sales data originates in


I have posted a lot of queries for getting sales totals from the Sales Order Processing module. However, many companies also enter transactions directly in the RM (Receivables Management) module. In that case, if you need to see the data from both of these sources, you may need to take a slightly different approach to your reporting. To identify where your sales data is coming from, you can use this script. It will show you the total number for each type of transaction broken out by source and year.

Sample results:

doc types

Links for additional SOP and RM table information and scripts:


select year(DOCDATE) [Doc Year],
case trxsrc
	when 'SL' then 'SOP'
	else 'RM'
	end [Trx Source],
case RMDTYPAL
	when 1 then 'Invoice'
	when 2 then 'Scheduled Payments'
	when 3 then 'Debit Memo'
	when 4 then 'Finance Charge'
	when 5 then 'Service/Repair'
	when 6 then 'Warranty'
	when 7 then 'Credit Memo'
	when 8 then 'Return'
	when 9 then 'Payment'
	else 'Other'
	end [Doc Type],
COUNT(*) [Total Trx] 

from
(select left(TRXSORCE,2) trxsrc, DOCDATE, RMDTYPAL
 from RM20101 where VOIDSTTS = 0
 union all
 select left(TRXSORCE,2) trxsrc, DOCDATE, RMDTYPAL
 from RM30101 where VOIDSTTS = 0) a

group by YEAR(DOCDATE), RMDTYPAL, trxsrc

order by YEAR(DOCDATE) desc, RMDTYPAL, trxsrc

One Response to “Identify what modules your Dynamics GP sales data originates in”

  1. This is awesome – thanks for sharing!

    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,556 other followers

%d bloggers like this: