RM Outstanding receivables 3

[hamzajosh]

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 )

 

[GreatPains]

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.

 

[hamzajosh]

Thanks a ton, that was awesome.

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

 

[hamzajosh]

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 )

 

[GreatPains]

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.

 

[hamzajosh]

Thanks again.

Learn everything but implement only what is needed.