cancel
Showing results for 
Search instead for 
Did you mean: 

querry for overdue invoices

Former Member
0 Kudos

HI Experts,

I am looking for help in generating a query which fires an alert for customer invoices over due with payment terms Net 30.

Any help?

Regards,

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

GREAT

Former Member
0 Kudos

Hi,

Try:

SELECT T0.CardCode,T0.CardName,T0.Docnum as 'Invoice No.',

T0.docdate, T0.docduedate,T0.doctotal,DateDiff(dd,t0.docdueDate,GetDate()) 'Days OverDue'

FROM OINV T0

WHERE DateDiff(dd,t0.docdueDate,GetDate()) > 30 and t0.docstatus = 'O' and

T0.GroupNum IN (SELECT GroupNum FROM OCTG WHERE PymntGroup like '%Net 30%')

ORDER BY T0.CardName

Thanks,

Gordon

Former Member
0 Kudos

Hi........

Please try this.......

SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], 
T0.[CardName], T0.[DocTotal] FROM OINV T0 
WHERE DateDiff (dd,  T0.[DocDate], T0.[DocDueDate] )>=30 
and DateDiff (dd,  T0.[DocDate], T0.[DocDueDate] )<=31 and T0.DocStatus='O'

Regards,

Rahul

jitin_chawla
Advisor
Advisor
0 Kudos

Hi,

You can check the following for alert :


select distinct 'TRUE'
from OCTG T0  
INNER JOIN OINV T1 ON T0.GroupNum = T1.GroupNum
where DateDiff(dd,t1.docdueDate,GetDate()) > 30 and t1.docstatus = 'O' 
and t0.PymntGroup like '%Net 30%'

The result can be checked using the following query :



select t1.Docnum as 'Invoice No.', t1.doctotal as 'Documnet amount',
t1.docdate, t1.docduedate
from OCTG T0  
INNER JOIN OINV T1 ON T0.GroupNum = T1.GroupNum
where DateDiff(dd,t1.docdueDate,GetDate()) > 30 and t1.docstatus = 'O' 
and t0.PymntGroup like '%Net 30%'

Check in DEMO if the same helps and brings you the correct result.

Kind Regards,

Jitin

SAP Business One Forum Team