Acq: In EDI invoices, avoid taking reference to PO too seriously

After applying this patch, we got repeated errors like:

2013-06-04 22:22:51 guitar ./edi_fetcher.pl: [ERR :4797:EDI.pm:258:] create_acq_invoice_from_edi(..., <acq.edi_message #1728>): no LI found with ID: 0088176455 : HASH(0x4d87d48) at /usr/local/share/perl/5.14.2/OpenILS/Application/Acq/EDI.pm line 944.

Previously, we were seeing random things like:

2013-06-04 22:25:11 guitar open-ils.cstore: [ERR :5160:oils_sql.c:5645:] open-ils.cstore: Error retrieving acq::purchase_order with query [SELECT "acqpo".id, "acqpo".owner, "acqpo".create_time, "acqpo".edit_time, "acqpo".provider, "acqpo".state, "acqpo".ordering_agency, "acqpo".creator, "acqpo".editor, "acqpo".order_date, "acqpo".name, "acqpo".cancel_reason, "acqpo".prepayment_required FROM acq.purchase_order AS "acqpo" WHERE "acqpo".id = 'TGPB31913';]: 0 ERROR: invalid input syntax for integer: "TGPB31913"#012LINE 1: ... acq.purchase_order AS "acqpo" WHERE "acqpo".id = 'TGPB31913...#012

Related to the changes or illustrating that there's some really junky stuff in the EDI files we're fetching? Interestingly, prior to the patch, edi_fetcher.pl lists zero new files grabbed, and post-adding this patch we get 103 files retrieved each time we run it. So maybe it's processing them and dying somewhere...

To follow up, I believe those EDI messages are coming from Baker and Taylor EDI files. We're still testing with them so it could be something screwy with our data that's making things worse than they appear....

Marking incomplete pending further testing...

Ben,

This sounds like a problem I've seen recently at another site, if I'm following the story correctly.

If B&T is sending invoices that refer to lineitems by numbers like 0088176455, and if there is no lineitem with id=88176455 in your database, anything you could do to find out from B&T what in the world they're referring to by such a number would be helpful.

My hypothesis at the moment is that they're sending invoices that mingle lineitems actually ordered via EDI from the ILS with lineitems ordered through other mechanisms, but I'm not sure yet. I'd be glad to learn anything you learn about this.

Thanks,

Lebbeous

So Ben and I have been talking via other channels, and with the help of Mary Llewellyn we've been able to get confirmation that B&T does send electronic invoices that include references to items ordered via Titlesource and items ordered via the ILS.

B&T may not be the only vendor that does this. I'll try to get more data, but this may just be standard practice for vendors using EDI, and as such it would be something that wasn't understood or considered in designing Evergreen Acq EDI functionality.

In case that isn't clear, to illustrate, imagine that in Evergreen you create and activate one or more purchase orders to order items A, B, and C. Meanwhile, you (or somebody else at your site) also goes to the vendor website to directly order X, Y, and Z, without involving the ILS. The vendor may very well send a single electronic invoice back that covers A, B, C, X, Y, and Z.

Evergreen balks at this situation today and fails to process the invoice as a whole. How should Evergreen react to this situation?

With a little bit of new code, Evergreen should be able to ignore X, Y, and Z altogether. There is not enough information in the electronic invoice to construct the line items at this point, so I can't readily see how Evergreen can do otherwise.

But this amounts to ignoring potentially large parts of the electronic invoice, with real dollars involved. Does there need to be a way to inform staff that a given electronic invoice bills for known lineitems A, B, and C but also demands $123 for unknown lineitems X, Y, and Z? If so, we're talking about bigger new development work.

How should this work?

I have received some feedback through other channels to the effect that library staff are not surprised by the combined electronic invoices, and are familiar with situations like the one I described, and with similar ones, such as when a library orders a rental version of a title and, the vendor's supply thereof being exhausted, eventually gets a retail one instead (different line item).

If Evergreen simply processes the line items that it can, staff can manually enter charges on the invoice the rest. Future development could possibly make this better, but as I understand it, this would not be a bad solution for the time being.

I do need to confirm that additional charges can still be added to Evergreen invoices generated from EDI, and that such invoices aren't "closed" right away.

Lebbeous, this fits my understanding of what our libraries are looking for.

Thanks Tim. I also have confirmation that adding additional charges is actually possible through the invoice interface.

All (especially Ben),

The original branch (working/user/senator/acq-edi-po-field-unreliable) has now been updated with an additional commit to achieve the plan described in comment #6. Would you mind retesting?

Thanks,

Lebbeous

Lebbeous, I think Evergreen processing what it can will work for the time being, but would like to see an alert, of some kind, added to notify the user that line items are missing.

My librarian using EDI with Baker & Taylor sent me word about what she is currently seeing. I'm attaching a Word doc with screen shots, but in a nutshell, a couple of titles are attached to the invoice that belong to another library. The bulk of the invoice is a list of "Processing fee" entries, with a title of "Cataloging services." They should be the list of titles from the purchase orders for this library.

I tracked down the original purchase orders and they do show the INVOIC among their EDI messages. But no invoice is linked to the purchase orders.

I don't know if this adds to what is already known about this ticket, or muddies the waters. If it should be filed as a separate issue on its own ticket, would someone please let me know.

Mary

Hi Mary,

I think you're experiencing a different issue.

The EDI shown in your attached screenshots tries to make references to many line items on different purchase orders, but it doesn't include the complete line item number, so Evergreen can't guess what line item is meant. For example, within the EDI code there seem to be cases of RFF+LI:12676/2499' even though the only line items I can see on PO #12676 from your have numbers like 249989 (six digits instead of four).

The vendor needs to fix their invoices and include the important number (the line item ID) in full.

Hope this helps!

Hi Lebbeous,

Thank you! That at least makes some sense. I've passed it to Baker & Taylor in hopes they will address this promptly.

Mary

Pushed to master, rel_2_4 and rel_2_3. Seems to work fine for us so far. Will continue testing along though.