[Bug] Currency Translation Adjustment ("CTA") balance is incorrect

[jmongerlyra]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

The converted_amount generated in NETSUITE2__BALANCE_SHEET for the currency translation adjustment account doesn't match the NetSuite balance sheet report in our environment. This is my query.

SELECT SUM(CONVERTED_AMOUNT)
FROM NETSUITE2__BALANCE_SHEET
WHERE ACCOUNTING_PERIOD_NAME = '[period name]'
AND BALANCE_SHEET_SORT_HELPER = 16

The relevant code section appears to be lines 128-178 in netsuite2__balance_sheet.

Relevant error log or model output

No response

Expected behavior

Summarized total from table matches the NetSuite Balance Sheet report.

dbt Project configurations

packages:

package: dbt-labs/dbt_utils
version: 1.0.0
package: brooklyn-data/dbt_artifacts
version: 2.3.0
package: dbt-labs/dbt_project_evaluator
version: 0.5.0
package: dbt-labs/dbt_external_tables
version: 0.8.3
package: fivetran/netsuite
version: 0.9.0

Package versions

0.9.0

What database are you using dbt with?

snowflake

dbt Version

1.4.5

Additional Context

No response

Are you willing to open a PR to help address this issue?

• [ ] Yes.
• [X] Yes, but I will need assistance and will schedule time during our office hours for guidance
• [ ] No.

[fivetran-joemarkiewicz]

Hi @jmongerlyra I have been able to validate the CTA for our internal data and it looks like nearly all months except the previous month match what Netsuite is displaying. This is likely as period close has not happened yet and there will be some adjustments before we see the balances match.

As a result, I was not able to find a specific reason why your CTA balance is significantly off 🤔. One thing we recently came across was a discrepancy with how we are cataloging intercompany transactions. Does your org by chance have a high volume of intercompany transactions? I wonder if Issue #87 may lead us to understand why you are seeing such a high variance. However, this is only if you do in fact have a large number of intercompany transactions. If not, we may need to come back to the drawing board to understand the solution to this variance.

[jmongerlyra]

@fivetran-joemarkiewicz This could very well be relevant. Is there a patch we could test? I am also open to additional meetings if that would be helpful.

[jmongerlyra]

@fivetran-joemarkiewicz The code below fixes the issue for us. Similar to the other issue, this moves from account_type based translation to general_rate_type.

After spending time with the code, I better understand that the model is essentially forcing the balance sheet to balance by translating every transaction and calling the difference CTA. This is a fine approach.

    case
      when lower(accounts.general_rate_type) in ('historical', 'average') then converted_amount_using_transaction_accounting_period
      else converted_amount_using_reporting_month
        end as converted_amount

[fivetran-joemarkiewicz]

@jmongerlyra that's great to hear that you were able to narrow in on the issue and see a resolution! I was able to make these changes on a copy of the branch @fivetran-avinash was working on with similar updates in leveraging general_rate_type.

Would you be able to test this branch and confirm the results are what you would expect?

packages:
  - git: https://github.com/fivetran/dbt_netsuite.git
    revision: fivetran-joemarkiewicz-patch-1
    warn-unpinned: false 

[jmongerlyra]

@fivetran-joemarkiewicz The changes on that branch look good. 👍

[fivetran-joemarkiewicz]

Thanks @jmongerlyra! Let me confirm this with our internal team that these look good. Once I get the all clear from them I feel comfortable in rolling these latest changes out!

[fivetran-joemarkiewicz]

Hi all! The updates highlighted in this issue have been merged and released in the latest release of the dbt_netsuite package. These updates should be live on the dbt hub at the top of the hour.

If you still see this issue persist after upgrading, please let us know. Closing this issue as the latest release includes the fix.