SQL view with all posted Receivables transactions


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.

22 Responses to “SQL view with all posted Receivables transactions”

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

    Like

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

    Like

    • 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

      Like

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

    Like

    • 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

      Like

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

        Like

        • 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

          Like

          • 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!

            Like

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

    Like

  5. Thank you for the view, Victoria. It makes a lot more sense now.

    Best regards,
    Yana

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

Trackbacks/Pingbacks

  1. SQL view with all GL distributions for AR transactions « Victoria Yudin - May 16, 2010

    […] a corollary to my SQL view with all posted Receivables transactions, below is a view that will add all the General Ledger distributions to the AR transactions. I made […]

    Like

  2. SQL view with all GL distributions for AR transactions - Victoria Yudin - February 10, 2010

    […] view with all GL distributions for AR transactions As a corollary to my SQL view with all posted Receivables transactions, below is a view that will add all the General Ledger distributions to the AR transactions. I made […]

    Like

  3. Around the Blogsphere - Developing for Dynamics GP - April 29, 2009

    […] Victoria Yudin is continuing her series of articles related to creating views on SQL Server and using them to get data out of Microsoft Dynamics GP.  This time she is covering how to create a SQL view with all posted Receivables transactions.  […]

    Like

  4. SQL View for All Dynamics GP Receivables Transactions - DynamicAccounting.net - April 26, 2009

    […] GP Receivables Transactions Victoria Yudin has cleanup her GP Reports page and added/updated a new SQL view to show all posted Receivables transactions in Dynamics GP. Even better, she is sharing it with us! Thanks Victoria. Published: […]

    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

%d bloggers like this: