You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
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?
Yes.
Yes, but I will need assistance.
No.
Anything else?
No response
The text was updated successfully, but these errors were encountered:
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
The text was updated successfully, but these errors were encountered: