[Evergreen-acq] [External] Re: Acquisitions reports

[Jennifer Pringle]

Thanks for creating the wiki page Tiffany!

I've added screenshots of the display fields and filters for the  
following Sitka reports:
  - Line items received but not invoiced
  - Line items invoice but not received
  - Line Items with the Status of On-order
  - Fund Report by Year
  - Claim Ready Items
  - Line items linked to deleted bibs

If there are other reports we have that people are interested in I'm  
happy to add them as well.

Jennifer

Quoting Tiffany Little <tlittle at georgialibraries.org>:

> Sure thing, I'm happy to share!
>
> I think adding them to the wiki is a great idea. I went ahead and created a
> page: https://wiki.evergreen-ils.org/doku.php?id=acq:reports
>
> I added the SQL for my fund debit query, and I'll plan on adding the info
> for my other reports. I'm excited to see what others are using too! If you
> don't have an account to edit the community wiki but you have a report that
> you use frequently and want to contribute, feel free to send me screenshots
> and I'm happy to add.
>
> Thanks!
>
> Tiffany Little, PINES Services Specialist: Acquisitions
>
> ------------------------------
>
> Georgia Public Library Service | University System of Georgia
>
> 2872 Woodcock Blvd, Suite 250 l Atlanta, GA 30341
>
> (470) 512-1454 | tlittle at georgialibraries.org |
> help at help.georgialibraries.org
>
> <https://www.facebook.com/georgialibraries>
> <https://www.twitter.com/georgialibs>
>
> Join our email list <http://georgialibraries.org> for stories of Georgia
> libraries making an impact in our communities.
>
>
> On Wed, Aug 12, 2020 at 9:57 AM Durrence, April <april.durrence at ncdcr.gov>
> wrote:
>
>> Oh wow, Tiffany, I would love to know the criteria for all of your reports
>> and the SQL script that you run to get the massive year-end fund debit
>> info! I will be glad to share the report criteria for our existing reports
>> and any that I am able to build in the near future after a scrum session
>> with Blake and the NC Cardinal team this afternoon.
>>
>>
>>
>> I spent some time yesterday reviewing acq bugs and saw the issues
>> surrounding cancelled items. One of our libraries is having a problem with
>> some items cancelled in the last FY and reordered with a new PO showing up
>> as backordered, even after she receives them. Not sure yet whether it has
>> to do with the EDI message from the vendor or not, so these reports might
>> help.
>>
>>
>>
>> The pending PO and deleted bibs issue is something we’ve run into in the
>> past and I like your report for older Pos that haven’t been activated yet!
>>
>>
>>
>> Do you think this info is something that would be of enough interest to
>> others that it would be worth posting on the wiki somewhere? Report
>> templates can be a bit of a pain to setup and test at the best of times (I
>> feel your v14 pain!) and sharing this info might save some grief for
>> someone down the road. Some things may be setup to work a bit differently
>> for each separate consortia or system, but most report templates should
>> work for most people.
>>
>>
>>
>>
>>
>> Regards,
>>
>>
>>
>> April
>>
>>
>>
>> *April Durrence*
>>
>> NC Cardinal Training Specialist
>>
>> State Library of North Carolina
>>
>> 919.814.6794 | april.durrence at ncdcr.gov
>>
>>
>>
>> NC Dept. of Natural and Cultural Resources
>>
>> 109 East Jones Street  | 4640 Mail Service Center
>>
>> Raleigh, North Carolina 27699-4600
>>
>> Facebook <https://www.facebook.com/StateLibraryNC/>  Twitter
>> <https://twitter.com/StateLibraryNC>  Instagram
>> <http://www.instagram.com/ncculture>  YouTube
>> <https://www.youtube.com/user/statelibrarync/>  Website
>> <https://statelibrary.ncdcr.gov/>
>>
>> [image: A close up of a logo Description automatically generated]
>> <https://statelibrary.ncdcr.gov/>
>>
>> *Email correspondence to and from this address is subject to the North
>> Carolina Public Records Law and may be disclosed to third parties.*
>>
>>
>>
>> #StayStrongNC
>> Learn more @ nc.gov/covid19
>>
>> And don’t forget your Ws!  Wear. Wait. Wash.
>>
>> *WEAR* a face covering.
>>
>> *WAIT* 6 feet apart from other people.
>>
>> *WASH* your hands often.
>>
>>
>>
>> *From:* Evergreen-acq <evergreen-acq-bounces at list.evergreen-ils.org> *On
>> Behalf Of *Tiffany Little
>> *Sent:* Tuesday, August 11, 2020 7:48 PM
>> *To:* Discussion of acquisitions in Evergreen <
>> evergreen-acq at list.evergreen-ils.org>
>> *Subject:* [External] Re: [Evergreen-acq] Acquisitions reports
>>
>>
>>
>> *CAUTION:* External email. Do not click links or open attachments unless
>> you verify. Send all suspicious email as an attachment to
>> report.spam at nc.gov
>>
>>
>>
>> Hi April,
>>
>>
>>
>> Here's the list of reports that we recommend to run at FYE here in PINES:
>> https://pines.georgialibraries.org/dokuwiki/doku.php?id=acq:yearend#reports
>> <https://urldefense.com/v3/__https:/pines.georgialibraries.org/dokuwiki/doku.php?id=acq:yearend*reports__;Iw!!HYmSToo!MqwMue0Kq1Vfp0uCEZ5YWCgSNwPw6jRCgd9JpwWAF-ik1p7kuYz2kKjvEm9ayHkUDbT0uQ$>
>>
>>
>>
>> I'm happy to share if any of those are of interest to anyone.
>>
>>
>>
>> I like your reports for encumbrances. I don't think that's really
>> reflected in mine, now that I'm looking at it. I've focused on finding
>> copies (line item details) that are still encumbered, essentially, but
>> there could be direct charge encumbrances too. A lot of times I end up
>> doing a massive query for libraries that shows the details for all fund
>> debits (like their associated invoices / POs / line item details / etc),
>> but we haven't been able to put that into a report source yet so I have to
>> do it manually. I may snag some of your encumbrances reports as a solution
>> in the meantime!
>>
>>
>>
>> Other templates that we have that I use a lot:
>>
>>  -- Items cancelled in a fiscal year using a specific cancel reason -- I
>> created a Cancelled: FYE cancel reason, so libraries who cancel things at
>> year end can use this report to find them all again and reorder in the next
>> FY
>>
>>
>>
>> -- Copies remaining after true cancels -- I've found that sometimes when
>> you cancel using a cancel reason keep_debits=f, the copy isn't deleted. I
>> haven't dug into when / why, so I just did a shortcut and created a report
>> to find them. :)
>>
>>
>>
>> -- Checking Bib Matches on Line Items -- This displays the title on the
>> line item to the title on the bib that it linked to so you can compare
>> them. It's definitely *not* an exact science, but when I was evaluating the
>> efficacy of our matchset I was able to get a rough idea if it was making
>> bad matches and / or how often it was matching to an existing record vs.
>> creating an Acquisitions record.
>>
>>
>>
>> -- Abandoned ACQ copies -- This finds copies where they've been received,
>> but they were never converted to a "real" barcode. (We use autogenerated
>> Acq barcodes that are supposed to be replaced with real ones.) So if it's
>> been received but was never cataloged, something is up.
>>
>>
>>
>> -- Pending purchase order older than X days -- Since the fix for deleting
>> bibs underneath line items is now in 3.4+ (
>> https://bugs.launchpad.net/evergreen/+bug/1634244
>> <https://urldefense.com/v3/__https:/bugs.launchpad.net/evergreen/*bug/1634244__;Kw!!HYmSToo!MqwMue0Kq1Vfp0uCEZ5YWCgSNwPw6jRCgd9JpwWAF-ik1p7kuYz2kKjvEm9ayHncf9ceaQ$>),
>> if you have pending purchase orders linked to bib records, but you're just
>> letting the PO sit there without ordering, you're sort of letting those
>> bibs hang around in the catalog when they might normally get deleted.
>> (Assuming that you're using that library setting.) So I have this report
>> autorun to find any PO's older than 45 days--if you haven't ordered it by
>> then, I'm guessing you might not want it. I usually reach out to the
>> library and they usually don't want the PO's so they delete the line items
>> and that frees up the bibs. I don't know if that explanation made any
>> sense, but I definitely use this one regularly.
>>
>>
>>
>> And looking through my folders has reminded me once again I really should
>> clean those out. Lots of report templates that include the word "Test v.14"
>> in there. :D
>>
>>
>>
>> [image: Image removed by sender.]
>>
>> *Tiffany Little, PINES Services Specialist: Acquisitions*
>> ------------------------------
>>
>> *Georgia Public Library Service | University System of Georgia*
>>
>> 2872 Woodcock Blvd, Suite 250 l Atlanta, GA 30341
>>
>> (470) 512-1454 | tlittle at georgialibraries.org |
>> help at help.georgialibraries.org
>>
>>
>>
>> [image: Image removed by sender.]
>> <https://urldefense.com/v3/__https:/www.facebook.com/georgialibraries__;!!HYmSToo!MqwMue0Kq1Vfp0uCEZ5YWCgSNwPw6jRCgd9JpwWAF-ik1p7kuYz2kKjvEm9ayHn1Mvj5vA$>
>>  [image: Image removed by sender.]
>> <https://urldefense.com/v3/__https:/www.twitter.com/georgialibs__;!!HYmSToo!MqwMue0Kq1Vfp0uCEZ5YWCgSNwPw6jRCgd9JpwWAF-ik1p7kuYz2kKjvEm9ayHlMtq4fmA$>
>>
>> *Join our email list*
>> <https://urldefense.com/v3/__http:/georgialibraries.org__;!!HYmSToo!MqwMue0Kq1Vfp0uCEZ5YWCgSNwPw6jRCgd9JpwWAF-ik1p7kuYz2kKjvEm9ayHl1hM0Ygg$>  
>> *for
>> stories of Georgia libraries making an impact in our communities.*
>>
>>
>>
>>
>>
>> On Tue, Aug 11, 2020 at 4:31 PM Durrence, April <april.durrence at ncdcr.gov>
>> wrote:
>>
>> Hi everyone,
>>
>>
>>
>> As we often have an uptick in help tickets around year-end closeout, I
>> want to make our list of pre-built report templates for acq more robust.
>>
>>
>>
>> I am particularly interested in these Sitka reports:
>>
>> Line items received but not invoiced
>>
>> Line items invoice but not received
>>
>> Line Items with the Status of On-order
>>
>> Fund Report by Year (may be similar to our Funds List template, but may
>> have additional info we should include)
>>
>> Claim Ready Items
>>
>>
>>
>> And the report to identify deleted bibs in PO that causes activation to
>> fail that Tiffany Little created and mentioned in this bug:
>>
>> https://bugs.launchpad.net/evergreen/+bug/1814793
>> <https://urldefense.com/v3/__https:/bugs.launchpad.net/evergreen/*bug/1814793__;Kw!!HYmSToo!MqwMue0Kq1Vfp0uCEZ5YWCgSNwPw6jRCgd9JpwWAF-ik1p7kuYz2kKjvEm9ayHmglE23dQ$>
>>
>>
>>
>> If you have other templates that you find useful and wouldn’t mind
>> sharing, I would appreciate it!
>>
>>
>>
>> If you can provide screenshots or some other means of letting me know your
>> Source, Display fields (with Source Path, Column Label, Data Type, Field
>> Transform) and Filter Fields (with Source Path, Name, Column, Data Type,
>> Operator, Field Transform, and Filter value, if any) I should be able to
>> replicate the report.
>>
>>
>>
>> We currently have these report templates:
>>
>>
>>
>> *name*
>>
>> *description*
>>
>> Amount Encumbered and Amount spent for any selected active fund
>>
>> Display PO Name, Provider Name, PO Creation Date, Amount Encumbered, and
>> Amount Spent. Filters on Ordering Agency, Name of fund, and fund is
>> active(=true)
>>
>> Fund Summary FYxx
>>
>> Provides monetary details of expenditures for acquisitions. Includes
>> encumbrances, balance, allocations, fund tag, account name. Must include
>> year of fund summary and OU code of libraries needing report.
>>
>> Funds list
>>
>> Lists all fund names and codes for selected year
>>
>> List POs created between selected dates
>>
>> Displays PO id, ordering agency create time, PO creator, edit time, PO
>> editor, order date, PO state, cancel cause, and cancel description. Filters
>> on ordering library and selected create date range.
>>
>> Outstanding encumbrances
>>
>> Lists all encumbrances (not direct charges), Includes Provider, PO number,
>> Title ordered
>>
>> Outstanding encumbrances filter by Fund Code
>>
>> Lists outstanding encumbrances (not direct charges), Includes Provider, PO
>> #, Title ordered. Allows you to filter for a specific Fund code name.
>>
>> POs that have been cancelled and reasoning
>>
>> History of cancelled POs.
>>
>> Purchase Order History
>>
>> History of PO for library system.
>>
>>
>>
>> If anyone would like the setup info for our templates, I would be glad to
>> share.
>>
>>
>>
>> Thanks!
>>
>>
>>
>> April
>>
>>
>>
>> *April Durrence*
>>
>> NC Cardinal Training Specialist
>>
>> State Library of North Carolina
>>
>> 919.814.6794 | april.durrence at ncdcr.gov
>>
>>
>>
>> NC Dept. of Natural and Cultural Resources
>>
>> 109 East Jones Street  | 4640 Mail Service Center
>>
>> Raleigh, North Carolina 27699-4600
>>
>> Facebook
>> <https://urldefense.com/v3/__https:/www.facebook.com/StateLibraryNC/__;!!HYmSToo!MqwMue0Kq1Vfp0uCEZ5YWCgSNwPw6jRCgd9JpwWAF-ik1p7kuYz2kKjvEm9ayHmfqNheaQ$>
>> Twitter
>> <https://urldefense.com/v3/__https:/twitter.com/StateLibraryNC__;!!HYmSToo!MqwMue0Kq1Vfp0uCEZ5YWCgSNwPw6jRCgd9JpwWAF-ik1p7kuYz2kKjvEm9ayHlQWCbv3Q$>
>>  Instagram
>> <https://urldefense.com/v3/__http:/www.instagram.com/ncculture__;!!HYmSToo!MqwMue0Kq1Vfp0uCEZ5YWCgSNwPw6jRCgd9JpwWAF-ik1p7kuYz2kKjvEm9ayHk_W3h-uQ$>
>> YouTube
>> <https://urldefense.com/v3/__https:/www.youtube.com/user/statelibrarync/__;!!HYmSToo!MqwMue0Kq1Vfp0uCEZ5YWCgSNwPw6jRCgd9JpwWAF-ik1p7kuYz2kKjvEm9ayHlW1cPsHA$>
>>  Website <https://statelibrary.ncdcr.gov/>
>>
>> [image: A close up of a logo Description automatically generated]
>> <https://statelibrary.ncdcr.gov/>
>>
>> *Email correspondence to and from this address is subject to the North
>> Carolina Public Records Law and may be disclosed to third parties.*
>>
>>
>>
>> #StayStrongNC
>> Learn more @ nc.gov/covid19
>>
>> And don’t forget your Ws!  Wear. Wait. Wash.
>>
>> *WEAR* a face covering.
>>
>> *WAIT* 6 feet apart from other people.
>>
>> *WASH* your hands often.
>>
>>
>>
>> _______________________________________________
>> Evergreen-acq mailing list
>> Evergreen-acq at list.evergreen-ils.org
>> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-acq
>> <https://urldefense.com/v3/__http:/list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-acq__;!!HYmSToo!MqwMue0Kq1Vfp0uCEZ5YWCgSNwPw6jRCgd9JpwWAF-ik1p7kuYz2kKjvEm9ayHnNnDiOcw$>
>>
>> _______________________________________________
>> Evergreen-acq mailing list
>> Evergreen-acq at list.evergreen-ils.org
>> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-acq
>>


-- 
Jennifer Pringle
Co-op Support
BC Libraries Cooperative
Tel: 1-888-848-9250
Email:jennifer.pringle at bc.libraries.coop
Website: http://bc.libraries.coop

Gratefully acknowledging that I live and work in the unceded  
Traditional Territory of the St'at'yemc Nations.