Invoice Data Mapping: Aleph to Alma
- Julie Brannon
Purpose: This page describes data value crosswalks and data element mapping for the migration of Invoice data from Aleph to Alma. This migration guide and video from ExLibris provide instructions to complete a migration form prior to the automated migration of Aleph data to Alma. The decisions we need to make about invoice data migration are outlined on this page.
Page Contents
Alma Invoice Structure
Alma invoices are comprised of invoice_details (mandatory) and invoice_lines (not mandatory). The migration of Aleph invoices creates:
- One Alma Invoice for each Aleph invoice header (Z77): Aleph invoice headers without attached invoice line item should not be migrated
- One Alma Invoice Line for each Aleph invoice line item (Z75) matching an invoice header (Z77).
- Invoice migration ensures the total invoice amounts are correct, not necessarily the net breakdowns.
- VAT is included in the total invoice amount. VAT details are mapped to Alma invoice and invoice lines notes (Z77_VAT_CODE, Z77_VAT_AMOUNT, 77_VAT_RECEIVER, Z75_VAT_CODE, and Z75_VAT_AMOUNT).
Crosswalks: Aleph code values to Alma code values
The following tables describe the crosswalk of Aleph code values for key invoice data elements to the target Alma code value.
Aleph Invoice Type to Alma Payment Method
The Aleph Invoice type maps to the Payment Method in Alma. The following section defines the steps needed to decide how we want to crosswalk the Aleph Invoice Type values to Alma Payment methods.
Next Steps
1. Review the list of Alma Payment Methods below to become familiar with them.
| Alma Payment Method Codes | Description |
| ACCOUNTINGDEPARTMENT | The payment method is determined by the institution's accounting department (Invoice in Alma cannot be defined as Pre-Paid with this payment method). |
| CASH | Payment is done using cash. |
| CREDITCARD | Payment is done using a credit card. |
| BANKTRANSFERS | Payment is done using bank transfers. |
| DEPOSITACCOUNT | Payment is done using a deposit account where the institution deposits money with the vendor in advance. |
| PREPAYMENT | Payment is transferred before the actual purchases take place. |
| SPECIALPAYMENT | Special payment as decided between the institution and the vendor. |
| ATTACHMENT | Payment method is described within the Attachment tab at the vendor account/Invoice. |
2. Decide which values to enable, decide on the "Description" labels that are seen by users, and choose a default value.
| Aleph Invoice Type Code | Aleph Description | Count (6/30/23) | Target Alma Payment Method | Alma Description Label - do we want to change any? |
| REG | Regular | 183,718 | ACCOUNTINGDEPARTMENT | Accounting Department |
| INT | Internal | 25,240 | ACCOUNTINGDEPARTMENT | Accounting Department |
| PRE | Prepaid | 4,784 | PREPAYMENT | Prepayment |
| PRO | Proforma | 1,304 | DEPOSITACCOUNT | Accounting Department |
| VOI | Void | 10 | ACCOUNTINGDEPARTMENT | Accounting Department |
| DEP | Deposit | 1 | DEPOSITACCOUNT | Deposit account |
*For the data migration, the default value when not mapped is ACCOUNTINGDEPARTMENT
Invoice Line Type
In Alma, there is an invoice line type which is a change from Aleph where we only had an invoice type on the header. Per Chana at ExLibris, Invoice lines from Aleph will be mapped as a "regular" line type. There are no recommended clean-up processes for invoice line types in Aleph prior to migration. The additional line types can be used in Alma moving forward and will be something we will explore in Acquisitions training.
The remainder of this section is a discussion which values to use going forward and doesn't impact data migration, but I'm putting it here as a placeholder. We'll need to decide whether we want to set the "split additional charges parameter" to true. This would allow individual invoice lines to have different types other than the ones that come out of the box.
Out of the box, in addition to the Regular line, the standard additional invoice-line types Shipment, Insurance, Overhead, Discount, and Other are automatically enabled for each invoice - see the screen shot below. We can enable and disable additional line types in the Invoice Line Types code table (Configuration Menu > Acquisitions > Invoices > Invoice Line Types). The Invoice Line Types option only appears in the menu when the customer parameter invoice_split_additional_charges is set to true, so if we want to change how this is setup we'll need to ask ExLibris to enable the split additional charges parameter so that we can access that in the Configurations area. The full list of possible invoice line types is available in the ExLibirs documentation here.
Aleph Invoice Status to Alma Payment Status
According to this Aleph to Alma migration guide, the Aleph Invoice Payment Status maps to the Alma Payment Status. The first table below lists the record counts in Aleph for each payment status.
| Z77_P_STATUS Payment status | Description | Count (10/3/23) |
|---|---|---|
| C | Paid and sent to R/3 | 216029 |
| Y | Payment authorization given | 311 |
| P | Paid | 4 |
| N | Not ready to be paid | 89 |
| R | Ready to be paid | 3 |
| null | 194 |
10/3/23: Per Chana at EL, they determine that the invoice is closed by the following mapping. I've included counts to list how many records in Aleph as of 10/3/23 match that criteria.
Next Step
Review the table below to determine whether any data cleanup is needed or if we're OK with invoices moving to Alma with the status values listed in the blue section of the table.
If Aleph invoice contains these values... | Migrate invoice to Alma as... | |||||
|---|---|---|---|---|---|---|
Z77_P_STATUS Payment status | Z77_I_DATE Invoice date | Z77_P_DATE Payment Date | Other/notes | Count of invoices matching this criteria in Aleph (10/3/23) | Alma Payment Status | Entry point in Alma |
| 'P' Paid | 4 | PAID | CLOSED | |||
| null | != 0 (an invoice date value exists) | != 0 (a payment date value exists) | 0 | PAID | CLOSED | |
| Any other custom values taken from tab48.lng (At Duke we use 'C' - Paid and sent to R/3) | != 0 (a payment date value exists) | 215451 | PAID | CLOSED | ||
| Any other custom values taken from tab48.lng (At Duke we use 'C' - Paid and sent to R/3) | = 0 (a payment date value doesn't exist) | 578 | PENDING | NEW | ||
| null | = 0 (an invoice date value doesn't exist) | Incomplete invoice header | 194 | Won't migrate to Alma | ||
| null | != 0 (an invoice date value exists) | = 0 (a payment date value doesn't exist) | 0 | PENDING | NEW | |
| 'N' Not ready to be paid | 89 | PENDING | NEW | |||
| 'R' Ready to be paid | Z77-APPROVAL_DEPARTMENT is null | 0 | PENDING | WAITING_APPROVAL | ||
| 'R' Ready to be paid | Z77-APPROVALDEPARTMENT is not null | 3 | PENDING | WAITING_SEND | ||
| 'Y' Payment notification sent | 311 | PENDING | WAITING_PAYMENT | |||
Data Mapping
This table describes the Alma target field for each Invoice field in Aleph. Note: Much of this content was copied from Harvard's data mapping guides, last updated in 2018. Please consider this page a draft until the Duke team verifies that this mapping information is current and concurs with Duke's migration decisions. For reference, Aleph invoice screenshots are included below.
| Aleph Field | Alma Field | Additional Information |
| Aleph General Invoice | ||
| Line items (count) | doesn't migrate to Alma | |
| Total Amount Gen. Invoice | Total Amount | |
| Total Amount Line Items | Total invoice lines amount | |
| Vendor Code | Vendor Code | |
| Invoice Number | Invoice Number | |
| Net Amount | doesn't migrate to Alma | |
| Shipment Amount | Shipment | |
| Overhead Amount | doesn't migrate to Alma | There is data in this field: 16,492 contain a value greater than zero; 198,865 records contain 0. (7/26/23) |
| Insurance Amount | doesn't migrate to Alma | 98 records contain a value greater than zero (7/26/23) |
| Discount Amount | doesn't migrate to Alma | 7510 records contain a value greater than zero (7/26/23) |
| Total Amount | Total amount | VAT is included in the total invoice amount. |
| Total Amount incl. VAT | ||
| Local Amount | ||
| Refers to Invoice | Invoice Reference # | |
| Type | Notes?, Payment method - see crosswalk above | Do we want to map the Aleph value to Notes for reference? This value also sets the Payment Method in Alma. See crosswalk above. |
| Status | Status | |
| Currency | Included in Total invoice lines amount | |
| Explicit Ratio | doesn't migrate to Alma | |
| Debit | Total invoice lines amount does not include a minus sign. | |
| Credit | Total invoice lines amount includes a minus sign. | |
| Invoice Date | Invoice Date | |
| Received Date | doesn't migrate to Alma | |
| Shipment Date | doesn't migrate to Alma | |
| VAT Recipient | Not used in Aleph - all records null in 77_VAT_RECEIVER as of 7/26/23 | VAT details are mapped to Alma invoice and invoice lines notes (Z77_VAT_CODE, Z77_VAT_AMOUNT, 77_VAT_RECEIVER, Z75_VAT_CODE). |
| VAT Percent | n/a | |
| VAT Amount | VAT details are mapped to Alma invoice and invoice lines notes | |
| VAT per Line Item | n/a | |
| Add VAT to Amount | n/a | |
| Note | Notes Tab | |
| Payment Date | Payment Date | |
| Check Number | Payment Identifier | |
| Amount | Payment Amount | |
| Status | Payment Status | Values in Alma are
|
| Approval Dep. | Notes Tab "Approved by <Approval department> | 10/3/23 Notes from Chana on Basecamp: "All invoices will be migrated at the institutional level with institution as the owner. We check Z77_APPROVAL_DEPARTMENT if it's null to determine whether an invoice is WAITING_APPROVAL and in a note to indicate "Approved by … <value in Z77_APPROVAL_DEPARTMENT>. So the information will end up in a note field on the invoices." |
| Approval Number | Notes Tab | |
| Aleph Line Item Form | ||
| Vendor Code | Vendor | |
| Invoice Number | In banner at top of screen | |
| Net Amount | Price | |
| Estimated Price | n/a | |
| Added Amount | n/a | |
| Currency | Included in Amount | |
| Total Amount | Total Price | |
| Object Code | Reporting Code | |
| Total, Incl. VAT | n/a | |
| VAT Percent | n/a | |
| Local Amount | n/a | |
| VAT Amount | n/a | |
| Number of Units | Quantity | |
| Debit | Invoice line amount does not include a minus sign. No - in Total invoice lines amount | |
| Credit | Invoice line amount does not include a minus sign. | |
| Note | Note | |
| Aleph Invoice Line Items columns | ||
| Seq. | Line # | |
| Order No. | PO Line # | |
| Net Amt. | Price | |
| Total Amt. | Total Price | |
| Note: | Note | |
| Budget | Funds | |
| Object Code | Reporting Code | |
| Local Amt. | Allocated Fund Transaction - Amount | |
| Units in Ord | Available in invoice line view | |
| Units in Inv | Available in invoice line view | |
Aleph Invoice Screenshots
General Invoice Screen:
Payment Screen
Invoice fields that don't migrate to Alama
Invoice header
- Z77-CREDIT-DEBIT
- Z77-I-CURRENCY-RATIO
- Z77-I-REC-DATE
- Z77-I-SHIP-DATE
- Z77-I-NO-ITEMS
- Z77-I-NET-AMOUNT (Net amount of invoice)
- Z77-I-SHIP-AMOUNT (Added charges)
- Z77-I-OVER-AMOUNT (Added charges)
- Z77-I-INSU-AMOUNT (Added charges)
- Z77-I-DISC-AMOUNT (Amount discounted)
Invoice line
- Z75-DOC-NUMBER
- Z75-SEQUENCE
- Z75-VENDOR-CODE
- Z75-INVOICE-NUMBER
- Z75-I-CREDIT-DEBIT
- Z75-I-DATE-RANGE
- Z75-I-NET-AMOUNT