Ready to Learn?Ex Libris products all provide open APIs

Tech Blog

 

Alma invoices for FinanceOne

Laszlo Gercsov on April 15th, 2015

Context

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.

Brief

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.

Prerequisites

You need to have a java development environment installed on your computer. You can check by keying in this command :-
java --version
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 :-
CLASSPATH='.:./commons-lang-2.6.jar';export CLASSPATH

The Program

See the Java program: "fin.java".

To compile the program (you need to have CLASSPATH set) use:-
javac fine.java

Sample data

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 :-
java fin

Sample display

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

Output files

You should have two new files :-
  • LBL01928.csv
  • LBF01929.csv
     

Further development

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.

Production context

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.

Library Finance Interface