How do I show an alert in a Sales Order to tell me if the BP has an AR invoice over 90 days?

[sapuser19454]

How do I show an alert in a Sales Order to show me if the BP has an AR invoice over 90 days? We do utilize B1up.

[Abdul]

Use below query in your alert , you don't need to add UDF

Select * From OINV where DATEDIFF(DAY, Get(), OINV.DocDate) > 90 and OINV.CardCode = (Select a.CardCode From OINV a where DATEDIFF(DAY, Get(), a.DocDate) > 90)

)

[former_member390407]

Hi David,

I would suggest the following: you can create a user field for this purpose, attach an FMS to fill this UDF when it's needed and move it using B1UP.

So, algorithm is the following:

1) Create a user field for the marketing document header.

2) Create a user query like this:

SELECT CASE
	WHEN COUNT(*) > 0 THEN
		'There are invoices older then 90 days'
	ELSE
		''
	END AS [TEXT]
FROM OINV
WHERE CardCode = $[$4.0]
	AND DocDate < DATEADD(DAY, -90,GETDATE()) 
	AND DocStatus != 'C' -- For opened only

3) Attach this query to the UDF with the "Auto refresh when field changes" settings and choose BP code as the trigger.

At this point in time every single time the CardCode is changed the field will be updated (when an existing document is opened - field will be updated as well).

4) Now you need to move it from the user fields area to the main window. For that you can use the following: B1UP - Item placement. Here you can move it and resize

[Johan_H]

Hi David,

I think you can do it with B1up, but I am not sure, and I don't know how. However you could use a formatted search with a query in some (user defined) field to show the user that this is the case. If that is not secure enough you can use the SBO_SP_TransactionNotification stored procedure to block adding an order.

Regards,

Johan