Stored Procedure where Item Cost per Warehouse is Zero
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.
Suda Sampath replied
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'
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'
/*SET @error = 1
SET @error_message = 'No Item Cost defined' */
SELECT @Error = 1, @error_message = 'No Item Cost defined'
(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)