Alert for Overdue A/R

[Former Member]

Dear Experts,

Good day!

I would like to know if it is possible for SAP B1 to create an alert that will prompt the user once there is A/R Invoice that was overdue.

This alert must include the Delivery Date, Due Date, Customer Name, Delivery #, A/R Invoice #, and Invoice Amount.

Hoping for your positive feedback regarding this matter.

Thank you very much!

Ashley

[Former Member · Accepted solution]

Hi Loyola,

Use this Query to get overdue documents

select  distinct d.DocDate 'Delivery Date' ,d.TaxDate 'Due Date' ,d.CardName 'Customer Name' ,d.DocNum'Delivery #'

,f.DocNum' 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.DocStatus='O' and convert(varchar(15),f.TaxDate,102) >=convert(varchar(15),GETDATE(),102)

[Former Member · Accepted solution]

Hi Bharathiraja J,

Good day!

Thank you for your feedback.

Unfortunately, the query is not working.

Thanks!

Ashley

[Former Member · Accepted solution]

What is the error message you are getting.

[Former Member · Accepted solution]

Hi Bharathiraja J,

Good day!

'No result was found.'

Thanks!

Ashley

[Former Member · Accepted solution]

Hi Bharathiraja J,

This query must be enough of I have the delivery details as stated above.

SELECT

T0.CardName, T0.DocNum,T0.DocTotal,T0.PaidToDate,

T0.DocStatus, T0.CANCELED AS 'Cancelled'

FROM dbo.OINV T0

WHERE T0.PaidToDate < T0.DocTotal AND DateDiff(DD,T0.DocDueDate,Getdate())>0

Thanks!

Ashley

[Former Member · Accepted solution]

Use this query

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

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

[Former Member · Accepted solution]

Hi Bharathiraja,

Thank you for this. Another question is how can I add link button in the delivery # and A/R Invoice #? So it will be easier for the user to view the document.

Thanks!

Ashley

[Former Member · Accepted solution]

Hi Loyola,

Link Button will work only for Docentry , Kindly use this query for link option

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

,f.DocNum' A/R Invoice #',f.docentry 'A/R Invoice Docentry' ,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

[Former Member · Accepted solution]

Hi Loyola,

Have you checked my query ?

[Former Member · Accepted solution]

Hello Bharrathiraja J,

Good day!

The query is working fine.

Thanks!

Ashley

[Former Member · Accepted solution]

Hi Bharathiraja,

Another question, can this query shows what's overdue for year 2015 only?

Thanks!

Ashley

[KennedyT21 · Accepted solution]

Try this

SELECT DISTINCT d.DocDate 'Delivery Date',

       d.DocDueDate 'Due Date',

       d.CardName 'Customer Name',

       d.DocNum'Delivery #',

       d.docentry'Delivery Docentry',

       f.DocNum' A/R Invoice #',

       f.docentry 'A/R Invoice Docentry',

       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

       AND YEAR(f.DocDueDate) = '2015'

Hope helpful

Regards

Kennedy

[Former Member · Accepted solution]

Use this query

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

,f.DocNum' A/R Invoice #',f.docentry 'A/R Invoice Docentry' ,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 and year(f.DocDate)='2015'

[former_member188586]

hey try with bellow Query

SELECT

T0.CardName, T0.DocNum,T0.DocTotal,T0.PaidToDate,

T0.DocStatus, T0.CANCELED AS 'Cancelled'

FROM dbo.OINV T0

WHERE T0.PaidToDate < T0.DocTotal AND DateDiff(DD,T0.DocDueDate,Getdate())>0

--Ramudu

[Former Member]

Hi Ramudu,

Good day!

Appreciate your feedback on my query.

Can we make it like the below format?

Delivery Date, Due Date, Customer Name, Delivery #, A/R Invoice #, and Invoice Amount

Thanks!

Ashley

[former_member188586]

Thanks for the feed back , can you give exactly filed name of Delivery #, A/R Invoice #,

--Ramudu

[Former Member]

Hi Ramudu!

Delivery # - ODLN.DocNum

A/R Invoice #: OINV.DocNum

Thanks!

Ashley

[former_member205766]

Hi Leigh Ashley

Check the below link it may helps you

alert for payments by overdue customers | SCN

With Regards

Balaji Sampath