[Bug] Bill Payments using the wrong FX rate

[brandonrf94]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

According to Quickbooks it looks like when a bill payment is applied, the FX rate that should be used is the FX rate of the originating bill when possible. If you use the FX rate of the bill_payment then the accounts payable account will not match what QBO expects.

Relevant error log or model output

No response

Expected behavior

AP account balances with QBO.

Possible solution

I have my own local fork of this repo and I will share the code I use. Note it looks a little different than your version of the
"int_quickbooks__bill_payment_Double_entry.sql" as I have deviated a bit from your model due to ongoing changes.

I essentially join to the bill table and use the FX rate from there if I am able to successfully join, then I fall back to the bill payment FX rate.

/*
Table that creates a debit record to accounts payable and a credit record to the specified cash account.
*/

--To disable this model, set the using_bill_payment variable within your dbt_project.yml file to False.
{{ config(enabled=var('using_bill', True)) }}

with bill_payments as (

    select *
    from {{ ref('stg_quickbooks__bill_payment') }}
),

bill_payment_lines as (

    select *
    from {{ ref('stg_quickbooks__bill_payment_line') }}
),

accounts as (

    select *
    from {{ ref('stg_quickbooks__account') }}
),

bills as (

    select *
    from {{ ref('stg_quickbooks__bill') }}
),

bill_linked_txn as (

    select *
    from {{ ref('stg_quickbooks__bill_linked_txn') }}
),

ap_accounts as (

    select
        account_id,
        source_relation,
        currency_id
    from accounts

    where account_type = '{{ var('quickbooks__accounts_payable_reference', 'Accounts Payable') }}'
        and is_active
        and not is_sub_account
),

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, bill_payments.source_relation 
            order by bill_payments.source_relation, bill_payments.transaction_date) - 1 as index,
        bill_payments.transaction_date,
        (bill_payment_lines.amount*coalesce(coalesce(bills.exchange_rate,bill_payments.exchange_rate),1)) as amount,
        bill_payment_lines.amount unexchanged_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

    join bill_payment_lines
        on bill_payment_lines.bill_payment_id = bill_payments.bill_payment_id
        and bill_payment_lines.source_relation = bill_payments.source_relation

    left join ap_accounts
        on ap_accounts.source_relation = bill_payments.source_relation
        and ap_accounts.currency_id = bill_payments.currency_id
    
    left join bill_linked_txn
        on bill_linked_txn.bill_payment_id = bill_payments.bill_payment_id
        and bill_linked_txn.source_relation = bill_payments.source_relation
        and bill_payment_lines.bill_id = bill_linked_txn.bill_id

    left join bills
        on bills.bill_id = bill_linked_txn.bill_id
        and bills.source_relation = bill_payments.source_relation
),

final as (

    select
        transaction_id,
        source_relation,
        index,
        transaction_date,
        cast(null as {{ dbt.type_string() }}) as customer_id,
        vendor_id,
        amount,
        unexchanged_amount,
        payment_account_id as account_id,
        cast(null as {{ dbt.type_string() }}) as class_id,
        department_id,
        'credit' as transaction_type,
        'bill payment' as transaction_source
    from bill_payment_join

    union all

    select
        transaction_id,
        source_relation,
        index,
        transaction_date,
        cast(null as {{ dbt.type_string() }}) as customer_id,
        vendor_id,
        amount,
        unexchanged_amount,
        account_id,
        cast(null as {{ dbt.type_string() }}) as class_id,
        department_id,
        'debit' as transaction_type,
        'bill payment' as transaction_source
    from bill_payment_join
)

select *
from final

dbt Project configurations

quickstart

Package versions

quickstart most recent

What database are you using dbt with?

snowflake

How are you running this dbt package?

Fivetran Quickstart Data Model, dbt Core™

dbt Version

most recent

Additional Context

No response

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance.
• No.

[brandonrf94]

My code isn't accurate, it's closer in some ways but not 100% there.

A single bill payment can be associated with multiple bills, and there seems to be some QBO logic where the sum of all the bill_payment_lines is not equal to the bill_payment total amount in many cases.

This is still an issue, if anyone has any ideas on how to do this - happy to collab.

[fivetran-avinash]

Hey @brandonrf94 , thanks for opening this issue! The logic makes sense from my end, just a few clairifying questions:

You use bill_payment_lines.amount, and we use bill_payments.total_amount. What are the advantages to using bill_payment_lines.amount in this case?

For (bill_payment_lines.amount*coalesce(coalesce(bills.exchange_rate,bill_payments.exchange_rate),1)) as amount, should we use the second coalesce on bill_payments.exchange_rate since you said it returns the improper amount? Or does it seem to return the proper amount when bills.exchange_rate is null?

Re: your last comment, is that a separate issue from the wrong FX rate? Do you want to open a separate issue if that's the case? And do you see any patterns for when the sum of the bill payment lines does not equate to the bill payment total amount?

[brandonrf94]

Hi @fivetran-avinash -

I switched from using bill_payments.total_amount to bill_payment_lines.amount because of a few reasons:

1. A single bill payment can be associated to multiple bills, the bill_ids that a bill payment is associated to lives in the bill_payment_line
2. Because of 1) you then need to use the bill_payment_line amount otherwise you'll just duplicate the amount by the number of lines

I think these 2 things are related:

1. We need to use the bill FX rate for a bill payment to get the right FX rate
2. We need to find a way to sum up the bill_payment_line.amount to match the bill_payment.total_amount in order to do 1.

I don't think it's a bug but I also couldn't find any explicit pattern on how to get the lines to match the header. It likely has to do with the types of transactions the bill_payment is being tied to.

Just to clarify what I think makes this harder..
A single bill could be paid by multiple bill payments
A single bill payment can be associated to multiple bills