PINES Documentation

User Tools

Site Tools

You are here: home » acq » reports
acq:reports

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
acq:reports [2017/07/03 11:36] – created tlittleacq:reports [2019/02/06 15:24] (current) – tlittle
Line 1: Line 1:
 ====== 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 ===== ===== Invoices =====
 +Reports can be found in Shared Folders->Templates->PINESacqadmin->Invoices.
 +
 ==== Blank invoices ==== ==== Blank invoices ====
 +
 //Use:// This report finds invoices that are closed with a paid amount of $0.00. //Use:// This report finds invoices that are closed with a paid amount of $0.00.
  
Line 8: Line 28:
  
 Displayed fields:\\ Displayed fields:\\
-Invoice->Provider->Code ("Provider")\\ +Invoice→Provider→Code ("Provider")\\ 
-Invoice->Vendor Invoice ID\\ +Invoice→Vendor Invoice ID\\ 
-Invoice->Invoice Entries->Amount Paid (sum) +Invoice→Invoice Entries→Amount Paid (sum) Invoice→Invoice Items→Amount Paid (sum) Invoice→Receiver→Short (Policy) Name ("Receiver") Invoice→Complete
-Invoice->Invoice Items->Amount Paid (sum) +
-Invoice->Receiver->Short (Policy) Name ("Receiver") +
-Invoice->Complete\\+
  
 Base Filters:\\ Base Filters:\\
-Invoice->Complete (Operator:Equals) (Value=TRUE)\\ +Invoice→Complete (Operator:Equals) (Value=TRUE)\\ 
-Invoice->Receiver->Organizational Unit ID (Operator: In List)\\+Invoice→Receiver→Organizational Unit ID (Operator: In List)
  
 Aggregate Filters:\\ Aggregate Filters:\\
-Invoice->Invoice Entries->Amount Paid (sum) (Operator: Equals) (Value=0)\\ +Invoice→Invoice Entries→Amount Paid (sum) (Operator: Equals) (Value=0)\\ 
-Invoice->Invoice Items->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",
 +
 +<code>
 +"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
 +</code>
 +
 +) 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 ===== ===== Distribution Formulas, list of =====
 +
 //Use:// Returns a list of the distribution formulas in use by a system. //Use:// Returns a list of the distribution formulas in use by a system.
  
Line 31: Line 77:
  
 Displayed fields:\\ Displayed fields:\\
-Distribution Formula->Owning Library->Organizational unit ("Owning library") +Distribution Formula→Owning Library→Organizational unit ("Owning library") Distribution Formula→Formula name Distribution Formula→Copy Location
-Distribution Formula->Formula name +
-Distribution Formula->Copy Location +
- +
  
 ===== Encumbered funds report ===== ===== Encumbered funds report =====
 +
 //Use:// This report is useful to get detailed information on encumbered items. //Use:// This report is useful to get detailed information on encumbered items.
  
Line 43: Line 86:
  
 Displayed fields:\\ Displayed fields:\\
-Line Item Detail->Fund->Name ("Fund Name")\\ +Line Item Detail→Fund→Name ("Fund Name")\\ 
-Line Item Detail->Owning Library->Short Policy Name\\+Line Item Detail→Owning Library→Short Policy Name\\
 \\ \\
 Filters: Filters:
  
 +===== Open Order report =====
  
- 
-===== Open Order report ===== 
 //Use:// This report can be useful to get more information on orders that are still in an "on-order" status. //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.+Uses Purchase Order source. Nullability selection enabled.
  
 Displayed Fields: Displayed Fields:
  
-Purchase Order -> (Default) Ordering Agency -> Short (Policy) Name as "Ordering Agency"\\ +Purchase Order → (Default) Ordering Agency → Short (Policy) Name as "Ordering Agency"\\ 
-Purchase Order -> (Default) Provider -> Provider Name\\ +Purchase Order → (Default) Provider → Provider Name\\ 
-Purchase Order -> Purchase Order ID\\ +Purchase Order → Purchase Order ID\\ 
-Purchase Order -> Name as "Purchase Order Name"\\ +Purchase Order → Name as "Purchase Order Name"\\ 
-Purchase Order -> (None) Line Items -> State as "Line Item State"\\ +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 → Lineitem ID as "Line Item ID"\\ 
-Purchase Order -> (None) Line Items -> (Parent) Evergreen Bib ID -> (Default) Simple Record Extracts -> Title\\+Purchase Order → (None) Line Items → (Parent) Evergreen Bib ID → (Default) Simple Record Extracts → Title\\
 Proper (normalized) as "Bib Record Title"\\ Proper (normalized) as "Bib Record Title"\\
-Purchase Order -> (None) Line Items -> Estimated Unit Price\\ +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"+Purchase Order → (None) Line Items → (None) Line Item Details → Item Detail ID (Count) as "Line Item Detail (Copy) Count"
  
 Base Filters: Base Filters:
  
-Purchase Order -> (Default) Ordering Agency -> Organizational Unit ID (In list)\\ +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 → 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 → Cancel Reason (Is NULL)\\ 
-Purchase Order -> (None) Line Items -> (None) Line Item Details -> Actual Receive Date (Is NULL)\\ +Purchase Order → (None) Line Items → (None) Line Item Details → Actual Receive Date (Is NULL)\\ 
-Purchase Order -> (Default) Provider -> Provider ID (In list)+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 ===== ===== Receiving Report =====
-List of Line Items Received - by Date and Org Unit.   + 
-Display Fields:\\ +List of Line Items Received - by Date and Org Unit. Display Fields:\\ 
-Status "Received"\\ +Status "Received"\\
 Receive Date\\ Receive Date\\
 policy name\\ policy name\\
Line 86: Line 139:
 Estimated Price\\ Estimated Price\\
 PO ID\\ PO ID\\
-Invoice ID +Invoice ID Filters: Ordering Agency, State equals "received", Receive Date On or After
-Filters: +
-Ordering Agency, State equals "received", Receive Date On or After +
  
 ===== ACQ copies with a shelving location as anything other than ON ORDER ===== ===== 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. //Use:// This report is helpful to find ACQ copies that have a shelving location of anything other than ON ORDER.
  
-Display fields: +Display fields: Item→Barcode Item→Shelving Location→Name ("Shelving loca 
-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.1499081767.txt.gz · Last modified: 2018/10/11 20:15 (external edit)