red white puzzle

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:

voids

Some links for additional SOP and RM table information and scripts:

 

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

Trackbacks/Pingbacks

  1. SQL script for comparing voids between RM and SOP in Dynamics GP : Interesting Findings & Knowledge Sharing - April 28, 2014

    […] Read the original post: SQL script for comparing voids between RM and SOP in Dynamics GP […]

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

%d bloggers like this: