April 24, 2009 
Below is code to create a SQL view that returns all the posted Receivables transactions in Dynamics GP. I have had an abbreviated version of this view on my GP Reports page for a while, but I cleaned it up a bit and gave it some more friendly column names. I also added values for the commonly used fields such as Void Status and Document Type. There may be a lot more fields than are needed for most reports in here, but it is difficult to know who is using what fields. If you have a large number of records in your database you can probably improve performance by taking out the fields you are not using.
This view will work with either SmartList Builder or Crystal Reports, however for SmartList Builder you may want to add spaces in the column names to make it more user-friendly.
~~~~~
CREATE VIEW view_RM_Transactions
AS
/*******************************************************************
view_RM_Transactions
Created on Apr. 24 2008 by Victoria Yudin - Flexible Solutions, Inc.
For updates see https://victoriayudin.com/gp-reports/
Contains all posted (open and history) Receivables transactions.
Returns all fields that are the same from RM20101 and RM30101 tables
with friendlier column names.
Updated May 19, 2011 to add Customer Name *******************************************************************/
SELECT T.CUSTNMBR CustomerID,
CUSTNAME CustomerName,
T.CPRCSTNM ParentCustomer,
RMDTYPAL DocTypeNumber,
CASE RMDTYPAL
WHEN 0 THEN 'Reserved'
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Scheduled Pmt'
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 ''
END DocType,
DOCNUMBR DocumentNumber,
CHEKNMBR CheckNumber,
BACHNUMB BatchID,
BCHSOURC BatchSource,
TRXSORCE TrxSource,
CASE CSHRCTYP
WHEN 0 THEN 'Check'
WHEN 1 THEN 'Cash'
WHEN 2 THEN 'Credit Card'
ELSE ''
END CashReceiptType,
DUEDATE DueDate,
DOCDATE DocumentDate,
POSTDATE PostedDate,
PSTUSRID PostUserID,
GLPOSTDT GLPostingDate,
LSTEDTDT LastEditDate,
LSTUSRED LastUserToEdit,
ORTRXAMT OriginalTrxAmount,
CURTRXAM CurrentTrxAmount,
SLSAMNT SalesAmount,
COSTAMNT CostAmount,
FRTAMNT FreightAmount,
MISCAMNT MiscAmount,
TAXAMNT TaxAmount,
COMDLRAM CommissionAmount,
CASHAMNT CashAmount,
DISTKNAM DiscountTakenAmount,
DISAVAMT DiscountAvailAmount,
DISCRTND DiscountReturned,
DISCDATE DiscountDate,
DSCDLRAM DiscountDollarAmount,
DSCPCTAM DiscountPercentAmount,
WROFAMNT WriteOffAmount,
TRXDSCRN TrxDescription,
CSPORNBR CustomerPO,
T.SLPRSNID SalespersonID,
SLSTERCD SalesTerritory,
DINVPDOF DateInvPaidOff,
PPSAMDED PPSAmountDeducted,
GSTDSAMT GSTDiscountAmount,
DELETE1 [Delete],
CASE VOIDSTTS
WHEN 0 THEN 'Not Voided'
WHEN 1 THEN 'Voided'
WHEN 2 THEN 'NSF check'
WHEN 3 THEN 'Waived finance charge'
ELSE ''
END VoidStatus,
VOIDDATE VoidDate,
T.TAXSCHID TaxScheduleID,
T.CURNCYID CurrencyID,
T.PYMTRMID PaymentTermsID,
T.SHIPMTHD ShippingMethod,
TRDISAMT TradeDiscountAmount,
SLSCHDID SalesScheduleID,
FRTSCHID FreightScheduleID,
MSCSCHID MiscScheduleID,
T.NOTEINDX NoteIndex,
Tax_Date TaxDate,
APLYWITH ApplyWithholding,
SALEDATE SaleDate,
CORRCTN Correction,
SIMPLIFD Simplified, Electronic,
ECTRX ECTransaction,
BKTSLSAM BackoutSalesAmount,
BKTFRTAM BackoutFreightAmount,
BKTMSCAM BackoutMiscAmount,
BackoutTradeDisc BackoutTradeDiscAmount,
Factoring,
DIRECTDEBIT DirectDebit
FROM RM20101 T
LEFT OUTER JOIN RM00101 C
ON T.CUSTNMBR = C.CUSTNMBR
UNION ALL
SELECT T.CUSTNMBR CustomerID,
CUSTNAME CustomerName,
T.CPRCSTNM ParentCustomer,
RMDTYPAL DocTypeNumber,
CASE RMDTYPAL
WHEN 0 THEN 'Reserved'
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Scheduled Pmt'
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 ''
END DocType,
DOCNUMBR DocumentNumber,
CHEKNMBR CheckNumber,
BACHNUMB BatchID,
BCHSOURC BatchSource,
TRXSORCE TrxSource,
CASE CSHRCTYP
WHEN 0 THEN 'Check'
WHEN 1 THEN 'Cash'
WHEN 2 THEN 'Credit Card'
ELSE ''
END CashReceiptType,
DUEDATE DueDate,
DOCDATE DocumentDate,
POSTDATE PostedDate,
PSTUSRID PostUserID,
GLPOSTDT GLPostingDate,
LSTEDTDT LastEditDate,
LSTUSRED LastUserToEdit,
ORTRXAMT OriginalTrxAmount,
CURTRXAM CurrentTrxAmount,
SLSAMNT SalesAmount,
COSTAMNT CostAmount,
FRTAMNT FreightAmount,
MISCAMNT MiscAmount,
TAXAMNT TaxAmount,
COMDLRAM CommissionAmount,
CASHAMNT CashAmount,
DISTKNAM DiscountTakenAmount,
DISAVAMT DiscountAvailAmount,
DISCRTND DiscountReturned,
DISCDATE DiscountDate,
DSCDLRAM DiscountDollarAmount,
DSCPCTAM DiscountPercentAmount,
WROFAMNT WriteOffAmount,
TRXDSCRN TrxDescription,
CSPORNBR CustomerPO,
T.SLPRSNID SalespersonID,
SLSTERCD SalesTerritory,
DINVPDOF DateInvPaidOff,
PPSAMDED PPSAmountDeducted,
GSTDSAMT GSTDiscountAmount,
DELETE1 [Delete],
CASE VOIDSTTS
WHEN 0 THEN 'Not Voided'
WHEN 1 THEN 'Voided'
WHEN 2 THEN 'NSF check'
WHEN 3 THEN 'Waived finance charge'
ELSE ''
END VoidStatus,
VOIDDATE VoidDate,
T.TAXSCHID TaxScheduleID,
T.CURNCYID CurrencyID,
T.PYMTRMID PaymentTermsID,
T.SHIPMTHD ShippingMethod,
TRDISAMT TradeDiscountAmount,
SLSCHDID SalesScheduleID,
FRTSCHID FreightScheduleID,
MSCSCHID MiscScheduleID,
T.NOTEINDX NoteIndex,
Tax_Date TaxDate,
APLYWITH ApplyWithholding,
SALEDATE SaleDate,
CORRCTN Correction,
SIMPLIFD Simplified, Electronic,
ECTRX ECTransaction,
BKTSLSAM BackoutSalesAmount,
BKTFRTAM BackoutFreightAmount,
BKTMSCAM BackoutMiscAmount,
BackoutTradeDisc BackoutTradeDiscAmount,
Factoring,
DIRECTDEBIT DirectDebit
FROM RM30101 T
LEFT OUTER JOIN RM00101 C
ON T.CUSTNMBR = C.CUSTNMBR
/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_RM_Transactions TO DYNGRP
~~~~~
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Victoria Yudin 
Hello Victoria,
I guess my comment didn’t post yesterday. I used this SQL query to create a view called xvr_rm_trx, so I could join the SOP10100 table. We use the MSTRNUMB and PSTGSTUS columns, to give SOP orders an identifying number and see what the status of the order is (Posted / Not Posted). However, the output of the join does not produce any data.
Here is the query I am using. What am I doing wrong?
SELECT
CustomerID,
CustomerName,
S.MSTRNUMB MasterNumber,
DocumentNumber,
DocType,
DocumentDate,
SalesAmount,
TradeDiscountAmount,
CASE
WHEN S.PSTGSTUS = 0 THEN ‘Unposted’
WHEN S.PSTGSTUS = 2 THEN ‘Posted’
ELSE ”
END AS DocumentStatus,
BatchID
FROM xvr_rm_trx R
FULL JOIN SOP10100 S ON S.SOPNUMBE = R.DocumentNumber AND S.CUSTNMBR = R.CustomerID
LikeLike
I was using an inner join before. That is what didn’t produce any data. I changed it to full join, but now the MasterNumber and DocumentStatus do not produce data.
LikeLike
Chase,
Orders stay in the SOP module, they will never go into the RM module. Also, anything in SOP10100 will not be in RM, so I would actually be surprised if you got any data. Finally you really cannot simply link on the SOPNUMBE = DOCNUMBR since there is the possibility of duplicate numbers for different document types.
What exactly are you trying to accomplish? It really helps to understand how a report will be used when you are creating it.
-Victoria
LikeLike
Thanks for the info. I found a view that was already in our system, to create the view that I needed. Also, it’s almost like the one that you posted, where you union the open SOP header/line tables to the historical tables. Thanks for your help!
LikeLike
Hi Victoria,
First of all – thank you SO MUCH for your website. It has helped me a ton over the past year.
I have a question – I am trying to get a breakdown of AR that matches the balance sheet. Is this possible? I see you have quite a AR views available, but wasn’t sure if any tackled this – I’m guessing they may but wasn’t sure where to start. I’m still somewhat of a GP beginner, so I’m sorry if this is a dumb question!
Basically, if the balance sheet says AR = 3 million, I would like a query that shows the supporting breakdown of documents that make up the 3 million – and of course the main thing I want is for the sum of the breakdowns to match the total AR on the balance sheet.
Is that possible?
Thanks,
Zak
LikeLike
Hi Zak,
Thanks for your kind words. Typically to compare to financial statements you need a report as of a particular date. GP calls that a ‘Historical Aged Trial Balance’ or HATB. You can get this out of the box by going to Reports | Sales | Trial Balance | Historical Aged Trial Balance, but it might not be in a format that you want and if you have a lot of data it might take a long time to run.
If you need this in SQL/Excel, I have created custom code to do this, but it was pretty complicated to do so I don’t just give it away. It also does not work in all situations, so I like to go over requirements and environment details ahead of time to see if it will work. If you’re interested in purchasing something like this, please let me know and we can chat about this in more detail via email or on a call.
Thanks again,
-Victoria
LikeLike
Hi Victoria,
I would be interested in SQL Code for HATB. Could you email me with more details? Thank you for your help!!
Thanks,
Zak
LikeLike
Hi Zak,
I have emailed you directly. And I stripped your email address out of the comments so you don’t get a lot of spam from it. 🙂 Please let me know if you don’t get the email.
-Victoria
LikeLike
Hi Victoria, I am trying to build a smartlist to match the “Distribution Breakdown Restier Summary-reprint” in receivables management which was created from reprinting the sales register for a period of time. I am just trying to tie out my financials and customer sales etc. I have a report that gives me the info in detail but we have 10-15 thousand invoices per month and I just need this in summary. Any suggestions?
Thanks
Chad
LikeLike
Hi Chad,
How would you want the data summarized? What would the report look like?
-Victoria
LikeLike
Hi Victoria,
I am new to using SQL, in this view how do I create a criteria that will allow me to select a beginning and end date on the field for posted date.
LikeLike
Hi Herb,
First use my code to create the view, then type the following into SQL Server Management Studio:
select * from view_RM_Transactions where PostedDate between ‘2014-01-01’ and ‘2014-08-31’
You can change the date range as needed, of course.
Hope that helps.
-Victoria
LikeLike
Hello Victoria
I discovered something odd about the RM write off documents (credit memo/debit memo) which consequently is affecting the results of this view.
In the RM20101 and RM30101 the RMDTYPAL = 7 (credit note) however, the WROFAMNT field is updated not the ORTRXAMT with the value of the document. In GP itself, the document is created for $0.00 but the distributions are created correctly. Its looks very odd and it even took me a while to understand what’s going on here.
I found this post about the subject on the MS GP Forum. Supposedly this functionality was desgined to work this way…but it makes no sense when one pulls the data together. https://community.dynamics.com/product/gp/f/32/p/35428/61965.aspx#61965
Any ideas how to fix this document type please?
LikeLike
Hi Lulu,
Sorry, I am not seeing what the issue is. This view correctly reflects what is in GP – the actual credit memo amount is zero and the writeoff amount is whatever was written off. Or are you asking how to fix this in GP? If so, there is nothing wrong with how GP does this, as Christina explained in the post you’ve linked to. It just happens to be how GP records these types of transactions, which is just a little different from other transactions. I just looked through our live data and we have a bunch of transactions like this, all are perfectly fine. Please let me know if I am misunderstanding your question.
-Victoria
LikeLike
Hi Victoria
Let me explain how I’m trying to use your view. I need to create a report by customer of all documents for a certain date period. If you total the OriginalTrxAmount (ORTRXAMT) it does not equal GP because the write off credit document has a value of $0.00
This is the issue. I will only balance to GP if somehow I take into account the WriteoffAmt based on some sort of criteria.
I dont understand why a $0.00 document would be created because its not the value of my writeoff.
LikeLike
Lulu,
This is just how GP tracks writeoffs so that it’s able to show you the writeoff amount separately. (Same with discounts, by the way.) You can look at a customer summary and see how much they have done in Sales vs. Returns vs. Writeoffs vs. Discounts. Some companies like to be able to see how much was written off for each customer, so they can use that information when deciding what kind of credit/deal to extend to them as well as for statistical reports comparing customers, performance, etc. For example, if a customer calls asking for a discount on a new order, the salesperson can go in GP and look at the customer summary – maybe if they see that this customer has a large writeoff, they might make a different decision about giving the customer a discount than if they didn’t know about the writeoff.
If you need to have an accurate report of everything that happened, you can add the writeoff amount to the original amount. You may find that you have other payments, credits or returns where the transaction amount is not zero, but there is still a writeoff amount – typically this happens if the writeoff is entered at the same time as entering the payment, maybe if someone underpaid by 2 cents, the person entering the cash receipt just writes that off. If you have any discounts, you may need to consider them as well.
If you do not agree with how this is handled by GP, then you can change how your users are entering the data – have them always manually enter a credit memo with a document amount for what they want to ‘writeoff’ and not to actually use the writeoff functionality. In this case, the writeoffs would show up as part of the ‘sales’ total for the customer, not as a separate writeoff amount.
-Victoria
LikeLike
Victoria,
I’ve figured about how GP handles the writeoff documents in the tables. The first thing is, when the writeoff functionality is used a credit or debit memo is created against the customer. In the RM20101 and RM30101 the ORTRXAMT = 0.00and the WROFAMNT is the total amount of the transaction.
The second thing is, GP allocates the writeoff amount against the specific RM document(s) used to create the writeoff and updates the WROFAMNT. This makes perfect sense why the original document is zero.
In my report now I’ve created a calculation – namely OriginatingTrxAmt-WriteoffAmt and my results seem fine!
LikeLike
Lulu,
Great, glad you got it working! 🙂
-Victoria
LikeLike
I need to create a report of all payments \ credits applied to invoices for a period of time with a field showing if the invoice is now closed and it’s current balance. We will be paying commisions to our sales team for closed invoices, so I need to show them which invoices have payments but aren’t closed so they can work to get them closed. Any help would be great! We are currently using GP10
Thanks, seth
LikeLike
Seth,
We’ve created reports similar to this before, specifically for collections, so it’s doable. I think you would need a combination of this view and the AR Apply Detail.
-Victoria
LikeLike
Thank you for the view, Victoria. It makes a lot more sense now.
Best regards,
Yana
LikeLike
Hi Victoria,
This statement returns almost everything I need, except that I need to add to the statement so that I can pull in the Sales Account Number from the Distributions. We are trying to build a smartlist that shows what Sales Accounts the posted transactions hit. Employees make a lot of mistakes and post to wrong accounts so we want to use this smartlist to reconcile our sales. Can you give me some suggestions on how to add the sales account to this view?
You are great!
Thanks,
Bill
LikeLike
Bill,
Check out my latest post: SQL view with all GL distributions for AR transactions. That may be just what you’re looking for.
-Victoria
LikeLike
Hi Victoria
thanks again for all your posts
now bothering you again, i was trying to make a query of RM transactions but with the original currency ammount, i got the multi currency table MC020102 but i am not sure wich field is the correct one for original doc ammount and current transaction amount i was guessing ORORGTRX and ORCTRXAM
am I correct?
Thanks Victoria
LikeLike
FAC,
Yes, those are the correct column names. When looking for this type of information in the future, I would recommend looking at the resource descriptions in GP. Go to Tools > Resource Descriptions, choose Tables, select the appropriate product and series, find the table you are looking for in the list and double click on it. This will give you a list of all the fields in that table with much more friendly column names helping to ‘decode’ the columns in the table.
-Victoria
LikeLike
Hi Victoria,
I’m very glad that you created the view to see all Receivables Transactions. I was about to create one myself because I’m looking for the payments that were made via Credit Card. You saved me the trip.
Thanks,
Hector
LikeLike