×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

RM Outstanding receivables
3

RM Outstanding receivables

RM Outstanding receivables

(OP)
I usually write reports for my company in this way. I write the queries for results required from GP into query analyzer. Put the results in excel and give it to my boss/users. They prefer the data in excel where we further process it.

I need to write a query to get all invoices that are past due (not received payments for) and the days they are past due. I was trying to understand the tables to get all this information from SQL but couldn't get much ahead. If anyone can explain which tables this data is stored in and where the relationship between the invoices and payments is, i would be highly obliged, thanks.

Learn everything but implement only what is needed (learned the hard way :) )

RE: RM Outstanding receivables

3
OK, I've written a similar report for us, the SQL Being..

SELECT RM20101.CUSTNMBR, RM20101.DOCNUMBR, RM20101.RMDTYPAL, RM20101.DOCDATE, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM00101.CUSTNAME, RM00101.SALSTERR, RM00101.SLPRSNID
FROM ANA.dbo.RM20101 RM20101 INNER JOIN
ANA.dbo.RM00101 RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
WHERE RM20101.CURTRXAM <> 0

ORTRXAMT is the Orignal amount and
CURTRXAM is the current balance for each invoice / credit.
Use the DOCDATE and todays date to get the Aging amount.

RE: RM Outstanding receivables

(OP)
Thanks a ton, that was awesome.

Learn everything but implement only what is needed (learned the hard way :) )

RE: RM Outstanding receivables

(OP)
I would appreciate it if you could explains the relations between the tables and how they work.

Learn everything but implement only what is needed (learned the hard way :) )

RE: RM Outstanding receivables

RM20101 is the Receivables Open File
RM00101 is the Customer Master File
They link on Customer Number

Goto

Tools / Resource Descriptions / Tables

This will the list all the table groups in each of the series within the application.

RE: RM Outstanding receivables

(OP)
Thanks again.

Learn everything but implement only what is needed.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close