Alert Management when Payment not received within duedate

[Former Member]

Hi Experts,

Is there any Qery for alering the Manager and User when the payment was not received with in the Due date of the customer. It meens the system must Alert all the Invoice Details which were open after due date.

Thanks

Srini

[former_member1269712]

Hi Srini,

select docnum,CONVERT(VARCHAR(8),docduedate,3) as Docduedate,cardcode,cardname,doctotal,paidtodate from oinv

where Doctotal>paidtodate and CONVERT(VARCHAR(8),docduedate,3)>=CONVERT(VARCHAR(8),getdate(),3)

Thanks

SD

[Former Member]

Hi Sachin,

The report is showing previos year postings also. We need only current year Doc.Numbers.Is it possible to get the report by parameters like Date from ---> Date To

Thanks & Regards,

Srini

[Former Member]

Hi Srini,

Alert query will not be compatible with any parameters.

You may check this:

SELECT T0.DocNum, T0.DocDueDate, T0.CardName,

T0.WTSum AS 'TDS Amount',

T0.DocTotal,

(T0.DocTotal-T0.PaidToDate) as 'BalanceDue'

FROM OINV T0

WHERE Year(T0.DocDate) = Year(GetDate()) AND T0.DocStatus = 'O' AND DateDiff(DD, T0.DocDueDate, GetDate()) > 0

GROUP BY T0.DocNum, T0.DocDate, T0.CardName,

T0.WTSum,T0.DocTotal,T0.PaidToDate

Thanks,

Gordon