Our University uses the Finance One accounting package to pay invoices. The Library uses Alma to manage vendor transactions. When vendor invoices are ready for payment we produce an XML file of invoices for payment. Previously we used a perl program to extract Voyager invoices ready for payment into CSV files, one for local currency and another for foreign currency.
Our objective was to produce the same format files for transfer to Finance One as we had from Voyager using a Java program to convert the Alma invoice export XML files.
Keeping it simple
The core of the task is conversion of the Alma XML data to the CSV format required for Finance One. The program is developed using the command line java compiler and running the program from the command line. The program displays the data as it is extracted from the XML. This blog entry is a bit long but I wanted it all in the one place so there is no need to use any other resource.
You need to have a java development environment installed on your computer. You can check by keying in this command :-
You should see a message including something like :-
openjdk version "1.8.0_31"
You will need a java module commons-lang-2.6.jar - the version may be different and it may already be loaded onto your computer. If it is not available/found you can download it from the internet and/or copy it to the location where you will be working. You will need an environment variable so that java will find the files you are using - something like this :-
See the Java program: "fin.java".
To compile the program (you need to have CLASSPATH set) use:-
You could use an exported invoice XML file from Alma or use the sample data: "inv.xml".
Either way, you need to copy the sample data to a file called "inv.xml" in the same location as the program.
Running the program
To run the program (after you have set the CLASSPATH and compiled and have the input file "inv.xml) use this command :-
You should see the following display :-
invoice 1 include Vendor code 1 : 3 Invoice number : 26078 Invoice date : 02/01/2015 Invoice total : 115.94 Invoice currency : USD Vendor name : YBP Library Services Inc Vendor code : 3YBP Vendor FinancialSys Code : 108692 Fiscal period : FY-2015 Vat amount : 11.59 GST : 0 Fund : 1 external_id : 828121025012 amount : 115.94 invoice 2 include Vendor code 1 : 3 Invoice number : 26080 Invoice date : 02/01/2015 Invoice total : 41.63 Invoice currency : USD Vendor name : YBP Library Services Inc Vendor code : 3YBP Vendor FinancialSys Code : 108692 Fiscal period : FY-2015 Vat amount : 4.16 GST : 0 Fund : 1 external_id : 828121025012 amount : 41.63 invoice 3 include Vendor code 1 : 1 invoice aud 1 Invoice number : SAIG1IS-400106 Invoice date : 05/12/2014 Invoice total : 18816.35 Invoice currency : AUD Vendor name : SAI Global Limited Vendor code : 1STANDS Vendor FinancialSys Code : 102042 Fiscal period : FY-2015 Vat amount : 1881.64 GST : 0 Fund : 1 external_id : 828121085012 amount : 18816.35 invoice 4 include Vendor code 1 : 9 Invoice number : 7126357 Invoice date : 19/12/2014 Invoice total : 259.34 Invoice currency : GBP Vendor name : EBSCO Australia (O/S) Subscription Servi Vendor code : 9EBSCOGBP Vendor FinancialSys Code : 100678 Fiscal period : FY-2015 Vat amount : 25.93 GST : 0 Fund : 1 external_id : 828121085012 amount : 259.34
You should have two new files :-
The program converts the Alma invoice export XML to CSV files in the correct format for Finance One in a minimal development environment.
You may want to use the Eclipse IDE as a development environment, Mercurial as a code repository and Maven as a build tool. Your institution may have a java development platform/process in place.
The program as presented uses hard coded values for the input and output filenames, as well as some text constants which are specific to our institution. A minimal approach is to copy the Alma exported XML invoice file(s) to the program location as "inv.xml". The output filenames need a bit more work as the CSV file data has a field which is the filename. Ideally, the output filenames should be numbered with a sequential number. The program should update the sequence number and create the output files and data using the current updated values.
The file transfers cold be automated. The input file needs to be accessible to the program. It is sent by Alma to your SFTP server. The output files will be sent to your finance department.
In our environment we have an Oracle RAC database to store a record for each output file. This is also used to maintain the sequential file numbering and to provide a web interface which Library staff can use to see the history and download the input and output files.