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.
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