Alert on Payment Term

[Former Member]

Hi All,

I want an alert on the invoice if the customer payment term is 30 days then it show an alert before 1 days to collect payment.

Thanks & Regards

Shiv

[Former Member · Accepted solution]

Hi Shiv,

You may start from this:

SELECT T0.DocNum, T0.CardCode, T0.DocTotal, T1.PymntGroup

FROM dbo.OINV T0

INNER JOIN dbo.OCTG T1 ON T1.GroupNum = T0.GroupNum

WHERE T1.PymntGroup LIKE '%30%' AND DateDiff(DD,T0.DocDueDate,GetDate()) = 29

Thanks,

Gordon

[Former Member · Accepted solution]

SELECT T0.[DocNum], T0.[DocDate], T0.[CardName], T0.[DocTotal] As 'Opening Amount', T0.[PaidToDate], (T0.[DocTotal]-T0.[PaidToDate]) As 'Pending Amount', T0.[DocDueDate], datediff(day,(select convert( datetime,(select T0.[DocDate]),3)),(select convert( datetime,(select T0.[DocDueDate]),3))) As 'OverDue Days' FROM OINV T0

Where

--T0.DOcDueDate<GetDate() And

datediff(day,(select convert( datetime,(select T0.[DocDate]),3)),(select convert( datetime,(select T0.[DocDueDate]),3)))>=28 and T0.DocStatus='O'

Hi.... Try the above query report........

Regards,

Rahul

[Former Member · Accepted solution]

Hi Rahul

Thanks for querry.

but it shows the wrong list of documents

it display the document those are closed and of last year not of current year.

Thanks

Shiv

[Former Member · Accepted solution]

How. its possible yarr.

I have given the the condition as DocStatus='O' means all the open documents. You can see in the in query.....

Regards,

Rahul

[Former Member · Accepted solution]

Hi Rahul,,

I have closed the FY 2009.

the open document are of 2010 but when i run this querry list of document is coming correct but when click on link of document no. it shows the document of closed FY i.e. 2009.

Thanks

Shiv

[Former Member · Accepted solution]

What is the result from my query?

[former_member204969 · Accepted solution]

Try to start the query with

SELECT T0.DocEntry 'Link',T0.DocNum . . .

Then if you click to link to DocEntry, it will go to the correct document.

[Former Member · Accepted solution]

Hi Istvan,,

i have done this but my client requirement is on Document No. not on Doc-entry.

even i have put the condition on Period Indicator but problem remain exits.

Thanks

Shiv

[Former Member · Accepted solution]

HI Gordon,

Thanks for Querry but actual querry that i want is -

SELECT T0.DocNum, T0.DocDate, T0.CardName, T0.DocTotal As 'Opening Amount',

T0.PaidToDate, (T0.DocTotal-T0.PaidToDate) As 'Pending Amount',

T0.DocDueDate, datediff(day,(select convert( datetime,(select getdate()),3)),(select convert( datetime,(select T0.DocDueDate),3))) As 'OverDue Days'

FROM OINV T0

Where

datediff(day,(select convert( datetime,(select getdate()),3)),

(select convert( datetime,(select T0.DocDueDate),3)))<='2' and T0.DocStatus='O'

but when i click ok link of document it shows the closed document of last FY.

Thanks & regards

Shiv

[michael_boucher2 · Accepted solution]

Are you sure they're definitely closed? It runs fine on my system.

[Former Member · Accepted solution]

Hi Michael,

Yes I am sure they are closed.

Thnaks

[Former Member · Accepted solution]

I am afraid you have to tell your customer that this is one of the limitation from B1. Only the unique key could link to the right record. The docnum will not do the job unless all your numbers are unique.

[Former Member · Accepted solution]

so, there is no solution for this problem???

[Former Member · Accepted solution]

Have you tried adding the following to the where clause?

and T0.PIndicator = (your period indicator)

[keith_taylor2]

I would suggest that you just look at the invoice due dates - 1 day regardless of the terms