determing days past due

[rolenee]

Hello,

I'm trying to send customers escalating emails based upon how many days late they are. I tried exporting a list out of AR Customers where I set the criteria to day's overdue to 7, 14, 21- When I pull the report, there is a bunch of 0's in the days overdue column? Is their a way to pull this type of report?

Eric

 

[DjangMan]

You could look at the AROBL table for unpaid documents and get the age of the oldest document.

 

[rolenee]

Hmmm- could anyone help me with this query I started. I want pull the customers information if they are more than 5 days late on a payment- I'm getting a error message

SELECT dbo.AROBL.CODETERM AS "Terms Code", dbo.ARCUS.EMAIL1 AS "E-Mail Address", dbo.ARCUS.IDCUST AS "Customer #", dbo.ARCUS.NAMECUST AS "Name", dbo.AROBS.IDINVC AS "Invoice #", dbo.AROBS.CNTPAYM AS "Payment #", dbo.AROBS.DATEDUE AS "Date Due", dbo.AROBS.AMTDUETC AS "Original Amount Due", dbo.AROBS.AMTPYMRMTC AS "Current Amount Due" FROM ((dbo.AROBL LEFT OUTER JOIN dbo.AROBS ON dbo.AROBL.IDINVC = dbo.AROBS.IDINVC) LEFT OUTER JOIN dbo.ARCUS ON dbo.ARCUS.IDCUST = dbo.AROBL.IDCUST) WHERE dbo.AROBL.CODETERM = 'DR3 ' AND dbo.AROBS.DATEDUE < {%Current Date YYYYMMDD%} AND dbo.AROBS.AMTPYMRMTC > 0

 

[ramam1]

This should get you there if you are SQL capable and using MS SQL Server (for other DBs the date processing might not be right).

SELECT IDCUST
FROM AROBL 
WHERE 
	DATEDUE < CONVERT(NVARCHAR, DATEADD(day, -5, GETUTCDATE()), 112) --Due Five days before today
	and SWPAID = 0  --Not Yet Fully Paid
	and TRXTYPETXT in (1, 2, 4)  --Only AR Documents of Type Invoice, Debit Note, Interest

cheers