User Tools

Site Tools


acq:reports

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.

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

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.)

Filters

Display Fields

Line Items that have been received but not invoiced

Created by BC Libraries Cooperative - Sitka

(Click on the images to see larger versions.)

Filters

Display Fields

Line Items with the status of on-order

Created by BC Libraries Cooperative - Sitka

(Click on the images to see larger versions.)

Filters

Display Fields

Line Items linked to delete bib records

Created by BC Libraries Cooperative - Sitka

(Click on the images to see larger versions.)

Filters

Display Fields

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.)

Filters

Display Fields

Purchase Orders

Claiming

Claim ready items

Created by BC Libraries Cooperative - Sitka

(Click on the images to see larger versions.)

Filters

Display Fields

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.

Invoices

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:

acq/reports.txt · Last modified: 2022/11/15 10:37 by tlittle

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki

© 2008-2022 GPLS and others. Evergreen is open source software, freely licensed under GNU GPLv2 or later.
The Evergreen Project is a U.S. 501(c)3 non-profit organization.