QBO has _tax_line tables available from fivetran, anyone using the tax featureset of QBO and leveraging Fivetran transforms will have shortages on the BS and I imagine income statement too.
This query will give visibility into the type of data that is likely missing from the transformation GL.
with data as(
select
COALESCE(a.account_number || ' ', '') || COALESCE(a.name, 'No Account Name') AS "GL Account Name",
a.name "GL Account Name (no number)",
a.account_number "GL Account Number",
a.account_Type "Account Type",
a.account_sub_type "Account Subtype",
tx.amount,
tx.tax_rate_id,
s.sales_item_account_id account_id,
d.transaction_date,
'Sales Receipt' tax_transaction_type,
from
quickbooks.sales_receipt_tax_line tx
join quickbooks.sales_receipt_line s on tx.sales_receipt_id = s.sales_receipt_id
and tx.index = s.index
join quickbooks.refund_receipt d on d.id = s.sales_receipt_id
join quickbooks.account a on s.sales_item_account_id = a.id
UNION ALL
select
COALESCE(a.account_number || ' ', '') || COALESCE(a.name, 'No Account Name') AS "GL Account Name",
a.name "GL Account Name (no number)",
a.account_number "GL Account Number",
a.account_Type "Account Type",
a.account_sub_type "Account Subtype",
tx.amount,
tx.tax_rate_id,
s.sales_item_account_id account_id,
d.transaction_date,
'Refund Receipt' tax_transaction_type,
from
quickbooks.refund_receipt_tax_line tx
join quickbooks.refund_receipt_line s on tx.refund_receipt_id = s.refund_id
and tx.index = s.index
join quickbooks.refund_receipt d on d.id = s.refund_id
join quickbooks.account a on s.sales_item_account_id = a.id
UNION ALL
/*select
COALESCE(a.account_number || ' ', '') || COALESCE(a.name, 'No Account Name') AS "GL Account Name",
a.name "GL Account Name (no number)",
a.account_number "GL Account Number",
a.account_Type "Account Type",
a.account_sub_type "Account Subtype",
tx.amount,
tx.tax_rate_id,
s.account_expense_account_id account_id,
d.transaction_date,
'Purchase Order' tax_transaction_type,
from
quickbooks.purchase_order_tax_line tx
join quickbooks.purchase_order_line s on tx.purchase_order_id = s.purchase_order_id
and tx.index = s.index
join quickbooks.purchase_order d on d.id = s.purchase_order_id
join quickbooks.account a on s.account_expense_account_id = a.id
UNION ALL*/
select
COALESCE(a.account_number || ' ', '') || COALESCE(a.name, 'No Account Name') AS "GL Account Name",
a.name "GL Account Name (no number)",
a.account_number "GL Account Number",
a.account_Type "Account Type",
a.account_sub_type "Account Subtype",
tx.amount,
tx.tax_rate_id,
s.account_id account_id,
d.transaction_date,
'Journal Entry' tax_transaction_type,
from
quickbooks.journal_entry_tax_line tx
join quickbooks.journal_entry_line s on tx.journal_entry_id = s.journal_entry_id
and tx.index = s.index
join quickbooks.journal_entry d on d.id = s.journal_entry_id
join quickbooks.account a on s.account_id = a.id
UNION ALL
select
COALESCE(a.account_number || ' ', '') || COALESCE(a.name, 'No Account Name') AS "GL Account Name",
a.name "GL Account Name (no number)",
a.account_number "GL Account Number",
a.account_Type "Account Type",
a.account_sub_type "Account Subtype",
tx.amount,
tx.tax_rate_id,
s.sales_item_account_id account_id,
d.transaction_date,
'Invoice' tax_transaction_type,
from
quickbooks.invoice_tax_line tx
join quickbooks.invoice_line s on tx.invoice_id = s.invoice_id
and tx.index = s.index
join quickbooks.invoice d on d.id = s.invoice_id
join quickbooks.account a on s.sales_item_account_id = a.id
UNION ALL
select
COALESCE(a.account_number || ' ', '') || COALESCE(a.name, 'No Account Name') AS "GL Account Name",
a.name "GL Account Name (no number)",
a.account_number "GL Account Number",
a.account_Type "Account Type",
a.account_sub_type "Account Subtype",
tx.amount,
tx.tax_rate_id,
s.sales_item_account_id account_id,
d.transaction_date,
'Estimate' tax_transaction_type,
from
quickbooks.estimate_tax_line tx
join quickbooks.estimate_line s on tx.estimate_id = s.estimate_id
and tx.index = s.index
join quickbooks.estimate d on d.id = s.estimate_id
join quickbooks.account a on s.sales_item_account_id = a.id
UNION ALL
select
COALESCE(a.account_number || ' ', '') || COALESCE(a.name, 'No Account Name') AS "GL Account Name",
a.name "GL Account Name (no number)",
a.account_number "GL Account Number",
a.account_Type "Account Type",
a.account_sub_type "Account Subtype",
tx.amount,
tx.tax_rate_id,
s.account_expense_account_id account_id,
b.transaction_date,
'Bill' tax_transaction_type
from
quickbooks.bill b
join quickbooks.purchase_order_linked_txn bpo on bpo.bill_id = b.id
join quickbooks.purchase_order_tax_line tx on tx.purchase_order_id = bpo.purchase_order_id
join quickbooks.purchase_order_line s on tx.purchase_order_id = s.purchase_order_id
and tx.index = s.index
join quickbooks.purchase_order d on d.id = s.purchase_order_id
join quickbooks.account a on s.account_expense_account_id = a.id
)
select
t.name tax_type,
t.rate_value tax_rate,
ta.display_name tax_agency,
d.*
from
data d
join quickbooks.tax_rate t on d.tax_rate_id = t.id
join quickbooks.tax_agency ta on ta.id = t.tax_agency_id
Will share a recorded video in a private chat.
Potentially add a new double entry model which captures tax amounts.
Is there an existing feature request for this?
Describe the Feature
QBO has _tax_line tables available from fivetran, anyone using the tax featureset of QBO and leveraging Fivetran transforms will have shortages on the BS and I imagine income statement too.
This query will give visibility into the type of data that is likely missing from the transformation GL.
Will share a recorded video in a private chat.
How would you implement this feature?
Potentially add a new double entry model which captures tax amounts.
Describe alternatives you've considered
No response
Are you interested in contributing this feature?
Anything else?
No response