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 , trackingExample 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.
