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 http://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.
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?
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
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.
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
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!
Lulu,
Great, glad you got it working!
-Victoria
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
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
Thank you for the view, Victoria. It makes a lot more sense now.
Best regards,
Yana
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
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
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
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
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