====== 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:** {{:acq:orphanedbarcodes_filters.jpg?400|}} **Display:** {{:acq:orphanedbarcodes_display.jpg?400|}} **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:** {{:acq:truecancels_filters.jpg?400|}} **Display:** {{:acq:truecancels_displays.jpg?400|}} ==== 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 {{:acq:dc_rpt_filterfields.png?400|}} Display Fields {{:acq:dc_rpt_displayfields.png?400|}} ==== 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 {{ :acq:invoiced-not-received-2.png?400 |}} Display Fields {{ :acq:invoiced-not-received-1.png?400 |}} ===Line Items that have been received but not invoiced=== //Created by BC Libraries Cooperative - Sitka// (Click on the images to see larger versions.) Filters {{ :acq:received-not-invoiced-2.png?400 |}} Display Fields {{ :acq:received-not-invoiced-1.png?400 |}} ===Line Items with the status of on-order=== //Created by BC Libraries Cooperative - Sitka// (Click on the images to see larger versions.) Filters {{ :acq:line-items-on-order-2.png?400 |}} Display Fields {{ :acq:line-items-on-order-1.png?400 |}} ===Line Items linked to delete bib records=== //Created by BC Libraries Cooperative - Sitka// (Click on the images to see larger versions.) Filters {{ :acq:deleted-bibs-2.png?400 |}} Display Fields {{ :acq:deleted-bibs-1.png?400 |}} ==== 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 {{ :acq:fund-report-2.png?400 |}} Display Fields {{ :acq:fund-report-1.png?400 |}} ==== Purchase Orders ==== ==== Claiming ===== ===Claim ready items=== //Created by BC Libraries Cooperative - Sitka// (Click on the images to see larger versions.) Filters {{ :acq:claim-ready-2.png?400 |}} Display Fields {{ :acq:claim-ready-1.png?400 |}} ==== EDI ==== **EDI-enabled POs ordered yesterday**\\ //Submitted by PINES// //Display fields// {{:acq:edipo_report.jpg?direct&600|}} //Filters// {{:acq:edipo_report_filter.jpg?direct&600|}} **EDI messages from yesterday** //Display fields// {{:acq:edimessages_report_columns.jpg?direct&600|}} //Filters// {{:acq:edimessages_report_filters.jpg?direct&600|}} 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: {{:acq:daily_spreadsheet.jpg?direct&600|}} If you would like to try to use the spreadsheet, a copy of mine is available [[https://docs.google.com/spreadsheets/d/1Hvw2tok2dIsNPj7SHYVRX-_dNdlockC5JWafVGoo_Yo/edit?usp=sharing|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: {{ :acq:list-invoices-display-fields.png?direct&600 |}} Filters: {{ :acq:list-invoices-filters.png?direct&600 |}}