-
Notifications
You must be signed in to change notification settings - Fork 31
Cookbook: Finances
- 1. General
- 2. Querying financial data from the FOLIO App Finance
- 3. Relations between purchase order and invoice
- 4. Vouchers
- 5. Organizations
The Finance app allows you to create fund structures and manage money.
[top]
The transactions from the Finance app provide reliable data on the financial flows within FOLIO. You can also use the data from the different apps, but these may be inaccurate. Within the app, transactions are created that are assigned to a fiscal year, budget, ledger, fund and, if applicable, an expense class. In the various FOLIO apps, where financial data play a role, a UUID can be used to link to a specific transaction in the Finance app.
[top]
Foreign currencies can be entered in the apps in FOLIO. These are converted into the set system currency in the Finance app transactions. The exchange rate can be generated automatically or entered manually.
[top]
A fiscal entity used to track transactions against a specific purpose or function within a Fund. Optional, tenant-defined, and can be assigned to one or more funds. Applied to order lines and invoice lines during fund distribution. Each fund can support multiple expense classes.
[top]
All queries shown here are for Metadb.
In FOLIO there is the possibility to use a fund over several fiscal years. This is the case, for example, when a Fiscal Year Rollover (FYRO) is to carry over the encumbrances into the new year. Because a fund is linked to a UUID and it remains the same after FYRO, the tables must be linked in a specific way.
Example:
SELECT
*
FROM
folio_finance.fiscal_year
LEFT JOIN folio_finance.budget ON budget.fiscalyearid = fiscal_year.id
LEFT JOIN folio_finance.fund ON fund.id = budget.fundid
LEFT JOIN folio_finance.fund_type ON fund_type.id = fund.fundtypeid
LEFT JOIN folio_finance.ledger ON ledger.id = fund.ledgerid;
Since this query occurs more frequently, the Reporting SIG has created a derived table for it. You can find the sql statement in the repository in the directory sql_metadb/derived_tables/ in the file finance_funds.sql.
[top]
A common use case is the viewing of transaction data and the associated invoices from the FOLIO app Invoices. A typical use case can be to see how amounts are recorded in system currency, if you used a foreign currency in the invoice. The linking is realized via UUIDs that link between the apps.
Example 1:
SELECT
*
FROM
folio_finance.transaction
LEFT JOIN folio_invoice.invoices ON invoices.id = jsonb_extract_path_text(transaction.jsonb, 'sourceInvoiceId')::uuid
LEFT JOIN folio_invoice.invoice_lines ON invoice_lines.id = jsonb_extract_path_text(transaction.jsonb, 'sourceInvoiceLineId')::uuid
LEFT JOIN folio_finance.fund AS from_fund ON from_fund.id = transaction.fromfundid
LEFT JOIN folio_finance.fund AS to_fund ON to_fund.id = transaction.tofundid
LEFT JOIN folio_finance.budget ON budget.fundid = transaction.fromfundid
AND budget.fiscalyearid = transaction.fiscalyearid
LEFT JOIN folio_organizations.organizations ON organizations.id = jsonb_extract_path_text(invoices.jsonb, 'vendorId')::uuid
WHERE
(
jsonb_extract_path_text(transaction.jsonb, 'transactionType') = 'Pending payment'
OR jsonb_extract_path_text(transaction.jsonb, 'transactionType') = 'Payment'
OR jsonb_extract_path_text(transaction.jsonb, 'transactionType') = 'Credit'
);
Since this query occurs more frequently, the Reporting SIG has created a derived table for it. You can find the sql statement in the repository in the directory sql_metadb/derived_tables/ in the file finance_transaction_invoices.sql.
Example 2:
SELECT
invoice_id,
invoice_line_id,
invoice_vendor_name,
invoice_date,
invoice_line_total,
invoice_currency,
invoice_exchange_rate,
transaction_amount,
transaction_currency,
transaction_from_budget_name,
transaction_from_fund_name
FROM
folio_derived.finance_transaction_invoices
In this example you can see that you only have to use the prepared sql statement from finance_transaction_invoices.sql. Here we selected only some attributes from this derived table.
[top]
The transaction amounts for credits are always positive in the transaction table. If you want to use these for calculations, you must build a case distinction into your database query to get negative amounts. The following example shows how the calculation is carried out within the case distinction.
In addition, it may be necessary to merge the fund IDs from_fund_id and to_fund_id into one column. Especially for credits, the fund ID is stored under to_fund_id. This allows you to better use the UUID for funds.
Example:
SELECT
transaction__t.id AS transaction_id,
CASE WHEN transaction__t.transaction_type = 'Credit'
THEN
transaction__t.to_fund_id
ELSE
transaction__t.from_fund_id
END AS effective_fund_id,
transaction__t.amount AS transaction_amount,
CASE WHEN transaction__t.transaction_type = 'Credit'
THEN
transaction__t.amount :: NUMERIC(19,2) * -1
ELSE
transaction__t.amount :: NUMERIC(19,2)
END AS effective_transaction_amount,
transaction__t.currency AS transaction_currency,
transaction__t.fiscal_year_id,
transaction__t.source_invoice_line_id AS source_invoice_line_id,
transaction__t.expense_class_id AS transaction_expense_class_id,
expense_class__t.code AS expense_class_code,
expense_class__t.name AS expense_class_name,
expense_class__t.external_account_number_ext
FROM
folio_finance.transaction__t
LEFT JOIN folio_finance.expense_class__t ON expense_class__t.id = transaction__t.expense_class_id
WHERE
(
transaction__t.transaction_type = 'Pending payment'
OR transaction__t.transaction_type = 'Payment'
OR transaction__t.transaction_type = 'Credit'
)
[top]
Sometimes you want to see in the transactions which encumbrances were recorded in system currency for an order.
Example:
SELECT
*
FROM
folio_finance.transaction
LEFT JOIN folio_orders.po_line ON po_line.id = jsonb_extract_path_text(transaction.jsonb, 'encumbrance', 'sourcePoLineId')::uuid
LEFT JOIN folio_orders.purchase_order ON purchase_order.id = jsonb_extract_path_text(transaction.jsonb, 'encumbrance', 'sourcePurchaseOrderId')::uuid
LEFT JOIN folio_finance.fund AS from_fund ON from_fund.id = transaction.fromfundid
LEFT JOIN folio_finance.budget ON budget.fundid = transaction.fromfundid
AND budget.fiscalyearid = transaction.fiscalyearid
LEFT JOIN folio_organizations.organizations ON organizations.id = jsonb_extract_path_text(purchase_order.jsonb, 'vendor')::uuid
WHERE
jsonb_extract_path_text(transaction.jsonb, 'transactionType') = 'Encumbrance';
Since this query occurs more frequently, the Reporting SIG has created a derived table for it. You can find the sql statement in the repository in the directory sql_metadb/derived_tables/ in the file finance_transaction_purchase_order.sql.
[top]
There is a connection between an order line and an invoice line via a UUID.
Example:
SELECT
*
FROM
folio_invoice.invoice_lines
LEFT JOIN folio_orders.po_line ON po_line.id = jsonb_extract_path_text(invoice_lines.jsonb, 'poLineId')::UUID
Metadb offers so-called transformed tables that represent the content of JSON objects relationally. The same query with transformed tables would look like this. However, the result is a relational representation.
Example with transformed tables:
SELECT
*
FROM
folio_invoice.invoice_lines__t
LEFT JOIN folio_orders.po_line__t ON po_line__t.id = invoice_lines__t.po_line_id
Please ensure that you select the attributes sensibly, otherwise a cross product could occur.
[top]
There is a table in the schema orders with the name order_invoice_relationship. In this table you can find the direct connection between a order and an invoice.
Example JSON object:
{
"id": "963a6c4f-7244-4ee5-b33f-e0f54e38c7bc",
"invoiceId": "ee709d6b-af8a-4b8a-8293-295f8fbffb94",
"purchaseOrderId": "74fac919-fa47-4146-ad92-039a4945b56a"
}
Example:
SELECT
*
FROM
folio_orders.order_invoice_relationship__t
You can use these UUIDs for the connection. However, you should know that the individual connections between the order line and invoice line will be lost.
[top]
The typical payment chain also exists in FOLIO: Invoice > Transaction > Voucher. This means you have an invoice that you pay. The moment you approve the invoice, a transaction is created. Later you have to transfer the invoice to pay status. After the transaction the system creates a voucher with voucher lines. The following example shows how to query the information about the vouchers. In this example we first look at the voucher lines and connect them to the record for the voucher.
Example:
SELECT
voucher_lines.id AS voucher_line_id,
jsonb_extract_path_text(fund_distributions.jsonb, 'value') :: NUMERIC(19,2) AS voucher_line_value,
jsonb_extract_path_text(fund_distributions.jsonb, 'distributionType') AS voucher_line_distribution_type,
jsonb_extract_path_text(fund_distributions.jsonb, 'fundId') :: UUID AS fund_id,
jsonb_extract_path_text(fund_distributions.jsonb, 'code') AS voucher_line_fund_code,
jsonb_extract_path_text(fund_distributions.jsonb, 'expenseClassId') :: UUID AS voucher_line_expense_class_id,
jsonb_extract_path_text(fund_distributions.jsonb, 'encumbrance') :: UUID AS voucher_line_encumbrance_id,
jsonb_extract_path_text(fund_distributions.jsonb, 'invoiceLineId') :: UUID AS invoice_line_id,
jsonb_extract_path_text(voucher_lines.jsonb, 'externalAccountNumber') AS external_account_number,
vouchers.id AS voucher_id,
jsonb_extract_path_text(vouchers.jsonb, 'voucherDate') :: TIMESTAMPTZ AS voucher_voucher_date,
jsonb_extract_path_text(vouchers.jsonb, 'voucherNumber') :: INTEGER AS voucher_voucher_number,
jsonb_extract_path_text(vouchers.jsonb, 'status') AS voucher_status,
jsonb_extract_path_text(vouchers.jsonb, 'amount') :: NUMERIC(19,2) AS voucher_total_amount,
jsonb_extract_path_text(vouchers.jsonb, 'systemCurrency') AS voucher_system_currency,
jsonb_extract_path_text(vouchers.jsonb, 'invoiceCurrency') AS voucher_invoice_currency,
jsonb_extract_path_text(vouchers.jsonb, 'exchangeRate') :: NUMERIC(19,14) AS voucher_exchange_rate,
jsonb_extract_path_text(vouchers.jsonb, 'vendorId') :: UUID AS voucher_vendor_id,
jsonb_extract_path_text(vouchers.jsonb, 'invoiceId') :: UUID AS voucher_invoice_id,
jsonb_extract_path_text(vouchers.jsonb, 'batchGroupId') :: UUID AS voucher_batch_group_id,
COALESCE(jsonb_extract_path_text(vouchers.jsonb, 'exportToAccounting') :: BOOLEAN, FALSE) AS voucher_export_to_accounting,
COALESCE(jsonb_extract_path_text(vouchers.jsonb, 'enclosureNeeded') :: BOOLEAN, FALSE) AS voucher_enclosure_needed
FROM
folio_invoice.voucher_lines
CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(voucher_lines.jsonb, 'fundDistributions')) WITH ORDINALITY AS fund_distributions (jsonb)
LEFT JOIN folio_invoice.vouchers ON vouchers.id = jsonb_extract_path_text(voucher_lines.jsonb, 'voucherId') :: UUID
If you want to connect the voucher lines with their invoice lines, please note that you need several UUIDs to create a meaningful connection.
You need the UUIDs:
- invoiceLineId
- fundId
- expenseClassId
The JOIN to the invoice line takes place in a single line with several UUIDs that are linked with an AND. Additionally, you must note that the expense class id is optional. For this reason you need a UNION when querying data.
Example:
WITH invoice_lines AS (
SELECT
invoice_lines.id AS invoice_line_id,
jsonb_extract_path_text(fund_distributions.jsonb, 'fundId') :: UUID AS fund_id,
jsonb_extract_path_text(fund_distributions.jsonb, 'expenseClassId') :: UUID AS expense_class_id
FROM
folio_invoice.invoice_lines
CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(invoice_lines.jsonb, 'fundDistributions')) WITH ORDINALITY AS fund_distributions (jsonb)
)
SELECT
*
FROM
folio_invoice.voucher_lines
CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(voucher_lines.jsonb, 'fundDistributions')) WITH ORDINALITY AS fund_distributions (jsonb)
LEFT JOIN folio_invoice.vouchers ON vouchers.id = jsonb_extract_path_text(voucher_lines.jsonb, 'voucherId') :: UUID
LEFT JOIN invoice_lines ON invoice_lines.invoice_line_id = jsonb_extract_path_text(fund_distributions.jsonb, 'invoiceLineId') :: UUID
AND invoice_lines.fund_id = jsonb_extract_path_text(fund_distributions.jsonb, 'fundId') :: UUID
WHERE
jsonb_extract_path_text(fund_distributions.jsonb, 'expenseClassId') IS NULL
UNION
SELECT
*
FROM
folio_invoice.voucher_lines
CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(voucher_lines.jsonb, 'fundDistributions')) WITH ORDINALITY AS fund_distributions (jsonb)
LEFT JOIN folio_invoice.vouchers ON vouchers.id = jsonb_extract_path_text(voucher_lines.jsonb, 'voucherId') :: UUID
LEFT JOIN invoice_lines ON invoice_lines.invoice_line_id = jsonb_extract_path_text(fund_distributions.jsonb, 'invoiceLineId') :: UUID
AND invoice_lines.fund_id = jsonb_extract_path_text(fund_distributions.jsonb, 'fundId') :: UUID
AND invoice_lines.expense_class_id = jsonb_extract_path_text(fund_distributions.jsonb, 'expenseClassId') :: UUID
WHERE
jsonb_extract_path_text(fund_distributions.jsonb, 'expenseClassId') IS NOT NULL
[top]
In Orders you can link to organizations depending on their role for the order.
There are 3 common use cases where you can link to:
- Vendor informations are stored in the Organizations app
- "Ship to" addresses are stored in the settings from FOLIO
- "Bill to" addresses are stored in the settings from FOLIO
Example:
SELECT
*
FROM
folio_orders.purchase_order__t
LEFT JOIN folio_organizations.organizations__t AS org_vendor ON org_vendor.id = purchase_order__t.vendor
LEFT JOIN folio_configuration.config_data__t AS ship_to ON ship_to.id = purchase_order__t.ship_to
LEFT JOIN folio_configuration.config_data__t AS bill_to ON bill_to.id = purchase_order__t.bill_to
[top]
In Invoices you can link to organizations depending on their role for the invoice.
There are 2 common use cases where you can link to:
- Vendor informations are stored in the Organizations app
- "Bill to" addresses are stored in the settings from FOLIO
Example:
SELECT
*
FROM
folio_invoice.invoices__t
LEFT JOIN folio_invoice.invoices ON invoices.id = invoices__t.id
LEFT JOIN folio_organizations.organizations__t AS org_vendor ON org_vendor.id = invoices__t.vendor_id
LEFT JOIN folio_configuration.config_data__t AS org_bill_to ON org_bill_to.id = jsonb_extract_path_text(invoices.jsonb, 'billTo') :: UUID
[top]