...
Attached below you will find a data-flow model that describes what data is being passed between this module and other modules within FOLIO. You will also find an Entity Relationship Diagram that illustrates what specific data elements are being stored in this module and what data elements are being retrieved from other modules. The dark grey tables indicate elements that are hard coded.
Interface Fields:
Invoice
| Columns | Relates to: | Data type | Req? | Repeatable? | Searchable | Filter on this? | Hardcoded/Editable? | Can edit post approval | Mapped from Order | Values | Notes |
| approved_by | user.id | UUID | R | N | Y | N | API | N | N/A | Will be captured by the system when an invoice is approved. Will only display in invoice details after the invoice has been approved | |
| chk_subscription_overlap | bool | N | N | N | N | Checkbox | N | IF TRUE the system will check if there is another invoice for this subscription and whether the dates overlap. IF the dates overlap, the system should issue an alert. Default should be Ture | |||
| date_created | date | Y | N | Y | Y (Date Range would be ideal) | Date | N | N/A | Would like to be able to search and fitler by a range. | ||
| created_by | user.id | string | Y | N | Y | N | API | N | N/A | ||
| currency | currency | string | Y | N | N | N | API | N | N/A | Ideally this is the ISO code and not something the user defines | |
| folio_invoice_no | string | Y | N | Y | N | Generated | N | N/A | |||
| invoice_date | date | Y | N | Y | Y (Date Range would be ideal) | Date | N | N/A | |||
| note | string | N | N | N | N | Free Text | Y | N/A | |||
| acquisitions_unit (formerly owner, team, approved_by) | library.id | string | Y (Option in Settings to not Require) | N | N | Y | API | Y | N/A | The location/branch/division etc that's responsible for this invoice. This may or may not be the same as the PO. ie. North Branch | |
| payment_due | date | N | N | N | Y (Date Range would be ideal) | Date | Y | N/A | When this is required to be paid. Generally governed by the relationship with the Vendor | ||
| payment_terms | string | N | N | N | N | Free Text | N | N/A | Eg. net 30... | ||
| payment_method | payment_method_id | string | Y | N | N | Y | Editable | Y | N/A | Inherited from vendor record | |
| prepaid | bool | Reference: pg. 134: FOLIO Plan of Attack. This field would be better served on the order | |||||||||
| prorata | bool | N | N | N | N | Checkbox | IF TRUE the additional charges will be applied to each line item and reflected in the total. IF FALSE each charge will be added to the invoice as it's own line item. (Default would be Yes) Ideally this can be done by price. | ||||
| approvalDate | date | Y | N | N | Y (Date Range would be ideal) | Date | N | N/A | Date the invoice was approved for processing | ||
| paidDate | date | Y | N | N | Y (Date Range would be ideal) | Date | N | N/A | Date the invoice transitioned to the status "Paid" | ||
| status | invoice_status | string | Y | N | N | Y | Enumerated | Y | N/A | Open: Record has been created, Reviewed: details have been verified, Approved: encumbered Funds are released and set as awaiting payment, Paid: funds are expended confirmation that funds have been exchanged and available details have been returned from AP system, Cancelled: invoice will not be process for payment. Note: invoices are never partially paid. | |
| source | string | Y | N | N | Y | Enumerated | N | N/A | User, API, EDI | This does not denote a user ID but describes how the record was created. Eg. Manually, EDIFACT, MARC etc | |
| total | decimal | N | N | N | N | Calculated | N | N/A | invoice amount after adjustments | ||
| adjustment-total | decimal | N | N | N | N | Calculated | N | N/A | sum of invoiceLine adjustments + (non prorated) invoice adjustments | ||
| sub-total | decimal | N | N | N | N | Calculated | N | N/A | invoice amount before invoice adjustments | ||
| lock-total | boolean | N | N | N | N | Editable | N | N/A | indicates that the total was keyed in and shouldn't be updated based on calculations. The calculated total must match this before the invoice can be approved | ||
| vat_per_invoice_line | bool | IF some of the invoice lines require vat, but others do not, this should be set to TRUE.(Remove) | |||||||||
| vendor_invoice_no | string | Y | N | Y | N | Editable | Y | N/A | this is the number from the vendor's invoice this mayor may notbe the same as the invoice_no | ||
| workflow_status | workflow_status | string | pending, in review, reviewed, approved, not approved update required, declined, cancelled. | ||||||||
| disbursement_number | string | N | N | Y | N | Editable | The identifier for the physical transaction corresponding to a payment (Eg. Check #, EFT # etc.) Brought in from external source | ||||
| voucher_number | string | Y | N | Y | N | Editable | N | N/A | Number generated by folio that will eventually identify the payment request sent out to external financial system. | ||
| payment(voucher)_id | uuid | Y | N | N | N | API | N | N/A | |||
| disbursement_date | date | Y (if you have a disburment #) | N | N | Y (Date Range would be ideal) | Editable | Date payment was made from financial system (eg. corresponding check date) | ||||
| po_numbers | string | N | Y | Y | N | API | N | po_number | May or may not be provided; references the PO associated to the invoice | ||
| vendor_contact_id | contact_info.id | uuid | THIS seems iffy. Should be reviewed. | ||||||||
| vendor_id | vendor.id | uuid | Y | N | Y | N | API | N | N/A | Should be shown in the UI as a Vendor Name or CODE | |
| accounting_code | string | Y | N | Y | N | API | Y | N/A | pulled based on selected Vendor and associated invoice lines. Would be a select list in there are multiple account numbers assciated with the invoice lines. However, there can only be one accounting code defined for the Invoice. Note: This should allow you to select OR input a code manually. | ||
| export_to_accounting | bool | N/A | N | N | Y | Checkbox | N | N/A | This would keep the invoice from being feed into the batch process (Not generate a external voucher/payment) but would still move values in the system. Note: this is ideally defined by the vendor relationship and exposed for override on the invoice. (Not yet added to Vendor Information) | ||
| Bill to | string | N | N | N | N | Y | Location that is used for us that we are feeding to the university system - May also be referenced from the order but invoice would be the authority. Should store in invoice but populate initially by first associated PO bill to address if possible. | ||||
| Ship to | string | Relevant only as reference from the Order | |||||||||
| adjustments | object | N | R | N | N | N | array of adjustments | ||||
| eclosureNeeded | boolean | N | N | N | N | boolean | N | When payment type is check this would indicate that an enclosure is needed for the check | |||
| Invoice Line | |||||||||||
| Columns | Relates to: | Data type | Req? | Repeatable? | Searchable | Filter on this? | Hardcoded/Editable? | Values | Notes | ||
| description (Title) | string | Y | N | N | N | Editable | Y | Title | Title as capured in the Purchase Order Line. In the absence of a POL link this could be used to describe the Invoice line | ||
| po_line_ids | uuid | N | N | Y | N | API | N | pol_number | UUID of the POL displayed as POL number | ||
| invoice_line_number | string | Y | N | Y | N | Generated | N | N/A | Start at one and increase similar to POL Number. 999 would be a reasonable max | Sequenstially generated and not editable by the user. May have as many as 800 on an order at Chicago | |
| invoice_line_status | string | Y | N | N | Y | Set by System | Y | N/A | This will mirror the invoice status but include an error status to flag lines the were loaded automatically and need attention. Possibly missing a POL link. | ||
| vendor_ref_no | string | N | N | N | N | API | Y | vendor_ref_no | This is the number used to match POLs created by import to invoices created by import. This will need to be stored in the invoice as there may not be an order in unique situations | ||
| account_number | string | N | N | N | N | API | N | account_number | Taken from POL or based on selected Vendor. This will be a select list as a Vendor could have more than one account number | ||
| accounting_code | string | N | N | N | N | API | N | accounting_code | pulled based on account number. | ||
| product_id | string | N | N | N | N | API | N | product_id | Item information pulled from PO Line | ||
| product_id_type | string | N | N | N | N | API | N | product_id_type | Item information pulled from PO Line | ||
| comment | string | N | N | N | N | Editable | Y | notes | |||
| total | decimal | N | N | Y | N | Calculated | N | N/A | includes adjustments | ||
| adjustment-total | decimal | N | N | N | N | Calculated | N | sum of the invoiceLine adjustments (including prorated) | |||
| sub-total | decimal | Y | N | list price x quantity | invoice amount before adjustments | ||||||
| quantity | decimal | Y | N | N | N | N | quantity physical + electronic | not used in calculation of total | |||
| release_encumbrance | bool | Y | N | N | N | checkbox | Y | N/A | If this is intended to not only release encumbrances but to indicate that this is the final invoice. Should the label be changed? This should take into account the fiscal year. We may release encumbrance for this FY but for on-going orders expect to encumber again next year. | ||
| subscription_info | string | N | N | N | N | Editable | N | N/A | the user should use this field to provide info on the subscription (eg. which volumes are being invoiced). | ||
| subscription_start | date | N | N | N | Y (Date Range would be ideal) | Date | N | subscription_from | |||
| subscription_end | date | N | N | N | Y (Date Range would be ideal) | Date | N | subscription_to | |||
| invoice_id | uuid | Y | N | N | N | N | N/A | Connection to the invoice from the invoice line | |||
| tags | Y | N | Y | Y | Editable | Y | POL Tags | ||||
| fund_distributions | Fund distributions | object | Y | Y | N | N | API | N | fund_distributions | Only valid at invoice level | |
| adjustments | object | N | R | N | N | N | N/A | array of adjustments | |||
| description | Adjustments | string | Y | N | N | N | Editable in settings | N | N/A | Displayed in invoice line as a name for the input | |
| type | Adjustments | enumeration | Y | N | N | N | Editable in settings | N | N/A | Percentage, Amount | Indicates what the amount has been reported in (currency or percentage) |
| value | Adjustments | number | Y | N | N | N | Editable | N | N/A | Amount of adjustment | |
| pro_rate | Adjustments | enumeration | Y | N | N | N | dropdown | N | N/A | By amount, By line, By quantity, Not prorated | Displayed in invoice per adjustment in toggled on in settings. Only valid at invoice level |
| relation_to_total | Adjustments | enumeration | Y | N | N | N | dropdown | N | N/A | In addition to, Included in, Separate from | In addition to: added to the subtotal, Included in: reported as a portion of the subtotal, Separate from: Calculated based on the subtotal and reported, but not included in the invoice total. |
| always_show | Adjustments | boolean | Y | N | N | N | Boolean | N | N/A | Allow this adjustment to be added to all invoices by default | |
Voucher:
| Columns | Relates to: | Data type | Req? | Repeatable? | Show on invoice | Searchable | Filter on this? | Hardcoded/Editable? | Keep in sync with Invoice field | Values | Notes | |
| id | uuid | Y | N | N | N | N | unique id for this record | |||||
| system_currency | currency | string | Y | N | N | N | N | API | The currency in which the voucher was paid | |||
| invoice_currency | Y | N | N | N | N | API | Currency invoice was originally defined in | |||||
| exchange rate | N | N | Y | N | N | Editable | Y | Exchange rate used to generate transaction. Should be editable by the user and the system in case this needs to be updated | ||||
| export_to_accounting | bool | Y | N/A | Y | N | Y | Checkbox | Export this voucher record to a financial system if connected properly. Setting inherited from invoice | ||||
| sentToAccounting | bool | Y | N/A | Y | N | Y | Checkbox | This voucher has been sent in a batch to the AP system | ||||
| voucher_type | payment_type | string | Y | N | Y | N | Y | Enum | Payment, Pre-payment, Credit, | Indicates type of payment to account for pre-payment and other unique payments, | ||
| voucher_status | payment_status | string | Y | N | Y | N | Y | Enum | Awaiting payment, Paid | Awaiting Payment, Paid | ||
| voucher_amount | decimal | Y | N | Y | Y | N | Calculated | The amount that is actually paid; in FOLIO system currency | ||||
| voucher_date | date | N | N | Y | N | Y | Calculated | The date the invoice status changes to approved | ||||
| voucher_no | string | Y | N | Y | Y | N | Generated | Assigned sequentially by FOLIO; if manually edited, then do not skip any real sequential number (different from what we do for POs); need setting to keep this field from being editable if library does not allow override; if dropped out of automated processing or manually created, ignore the sequential number generator and make the voucher number editable. * Format must be alpha numberic only * Editable if gerated manually - in these situations a number should not be generated in the first place * No perscribed character limit * User can define format and starting value in the sequence ** Eg. (R36001, R36002) | ||||
| batch_no | string | Y* | N | N | N | N | Generated | Only required when voucher is being output automatically; date formatted or allow library to specify structure and start | ||||
| batchGroup | batch group ID that displays as name | Y | Eg. “PERKLIB” “MEDLIB” ”LAWLIB” or “FORDLIB” Derived from invoice data | |||||||||
| invoice_id | uuid | Y | Y | N | N | N | API | Display invocie number, date and total on the voucher. A voucher could relate to many invoices but still roll them up by external account numbers at the voucher line level. All will need to ref the same accounting code and be in the same currency | ||||
| user_id | uuid | Y | N | N (Only if allowed in settings) | N | Y | API | Potentially need to capture a user id when possible. Need to decide what the ID would be when it's system or EDI. This is the ID of the user who transitioned the invoice to approved. (Or manually created the voucher if that is possible) | ||||
| voucher_lines | array | Y | Y | Y | N/A | N/A | Generated | |||||
| accounting_code | Y | N | Y | N | N | Generated | Y | Number that represents the vendor is an external accounting system which may include details like "Address code" in it. This is the number as stated on the invoice. (Consider specifying Vendor Accounting Code in the label to make clear) | ||||
| disbursement_number | string | N | N | Y | Y | N | Editable | The identifier for the physical transaction corresponding to a payment (Eg. Check #, EFT # etc.) Brought in from external source | ||||
| disbursement_date | date | Y (if you have a disburment #) | N | Y | N | Y (Date Range would be ideal) | Editable | Date payment was made from financial system (eg. corresponding check date) | ||||
| disbursement_amount | decimal | N | N | N | N | N | Editable | The amount of the payment made outside of FOLIO | ||||
| Voucher Line | ||||||||||||
| external_account_number | string | Y | N | Y | Y | N | API | Captured base on fund distributions on the invoices lines. All distributions that come flrom funds with the same account number are grouped by voucher line | ||||
| voucher_line_amount | decimal | Y | N | Y | Y | N | Calculated | Total of the fund distributions that share an external account number from the invoice | ||||
| source_id | inovice_line.id | uuid | Y | Y | Y | N | N | API | There could be many invoice lines against a single voucher line. These are group on a voucher line based on "External account number" | |||
| fund_group? | Certain institution may need to see the FUnd group identified on the voucher and some may not. Will discuss after revisiting Finance app | |||||||||||
| fund_id | Y | Y | N | Y | API | |||||||
| fund_distributions | uuid | Y | Y | N | N | N | API | Amount associated with that particular fund. Not required by all institutions but may need to display on voucher. This is not stored in the voucher but referenced from the invoice | ||||
| sub_transaction_id | uuid | Y | Y | N | N | N | API | could be multiple UUIDs if the payment is draw from multiple funds $50, broken into $20 and $30 | ||||
| Batch export | ||||||||||||
| batchID | string | Y* | N | N | N | N | Generated | UUID for batch | ||||
| batchGroup | batch group ID that displays as name | Eg. “PERKLIB” “MEDLIB” ”LAWLIB” or “FORDLIB” Derived from invoice data | ||||||||||
| dateCreated | Date and time batch was created | date | Y | N | Y | N | Y | Generated | The date and time the batch was created. This will also be used as human readable ID | |||
| Start | ||||||||||||
| End | ||||||||||||
| Exported Voucher | ||||||||||||
| voucherNo | “H” | |||||||||||
| folioInvoiceNo | N | Folio invoice number that will provide exact match for the user if searching for the source of the voucher | ||||||||||
| invoiceID | Reference - Vendor invoice number | Invoice number | ||||||||||
| accountingCode | AP Vendor identifier | R3 Vendor Code | ||||||||||
| batchGroup | batch group ID that displays as name | Eg. “PERKLIB” “MEDLIB” ”LAWLIB” or “FORDLIB” Derived from invoice data | ||||||||||
| voucherType | payment_type | string | Y | N | Y | N | Y | Enum | Payment, Pre-payment, Credit, | Indicates type of payment to account for pre-payment and other unique payments, | ||
| voucherStatus | payment_status | string | Y | N | Y | N | Y | Enum | Awaiting payment, Paid | Awaiting Payment, Paid | ||
| voucherAmount | decimal | Y | N | Y | Y | N | Calculated | The amount that is actually paid; in FOLIO system currency | ||||
| voucherDate | date | N | N | Y | N | Y | Calculated | The date the invoice status changes to approved | ||||
| systemCurrency | currency | string | Y | N | N | N | N | API | The currency in which the voucher was paid | |||
| invoiceCurrency | Y | N | N | N | N | API | Currency invoice was originally defined in | |||||
| exchangeRate | N | N | Y | N | N | Editable | Exchange rate used to generate transaction. Should be editable by the user and the system in case this needs to be updated | |||||
| invoiceNote | Invoice notes | Populated based on contents of Aleph invoice note field. If contains “PI” for pay immediately, set this to “N00”; otherwise blank | This could also be used to indicate that a check may need to have an enclosure (ENC) Some would indicate they need to pick it up and enclose it themselves | |||||||||
| vendorName | Name of Vendor (1 time) | Should be retrievable with accountCode | ||||||||||
| disbursementNumber | string | N | N | Y | Y | N | Editable | The identifier for the physical transaction corresponding to a payment (Eg. Check #, EFT # etc.) Brought in from external source | ||||
| disbursementDate | date | Y (if you have a disburment #) | N | Y | N | Y (Date Range would be ideal) | Editable | Date payment was made from financial system (eg. corresponding check date) | ||||
| disbursementAmount | decimal | N | N | N | N | N | Editable | The amount of the payment made outside of FOLIO | ||||
| voucherLines | array | Y | Y | Y | N/A | N/A | Generated | |||||
| eclosureNeeded | boolean | N | N | N | N | N | boolean | Inherited from When payment type is check this would indicate that an enclosure is needed | ||||
| Exported Voucher lines | ||||||||||||
| externalAccountNumber | string | Y | N | Y | Y | N | API | Captured base on fund distributions on the invoices lines. All distributions that come flrom funds with the same account number are grouped by voucher line | ||||
| adjustments | Amount of payment that is Tax or discount and what type of tax it is. | Describes the related adjustments so steps can be taken to account for any associated taxes. Do we need a total? Could we indicate in adjustment that it needs to be a shown on voucher? | Need to report adjustments for each voucher line. Should be percentage for percentage and can be amount for the amounts. | |||||||||
| voucherLineAmount | decimal | Y | N | Y | Y | N | Calculated | Total of the fund distributions that share an external account number from the invoice | ||||
| fundCode | Y | Y | N | Y | API | The Fund code should provide desired context | ||||||
| Small group indicated this detail isn't necessary in voucher. | ||||||||||||
Data Flow(s):
ERD (Entity Relationship Diagram):
...