February 10, 2010 
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 Updated on Sep 28, 2017 to add Customer Name *******************************************************************/ SELECT RO.CUSTNMBR Customer_ID, C.CUSTNAME Customer_Name, 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 LEFT OUTER JOIN RM00101 C ON RO.CUSTNMBR = C.CUSTNMBR UNION ALL SELECT RH.CUSTNMBR Customer_ID, C.CUSTNAME Customer_Name, 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 LEFT OUTER JOIN RM00101 C ON RH.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_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.
Victoria Yudin 
Good day,
Thank you for your many scripts they have been a tremendous help in understanding GP and resolving issues. I am using this script to pull some info and with a couple of months the original trx amount does not balance with the debits and credits. When the original trx amount is divided by 2 I am expected to get the same value as the debit or credit. Am I reading this correctly?
Rosemary
LikeLike
Hi Rosemary,
The total of the debits should always equal the total of the credits. However, there is no reason why the original trx amount must be in any way related to the debits or the credits. In the very simplest of situations the sum of the original trx amounts will equal the sum of the debits and the sum of the credits. (For example: invoice for $100, debit AR $100, credit Sales $100.) But in real life, it’s unlikely that will be the case for all transactions over time.
-Victoria
LikeLike
Thank you for your response, I was checking for a difference in our AR and I thought it may be a distribution issue. Some months it balanced and others it did not. Thanks again for your response.
LikeLike
Hi Rosemary,
What might be helpful to track down reconciliation issues is looking for entries where the Distribution Type is “Accounts Receivable”, but the Account Number is not. And vice versa – where the Account Number is an AR GL account, but the Distribution Type is something other than “Accounts Receivable”.
-Victoria
LikeLike
Thank you for that suggestion. From your RM Tables I am able to see the distribution types but where can I get info on if it’s a debit or credit entry. As you pointed out if the distribution type is changed and the AR account is valid how does GP respond. I’m going through the community forum to get some more info.
Thanks.
LikeLike
Rosemary,
I would not worry so much about Debits or Credits. In this view you will see a column called Distribution Type and a column called Account Number, those are the ones you want to look at. Regardless of the type of entry it is, or whether something is a Debit or Credit, if the Distribution Type is “Accounts Receivable” but the Account Number is not an AR General Ledger account, you will likely have a reconciliation issue. Of course, this is a very general statement and you might have something unique going on. This view is intended to give you a quicker/better way of looking at a lot of data at once. For more specific reconciliation issues you may need to get your GP Partner involved to help you more specifically.
-Victoria
LikeLike
Hi Victoria,
I just want to say thank you for all you advice you give on this site! You have helped me and my team out with info from this site sooo many times.
I was wondering if you have the syntax for this same view that also includes both functional and originating currencies? we have need for it to have both and thought I would as before tackling it myself in case someone else has already asked this. Thank you again for all you do!
LikeLike
Hi Chris,
Glad to have been able to help!
I don’t have anything already saved with multiple currencies – sorry.
-Victoria
LikeLike
Hi Victoria,
Thank you very much for this?
How can I add Item and Item description to this code?
Kind regards
LikeLike
Hi Nihad,
The AR (Receivables) module does not have any line item details, so the short answer is you cannot do this. Even if you linked back to the SOP (Sales Order Processing) module for AR transactions that originated there, that stores the GL distributions at a summary level, so you would likely not be able to accomplish this.
I have a number of SOP scripts available in case they help with what you need, here is the page: https://victoriayudin.com/gp-reports/sop-sql-views/.
If you’re looking for something else, please give me some more details on what you are trying to achieve.
-Victoria
LikeLike
Victoria,
This view is very helpful, but can the Customer Name be added to this SQL View for AR with GL Distributions.
LikeLike
Hi Deb,
That’s a great idea – I just updated the code to add the customer name.
-Victoria
LikeLike
Thanks Victoria!
LikeLike
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!
LikeLike
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
LikeLike
Hi Chad,
Are you looking for this for unposted Receivables transactions or SOP transactions? Just want to make sure…since unposted SOP transactions will not show up in RM tables.
-Victoria
LikeLike
Hi, I’m curious about this as well. I’m looking for a report which looks at receivables batch entry and pulls the unposted/pending sales invoices with GL distributions.
LikeLike
Hi Daniel,
The following code should do it:
select
r.CUSTNMBR Customer_ID,
c.CUSTNAME Customer_Name,
r.RMDTYPAL Doc_Type_Number,
case r.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,
r.DOCNUMBR Document_Number,
r.CHEKNMBR Check_Number,
r.BACHNUMB Batch_ID,
r.BCHSOURC Batch_Source,
r.DUEDATE Due_Date,
r.DOCDATE Document_Date,
r.GLPOSTDT GL_Posting_Date,
r.LSTEDTDT Last_Edit_Date,
r.LSTUSRED Last_User_To_Edit,
r.DOCAMNT Document_Amount,
r.ACCTAMNT Account_Amount,
r.SLSAMNT Sales_Amount,
r.COSTAMNT Cost_Amount,
r.FRTAMNT Freight_Amount,
r.MISCAMNT Misc_Amount,
r.TAXAMNT Tax_Amount,
r.COMDLRAM Commission_Amount,
r.CASHAMNT Cash_Amount,
r.DISTKNAM Discount_Taken_Amount,
r.DISAVAMT Discount_Avail_Amount,
r.DISCRTND Discount_Returned,
r.DISCDATE Discount_Date,
r.DSCDLRAM Discount_Dollar_Amount,
r.DSCPCTAM Discount_Percent_Amount,
r.WROFAMNT Write_Off_Amount,
r.DOCDESCR Trx_Description,
r.CSTPONBR Customer_PO,
r.SLPRSNID Salesperson_ID,
r.SALSTERR Sales_Territory,
r.PPSAMDED PPS_Amount_Deducted,
r.GSTDSAMT GST_Discount_Amount,
r.TAXSCHID Tax_Schedule_ID,
r.CURNCYID Currency_ID,
r.PYMTRMID Payment_Terms_ID,
r.SHIPMTHD Shipping_Method,
r.TRDISAMT Trade_Discount_Amount,
r.NOTEINDX Note_Index,
r.Tax_Date Tax_Date,
coalesce(g.ACTNUMST,'') Account_Number,
case d.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(d.DEBITAMT,0) Debit_Amount,
coalesce(d.CRDTAMNT,0) Credit_Amount,
coalesce(d.DistRef,'') Distribution_Reference
from RM10301 r
left outer join RM10101 d
on r.RMDTYPAL = d.RMDTYPAL
and r.DOCNUMBR = d.DOCNUMBR
left outer join GL00105 g
on d.DSTINDX = g.ACTINDX
left outer join RM00101 c
on r.CUSTNMBR = c.CUSTNMBR
-Victoria
LikeLike
This works perfectly. Thanks so much!
LikeLike
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?
LikeLike
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
LikeLike
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?
LikeLike
John,
In this particular code, you would need to do that in two places: right before the UNION ALL and at the very end.
-Victoria
LikeLike
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ú
LikeLike
Hi Maximo,
Thanks for your comment. I don’t have any RMMSC transactions in any of my sample data – what type of transaction is that?
-Victoria
LikeLike
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
LikeLike
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:
-Victoria
LikeLike
Hi Victoria,
As always, thanks so much for your help
Iqbal
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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?
LikeLike
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
LikeLike
That’s what I was afraid of…the Ship-To would not be part of the RM tables. Thanks for the quick response Victoria.
LikeLike
Hi Vic,
How can I get GL account numbers of all RM posted Transactions ?
LikeLike
Yassir,
The SQL view in this post will show the account numbers for all RM (also known as AR) posted transactions.
-Victoria
LikeLike
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.
LikeLike
Thanks again. You don’t have any idea how well your simple explanation fits in.
LikeLike
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?
LikeLike
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
LikeLike
I wonder whether or not I could find the same posted document number in Open Transactions (RM20101), and Historical Transactions (RM30101).
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Many Thanks!
Very helpful to do Receivable GL reconciliation.
Shabnam
LikeLike
Hi Victoria,
Thanks so much for this. It saved me a lot of work!
Regards,
Bill
LikeLike