[Bug] "DescriptionOnly" detail_type causes incorrect profit_and_loss

[CPAanalytics]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

'DescriptionOnly' DETAIL_TYPE on STG_QUICKBOOKS_INVOICE_LINE causes P&L to report incorrect values.

Relevant error log or model output

No errors were thrown during dbt build.

Expected behavior

The profit and loss should match quickbooks reporting.

dbt Project configurations

config-version: 2
name: 'quickbooks'

version: '0.12.1'

require-dbt-version: [">=1.3.0", "<2.0.0"]

models:
  quickbooks:
    +materialized: table
    +schema: quickbooks
    double_entry_transactions:
      +schema: quickbooks_intermediate
      +materialized: table
    transaction_lines:
      +materialized: ephemeral
    intermediate:
      +materialized: ephemeral
vars:
  quickbooks:
    account: "{{ ref('stg_quickbooks__account') }}"
    address: "{{ ref('stg_quickbooks__address') }}"
    bill_line: "{{ ref('stg_quickbooks__bill_line') }}"
    bill_linked_txn: "{{ ref('stg_quickbooks__bill_linked_txn') }}"
    bill_payment_line: "{{ ref('stg_quickbooks__bill_payment_line') }}"
    bill_payment: "{{ ref('stg_quickbooks__bill_payment') }}"
    bill: "{{ ref('stg_quickbooks__bill') }}"
    bundle_item: "{{ ref('stg_quickbooks__bundle_item') }}"
    bundle: "{{ ref('stg_quickbooks__bundle') }}"
    credit_memo_line: "{{ ref('stg_quickbooks__credit_memo_line') }}"
    credit_memo: "{{ ref('stg_quickbooks__credit_memo') }}"
    credit_card_payment_txn: "{{ ref('stg_quickbooks__credit_card_payment_txn') }}"
    customer: "{{ ref('stg_quickbooks__customer') }}"
    department: "{{ ref('stg_quickbooks__department') }}"
    deposit_line: "{{ ref('stg_quickbooks__deposit_line') }}"
    deposit: "{{ ref('stg_quickbooks__deposit') }}"
    estimate: "{{ ref('stg_quickbooks__estimate') }}"
    estimate_line: "{{ ref('stg_quickbooks__estimate_line') }}"
    invoice_line: "{{ ref('stg_quickbooks__invoice_line') }}"
    invoice_line_bundle: "{{ ref('stg_quickbooks__invoice_line_bundle') }}"
    invoice_linked_txn: "{{ ref('stg_quickbooks__invoice_linked_txn') }}"
    invoice: "{{ ref('stg_quickbooks__invoice') }}"
    item: "{{ ref('stg_quickbooks__item') }}"
    journal_entry_line: "{{ ref('stg_quickbooks__journal_entry_line') }}"
    journal_entry: "{{ ref('stg_quickbooks__journal_entry') }}"
    payment_line: "{{ ref('stg_quickbooks__payment_line') }}"
    payment: "{{ ref('stg_quickbooks__payment') }}"
    purchase_line: "{{ ref('stg_quickbooks__purchase_line') }}"
    purchase: "{{ ref('stg_quickbooks__purchase') }}"
    refund_receipt_line: "{{ ref('stg_quickbooks__refund_receipt_line') }}"
    refund_receipt: "{{ ref('stg_quickbooks__refund_receipt') }}"
    sales_receipt_line: "{{ ref('stg_quickbooks__sales_receipt_line') }}"
    sales_receipt: "{{ ref('stg_quickbooks__sales_receipt') }}"
    transfer: "{{ ref('stg_quickbooks__transfer') }}"
    vendor_credit_line: "{{ ref('stg_quickbooks__vendor_credit_line') }}"
    vendor_credit: "{{ ref('stg_quickbooks__vendor_credit') }}"
    vendor: "{{ ref('stg_quickbooks__vendor') }}"

    financial_statement_ordinal: []
    cash_flow_statement_type_ordinal: []

    using_address: true
    using_bill: true
    using_credit_memo: true
    using_department: true
    using_deposit: true
    using_estimate: true
    using_invoice: true
    using_invoice_bundle: false
    using_journal_entry: true
    using_payment: true
    using_refund_receipt: true
    using_transfer: true
    using_vendor_credit: true
    using_sales_receipt: false
    using_credit_card_payment_txn: false

analysis-paths: ["analysis"]
clean-targets:
- target
- dbt_modules
- dbt_packages

Package versions

Forked copy of most recent version

What database are you using dbt with?

snowflake

dbt Version

1.7.4

Additional Context

invoice_filter as (

select *
from invoice_join
where invoice_line_transaction_type not in ('SubTotalLineDetail','NoAccountMapping', 'DescriptionOnly')

)

Updating int_quickbooks__invoice_double_entry.sql to exclude descriptiononly lines seems to solve the issue.

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-joemarkiewicz]

Hi @CPAanalytics thanks for opening this issue. Do you know why excluding DescriptionOnly for your account ensures the P&L ties out? My understanding is the DescriptionOnly invoice entries are intended only for descriptions and not to calculate amounts?

My concern is if I do not exclude these entries for other customers, then we may get inflated P&L's due to the description only invoice entries being erroneously counted. What are your thoughts?

[CPAanalytics]

The root cause is actually multiple accounts with the account type "Accounts Receivable" when int_quickbooks__invoice_double_entry.sql runs it does a join on ar_accounts. If there are multiple accounts with the type Accounts Receivable it results in duplicate entries. The "DescriptionOnly" lines compounded the issue by somehow scrambling the join to retrieve the correct account identifiers.

To recap eliminating DescriptionOnly in the filter corrected the P&L but the Balance Sheet will not work due to the duplicate AR type accounts. Not sure of the work around for this as those accounts are inactive and "deleted."

[fivetran-reneeli]

Hey @CPAanalytics , just to make sure I'm understanding the issue-- the incorrect P&L is caused by a fanout resulting in duplicates, because of a join on ar_accounts. Which is problematic because multiple accounts can have type Accounts Receivable?

Could you clarify what you meant by the following?

The "DescriptionOnly" lines compounded the issue by somehow scrambling the join to retrieve the correct account identifiers.

If it's relevant, I'm curious if you've looked at the account type variable configuration here in line 139 (make sure to expand the drop down)

[fivetran-reneeli]

Hi @CPAanalytics , just checking up!

[CPAanalytics]

Even after consolidating AR Accounts into one the DescriptionOnly lines cause issue. My suggestion would be to exclude these in the main branch as I've done above.

[fivetran-reneeli]

Thanks @CPAanalytics for the suggestion! You bring up a good point. And thanks for chiming into https://github.com/fivetran/dbt_quickbooks/issues/115 as well. We'll look to updating this package to address these two issues in an upcoming sprint.