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

[jmongerlyra]

Is there an existing issue for this?

• [X] 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__transactionlines.sql

• transaction_accounting_lines.exchange_rate: Exchange rate used on the transaction

_models/netsuite2/netsuite2__balancesheet.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__transactiondetails.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?

• [X] 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 #134 , #136 and #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.