fct_invoices


Description

Invoices or credit notes from helpers to funders or other helpers. For example, it can be from a corporate helper to a funder (e.g. monthly fees, etc.), a care worker to funder (e.g. full visit cost), a corporate helper to a care worker (e.g. platform fees, claiming back companies portion), or a TPP Helper to a funder.

Columns

Column Type Children Parents Comments
invoice_id int8
fct_invoice_items.invoice_id Implied Constraint R
fct_purchase_order_items.supplier_generated_invoice_id Implied Constraint R

The unique invoice ID, e.g. 8413.
category text

The type of document. E.g. “Credit Note” or “Invoice”.
is_adhoc bool

If true, then the invoice (or credit note) was created directly by finance instead of generated by a billing run. E.g. true or false.
state text

Either “published” or “unpublished”. See also published_at.
company_id int8
dim_companies.company_id Implied Constraint R

The company ID. E.g. 345.
source_type varchar

The source of the invoice or credit note. E.g. “FinanceImport”, “Invoice”, “SupplierGeneratedInvoice”, null.
source_id int8

The ID of the source record specified by source_type. E.g. 8354.
transacted_at timestamp

The date and time (in UTC) that a transaction was created for the invoice in the funding account (aka Care and services account). E.g. 2021-03-02 00:12:59.996101.
published_at timestamp

The date and time (in UTC) that the invoice or credit note was published (as specfied by the user). E.g. 2021-03-02 00:12:59.996101.
issued_at timestamp

For ad-hoc invoices and credit notes, it is a date and time selected by the user. For visits, it is the checked_out_at time. Purchase orders and memberships use the billing run end time. E.g. 2021-03-02 00:12:59.996101.
issued_at_company_tz timestamp
billing_run_id int8

The ID of the billing run that generated the invoice or credit note. It will be null if ad-hoc or otherwise generated outside of a billing run. E.g. 29567.
billing_run_closed_at timestamp

The date and time (in UTC) that the user closed the billing runs. Closing a billing run indicates that the time period is complete and that the invoices and credit notes have been confirmed for the period. Billing runs can never occupy overlapping periods of time. E.g. 2021-03-02 00:12:59.996101.
issuer_profile_id int8

The profile ID of the helper issuing the invoice. (It could be the corporate helper.) E.g. 38593.
recipient_profile_id int8

The profile ID of the individual or company receiving the invoice. (It could be a membership funder, the corporate helper or another helper.) E.g. 210956.
viewer_membership_id int8
viewer_helper_id int8

The ID of a helper that has permission to view the invoice (optional). E.g. 2839, null.
item_source_types _varchar

An array of the distinct item source types that appear in the invoice. These can include “PurchaseOrderLine”, “Membership”, “Visit”, and/or “NULL”. It can also be null. E.g. “{Visit,NULL}”, “{Membership}”, null, etc.
service_delivered_at timestamp

The date and time (in UTC) that the product or service associated with the invoice was delivered. E.g. 2023-01-08 23:00:00.
total_gross_amount numeric

The total of all line items, including tax. E.g. 89.99.
total_net_amount numeric

The total of all line items, excluding tax. E.g. 81.81.
item_count int8

The number of line items in the invoice. It can be zero. E.g. 4.

Indexes

Constraint Name Type Sort Column(s)
fct_invoices_pkey Primary key Asc invoice_id
fct_invoices_billing_run_id_idx Performance Asc billing_run_id
fct_invoices_company_id_idx Performance Asc company_id
fct_invoices_viewer_helper_id_idx Performance Asc viewer_helper_id
fct_invoices_viewer_membership_id_idx Performance Asc viewer_membership_id

Relationships

Close relationships within degrees of separation