A/P and A/R SQL Query

We're running GP80 and I have some managment that is interested in a custom 
report that is not embedded inside of GP, hence no using the report writer.  
I have not had much experience digging around in the tables GP either.

One of the items they are interested in is the total A/R and A/P outstanding 
as well as those same items aged 30 60 90 days.

Ive been looking thru the tables in the database and am having some trouble 
getting a query together that matches what GP reports those values should.  
Basically what i am looking for is a query t hat will produce the Totals row 
that shows up on the Aged Trial Balance with Options-Detail report.

If anyone has a query that does this or can point me in the right direction 
Id greatly appreciate it.  Even just the tables that I need to look at would 
be a big help.

Thanks!

R



0
Robert4087 (350)
4/28/2006 12:11:02 PM
greatplains 29623 articles. 6 followers. Follow

7 Replies
1112 Views

Similar Articles

[PageSpeed] 44

Check out RM00103, Customer Master Summary.  That table holds each
customer's balance and aging bucket amount.  SELECT * FROM RM20101
WHERE CURTRXAM will return to you all Open AR Transactions.

I'm not sure there is a comparable RM00103 table for PM.  You may need
to summarize the data in the PM20000, PM Trx Open, to get what you
need.  SELECT * FROM PM20000 WHERE CURTRXAM <> 0 will return all Open
PM Transactions.

This should get you started.  You might need some help interpretting
RMDTYPAL values and such but you can get the help here.

I have some stored procs that I used to recreate the RM and PM Trial
Balances for v7.  You would have to tweak them to fit your environment
but I would be glad to share.  Post an e-mail address and I will send
them to you.

0
michaelj2 (180)
4/28/2006 12:53:18 PM
Greatly appreciate the reply and insight.

You can send to me at:  rmc@virtualgrp.com

"MichaelJ" wrote:

> Check out RM00103, Customer Master Summary.  That table holds each
> customer's balance and aging bucket amount.  SELECT * FROM RM20101
> WHERE CURTRXAM will return to you all Open AR Transactions.
> 
> I'm not sure there is a comparable RM00103 table for PM.  You may need
> to summarize the data in the PM20000, PM Trx Open, to get what you
> need.  SELECT * FROM PM20000 WHERE CURTRXAM <> 0 will return all Open
> PM Transactions.
> 
> This should get you started.  You might need some help interpretting
> RMDTYPAL values and such but you can get the help here.
> 
> I have some stored procs that I used to recreate the RM and PM Trial
> Balances for v7.  You would have to tweak them to fit your environment
> but I would be glad to share.  Post an e-mail address and I will send
> them to you.
> 
> 
0
Robert4087 (350)
4/28/2006 12:59:01 PM
If you take a look at RM00103, you will see the outstanding amounts by time 
periods.
The AGPERAMT fields are the different time periods you include the 
outstanding balances for the different time frames. It also gives last 
payment information.

"Robert" <Robert@discussions.microsoft.com> wrote in message 
news:1F3E70CF-4802-4407-87A7-2D0CB0A35155@microsoft.com...
> We're running GP80 and I have some managment that is interested in a 
> custom
> report that is not embedded inside of GP, hence no using the report 
> writer.
> I have not had much experience digging around in the tables GP either.
>
> One of the items they are interested in is the total A/R and A/P 
> outstanding
> as well as those same items aged 30 60 90 days.
>
> Ive been looking thru the tables in the database and am having some 
> trouble
> getting a query together that matches what GP reports those values should.
> Basically what i am looking for is a query t hat will produce the Totals 
> row
> that shows up on the Aged Trial Balance with Options-Detail report.
>
> If anyone has a query that does this or can point me in the right 
> direction
> Id greatly appreciate it.  Even just the tables that I need to look at 
> would
> be a big help.
>
> Thanks!
>
> R
>
>
> 


0
tamking2 (19)
4/28/2006 6:48:18 PM
Michael - can you send them to me at staceyleelee@hotmail.com

"MichaelJ" wrote:

> Check out RM00103, Customer Master Summary.  That table holds each
> customer's balance and aging bucket amount.  SELECT * FROM RM20101
> WHERE CURTRXAM will return to you all Open AR Transactions.
> 
> I'm not sure there is a comparable RM00103 table for PM.  You may need
> to summarize the data in the PM20000, PM Trx Open, to get what you
> need.  SELECT * FROM PM20000 WHERE CURTRXAM <> 0 will return all Open
> PM Transactions.
> 
> This should get you started.  You might need some help interpretting
> RMDTYPAL values and such but you can get the help here.
> 
> I have some stored procs that I used to recreate the RM and PM Trial
> Balances for v7.  You would have to tweak them to fit your environment
> but I would be glad to share.  Post an e-mail address and I will send
> them to you.
> 
> 
0
Stacey (160)
4/29/2006 9:34:01 PM
Robert,

What you are going to need to do is examine the Open Table (in either AR or 
AP) and then the Open Apply To table.  The Open table contains both invoices 
AND payments(checks, credits, debits).  The Open Apply To table relates the 
payments to the invoices and lists how much of the payment was posted against 
the invoice.

This can be a bit complex.  With some work, you can figure this out.  As an 
alternative. there is a book available from www.AccoladePublications.com 
called Information Flow and Posting that explains this and all of the other 
transactions in the basic modules that you should find helpfull.

Richard

"Robert" wrote:

> We're running GP80 and I have some managment that is interested in a custom 
> report that is not embedded inside of GP, hence no using the report writer.  
> I have not had much experience digging around in the tables GP either.
> 
> One of the items they are interested in is the total A/R and A/P outstanding 
> as well as those same items aged 30 60 90 days.
> 
> Ive been looking thru the tables in the database and am having some trouble 
> getting a query together that matches what GP reports those values should.  
> Basically what i am looking for is a query t hat will produce the Totals row 
> that shows up on the Aged Trial Balance with Options-Detail report.
> 
> If anyone has a query that does this or can point me in the right direction 
> Id greatly appreciate it.  Even just the tables that I need to look at would 
> be a big help.
> 
> Thanks!
> 
> R
> 
> 
> 
0
4/29/2006 11:24:01 PM
Hi, MichaelJ:

appreciate if you can also send your script to:
hoifook@pcs.com.my

thks in advance


"MichaelJ" wrote:

> Check out RM00103, Customer Master Summary.  That table holds each
> customer's balance and aging bucket amount.  SELECT * FROM RM20101
> WHERE CURTRXAM will return to you all Open AR Transactions.
> 
> I'm not sure there is a comparable RM00103 table for PM.  You may need
> to summarize the data in the PM20000, PM Trx Open, to get what you
> need.  SELECT * FROM PM20000 WHERE CURTRXAM <> 0 will return all Open
> PM Transactions.
> 
> This should get you started.  You might need some help interpretting
> RMDTYPAL values and such but you can get the help here.
> 
> I have some stored procs that I used to recreate the RM and PM Trial
> Balances for v7.  You would have to tweak them to fit your environment
> but I would be glad to share.  Post an e-mail address and I will send
> them to you.
> 
> 
0
HFLo (288)
5/2/2006 5:09:02 AM
We have created an AP AGING for one of our customers.  We have our own 
Reporting tool that can be easily exported to excel.  What this does is that 
it will get all the transactions and sum them up to have the summary by 
customer and you can also see the detailed invoices plus the payments that is 
applied to that invoice.  One more good thing is that, when you have multiple 
companies in Great Plains in can run across companies so that you will have a 
big picture of you AGING. and can be subdivided by companies too.

if you are interested and want a demo, email me gbuenafe@gpcsystems.com

"Richard L. Whaley" wrote:

> Robert,
> 
> What you are going to need to do is examine the Open Table (in either AR or 
> AP) and then the Open Apply To table.  The Open table contains both invoices 
> AND payments(checks, credits, debits).  The Open Apply To table relates the 
> payments to the invoices and lists how much of the payment was posted against 
> the invoice.
> 
> This can be a bit complex.  With some work, you can figure this out.  As an 
> alternative. there is a book available from www.AccoladePublications.com 
> called Information Flow and Posting that explains this and all of the other 
> transactions in the basic modules that you should find helpfull.
> 
> Richard
> 
> "Robert" wrote:
> 
> > We're running GP80 and I have some managment that is interested in a custom 
> > report that is not embedded inside of GP, hence no using the report writer.  
> > I have not had much experience digging around in the tables GP either.
> > 
> > One of the items they are interested in is the total A/R and A/P outstanding 
> > as well as those same items aged 30 60 90 days.
> > 
> > Ive been looking thru the tables in the database and am having some trouble 
> > getting a query together that matches what GP reports those values should.  
> > Basically what i am looking for is a query t hat will produce the Totals row 
> > that shows up on the Aged Trial Balance with Options-Detail report.
> > 
> > If anyone has a query that does this or can point me in the right direction 
> > Id greatly appreciate it.  Even just the tables that I need to look at would 
> > be a big help.
> > 
> > Thanks!
> > 
> > R
> > 
> > 
> > 
0
Gerald (227)
5/19/2006 3:29:01 PM
Reply:

Similar Artilces:

Search Function with queries
Hi guys, new user to access 2000 and i am trying to create a search query that will allow me to enter in a value and return that value. The part i am having trouble with is i need to do this for many fields and if i use the method i came up with, when i leave a entry empty using the "AND" function i get nothing and when i use the "OR" function i get all the values for both. maybe you guys can help me get in the right direction. this is the command i am using in SQL: SELECT [Chain Machine prod Machine list].Description, [Chain Machine prod Machine list]...

Linq to sql
Hi. There must be something I missed... If I do a simple linq query like this: var sections = from s in ProjectGlobal.rd.ER_Person_Sections where s.Person_Id == "10" select s; The result is shown in a grid and a new itemrow is visible last in the grid. I can also change values in the grid. But if I change the "select s" with specific return values: select new { s.Person_Id, s.Section_Code, s.ER_Section.SectionName }; The new row is not visible and I can't change any values. The dataset seems...

macro query disable download failure
Hello, I have a macro which retrieve different queries from the web. It happens that a resource at that moment isn''t available. If this happens the querie gives a message which said unable to open http://webpage This message i don't want to display (other routines herefor). I use "Application.DisplayAlerts = False" but this won't help. Can anybody help me with this problem. Thanks ...

=?Utf-8?Q?Why_doesn=E2=80=99t_Microsoft_Market_Mon?= =?Utf-8?Q?ey_heavily_as_it_does_their_othe?= =?Utf-8?Q?r?=
I've used Money for sometime now and have been reasonably happy with it. My question-why doesn't MS market Money as it does their other products? The advantage of this to customers would be that a higher sales means more bug fixes, more online banks, and more features, etc. With Money being somewhat relatively 'unknown' to the world, I wonder if one day MS might end up pulling it off the market because of competition....just something to think about... They used to spend megabucks marketing it by comparison. I think they've discovered that the demand is inelastic ...

Nz function in query
Hi All, the query below shows an error if any of the date in / date out entries in the table are empty. How can i impiment the Nz function to stop this. SQL from query... SELECT Employees.[First Name], Employees.[Last Name], Format([Normal start Time],"h:nn AMPM") AS ['Normal Start Time], Format([Normal End Time],"h:nn AMPM") AS ['Normal End Time], Format([scan time in],"h:nn AMPM") AS ['Scan Time In], Format([scan time Out],"h:mm AMPM") AS ['Scan Time Out], Format( [Scan Date In],"dddd dd,mm,yyyy") AS ['Scan Date ...

Programatically Change Recordsource SQL of a form
I am wondering if it is possible to have the record source of a form be the SELECT query of a table that would be "defined" by the entry the user makes on a field in the form. Example - 3 tables all have the same fields, they are named: 55091 55104 55116 The user opens the form and enters the ZipCode they are working with in a field - 55901. I'd like to program the form so that the record source becomes SELECT * From 55901. It seems like that would be something simple ... I would also welcome any feed back as to whether this would be a wise set up. A lit...

how do i create subset and union in XPath Query
Hi Friends, I need a XPath query, whcih ll return me subset of data.... check below xml stream <label id="MyExpenseDetails_lbl" xlink:role="terseLabel">Short Expense Details</label> <label id="MyExpenseDetails_lbl" xlink:role="displayLabel">Expense Details</label> <label id="InternalExpense_lbl" xlink:role="displayLabel">Internal Expense</label> <label id="ExternalExpense_lbl" xlink:role="terseLabel">Short External Expense</label> See above xml data having 4 label ...

Access 2003 query to Excel 2007 problem?
I have a query that I'd set up a while back for a user on an Access 2003 db. The main table has keys that point to other tables (schools and departments). She needs the results in Excel, so we have been exporting it directly to an excel file (v97-2003), which has been working just fine. However, the other day when I was working with her on this year's stuff, we'd run it once as a test, it worked fine, then I made a minor change to add a field, and re-ran it, and got quite different results. The first time we ran it, the resulting Excel file had "resolved" the k...

Autonumber Workaround with SQL Server 2005 BE
I built a multi-user contacts application as a split Access database initially and then moved the back end to SQL Server 2005 using an ODBC connection. I implemented an AutoNumber workaround to avoid primary key collisions when users attempt to add records at the same time (probably borrowed from Allen Browne or Albert Kallal?) by setting the default value of the ContactID field = DMax("ContactID", "tblContacts") + 1 and trapping any duplicate key errors as follows: Private Sub Form_Error(DataErr As Integer, Response As Integer) On Error GoTo Err_Form_Error Resp...

SQL Server Express: Small Work Group Sans Server?
Extrapolating from David W. Fenton's comments in the "Rules of thumb" thread, it seems to me like there is an argument for making most new MS Access projects use a SQL Server Express back end - even if they just go at it via ODBC links the same as they would against a .MDB. Given that, and given that at least some projects' reason for being is tb independent of an IT organization, what would the downsides be of implementing a work group's SQL Server Express back end on a plain old Windows XP PC right in their physical area instead of becoming a tightly-controll...

maximum number of indexes per SQL table that Access (jet) can deal with?
why is there a maximum number of indexes per SQL table that Access (jet) can deal with? is it really the count of indexes + statistics? I've seen plenty of documentation that states that typically DSS systems have twice as much index space as data space.. So I don't think that it's a case of 'over-indexing' I'm just tired of a crippled Jet front end and other people making me 'temporarily drop my indexes' so that they can link to my tables using Jet. has this bug gone away with Access 2007? Is it going away with Access 2010? ...

Problems moving SQL to Code using Mark Plumpton method
Hi Am using Mark Plumptons method to speed up form loading. This is the first time I have done this so being extra careful. Problem is I have a combo box which has a WHERE selection which Marks example does not provide an example code. The code I have below creates a Syntax error, could someone please advise what the problem is. Me.model.RowSource = _ "SELECT type " & _ "FROM tblcartype " & _ "WHERE tblcartype.carmake=Me.model" & _ "ORDER BY type;" If I take out the '&_' at the end of the WHERE clause the...

Import Access Query to Excel
I am trying to import MS Access Query to Excel. I tried with "Data --> Import External Data --> Import Data" but I found two issues. First, "Select Table" lists only 5 of 15 query tables I made. Second, data range only displays column headings, no record. Both Access and Excel files are on local drive. My computer is on domain network and I heard there is a security issue with Import External Data within domain environment. However, I used to do this on my previous employment within domain environment. I appreciate any help! Thank you! Jonathan Kim Jonathan, wh...

Rank Query With Groups (Array?)
Hello, I have a table of data with grades which are grouped by study. I need to rank the grades relative to all the other grades in the same study. Below is an example the study and grade fields with the desired outcome for the rank. Study Grade Rank 11111 89 1 11111 75 2 11111 65 3 22222 99 1 22222 87 2 Hopefully this can be done with simple SQL in a query and not with code but I am open to anything that works. Many thanks in advance for your time and expertise! Try this -- SELECT Study, Grade, (SELECT Count(*) FROM YourTable AS [XX] WHERE You...

Web Query File Reference
Is there a Web Query File (.IQY) Format Reference anywhere? Thanks, - Vlad ...

Routine to run/export queries
Looking for some links to examples that do the following (or something close): 1. user would create a whole bunch of queries themselves (using Access) 2. form that lets the user runs these queries to display results and/or export 3. Would be good if user could select multiple queries and get multiple resultsets to display or be exported to an excel file with multiple worksheets. Example: user selects 5 queries and wants an excel file made with 5 tabs with the 5 different outputs of the queries, or the outputs of the 5 queries show in a form with multiple tabs in Access (hide...

integration manager 10.0-SQL Data Source
We are trying to run an integration by using a link to the GP tables. One of our data sources is using the SQL Server as the data source. We can query the data source and return all rows in the integration. However, when we run the integration we receive the error: An error handler was not found for: Object reference not set to an instance of an object. We are on the latest service pack too. Are you using any field script or event script in IM? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogst...

Still unable to run successful query
I've used the IFF suggestions given but it returns only Not Active results. I'm working on a case problem that reads: Create a query to display all matching records from the tblProgram and tblMember tables, selecting the ProgramType and MonthlyFee fields from the tblProgram table, and the FirstName and LastName fields from the tblMember table. Add a calculated field named MonthlyFeeStatus as the last column that equals Active if the MembershipStatus field is equal to Active and equals Not Active otherwise. -- jj Assuming MembershipStatus is a string (varchar) data field,...

Query Help Please
I have a relational db with a Client Table and a Project Table. The Client table holds general info that usually doesn't change. The Project table has a record for each project we've done for the client, and contains a Manager field. The manager could change from year to year. I want to show by current manager all the other projects for that client (regardless of who previous manager was). Current manager is just the manager on the latest project. I'm guessing I need a query of a query, but I'm stumped. Any advice would be appreciated. Thanks. Do you hav...

SQL Not Working
What is wrong with this SQL? I am trying to use a Median function and trying to have the median calculate for each JobCode. SELECT tEmployeeMasterCopy.JobCode, (SELECT Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1 WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) AS MedianByJobCode FROM tEmployeeMasterCopy GROUP BY tEmployeeMasterCopy.JobCode; Thanks in advance. It seems that (SELECT Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1 WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) ...

P L E A S E
Please, could someone help me with "DLL (Resource)" post (some 25 posts below this post). Please. Thank you. "Jan Plastenjak" <none@ms.com> wrote in message news:ewOtE0A3DHA.2700@tk2msftngp13.phx.gbl... > > Please, could someone help me with "DLL (Resource)" post (some 25 posts > below this post). Please. What? I'm to search through the newsgroup looking for find your post? I don't think so... Cheers Richard. Jan, I have already answered your post, let me repeat it: "If you go to www.microsoft.com and enter the fo...

Help Using APPEND Query
Hi, I'm looking to create a "LOG" table. This table will contain 4 fields; 1. SessionID - AutoNumber 2. DateTime - Text 3. User - Text 4. AccessLevel - Text Very simple. Purpose: I can eventually (6months or more from now) create a report to see if more staff/users/admin are accessing the system and what times they are accessing the system, am/pm etc. I have created a public function which determines and returns the username I have created a public function which determines and returns a string identifying their access level. "Student" "Staff" "Adm...

SQL SSAS Traingin CDs
Hi, Can someone suggest good SQL 2005 SSAS training CDs that I can buy online? Thanks http://msdn.microsoft.com/en-us/library/ms170208.aspx Sorry, but it's free. You may send me a check, if you like. "Mecn" <mecn@yahoo.com> wrote in message news:uwuqT$yqKHA.3408@TK2MSFTNGP06.phx.gbl... > Hi, Can someone suggest good SQL 2005 SSAS training CDs that I can buy > online? > > Thanks > ...

Activity Report/Query Questions
Im trying to get a report out of CRM (whether Crystal or Advanced Find) that will pull all of the contacts that have not had an activity associated with them in the past year. Maybe it's me but i just cannot seem to find a way to do this. This must be a common thing that most companies use so Im figuring it must be possible. Has anyone done a data pull like this before and if so what did you do? Thanks for any help Robert Both advanced find and Crystal reports using the Crystal Enhancements for CRM use fetch XML for queries -- and this is the limiting factor in the type of quer...

Partial match for query criteria
Hello, I am trying to create a query that will deliver results based on matching only a part of the text in a table field. For example, if I have a table that contains the names, address and phone numbers of 10,000 businesses but I only want my query to show me businesses that have the word "National" in their name, how would I do that? I'm not sure how to set up the criteria. Thanks for your help! Use the Like operator with wildcards. In the 'criteria' row of the business name column in query design view enter: Like "*National*" Or you can use a ...