Weld logo
xero logo
Xero

Tracking Details Report

This template generates a report that brings together tracking details from journal and invoice records, outlining essential details including source type, source ID, and financial particulars such as net and gross amounts, tax details, and tracking categories.
1with
2    JournalTracking as (
3        select
4            'Journal' as source
5          , journal_id as source_id
6          , description
7          , net_amount
8          , gross_amount
9          , tax_amount
10          , tax_type
11          , tax_name
12          , tracking_categories as tracking
13        from
14            {{raw.xero.journal_line}}
15    )
16  , -- Next, we'll collect data from the invoice_line_item table
17    InvoiceTracking as (
18        select
19            'Invoice' as source
20          , invoice_id as source_id
21          , description
22          , null as net_amount
23          , line_amount as gross_amount
24          , tax_amount
25          , tax_type
26          , null as tax_name
27          , tracking
28        from
29            {{raw.xero.invoice_line_item}}
30    )
31    -- Now, we'll union these two CTEs to create the report
32select
33    source
34  , source_id
35  , description
36  , net_amount
37  , gross_amount
38  , tax_amount
39  , tax_type
40  , tax_name
41  , tracking
42from
43    JournalTracking
44union all
45select
46    source
47  , source_id
48  , description
49  , net_amount
50  , gross_amount
51  , tax_amount
52  , tax_type
53  , cast(tax_name as string)
54  , tracking
55from
56    InvoiceTracking
57order by
58    source
59  , tracking
Example of output from model:
+--------+----------+-------------+-----------+-------------+------------+----------+----------+----------+
| source | source_id | description | net_amount | gross_amount | tax_amount | tax_type | tax_name | tracking |
+--------+----------+-------------+-----------+-------------+------------+----------+----------+----------+
| Journal | J12345   | Desc1       | 1000.00    | 1150.00      | 150.00     | TAX001   | TaxName1 | Track1   |
| Invoice | I12345   | Desc2       | null       | 2000.00      | 200.00     | TAX002   | null     | Track2   |
+--------+----------+-------------+-----------+-------------+------------+----------+----------+----------+

This SQL template creates a unified view of tracking details stemming from both journal and invoice records in the Xero accounting system. Utilizing Common Table Expressions (CTEs) to segregate data from journal lines and invoice line items separately, it then combines them to present a holistic report. This detailed report not only outlines the financial particulars such as net amount, gross amount, and tax details but also categorizes them based on their source, aiding in a comprehensive and streamlined financial tracking and reporting.

Ready to start modeling your own xero data?

Get started building your data warehouse with xero and connect all your apps and databases.

xero logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.