Invoice line items
Retrieves invoice line items from the economic integration. It combines data from the "booked_invoices" table and the "products" and "customers" tables to provide information such as the customer name, invoice details, product details, and order by date in descending order.
- Grain
- See the notes
- Built from
- 3Raw tables
- 1Core
- 1Analytics
- Reads from E-conomic
- booked_invoicescustomersproducts
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.economic.invoice_line_items
2-- Invoice line items. Retrieves invoice line items from the economic integration.
3-- It combines data from the "booked_invoices" table and the "products" and
4-- "customers" tables to provide information such as the customer name, invoice
5-- details, product details, and order by date in descending order.
6--
7-- The line ID is the document number and line number joined with a hyphen,
8-- so invoice 12 line 3 and invoice 1 line 23 stay distinct.
9--
10-- From the weld.app template library, corrected. Not reconciled against
11-- e-conomic; check the output against the source before relying on it.
12
13with invoice_items as ( select date as date , concat(cast(invoiceNumber as string), '-', cast(a.lineNumber as string)) as invoice_line_id , invoiceNumber as invoice_id , i.customer_customerNumber as customer_id , invoiceType as invoice_type , currency , a.quantity as quantity , a.lineNumber as line_id , a.unitNetPrice as unit_net_price , a.discountPercentage as discount_percentage , a.unitCostPrice as unit_cost_price , a.vatRate as vat_rate , a.vatamount as vat_amount , a.totalNetAmount as net_amount_excl_tax , (a.totalNetAmount + a.vatamount) as net_amount_incl_tax , json_value(a.product, '$.productNumber') as product_id from {{raw.economic.booked_invoices}} i left join unnest (lines) as a ) select c.name , i.* from invoice_items i left join {{raw.economic.products}} p on i.product_id = p.productNumber left join {{raw.economic.customers}} c on i.customer_id = c.customerNumber order by date descThis model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().
How it counts. And where it stops.
The SQL template "Invoice line items" is designed to retrieve detailed information about invoice line items from the Economic integration. This SQL model allows you to extract data such as the date, invoice line ID, invoice ID, customer ID, invoice type, currency, quantity, line ID, unit net price, discount percentage, unit cost price, VAT rate, VAT amount, net amount excluding tax, net amount including tax, product ID, customer name, and more. By executing this SQL template, you can gain insights into the line items of invoices, including the products purchased, customer details, pricing information, and invoice specifics. This can be useful for various purposes, such as analyzing sales trends, calculating revenue, identifying popular products, understanding customer preferences, and generating financial reports. The SQL code retrieves the necessary data from the "booked_invoices" table and joins it with the "products" and "customers" tables from the Economic integration. The resulting output is sorted in descending order based on the date of the invoice.
Raw tables it reads
- raw.economic.booked_invoices
- raw.economic.customers
- raw.economic.products
More E-conomic models
Put E-conomic in front
of your agents.
Connect E-conomic, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.