v0.14.1 dbt_quickbooks

PR #138 introduces the following updates:

Bug Fixes

• Added nullif logic to account for "divide by zero" errors in int_quickbooks__deposit_double_entry and int_quickbooks__deposit_transactions for when total_amount values from the deposit source table
  are zero.

Under the Hood

• Added integrity test deposit_ledger_amounts_match within integration tests to compare deposit amounts between int_quickbooks__deposit_double_entry and deposit transaction_type values in quickbooks__general_ledger.
• Modified seed files in integration_tests to reproduce issue and confirm fixes.

Full Changelog: v0.14.0...v0.14.1

v0.14.0 dbt_quickbooks

New major feature alert! Multicurrency is here!

🚨 Breaking Changes 🚨

Feature Updates: Multicurrency Support

• We have introduced multicurrency support to the following models by providing these new fields that convert transaction amounts by their exchange rates. (PR #134)
• IMPORTANT: We do not yet have proper converted_amount values for credit card payments and transfers. Currently it is being brought in as the equivalent of amount, so you might see slight discrepancies if need these values converted as well. Please open an issue with us to help work with us to support this feature.
• We have kept the existing cash value fields that provides amounts and balances to ensure full coverage to customers regardless of their currency setup. (PR #134)
• The new multicurrency fields that fulfill the same function as the respective existing fields is below:

Model	New Multicurrency Fields	Respective Single Currency Fields
quickbooks__general_ledger	adjusted_converted_amount, running_converted_balance	adjusted_amount, running_balance
quickbooks__general_ledger_by_period	period_net_converted_change, period_beginning_converted_balance, period_ending_converted_balance	period_net_change, period_beginning_balance, period_ending_balance
quickbooks__profit_and_loss	converted_amount	amount
quickbooks__balance_sheet	converted_amount	amount
quickbooks__cash_flow_statement	cash_converted_ending_period, cash_converted_beginning_period, cash_converted_net_period	cash_ending_period, cash_beginning_period, cash_net_period
quickbooks__ap_ar_enhanced	total_converted_amount, estimate_total_converted_amount, total_current_converted_payment	total_amount, estimate_total_amount, total_current_payment
quickbooks__expenses_sales_enhanced	total_converted_amount, converted_amount	total_amount, amount

• Introduced *_converted_* type fields in our intermediate models to convert amounts where exchange rates exist for those transactions. If there is no exchange rate, these *_converted_* fields will default back to the already existing fields created for single currency, and all downstream calculations should match the single currency amount, balance and cash values. (PR #134)
• For double-entry models that applied a cross-join to either AP/AR accounts, we are now mapping those accounts based on the currency_id value in the accounts source table for those transactions. (PR #134)
• In the analysis folder, added the converted_balance to the quickbooks__balance_sheet and ending_converted_balance to the quickbooks__income_statement models. (PR #134)

Bug Fixes

• Adjusted logic for discount sales receipt lines in int_quickbooks__sales_receipt_double_entry model to bring in these values properly as negative adjusted amounts in the quickbooks__general_ledger.
  (PR #130)
• Applied filter in int_quickbooks__invoice_double_entry to filter out 'Accounts Receivable' accounts that are inactive. (PR #134)

Under the Hood

• Added consistency and integrity tests within integration tests for all end models. (PR #130) & (PR 134)
• Appended using_credit_card_payment_txn check in get_enabled_unioned_models macro to false to match consistency of how the variable is defined throughout our Quickbooks models by default.

Documentation Update

• Updated README to reflect the new multicurrency support. (PR #134)
• Added yml documentation with the new multicurrency fields and descriptions. (PR #134)

Contributors

• @mikerenderco (PR #131)

Full Changelog: v0.13.1...v0.14.0

v0.13.1 dbt_quickbooks

PR #125 includes the following updates:

Bug Fix

• The period_first_day and period_last_day fields were mistakenly left out in the quickbooks__profit_and_loss model although they were intended to be introduced as new fields in the v0.13.0 release. We have added these fields into the model.

Full Changelog: v0.13.0...v0.13.1

v0.13.0 dbt_quickbooks

PR #124 includes the following updates:

🚨 Breaking Changes 🚨:

• Updates the int_quickbooks__invoice_join and downstream quickbooks__ap_ar_enhanced models to include and require the using_payments config. Previously, these models would fail if the payment or the payment_line source tables did not exist.
• Corrects the misspelled customer_vendor_webiste field to customer_vendor_website in quickbooks__ap_ar_enhanced.

Bug Fixes

• Updates the logic for the amount field in int_quickbooks__invoice_double_entry to use invoice.total_amount only on the condition when a bundle is associated with the invoice and invoice.total_amount is 0, otherwise invoice_lines.amount is used.
  • This avoids double counting when aggregating invoice_line items and accounts for the edge cases where a bundle_id is involved.

Feature Updates

• Updates the quickbooks__profit_and_loss and quickbooks__balance_sheet models to include both period_first_day and period_last_day in addition to calendar_date. This allows users to have greater flexibility in choosing which date to aggregate records upon.
  • Please note calendar_date is slated to be deprecated, and the fields period_first_day and period_last_day are both offered as replacements, depending on how your company performs their financial reporting.

Full Changelog: v0.12.4...v0.13.0

v0.12.4 dbt_quickbooks

PR #123 includes the following updates:

Bug Fixes

• Added source_relation to joins within the following models as it was previously missed:
  • int_quickbooks__invoice_join
  • int_quickbooks__bill_join
  • int_quickbooks__refund_receipt_double_entry
  • int_quickbooks__sales_receipt_double_entry
  • quickbooks__balance_sheet analysis model.

Contributors

• @MatteyRitch (PR #121)

Full Changelog: v0.12.3...v0.12.4

v0.12.3 dbt_quickbooks

PR #119 includes the following updates:

Bug Fixes

• Included a default start and end date in the int_quickbooks__general_ledger_date_spine logic when generating the date spine. These default start and end dates will ensure the model still succeeds when no transactions are yet available.
  • The default start date will be one month in the past
  • The default end date will be the current date

Under the Hood

• Updated the maintainer PR template to resemble the most up to date format.
• Removed the check docs GitHub Action as it is no longer necessary.

Full Changelog: v0.12.2...v0.12.3

v0.12.2 dbt_quickbooks

PR #114 includes the following updates:

Bug Fixes

• Updated model int_quickbooks__invoice_double_entry to account for the sales_item_account_id field from the invoice_lines source when determining the account_id associated with an invoice.

Full Changelog: v0.12.1...v0.12.2

v0.12.1 dbt_quickbooks

PR #109 includes the following updates:

Bug Fixes

• Adjusted the joins within the below intermediate double entry models to be left join as opposed to an inner join. This update was necessary as there was the possibility of the respective account cte joins to return no records. If this was the case, the logic could erroneously remove transactions from the record.
  • int_quickbooks__bill_payment_double_entry
  • int_quickbooks__credit_memo_double_entry
  • int_quickbooks__deposit_double_entry
  • int_quickbooks__invoice_double_entry
  • int_quickbooks__payment_double_entry

Full Changelog: v0.12.0...v0.12.1

v0.12.0 dbt_quickbooks

PR #103 includes the following updates:

🚘 Under the Hood

• Update seeds and configs in the integration tests folder to match what was updated upstream in the (source package PR #51) to correct timestamp fields that should be date fields (due_date, transaction_date). Previously, some fields were getting interpreted as timestamps while some were interpreted as dates, leading to errors on downstream joins. In the upstream staging models, due_date and transaction_date are now explicitly cast as date types.

This will be a breaking change to those whose source tables still use the old timestamp formats, so please update your package version accordingly.

Full Changelog: v0.11.1...v0.12.0

v0.11.1 dbt_quickbooks

This release of dbt_quickbooks includes the following updates:

🐛 Bug Fixes 🩹

• Updated intermediate double entry models that have account_type and account_sub_type filters with configurable variables, since the type names used in the filter can be adjusted internally by QuickBooks customers. (#98)
• Includes items.asset_account_id as the second field of the coalesce for the payed_to_account_id field within the int_quickbooks__bill_payment_double_entry model to ensure all account_id types are taken into consideration when mapping the transaction to the proper account. (#100)

🎉 Feature Updates 🎉

• The intermediate models where these variables were introduced in the models below (#98):

Updated model	New variables to filter on
int_quickbooks__bill_payment_double_entry	quickbooks__accounts_payable_reference
int_quickbooks__credit_memo_double_entry	quickbooks__accounts_receivable_reference
int_quickbooks__deposit_double_entry	quickbooks__undeposited_funds_reference
int_quickbooks__invoice_double_entry	quickbooks__sales_of_product_income_reference, quickbooks__accounts_receivable_reference
int_quickbooks__payment_double_entry	quickbooks__accounts_receivable_reference

🗒️ Documentation

• Updated README with additional steps for configuration. This is an optional step since most customers will rely on the default account type/subtype values available.

Contributors

• @mikerenderco (#100)

Full Changelog: v0.11.0...v0.11.1