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

[brandonrf94]

Is there an existing issue for this?

• [X] 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:

https://github.com/fivetran/dbt_quickbooks/blob/e604697a51880e2f57bc89606517bb499eb6a017/models/double_entry_transactions/int_quickbooks__sales_receipt_double_entry.sql#L39 & https://github.com/fivetran/dbt_quickbooks/blob/e604697a51880e2f57bc89606517bb499eb6a017/models/transaction_lines/int_quickbooks__sales_receipt_transactions.sql#L41 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.