Batch invoicing in ALMA
Invoicing is an important step of ALMA Acquisition. The correct invoices help managing Acquisition data and tracking the payment history. After the purchasing step, receiving physical items step or activating electronic titles step, the invoicing step jumps on the stage.
There were 3 ways to process the invoices, which encompass creating invoices via EDI, creating invoices from PO/manually, and creating invoices from the Excel file. The Excel file format provided by ALMA adopts an interlacing way to store the invoice and invoice line data. By this way, you could put as more invoice lines as you like, with huge advantage. However, before Excel file was uploaded, you had to select the vendor. It probably means you have to organize your invoice Excel files vendor by vendor before importing to ALMA.
Due to the requirements to process hundreds of invoices in batch, a PHP script was written to create the Invoices, then to add the corresponding invoices lines data by using ALMA API. A new Excel file format was used to achieve the target. Each line in this Excel table contains 3 chunks of data which are the invoice, the regular invoice line, and the shipment invoice line. In very rare situations, other invoice line types will be used, so only the regular and shipment type were included.
The PHP script will parse the Excel file line by line. For each line, the script will read the data, generate XML invoice data, and create an invoice in ALMA. Then the script will generate the regular and shipment invoice line data in XML format and imported to ALMA by using the invoice id generated by previous step.
The Excel data file name is invoice_full.xlsx. If you like, you could change it. However, you have to change the Excel data file name in code either.
The invoice data reminder:
- The invoice created is standalone itself and not affiliated with any PO Lines. The invoices build the connection with PO lines through added invoice lines.
- Vendor: the correct abbreviation code was needed.
- Invoice date: Please add Z at the end of date, like 2021-06-06Z
- Owner: it must match the owner code in ALMA
The invoice line data reminder:
- Type: must be REGULAR or SHIPMENT
- Number: must be the number without any alphabetical letter.
- subscription_from_date: please add Z at the end of date, like 2021-06-06Z.
- subscription_to_date: same as above.
- fund_code: must match the one in ALMA.
- Percent and amount: please use either percent field or amount field, not both of them. If you use both of them, it will fail.
This script got many helps from Tamar Fuches’ blog “Creating an invoice using APIs” which is in the link of https://developers.exlibrisgroup.com/blog/creating-an-invoice-using-apis/. It was highly appreciated.
The article by Tamar mentioned 3 steps of invoicing, which were creating an invoice, adding invoice lines, and processing invoice. However, I found the 3rd step “process invoice” didn’t make any difference. With or without 3rd step, either way, I have to click edit button to add edit choice into invoice line.
As regards of creating invoices, another difference between API and manually processing is that after receiving the one-time item, manually creating the invoices in ALMA can close the POL automatically. However, using API to create the invoices and invoice lines has no way to close POL automatically. If using API, you have to manually close the POL or use the job to close POL.
The script was coded by using PHP. A third party PHP code was used which is simpleXLSX.php to process the Excel file. The environment is Windows 7 + PHP 5.6.26. The Excel file version is Microsoft Excel 2010. You need to replace the API key with the one of your institutions in PHP code.
The code is in https://github.com/andytang2008/invoicing
The echo lines in the programming codes were reserved for the convenience of debug. I would recommend running the script in DOS command window like below, so that you can check the results in the file of 1.txt. The data file invoice_full.xlsx was also included in GitHub for the reference.