Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[Feature] Incorporate Tax Lines into QBO Transform #153

Open
2 of 4 tasks
brandonrf94 opened this issue Jan 23, 2025 · 0 comments
Open
2 of 4 tasks

[Feature] Incorporate Tax Lines into QBO Transform #153

brandonrf94 opened this issue Jan 23, 2025 · 0 comments
Labels
type:enhancement New functionality or enhancement

Comments

@brandonrf94
Copy link

Is there an existing feature request for this?

  • I have searched the existing issues

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.

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
type:enhancement New functionality or enhancement
Projects
None yet
Development

No branches or pull requests

1 participant