This purpose of this report is to provide a summary of what was spent by purchase order line on an invoice line across all the invoices within a particular time period. Prorated invoice line adjustments are included in the invoice line total. The "invoice_line_total_sum" column shows the sum of invoice line totals associated with each purchase order line number. This query only shows invoice line data that is linked to a purchase order. Note that there must be a po line for every invoice line. The results are aggregated by purchase order line id, purchase order line number, invoice_line_total_sum, invoice status, and invoice payment date, and vendor invoice number. Results may be filtered by invoice payment date and invoice status. The invoice line total calculations include everything except non-prorated adjustments to invoices at the invoice level.For a version of this query that provides nonprorated adjustments, see Option C of the ACRL Collection Expenditures reports.
The parameters in the table below can be set in the WITH clause to filter the report output.
| parameter | description | options |
|---|---|---|
| invoice_payment_date | date invoice was paid | Set start_date and end_date in YYYY-MM-DD format. |
| invoice_status | status of the invoices to show, which can be open, reviewed, approved, paid, or cancelled | Set "invoice_status" to "Open," "Reviewed," "Approved," "Paid" or "Cancelled." |
| po_line_id | po_line_number | invoice_line_total_sum | invoice_status | invoice_payment_date | vendor_invoice_number |
|---|---|---|---|---|---|
| 01d8a061-0865-4660-9e5c-7b837544748a | 10004-1 | 15.76 | Approved | test1 | |
| 1e4196bb-7856-4b33-aef5-2b2bd0eb70cf | 10000-1 | 12060 | Paid | 2/18/2021 | BMJ 2018 |
| 1e4196bb-7856-4b33-aef5-2b2bd0eb70cf | 10000-1 | Reviewed | BMJ 2019 |