netsuite.yml

version: 2

models:
  - name: accountxperiod_exchange_rate_map
    description: >
      (Step 1/4) In order to accurately recreate the balance sheet and income statement, 
      it is necessary to convert all transaction amounts into the currency of 
      the parent subsidiary. The logic gets complicated fast, mostly because 
      of the behavior of the balance sheet.  On the balance sheet, the conversion 
      rate you use for a single transaction will differ by accounting period.  
      For example, if a transaction took place in August, and you are generating 
      balances for the December period, you will need to convert the August transaction 
      using the December conversion rate.

      The first step here is to create a mapping of all accounting periods and the 
      respective exchange rates, by subsidiary. This is called period_exchange_rate_map

      Next, we cross join the accounts table to the period_exchange_rate_map, so we can 
      generate a map of exchange rates, by account, accounting period, and subsidiary.
  
  - name: transaction_lines_w_accounting_period
    description: >
      (Step 2/4) Next, we need to prepare a cleaned version of all transactions we need.  
      Using the transactions and transaction_lines tables, the necessary fields are extracted, 
      and transactions that are deleted, revenue arrangements, or non-posting transactions are 
      filtered out. This is called transaction_lines_w_accounting_period. 
    
  - name: transaction_and_reporting_periods
    description: >
      (Step 3/4) Once we have the cleaned transaction_lines_w_accounting_periods, we now need to figure out
      which exchange rate we should use for the currency conversion.  The balance sheet complicates 
      things, as conversion rates vary based on the reporting period.  Therefore, all transactions 
      need to be converted not only for the period in which the transaction took place in, but also 
      all subsequent periods. transaction_and_reporting_periods creates the necessary mapping for this.

  - name: transactions_with_converted_amounts
    description: >
      (Step 4/4) Now that we have the exchange rates and the unconverted amounts, the next step is 
      to calculate the converted total.
      Additonally, we add in a couple of extra fields that will help us in our final balance sheet 
      and income statement queries.

  - name: balance_sheet
    description: >
      The balance sheet query uses the transactions_with_converted_amount
      transformation to recreate all lines necessary for the balance sheet.
      Transactions that are not balance sheet transactions are categorized as
      either Retained Earnings or Net Income.  The Cumulative Translation
      Adjustment total, which in most cases does not have transactions
      associated with it, is calculated manually in the second part of the query.
    columns:
    - name: accounting_period_ending
      description: End date of the accounting period
    - name: is_accounting_period_adjustment
      description: Yes/No field, indicating whether or not the selecting accounting period is an adjustment period
    - name: is_accounting_period_closed
      description: Yes/No field, indicating whether or not the selecting accounting period is closed
    - name: account_category
      description: Category of the account.  Options include Asset, Liability, Equity, Expense or Income.
    - name: converted_amount
      description: Transaction amount, converted into the primary subsidiary's default currency

  - name: income_statement
    description: >
      The income statment query uses the transactions_with_converted_amount transformation 
      to recreate all lines necessary for the income statment.  It also joins in class, 
      department and location information for enhanced reporting. 
    columns:
    - name: accounting_period_ending
      description: End date of the accounting period
    - name: is_accounting_period_adjustment
      description: Yes/No field, indicating whether or not the selecting accounting period is an adjustment period
    - name: is_accounting_period_closed
      description: Yes/No field, indicating whether or not the selecting accounting period is closed
    - name: account_number_and_name
      description: Concatenation of account number and account name
    - name: converted_amount
      description: Transaction amount, converted into the primary subsidiary's default currency
    - name: account_category
      description: Category of the account. Options include Asset, Liability, Equity, Expense or Income.

  - name: transaction_details
    description: >
      This table uses Netsuite's core table, transaction_lines, and joins a handful of 
      other tables to add more detail to those line items.  For all transactions, you are 
      able to see the associated accounting period, account and subsidiary.  Where applicable, 
      you can also see information about the customer, location, item, vendor, and department.
    columns:
    - name: transaction_line_id
      description: Netsuite internal transaction line ID
    - name: is_transaction_non_posting
      description: Yes/No field, indicating whether or not the transaction line is non-posting
    - name: transaction_id
      description: Netsuite internal transaction ID
    - name: is_transaction_intercompany
      description: Yes/No field, indicating whether or not the transaction is an intercompany transaction or an advanced intercompany transaction
    - name: accounting_period_ending
      description: End date of the accounting period
    - name: is_accounting_period_adjustment
      description: Yes/No field, indicating whether or not the selecting accounting period is an adjustment period
    - name: is_accounting_period_closed
      description: Yes/No field, indicating whether or not the selecting accounting period is closed
    - name: is_account_leftside
      description: Yes/No field indicating whether or not the account is leftside
    - name: is_accounts_payable
      description: Yes/No field indicating whether or not the account type name includes 'accounts payable'
    - name: is_accounts_receivable
      description: Yes/No field indicating whether or not the account type name includes 'accounts receivable'
    - name: is_account_intercompany
      description: Yes/No field indicating whether or not the account type name includes 'intercompany'
    - name: parent_account_name
      description: Name of the parent account, if parent account relationship exists.  Otherwise, the name of the account.
    - name: is_income_account
      description: Yes/No field indicating whether or not the account is an income account
    - name: is_expense_account
      description: Yes/No field indicating whether or not the account is an expense account
    - name: customer_date_first_order
      description: Date customer placed first order
    - name: vendor_create_date
      description: Date vendor was created
    - name: converted_amount
      description: Transaction amount, converted into the primary subsidiary's default currency
    - name: transaction_amount
      description: Total amount of the transaction line