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.

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

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.

Filters:

Display:

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.

Filters:

Display:

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.

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

Created by BC Libraries Cooperative - Sitka

(Click on the images to see larger versions.)

Filters

Display Fields

Created by BC Libraries Cooperative - Sitka

(Click on the images to see larger versions.)

Filters

Display Fields

Created by BC Libraries Cooperative - Sitka

(Click on the images to see larger versions.)

Filters

Display Fields

Created by BC Libraries Cooperative - Sitka

(Click on the images to see larger versions.)

Filters

Display Fields

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;

Created by BC Libraries Cooperative - Sitka

(Click on the images to see larger versions.)

Filters

Display Fields

Created by BC Libraries Cooperative - Sitka

(Click on the images to see larger versions.)

Filters

Display Fields

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.

List Invoices by Date Range

Submitted by Bibliomation, Inc.

This report lists invoices with fund information; a sum of direct charges, taxes, fees, etc paid; and the total paid for items.

Click the image to see an enlarged version.

Display Fields:

Filters: