Table of Contents
Acquisitions Reports
This page includes reports created by different Evergreen consortia that might be useful. The templates have attribution in case you would like to contact that consortia to ask any questions about the reports.
Report Sources
Beyond the list of Core Sources, the list of reports sources is very long. These are some suggested report sources to start with for Acquisitions reports.
- Debit from Fund
- EDI Account
- Fund
- Funding Source
- Invoice
- Invoice Entry
- Line Item
- Line Item Detail
- Purchase Order
Sample Templates
Copies
Orphaned Barcodes
Created by PINES
Locates copies in the catalog where the associated lineitem has been deleted, but the copy wasn't also deleted. Uses nullability.
(Click on the images to see larger versions.)
NOTE: The filter on this report relies on the acquisition barcode containing "ACQ." Replace that value with some consistent prefix that you use for your acquisitions barcodes, if different.
Barcodes Left after True Cancels
Created by PINES
Locates copies in the catalog where the associated line item was cancelled, but the copy was not automatically deleted. Uses nullability.
(Click on the images to see larger versions.)
NOTE: The filter on this report relies on the acquisition barcode containing "ACQ." Replace that value with some consistent prefix that you use for your acquisitions barcodes, if different.
Direct Charges
Direct charges on purchase orders are called PO Items in the reporter/database. Direct charges on invoices are called Invoice Items in the reporter/database. There currently are not reporter sources for each of these, so to find PO Items, start from the Purchase Order source. To find Invoice Items, start from the Invoice source.
Sum of direct charges invoiced by a provider
Created by PINES
Uses nullability.
Note from creator: I've used nullability on this report, but I can't remember if it was truly necessary or not. You might try creating this without using nullability and see if you get the same results. It's only using "inner" joins, which are the "None nullable" option when using nullability in the reporter.
(Click on the images to see larger versions)
Filters
Display Fields
Line Items
Line Items that have been invoiced but not received
Created by BC Libraries Cooperative - Sitka
(Click on the images to see larger versions.)
Line Items that have been received but not invoiced
Created by BC Libraries Cooperative - Sitka
(Click on the images to see larger versions.)
Line Items with the status of on-order
Created by BC Libraries Cooperative - Sitka
(Click on the images to see larger versions.)
Line Items linked to delete bib records
Created by BC Libraries Cooperative - Sitka
(Click on the images to see larger versions.)
Funds / Fund Debits
Fund Debit SQL query
Created by PINES
SELECT debit.id AS Fund_Debit_ID, TO_CHAR(debit.create_time,'MM-DD-YYYY') AS Debit_Creation_Time, amount, encumbrance, debit_type, fund.id AS Fund_ID, fund.code AS Fund_Code, li.purchase_order AS PO_ID, purchases_provider.code AS Provider_on_PO, lid.lineitem AS Lineitem_ID, lid.id AS Lineitem_Detail_ID, TO_CHAR(invoice.recv_date,'MM-DD-YYYY') AS Invoice_date_for_purchases, invoice.id AS Internal_Invoice_ID_for_purchases, invoice.inv_ident AS Vendor_Invoice_ID_for_purchases, podirectcharge.purchase_order AS PO_ID_for_direct_charge, podirectcharge.id AS Direct_Charge_ID_on_purchase_order, TO_CHAR(inv2.recv_date,'MM-DD-YYYY') AS Invoice_Date_for_direct_charge, invoicedirectcharge.id AS Direct_Charge_ID_on_invoice, inv2.inv_ident AS Vendor_Invoice_ID_for_direct_charge, provider.code AS Provider_on_Invoice_for_direct_charge FROM acq.fund_debit debit JOIN acq.fund fund ON debit.fund=fund.id LEFT JOIN acq.invoice_entry invlineitem ON debit.invoice_entry=invlineitem.id LEFT JOIN acq.invoice AS invoice ON invlineitem.invoice=invoice.id LEFT JOIN acq.lineitem_detail lid ON debit.id=lid.fund_debit LEFT JOIN acq.po_item podirectcharge ON debit.id=podirectcharge.fund_debit LEFT JOIN acq.invoice_item invoicedirectcharge ON debit.id=invoicedirectcharge.fund_debit LEFT JOIN acq.lineitem li ON lid.lineitem=li.id LEFT JOIN acq.invoice AS inv2 ON invoicedirectcharge.invoice=inv2.id LEFT JOIN acq.provider provider ON provider.id=inv2.provider LEFT JOIN acq.provider purchases_provider ON purchases_provider.id=li.provider WHERE fund.org=140 AND YEAR=2020;
Fund report by year
Created by BC Libraries Cooperative - Sitka
(Click on the images to see larger versions.)
Purchase Orders
Claiming
Claim ready items
Created by BC Libraries Cooperative - Sitka
(Click on the images to see larger versions.)
EDI
EDI-enabled POs ordered yesterday
Submitted by PINES
Display fields
Filters
EDI messages from yesterday
Display fields
Filters
If you set these two reports to run as recurring every day after midnight, you can then create a spreadsheet to pair them to find if there are any errors in your purchase orders or invoices, and if you received a corresponding ORDRSP for each of your purchase orders. The spreadsheet also checks that the ORDERS message and ORDRSP message came through on the same EDI account, so it's checking that there's not a mismatch between those. For invoices, you can check to make sure that there wasn't a proc_error on any of the invoices that came in the day before.
The spreadsheet has three tabs, the result tab (below), one for POs ordered yesterday, and one for yesterday's EDI messages. You can just open the HTML version of each report and then copy and paste it directly into its corresponding tab, and the formulas in the spreadsheet do the rest of the work.
An example spreadsheet might look like this:
If you would like to try to use the spreadsheet, a copy of mine is available here and you can make a copy for yourself. You're welcome to contact me (Tiffany Little at PINES) with any questions on the reports or spreadsheet.