alert for payments by overdue customers

[Former Member]

hi

im trying to create an alert for payments by overdue customers and its not going well. i created this query:

SELECT T1.[DocTotal] AS 'Document Total', T1.[BpAct] AS 'BP Account', T0.[DocStatus] AS 'Document Status', T0.[CANCELED] AS 'Cancelled' FROM  [dbo].[OINV] T0 ,  [dbo].[ORCT] T1  WHERE (T1.[DocTotal] <> (0 )  AND  T1.[BpAct] <> (N'0' )  AND  T0.[DocStatus] = (N'[%0]' )

im getting an error:

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'FOR'.

2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'User-Defined Values' (CSHS) (s) could not be prepared.

please help

thank you

travelyn

[Former Member · Accepted solution]

Hi Travelyn,

You can try:

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,

Gordon

[Former Member · Accepted solution]

thanks Gordon

i tried to do you query but the last line: DateDiff(DD,T0.DocDueDate,Getdate())>0 im not finding any of that on the tables.

Jitin: please inform what the condition for the date will be as m confused

[jitin_chawla · Accepted solution]

Hi,

Gordon query should work fine.

DateDiff(DD,T0.DocDueDate,Getdate())>0 is not on the tables. You will only find the docduedate field in the table OINV.

Getdate() is a function for the system date/current date on which the query is run

and the complete function works as checking the difference between the number of days between todays date and the document due date.

Need more clarification, please let us know.

Kind Regards,

Jitin

SAP Business One Forum Team

[former_member186095]

Here is the correct query alerts:

SELECT T1.[DocTotal] AS 'Document Total', T1.[BpAct] AS 'BP Account', T0.[DocStatus] AS 'Document Status', T0.[CANCELED] AS 'Cancelled' FROM  [dbo].[OINV] T0 ,  [dbo].[ORCT] T1  WHERE (T1.[DocTotal] <> (0 )  AND  T1.[BpAct] <> (N'0' )  AND  T0.[DocStatus] = N'[%0]' )

you can try it again

Rgds,

JimM

[Former Member]

i re-did the quer and these were my results.

i now want to add BP name. is it possible to edit this query?

and when the alerts are up and running is it possible to only see the last invoice that was added?

[jitin_chawla]

Hi,

Check this :

SELECT T1.[DocTotal] AS 'Document Total', T1.[BpAct] AS 'BP Account',
T0.CardName as 'Business Partner Name',
T0.[DocStatus] AS 'Document Status',
T0.[CANCELED] AS 'Cancelled'
FROM  [dbo].[OINV] T0 ,  [dbo].[ORCT] T1 
WHERE (T1.[DocTotal] <> (0 ) 
AND  T1.[BpAct] <> (N'0' )  AND  T0.[DocStatus] = N'[%0]' )

Have you checked the option of having the max(T0.docdate) as an option for the last invoice added in the system for that business partner?

Kind Regards,

Jitin

SAP Business One Forum Team

[former_member186095]

Hi,

That's possible.

I agree with Jitin's query. You can add BP name i.e. T0.cardname in the query alerts.

You must also concern with invoice posting date. What I mean here is that when you use the above query, all overdue invoices will be displayed without any limitations.

Rgds,

JimM

[Former Member]

thanks Jitin

but what would the condition for the doctdate be? and why is it that after i created the query the query selection criteria window always pops up?

thanks

travelyn