Hello community! How are you all? I hope you're doing well!
I'm dealing with a customization case where the client has requested the creation of a field that calculates the sum of the taxed bases. As we all know, here in Brazil, we have several taxes with various application scenarios, and I need this query to work according to these scenarios. Let me try to explain myself better... The client generates the incoming and outgoing invoices as a draft, and the purpose of this field is to assist in validating the total taxed base. Essentially, this field needs to sum the taxed base for each tax under the tax code of each line in the document.
I'm still getting the hang of query and procedure development, but I believe that with a transaction, we can solve this. I've created the following formatted query:
Select SUM(T0.BaseSum) from (SELECT distinct T1.[BaseSum] FROM ODRF T0 INNER JOIN DRF4 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocNum] = $[$8.0.1]) as T0

In the image above, you can see that the value returned in the "total ICMS" field is 920, but if we add up the values from the "taxed base" and "other base," the value is 530.
I don't know what to do from here on out, can you help me? How can we create a query that calculates what we need?
Att,
Ryan Santos