![SAP Business One Tips Run Query to Update or Insert Table when Create Document](https://www.sap-business-one-tips.com/wp-content/uploads/2020/07/SAP-Business-One-Tips-Run-Query-to-Update-or-Insert-Table-when-Create-Document.jpg)
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.
SAP Business One Tips – Run Query to Update or Insert table when Create Document
- Then please find SBO_SP_PostTransactionNotice > right-click on it then choose Modify.
SAP Business One Tips – Run Query to Update or Insert table when Create Document
- 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
SAP Business One Tips – Run Query to Update or Insert table when Create Document
- 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.
SAP Business One Tips – Run Query to Update or Insert table when Create Document
Also read: