[Bug] Issue with Multiple Account Payable

[bernhardF1984]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

In case a quickbook account has multiple accounts payable it will cause an issue in the general ledger, multiplying the data.
I am talking about the model: "int_quickbooks__bill_payment_double_entry.sql"
In this model, you check for the active Account Payable, but sometimes there are multiple such accounts:

`ap_accounts as (

select
    account_id
from accounts

where account_type = 'Accounts Payable'
    and is_active
    and not is_sub_account

),`

Afterwards, there is a cross-join, which in the case of multiple accounts, causes duplicate data in the general ledger:

bill_payment_join as (
select
bill_payments.bill_payment_id as transaction_id,
bill_payments.source_relation,
row_number() over(partition by bill_payments.bill_payment_id order by bill_payments.transaction_date) - 1 as index,
bill_payments.transaction_date,
bill_payments.total_amount as amount,
coalesce(bill_payments.credit_card_account_id,bill_payments.check_bank_account_id) as payment_account_id,
ap_accounts.account_id,
bill_payments.vendor_id,
bill_payments.department_id
from bill_payments

**cross join ap_accounts**

),

Relevant error log or model output

No response

Expected behavior

I think it would be good to give the option to define which one is the real account payable in the project yaml, maybe,, and have a note about this issue.
maybe raise an alert if there are multiple accounts!

dbt Project configurations

quickbooks dbt project.yml:

config-version: 2
name: 'quickbooks'

version: '0.9.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: true
using_journal_entry: true
using_payment: true
using_refund_receipt: true
using_transfer: true
using_vendor_credit: true
using_sales_receipt: true
using_credit_card_payment_txn: false

analysis-paths: ["analysis"]
clean-targets:

• target
• dbt_modules
• dbt_packages

Package versions

packages:

• package: fivetran/quickbooks
  version: [">=0.9.0", "<0.10.0"] # we recommend using ranges to capture non-breaking changes automatically

What database are you using dbt with?

bigquery

dbt Version

1.5.1

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

hi there @bernhardF1984 👋 thanks for taking the time to open this issue, we really appreciate it and agree with your proposed approach! i also really like the idea of raising a warning if there are multiple Accounts Payable (perhaps it could be automatically disabled if you provide the "real" accounts payable n the dbt_project.yml).

Couple of follow up questions for ya:

1. On your end, how do you determine which is the appropriate Accounts Payable to use? is it something you just know internally, or could the package somehow programmatically figure it out? I imagine it's probably the former, but wanted to check first

2. We have similar logic in the following models, where we assume there is only one account of the respective type and perform a cartesian join. I assume we would also introduce variables to configure the "real" accounts of these types as well -- are you seeing duplicates from these models?

• int_quickbooks__credit_memo_double_entry - Accounts Payable
• int_quickbooks__deposit_double_entry - UndepositedFunds
• int_quickbooks__payment_double_entry - Accounts Receivable
• int_quickbooks__invoice_double_entry - Accounts Receivable

We'll get started on this next sprint, which begins mid next week 🤠

[bernhardF1984]

Hi jamie, thanks for getting back to me. 1) Yes, this is mainly something internal. The only thing that could help recognize it, is if one account balance is 0 and the other one not. But again I think having an alert to make the user know about the issue would be great. 2) Yes definitely I would do that, as it can also happen here, that the accountant didn't disable the secondary account. Cheers, Bernhard

…

On Tue, Jul 18, 2023 at 10:46 PM Jamie Rodriguez ***@***.***> wrote: hi there @bernhardF1984 <https://github.com/bernhardF1984> 👋 thanks for taking the time to open this issue, we really appreciate it and agree with your proposed approach! i also really like the idea of raising a warning if there are multiple Accounts Payable (perhaps it could be automatically disabled if you provide the "real" accounts payable n the dbt_project.yml). Couple of follow up questions for ya: 1. On your end, how do you determine which is the appropriate Accounts Payable to use? is it something you just know internally, or could the package somehow programmatically figure it out? I imagine it's probably the former, but wanted to check first 2. We have similar logic in the following models, where we assume there is only one account of the respective type and perform a cartesian join. I assume we would also introduce variables to configure the "real" accounts of these types as well -- are you seeing duplicates from these models? - int_quickbooks__credit_memo_double_entry <https://github.com/fivetran/dbt_quickbooks/blob/main/models/double_entry_transactions/int_quickbooks__credit_memo_double_entry.sql#L39> - Accounts Payable - int_quickbooks__deposit_double_entry <https://github.com/fivetran/dbt_quickbooks/blob/main/models/double_entry_transactions/int_quickbooks__deposit_double_entry.sql#L34> - UndepositedFunds - int_quickbooks__payment_double_entry <https://github.com/fivetran/dbt_quickbooks/blob/main/models/double_entry_transactions/int_quickbooks__payment_double_entry.sql#L33> - Accounts Receivable - int_quickbooks__invoice_double_entry <https://github.com/fivetran/dbt_quickbooks/blob/main/models/double_entry_transactions/int_quickbooks__invoice_double_entry.sql#L93> - Accounts Receivable We'll get started on this next sprint, which begins mid next week 🤠 — Reply to this email directly, view it on GitHub <#97 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A7XPLJ2KGCVAT7574DOK523XQ3RYHANCNFSM6AAAAAA2L4QP4U> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[fivetran-avinash]

Hi @bernhardF1984 ! We're in the process of deploying a fix. If you want to test this before we deploy it live, try pointing to this branch in your packages.yml rather than the default dbt package, and try configuring your account type values in your dbt_project.yml by following the instructions in the new README?

packages
  - git: https://github.com/fivetran/dbt_quickbooks.git 
     revision: bugfix/set-account-type-variables
     warn-unpinned: false

If not, we should be deploying this live by next week!

[bernhardF1984]

Thanks, I'll check it out!

…

On Wed, Aug 2, 2023 at 2:48 AM Avinash Kunnath ***@***.***> wrote: Hi @bernhardF1984 <https://github.com/bernhardF1984> ! We're in the process of deploying a fix. If you want to test this before we deploy it live, try pointing to this branch in your packages.yml rather than the default dbt package, and try configuring your account type values in your dbt_project.yml by following the instructions in the new README? packages - git: https://github.com/fivetran/dbt_quickbooks.git revision: bugfix/set-account-type-variables warn-unpinned: false If not, we should be deploying this live by next week! — Reply to this email directly, view it on GitHub <#97 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A7XPLJ3LIZDKRNVSKZWEFM3XTGITTANCNFSM6AAAAAA2L4QP4U> . You are receiving this because you were mentioned.Message ID: ***@***.***>