[Bug] Sales Receipt Tax Amounts not flowing back up to the GL/IS/BS

[brandonrf94]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Right now if there is a sales receipt that has a tax amount, that amount is not flowing through the DBT transformations. I rolled out a change on my local repo that you may want to consider:

dbt_quickbooks/models/double_entry_transactions/int_quickbooks__sales_receipt_double_entry.sql

Line 39 in e604697

sales_receipt_lines.amount,

&

dbt_quickbooks/models/transaction_lines/int_quickbooks__sales_receipt_transactions.sql

Line 41 in e604697

sales_receipt_lines.amount,

Replace
sales_receipt_lines.amount,
with

        case 
            when sales_receipt_lines.index = 0 then (sales_receipt_lines.amount + sales_receipts.total_tax)
            else sales_receipt_lines.amount 
        end amount,

The idea being to just apply the taxable amount to the first line item. There may be a better solution to evenly distribute across the lines, but this seemingly does that trick just fine.

Additionally,
Here: https://github.com/fivetran/dbt_quickbooks_source/blob/7a2ef84dfc79bb441a059bdac13bb252edc119e9/models/stg_quickbooks__sales_receipt.sql#L42
You would need to also select
total_tax

Relevant error log or model output

No response

Expected behavior

The amounts for sales receipts should be inclusive of the tax amount.
Tax amount is only stored on the sales_receipt header table, not the sales_receipt_line table.

dbt Project configurations

Default

Package versions

Most recent

What database are you using dbt with?

snowflake

dbt Version

N.A

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

Hi @brandonrf94 thanks for opening this issue.

After looking over your description, I definitely see the need to incorporate the tax amount into the sales receipt. Additionally, thank you for contributing your code suggestions to address this gap. My only concern with this approach is we will be associating the tax amount with the sale amount. Could this then result in incorporating the tax amount as revenue downstream? To handle this, should we instead be separating out the tax amount as a separate line item which is attributed to a tax account or to the respective sales receipt account as a tax entry?

Would you be able to provide an example sales receipt which has a tax amount associated with it and share how you would expect that tax amount to be recorded downstream? Let me know your thoughts, thanks!

[brandonrf94]

sales_receipt.csv
sales_receipt_line.csv

here's the 2 files. You'll see in the sales receipt line it's $500 and the tax from the sales_receipt is 42.50.

As for downstream how it'd be recorded, according to an export in Quickbooks it just shows the full amount aggregated together on the account.

[fivetran-joemarkiewicz]

Thanks for sharing @brandonrf94! Those files are helpful in seeing how these tax amounts are looking on your end.

As for downstream how it'd be recorded, according to an export in Quickbooks it just shows the full amount aggregated together on the account.

Just to confirm on the above statement, are you saying that the credit and debit accounts for these sales receipts should in fact be $542.50 in the final GL entry? After looking through some QuickBooks docs this does seem accurate that the $42.50 in this case would be attributed to the revenue from the sales receipt. However, we should see this $42.50 as a liability to the sales tax payable account. Can you confirm, do you see this $42.50 as maybe a journal entry to a similar sales tax payable account? If we can, then I feel comfortable with this approach as it will attribute the sales tax to revenue, but then we can gain comfort that there is an offsetting journal entry to list the tax as a payable.

[brandonrf94]

I believe I see it attributed to an Accrued Expense account on the journal entry table.
It seems like this may have been an accident on the customers part (having tax amounts on the sales receipt) because there are comments about reversing it - but regardless it is throwing things off still down the line.

[fivetran-joemarkiewicz]

Thanks for the additional details and mentioning that you do see a possible related entry on the JE table. While this case may have seemingly been a mistake on the customers part, I do believe this is a feature of QuickBooks to allow taxes to be applied in this way on a sales receipt. Do you know if this tax amount for the sales receipt in question was ever reversed?

I want to be cautious when applying this proposed update in case this doesn't apply for all QuickBooks use cases. Although it does seem that this is a native feature of QuickBooks (after doing some searching in QuickBooks forums) and we should support it. I know you mentioned the export includes the full amount with tax included, but would you also be able to confirm that the debit/credit accounts for this sales receipt transaction look accurate with the tax amount included?

[fivetran-reneeli]

Hi @brandonrf94 taking over for Joe! Let me know if you had a chance to look into Joe's question above or if you have any suggestions.

[fivetran-reneeli]

@brandonrf94 let us know if you're able to take a look at Joe's comments. We'll mark this as stale for the time being but feel free to comment on this when you have time and we'll take a look then!