PT Sterling Tulus Cemerlang
www.sterling-team.com

Phone
+62-877-8655-5055
+62 (21) 580-6055

Email
sales-enquiry@sterling-team.com

Address
Sampoerna Strategic Square
South Tower 18th
Jl Jend. Sudirman kav 45-46
Jakarta 12930 - Indonesia

400+ FREE Tips for you from Sterling Tulus Cemerlang (STEM) - SAP Indonesia Gold Partner, and Become SAP B1 Super User

Run Query to Update or Insert Table when Create Document

SAP Business One Tips Run Query to Update or Insert Table when Create Document
SHARE ON


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.

  1. please open SQL Server Management Studio and then log in using your database username and password.
  2. 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

    SAP Business One Tips – Run Query to Update or Insert table when Create Document

  3. 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

    SAP Business One Tips – Run Query to Update or Insert table when Create Document

  4. 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

    SAP Business One Tips – Run Query to Update or Insert table when Create Document

  5. After that please run the alter stored procedure query to apply the changes.
  6. 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

    SAP Business One Tips – Run Query to Update or Insert table when Create Document

Run Your Business With The Piece of Mind Using SAP Business One

This article was written by PT. Sterling Tulus Cemerlang, SAP Business One GOLD Partner in Indonesia.
More details about SAP Business One,
email to: sales-enquiry@sterling-team.com or call +6221-5806336 or wa +6287786555055

, , , , , ,