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 http://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 *******************************************************************/ 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 VoidS_tatus, 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.
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?
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
That’s what I was afraid of…the Ship-To would not be part of the RM tables. Thanks for the quick response Victoria.
Hi Vic,
How can I get GL account numbers of all RM posted Transactions ?
Yassir,
The SQL view in this post will show the account numbers for all RM (also known as AR) posted transactions.
-Victoria
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.
Thanks again. You don’t have any idea how well your simple explanation fits in.
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?
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
I wonder whether or not I could find the same posted document number in Open Transactions (RM20101), and Historical Transactions (RM30101).
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
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.
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
Many Thanks!
Very helpful to do Receivable GL reconciliation.
Shabnam
Hi Victoria,
Thanks so much for this. It saved me a lot of work!
Regards,
Bill