In sap business one, we can create a query to update or insert tables when creating or updating documents using store procedures in the database.
for example:
We want to calculate the total quantity of all rows in document and update to document header UDF.
- please open SQL Server Management Studio and then log in using your database username and password.
- After login, at the left panel find your database name > click the drop-down menu on Programmability > click the drop-down menu on Stored Procedures.
- Then please find SBO_SP_PostTransactionNotice > right-click on it then choose Modify.
- Now we can add a query to calculate the total quantity in the document.
-- Return values declare @error int declare @error_message nvarchar (200) select @error = 0 select @error_ message = N 'Ok' -------------------------------------------------------------------------------------------- -- ADD YOUR CODE HERE IF @object_type='13' AND (@transaction_type='A' OR @transaction_tupe='U') BEGIN UPDATE OINV SET U_TotalQty=(SELECT SUM(Quantity) FROM INV1 WHERE DocEntry=OINV.DocEntry) WHERE DocEntry @list_of_cols_val_tab_del; END --------------------------------------------------------------------------------------------- -- Select the return values select @error, @error_message end
- After that please run the alter stored procedure query to apply the changes.
- Now we have UDF in the document that shows the total quantity of all rows.
Also read: