Skip to Content
0
Former Member
Aug 20, 2015 at 06:41 AM

Overdue A/P Invoice

72 Views

Dear Experts,

Good day.

I have a query for overdue A/R Invoice which goes like this:

select distinct d.DocDate 'Delivery Date' ,d.DocDueDate 'Due Date' ,d.CardName 'Customer Name' ,d.docentry'Delivery #'

,f.docentry 'A/R Invoice #' ,f.DocTotal 'Invoice Amount'

from DLN1 C

Inner Join ODLN D on C.docEntry = D.DocEntry and D.Canceled = 'N'

Inner Join INV1 E on E.BaseType = 15 and E.BaseEntry = C.DocEntry and E.BaseLine = C.LineNum

Inner Join OINV F on E.docEntry = F.DocEntry and F.Canceled = 'N'

where f.PaidToDate < f.DocTotal AND DateDiff(DD,f.DocDueDate,Getdate())>0

I want also to monitor my Overdue A/P Invoice but when I tried to change the table of delivery to GRPO and AR Invoice to AP Invoice but the query doesn't work and it's working on Sales module.

select distinct d.DocDate 'Delivery Date' ,d.DocDueDate 'Due Date' ,d.CardName 'Customer Name' ,d.docentry'Delivery #'

,f.docentry 'A/R Invoice #' ,f.DocTotal 'Invoice Amount'

from PDN1 C

Inner Join OPDN D on C.docEntry = D.DocEntry and D.Canceled = 'N'

Inner Join PCH1 E on E.BaseType = 15 and E.BaseEntry = C.DocEntry and E.BaseLine = C.LineNum

Inner Join OPCH F on E.docEntry = F.DocEntry and F.Canceled = 'N'

where f.PaidToDate < f.DocTotal AND DateDiff(DD,f.DocDueDate,Getdate())>0

Can someone help me on this please?

Thanks!

Ashley