SQL 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 a few changes to the original AR transactions view, primarily to remove some columns that are typically not needed and add underscores to the column names so that this can be used with SmartList Builder without having to change column names.

For more views like this, check out my GP Reports page.

For help with using this in SmartList Builder, take a look at my post on How to use a SQL view in SmartList Builder.

~~~~~

CREATE VIEW view_RM_Trx_Distributions
AS

/*******************************************************************
view_RM_Trx_Distributions
Created on Feb 10, 2010 by Victoria Yudin - Flexible Solutions, Inc.
For updates see https://victoriayudin.com/gp-reports/
Returns all posted (open and history) Receivables transactions
with their GL distributions. All amounts are functional.
Tables used:
RO: RM20101 - Open Transactions
RH: RM30101 – Historical Transactions
DO: RM10101 - Work and Open Distributions
DH: RM30301 - Historical Distributions
G: GL00105 - Account Index Master
Updated on Aug 8, 2013 to fix Void_Status column name
*******************************************************************/

SELECT RO.CUSTNMBR Customer_ID,
       RO.CPRCSTNM Parent_Customer,
       RO.RMDTYPAL Doc_Type_Number,
       CASE RO.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 Document_Type,
       RO.DOCNUMBR Document_Number,
       RO.CHEKNMBR Check_Number,
       RO.BACHNUMB Batch_ID,
       RO.BCHSOURC Batch_Source,
       RO.TRXSORCE Trx_Source,
       CASE RO.CSHRCTYP
         WHEN 0 THEN 'Check'
         WHEN 1 THEN 'Cash'
         WHEN 2 THEN 'Credit Card'
         ELSE ''
         END Cash_Receipt_Type,
       RO.DUEDATE Due_Date,
       RO.DOCDATE Document_Date,
       RO.POSTDATE Posted_Date,
       RO.PSTUSRID Post_User_ID,
       RO.GLPOSTDT GL_Posting_Date,
       RO.LSTEDTDT Last_Edit_Date,
       RO.LSTUSRED Last_User_To_Edit,
       RO.ORTRXAMT Original_Trx_Amount,
       RO.CURTRXAM Current_Trx_Amount,
       RO.SLSAMNT Sales_Amount,
       RO.COSTAMNT Cost_Amount,
       RO.FRTAMNT Freight_Amount,
       RO.MISCAMNT Misc_Amount,
       RO.TAXAMNT Tax_Amount,
       RO.COMDLRAM Commission_Amount,
       RO.CASHAMNT Cash_Amount,
       RO.DISTKNAM Discount_Taken_Amount,
       RO.DISAVAMT Discount_Avail_Amount,
       RO.DISCRTND Discount_Returned,
       RO.DISCDATE Discount_Date,
       RO.DSCDLRAM Discount_Dollar_Amount,
       RO.DSCPCTAM Discount_Percent_Amount,
       RO.WROFAMNT Write_Off_Amount,
       RO.TRXDSCRN Trx_Description,
       RO.CSPORNBR Customer_PO,
       RO.SLPRSNID Salesperson_ID,
       RO.SLSTERCD Sales_Territory,
       RO.DINVPDOF Date_Inv_Paid_Off,
       RO.PPSAMDED PPS_Amount_Deducted,
       RO.GSTDSAMT GST_Discount_Amount,
       CASE RO.VOIDSTTS
         WHEN 0 THEN 'Not Voided'
         WHEN 1 THEN 'Voided'
         WHEN 2 THEN 'NSF check'
         WHEN 3 THEN 'Waived finance charge'
         ELSE ''
         END Void_Status,
       RO.VOIDDATE Void_Date,
       RO.TAXSCHID Tax_Schedule_ID,
       RO.CURNCYID Currency_ID,
       RO.PYMTRMID Payment_Terms_ID,
       RO.SHIPMTHD Shipping_Method,
       RO.TRDISAMT Trade_Discount_Amount,
       RO.NOTEINDX Note_Index,
       RO.Tax_Date Tax_Date,
       coalesce(G.ACTNUMST,'') Account_Number,
       CASE DO.DISTTYPE
         WHEN 1 THEN 'Cash'
         WHEN 2 THEN 'Terms Taken'
         WHEN 3 THEN 'Accounts Receivable'
         WHEN 4 THEN 'Writeoffs'
         WHEN 5 THEN 'Terms Available'
         WHEN 6 THEN 'GST'
         WHEN 7 THEN 'PPS'
         WHEN 8 THEN 'Other'
         WHEN 9 THEN 'Sales'
         WHEN 10 THEN 'Trade'
         WHEN 11 THEN 'Frieght'
         WHEN 12 THEN 'Miscellaneous'
         WHEN 13 THEN 'Taxes'
         WHEN 14 THEN 'COGS'
         WHEN 15 THEN 'Inventory'
         WHEN 16 THEN 'Finance Charges'
         WHEN 17 THEN 'Returns'
         WHEN 18 THEN 'Debit Memo'
         WHEN 19 THEN 'Credit Memo'
         WHEN 20 THEN 'Service'
         WHEN 21 THEN 'Warranty Expense'
         WHEN 22 THEN 'Warranty Sales'
         WHEN 23 THEN 'Commissions Expense'
         WHEN 24 THEN 'Commissions Payable'
         WHEN 25 THEN 'Unit Account'
         WHEN 26 THEN 'Rounding'
         WHEN 27 THEN 'Realized Gain'
         WHEN 28 THEN 'Realized Loss'
         WHEN 29 THEN 'Unrealized Gain'
         WHEN 30 THEN 'Unrealized Loss'
         ELSE ''
         END 'Distribution_Type',
       coalesce(DO.DEBITAMT,0) Debit_Amount,
       coalesce(DO.CRDTAMNT,0) Credit_Amount,
       coalesce(DO.DistRef,'') Distribution_Reference

FROM RM20101 RO
    LEFT OUTER JOIN
       RM10101 DO
       ON RO.RMDTYPAL = DO.RMDTYPAL
       AND RO.DOCNUMBR = DO.DOCNUMBR
    LEFT OUTER JOIN
       GL00105 G
       ON DO.DSTINDX = G.ACTINDX

UNION ALL

SELECT RH.CUSTNMBR Customer_ID,
       RH.CPRCSTNM Parent_Customer,
       RH.RMDTYPAL Doc_Type_Number,
       CASE RH.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 Document_Type,
       RH.DOCNUMBR Document_Number,
       RH.CHEKNMBR Check_Number,
       RH.BACHNUMB Batch_ID,
       RH.BCHSOURC Batch_Source,
       RH.TRXSORCE Trx_Source,
       CASE RH.CSHRCTYP
         WHEN 0 THEN 'Check'
         WHEN 1 THEN 'Cash'
         WHEN 2 THEN 'Credit Card'
         ELSE ''
         END Cash_Receipt_Type,
       RH.DUEDATE Due_Date,
       RH.DOCDATE Document_Date,
       RH.POSTDATE Posted_Date,
       RH.PSTUSRID Post_User_ID,
       RH.GLPOSTDT GL_Posting_Date,
       RH.LSTEDTDT Last_Edit_Date,
       RH.LSTUSRED Last_User_To_Edit,
       RH.ORTRXAMT Original_Trx_Amount,
       RH.CURTRXAM Current_Trx_Amount,
       RH.SLSAMNT Sales_Amount,
       RH.COSTAMNT Cost_Amount,
       RH.FRTAMNT Freight_Amount,
       RH.MISCAMNT Misc_Amount,
       RH.TAXAMNT Tax_Amount,
       RH.COMDLRAM Commission_Amount,
       RH.CASHAMNT Cash_Amount,
       RH.DISTKNAM Discount_Taken_Amount,
       RH.DISAVAMT Discount_Avail_Amount,
       RH.DISCRTND Discount_Returned,
       RH.DISCDATE Discount_Date,
       RH.DSCDLRAM Discount_Dollar_Amount,
       RH.DSCPCTAM Discount_Percent_Amount,
       RH.WROFAMNT Write_Off_Amount,
       RH.TRXDSCRN Trx_Description,
       RH.CSPORNBR Customer_PO,
       RH.SLPRSNID Salesperson_ID,
       RH.SLSTERCD Sales_Territory,
       RH.DINVPDOF Date_Inv_Paid_Off,
       RH.PPSAMDED PPS_Amount_Deducted,
       RH.GSTDSAMT GST_Discount_Amount,
       CASE RH.VOIDSTTS
         WHEN 0 THEN 'Not Voided'
         WHEN 1 THEN 'Voided'
         WHEN 2 THEN 'NSF check'
         WHEN 3 THEN 'Waived finance charge'
         ELSE ''
         END Void_Status,
       RH.VOIDDATE Void_Date,
       RH.TAXSCHID Tax_Schedule_ID,
       RH.CURNCYID Currency_ID,
       RH.PYMTRMID Payment_Terms_ID,
       RH.SHIPMTHD Shipping_Method,
       RH.TRDISAMT Trade_Discount_Amount,
       RH.NOTEINDX Note_Index,
       RH.Tax_Date Tax_Date,
       coalesce(G.ACTNUMST,'') Account_Number,
       CASE DH.DISTTYPE
         WHEN 1 THEN 'Cash'
         WHEN 2 THEN 'Terms Taken'
         WHEN 3 THEN 'Accounts Receivable'
         WHEN 4 THEN 'Writeoffs'
         WHEN 5 THEN 'Terms Available'
         WHEN 6 THEN 'GST'
         WHEN 7 THEN 'PPS'
         WHEN 8 THEN 'Other'
         WHEN 9 THEN 'Sales'
         WHEN 10 THEN 'Trade'
         WHEN 11 THEN 'Frieght'
         WHEN 12 THEN 'Miscellaneous'
         WHEN 13 THEN 'Taxes'
         WHEN 14 THEN 'COGS'
         WHEN 15 THEN 'Inventory'
         WHEN 16 THEN 'Finance Charges'
         WHEN 17 THEN 'Returns'
         WHEN 18 THEN 'Debit Memo'
         WHEN 19 THEN 'Credit Memo'
         WHEN 20 THEN 'Service'
         WHEN 21 THEN 'Warranty Expense'
         WHEN 22 THEN 'Warranty Sales'
         WHEN 23 THEN 'Commissions Expense'
         WHEN 24 THEN 'Commissions Payable'
         WHEN 25 THEN 'Unit Account'
         WHEN 26 THEN 'Rounding'
         WHEN 27 THEN 'Realized Gain'
         WHEN 28 THEN 'Realized Loss'
         WHEN 29 THEN 'Unrealized Gain'
         WHEN 30 THEN 'Unrealized Loss'
         ELSE ''
         END 'Distribution_Type',
       coalesce(DH.DEBITAMT,0) Debit_Amount,
       coalesce(DH.CRDTAMNT,0) Credit_Amount,
       coalesce(DH.DistRef,'') Distribution_Reference

FROM RM30101 RH
    LEFT OUTER JOIN
       RM30301 DH
       ON RH.RMDTYPAL = DH.RMDTYPAL
       AND RH.DOCNUMBR = DH.DOCNUMBR
    LEFT OUTER JOIN
       GL00105 G
       ON DH.DSTINDX = G.ACTINDX

/** 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_Trx_Distributions 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.

32 Responses to “SQL view with all GL distributions for AR transactions”

  1. Victoria – your views are always so helpful On this one, there is a column that is Posted Date (between Document Date and Post User ID). This date is different than the GL Posting Date. Can you tell me what date this represents? Is this the actual day that the transaction was posted on, meaning the GL date was 04/06 but the date the user posted the transaction was 04/07, a day later. Any help is appreciated!

    Like

  2. Hi Victoria,
    Thank you. We have used several of your scripts and they work great.

    I am looking for a view similar to this that shows the receivables GL distributions but only for unposted transactions. Do you have a suggestion how I could modify this script to show unpotsed transactions?

    Thanks

    Chad

    Like

  3. Victoria..I really appreciate your website and the immense help! This is the exact view I am trying to find but my user wants the transaction to be displayed in one row with a column for each distribution account. For example if there one transaction with 3 distribution account (AR, Taxes, Sales) instead of displaying 3 rows, she wants 1 row that has the AR distribution account in one column, Taxes distribution account in one column, and Sales distribution account in one column, etc. Is this possible to do through a view?

    Like

    • Ashley,

      It’s theoretically possible, however I really don’t think this kind of data lends itself to that. What if there are 3 different Sales distributions? What if there are 20? Same for AR and Taxes. What about other distribution types? There are 30 possible distribution types. While you might not normally use a lot of them, the first time you have a transaction with a distribution you have not explicitly coded for, your report won’t be correct. You would have to either allow for the max number of columns for each distribution type upfront, or concatenate the account numbers, but then, how do you tell how much went to each account?

      You could probably create a report based on this view in either SSRS or Crystal Reports that will show the data grouped into columns, like you want. But I don’t see how you could easily do so directly in SQL. Hope that helps.

      -Victoria

      Like

  4. If I wanted to restrict the SQL query to only retrieve transactions with posting in specific range, where would I add this “where” clause in the code above?

    Like

  5. Hi Victoria, I checked your query and I suggest one change in GL_Posting_Date that depends the Trx. Source in RM Distribution, my recomend:

    Open

    CASE WHEN DO.TRXSORCE like ‘RMMSC%’ then RO.VOIDDATE ELSE RO.GLPOSTDT END GL_Posting_Date

    Historical

    CASE WHEN DH.TRXSORCE like ‘RMMSC%’ then RH.VOIDDATE ELSE RH.GLPOSTDT END GL_Posting_Date

    In order to cross GL Distributions vs RM Distributions.

    Regards,
    Max (Maximo Toledo)
    Lima/Perú

    Like

  6. Hi Victoria,
    In the ‘GL distributions for AR transactions’ view I would like to add individual account segments from the ACTINDX table. Normally I go to graphical view and add them in the view but since this view has unions, I cannot get the graphical view. Can you please advise how I can add the individual GL Account segments to this view.
    Any help is greatly appreciated.

    Thanks,

    Iqbal

    Like

    • Iqbal,

      This code already includes the GL00105 table, which has the individual account segments, in each section of the union as G. So you can add something like the following before each of the two ‘FROM’ clauses:

      ,G.ACTNUMBR_1, G.ACTNUMBR_2, G.ACTNUMBR_3
      

      -Victoria

      Like

  7. Victoria,

    Shouldn’t the outer join also contain ” and RO.CUSTNMBR = DO.CUSTNMBR” to ensure uniqueness of document number?

    Thank you for all your great code examples. It makes me look like a genius when I get data to management quickly.

    Fred

    Like

    • Hi Fred,

      Thanks for the kind words!

      I believe Document Number and Doc Type are enough to ensure uniqueness in the Receivables module, you should not need the Customer ID. Are you running into a situation where that is not the case?

      -Victoria

      Like

      • No, i’m not having any situation where that is the case. I thought I had remembered (from the initial importing rules, maybe) that document, document type and customer defined the uniqueness of the transaction…..

        So one less thing to worry about.

        Thanks.

        Like

  8. Victoria

    We are attempting to pull AR data to help with our Sales Tax reporting and find we need to do so using several SmartList queries. I have been using your view_RM_Trx_Distributions view and I’m wondering if it might be possible to create a new view off of this and include the ship-to address information used at the time the transaction was created? We’re trying to get to the City/State/Zip & Tax Schedule ID of the transactions in one query. We are hoping to get the following:
    1. Invoice Number
    2. Invoice Date
    3. Customer Name
    4. Customer ID #
    5. Invoice Amount Before Tax
    6. Tax Amount
    7. Total Invoice Amount
    8. Ship to City
    9. Ship to State
    10. Ship to Zip Code
    Regardless where the transaction originated (SOP, PA, CA, AR, etc.)
    Is this sort of view possible?

    Like

    • Kristie,

      Since the RM tables do not store the ship to address, and since (at least in SOP) it’s possible to have a ship to address manually keyed in for each transaction, theoretically, the only way this would be possible is if you started with the RM tables, and then linked back to every module/product you could possibly have RM transactions originating in. To answer your question…I think this is possible, but certainly wold take some work and should really be created while working with the environment and data set you need to report on, or at least an environment that has all the same modules/products installed and a good set of sample data.

      -Victoria

      Like

  9. Hi Vic,

    How can I get GL account numbers of all RM posted Transactions ?

    Like

    • Yassir,

      The SQL view in this post will show the account numbers for all RM (also known as AR) posted transactions.

      -Victoria

      Like

      • Victoria,

        The post above included invoices+returns (from SOP transactions) while i need RM trx only. However, I used your post + some criteria to filter what i need.

        Thank you for all and appreciate you fast response.

        Like

  10. Thanks again. You don’t have any idea how well your simple explanation fits in.

    Like

  11. Victoria,

    Thank you very much for your answer. I appretiated it very, very much. And I would like to make another related question. An Historical Document may be Applied to an Open Document, and an Historical One? Or viceversa.

    My questions are coming from arguing with someone who said it’s not an Data Base problem. I’m not an GP expert, but it means to me Data Inconsistency. Am I wrong?

    Like

    • Alfredo,

      The definition of a historical AR document in GP is that it is fully applied (or voided) and has been moved to history (this is a manual process in GP).

      There is no problem with having a historical document that is applied to multiple documents, which can be either ‘open’ or historical. And this can happen, yes.

      When reporting, it’s important to capture both the open and historical transactions and their apply information so that you have all the data.

      Is it the best design anyone can think of? Maybe not. However, I understand the reasoning for it and it works with no problems once you understand it.

      Hope this helps.
      -Victoria

      Like

  12. I wonder whether or not I could find the same posted document number in Open Transactions (RM20101), and Historical Transactions (RM30101).

    Like

    • Alfredo,

      A transaction cannot have 2 statuses (open and history), it can either be one or the other. So it should only be in one or the other of those tables, not in both.

      -Victoria

      Like

  13. overworked accountant Reply February 27, 2010 at 9:05 pm

    I am just the Accountant left to cleanup the sloppy work of a GP consultant that intergrated transactions from our inventory system into GP using eConnect.

    They posted the wrong GL account for the GL distro. For example, my return GL distro shows a credit to the inventory account, but the distro type shows COGS and vice versa. Basically, allowing the returns to have the same GL impact as an invoice.

    I used the query provided above and filtered the results in Excel to give me the results for the distro lines that had COGS distro type with GL = inventory and the INVENTORY distro type. I research a few of them to validate the results. I discovered that he had updated the journal entry to update the GL account to match the distro type. However, he didn’t update all of them.

    Can you help me with a revised query to pull in the journal entry details like je number, account number, etc. to see if it matches or not? I need to figure out which lines are still in the wrong acct.

    Like

    • Unfortunately, this would take some considerable time and effort to put together, not something that can just be added simply to the existing code. If you’re interested in having this done as a consulting engagement, please let me know. One more wrench to throw in there…you say some entries were updated in the GL (I am assuming this was before they were posted). Could the others have been updated with additional GL entries to reclass to the correct accounts? If so, depending on how these were entered, it may not be possible to trap that.

      I am guessing this is not something you can get details or help on from the original consultant? If they made a mistake, they should at the very least help you fix it. If they were simply going on the information they were given and it turned out to be incorrect, they are in the best position to help you gather all the data and correct it.

      -Victoria

      Like

  14. Many Thanks!

    Very helpful to do Receivable GL reconciliation.

    Shabnam

    Like

  15. Hi Victoria,

    Thanks so much for this. It saved me a lot of work!

    Regards,

    Bill

    Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | SQL View for AR with GL DistributionsSQL View for AR with GL Distributions - DynamicAccounting.net - April 27, 2016

    […] Posted on February 11, 2010 by Mark Polino Victoria Yudin has a new post up with a SQL view that shows all posted receivables transactions and the related GL distributions. […]

    Like

  2. SQL View for AR with GL Distributions - DynamicAccounting.net - February 11, 2010

    […] View for AR with GL Distributions Victoria Yudin has a new post up with a SQL view that shows all posted receivables transactions and the related GL distributions. Published: Thursday, February 11, 2010, 12:00 […]

    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: