-
Notifications
You must be signed in to change notification settings - Fork 21
/
xero__invoice_line_items.sql
62 lines (47 loc) · 1.38 KB
/
xero__invoice_line_items.sql
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
with line_items as (
select *
from {{ var('invoice_line_item') }}
), invoices as (
select *
from {{ var('invoice') }}
), accounts as (
select *
from {{ var('account') }}
), contacts as (
select *
from {{ var('contact') }}
), joined as (
select
line_items.*,
invoices.invoice_date,
invoices.updated_date,
invoices.planned_payment_date,
invoices.due_date,
invoices.expected_payment_date,
invoices.fully_paid_on_date,
invoices.currency_code,
invoices.currency_rate,
invoices.invoice_number,
invoices.is_sent_to_contact,
invoices.invoice_status,
invoices.type,
invoices.url,
invoices.reference as invoice_reference,
accounts.account_id,
accounts.account_name,
accounts.account_type,
accounts.account_class,
contacts.contact_name
from line_items
left join invoices
on (line_items.invoice_id = invoices.invoice_id
and line_items.source_relation = invoices.source_relation)
left join accounts
on (line_items.account_code = accounts.account_code
and line_items.source_relation = accounts.source_relation)
left join contacts
on (invoices.contact_id = contacts.contact_id
and invoices.source_relation = contacts.source_relation)
)
select *
from joined