Ready to Learn?Ex Libris products all provide open APIs

Tech Blog

 

Exporting Invoices and Importing Payments with Banner and Alma

Nathan Mealey on May 13th, 2015

On the surface of it, integrating Alma and Banner for invoices and payments is relatively straightforward. Alma has well-defined XML formats for these integrations, and as long as either your campus IT staff can parse Alma's XML into a format appropriate for Banner, or you can do this for them, setting up either is very doable. Portland State University set these integrations up as part of our go-live migration, and in our experience the principal challenges weren't technical ones, but instead centered on communication with our campus IT staff and identifying the optimal data mapping between the two systems.
 

Communication

Campus IT staff are always in high demand, and so we began working with them well in advance of when we wanted the integrations in place. Early on we collected the available documentation for exporting invoices and importing payments, along with all of the sample invoice and payment XML files (and related XSD files) that we could find (available here). We also outlined our existing workflow for exporting invoices (prior to Alma we didn't import payment info) and drew up initial expectations for how this workflow would be conducted with Alma. 
 
We shared all of this info with them, and then through a series of meetings began clarifying the details for how data from Alma would be imported into Banner. This would prove to be an ongoing, iterative process, but planning at this initial stage using the documentation and XML samples was sufficient to get us well on the way to completion. 
 

Exporting Invoices to Banner

On the Alma side, to begin exporting invoices to Banner you just need to setup the integration under General Configuration. You'll need to have an FTP definition in Alma to use for the configuration, and this will either be an FTP server maintained by your campus IT staff (if they'll be processing the exported data) or one maintained by your library (if you will be doing the data processing before sending the file to Banner). You're probably going to want to schedule this integration as well, though you can run it manually during testing.
 
On the Banner side, there are two challenges that you may encounter. The first is related to the Alma setting labeled "invoice_not_unique" in Acquisitions Configuration. If you have this set to false (so that your system allows for duplicate invoice numbers), then you'll need to be certain that your Banner system can handle non-unique invoice numbers. If not, then you'll probably need to arrive at a consistent naming scheme in Alma for non-unique invoice numbers from vendors. For instance, to accommodate this Banner restriction, we add an "a" to the end of the invoice number if it's been used previously by (this happens with certain vendors).
 
The second challenge concerns the length of the Alma invoice record number. Our Banner system uses an 8-digit field for the "document number", which is the core record number for each invoice. This worked fine with our previous system, where the system record number was very short. But Alma's 16-digit invoice record numbers were too long for this field, and it wasn't possible to expand it to accommodate this. This was a real issue, because it was critical for us to have the Alma record number in the Banner invoice, for two reasons:
 
  1. We were planning on exporting payment info back to Alma from Banner, and wanted to rely on the Alma record number as the match point for this.
  2. Having the Alma record number in Banner would enable library staff to easily find the matching record in Alma when conducting reconciliation in Banner. Without the Alma record number being in Banner, this would have been much more difficult and time-consuming.
 
We resolved this by adding a step to the Banner import process that generated a generic, auto-incrementing document number that picked up with the last number used in our previous system. We then opted to store the Alma invoice record number in an unused field in Banner, the "commodity description" field. 
 
Our last challenge concerned prorating additional charges related to invoices. When creating a new invoice, you have the option of prorating the additional charges (e.g. shipping) or not. If you do, then the charges are broken up among the individual PO lines. But if you don't, then the charges show up as line items of their own. When parsing the data on the Banner side, this can be accommodated based on your business rules. But it's important to be aware of this distinction when putting together the logic for the Banner import. In the end, we set up the Banner import script to prorate the charges in all cases - even if they appeared as their own lines items. 
 
Example: shipping charges as standalone line items
<invoice_line>

          <line_number>999991</line_number>

          <line_type>SHIPMENT</line_type>

          <quantity>0</quantity>

          <reporting_code>b</reporting_code>

          <total_price>7.28</total_price>

          <price>7.28</price>
 
Example: shipping charges prorated among line items - note how the <total_price> is higher than the <price>, as it includes the prorated <shipment_amount>
<additional_charges>

        <discount_amount>0.0</discount_amount>

        <insurance_amount>0.0</insurance_amount>

        <overhead_amount>0.0</overhead_amount>

        <shipment_amount>5.98</shipment_amount>

        <total_charges_amount>5.98</total_charges_amount>

      </additional_charges>

 <invoice_line_list>

<invoice_line>

          <line_number>1</line_number>

          <line_type>REGULAR</line_type>

          <quantity>1</quantity>

          <reporting_code>x</reporting_code>

          <total_price>43.34</total_price>

          <price>42.14</price>
 
In total, three fields from the Alma invoice mapped with direct 1-to-1 relationships to the invoice record in Banner:

Alma Invoice FieldBanner Invoice Field
invoice_numberinvoice
vendor_FinancialSys_codevendor
fund: external_idindex

In addition, all of the fields within the Alma invoice's "additional charges" element ("discount_amount", "insurance_amount", "overhead_amount", and "shipment_amount") were summed and stored in the "additional" field in the Banner invoice. And lastly, specific invoice lines were not mapped to the Banner invoice. Instead, individual invoice lines in the Alma invoice were grouped and summarized by index code, so that what was added to the Banner invoice was a sum total for each index code contained in the Alma invoice. For example, if there were 5 total lines in the Alma invoice, with 3 having external_id "LIBG30" and 2 having external_id "LIBG40", then the resulting Banner invoice would contain 2 lines - one for "LIBG30" with a sum total of charges for the 3 invoice lines with that external_id, and one for "LIBG40" with a sum total of charges for the 2 invoice lines having that external_id. None of the actual line items would be mapped to Banner.
 
After these challenges were dealt with and the data mapped correctly, campus IT staff created the script to parse the Alma XML data so that the it could be loaded into Banner using Oracle's SQL loader functions. After some iterative development and testing, this was in place and we were ready to move forwards with importing payment info from Banner.
 

Importing payments from Banner

Once the invoice information has been successfully imported into Banner and includes each invoice MMS ID, payment information can be exported back to Alma. The first step in setting this up is to look up the data dictionary and payment XSD file for the payment import on the Developer Network. Here you also find a sample XML file that you can share with the staff who will be creating the XML output from Banner.
 
It's important to read through the payment XSD file and determine which fields are required and which are optional. For all of the required fields, make sure that they are included and contain appropriate data in the incoming XML files. This is an important step because some of the required fields would not be obvious without consulting the XSD file. Potentially unexpected required fields include currency, foreign currency, exchange rate, and explicit rate. Making sure that staff know that these fields are required at the outset will save time in testing down the road. 
 
After this, it's simply a matter of slotting data from the Banner invoice/payment record into the XML export and setting up the integration to import this data from an FTP server defined in Alma. To extract the data from Banner, our campus IT staff used Oracle's XML functions to directly draw XML-formatted data from the Banner database. In addition, a trigger of some type will need to be setup for the export to happen at the right time. In our case, we actually set this up to happen immediately after the import into Banner was completed. Since we were most interested in using this as a means of setting up a cross-reference in each system relative to the other, this worked fine for our purposes and was easy for campus IT to setup. If you want to import true payment info, then a trigger would need to be setup on the Banner side so that when an invoice is actually paid, that data is exported to Alma. 
 
You're probably going to want to use the same FTP server that you used for the invoice export, though you may want to setup a different subfolder for the payment files, separate from the invoice files. And as long as you're importing payment info for the same invoices you exported to Alma, then the invoice records will be in the right status to accept the payment info. From there, just as with the invoice export, it's just a matter of iterative development as you and your developers work through refining the XML so that it imports correctly into Alma. 
 
This integration can be scheduled as well, which you will likely want to do. If so, the one catch is to be sure to schedule the Banner data export so that it will have sufficient time to run to completion. Since you only have specific scheduled times to choose from in Alma, you will end up needing to work around this.
 
In the end, successfully setting up both of these integrations hinged on working closely with the campus IT staff that were implementing the Banner import/export, and thoroughly understanding the XML data that Alma would work with. And ultimately, setting them up has saved a great deal of staff time both in maintaining the Library's invoice info in Banner, and reconciling invoices on an ongoing basis.