fivetran / fivetran/dbt_quickbooks

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

Open
#132 8 comments 0 reactions 0 assignees View on GitHub
error:forced status:stale type:bug
Dominant language
No language data
Stars
41
Forks
46
PR merge metrics
No merged PRs in 30d

Description

### 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](https://calendly.com/fivetran-solutions-team/fivetran-solutions-team-office-hours) for guidance
- [ ] No.

Contributor guide

No contributing guide indexed for this repository

Research direction

Start with models/transaction_lines/int_quickbooks__sales_receipt_transactions.sql and models/double_entry_transactions/int_quickbooks__sales_receipt_double_entry.sql, then inspect models/stg_quickbooks__sales_receipt.sql in dbt_quickbooks_source. Verify how sales_receipt totals and lines flow through the transformations. Done means sales receipt amounts include header tax in the GL/IS/BS outputs, with the relevant dbt models still compiling and producing the expected results.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
data-engineering
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.