SQL script for comparing voids between RM and SOP in Dynamics GP

How do we find transactions that were voided in the Receivables Management (RM) module, but not in Sales Order Processing (SOP)? This question has come up few a times on newsgroups and also with our customers. Most recently there was a question on the Dynamics GP Community Forum about this. The script below is a slightly cleaned up version of my answer from that thread. Here is a sample of what the results will look like:

Some 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
rm.CUSTNMBR Customer_ID,
rm.DOCDATE Doc_Date,
rm.DOCNUMBR Doc_Number,
case s.SOPTYPE when 3 then 'Invoice' else 'Return' end Doc_Type,
case rm.VOIDSTTS when 0 then 'No' else 'Yes' end Voided_in_RM,
case s.VOIDSTTS when 0 then 'No' else 'Yes' end Voided_in_SOP
from
(select CUSTNMBR, DOCDATE, DOCNUMBR,
 case RMDTYPAL when 1 then 3 else 4 end SOPTYPE, VOIDSTTS
 from rm20101 where RMDTYPAL in (1,8)
  union
 select CUSTNMBR, DOCDATE, DOCNUMBR,
 case RMDTYPAL when 1 then 3 else 4 end SOPTYPE, VOIDSTTS
 from rm30101 where RMDTYPAL in (1,8) ) rm
inner join SOP30200 s
   on rm.SOPTYPE = s.SOPTYPE and rm.DOCNUMBR = s.SOPNUMBE
where rm.VOIDSTTS <> s.VOIDSTTS
order by rm.DOCDATE desc