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:

Links for additional SOP and RM table information and scripts:

• Sales Order Processing (SOP) commonly used tables
• Sales Order Processing (SOP) SQL views
• Receivables Management (RM) commonly used tables
• Receivables Management (RM) SQL views
• Other GP Reporting links

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