Alert for invoice without base document

[former_member730536]

Hi,

I'm using SAP Business One 9.2

I know nothing about query but I would like to be able to benefit from its powerful possibilities to create some particular searches and alerts.

I found this query in a book but I was not able to run it. If you can help. Thanks

SELECT T1.DocNum

FROM dbo.INV1 T0

INNER JOIN dbo.OINV T1 ON T1.DocEntry = T0.DocENtry

WHERE T1.DocNum = $[$8.0.0] AND

T0.ItemCode = $[$38.1.0] AND

T0.BaseType = -1

[support_19]

hjomaa,

If you have any query with $ symbol, that means it is a formatted search query. And it will throw error when you run like normal query.

For your scenario there are two options:

Either create transaction notification to block the Invoice without base document (requires technical skill on sql)

or

Create alert with the following query

Select T0.DocNum From OINV T0 Inner Join INV1 T1 On T0.DocEntry=T1.DocEntry Where T1.BaseType not in (17,15)

17- is the objecttype to verify if the invoice is based on sales order

15-is the objecttype to verify if the invoice is based on delivery

Thank You.