fivetran / fivetran/dbt_quickbooks
[Bug] Sales Receipt Tax Amounts not flowing back up to the GL/IS/BS
- 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