Export FOLIO orders in EDIFACT format

UXPROD-531 - Getting issue details... STATUS

Problem(s)

  1. Libraries often create orders in their own LSP system to keep track of order commitments and manage receiving. If the library has no means of transmitting this data to the relevant vendors they would need to manually re-create these orders in a vendor system.
  2. Creating orders in a vendor system requires the library to first confirm in their own system whether they currently own the material or not. If the order can be created in the library system and electronically sent it helps prevent the duplication acquisitions work.

Use Cases & Requirements

RequirementStatusUse cases
Generate EDIFACT format files based on FOLIO Orders.

VERIFIED

  • Library currently communicates all orders with large vendors via EDI. This reduces the amount of duplicate effort needed to manage acquisitions through this vendor. Often this is because the vendor could not or would not support a direct API integration with their ILS system
  • Users need to confirm that they don't already own or have an outstanding or for the material they want to acquire. This is most easily done in the LSP system. Libraries will then check multiple vendors to compare pricing before acquisition.
Send orders in different formats as specified in the Organization record. Eg EDIFACT format, email etc

VERIFIED

  • Library needs to configure different EDI details for different organizations. Often files need to be uploaded to different directories or use different naming conventions or different codes
  • Horrasawitz does not accept standing orders via EDI so the library will send PDFs via mail or email.
Send orders based on different export method configurations for the same vendor. Configure multiple export methods per Organization record

VERIFIED

  • The institution has multiple libraries there will be different pickup and dropoff directories for the EDI files. Meaning two separate EDI files would be uploaded to two separate directories OR two separate FTP locations. Eg. one for Main library and one for Law
  • Some accounts with different envelope addresses (Library EDI codes) may be included on the same EDI file OR the library may need to have them exported on separate files. 
Allow user to indicate they want the order to be sent AFTER the order has been opened. "Automate export" can be edited after order is opened. However, once exported this field must be locked down an user must explicitly state they want the order exported again for it to be included in a second export file.

VERIFIED

  • When we know files are being uploaded later in the day to the FTP location, someone may notice that an order was included on the wrong file. To correct this they will edit the order and indicate it should be exported and save. This should allow this order to be included in the file before it is uploaded to the Vendors FTP location at the set time.
  • The library has not noticed that the box was checked and would like to uncheck the export box after it was opened but before it was included in the export.
  • Library occasionally will want to re-send an order in a "new" export file. Say there has been a change of Vendor.
Send orders in different formats for the same Organization based on account number

VERIFIED

  • For a given vendor the library may send most orders through EDI but treat some order types differently.
  • Data that drives the identification of a different communication method
    • Account number (Team/location/Acquisition unit. These would likely be represented by account number)
    • Acquisition method (for diff methods the order may of may not be exported for a given Vendor)
    • Order type (One-time, Ongoing, Ongoing-subs)
    • Material type (this could be a concern but generally if it is it will be backed into the account number or order type)
Limit what orders are sent based on Acquisition method for a given vendor

VERIFIED

  • For some material types the library will send order info through EDI but for others, they may create orders in the vendor systems and recreate in FOLIO.
  • The library will differentiate between rush and non-rush. Non-rush orders going through EDI and Rush orders going through email
  • Orders that are identified as certain acquisition methods should NOT be exported because the Vendor would already have these orders. 
Allow library to trigger a "Resend" for the generated file

VERIFIED

  • If something goes wrong with the upload to ftp (Which does happen) we need to be able to resend the EDI file, without regenerating it and sending additional orders along with it. Eg. If order was changed from hard cover to paperback we would want to send in a new file.

Display export summary on POL. Allow user to click a link and find the export file in the export log area. 

VERIFIED

  • Generally not many users are involved in trouble shooting EDI issues. Being able to find the file that a specific vendor did not receive is important but would be done by administrators.
There is a separate permission for download or resend export

VERIFIED

  • It is helpful for users to be able to identify the sent date and status of export etc. but only admins should be able to manage exports.
Allow user to immediately send 1 specific order to a vendor in EDI format.
Sent on it's own file? Yes
Transmit via FTP or other? Other, sent via email.

VERIFIED

  • For rush orders we have the ability to choose to send an order immediately in EDI format. This file is sent as a single record via email to the vendor. These are sent to a specific address noted in the Vendor record. They are sent with specific subject and via an email address specified in the Vendor file.

Proposed workflow

  • Acquisition method is a converted to a controlled vocabulary

Option 1:

  • Default "Automate export" Yes/no, is set by acq method and applied to orders created for that organization accordingly. If it equals yes that orders will be a part of the export job

Option 2:

All acquisition methods can trigger the "Export by default" for specific Organizations. User assigns organizations to acquisition methods in settings

Option 3:

All acquisition methods can trigger the "Export by default" for specific Integrations. User identifies acquisition methods the default export to true in integration settings


  • Each organization can have multiple "Integration methods" and each method is configured separately. Ie. It is possible to have multiple EDI configurations, Email, csv etc.

  • Each organization account number can be associated with 1 Integration method. (Ie. EDI 1 OR EDI 2)
  • Each export configuration can have multiple accounts associated with it. (Ie. EDI 1 can be assigned 3 accounts) NOTE we would need to consider No account specified as a separate assignment for organizations that have no accounts configured.
  • Each Integration method may have more than 1 transmission method. In the screenshot you see FTP and Email as possibilities. 1 method is set as the primary and used by default for all transmissions

  • The POL as a whole will have "Automate export" checkbox set and will have the "Export method" determined by account number. However, it can be adjusted on an order by order basis if the user desires.

Note: "Export method" is only editable if Automate export = true

  • Each order line could have a different account number (Or no account number) associated with it. This will attach it to the correct export details for the format selected at the PO level. Ie. if EDI is selected the account may indicate EDI 1 or 2 etc. If email it could be different per account as well
  • In this workflow the export path for acq method + account number = export details for POL.

Export details summary is be presented on the POL so user can easily confirm what happened with an export

  • Accordion contains key details of the export

Orders can be filtered based on the following export details

  • Export date (Date range)
  • Export method (Select list) Difficult because of organization dependency.

Order unopened and reopened

  • When an order is being opened that was previously export. Ie. if user unopened an order to edit it and reopens again.
  • User is intercepted by confirmation modal and must decide to reset the order for export. If yes it will be included in the next export for the assigned vendor.

Send Immediately

  • For urgent orders user can choose to send immediately
  • The orders trigger an immediate export and are sent on their own file via the transmission method

View export history

View export history for a specific export type by clicking the associated link or button

Arrive at the full screen view of the export history for that organization

Using export manager

Using Organizations

Hybrid approach


Workflow diagram:


User Questions

Question

Status

Conclusion

Comments

Should the decision to export be determined by the acq method on the POL and be based on organization?

CLOSED

Yes, this provides the flexibility we need.

This allows export decisions to be based on the Organization AND Acquisition method.

Should separate POLs potentially have different export settings?

CLOSED

This is not needed at the POL. Nor is it necessary to adjust the export method per POL beyond the default. It would either be Automate export or Not.No use cases were identified for creating orders with multiple POLs for different accounts that should have different export details. 
Do export details need to be shown at the PO level?

CLOSED

Yes display export details for PO as a wholeMost institutions will create separate orders for separate accounts which means the export data will often be relevant for the order as a whole
How will this relate to renewals?

OPEN


Waiting for use cases via Slack channel to confirm. No specific use cases brought forward in meeting.




Vendor Questions

Question

Status

Conclusion

Comments

Do you have multiple configurations for individual libraries?

CLOSED

Even if the the library or entities within the library have different base accounts they MAY have separate Library EDI Codes or not. FOLIO will need to allow different configuration to have the same library editing code and vendor editing code. 

If there were going to be multiple configuration there would likely be multiple vendor records. This is generally the case when there are separate base accounts.

Do you separate orders by account in separate EDI files in in a single file?

CLOSED

Separate accounts sometimes appear on the same file in the same location. They can also be separated, this is not a concern for the vendor processing and is common practice.

From the GOBI perspective the same customer may send multiple files in a single day. It is unusual to have separate locations for an individual institution. It typically happens when you have one base account but separate subs for law, bus, etc. which will be uploaded to diff directory locations.


Does the file need a specific identifier?

CLOSED

Doesn't have to be specific format just needs to be unique over timeWhat the ID is is not really a concern so long as the file names are not duplicated.

If you are going to "re-send" a file within 90 days. The danger is if the file name is the same it will not be "Reprocessed" because GOBI keeps track of what's been processed.
Does the library need to contact GOBI when a file needs to be "Re-loaded"?

CLOSED

As long as they have a resend function they can contact the vendor and the vendor will sort out the file.The concern is more that the file never reach gobi. Ie. it wasn't successfully uploaded. In this case it would need to be resent.

Development Questions

Question

Status

Conclusion

Comments

If user unopens order and reopens should this order be exported again?

CLOSED

No, exporting order in a second file should require explicit instruction from a user. Ie. a specific action that they trigger

Often times new orders will be created when significant details change. 

There are some situations where significant details change but we don't want the order to be sent.

How are the export dates managed? What will be the start and end date of each export?

CLOSED

Export based on what has not been sent yet. If user triggers the re export for an order the flag should be reset so the order can be included on a new export.

Build the export file based on what orders have not yet been sent for a given vendor. When we try to send a previously exported order the system shows error message

Build base on orders that have been opened and not exported yet.

Should we allow different POLs on a single order to be exported via different methods

OPEN


This is not something libraries currently do, no use cases were brought forward. 
If order is updated before re-send of EDI file. Should the EDI file include updates or just the original data.

CLOSED

Exports can send the latest version of an order. If libraries want or need to maintain copies they could look to the FTP server or other transmission method for a record of exactly what data was sentIf the Vendor is confirming receipt of EDI files it is not important for us to maintain proof of transmission. Generally we rely on an exact copy appearing in the method of transmission. Ie. in the email account or on the FTP server. 
Do we need to be able to export EDI files with methods other than FTP? Ie. Email?

CLOSED

We will ultimately need to support multiple transmission methods for EDI.Some vendors require SFTP some FTP. In some cases email is used

User makes changes of the config for which scheduled export orders In Progress :

What is expected behavior?

a) existing task should be stopped and overwritten by new one

b) new task should wait completion of active scheduler execution in the queue?

OPEN

When a user makes changes to the export configuration the changes should only impact exports with a start date/time greater than the date/time the changes were saved.If an export is in the queue it should not be overwritten. The start date of the export is the most important date as the export should include data from that point in time. This would include configuration. If an export was set to run at 5pm there should be no possibility that it will include data that we entered into folio after that time. Even if the export is waiting in a queue.


Work Breakdown Structure


WorkCommentsStory~ Estimate
1

Export must be based on "Acquisition method" and "Acquisition method" is a converted to a controlled vocabulary.

(question) Dennis Bridges Now "Acquisition method" on the POL level and not on Order level.

Should it be moved to Order level? I think acquisition method should remain at the POL. It may not be important for the exporting of POL but users may use different acquisition methods for reporting purposes that all trigger export.

MODORDSTOR-256 - Getting issue details... STATUS

MODORDSTOR-258 - Getting issue details... STATUS

MODORDERS-615 - Getting issue details... STATUS


2

Investigate a difference between FTPS (Secure FTP) and SFTP - https://stackoverflow.com/questions/12803942/secure-ftp-with-org-apache-commons-net-ftp-ftpclient

  • What format is required - FTPS or SFTP? The fact is that there are certain differences between them, and different clients are to be used in program code. Dennis Bridges Some vendors require SFTP and some FTP. It should be reasonable to begin with FTP but we must keep in mind that additional formats will be needed. Users also expect to be able to transmit EDI files via email for some vendors 
  • Would it be possible to have test access to similar server for integration verification? Dennis Bridges the FSE team should be able to assist us in setting something like this up. That team helped create the FTP server we currently use to test voucher export.

MODEXPW-33 - Getting issue details... STATUS

MODEXPW-39 - Getting issue details... STATUS

MODEXPW-33 - Getting issue details... STATUS

5
3

Based on the current architecture and design, the export configuration should be stored in mod-configuration.

CRUD operation should be done through  mod-data-export-spring

Note : !!!Only one configuration is supported in current implementation and refactoring is mandatory!!! 


MODEXPS-40 - Getting issue details... STATUS




4

Check and confirm that initially set up job is not executed immediately (mod-data-export)

!Checked with Slava Kandramai :  Scheduled job run immediately

Code ref : org.folio.des.scheduling.ExportTrigger#scheduleTaskWithDayPeriod


MODEXPS-39 - Getting issue details... STATUS


5

(mod-data-export-spring) Refactor existing approach to store configuration for Schedulers :

The design should be easily extensible for new types of exports and customer requirements for export settings (for now BurSar configuration is hardcoded).


MODEXPS-41 - Getting issue details... STATUS

3
6
  1. Rework scheduling approach for supporting multiple Cron jobs with different configurations 
  2. Implement scheduling of the EDIFACT orders export

MODEXPS-42 - Getting issue details... STATUS

MODEXPS-54 - Getting issue details... STATUS

8
7

Implement EDIFACT export worker (mod-data-export)

  • A new worker should be able to query data from mod-orders, convert it from FOLIO format into EDIFACT and upload the result onto FTP
  • Is there any existing open-source re-usable implementation, or own implementation is required?

MODEXPW-40 - Getting issue details... STATUS

MODEXPW-41 - Getting issue details... STATUS


8

Move uploading logic on FTP from mod-invoice (src/main/java/org/folio/services/ftp) to mod-data-export-worker (mod-invoice → Kafka → mod-data-export)


MODEXPW-32 - Getting issue details... STATUS

3

Clarify requirements for

  • performance - How many orders have to be processed per second / minute? Dennis Bridges This task is happening in the background and at a set interval, so performance thresholds are higher. This export should take less than 8 hours and ideally it would be less than 30 minutes, so any errors are reasonably possible to troubleshoot and correct before the next business day.
  • expected data volume - Approximate maximum number of orders to be exported at one time? Dennis Bridges see comments column but to be safe we should consider the possibility that one export could include thousands of POLs. Library ordering can be concentrated to specific times of year which makes averages unreliable measures.
  • reliability - What is the expected behavior if export process fails during execution? Dennis Bridges ideally If an export fails an administrator should be alerted via email (Raman Auramau make sense; need to add to configuration). Otherwise a record of this failure should be visible in the log with a description of the error that occurred.
  • how long to keep export job history and store generated files? Dennis Bridgesaccording to the user group it does not seem necessary to store generated files as historical copies should be available at FTP location or in email when files are transmitted successfully. If not transmitted successfully, then it would be ideal to transmit the most recent data when the transmission is triggered again. 

Institutions reported that they will send EDIFACT order data to an average of 6-10 different Vendors

Between 100 and 300 orders per week

An average of 50 orders per Vendor per week

Conservative estimate would be that a larger file would include 500 orders. Which could be 5000 order lines in one single export file.




Make performance testing of EDIFACT export implementation



EDI export result download

  • mod-data-export-spring Add GET API for retriving FILE from SFTP or MINIO
  • Add download functionality in scope of 
retry job instead, download our of lotus
  • To be created
  • MODEXPW-33 - Getting issue details... STATUS  
3 SP

Resend

  • Save file in MinIO
  • mod-data-export-spring Add Resend API to move EDI file from MINIO to SFTP (Export type strategy)
out of lotus
  • MODEXPW-43 - Getting issue details... STATUS
  • To be created
8 SP

Order/Order line export history

  • mod-order-storage  Create table with indexes
  • Create mod-orders export-history API
  • mod-orders Create Event consumer from mod-data-worker-spring

out of lotus except filter by export date


3 SP + 1 SP

10 SP


Notes (Dennis' comments)

  • We do not keep our copy of generated file in own object storage; only Re-export action is available vs. We keep our copy of generated file in own object storage; this copy is used for Download and Re-send actions
    • (warning) Learn more about MinIO (is there an option to restrict read access there?)
  • Organization & export method - 1-to-many or many-to-many? The plan is to allow 1 organization to have many configurations, we do not need one configuration to reference many organizations.
    • One and the same config can be shared with several organization? No, each organization will have a config that produces a unique file. However, an organization can have multiple accounts. Each account will be assigned to 1 configuration. Meaning 1 file for one or many account numbers.
    • Is it possible to export one and the same order twice? E.g., it is exported in EDIFACT for consumer A, and then exported in CSV for consumer B? I would say yes, but the most common would be that the order is exported as EDIFACT for consumer A. Then some time later exported in EDIFACT for consumer B. So the order may be associated with multiple exports but the user must explicitly tell the system to export it again.
  • (question) What is the condition for export? E.g., export all open orders that correspond to a particular organization? More specifically export all open orders that correspond to a particular organization and have not yet been exported.
  • Export history as an event sourcing?
  • ....


Features:

UXPROD-531 - Getting issue details... STATUS


UI Stories

key summary type created updated due assignee reporter priority status resolution
Loading...
Refresh

MOD Stories