Order - Invoice Analysis

Usage

Compound entity is built around purchase orders. It consists of the following entities from different domains:

• Purchase Order

• Purchase Order Line

• Invoice

• Invoice Line

• Fiscal Year¹

• Organization¹

• Fund²

• Fund Type

• Expense class²

1 - associated entities are derived from Invoice
2 - associated entities are derived from Invoice Line's fund distributions

In order to create queries based on different requirements, any fields of these entities can be used to filter the results. To build a query, 3 necessary parameters need to be provided: Field, Operator and Value. Field can be chosen from any of the mentioned entities. Operator selection usually depends on the field type and determines how the value would be filtered. Value is the input that the filter will be applied with for the provided field.

For example, below are 4 simple query definitions with their descriptions:
1) Provide a list of all invoices and invoice lines associated with ongoing orders

Open

2) Provide a list of what was paid for ongoing orders for each vendor last fiscal year

Open

3) Provide a report on expense classes used for ongoing orders last year for a certain vendor

Open

4) Provide a report on a certain fund’s expenditure over time by vendor

Open

In order to alter the output list columns Actions dropdown can be used and different fields be selected that are desired to be returned by the query

Open

Notes

• Currently the analysis entity contains Fiscal Year as a separate entity with all its fields and an additional field of Invoice entity named Fiscal Year. The additional field would act the same as the Name field of Fiscal Year entity, so they are interchangeable.

• Fund and Expense class related data is dependent on the fund distributions of invoice lines, not purchase order lines

Documentation

This document provides guidance on how the composite_order_invoice_analytics entity is constructed. It serves both as a technical explanation of this entity and a reference for creating future composite entities in a similar fashion.

Composite entities are built by combining data from multiple sources, which may include database tables/views or simple entity types (which themselves map to a table/view). The final goal is to expose a normalized and analytical view over complex, nested structures like invoices and purchase orders.

Data Source Types

There are two primary types of sources used to build composite entities:

• entity-type: Refers to a pre-defined simple or composite entity. Simple entity types usually wrap a direct table/view and provide standardized access to fields. As for composite entity types, they can be a combination of several other types.

• db: Refers to a raw SQL-based data source, typically used in simple entity types

Each source can have a defined order, defines the order in which fields show up in the final entity type that the API exposes. Default value for this field is usually MAX_INT/2

Entity Construction Steps

1. Start from Purchase Order (po)

The entry point for this entity is the Purchase Order, as it forms the foundation for all subsequent data. Without a purchase order, associated invoice lines, fund distributions, and related data would not be meaningful.

The useIdColumns: true setting ensures that each row is treated distinctly when computing uniqueness.

2. Join to Purchase Order Line (pol)

Purchase Order Lines are joined using:

• sourceField: po.id

• targetField: purchase_order_id

This is a left join, ensuring all PO lines are included even if they don’t have associated downstream data.

3. Invoice Line Fund Distribution Join

The invoice_line_fund_distribution alias is not a standalone entity but a composite that exists to flatten the fundDistributions array found in invoice line JSON. It serves the purpose of exposing individual rows for:

• fund_id

• expense_class_id

• invoice_id

• po_line_id (for joining)

These fields are then used to join with other simple entities like fund, expense_class, and invoice.

Implementation Details:

A CROSS JOIN LATERAL is used together with jsonb_array_elements to extract individual fundDistribution objects from the invoice line JSON array.

The resulting structure allows us to create a normalized view over array-based data and extract individual fields from each distribution.

Additional Joins

After extracting the fund and expense class references from the fund distribution array, we proceed to join additional related entities:

• Invoice (invoice_line_fund_distribution.invoice_id → invoice.id)

• Organization (invoice.vendor_id → organization.id)

• Fiscal Year (invoice.fiscal_year_id → fiscal_year.id)

• Fund (invoice_line_fund_distribution.fund_id → fund.id)

• Fund Type (fund.fund_type_id → fund_type.id)

• Expense Class (invoice_line_fund_distribution.expense_class_id → expense_class.id)

Each of these joins uses the standard sourceField to targetField pattern with left join semantics and an order value to maintain evaluation sequence.

Row Differentiation and useIdColumns

To avoid ambiguity in the result set, any source that contributes to row-level uniqueness must have useIdColumns: true.

For example, if a PO has multiple lines and each line has multiple fund distributions, then uniqueness depends not only on the PO but also on the PO Line and Fund. Omitting useIdColumns on these levels would cause duplicate detection issues.

Summary Table

Best Practices for Future Composite Entities

1. Start with the primary/root entity to ensure all dependent data can be meaningfully joined.

2. Use sourceField and targetField consistently to define join conditions.

3. Respect loading order by using ascending order values to resolve dependencies before use.

4. Use useIdColumns: true for all sources that contribute to uniqueness of the final dataset.

5. Handle JSON arrays with CROSS JOIN LATERAL when normalization is required.

6. Avoid redundant sources. If a source is already transitively available (e.g., invoice line via fund distribution), do not include it again.

7. Use essentialOnly: true to fetch only essential data from a given entity (not to include all fields).

Scripts used to set up the test data:

Open