[Bug] Fix account number on balance sheet - income statement and CTA

[jmongerlyra]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Issue 1
The account number for income statement and CTA accounts in the balance sheet model is currently set to NULL. This is not how it is presented on the native Balance Sheet report in NetSuite.

Income statement accounts should use the account number of the system-generated retained earnings account. CTA should use the account number of the system-generated CTA account.

Issue 2
Commonly used fields are missing from the balance sheet, income statement, and transaction details models. See list below.

models/netsuite2/intermediate/base/int_netsuite2__transaction_lines.sql

• transaction_accounting_lines.exchange_rate: Exchange rate used on the transaction

models/netsuite2/netsuite2__balance_sheet.sql

• subsidiaries.full_name: Full hierarchical name of the subsidiary. Useful in sorting.
• accounts.display_name: Account name without number or hierarchy. Cleaner than other name values.
• is_account_leftside: Boolean indicating if account has a native debit balance. This is on other models but missing from balance sheet.

models/netsuite2/netsuite2__transaction_details.sql

• transactions.is_reversal: Boolean indicating line is reversal
• transactions.reversal_transaction_id: Transaction ID of the counterparty in a reversing pair
• transactions.reversal_date: Transaction Date of the counterparty in a reversing pair
• transactions.is_reversal_defer: Boolean indicating reversal deferral
• transaction_lines.is_eliminate: Boolean indicating line will auto-eliminate
• departments.full_name: Full hierarchical name of the department. Useful for sorting.
• subsidiaries_currencies.symbol: Currency of the subsidiary
• transaction_lines.netamount: Net amount of transaction line. This is the actual amount entered when it's in a currency other than the functional currency of the subsidiary.

Issue 3
Would like to add subsidiaries_pass_through_columns so that fields from the subsidiary table can be added to the transaction details model.

Relevant error log or model output

n/a

dbt Project configurations

config-version: 2
name: 'netsuite'
version: '0.12.0'
require-dbt-version: [">=1.3.0", "<2.0.0"]

models:
  netsuite:
    +materialized: table
    +schema: netsuite
    netsuite:
      intermediate:
        +materialized: ephemeral
    netsuite2:
      intermediate:
        +materialized: ephemeral

vars:
  netsuite:
    ## Netsuite staging models
    netsuite_accounting_books: "{{ ref('stg_netsuite__accounting_books') }}"
    netsuite_accounting_periods: "{{ ref('stg_netsuite__accounting_periods') }}"
    netsuite_accounts: "{{ ref('stg_netsuite__accounts') }}"
    netsuite_classes: "{{ ref('stg_netsuite__classes') }}"
    netsuite_consolidated_exchange_rates: "{{ ref('stg_netsuite__consolidated_exchange_rates') }}"
    netsuite_currencies: "{{ ref('stg_netsuite__currencies') }}"
    netsuite_customers: "{{ ref('stg_netsuite__customers') }}"
    netsuite_departments: "{{ ref('stg_netsuite__departments') }}"
    netsuite_expense_accounts: "{{ ref('stg_netsuite__expense_accounts') }}"
    netsuite_income_accounts: "{{ ref('stg_netsuite__income_accounts') }}"
    netsuite_items: "{{ ref('stg_netsuite__items') }}"
    netsuite_locations: "{{ ref('stg_netsuite__locations') }}"
    netsuite_subsidiaries: "{{ ref('stg_netsuite__subsidiaries') }}"
    netsuite_transaction_lines: "{{ ref('stg_netsuite__transaction_lines') }}"
    netsuite_transactions: "{{ ref('stg_netsuite__transactions') }}"
    netsuite_vendor_types: "{{ ref('stg_netsuite__vendor_types') }}"
    netsuite_vendors: "{{ ref('stg_netsuite__vendors') }}"
    netsuite2_account_types: "{{ ref('stg_netsuite2__account_types') }}"
    netsuite2_accounting_book_subsidiaries: "{{ ref('stg_netsuite2__accounting_book_subsidiaries') }}"
    netsuite2_accounting_books: "{{ ref('stg_netsuite2__accounting_books') }}"
    netsuite2_accounting_period_fiscal_calendars: "{{ ref('stg_netsuite2__accounting_period_fiscal_cal') }}"
    netsuite2_accounting_periods: "{{ ref('stg_netsuite2__accounting_periods') }}"
    netsuite2_accounts: "{{ ref('stg_netsuite2__accounts') }}"
    netsuite2_classes: "{{ ref('stg_netsuite2__classes') }}"
    netsuite2_consolidated_exchange_rates: "{{ ref('stg_netsuite2__consolidated_exchange_rates') }}"
    netsuite2_currencies: "{{ ref('stg_netsuite2__currencies') }}"
    netsuite2_customers: "{{ ref('stg_netsuite2__customers') }}"
    netsuite2_departments: "{{ ref('stg_netsuite2__departments') }}"
    netsuite2_entities: "{{ ref('stg_netsuite2__entities') }}"
    netsuite2_entity_address: "{{ ref('stg_netsuite2__entity_address') }}"
    netsuite2_items: "{{ ref('stg_netsuite2__items') }}"
    netsuite2_jobs: "{{ ref('stg_netsuite2__jobs') }}"
    netsuite2_location_main_address: "{{ ref('stg_netsuite2__location_main_address') }}"
    netsuite2_locations: "{{ ref('stg_netsuite2__locations') }}"
    netsuite2_subsidiaries: "{{ ref('stg_netsuite2__subsidiaries') }}"
    netsuite2_transaction_accounting_lines: "{{ ref('stg_netsuite2__transaction_accounting_lines') }}"
    netsuite2_transaction_lines: "{{ ref('stg_netsuite2__transaction_lines') }}"
    netsuite2_transactions: "{{ ref('stg_netsuite2__transactions') }}"
    netsuite2_vendor_categories: "{{ ref('stg_netsuite2__vendor_categories') }}"
    netsuite2_vendors: "{{ ref('stg_netsuite2__vendors') }}"
    accounts_pass_through_columns: []
    classes_pass_through_columns: []
    departments_pass_through_columns: []
    transactions_pass_through_columns: []
    transaction_lines_pass_through_columns: []
    balance_sheet_transaction_detail_columns: []
    income_statement_transaction_detail_columns: []

Package versions

packages:
  - package: fivetran/netsuite_source
    version: [">=0.10.0", "<0.11.0"]

What database are you using dbt with?

snowflake

dbt Version

1.7.14

Additional Context

No response

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

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

[fivetran-catfritz]

Hi @jmongerlyra thank you for opening this issue and the PR. I will take a closer look at this after the weekend!

[fivetran-avinash]

Hello @jmongerlyra , thanks for doing all the work in separating out the initial PR!

I do have two requests before we bring this into a future sprint:

• Can you consolidate PRs Add subsidiaries_pass_through_columns #134 , Fix account_number, add common fields, and subsidiaries_pass_through_columns #136 and Add additional fields #137 into one PR? The changes in each are pretty small so we can just incorporate them into the larger request made in this issue.
• Can you add details as to why these changes are being made from the other PRs to this issue? It looks like you touch on it briefly in the other PRs, but any additional details will help us better understand why these changes should be made and make the validation process easier.

Sorry for all the extra leg work you have to do here, but it'll accelerate our development cycle if you provide these details. Let me know if you have any questions!

[jmongerlyra]

@fivetran-avinash I cherry picked the commits and closed the other PRs. Let me know if you have any questions or feedback.

[fivetran-avinash]

Thanks @jmongerlyra ! We've kicked off work on #131 this sprint (as well as this issue related to your PR in the source) and our team will revisit this issue afterward.

[fivetran-avinash]

Hi @jmongerlyra ! Thanks for your patience here. I've kicked off work on this issue this week and am reviewing your PR. I just wanted to check and see if there's anything else you needed to add your PR before moving into the release process?

Let me know if you have any thoughts, questions or concerns.

[jmongerlyra]

@fivetran-avinash Thanks for checking. There's no additional work planned.

[fivetran-avinash]

Thanks @jmongerlyra . Just want to check: Is this logic in line 113 of the netsuite2__balance_sheet supposed to live in the case/when account_number logic in lines 116-119, rather than the account_id? I didn't see any mention of changing account_id logic and the subquery is pulling an account_number rather than an account_id.

[jmongerlyra]

@fivetran-avinash Good catch. I had to cherry pick/resolve merge conflicts from our fork, and inadvertently swapped these. I have corrected.

[fivetran-avinash]

Thanks for addressing! One last thing, I saw this change in the logic for account_type_name, but didn't see it mentioned in the issue above.

Could you elaborate on why these changes were applied? It does look like it matches the logic applied for account_type_id.

Also, should we apply the logic on lines 99-100 to account_display_name, or does it not apply here?

[jmongerlyra]

Thanks for addressing! One last thing, I saw this change in the logic for account_type_name, but didn't see it mentioned in the issue above.

That's just the way the diff is presenting because that section of code is the same for account_type_name and account_display_name. 87-91 was originally part of account_type_name.

Also, should we apply the logic on lines 99-100 to account_display_name, or does it not apply here?

It does not apply here. That code is unique to account_type_name.

[fivetran-avinash]

Thanks @jmongerlyra ! I've gone ahead and merged your changes into our branch and will hopefully have a release for these changes in a new version of the package out in the coming days.

[fivetran-avinash]

Hi @jmongerlyra! These changes are now live. Let us know if everything looks good on your end. I'll go ahead and close this issue but feel free to get in touch if you have any questions, thoughts or concerns.

[jmongerlyra]

@fivetran-avinash Looks good. Thanks!