dbt_quickbooks/models/double_entry_transactions/int_quickbooks__purchase_double_entry.sql at f3d9824585ada713db3486545b4337751200f140 · fivetran/dbt_quickbooks · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
/*
Table that creates a debit record to a specified expense account and a credit record to the payment account.
*/
with purchases as (

    select *
    from {{ ref('stg_quickbooks__purchase') }}
),

purchase_lines as (

    select *
    from {{ ref('stg_quickbooks__purchase_line') }}
),

{% if var('using_purchase_tax_line', False) %}

purchase_tax_lines as (

    select purchase_id,
        source_relation,
        index + 10000 as index,
        tax_rate_id,
        amount,
        tax_percent
    from {{ ref('stg_quickbooks__purchase_tax_line') }}
),
{% endif %}

items as (

    select
        item.*,
        parent.expense_account_id as parent_expense_account_id
    from {{ ref('stg_quickbooks__item') }} item

    left join {{ ref('stg_quickbooks__item') }} parent
        on item.parent_item_id = parent.item_id
        and item.source_relation = parent.source_relation
),

purchase_join as (

    select
        purchases.purchase_id as transaction_id,
        purchases.source_relation,
        purchase_lines.index,
        purchases.transaction_date,
        purchase_lines.amount,
        case
            when purchases.currency_id = '{{ var('quickbooks__home_currency', '') }}'
                then purchase_lines.amount
            else purchase_lines.amount * coalesce(purchases.exchange_rate, 1)
        end as converted_amount,
        coalesce(purchase_lines.account_expense_account_id, items.parent_expense_account_id, items.expense_account_id) as paid_to_account_id,
        purchases.account_id as paid_from_account_id,
        cast(case when coalesce(purchases.credit, false) = true then 'debit' else 'credit' end as {{ dbt.type_string() }}) as paid_from_transaction_type,
        cast(case when coalesce(purchases.credit, false) = true then 'credit' else 'debit' end as {{ dbt.type_string() }}) as paid_to_transaction_type,
        purchases.customer_id,
        coalesce(purchase_lines.item_expense_class_id, purchase_lines.account_expense_class_id) as class_id,
        purchases.vendor_id,
        purchases.department_id,
        purchases.created_at,
        purchases.updated_at
    from purchases

    inner join purchase_lines
        on purchases.purchase_id = purchase_lines.purchase_id
        and purchases.source_relation = purchase_lines.source_relation

    left join items
        on purchase_lines.item_expense_item_id = items.item_id
        and purchase_lines.source_relation = items.source_relation

    {% if var('using_purchase_tax_line', False) %}
    union all

    select
        purchase_tax_lines.purchase_id as transaction_id,
        purchase_tax_lines.source_relation,
        purchase_tax_lines.index,
        purchases.transaction_date,
        purchase_tax_lines.amount,
        case
            when purchases.currency_id = '{{ var('quickbooks__home_currency', '') }}'
                then purchase_tax_lines.amount
            else purchase_tax_lines.amount * coalesce(purchases.exchange_rate, 1)
        end as converted_amount,
        coalesce(purchase_lines.account_expense_account_id, items.parent_expense_account_id, items.expense_account_id) as paid_to_account_id,
        purchases.account_id as paid_from_account_id,
        cast(case when coalesce(purchases.credit, false) = true then 'debit' else 'credit' end as {{ dbt.type_string() }}) as paid_from_transaction_type,
        cast(case when coalesce(purchases.credit, false) = true then 'credit' else 'debit' end as {{ dbt.type_string() }}) as paid_to_transaction_type,
        purchases.customer_id,
        coalesce(purchase_lines.item_expense_class_id, purchase_lines.account_expense_class_id) as class_id,
        purchases.vendor_id,
        purchases.department_id,
        purchases.created_at,
        purchases.updated_at
    from purchase_tax_lines
    inner join purchases
        on purchases.purchase_id = purchase_tax_lines.purchase_id
        and purchases.source_relation = purchase_tax_lines.source_relation
    inner join purchase_lines
        on purchases.purchase_id = purchase_lines.purchase_id
        and purchases.source_relation = purchase_lines.source_relation
    left join items
        on purchase_lines.item_expense_item_id = items.item_id
        and purchase_lines.source_relation = items.source_relation
    {% endif %}
),

final as (

    select
        transaction_id,
        source_relation,
        index,
        transaction_date,
        customer_id,
        vendor_id,
        amount,
        converted_amount,
        paid_from_account_id as account_id,
        class_id,
        department_id,
        created_at,
        updated_at,
        paid_from_transaction_type as transaction_type,
        cast('purchase' as {{ dbt.type_string() }}) as transaction_source
    from purchase_join

    union all

    select
        transaction_id,
        source_relation,
        index,
        transaction_date,
        customer_id,
        vendor_id,
        amount,
        converted_amount,
        paid_to_account_id as account_id,
        class_id,
        department_id,
        created_at,
        updated_at,
        paid_to_transaction_type as transaction_type,
        cast('purchase' as {{ dbt.type_string() }}) as transaction_source
    from purchase_join
)

select *
from final