cancel
Showing results for 
Search instead for 
Did you mean: 

Overdue A/P Invoice

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

KennedyT21
Active Contributor
0 Kudos

Try 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   PDN1 C

       INNER JOIN OPDN D

            ON  C.docEntry = D.DocEntry

            AND D.Canceled = 'N'

       INNER JOIN PCH1 E

            ON  E.BaseType = '20'

            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

Regards

Kennedy

Former Member
0 Kudos

Hello Kennedy,

Good day!

The query is working fine also!

But is it possible for this query to show overdue invoices for year 2015 only?

Your immediate response is highly appreciated.

Thank you very much!

Ashley

KennedyT21
Active Contributor
0 Kudos

Try 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',  f.DocDueDate

FROM   PDN1 C

       INNER JOIN OPDN D

            ON  C.docEntry = D.DocEntry

            AND D.Canceled = 'N'

       INNER JOIN PCH1 E

            ON  E.BaseType = '20'

            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  AND YEAR( f.DocDueDate)='2015'

Regards

Kennedy

Answers (1)

Answers (1)

former_member212181
Active Contributor
0 Kudos

Hi Ashley,

Please try below Query

I changed Base Type area in PCH1 JOIN

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 = 20 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

Former Member
0 Kudos

Hello Unnikrishnan Balan,

Good day!

The query is working fine!

But is it possible for this query to show overdue invoices for year 2015 only?

Your immediate response is highly appreciated.

Thank you very much!

Ashley

KennedyT21
Active Contributor
0 Kudos

Hi Ashley...

Try 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',  f.DocDueDate

FROM   PDN1 C

       INNER JOIN OPDN D

            ON  C.docEntry = D.DocEntry

            AND D.Canceled = 'N'

       INNER JOIN PCH1 E

            ON  E.BaseType = '20'

            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  AND YEAR( f.DocDueDate)='2015'

former_member212181
Active Contributor
0 Kudos

Hi Ashley,

Please try below query,

I added one more condition in last line

If required  please replace "and f.DocDate>='20150101'" to and f.DocDueDate>='20150101' as per your expectation.

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 = 20 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 and f.DocDate>='20150101'

Thanks

Unnikrishnan

Message was edited by: Unnikrishnan Balan