User Tools

Site Tools


acq:reports

Reports

Copies/Items

Copies remaining on true cancelled line items

Use: Finds Acquisitions copies attached to line items with true cancels. Copies should have been deleted; if any found they should be deleted.

Source: Item

Filters: Item→Is Deleted (Filter value=f) Cancel Reason ID (in list)

Columns: Line Item ID PO ID PO Name Barcode

Invoices

Reports can be found in Shared Folders→Templates→PINESacqadmin→Invoices.

Blank invoices

Use: This report finds invoices that are closed with a paid amount of $0.00.

Uses Invoice source.

Displayed fields:
Invoice→Provider→Code (“Provider”)
Invoice→Vendor Invoice ID
Invoice→Invoice Entries→Amount Paid (sum) Invoice→Invoice Items→Amount Paid (sum) Invoice→Receiver→Short (Policy) Name (“Receiver”) Invoice→Complete

Base Filters:
Invoice→Complete (Operator:Equals) (Value=TRUE)
Invoice→Receiver→Organizational Unit ID (Operator: In List)

Aggregate Filters:
Invoice→Invoice Entries→Amount Paid (sum) (Operator: Equals) (Value=0)
Invoice→Invoice Items→Amount Paid (sum) (Operator:Equals) (Value=0)

List of invoices

Use: Find a list of invoices with an invoice date after a certain day.

Uses Invoice source.

Displayed fields:
Invoice→Provider→Provider Name Invoice→Vendor Invoice ID

SELECT * FROM (SELECT “726fa7d8d276b403e875808083a4e1ef”.“inv_vendor_ident” AS “Vendor Invoice ID”,

"726fa7d8d276b403e875808083a4e1ef"."total_amount_paid" AS "Total Amount Paid",
date("726fa7d8d276b403e875808083a4e1ef"."recv_date") AS "Receive Date/Time",
"726fa7d8d276b403e875808083a4e1ef"."provider_name" AS "Provider Name"
FROM    reporter.acq_invoice_summary_view AS "726fa7d8d276b403e875808083a4e1ef"
INNER JOIN acq.invoice AS "3ee6cb3a00e712c7420cb425873cab62" ON ("726fa7d8d276b403e875808083a4e1ef"."inv_id" = "3ee6cb3a00e712c7420cb425873cab62"."id")
WHERE    date("3ee6cb3a00e712c7420cb425873cab62"."recv_date")> $_54631$2016-07-01$_54631$
AND "3ee6cb3a00e712c7420cb425873cab62"."complete" = $_54631$f$_54631$
AND "3ee6cb3a00e712c7420cb425873cab62"."receiver" IN ($_54631$213$_54631$)
GROUP BY 1, 2, 3, 4
ORDER BY "726fa7d8d276b403e875808083a4e1ef"."inv_vendor_ident" ASC, "726fa7d8d276b403e875808083a4e1ef"."total_amount_paid" ASC, date("726fa7d8d276b403e875808083a4e1ef"."recv_date") ASC, "726fa7d8d276b403e875808083a4e1ef"."provider_name" ASC

) limited_to_1048575_hits LIMIT 1048575

Completed Invoices showing Sum of Inv.Entries Amt Paid

Completed Invoices showing Sum of Inv.Items Amt Paid

Distribution Formulas, list of

Use: Returns a list of the distribution formulas in use by a system.

Uses Distribution Formula source.

Displayed fields:
Distribution Formula→Owning Library→Organizational unit (“Owning library”) Distribution Formula→Formula name Distribution Formula→Copy Location

Encumbered funds report

Use: This report is useful to get detailed information on encumbered items.

Uses Line Item Detail source.

Displayed fields:
Line Item Detail→Fund→Name (“Fund Name”)
Line Item Detail→Owning Library→Short Policy Name

Filters:

Open Order report

Use: This report can be useful to get more information on orders that are still in an “on-order” status.

Uses Purchase Order source. Nullability selection enabled.

Displayed Fields:

Purchase Order → (Default) Ordering Agency → Short (Policy) Name as “Ordering Agency”
Purchase Order → (Default) Provider → Provider Name
Purchase Order → Purchase Order ID
Purchase Order → Name as “Purchase Order Name”
Purchase Order → (None) Line Items → State as “Line Item State”
Purchase Order → (None) Line Items → Lineitem ID as “Line Item ID”
Purchase Order → (None) Line Items → (Parent) Evergreen Bib ID → (Default) Simple Record Extracts → Title
Proper (normalized) as “Bib Record Title”
Purchase Order → (None) Line Items → Estimated Unit Price
Purchase Order → (None) Line Items → (None) Line Item Details → Item Detail ID (Count) as “Line Item Detail (Copy) Count”

Base Filters:

Purchase Order → (Default) Ordering Agency → Organizational Unit ID (In list)
Purchase Order → (None) Line Items → State (Equals) (“on-order”) (Field Hint: “Line Item State”),
Purchase Order → (None) Line Items → (None) Line Item Details → Cancel Reason (Is NULL)
Purchase Order → (None) Line Items → (None) Line Item Details → Actual Receive Date (Is NULL)
Purchase Order → (Default) Provider → Provider ID (In list)

Orphaned ACQ barcodes

Use: Finds ACQ barcodes where the originating line item has been deleted, thus “orphaning” the copy.

Uses Line Item Detail source. Nullability selection enabled. Displayed Fields: Line Item Detail → (Parent) Evergreen Copy ID → Barcode Base Filters: Line Item Detail → Evergreen Copy ID (Is NULL) Line Item Detail → (Parent) Evergreen Copy ID → Is Deleted (Equals) (False) Line Item Detail → (Parent) Evergreen Copy ID → Barcode (Contains Matching substring) (“ACQ”)

Providers

List of expenditures by provider

Use: Find a list of expenditures, filtered by provider and on/after a certain date.

Receiving Report

List of Line Items Received - by Date and Org Unit. Display Fields:
Status “Received”
Receive Date
policy name
Provider
Line Item #
Estimated Price
PO ID
Invoice ID Filters: Ordering Agency, State equals “received”, Receive Date On or After

ACQ copies with a shelving location as anything other than ON ORDER

Use: This report is helpful to find ACQ copies that have a shelving location of anything other than ON ORDER.

Display fields: Item→Barcode Item→Shelving Location→Name (“Shelving loca

Funds

These reports are found in Shared Folders→Templates→PINESacqadmin→Funds.

List of items purchased in a time period, by fund

Use: Find a list of items that were purchased between two dates, filtered on the fund code.

Report source: Line item detail (Nullability enabled)

Displayed:

  • Line Item Detail→Fund Debit (none)→Amount
  • Line Item Detail→PO Line Item (none)→Evergreen bib ID (none)→Simple Record Extracts(none)→Author
  • Line Item Detail→PO Line Item (none)→Evergreen bib ID (none)→Simple Record Extracts(none)→Title
  • Line Item Detail→Fund (none)→Code

Filters:

  • Line Item Detail→Fund Debit (none)→Create Time (Date) BETWEEN
  • Line Item Detail→Fund→Fund ID (in list)
acq/reports.txt · Last modified: 2019/02/06 15:24 by tlittle