Stored Procedure where Item Cost per Warehouse is Zero

[Former Member]

Hi There,

I looking for a solution to check on Purchasing documents if the item's warehouse has a cost price or not, if it doesn't the document should not be added. I have tried to write a stored procedure modifying the standard Business One 'Transaction Notification' stored proc but with no success. Is there any possibility that someone could help me write this stored procedure or provide me with an alternative solution. Business One provides built in functionality for sales documents but not for purchase documents.

Any help would be greatly appreciated.

Regards,

Wynand Cilliers

[former_member583013 · Accepted solution]

Wynard,

Couple of things:

Could you paste the SQL code you have already created for the Stored Proc.

Generally, the Cost of the Item which gets updated to OITW. AvgPrice only when you do Goods Receipts. Unless when you are using Standard Cost where you define the Cost first.

Please confirm you scenario and SQL.

In the meanwhile, I have tested the following code which you can add to the SBO Transaction Notification and it works

IF @object_type = '22'

BEGIN

DECLARE @status as varchar

SELECT @status = 'T' FROM [DBO].[OPOR] T0 INNER JOIN [DBO].[POR1] T1 ON T0.DOCENTRY = T1.DOCENTRY

INNER JOIN [DBO].[OITW] T2 ON T2.ITEMCODE = T1.ITEMCODE

WHERE T2.WHSCODE = T1.WHSCODE AND T2.AVGPRICE = 0 AND T0.DOCENTRY = @list_of_cols_val_tab_del

IF @status = 'T'

BEGIN

/*SET @error = 1

SET @error_message = 'No Item Cost defined' */

SELECT @Error = 1, @error_message = 'No Item Cost defined'

END

END

(alternately, Eric on the Forum suggested to try SELECT @Error = 1, @error_message = 'No Item Cost defined' so that the error message would show on the Status Bar and that why I have used it this way)

Suda